Spis treści
Sformułowanie problemu
Jako dane wejściowe mamy plik Excel, gdzie jeden z arkuszy zawiera kilka tabel z danymi sprzedażowymi o następującej postaci:
Zauważ, że:
- Tabele o różnych rozmiarach iz różnymi zestawami produktów i regionów w rzędach i kolumnach bez żadnego sortowania.
- Między tabelami można wstawiać puste wiersze.
- Liczba tabel może być dowolna.
Dwa ważne założenia. Zakłada się, że:
- Nad każdą tabelą, w pierwszej kolumnie znajduje się nazwisko kierownika, którego sprzedaż ilustruje tabela (Iwanow, Pietrow, Sidorow itp.)
- Nazwy towarów i regionów we wszystkich tabelach pisane są w ten sam sposób – z dokładnością do wielkości liter.
Ostatecznym celem jest zebranie danych ze wszystkich tabel w jedną płaską znormalizowaną tabelę, wygodną do późniejszej analizy i budowania podsumowania, czyli w tej:
Krok 1. Połącz się z plikiem
Stwórzmy nowy pusty plik Excela i wybierzmy go na karcie Dane Command Pobierz dane – z pliku – z książki (Dane — Z pliku — Ze skoroszytu). Określ lokalizację pliku źródłowego z danymi sprzedaży a następnie w oknie nawigatora wybierz potrzebny nam arkusz i kliknij przycisk Konwertuj dane (Dane transformacji):
W rezultacie wszystkie z niego dane powinny zostać załadowane do edytora Power Query:
Krok 2. Posprzątaj śmieci
Usuń automatycznie wygenerowane kroki zmodyfikowany typ (Zmieniony typ) и Podwyższone nagłówki (Promowane nagłówki) i pozbądź się pustych linii i linii z sumami za pomocą filtra zero и RAZEM przy pierwszej kolumnie. W rezultacie otrzymujemy następujący obraz:
Krok 3. Dodawanie menedżerów
Aby później zrozumieć, gdzie jest sprzedaż, konieczne jest dodanie kolumny do naszej tabeli, gdzie w każdym wierszu będzie odpowiednie nazwisko. Dla tego:
1. Dodajmy kolumnę pomocniczą z numerami wierszy za pomocą polecenia Dodaj kolumnę – kolumnę indeksu – od 0 (Dodaj kolumnę — Kolumnę indeksu — Od 0).
2. Dodaj kolumnę z formułą za pomocą polecenia Dodawanie kolumny – niestandardowa kolumna (Dodaj kolumnę — Kolumna niestandardowa) i wprowadzić tam następującą konstrukcję:
Logika tej formuły jest prosta – jeśli wartość następnej komórki w pierwszej kolumnie to „Produkt”, to oznacza to, że natknęliśmy się na początek nowej tabeli, więc wyświetlamy wartość poprzedniej komórki z imię i nazwisko kierownika. W przeciwnym razie nic nie wyświetlamy, czyli null.
Aby uzyskać komórkę nadrzędną z nazwiskiem, najpierw odwołujemy się do tabeli z poprzedniego kroku #”Dodano indeks”, a następnie podaj nazwę kolumny, której potrzebujemy [Kolumna1] w nawiasach kwadratowych, a numer komórki w tej kolumnie w nawiasach klamrowych. Numer komórki będzie o jeden mniejszy od aktualnego, który pobieramy z kolumny wskaźnik, Odpowiednio.
3. Pozostaje wypełnić puste komórki zero nazwy z wyższych komórek za pomocą polecenia Przekształć – Wypełnij – W dół (Przekształć — Napełnij — W dół) i usuń niepotrzebną już kolumnę z indeksami i wiersze z nazwiskami w pierwszej kolumnie. W rezultacie otrzymujemy:
Krok 4. Grupowanie w osobne tabele przez menedżerów
Następnym krokiem jest pogrupowanie wierszy dla każdego menedżera w osobne tabele. W tym celu na zakładce Transformacja użyj polecenia Grupuj według (Przekształć – Grupuj według) i w oknie, które się otworzy, wybierz kolumnę Menedżer i operację Wszystkie wiersze (Wszystkie wiersze), aby po prostu zbierać dane bez stosowania jakiejkolwiek funkcji agregacji do je (suma, średnia itp.). P.):
W rezultacie otrzymujemy osobne tabele dla każdego menedżera:
Krok 5: Przekształć zagnieżdżone tabele
Teraz podajemy tabele, które znajdują się w każdej komórce wynikowej kolumny Wszystkie dane w przyzwoitym stanie.
Najpierw usuń kolumnę, która nie jest już potrzebna w każdej tabeli Menedżer. Używamy ponownie Kolumna niestandardowa Zakładka Transformacja (Przekształć — kolumna niestandardowa) i następujący wzór:
Następnie za pomocą innej kolumny obliczeniowej podnosimy pierwszy wiersz w każdej tabeli do nagłówków:
I na koniec wykonujemy główną transformację – rozkładanie każdego stołu za pomocą funkcji M Tabela.UnpivotOtherColumns:
Nazwy regionów z nagłówka trafią do nowej kolumny i otrzymamy węższą, ale jednocześnie dłuższą znormalizowaną tabelę. Puste komórki z zero są ignorowane.
Pozbywając się niepotrzebnych kolumn pośrednich, mamy:
Krok 6 Rozwiń zagnieżdżone tabele
Pozostaje rozwinąć wszystkie znormalizowane tabele zagnieżdżone w jedną listę za pomocą przycisku z podwójnymi strzałkami w nagłówku kolumny:
… i w końcu otrzymujemy to, czego chcieliśmy:
Możesz wyeksportować wynikową tabelę z powrotem do Excela za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…).
- Twórz tabele z różnymi nagłówkami z wielu książek
- Zbieranie danych ze wszystkich plików w danym folderze
- Zbieranie danych ze wszystkich arkuszy księgi w jedną tabelę