Технологія вибіркового використання даних БД у Excel: фільтрація, пошук даних, реалізація запитів

Фільтрація списку за допомогою розширеного фільтра

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

1  Скопіюйте зі списку заголовки стовпців, які фільтруються.

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

3  Введіть у рядки під заголовками умов необхідні критерії добору. Переконаєтеся, що між значеннями умов і списком знаходиться як мінімум один порожній рядок.

4  Вкажіть осередок у списку.

5  Виберіть пункт Фільтр у меню Дані, а потім — команду Розширений фільтр.

6  Щоб показати результат фільтрації, сховавши непотрібні рядки, установите перемикач Обробка в положення Фільтрувати список на місці.

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

7  Введіть у поле Діапазон критеріїв посилання на діапазон умов добору, що включає заголовки стовпців.

Якщо на листі існує діапазон з ім'ям Критерії, то в поле Діапазон умов автоматично з'явиться посилання на цей діапазон.

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

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

На осередки одного стовпця накладаються три чи більш умови добору

Щоб задати для окремого стовпця три чи більш умови добору, введіть умови в осередки, розташовані в суміжних рядках. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або «Белов», «Батурін» чи «Сушкин» у стовпці «Продавець» (Рис. 7.).

Продавець

Белов

Батурін

Сушкин

Рис. 7.

Умова добору накладається на осередки двох чи більш стовпців

Щоб накласти умови добору не кілька стовпців одночасно, введіть умови в осередки, розташовані в одному рядку діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Продукти» у стовпці «Товар», «Белов» у стовпці «Продавець», і реалізації, що мають суму, більше 1000 (рис. 8.).

 

Товар	Продавець	Продажу
Продукти	Белов	>1000

Рис. 8.

Для накладення обмежень на значення в різних стовпцях і відображення тільки потрібних рядків також використовується команда Автофільтр у меню Дані.

Щоб вибрати рядка, що задовольняють одному з декількох умов, накладених на різні стовпці, введіть умови в осередки, розташовані в різних рядках діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або «Продукти» у стовпці «Товар», або «Белов» у стовпці «Продавець», або реалізації, що мають суму, більше 1000 (рис. 9.).

 

Товар	Продавець	Продажу
Продукти
	Белов
		>1000

Рис. 9.

Щоб накласти складна умова добору, уведіть його складові частини в окремі рядки діапазону умов. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «Белов» у стовпці «Продавець» і реалізації, що мають суму, більше 3000 чи рядка, що містять «Батуріна» у стовпці «Продавець» і реалізації, що мають суму, більше 1500 (рис. 10.).

 

 

Продавець	Продажу
Белов	>3000
Батурін	>1500

Рис. 10.

В умові фільтрації можна використовувати значення, що повертається формулою. При завданні формул в умовах не використовуйте як заголовок умови заголовки стовпців списку. Уведіть заголовок, що не є заголовком стовпця чи списку залишіть заголовок умови незаповненим. Наприклад, для наступного діапазону умов будуть відображені рядки, у яких значення в стовпці G перевищує середнє значення в осередках E5: E14; заголовок умови не використовується (рис.11.).

 

=G5>СРЗНАЧ($E$5:$E$14)

Рис.11.

Використовувана в умові формула повинна посилатися або на заголовок стовпця (наприклад, «Продажу»), або на відповідне поле в першому записі. У приведеному прикладі G5 посилається на відповідне поле (стовпець G) першого запису (рядок 5) списку.