Przygotowywanie danych źródłowych do tabeli przestawnej

Posiadanie danych w odpowiednim formacie jest kluczowym krokiem w tworzeniu solidnej i wolnej od błędów tabeli przestawnej. Jeśli nie zrobisz tego we właściwy sposób, możesz mieć wiele problemów z tabelą przestawną.

Jaki jest dobry projekt danych źródłowych dla tabeli przestawnej?

Rzućmy okiem na przykład dobrych danych źródłowych dla tabeli przestawnej.

Oto, co sprawia, że ​​jest to dobry projekt danych źródłowych:

• Pierwszy wiersz zawiera nagłówki opisujące dane w kolumnach.
• Każda kolumna reprezentuje unikalną kategorię danych. Na przykład kolumna C zawiera tylko dane produktów, a kolumna D i tylko dane miesiąca.
• Każdy wiersz jest rekordem reprezentującym jedno wystąpienie transakcji lub sprzedaży.
• Nagłówki danych są unikalne i nie powtarzają się w żadnym miejscu zestawu danych. Na przykład, jeśli masz numery sprzedaży dla czterech kwartałów w roku, NIE powinieneś nazywać ich wszystkich jako Sprzedaż. Zamiast tego nadaj tym nagłówkom kolumn unikatowe nazwy, takie jak Sprzedaż K1, Sprzedaż K2 i tak dalej…
  • Jeśli nie masz unikalnych tytułów, możesz nadal tworzyć tabelę przestawną, a program Excel automatycznie nada im niepowtarzalność, dodając sufiks (np. Sprzedaż, Sprzedaż2, Sprzedaż3). Byłby to jednak okropny sposób na przygotowanie i użycie tabeli przestawnej.

Typowe pułapki, których należy unikać podczas przygotowywania danych źródłowych

• W danych źródłowych nie powinno być żadnych pustych kolumn. Ten jest łatwy do zauważenia. Jeśli masz pustą kolumnę w danych źródłowych, nie będziesz w stanie utworzyć tabeli przestawnej. Wyświetli błąd, jak pokazano poniżej.
• W danych źródłowych nie powinno być pustych komórek/wierszy. Chociaż możesz z powodzeniem utworzyć tabelę przestawną, mimo że masz puste komórki lub wiersze, istnieje wiele skutków ubocznych, które mogą cię ugryźć później w ciągu dnia.
  • Załóżmy na przykład, że masz pustą komórkę w kolumnie sprzedaży. Jeśli utworzysz tabelę przestawną przy użyciu tych danych i umieścisz pole sprzedaży w obszarze kolumn, wyświetli się LICZBA, a nie SUMA. Dzieje się tak, ponieważ program Excel interpretuje całą kolumnę jako zawierającą dane tekstowe (tylko z powodu pojedynczej pustej komórki).
• Zastosuj odpowiedni format do komórek w danych źródłowych. Na przykład, jeśli masz daty (które są przechowywane jako numery seryjne w zapleczu programu Excel), zastosuj jeden z dopuszczalnych formatów dat. Pomogłoby to w utworzeniu tabeli przestawnej i użyciu daty jako jednego z kryteriów do podsumowywania, grupowania i sortowania danych.
  • Jeśli masz kilka sekund, spróbuj tego. Sformatuj daty w tabeli przestawnej jako liczby, a następnie utwórz tabelę przestawną na podstawie tych danych. Teraz w tabeli przestawnej wybierz pole daty i zobacz, co się stanie. Automatycznie umieści go w polu wartości. Dzieje się tak, ponieważ Twoja tabela przestawna nie zna tych dat. Interpretuje je jako liczby.
• Nie dołączaj żadnych sum kolumn, sum wierszy, średnich itp. jako części danych źródłowych. Gdy już będziesz mieć tabelę przestawną, możesz je łatwo uzyskać później.
• Zawsze twórz tabelę Excel, a następnie używaj jej jako źródła tabeli przestawnej. To raczej dobra praktyka, a nie pułapka. Twoja tabela przestawna będzie działać dobrze z danymi źródłowymi, które również nie są tabelą programu Excel. Zaletą tabeli programu Excel jest to, że może dostosować rozszerzające się dane. Jeśli dodasz więcej wierszy do zestawu danych, nie musisz ciągle dostosowywać danych źródłowych. Możesz po prostu odświeżyć tabelę przestawną, która automatycznie uwzględni nowe wiersze dodane do danych źródłowych.

Przykłady złych projektów danych źródłowych

Rzućmy okiem na kilka złych przykładów projektów danych źródłowych.

Zły projekt danych źródłowych - przykład 1

Jest to powszechny sposób na przechowywanie danych, ponieważ są one łatwe do śledzenia i zrozumienia. Z takim układem danych wiążą się dwa problemy:

• Nie masz pełnego obrazu. Na przykład można zobaczyć, że sprzedaż w regionie Mid West w kwartale 1 wynosi 2924300. Ale czy jest to pojedyncza sprzedaż, czy liczba sprzedaży. Jeśli masz każdy rekord dostępny w osobnym wierszu, możesz przeprowadzić lepszą analizę.
• Jeśli pójdziesz dalej i utworzysz tabelę przestawną za pomocą tego (co możesz), otrzymasz różne pola dla różnych kwartałów. Coś, jak pokazano poniżej:

Zły projekt danych źródłowych - przykład 2

Ta reprezentacja danych może zostać dobrze odebrana przez kierownictwo i publiczność prezentacji PowerPoint, ale nie nadaje się do tworzenia tabeli przestawnej.

Ponownie, jest to rodzaj podsumowania, które można łatwo utworzyć za pomocą tabeli przestawnej. Więc nawet jeśli ostatecznie chcesz mieć taki wygląd swoich danych, zachowaj dane źródłowe w formacie gotowym do przestawiania i utwórz ten widok za pomocą tabeli przestawnej.

Zły projekt danych źródłowych - przykład 3

To znowu wynik, który można łatwo uzyskać za pomocą tabeli przestawnej. Ale nie można go użyć do utworzenia tabeli przestawnej.

W zestawie danych znajdują się puste komórki, a ćwiartki są rozłożone jako nagłówki kolumn.

Również region jest określony na górze, chociaż powinien być częścią każdego rekordu.

[STUDIUM PRZYPADKU] Konwertowanie źle sformatowanych danych na dane źródłowe gotowe do użycia w tabeli przestawnej

Czasami możesz uzyskać zestaw danych, który nie nadaje się do użycia jako dane źródłowe tabeli przestawnej. W takim przypadku możesz nie mieć innego wyjścia, jak przekonwertować dane do formatu przyjaznego Pivot.

Oto przykład złego projektowania danych:

Teraz możesz użyć funkcji programu Excel lub zapytania przestawnego, aby przekonwertować te dane na format, który może być używany jako dane źródłowe tabeli przestawnej.

Zobaczmy, jak działają obie te metody.

Metoda 1: Korzystanie z formuł programu Excel

Zobaczmy, jak za pomocą funkcji programu Excel przekonwertować te dane na format gotowy do tabeli przestawnej.

• Utwórz unikalny nagłówek kolumny dla wszystkich kategorii w oryginalnym zestawie danych. W tym przykładzie byłby to region, kwartał i sprzedaż.
• W komórce poniżej nagłówka Region użyj następującej formuły: =INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))
  • Przeciągnij formułę w dół i powtórzy wszystkie regiony.
• W komórce poniżej nagłówka Quarter użyj następującej formuły: =INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1))+0.1) ,0))
  • Przeciągnij formułę w dół i powtórzy wszystkie ćwiartki.
• W nagłówku poniżej Sprzedaż użyj następującej formuły: =INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0) ))
  • Przeciągnij go w dół, aby uzyskać wszystkie wartości. Ta formuła wykorzystuje dane regionu i kwartału jako wartości wyszukiwania i zwraca wartość sprzedaży z oryginalnego zestawu danych.

Teraz możesz użyć tych danych wynikowych jako danych źródłowych dla tabeli przestawnej.

Kliknij tutaj, aby pobrać przykładowy plik.

Metoda 2: Korzystanie z Power Query

Dodatek Power Query ma funkcję, która umożliwia łatwe konwertowanie tego rodzaju danych do formatu danych gotowych do przestawiania.

Jeśli korzystasz z programu Excel 2016, funkcje dodatku Power Query będą dostępne na karcie Dane w grupie Pobierz i przekształć. Jeśli używasz programu Excel 2013 lub wcześniejszych wersji, możesz użyć go jako dodatku.

Oto doskonały przewodnik dotyczący instalowania dodatku Power Query autorstwa Jona z kampusu programu Excel.

Ponownie, biorąc pod uwagę, że masz dane sformatowane w sposób pokazany poniżej:

Oto kroki, aby przekonwertować dane źródłowe na format gotowy do tabeli przestawnej:

• Przekształć dane w tabelę programu Excel. Wybierz zestaw danych i przejdź do Wstaw -> Tabele -> Tabela.
• W oknie dialogowym Wstaw tabelę upewnij się, że wybrano właściwy zakres i kliknij przycisk OK. Spowoduje to przekształcenie danych tabelarycznych w tabelę programu Excel.
• W Excel 2016 przejdź do Dane -> Pobierz i przekształć -> Z tabeli.
  • Jeśli używasz dodatku Power Query w poprzedniej wersji, przejdź do Power Query -> Dane zewnętrzne -> Z tabeli.
• W edytorze zapytań wybierz kolumny, które chcesz przestawić. W tym przypadku są to te z czterech kwartałów. Aby zaznaczyć wszystkie kolumny, przytrzymaj klawisz Shift, a następnie wybierz pierwszą kolumnę, a następnie ostatnią kolumnę.
• W Edytorze zapytań przejdź do Przekształć -> Dowolna kolumna -> Kolumny unpivot. Spowoduje to przekonwertowanie danych kolumny do formatu przyjaznego dla tabeli przestawnej.
• Dodatek Power Query nadaje kolumnom nazwy ogólne. Zmień te nazwy na te, które chcesz. W takim przypadku zmień Atrybut na Kwartał i Wartość na Sprzedaż.
• W Edytorze zapytań przejdź do Plik -> Zamknij i załaduj. Spowoduje to zamknięcie okna dialogowego Edytor Power Query i utworzenie oddzielnego arkusza, który będzie zawierał dane z kolumnami nieprzestawnymi.

Teraz, gdy wiesz, jak przygotować dane źródłowe dla tabeli przestawnej, jesteś gotowy do pracy w programie Excel w świecie tabel przestawnych.

Oto kilka innych samouczków dotyczących tabel przestawnych, które mogą Ci się przydać:

• Jak odświeżyć tabelę przestawną w programie Excel.
• Korzystanie z fragmentatorów w tabeli przestawnej programu Excel - przewodnik dla początkujących.
• Jak grupować daty w tabelach przestawnych w programie Excel.
• Jak grupować liczby w tabeli przestawnej w programie Excel.
• Pamięć podręczna przestawna w programie Excel - co to jest i jak najlepiej z niej korzystać.
• Jak filtrować dane w tabeli przestawnej w programie Excel.
• Jak dodać i używać pola obliczeniowego tabeli przestawnej programu Excel.
• Jak zastosować formatowanie warunkowe w tabeli przestawnej w programie Excel.
• Jak zastąpić puste komórki zerami w tabelach przestawnych programu Excel.