Фільтрування даних (Power Query)

Застосовується до

Excel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

У Power Query можна додавати або виключати рядки на основі значень стовпця. Відфільтрований стовпець містить у заголовку стовпця маленьку піктограму фільтра ( піктограма застосованого фільтра ). Щоб видалити фільтр стовпців, клацніть стрілку поруч зі стовпцем, а потім виберіть пункт "Очистити фільтр".

Фільтрування за допомогою автофільтра

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

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть стрілку вниз , який потрібно відфільтрувати.
Зніміть прапорець (Виділити все), щоб скасувати виділення всіх стовпців.
Установіть прапорець поруч зі значеннями стовпців, за якими потрібно відфільтрувати дані, а потім натисніть кнопку OK.

Виділення стовпця

Фільтрування за допомогою текстових фільтрів

Ви можете виконати фільтрування за певним текстовим значенням за допомогою підменю « Текстові фільтри ».

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть вниз поруч зі стовпцем із текстовим значенням, за яким потрібно виконати фільтрування.
Виберіть текстові фільтри, а потім виберіть тип рівності Назва "дорівнює", "Д oes не дорівнює", "Не дорівнює", "Не починається", "Не починається з", "Закінчується", "Не закінчується", "Містить" і "Не містить".
У діалоговому вікні "Фільтр рядків" зробіть ось що.
- Використовується в основному режимі для введення або оновлення двох операторів і значень.
- Використовуйте розширений режим , щоб ввести або оновити більше двох речень, порівнянь, стовпців, операторів і значень.
Натисніть кнопку OK.

Фільтрування за допомогою числових фільтрів

Фільтри чисел можна виконати в підменю " Фільтри чисел ".

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть стрілку вниз поруч зі із числовим значенням, за яким потрібно виконати фільтрування.
Виберіть фільтри чисел, а потім виберіть тип рівності Назва типу "дорівнює", "Не дорівнює", "Більше ніж", "Більше ніж", "Менше", "Менше або дорівнює" або "Між".
У діалоговому вікні "Фільтр рядків" зробіть ось що.
- Використовується в основному режимі для введення або оновлення двох операторів і значень.
- Використовуйте розширений режим , щоб ввести або оновити більше двох речень, порівнянь, стовпців, операторів і значень.
Натисніть кнопку OK.

Фільтрування за допомогою фільтрів дати й часу

Фільтрувати за значенням дати й часу можна в підменю «Фільтри дати й часу ».

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть вниз "Фільтр" у стовпці, що містить значення дати й часу, за якими потрібно виконати фільтрування.
Виберіть фільтри дати й часу, а потім виберіть тип рівності Назва типу рівності: "до", "після", "між", "між", "далі", "у попередньому", "найраніший", "найпізніший", "не найраніший", "ненайпізніший" і "настроюваний фільтр".

Порада Щоб спростити використання попередньо визначених фільтрів, виберіть " Рік", " Квартал", " Місяць", " Тиждень", " День", " Години", " Хвилини" та "Секунди". Ці команди працюють одразу.
У діалоговому вікні "Фільтрувати рядок" виконайте наведені нижче дії.
- Використовується в основному режимі для введення або оновлення двох операторів і значень.
- Використовуйте розширений режим , щоб ввести або оновити більше двох речень, порівнянь, стовпців, операторів і значень.
Натисніть кнопку OK.

Фільтрування кількох стовпців

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

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

Результати фільтрування

Фільтрування за Null- або пустими значеннями

Null-значення або пусте значення виникає, якщо клітинка нічого не містить. Видалити Null- або пусті значення можна двома способами:

Використання автофільтра

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Клацніть стрілку вниз , який потрібно відфільтрувати.
Зніміть прапорець (Виділити все), щоб скасувати виділення всіх стовпців.
Виберіть "Видалити пустий", а потім натисніть кнопку "OK".

Цей метод аналізує кожне значення в стовпці за допомогою такої формули (для стовпця "Ім'я"):

Table.SelectRows(#"Changed Type", each ([Name] <> null and [Name] <> ""))

Використання команди "Видалити пусті рядки"

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Видалити>рядки", "Видалити пусті> рядки".

Щоб очистити цей фільтр, видаліть відповідний крок у розділі "Застосовані кроки " в параметрах запиту.

Цей метод аналізує весь рядок як запис за допомогою такої формули:

Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

Фільтрування за розташуванням рядка

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

Примітка.

Якщо вказати діапазон або шаблон, першим рядком даних у таблиці буде рядок нуль (0), а не перший (1). Ви можете створити стовпець індексу, щоб відображати позиції рядків перед указанням рядків. Докладні відомості див. в статті " Додавання стовпця індексу".

Щоб зберегти верхні рядки, виконайте такі дії:

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Зберегти>рядки", "Зберегти верхні> рядки".
У діалоговому вікні " Зберегти рядки зверху " введіть число в полі "Кількість рядків".
Натисніть кнопку OK.

Збереження нижніх рядків

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Зберегти>рядки", "Зберегти нижні> рядки".
У діалоговому вікні « Зберегти нижні рядки » введіть число в поле «Кількість рядків».
Натисніть кнопку OK.

Збереження діапазону рядків

Іноді таблицю даних отримують зі звіту з фіксованою структурою. Наприклад, перші п'ять рядків – це заголовок звіту, за ними йдуть сім рядків даних, а далі йде різна кількість рядків із примітками. Але вам потрібно зберегти лише рядки даних.

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Зберегти>рядки",> "Зберегти діапазон рядків".
У діалоговому вікні " Зберегти діапазон рядків " введіть числа в поля "Перший рядок " і " Кількість рядків". Щоб виконати процедуру, описану в прикладі, введіть шість як перший рядок і сім як кількість рядків.
Натисніть кнопку OK.

Видалення рядків зверху

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Видалити>рядки", "Видалити верхні> рядки".
У діалоговому вікні " Видалення рядків зверху " введіть число в поле "Кількість рядків".
Натисніть кнопку OK.

Видалення нижніх рядків

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Видалити>рядки", "Видалити нижні> рядки".
У діалоговому вікні " Видалення нижніх рядків" введіть число в поле "Кількість рядків".
Натисніть кнопку OK.

Фільтрування видаленням почергових рядків

Можна фільтрувати за чергуванням рядків і навіть визначити шаблон альтернативного рядка. Наприклад, після кожного рядка даних у таблиці міститься рядок коментаря. Потрібно зберегти непарні рядки (1, 3, 5 і т. д.), але видалити парні рядки (2, 4, 6 і т. д.).

Щоб відкрити запит, знайдіть запит, попередньо завантажений із Редактор Power Query, виберіть клітинку в даних, а потім натисніть кнопку "Редагувати запит".> Докладні відомості див. в статті "Створення, завантаження та редагування запиту в програмі Excel".
Виберіть "Основне", "Видалити>рядки",> "Видалити почергові рядки".
У діалоговому вікні " Видалення почергових рядків" введіть такі дані:
- Перший рядок, який потрібно видалити Почніть відлік з цього рядка. Якщо ввести 2, перший рядок збережеться, а другий рядок буде видалено.
- Кількість рядків, які потрібно видалити Визначте початок візерунка. Якщо ввести 1, рядок буде видалено за один раз.
- Кількість рядків, які потрібно зберегти Визначте кінець візерунка. Якщо ви введете 1, продовжуйте візерунок з наступного ряду, який є третім рядом.
Натисніть кнопку OK.