Теоретична довідка на тему Використання розширеного фільтра

Використання розширеного фільтра в Excel

На відміну від автофільтра, де критерії заносяться під час роботи фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і занесені у визначений діапазон комірок таблиці. Цей діапазон бажано має міститися над списком і має бути відокремленим від списку щонайменше одним порожнім рядком.

Простий критерій складається з двох комірок, розміщених у таблиці вертикально: верхня комірка містить точну копію заголовка поля списку, в якому перевіряється умова пошуку даних, а нижня комірка містить безпосередньо саму умову по­шуку.

Складний критерій містить у собі декілька простих крите­ріїв, сполучених або по вертикалі – декілька умов на одне по­ле, або по горизонталі – по одній умові на декілька полів.

Критерії, сполучені по горизонталі, поєднуються зв'язкою "И", тобто записи зі списку за таким критерієм вибираються у випадку, коли всі прості критерії, що входять у складний кри­терій, виконуються.

Критерії, сполучені по вертикалі, поєднуються зв'язкою "ИЛИ", тобто записи зі списку за таким критерієм вибираються, коли хоча б один із простих критеріїв, що входять у складний критерій, виконується. У таких критеріях допускається заголо­вок поля заносити тільки один раз.

Складений критерій – це сукупність декількох складних критеріїв – містить по декілька умов на декілька полів.

Обчислювальний критерій може бути різновидом просто­го або складного критерію, де в якості умови виступає функція або формула. Записи за таким критерієм вибираються зі списку за значенням, отриманим у результаті обчислення цієї формули або функції.

Після створення критеріїв можна використовувати Расши­ренный фильтр для організації запитів. Розширений фільтр ви­кликається на вкладці Данныев групі Сортировка и фильтр командою Дополнительно. З'явиться діалогове вікно розширено­го фільтру.

У цьому вікні автоматично позначиться діапазон списку (зона "Исходный диапазон"), якщо список був попередньо виді­лений або курсор мишки розміщувався на будь-якому із запи­сів списку.

У зону "Діапазон условий" треба занести адреси потрібного діапазону критерію і, якщо натиснути на [Ok], у списку будуть показані тільки ті записи, що відповідають уведеному критерію.

Якщо ж вибрати мишкою покажчик "Скопировать результат в другое место", то активізується зона "Поместить результат в диапазон", де треба вказати адресу лі­вої верхньої комірки майбутнього діапазону для вставляння вибраних зі списку записів.

При повторній фільтрації будуть проглядатися усі рядки, і приховані і відкриті.

Приклад

Нижче наведені приклади створення критеріїв для пошуку такої інформації:

1. Критерій 1 – вибрати зі списку всі записи про фірму "САМ". За правилами створення критеріїв діапазон для них буде розміщуватися у робочому аркуші по рядках.

Такий запит включає одну умову (назву фірми – "САМ"), тому створюється простий критерій для пошуку в полі Фірма/місто фірми "САМ".

У комірку G1 копіюється заголовок поля Фірма/місто, а в комірку G2 – умова для пошуку – "САМ" - Київ:

Таким чином, діапазон створеного простого критерію роз­міщується у комірках G1:G2.

2. Критерій 2 – вибрати зі списку всі записи, що стосуються або фірми "САМ", або "Вояж", або "Венея".

Такий запит включає три умови, що накладаються на поле Фірма/місто (фірма – або "САМ", або "Вояж", або "Венея"), тому створюється складний критерій із трьох простих, які поєднані зв'язкою "ИЛИ".

У комірку GЗ копіюється умова для пошуку – "Вояж"- Донецьк, а в комірку G4 – " Венея "- Донецьк:

Діапазоном створеного складного критерію будуть комірки G1:G4.

3. Критерій 3 – вибрати зі списку всі записи, де кількість груп більше ніж 10 і менше ніж 25.

Такий запит включає дві умови, тому створюється критерій для пошуку в полі Кількість груп, які поєднані зв'язкою "И".

У комірки G1 і H1 копіюється заголовок поля Кількість груп, а в комірку G2 – умова для пошуку  >10, а в комірку H2 – умова для пошуку <25:

Діапазоном створеного складного критерію будуть комірки G1:H2.

Нижче розміщені дані для фільтрації і результати запитів.

Дані для критерію 1:

Результат першого запиту:

Дані для критерію 2:

Результат другого запиту:

Дані для критерію 3:

Результат третього запиту:

ЗВЕДЕНІ ТАБЛИЦІ

Зведені таблиці використовують для аналізу значної кількості даних у великих таблицях. Зведена таблиця містить всі або лише потрібні для аналізу дані основної таблиці, які відображені на екрані так, щоб залежності між ними проглядалися якнай­краще. Користувач залежно від умови задачі має лише зазначити, що відображати в заголовках рядків і стовпців, які дані відображати на їх перетині, а також по якому полю з основної таблиці групувати дані. Зведені таблиці  можна створити на вкладці Вставка командою Сводная таблица із групи Таблицы.

З повагою ІЦ “KURSOVIKS”!