Pivot Table Formula Excel-ben (Tartalomjegyzék)
- Pivot Table Formula Excelben
- Egyéni mező a nyereség összegének kiszámításához
- Speciális képlet a kiszámított mezőben
Pivot Table Formula Excelben
A Pivot Table egy olyan eszköz, amely lehetővé teszi számunkra, hogy elemezzük a nagy adattartományokat. Elemezhetjük, értelmezhetjük és még sok más dolgot megtehetünk anélkül, hogy nagy részünket megrontanánk és izzadnánk. Szinte mindent megadhat, ami a forrásadatokban található.
Ha bizonyos információk vannak a forrásadatokban, akkor ezt magának kell kiszámítanunk. Például, ha rendelkezünk teljes értékesítési összeggel és teljes költséggel, akkor a teljes nyereséget vagy veszteséget önmagában kell kiszámítanunk.
Nem kell ezt megtennünk a forrásban, de ezt megtehetjük magában a pivot táblában is, ezeket a pivot táblán belül kiszámított mezőknek nevezzük. Használhatunk egyedi képleteket az adatok elkészítéséhez, hogy minél több történetet mutassunk az adatokból. A Számított mezők lehetővé teszi egy új számított oszlop felépítését, amely a tényleges adatforrásban nem létezik.
Ebben a cikkben bemutatjuk a Pivot Table kiszámított mezők felhasználásának módját új oszlopok felépítésére igényeink alapján.
Egyéni mező a nyereség összegének kiszámításához
Itt letöltheti ezt a Pivot Table Formula Excel sablont - Pivot Table Formula Excel sablonEz a leggyakrabban használt számított mező a pivot táblában. Vessen egy pillantást az alábbi adatokra, országnév, terméknév, eladott egységek, egységár, bruttó értékesítés, COGS (eladott áruk költsége), dátum és év oszlopban.
Hadd alkalmazjam a pivot táblát az egyes országok összes eladásának és összköltségének megállapításához. Az alábbiakban látható a fenti adatok pivot táblája.
A probléma az, hogy a forrásadatokban nincs nyereség oszlop. Meg kell határoznom az egyes országok nyereségét és százalékos arányát. Ezt a két oszlopot hozzáadhatjuk a forgótáblához is.
1. lépés: Jelöljön ki egy cellát a pivot táblában. Lépjen az elemzés fülre a szalagon, és válassza a Mezők, tételek és készletek lehetőséget. Ez alatt válassza a Számított mező lehetőséget.
2. lépés: Az alábbi párbeszédpanelen adjon nevet az új kiszámított mezőnek.
3. lépés: A Képlet szakaszban alkalmazza a képletet a nyereség megállapításához. A nyereség megállapítására szolgáló formula a bruttó értékesítés - COGS.
Lépjen be a képletsávba> Válassza a bruttó eladást az alábbi mezőből, majd kattintson duplán a képlet sávba.
Most írja be a mínusz szimbólumot (-), és válassza a COGS> Double click.
4. lépés: Kattintson az ADD és az OK gombra a képlet kitöltéséhez.
5. lépés: Most van a teljes nyereség oszlop a forgótáblában.
Ez a kiszámított mező rugalmas, nem csak az országspecifikus elemzésre korlátozódik, hanem mindenféle elemzéshez felhasználható. Ha országonként és termékenkénti elemzést akarok látni, akkor csak át kell húznom a termék oszlopot a ROW mezőbe, ez megmutatja az egyes termékek profitjának eloszlását az egyes országokban.
6. lépés: Most ki kell számolnunk a profit százalékát. A nyereségszázalék kiszámításához használt képlet a teljes nyereség / bruttó eladás.
Lépjen az Elemzés elemre, majd válassza ki ismét a Számított mező mezők, mezők, tételek és halmazok részét.
7. lépés: Most látnunk kell az újonnan beillesztett, összesített nyereség mezőt a Mezők listában. Helyezze be ezt a mezőt a képletbe.
8. lépés: Írja be az elválasztó szimbólumot (/) és illessze be a bruttó értékesítési mezőt.
9. lépés: Nevezze meg ezt a kiszámított mezőt profitszázalékként.
10. lépés: Kattintson az ADD és az OK gombra a képlet kitöltéséhez. Az új oszlop nyereségszázalékot mutat.
Speciális képlet a kiszámított mezőben
Amit most megmutattam, az a Számított mező alapvető dolgai. Ebben a példában a fejlett képleteket mutatom meg a pivot tábla kiszámított mezőiben. Most az ösztönző összeget akarom kiszámítani a profit százaléka alapján.
Ha a Profit%> 15%, az ösztönzőnek a teljes profit 6% -ának kell lennie.
Ha a Profit%> 10%, akkor az ösztönzőnek a teljes profit 5% -ának kell lennie.
Ha a Profit% kevesebb, mint 10%, akkor az ösztönzőnek a teljes profit 3% -ának kell lennie.
1. lépés: Lépjen a Számított mezőbe, és nyissa meg az alábbi párbeszédpanelt. Adja meg a nevet ösztönző összegként.
2. lépés: Most IF feltételt fogok használni az ösztönző összeg kiszámításához. Használja az alábbi képleteket a képen látható módon.
= IF ('ProfitPercentage'> 15%, 'TotalProft' * 6%, IF ('ProfitPercentage'> 10%, 'Total Proft' * 5%, 'Total Proft' * 3%))
3. lépés: A befejezéshez kattintson az ADD & OK gombra. Most van egy ösztönző összeg oszlop.
A kiszámított mező korlátozása
Láttuk a Számított mezők csodáját, de ennek vannak bizonyos korlátozásai is. Most nézzük meg az alábbi képet, ha azt akarom látni, hogy a termékenkénti ösztönző összeg feloszlik-e, akkor az ÖSSZES ÖSSZESEN és ÖSSZESEN ÖSSZESEN az Ösztönző ÖSSZETET fogjuk kiszámítani.
Tehát légy óvatos, miközben megmutatja a kiszámított mezők Összes részét. Megmutatja a helytelen összegeket.
Töltse le az összes kiszámított mező képletet
Ha nem tudja, hány képlet van a pivot tábla kiszámított mezőjében, akkor ezek összefoglalását külön munkalapon kaphatja meg.
Lépjen az Elemzés> Mezők, tételek és készletek -> Képletek listája elemre.
Összefoglalót ad az új munkalap összes képletéről.
Ne feledje, hogy mi a Pivot Table Formula az Excel programban
- Törölhetjük, módosíthatjuk az összes kiszámított mezőt.
- Nem használhatunk olyan képleteket, mint a VLOOKUP, SUMIF és sok más tartományba tartozó képletet a kiszámított mezőkben, azaz az összes olyan képlet, amely tartományt igényel, nem használható.
Ajánlott cikkek
Ez egy útmutató a Pivot Table Formula programhoz Excelben. Itt megvitattuk a Pivot Table Formula alkalmazásának lépéseit az Excelben, példákat és letölthető excel sablont. Ezeket a hasznos funkciókat Excelben is megnézheti -
- Oktatóanyagok a Pivot Chart-ban Excelben
- Pivot tábla létrehozása Excelben
- VLOOKUP oktatóprogram Excelben
- Excel adatbázis létrehozása