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

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

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

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

Зміст

  • Створення простих фільтрів у Excel
  • Створення розширених фільтрів у Excel
  • Узагальнення відфільтрованих даних

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

Створення простих фільтрів у Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Далі клацніть стрілку спадного меню в стовпці C, і ви побачите однакові прапорці для кожного унікального значення в стовпці. Однак у цьому випадку ми хочемо натиснути «Фільтри чисел » , а потім натиснути «Більше» . Як бачите, є ще купа інших варіантів.

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

З’явиться нове діалогове вікно, у якому ви зможете ввести значення для фільтра. Ви також можете додати більше ніж один критерій за допомогою функції І або АБО. Ви можете сказати, що вам потрібні рядки, у яких значення більше 2 і не дорівнює 5, наприклад.

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

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

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

Ось і все для простих фільтрів у Excel. Вони дуже прості у використанні, а результати досить очевидні. Тепер давайте розглянемо складні фільтри за допомогою діалогового вікна « Розширені фільтри».

Створення розширених фільтрів у Excel

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

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

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

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

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

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

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

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

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

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

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

Зробивши це, клацніть будь-де в наборі даних, а потім натисніть кнопку Додатково . Поле «Діапазон списку » автоматично визначає ваш набір даних, оскільки ви клацнули його перед натисканням кнопки «Додатково». Тепер натисніть маленьку кнопку праворуч від кнопки діапазону критеріїв .

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

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

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

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

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

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

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

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

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

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

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

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

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

Перший – ПРОМІЖНИЙ ПІДСУМОК . Перш ніж використовувати будь-яку з цих спеціальних функцій, ви захочете очистити фільтр, а потім ввести функцію.

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

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

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

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

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

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

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

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

Перший параметр – це функція підсумовування, яку ви бажаєте використати, і, як і у випадку з ПРОМІЖНИМ ПІДСУМКОМ, 9 представляє функцію СУММА. У другому варіанті вам потрібно ввести 5, щоб ігнорувати приховані рядки. Останній параметр такий самий і є діапазоном клітинок.

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

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


Як створити та використовувати автотекст у Microsoft Word

Як створити та використовувати автотекст у Microsoft Word

Текстові процесори пройшли довгий шлях з початку 1980-х років, коли Microsoft вперше випустила Microsoft Word для MS-DOS. Його новаторською особливістю було те, що він був розроблений для використання з мишею.

Як створити діаграми Ганта в Microsoft Excel

Як створити діаграми Ганта в Microsoft Excel

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

Як вставити підпис у документ Microsoft Word

Як вставити підпис у документ Microsoft Word

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

Як створити мітки в Word з електронної таблиці Excel

Як створити мітки в Word з електронної таблиці Excel

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

Як використовувати злиття в Word для створення листів, наклейок і конвертів

Як використовувати злиття в Word для створення листів, наклейок і конвертів

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

Як використовувати функції автовідновлення та автоматичного резервного копіювання Excel

Як використовувати функції автовідновлення та автоматичного резервного копіювання Excel

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

Як відсортувати текст у Word

Як відсортувати текст у Word

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

Як зробити гістограму в Excel

Як зробити гістограму в Excel

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

Як створити вітальну листівку в MS Word

Як створити вітальну листівку в MS Word

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

Як налаштувати та використовувати формат MLA у Microsoft Word

Як налаштувати та використовувати формат MLA у Microsoft Word

Асоціація сучасної мови (MLA) — це організація, яка надає рекомендації для професійних та академічних письменників. Зараз багато університетів, роботодавців і професійних агентств вимагають від авторів відповідності стилю MLA, оскільки він простий у використанні та послідовний.

Як зберегти документи Office на локальному комп’ютері за замовчуванням

Як зберегти документи Office на локальному комп’ютері за замовчуванням

За умовчанням, якщо ви ввійшли в свої програми Office за допомогою облікового запису Microsoft, ваші програми зберігають ваші документи в сховищі OneDrive. Це спонукає вас зберігати свої файли в хмарі, щоб мати доступ до них на інших синхронізованих пристроях.

Як змусити OneDrive припинити надсилати спогади електронною поштою

Як змусити OneDrive припинити надсилати спогади електронною поштою

Якщо ви використовуєте OneDrive для резервного копіювання бібліотеки фотографій, він регулярно надсилає вам спогади електронною поштою — зображення та відео того самого дня минулих років. Ось як вимкнути їх на Windows, Android, iPhone та iPad.

Як відкрити декілька екземплярів Excel

Як відкрити декілька екземплярів Excel

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

Як друкувати на конверті в Microsoft Word

Як друкувати на конверті в Microsoft Word

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

Як створити форми для заповнення в Word

Як створити форми для заповнення в Word

Чи знаєте ви, що в Word можна створювати форми, які люди зможуть заповнювати. Коли ви чуєте про заповнювані форми, це майже завжди стосується документів Adobe і PDF, оскільки це найпопулярніший формат.

Як зробити буклет в Word

Як зробити буклет в Word

Microsoft Word необхідний кожному, хто виконує будь-яку роботу з документами. Слова існують так давно, що без них неможливо уявити роботу в офісі, у школі чи будь-яку іншу цифрову роботу.

Як записати макрос в Excel

Як записати макрос в Excel

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

Як знайти та обчислити діапазон у Excel

Як знайти та обчислити діапазон у Excel

Математично ви обчислюєте діапазон, віднімаючи мінімальне значення від максимального значення певного набору даних. Він представляє розкид значень у наборі даних і корисний для вимірювання мінливості – чим більший діапазон, тим більш розкиданими та мінливими є ваші дані.

Як відняти дати в Excel

Як відняти дати в Excel

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

Як приховати аркуші, клітинки, стовпці та формули в Excel

Як приховати аркуші, клітинки, стовпці та формули в Excel

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

LinkedIn: Як заблокувати/розблокувати людей

LinkedIn: Як заблокувати/розблокувати людей

Дізнайтеся, як ефективно заблокувати чи розблокувати користувачів на LinkedIn з нашими зрозумілими інструкціями.

Як приховати, за ким ви стежите у Facebook

Як приховати, за ким ви стежите у Facebook

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

Facebook: як приховати своє прізвище

Facebook: як приховати своє прізвище

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

Як виправити проблему «Відео на Facebook не відтворюється» 12 найкращих методів

Як виправити проблему «Відео на Facebook не відтворюється» 12 найкращих методів

Дізнайтеся, як вирішити проблему «Відео на Facebook не відтворюється» за допомогою 12 перевірених методів.

Як додати фігури в Google Docs

Як додати фігури в Google Docs

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

Google Таблиці: видалити лінії сітки (відображення та роздруківки)

Google Таблиці: видалити лінії сітки (відображення та роздруківки)

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

Виправлено: історія переглядів YouTube не оновлюється

Виправлено: історія переглядів YouTube не оновлюється

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

Зум: як віддзеркалити веб-камеру

Зум: як віддзеркалити веб-камеру

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

Як змінити відображуване ім’я та ім’я в Twitter

Як змінити відображуване ім’я та ім’я в Twitter

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

Ваша PS5 перегрівається? 6 способів охолодити

Ваша PS5 перегрівається? 6 способів охолодити

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