Elementy nieobrotowe (Unpivot)

Opis

Tabele podsumowujące (pivot tables) są potężnym narzędziem w analizie danych. Czasami jednak trzeba wdrożyć (unpivot) zagregowane dane (inaczej zwane „prostowaniem” lub rozwijaniem danych) w celu umieszczenia ich w zwykłym formacie tabeli, tak aby wszystkie podobne wartości znajdowały się w tej samej kolumnie. Jest to konieczne np. w celu utworzenia nowego lub zmodyfikowanego wykresu lub raportu.

Możesz wykonać tę konwersję w Excel za pomocą narzędzia Power Query, ale nie jest to szybkie i niezbyt wygodne. Wypróbujmy inne podejście do wdrażania danych podsumowujących bez Power Query.

Sformułowanie problemu

Wyobraź sobie, że musimy przekonwertować jakiś zbiór danych z tabeli przestawnej na tabelę płaską (Unpivot):

Takie zadanie lepiej rozwiązać za pomocą makra. Napiszmy własną procedurę, która będzie w stanie przekonwertować tabelę krzyżową (tablicę krzyżową) z danymi na zwykły (płaski) format tabeli, co nam ponownie pozwoli tworzyć tabele podsumowujące ze zmienionymi lub uzupełnionymi danymi.

Kod VBA dla procedury UnPivotTable

Otwórz edytor Visual Basic ( Usługa - Makro - Edytor Visual Basic ), włóż nowy moduł (Insert - Module) i skopiuj tam tekst tego makra:

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 

Następnie możesz zamknąć edytor VBA, wrócić do Excel, wybrać tabelę źródłową (w całości, z nagłówkiem i pierwszą kolumną z krajami) i uruchomić nasze makro poprzez menu Usługa - Makro - Makra (Tools - Macro - Macros) (ALT+F8) .

Makro wstawi nowy arkusz do skoroszytu i utworzy na nim nową, zrekonstruowaną wersję wybranej tabeli. Z taką tabelą można pracować „z pełnym programem”, korzystając z całego arsenału narzędzi Excel do przetwarzania i analizy dużych tabel.

Powiązane artykuły:

• Reorganizacja tabel (Unpivot) w LibreOffice Calc
• Tworzenie raportów przy użyciu tabel przestawnych