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

Для фільтрації списків програма Місгозой ЕхсеІ крім команди Автофильтр пропонує користувачам команду Расширенный фильтр, яку використовують для створення складних умов відбору даних.

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

• Спочатку слід створити таблицю-критерій. Для цього під початковою таблицею введіть назви стовпців, по яких ви збираєтеся провести фільтрацію. Назви стовпців краще всього вводити копіюванням з відповідного рядка початкової таблиці, оскільки необхідно співпадання (з точністю до пропуску) назв стовпців в таблиці критеріїв і в початковій таблиці. Рядком нижче під назвами введіть з клавіатури критерії відбору. Як критерії можуть виступати текстові записи, співпадаючі із значеннями осередків (для стовпців з текстом), або числа або умови, оператори порівняння, що містять (для стовпців з числами). Таблиця-критерій може розміщуватися, взагалі кажучи, в будь-якому місці листа, але зручніше її поміщати все ж таки під початковою таблицею.

• Активізуйте будь-який елемент початкової таблиці і виконайте команду Данные - Фільтр - Розширений фільтр. В результаті з'явиться діалог Расширенный фільтр, в якому потрібно задати параметри розширеного фільтру (рис. 8).

• У верхнє поле введення вікна Розширений фільтр потрібно ввести діапазон фільтрованої таблиці (програма звичайно самf правильно встановлює початковий діапазон), а в середньому полі введення необхідно задати діапазон таблиці-критерію (в даному випадку $А$13:$Е$14).

командою Автофильтр, а саме:

- задавати для кількох стовпців умови,з'єднані логічним опера­тором ИЛИ. Наприклад, із загального списку співробітників можна вибрати осіб віком старше 50 років або таких, річна за­робітна плата яких перевищує 10000 грн;

- задавати три або більше умовдля конкретного стовпця з вико­ристанням хоча б одного логічного оператора ИЛИ. Наприк­лад, можна вивести на екран список співробітників, чиї прізви­ща починаються з "А", "Г" чи "Н";

- задавати обчислювані умови.Наприклад, можна вивести на ек­ран списки тільки тих співробітників, заробітна плата яких на 25 % перевищує середню.

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

Команда Расширенный фильтр вимагає визначення умов відбору рядків в окремому діапазоні комірок робочого аркуша. Діапазон умов рекомендується задавати в порожніх рядках над списком.

Для використання розширеного фільтрадо створеного засобами Ехсеl списку необхідно виконати такі дії.

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

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

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

- умови одного рядка вважаються з'єднаними логічним операто­ром Й (тобто всі умови повинні виконуватись одночасно);

- умови у двох рядках вважаються з'єднаними логічним операто­ром ИЛИ (тобто достатньо виконання хоча б однієї умови). • Для того щоб знайти дані, які відповідають одній умові в одному стовпці або іншій умові в іншому стовпці, слід ввести умови від­бору в різні рядки діапазону умов відбору. Наприклад, наведений далі діапазон умов відображає всі рядки, що містять значення "Ар­тикул 1" у стовпці "Товар" або "Іванов" у стовпці "Менеджер", або обсяг продажів на суму понад 1000 грн у стовпці "Обсяг про­дажів".

За наявності для одного стовпця двох і більше умов відбору треба ввести їх безпосередньо одна під одною в суміжні рядки. Наприклад, наведе­ний діапазон умов відображає рядки, що містять у стовпці "Менеджер" список співробітників, чиї прізвища починаються з "А", "Г" та "Н".

Для того щоб знайти дані, які відповідають одному з кількох наборів умов, кожний з яких містить умови більше як для одного стовпця, необхідно ввести ці умови відбору в окремі рядки. На­приклад, наведений діапазон умов відображає рядки, що містять у стовпці "Менеджер" співробітників, прізвища яких починаються з "І" і які мають обсяг продажів на суму понад 5000 грн., або пріз­вища яких починаються з "Г", а обсяг продажів на суму менше 6000 грн.

Кілька умов, необхідно вклю­чити в діапазон умов кілька стовпців з однаковими заголовка­ми. Наприклад, наведений діапазон умов відображає рядки, які містять у стовпці "Обсяг продажів" обсяг продажів від 5000 до 8000 грн.

Задаючи текстові умови, слід дотримуватися таких правил:

- одна літера означає, що пошуку підлягають усі значення, що по­чинаються із зазначеної літери;

- символ ">" або "<" означає, що пошуку підлягають значення, що за абеткою стоять після введеного текстового значення або перед ним;

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

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

Перемикач Обработка слід встановити в положення Фильтровать список на месте і натиснути кнопку ОК.

У результаті виконання команди Расширенный фильтр прихо­вуються всі рядки, що не задовольняють введені умови. Номери відібраних рядків виділяються синім кольором.

Умовою відбору може бути обчислене значення, що є результатом виконання певної формули. При використанні обчислюваної умови слід дотримуватися таких правил:

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

- посилання на комірки, що перебувають за межами списку, по­винні бути абсолютними;

- посилання на комірки списку повинні бути відносними (за од­ним винятком, який розглянемо далі).

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

- у комірку, що перебуває за межами списку (наприклад, D2), ввести формулу =СРЗНАЧ(Р8:РЗО), за допомогою якої обчис­люється середнє значення за стовпцем "Обсяг продажів" з діа­пазону Р8:РЗО;

№ 38. Правила побудови критеріїв для вибірки