Spajanje tablica u Excelu pomoću programa Power Query (jednostavan korak-po-korak vodič)

Pomoću programa Power Query rad s podacima raspršenim po radnim listovima ili čak radnim knjigama postao je lakši.

Jedna od stvari na kojima vam Power Query može uštedjeti mnogo vremena je kada morate spojiti tablice različitih veličina i stupaca na temelju odgovarajućeg stupca.

Ispod je videozapis u kojem sam pokazao kako točno spojiti tablice u Excelu pomoću programa Power Query.

U slučaju da radije čitate tekst nego gledate video, dolje su napisane upute.

Pretpostavimo da imate tablicu kao što je prikazano u nastavku:

Ova tablica sadrži podatke koje želim upotrijebiti, ali još uvijek nedostaju dva važna stupca - "ID proizvoda" i "Regija" u kojoj prodajni predstavnik radi.

Ove su informacije navedene kao zasebne tablice kako je prikazano u nastavku:

Da biste sve te podatke unijeli u jednu tablicu, morat ćete spojiti ove tri tablice kako biste zatim mogli stvoriti zaokretnu tablicu i analizirati je ili upotrijebiti u druge svrhe izvješćivanja/nadzorne ploče.

I pod spajanjem, ne mislim na jednostavno copy paste.

Morat ćete preslikati relevantne zapise iz tablice 1 s podacima iz tablica 2 i 3.

Sada se za to možete osloniti na VLOOKUP ili INDEX/MATCH.

Ili ako ste VBA čarobnjak, možete napisati kôd za to.

No, ove su opcije dugotrajne i komplicirane u usporedbi s Power Queryjem.

U ovom vodiču pokazat ću vam kako spojiti ove tri tablice programa Excel u jednu.

Da bi ova tehnika funkcionirala, morate imati povezujuće stupce. Na primjer, u tablici 1. i tablici 2. zajednički stupac je "stavka", a u tablici 1. i tablici 3. zajednički stupac je "prodajni predstavnik". Također imajte na umu da se u tim spojnim stupcima ne smije ponavljati.

Napomena: Power Query se može koristiti kao dodatak u programima Excel 2010 i 2013, a ugrađena je značajka od programa Excel 2016 nadalje. Na temelju vaše inačice, neke slike mogu izgledati drugačije (snimke slika korištene u ovom vodiču su iz programa Excel 2016).

Spoji tablice pomoću Power Queryja

Nazvao sam ove tablice kako je dolje prikazano:

1. Tabela 1 - Podaci o prodaji
2. Tablica 2 - Pdt_Id
3. Tablica 3 - Regija

Nije potrebno preimenovati ove tablice, ali bolje je dati imena koja opisuju o čemu se radi u tablici.

U jednom potezu možete spojiti samo dvije tablice u Power Queryju.

Dakle, prvo ćemo morati spojiti tablicu 1 i tablicu 2, a zatim u sljedećem koraku spojiti tablicu 3 u nju.

Spajanje tablice 1 i tablice 2

Za spajanje tablica, prvo morate pretvoriti te tablice u veze u programu Power Query. Kad uspostavite veze, možete ih jednostavno spojiti.

Evo koraka za spremanje Excel tablice kao veze u Power Queryju:

1. Odaberite bilo koju ćeliju u tablici Podaci o prodaji.
2. Kliknite karticu Podaci.
3. U grupi Get & Transform kliknite "From Table/Range". Ovo će otvoriti uređivač upita.
4. U uređivaču upita kliknite karticu "Datoteka".
5. Kliknite opciju "Zatvori i učitaj u".
6. U dijaloškom okviru "Uvoz podataka" odaberite "Samo stvori vezu".
7. Pritisnite U redu.

Gore navedeni koraci stvorili bi vezu s imenom Sales_Data (ili bilo kojim imenom koje ste dali Excel tablici).

Ponovite gore navedene korake za tablicu 2 i tablicu 3.

Dakle, kad završite, imat ćete tri veze (s imenom Sales_Data, Pdt_Id i Region).

Pogledajmo sada kako spojiti tablicu Sales_Data i Pdt_Id.

1. Kliknite na karticu Podaci.
2. U grupi Get & Transform Data kliknite Get Data.
3. Na padajućem izborniku kliknite Kombiniraj upite.
4. Kliknite na Spajanje. Ovo će otvoriti dijaloški okvir Spajanje.
5. U dijaloškom okviru Spajanje odaberite "Podaci o prodaji" s prvog padajućeg izbornika.
6. Odaberite "Pdt_Id" s drugog padajućeg izbornika.
7. U pregledu "Podaci o prodaji" kliknite stupac "Stavka". Time ćete odabrati cijeli stupac.
8. U pregledu "Pdt_Id" kliknite stupac "Stavka". Time ćete odabrati cijeli stupac.
9. Na padajućem izborniku "Join Kind" odaberite "Left Outer (all from first, matching from second)".
10. Pritisnite U redu.

Gore navedeni koraci otvorili bi uređivač upita i prikazali vam podatke iz Sales_Data s jednim dodatnim stupcem (od Pdt_Id).

Spajanje tablica programa Excel (tablice 1 i 2)

Sada će se proces spajanja tablica dogoditi unutar uređivača upita sa sljedećim koracima:

1. U dodatnom stupcu (Pdt_Id) kliknite dvostruku strelicu u zaglavlju.
2. U okviru opcija koji se otvori poništite sve nazive stupaca i odaberite samo Stavka. To je zato što već imamo stupac naziva proizvoda u postojećoj tablici i želimo samo ID proizvoda za svaki proizvod.
3. Poništite opciju "Koristi izvorni naziv stupca kao prefiks".
4. Pritisnite U redu.

To će vam dati rezultirajuću tablicu koja sadrži svaki zapis iz tablice Sales_Data i dodatni stupac koji također sadrži ID -ove proizvoda (iz tablice Pdt_Id).

Sada, ako samo želite kombinirati dvije tablice, možete učitati ovaj Excel, gotovi ste.

Ali moramo spojiti tri tablice, pa ima još posla za obaviti.

Morate spremiti ovu rezultirajuću tablicu kao vezu (kako bismo je mogli koristiti za spajanje s tablicom 3).

Evo koraka za spremanje ove spojene tablice (s podacima iz tablice Sales_Data i Pdt_Id) kao veze:

1. Kliknite karticu Datoteka
2. Kliknite opciju "Zatvori i učitaj u".
3. U dijaloškom okviru "Uvoz podataka" odaberite "Samo stvori vezu".
4. Pritisnite U redu.

To će spremiti novo spojene podatke kao vezu. Ovu vezu možete preimenovati ako želite.

Spajanje tablice 3 s rezultirajućom tablicom

Postupak spajanja treće tablice s rezultirajućom tablicom (koju smo dobili spajanjem tablice 1 i tablice 2) potpuno je isti.

Evo koraka za spajanje ovih tablica:

1. Kliknite na karticu Podaci.
2. U grupi Get & Transform Data kliknite "Get Data".
3. Na padajućem izborniku kliknite "Kombiniraj upite".
4. Kliknite na "Spajanje". Ovo će otvoriti dijaloški okvir Spajanje.
5. U dijaloškom okviru Spajanje odaberite "Spajanje 1" s prvog padajućeg izbornika.
6. Na drugom padajućem izborniku odaberite "Regija".
7. U pregledu "Merge1" kliknite stupac "Sales Rep". Time ćete odabrati cijeli stupac.
8. U pregledu regije kliknite stupac "Sales Rep". Time ćete odabrati cijeli stupac.
9. Na padajućem izborniku "Join Kind" odaberite Lijevo vanjsko (sve od prvog, podudaranje od drugog).
10. Pritisnite U redu.

Gore navedeni koraci otvorili bi uređivač upita i prikazali podatke iz Merge1 s jednim dodatnim stupcem (regija).

Sada će se proces spajanja tablica dogoditi unutar uređivača upita sa sljedećim koracima:

1. U dodatnom stupcu (Regija) kliknite dvostranu strelicu u zaglavlju.
2. U okviru opcija koji se otvara poništite sve nazive stupaca i odaberite samo Regija.
3. Poništite opciju "Koristi izvorni naziv stupca kao prefiks".
4. Pritisnite U redu.

Gore navedeni koraci dat će vam tablicu u kojoj su sve tri tablice spojene (tablica Sales_Data s jednim stupcem za Pdt_Id i jednim za regiju).

Evo koraka za učitavanje ove tablice u Excel:

1. Kliknite karticu Datoteka.
2. Kliknite na "Zatvori i učitaj u".
3. U dijaloškom okviru "Uvoz podataka" odaberite opcije Tablica i Novi radni listovi.
4. Pritisnite U redu.

Tako ćete dobiti dobivenu spojenu tablicu na novom radnom listu.

Jedna od najboljih stvari u vezi s Power Queryjem je ta što možete jednostavno prilagoditi sve promjene u temeljnim podacima (tablice 1, 2 i 3) jednostavnim osvježavanjem.

Na primjer, pretpostavimo da će Laura biti prebačena u Aziju i da ćete dobiti nove podatke za sljedeći mjesec. Sada ne morate ponovno ponavljati gore navedene korake. Sve što trebate učiniti je osvježiti stol i sve će to učiniti za vas.

U roku od nekoliko sekundi imat ćete novu spojenu tablicu.

Možda će vam se svidjeti i sljedeći udžbenici Power Query:

• Kombinirajte podatke iz više radnih knjiga u Excelu (pomoću programa Power Query).
• Kombinirajte podatke s više radnih listova u jedan radni list u Excelu.
• Kako otkinuti podatke u Excelu pomoću programa Power Query (poznat i kao Get & Transform)
• Nabavite popis imena datoteka iz mapa i podmapa (pomoću programa Power Query)