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

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. A forgatókönyvek és adattáblák bemeneti értékkészleteket használnak a lehetséges eredmények kiszámításához. 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 beviteli é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.

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.

Az egyváltozós adattáblák 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 cella 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ábla (az Adateszközök csoportjában vagy az Előrejelzés Excel 2016 csoportjában).

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

   • Ha az adattábla oszloporientált, adja meg a beviteli cella cellahivatkozás értékét az Oszlop beviteli cella mezőben. 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.

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 Adateszközök csoportban vagy az ElőrejelzésExcel 2016 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.

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 (Excel 2016) válassza a Lehetőségelemzés > adattáblát (az Adateszközök csoportban vagy az Előrejelzés Excel 2016 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.
   Az Oszlop bemeneti cellája mezőbe írja be a B3 értéket.

8. Válassza az OK gombot.

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.

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. Kattintson a Fájl > Beállítások > Képletek lehetőségre.

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.

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.