Розширений фільтр

Пункт меню Данные – Фильтр – Расширенный фильтр дозво­ляє фільтрувати список за складнішими критеріями. Відфільтровані записи можна винести в інше місце робочого листа Ехсel, не зіпсу­вавши первинний список, а також можна вивести частину наданого списку з декількома потрібними стовпцями.

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

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

Якщо умови зв'язані логічним оператором И, вони повинні бути записані в одному рядку. Якщо умови зв'язані логічним оператором ИЛИ, вони повинні бути записані в різних рядках.

Приклад. Вивести відомості про робітників 4 розряду, які отри­мали заробітну плату менше за 650 грн.

Таблиця критеріїв матиме вигляд:

Розряд	Одержати
	< 650

Результати фільтру – на рис. 3.4.

Рисунок 3.4 – Результати фільтру

Якщо таблицю критеріїв записати у такому вигляді,

Розряд	Одержати
	< 650

то ми отримаємо зовсім інший результат (див. рис. 3.5). Будуть виве­дені дані про всіх робітників, що мають 4 розряд, та про робітників всіх розрядів (4, 5 і 6), які отримали зарплату менше за 650 грн. Сюди потра­пить і Петренко О.В., який отримав 652 грн., але має 4 розряд.

Рисунок 3.5 – Результати роботи фільтру

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

Наприклад, щоб одержати номер бригади та прізвища робітни­ків, які отримали заробітну плату менше за 650 грн., треба:

До клітинок В20:В21 записати таблицю умов (див. рис. 3.6), а в діапазон А23:С23 скопіювати заголовки для вихідного документу. Потім в меню Данные – Фильтр – Расширенный фильтр встановити перемикач "скопировать результат в другое место" ізаповнити поля Исходный диапазон, Диапазон условий, Поместить результат в диапазон так, як на рис. 3.7.

Таким чином, для виконання команди Расширенный фильтр треба виконати такі дії:

ü сформувати у вільному місці робочого листа таблицю критеріїв;

ü сформувати шапку вихідного документа у разі потреби;

ü виділити первісний список або клітинку в ньому, якщо виконані всі умови для оформлення списку;

ü викликати команду Расширенный фильтр.

Рисунок 3.6 – Таблиця критеріїв та заголовки для результатів

Рисунок 3.7 – Вікно Расширенный фильтр

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

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

ü Формула повинна містити умову і посилатися на відносну адресу клітинки з відповідним полем в першому записі списку або на текст заголовку стовпця списку.

ü Всі інші посилання на клітинки повинні бути абсолютними.

ü Якщо формула використовує клітинку з першим записом спис­ку, то вона приймає одне з двох значень: ИСТИНА або ЛОЖЬ. Виводяться ті рядки, які надають формулі значення ИСТИНА.

ü Якщо формула використовує заголовок стовпця, то виводиться значення помилки #ИМЯ? або #ЗНАЧ!. Цю "помилку" випра­вляти не треба..

Приклад. Здійснити пошук тих робітників, розмір заробітної плати яких (стовпчик Одержати) перевищує середнє значення заро­бітної плати по всіх бригадах. (основна таблиця – рис. 3.1).

Для контролю знайдемо середній заробіток по бригадах. Він становить 658,88 грн. Створимо таблицю умов з критерієм, що обчис­люється. Записати формулу, використовуючи функцію СРЗНАЧ, та блок критеріїв можна одним із способів:

=Н4 > СРЗНАЧ($H$4: $H$18) або

=Одержати > СРЗНАЧ($H$4: $H$18)

Блок критеріїв F20: F21 має заголовок " пошук ", блок критеріїв H20: H21 заголовку не має (див. рис. 3.8).

Рисунок 3.8 – Таблиці критеріїв та результати фільтру

3.3 Контрольні питання

1. Як встановити Автофильтр? Як його відмінити?

2. Які можливості надає користувачу опція Автофильтр?

3. Як встановити розширений фільтр?

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