Фільтрація даних в БД

Таблиці можуть бути дуже великими, що утруднює їх аналіз в конкретному напрямку. За допомогою команд ДанныеÞФильтр можна локалізувати деяку підмножину даних у списку. Підменю команди Фильтр містить два варіанти: Автофильтр і Расширенный Фильтр. Відпрацьо­вування першої із цих команд призводить до появи в комірках із заголовками стовпчиків кнопок розкриття списку всіх значень даного стовпчика. Клацання мишки на будь-якому із них призводить до того, що видимими залишаються лише рядки з указаними значеннями. При цьому інші рядки будуть тимчасово сховані. Крім конкретних значень у розкритих списках (див. рисунок 8.10) з'являються також команди, які забезпе­чують сортування даних (Сортировка по возрастанию, Сортировка по убыва­нию); знаходження певної кількості найбільших або найменших значень (Первые 10…) та завдання більш складної умови з використанням нерівностей (Условие…).

Рисунок 8.10 - Приклад задання автофільтру

Активація команди Первые 10… призводить до появи діалогового вікна, в якому можна вказати, скільки саме та яких саме (з найбільшими чи наймен­шими значеннями даного стовпця) записів слід показати. Крім того, можна замість параметра элемен­тов списка ввести % от количества элементов і тоді можна вивести певну кількість відсотків записів.

Активація команди Условие … приводить до появи діалогового вікна Пользовательский автофильтр, в якому можна ввести більш складні умови по заданому полю з використанням одного або двох операторів порівняння Excel (див. рис. 8.11). Якщо використовують­ся два оператори порівняння, то вони можуть бути об’єднані логічними операто­рами И або ИЛИ. В першому випадку відбираються ті записи, для яких справедливі і перша, і друга умова, в другому - тільки ті записи, для яких справедлива хоча б одна з умов.

Для числових полів і полів типу «Дата» оператори порівняння – це звичні для користувача співвідношення: равно, не равно, больше, больше или равно, меньше, меньше или равно. Для текстових полів можна використову­вати більш специфічні оператори порівняння: начинается с, не начинается с, заканчивается на, не заканчивается на, содержит, не содержит. В прикладі, показаному на рис. 8.11, задається виведення тільки тих записів, для яких значення поля З/п знаходяться в межах від 800 до 1500, включно.

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

Щоб видалити автофільтр для конкретного стовпчика необхідно розкрити відповідний список автофільтра і вибрати в ньому Всё. Щоб скасувати всі застосовані автофільтри, потрібно вибрати в меню Данные команду Фильтр і потім Отобразить все. Для видалення всіх автофільтрів і їхніх кнопок слід вибрати команду Автофильтр ще раз, видаляючи таким чином галочку поруч із назвою цієї команди в підменю Фильтр із меню Данные.

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

Зокрема, команда Расширенный фильтр дозволяє:

· задати умови із логічними операторами И або ИЛИ для декількох стовпчиків;

· задати три або більше умов для конкретного стовпчика з використанням логічних операторів;

· задати умови, що розраховуються, наприклад, показати на екрані тільки тих співробітників, у яких заробітна плата принаймні на 25 відсотків більша ніж середня по фірмі.

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

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

Діапазон умов повинен містити принаймні два рядки. В першому обов’язково розміщується назва стовпчика (поля), по якому задається умова, в другому та наступних – власне самі умови. Заголовки в діапазоні умов повинні точно збігатися з заголовками стовпчиків у списку. Для забезпечення точності ці заголовки найкраще створювати, виділяючи заголовки стовпчиків у списку і копіюючи їх у верхній рядок діапазону умов. У діапазон умов слід включати заголовки тільки тих стовпчиків, що використовуються в умовах добору.

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

Слід мати на увазі, що:

· при накладенні двох або більше умов ИЛИ для одного стовпчика слід розташувати умови вибору нижче заголовку стовпця в різних рядках;

· при накладенні умовдля двох стовпчиків з використанням оператора И слід ввести імена цих стовпчиків в суміжні клітинки, а умови повинні знаходитись нижче заголовка на однакових рядках;

· при накладенні умов з використанням оператору ИЛИ для двох стовпчиків слід ввести імена цих стовпчиків в суміжні клітинки, а умови повинні знаходитись нижче заголовка на різних рядках.

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

В прикладах, наведених на рис. 8.12, умови діапазону В30:В32 відповідають записам, в яких поле Прізвище набуває або значення Сидоров, або значення Іванов, тобто в відфільтрованій базі даних будуть записи лише про співробітників з прізвищами Сидоров та Іванов. Умови діапазону D30:E31 відповідають записам, в яких співробітник працює на посаді економіста і має при цьому стаж, який перевищує 180 місяців. Умови діапазону G30:H32 відповідають записам, в яких співробітник або працює на посаді економіста і має при цьому стаж, який перевищує 60 місяців, або працює на посаді бухгалтера і має при цьому стаж, який перевищує 48 місяців. Умови діапазону D34:F36 відповідають записам, в яких співробітник або працює на посаді економіста і має при цьому стаж, який перевищує 60 місяців, або працює на посаді бухгалтера і має склад сім’ї, який перевищує 2 особи.