Фільтрація таблиці даних

Для обробки таблиць даних у програмі Excel призначено низку функцій і процедур. Для простих умов фільтрації даних найзручніше скористатися командою Автофильтр з підменю Фильтр пункту меню Данние. Якщо помістити курсор всередину таблиці даних (списку) і виконати цю команду, то справа від кожного заголовка

стовпця з'явиться кнопка зі стрілкою вниз

, за допомогою якої

можна виконувати різні команди фільтрації списку. Коли потрібно відфільтрувати дані лише у деяких стовпцях, можна застосувати ав-тофільтр лише для них (треба, щоб вони були розташовані поряд, створювали один діапазон комірок). Перед виконанням команди Автофильтр потрібно ці стовпці виділити.

Кнопка

розкриває команди автофільтра

, а та-

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

лад

Найдено запнсей: 2 из 9

. На відміну від сортування порядок відфіль-

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

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

Перша команда автофільтра Все відміняє будь-які введені умови філь­трації і повертає таблицю даних до початкового вигляду. Команда Пер-вьіе 10... призначена для стовпців з числовими даними. За цією командою відкривається діалогове вікно Наложение условия по списку

Основне праве поле

призначене для відображення визначеної в лівому полі (

) кількості

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

Параметр

|% от количества злементов _^J

застосовується для відображення визначеної у відсотках кількості рядків з найбільшими або найменшими значеннями.

Команда Условие дає змогу задавати складніші критерії фільтрації, ніж точна рівність певному значенню. За цією командою відкриваєть­ся діалогове вікно Пользовательский автофильтр, в якому визначають одну (перший рядок) або дві умови (перший і другий рядки), що мо­жуть бути зв'язані між собою логічними операторами "Й" (елементи стовпця повинні задовольняти як першу, так і другу умову) або "ИЛИ" (елементи мають задовольняти одну з цих двох умов):

Якщо стовпець містить числові дані, для них можна встановити звичайні умови порівняння (равно, не равно, меньше, меньше илирав-но і т. ін.). Для текстових даних можуть бути застосовані умови начи-нается с, не начинается с, заканчивается на, содержит тощо, а також використані підстановочні знаки: "?" — один будь-який довільний символ, "*" — будь-яка кількість будь-яких припустимих символів. При пошуку в тексті власне символу "знак питання" або "зірочка", перед ними треба поставити знак "~"(тильда).

У разі недостатності фільтрації даних за одним стовпцем, можна за­дати додаткові умови для інших стовпців. Додаткові критерії відбору зв'язані логічним оператором "Й", тобто зрештою залишаться лише записи, що одночасно задовольняють усі задані умови.

Для деактивації автофільтра слід повторно виконати команду Ав-тофильтр з підменю Фильтр пункту меню Данние.

У разі застосування розширеного фільтра можна задати складніші умови відбору записів зі списку (більш як два набори умов для одного стовпця, одна умова для кількох стовпців, умови, що за­даються за результатом обчислення за формулою). На відміну від ав­тофільтра, для розширеного фільтра умови фільтрації для списку вводяться в окремий діапазон комірок. Після виконання команди Расширенний фильтр з підменю Фильтр пункту менюДанньїе відкри­вається діалогове вікно з такою ж назвою, в якому потрібно встано­вити діапазони комірок для таблиці даних, умов відбору і резуль­татів фільтрації, а потім натиснути кнопку <ОК>:

Якщо активною є комірка всередині таблиці даних, Excel автома­тично визначає діапазон комірок списку, в іншому разі слід вказати цей діапазон, включаючи заголовки стовпців.

Для формулювання умов відбору в розширеному фільтрі треба в будь-якому вільному місці робочого аркуша створити додатковий рядок із заголовками стовпців, а наступні кілька рядків використати для введення в них умов відбору записів таблиці даних. Можна обме­житися тільки тими заголовками стовпців (назвами полів), для яких встановлюються умови фільтрації. Умови відбору в комірках відпо­відних стовпців можна записувати у кілька рядків. Критерії в одно­му рядку з'єднуються логічним оператором "Й", в різних рядках — логічним оператором "ИЛИ". Якщо в діапазоні умов деяка комірка порожня, це означає, що таку умову задовольнятиме будь-яке зна­чення у стовпці. Тому фільтрація списку не відбудеться, якщо в діапа­зоні умов виявиться порожній рядок.

У найпростішому випадку, коли зі списку треба отримати тільки записи з одним значенням в одному стовпці (наприклад, на рис. на с. 19 виділити лише записи, що мають значення "Васильківська" для стовпця "Вулиця"), достатньо для діапазону умов вказати тільки дві комірки — з назвою стовпця і нижче з потрібним значенням

. Якщо ж цікавлять два значення з одного стовпця (логіч-

ний оператор "ИЛИ" — або "Васильківська", або "Антоновича"), в діапазон умов слід нижче додати ще одну комірку з відповідним зна-

ченням

. У разі необхідності обмежити відібрані записи

додатковою умовою для іншого стовпця (цікавлять з вулиці "Василь­ківська" лише записи з прізвищем "Петренко", тобто з'єднані логіч­ним оператором "Й"), у тих самих рядках слід додати назву стовпця та

відповідне значення

. Для іншої ситуації, коли

до відібраних записів потрібно додати записи з умовою для Іншого стовпця (цікавлять усі з вулиці "Васильківська" та всі з прізвищем "Петренко", тобто зв'язані логічним оператором "ИЛИ"), значення для другого стовпця записують у наступному рядку

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

всю таблицю даних, а не тільки відфільтровану її частину, тому не потрібно виконувати команду Отобразить все перед новою фільтра­цією списку записів.

Крім критеріїв точної рівності деякому значенню можна викори­стовувати інші критерії порівняння: <, <=, <>, >, >=. Для текстових критеріїв слід враховувати певні правила. Якщо в комірці записати одну літеру, при фільтрації будуть знайдені всі значення, що почина­ються на цю літеру. За умовою ">Л" після фільтрації залишаться за­писи, що починаються з літер від "М" до "Я", для "<Л" — від "А" до "К". Крім того символи шаблону ("+'', "?") обробляються так само, як в автофільтрі.

Система Excel дає можливість також використовувати обчислю­вальні критерії, що потребують виконання складніших операцій, ніж просте порівняння. Це може бути, наприклад, порівняння із середнім значенням якогось поля всієї таблиці даних (середній вік, середня за­робітна плата працівників тощо). Для виконання фільтрації такого типу треба в деякій комірці за межами списку обчислити потрібне значення (наприклад, у комірці G15 записати =CP3HA4(D2:D11)), потім у діапазоні умов ввести заголовок стовпця, що не повинен збігатися з будь-яким заголовком таблиці даних, а нижче ввести умову обчислювального критерію (=D2>$G$15). При цьому слід дотримуватись правил: посилання на комірки за межами списку по­винні бути абсолютними, а посилання на комірки всередині спис­ку — відносними.

Поиск по сайту: