Excel döntéshozatalhoz

a képletek mellett a matematikai számítások Excelben történő elvégzésének másik módja a függvények. A statisztikai függvények matematikai folyamatot alkalmaznak a munkalap celláinak egy csoportjára. Például a SUM függvény a cellatartományban található értékek hozzáadására szolgál. Az általánosan használt statisztikai függvények listáját a 2.4.táblázat mutatja. A függvények hatékonyabbak, mint a képletek, ha matematikai folyamatot alkalmaz egy cellacsoportra. Ha képlet segítségével adja hozzá az értékeket egy cellatartományban, akkor minden cellahelyet egyszerre kell hozzáadnia a képlethez. Ez nagyon időigényes lehet, ha az értékeket néhány száz cellahelyen kell hozzáadnia. Ha azonban függvényt használ, akkor egyetlen lépésben kiemelheti az összes olyan cellát, amely az összegezni kívánt értékeket tartalmazza. Ez a szakasz számos statisztikai funkciót mutat be, amelyeket hozzáadunk a személyes költségvetési munkafüzethez. A függvények bemutatása mellett ez a szakasz az összes számítás százalékos arányát és az abszolút hivatkozások használatát is áttekinti.

2.táblázat.4 általánosan használt statisztikai függvények

a SUM függvény akkor használható, ha a munkalapon egy cellatartomány vagy kiválasztott cellák csoportjának összegét kell kiszámítania. Ami a költségvetési részletes munkalapot illeti, a SUM függvényt fogjuk használni a 12.sorban szereplő összegek kiszámításához. Fontos megjegyezni, hogy számos módszer létezik egy függvény hozzáadására a munkalaphoz, amelyet a fejezet hátralévő részében bemutatunk. Az alábbiakban bemutatjuk, hogyan lehet egy függvényt hozzáadni egy munkalaphoz egy cellahely beírásával:

1. kattintson a költségkeret részletes munkalap lapra a munkalap megnyitásához.
2. kattintson a C12 cellára.
3. írja be az egyenlőségjelet =.
4. írja be a függvény nevét SUM.
5. írjon be egy nyitott zárójelet (.
6. kattintson a C3 cellára, majd húzza le a C11 cellába. Ez a C3:C11 tartományt helyezi a funkcióba.
7. írja be a záró zárójelet ).
8. nyomja meg az ENTER billentyűt. A függvény kiszámítja a havi költés oszlop teljes összegét, amely 1496 USD.

a 2.11.ábra a költségkeret részletes munkalapjához hozzáadott összeg függvény megjelenését mutatja az ENTER billentyű megnyomása előtt.

a 2.11. ábrán látható módon a SUM függvényt hozzáadtuk a C12 cellához. Ez a függvény azonban az éves kiadások és a LY kiadások oszlopok összegének kiszámításához is szükséges. A függvény másolható és beilleszthető ezekbe a cellákba a relatív hivatkozás miatt. A relatív hivatkozás ugyanazt a célt szolgálja a függvényeknél, mint a képleteknél. Az alábbiakban bemutatjuk, hogyan fejeződik be a teljes sor:

1. kattintson a C12 cellára a költségvetési Részletek munkalapon.
2. kattintson a Másolás gombra a szalag Kezdőlap lapján.
3. jelölje ki a D12 és E12 cellákat.
4. kattintson a Beillesztés gombra a szalag Kezdőlap lapján. Ez beilleszti a SUM függvényt A D12 és E12 cellákba, és kiszámítja ezeknek az oszlopoknak az összegét.
5. kattintson az F11 cellára.
6. kattintson a Másolás gombra a szalag Kezdőlap lapján.
7. kattintson az F12 cellára, majd kattintson a Beillesztés gombra a szalag Kezdőlap lapján. Mivel most már a 12. sorban vannak összegek, beilleszthetjük a százalékos változás képletét ebbe a sorba.

a 2.12.ábra a C12, D12 és E12 cellákhoz hozzáadott ÖSSZEGFÜGGVÉNY kimenetét mutatja. Ezenkívül a százalékos változás képletét átmásolták és beillesztették az F12 cellába. Vegye figyelembe, hogy a költségvetés ezen verziója a kiadások 1,7% – os csökkenését tervezi az előző évhez képest.

abszolút hivatkozások (az összegek százalékának kiszámítása)

adatfájl: folytassa a CH2 személyes költségvetéssel.

mivel a költségvetés részletezése munkalap 12. sorába az összegek kerültek, a teljes számítás egy százaléka hozzáadható a B oszlophoz, amely a B3 cellában kezdődik. A teljes számítás százaléka az éves költés oszlopban az egyes értékek százalékos arányát mutatja A D12 cellában szereplő összeghez viszonyítva. A képlet létrehozása után azonban ki kell kapcsolnia az Excel relatív hivatkozási funkcióját, mielőtt átmásolja és beilleszti a képletet az oszlop többi cellájába. Az Excel relatív hivatkozási funkciójának kikapcsolása abszolút hivatkozással történik. A következő lépések elmagyarázzák, hogyan történik ez:

1. kattintson a B3 cellára a költségvetési Részletek munkalapon.
2. írja be az egyenlőségjelet =.
3. kattintson a D3 cellára.
4. a típusú perjel/.
5. kattintson a D12 cellára.
6. nyomja meg az ENTER billentyűt. Látni fogja, hogy a háztartási közművek az éves kiadási költségvetés 16,7% – át teszik ki (lásd: 2.14.ábra).

százalékának kiszámításához 2. ábra.A 14. ábra azt a kitöltött képletet mutatja, amely kiszámítja azt a százalékot, amelyet a háztartási közművek éves kiadása képvisel a költségvetés teljes éves kiadásához képest (lásd a B3 cellát). Általában átmásoljuk ezt a képletet, és beillesztjük a B4:B11 tartományba. A relatív hivatkozás miatt azonban mindkét cellahivatkozás egy sorral növekszik, amikor a képletet beillesztik a B3 alatti cellákba. Ez rendben van a képlet első cellahivatkozásánál (D3), de a második cellahivatkozásnál (D12) nem. A 2.15. ábra szemlélteti, mi történik, ha a képletet a jelenlegi állapotában a B4:B12 tartományba illesztjük. Figyelje meg, hogy az Excel előállítja a #DIV/0 hibakódot. Ez azt jelenti, hogy az Excel megpróbálja megosztani a számot nullával, ami lehetetlen. A B4 cellában lévő képletet tekintve láthatja, hogy az első cellahivatkozás D3-ról D4-re változott. Ez rendben van, mert most el akarjuk osztani a biztosítási éves kiadásokat a D12 cellában lévő teljes éves kiadással. Az Excel azonban a D12 cellahivatkozást is megváltoztatta D13-ra. Mivel a D13 cellahely üres, a képlet előállítja a #DIV/0 hibakódot.

a 2.15 ábrán látható nulla Osztás hiba kiküszöböléséhez abszolút hivatkozást kell hozzáadnunk a képletben a D12 cellához. Az abszolút hivatkozás megakadályozza, hogy a relatív hivatkozás megváltoztassa a képlet cellahivatkozását. Ez is nevezik zár egy cella. Az alábbiakban bemutatjuk, hogyan valósul meg ez:

1. kattintson duplán a B3 cellára.
2. helyezze az egérmutatót a D12 elé, majd kattintson. A villogó kurzornak a D előtt kell lennie a D12 cellahivatkozásban.
3. nyomja meg az F4 billentyűt. A D oszlop és a 12.sor elé egy dollárjel ($) kerül. A dollárjeleket az oszlop betűje és sorszáma elé is beírhatja.
4. nyomja meg az ENTER billentyűt.
5. kattintson a B3 cellára.
6. kattintson a Másolás gombra a szalag Kezdőlap lapján.
7. jelölje ki a B4:B11 tartományt.
8. kattintson a Beillesztés gombra a szalag Kezdőlap lapján.

a 2.16.ábra a teljes képlet százalékát mutatja, abszolút hivatkozással hozzáadva a D12-hez. Figyeljük meg, hogy a B4 cellában a cellahivatkozás D12 marad, ahelyett, hogy D13-ra váltana, amint azt a 2.15.ábra mutatja. Azt is látni fogja, hogy a százalékokat az oszlop többi cellájában számítják ki, és az Osztás nullával hiba megszűnik.

a gróf funkció

adatfájl: folytassa a CH2 személyes költségvetéssel.

a következő funkció, amelyet hozzáadunk a költségvetési részletes munkalaphoz, a COUNT funkció. A COUNT függvény annak meghatározására szolgál, hogy egy tartomány hány cellája tartalmaz numerikus bejegyzést. A COUNT funkció nem működik a szöveg vagy más nem numerikus bejegyzések számlálásához. A költségvetési Részletek munkalaphoz a COUNT függvény segítségével megszámoljuk az éves költés oszlopban (D oszlop) tervezett elemek számát. Az alábbiakban bemutatjuk, hogy a COUNT függvény hogyan kerül hozzáadásra a munkalaphoz a függvénylista használatával:

1. kattintson a D13 cellára a költségvetési Részletek munkalapon.
2. írja be az egyenlőségjelet =.
3. írja be a C betűt.
4. kattintson a lefelé mutató nyílra a függvénylista görgetősávján (lásd a 2.17. ábrát), és keresse meg a szavak számát.
5. kattintson duplán a szavak száma a funkció listából.
6. jelölje ki a D3:D11 tartományt.
7. beírhat záró zárójelet), majd nyomja meg az ENTER billentyűt, vagy egyszerűen nyomja meg az ENTER billentyűt, és az Excel bezárja a funkciót az Ön számára. A függvény 9 kimenetet eredményez, mivel a munkalapon 9 elem van tervezve.

2.ábra.A 17.ábra a COUNT függvényhez tartozó 2. és 3. lépés elvégzése után megjelenő funkciólistát mutatja. A függvénylista alternatív módszert kínál a függvény munkalaphoz történő hozzáadásához.

a 2.18. ábra a számlálási funkció kimenetét mutatja az ENTER gomb megnyomása után. A függvény megszámolja a D3:D11 tartományban lévő cellák számát, amelyek numerikus értéket tartalmaznak. Az 9 eredménye azt jelzi, hogy 9 kategóriát terveznek erre a költségvetésre.

az átlagos funkció

a következő funkció, amelyet hozzáadunk a költségvetési részletes munkalaphoz, az átlagos funkció. Ez a függvény egy számcsoport számtani átlagának kiszámítására szolgál. A költségvetési Részletek munkalaphoz a függvényt használjuk az éves kiadások oszlop értékeinek átlagának kiszámításához. Ezt a függvénykönyvtár használatával adjuk hozzá a munkalaphoz. A következő lépések elmagyarázzák, hogyan történik ez:

1. kattintson a D14 cellára a költségvetési Részletek munkalapon.
2. kattintson a menüszalag képletek lapjára.
3. kattintson a További funkciók gombra a Funkciókönyvtár parancsok csoportjában.
4. helyezze az egérmutatót a statisztikai opció fölé az opciók legördülő listájából.
5. kattintson a menüben megjelenő függvények listájának átlagos függvénynevére (lásd a 2.19.ábrát). Ez megnyitja a függvény argumentumok párbeszédpanelt.
6. kattintson a párbeszédablak összecsukása gombra a funkció argumentumok párbeszédpanelen (lásd a 2.20.ábrát).
7. jelölje ki a D3:D11 tartományt.
8. kattintson a párbeszédablak kibontása gombra a funkció argumentumok párbeszédpanelen (lásd a 2.21.ábrát). Az ENTER billentyűt is megnyomhatja, hogy ugyanazt az eredményt kapja.
9. kattintson az Ok gombra a funkció argumentumok párbeszédpanelen. Ez hozzáadja az átlagos függvényt a munkalaphoz.

a 2.19.ábra szemlélteti, hogy a Függvénykönyvtárból hogyan kerül kiválasztásra egy függvény a menüszalag képletek lapján.

a 2.20. ábra a függvény argumentumok párbeszédpanelt mutatja. Ez akkor jelenik meg, ha EGY függvényt kiválasztottak a Függvénykönyvtárból. A párbeszédablak összecsukása gomb a párbeszédpanel elrejtésére szolgál, így egy cellatartomány kiemelhető a munkalapon, majd hozzáadható a funkcióhoz.

a 2.21. ábra bemutatja, hogyan lehet cellatartományt kiválasztani a függvény argumentumok párbeszédpanelen, miután összecsukta.

2.ábra.A 22. ábra a függvény argumentumok párbeszédpanelt mutatja az átlagos függvény cellatartományának meghatározása után. A párbeszédpanel megjeleníti a függvény eredményét, mielőtt hozzáadná a cellahelyhez. Ez lehetővé teszi a függvény kimenetének felmérését annak megállapításához, hogy van-e értelme, mielőtt hozzáadná a munkalaphoz.

a 2.23.ábra a befejezett átlagos függvényt mutatja A költségvetési részlet munkalapon. A függvény kimenete azt mutatja, hogy átlagosan 1994 dollárt költünk a költségvetés a oszlopában felsorolt kategóriák mindegyikére. Ez a kategóriánkénti átlagos kiadási számítás mutatóként használható annak meghatározására, hogy mely kategóriák kerülnek többe vagy kevesebbe, mint az átlagos költségvetésben szereplő kiadási Dollár.

a MAX és MIN funkciók

adatfájl: folytassa a CH2 személyes költségvetéssel.

az utolsó két statisztikai függvény, amelyet hozzáadunk a költségvetési részletes munkalaphoz, a MAX és a MIN függvény. Ezek a függvények a cellatartomány legmagasabb és legalacsonyabb értékeit azonosítják. A következő lépések ismertetik, hogyan adhatók hozzá ezek a függvények A költségvetési Részletek munkalaphoz:

1. kattintson a D15 cellára a költségvetési Részletek munkalapon.
2. írja be az egyenlőségjelet =.
3. írja be a MIN szót.
4. írjon be egy nyitott zárójelet (.
5. jelölje ki a D3:D11 tartományt.
6. írja be a záró zárójelet), majd nyomja meg az ENTER billentyűt, vagy egyszerűen nyomja meg az ENTER billentyűt, és az Excel bezárja a funkciót az Ön számára. A MIN függvény 1200 dollár kimenetet eredményez, ami a legalacsonyabb érték az éves költés oszlopban (lásd a 2.24.ábrát).
7. kattintson a D16 cellára.
8. írja be az egyenlőségjelet =.
9. írja be a MAX szót.
10. írjon be egy nyitott zárójelet (.
11. jelölje ki a D3:D11 tartományt.
12. írja be a záró zárójelet), majd nyomja meg az ENTER billentyűt, vagy egyszerűen nyomja meg az ENTER billentyűt, és az Excel bezárja a funkciót az Ön számára. A MAX funkció 3500 dolláros kimenetet eredményez. Ez a legmagasabb érték az éves kiadási oszlopban (lásd: 2.25.ábra).

képletek másolása és beillesztése (beillesztés formátumok nélkül)

adatfájl: folytassa a CH2 személyes költségkerettel.

a 2.25. ábrán látható módon a COUNT, AVERAGE, MIN és MAX függvények összegzik az adatokat az éves költés oszlopban. Azt is észre fogja venni, hogy van hely ezeknek a funkcióknak a másolására és beillesztésére A LY költés oszlop alatt. Ez lehetővé teszi számunkra, hogy összehasonlítsuk, mit költöttünk tavaly, és mit tervezünk költeni ebben az évben. Általában ezeket a funkciókat egyszerűen átmásoljuk és beillesztjük az E13:E16 tartományba. Előfordulhat azonban, hogy észrevette a kétsoros stílusú szegélyt, amelyet a B13:E16 tartomány kerületén használtak. Ha a szokásos Beillesztés parancsot használjuk, akkor az E13:E16 tartomány jobb oldalán található kettős Sort egyetlen sorra cseréljük. Ezért a Paste Special parancsok egyikét fogjuk használni, hogy csak a funkciókat illesszük be a formázási kezelések nélkül. Ez a következő lépésekkel érhető el:

1. jelölje ki a D13:D16 tartományt a költségvetési Részletek munkalapon.
2. kattintson a Másolás gombra a szalag Kezdőlap lapján.
3. kattintson az E13 cellára.
4. kattintson a lefelé mutató nyílra a beillesztés gomb alatt a szalag Kezdőlap lapján.
5. kattintson a képletek lehetőségre a gombok legördülő listájában (lásd a 2.26.ábrát).

a 2.26.ábra azon gombok listáját mutatja, amelyek akkor jelennek meg, amikor a menüszalag Kezdőlap lapján a beillesztés gomb alatti lefelé mutató nyílra kattint. Ezekkel a lehetőségekkel kapcsolatban egy dolgot meg kell jegyezni, hogy a kiválasztás előtt megtekintheti őket, ha az egérmutatót az opciók fölé húzza. Amint az az ábrán látható, amikor az egérmutatót a képletek gomb fölé helyezi, a kiválasztás előtt láthatja, hogyan jelennek meg a funkciók. Figyeljük meg, hogy a kétsoros szegély nem változik, ha ez a Beállítás előnézeti. Ezért történik ez a kiválasztás a szokásos paszta opció helyett.