Не секрет, що більшість користувачів Excel, створюючи таблиці на аркушах, в першу чергу думають про власний комфорт і зручність. Так народжуються красиві, барвисті і громіздкі таблиці зі складними «заголовками», які, при цьому, взагалі неможливо відфільтрувати або відсортувати, а про автоматичний звіт зі зведеною таблицею краще взагалі не думати.
Рано чи пізно користувач такого столу приходить до висновку, що «може і не так красиво, але може працювати» і починає спрощувати дизайн свого столу, приводячи його у відповідність з класичними рекомендаціями:
- простий однорядковий заголовок, де кожен стовпець матиме власну унікальну назву (ім’я поля)
- один рядок – одна виконана операція (угода, продаж, розміщення, проект тощо)
- немає об'єднаних комірок
- без розривів у вигляді порожніх рядків і стовпців
Але якщо зробити однорядковий заголовок з багаторівневого або розбити один стовпець на кілька, це досить просто, то реконструкція таблиці може зайняти багато часу (особливо при великих розмірах). Це означає наступну ситуацію:
| Of |  | do |  |
З точки зору баз даних праву таблицю зазвичай називають плоскою (flat) – саме за такими таблицями найкраще будувати звіти зведених таблиць (pivot tables) і проводити аналітику.
Ви можете перетворити двовимірну таблицю на плоску за допомогою простого макросу. Відкрийте редактор Visual Basic за допомогою вкладки Розробник – Visual Basic (Розробник — редактор Visual Basic) або комбінацію клавіш інший+F11. Вставте новий модуль (Вставка – модуль) і скопіюйте туди текст цього макросу:
Sub Redesigner() Dim i As Long Dim hc As Integer, hr As Integer Dim ns As Worksheet hr = InputBox("Скілько рядків з підписами зверху?") hc = InputBox("Скілько столбців з підписами слева?") Application.ScreenUpdating = False i = 1 Set inpdata = Selection Set ns = Worksheets.Add For r = (hr + 1) To inpdata.Rows.Count For c = (hc + 1) To inpdata.Columns.Count For j = 1 To hc ns. Cells(i, j) = inpdata.Cells(r, j) Наступний j Для k = 1 До hr ns.Cells(i, j + k - 1) = inpdata.Cells(k, c) Наступний k ns.Cells( i, j + k - 1) = inpdata.Cells(r, c) i = i + 1 Наступний c Наступний r Кінець Sub Потім ви можете закрити редактор VBA та повернутися до Excel. Тепер ми можемо вибрати вихідну таблицю (повністю, із заголовком і першим стовпцем із місяцями) і запустити наш макрос Розробник – макроси (Розробник — Макроси) або натискання комбінації інший+F8.
Макрос вставить у книгу новий аркуш і створить на ньому нову реконструйовану версію вибраної таблиці. З такою таблицею можна працювати «на повну», використовуючи весь арсенал інструментів Excel для обробки та аналізу великих списків.
- Що таке макроси, куди вставляти макрокод у VBA, як ними користуватися
- Створення звітів за допомогою зведених таблиць
- Інструмент для переробки XNUMXD таблиць на плоскі з надбудови PLEX