Фільтрування даних у діапазоні або таблиці

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

Фільтрування діапазону даних

1. Виберіть будь-яку комірку в діапазоні даних.
2. Виберіть Дані >Фільтр.

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

При створенні та форматі таблиць їх великі таблиці автоматично додаються елементи управління фільтром.

Виберіть стрілку в

фільтра. Натисніть цю піктограму, щоб змінити або очистити фільтр.

Розширений фільтр в Excel та приклади його можливостей

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

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

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

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

1. Виділяємо мишкою будь-яку комірку всередині діапазону. Переходимо на вкладку «Дані» та натискаємо кнопку «Фільтр».
2. Поруч із заголовками таблиці з'являються стрілочки, що відкривають списки автофільтра.

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

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

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

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

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

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

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

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

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

1. Створимо таблицю з умовами відбору. Для цього копіюємо заголовки вихідного списку та вставляємо вище. У табличці з умовами для фільтрації залишаємо достатню кількість рядків плюс порожній рядок, що відокремлює від вихідної таблиці.
2. Налаштуємо параметри фільтрації для відбору рядків зі значенням "Москва" (у відповідний стовпець таблички з умовами вносимо = "=Москва"). Активізуємо будь-яку комірку у вихідній таблиці. Переходимо на вкладку «Дані» – «Сортування та фільтр» – «Додатково».
3. Заповнюємо параметри фільтрації. Вихідний діапазон - таблиця з вихідними даними. Посилання з'являються автоматично, т.к. була активна одна з осередків. Діапазон умов – табличка із умовою.
4. Виходимо з меню розширеного фільтра, натиснувши кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Натисніть кнопку ОК. Повторювані рядки будуть приховані.

• Створити таблицю
• Форматування
• Функції Excel
• Формули та діапазони
• Фільтр та сортування
• Діаграми та графіки
• Зведені таблиці
• Друк документів
• Бази даних та XML
• Можливості Excel
• Налаштування параметри
• Уроки Excel
• Макроси VBA
• Завантажити приклади