Перебудова таблиць (Unpivot)

Опис

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

Ви можете виконати таке перетворення в Excel за допомогою інструменту Power Query, але це не є швидко та не дуже зручно. Давайте спробуємо інший підхід для розгортання зведених даних без Power Query.

Постановка задачі

Уявіть, що нам потрібно перетворити деякий набір даних із перехресної таблиці в плоску (Unpivot):

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

VBA код для процедури UnPivotTable

Відкрийте редактор Visual Basic (Сервіс - Макрос - Редактор Visual Basic), вставте новий модуль (Insert - Module) і скопіюйте туди текст цього макросу:

Sub UnPivotTable()
  'moonexcel.com.ua
  Dim InVal    As Variant
  Dim OutVal() As Variant
  Dim j, k, i  As Long
  Dim NewSheet
  
  i = 1
  InVal = Selection.Formula
  ReDim OutVal(1 To Selection.Count, 1 To 3)
  
  For j = 2 To UBound(InVal, 1)
    For k = 2 To UBound(InVal, 2)
      If InVal(j, k) <> "" Then
        OutVal(i, 1) = InVal(j, 1)
        OutVal(i, 2) = InVal(1, k)
        OutVal(i, 3) = InVal(j, k)
        i = i + 1
      End If
    Next k
  Next j
  
  Set NewSheet = Worksheets.Add
  NewSheet.Range("A1").Resize(UBound(OutVal, 1), 3).Value = OutVal
End Sub 

Після цього можна закрити редактор VBA, повернутися в Excel, виділити вихідну таблицю (повністю, з шапкою і першим стовпцем з країнами) і запустити наш макрос через меню Сервіс - Макрос - Макроси (Tools - Macro - Macros) (ALT+F8).

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

Пов'язані статті:

• Перебудова таблиць (Unpivot) в LibreOffice Calc
• Створення звітів за допомогою Зведених таблиць