Іноді під час роботи з великою базою даних виникає необхідність відібрати інформацію за певним критерієм (наприклад, показати лише тих людей, які народилися 1980 року). Для виконання подібних завдань використовується фільтр в Excel.
Відео з фільтрації та сортування даних в Excel
Види фільтрів в Excel
Фільтрування - це вибір необхідних даних зі списку для подальшої роботи з ними.Результатом цієї процедури будуть певні рядки, які відповідають критеріям відбору. Інші записи тимчасово ховаються і не використовуються, доки користувач не відключить фільтр. З відібраними даними можна виконувати стандартні дії: редагування, форматування, друк, побудова графіків, діаграм тощо.
Всього в Екселі є 2 способи фільтрації: автофільтр та розширений фільтр.Запустити їх можна через панель меню, клацнувши "Дані - Фільтр". За допомогою першого варіанта здійснюється швидкий відбір необхідної інформаціїз найпростішими критеріями пошуку. У режимі автофільтра великий рядок таблиці в кожній колонці міститиме кнопку зі стрілочкою, клікнувши на яку можна вказати критерії для відбору. Для кожного стовпця можна встановити налаштування. У даному режиміможна встановити такі параметри:
- Сортування за зростанням або зменшенням.
- "Все" - Excel покаже (відновить) всі рядки.
- "Перші 10" - Excel відобразить перші 10 записів. При виборі цього пункту відкриється нове вікно, в якому можна вказати кількість записів, вибрати, які з них показувати (найбільші або найменші), а також встановити обмеження на кількість записів, що відображаються.
- "Умова" - тут користувач може самостійно створити 2 критерії відбору даних, об'єднуючи їх простими операторамиІ, АБО.
- Будь-який із елементів. Тут можна вибрати будь-яке значення, яке знаходиться у колонці. Наприклад, якщо стовпець складається з найменувань товарів, то у переліку елементів буде вказано абсолютно всі товари. Користувач може вказати будь-який із них.
- "Порожні" і "Непорожні" - Excel відобразить порожні (або непусті) осередки. Ця опціяз'являється лише в тому випадку, якщо у стовпці є незаповнені поля.
Щоб видалити фільтр із однієї колонки, потрібно клікнути у переліку елементів на пункт «Все». Якщо потрібно скасувати його для всієї таблиці, необхідно вибрати в панелі меню пункти "Дані - Фільтр - Відобразити все". Видалення автофільтра здійснюється так само, як і його запуск.
Розширений фільтр
Приклад створення розширеного фільтра — необхідно встановити діапазон вихідних даних, фільтрів і діапазон, куди поміщаються відфільтровані дані
Другий варіант фільтрації надає користувачеві більше можливостейДля відбору необхідних даних. Щоб запустити розширену опцію, необхідно створити копію заголовків таблиці (тобто просто скопіювати шапку). Це буде діапазон умов. Потім треба заповнити цей діапазон критеріями відбору. Але тут важливо дотримуватися правил: якщо потрібно, щоб значення відбиралися за двома параметрами (наприклад, прізвище студента та оцінка), умови записуються в один рядок; якщо ж критерії будуть відбиратися в режимі "АБО" (марка машини або об'єм двигуна), тоді вони записуються в різні рядки.
Припустимо, є таблиця з двома колонками - найменування товару та кількість. Усього товарів 3 — банани, апельсини, мандарини, а кількість — 10, 20 та 15 штук відповідно. Після того, як буде скопійовано шапку, можна створити умову, наприклад, показати товари, кількість яких менша або дорівнює 15. Тобто під скопійованою шапкою в колонці «Кількість» треба написати<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).
Таким чином, принцип роботи фільтрації має бути зрозумілим. Цим способом можна відбирати будь-які елементи з бази даних, наскільки великою вона не була б. Така процедура допоможе спростити роботу користувача з більшими обсягами даних.
Фільтр в Excel
- Це відбір даних за певними ознаками.Фільтрування в Excelза певними умовами проводиться за допомогою функції "Фільтр в Excel Якщо у таблиці в осередках розміщені картинки, то дивіться у статті "Вставити картинку в осередок в Excel", як закріпити в осередку картинки, щоб вони не зміщувалися при фільтрації.
Як фільтрувати в Excel.
Спочатку виділимо стовпець, у якому потрібно відфільтрувати дані таблиці. Потім на закладці «Головна» натискаємо кнопку «Сортування та фільтр», вибираємо функцію «Фільтр».
Або заходимо на вкладку «Дані» та натискаємо кнопку «Фільтр».
У верхньому осередку виділеного стовпця з'явилася кнопка фільтра. Натискаємо на цю кнопку та вибираємо «Числові фільтри», потім – «менше». У вікні, що навпроти осередку «менше» пишемо ту цифру, менше якої нам треба вибрати дані, наприклад, 7. Натискаємо «ОК».
Щоб кнопка фільтра не закривала значення у верхній комірці, можна поставити число в комірку вище і виділити її теж, або просто виділити над стовпцем порожню комірку. Тоді значок фільтра буде в цій додатковій комірці, і не закриватиме потрібних даних.
Як правильно налаштувати таблицю та фільтр за датою, читайте у статті "Сортування за датою в Excel".
Фільтр за кольором комірки в Excel.
Можна відфільтрувати дані за кольором комірки. Осередки можуть бути пофарбовані вручну або умовним форматуванням. Як забарвити осередки умовним форматуванням за кількістю, за словами, за датою, т. д., "Умовне форматування в Excel".
Натискаємо на рожевий колір. Натискаємо "ОК". Вийшло так.
Як виділити тільки видимі осередки в Excel, як вставити лише у видимі рядки Excel, дивіться статтю "Вставити у видимі рядки в Excel" .В Excel можна налаштувати комірку, в якій буде вказано кількість відфільтрованих рядків, увімкнений фільтр чи ні. Це потрібно для того, щоб не заплутатися, коли часто користуємося фільтром, або кілька людей працюють в одній таблиці, і т.д. Докладніше про це читайте у статті "Як налаштувати фільтр в Excel".
Після роботи з відфільтрованими даними (наприклад, роздрукували цей список), можна таблицю повернути в початковий вигляд, тобто з усіма, не відфільтрованими даними. Натискаємо кнопку фільтра вгорі стовпчика та вибираємо «Виділити все». У нас з'явилася таблиця у первісному вигляді.
Як копіювати дані фільтра, дивиться у статтіКопіювання в Excel відфільтрованих даних" .
Можна, можливо видалити кнопку фільтра з таблиці Excel. Натискаємо на вкладці "Головна" кнопку "Сортування та фільтр", потім "Фільтр".
Або заходимо на вкладку «Дані» та натискаємо кнопку «Фільтр». Кнопка зникла.
Якщо таблицю з відфільтрованими даними потрібно роздрукувати, щоб порядковий номер рядків йшов поспіль (1, 2, 3 ...) або порахувати кількість відфільтрованих рядків, осередків, то це можна налаштувати. Дивіться "Порядковий номер рядків по порядку після фільтру Excel".
Як зробити сортування даних таблиці, читайте у статті
Напевно, всі користувачі, які постійно працюють із програмою Microsoft Excel, знають про таку корисну функцію цієї програми, як . Але не кожен знає, що існують також розширені можливості даного інструменту. Давайте розглянемо, що вміє робити розширений фільтр Microsoft Excel і як ним користуватися.
Для того, щоб встановити розширений фільтр, перш за все потрібно створити додаткову таблицю з умовами відбору. Шапка цієї таблиці точно така, як і в основної таблиці, яку ми, власне, і фільтруватимемо.
Наприклад, ми розмістили додаткову таблицю над основною, і пофарбували її комірки у помаранчевий колір. Хоча розміщувати цю таблицю можна в будь-якому вільному місці, і навіть на іншому аркуші.
Тепер вписуємо в додаткову таблицю дані, які потрібно буде відфільтрувати з основної таблиці. У нашому конкретному випадку зі списку виданої співробітникам заробітної плати ми вирішили обрати дані щодо основного персоналу чоловічої статі за 25.07.2016.
Запуск розширеного фільтра
Тільки після того, як створено додаткову таблицю, можна переходити до запуску розширеного фільтра. Для цього переходимо у вкладку «Дані» та на стрічці в блоці інструментів «Сортування та фільтр» тиснемо на кнопку «Додатково».
Відкриється вікно розширеного фільтра.
Як бачимо, існує два режими використання цього інструменту: «Фільтрувати список на місці» та «Скопіювати результати в інше місце». У першому випадку, фільтрація буде проводитися прямо у вихідній таблиці, а в другому випадку окремо в діапазоні осередків, які ви вкажете самі.
У полі Вихідний діапазон потрібно вказати діапазон осередків вихідної таблиці. Це можна зробити вручну, вбивши координати з клавіатури, або виділивши необхідний діапазон комірок за допомогою мишки. У полі «Діапазон умов» потрібно аналогічним чином вказати діапазон шапки додаткової таблиці та того рядка, який містить умови. При цьому потрібно звернути увагу, щоб у цей діапазон не потрапили порожні рядки, інакше нічого не вийде. Після того, як всі налаштування виконані, натискаємо на кнопку «OK».
Як бачимо, у вихідній таблиці залишилися ті значення, які ми вирішили відфільтрувати.
Якщо вибрано варіант з виведенням результату в інше місце, то в полі «Помістити результат у діапазон» потрібно вказати діапазон комірок, в які будуть виводитися відфільтровані дані. Можна вказати і один осередок. У цьому випадку, вона стане верхнім лівим осередком нової таблиці. Після того, як вибір зроблено, тиснемо на кнопку "OK".
Як можна спостерігати, після цієї дії вихідна таблиця залишилася без змін, а відфільтровані дані виводяться в окрему таблицю.
Для того, щоб скинути фільтр при використанні побудови списку на місці, потрібно на стрічці в блоці інструментів «Сортування та фільтр», натиснувши кнопку «Очистити».
Таким чином, можна дійти невтішного висновку, що розширений фільтр надає більше можливостей, ніж звичайна фільтрація даних. При цьому, не можна не відзначити, що робота з цим інструментом менш зручна, ніж зі стандартним фільтром.
Для зручності роботи з великим масивом даних у таблицях, їх необхідно впорядковувати за певним критерієм. Крім того, для виконання конкретних цілей іноді весь масив даних не потрібен, а лише окремі рядки. Тому, щоб не плутатися у величезній кількості інформації, раціональним рішенням буде впорядкувати дані та відфільтрувати від інших результатів. Давайте з'ясуємо, як проводиться сортування та фільтрація даних у програмі Microsoft Excel.
Сортування є одним із найзручніших інструментів під час роботи в програмі Microsoft Excel. За допомогою неї можна розмістити рядки таблиці в алфавітному порядку, згідно з даними, які знаходяться в осередках стовпців.
Сортування даних у програмі Microsoft Excel можна виконувати, скориставшись кнопкою «Сортування та фільтр», яка розміщена у вкладці «Головна» на стрічці в блоці інструментів «Редагування». Але, перш, нам потрібно клікнути по будь-якому осередку того стовпця, яким ми збираємося виконати сортування.
Наприклад, у запропонованій нижче таблиці слід відсортувати співробітників за абеткою. Стаємо в будь-яку комірку стовпця «Ім'я», і тиснемо на кнопку «Сортування та фільтр». Щоб імена впорядкувати за алфавітом, зі списку вибираємо пункт «Сортування від А до Я».
Як бачимо, всі дані в таблиці розмістилися згідно з алфавітним списком прізвищ.
Для того щоб виконати сортування у зворотному порядку, у тому ж меню вибираємо кнопку Сортування від Я до А».
Список перебудовується у зворотному порядку.
Слід зазначити, що такий вид сортування вказується лише за текстовому форматі даних. Наприклад, при числовому форматі вказується сортування «Від мінімального до максимального» (і навпаки), а при форматі дати – «Від старих до нових» (і, навпаки).
Сортування, що настроюється.
Але, як бачимо, при зазначених видах сортування за одним значенням, дані, що містять імена однієї й тієї ж людини, вишиковуються всередині діапазону в довільному порядку.
А що робити, якщо ми хочемо відсортувати імена за алфавітом, але наприклад, при збігу імені зробити так, щоб дані розташовувалися за датою? Для цього, а також для використання деяких інших можливостей, все в тому ж меню «Сортування та фільтр», нам потрібно перейти в пункт «Сортування, що настроюється…».
Після цього відкривається вікно налаштувань сортування. Якщо у вашій таблиці є заголовки, зверніть увагу, щоб у цьому вікні обов'язково стояла галочка біля параметра «Мої дані містять заголовки».
У полі «Стовпець» вказуємо найменування стовпця, яким буде виконуватися сортування. У нашому випадку це стовпець «Ім'я». У полі «Сортування» вказується, за яким саме типом контенту проводитиметься сортування. Існує чотири варіанти:
- значення;
- Колір комірки;
- Колір шрифту;
- Комірка значок.
Але в переважній більшості випадків використовується пункт «Значення». Він і виставлений за умовчанням. У нашому випадку ми також будемо використовувати саме цей пункт.
У графі «Порядок» нам потрібно зазначити, в якому порядку будуть розташовуватися дані: «Від А до Я» чи навпаки. Вибираємо значення "Від А до Я".
Отже, ми налаштували сортування по одному зі стовпців. Для того, щоб налаштувати сортування по іншому стовпцю, натискаємо кнопку «Додати рівень».
З'являється ще один набір полів, який слід заповнити для сортування по іншому стовпцю. У нашому випадку, стовпцем «Дата». Оскільки у даних осередків встановлено формат дати, то полі «Порядок» ми встановлюємо значення не «Від А до Я», а «Від старих до нових», або «Від нових до старих».
Таким же чином, у цьому вікні можна налаштувати, при необхідності, і сортування по інших стовпцях як пріоритет. Коли всі налаштування виконані, натискаємо кнопку «OK».
Як бачимо, тепер у нашій таблиці всі дані відсортовані насамперед за іменами співробітника, а потім, за датами виплат.
Але, це ще не всі можливості сортування, що налаштовується. За бажанням, у цьому вікні можна налаштувати сортування не за стовпцями, а за рядками. Для цього клацаємо по кнопці «Параметри».
У вікні параметрів сортування, переводимо перемикач з позиції «Рядки діапазону» в позицію «Стовпці діапазону». Тиснемо на кнопку «OK».
Тепер, за аналогією з попереднім прикладом, можна вписувати дані для сортування. Вводимо дані і тиснемо на кнопку «OK».
Як бачимо, після цього стовпці помінялися місцями, згідно з введеними параметрами.
Звичайно, для нашої таблиці, взятої для прикладу, застосування сортування зі зміною розташування стовпців не несе особливої користі, але для деяких інших таблиць подібний вид сортування може бути доречним.
Фільтр
Крім того, у Microsoft Excel існує функція фільтра даних. Вона дозволяє залишити видимими лише ті дані, які ви вважаєте за потрібне, а решту приховати. При необхідності приховані дані завжди можна буде повернути у видимий режим.
Щоб скористатися цією функцією, стаємо на будь-яку комірку в таблиці (а бажано в шапці), знову тиснемо на кнопку «Сортування та фільтр» у блоці інструментів «Редагування». Але, цього разу в меню вибираємо пункт «Фільтр». Можна також замість цих дій просто натиснути клавіші Ctrl+Shift+L.
Як бачимо, в осередках з найменуванням всіх стовпців з'явився значок у вигляді квадрата, в який вписано трикутник.
Клацаємо за цим значком у тому стовпці, за даними якого ми збираємося проводити фільтрацію. У нашому випадку ми вирішили провести фільтрацію на ім'я. Наприклад, нам потрібно залишити дані лише працівника Миколаєва. Тому, знімаємо галочки з імен решти працівників.
Коли процедуру виконано, тиснемо на кнопку «OK».
Як бачимо, у таблиці залишилися лише рядки з ім'ям працівника Миколаєва.
Ускладнимо завдання, і залишимо в таблиці лише дані, які відносяться до Миколаєва за III квартал 2016 року. Для цього клацаємо по значку в осередку «Дата». У списку, що відкрився, знімаємо галочки з місяців «Май», «Червень» і «Жовтень», оскільки вони не відносяться до третього кварталу, і тиснемо на кнопку «OK».
Як бачимо, залишилися лише потрібні нам дані.
Для того, щоб видалити фільтр по конкретному стовпцю, і показати приховані дані, знову клацаємо по значку, розташованому в комірці із назвою даного стовпця. У меню, що розкрилося, клацаємо по пункту «Видалити фільтр з…».
Якщо ж ви хочете скинути фільтр загалом по таблиці, тоді потрібно натиснути кнопку «Сортування та фільтр» на стрічці та вибрати пункт «Очистити».
Якщо потрібно повністю видалити фільтр, то, як і під час його запуску, в цьому ж меню слід вибрати пункт «Фільтр», або набрати клавіші на клавіатурі Ctrl+Shift+L.
Крім того, слід зауважити, що після того, як ми ввімкнули функцію «Фільтр», то при натисканні на відповідний значок в осередках шапки таблиці, у меню, що з'являються меню стають доступні функції сортування, про які ми говорили вище: «Сортування від А до Я» , «Сортування від Я до А», та «Сортування за кольором».
Розумна таблиця
Сортування та фільтр можна також активувати, перетворивши область даних, з якою ви працюєте, на так звану «розумну таблицю».
Існує два способи створення "розумної таблиці". Для того, щоб скористатися першим, виділяємо всю область таблиці, і, перебуваючи у вкладці «Головна», клацаємо по кнопці на стрічці «Форматувати як таблицю». Ця кнопка знаходиться в блоці інструментів "Стилі".
Після цього відкривається діалогове вікно, в якому можна змінити координати таблиці. Але якщо ви раніше виділили область правильно, то більше нічого робити не потрібно. Головне, зверніть увагу, щоб біля параметра "Таблиця із заголовками" стояла галочка. Далі просто натиснути на кнопку «OK».
Якщо ви вирішили скористатися другим способом, тоді також потрібно виділити всю область таблиці, але цього разу перейти у вкладку «Вставка». Перебуваючи на стрічці в блоці інструментів «Таблиці», слід натиснути на кнопку «Таблиця».
Після цього, як і минулого разу, відкриється вікно, де можна скоригувати координати розміщення таблиці. Тиснемо на кнопку «OK».
Незалежно від того, яким способом ви скористаєтеся при створенні "розумної таблиці", у результаті отримаєте таблицю, в комірках шапки якої вже буде встановлені значки фільтрів, описані нами раніше.
При натисканні на цей значок, будуть доступні ті самі функції, що і при запуску фільтра стандартним способом через кнопку «Сортування та фільтр».
Як бачимо, інструменти сортування та фільтрації, при правильному їх використанні можуть значно полегшити користувачам роботу з таблицями. Особливо актуальним питання їх використання стає у тому випадку, якщо у таблиці записано дуже великий масив даних.
Якщо дочитаєте до кінця, навчитеся використовувати таку корисну функцію Ексель, як фільтр. Зараз на цілком реальному прикладі я покажу, що таке фільтри Excel і як економити час при роботі з великими таблицями. Це дуже просто. Наприкінці статті ви зможете завантажити таблицю, на прикладі якої тут розглядаю роботу з фільтрами Excel.
Навіщо потрібні фільтри у таблицях Ексель
А потім, щоб мати можливість швидковідбирати лише необхідні вам дані, приховуючинепотрібні рядкитаблиці. Таким чином фільтр дозволяє без видаленнярядків таблиці Ексель просто тимчасово приховати їх.
Приховані за допомогою фільтра рядки таблиці не зникають. Можна умовно уявити, що їхня висота стає рівною нулю (про зміну висоти рядків і ширини стовпців я раніше розповідав). Таким чином, інші рядки, не приховані фільтром, як би "склеюються". Те, що виходить в результаті, є таблиця з накладеним фільтром.
