Często spotykam się z sytuacją, gdy dane, które otrzymuję, są w układzie nieprzystosowanym do utworzenia  tabeli przestawnej. Mamy możliwość zmiany takiego układu ręcznie, jednak jest to bardzo czasochłonne. Warto zatem poznać makro, które umożliwia zmianę układu danych w taki sposób, aby mogły zostać użyte do stworzenia tabeli przestawnej. Takie makro będzie mogło zostać wykorzystane w każdym przypadku, który będzie wymagał zmiany układu danych.

Co to jest makro?

Makro jest to kod programu w języku VBA (Visual Basic for Application), który został nagrany lub napisany, aby automatyzować pewne działania. Makra można tworzyć we wszystkich programach pakietu Office – m.in. Excel, Word, PowerPoint. Temat makr pojawia się przy okazji tego artykułu po raz pierwszy na blogu, jednak jest on nieodłącznym elementem procesu raportowania w Excelu, zwłaszcza, gdy myślimy o automatyzacji pewnych działań. Korzystanie z makr wiąże się z obniżeniem poziomu bezpieczeństwa w opcjach programu, dlatego należy korzystać tylko z makr z zaufanego źródła.

Tabela przestawna na danych o błędnej strukturze

Dane testowe składają się z 10 kolumn: pierwsze dwie dotyczą kanału sprzedaży oraz produktu, natomiast pozostałe to nazwy miesięcy (od stycznia do sierpnia).

Oczywiście możemy z powyższych danych utworzyć tabelę przestawną. Robimy to poprzez zaznaczenie danych oraz wybranie Tabeli przestawnej z paska Wstawianie. Jednak zamiast otrzymać pole Data otrzymamy wiele pól dla każdego miesiąca oddzielnie.

Tabelę wstawmy w tym samym arkuszu po to, aby widzieć dokładnie jaki wpływ ma układ danych źródłowych na efektywność tabeli przestawnej.

Poszczególne kolumny stały się polami w tabeli, a my potrzebujemy jednego pola o nazwie Data.

Użycie makra Depivot Data (dpd)

Aby korzystanie z danych było najwygodniejsze, powinny one mieć układ bazodanowy, co w przypadku dat oznacza, że powinny być w kolejnych wierszach. Oczywiście można przepisać dane, a co zrobić w przypadku, gdy rekordów będą setki, tysiące? Najlepszym rozwiązaniem będzie zastosowanie poniższego makra, które przekonwertuje zaznaczone dane.

Aby skorzystać z makra należy pobrać poniższy plik, wkleić tam dane a następnie postępować zgodnie z poniższą instrukcją:

1. Zaznaczamy dane, które chcemy przekonwertować. Następnie klikamy Alt+F8, wybieramy makro dpd i klikamy Uruchom.

2. W oknie dialogowym wpisujemy liczbę kolumn początkowych, które nie zawierają danych liczbowych. W naszym przypadku umieszczamy tam liczbę 2, gdyż mamy 2 kolumny – Kanał sprzedaży i Produkt. Dane z tych kolumn będą powtarzane dla każdej daty.

3. Dane przekonwertują się do układu wymaganego przez tabelę przestawną:

Na tak przygotowanych danych możemy bez przeszkód wstawić tabelę przestawną.

Plik do pobrania

W załączeniu plik, w którym można szczegółowo zapoznać się z treścią makra Depivot Data oraz z niego skorzystać.