Фільтр даних за умовами списку. Способи фільтрації списків
Вивести на екран інформацію по одному / декільком параметрам можна за допомогою фільтрації даних в Excel.
Для цієї мети призначене два інструменти: автофильтр і розширений фільтр. Вони не видаляють, а приховують дані, що не підходять за умовою. Автофільтр виконує найпростіші операції. У розширеного фільтра набагато більше можливостей.
Автофільтр і розширений фільтр в Excel
Є проста таблиця, що не відформатована і не оголошена списком. Включити автоматичний фільтр можна через головне меню.
Якщо відформатувати діапазон даних як таблицю або оголосити списком, то автоматичний фільтр буде додано відразу.
Користуватися Автофільтри просто: потрібно виділити запис з потрібним значенням. Наприклад, відобразити поставки в магазин №4. Ставимо галочку навпроти відповідного умови фільтрації:
Відразу бачимо результат:
Особливості роботи інструменту:
- Автофільтр працює тільки в нерозривній діапазоні. Різні таблиці на одному аркуші не фільтруються. Навіть якщо вони мають однотипні дані.
- Інструмент сприймає верхній рядок як заголовки стовпців - ці значення в фільтр не включаються.
- Припустимо застосовувати відразу кілька умов фільтрації. Але кожен попередній результат може приховувати необхідні для наступного фільтра записи.
У розширеного фільтра набагато більше можливостей:
- Можна задати стільки умов для фільтрації, скільки потрібно.
- Критерії вибору даних - на увазі.
- За допомогою розширеного фільтра користувач легко знаходить унікальні значення в багаторядковому масиві.
Як зробити розширений фільтр в Excel
Готовий приклад - як використовувати розширений фільтр в Excel:
У вихідній таблиці залишилися тільки рядки, що містять значення «Москва». Щоб скасувати фільтрацію, потрібно натиснути кнопку «Очистити» в розділі «Сортування і фільтр».
Як користуватися розширеним фільтром в Excel
Розглянемо застосування розширеного фільтра в Excel з метою відбору рядків, що містять слова «Москва» або «Рязань». Умови для фільтрації повинні знаходитися в одному стовпці. У нашому прикладі - один під одним.
Заповнюємо меню розширеного фільтра:
Отримуємо таблицю з відібраними за заданим критерієм рядками:
Виконаємо відбір рядків, які в стовпці «Магазин» містять значення «№1», а в стовпці вартість - «\u003e 1 000 000 р.». Критерії для фільтрації повинні перебувати у шпальтах таблички для умов. На одному рядку.
Заповнюємо параметри фільтрації. Натискаємо ОК.
Залишимо в таблиці тільки ті рядки, які в стовпці «Регіон» містять слово «Рязань» або в стовпці "Вартість" - значення «\u003e 10 000 000 р.». Так як критерії відбору відносяться до різних стовпчиках, розміщуємо їх на різних рядках під відповідними заголовками.
Застосуємо інструмент «Розширений фільтр»:
Даний інструмент вміє працювати з формулами, що дає можливість користувачеві вирішувати практично будь-які завдання при відборі значень з масивів.
Основні правила:
- Результат формули - це критерій відбору.
- Записана формула повертає результат ІСТИНА або БРЕХНЯ.
- Вихідний діапазон вказується за допомогою абсолютних посилань, а критерій відбору (у вигляді формули) - за допомогою відносних.
- Якщо повертається значення ІСТИНА, то рядок відобразиться після застосування фільтра. БРЕХНЯ - немає.
Відобразимо рядки, що містять кількість вище середнього. Для цього в стороні від таблички з критеріями (в клітинку I1) введемо назву «Найбільша кількість». Нижче - формула. Використовуємо функцію СРЗНАЧ.
Виділяємо будь-яку клітинку в вихідному діапазоні і викликаємо «Розширений фільтр». В якості критерію для відбору вказуємо I1: I2 (посилання відносні!).
У таблиці залишилися тільки ті рядки, де значення в стовпці «Кількість» вище середнього.
Щоб залишити в таблиці лише неповторним рядки, у вікні «Розширеного фільтру» поставте галочку навпроти «Тільки унікальні записи».
Натисніть ОК. Повторювані рядки будуть приховані. На аркуші залишаться тільки унікальні записи.
Фільтр - це швидкий і легкий спосіб пошуку підмножини даних і роботи з ними в списку. У відфільтрованому режимів Лише рядки, що відповідають умовам. На відміну від сортування, фільтр не змінює порядок записів у списку. При фільтрації тимчасово ховаються рядки, які не потрібно відображати.
Рядки, відібрані при фільтрації, можна редагувати, форматувати, створювати на їх основі діаграми, виводити їх на друк, не змінюючи порядок рядків і не переміщаючи їх.
При фільтрації вибираються тільки необхідні дані, а інші дані ховаються. Таким чином, відображається тільки те, що ви хочете побачити, і це можна зробити одним клацанням.
При фільтрації дані ніяк не змінюються. Як тільки фільтр видалений, всі дані з'являються знову в тому ж вигляді, в якому вони були до застосування фільтра.
В Excel доступні дві команди для фільтрації списків:
- Автофільтр, Включаючи фільтр по виділеному, для простих умов відбору.
- Розширений фільтр для більш складних умов відбору.
Автофільтр
для включення автофільтра потрібно виділити будь-яку клітинку в таблиці, потім на вкладці дані в групі Сортування і фільтр натиснути велику кнопку :
Після цього в шапці таблиці праворуч від кожного заголовка стовпця з'явиться кнопка зі стрілкою вниз:
Клацання по стрілці відкриває меню зі списком для відповідного стовпчика. У списку містяться всі елементи стовпця в алфавітному або числовому порядку (в залежності від типу даних), так що можна швидко знайти потрібний об'єкт:
Якщо нам потрібен фільтр тільки для одного стовпчика, то можна не виводити кнопки зі стрілкою для інших стовпців. Для цього перед натисканням кнопки виділяємо кілька осередків потрібного стовпчика разом з заголовком.
Фільтрація по точному значенням
включаємо Автофільтр, Клацаємо по кнопці зі стрілкою і вибираємо з розкрився списку якесь значення. Для того, щоб швидко виділити всі елементи стовпця або зняти виділення з усіх елементів, клацніть по пункту (Виділити все) :
При цьому всі рядки, в поле яких не міститься вибране значення, ховаються.
При виконанні лабораторної роботи, виділяємо результат фільтрації, копіюємо на інше місце листа і підписуємо.
для виключення автофільтра потрібно ще раз натиснути кнопку .
Для скасування дії фільтра, не виходячи з режиму фільтрації, клацаємо по кнопці і вибираємо з розкрився списку пункт (Виділити все) . При цьому з'являються приховані фільтром рядки таблиці.
Ознаки фільтрації даних
Фільтри приховують дані. Саме для цього вони і призначені. Однак, якщо про фільтрації даних не відомо, може виникнути враження, що деякі дані пропущені. Ви могли, наприклад, відкрити чийсь відфільтрований лист або навіть забути, що самі раніше застосували фільтр. Тому коли на аркуші є фільтри, можна виявити різні візуальні покажчики і повідомлення.
(Знаходиться зліва внизу вікна). Вихідний стан:
Відразу після фільтрації даних підсумок застосування фільтра відображаються в лівому нижньому куті рядка стану. Наприклад, " Знайдено записів: 2 з 11”:
номери рядків . За переривчастим номерами рядків можна сказати, що деякі рядки приховані, а змінився колір номерів видимих \u200b\u200bрядків вказує на те, що виділені рядки є результатом відбору фільтра.
вид стрілок . Зміна стрілки автофільтра в відфільтрованому стовпці на вказує на те, що даний стовпець відфільтрований.
”
"" - це ще один універсальний фільтр, який можна застосовувати до стовпців з числами або датами.
"" - це дуже умовна назва. Насправді можливості цього фільтра набагато ширше. За допомогою цього фільтра можна знаходити або перші елементи або останні елементи (найменші або найбільші числа або дати). І, всупереч назві фільтра, одержувані результати не обмежуються першими 10 елементами або останніми 10 елементами. Число відображуваних елементів можна вибирати від 1 до 500.
"Дозволяє також відбирати дані за відсотком від загального числа рядків в стовпці. Якщо в стовпці міститься 100 чисел і потрібно переглянути найбільші п'ятнадцять, то вибираємо 15 відсотків.
Фільтр можна використовувати для знаходження продуктів з найбільшими або найменшими цінами, для визначення списку співробітників, найнятих останніми за часом, або, щоб переглянути студентів з найкращими або найгіршими оцінками. Щоб застосувати фільтр "" до колонку даних ( тільки числа або дати !!!), Клацаємо в стовпці стрілку і вибираємо пункт числові фільтри далі :
Після цього відкриється діалогове вікно накладення умови по списку :
У діалоговому вікні вибираємо число (Рядків або відсотків), найбільших або найменших, елементів списку або % Від кількості елементів.
Створення власних настроюються фільтрів
Наприклад, нам потрібно вивести за виключенням окремих виразів з посадами, починаються на літеру ' Д'. Для цього клацаємо по стрілці автофільтра в першому стовпці і вибираємо текстові фільтри , Потім пункт починається з… :
При цьому з'явиться діалогове вікно (Якою б пункт справа ви б не вибирали, все одно з'явиться одне і те ж діалогове вікно.):
В полі Посада вибираємо - починається з , Праворуч вводимо д:
У вікні є підказка:
Знак питання " ? "Позначає один довільний знак.
знак " * "Позначає послідовність будь-яких знаків.
ОБРОБКА ДАНИХ В ЕЛЕКТРОННОЇ ТАБЛИЦІ
Подання електронної таблиці у вигляді списку
Список або база даних - це один із способів організації даних на робочому аркуші. Він створюється як позначений ряд, що складається з рядків з однотипними даними. Наприклад, перелік співробітників деякого відділу, в якому стовпці мають такі імена: ПІБ, Всього нараховано, Всього утримано, Сума до видачі.
Дані, організовані в список, називаються базою даних. При цьому рядки таблиці - записи бази даних, а стовпці - поля записів. Щоб перетворити таблицю 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 рублів.
