Vaadake videot - kiireim viis Exceli andmete lahtiühendamiseks
Pivot -tabelid on suurepärased, kui soovite analüüsida tohutul hulgal andmeid sekunditega. See võimaldab teil ka lihtsalt lohistades kiiresti erinevaid andmevaateid luua.
Pivot -tabeli loomiseks peavad teil olema andmed kindla Pivot -tabeli vormingus.
Paljudel juhtudel saate tõenäoliselt andmed vormingutes, mis pole Pivot Tabeli jaoks valmis.
Sageli juhtub see siis, kui keegi kogub andmeid käsitsi ja loob inimestele paremini loetava vormingu (mitte Pivot Tables).
Midagi, nagu allpool näidatud:
Ülaltoodud andmevorming on midagi, mida ootate Pivot Tabeli analüüsi väljundina.
Mis siis, kui soovite neid samu andmeid analüüsida ja vaadata, milline oli kogumüük iga piirkonna või iga kuu kohta.
Kuigi seda saab hõlpsasti teha pöördetabelite abil, ei saa kahjuks ülaltoodud andmeid pöördetabelisse sisestada.
Seega peate andmed lahti võtma ja muutma need liigendtabeli jaoks sobivaks.
Kuigi Exceli valemi või VBA abil saab seda teha mitmel viisil, on Power Query (Get & Transform in Excel 2016) parim vahend andmete eemaldamiseks.
Andmete eemaldamine Power Query abil
Power Query abil andmete lahtiühendamiseks toimige järgmiselt.
(Kui teie andmed on juba Exceli tabelis, alustage 6. sammust)
- Valige andmekogus suvaline lahter.
- Minge vahekaardile Lisa.
- Klõpsake tabeliikooni.
- Veenduge dialoogiboksis „Loo tabel”, et vahemik on õige. Vajadusel saate vahemikku muuta.
- Klõpsake nuppu OK. See teisendab teie tabeliandmed Exceli tabeliks.
- Kui Exceli tabelis on valitud mõni lahter, klõpsake vahekaarti Andmed.
- Klõpsake andmete hankimise ja teisendamise rühmas ikooni „Tabelist/vahemikust”.
- Avanevas dialoogiboksis Tabeli loomine (kui see avaneb) klõpsake nuppu OK. See avab päringiredaktori, kasutades Exceli tabeli andmeid.
- Päringu redaktoris paremklõpsake veerul Piirkond.
- Klõpsake suvandil „Unpivot Other veerud”. See tühistab teie andmed koheselt.
- Muutke veeru „Atribuut” nimi sisukamaks, näiteks „Kuud”.
- Kui teil on unpivoted andmed, on hea tava veenduda, et kõik andmetüübid on õiged. Selles näites klõpsake iga veeru jaoks ühte lahtrit ja vaadake andmetüüpi vahekaardil Teisenda. Vajadusel saate ka andmetüüpi muuta.
- (Valikuline) Muutke oma päringu nimeks „Müük”.
- Minge vahekaardile Avaleht (päringute redaktoris).
- Klõpsake nuppu Sule ja laadi.
Ülaltoodud toimingud tühistaksid teie andmekogumi Power Query abil ja paneksid uue Exceli tabeli tabelisse tagasi.
Nüüd saate neid andmeid kasutada Pivot -tabeli abil erinevate vaadete loomiseks. Näiteks saate kontrollida müügi koguväärtust kuude või piirkondade kaupa.
Päringu värskendamine uute andmete lisamisel
See kõik töötab hästi.
Mis saab aga siis, kui meie esialgsesse andmekogumisse lisatakse uusi andmeid.
Oletame, et saate andmeid juuli kohta, mis on samas vormingus kui see, millega alustasime.
Kas mul on vaja kõiki toiminguid uuesti korrata, et need andmed oma pöördeta andmekogumisse lisada?
Vastus on EI.
Ja see on Power Query puhul nii äge. Saate jätkata uute andmete lisamist (või olemasolevate andmete muutmist) ja Power Query värskendab neid kohe pärast värskendamist.
Las ma näitan teile, kuidas.
Oletame, et allpool on uus andmekogum, mille saan (millel on juuli kohta täiendavaid andmeid):
Siin on juhised juba loodud päringu värskendamiseks ja nende andmete tühistamiseks.
- Lisage need uued andmed algsetele andmetele, mida kasutasite päringu loomisel.
- Kuna lisate andmeid Exceli tabeli külgnevasse veergu, laieneb Exceli tabel, et need andmed sinna lisada. Kui seda juhuslikult ei juhtu, tehke seda käsitsi, lohistades Exceli tabeli paremas alanurgas väikest ümberpööratud L-ikooni.
- Minge vahekaardile Andmed ja klõpsake nuppu Päringud ja ühendused. See näitab paani, kus on kõik olemasolevad päringud.
- Paremklõpsake paanil Päringud müügipäringut.
- Klõpsake nuppu Värskenda.
See on kõik! Teie uued andmed eemaldatakse koheselt ja lisatakse olemasolevatele andmetele.
Märkasite, et päringus näidatud ridade arv värskendatakse uute numbrite kuvamiseks. Selles näites oli enne värskendamist 24 ja pärast värskendamist 28.
See tähendab ka seda, et kui olete loonud Power Queryst saadud andmete põhjal mis tahes liigendtabelid, värskendatakse ka neid liigendtabeleid, et kuvada teile värskendatud tulemusi.
