összeadás az Excelben - különlegességek
2020-10-14    Excel 2019
változó méretű tartomány összegzése
Van egy oszlopunk, legyen a felirata „bevétel”, és ennek celláiba jegyezzük fel a jövedelmeinket. Szeretném egy cellában, amely nem a be­vé­tel oszlopban áll, folyamatosan követni ezeknek a pénzeknek az összegét! Tehát nem az utolsó szám után álló, folyamatosan moz­gó összeg­ről van szó! Az nem nagy ügy: adatbázis táblázat, összegsor. Hanem erről!
változó méretű tartomány számainak összegzése, kiinduló adatok
A probléma az, hogy a C4 cellában álló =SZUM( A2:A5 ) képletet újra és újra módosítanunk kellene, minden új bevétel rögzítését kö­ve­tő­en. A legegyszerűbb az, ha a SZUM függvénynek nem egy tartományt adunk meg, hanem a teljes „A” oszlopot: =SZUM( A:A ). Ez a meg­ol­dás elég általános, és más függvényeknél is alkalmazzák.
    De a profik ezt sohasem így csinálják! A tényleges, csak a számokat tartalmazó cellákat adják meg. De, hogyan? A tartomány első cel­lá­ja adott. A mi esetünkben ez az A2-es cella. Állapítsuk meg az utolsó számot tartalmazó cella sorszámát az oszlopban! Az nem jó ki­in­du­lás, mert előfordulhat az oszlopban más, az összeadandóktól független szám is! Inkább az első üreset keressük meg! Az a biz­tos! =HOL.VAN( IGAZ ; ÜRES( A:A ) ; 0 ). A HOL.VAN első argumentuma a keresett adat, a második az egyoszlopos vagy egysoros tartomány hi­vat­ko­zá­sa, a harmadik a keresés módját határozza meg. Az ÜRES függvény egy cellát vizsgál, üres-e. Logikai értéket ad eredményül: ha üres, akkor IGAZ, ha nem, akkor HAMIS. A HOL.VAN harmadik argumentumának nullája a pontos egyezéses keresést írja elő. Van még egy bibi! Ne­künk nem egyetlen cellát kell megvizsgálni, hanem többet. Ez sem probléma! Tömbös kiértékelést kérünk a Shift + Ctrl + En­ter bil­len­tyű­pa­ranccsal!
az első üres cellapozíciójának megállapítása a vátozó meretű tartományban
Tehát az „A” oszlop hatodik cellája az első üres cella. Ebből már képezni tudjuk az összeadandó számok tartománya utolsó cellájának hi­vat­ko­zá­sát: ”A& HOL.VAN( IGAZ ; ÜRES( A:A ) ; 0 ) - 1. Természetesen ezt így nem írhatjuk be a képletbe, csak az INDIREKT függ­vénnyel, amely feldolgozza az argumentumában álló kifejezést és eredményül az összeállított cella-hivatkozást adja.
a SZUM függvény tartomány-hivatkozásának összeállítása az INDIREKT függvénnyel
Ha a #HIÁNYZIK hibaértéket kaptuk eredményül, akkor valószínűleg elfelejtkeztünk a képlet Shift + Ctrl + Enter-es lezárásáról.
    A változó méretű tartományok deklarálásának van egy másik, nagyon elterjedt módja: megszámláltatják a DARAB2 függvénnyel az osz­lop­ban álló adatokat és ebből képzik a szám-tartomány utolsó cellája hivatkozását. Például, ”A& DARAB2( A:A ). Ennek a mód­szer­nek két hibája van. Lefoglalja a teljes „A” oszlopot és feleslegesen néz át több mint egymillió cellát. De, hát a HOL.VAN( IGAZ ; ÜRES( A:A ) ; 0 ) ki­fe­je­zés­sel pontosan ugyanilyen felesleges munkát végeztetünk. Nem egészen! A HOL.VAN függvény első argumentumának első (!) elő­for­du­lá­sát ke­re­si. Te­hát, ha megtalálta leáll az ellenőrzéssel.
minden n. szám összeadása
Ez egy nagyon gyakori probléma és nem csak összeadásnál kerül elő: egy táblázat minden meghatározott sorát akarom csak fel­dol­goz­ni. Például, minden harmadik, vagy minden hetedik sorát. Matekosan megfogalmazva, minden n. sorát.
minden n. szám összeadása kiindulás
A képen egy konkrét feladatot látunk. Adjuk össze a dátumok minden harmadik darabszámát! A megoldáshoz a SOR függvényen ke­resz­tül vezet az út. Ennek a függvénynek vagy nincs argumentuma, vagy ha van, akkor az egyetlen cellahivatkozás illetve cel­la­hi­vat­ko­zást ered­mé­nye­ző kifejezés. Az argumentum nélküli függvény annak a sornak a számát adja eredményül, amelyben áll. Az ar­gu­men­tu­mos pedig az argumentumával meghatározott hivatkozás sorának számát.
a SOR függvény működése és szintaktikai szabályai
Hát ezzel, azért még nem jutottunk túl messzire! Kellene egy közös tulajdonságot találnunk ezekhez a sorszámokhoz: 4, 7, 10. A kulcs biz­tos, hogy a hármas, mert hogy minden harmadik számot kell összeadnunk. Igen! Ha elosztjuk a számokat hárommal akkor mindig egyet kapunk. A maradék-képzésnek van saját függvénye az Excelben. Nem fog nagy meglepetést okozni, ha elárulom, hogy MARADÉK a neve. És az sem, hogy két argumentuma az osztandó és az osztó.
a MARADÉK és a SOR függvény együttes alkalmazása
Most már csak ezeket a műveleteket kellene excelesen megfogalmazni: [1] vedd sorban a B2:B10 tartomány celláit, [2] nézd meg hogy a cel­la sorának száma egyet ad-e maradékul, ha elosztod hárommal, [3] ha igen, akkor a cellában álló számmal képezz összeget! Mivel több cella feldolgozásáról van szó egyetlen képletben, ezért biztos, hogy tömbös kiértékelést kell kérnünk és a HA függvény is kell majd a fel­té­te­les művelet miatt.
csoportosító dátum-egységek értékeinek kiszámítása
Természetesen a SOR függvény argumentumában megadott tartomány oszlopa közömbös. Megadhattuk volna az első oszlopot is, A2:A10. Az eredmény ugyanaz. A HA függvény tömbös kiértékelésű változatában csak a feltételt, illetve a feltételeket, valamint a vizs­gá­lan­dó tar­to­mányt kell deklarálnunk.
rangsor egy szakaszának összegzése
A rangsor a számok torna-sora. Elől áll a legnagyobb szám és a sor végén a legkisebb. A számok rangsor-pozícióját egy sorszám, az in­dex mutatja. Ha a rangsor egy szakaszáról beszélünk akkor a rangsorban egymás után álló számokról van szó. A rangsor-szakaszt az el­ső és az utolsó számának indexe azonosítja. Ha egy rangsor-szakasz számait össze akarjuk adni, akkor a szakasz pozíciója ha­tá­roz­za meg az eljárást.
rangsor-szakaszok pozíciója
A kép tetején álló számok rangsorát látjuk három példányban. Az összegzendő szakaszokat kék színnel jelöltem. Fogalmazzuk meg a há­rom feladatot! [1] Add össze a négy legnagyobb számot! [2] Add össze a rangsor legnagyobb számait, a harmadiktól a hetedikig! [3] Add össze a három legkisebb számot!
    Az első két esetben a NAGY a harmadikban a KICSI függvényt kell használnunk, tömbös kiértékeléssel. A NAGY a rangsor elejéről a KI­CSI a végéről ad vissza számokat. A két függvény argumentumai azonosak: az elsővel a számok tartományát, a másodikkal a vissza­a­dan­dó leg­na­gyobb, illetve legkisebb számok indexének tömbjét deklaráljuk. Az eredményül kapott számokat a SZUM függvénnyel adjuk össze. Ha csak néhány indexet kell megadnunk akkor a képletben tömb-állandót használunk. Magyarul, beírjuk az összeadandó szá­mok indexét az előírt formában. A képlet szerkesztését követően nem kell tömbös kiértékelés kérnünk, mert a tömb-állandót a program tömbösen dolgoza fel!
a rangsor-szakasz szamai indexének megadása tömbállandóval
Néhány index beírása nem gond, de hosszabb szakaszok megadása már fáradságos lenne ezzel a módszerrel. Egy mesterfogást fo­gunk alkalmazni: a SOR függvénynek sorok tartományát határozzuk meg feldolgozásra. Például, {=SOR( 1:5 )}. Ez a képlet egy tömböt ad ered­mé­nyül, amelynek elemei sorszámok. Az első az egyes, az utolsó az ötös. Természetesen ez az öt sor semmilyen logikai kap­cso­lat­ban sincs az összeadandó számokkal, se a rangsorukkal. Mivel a képletünk már nem tartalmaz tömb-állandót, ezért Shift + Ctrl + Enter bil­len­tyű-paranccsal kérnünk kell a tömbös kiértékelést.
a rangsor-szakasz szamai indexének megadása a SOR függvénnyel
Gyakori feladat számok meghatározott százalékú legnagyobb illetve legkisebb számainak feldolgozása. Páldául, adjuk össze az A1:C3 tar­to­mány számainak legnagyobb huszonöt százalékát vagy a legkisebb ötven százalékát!
    A megoldás logikáját már ismerjük, az egyetlen újdonság az összes szám darabszámának megállapítása. De ez sem okoz gondot, ott a DARAB függvény. A darabszámot szorozni kell a megadott (x) százalékkal és a szorzatot egészre kell kerekíteni, hiszen darab-szá­mok­ról van szó. Íme így fest, meghatározott tartományban álló számok legnagyobb x százaléka összegének képzése!
a rangsor-szakasz szamai indexének megadása a SOR függvénnyel
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com