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

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

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

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

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

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

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

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

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

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

4. У діалоговому вікні Фільтрування рядків виконайте наведені нижче дії.

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

5. Натисніть кнопку OK.

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

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

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

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

3. Виберіть фільтри чисел, а потім виберіть ім'я типу рівності дорівнює, не дорівнює, більше, більше або дорівнює, менше, менше або дорівнює або між.

4. У діалоговому вікні Фільтрування рядків виконайте наведені нижче дії.

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

5. Натисніть кнопку OK.

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

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

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

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

3. Виберіть фільтри дати й часу, а потім виберіть ім'я типу рівності Дорівнює, Перед, Після, Між, У наступному, У попередньому, Найраніше, Ненайраніше, Не найпізніше, Неостаннє та Настроюваний фільтр.

   Порада Попередньо визначені фільтри можна спростити, вибравши елементи Рік, Квартал, Місяць, Тиждень, День, Година, Хвилина та Другий. Ці команди працюють одразу.

4. У діалоговому вікні Фільтрувати рядок:

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

5. Натисніть кнопку OK.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Виберіть основне>Видалення рядків>Видалення альтернативних рядків.

3. У діалоговому вікні Видалення альтернативних рядків введіть такі дані:

   • Перший рядок для видалення Почати підрахунок із цього рядка. Якщо ввести 2, перший рядок буде збережено, але другий рядок буде видалено.
   •   Кількість рядків для видалення Визначте початок візерунка. Якщо ввести 1, один рядок буде видалено за раз.
   •   Кількість рядків, які потрібно зберегти Визначте кінець візерунка. Якщо ви введете 1, продовжуйте шаблон у наступному рядку, тобто третьому рядку.

4. Натисніть кнопку OK.

Результат

Power Query має шаблон для всіх рядків. У цьому прикладі видаляються непарні рядки та зберігаються парні рядки.

Додаткові відомості

Power Query довідки з Excel

Видалення або збереження рядків із помилками

Збереження або видалення повторюваних рядків

Фільтрувати за розташуванням рядка (docs.com)

Фільтрувати за значеннями (docs.com)