összeadás az Excelben - függvények
2020-09-11    Excel 2019
Mindennapi használatra, illetve mindennapi feladatok elvégzésére az Excel hat összeadó függvényt biztosít.
az összeadás függvényei az Excelben
A SZUM függvény az argumentumaival meghatározott számokat adja össze. Argumentumai lehetnek állandók, cella- és tartomány-hi­vat­ko­zá­sok és kifejezések. Maximális számuk kettőszáz-ötven-öt. A függvény csak a kifejezés-argumentumai eredményeként kapott IGAZ logikai értéket és szövegként tárolt számokat veszi figyelembe, a hivatkozás-argumentumaival meghatározottakat nem.
a SZUM függvény működése
Tehát a SZUM( A2 ; B2:B4 ; C2:D2 ) képlet eredménye 1000 lesz, mert a C2-ben álló logikai értéket és a D2-ben szövegként tárolt számot a SZUM függvény nem veszi figyelembe. De a SZUM( A2 ; B2:B4 ; HA( 2 * 2 = 4 ; IGAZ ) ; SZÖVEG( 2 / 2 ; ”#” )) képlet már 1002-t ad eredményül, mert a két ki­fe­je­zés-argumentum eredménye is része az összegnek.
a SZUM függvény működése
Ha sok tartományt kell argumentumként megadnunk, akkor a tartományok kijelölését ne szakítsuk meg a pontosvesszők beírásával, mert azt a program automatikusan megteszi! Tehát, [1] beírjuk az egyenlőségjelet és a SZUM-ot. Utóbbit a Tab billentyűvel visszük be a kép­let­be, mert akkor a nyitó zárójelet se nekünk kell begépelnünk, [2] kijelöljük az első tartományt, [3] lenyomjuk a Ctrl billentyűt és ad­dig nyom­va tart­juk, amíg ki nem jelöltük az utolsó tartományt is… Ugye azt mindenki tudja, hogy az egyetlen függvényt tartalmazó kép­let, amit ne­vez­het­nénk akár egytényezős képletnek is, mint például =SZUM( <tartományok> ), záró zárójelét sem kell nekünk begépelni? Azt a prog­ram au­to­ma­ti­ku­san megteszi a szerkesztés lezárását követően.
    A SZUM függvény hibaértéket ad eredményül, ha hivatkozás argumentumának valamely cellájában hibaérték áll vagy bármely ki­fe­je­zés-argumentumának kiértékelése hibaértéket eredményez. Az eredmény azonos az első hibaértékkel.
hibaértékes tartományok összeadása a SZUM függvénnyel
A hetedik sorban álló SZUM függvények hivatkozás-argumentumával meghatározott hibaértékek megakadályozzák az oszlop-összegek kép­zé­sét: =SZUM( A1:A6), =SZUM( B1:B6), =SZUM( C1:C6 )… Nézzünk két példát a kifejezés-argumentumok hibaértékeire is.
a SZUM függvény er5edménye hibaérték
Az első képlet nem igényel magyarázatot, de a második már igen. A képletben a kifejezés-argumentum kiértékelése nem hibaértéket, ha­nem egy karakterláncot eredményez. Tehát, a SZUM függvény hibaértékes eredményét nem csak egyik argumentumának hibája, ha­nem a számmá nem konvertálható szöveg is okozhatja.
    Szűrt- és rejtett sorokat tartalmazó táblázatban a függvény működik ugyan, de nem úgy, ahogy várnánk: a nem látható számokat is sze­re­pel­te­ti az összegben.
a SZUM függvény szűrt és rejtett sorokat tartalmazó táblázatban
A képen látható táblázatot napokra szűrtük, de a B9 cellában álló SZUM függvény eredményét ez nem befolyásolta. A helyzet, akkor sem változik, ha a szűrést a sorok rejtésével helyettesítjük.
    A SZUM függvényt részösszeges táblázatban sem tudjuk használni, mert a függvény nem ismeri fel a részösszegeket tartalmazó SZUM-os cellákat és ezért a részösszegeket is szerepelteti a végösszegben.
a SZUM függvény részösszeges táblázatban
A SZUM függvény mentségére legyen mondva, a legtöbb statisztikai függvény hasonló helyzetekben hasonlóan viselkedik.
    A problémát az ÖSSZESÍT és a RÉSZÖSSZEG függvényekkel orvosolhatjuk. Kezdjük a szűrt- illetve a rejtett sorokat tartalmazó táb­lá­za­tok­kal. A függvények első argumentuma egy szám: a helyettesítendő függvény azonosítója. A SZUM azonosítója az ÖSSZESÍT-ben a 9-es, a RÉSZ­ÖSSZEG-ben a 109-es. Utóbbi esetében, azért nem a „sima” 9-es, mert az a rejtett sorokban álló számokat is hozzáadja az összeghez. A szűréssel elrejtett sorokban állókat viszont alapból kihagyja.
az ÖSSZESÍT és a RÉSZÖSSZEG függvények első argumentuma
Az ÖSSZESÍT viszont nem tesz különbséget a szűréssel vagy a rejtéssel láthatatlanná tett számok között. Ha a második argumentuma 5-ös, akkor a nem látható számok nem szerepelnek az összegben.
az ÖSSZESÍT függvény második argumentuma
A függvények további argumentumai az összeg-tartományok hivatkozásai. Természetesen, most csak a függvények összeadó funk­ci­ó­já­val foglalkozunk.
    Ahogy a képen is látható az ÖSSZESÍT a hibaértékes tartományokban is tud összeadni, ha erre a második argumentumában álló 6-os­sal utasítjuk. Sőt a 7-essel nem csak a hibaértékeket, de a nem látható számokat is mellőzi.
    Részösszeges tartomány SZUM-os részösszegeit sem az ÖSSZESÍT sem a RÉSZÖSSZEG függvénnyel nem tudjuk kizárni az összegből. Egyet tehetünk lecseréljük a SZUM-okat RÉSZÖSSZEG-re.
SZUM függvény cseréje részösszeges táblázatban RÉSZÖSSZEG-re
A fenti részösszeg-végösszeg felépítést az ÖSSZESÍT függvénnyel is kialakíthatjuk, sőt a függvények „felismerik” egymást, a rész­össze­gek képletében. Természetesen az ÖSSZESÍT csak akkor, ha erre a második argumentumával utasítjuk.
    Ne felejtsük el azt a lehetőséget sem, hogy az automatikus statisztikai képlet szolgáltatással, népi nevén az autoszum-mal, össze tudjuk adatni a SZUM-os részösszegeket.
    A SZUM függvény nem válogat a számok között, az argumentumaival meghatározott összes számot összeadja. Ezzel szemben, a be­mu­ta­tás­ra kerülő további függvények már lehetőséget kínálnak az összeadandó számok szelektálására is.
    Mielőtt folytatnánk, tisztázzunk néhány fogalmat. Az összeadásra kerülő számok általában egy táblázatban állnak. Mondjuk egy osz­lop­ban. Ha a táblázat első sorában és első oszlopában címkék állnak, és általában állnak, akkor minden számot meg tudunk nevezni.
az összeadandó szám tulajdonságai
Például a képen megjelölt szám a 05-59-69-48 számú számla egyenlege. Tehát a szám neve „egyenleg”, azonosítója a „számlaszám”. Az egyenleg szempontjából, a sorában álló további adatok, az egyenleg tulajdonságai.
    A SZUMHA függvény a feltételes összeadás eszköze. Az összeadandó számok körét egy feltétellel szabályozzuk, amely vonatkozhat magára a számra vagy egyik tulajdonságára. A függvény három argumentuma, sorrendben a következő. [1] Feltétel-tartomány hi­vat­ko­zá­sa: azoknak az adatoknak a cellái, amelyekben a feltétel teljesülését vizsgálni kell. [2] Feltétel: relációból és a viszonyítási alapból álló logikai kifejezés. [3] Összeg-tartomány hivatkozása: az összeadandó számok cellái. Ha a feltétel közvetlenül a számokra irányul, akkor a számok tartományát nem kell még egyszer megadnunk.
    Nézzünk egy-egy példát a függvény alkalmazására. Kíváncsiak vagyunk a pozitív egyenlegű számlák egyenlegeinek összegére. Te­hát a feltétel most közvetlenül az összeadandó számokra irányul: SZUMHA( <egyenlegek cellái> ; <nagyobb mint nulla> ). De, ha azt kér­dez­zük, hogy mennyi a pécsi számlák egyenlegeinek összege, akkor már a feltétel az összeadandó számok egy tulajdonságára vo­nat­ko­zik: SZUM­HA( <városok cellái> ; <pécs> ; <egyenlegek cellái> ).
    A függvény második argumentumának írásmodja kötött és szabályai számosak. Megpróbálom értelmesen ismertetni őket. A feltétel két részből áll, reláció és viszonyítási alap. Kivéve, ha a reláció egyenlőség. Azt nem jelöljük. Tehát a reláció nélküli feltétel-ar­gu­men­tum­mal azonosságot írunk elő! A relációs jelek sorrendje kötött. Nem egyenlő: <>. Kisebb vagy egyenlő: <=. Nagyobb egyenlő: >=.
a SZUMHA függvény feltételének összetétele: reláció és viszonyítási alap
A viszonyítási alap lehet szám, dátum, idő, szöveg, logikai érték vagyis állandó, cella-hivatkozás és kifejezés.
a SZUMHA feltételének viszonyítási alapja lehet állandó, hivatkozás és kifejezés
A feltétel idézőjelek között áll. Kivéve, ha a reláció egyenlő és a viszonyítási alap szám vagy logikai érték vagy hivatkozás vagy ki­fe­je­zés.
a SZUMHA függvény feltételének idézőjelezése és az idézőjelének elhagyása
Szám-állandó tartalmazhat tizedesvesszőt, százalék jelet és megadhatjuk a számot normál alakban is. További formátumoktól, például Ft vagy HUF tartózkodjunk! Egyenlő reláció esetén a feltétel csak akkor teljesül, ha a viszonyítási alap és a cella tartalma a pontosság szerint azonos. Magyarán, ha a viszonyítási alap 1,2, akkor sem a 1,20001, sem a 1,199 nem teljesíti a feltételt.
szám-állandó a SZUMHA függvény feltétel-argumentumában
A dátum-állandóban a dátumegységeket elválaszthatjuk ponttal, kötőjellel vagy perjellel. Idő-állandóban csak a kettőspontot ismeri fel a függvény. Az elválasztó karakter után szóköz is állhat. A dátumban az évszázad két számjegye, valamint a hónap-szám és a nap-szám ve­ze­tő nullája elhagyható. Idő-állandóban az órák vezető nullja hagyható el. A dátum-feltétel viszonyítási alapjában pozitív egész szá­mot, az idő-feltételben egynél kisebb, pozitív számot is megadhatunk. Magyarul, használhatjuk a dátum és időpont illetve időegység szám-ér­té­két is. Az idő-feltételben megadott ó:pp formátumú idő automatikusan nulla másodperccel egészül ki: ó:pp:00. Az azonosság el­len­őr­zé­sé­nél ezt figyelembe kell vennünk!
dátum és idő a SZUMHA függvény feltétel-argumentumában
Vegyes adattípusú tartományban a szöveg adattípusú adatokat a csillag karakterrel választhatjuk ki: ”*”. A feltétel tagadása (”<>*”) az összes nem szöveg adattípusú adat elfogadását jelenti, beleértve az üres és a hibaértéket tartalmazó cellákat is.
szövegek kiválasztása a SZUMHA függvény második argumentumával
A függvény a karakterláncok vizsgálatánál a kis- és nagybetűket nem különbözteti meg. A feltételben használhatunk helyettesítő ka­rak­te­re­ket is. Csillag (*): nem meghatározott számú karakter. Kérdőjel (?): egy karakter. A helyettesítő funkciót ellátó karaktereket a hul­lám­vo­nal (~) karakter segítségével kereshetjük. A csillagot: ~*. A kérdőjelet: ~?.
karakterláncok keresése a SZUMHA függvény második argumentumával
Ha a viszonyítási alap hivatkozás vagy kifejezés, akkor a reláció idézőjelek között áll és end (&) jellel kapcsolódik a viszonyítási alap­hoz.
hivatkozás relációval a SZUMHA függvény második argumentumában
Az üres cellát a feltételben nulla hosszúságú szöveggel, a tartalommal rendelkezőt pedig az idézőjelek között álló, viszonyítási alap nél­kü­li, nem egyenlő relációval adjuk meg.
üres és tartalommal rendelkező cellák kiválasztása a SZUMHA függvény második argumentumával
Tehát a függvény három argumentuma sorrendben: a feltétel-tartomány hivatkozása, a feltétel és az összeg-tartomány hivatkozása. A függ­vény szépen sorban, megvizsgálja a feltétel-tartomány celláit, teljesíti-e a cellában álló adat a feltételt. Ha igen, akkor megkeresi az összeg-tartomány azonos pozíciójában álló celláját és a benne álló számot hozzáadja az összeghez. Ha a feltétel közvetlenül a számra irányul, ak­kor még keresgélnie sem kell.
üres és tartalommal rendelkező cellák kiválasztása a SZUMHA függvény második argumentumával
Elárulok egy titkot. A függvény az összeg-tartomány deklarációjának csak az első celláját veszi figyelembe. Ettől a cellától kezdve „méri ki” a feltétel-tartomány sor- és oszlopszámával azonos összeg-tartományt. Tehát, ha csak ezt írjuk: =SZUMHA( B3:C6 ; ”<10” ; D3 ), a függ­vény működni fog. De ez, azt is jelenti, hogyha az összeg-tartományt rosszul adjuk meg, akkor a függvény nem küld hibaüzenetet, nem ad hi­ba­ér­té­ket eredményül, csak éppen a kapott összeg nem megfelelő. És figyelem, figyelem! A feltétel szintaktikai hibája esetén is ugyanez tör­té­nik!
    A SZUMHATÖBB függvény a SZUMHA továbbfejlesztett és javított változata. Nemcsak egy, de akár száz-húszon-hét feltételt is meg­szab­ha­tunk közvetlenül az összeadandó számok- vagy valamely tulajdonságuk szelektálására. Első argumentuma az összeg-tar­to­mány hi­vat­ko­zá­sa. További argumentumai párban állnak: feltétel-tartomány hivatkozása, feltétel. A feltétel-tartományok sor- és osz­lop­szá­má­nak azo­nos­nak kell lenniük az összeg-tartomány sor- és oszlopszámával. Az előző bekezdésben ismertetett „laza működésnek” ennél a függ­vény­nél már nyoma sincs: az eltérő alakú tartományok hibaértéket a szintaktikai hibák hibaüzenetet generálnak. A feltétel ar­gu­men­tu­mok írás­mód­ja azonos a SZUMHA feltételének írásmódjával.
    A függvény egy munkamenetben az azonos pozícióban álló feltétel-tartományok celláit vizsgálja: tartalmuk teljesíti-e a tar­to­má­nyuk­hoz rendelt feltételt. Ha az összes feltétel teljesült, akkor az összeg-tartomány azonos pozíciójú cellájának száma hozzáadódik az összeg­hez.
üres és tartalommal rendelkező cellák kiválasztása a SZUMHA függvény második argumentumával
A fenti táblázatban a váci eladásokat összesítettük, de csak azokat, amelyeknek a mennyisége meghaladja az egyet.
    A szorzatok összeadása mindennapi művelet. Gondoljunk csak egy vásárlásra: több tétel eltérő darabszámmal és eltérő egységárral. Mennyit kell majd fizetnünk?
mennyit fizetünk, szorzatok összeadása
Az összeget a TÖBBSZ.KEREKÍT függvénnyel az öt többszörösére kerekítettem. A függvény első argumentumával a kerekítendő számot a másodikkal a kerekítés alapját deklaráljuk.
    [népművelés] Egy képletet aposztróffal kezdve tudunk szövegként megjeleníteni a cellában: ’<képlet>. De ezt teszi a KÉPLETSZÖVEG függvény is, amely az egyetlen argumentumaként megadott cella képletét alakítja át szöveggé. [népművelés vége]
    Szóval van két tartományunk „mennyiség” és „egységár”. A tartományok azonos pozíciójú számait összeszoroztuk, majd a szor­za­to­kat összeadtuk. Pontosan ezt teszi a SZORZATÖSSZEG függvény is. Argumentumai a tartományok hivatkozásai. Nézzünk még egy pél­dát, amin megfigyelhetjük a függvény használatát.
mennyit fizetünk, szorzatok összeadása
Szándékosan választottam olyan példát, amelyben az összeszorzandó tartományok nem függőleges tájolásúak. Ez az elrendezés ritka, ezért gyakran zavarba hozza a gyanútlan Excel-használót…
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com