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

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

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

Варіанти роздільників із виділеною кнопкою множинного вибору

Порада : Тепер в Excel 2016 можна вибрати кілька роздільників, натиснувши кнопку в підписі, як показано вище.

  1. Клацніть будь-яке місце зведеної таблиці, щоб відобразити на стрічці контекстну вкладку Знаряддя для зведених таблиць.

    Стрічка "Знаряддя для зведених таблиць" із вкладками "Аналізувати" й "Конструктор"

  2. В Excel 2016 або 2013 виберіть Аналізувати > Вставити роздільник.

    Кнопка "Вставити роздільник" на вкладці "Аналізувати"

    В Excel 2010 або 2007 виберіть Параметри > Вставити роздільник > Вставити роздільник.

    Зображення стрічки Excel
  3. У діалоговому вікні Вставлення роздільника встановіть прапорець поруч із полями, для яких потрібно створити роздільники.

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

    Роздільник відображається для кожного поля, для якого встановлено прапорець у діалоговому вікні Вставлення роздільника.

  5. У кожному роздільнику виберіть елементи, які потрібно відобразити у зведеній таблиці.

    Щоб вибрати кілька елементів, виберіть потрібні елементи, утримуючи натиснутою клавішу Ctrl.

Порада :  Щоб змінити вигляд роздільника, клацніть роздільник для відображення на стрічці контекстної вкладки Знаряддя для роздільника, а потім застосуйте стиль роздільника або змініть параметри на вкладці Параметри.

Інші способи фільтрування даних зведеної таблиці

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

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

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

Відображення лише певного тексту, значень або дат

Відображення десяти перших або останніх елементів

Відображення або приховання вибраних елементів за допомогою фільтрування за вибраним

Увімкнення або вимкнення параметрів фільтрування

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

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

    Фільтр "Позначки рядків"

  2. У списку підписів рядків або стовпців зніміть прапорець (Виділити все) у верхній частині списку, а потім установіть прапорці для елементів, які потрібно відобразити у зведеній таблиці.

    Поле "Виділити все" в колекції фільтрів

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

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

    Стрілка фільтра зміниться на піктограму піктограма застосованого фільтра , щоб позначити, що фільтр застосовано. Клацніть її, щоб змінити або видалити фільтр, вибравши пункт Видалити фільтр із <ім’я поля>.

    Щоб вилучити всі фільтри одночасно, зробіть ось що:

    • В Excel 2016 або 2013 клацніть будь-де у зведеній таблиці, а потім виберіть Аналізувати > Очистити > Очистити фільтри.

    • В Excel 2010 або 2007 клацніть будь-де у зведеній таблиці, а потім виберіть Параметри > Очистити > Очистити фільтри.

    Кнопка "Очистити" на вкладці "Аналізувати"

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

Примітка : В Excel 2016 і Excel 2013 область "Фільтр звіту" називається Фільтри. Її розташовано в області "Поля зведеної таблиці", як показано на знімку екрана нижче.

Область "Фільтри" в області "Поля зведеної таблиці"
Область "Поля зведеної таблиці" в Excel 2016
"Фільтр звітів" в області "Список полів зведеної таблиці"
Область "Список полів зведеної таблиці" в Excel 2010

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

Додавання фільтра звіту

  1. Клацніть будь-де у зведеній таблиці.

    З’явиться область Список полів зведеної таблиці.

  2. В області Список полів зведеної таблиці клацніть поле в області "Фільтр звіту" та виберіть Перейти до фільтру звіту.

    "Перейти до фільтра звіту"

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

  1. Щоб змінити порядок полів, в області Фільтр звіту перетягніть їх у потрібне розташування. Порядок також змінюється у зведеній таблиці.

Відображення фільтрів звіту в рядках або стовпцях

  1. Клацніть окрему зведену таблицю або таблицю, зв’язану зі зведеною діаграмою.

  2. Клацніть правою кнопкою миші будь-де у зведеній таблиці, а потім виберіть Параметри зведеної таблиці.

  3. На вкладці Макет і форматування задайте такі параметри:

  4. У розділі Макет у полі зі списком Відображати поля в області фільтра звіту виконайте одну з таких дій:

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

    • Щоб відобразити фільтри звіту в рядках згори вниз, виберіть вниз, потім вправо.

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

Вибір елементів у фільтрі звіту

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

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

    Вибрати кілька елементів
  3. Тепер ви можете встановити прапорці поруч з елементами, які потрібно відобразити у звіті. Щоб вибрати всі елементи, установіть прапорець (Усі).

  4. Натисніть кнопку OK. Щоб активувати цю кнопку, потрібно встановити принаймні один прапорець.

    Після цього у фільтрі звіту відображатимуться відфільтровані елементи.

    Елементи, відфільтровані за значенням фільтра, указаним в області "Фільтр звітів"

Примітка : Коли ви використовуєте джерело даних OLAP Microsoft SQL Server Analysis Services (2005 або новішої версії), обчислюваний член можна вибрати, лише якщо це окремий елемент. Якщо один або кілька таких елементів використовуються як обчислювані члени, кілька елементів вибрати не можна.

Відображення сторінок фільтра звіту на окремих аркушах

  1. Клацніть будь-де в окремій зведеній таблиці або у зв’язаній зі зведеною діаграмою з одним або кількома фільтрами звіту.

  2. В Excel 2016 або 2013 на вкладці Аналізувати в групі Зведена таблиця клацніть стрілку поруч із пунктом Параметри й виберіть Відобразити сторінки фільтра звіту.

    Параметр "Відображення сторінок фільтра звіту"

    В Excel 2010 або 2007 на вкладці Параметри в групі Зведена таблиця клацніть стрілку поруч із пунктом Параметри й виберіть Відобразити сторінки фільтра звіту.

    Група "Зведена таблиця" на вкладці "Параметри" контекстної вкладки "Знаряддя для зведених таблиць"

  3. У діалоговому вікні Відображення сторінок фільтра звіту виберіть поле фільтра звіту й натисніть кнопку OK.

Відображення лише певного тексту, значень або дат

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

  2. Виберіть потрібну команду оператора порівняння.

    Наприклад, щоб відфільтрувати за текстом, який починається з певного символу, виберіть параметр Починається з, а щоб відфільтрувати за текстом, що містить певні символи в будь-якому місці, виберіть параметр Містить.

    Примітка : Фільтри підписів недоступні, якщо поля підпису рядка або стовпця не містять текстових підписів.

  3. Виконайте одну з наведених нижче дій.

    • У діалоговому вікні Фільтр підписів <ім’я поля> введіть текст, за яким слід фільтрувати.

      Наприклад, щоб відфільтрувати за текстом, який починається з букви «Ю», введіть Ю, а щоб відфільтрувати за текстом, який містить слово «дзвін» у будь-якому місці, введіть дзвін.

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

Використовуйте

Щоб знайти

? (знак питання)

Будь-який окремий символ.
Наприклад, умові «ма?ка» відповідають результати «мавка» та «марка».

* (зірочка)

Будь-який набір символів.
Наприклад, умові «пів*» відповідають результати «південь» і «північ».

~ (тильда) зі знаком ?, * або ~ в кінці

Знак питання, зірочка або тильда.
Наприклад, умові «ФР06~?» відповідає результат «ФР06?».

  • У діалоговому вікні Фільтр значень <ім’я поля> введіть значення, за якими слід фільтрувати.

  • У діалоговому вікні Фільтр дат <ім’я поля> введіть дати, за якими слід фільтрувати.

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

Порада : Щоб видалити фільтр підпису, дати або значення, клацніть стрілку піктограма застосованого фільтра в підписі рядка або стовпця, виберіть пункт Фільтр підписів, Фільтр дати або Фільтр значення, а потім виберіть пункт Очистити фільтр.

Відображення десяти перших або останніх елементів

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

  1. У зведеній таблиці клацніть стрілку Стрілка розкривного меню фільтра для елемента Позначки рядків або Позначки стовпців, виберіть Фільтри значень, а потім – Перші 10.

  2. У діалоговому вікні Фільтр перших 10 елементів <ім’я поля> зробіть ось що.

    1. У першому полі виберіть пункт найбільших або найменших.

    2. У другому полі введіть число.

    3. У третьому полі виберіть параметр, за яким потрібно фільтрувати.

      • Щоб фільтрувати за кількістю елементів, виберіть пункт елементів списку.

      • Щоб фільтрувати за відсотками, виберіть пункт % від кількості елементів.

      • Щоб фільтрувати за сумою, виберіть пункт Сума.

    4. У четвертому полі виберіть потрібне поле.

Відображення або приховання вибраних елементів за допомогою фільтрування за вибраним

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

    Щоб вибрати кілька окремих елементів, утримуючи натиснутою клавішу Ctrl, клацніть потрібні.

  2. Клацніть правою кнопкою миші вибрані елементи, а потім виберіть Фільтр.

  3. Виконайте одну з таких дій:

    • Щоб відобразити вибрані елементи, клацніть Зберігати лише вибрані елементи.

    • Щоб приховати вибрані елементи, клацніть Приховати вибрані елементи.

      Порада :  Щоб знову відобразити приховані елементи, вилучіть фільтр. Клацніть правою кнопкою миші інший елемент у тому самому полі, виберіть Фільтр, а потім клацніть Очистити фільтр.

Увімкнення або вимкнення параметрів фільтрування

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

  1. Клацніть будь-яке місце зведеної таблиці, щоб відобразити на стрічці контекстну вкладку Знаряддя для зведених таблиць.

    Стрічка "Знаряддя для зведених таблиць" із вкладками "Аналізувати" й "Конструктор"

  2. На вкладці Аналізувати у групі Зведена таблиця натисніть кнопку Параметри.

    Кнопка "Параметри" на вкладці "Аналізувати"

  3. У діалоговому вікні Параметри зведеної таблиці відкрийте вкладку Підсумки та фільтри.

  4. У розділі Фільтри виконайте будь-яку з наведених нижче дій.

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

    • Щоб показувати або не показувати відфільтровані елементи в підсумках, установіть або зніміть прапорець Включати фільтровані елементи в підсумки, а потім установіть або зніміть прапорець Позначати підсумки (*). Якщо цей прапорець установлено, у підсумках за межами полів, для яких вимкнуто візуалізацію підсумків, відображатиметься зірочка (*). Цей параметр доступний лише у зведених таблицях, підключених до джерела даних OLAP, що підтримує мову багатовимірних виразів. Він впливає на всі поля зведеної таблиці.

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

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

  5. Щоб відобразити або приховати назви полів або розкривні меню фільтрів, виберіть вкладку Відображення, а потім установіть або зніміть прапорець Відображати назви полів і розкривні меню фільтрів.

  1. Клацніть будь-де у зведеній таблиці, щоб відобразити на стрічці вкладки для зведених таблиць PivotTable Analyze (Аналіз зведеної таблиці) і Design (Конструктор).

  2. Виберіть PivotTable Analyze (Аналіз зведеної таблиці) > Insert Slicer (Вставити роздільник).

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

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

    Роздільник відображається для кожного поля, для якого встановлено прапорець у діалоговому вікні Вставлення роздільника.

  5. У кожному роздільнику виберіть елементи, які потрібно відобразити у зведеній таблиці.

Порада :  Якщо потрібно змінити вигляд роздільника, клацніть його, щоб відобразити на стрічці вкладку Slicer (Роздільник), а потім застосуйте потрібний стиль або змініть параметри на вкладці.

Інші способи фільтрування даних зведеної таблиці

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

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

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

Відображення десяти перших або останніх елементів

Відображення або приховання вибраних елементів за допомогою фільтрування за вибраним

Увімкнення або вимкнення параметрів фільтрування

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

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

  2. У списку підписів рядків або стовпців зніміть прапорець (Виділити все) у верхній частині списку, а потім установіть прапорці для елементів, які потрібно відобразити у зведеній таблиці.

  3. Стрілка фільтра зміниться на піктограму піктограма застосованого фільтра , щоб позначити, що фільтр застосовано. Клацніть її, щоб змінити або видалити фільтр, вибравши пункт Видалити фільтр із <ім’я поля>.

    Щоб видалити всі фільтри одночасно, на вкладці PivotTable Analyze (Аналіз зведеної таблиці) виберіть Clear (Очистити) > Clear Filters (Очистити фільтри).

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

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

Додавання фільтра звіту

  1. Клацніть будь-де у зведеній таблиці.

    З’явиться область PivotTable Fields (Поля зведеної таблиці).

  2. У розділі PivotTable Field List (Список полів зведеної таблиці) клацніть поле в області та виберіть Move to Report Filter (Перейти до фільтру звіту).

Щоб створити кілька фільтрів звіту, повторіть цю дію. Для зручного використання ці фільтри відображаються над зведеною таблицею.

  • Щоб змінити порядок полів в області Фільтри, можна перетягнути їх у потрібні позиції або двічі клацнути поле та вибрати команду Move Up (Перемістити вгору) або Move Down (Перемістити вниз). У зведеній таблиці порядок також відповідно зміниться.

Відображення фільтрів звіту в рядках або стовпцях

  1. Клацніть окрему зведену таблицю або таблицю, зв’язану зі зведеною діаграмою.

  2. Клацніть правою кнопкою миші будь-де у зведеній таблиці, а потім виберіть Параметри зведеної таблиці.

  3. На вкладці Layout (Макет) налаштуйте такі параметри:

    1. В області Report Filter (Фільтр звіту) у списку Arrange fields (Упорядкувати поля) виконайте одну з таких дій:

      • Щоб відобразити фільтри звіту в рядках згори вниз, виберіть Down, Then Over (Вниз, потім вправо).

      • Щоб відобразити фільтри звіту в стовпцях зліва направо, виберіть Over, Then Down (Вправо, потім вниз).

    2. У полі Filter fields per column (Кількість полів фільтра в стовпці) введіть чи виберіть кількість полів, які потрібно відобразити, перш ніж перейти до іншого стовпця або рядка (залежно від значення, яке ви вибрали в списку Arrange fields (Упорядкувати поля) на попередньому кроці.

Вибір елементів у фільтрі звіту

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

  2. Установіть прапорці поруч з елементами, які потрібно відобразити у звіті. Щоб вибрати всі елементи, установіть прапорець (Select All) (Вибрати все).

    Після цього у фільтрі звіту відображатимуться відфільтровані елементи.

Відображення сторінок фільтра звіту на окремих аркушах

  1. Клацніть будь-де в окремій зведеній таблиці або у зв’язаній зі зведеною діаграмою з одним або кількома фільтрами звіту.

  2. На вкладці стрічки PivotTable Analyze (Аналіз зведеної таблиці) виберіть Options (Параметри) > Show Report Filter Pages (Відобразити сторінки фільтра звіту).

  3. У діалоговому вікні Show Report Filter Pages (Відображення сторінок фільтра звіту) виберіть поле фільтра звіту й натисніть кнопку OK.

Відображення десяти перших або останніх елементів

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

  1. У зведеній таблиці клацніть стрілку Стрілка розкривного меню фільтра поруч з елементом Row Labels (Підписи рядків) або Column Labels (Підписи стовпців).

  2. Клацніть правою кнопкою миші один із вибраних елементів, а потім виберіть Filter (Фільтр) > Top 10 (Перші 10) або Bottom 10 (Останні 10).

  3. У перше поле введіть число.

  4. У другому полі виберіть параметр, за яким потрібно фільтрувати. Доступні такі варіанти:

    • Щоб фільтрувати за кількістю елементів, виберіть пункт Items (Елементи).

    • Щоб фільтрувати за відсотками, виберіть пункт Percentage (Відсоток).

    • Щоб фільтрувати за сумою, виберіть пункт Sum (Сума).

  5. За потреби можна скористатися полем пошуку, щоб знайти певне значення.

Відображення або приховання вибраних елементів за допомогою фільтрування за вибраним

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

  2. Клацніть правою кнопкою миші вибрані елементи, а потім виберіть Фільтр.

  3. Виконайте одну з таких дій:

    • Щоб відобразити вибрані елементи, клацніть Зберігати лише вибрані елементи.

    • Щоб приховати вибрані елементи, клацніть Приховати вибрані елементи.

      Порада :  Щоб знову відобразити приховані елементи, вилучіть фільтр. Клацніть правою кнопкою миші інший елемент у тому самому полі, виберіть Фільтр, а потім клацніть Очистити фільтр.

Увімкнення або вимкнення параметрів фільтрування

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

  1. Клацніть будь-де у зведеній таблиці, щоб відобразити на стрічці вкладки для зведених таблиць.

  2. На вкладці PivotTable Analyze (Аналіз зведеної таблиці) виберіть команду Options (Параметри).

    1. У діалоговому вікні PivotTable Options (Параметри зведеної таблиці) відкрийте вкладку Layout (Макет).

    2. В області Layout (Макет) установіть або зніміть (відповідно до своїх потреб) прапорець Allow multiple filters per field (Дозволити кілька фільтрів для одного поля).

    3. Відкрийте вкладку Display (Відображення), а потім установіть або зніміть прапорець Field captions and filters (Підписи полів і фільтри), щоб відобразити або приховати підписи полів і розкривні списки фільтрів.

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

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

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

Параметри фільтрування для даних зведеної таблиці

  • Якщо зведена таблиця містить роздільники, просто клацніть елемент, який має відображатися в кожному роздільнику.

Роздільник із вибраними елементами

Маючи програму Excel для настільних комп’ютерів, можна скористатися кнопкою Відкрити у програмі Excel, щоб відкрити книгу та застосувати в ній додаткові фільтри або створити нові роздільники для даних у зведеній таблиці. Ось як це зробити:

Натисніть кнопку Відкрити в Excel і відфільтруйте дані у зведеній таблиці.

Кнопка "Відкрити в Excel"

Щоб дізнатися про останні оновлення веб-програми Excel Online, відвідайте блоґ Microsoft Excel.

Ознайомтеся з повним пакетом програм і служб Office або придбайте його на веб-сайті Office.com.

Див. також

Відео. Фільтрування елементів у зведеній таблиці

Створення зведеної таблиці для аналізу даних аркуша

Створення зведеної таблиці для аналізу зовнішніх даних

Створення зведеної таблиці для аналізу даних у кількох таблицях

Сортування даних у зведеній таблиці або зведеній діаграмі

Групування або розгрупування даних у зведеній таблиці

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×