Twórz tabele z różnymi nagłówkami z wielu książek

Sformułowanie problemu

Mamy kilka plików (w naszym przykładzie – 4 sztuki, w ogólnym przypadku – tyle, ile chcesz) w jednym folderze Raporty:

Wewnątrz te pliki wyglądają tak:

W którym:

• Arkusz danych, którego potrzebujemy, jest zawsze nazywany ZDJĘCIA, ale może znajdować się w dowolnym miejscu skoroszytu.
  
• Poza prześcieradłem ZDJĘCIA Każda książka może mieć inne arkusze.
• Tabele z danymi mają różną liczbę wierszy i mogą zaczynać się od innego wiersza w arkuszu.
• Nazwy tych samych kolumn w różnych tabelach mogą się różnić (na przykład Ilość = Ilość = Ilość).
• Kolumny w tabelach można ułożyć w innej kolejności.

Zadanie: zebrać dane sprzedażowe ze wszystkich plików z arkusza ZDJĘCIA do jednej wspólnej tabeli, aby następnie zbudować na niej podsumowanie lub inne analizy.

Krok 1. Przygotowanie katalogu nazw kolumn

Pierwszą rzeczą do zrobienia jest przygotowanie podręcznika ze wszystkimi możliwymi opcjami nazw kolumn i ich poprawną interpretacją:

Konwertujemy tę listę w dynamiczną „inteligentną” tabelę za pomocą przycisku Formatuj jako tabelę na karcie Strona główna (Strona główna — Formatuj jako tabelę) lub skrót klawiaturowy Ctrl+T i załaduj go do Power Query za pomocą polecenia Dane — z tabeli/zakresu (Dane — z tabeli/zakresu). W ostatnich wersjach programu Excel zmieniono jego nazwę na Z liśćmi (z arkusza).

W oknie edytora zapytań Power Query tradycyjnie usuwamy krok Zmieniony typ i dodaj nowy krok zamiast niego, klikając przycisk f_xna pasku formuły (jeśli nie jest widoczny, możesz go włączyć na karcie) Review) i wprowadź tam formułę we wbudowanym języku Power Query M:

=Tabela.ToRows(Źródło)

To polecenie przekonwertuje to, które zostało załadowane w poprzednim kroku Źródło tabela referencyjna w listę składającą się z list zagnieżdżonych (List), z których każda z kolei jest parą wartości Stało się z jednej linii:

Tego typu danych będziemy potrzebować nieco później, przy masowej zmianie nazw nagłówków wszystkich załadowanych tabel.

Po zakończeniu konwersji wybierz polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… i rodzaj importu Po prostu stwórz połączenie (Dom — Zamknij i wczytaj — Zamknij i wczytaj do… — Utwórz tylko połączenie) i wróć do Excela.

Krok 2. Ładujemy wszystko ze wszystkich plików bez zmian

Teraz załadujmy zawartość wszystkich naszych plików z folderu – na razie bez zmian. Wybór drużyn Dane – Pobierz dane – Z pliku – Z folderu (Dane — Pobierz dane — Z pliku — Z folderu) a następnie folder, w którym znajdują się nasze książki źródłowe.

W oknie podglądu kliknij konwertować (Przekształcać) or zmiana (Edytować):

A następnie rozwiń zawartość wszystkich pobranych plików (Dwójkowy) przycisk z podwójnymi strzałkami w nagłówku kolumny Treść:

Power Query na przykładzie pierwszego pliku (Wostok.xlsx) zapyta nas o nazwę arkusza, który chcemy pobrać z każdego skoroszytu – wybierz ZDJĘCIA i naciśnij OK:

Po tym (w rzeczywistości) nastąpi kilka zdarzeń, które nie są oczywiste dla użytkownika, których konsekwencje są wyraźnie widoczne w lewym panelu:

1. Power Query pobierze pierwszy plik z folderu (będziemy go mieć Wostok.xlsx — widzieć Przykład pliku) jako przykład i importuje jego zawartość tworząc zapytanie Konwertuj przykładowy plik. To zapytanie będzie miało kilka prostych kroków, takich jak Źródło (dostęp do plików) Nawigacja (wybór arkuszy) i ewentualnie podniesienie tytułów. To żądanie może załadować dane tylko z jednego określonego pliku Wostok.xlsx.
   
2. Na podstawie tego żądania zostanie utworzona powiązana z nim funkcja Konwertuj plik (oznaczone charakterystyczną ikoną f_x), gdzie plik źródłowy nie będzie już stałą, ale wartością zmiennej – parametrem. Tak więc ta funkcja może wyodrębnić dane z dowolnej książki, którą włożymy do niej jako argument.
   
3. Funkcja zostanie zastosowana po kolei do każdego pliku (binarnego) z kolumny Treść – odpowiada za to step Zadzwoń do funkcji niestandardowej w naszym zapytaniu, które dodaje kolumnę do listy plików Konwertuj plik z wynikami importu z każdego skoroszytu:

   

4. Dodatkowe kolumny są usuwane.
5. Zawartość tabel zagnieżdżonych jest rozwijana (krok Rozszerzona kolumna tabeli) – i widzimy ostateczne wyniki zbierania danych ze wszystkich książek:

   
   

Krok 3. Szlifowanie

Poprzedni zrzut ekranu wyraźnie pokazuje, że montaż bezpośredni „tak jak jest” okazał się kiepskiej jakości:

• Kolumny są odwrócone.
• Wiele dodatkowych linii (pustych i nie tylko).
• Nagłówki tabeli nie są postrzegane jako nagłówki i są mieszane z danymi.
  

Wszystkie te problemy można rozwiązać bardzo łatwo — wystarczy dostosować zapytanie Konwertuj przykładowy plik. Wszystkie zmiany, które w nim wprowadzimy, zostaną automatycznie przypisane do powiązanej funkcji Konwertuj plik, co oznacza, że ​​zostaną użyte później podczas importowania danych z każdego pliku.

Otwierając wniosek Konwertuj przykładowy plik, dodaj kroki, aby filtrować niepotrzebne wiersze (na przykład według kolumny Column2) i podnoszenie nagłówków przyciskiem Użyj pierwszego wiersza jako nagłówków (Użyj pierwszego wiersza jako nagłówków). Stół będzie wyglądał znacznie lepiej.

Aby kolumny z różnych plików później automatycznie pasowały do ​​siebie, muszą mieć takie same nazwy. Możesz wykonać taką masową zmianę nazwy według wcześniej utworzonego katalogu za pomocą jednej linii kodu M. Wciśnijmy ponownie przycisk f_x na pasku formuły i dodaj funkcję do zmiany:

= Table.RenameColumns(#”Podwyższone nagłówki”, Nagłówki, MissingField.Ignore)

Ta funkcja pobiera tabelę z poprzedniego kroku Podwyższone nagłówki i zmienia nazwy wszystkich kolumn zgodnie z zagnieżdżoną listą wyszukiwania Nagłówki. Trzeci argument Brakujące pole. Ignoruj jest potrzebne, aby w tych nagłówkach, które są w katalogu, ale nie ma ich w tabeli, nie wystąpił błąd.

Właściwie to wszystko.

Wracając do prośby Raporty zobaczymy zupełnie inny obraz – znacznie ładniejszy niż poprzedni:

• Co to są Power Query, Power Pivot, Power BI i dlaczego użytkownik programu Excel ich potrzebuje
  
• Zbieranie danych ze wszystkich plików w danym folderze
• Zbieranie danych ze wszystkich arkuszy księgi w jedną tabelę