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

A Microsoft Excelben az adattáblák a What-If elemző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.

Lehetőségelemzés: a cellák értékeinek módosítása annak megtekintéséhez, hogy ezek a változások hogyan befolyásolják a munkalapon lévő képletek eredményét. Egy adattáblával például módosíthatja a hitel kamatlábát és a futamidő hosszát, így kiértékelheti a lehetséges havi törlesztőrészleteket.

A lehetőségelemzés típusai    

Az Excelben háromféle lehetőségelemzési eszköz létezik: forgatókönyvek, adattáblák és célkeresés. A forgatókönyvek és adattáblák bemeneti értékek készleteit használják a lehetséges eredmények kiszámításához. A célérték keresése teljesen eltérő, egyetlen eredményt használ, és kiszámítja azokat a lehetséges bemeneti értékeket, amelyek ezt az eredményt eredményeznék.

Az esetekhez hasonlóan, az adattáblák segítségével lehetséges eredmények tárhatók fel. A forgatókönyvekkel ellentétben az adattáblák egy munkalapon egy táblázatban jelenítik meg az összes eredményt. 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 csak egy vagy két változóra korlátozódik (egy a sor bemeneti cellájára és egy az oszlop bemeneti cellájára), az adattáblák tetszőleges számú változóértéket tartalmazhatnak. 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 Bevezetés a What-If Analysis használatába című cikkben talál.

Hozzon létre egy- vagy kétváltozós adattáblákat 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ával például megnézheti, hogyan befolyásolják a különböző kamatlábak a havi jelzáloghitel-fizetést a RÉSZLET függvénnyel. 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 a =RÉSZLET(B3/12;B4;-B5) fizetési képletet tartalmazza, amely a B3 bemeneti 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 a =RÉSZLET(B3/12;B4;-B5) fizetési képletet tartalmazza, amely két bemeneti cellát használ: B3 és B4.

 

Adattábla-számítások    

Amikor egy munkalap újraszámolódik, az adattáblák is újraszámolódnak – még akkor is, ha az adatok nem változtak. Az adattáblát tartalmazó munkalapok számításának felgyorsításához módosíthatja a Számítási beállításokat úgy, hogy automatikusan újraszámolja a munkalapot, az adattáblákat azonban nem. További információt az Adattáblákat tartalmazó munkalap számításának felgyorsítása című szakaszában talál.

Az egyváltozós adattáblák bemeneti értékei egyetlen oszlopban (oszloporientált) vagy sorban (sororientált) találhatók. Az egyváltozós adattáblákban lévő képletek csak egy beviteli cella hivatkozhatnak.

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

1. Írja be a bemeneti cellába behelyettesíteni kívánt értékek listáját – egy oszloppal lejjebb vagy egy sorban. 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 feljebb és egy cellával az értékoszloptól jobbra. Ez az egyváltozós adattábla oszloporientált, és a képlet a D2 cellában található.
     
     
     
     Ha meg szeretné vizsgálni a különböző értékek más képletekre gyakorolt hatását, írja be a további képleteket az első képlettől jobbra lévő cellákba.

   • Ha az adattábla sororientált (a változóértékek egy sorban vannak), írja be a képletet a cellába az első érték bal oldalán egy oszloppal balra, és egy cellával az értéksor alatt.
     
     Ha meg szeretné vizsgálni a különböző értékek más képletekre gyakorolt hatását, írja be a további képleteket az első képlet alatti cellákba.

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 lehetőséget (az Adateszközök csoportban vagy a Excel 2016Előrejelzés csoportjában).

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

   • Ha az adattábla oszloporientált, adja meg a bemeneti cella cellahivatkozás az Oszlop bemeneti cella mezőjében. A fenti ábrán a bemeneti cella a B3.

   • Ha az adattábla sororientált, írja be a bemeneti cella hivatkozását a Sor típusú bemeneti cella mezőbe.

     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 egy meglévő képlet jobb oldalán az adattábla felső sorában.

   • 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ában.

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 a Excel 2016Előrejelzés csoportjában).

4. Hajtsa végre az alábbi műveletek egyikét:

   • Ha az adattábla oszloporientált, adja meg a bemeneti cella hivatkozását az Oszlop bemeneti cellái mezőben.

   • Ha az adattábla sororientált, adja meg a bemeneti cella hivatkozását a Sor típusú beviteli cella 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 a két bemeneti cellára hivatkozó képletet.

   A következő példában – amelyben a képlet kezdő értékeit a B3, A4 és a B5 cellába írja be, a C2 cellába írja be a =RÉSZLET(B3/12;B4;-B5) képletet.

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. Írja be a második listát ugyanabban a sorban, mint a képlet – 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 a Excel 2016Előrejelzés csoportjában).

6. A Sor bemeneti cella mezőjébe írja be a sor bemeneti értékeinek bemeneti cellájára mutató hivatkozást.
   Írja be a B4 cellát a Sorbeviteli cella mezőbe.

7. Az Oszlop bemeneti cella mezőjébe írja be az oszlop bemeneti értékeinek bemeneti cellájára mutató hivatkozást.
   Írja be a B3 kifejezést az Oszlop beviteli cellába .

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 alábbi ábrán a C2 cella tartalmazza a =RÉSZLET(B3/12;B4;-B5) fizetési képletet, amely két bemeneti cellát használ: B3 és B4.

A számítási beállítás megadásakor nem történik adattábla-számítás, ha a teljes munkafüzeten újraszámítás történik. 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ájl > Beá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.

Néhány más Excel-eszközzel lehetőségelemzést végezhet, ha konkrét céljai vannak, vagy nagyobb változóadat-készleteket használ.

Célérték keresése

Ha tudja, hogy milyen eredményt vár el egy képlettől, de nem tudja pontosan, milyen bemeneti értékre van szüksége a képletnek az eredmény eléréséhez, használja a Goal-Seek funkciót. A célérték módosításával keresse meg a kívánt eredményt a Célérték keresése című cikkben.

Excel Solver

Az Excel Solver bővítmény használatával megkeresheti a bemeneti változók halmazának optimális értékét. A Solver a cél- és kényszercellák képleteinek kiszámításához használt cellacsoportokkal (úgynevezett döntési változókkal vagy egyszerűen változócellákkal) működik. 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 Solver használatával.