Фільтр даних щодо умов списку. Складне фільтрування (розширений фільтр). Як користуватися розширеним фільтром в Excel

ОБРОБКА ДАНИХ В ЕЛЕКТРОННІЙ ТАБЛИЦІ

Подання електронної таблиці у вигляді списку

Список або база даних – це один із способів організації даних на робочому аркуші. Він створюється як позначений ряд, що складається з рядків із однотипними даними. Наприклад, перелік співробітників деякого відділу, в якому стовпці мають такі імена: ПІБ, Усього нараховано, Усього утримано, Сума до видачі.

Дані, організовані до списку, називаються базою даних. У цьому рядки таблиці – записи бази даних, а стовпці – поля записів. Щоб перетворити таблицю Excel на список, необхідно присвоїти стовпцям імена, які будуть використовуватися як імена полів записів. Під час створення бази даних необхідно виконувати такі правила:

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

2. Слід відокремлювати список даних від інших даних робочого листа хоча б одним незаповненим стовпцем або одним незаповненим рядком. Це допоможе автоматично виділити список під час фільтрації або сортування даних.

3. У заголовках стовпців списку даних не слід об'єднувати комірки.

4. Імена стовпців повинні розташовуватись у першому рядку списку, т.к. Excel завжди перший рядок розглядає як заголовки стовпців.

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

Сортування даних у таблиці

Сортування даних у Excel має позитивні та негативні сторони. Позитивні – універсальність операції та зручний інтерфейс користувача. Негативні: легкість, з якою таблиця може бути перетворена на набір безладних даних.

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

У трьох полях введення вікна Сортуванняможна встановити ключі, за якими дані будуть сортуватися. Можна також встановити параметри: встановити порядок сортування за першим ключем (звичайний або визначений користувачем), а також напрямок сортування (за зростанням або за спаданням).

Для швидкого сортування можна скористатися кнопками «Сортування за зростанням» та «Сортування за спаданням» стандартної панелі інструментів (у цьому випадку ключем сортування є стовпець з поточним осередком).

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

Фільтрування даних у списку

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

Автофільтр

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

Кнопки, які розкривають список фільтра, створюються у першому рядку діапазону. Якщо у стовпці зустрічаються незаповнені осередки, список доповнюється категоріями «порожні» та «непорожні». У будь-якому випадку верхній рядок діапазону не розглядається як об'єкт для фільтрації і не включається до списку. Навіть якщо частина рядків не потрапила у виділення, принцип фільтрації поширюватиметься і на них, включаючи підсумковий рядок. Після того, як діапазон виділено вибираємо команду Дані/ Фільтр / Автофільтр.

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

Щоб видалити критерій фільтрації для окремого стовпця, потрібно вибрати параметр «Все» у списку, що розкривається. Щоб відобразити всі приховані у списку рядки, потрібно вибрати Дані/ Фільтр/Відобразити все.

Користувач може сформувати умову відбору рядків таблиці кожного стовпця. Користувальницькі критеріївідбору записів можуть складатися з одного або двох логічних виразів, що з'єднуються операторами І (АБО). Використання оператора І передбачає, що умова буде виконана, якщо одночасно будуть виконані обидві його частини. Використання оператора АБО допускає виконання хоча б однієї частини умови.

Щоб задати користувальницький критерій треба вибрати параметр Умова в списку, що розкривається, а потім, у вікні «Користувачський автофільтр» задати необхідні критерії: Показувати тільки ті рядки, значення яких ... - вказати потрібні критерії.

Для видалення автофільтра необхідно повторно вибрати команду Дані/Фільтр/Автофільтр.

Перші 10.Цей метод має сенс лише для полів із числовими даними, у тому числі й з датами. Щоб скористатися цим методом, потрібно вибрати варіант «Перші 10…». У діалоговому вікні, що з'явилося, слід вказати, скільки найбільших або найменших елементів слід відображати. Наприклад, БД є поле «Оклад» для зберігання розміру окладу співробітника. Організація має можливість надати матеріальну допомогу 5 співробітникам із найменшим окладом. Для вирішення цього завдання можна скористатися методом відбір «Перші 10…» для поля «Оклад»: задати кількість записів, що відображаються з найменшим значенням.

Складна фільтрація (розширений фільтр)

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

Розширений фільтр дозволяє:

1. відразу копіювати відфільтровані записи до іншого місця робочого листа.

2. зберігає критерій відбору для подальшого використання (це корисно, коли до списку вносяться нові дані, і потрібно періодично витягувати з нього інформацію відповідно до критерію).

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

4. об'єднувати оператором АБО умови для різних стовпців

5. для одного стовпця об'єднувати операторами І, АБО понад дві умови.

6. створювати обчислювані критерії.

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

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

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

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

· Сформувати шапку вихідного документа

· Виділити область списку у вихідному документі

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

Завдання умов з використанням логічної операції АБО:

Щоб задати умови в діапазоні критеріїв логічної операцією АБО, потрібно ці умови розташувати на різних рядках: наприклад:

1) Відобразити записи про менеджерів з прізвищем «Кислов» або про менеджерів, які продають «Хліб»:

2) Отримати інформацію про клієнтів, чиї прізвища починаються на букву В і Т:

Завдання умов з використанням логічної операції І:

Щоб задати умови в діапазоні критеріїв логічної операцією І, потрібно ці умови розташувати на одному рядку: наприклад:

1) Знайти інформацію про менеджерів з прізвищем «Петрова», які продали товар більш ніж на 50 одиниць:

2) Знайти інформацію про товари, ціна яких більше 30 і менше 80 рублів.

Вивести на екран інформацію за одним / декількома параметрами можна за допомогою фільтрації даних в Excel.

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

Автофільтр та розширений фільтр в Excel

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

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

Користуватися автофільтром легко: необхідно виділити запис з необхідним значенням. Наприклад, відобразити постачання до магазину №4. Ставимо пташку навпроти відповідної умови фільтрації:

Відразу бачимо результат:

Особливості роботи інструменту:

1. Автофільтр працює лише у нерозривному діапазоні. Різні таблиці одному листі не фільтруються. Навіть якщо вони мають однотипні дані.
2. Інструмент сприймає верхній рядок як заголовки стовпців – ці значення фільтр не включаються.
3. Допустимо застосовувати відразу кілька умов фільтрації. Але кожен попередній результат може приховувати необхідні записи для наступного фільтра.

У розширеного фільтра набагато більше можливостей:

1. Можна встановити стільки умов для фільтрації, скільки потрібно.
2. Критерії вибору даних – на увазі.
3. За допомогою розширеного фільтра користувач легко знаходить унікальні значення у багаторядковому масиві.

Як зробити розширений фільтр в Excel

Готовий приклад - як використовувати розширений фільтр в Excel:

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

Як користуватися розширеним фільтром в Excel

Розглянемо застосування розширеного фільтра в Excel для відбору рядків, що містять слова «Москва» або «Рязань». Умови для фільтрації повинні знаходитись в одному стовпці. У нашому прикладі – один під одним.

Заповнюємо меню розширеного фільтра:

Отримуємо таблицю з відібраними за заданим критерієм рядками:

Виконаємо відбір рядків, які у стовпці «Магазин» містять значення «№1», а стовпці вартість – «>1 000 000 р.». Критерії для фільтрації повинні знаходитись у відповідних стовпцях таблички для умов. На одному рядку.

Заповнюємо параметри фільтрації. Натискаємо ОК.

Залишимо в таблиці лише ті рядки, які у стовпці «Регіон» містять слово «Рязань» або в стовпці «Вартість» - значення «>10 000 000». Оскільки критерії відбору відносяться до різних стовпців, розміщуємо їх на різних рядках під відповідними заголовками.

Застосуємо інструмент «Розширений фільтр»:

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

Основні правила:

1. Результат формули – це критерій відбору.
2. Записана формула повертає результат ІСТИНА або БРЕХНЯ.
3. Вихідний діапазон вказується у вигляді абсолютних посилань, а критерій відбору (як формули) – з допомогою відносних.
4. Якщо повертається значення ІСТИНА, рядок з'явиться після застосування фільтра. Брехня - ні.

Відобразимо рядки, що містять кількість вище середнього. Для цього осторонь таблички з критеріями (в комірку I1) введемо назву «Найбільша кількість». Нижче – формула. Використовуємо функцію СРЗНАЧ.

Виділяємо будь-яку комірку у вихідному діапазоні та викликаємо «Розширений фільтр». Як критерій для відбору вказуємо I1:I2 (посилання відносні!).

У таблиці залишилися лише ті рядки, де значення в стовпці «Кількість» вище за середнє.

Щоб залишити в таблиці лише рядки, що не повторюються, у вікні «Розширеного фільтра» поставте пташку навпроти «Тільки унікальні записи».

Натисніть кнопку ОК. Рядки, що повторюються, будуть приховані. На аркуші залишаться лише унікальні записи.

Фільтрування або вибірка – дуже часта операція під час роботи зі списками. Суть її в тому, щоб відібрати зі списку всі рядки (записи), які відповідають певним умовам. Умов може бути багато, вони можуть бути простими та складними, пов'язаними один з одним чи незалежними. Існує кілька способів фільтрації списків Excel.

Спосіб 1. Автофільтр

Відфільтрувати список автофільтром - значить приховати всі рядки, за винятком тих, яких задовольняють заданим умовам відбору. Для виконання такої операції необхідно виділити будь-яку комірку списку і вибрати в менюДані - Фільтр - Автофільтр(Data - Filter - AutoFilter).

У першому рядку, який містить заголовки стовпців, з'являться кнопки зі стрілками - кнопки автофільтра:

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

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

На жаль, жодна версія Excel, крім Excel 2007 не дозволяє вибрати зі списку, що випадає, більше одного варіанту.

Ми можемо також використовувати автофільтр, щоб знайти задане число (або відсоток) найбільших чи найменших елементів у списку. Для цього необхідно клацнути по кнопці автофільтра у відповідному полі та вибратиПерші 10 (Top 10). Відкриється вікно, в якому користувач може задати кількість елементів і вибрати з списків їх тип - найбільші або найменші:

Спосіб 2. Користувальницький автофільтр

Ми можемо використовувати автофільтр для вибірки записів за складнішими умовами. Допустимо, необхідно вибрати зі списку рядка, де оклад співробітника лежить у діапазоні від 1000 до 2000 або менше/більше певної величини. Для виконання такої фільтрації зі списку поля "Вартість замовлення" необхідно вибрати пунктУмова (Custom). З'явиться вікно:

За допомогою списків, що випадають, необхідно задати умову відбору і натиснути кнопкуОК - Excel відобразить лише рядки, які задовольняють заданим умовам.

Користувальницький автофільтр здатний також працювати з текстом – для цього у списку умов є операториПочинається з... , Закінчується на..., Містить... , Не містить... і т.д.

Спосіб 3. Розширений фільтр

На відміну від Автофільтра та Користувальницький автофільтр - Розширений фільтрпрактично не має обмежень на кількість умов, що накладаються на список. Але (безкоштовних тістечок не буває!) потребує деяких підготовчих операцій. А саме:

Наприклад, щоб відібрати зі списку співробітників у нашому файлі потенційні пари молодят, можна задати умови таким чином:

Excel відбере всіх чоловіків з окладом 1000 і більше, неодружених і жінок не старше 1 січня 1950 народження, незаміжніх, з дітьми.

Для того, щоб відфільтрувати дані за критеріями нашої таблиці, виділіть будь-яку комірку вихідного списку співробітників і виберіть в менюДані - Фільтр - Розширений фільтр(Data - Filter - Advanced Filter). Відкриється вікно Розширений фільтр, в яку необхідно ввести адресу вихідного діапазону, адресу діапазону умов та вказати місце, куди помістити результати фільтрації:

Прапорець Тільки унікальні записислужить для відборунеповторних даних.

Мета роботи:виконання сортування даних, ознайомлення із способом фільтрації записів списку, автофільтрацією, роботою з формою даних.

Завдання 1.

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

Методика виконання роботи

1. Відкрийте нову робочу книгу та збережіть її під назвою «Сортування» у своїй робочій папці .

2. Створіть таблицю, представлену на малюнку 5.56.

Малюнок 5.56 – Початкова таблиця з даними

3. Вкажіть параметри форматування для таблиці.

Шрифт Times New Roman, розмір шрифту 12 пт., для заголовків накреслення напівжирне та вирівнювання по центру, перенесення за словами, заливка сірим кольором; для більшості. Нагадуємо, що команди форматування доступні на стрічці Головна Þ Осередки .

4. Для сортування по полю прізвище покупця поставте курсор у будь-яке місце цього стовпця і виконайте команду Дані Þ Сортування (Рис. 5.51) .

У діалоговому вікні, що відкрилося, в полі Сортувати завиберіть "Прізвище покупця". За зростанням.

5. Повторіть усі кроки пункту 4 і задайте сортування за сумою угоди, за спаданням.

6. Виконайте повторне сортування по полю «Дата угоди» за зростанням.

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

8. За допомогою команди Перейменувати надайте імена цим двом аркушам.

Завдання 2. Здійсніть вибірку інформації зі списку на основі команди Автофільтр.

Методика виконання роботи.

1. На аркуші 4 створіть таблицю та заповніть її відомостями з таблиці 5.5.

2. Перейменуйте Лист4, надавши йому ім'я «Автофільтр №1».

3. Щоб застосувати Автофільтрацію, встановіть курсор у область списку та виконайте команду Дані Фільтр. Поруч із назвами граф таблиці з'являться стрілки, спрямовані вниз, які розкривають список можливих значень. У графі «Підлога» виберіть «М» Скопіюйте таблицю на аркуш 5 і перейменуйте його на «Автофільтр №2».

4. На аркуші «Автофільтр №1» у графі «Підлога» відкрийте список фільтрування та виберіть «Всі». Потім у графі "Дата народження" виберіть у списку фільтрування "Умова" і задайте умову (рис.5.57):

Таблиця 5.5

Рисунок 5.57 – Встановлення умов фільтрації

5. Скопіюйте відфільтровану таблицю на аркуш 6 і перейменуйте його на «Автофільтр №3. На аркуші «Автофільтр №1» скасуйте вибірку.

Рисунок 5.58 – Користувальницький фільтр

6. У стовпці «Прізвище» виберіть у списку фільтрації «Умова» та задайте умову на відбір усіх співробітників, чиє прізвище починається на «Б» (рис.5.58).

7. Скопіюйте відфільтрований список на аркуш 7 перейменуйте його на «Автофільтр №4».

8. На аркуші «Автофільтр №1» для графи «Прізвище» задайте «Всі», а у графі «Оклад» задайте «Перші 10…», де в діалоговому вікні введіть «Показати 5 найбільших елементів списку».

9. Збережіть файл.

Завдання 3.Виконайте відбір записів, використовуючи команду Розширений фільтр.

Методика виконання.

1. Перейдіть на аркуш 8 і перейменуйте його на «Розширений фільтр».

2. Скопіюйте на цей аркуш таблицю з попереднього завдання (табл. 5.5), вставте її починаючи з рядка 7. Перші 6 рядків відводяться для завдання умов.

3. Створимо діапазон умов. Припустимо, нам потрібно відібрати прізвища співробітників, які отримують більше 5000 руб. Або чий вік перевищує 50 років. Заповніть умови, як показано на малюнку 5.59.

Рисунок 5.59 – Умови розширеного фільтра

4. Виконайте команду Дані Þ Додатково . Заповніть діалогове вікно в такий спосіб (рис. 5.60):

Рисунок 5.60 – Вікно параметрів розширеного фільтра

Перегляньте результати відбору. При записі умов в одному рядку реалізується логічне І. Під час запису умов на різних рядках вони вважаються пов'язаними логічним АБО. Ми розглянули перший варіант, тепер розглянемо другий.

5. Припустимо, нам потрібно вивести тільки тих співробітників, прізвища яких починаються з літер А, Г або Н. Заповніть діапазон умов (рисунок 5.61).

Рисунок 5.61 – Умови розширеного фільтра

6. Виконайте команду Дані ÞДодатково та заповніть діалогове вікно (рисунок 5.62).

Рисунок 5.62 – Вікно параметрів розширеного фільтра

Перегляньте результати відбору записів.

1. Виведіть список усіх працівників, заробітна плата яких більша за середній рівень. Перед створенням цього фільтра введіть у комірку H2 формулу =СРЗНАЧ(F8:F20) для обчислення середнього окладу.

2. Потім в комірку А2 вводимо умову, що обчислюється =F8>$H$2, що посилається на комірку H2 (малюнки 5.63 і 5.64).

Рисунок 5.63 – Умови розширеного фільтра

Рисунок 5.64 – Параметри розширеного фільтра

КОРОТКА ДОВІДКА

Загальні відомості

Автофільтрація

• всі -вибираються усі записи без обмежень;
• перші 10- даний пункт дозволяє у новому діалоговому вікні «Накладення умови за списком» (рис.3.37) вибрати певну кількість найбільших або найменших елементів списку, які необхідно відобразити;
• значення -будуть вибрані лише ті записи, які в даному стовпці містять зазначене значення;

• умова -вибираються записи за умовою, що формується користувачем у діалоговому вікні «Користувачський фільтр» (рис.3.3 8).

• оператор відносини: = (рівно),<>(Не дорівнює), > (більше), >= (більше або одно),< (меньше), <= (меньше или равно), начинается с, содержит и т.п.;
• значення, яке може вибиратися зі списку або містити шаблонні символи *,?

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

• критерій порівняння;
• обчислюваний критерій.

• склад стовпців області критеріїв визначається стовпцями, якими задаються умови фільтрації записів;
• імена стовпців області критеріїв повинні точно збігатися з іменами стовпців вихідного списку;
• нижче імен стовпців розташовуються критерії порівняння типу:
• точного значення;
• значення, що формується за допомогою операторів відношення;
• шаблону значення, що включає символи * та (або) ?.

• присвоїти відмінне від імен полів списку нове ім'я стовпцю, куди буде введений критерій, що обчислюється;
• встановити курсор у комірку введення;
• викликати Майстер функцій -команда Вставка, Функція, вибір категорії - Логічніта вибір функції - АБО;
• введення параметрів функції АБО: