ö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.
A SZUM függvény az argumentumaival meghatározott számokat adja össze. Argumentumai lehetnek állandók, cella- és tartomány-hivatkozá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.
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 kifejezés-argumentum eredménye is része az összegnek.
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épletbe, 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 addig nyomva tartjuk, amíg ki nem jelöltük az utolsó tartományt is… Ugye azt mindenki tudja, hogy az egyetlen függvényt tartalmazó képlet, amit nevezhetné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 program automatikusan 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 kifejezés-argumentumának kiértékelése hibaértéket eredményez. Az eredmény azonos az első hibaértékkel.
A hetedik sorban álló SZUM függvények hivatkozás-argumentumával meghatározott hibaértékek megakadályozzák az oszlop-összegek képzé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.
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, hanem 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, hanem 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 szerepelteti az összegben.
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 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áblázatokkal. 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 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.
A függvények további argumentumai az összeg-tartományok hivatkozásai. Természetesen, most csak a függvények összeadó funkció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-ossal 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.
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összegek 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 bemutatásra 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 oszlopban. 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.
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 hivatkozá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. Tehá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érdezzü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 vonatkozik: SZUMHA( <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-argumentummal azonosságot írunk elő! A relációs jelek sorrendje kötött. Nem egyenlő: <>. Kisebb vagy egyenlő: <=. Nagyobb egyenlő: >=.
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 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 kifejezés.
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.
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 vezető 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-érté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 ellenőrzésénél ezt figyelembe kell vennünk!
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.
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ő karaktereket is. Csillag (*): nem meghatározott számú karakter. Kérdőjel (?): egy karakter. A helyettesítő funkciót ellátó karaktereket a hullámvonal (~) karakter segítségével kereshetjük. A csillagot: ~*. A kérdőjelet: ~?.
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 alaphoz.
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élküli, nem egyenlő relációval adjuk meg.
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üggvé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, akkor még keresgélnie sem kell.
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üggvé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 hibaérté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örté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 megszabhatunk közvetlenül az összeadandó számok- vagy valamely tulajdonságuk szelektálására. Első argumentuma az összeg-tartomány hivatkozása. További argumentumai párban állnak: feltétel-tartomány hivatkozása, feltétel. A feltétel-tartományok sor- és oszlopszámának azonosnak 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üggvényné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 argumentumok írásmódja 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 tartományukhoz 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 összeghez.
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?
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 szorzatokat összeadtuk. Pontosan ezt teszi a SZORZATÖSSZEG függvény is. Argumentumai a tartományok hivatkozásai. Nézzünk még egy példát, amin megfigyelhetjük a függvény használatát.
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…
margitfalvi.arpad@gmail.com
