Як фільтрувати дані у зведеній таблиці в Excel

Існує різні способи фільтрації даних у зведеній таблиці в Excel.

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

Типи фільтрів у зведеній таблиці

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

Давайте розглянемо ці фільтри по одному:

• Фільтр звітів: Цей фільтр дозволяє детально перейти до підмножини загального набору даних. Наприклад, якщо у вас є дані про роздрібні продажі, ви можете аналізувати дані для кожного регіону, вибравши один або кілька регіонів (так, це також дозволяє декілька варіантів вибору). Ви створюєте цей фільтр, перетягуючи поле зведеної таблиці в область фільтрів.
• Фільтр міток рядків/стовпців: Ці фільтри дозволяють фільтрувати відповідні дані на основі елементів поля (наприклад, фільтрування конкретного елемента або елемента, що містить певний текст) або значень (наприклад, фільтрування 10 найпопулярніших елементів за значенням або елементів зі значенням більше/менше вказане значення).
  • Вікно пошуку: Ви можете отримати доступ до цього фільтра у фільтрі мітки рядка/стовпця, що дозволяє швидко фільтрувати на основі введеного тексту. Наприклад, якщо вам потрібні дані лише для Costco, просто введіть Costco тут, і він відфільтрує це для вас.
  • Прапорці: Вони дозволяють вибрати певні елементи зі списку. Наприклад, якщо ви хочете вручну відібрати роздрібних торговців для аналізу, ви можете зробити це тут. Крім того, ви також можете вибірково виключити деяких продавців, знявши прапорець.

Зверніть увагу, що користувачу доступні ще два інструменти фільтрації: нарізки та часові шкали (які не розглянуті в цьому посібнику).

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

Приклади використання фільтрів у зведеній таблиці

У цьому розділі розглянуті такі приклади:

• Фільтруйте 10 найпопулярніших предметів за значенням/відсотком/сумою.
• Фільтрувати елементи на основі значення.
• Фільтрувати за допомогою фільтра етикеток.
• Фільтрувати за допомогою вікна пошуку.

Фільтруйте 10 найкращих елементів у зведеній таблиці

Ви можете використовувати 10 найкращих параметрів фільтра у зведеній таблиці, щоб:

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

Припустимо, у вас є зведена таблиця, як показано нижче:

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

Фільтрувати верхній/нижній елементи за значенням

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

Нижче наведено кроки для цього.

• Перейдіть до Фільтр міток рядків -> Фільтри значень -> 10 найкращих.
• У діалоговому вікні 10 найкращих фільтрів потрібно вказати чотири варіанти:
  • Зверху/Знизу: У цьому випадку, оскільки ми шукаємо 10 найкращих роздрібних продавців, виберіть Верх.
  • Кількість елементів, які потрібно відфільтрувати. У цьому випадку, оскільки ми хочемо отримати топ -10 предметів, це буде 10.
  • Третє поле - це спадне меню з трьома параметрами: Елементи, Відсоток та Сума. У цьому випадку, оскільки ми хочемо 10 найкращих роздрібних продавців, оберіть Товари.
  • В останньому полі перераховані всі різні значення, перелічені в області значень. У цьому випадку, оскільки у нас є лише сума продажів, вона показує лише „Суму продажів“.

Це дасть вам відфільтрований список із 10 роздрібних продавців на основі їх вартості продажів.

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

Фільтрувати елементи зверху/знизу, які складають визначений відсоток значення

Ви можете використовувати фільтр 10 найкращих, щоб отримати список 10 найкращих відсотків (або будь -якої іншої кількості, скажімо, 20 відсотків, 50 відсотків тощо) за вартістю.

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

Нижче наведено кроки для цього.

• Перейдіть до Фільтр міток рядків -> Фільтри значень -> 10 найкращих.
• У діалоговому вікні 10 найкращих фільтрів потрібно вказати чотири варіанти:
  • Зверху/знизу: У цьому випадку, оскільки ми шукаємо кращих роздрібних продавців, які складають 25% від загального обсягу продажів, виберіть Верх.
  • У другому полі вам потрібно вказати відсоток продажів, який повинні враховувати провідні роздрібні торговці. У цьому випадку, оскільки ми хочемо отримати провідних роздрібних продавців, які складають 25% продажів, це буде 25.
  • У третьому полі виберіть Відсоток.
  • В останньому полі перераховані всі різні значення, перелічені в області значень. У цьому випадку, оскільки у нас є лише сума продажів, вона показує лише „Суму продажів“.

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

Ви можете використовувати той самий процес, щоб отримати роздрібні торговці, які складають нижчі 25% (або будь -який інший відсоток) загального обсягу продажів.

Фільтрувати елементи зверху/знизу, які складають визначене значення

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

Це можна зробити за допомогою фільтра 10 найкращих у зведеній таблиці.

Зробити це:

• Перейдіть до Фільтр міток рядків -> Фільтри значень -> 10 найкращих.
• У діалоговому вікні 10 найкращих фільтрів потрібно вказати чотири варіанти:
  • Зверху/знизу: У цьому випадку, оскільки ми шукаємо найкращих роздрібних продавців, які мають загальний обсяг продажів 20 мільйонів, виберіть Верх.
  • У другому полі потрібно вказати значення, яке повинні враховувати провідні роздрібні торговці. У цьому випадку, оскільки ми хочемо отримати провідних роздрібних продавців, які складають 20 мільйонів продажів, це буде 20000000.
  • У третьому полі виберіть Сума.
  • В останньому полі перераховані всі різні значення, перелічені в області значень. У цьому випадку, оскільки у нас є лише сума продажів, вона показує лише „Суму продажів“.

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

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

Ви можете фільтрувати елементи на основі значень у стовпцях у області значень.

Припустимо, у вас є зведена таблиця, створена з використанням даних роздрібних продажів, як показано нижче:

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

Нижче наведено кроки для цього.

• Перейдіть до Фільтр міток рядків -> Фільтри значень -> Більш ніж.
• У діалоговому вікні Фільтр значень:
  • Виберіть значення, які потрібно використовувати для фільтрації. У цьому випадку це сума продажів (якщо у вас є більше елементів у області значень, у спадному меню буде показано все це).
  • Виберіть умову. Оскільки ми хочемо, щоб усі продавці, продажі яких перевищують 3 мільйони, виберіть "більше ніж".
  • Введіть 3000000 в останнє поле.
• Натисніть OK.

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

Аналогічно, є багато інших умов, які можна використовувати, наприклад, дорівнює, не дорівнює, менше, між, тощо.

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

Фільтри міток стають у нагоді, коли у вас є величезний список, і ви хочете відфільтрувати певні елементи на основі його назви/тексту.

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

Нижче наведено кроки для цього.

• Перейдіть до Фільтр міток рядків -> Фільтри міток -> Містить.
• У діалоговому вікні фільтра міток:
  • За умовчанням вибрано "Містить" (оскільки ми вибрали містить у попередньому кроці). Ви можете змінити це тут, якщо хочете.
  • Введіть текстовий рядок, для якого потрібно відфільтрувати список. В даному випадку це «долар».
• Натисніть OK.

Ви також можете використовувати символи підстановки разом з текстом.

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

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

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

Фільтрація списку за допомогою вікна пошуку багато в чому нагадує опцію contains у фільтрі міток.

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

Ось кроки:

• Натисніть спадне меню «Фільтр міток», а потім клацніть поле пошуку, щоб розмістити в ньому курсор.
• Введіть пошуковий термін, який у даному випадку означає «долар». Ви помітите, що список фільтрується у вікні пошуку нижче, і ви можете зняти прапорець у будь -якого роздрібного продавця, якого потрібно виключити.
• Натисніть OK.

Це миттєво відфільтрувало б усіх роздрібних торговців, які містять термін "долар".

У вікні пошуку можна використовувати символи підстановки. Наприклад, якщо ви хочете отримати назву всіх роздрібних продавців, які починаються з алфавіту Т, використовуйте рядок пошуку як Т* (Т, за яким йде зірочка). Оскільки зірочка означає будь -яку кількість символів, це означає, що назва може містити будь -яку кількість символів після Т.

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

Про рядок пошуку потрібно знати кілька важливих речей:

• Якщо ви фільтруєте один раз за одним критерієм, а потім знову за іншим, перший критерій відкидається, і ви отримуєте список другого критерію. Фільтрація не є додатковою.
• Перевага використання вікна пошуку полягає в тому, що ви можете вручну скасувати вибір деяких результатів. Наприклад, якщо у вас є величезний список фінансових компаній, і ви хочете лише відфільтрувати банки, ви можете шукати термін "банк". Але у випадку, якщо деякі компанії прокралися, але це не банки, ви можете просто зняти прапорець і утримати його.
• Не можна виключати конкретні результати. Наприклад, якщо ви хочете виключити лише роздрібних торговців, які містять у собі долар, це неможливо зробити за допомогою вікна пошуку. Це, однак, можна зробити за допомогою фільтра етикетки з використанням умови "не містить".

Вам також можуть сподобатися такі підручники зведеної таблиці:

• Створення зведеної таблиці в Excel - покроковий посібник.
• Підготовка вихідних даних для зведеної таблиці.
• Групування номерів у зведеній таблиці в Excel.
• Групування дат у зведених таблицях у Excel.
• Оновити зведену таблицю в Excel.
• Видалення зведеної таблиці в Excel.
• Як додати та використовувати обчислюване поле зведеної таблиці Excel.
• Застосування умовного форматування у зведеній таблиці в Excel.
• Зведений кеш в Excel - що це таке і як його краще використовувати?
• Замініть порожні клітинки нулями у зведених таблицях Excel.
• Підрахувати окремі значення у зведеній таблиці