Obejrzyj wideo - najszybszy sposób na unpivot danych w programie Excel
Tabele przestawne są świetne, gdy chcesz przeanalizować ogromne ilości danych w kilka sekund. Umożliwia także szybkie tworzenie różnych widoków danych poprzez proste przeciąganie i upuszczanie.
Aby utworzyć tabelę przestawną, musisz mieć dane w określonym, gotowym formacie tabeli przestawnej.
W wielu przypadkach prawdopodobnie otrzymasz dane w formatach, które nie są gotowe do użycia w tabeli przestawnej.
Dzieje się tak często, gdy ktoś ręcznie zbiera dane i tworzy format, który jest bardziej czytelny dla ludzi (nie tabele przestawne).
Coś, jak pokazano poniżej:
Powyższy format danych to coś, czego oczekujesz jako wynik analizy tabeli przestawnej.
A co, jeśli chcesz przeanalizować te same dane i zobaczyć, jaka była łączna sprzedaż w każdym regionie lub w każdym miesiącu.
Chociaż można to łatwo zrobić za pomocą tabel przestawnych, niestety nie można wprowadzić powyższych danych do tabeli przestawnej.
Musisz więc wycofać dane i dostosować je do tabeli przestawnej.
Chociaż można to zrobić na kilka sposobów za pomocą formuły programu Excel lub języka VBA, Power Query (pobieranie i przekształcanie w programie Excel 2016) jest najlepszym narzędziem do unpivot danych.
Unpivot dane za pomocą Power Query
Oto kroki, aby przestawić dane za pomocą dodatku Power Query:
(Jeśli Twoje dane znajdują się już w tabeli programu Excel, zacznij od kroku 6 i dalej)
- Wybierz dowolną komórkę w zestawie danych.
- Przejdź do zakładki Wstaw.
- Kliknij ikonę Stół.
- W oknie dialogowym „Utwórz tabelę” upewnij się, że zakres jest poprawny. W razie potrzeby możesz zmodyfikować zakres.
- Kliknij OK. Spowoduje to przekształcenie danych tabelarycznych w tabelę programu Excel.
- Po zaznaczeniu dowolnej komórki w tabeli programu Excel kliknij kartę Dane.
- W grupie Pobierz i przekształć dane kliknij ikonę „Z tabeli/zakresu”.
- W otwartym oknie dialogowym Utwórz tabelę (jeśli się otworzy), kliknij OK. Spowoduje to otwarcie Edytora zapytań przy użyciu danych z tabeli programu Excel.
- W edytorze zapytań kliknij prawym przyciskiem myszy kolumnę Region.
- Kliknij opcję „Unpivot Inne kolumny”. Spowoduje to natychmiastowe wycofanie Twoich danych.
- Zmień nazwę kolumny „Atrybut” na bardziej opisową, na przykład „Miesiące”.
- Gdy masz już dane Unpivoted, dobrą praktyką jest upewnienie się, że wszystkie typy danych są poprawne. W tym przykładzie kliknij jedną komórkę dla każdej kolumny i zobacz typ danych na karcie Przekształć. W razie potrzeby możesz również zmienić typ danych.
- (Opcjonalnie) Zmień nazwę zapytania na „Sprzedaż”.
- Przejdź do zakładki Strona główna (w edytorze zapytań).
- Kliknij Zamknij i załaduj.
Powyższe kroki spowoduje cofnięcie zestawu danych za pomocą dodatku Power Query i umieszczenie go z powrotem w programie Excel jako tabelę w nowym arkuszu.
Teraz możesz użyć tych danych do tworzenia różnych widoków za pomocą tabeli przestawnej. Na przykład możesz sprawdzić całkowitą wartość sprzedaży według miesiąca lub regionu.
Odświeżanie zapytania po dodaniu nowych danych
To wszystko działa dobrze.
Ale co się stanie, gdy do naszego oryginalnego zestawu danych zostaną dodane nowe dane.
Załóżmy, że otrzymujesz dane za lipiec, które są w tym samym formacie, co ten, od którego zaczęliśmy.
Czy muszę powtórzyć wszystkie kroki ponownie, aby uwzględnić te dane w moim nieprzestawnym zbiorze danych?
Odpowiedź brzmi nie.
I to właśnie jest niesamowite w dodatku Power Query. Możesz nadal dodawać nowe dane (lub modyfikować istniejące), a dodatek Power Query zaktualizuje je natychmiast po odświeżeniu.
Pokażę ci jak.
Załóżmy, że poniżej znajduje się nowy zestaw danych, który otrzymałem (który zawiera dodatkowe dane za lipiec):
Oto kroki, aby odświeżyć już utworzone zapytanie i przestawić te dane:
- Dodaj te nowe dane do oryginalnych danych użytych do utworzenia zapytania.
- Ponieważ dodajesz dane do sąsiedniej kolumny tabeli programu Excel, tabela programu Excel rozszerzy się, aby uwzględnić w niej te dane. Jeśli tak się nie stanie, zrób to ręcznie, przeciągając małą odwróconą ikonę „L” w prawym dolnym rogu tabeli Excela.
- Przejdź do zakładki Dane i kliknij Zapytania i połączenia. Spowoduje to wyświetlenie okienka ze wszystkimi istniejącymi zapytaniami.
- Kliknij prawym przyciskiem myszy zapytanie Sales w okienku Zapytania.
- Kliknij Odśwież.
Otóż to! Twoje nowe dane są natychmiast unpivoted i dodane do istniejących danych.
Zauważysz, że liczba wierszy pokazana w Zapytaniu aktualizuje się, aby pokazać nowe liczby. W tym przykładzie było to 24 przed odświeżeniem, a po odświeżeniu zmieniło się na 28.
Oznacza to również, że jeśli utworzysz jakiekolwiek tabele przestawne przy użyciu danych uzyskanych z dodatku Power Query, te tabele przestawne również zostaną odświeżone, aby wyświetlić zaktualizowane wyniki.
