Združite tabele v Excelu z uporabo Power Query (enostaven vodnik po korakih)

S programom Power Query je delo s podatki, razpršenimi po delovnih listih ali celo delovnih zvezkih, postalo lažje.

Ena od stvari, pri kateri lahko Power Query prihrani veliko časa, je, ko morate združiti tabele različnih velikosti in stolpcev na podlagi ujemajočega se stolpca.

Spodaj je video, kjer natančno pokažem, kako združiti tabele v Excelu z uporabo Power Query.

Če želite besedilo raje brati kot gledati video, so spodaj napisana navodila.

Recimo, da imate tabelo, kot je prikazano spodaj:

Ta tabela vsebuje podatke, ki jih želim uporabiti, vendar še vedno manjkata dva pomembna stolpca - „ID izdelka“ in „Regija“, kjer deluje prodajni predstavnik.

Ti podatki so na voljo kot ločene tabele, kot je prikazano spodaj:

Če želite vse te podatke združiti v eno samo tabelo, boste morali združiti te tri tabele, da boste lahko nato ustvarili vrtilno tabelo in jo analizirali ali uporabili za druge namene poročanja/nadzorne plošče.

In z združitvijo ne mislim na preprosto kopiranje paste.

Ustrezne zapise iz tabele 1 boste morali preslikati s podatki iz tabel 2 in 3.

Zdaj se lahko zanesete na VLOOKUP ali INDEX/MATCH.

Če pa ste čarovnik VBA, lahko za to napišete kodo.

Toda te možnosti so zamudne in zapletene v primerjavi z Power Query.

V tej vadnici vam bom pokazal, kako združiti te tri Excelove tabele v eno.

Če želite, da ta tehnika deluje, morate imeti povezovalne stolpce. Na primer v tabeli 1 in tabeli 2 je skupni stolpec „postavka“, v tabeli 1 in tabeli 3 pa skupni stolpec „prodajni predstavnik“. Upoštevajte tudi, da se v teh povezovalnih stolpcih ne sme ponoviti.

Opomba: Power Query je mogoče uporabiti kot dodatek v Excelih 2010 in 2013 in je vgrajena funkcija od Excela 2016 naprej. Glede na vašo različico so lahko nekatere slike videti drugače (posnetki slik, uporabljeni v tej vadnici, so iz Excela 2016).

Združite tabele z uporabo Power Query

Te tabele sem poimenoval, kot je prikazano spodaj:

1. Tabela 1 - Podatki o prodaji
2. Tabela 2 - Pdt_Id
3. Tabela 3 - Regija

Preimenovanje teh tabel ni obvezno, vendar je bolje dati imena, ki opisujejo, o čem govori tabela.

Naenkrat lahko v Power Query združite samo dve tabeli.

Tako bomo morali najprej združiti tabelo 1 in tabelo 2, nato pa vanj v naslednjem koraku združiti tabelo 3.

Združitev tabele 1 in tabele 2

Če želite združiti tabele, jih morate najprej pretvoriti v povezave v programu Power Query. Ko imate povezave, jih lahko preprosto združite.

Spodaj so navedeni koraki za shranjevanje tabele Excel kot povezave v Power Query:

1. Izberite katero koli celico v tabeli Sales_Data.
2. Kliknite zavihek Podatki.
3. V skupini Get & Transform kliknite »From Table/Range«. S tem se odpre urejevalnik poizvedb.
4. V urejevalniku poizvedb kliknite zavihek »Datoteka«.
5. Kliknite možnost »Zapri in naloži v«.
6. V pogovornem oknu »Uvozi podatke« izberite »Samo ustvari povezavo«.
7. Kliknite V redu.

Zgornji koraki bi ustvarili povezavo z imenom Sales_Data (ali katerim koli imenom, ki ste ga dali Excelovi tabeli).

Zgornje korake ponovite za tabelo 2 in tabelo 3.

Ko boste končali, boste imeli tri povezave (z imenom Sales_Data, Pdt_Id in Region).

Zdaj pa poglejmo, kako združiti tabelo Sales_Data in Pdt_Id.

1. Kliknite zavihek Podatki.
2. V skupini Get & Transform Data kliknite Get Data.
3. V spustnem meniju kliknite Združi poizvedbe.
4. Kliknite na Združi. S tem se odpre pogovorno okno Merge.
5. V pogovornem oknu Merge v prvem spustnem meniju izberite »Sales_Data«.
6. V drugem spustnem meniju izberite „Pdt_Id“.
7. V predogledu »Podatki o prodaji« kliknite stolpec »Postavka«. S tem boste izbrali celoten stolpec.
8. V predogledu »Pdt_Id« kliknite stolpec »Element«. S tem boste izbrali celoten stolpec.
9. V spustnem meniju »Join Kind« izberite »Left Outer (vse od prvega, ujemanje od drugega)«.
10. Kliknite V redu.

Zgornji koraki bi odprli urejevalnik poizvedb in prikazali podatke iz podatkov Sales_Data z enim dodatnim stolpcem (Pdt_Id).

Združevanje tabel Excel (tabeli 1 in 2)

Zdaj se bo postopek združevanja tabel zgodil v urejevalniku poizvedb z naslednjimi koraki:

1. V dodatnem stolpcu (Pdt_Id) kliknite dvojno puščico v glavi.
2. V polju z možnostmi, ki se odpre, počistite vsa imena stolpcev in izberite samo Element. To je zato, ker v obstoječi tabeli že imamo stolpec z imenom izdelka in želimo le ID izdelka za vsak izdelek.
3. Počistite polje »Uporabi izvirno ime stolpca kot predpono«.
4. Kliknite V redu.

Tako boste dobili nastalo tabelo, ki vsebuje vse zapise iz tabele Sales_Data, in dodaten stolpec, ki vsebuje tudi ID -je izdelkov (iz tabele Pdt_Id).

Če želite združiti samo dve tabeli, lahko naložite ta Excel, končali ste.

Imamo pa tri tabele, ki jih je treba združiti, zato je treba opraviti še več dela.

To dobljeno tabelo morate shraniti kot povezavo (da jo lahko uporabimo za združitev s tabelo 3).

Spodaj so navedeni koraki za shranjevanje te združene tabele (s podatki iz tabele Sales_Data in Pdt_Id) kot povezave:

1. Kliknite zavihek Datoteka
2. Kliknite možnost »Zapri in naloži v«.
3. V pogovornem oknu »Uvozi podatke« izberite »Samo ustvari povezavo«.
4. Kliknite V redu.

Tako boste novo združene podatke shranili kot povezavo. To povezavo lahko preimenujete, če želite.

Združitev tabele 3 z rezultatsko tabelo

Postopek združevanja tretje tabele z nastalo tabelo (ki smo jo dobili z združitvijo tabele 1 in tabele 2) je popolnoma enak.

Tu so koraki za združevanje teh tabel:

1. Kliknite zavihek Podatki.
2. V skupini Get & Transform Data kliknite "Get Data".
3. V spustnem meniju kliknite »Združi poizvedbe«.
4. Kliknite »Združi«. S tem se odpre pogovorno okno Merge.
5. V pogovornem oknu Merge v prvem spustnem meniju izberite "Merge1".
6. V drugem spustnem meniju izberite »Regija«.
7. V predogledu »Merge1« kliknite stolpec »Sales Rep«. S tem boste izbrali celoten stolpec.
8. V predogledu regije kliknite stolpec »Predstavnik prodaje«. S tem boste izbrali celoten stolpec.
9. V spustnem meniju »Join Kind« izberite Left Outer (vse od prvega, ujemanje od drugega).
10. Kliknite V redu.

Zgornji koraki bi odprli urejevalnik poizvedb in prikazali podatke iz Merge1 z enim dodatnim stolpcem (Regija).

Zdaj se bo postopek združevanja tabel zgodil v urejevalniku poizvedb z naslednjimi koraki:

1. V dodatnem stolpcu (Regija) kliknite dvojno puščico v glavi.
2. V polju z možnostmi, ki se odpre, počistite vsa imena stolpcev in izberite samo Regija.
3. Počistite polje »Uporabi izvirno ime stolpca kot predpono«.
4. Kliknite V redu.

Zgornji koraki bi vam dali tabelo, v kateri so združene vse tri tabele (tabela Sales_Data z enim stolpcem za Pdt_Id in enim za regijo).

Tu so koraki za nalaganje te tabele v Excelu:

1. Kliknite zavihek Datoteka.
2. Kliknite »Zapri in naloži v«.
3. V pogovornem oknu »Uvozi podatke« izberite možnosti Tabela in novi delovni listi.
4. Kliknite V redu.

Tako boste dobili nastalo združeno tabelo na novem delovnem listu.

Ena najboljših stvari pri Power Queryju je, da lahko preprosto prilagodite kakršne koli spremembe osnovnih podatkov (tabela 1, 2 in 3), tako da jih preprosto osvežite.

Recimo, na primer, da bodo Lauro premestili v Azijo, za naslednji mesec pa boste dobili nove podatke. Zdaj vam ni treba več ponoviti zgornjih korakov. Vse, kar morate storiti, je, da osvežite mizo in za vas bo vse znova naredila.

V nekaj sekundah boste dobili novo združeno tabelo.

Morda vam bodo všeč tudi naslednje vaje Power Query:

• Združite podatke iz več delovnih zvezkov v Excelu (z uporabo Power Query).
• Združite podatke iz več delovnih listov v en sam delovni list v Excelu.
• Kako odpreti podatke v Excelu z uporabo Power Query (aka Get & Transform)
• Pridobite seznam imen datotek iz map in podmap (z uporabo Power Query)