Több eredmény kiszámolása adattábla segítségével

Áttekintés

A Microsoft Excelben az adattáblák a lehetőségelemzési eszközök néven ismert parancscsomag részét képezik. Adattáblák létrehozásakor és elemzésekor lehetőségelemzést végez.

A lehetőségelemzés az a folyamat, melynek során módosítja a cellaértékeket, így megtekintheti, a változtatások hogyan hatnak a munkafüzet képleteinek végeredményére. Egy adattáblával például módosíthatja a hitel kamatlábát és időtartamát a lehetséges havi törlesztőrészletek kiértékeléséhez.

A lehetőségelemzés típusai 

Az Excelben háromféle típusú lehetőségelemzési eszköz található: forgatókönyvek, adattáblák és célérték-keresés. Az esetek és adattáblák bemeneti értékkészletek használatával számítják ki a lehetséges eredményeket. A célérték keresése eltérő, egyetlen eredményt használ, és kiszámítja az eredményt eredményező lehetséges bemeneti értékeket.

Az esetekhez hasonlóan, az adattáblák segítségével lehetséges eredmények tárhatók fel. Az esetektől eltérően, az adattáblák az összes eredményt egy munkalap egyetlen táblájában jelenítik meg. Az adattáblákkal könnyen és gyorsan megvizsgálhatja a lehetőségeket. Mivel csak egy vagy két változóra kell figyelnie, az eredmények könnyen olvashatók és megoszthatók táblázatos formátumban.

Egy adattábla legfeljebb két változót tartalmazhat. Ha több mint két változót szeretne elemezni, használjon inkább eseteket. Bár az adattáblák csak egy vagy két változóval képesek dolgozni (egy a bemeneti cella sorban, egy a bemeneti cella oszlopban), a változók értékének száma nincs korlátozva. Egy eset legfeljebb 32 különböző értékkel rendelkezhet, de korlátlan számú esetet létrehozhat.

További információt A lehetőségelemzés bemutatása című cikkben talál.

Fontos tudnivalók az adattáblákról

Egyváltozós vagy kétváltozós adattáblákat hozhat létre a tesztelni kívánt változók és képletek számától függően.

Egyváltozós adattáblák 

Egyváltozós adattáblát akkor érdemes használnia, ha tudni szeretné, hogy egyetlen változó különböző értékei egy vagy több képletben hogyan módosítják a képletek eredményét. Egyváltozós adattáblát használva például a RÉSZLET függvénnyel megállapíthatja, hogy a különböző kamatlábak miként befolyásolják a havonta fizetendő részletet. Az egyik sorba vagy oszlopba írhatja a változóértékeket, az eredmények pedig egy szomszédos sorban vagy oszlopban jelennek meg.

Az alábbi ábrán a D2 cella tartalmazza a fizetési képletet, =RÉSZLET(B3/12,B4,-B5), amely a B3 beviteli cellára hivatkozik.

Kétváltozós adattáblák 

Kétváltozós adattáblát akkor érdemes használnia, ha meg szeretné tudni, hogy két változó különböző értékei egyetlen képletben hogyan módosítják a képlet eredményét. Kétváltozós adattáblát használva például megállapíthatja, hogy a kamatlábak és a futamidők különböző kombinációi miként befolyásolják a havonta fizetendő részletet.

Az alábbi ábrán a C2 cella tartalmazza a fizetési képletet, =RÉSZLET(B3/12,B4,-B5), amely két beviteli cellát használ: B3 és B4.

Adattábla-számítások 

Amikor egy munkalap újraszámításra kerül, az adattáblákat is újraszámítja a rendszer – még akkor is, ha az adatokban nem történt módosítás. Az adattáblát tartalmazó munkalapok számításának felgyorsításához módosíthatja a Számítás beállításait úgy, hogy a rendszer automatikusan újraszámolja a munkalapot, de az adattáblákat nem. További információ: A számítás felgyorsítása olyan munkalapon, amely adattáblákat tartalmaz.

Egyváltozós adattábla készítése

Az egyváltozós adattábla beviteli értékei egyetlen oszlopban (oszloporientált) vagy sorban (sororientált) találhatók. Az egyváltozós adattáblákban szereplő képletek csak egy beviteli cellára hivatkozhatnak.

Kövesse az alábbi lépéseket:

1. Írja be a beviteli cellába behelyettesíteni kívánt értékek listáját egy sorba vagy egy oszlopba. Hagyjon néhány üres sort és oszlopot az értékek mindkét oldalán.

2. Tegye a következők valamelyikét:

   • Ha az adattábla oszloporientált (a változóértékek egy oszlopban találhatók), írja be a képletet a cellába egy sorral az értékoszlop felett és egy cellával jobbra. Ez az egyváltozós adattábla oszloporientált, és a képlet a D2 cellában található.

     

     Ha többféle érték más képletekre gyakorolt hatását szeretné vizsgálni, írja be a további képleteket az első képlettől jobbra eső cellákba.

   • Ha az adattábla sororientált (a változóértékek egy sorban vannak), írja be a képletet az első érték bal oldalán lévő oszlopba és az értékek sora alatt egy cellával lejjebb lévő cellába.

     Ha meg szeretné vizsgálni a különböző értékek más képletekre gyakorolt hatását, adja meg a további képleteket az első képlet alatti cellákban.

3. Jelölje ki a képleteket és a helyettesíteni kívánt értékeket tartalmazó cellatartományt. A fenti ábrán ez a tartomány a C2:D5.

4. Az Adatok lapon válassza a Lehetőségelemzés > adattáblát (az Excel 2016 Adateszközök vagy Előrejelzés csoportjában).

5. Tegye a következők valamelyikét:

   • Ha az adattábla oszloporientált, írja be a beviteli cella hivatkozását az Oszlopértékek bemeneti cellájának mezőbe. A fenti ábrán a bemeneti cella a B3.

   • Ha az adattábla sororientált, adja meg a beviteli cella hivatkozását a Sor beviteli cellája mezőben.

     Megjegyzés

     Az adattábla létrehozását követően érdemes módosítania az eredménycellák formátumát. Az ábrán az eredménycellák pénznemként vannak formázva.

Képlet beírása egyváltozós adattáblába

Az egyváltozós adattáblában használt képleteknek ugyanarra a bemeneti cellára kell hivatkozniuk.

Követendő lépések

1. Tegye a következők egyikét:

   • Ha az adattábla oszloporientált, írja be az új képletet egy üres cellába az adattábla felső sorában lévő meglévő képlet jobb oldalán.
   • Ha az adattábla sororientált, írja be az új képletet egy üres cellába egy meglévő képlet alatt az adattábla első oszlopába.

2. Jelölje ki azt a cellatartományt, amely az adattáblát és az új képletet tartalmazza.

3. Az Adatok lapon válassza a Lehetőségelemzés>adattáblát (az Excel 2016 Adateszközök vagy Előrejelzés csoportjában).

4. Az alábbi lehetőségek közül választhat:

   • Ha az adattábla oszloporientált, írja be a beviteli cella hivatkozását az Oszlop beviteli cellája mezőbe.
   • Ha az adattábla sororientált, adja meg a bemeneti cella hivatkozását a Sor bemeneti cellája mezőben.

Kétváltozós adattábla készítése

A kétváltozós adattábla olyan képletet használ, amely két bemenetiérték-listát tartalmaz. A képletnek két különböző bemeneti cellára kell hivatkoznia.

Kövesse az alábbi lépéseket:

1. A munkalap egyik cellájába írja be azt a képletet, amely a két bemeneti cellára hivatkozik.
   A következő példában, amelyben a képlet kezdőértékeit a B3, B4, és B5 cellában adtuk meg, írja be a =RÉSZLET(B3/12;B4;-B5) képletet a C2 cellába.
2. A bemeneti értékek egyik felsorolását írja a képlet alá ugyanebbe az oszlopba.
   Ebben az esetben írja be a különböző kamatlábakat a C3, a C4 és a C5 cellába.
3. Töltse ki a második listát ugyanebben a sorban a képlettől jobbra.
   Írja be a futamidőket (hónapokban) a D2 és az E2 cellába.
4. Jelölje ki a képletet tartalmazó cellatartományt (C2), az értékek sorát és oszlopát (C3:C5 és D2:E2), valamint azokat a cellákat, amelyekben a számított értékeket meg szeretné jeleníteni (D3:E5).
   Ebben az esetben jelölje ki a C2:E5 tartományt.
5. Az Adatok lap Adateszközök vagy Előrejelzés csoportjában (az Excel 2016-ban) válassza a Lehetőségelemzés > adattáblát (az Excel 2016 Adateszközök vagy Előrejelzés csoportjában).
6. A Sorértékek bemeneti cellája mezőbe írja be annak a bemeneti cellának a hivatkozását, amelybe az értékek sorát szeretné behelyettesíteni.
   Írja be a B4 cella értéket a sor bemeneti cellája mezőbe.
7. Az Oszlopértékek bemeneti cellája mezőbe írja be annak a bemeneti cellának a hivatkozását, amelybe az értékek sorát szeretné behelyettesíteni.
   Írja be a B3 értéket az Oszlopértékek bemeneti cellája mezőbe.
8. Kattintson az OK gombra.

Példa kétváltozós adattáblára

Kétváltozós adattábla segítségével megállapíthatja, hogy a kamatlábak és a futamidők különböző kombinációi miként befolyásolják a havonta fizetendő részletet. Az ábrán a C2 cellában található a fizetési képlet, =RÉSZLET(B3/12,B4,-B5), amely két bemeneti cellát használ, a B3-at és a B4-et.

Az adattáblákat tartalmazó munkalap számításának felgyorsítása

Ha beállítja ezt a számítási beállítást, nem történik adattábla-számítás, amikor újraszámítást végez a teljes munkafüzeten. Az adattábla manuális újraszámításához jelölje ki a képleteket, majd nyomja le az F9 billentyűt.

A számítási teljesítmény javításához kövesse az alábbi lépéseket:

1. Válassza a Fájlbeállítások>>képletek lehetőséget.

2. A Számítási beállítások szakaszban válassza az Automatikus lehetőséget.

   Tipp:

   Ha szeretné, a Képletek lapon válassza a Számítási beállítások menü nyilát, majd az Automatikus lehetőséget.

Mi a következő lépés?

Néhány egyéb Excel-eszközzel lehetőségelemzést végezhet, ha meghatározott céljai vannak vagy változóadatok nagyobb halmazát szeretné használni.

Célérték keresése

Ha ismeri a képlettől elvárható eredményt, de nem tudja pontosan, hogy a képletnek milyen bemeneti értékre van szüksége az eredmény eléréséhez, használja a Célérték keresése funkciót. Lásd a cikket: A Célérték keresés használata a kívánt eredmény eléréséhez egy bemeneti érték módosításával.

Excel Megoldó

Az Excel Megoldó bővítményével megkeresheti a beviteli változók egy csoportjának optimális értékét. Ehhez a Megoldó a cellák olyan, döntési változóknak vagy egyszerűen változócelláknak nevezett csoportját használja fel, amelyek a képletek kiszámításához használhatók a célérték- vagy a korlátozáscellákban. A Solver úgy módosítja a döntési változócellák értékeit, hogy megfeleljenek a korlátozáscella megkötéseinek és a célértékcellához kívánt eredményt hozza létre. További információ: Probléma definiálása és megoldása a Megoldó használatával.

Ha különböző számokat ír egy cellába, gyorsan különböző válaszokat találhat egy problémára. Erre jó példa a RÉSZLET függvény használata különböző kamatlábakkal és hitelidőszakokkal (hónapokban kifejezve), hogy kiszámítsa, mekkora hitelt tud felvenni egy ház vagy autó megvásárlásához. A számokat egy adattábla nevű cellatartományba kell beírnia.

Itt az adattábla a B2:D8 cellatartomány. A B4 cellában lévő értéket módosíthatja, a hitelösszeg és a havi törlesztőrészletek a D oszlopban automatikusan frissülnek. 3,75% kamatlábat alkalmazva a D2 cellában a következő képlet segítségével 1042,01 dolláros havi törlesztőrészletet kapunk: =RÉSZLET(C2/12,$B$3,$B$4).

A vizsgálni kívánt változók és képletek számától függően egy- vagy kétváltozós adattáblákat hozhat létre.

Egyváltozós teszt használatával megállapíthatja, hogy egy képlet egy változójának különböző értékei hogyan módosítják az eredményeket. Például a RÉSZLET függvény segítségével módosíthatja a havi jelzáloghitel-törlesztés kamatlábát. A változó értékeket (a kamatlábakat) írja be egy oszlopba vagy sorba, és az eredmények a szomszédos oszlopban vagy sorban jelennek meg.

Ebben az élő munkafüzetben a D2 cella a =RÉSZLET(C2/12;$B 3 $B 4 Ft) törlesztőrészlet-számító képletet tartalmazza. A B3 cella a változó cella, ahová beírhat egy másik futamidőt (a havi törlesztési időszakok számát). A D2 cellában a RÉSZLET függvény beírja a 3,75%/12 kamatlábat, a 360 hónapot és a 225 000 dolláros hitelt, majd kiszámítja a 1042,01 dolláros havi törlesztőrészletet.

Használjon kétváltozós tesztet annak megtekintéséhez, hogy egy képlet két változójának különböző értékei hogyan módosítják az eredményeket. Például különböző kamatlábak és havi törlesztési időszakok kombinációit tesztelheti a jelzáloghitel törlesztőrészletének kiszámításához.

Ebben az élő munkafüzetben a C3 cella a =RÉSZLET($B 3/12 Ft$B 2 Ft;B4) törlesztőrészlet-számító képletet tartalmazza, amely két változócellát (B2 és B3) használ. A C2 cellában a RÉSZLET függvény megadja a 3,875%/12 kamatlábat, 360 hónapot és egy 225 000 dolláros hitelt, majd kiszámítja a 1058,03 dolláros havi törlesztőrészletet.