Свайпніть щоб показати меню

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

Базовий AutoFilter

tbl.Range.AutoFilter Field:=1, Criteria1:="January"
' Field:=1 means the 1st column of the table (Month)

AutoFilter не видаляє і не переміщує жодних даних — він приховує рядки, які не відповідають критерію, так само, якби ви вручну натиснули стрілку випадаючого списку та зняли всі прапорці, крім January. Field:=1 рахує стовпці, починаючи з 1 у самій таблиці (Month, Region, Sales, Expenses, Profit, Target — тобто Region буде Field:=2, Profit — Field:=5), тому цей параметр потрібно оновлювати, якщо порядок стовпців змінюється.

Фільтри з кількома умовами

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

tbl.Range.AutoFilter Field:=2, _
    Criteria1:=Array("North", "Central"), _
    Operator:=xlFilterValues

Порівняйте це з фільтром для одного значення вище: Criteria1 тепер містить Array(...) допустимих значень замість одного рядка, а Operator:=xlFilterValues вказує AutoFilter розглядати цей масив як список відповідностей, а не як єдиний вираз критерію. Якщо пропустити Operator:=xlFilterValues, цей рядок або викликає помилку, або поводиться непередбачувано — це легко забути, тому завжди перевіряйте, коли Criteria1 є списком.

Фільтрація за числовою умовою — наприклад, лише рядки, де Profit перевищує Target на значну суму — використовує оператори порівняння:

tbl.Range.AutoFilter Field:=5, Criteria1:=">15000"

Зверніть увагу, що ">15000" записано як текст у лапках, хоча це числове порівняння — AutoFilter завжди очікує Criteria1 як рядок і самостійно розбирає символ >. Якщо написати Criteria1:=15000 без >, буде відфільтровано лише рядки, рівні 15000, а не більші за це значення, що є поширеною помилкою.

Сортування

Об'єкт Sort підтримує кілька ключів, аналогічно діалоговому вікну Data → Sort:

With tbl.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=tbl.ListColumns("Month").Range, _
        SortOn:=xlSortOnValues, Order:=xlAscending
    .SortFields.Add2 Key:=tbl.ListColumns("Profit").Range, _
        SortOn:=xlSortOnValues, Order:=xlDescending
    .Header = xlYes
    .Apply
End With

.SortFields.Clear виконується першим, щоб залишкові ключі сортування з попереднього запуску макросу (або ручного сортування користувачем) не поєднувалися з новими — завжди очищайте перед додаванням. Порядок, у якому викликаються .Add2, такий же важливий, як і параметри Order:=xlAscending/xlDescending: перший доданий стає основним ключем сортування (Month), другий — додатковим для розв'язання однакових значень (Profit, спочатку найбільший у кожному місяці). .Header = xlYes вказує Excel, що перший рядок — це заголовок, і його не можна переміщати під час сортування; .Apply фактично запускає сортування — усе до цього лише формує інструкції.

Очищення фільтрів

Завжди очищайте фільтри на початку макросу звіту, щоб кожен запуск починався з відомого, нефільтрованого стану:

If tbl.AutoFilter.FilterMode Then
    tbl.AutoFilter.ShowAllData
End If

FilterMode — це логічне значення True, коли будь-який фільтр звужує видимі рядки таблиці — перевірка цього запобігає помилці виконання, оскільки виклик ShowAllData, коли нічого не відфільтровано, викликає помилку замість того, щоб просто нічого не робити.

Завдання

Написати макрос, який фільтрує tblReports лише за лютий, використовуючи AutoFilter Field:=1.
Розширити його для фільтрації Регіону (Field:=2) лише на "East" та "West" одночасно, використовуючи xlFilterValues.
Очистити обидва фільтри, потім відсортувати таблицю спочатку за Регіоном за зростанням, потім за Прибутком за спаданням, використовуючи об'єкт Sort, показаний вище.

Підказка

1. Фільтрація за лютий

Field:=1 відноситься до першого стовпця Таблиці, а не аркуша — Month є першим стовпцем у tblReports, незалежно від того, в якому стовпці аркуша він фізично знаходиться.
Criteria1 приймає точний текст, за яким ви фільтруєте, у лапках.
Ви викликаєте AutoFilter для tbl.Range, а не безпосередньо для аркуша.

2. Додавання фільтра за Регіоном

Region — це другий стовпець таблиці, тому це інший номер Field:=, ніж для Month.
Для фільтрації за двома значеннями в одному стовпці потрібно використовувати Criteria1:=Array(...) з обома значеннями всередині, а також Operator:=xlFilterValues — найпоширеніша помилка тут — пропустити цей оператор.
Обидва фільтри (Month і Region) можуть бути активними одночасно — просто викликайте AutoFilter двічі, по одному разу для кожного стовпця.

3. Очищення фільтрів і сортування

Перевіряйте tbl.AutoFilter.FilterMode перед викликом ShowAllData — якщо викликати його, коли нічого не відфільтровано, виникне помилка.
Для об'єкта Sort спочатку потрібно .SortFields.Clear, потім по одному .SortFields.Add2 для кожного рівня сортування — порядок додавання визначає, що є основним ключем, а що — додатковим, а не порядок у таблиці.
Region за зростанням потрібно додати перед Profit за спаданням, оскільки Region має бути основним критерієм сортування.

Розв'язок

Option Explicit

Sub FilterToFebruary()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")

    tbl.Range.AutoFilter Field:=1, Criteria1:="February"
End Sub

Sub FilterFebruaryEastWest()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")

    tbl.Range.AutoFilter Field:=1, Criteria1:="February"
    tbl.Range.AutoFilter Field:=2, _
        Criteria1:=Array("East", "West"), _
        Operator:=xlFilterValues
End Sub

Sub ClearFiltersAndSort()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Reports").ListObjects("tblReports")

    ' Clear any active filters
    If tbl.AutoFilter.FilterMode Then
        tbl.AutoFilter.ShowAllData
    End If

    ' Sort by Region ascending, then Profit descending
    With tbl.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=tbl.ListColumns("Region").Range, _
            SortOn:=xlSortOnValues, Order:=xlAscending
        .SortFields.Add2 Key:=tbl.ListColumns("Profit").Range, _
            SortOn:=xlSortOnValues, Order:=xlDescending
        .Header = xlYes
        .Apply
    End With
End Sub

Запустіть FilterFebruaryEastWest, і ви побачите лише рядки за лютий для регіонів East і West. Потім запустіть ClearFiltersAndSort — усі рядки знову з'являться, відсортовані спочатку за Region (в алфавітному порядку), а всередині кожного Region — спочатку з найбільшим Profit.

Все було зрозуміло?

Дякуємо за ваш відгук!

Секція 4. Розділ 2

Запитати АІ

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат

Сортування та фільтрація даних