Általában 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 belefoglalnia.
Ha olyan új adatpontra van szüksége, amelyet a pivot tábla egy meglévő adatpontjával lehet megszerezni, akkor nem kell visszamenni, és hozzá kell adni a forrásadatokhoz.Ehelyett használhatjaKimutatás kiszámított mezőiezt csináld meg.
tartalom
Mi az a PivotTable számított mező?
Kezdjük egy pivot tábla alappéldájával.
Tegyük fel, hogy van egy kiskereskedői adatkészlete, és létrehoz egy pivot táblát, például:
A fenti pivot táblázat összefoglalja a kiskereskedő értékesítési és nyereségértékeit.
Nos, mi van akkor, ha azt is tudni szeretné, hogy mik ezek a kiskereskedők haszonkulcsa (a haszonkulcs a „nyereség” osztva az „eladásokkal”)?
Ennek többféle módja van:
- Menjen vissza az eredeti adatkészlethez, és adja hozzá ezt az új adatpontot.Így beszúrhat egy új oszlopot a forrásadatokba, és kiszámolhatja benne a haszonkulcsot.Ha ez megtörtént, frissítenie kell a kimutatástábla forrásadatait, hogy az új oszlop része legyen.
- Míg ez a megközelítés működik, manuálisan kell visszalépnie az adatkészlethez, és el kell végeznie a számításokat.Például érdemes lehet hozzáadni egy másik oszlopot az egységenkénti átlagos eladások (é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 a pivot táblát is felduzzasztja, amikor új adatokat ad hozzá.
- Számítások hozzáadása a kimutatástáblákon kívül.Válassza ezt a lehetőséget, 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ások nem frissülnek megfelelően, és hibás eredményeket vagy hibákat adhatnak.Az alábbiak szerint kiszámítottam a haszonkulcsot, ha kiskereskedők vannak a sorban.De amikor vásárlóról területre változtatom, a képlet elromlik.
- Számítsa ki a mezőket pivot táblákkal.ezHasználja a meglévő kimutatásadatokat, és számítsa ki aleghatékonyabb módja.Tekintse a számított mezőket virtuális oszlopoknak, amelyeket a kimutatástáblázat meglévő oszlopaiból ad hozzá.A pivot tábla számított mezőinek használata számos előnnyel jár (amint azt később látni fogjuk):
- Nem szükséges képletekkel dolgozni vagy forrásadatokat frissíteni.
- Bővíthető, mert automatikusan figyelembe vesz minden új adatot, amelyet esetleg hozzáad a pivot táblához.Egy számított mező hozzáadása után a pivot tábla bármely más mezőjéhez hasonlóan használhatja.
- Könnyen frissíthető és kezelhető.Például, ha egy metrika megváltozik, vagy módosítania kell egy számítást, ezt könnyen megteheti magából a kimutatástáblából.
Számított mezők hozzáadása a kimutatástáblához
Nézzük meg, hogyan adjunk hozzá egy kimutatástáblázattal számított mezőt egy meglévő kimutatástáblához.
Tegyük fel, hogy van egy pivot táblája, amely így néz ki, és ki szeretné számítani az egyes kiskereskedők haszonkulcsát:
Íme a lépések egy kimutatástáblázat számított mező hozzáadásához:
A számított mező hozzáadása után az egyik mezőként jelenik meg a kimutatás mezői listájában.
Mostantól ezt a számított mezőt bármely más kimutatástábla-mezőként használhatja (vegye figyelembe, hogy a kimutatástáblázatból számított mezőt nem használhatja jelentésszűrőként vagy szeletelőként).
Ahogy korábban említettem, a pivot tábla számított mező használatának az az előnye, hogy megváltoztathatja a pivot tábla szerkezetét, és az automatikusan igazodik.
Például, ha áthúzok egy régiót a sorterületre, akkor az alábbihoz hasonló eredményeket kap, ahol a kiskereskedő és a régió haszonkulcsai vannak jelentve.
A fenti példában egy egyszerű képletet (=Profit/Sales) használtam a számított mező beszúrásához.Használhat azonban néhány speciális képletet is.
Mielőtt bemutatnék egy példát egy speciális képlet használatára a kimutatástáblázat számított mezőjének létrehozására, a következőket kell tudnia:
- Nem használhat hivatkozásokat vagy elnevezett tartományokat a kimutatástáblázat számított mezőinek létrehozásakor.Ez számos képletet kizár, például VLOOKUP, INDEX, OFFSET stb.Használhatunk azonban hivatkozások nélkül működő képleteket (pl. SUM, IF, COUNT stb...).
- A képletekben konstansokat használhat.Például, ha meg akarja tudni az előre jelzett értékesítési növekedést, amely várhatóan 10%-kal fog növekedni, akkor az =Eladás*1.1 képletet használja (ahol az 1.1 egy állandó).
- Kövesse az elsőbbségi sorrendet a számított mezőket generáló képletekben.Bevált gyakorlatként használjon zárójeleket, hogy ne kelljen emlékeznie az elsőbbségre.
Most nézzünk egy példát egy számított mező létrehozására speciális képlet segítségével.
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és meg kell jelenítenie az előre jelzett eladásokat egy kimutatástáblázatban.
Az előrejelzési értékekhez 300%-os eladásnövekedést használna a nagy kereskedőknél (több mint 5 millió eladás), és 300%-os eladásnövekedést a kis- és közepes kereskedőknél (10 milliónál kevesebb értékesítés).
Megjegyzés: Az itt található eladási adatok hamisak, és az oktatóanyag példáinak illusztrálására szolgálnak.
Íme, hogyan kell csinálni:
Ezzel egy új oszlopot ad hozzá a kimutatáshoz az értékesítési előrejelzési értékekkel.
Kimutatástábla számított mezőprobléma
A kiszámított mezők egy csodálatos funkció, amely valóban növeli a pivot táblák értékét mezőszámításokkal, miközben mindent skálázható és kezelhető marad.
Van azonban egy probléma a kimutatástábla számított mezőivel, amelyet meg kell értenie használatuk előtt.
Tegyük fel, hogy van egy pivot táblám, mint az alábbi, és egy számított mezőt használok az előrejelzési értékesítési számok lekéréséhez.
Vegye figyelembe, hogy a részösszegek és az összegek helytelenek.
Bár ezeknek külön értékesítési előrejelzési értékeket kell hozzáadniuk minden kiskereskedőhöz, a valóságban ugyanazt a számított mezőképletet követik, amelyet létrehoztunk.
Tehát a South Total esetében, míg az értéknek 22,824,000 22,287,000 XNUMX-nek kell lennie, a South Total helytelenül XNUMX XNUMX XNUMX-nek adja meg.Ez azért történik, mert a 21,225,800*1.05 képletet használja az érték meghatározásához.
Sajnos ezt a problémát nem tudja orvosolni.
A legjobb módja ennek az, ha eltávolítja a részösszegeket és a végösszegeket a kimutatástáblából.
Ön is passzolhat Debra bemutatjaNéhány innovatív megoldás a probléma kezelésére.
Hogyan lehet módosítani vagy törölni egy pivot tábla számított mezőt?
A kimutatástáblázat számított mezőjének létrehozása után módosíthatja vagy törölheti a képletet a következő lépésekkel:
Hogyan kaphat listát az összes számított mezőképletről?
Ha sok kimutatástábla-számítási mezőt hoz létre, akkor nem kell aggódnia az egyes mezőkben használt képletek nyomon követése miatt.
Az Excel segítségével gyorsan létrehozhat egy listát a számított mezők létrehozásához használt összes képletről.
Az alábbi lépésekkel gyorsan lekérheti az összes számított mezőképlet listáját:
- Jelöljön ki egy cellát a kimutatásban.
- Lépjen a Kimutatáseszközök -> Elemzés -> Mezők, elemek és készletek -> Képletek listázása menüpontra.
Miután rákattint a lista képletére, az Excel automatikusan beszúr egy új munkalapot a kimutatásban használt összes számított mező/elem adataival.
Ez nagyon hasznos eszköz lehet, ha munkát kell küldenie egy ügyfélnek, vagy meg kell osztania a csapatával.
