Forrásadatok előkészítése a kimutatástáblához

Az adatok megfelelő formátumban való elhelyezése kulcsfontosságú lépés egy robusztus és hibamentes kimutatás létrehozásában. Ha nem a megfelelő módon jár el, akkor sok probléma merülhet fel a pivot táblával.

Mi a jó tervezés a Pivot tábla forrásadataihoz?

Nézzünk egy példát a Pivot tábla jó forrásadataira.

Ez teszi jó forrásadat -tervezéssé:

• Az első sor fejléceket tartalmaz, amelyek leírják az oszlopok adatait.
• Minden oszlop egyedi adatkategóriát képvisel. Például a C oszlop csak a termékadatokat, a D oszlop és a hónap adatait tartalmazza.
• Minden sor egy rekord, amely a tranzakció vagy eladás egy példányát reprezentálja.
• Az adatfejlécek egyediek, és nem ismétlődnek sehol az adathalmazban. Például, ha van értékesítési száma négy negyedévre egy évben, akkor NE nevezze meg ezeket értékesítésnek. Ehelyett adjon egyedi oszlopfejléceket ezeknek az oszlopfejléceknek, például Értékesítés Q1, Értékesítési Q2 stb.
  • Ha nem rendelkezik egyedi címekkel, továbbra is létrehozhat egy kimutatástáblát, és az Excel automatikusan egyedivé teszi ezeket utótag hozzáadásával (például Értékesítés, Értékesítés2, Értékesítés3). Ez azonban szörnyű módja lenne a Pivot táblázat elkészítésének és használatának.

Gyakori buktatók, amelyeket el kell kerülni a forrásadatok előkészítése során

• A forrásadatokban nem lehet üres oszlop. Ezt könnyű észrevenni. Ha üres oszlop van a forrásadatokban, nem tud létrehozni egy kimutatástáblát. Hiba jelenik meg az alábbiak szerint.
• A forrásadatokban nem lehet üres cellák/sorok. Annak ellenére, hogy sikeresen létrehozhat egy kimutatástáblát annak ellenére, hogy üres cellái vagy sorai vannak, számos mellékhatás jelentkezhet a nap későbbi szakaszában.
  • Tegyük fel például, hogy üres cella van az értékesítési oszlopban. Ha ezen adatok felhasználásával létrehoz egy kimutatástáblát, és az értékesítési mezőt az oszlopok területére helyezi, akkor a COUNT jelenik meg, és nem az SUM. Ez azért van, mert az Excel az egész oszlopot szöveges adatként értelmezi (csak egyetlen üres cella miatt).
• Alkalmazza a megfelelő formátumot a forrásadatok celláira. Például, ha dátumokkal rendelkezik (amelyeket sorszámként tárol az Excel háttéren), alkalmazza az egyik elfogadható dátumformátumot. Ez segít létrehozni a kimutatástáblát, és a dátumot az adatok összegzésének, csoportosításának és rendezésének egyik kritériumaként használni.
  • Ha van pár másodperce, próbálja meg ezt. Formázza a dátumokat a kimutatástáblában számokként, majd hozzon létre egy kimutatástáblát ezen adatok felhasználásával. Most a kimutatástáblában válassza ki a dátummezőt, és nézze meg, mi történik. Ez automatikusan az értékek területére kerül. Ennek az az oka, hogy a Pivot táblázat nem tudja, hogy ezek dátumok. Ezeket számként értelmezi.
• A forrásadatok részeként ne tartalmazzon oszlopösszegeket, sorok összesítéseit, átlagokat stb. Ha megvan a Pivot tábla, később könnyen beszerezheti ezeket.
• Mindig hozzon létre egy Excel -táblázatot, majd használja azt a Pivot tábla forrásaként. Ez inkább jó gyakorlat, és nem buktató. A Pivot táblázat tökéletesen működne olyan forrásadatokkal, amelyek nem Excel táblázatok. Az Excel tábla előnye, hogy módosíthatja a bővülő adatokat. Ha további sorokat ad hozzá az adatkészlethez, nem kell újra és újra módosítania a forrásadatokat. Egyszerűen frissítheti a kimutatástáblát, és az automatikusan figyelembe veszi a forrásadatokhoz hozzáadott új sorokat.

Példák a rossz forrásadatok tervezésére

Nézzünk néhány rossz példát a forrásadatok tervezésére.

Rossz forrásadatok tervezése - 1. példa

Ez egy általános módszer az adatok karbantartására, mivel könnyen követhetők és érthetők. Ezzel az adatrendezéssel két probléma van:

• Nem kapja meg a teljes képet. Láthatja például, hogy az 1. negyedévben a Mid West eladásai 2924300. De vajon ez egyetlen értékesítés, vagy több értékesítés. Ha minden rekord külön sorban áll rendelkezésre, akkor jobb elemzést végezhet.
• Ha továbblép, és létrehoz egy pivot táblát ezzel (amit megtehet), akkor különböző mezőket kap a különböző negyedévekhez. Valami az alábbiak szerint:

Rossz forrásadatok tervezése - 2. példa

Ezt az adatábrázolást a menedzsment és a PowerPoint prezentációk közönsége jól fogadhatja, de nem alkalmas pivot tábla létrehozására.

Ismételten, ez az a fajta összefoglaló, amelyet könnyedén létrehozhat egy kimutatástábla segítségével. Tehát még ha végül is ilyen kinézetre vágyik az adataira, tartsa a forrásadatokat Pivot -kész formátumban, és hozza létre ezt a nézetet a Pivot tábla használatával.

Rossz forrásadatok tervezése - 3. példa

Ez ismét egy kimenet, amely könnyen beszerezhető a Pivot tábla segítségével. De nem használható pivot tábla létrehozására.

Üres cellák vannak az adathalmazban, és a negyedek oszlopfejlécekként oszlanak el.

Ezenkívül a régió a tetején van megadva, miközben minden rekord része kell, hogy legyen.

[ESETTANULMÁNY] Rosszul formázott adatok konvertálása Pivot Table Ready forrásadatokká

Előfordulhat, hogy olyan adatkészletet kap, amely alkalmatlan a Pivot tábla forrásadataihoz. Ilyen esetben lehet, hogy nincs más választása, mint az adatokat Pivot -barát adatformátummá konvertálni.

Íme egy példa a rossz adattervezésre:

Mostantól az Excel Functions vagy a Pivot Query segítségével konvertálhatja ezeket az adatokat olyan formátumba, amely a Pivot tábla forrásadataiként használható.

Lássuk, hogyan működik mindkét módszer.

Módszer: Excel képletek használata

Lássuk, hogyan használhatjuk az Excel függvényeket ezen adatok Pivot Table ready formátumba konvertálására.

• Hozzon létre egyedi oszlopfejlécet az eredeti adatkészlet összes kategóriájához. Ebben a példában régió, negyed és értékesítés lehet.
• A Régió fejléc alatti cellában használja a következő képletet: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (SOROK ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
  • Húzza le a képletet, és megismétli az összes régiót.
• A Negyed fejléce alatti cellában használja a következő képletet: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (SOROK ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
  • Húzza le a képletet, és megismétli az összes negyedet.
• Az Értékesítés alatti fejlécben használja a következő képletet: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0) ))
  • Húzza lefelé az összes érték megjelenítéséhez. Ez a képlet a Régió és a negyedév adatait használja keresési értékként, és az értékesítési értéket adja vissza az eredeti adatkészletből.

Most ezeket a kapott adatokat használhatja a Pivot tábla forrásadataiként.

Kattintson ide a mintafájl letöltéséhez.

2. módszer: A Power Query használata

A Power Query rendelkezik egy olyan funkcióval, amely könnyen átalakíthatja az ilyen típusú adatokat Pivot -kész adatformátummá.

Ha Excel 2016 -ot használ, a Power Query funkciói a Letöltés és átalakítás csoport Adatok lapján érhetők el. Ha Excel 2013-at vagy korábbi verziókat használ, akkor azt bővítményként is használhatja.

Itt található egy kiváló útmutató a Power Query telepítéséről Jon által az Excel Campusból.

Ismét, tekintettel arra, hogy az adatok az alábbiak szerint vannak formázva:

Az alábbi lépésekkel konvertálhatja a forrásadatokat Pivot Table ready formátumba:

• Konvertálja az adatokat Excel táblává. Válassza ki az adatkészletet, és lépjen a Beszúrás -> Táblázatok -> Táblázat menüpontra.
• A Táblázat beszúrása párbeszédpanelen ellenőrizze, hogy a megfelelő tartomány van -e kiválasztva, majd kattintson az OK gombra. Ezzel a táblázatos adatokat Excel táblává alakítja át.
• Az Excel 2016 alkalmazásban lépjen az Adatok -> Get & Transform -> From Table menüpontra.
  • Ha a Power Query bővítményt korábbi verzióban használja, lépjen a Power Query -> Külső adatok -> Táblázat menüpontra.
• A Lekérdezésszerkesztőben válassza ki a feloldani kívánt oszlopokat. Ebben az esetben ezek a négynegyedek. Az összes oszlop kijelöléséhez tartsa lenyomva a Shift billentyűt, majd válassza ki az első, majd az utolsó oszlopot.
• A Lekérdezésszerkesztőben lépjen az Átalakítás -> Bármely oszlop -> Oszlopok feloldása menüpontra. Ez átalakítja az oszlop adatait Pivot Table -barát formátumba.
• A Power Query általános neveket ad az oszlopoknak. Változtassa meg ezeket a neveket a kívánt névre. Ebben az esetben módosítsa az attribútumot a negyedévre és az értéket az értékesítésre.
• A Lekérdezésszerkesztőben lépjen a Fájl -> Bezárás és betöltés menüpontra. Ezzel bezárja a Power Query Editor párbeszédpanelt, és létrehoz egy külön munkalapot, amelyen az adatok nem elforgatott oszlopokkal lesznek ellátva.

Most, hogy tudja, hogyan kell előkészíteni a forrásadatokat a kimutatástáblához, készen áll az Excel használatára a kimutatástáblák világában.

Íme néhány más Pivot táblázat oktatóanyag, amelyek hasznosak lehetnek:

• A pivot táblázat frissítése az Excelben.
• A szeletelők használata az Excel pivot táblázatban - Kezdő útmutató.
• A dátumok csoportosítása az Excel pivot tábláiban.
• Számok csoportosítása az Excel kimutatásában.
• Pivot gyorsítótár az Excelben - mi ez és hogyan lehet a legjobban használni.
• Az adatok szűrése a pivot táblázatban az Excelben.
• Excel Pivot táblázat számított mező hozzáadása és használata.
• Feltételes formázás alkalmazása pivot táblázatban az Excelben.
• Hogyan lehet az üres cellákat nullákkal helyettesíteni az Excel pivot táblákban.