Фільтрування даних в ms Excel за допомогою Розширеного фільтру

За допомогою розширеного фільтра можна виконувати пошук даних за складних умов, при цьому в окремому діапазоні відображаються на екрані умови, відповідно до яких слід виконати пошук та відображення даних.

Умови можуть бути такими:

1. кілька умов для одного стовпчика даних;

2. одна умова для кількох стовпчиків даних;

3. одна умова для одного стовпчика та інша для іншого;

4. набір умов для кількох стовпчиків;

5. пошук за умовою у вигляді формули.

За допомогою розширеного фільтра можна виконати фільтрацію на місці чи помістити відфільтровані записи на окремий аркуш.

Порядок фільтрування даних з використанням розширеного фільтру:

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

Приклад:

2. Введіть у рядки під заголовками стовпців потрібні умови добору.

Приклади умов:

1. Декілька умов для одного стовпця

У разі наявності двох або більше умов для одного стовпця, введіть ці умови безпосередньо одну під одною в окремі рядки.

Наприклад, наведений нижче діапазон умов відображає рядки, які містять у стовпці «Продавець» значення «Давиденко», «Петренко» або «Василенко».

2. Одна умова для декількох стовпців

Щоб знайти в декількох стовпцях дані, які відповідають одній умові, введіть усі критерії в один рядок діапазону критеріїв.

Наприклад, нижченаведений діапазон критеріїв відображає всі рядки, які містять значення «Овочі» у стовпці «Товар», «Давиденко» у стовпці «Продавець» і обсяг продажу більше 1 000 грн.

3. Одна умова в одному стовпці або інша умова в іншому стовпці

Щоб знайти дані, які відповідають одній умові, в одному стовпці або дані, які відповідають іншій умові, в іншому стовпці, введіть критерії в різні рядки діапазону критеріїв.

Наприклад, нижченаведений діапазон умов відображає всі рядки, які містять значення «Овочі» у стовпці «Товар», «Давиденко» у стовпці «Продавець» і обсяг продажу більше 1 000 грн.

4. Один або два набори умов для двох стовпців

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

Наприклад, нижченаведений діапазон умов відображає рядки, які містять як значення «Давиденко» у стовпці «Продавець», так і обсяг продажу більше 3 000 грн., а також рядки для продавця Петренко з обсягами продажу більше 1 500 грн.

5. Більше двох наборів умов для одного стовпця

Щоб знайти рядки, які відповідають більше ніж двом наборам умов, вставте декілька стовпців з однаковими заголовками.

Наприклад, нижченаведений діапазон умов відображає продаж на суму від 5000 до 8000, а також продаж на суму менше 500.

6. Умови, створені як результат виконання формули

Як умову можна використовувати обчислюване значення, яке є результатом виконання формули Створюючи умову за допомогою формули, не використовуйте заголовок стовпця як заголовок умови; або залишіть критерій без заголовка, або використайте заголовок, який не є заголовком стовпця в діапазоні.

Наприклад, нижченаведений діапазон умов відображає рядки, які містять у стовпці С значення, більше за середнє значення клітинок діапазону С7:С10.

7. Знаки підстановки

Щоб знайти текстові рядки, які містять декілька послідовностей певних знаків, скористайтеся знаками підстановки.

Наведені нижче знаки підстановки можна використовувати в умовах порівняння для фільтрів, а також під час пошуку та заміни.

Використовуйте	Щоб знайти
? (знак запитання)	Один будь-який символ Наприклад, умові «ст?ль» відповідають слова «сталь» і «стиль»
* (зірочка)	Будь-яку кількість символів Наприклад, умові «*-західний» відповідають слова «південно-західний» і «північно-західний»
~ (тильда), за якою йде знак ?, * або ~	Знак запитання, зірочку, тильду Наприклад, за умовою «ан91~?» буде зайдено слово «ан91?»

3. Клацніть клітинку в діапазоні.

4. У меню Данные виберіть пункт Фильтр, а потім – команду Розширенный фильтр.

5. Щ об відобразити результат фільтрування, приховавши рядки, які не відповідають указаним умовам, виберіть варіант фільтрувати список на місці. Щоб скопіювати відфільтровані рядки, які відповідають указаним умовам, до іншої області аркуша, виберіть варіант скопіювати результат до іншого розташування, клацніть у полі Діапазон для результату, а потім клацніть у лівому верхньому куті області вставки.

6. У полі Диапазон условий введіть посилання на діапазон умов, включаючи заголовки умов.

7. Щоб змінити спосіб фільтрації даних, змініть виконайте фільтрацію.

Поради:

Якщо призначити діапазону ім’я Умови, посилання на діапазон буде автоматично з’являтися в полі Діапазон умов. Можна також указати ім'я База_даних для діапазону даних, які фільтруються, та ім'я Витягнути для області вставки рядків, і ці діапазони будуть автоматично з’являтися в полях Вихідний діапазон і Діапазон для результату відповідно.

У разі копіювання відфільтрованих рядків в інше місце можна вказати стовпці, які включатимуться до копії. Перед фільтрацією скопіюйте заголовки потрібних стовпців до першого рядка області вставки результатів. Виконуючи фільтрацію, введіть посилання на скопійовані заголовки стовпців у полі Діапазон для результату. Скопійовані рядки будуть містити лише стовпці, заголовки яких були скопійовані.

Лабораторна робота №2 (4 год)

Тема: Аналіз даних з використанням зведених таблиць.

Мета: навчитися проводити аналіз даних за допомогою зведених таблиць та діаграм.

Забезпечення заняття: роздатковий матеріал, комп’ютери із програмою Excel, файл svod.xls.