Łączenie plików w Excelu - czyli nie da się proszę Pana - ale czy na pewno?

Praca z danymi w Excelu potrafi być męcząca — zwłaszcza wtedy, gdy co miesiąc (albo nawet codziennie) otrzymujesz kolejne pliki do analizy. Kopiowanie danych z dziesiątek niemal identycznych arkuszy nie tylko zajmuje czas, ale też zwiększa ryzyko błędów. A przecież wcale nie musi tak być.

Power Query, narzędzie dostępne w Excelu i Power BI Desktop, pozwala w łatwy sposób połączyć wiele plików Excela w jedną, zautomatyzowaną tabelę. Dzięki temu możesz raz przygotować zapytanie i potem tylko dodawać nowe pliki do folderu — bez konieczności ręcznej pracy.

Czy masz dość ręcznego kopiowania danych z wielu plików Excel? Power Query może być Twoim najlepszym pomocnikiem i całą „czarną robotę” wykonać za Ciebie! Niezależnie od tego, czy analizujesz miesięczne raporty sprzedaży, budżety działowe czy wyniki ankiet, łączenie wielu plików w jeden zestaw danych nigdy nie było prostsze i można to zrobić kilkoma kliknięciami w edytorze Power Query.

W tym artykule omówimy sobie kilka scenariuszy, jak krok po kroku połączyć wiele plików Excela w Power Query, zautomatyzować proces i oszczędzić mnóstwo czasu.

Ale zacznijmy od początku. Może nie od tematu co było pierwsze kura czy jajko😊 ale od omówienia gdzie w ogóle mogę wykorzystać Power Query. Warto wiedzieć, że to samo środowisko znajdziesz w MS Excel i w Power BI Desktop więc łączyć pliki i przekształcać dane możesz w każdym z tych programów w taki sam sposób.

Zanim przejdziemy do konkretnych przykładów zastanówmy się kiedy warto łączyć wiele plików Excela.

Przykładowe Scenariusze, w których ta technika się przydaje:

• Masz miesięczne raporty sprzedaży w osobnych plikach.
• Każdy dział firmy wysyła dane w osobnym arkuszu.
• Codziennie generujesz raporty, które trzeba analizować zbiorczo.
• Dane z różnych oddziałów firmy spływają do Ciebie w plikach o tej samej strukturze.

Jeżeli chociaż  jeden z tych punktów brzmi dla Ciebie znajomo to oznacza, że warto poświęcić najbliższe kilka minut na przeczytanie tego artykułu i zapoznanie się w jaki sposób połączyć pliki w jedno zapytanie, aby tworzyć zbiorcze raporty.

Przejdźmy zatem do omówienia ważnych aspektów, o które trzeba zadbać aby łączenie wykonało się po naszej myśli:

Przede wszystkim przygotuj pliki, które chcesz połączyć, w jednym folderze. Upewnij się, że mają taką samą strukturę danych (te same kolumny i ich nazwy). Nie jest to konieczne ale zdecydowanie skomplikuje to dalsze łączenie.

Poniżej cztery pliki miesięcznej sprzedaży w folderze , które potrzebuję złączyć:

Na początek prosty scenariusz – łączymy dane z plików, w którym jest tylko jeden arkusz o tej samej nazwie w każdym pliku – (w kolejnych przykładach omówimy sobie bardziej złożone przypadki)

Gdy pliki znajdują się we właściwym folderze należy w MS Excel wykonać poniższe kroki aby wskazać konkretny folder”

1. Otwórz nowy plik Excel.
2. Przejdź do zakładki Dane.
3. Wybierz Pobierz dane > Z pliku > Z folderu.
4. Wskaż folder, w którym znajdują się Twoje pliki.

Po wybraniu folderu Power Query wyświetli listę plików.

1. Kliknij Połącz.
2. Power Query automatycznie spróbuje otworzyć pliki i załadować dane z arkuszy (domyślnie pierwszy arkusz).
3. W oknie podglądu możesz zdecydować, z których części plików chcesz korzystać — np. konkretny arkusz, tabela lub zakres nazwany –  (omówimy to w dalszej części artykułu)
4. Zaznacz arkusz i kliknij OK, a Power Query połączy dane w jedną tabelę.

Teraz wystarczy tylko dostosować dane (np. przefiltrować niepotrzebne wiersze, zmienić typy danych czy dodać kolumnę z nazwą pliku — przydatne, by wiedzieć, skąd pochodzą dane).

Ostatnim krokiem będzie załadowanie danych do Excela

1. Kliknij Zamknij i załaduj.
2. Dane pojawią się w arkuszu Excela jako tabela.

Kilkoma kliknięciami Power Query połączył wiele plików w jedno zapytanie. Ale to jeszcze nie wszystko! Taki mały prezent od Power Query – gdy dodasz nowy plik do folderu (z tą samą strukturą), wystarczy kliknąć Odśwież wszystko w Excelu – dane z nowego pliku automatycznie pojawią się w Twojej tabeli.

W ramach pracy domowej przetestuj to rozwiązanie!

My w artykule skupimy się na trochę bardziej skomplikowanym przypadku gdy pliki mają wiele arkuszy i potrzebujemy złączyć tylko jeden konkretnie wskazany. Częstymi scenariuszami są łączenia arkusza o konkretnej nazwie, tylko odkrytych arkuszy czy zawsze pierwszego. Do wykonania tego łączenia będziemy potrzebowali przefiltrować w odpowiednich miejscach dane w Power Query.

Pierwszym naszym wyzwaniem jest połączenie tylko danych z plików, w których arkusze nazywają się „Dane do połączenia”.

Pierwsza część wskazywania danych w folderze jest identyczna jak powyżej. Po wskazaniu plików należy w wyskakującym oknie wybrać nazwę arkusza, który potrzebujemy złączyć. Uwaga! Złączenie zadziała poprawnie tylko wtedy gdy arkusze w plikach mają zawsze takie same nazwy. Power Query na podstawie pierwszego pliku, który wybiera alfabetycznie z dostępnych łączy dane z pozostałych.

Po połączeniu należy jak zawsze oczyścić dane i załadować do MS Excel lub Power BI i cieszyć się z zaoszczędzonego czasu – najlepiej wykorzystując go na naukę języka DAX, który jest kluczowym elementem bardziej zaawansowanych i złożonych raportów analitycznych 💪.

A co jeżeli naszym celem jest złączenie tylko arkuszy odkrytych? Czyli innymi słowy mówiąc nie chcemy łączyć arkuszy ukrytych w MS Excel.

W przykładzie poniżej widać, że niektóre arkusze są ukryte i tych danych nie chcemy połączyć.

W pierwszym pliku jest 5 ukrytych arkuszy:

W drugim pliku jest 6 ukrytych arkuszy:

A więc celem jest złączenie tylko arkuszy o nazwach Styczeń, Luty, Marzec, Kwiecień z pierwszego pliku oraz Sierpień i Wrzesień z pliku drugiego.

Aby wykonać takie łączenie w znajomym już nam oknie łączenie plików wybieramy parametr aby przefiltrować dane po kolumnie Hidden.

Proszę zwrócić uwagę, że na tym etapie nie będą widoczne arkusze ukryte co może nas wprowadzić w błąd.  Arkusze te pojawią się po połączeniu plików.

Wartość FALSE będzie przypisana do arkuszy odkrytych, a wartość TRUE do arkuszy ukrytych.

Więc gdy naszym celem jest złączenie danych z plików tylko z odkrytych arkuszy zakładamy filtr na kolumnie Hidden  = FALSE i Power Query tylko te dane złączy.

Następnie należy rozwinąć dane w kolumnach i rozwinąć je w wierszach. Nie zapomnijmy zadbać o nagłówki, niepotrzebne dane itp.

Poniżej dane tylko z odkrytych arkuszy:

Ostatnim przykładem będzie połączenie zawsze pierwszego arkusza niezależnie od jego nazwy. Wykorzystamy do tego kolumnę indeksu aby ponumerować wiersze a w rzeczywistości kolejność arkuszy w pliku.

W tym mało przyjaznym dla użytkownika drzewku wybieramy opcję „Przekształć przykładowy plik” – i tam dodajemy kolumnę indeksu od 1 aby ponumerować kolejność arkuszy w pliku. Należy to zrobić w tym miejscu aby taka kolumna została zastosowana dla każdego pliku, który łączymy. Celem takiej kolumny indeksu jest przypisanie 1 dla każdego pierwszego arkusza w każdym pliku. Kolumna ta zostanie dodana do każdego pliku numerując arkusze niezależnie.

Następnie należy odfiltrować we właściwym zapytaniu dane z tej kolumny po wartości 1 i jak zwykle załadować dane do MS Excel. W ten sposób Power Query będzie zawsze łączył tylko pierwsze arkusze z każdego pliku.

W ramach ćwiczenia proszę pozamieniać kolejność arkuszy w plikach i sprawdzić czy Power Query zawsze łączy ten pierwszy.

Implementując tą samą logikę można połączyć na wiele sposobów inne arkusze.

Podsumowując łączenie wielu plików Excela w Power Query to ogromna oszczędność czasu. Zamiast ręcznie kopiować dane, wystarczy kilka kliknięć, by zautomatyzować cały proces. A co najlepsze — raz przygotowane zapytanie można aktualizować jednym kliknięciem. Jeśli regularnie pracujesz z wieloma plikami, ta umiejętność jest absolutnie niezbędna.

Powodzenia!