2 January 2024

Łączenie danych w tabeli – Query

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.

Łączenie danych w tabeli – Query
theme-w-ico
theme-ico

Opis filmu

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

Pobierz darmowy materiał szkoleniowy:

Dzielimy się
wiedzą

Nasza Firma zapewnia nie tylko najnowsze rozwiązania z dziedziny IT ale również nie pozostaje w miejscu każdego dnia poszerza swoją wiedzę którą bardzo chętnie dzielimy się z innymi. Zapraszamy do zapoznania się z ostatnimi nowinkami.

theme-w-ico
arrows-ico-wh
Power automate dla praktyków – odcinek 1.

Platforma Microsoft 365 oferuje nam gamę aplikacji których sprawne wykorzystanie umożliwia realizację całych procesów w organizacji.

 

 

theme-w-ico
arrows-ico-wh
Predefiniowane Typy Danych w Excelu

Zobacz jak wykorzystać wbudowane mechanizmy Excela do uzyskania stałego dostępu do danych bez konieczności żmudnego ich poszukiwania w sieci.

theme-w-ico
arrows-ico-wh
Najczęstsze błędy popełniane w Excelu

Dzisiaj pokażemy, w jakich miejscach występują najczęstsze błędy popełniane w Excelu i jak ich uniknąć.

theme-w-ico
theme-ico

Poznajmy się
i porozmawiajmy!

arrows-b

Skontaktuj się z nami i otwórz drzwi do innowacyjnych rozwiązań technologicznych dla Twojej firmy. Nasz zespół ekspertów jest gotów odpowiedzieć na Twoje pytania, dostosować ofertę do Twoich potrzeb i wesprzeć Cię w osiągnięciu sukcesu.

Wykorzystaj potencjał technologii już dziś, by zwiększyć efektywność, rozwijać biznes i zadbać o bezpieczeństwo swoich danych.

zespół
theme-ico