Szukaj
Close this search box.
Odtwórz wideo

Łączenie danych w tabeli – Query

Udostępnij ten film:

Ocena tego filmu:

5/5 - (6 ocen)

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 :

  • Usunąć wiersze z niepotrzebnym tekstem
  • Rozdzielić na kolumny wartości rozdzielone przecinkami

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

Jak połączyć dane z wielu tabel
Film otagowano jako:
Poznaj autora wideo
Picture of Konrad Pogódź
Konrad Pogódź
Wiceprezes Zarządu
Pobierz darmowy materiał szkoleniowy

Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.

Odkryj podobne filmy

Zgłoszenie serwisowe

Dane firmy

Masz pytanie? Napisz do nas