W 17 odcinku pokazywałem Wam jak w prosty sposób połączyć takie same tabele za pomocą funkcji Konsoliduj. Co zrobić jednak gdy nasze tabele nie są takie same choć zawierają podobne dane. Oczywiście możemy ręcznie postarać się je zunifikować jednak nie po to mamy Excela aby coś robić ręcznie.
W naszym pliku znajdują się 4 przykładowe tabele w 4 różnych arkuszach. Każda z nich zawiera pesel, imię i nazwisko oraz kwotę wypłaty z poszczególnego kwartału jednak w każdej z nich znajdziemy dodatkowe utrudnienia uniemożliwiające użycie bezpośrednio funkcji Konsoliduj. Będziemy dążyli do połączenia naszych czterech tabel w jedną zawierającą dane o wysokości wszystkich wypłaconych premii. Dla lepszej przejrzystości poszczególnych kroków wykonamy każde przejście osobno.
Tabela w arkuszu Odcinek018_1 wygląda następująco:
……………………..
Jak widać dane znajdują się w 2 kolumnach, częściowo są oddzielone przecinkami a co druga linijka zawiera dowolne teksty które nas nie interesują. Zaimportujmy tą tabelę jeszcze raz dokonując przy okazji jej przekształceń. Użyjemy Menu -> Dane -> Pobierz dane -> Z innych źródeł -> Z tabeli/zakresu
Pojawi nam się dodatek Power Query za pomocą którego będziemy mogli :
Wybierzmy Menu -> Usuń wiersze -> Usuwanie naprzemiennych wierszy
I wypełnijmy odpowiednio wzorzec usuwania wierszy: rozpoczynając od 2 wiersza usuwamy 1 wiersz i zostawiamy 1.
Następnie rozdzielimy kolumny przy pomocy Manu -> Podziel kolumny -> Według ogranicznika
Jako ogranicznik wybieramy Przecinek i nasza tabela zaczyna wyglądać zgodnie z naszymi oczekiwaniami
Więc importujemy ją do pliku funkcją Zamknij i załaduj
Tabela w arkuszu Odcinek018_2 wygląda podobnie jednak część danych jest rozdzielona spacjami a niektóre wiersze są puste.
Znowu wybieramy Menu -> Dane -> Pobierz dane -> Z innych źródeł -> Z tabeli/zakresu
Tym razem za pomocą Manu -> Usuń wiersze -> Usuń puste wiersze
Pozostawimy tylko wiersze z danymi i analogicznie jak poprzednio rozdzielimy kolumny stosując Manu -> Podziel kolumny -> Według ogranicznika którym tym razem będzie Spacja
Na końcu znowu zastosujemy Zamknij i załaduj.
Nasza trzecia tabela z arkusza Odcinek018_3 zawiera dodatkową kolumnę której musimy się pozbyć
Uruchamiamy jak poprzednio import danych i w Power Query wybieramy Menu-> Usuń Kolumny zaznaczając wcześniej kolumnę B
Teraz jak w poprzednich przypadkach możemy rozdzielić kolumny wybierając jako separator Przecinek i załadować dane.
Ostatnia tabela w arkuszu Odcinek018_4 ma zamienioną kolejność kolumn z których część jest rozdzielona Średnikami.
Jak poprzednio uruchamiamy -> Dane -> Pobierz dane -> Z innych źródeł -> Z tabeli/zakresu. W Power Query przeciągamy nagłówek kolumny Premia Q4 na prawą stronę kolumny Osoba i znowu rozdzielamy kolumny stosując jako separator średnik.
Ostatecznie uzyskujemy 4 nowe tabele w których dane są już poukładane w analogiczny sposób i z których każda zawiera inną ilość wierszy. Zapytania które przekształciły nasze pierwotne tabele są wymienione z prawej strony naszych arkuszy.
W kolejnym kroku połączymy te zapytania w jedno łącząc nasze tabele do pojedynczej tabeli. Klikamy dwukrotnie na dowolne zapytanie i po otwarciu Power Query wybieramy Menu -> Dołącz zapytanie -> Dołącz zapytanie jako nowe.
Następnie wybieramy opcję Co najmniej trzy tabele i dodajemy brakujące tabele do okienka Tabele do dołączenia.
Po wybraniu OK oraz Zamknij i załaduj otrzymamy pojedynczą tabelę w której dodane zostały wiersze ze wszystkich 4 tabel. W takim układzie możemy posortować dane po kolumnie Osoba.1 i dane dla każdej osoby będą ułożone jedna pod drugą
Efekt jest zbliżony do oczekiwanego jednak my chcielibyśmy mieć dla każdej osoby tylko 1 linijkę. W tym celu wchodzimy w zapytanie Dołączenie1 i użyjemy Menu -> Grupowanie według
W menu które się pojawi wybieramy dla naszego przypadku opcję Zaawansowane i dodajemy grupowania po kolumnach Osoba.1 do 3 natomiast sumowanie po kolumnach Premia Q1 do Q4
Wybranie OK oraz Zamknij i załaduj powoduje wyświetlenie oczekiwanej przez nas tabeli zawierającej dane z pierwotnych czterech tabel
Dodatkowym atutem zastosowania Power Query oprócz możliwości przekształcania poszczególnych tabel indywidualnie jest fakt, że nasze połączenie jest dynamiczne czyli po zmianie danych w tabelach pierwotnych wystarczy odświeżyć naszą tabelę docelową aby dane zostały uaktualnione.
Mam nadzieję, że dostrzeżecie potencjał tego rozwiązania i możliwość jego zastosowania w Waszej codziennej pracy. Gdybyście mieli podobne lub inne problemy z łączeniem danych w Excelu jak zawsze zapraszam do korespondencji na adres konrad.pogodz@itch.pl
Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.
