Videó megtekintése - A leggyorsabb módja az adatok kibontásának Excelben
A pivot táblázatok nagyszerűek, ha hatalmas mennyiségű adatot szeretne elemezni másodpercek alatt. Ezenkívül lehetővé teszi az adatok különböző nézeteinek gyors létrehozását egyszerűen húzással.
Pivot tábla létrehozásához az adatoknak egy adott Pivot Table kész formátumban kell lennie.
Sok esetben valószínűleg olyan formátumban kapja meg az adatokat, amelyek nem állnak készen a kimutatástáblára.
Gyakran ez a helyzet, amikor valaki manuálisan gyűjt adatokat, és olyan formátumot hoz létre, amely jobban olvasható az emberek számára (nem a Pivot táblázatok).
Valami az alábbiak szerint:
A fenti adatformátum a Pivot Table elemzés kimenete.
Mi van akkor, ha ezeket az adatokat elemezni szeretné, és látni szeretné, hogy mennyi volt az összes értékesítés régiónként vagy havonta.
Bár ez könnyen elvégezhető a kimutatástáblák használatával, sajnos nem tudja betölteni a fenti adatokat egy kimutatástáblába.
Tehát le kell választania az adatokat, és a Pivot Table -t barátságossá kell tennie.
Bár van néhány módszer erre Excel képlet vagy VBA használatával, a Power Query (Get & Transform in Excel 2016) a legjobb eszköz az adatok feloldásához.
Az adatok feloldása a Power Query használatával
Íme a lépések az adatok kibontásához a Power Query használatával:
(Ha az adatok már szerepelnek Excel táblázatban, kezdje a 6. lépéstől)
- Válassza ki az adathalmaz bármely celláját.
- Lépjen a Beszúrás fülre.
- Kattintson a Táblázat ikonra.
- A „Táblázat létrehozása” párbeszédpanelen ellenőrizze, hogy a tartomány helyes -e. Szükség esetén módosíthatja a tartományt.
- Kattintson az OK gombra. Ezzel a táblázatos adatokat Excel táblává alakítja át.
- Ha az Excel táblázat bármely celláját kijelölte, kattintson az Adatok fülre.
- Az Adatok lekérése és átalakítása csoportban kattintson a „Táblázatból/tartományból” ikonra.
- A megnyíló Táblázat létrehozása párbeszédpanelen (ha megnyílik) kattintson az OK gombra. Ezzel megnyílik a Lekérdezésszerkesztő az Excel táblázat adataival.
- A Lekérdezésszerkesztőben kattintson a jobb gombbal a Régió oszlopra.
- Kattintson az „Egyéb oszlopok feloldása” lehetőségre. Ez azonnal megszünteti az adatok törlését.
- Módosítsa az „Attribútum” oszlop nevét értelmesebb névre, például „Hónapok”.
- Ha megvannak a Pivoted adatok, jó gyakorlat, ha meggyőződik arról, hogy az adattípusok helyesek -e. Ebben a példában kattintson minden oszlopra egy cellára, és nézze meg az adattípust az Átalakítás lapon. Ha szükséges, megváltoztathatja az adattípust is.
- (Nem kötelező) Módosítsa a lekérdezés nevét „Értékesítés” -re.
- Lépjen a Kezdőlap lapra (a lekérdezésszerkesztőben).
- Kattintson a Bezárás és betöltés gombra.
A fenti lépések kibontják az adatkészletet a Power Query használatával, és visszahelyezik az Excelbe táblázatként egy új munkalapon.
Most ezekkel az adatokkal különböző nézeteket hozhat létre egy kimutatástábla segítségével. Például ellenőrizheti a teljes értékesítési értéket hónap vagy régió szerint.
A lekérdezés frissítése új adatok hozzáadása esetén
Ez mind jól működik.
De mi történik, ha új adatokat adunk hozzá az eredeti adatkészletünkhöz.
Tegyük fel, hogy júliusra vonatkozó adatokat kap, amelyek ugyanabban a formátumban vannak, mint amellyel elkezdtük.
Újra meg kell ismételnem az összes lépést ahhoz, hogy ezeket az adatokat bele lehessen foglalni a nem forgatott adatkészletembe?
A válasz nem.
És ez az, ami annyira fantasztikus a Power Queryben. Folytathatja az új adatok hozzáadását (vagy módosíthatja a meglévő adatokat), és a Power Query azonnal frissíti, amint frissíti.
Hadd mutassam meg, hogyan.
Tegyük fel, hogy az alábbiakban az új adatkészletet kapom (amely további adatokat tartalmaz júliusra):
Az alábbi lépésekkel frissítheti a már létrehozott lekérdezést, és megszüntetheti az adatok törlését:
- Adja hozzá ezeket az új adatokat a lekérdezés létrehozásához használt eredeti adataihoz.
- Mivel adatokat ad hozzá az Excel -táblázat szomszédos oszlopához, az Excel -táblázat kibővül, és tartalmazza ezeket az adatokat. Ha nem véletlenül, akkor manuálisan tegye meg az Excel-táblázat jobb alsó sarkában található kis, fordított „L” ikon húzásával.
- Lépjen az Adatok fülre, és kattintson a Lekérdezések és kapcsolatok elemre. Ekkor megjelenik egy ablaktábla, amely tartalmazza az összes létező lekérdezést.
- Kattintson a jobb gombbal az Értékesítési lekérdezésre a Lekérdezések panelen.
- Kattintson a Frissítés gombra.
Ez az! Az új adatok azonnal leválasztásra kerülnek, és hozzáadódnak a meglévő adatokhoz.
Észre fogja venni, hogy a Lekérdezésben megjelenített sorok száma frissíti az új számokat. Ebben a példában 24 volt a frissítés előtt, és 28 lett a frissítés után.
Ez azt is jelenti, hogy ha létrehozott bármilyen kimutatástáblát a Power Query -ből kapott adatok felhasználásával, akkor ezek a kimutatástáblák is frissülnek, hogy megjelenjenek a frissített eredmények.
