Gyakran előfordul, hogy a Pivot tábla létrehozása után ki kell bővítenie az elemzést, és több adatot/számítást kell tartalmaznia annak részeként.
Ha új adatpontra van szüksége, amelyet a kimutatástábla meglévő adatpontjaival szerezhet be, akkor nem kell visszamennie, és hozzá kell adnia a forrásadatokhoz. Ehelyett használhatja a Pivot Table Calculated Field ezt csináld meg.
Töltse le az adatkészletet, és kövesse.
Mi az a Pivot tábla számított mező?
Kezdjük a pivot tábla alapvető példájával.
Tegyük fel, hogy rendelkezik kiskereskedők adatkészletével, és létrehoz egy kimutatástáblát az alábbiak szerint:
A fenti pivot táblázat összegzi a kiskereskedők értékesítési és nyereségértékeit.
Mi van akkor, ha azt is szeretné tudni, hogy mi volt ezeknek a kiskereskedőknek a haszonkulcsa (ahol a haszonkulcs a „Nyereség” osztva az „Értékesítéssel”).
Ennek két módja van:
- Térjen vissza az eredeti adatkészlethez, és adja hozzá ezt az új adatpontot. Tehát beszúrhat egy új oszlopot a forrásadatokba, és kiszámíthatja a haszonkulcsot. Ha ezt megtette, frissítenie kell a kimutatástábla forrásadatait, hogy az új oszlopot részeként kaphassa meg.
- Bár ez a módszer lehetséges, manuálisan kell visszamenni az adatkészlethez és elvégezni a számításokat. Például előfordulhat, hogy hozzá kell adnia egy másik oszlopot az egységenkénti átlagos eladás (Értékesítés/Mennyiség) kiszámításához. Ismét hozzá kell adnia ezt az oszlopot a forrásadatokhoz, majd frissítenie kell a pivot táblát.
- Ez a módszer felfújja a kimutatástáblát is, miközben új adatokat ad hozzá.
- Adjon hozzá számításokat a Pivot táblán kívül. Ez akkor lehet opció, ha a kimutatástábla szerkezete valószínűleg nem változik. Ha azonban módosítja a Pivot táblát, előfordulhat, hogy a számítás nem frissül megfelelően, és rossz eredményeket vagy hibákat eredményezhet. Amint az alább látható, kiszámítottam a haszonkulcsot, amikor kiskereskedők voltak a sorban. De amikor ügyfelekről régiókra változtattam, a képlet hibát adott.
- Pivot tábla számított mező használata. Ez a leghatékonyabb módja a meglévő kimutatástábla -adatok felhasználásához és a kívánt mutató kiszámításához. Tekintsük a Számított mezőt virtuális oszlopnak, amelyet a kimutatástábla meglévő oszlopai segítségével adtunk hozzá. A Pivot Table Calculated Field használatának számos előnye van (amint egy perc múlva látni fogjuk):
- Nem igényli a képletek kezelését vagy a forrásadatok frissítését.
- Skálázható, mivel automatikusan figyelembe veszi az új adatokat, amelyeket hozzáadhat a kimutatástáblához. Miután hozzáadott egy számítási mezőt, a Pivot táblázat bármely más mezőjéhez hasonlóan használhatja.
- Könnyen frissíthető és kezelhető. Például, ha a mutatók megváltoznak, vagy módosítania kell a számítást, ezt könnyen megteheti a kimutatástáblából.
Számított mező hozzáadása a pivot táblához
Lássuk, hogyan adhat hozzá egy kimutatástábla számított mezőjét egy meglévő kimutatástáblához.
Tegyük fel, hogy rendelkezik egy pivot táblával, amint az alább látható, és ki szeretné számítani az egyes kiskereskedők haszonkulcsát:
Az alábbi lépésekkel adhatja hozzá a kimutatástábla számított mezőjét:
- Válassza ki a cellákat a kimutatástáblában.
- Lépjen a Pivot táblaeszközök -> Elemzés -> Számítások -> Mezők, elemek és készletek menüpontra.
- A legördülő menüből válassza a Számított mező lehetőséget.
- A Számított fájl beszúrása párbeszédpanelen:
- Adjon nevet a Név mezőbe.
- A Képlet mezőben hozza létre a kívánt képletet a számított mezőhöz. Ne feledje, hogy az alatta található mezőnevek közül választhat. Ebben az esetben a képlet ‘= Profit/ Sales’. A mezőneveket manuálisan is megadhatja, vagy duplán kattinthat a Mezők mezőben szereplő mezőnévre.
- Kattintson a Hozzáadás gombra, és zárja be a párbeszédpanelt.
Amint hozzáadja a Számított mezőt, a PivotTable -mezők listájának egyik mezőjeként jelenik meg.
Most ezt a számított mezőt bármely más kimutatástábla mezőként is használhatja (vegye figyelembe, hogy a Pivot tábla számított mezője nem használható jelentésszűrőként vagy szeletelőként).
Amint azt korábban említettem, a Pivot Table Calculated Field használatának előnye, hogy megváltoztathatja a Pivot Table szerkezetét, és az automatikusan módosul.
Például, ha a régiót húzom a sorok területen, akkor az alábbi módon kapja meg az eredményt, ahol a haszonkulcs értéke a kiskereskedők és a régió esetében is megjelenik.
A fenti példában egy egyszerű képletet (= Profit/Sales) használtam egy számított mező beszúrásához. Használhat azonban néhány speciális képletet is.
Mielőtt példát mutatnék egy speciális képlet használatára a Pivot Table Calculate mező létrehozásához, itt van néhány dolog, amit tudnia kell:
- NEM használhat hivatkozásokat vagy elnevezett tartományokat a kimutatástábla kiszámított mezőjének létrehozásakor. Ez kizárna sok olyan képletet, mint a VLOOKUP, INDEX, OFFSET stb. Használhat azonban olyan képleteket, amelyek hivatkozások nélkül is működhetnek (például SUM, IF, COUNT stb.).
- Használhat konstansot a képletben. Például, ha szeretné tudni az előrejelzett eladásokat, ahol 10%-os növekedést prognosztizál, használhatja a = Értékesítés*1.1 képletet (ahol az 1.1 állandó).
- A fontossági sorrendet a számított mezőt alkotó képlet követi. A legjobb gyakorlat szerint használjon zárójeleket, hogy ne kelljen emlékeznie az elsőbbségi sorrendre.
Most nézzünk egy példát egy speciális képlet használatára a Számított mező létrehozásához.
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és meg kell jelenítenie az előrejelzett értékesítési értéket a kimutatástáblában.
Az előrejelzett értékhez a nagy kiskereskedőknél (3 millió feletti értékesítés) 5% -os, a kis- és közepes kiskereskedőknél (3 millió alatti értékesítés) 10% -os értékesítési növekedést kell használni.
Megjegyzés: Az itt található értékesítési számok hamisak, és az oktatóanyag példáinak illusztrálására szolgáltak.
Ezt a következőképpen teheti meg:
- Válassza ki a cellákat a kimutatástáblában.
- Lépjen a Pivot táblaeszközök -> Elemzés -> Számítások -> Mezők, elemek és készletek menüpontra.
- A legördülő menüből válassza a Számított mező lehetőséget.
- A Számított fájl beszúrása párbeszédpanelen:
- Adjon nevet a Név mezőbe.
- A Képlet mezőben használja a következő képletet: = HA (Régió = ”Dél”, Értékesítés *1,05, Értékesítés *1,1)
- Kattintson a Hozzáadás gombra, és zárja be a párbeszédpanelt.
Ez új oszlopot ad a pivot táblához az értékesítési előrejelzés értékével.
Kattintson ide az adatkészlet letöltéséhez.
Probléma a Pivot tábla számított mezőivel
A Calculated Field egy csodálatos funkció, amely valóban növeli a Pivot Table értékét terepi számításokkal, miközben továbbra is mindent skálázható és kezelhető.
Van azonban egy probléma a kimutatástábla kiszámított mezőivel, amelyet ismernie kell a használata előtt.
Tegyük fel, hogy van egy pivot táblám, amint az alább látható, ahol a számított mezőt használtam az előrejelzett értékesítési számok lekéréséhez.
Vegye figyelembe, hogy a részösszeg és a végösszeg nem helyes.
Bár ezeknek hozzá kell adniuk az egyes értékesítési előrejelzési értékeket minden kiskereskedőhöz, valójában ugyanazt a számított mező képletet követi, amelyet mi készítettünk.
Tehát a South Total esetében, míg az értéknek 22 824 000 -nek kell lennie, a South Total tévesen 22 287 000 -nek számol be. Ez akkor történik, amikor a 21 225 800*1,05 képletet használja az érték megszerzéséhez.
Ezt sajnos nem lehet korrigálni.
Ennek legjobb kezelése az lenne, ha eltávolítaná a részösszegeket és a végösszegeket a Pivot táblázatból.
A Debra által bemutatott innovatív megoldásokon keresztül is megoldhatja ezt a problémát.
Hogyan lehet módosítani vagy törölni egy kimutatástábla számított mezőjét?
Miután létrehozta a Pivot tábla számított mezőjét, módosíthatja vagy törölheti a képletet a következő lépésekkel:
- Válassza ki a cellákat a kimutatástáblában.
- Lépjen a Pivot táblaeszközök -> Elemzés -> Számítások -> Mezők, elemek és készletek menüpontra.
- A legördülő menüből válassza a Számított mező lehetőséget.
- A Név mezőben kattintson a legördülő nyílra (kis lefelé mutató nyíl a mező végén).
- A listából válassza ki a törölni vagy módosítani kívánt számított mezőt.
- Módosítsa a képletet, ha módosítani szeretné, vagy kattintson a Törlés gombra, ha törölni szeretné.
Hogyan lehet listát szerezni az összes számított mezőképletekről?
Ha sok Pivot tábla számított mezőt hoz létre, ne aggódjon, hogy nyomon követi az egyes képleteket.
Az Excel lehetővé teszi a Számított mezők létrehozásakor használt képletek gyors listájának létrehozását.
Az alábbi lépésekkel gyorsan lekérheti az összes számított mező képletét:
- Válassza ki a cellákat a kimutatástáblában.
- Lépjen a Pivot táblaeszközök -> Elemzés -> Mezők, elemek és készletek -> Lista képletek menüpontra.
Amint rákattint a Lista képletekre, az Excel automatikusan beilleszt egy új munkalapot, amely tartalmazza a Pivot táblázatban használt összes számított mező/elem adatait.
Ez igazán hasznos eszköz lehet, ha munkáját el kell küldenie az ügyfélnek, vagy meg kell osztania a csapatával.
Hasznosnak találhatja az alábbi Pivot Table oktatóanyagokat is:
- Forrásadatok előkészítése a kimutatástáblához.
- 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.
- Az adatok szűrése a pivot táblázatban az Excelben.
- Hogyan lehet az üres cellákat nullákkal helyettesíteni az Excel pivot táblákban.
- Feltételes formázás alkalmazása pivot táblázatban az Excelben.
- Pivot gyorsítótár az Excelben - mi ez és hogyan lehet a legjobban használni?
