Робота з таблицями Excel

Таблиця Excel — те, що VBA називає ListObject — це іменований, саморозширюваний діапазон із вбудованими стрілками фільтрації, чергуванням рядків і структурованими посиланнями на стовпці. Якщо ваші дані ще не є Таблицею, виберіть будь-яку клітинку всередині діапазону та натисніть Ctrl+T, або дозвольте VBA створити її за допомогою ListObjects.Add.

Посилання на ListObject

Dim ws As Worksheet
Dim tbl As ListObject
 
Set ws = ThisWorkbook.Worksheets("Reports")
Set tbl = ws.ListObjects("tblReports")
 
Debug.Print tbl.Range.Address        ' full table including header
Debug.Print tbl.DataBodyRange.Rows.Count   ' data rows only, no header

Оголошення tbl As ListObject (а не просто As Range) відкриває доступ до всіх специфічних для Таблиці можливостей, які використовуються у цій секції — ListRows, ListColumns і Total Row працюють саме тому, що об'єкт має правильний тип. Зверніть увагу на різницю між двома рядками Debug.Print: tbl.Range охоплює всю Таблицю разом із заголовком, тоді як tbl.DataBodyRange містить лише дані під ним.

Майже всі дії — додавання рядка, підсумовування стовпця, перебір записів — слід виконувати через DataBodyRange, саме тому, що ви не хочете випадково обробити текст заголовка як рядок даних.

Додавання рядків

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

Dim newRow As ListRow
Set newRow = tbl.ListRows.Add
 
newRow.Range(1, 1).Value = "April"
newRow.Range(1, 2).Value = "North"
newRow.Range(1, 3).Value = 45200
newRow.Range(1, 4).Value = 30750
newRow.Range(1, 5).Value = 14450
newRow.Range(1, 6).Value = 41000

tbl.ListRows.Add створює порожній рядок і повертає його як об'єкт ListRow, тому наступні шість рядків записують дані саме в newRow, а не в tbl. newRow.Range(1, 1) означає "рядок 1 цього конкретного нового рядка, стовпець 1" — індексація починається з 1 для нового рядка, а не з початку всієї таблиці. Це значно краща практика, ніж знаходити останній рядок аркуша через End(xlUp) і вручну записувати дані в наступний стовпець: ListRows.Add завжди додає рядок у межах Таблиці, тому будь-який підсумковий рядок, формула зі структурованим посиланням чи правило умовного форматування автоматично поширюється на нього.

Оновлення записів

Щоб оновити існуючий рядок, перебирайте DataBodyRange і шукайте збіг у ключовому стовпці — наприклад, оновлення цільового показника для Центрального регіону за лютий після перегляду бюджету:

Dim r As Long
For r = 1 To tbl.DataBodyRange.Rows.Count
    If tbl.DataBodyRange.Cells(r, 1).Value = "February" And _
       tbl.DataBodyRange.Cells(r, 2).Value = "Central" Then
        tbl.DataBodyRange.Cells(r, 6).Value = 52000   ' revised Target
        Exit For
    End If
Next r

Це той самий шаблон "зверху вниз, зупинитися при першому збігу", що й у умовній логіці, але застосований до реальних рядків замість жорстко заданих значень: цикл перевіряє Місяць і Регіон разом через And, і щойно обидва збігаються — оновлює стовпець Target і викликає Exit For, щоб не сканувати зайві рядки. Використання tbl.DataBodyRange.Cells(r, 1) замість посилання на Cells на рівні аркуша обмежує нумерацію рядків лише даними Таблиці — тут рядок 1 означає перший рядок даних, незалежно від того, з якого фізичного рядка аркуша починається Таблиця.

Посилання на стовпці таблиці

Структуровані посилання — ListColumns("Name") — більш читабельні та стійкі до змін, ніж підрахунок стовпців за номером, особливо коли таблицю редагують і стовпці переміщуються:

Dim profitCol As Range
Set profitCol = tbl.ListColumns("Profit").DataBodyRange
 
Debug.Print Application.WorksheetFunction.Sum(profitCol)
Debug.Print Application.WorksheetFunction.Average(profitCol)

ListColumns("Profit") знаходить стовпець за текстом заголовка, а не за позицією, тому код продовжить працювати навіть якщо Profit згодом переміститься з колонки E у колонку F — ручний підрахунок Cells(r, 5) у такому випадку непомітно зламається. Application.WorksheetFunction — це міст, який дозволяє VBA викликати звичайні функції Excel, такі як SUM і AVERAGE, безпосередньо для об'єкта Range, замість написання ручного циклу з накопиченням підсумку, що і скорочує код, і зменшує ймовірність помилки на одну позицію.

Завдання

1. Відкрийте Section_4_Reports.xlsx, збережіть як Section_4_Reports.xlsm і переконайтеся, що дані на аркуші Reports є Таблицею з іменем tblReports (натисніть будь-яку клітинку всередині — має з’явитися вкладка Table Design).
2. Напишіть макрос, який додає рядок за квітень для кожного з п’яти регіонів за допомогою ListRows.Add (усього п’ять нових рядків, цифри можна вигадати).
3. Напишіть другий макрос, використовуючи ListColumns("Sales").DataBodyRange і WorksheetFunction.Sum, щоб вивести загальну суму продажів по всіх рядках у Вікно негайного виконання.