Фільтрування даних у Excel – просте та комплексне

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

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

---

1. Як додати фільтр в Excel

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

1.1 Команда фільтрації на вкладці Дані

Виберіть будь-які клітинки в діапазоні або таблиці, до яких потрібно додати фільтр, натисніть дані > Фільтрувати

1.2 Команда «Фільтр» на вкладці «Домашня сторінка».

Виберіть будь-які клітинки в діапазоні або таблиці, до яких потрібно додати фільтр, натисніть Головна > Сортувати та фільтрувати > Фільтрувати

1.3 Додайте фільтр за допомогою ярлика

Виділіть будь-які комірки в діапазоні або таблиці, до яких потрібно додати фільтр, а потім натисніть Ctrl + Shift + L ключі.

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

---

2. Як застосувати фільтр у Excel (один або кілька критеріїв)

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

2.1 Застосувати фільтр до одного стовпця (один критерій)

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

1. 1) Клацніть на стрілку спадного меню у заголовку стовпця.
2. 2) Укажіть необхідну умову фільтра.
3. 3) Клацніть на OK , щоб почати фільтрування. Дивіться знімок екрана:
   

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

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

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

2.2 Застосувати фільтр із кількома критеріями до кількох стовпців (кілька критеріїв)

2.2.1 Застосувати фільтр із кількома критеріями до кількох стовпців по одному

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

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

2.2.2 Одночасно застосовувати фільтр з кількома критеріями до кількох стовпців

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

Припустимо, у вас є таблиця даних, як показано на знімку екрана, і ви хочете фільтрувати дані з декількох стовпців на основі кількох критеріїв: Товар = AAA-1 та замовлення> 80, or Загальна ціна> 10000. Будь ласка, спробуйте один із наступних методів, щоб це зробити.

2.2.2.1 Застосувати фільтр до кількох стовпців за допомогою функції Розширений фільтр

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

1. По-перше, створіть критерії на робочому аркуші, як показано на скріншоті нижче.

Примітка: Для критеріїв AND розмістіть значення критеріїв у різних клітинках одного рядка. І розмістіть значення критерію АБО в іншому рядку.

2. клацання дані > Advanced ввімкнути Розширений фільтр функції.

3 В Розширений фільтр діалогове вікно, будь ласка, налаштуйте наступним чином.

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

2.2.2.2 Легко застосувати фільтр до кількох стовпців за допомогою чудового інструменту

Як І та OR критеріями фільтрації непросто керуватись у вищезазначеному методі, тут настійно рекомендуємо Супер фільтр особливість Kutools для Excel. За допомогою цієї функції ви можете легко застосувати фільтр до кількох стовпців з критеріями І та АБО в Excel.

1. Після встановлення Kutools для Excel, Натисніть Kutools Plus > Супер фільтр.

Тоді Супер фільтр панель відображається з правого боку аркуша.

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

2 В Супер фільтр , будь ласка, налаштуйте критерії фільтра наступним чином.

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

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

Натисніть, щоб дізнатися більше про цю функцію.

---

3. Як використовувати фільтр в Excel

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

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

3.1.1 Фільтрувати текстові комірки з певними критеріями (починати з, закінчувати, містити тощо)

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

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

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

3. клацання Текстові фільтри > Починається з.

4 В Спеціальний автофільтр діалогове вікно, введіть конкретний символ (тут я вводжу символ J) у текстове поле, а потім клацніть ОК.

Порада: Ви можете додати інший і or Or критерії стосунків, як вам потрібно.

Тепер усі клітинки, що починаються з символу J, відображаються в стовпці D, як показано нижче.

3.1.2 Фільтр з урахуванням регістру

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

3.1.2.1 Відфільтруйте певний текст з урахуванням регістру за формулою та командою Фільтр

Припустимо, ви хочете відфільтрувати всі великі регістри певного тексту, наприклад, «ТЕКСТОВІ ІНСТРУМЕНТИ» у стовпці B, виконайте наступне.

1. Створіть допоміжний стовпець крім вихідного діапазону даних (тут я вибираю стовпець D як допоміжний стовпець). Введіть формулу нижче у другу комірку, а потім натисніть клавішу Enter. Виділіть комірку результату, перетягніть її Ручка автозаповнення вниз, щоб отримати інші результати.

= ТОЧНО (B2, ВЕРХНІЙ (B2))

Примітка: Ця формула допомагає ідентифікувати великі та малі клітини. Якщо комірка містить усі великі символи, результат буде ІСТИНА, в іншому випадку ви отримаєте результат як ПОМИЛКОВИЙ.

2. Виберіть стовпці B і D (буде вибрано стовпець C, неважливо), натисніть Data> фільтр щоб додати до них фільтри.

3. Перейдіть до стовпця B (стовпець містить тексти, які ви фільтруватимете), а потім налаштуйте наступним чином.

Тепер у стовпці Б відображаються лише великі та малі регістри "текстових інструментів".

4. Клацніть стрілку спадного меню у стовпці D, зніміть прапорець Вибрати всі , поставте прапорець біля ІСТИНА , а потім клацніть на OK кнопки.

Потім усі великі текстові «текстові інструменти» у стовпці B відфільтровуються, як показано на скріншоті нижче.

3.1.2.2 Легко фільтруйте комірки з урахуванням регістру за допомогою чудового інструменту

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

1. Виберіть діапазон стовпців, у якому ви будете фільтрувати тексти, і натисніть Kutools Plus > Спеціальний фільтр > Спеціальний фільтр.

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

3. Тоді a Kutools для Excel спливає діалогове вікно, щоб повідомити, скільки клітинок знайдено і буде відфільтровано, клацніть на OK кнопки.

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

Натисніть, щоб дізнатися більше про цю функцію.

3.1.3 Фільтрувати за довжиною тексту

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

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

Насправді, команда Filter має вбудований оператор для вирішення цієї проблеми, будь ласка, виконайте наступне.

1. Виберіть діапазон комірок, які потрібно відфільтрувати (тут я вибираю B1: B27), а потім додайте фільтр до цього діапазону стовпців, клацнувши дані > Фільтрувати

2. Клацніть стрілку спадного меню у заголовку стовпця, а потім клацніть Текстові фільтри > Спеціальний фільтр. Дивіться знімок екрана:

3 В Спеціальний автофільтр діалоговому вікні, виберіть критерії як рівні, введіть 10 знаків питання (?) як режим шаблону в текстове поле, а потім клацніть на OK кнопки.

Tips : Ці 10 знаків запитання означають, що він буде відповідати текстовому рядку, довжина якого дорівнює 10.

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

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

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

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

= LEN (B2)

Тепер ви отримуєте довжину тексту кожної комірки у вказаному стовпці.

2. Виділіть допоміжний стовпець (включайте заголовок), клацніть дані > фільтр додати до нього фільтр.

3. Клацніть стрілку спадного меню, зніміть прапорець Вибрати всі , щоб скасувати вибір усіх елементів, а потім встановіть лише прапорець біля номера 10 і, нарешті, клацніть на OK кнопки.

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

3.1.3.3 Легко фільтруйте комірки за довжиною тексту за допомогою чудового інструменту

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

1. Виберіть діапазон стовпців, який ви будете фільтрувати, на основі певної довжини тексту, натисніть Kutools Plus > Спеціальний фільтр > Спеціальний фільтр. Дивіться знімок екрана:

2 В Спеціальний фільтр діалогове вікно, будь ласка, налаштуйте наступним чином.

3. Kutools для Excel спливає діалогове вікно, щоб повідомити, скільки комірок знайдено і буде відфільтровано, натисніть OK йти вперед.

Потім усі комірки з довжиною текстового рядка, рівною 10, фільтруються, як показано на знімку екрана нижче.

3.2 Фільтрувати номери

В Excel також дуже легко фільтрувати номери за допомогою команди Числові фільтри.

Припустимо, ви хочете відфільтрувати комірки з числами від 15000 до 20000 у стовпці (наприклад, стовпець C, як показано на знімку екрана нижче), ви можете зробити наступне для його досягнення.

1. Виберіть діапазон стовпців, що містить цифри, які ви фільтруєте, натисніть дані > фільтр додати фільтр.

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

Порада: Оскільки я хочу відфільтрувати комірки з числами від 15000 до 20000, тут я ввожу 15000 та 20000 окремо в текстові поля.

Тепер комірки з номерами від 15000 до 20000 фільтруються, як показано на знімку екрана нижче.

3.3 Фільтрувати дати

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

3.3.1 Фільтрувати дати за днями тижня або вихідними

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

3.3.1.1 Фільтрувати дати за днями тижня або вихідними за формулою та командою Фільтр

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

1. У порожню комірку (у цьому випадку D2) введіть у неї формулу, наведену нижче, і натисніть клавішу Що натомість? Створіть віртуальну версію себе у ключ. Виділіть комірку результату, а потім перетягніть Ручка автозаповнення над наведеними нижче клітинками застосувати цю формулу.

= ВИХОДНИЙ (A2)

Порада:

Примітка: Як бачите, формула повертає числа з 1 до 7, який вказує день тижня з неділя до Saturday (1 - для неділі, 7 - для суботи).

2. Виділіть цілі результати формули (включіть клітинку заголовка), натисніть дані > Фільтрувати

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

Потім усі вихідні або певний день тижня клітини фільтруються. Дивіться знімок екрана:

3.3.1.2 Легко фільтруйте дати за днями тижня або вихідними за допомогою чудового інструменту

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

1. Виберіть комірки, що містять дати, які потрібно відфільтрувати, за певним днем ​​тижня.

2. клацання Kutools Plus > Спеціальний фільтр > Спеціальний фільтр.

3 В Спеціальний фільтр діалогове вікно, будь ласка, налаштуйте наступним чином.

4. Тоді a Kutools для Excel спливає діалогове вікно з кількістю знайдених комірок, які будуть відфільтровані, натисніть OK йти вперед.

Тепер усі вихідні або будь-який день тижня клітини фільтруються.

3.3.2 Відфільтруйте клітини за робочими днями за допомогою чудового інструменту

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

1. Застосуйте кроки, описані вище щоб увімкнути утиліту Спеціальний фільтр.

2 В Спеціальний фільтр діалоговому вікні, виконайте наведені нижче налаштування.

3. Тоді a Kutools для Excel спливає діалогове вікно. Клацніть OK йти вперед.

Тепер усі комірки робочого дня відфільтровані.

3.4 Формати фільтрів

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

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

3.4.1 Фільтрувати за текстом, відформатованим жирним / курсивом

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

3.4.1.1 Відфільтруйте текст, відформатований жирним / курсивом, за формулою та командою Фільтр

Поєднання формули Get.Cell і команди "Фільтр" може допомогти відфільтрувати жирний відформатований текст у діапазоні стовпців.

1. клацання Формули > Визначте ім’я.

2 В Нове ім'я у діалоговому вікні потрібно:

Синтаксис формули:

=GET.CELL(type_num, reference)

Аргументи формули

3. Виділіть порожню комірку в тому ж рядку B2, введіть у неї формулу, наведену нижче, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть комірку результату, перетягніть її Ручка автозаповнення над наведеними нижче клітинками застосувати цю формулу.

= Filter_Bold_Cells

4. Виділіть цілі комірки результату (включайте заголовок), натисніть дані > Фільтрувати

5. Клацніть стрілку спадного меню, встановіть лише прапорець біля ІСТИНА параметр, а потім натисніть кнопку ОК.

Потім усі текстові комірки жирного або курсиву фільтруються. Дивіться знімок екрана:

3.4.1.2 Відфільтруйте жирний або курсив відформатований текст за допомогою команд Знайти та Замінити та Фільтрувати

Для досягнення можна також використовувати комбінацію команд Знайти та Замінити та Фільтрувати.

1. Виберіть діапазон стовпців, що містить жирний або курсив текстові комірки, які ви фільтруєте, а потім натисніть Ctrl + F ключі.

2 В Знайти і замінити діалогове вікно, вам потрібно налаштувати наступним чином.

3. Тепер усі клітинки з жирним або курсивом виділено в оригінальному діапазоні, натисніть Головна > Колір заливки, а потім виберіть колір заливки для вибраних комірок.

4. Знову виділіть весь діапазон стовпців, натисніть дані > фільтр додати до нього фільтр.

5. Клацніть стрілку спадного меню, виберіть Фільтр за кольором, а потім клацніть колір заливки, який ви вказали зараз Фільтрувати за кольором комірки. Дивіться знімок екрана:

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

3.4.1.3 Легко фільтруйте жирний або курсив відформатований текст за допомогою чудового інструменту

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

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

2. клацання Kutools Plus > Спеціальний фільтр > Фільтр жирний / Фільтр курсив. Дивіться знімок екрана:

3. клацання OK в спливаючому Kutools для Excel діалогове вікно для продовження (це діалогове вікно з’явиться, щоб повідомити, скільки комірок відповідає критеріям).

Тепер усі текстові комірки, відформатовані жирним або курсивом, відфільтровані.

3.4.2 Фільтрування за закресленим відформатованим текстом

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

3.4.2.1 Відфільтруйте закреслений відформатований текст за допомогою визначеної користувачем функції та команди Фільтр

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

1 Натисніть кнопку інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль. А потім скопіюйте наведений нижче код VBA в код вікна.

Function HasStrike(Rng As Range) As Boolean
HasStrike = Rng.Font.Strikethrough
End Function

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

4. Виберіть порожню комірку (ця комірка повинна знаходитися в тому самому рядку комірки, яку потрібно обчислити), введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть комірку результату, перетягніть її Ручка автозаповнення над наведеною нижче клітинкою застосувати цю формулу.

= HasStrike (B2)

Примітка: Якщо відповідна комірка має ефект закреслення шрифту, вона повертається ІСТИНА, інакше повертається ПОМИЛКОВИЙ.

5. Виділіть цілі комірки результату (включаючи комірку заголовка), натисніть дані > Фільтрувати

6. Потім клацніть стрілку спадного меню> встановіть лише прапорець біля опції TRUE> натисніть ОК. Див. Показаний знімок екрана.

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

3.4.2.2 Легко фільтруйте закреслений відформатований текст за допомогою чудового інструменту

З Спеціальний фільтр корисність Kutools для Excel, всі відформатовані закреслені комірки можна відфільтрувати безпосередньо лише за кілька кліків.

1. Виберіть діапазон стовпців, у якому ви фільтруєте всі відформатовані комірки, натисніть Kutools Plus > Спеціальний фільтр > Фільтруйте закреслення.

2. Тоді a Kutools для Excel спливає діалогове вікно, щоб повідомити, скільки клітинок має право, натисніть OK йти вперед.

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

3.4.3 Фільтрувати за шрифтом або кольором тла

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

3.4.3.1 Відфільтруйте за одним шрифтом або кольором тла за допомогою команди Фільтр

Ви можете безпосередньо застосувати функцію "Фільтрувати за кольором" команди "Фільтр" для фільтрування комірок за певним шрифтом або кольором тла в Excel.

1. Виберіть діапазон стовпців, для якого ви фільтруватимете комірки, за шрифтом або кольором тла, а потім натисніть дані > Фільтрувати

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

3.4.3.2 Фільтрувати за кількома кольорами тла за допомогою визначеної користувачем функції та команди Фільтр

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

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

1 Натисніть кнопку інший + F11 клавіші одночасно.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль. Потім скопіюйте наведений нижче код VBA у вікно коду.

Код VBA: Отримайте індекс кольору фону клітинки

Function GetColor(x As Range) As Integer
GetColor = x.Interior.ColorIndex
End Function

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

4. У новому стовпці введіть заголовок у першу комірку (ця комірка повинна знаходитись у тому самому рядку заголовка вихідного діапазону).

5. Виділіть порожню комірку поруч із коміркою заголовка (тут я вибираю E2), введіть в неї формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у ключ. Виділіть комірку результату, а потім перетягніть її Ручка автозаповнення над наведеними нижче клітинками застосувати цю формулу.

= GetColor (B2)

Примітка: Якщо комірка не має кольору заливки, вона повертає -4142.

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

7. Клацніть стрілку спадного меню, щоб відкрити випадаючий список, а потім налаштуйте наступним чином.

Тепер комірки фільтруються за вказаними кольорами фону, як показано на знімку екрана нижче.

3.4.3.3 Легко фільтруйте за шрифтом або кольором тла за допомогою чудового інструменту

Безсумнівно, застосувати вбудовану функцію "Фільтр за кольором" легко фільтрувати комірки за шрифтом або кольором тла. Однак недоліком є ​​те, що розкривний список охоплює вміст вихідних даних, тому ми не можемо переглядати дані в будь-який час для правильного вибору шрифту або кольору тла. Щоб уникнути цієї проблеми, тут рекомендуємо Спеціальний фільтр корисність Kutools для Excel.

1. Виберіть діапазон стовпців, для якого ви фільтруватимете комірки, за шрифтом або кольором тла, а потім натисніть Kutools Plus > Спеціальний фільтр > Спеціальний фільтр.

2 В Спеціальний фільтр діалогове вікно, будь ласка, налаштуйте наступним чином.

Потім усі комірки із заданим кольором шрифту або кольором тла у вибраному діапазоні фільтруються.

3.4.4 Фільтрувати комірки, що містять формули

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

3.4.4.1 Фільтрувати комірки формул за допомогою визначеної користувачем функції та команди Фільтрувати

По-перше, вам потрібно з’ясувати всі клітинки формул у списку за допомогою функції, визначеної користувачем, а потім застосувати команду «Фільтр», щоб відфільтрувати клітинки формули на основі результатів.

1 Натисніть кнопку інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль потім скопіюйте наведений нижче код VBA у вікно коду.

Function HasFormula(Cell)
HasFormula = Cell.HasFormula
End Function

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

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

= HasFormula (C2)

Як показано на скріншоті вище, результати є ПОМИЛКОВИЙ та ІСТИНА, які вказують, що якщо відповідна комірка є коміркою формули, вона повертає TRUE, інакше повертає FALSE.

5. Виділіть комірки результату (включіть комірку заголовка), натисніть дані > фільтр додати до нього фільтр.

6. Клацніть стрілку спадного меню, встановіть лише прапорець біля ІСТИНА поле, а потім натисніть кнопку ОК.

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

3.4.4.2 Легко фільтруйте клітини формул за допомогою чудового інструменту

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

1. Виберіть список, який потрібно відфільтрувати, все клітинки формули, а потім клацніть Kutools Plus > Спеціальний фільтр > Формула фільтра.

2. Kutools для Excel спливає діалогове вікно, щоб повідомити, скільки клітинок має право, натисніть OK йти вперед.

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

Крім того, ви можете використовувати Спеціальний фільтр особливість Kutools для Excel легко фільтрувати комірки з іншим форматуванням, наприклад:

Фільтрувати всі комірки з коментарями, натисніть, щоб дізнатись більше ...

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

3.5 Фільтр із підстановкою

Іноді під час фільтрації ви можете забути точні критерії пошуку. У цьому випадку ми рекомендуємо використовувати символи підстановки.

У Excel лише 3 символи підстановки:

Символ підстановки	Опис	Приклад
* (зірочка)	Представляє будь-яку кількість символів	Наприклад, * ягода знаходить “Ожина»,«полуничний»,«Чорниця" і так далі
? (знак питання)	Представляє будь-який окремий символ	Наприклад, l? ck знаходить “Лизати»,«замикати»,«відсутність" і так далі
~ (приплив) подальшою *, ?або ~	Представляють справжнє *,? or ~ характер	Наприклад, Обличчя ~ * Магазин знахідки “Face * Shop”

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

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

1. По-перше, створіть діапазон критеріїв. Введіть заголовок, такий самий, як і вихідний заголовок стовпця, а потім введіть критерії фільтра в клітинку нижче. Дивіться знімок екрана:

2. клацання дані > Додатково.

3. На відкритті Розширений фільтр діалогове вікно, налаштуйте наступним чином.

Потім усі комірки, що закінчуються маркетом, фільтруються. Дивіться знімок екрана:

Використання символів узагальнення * та ~ у фільтрації є таким самим, як і в описаних вище операціях.

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

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

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

1. Виберіть діапазон стовпців, для якого ви будете фільтрувати дані, натисніть дані > фільтр додати до нього фільтр.

2. Клацніть стрілку спадного меню, введіть “Ринок” у вікно пошуку, а потім натисніть ОК.

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

Потім усі комірки, що містять “Ринок”, фільтруються, як показано на наведеному нижче знімку екрана.

---

4. Копіювати лише видимі дані (ігнорувати приховані або відфільтровані дані)

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

Копіюйте видимі дані лише за допомогою клавіш швидкого доступу

Ви можете використовувати комбінації клавіш, щоб виділити лише видимі клітинки, а потім скопіювати та вставити їх у потрібне місце вручну.

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

2 Натисніть кнопку інший та ; клавіші одночасно. Тепер виділено лише видимі клітинки.

3 Натисніть кнопку Ctrl + C , щоб скопіювати вибрані комірки, а потім натисніть Ctrl + V клавіші, щоб вставити їх.

Легко копіюйте видимі дані лише за допомогою чудового інструменту

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

1. Виберіть відфільтрований діапазон, для якого потрібно скопіювати лише видимі комірки, а потім натисніть Кутулс > Діапазон > Паста to Видно > ВСІ / Тільки вставити значення.

Виберіть для клітин формули ВСІ копіює як результат, так і формули, вибирай Тільки вставити значення копіює лише фактичні значення.

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

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

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

---

5. Видаліть приховані або видимі рядки після фільтрування

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

Видаліть усі приховані рядки з поточного аркуша з кодом VBA

Наведений нижче код VBA може допомогти видалити всі приховані рядки з поточного аркуша в Excel.

Примітка: Цей VBA видаляє не тільки прихований рядок у відфільтрованому списку, але також видаляє рядки, які ви приховували вручну.

1. На аркуші містяться приховані рядки, які потрібно видалити, натисніть інший + F11 клавіші одночасно, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль а потім скопіюйте наведений нижче код VBA у вікно Модуль.

Код VBA: Видаліть усі приховані рядки з поточного аркуша

Sub RemoveHiddenRows()
    Dim xRow As Range
    Dim xRg As Range
    Dim xRows As Range
    On Error Resume Next
    Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)
    If xRows Is Nothing Then Exit Sub
        For Each xRow In xRows.Columns(1).Cells
            If xRow.EntireRow.Hidden Then
                If xRg Is Nothing Then
                    Set xRg = xRow
                Else
                    Set xRg = Union(xRg, xRow)
                End If
            End If
        Next
        If Not xRg Is Nothing Then
            MsgBox xRg.Count & " hidden rows have been deleted", , "Kutools for Excel"
            xRg.EntireRow.Delete
        Else
            MsgBox "No hidden rows found", , "Kutools for Excel"
        End If
End Sub

3 Натисніть кнопку F5 клавіша для запуску коду.

4. Тоді a Kutools для Excel спливає діалогове вікно, щоб повідомити, скільки прихованих рядків було видалено, натисніть OK закрити його.

Тепер усі приховані рядки (включаючи автоматично приховані рядки та приховані вручну рядки) видаляються.

Видаліть видимі рядки після фільтрування за допомогою функції «Перейти»

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

1. Виберіть відфільтрований діапазон, з якого потрібно видалити видимі рядки, натисніть F5 ключ, щоб відкрити Перейти до діалогове вікно.

2 В Перейти до у діалоговому вікні натисніть спеціальний кнопки.

3 В Перейти до спеціального діалоговому вікні, виберіть Тільки видимі клітини параметр, а потім натисніть кнопку OK кнопки.

4. Тепер виділено всі видимі комірки. Клацніть правою кнопкою миші на вибраному діапазоні та натисніть Видалити рядок в контекстному меню.

Потім усі видимі комірки видаляються.

Легко видаляйте приховані або видимі рядки після фільтрування за допомогою чудового інструменту

Вищевказані методи виснажливі і трудомісткі. Тут рекомендуємо Видалити приховані (видимі) рядки та стовпці особливість Kutools для Excel. За допомогою цієї функції ви можете легко видалити приховані або видимі рядки не лише у вибраному діапазоні, але і на поточному аркуші, декількох вибраних аркушах або в цілій книзі. Будь ласка, виконайте наступне.

1. Виберіть діапазон, який потрібно видалити з нього всі приховані або видимі рядки.

Примітки:

2. клацання Кутулс > видаляти > Видалити приховані (видимі) рядки та стовпці.

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

4. Потім усі видимі або приховані рядки видаляються відразу. Тим часом з’являється діалогове вікно, щоб повідомити вам кількість видалених рядків, натисніть OK щоб закінчити всю операцію.

---

6. Фільтруйте кілька аркушів

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

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

1 Натисніть кнопку інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль а потім скопіюйте наведений нижче код VBA у вікно модуля.

Код VBA: Фільтруйте дані одночасно на декількох робочих аркушах

Sub apply_autofilter_across_worksheets()
'Updateby Extendoffice 20210518
    Dim xWs As Worksheet
    On Error Resume Next
    For Each xWs In Worksheets
        xWs.Range("A1").AutoFilter 1, "=KTE"
    Next
End Sub

Примітка: Лінія "Xws.Range (“A1” .AutoFilter 1, “= KTE”)”У коді вказує, що ви будете фільтрувати дані у стовпці А з критеріями = КТЕ, а цифра 1 - це номер стовпця стовпця А. Ви можете змінити їх відповідно до своїх потреб. Наприклад, якщо ви хочете відфільтрувати всі цифри, що перевищують 500, у стовпці B, ви можете змінити цей рядок на “Xws.Range (“B1”. AutoFilter 2, “> 500”)".

3 Натисніть кнопку F5 клавіша для запуску коду.

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

---

7. Повторно застосувати фільтр після зміни даних

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

Повторно застосуйте фільтр вручну за допомогою команди Повторно застосувати

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

Натисніть дані > Подайте заявку для повторного застосування фільтра в поточному аркуші.

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

Автоматично повторно застосовувати фільтр із кодом VBA

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

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

2. На відкритті Microsoft Visual Basic для додатків вікно, скопіюйте наведений нижче код VBA у вікно коду.

Код VBA: автоматично застосовувати фільтр повторно при зміні даних

Private Sub Worksheet_Change(ByVal Target As Range)
   Sheets("Sheet2").AutoFilter.ApplyFilter
End Sub

Примітка: У коді, “Аркуш2” - назва поточного робочого аркуша. Ви можете змінити його відповідно до своїх потреб.

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

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

---

8. Очистіть або вийміть фільтр

Ми навчилися додавати, застосовувати та використовувати фільтр у вищевказаному вмісті. Тут ми збираємось дізнатись, як очистити або видалити фільтр в Excel.

8.1 Очищення фільтра зі стовпця

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

8.2 Очистіть усі фільтри на аркуші

Якщо ви застосували фільтр до кількох стовпців і хочете очистити всі одночасно, натисніть дані > Очистити.

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

8.3 Очистити фільтри з усіх аркушів поточної книги

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

1. Відкрийте книгу, в якій ви очистите всі фільтри, а потім натисніть інший + F11 клавіші одночасно.

2. На відкритті Microsoft Visual Basic для додатків вікна, натисніть Insert > Модуль а потім скопіюйте наведений нижче код VBA у вікно модуля.

Код VBA: очистити фільтри з усіх аркушів поточної книги

Sub Auto_Open()
'Updated by Extendoffice 20201113
    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
         Next
        Next
    Next
    Application.ScreenUpdating = True
End Sub

3 Натисніть кнопку F5 клавіша для запуску коду. Тоді всі фільтри видаляються з усіх аркушів поточної книги.

8.4 Видаліть усі фільтри з аркуша

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

Видаліть усі фільтри з аркуша, вимкнувши фільтр

Натисніть дані > фільтр щоб вимкнути функцію (кнопка «Фільтр» не перебуває у стані виділення).

Видаліть усі фільтри з аркуша за допомогою комбінації клавіш

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

На аркуші містяться фільтри, які потрібно видалити, натисніть Ctrl + Shift + L клавіші одночасно.

Потім усі фільтри на поточному аркуші негайно видаляються.