Фільтрування даних (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-значення або пусті значення можна двома способами:

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

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

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

3. Зніміть прапорець (Виділити все), щоб скасувати вибір усіх стовпців.

4. Виберіть Видалити пусте , а потім натисніть кнопку OK.

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

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

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

1. Щоб відкрити запит, знайдіть попередньо завантажену з Редактор Power Query, виділіть клітинку в даних, а потім виберіть Пункт Data > Query > Edit (Редагувати). Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі 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, виділіть клітинку в даних, а потім виберіть Пункт Data > Query > Edit (Редагувати). Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel.

2. Виберіть Основне > Зберегти рядки , > Зберегти діапазон рядків.

3. У діалоговому вікні Зберегти діапазон рядків введіть числа в першому рядку та Кількість рядків. Щоб виконати наведений нижче приклад, введіть шість як перший рядок, а сім – як кількість рядків.

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

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

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

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

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

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

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

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

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

3. У діалоговому вікні Видалення нижніх рядків введіть число в полі Кількість рядків.

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