Як фільтрувати дані в Excel

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

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

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

Створюйте прості фільтри в Excel

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

Вище, у мене є деякі підроблені дані, і я хочу створити фільтр на Місто стовпця. У Excel це дійсно легко зробити. Ідіть вперед і натисніть на Дані на стрічці і натисніть на Фільтр кнопки. Вам не потрібно вибирати дані на аркуші або натискати в першому рядку.

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

Тепер натисніть на стрілку зі спадного списку у стовпці "Місто". Ви побачите кілька різних варіантів, які я поясню нижче.

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

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

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

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

Тепер, скажімо, я хочу фільтрувати на другій колонці, щоб ще більше зменшити кількість результатів. У стовпці C, у мене є загальна кількість членів кожної сім'ї, і я хочу бачити результати тільки для сімей з більш ніж двома членами.

Натисніть стрілку зі спадного списку в стовпці С, і ви побачите однакові прапорці для кожного унікального значення в стовпці. Однак у цьому випадку ми хочемо натиснути на Кількість фільтрів а потім натисніть кнопку Більш чим. Як ви можете бачити, є й багато інших варіантів.

З'явиться нове діалогове вікно, у якому ви зможете ввести значення фільтра. Також можна додати кілька критеріїв з функцією AND або OR. Можна сказати, що вам потрібні рядки, де значення більше 2, а не 5, наприклад.

Тепер я до 5 рядків даних: сім'ї тільки з Нового Орлеана і з 3 або більше членів. Досить просто? Зауважте, що ви можете легко очистити фільтр у стовпці, натиснувши на спадне меню і натиснувши кнопку Очистити фільтр із назви "Стовпець" посилання.

Так ось про це прості фільтри в Excel. Вони дуже прості у використанні, і результати досить прості. Тепер давайте розглянемо складні фільтри за допомогою Додатково діалогового вікна фільтрів.

Створіть додаткові фільтри в Excel

Якщо ви хочете створити більш просунуті фільтри, ви повинні використовувати Додатково діалогового вікна фільтра. Наприклад, припустимо, я хотів би бачити всі сім'ї, які живуть у Новому Орлеані з більш ніж 2 членами їх сім'ї АБО всі сім'ї в Кларксвіллі з більш ніж 3 членами їх сім'ї І тільки ті, які мають a .EDU закінчення адреси електронної пошти. Тепер ви не можете зробити це за допомогою простого фільтра.

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

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

Отже, що ж ми можемо вводити в ці клітини? Так, давайте почнемо з нашого прикладу. Ми хочемо бачити дані тільки з Нового Орлеана чи Кларксвілла, так що давайте наберемо їх у клітинки E2 та E3.

Коли ви вводите значення на різні рядки, це означає, що це АБО. Тепер ми хочемо, щоб сім'ї Нового Орлеана мали більше двох членів і сім'ї Кларксвілл з більш ніж 3 членами. Для цього введіть > 2 в С2 і > 3 у С3.

Оскільки> 2 і Новий Орлеан знаходяться в одному рядку, це буде оператор AND. Те ж саме стосується рядка 3 вище. Нарешті, ми хочемо, щоб тільки сім'ї з .EDU закінчували адресу електронної пошти. Для цього просто введіть * .edu як D2, так і D3. Символ * означає будь-яку кількість символів.

Після цього натисніть будь-де у вашому наборі даних і натисніть кнопку Додатково кнопки. The Список RangПоле автоматично визначить ваш набір даних, оскільки ви натиснули на нього, перш ніж натиснути кнопку Додатково. Тепер натисніть маленьку маленьку кнопку справа Діапазон критеріїв кнопки.

Виберіть все від A1 до E3, а потім знову натисніть на ту ж кнопку, щоб повернутися до діалогового вікна Розширений фільтр. Натисніть "ОК", і ваші дані тепер повинні бути відфільтровані!

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

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

Узагальнення відфільтрованих даних

Тепер, скажімо, я хочу підсумувати кількість членів сім'ї на моїх відфільтрованих даних, як би я це зробив? Ну, давайте очистимо наш фільтр, натиснувши на Очистити на стрічці. Не хвилюйтеся, застосувати розширений фільтр дуже просто, просто натиснувши кнопку "Додатково" і знову натиснувши кнопку "OK".

У нижній частині нашого набору даних додамо клітинку, яку називаємо Усього а потім додайте функцію суми, щоб підсумувати загальну кількість членів родини. У моєму прикладі я просто набрав = SUM (C7: C31).

Тому, якщо я дивлюся на всі сім'ї, у мене є 78 членів. Тепер давайте перейдемо і повторно застосуємо наш Розширений фільтр і подивимося, що відбувається.

Ой! Замість того, щоб показувати правильне число, 11, я все ще бачу загальний 78! Чому так? Ну, функція SUM не ігнорує приховані рядки, тому вона все ще виконує розрахунок, використовуючи всі рядки. На щастя, існує кілька функцій, які можна використовувати для ігнорування прихованих рядків.

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

Після видалення фільтра введіть його та введіть = SUBTOTAL ( і ви побачите випадаюче вікно з набором опцій. Використовуючи цю функцію, ви спочатку вибираєте тип функції підсумовування, який ви бажаєте використовувати, використовуючи номер.

У нашому прикладі я хочу використовувати SUM, так що я б ввів номер 9 або просто клацніть по ньому з випадаючого меню. Потім введіть кому і виберіть діапазон клітин.

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

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

Друга функція, яка працює майже так само, як функція SUBTOTAL АГРЕГАТ. Єдина відмінність полягає в тому, що в функції AGGREGATE існує інший параметр, в якому потрібно вказати, що ви хочете ігнорувати приховані рядки.

Перший параметр - це функція підсумовування, яку потрібно використовувати, і, як і у випадку з SUBTOTAL, 9 - це функція SUM. Другий варіант полягає в тому, де потрібно ввести 5, щоб ігнорувати приховані рядки. Останній параметр є однаковим і є діапазоном клітин.

Ви також можете прочитати мою статтю про зведені функції, щоб дізнатися, як використовувати функцію AGGREGATE та інші функції, наприклад, MODE, MEDIAN, AVERAGE тощо..

Сподіваємося, ця стаття дасть вам гарну відправну точку для створення та використання фільтрів в Excel. Якщо у Вас виникли питання, не соромтеся розмістити коментар. Насолоджуйтесь!