Kako odpreti podatke v Excelu z uporabo Power Query (aka Get & Transform)

Oglejte si video - Najhitrejši način odmikanja podatkov v Excelu

Vrtilne tabele so odlične, če želite v nekaj sekundah analizirati ogromno količino podatkov. Omogoča tudi hitro ustvarjanje različnih pogledov podatkov s preprostim vlečenjem in spuščanjem.

Če želite ustvariti vrtilno tabelo, morate imeti podatke v posebni obliki pripravljene za vrtilno tabelo.

V mnogih primerih boste verjetno dobili podatke v oblikah, ki niso pripravljene za vrtilno tabelo.

To se pogosto zgodi, ko nekdo ročno zbira podatke in ustvari obliko, ki je za ljudi bolj berljiva (ne vrtilnih tabel).

Nekaj, kot je prikazano spodaj:

Zgornji format podatkov je nekaj, kar pričakujete kot rezultat analize vrtilne tabele.

Kaj pa, če želite analizirati te iste podatke in videti, kakšna je bila skupna prodaja po posameznih regijah ali po posameznih mesecih.

Čeprav je to enostavno mogoče storiti s pomočjo vrtilnih tabel, na žalost zgornjih podatkov ne morete vnesti v vrtilno tabelo.

Zato morate odkleniti podatke in jih narediti prijazne do vrtilne tabele.

Čeprav obstaja nekaj načinov za to z uporabo Excelove formule ali VBA, je Power Query (Get & Transform v Excelu 2016) najboljše orodje za odmikanje podatkov.

Odstranite podatke s pomočjo Power Query

Tu so koraki za odmikanje podatkov z uporabo Power Query:

(Če so vaši podatki že v Excelovi tabeli, začnite od 6. koraka dalje)

1. Izberite katero koli celico v naboru podatkov.
2. Pojdite na zavihek Vstavi.
3. Kliknite ikono tabele.
4. V pogovornem oknu »Ustvari tabelo« se prepričajte, da je obseg pravilen. Po potrebi lahko spremenite obseg.
5. Kliknite V redu. S tem boste pretvorili svoje tabelarne podatke v Excelovo tabelo.
6. Pri kateri koli celici, izbrani v Excelovi tabeli, kliknite zavihek Podatki.
7. V podatkovni skupini Get & Transform kliknite ikono 'From Table/Range'.
8. V pogovornem oknu Ustvari tabelo, ki se odpre (če se odpre), kliknite V redu. S tem se odpre urejevalnik poizvedb s podatki v Excelovi tabeli.
9. V urejevalniku poizvedb z desno tipko miške kliknite stolpec Regija.
10. Kliknite možnost »Odstrani druge stolpce«. To bo v trenutku odstranilo vaše podatke.
11. Spremenite ime stolpca »Atribut« v bolj smiselno ime, na primer »Meseci«.
12. Ko imate podatke Unpivoted, je dobro, da se prepričate, da so vse vrste podatkov pravilne. V tem primeru kliknite eno celico za vsak stolpec in si oglejte vrsto podatkov na zavihku Transform. Po potrebi lahko spremenite tudi vrsto podatkov.
13. (Izbirno) Spremenite ime poizvedbe v »Prodaja«.
14. Pojdite na zavihek Domov (v urejevalniku poizvedb).
15. Kliknite Zapri in naloži.

Zgornji koraki bi razveljavili vaš nabor podatkov z uporabo Power Query in ga vrnili v Excel kot tabelo na novem delovnem listu.

Zdaj lahko te podatke uporabite za ustvarjanje različnih pogledov s pomočjo vrtilne tabele. Skupno vrednost prodaje lahko na primer preverite po mesecih ali po regijah.

Osveževanje poizvedbe, ko so dodani novi podatki

Vse to dobro deluje.

Kaj pa se zgodi, ko v naš prvotni nabor podatkov dodamo nove podatke.

Recimo, da dobite podatke za julij, ki so v isti obliki, kot smo jo začeli.

Ali moram znova ponoviti vse korake, da te podatke vključim v svoj neobdelani nabor podatkov?

Odgovor je NE.

In to je tisto, kar je tako super pri Power Queryju. Še naprej lahko dodajate nove podatke (ali spreminjate obstoječe), Power Query pa bi jih takoj posodobil, takoj ko jih osvežite.

Naj vam pokažem, kako.

Recimo, da je spodaj novi niz podatkov, ki ga dobim (ki vsebuje dodatne podatke za julij):

Tu so koraki za osvežitev že ustvarjene poizvedbe in odmik teh podatkov:

1. Te nove podatke dodajte izvirnim podatkom, ki ste jih uporabili za ustvarjanje poizvedbe.
2. Ker podatke dodajate v sosednji stolpec Excelove tabele, se bo Excelova tabela razširila in vanj vključila te podatke. Če slučajno ne, naredite to ročno, tako da povlečete majhno obrnjeno ikono 'L' v spodnjem desnem kotu Excelove tabele.
3. Pojdite na zavihek Podatki in kliknite Poizvedbe in povezave. To bo prikazalo podokno z vsemi obstoječimi poizvedbami.
4. Z desno miškino tipko kliknite podatek o prodaji v podoknu poizvedbe.
5. Kliknite Osveži.

To je to! Vaši novi podatki se takoj odstranijo in dodajo obstoječim.

Opazili boste, da je število vrstic, prikazanih v poizvedbi, posodobljenih, da vam prikažejo nove številke. V tem primeru je bilo pred osvežitvijo 24, po osvežitvi pa 28.

To tudi pomeni, da če ste ustvarili vrtilne tabele z uporabo podatkov, pridobljenih iz programa Power Query, bi se te vrtilne tabele osvežile in prikazale posodobljene rezultate.