Tabele przestawne (pivottables) są potężnym narzędziem do analizy danych. Czasami jednak trzeba cofnąć przestawienie (unpivot) przestawionych danych (inaczej zwane „prostowaniem” lub cofnięciem obrotu danych), aby umieścić je 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.
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ą (tabelę krzyżową) z danymi na zwykły (płaski) format tabeli, co nam ponownie pozwoli utwórz tabele pozmukjujące ze zmienionymi lub uzupełnionymi danymi.
Otwórz edytor Visual Basic (Usługa - Makro - Edytor Visual Basic), zainstaluj 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”, wykorzystując cały arsenał narzędzi Excel do przetwarzania i analizy dużych tabel.