Багато функцій Excel також доступні для використання у VBA - і Автофільтр метод є однією з таких функцій.
Якщо у вас є набір даних і ви хочете відфільтрувати його за критерієм, ви можете легко це зробити, скориставшись опцією Фільтр на стрічці даних.
І якщо ви хочете отримати більш просунуту версію, у Excel також є розширений фільтр.
Тоді навіщо навіть використовувати автофільтр у VBA?
Якщо вам просто потрібно відфільтрувати дані та виконати деякі основні дії, я б рекомендував дотримуватися вбудованих функцій фільтра, які пропонує інтерфейс Excel.
Вам слід використовувати автоматичний фільтр VBA, якщо ви хочете відфільтрувати дані як частину вашої автоматизації (або якщо це допоможе вам заощадити час, прискоривши фільтрацію даних).
Наприклад, припустимо, що ви хочете швидко відфільтрувати дані на основі випадаючого списку, а потім скопіювати ці відфільтровані дані на новий аркуш.
Хоча це можна зробити за допомогою вбудованої функції фільтра разом з деякою копіюванням-вставленням, це може зайняти багато часу, щоб зробити це вручну.
У такому випадку використання автофільтра VBA може прискорити роботу та заощадити час.
Примітка: Я розгляну цей приклад (про фільтрацію даних на основі випадаючого вибору та копіювання на новий аркуш) далі в цьому підручнику.
Синтаксис автофільтра Excel VBA
Вираз. Автофільтр (_Field_, _Criteria1_, _Operator_, _Criteria2_, _VisibleDropDown_)
- Вираз: Це діапазон, до якого потрібно застосувати автоматичний фільтр.
- Поле: [Необов’язковий аргумент] Це номер стовпця, який потрібно відфільтрувати. Це відлічується зліва в наборі даних. Отже, якщо ви хочете фільтрувати дані на основі другого стовпця, це значення буде 2.
- Критерії1: [Необов’язковий аргумент] Це критерії, на основі яких потрібно фільтрувати набір даних.
- Оператор: [Необов’язковий аргумент] Якщо ви також використовуєте критерій 2, ви можете поєднати ці два критерії на основі оператора. Доступні наступні оператори: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Критерії 2: [Необов’язковий аргумент] Це другий критерій, за яким можна фільтрувати набір даних.
- VisibleDropDown: [Необов’язковий аргумент] Ви можете вказати, чи потрібно, щоб розкривний значок фільтра відображався у відфільтрованих стовпцях чи ні. Цей аргумент може бути ІСТИННИМ або ХІБНИМ.
Крім Expression, усі інші аргументи є необов’язковими.
Якщо ви не використовуєте жодного аргументу, він просто застосує або видалить піктограми фільтра до стовпців.
Додаткові робочі аркуші FilterRows () ("Фільтрувати дані"). Діапазон ("A1"). Кінцева піддона автофільтра
Наведений вище код просто застосує метод автофільтрації до стовпців (або якщо він уже застосований, він видалить його).
Це просто означає, що якщо ви не бачите піктограми фільтрів у заголовках стовпців, ви побачите це, коли цей код вище буде виконано, і якщо ви побачите його, то його буде видалено.
Якщо у вас є відфільтровані дані, вони видалять фільтри та покажуть вам повний набір даних.
Тепер давайте розглянемо деякі приклади використання автофільтра Excel VBA, які зроблять його використання зрозумілим.
Приклад: Фільтрування даних на основі умови тексту
Припустимо, у вас є набір даних, як показано нижче, і ви хочете відфільтрувати його на основі стовпця "Елемент".
У наведеному нижче коді буде відфільтровано всі рядки, де елементом є "Принтер".
Робочі аркуші підфільтра ("Аркуш1"). Діапазон ("А1"). Поле автофільтра: = 2, Критерії1: = "Принтер"
Наведений вище код посилається на Sheet1, а всередині нього - на A1 (комір у наборі даних).
Зауважте, що тут ми використовували поле: = 2, оскільки стовпець елемента є другим стовпцем у нашому наборі даних зліва.
Тепер, якщо ви думаєте - навіщо мені це робити за допомогою коду VBA. Це легко можна зробити за допомогою вбудованих функцій фільтра.
Ти правий!
Якщо це все, що ви хочете зробити, краще скористайтеся вбудованою функцією фільтра.
Але, прочитавши решту підручника, ви побачите, що це можна поєднати з деяким додатковим кодом для створення потужної автоматизації.
Але перш ніж я покажу вам це, дозвольте мені спершу навести кілька прикладів, щоб показати вам, що може зробити весь метод автофільтрації.
Натисніть тут завантажити приклад файлу та продовжити.
Приклад: Кілька критеріїв (І/АБО) в одній і тій же стовпці
Припустимо, у мене той самий набір даних, і цього разу я хочу відфільтрувати всі записи, де елементом є "Принтер" або "Проектор".
Нижче наведений код зробить це:
Додаткові робочі аркуші FilterRowsOR () ("Аркуш1"). Діапазон ("A1"). Поле автофільтра: = 2, Критерії1: = "Принтер", Оператор: = xlOr, Критерії2: = "Проектор" Кінцева підмітка
Зауважте, що тут я використав xlOR оператор.
Це повідомляє VBA використовувати обидва критерії та фільтрувати дані, якщо будь -який із двох критеріїв відповідає.
Аналогічно, ви також можете використовувати критерії І.
Наприклад, якщо ви хочете відфільтрувати всі записи, де кількість більше 10, але менше 20, ви можете скористатися наведеним нижче кодом:
Додаткові фільтриRowsAND () Робочі аркуші ("Аркуш1"). Діапазон ("A1"). Поле автофільтра: = 4, Критерії1: = "> 10", _ Оператор: = xlAnd, Критерії2: = "<20" Закінчити суб
Приклад: Кілька критеріїв з різними стовпцями
Припустимо, у вас є наступний набір даних.
За допомогою автофільтра можна фільтрувати кілька стовпців одночасно.
Наприклад, якщо ви хочете відфільтрувати всі записи, де позицією є "Принтер", а представником відділу продажів - "Позначити", ви можете скористатися наведеним нижче кодом:
Sub FilterRows () з аркушами ("Sheet1"). Діапазон ("A1"). Поле AutoFilter: = 2, Criteria1: = "Принтер".
Приклад: Фільтруйте 10 найкращих записів за допомогою методу автофільтрації
Припустимо, у вас є набір даних нижче.
Нижче наведено код, який дасть вам 10 найкращих записів (на основі стовпця кількості):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Поле автофільтра: = 4, Criteria1: = "10", Оператор: = xlTop10Items End Sub
У наведеному вище коді я використовував ActiveSheet. Ви можете використовувати назву аркуша, якщо хочете.
Зауважте, що в цьому прикладі, якщо ви хочете отримати 5 найкращих елементів, просто змініть номер Критерії1: = ”10 ″ від 10 до 5.
Отже, для 5 найкращих елементів код буде таким:
Sub FilterRowsTop5 () ActiveSheet.Range ("A1"). Поле автофільтра: = 4, Criteria1: = "5", Оператор: = xlTop10Items End Sub
Це може виглядати дивно, але незалежно від того, скільки найкращих елементів ви хочете, значення Operator завжди залишається xlTop10Items.
Аналогічно, наведений нижче код дасть вам 10 найнижчих елементів:
Sub FilterRowsBottom10 () ActiveSheet.Range ("A1"). Поле автофільтра: = 4, Criteria1: = "10", Оператор: = xlBottom10Items End Sub
І якщо ви хочете 5 найнижчих елементів, змініть номер Критерії1: = ”10 ″ від 10 до 5.
Приклад: Відфільтруйте 10 кращих відсотків за допомогою методу автофільтрації
Припустимо, у вас той самий набір даних (як у попередніх прикладах).
Нижче наведено код, який дасть вам 10 найкращих записів (на основі стовпця кількості):
Sub FilterRowsTop10 () ActiveSheet.Range ("A1"). Поле автофільтра: = 4, Criteria1: = "10", Оператор: = xlTop10Percent End Sub
У нашому наборі даних, оскільки у нас є 20 записів, він поверне 2 найкращі записи (що становить 10% від загальної кількості записів).
Приклад: використання символів підстановки в автофільтрі
Припустимо, у вас є набір даних, як показано нижче:
Якщо ви хочете відфільтрувати всі рядки, де назва елемента містить слово "Дошка", ви можете скористатися наведеним нижче кодом:
Додаткові фільтриRowsWildcard () Робочі аркуші ("Аркуш1"). Діапазон ("A1"). Поле автофільтра: = 2, Критерії1: = "*Дошка*" Закінчити підмінець
У наведеному вище коді я використовував символ підстановки * (зірочка) перед та після слова «Дошка» (що є критерієм).
Зірочка може представляти будь -яку кількість символів. Таким чином, це буде фільтрувати будь -який елемент, у якому є слово "дошка".
Приклад: Скопіюйте відфільтровані рядки на новий аркуш
Якщо ви хочете не лише фільтрувати записи на основі критеріїв, а й копіювати відфільтровані рядки, ви можете скористатися наведеним нижче макросом.
Він копіює відфільтровані рядки, додає новий аркуш, а потім вставляє ці скопійовані рядки в новий аркуш.
Sub CopyFilteredRows () Змінити rng як діапазон Dim ws як робочий аркуш, якщо аркуші ("Sheet1"). AutoFilterMode = False Тоді MsgBox "Немає відфільтрованих рядків" Exit Sub End If Set rng = Worksheets ("Sheet1"). AutoFilter.Range Set ws = Робочі листи.Додайте rng.Copy Range ("A1") End Sub
Наведений вище код перевірятиме, чи є у Sheet1 фільтровані рядки чи ні.
Якщо немає відфільтрованих рядків, він покаже вікно повідомлення про це.
І якщо є відфільтровані рядки, він їх копіює, вставляє новий аркуш і вставляє ці рядки на цей щойно вставлений аркуш.
Приклад: Фільтрування даних на основі значення комірки
Використовуючи Автофільтр у VBA разом із розкривним списком, ви можете створити функціонал, де, як тільки ви виберете елемент із розкривного списку, усі записи для цього елемента будуть відфільтровані.
Щось, як показано нижче:
Натисніть тут завантажити приклад файлу та продовжити.
Цей тип конструкції може бути корисним, коли ви хочете швидко відфільтрувати дані, а потім використовувати їх надалі у своїй роботі.
Нижче наведено код, який буде це робити:
Приватний робочий аркуш_Зміна (ByVal Target As Range) If Target.Address = "$ B $ 2" Тоді, якщо Range ("B2") = "All" Тоді Діапазон ("A5"). Автофільтр Інший діапазон ("A5"). Поле автофільтра : = 2, Критерії1: = Діапазон ("B2") End If End Якщо End End Sub
Це код події робочого аркуша, який виконується лише тоді, коли на робочому аркуші є зміна, а цільовою осередком є В2 (де у нас є спадне меню).
Крім того, умова "Якщо тоді ще" використовується для перевірки, чи користувач вибрав "Усі" зі спадного меню. Якщо вибрано Усі, відображається весь набір даних.
Цей код НЕ розміщений у модулі.
Натомість його потрібно помістити в бекенд аркуша, що містить ці дані.
Ось кроки для розміщення цього коду у вікні коду робочого аркуша:
- Відкрийте редактор VB (комбінація клавіш - ALT + F11).
- На панелі "Провідник проектів" двічі клацніть на назві аркуша, у якому ви хочете використовувати цю функцію фільтрації.
- У вікні коду робочого аркуша скопіюйте та вставте наведений вище код.
- Закрийте редактор VB.
Тепер, коли ви використовуєте випадаючий список, він автоматично фільтруватиме дані.
Це код події робочого аркуша, який виконується лише тоді, коли на робочому аркуші є зміна, а цільовою осередком є В2 (де у нас є спадне меню).
Крім того, умова "Якщо тоді ще" використовується для перевірки, чи користувач вибрав "Усі" зі спадного меню. Якщо вибрано Усі, відображається весь набір даних.
Увімкніть/вимкніть автофільтр Excel за допомогою VBA
При застосуванні автофільтра до діапазону комірок, можливо, вже є деякі фільтри.
Ви можете використати код нижче, щоб вимкнути всі попередньо застосовані автоматичні фільтри:
Sub TurnOFFAutoFilter () Робочі аркуші ("Sheet1"). AutoFilterMode = Subsex
Цей код перевіряє всі аркуші та видаляє всі застосовані фільтри.
Якщо ви не хочете вимикати фільтри з усього аркуша, а лише з певного набору даних, скористайтеся наведеним нижче кодом:
Sub TurnOFFAutoFilter () Якщо робочі аркуші ("Sheet1"). Діапазон ("A1"). AutoFilter Потім робочі аркуші ("Sheet1"). Діапазон ("A1"). Автофільтр закінчується, якщо End Sub
Наведений вище код перевіряє, чи є фільтри на місці чи ні.
Якщо фільтри вже застосовані, він видаляє їх, інакше він нічого не робить.
Аналогічно, якщо ви хочете увімкнути автофільтр, скористайтеся наведеним нижче кодом:
Sub TurnOnAutoFilter () Якщо не робочі аркуші ("Sheet1"). Діапазон ("A4"). AutoFilter Потім робочі аркуші ("Sheet1"). Діапазон ("A4"). AutoFilter End Якщо закінчується Sub
Перевірте, чи автоматичний фільтр уже застосовано
Якщо у вас є аркуш з кількома наборами даних, і ви хочете переконатися, що знаєте, що фільтри вже не встановлені, ви можете скористатися наведеним нижче кодом.
Sub CheckforFilters () If ActiveSheet.AutoFilterMode = True Тоді MsgBox "На місці вже є фільтри" Інше MsgBox "Немає фільтрів" End If End Sub
У цьому коді використовується функція вікна повідомлень, яка відображає повідомлення "Уже є фільтри", коли він знаходить фільтри на аркуші, інакше показує "Немає фільтрів".
Показати всі дані
Якщо до набору даних застосовано фільтри, і ви хочете показати всі дані, скористайтеся наведеним нижче кодом:
Sub ShowAllData () Якщо ActiveSheet.FilterMode То ActiveSheet.ShowAllData Кінець Sub
Наведений вище код перевіряє, чи FilterMode має значення TRUE або FALSE.
Якщо це правда, це означає, що застосовано фільтр, і він використовує метод ShowAllData для відображення всіх даних.
Зауважте, що це не видаляє фільтри. Значки фільтрів все ще доступні для використання.
Використання автофільтра на захищених аркушах
За умовчанням, коли ви захищаєте аркуш, фільтри не працюватимуть.
Якщо у вас вже є фільтри, ви можете ввімкнути функцію автофільтра, щоб переконатися, що вона працює навіть на захищених аркушах.
Для цього встановіть прапорець Використовувати автофільтр, захищаючи аркуш.
Хоча це працює, коли у вас вже є фільтри, якщо ви спробуєте додати автофільтри за допомогою коду VBA, це не спрацює.
Оскільки аркуш захищений, він не дозволить виконувати жоден макрос та вносити зміни до автофільтра.
Тому вам потрібно використовувати код для захисту робочого аркуша та переконатися, що в ньому ввімкнено автоматичні фільтри.
Це може бути корисним, коли ви створили динамічний фільтр (те, що я розглянув у прикладі - "Фільтрувати дані на основі значення клітинки").
Нижче наведено код, який захищатиме аркуш, але водночас дозволить використовувати у ньому фільтри, а також макроси VBA.
Private Sub Workbook_Open () з робочими листами ("Sheet1") .EnableAutoFilter = True .Protect Password: = "password", Зміст: = True, UserInterfaceOnly: = True Кінець з End Sub
Цей код потрібно розмістити у вікні коду ThisWorkbook.
Ось кроки для введення коду у вікно коду ThisWorkbook:
- Відкрийте редактор VB (комбінація клавіш - ALT + F11).
- На панелі «Провідник проектів» двічі клацніть об’єкт ThisWorkbook.
- У вікні коду, що відкриється, скопіюйте та вставте наведений вище код.
Як тільки ви відкриєте книгу та увімкнете макроси, вона автоматично запустить макрос та захистить Sheet1.
Однак перед цим він вкаже "EnableAutoFilter = True", що означає, що фільтри працюватимуть і на захищеному аркуші.
Крім того, він встановлює для аргументу "UserInterfaceOnly" значення "Правда". Це означає, що поки робочий аркуш захищений, код макросів VBA продовжує працювати.
Вам також можуть сподобатися такі підручники з VBA:
- Цикли VBA Excel.
- Фільтрування клітинок із жирним форматуванням шрифтів.
- Запис макросу.
- Сортувати дані за допомогою VBA.
- Сортувати вкладки робочого аркуша в Excel.
