Spis treści
Sformułowanie problemu
Przyjrzyjmy się pięknemu rozwiązaniu jednej z bardzo standardowych sytuacji, z którymi prędzej czy później spotyka się większość użytkowników programu Excel: musisz szybko i automatycznie zbierać dane z dużej liczby plików w jednej tabeli końcowej.
Załóżmy, że mamy następujący folder, który zawiera kilka plików z danymi z miast oddziałów:
Liczba plików nie ma znaczenia i może ulec zmianie w przyszłości. Każdy plik ma arkusz o nazwie Obrotygdzie znajduje się tabela danych:
Liczba wierszy (porządków) w tabelach jest oczywiście inna, ale zestaw kolumn jest wszędzie standardowy.
Zadanie: zebranie danych ze wszystkich plików do jednej księgi z późniejszą automatyczną aktualizacją przy dodawaniu lub usuwaniu plików miast lub wierszy w tabelach. Zgodnie z ostateczną tabelą skonsolidowaną, wtedy będzie można budować dowolne raporty, tabele przestawne, filtrować dane, itp. Najważniejsze, żeby móc je zbierać.
Wybieramy broń
Do rozwiązania potrzebujemy najnowszej wersji programu Excel 2016 (niezbędna funkcjonalność jest już w nim domyślnie wbudowana) lub poprzednich wersji programu Excel 2010-2013 z zainstalowanym bezpłatnym dodatkiem Zapytanie o moc od firmy Microsoft (pobierz ją tutaj). Power Query to bardzo elastyczne i niezwykle wydajne narzędzie do ładowania danych ze świata zewnętrznego do programu Excel, a następnie ich usuwania i przetwarzania. Power Query obsługuje prawie wszystkie istniejące źródła danych — od plików tekstowych po SQL, a nawet Facebook 🙂
Jeśli nie masz programu Excel 2013 lub 2016, nie możesz dalej czytać (tylko żartuję). W starszych wersjach Excela takie zadanie można wykonać tylko przez zaprogramowanie makra w Visual Basic (co jest bardzo trudne dla początkujących) lub przez monotonne ręczne kopiowanie (co zajmuje dużo czasu i generuje błędy).
Krok 1. Importuj jeden plik jako próbkę
Najpierw zaimportujmy dane z jednego skoroszytu jako przykład, aby Excel „podchwycił pomysł”. Aby to zrobić, utwórz nowy pusty skoroszyt i…
- jeśli masz Excel 2016, otwórz kartę Dane , a następnie Utwórz zapytanie – Z pliku – Z książki (Dane — Nowe zapytanie — Z pliku — Z programu Excel)
- jeśli masz program Excel 2010-2013 z zainstalowanym dodatkiem Power Query, otwórz kartę Zapytanie o moc i wybierz na nim Z pliku – Z książki (Z pliku — Z Excela)
Następnie w oknie, które się otworzy, przejdź do naszego folderu z raportami i wybierz dowolny z plików miasta (nie ma znaczenia który, bo wszystkie są typowe). Po kilku sekundach powinno pojawić się okno Nawigatora, w którym po lewej stronie należy wybrać potrzebny nam arkusz (Sprzedaż), a jego zawartość zostanie wyświetlona po prawej stronie:
Jeśli klikniesz przycisk w prawym dolnym rogu tego okna Do pobrania (Obciążenie), tabela zostanie natychmiast zaimportowana do arkusza w swojej oryginalnej formie. Dla jednego pliku jest to dobre, ale musimy wczytać wiele takich plików, więc pójdziemy trochę inaczej i klikniemy przycisk Korekta (Edytować). Następnie w osobnym oknie powinien wyświetlić się edytor zapytań Power Query z naszymi danymi z książki:
To bardzo potężne narzędzie, które pozwala „wykończyć” tabelę do potrzebnego nam widoku. Nawet powierzchowny opis wszystkich jego funkcji zająłby około stu stron, ale w bardzo krótkim czasie, korzystając z tego okna możesz:
- odfiltrować niepotrzebne dane, puste linie, linie z błędami
- sortuj dane według jednej lub kilku kolumn
- pozbyć się powtórzeń
- podziel przyklejony tekst na kolumny (według ograniczników, liczby znaków itp.)
- uporządkuj tekst (usuń dodatkowe spacje, popraw wielkość liter itp.)
- konwertuj typy danych w każdy możliwy sposób (zmieniaj liczby, takie jak tekst, na normalne liczby i odwrotnie)
- transponować (obracać) tabele i rozszerzać dwuwymiarowe tabele krzyżowe na płaskie
- dodawaj dodatkowe kolumny do tabeli i używaj w nich formuł i funkcji za pomocą języka M wbudowanego w dodatek Power Query.
- ...
Na przykład dodajmy do naszej tabeli kolumnę z tekstową nazwą miesiąca, aby później łatwiej było budować raporty tabel przestawnych. Aby to zrobić, kliknij prawym przyciskiem myszy nagłówek kolumny danei wybierz polecenie Zduplikowana kolumna (Zduplikowana kolumna), a następnie kliknij prawym przyciskiem myszy nagłówek zduplikowanej kolumny, która się pojawi i wybierz Polecenia Przekształć – Miesiąc – Nazwa miesiąca:
Należy utworzyć nową kolumnę z nazwami tekstowymi miesiąca dla każdego wiersza. Klikając dwukrotnie nagłówek kolumny, możesz zmienić jego nazwę z Kopiuj datę do wygodniejszego Miesiącnp.
Jeśli w niektórych kolumnach program nie do końca poprawnie rozpoznał typ danych, możesz mu pomóc, klikając ikonę formatu po lewej stronie każdej kolumny:
Możesz wykluczyć wiersze z błędami lub pustymi wierszami, a także niepotrzebnych menedżerów lub klientów, korzystając z prostego filtra:
Ponadto wszystkie wykonane przekształcenia są naprawione w prawym panelu, gdzie zawsze można je cofnąć (krzyż) lub zmienić ich parametry (koło zębate):
Lekkie i eleganckie, prawda?
Krok 2. Przekształćmy naszą prośbę w funkcję
Aby następnie powtórzyć wszystkie przekształcenia danych wykonane dla każdej importowanej książki, musimy przekonwertować nasze utworzone żądanie w funkcję, która następnie zostanie zastosowana z kolei do wszystkich naszych plików. Zrobienie tego jest w rzeczywistości bardzo proste.
W Edytorze zapytań przejdź do zakładki Widok i kliknij przycisk Zaawansowany edytor (Widok — Edytor zaawansowany). Powinno otworzyć się okno, w którym wszystkie nasze poprzednie działania zostaną zapisane w postaci kodu w języku M. Zwróć uwagę, że ścieżka do pliku, który zaimportowaliśmy dla przykładu, jest zakodowana w kodzie:
Teraz dokonajmy kilku poprawek:
Ich znaczenie jest proste: pierwsza linia (ścieżka pliku)=> zamienia naszą procedurę w funkcję z argumentem ścieżka pliku, a poniżej zmieniamy stałą ścieżkę na wartość tej zmiennej.
Wszystko. Kliknij koniec i powinien to zobaczyć:
Nie bój się, że dane zniknęły – tak naprawdę wszystko jest OK, wszystko powinno tak wyglądać 🙂 Udało nam się stworzyć naszą niestandardową funkcję, w której cały algorytm importu i przetwarzania danych jest zapamiętywany bez przywiązywania się do konkretnego pliku . Pozostaje nadać mu bardziej zrozumiałą nazwę (na przykład otrzymać dane) w panelu po prawej stronie w polu Imię i możesz zbierać Strona główna — Zamknij i pobierz (Strona główna — Zamknij i załaduj). Zwróć uwagę, że ścieżka do pliku, który zaimportowaliśmy dla przykładu, jest zakodowana w kodzie. Powrócisz do głównego okna Microsoft Excel, ale po prawej stronie powinien pojawić się panel z utworzonym połączeniem do naszej funkcji:
Krok 3. Zbieranie wszystkich plików
Cała najtrudniejsza część za nami, przyjemna i łatwa pozostaje. Przejdź do zakładki Dane – Utwórz zapytanie – Z pliku – Z folderu (Dane — Nowe zapytanie — Z pliku — Z folderu) lub, jeśli masz Excel 2010-2013, podobnie jak w zakładce Zapytanie o moc. W wyświetlonym oknie określ folder, w którym znajdują się wszystkie nasze źródłowe pliki miasta i kliknij OK. Następnym krokiem powinno być otwarcie okna, w którym zostaną wyświetlone wszystkie pliki Excel znalezione w tym folderze (i jego podfolderach) oraz szczegóły dla każdego z nich:
Kliknij zmiana (Edytować) i znowu dostajemy się do znajomego okna edytora zapytań.
Teraz musimy dodać do naszej tabeli kolejną kolumnę z utworzoną przez nas funkcją, która „wyciągnie” dane z każdego pliku. W tym celu przejdź do zakładki Dodaj kolumnę – niestandardowa kolumna (Dodaj kolumnę — Dodaj niestandardową kolumnę) i w okienku, które się pojawi, wpisz naszą funkcję otrzymać dane, podając jako argument pełną ścieżkę do każdego pliku:
Po kliknięciu OK utworzoną kolumnę należy dodać do naszej tabeli po prawej stronie.
Usuńmy teraz wszystkie niepotrzebne kolumny (jak w Excelu, używając prawego przycisku myszy – Usunąć), pozostawiając tylko dodaną kolumnę i kolumnę z nazwą pliku, ponieważ ta nazwa (a dokładniej miasto) przyda się mieć w danych sumarycznych dla każdego wiersza.
A teraz „wow moment” – kliknij na ikonkę z własnymi strzałkami w prawym górnym rogu dodanej kolumny z naszą funkcją:
… odznacz Użyj oryginalnej nazwy kolumny jako przedrostka (Użyj oryginalnej nazwy kolumny jako prefiksu)i kliknij OK. A nasza funkcja załaduje i przetworzy dane z każdego pliku, zgodnie z nagranym algorytmem i zbierając wszystko we wspólnej tabeli:
Dla pełnego piękna możesz również usunąć rozszerzenia .xlsx z pierwszej kolumny z nazwami plików – przez standardową zamianę na „nic” (kliknij prawym przyciskiem myszy na nagłówek kolumny – namiastka) i zmień nazwę tej kolumny na Miasto. A także popraw format danych w kolumnie z datą.
Wszystko! Kliknij Strona główna – Zamknij i załaduj (Strona główna — Zamknij i załaduj). Wszystkie dane zebrane przez zapytanie dla wszystkich miast zostaną załadowane do aktualnego arkusza Excel w formacie „inteligentnej tabeli”:
Utworzone połączenie i nasza funkcja asemblera nie muszą być w żaden sposób osobno zapisywane – są zapisywane razem z aktualnym plikiem w zwykły sposób.
W przyszłości przy jakichkolwiek zmianach w folderze (dodawanie lub usuwanie miast) lub w plikach (zmiana liczby wierszy) wystarczy kliknąć prawym przyciskiem myszy bezpośrednio na tabelę lub zapytanie w prawym panelu i wybrać Komenda Zaktualizuj i zapisz (Odświeżać) – Dodatek Power Query ponownie „odbuduje” wszystkie dane w ciągu kilku sekund.
PS
Poprawka. Po aktualizacjach ze stycznia 2017 r. Power Query nauczył się, jak samodzielnie zbierać skoroszyty Excela, czyli nie trzeba już tworzyć osobnej funkcji – dzieje się to automatycznie. Tym samym drugi krok z tego artykułu nie jest już potrzebny, a cały proces staje się zauważalnie prostszy:
- Dodaj Utwórz żądanie – Z pliku – Z folderu – Wybierz folder – OK
- Po wyświetleniu listy plików naciśnij zmiana
- W oknie Edytora zapytań rozwiń kolumnę Binarne podwójną strzałką i wybierz nazwę arkusza, która ma zostać pobrana z każdego pliku
I to wszystko! Utwór muzyczny!
- Przeprojektowanie tabeli przestawnej na płaską odpowiednią do budowy tabel przestawnych
- Tworzenie animowanego wykresu bąbelkowego w programie Power View
- Makro do łączenia arkuszy z różnych plików Excel w jeden