Értékek kiszámítása a kimutatásokban

A kimutatások lehetőséget nyújtanak különféle adatok kiszámítására. Cikkünkből megismerheti az elérhető számítási módokat, a forrásadatok típusának a számításokra kifejtett hatásait, valamint a képletek kimutatásokban és kimutatásdiagramokban való használatát.

Elérhető számítási módszerek

Az alábbi számítási módszerekkel számíthat ki értékeket a kimutatásokban:

• Értékmezők összegző függvényei Az értékek terület adatai összegzik a kimutatás mögöttes forrásadatait. Például az alábbi forrásadatok:

• Ezeket a kimutatásokat és kimutatásdiagramokat eredményezik. Ha egy kimutatás adataiból hoz létre egy kimutatásdiagramot, a kimutatásdiagram értékei a kapcsolódó kimutatás számításait tükrözik.

• A kimutatás Hónap oszlopmezője a Március és az Április elemet használja. A Régió sormező az Észak, a Dél, a Kelet és a Nyugat elemet használja. Az Április oszlop és az Észak sor metszetében található érték a forrásadatok ÁprilisHónap értékét és ÉszakRégió értékét tartalmazó rekordok teljes bevétele.

• A kimutatásdiagramokban előfordulhat, hogy a Régió mező kategóriamező, amely az Észak, a Dél, a Kelet és a Nyugat kategóriát jeleníti meg. A Hónap mező lehet sorozatmező, amely a Március, az Április és a Május elemet jeleníti meg sorozatként a jelmagyarázatban. Az Eladások összesen nevű Értékek mező olyan adatjelölőket tartalmazhat, amelyek az egyes régiók hónapokra lebontott teljes bevételét jelenítik meg. Az egyik adatjelölő például a függőleges (érték) tengelyen elfoglalt helye alapján az Április havi, Észak régióbeli összforgalmat jelöli.

• Az értékmezők kiszámításához az alábbi összegző függvényeket használhatja, az OLAP típusú forrásadatokon kívül bármilyen típusú forrásadatokhoz.

  Funkció	Ezt összegzi
  Szum	Az értékek összege. Numerikus adatok esetében ez az alapértelmezett összegzési függvény.
  Darab	Az adatértékek száma. A Darab összegző függvény ugyanúgy működik, mint a DARAB2 függvény. Ha az adatok nem számokból állnak, ez az alapértelmezett függvény.
  Átlag	Az értékek átlaga.
  Max	A legnagyobb érték.
  Min	A legkisebb érték.
  Szorzat	Az értékek szorzata.
  SzámDarab	A számokból álló adatértékek száma. A SzámDarab összegző függvény ugyanúgy működik, mint a DARAB függvény.
  Szórás	A sokaság szórásának becslése, ahol a minta a teljes sokaság része.
  Szórásp	Az összes összegezni kívánt adathoz mint teljes sokasághoz tartozó (nem korrigált) tapasztalati szórás.
  Var	A sokaság variációjának becslése, ahol a minta a teljes sokaság része.
  Varp	Az összes összegezni kívánt adathoz mint teljes sokasághoz tartozó variancia.

• Egyéni számítások Az egyéni számítások az adatterület más elemei vagy cellái alapján jelenítenek meg értékeket. Például megjelenítheti az Eladások összesen adatmezőt a Március havi eladások százalékos értékeként, de a Hónap mező elemeinek göngyölített összegeként is.
  Az értékmezők egyéni számításaihoz az alábbi függvényeket használhatja.

  Funkció	Eredmény
  Nincs számítás	A mezőben megadott értéket jeleníti meg.
  Végösszeg százaléka	Az értékek százalékos aránya a kimutatás értékeinek vagy adatpontjainak összegében.
  Oszlop összegének százaléka	Az egyes oszlopok vagy adatsorok értékeinek százalékos aránya az oszlop/adatsor összegében.
  Sor összegének százaléka	Az egyes sorok vagy kategóriák értékeinek százalékos aránya a sor/kategória összegében.
  Százalék	Az értékek százalékos aránya a Viszonyítási mezőben található Viszonyítási tételhez viszonyítva.
  Szülősorösszeg százalékában	A következőképpen számítja az értékeket: (a tétel értéke) / (a sorokban található szülőtétel értéke)
  Szülőoszlopösszeg százalékában	A következőképpen számítja az értékeket: (a tétel értéke) / (az oszlopokban található szülőtétel értéke)
  Szülőösszeg százaléka	A következőképpen számítja az értékeket: (a tétel értéke) / (a kijelölt Viszonyítási mező szülőtételének értéke)
  Eltérés	Az értékek eltérése a kiválasztott Viszonyítási mezőben található Viszonyítási tételtől.
  Százalékos eltérés	Az értékek százalékos eltérése a kiválasztott Viszonyítási mezőben található Viszonyítási tételtől.
  Göngyölített összeg	A Viszonyítási mezőben egymást követő tételek göngyölített összege.
  Göngyölített összeg százaléka	A Viszonyítási mezőben egymást követő tételek göngyölített összegéhez viszonyítva kiszámított százalékos érték.
  A legkisebbtől a legnagyobbig rangsorolva	Egy adott mező kijelölt értékeinek rangsora, amelyben a legkisebb tétel értéke 1, a nagyobb tételek pedig magasabb besorolást kapnak.
  A legnagyobbtól a legkisebbig rangsorolva	Egy adott mező kijelölt értékeinek rangsora, amelyben a legnagyobb tétel értéke 1, a kisebb tételek pedig magasabb besorolást kapnak.
  Index	A következőképpen számítja az értékeket: ((cellaérték) x (végösszegek végösszege)) / ((sor végösszege) x (oszlop végösszege))

• Képletek Ha az összegző függvényekkel és az egyéni számításokkal nem éri el a kívánt eredményt, saját képleteket hozhat létre a számított mezőkben és elemekben. Hozzáadhat például egy értékesítésijutalék-képlettel ellátott számított elemet, amely régiónként eltérő. A kimutatás ekkor automatikusan beleszámolná a jutalékot a rész- és végösszegekbe.

A forrásadatok típusainak hatása a számításokra

A kimutatásokban elérhető számítások és opciók attól függnek, hogy a forrásadatok OLAP-adatbázisból származnak-e.

• OLAP-forrásadatokon alapuló számítások Az OLAP-kockákból létrehozott kimutatások összegzett értékeit az OLAP-kiszolgáló számítja ki előre, mielőtt az Excel megjelenítené az eredményeket. A kimutatásban nem módosíthatja, hogy a program hogyan számítsa ki előre az értékeket. Nem módosíthatja például az adatmezők vagy részösszegek kiszámítására használt összegző függvényt, és nem adhat hozzá számított mezőket vagy elemeket.
  Emellett ha az OLAP-kiszolgáló számított mezőkkel – más néven számított tagokkal – rendelkezik, ezek a mezők megjelennek a kimutatás mezőlistájában. A Visual Basic for Applications (VBA) által írt és a munkafüzetben tárolt makrók által létrehozott számított mezők és elemek is megjelennek, azonban ezeket nem módosíthatja. Ha további számítástípusokat szeretne végezni, lépjen kapcsolatba az OLAP-adatbázis rendszergazdájával.
  OLAP-forrásadatokkal való munka során – rész- és végösszegek számításakor – belefoglalhatja vagy kizárhatja a rejtett tételek értékeit.
• Nem OLAP-forrásadatokon alapuló számítások Az egyéb külső adatokon vagy munkalap-adatokon alapuló kimutatásokban az Excel a numerikus adatokat tartalmazó értékmezőket a SZUM összegző függvénnyel számítja ki, a szöveget tartalmazó adatmezőket pedig a DARAB összegző függvénnyel. Az adatok további elemzéséhez és testreszabásához másik összegző függvényt is választhat, például az Átlag, a Max vagy a Min függvényt. Számított mező vagy egy mezőn belüli számított tétel létrehozásával saját, a kimutatás vagy más munkalap adatainak elemeit használó képletet is létrehozhat.

Képletek használata a kimutatásokban

Képleteket csak a nem OLAP-forrásadatokon alapuló kimutatásokban hozhat létre. Az OLAP-adatbázisokon alapuló kimutatásokban nem használhat képleteket. A kimutatásokban való képlethasználathoz érdemes tisztában lenni a képletek alábbi szintaktikai szabályaival és viselkedésével:

• Kimutatások képlet-elemei A számított mezőkhöz és tételekhez létrehozott képletekben a munkalapképletekhez hasonlóan használhat operátorokat és kifejezéseket. Állandókat is használhat, valamint hivatkozhat a kimutatás adataira, azonban nem használhat cellahivatkozásokat és definiált neveket. Nem használhat olyan munkalapfüggvényeket, amelyek argumentumai cellahivatkozások vagy definiált nevek, és nem használhat tömbfüggvényeket.

• Mező- és tételnevek Az Excel mező- és tételnevekkel azonosítja a képletekben a kimutatások elemeit. A következő példában a C3:C9 adattartomány a Tejtermék mezőnevet használja. Például a Típus mező egyik számított tétele, amely a tejtermékek eladásai alapján egy új termék értékesítési adatait becsli meg, az =Dairy * 115% képletet használhatja.
  

  Megjegyzés

  A kimutatásdiagramok mezőnevei a kimutatás mezőlistájában jelennek meg, míg a tételek nevei az egyes mezők legördülő listájában láthatók. Ne tévessze össze ezeket a neveket a diagramtippekben látható nevekkel, mert azok a sorozatok és az adatpontok nevét jelenítik meg.

• A képletek összegeken működnek, nem különálló rekordokon A számított mezők képletei a képlet mezőiben található mögöttes adatainak összegén működnek. Az =Értékesítés * 1.2 számítottmező-képlet például 1,2-vel szorozza meg minden típus és régió értékesítésének összegét, nem pedig az egyes eladások értékét 1,2-vel, és nem ezt követően adja össze a kapott értékeket.
  A számított tételek képletei a különálló rekordok alapján működnek. A =Tejtermék *115% számított tétel képlete például minden különálló tejtermék-értékesítést 115%-kal szoroz meg, majd összegzi a kapott értékeket az Értékek területen.

• Szóközök, számok és szimbólumok a nevekben Az egynél több mezőt tartalmazó nevek esetében a mezők bármilyen sorrendben szerepelhetnek. A fenti példában a C6:D6 cellák „Április Észak” vagy „Észak Április” nevet is kaphatnak. Az egynél több szóból álló, illetve a számokat vagy jeleket tartalmazó nevek esetében használjon egyszerű idézőjeleket.

• Összegek A képletek nem hivatkozhat összegekre (például Március összesen, Április összesen és Végösszeg a példában).

• Mezőnevek tételhivatkozásokban A mezőnevek szerepelhetnek a tételhivatkozásokban. A tétel nevének szögletes zárójelben kell lennie, például Régió[Észak]. Használja ezt a formátumot, ha el szeretné kerülni a #NAME? hibát, ha egy kimutatás két különböző mezőjének két tételének neve azonos. Ha például egy jelentés Típus és Kategória mezőjében is szerepel egy-egy Hús nevű tétel, akkor meggátolhatja a #NAME? hibát ad vissza, ha a tételekre Típus[Hús] és Kategória[Hús] néven hivatkozik.

• Pozíció szerinti tételhivatkozások A tételekre hivatkozhat az aktuális kimutatásban elfoglalt helyük alapján. A Típus[1] a Tejtermék, a Típus[2] pedig a Tenger gyümölcsei. Az így hivatkozott tételek módosulhatnak minden alkalommal, amikor a tételek pozíciója változik, vagy amikor megjelenít és elrejt különböző tételeket. Ez az index nem számolja a rejtett tételeket.
  A tételekre hivatkozhat relatív pozíció szerint. A program a pozíciókat a képletet tartalmazó számított tételhez képest határozza meg. Ha az aktuális régió a Dél, a Terület[-1] értéke Észak. Ha az aktuális régió az Észak, a Terület[+1] értéke Dél. Egy számított tétel például a következő képletet használhatja: =Régió[-1] * 3%. Ha az Ön által megadott pozíció a mező első tétele előtt vagy utolsó tétele után szerepel, a képlet #HIV! hibaüzenetet fog adni.

Képletek használata a kimutatásdiagramokban

Ha egy kimutatásdiagramban szeretne képletet használni, azt a diagramhoz tartozó kimutatásban kell létrehoznia. Ott megtekintheti az adathalmazt alkotó értékeket, majd grafikusan ábrázolhatja az eredményt a kimutatásdiagramban.

Az alábbi kimutatásdiagram például az üzletkötők értékesítését mutatja régiónkénti bontásban:

Ha meg szeretné tekinteni, hogyan változna az értékesítés, ha 10 százalékkal növelné, hozzon létre egy számított mezőt a társított kimutatásban. Ez egy ehhez hasonló képletet alkalmaz: =Értékesítés * 110%.

Az eredmény azonnal megjelenik a kimutatásdiagramban, ahogy az az alábbi diagramon látható:

Ha külön adatjelölővel szeretné ellátni az Észak régió értékesítését, amelyből levonja a 8 százalékos szállítási költséget, hozzon létre egy számított tételt a Régió mezőben, az alábbihoz hasonló képlettel: =Észak – (Észak * 8%).

Az eredményül kapott diagram így néz ki:

Az Üzletkötő mezőben létrehozott számított tétel sorozatként jelenne meg a jelmagyarázatban, a diagramon pedig minden kategóriában egy adatpontként.

Képlet létrehozása egy kimutatásban

A kezdés előtt döntse el, hogy szeretne-e létrehozni egy számított mezőt vagy egy mezőn belüli számított tételt. Ha a képletben egy másik mező adatait szeretné felhasználni, használjon számított mezőt. Ha a képletben egy mező egy vagy több konkrét tételének adatait szeretné felhasználni, használjon számított tételt.

A számított tételek esetében cellánként különböző képleteket adhat meg. Ha például az OrangeCounty nevű számított mező képlete =Narancs * 0,25 minden hónapra, azt módosíthatja =Narancs *0,5 értékre június, július és augusztus hónapokra.

Ha több számított tétellel vagy képlettel dolgozik, megszabhatja a számítás sorrendjét.

Számított mező hozzáadása

1. Jelölje ki a kimutatást.
   Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.
2. Az Elemzés lap Számítások csoportjában válassza a Mezők, Cikkek & Készletek, majd a Számított mező lehetőséget.
   
3. A Név mezőben adjon nevet a mezőnek.
4. A Képlet mezőbe írja be a mező képletét.
   Ha a képletben egy másik mező adatait szeretné használni, jelölje ki a mezőt a Mezők mezőben, majd válassza a Mező beszúrása lehetőséget. Ha például 15%-os jutalékot szeretne számítani az Értékesítés mező minden értékéhez, írja be az = Értékesítés * 15% képletet.
5. Válassza a Hozzáadás lehetőséget.

Számított tétel hozzáadása egy mezőhöz

1. Jelölje ki a kimutatást.
   Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.
2. Ha a mező elemei csoportosítva vannak, az Elemzés lap Csoportosítás csoportjában válassza a Csoportbontás lehetőséget.
   
3. Jelölje ki azt a mezőt, amelyhez hozzá szeretné adni a számított tételt.
4. Az Elemzés lap Számítások csoportjában válassza a Mezők, Cikkek & Készletek, majd a Számított tétel lehetőséget.
   
5. Írja be a számított tétel nevét a Név mezőbe.
6. A Képlet mezőbe írja be a tétel képletét.
   Ha egy tétel adatait szeretné használni a képletben, jelölje ki a tételt a Tételek listában, majd válassza a Tétel beszúrása lehetőséget (a tételnek ugyanabból a mezőből kell származnia, mint a számított tételnek).
7. Válassza a Hozzáadás lehetőséget.

Cellánként különböző képletek megadása számított tételekhez

1. Jelölje ki azt a cellát, amelynek módosítani szeretné a képletét.
   Ha több cella képletét szeretné módosítani, tartsa lenyomva a CTRL billentyűt, majd jelölje ki a további cellákat.
2. A szerkesztőlécen írja be a képlet módosításait.

Több számított tétel vagy képlet számítási sorrendjének módosítása

1. Jelölje ki a kimutatást.
   Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.
2. Az Elemzés lap Számítások csoportjában válassza a Mezők, Cikkek & Készletek, majd a Megoldási sorrend lehetőséget.
   
3. Jelöljön ki egy képletet, majd válassza a Fel vagy a Le lehetőséget.
4. Folytassa addig, amíg a képletek a kívánt számítási sorrendbe nem kerülnek.

Egy kimutatás összes képletének megtekintése

Megjelenítheti az aktuális kimutatásban használt összes képletet.

1. Jelölje ki a kimutatást.
   Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.
2. Az Elemzés lap Számítások csoportjában válassza a Mezők, Elemek & Készletek, majd a Képletek listabeállítása lehetőséget.
   

Kimutatásképlet szerkesztése

Egy képlet szerkesztése előtt meg kell határoznia, hogy a képlet számított mezőben vagy számított tételben szerepel-e. Ha a képlet egy számított tételben található, azt is meg kell határoznia, hogy csak az adott képlet tartozik a számított tételhez.

A számított tételek esetében szerkesztheti az egyes cellák különálló képleteit. Ha például az OrangeCalc nevű számított mező képlete =Narancs * 0,25 minden hónapra, azt módosíthatja =Narancs *0,5 értékre június, július és augusztus hónapokra.

Annak meghatározása, hogy a képlet számított mezőben vagy számított tételben szerepel-e

1. Jelölje ki a kimutatást.
   Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.

2. Az Elemzés lap Számítások csoportjában válassza a Mezők, Elemek & Készletek, majd a Képletek listabeállítása lehetőséget.
   

3. A képletlista Számított mező vagy Számított tétel csoportjában keresse meg a módosítani kívánt képletet.

   Ha egy számított tételhez több képlet tartozik, a tétel létrehozásakor megadott alapértelmezett képlet a számított tétel nevét a B oszlopban jeleníti meg. A számított tétel további képleteihez a B oszlop tartalmazza a számított tétel nevét és az egymást metsző tételek neveit is.
   Előfordulhat például, hogy a Tételem nevű számított tételhez egy alapértelmezett képlet tartozik, a Tételem januári értékesítései néven azonosított megegyező tételhez pedig egy másik képlet. A kimutatásban ez a képlet a Tételem sor és a Január oszlop Értékesítés cellájában szerepelne.

4. Folytassa a következő szerkesztési módszerek valamelyikével.

Számított mező képletének szerkesztése

1. Jelölje ki a kimutatást.
   Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.
2. Az Elemzés lap Számítások csoportjában válassza a Mezők, Cikkek & Készletek, majd a Számított mező lehetőséget.
   
3. A Név mezőben jelölje ki a módosítandó képletet tartalmazó mezőt.
4. Szerkessze a képletet a Képlet mezőben.
5. Válassza a Módosítás lehetőséget.

Számított tétel egyetlen képletének szerkesztése

1. Jelölje ki a számított tételt tartalmazó mezőt.
2. Az Elemzés lap Számítások csoportjában válassza a Mezők, Cikkek & Készletek, majd a Számított tétel lehetőséget.
   
3. A Név mezőben jelölje ki a számított tételt.
4. Szerkessze a képletet a Képlet mezőben.
5. Válassza a Módosítás lehetőséget.

Különálló képlet szerkesztése egy számított tétel adott cellájában

1. Jelölje ki azt a cellát, amelynek módosítani szeretné a képletét.
   Ha több cella képletét szeretné módosítani, tartsa lenyomva a CTRL billentyűt, majd jelölje ki a további cellákat.

2. A szerkesztőlécen írja be a képlet módosításait.

   Tipp:

   Ha több számított tétellel vagy képlettel dolgozik, megszabhatja a számítás sorrendjét. További információ: Több számított tétel vagy képlet számítási sorrendjének módosítása.

Kimutatásképlet törlése

1. Határozza meg, hogy a képlet számított mezőben vagy számított tételben szerepel-e.
   A számított mezők a kimutatás mezőlistájában jelennek meg. A számított tételek más mezőkben található tételekként jelennek meg.

2. Hajtsa végre a megfelelő műveletet:

   • Számított mező törléséhez jelölje ki a kimutatás tetszőleges pontját.
   • Számított tétel törléséhez a kimutatásban jelölje ki a törölni kívánt tételt tartalmazó mezőt.
     Ekkor megjelenik a Kimutatáseszközök eszközcsoport és annak Elemzés és Tervezés lapja.

3. Az Elemzés lap Számítások csoportjában válassza a Mezők, Tételek & Készletek lehetőséget, majd a Számított mező vagy a Számított tétel elemet.
   

4. A Név mezőben válassza ki a törölni kívánt mezőt vagy tételt.

5. Válassza a Törlés lehetőséget.

A kimutatás értékeinek összegzéséhez a Webes Excel olyan összegző függvényeket használhat, mint például az Összeg, a Darab és az Átlag. A program alapértelmezés szerint az Összeg függvényt használja a számértékeket tartalmazó értékmezőknél. A kimutatások OLAP-adatforrás alapján is megtekinthetők és szerkeszthetők, de nem hozhatók létre a Webes Excelben.

Az alábbiak szerint választhat egy másik összegző függvényt:

1. Jelölje ki a kimutatás tetszőleges pontját, majd válassza a Kimutatás>mezőlista elemet.  Azt is megteheti, hogy a jobb gombbal a kimutatásra kattint, majd a Mezőlista megjelenítése parancsot választja.

2. A Kimutatás mezőlistaÉrtékek területén válassza az értékmező melletti nyilat.

   

3. Válassza az Értékmező-beállítások lehetőséget.

4. Válassza ki a kívánt összegző függvényt, majd válassza az OK gombot.

   Megjegyzés

   Az OLAP forrásadatokon alapuló kimutatásokban nem érhetők el összegző függvények.

   Összegző függvény	Számított érték
   Összeg	Az értékek összege. Számértékeket tartalmazó értékmezők esetén ez az alapértelmezett függvény.
   Darab	A nem üres értékek száma. A Darab összegző függvény ugyanúgy működik, mint a DARAB2 függvény. A nem számértékeket vagy üres értékeket tartalmazó értékmezőknél a Darab az alapértelmezett összegző függvény.
   Átlag	Az értékek átlaga.
   Max	A legnagyobb érték.
   Min	A legkisebb érték.
   Szorzat	Az értékek szorzata.
   Darabszám	A számokat tartalmazó értékek száma (nem ugyanaz, mint a Darab, amely a nem üres értékeket tartalmazza).
   Szórás	A sokaság szórásának becslése, ahol a minta a teljes sokaság része.
   Szórásp	Az összes összegezni kívánt adathoz mint teljes sokasághoz tartozó (nem korrigált) tapasztalati szórás.
   Var	A sokaság variációjának becslése, ahol a minta a teljes sokaság része.
   Varp	Az összes összegezni kívánt adathoz mint teljes sokasághoz tartozó variancia.

Az iPad Excel alkalmazásban a kimutatás értékeinek összesítésére olyan összegző függvényeket használhat, mint például az Összeg,a Darab és az Átlag. A program alapértelmezés szerint az Összeg függvényt használja a számértékeket tartalmazó értékmezőknél. Az OLAP-adatforráson alapuló kimutatások megtekinthetők és szerkeszthetők, de az iPad Excel alkalmazásban nem hozhatók létre. 

Az alábbiak szerint választhat egy másik összegző függvényt:

1. Koppintson a kimutatás tetszőleges pontjára, válassza a Kimutatás fület, majd a Mezőlista elemet. Előfordulhat, hogy a Mezőlista kiválasztásához balra kell pöccintenie.

   

2. A kimutatásmezők lista Értékek területén koppintson az értékmező melletti három pontra.

   

3. Koppintson a Mezőbeállítások nézete elemre, és jelölje be a kívánt összegző függvényt.