Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013
Ha olyan tartományt akarunk összegezni, amelyben bármilyen hibaüzenet van, akkor az Excel SZUM (SUM) függvénye hibát ad vissza, ahogy a következő ábrán is látható.
A hiba ott keletkezik, hogy az „D” oszlopban lévő FKERES (VLOOKUP) függvény a termékkód alapján megpróbálja megkeresni a termék egységárát a termék-törzs táblában, ami egy másik lapon, vagy munkafüzetben van. Azonban vannak olyan kódok, amikhez nem tartozik pár a törzsben, és ott jól ismert #HIÁNYZIK (#N/A) hibaüzenetet látjuk.
Az „F” oszlopban számoljuk az egységár és a mennyiség szorzatát, ami a hibát tovább viszi,örökölve a „D” oszlopból. Ha az „F” oszlopot összegezni akarjuk, akkor a hagyományos módon használt SZUM függvény is hibát fog adni.
Megoldások
1. Az FKERES javítása
A „D” oszlopban lévő FKERES eredetileg így néz ki (D2 képlet):
=FKERES(C2;torzs!$A$2:$C$46;3;0)
Azt kell csinálni, hogy ha az FKERES nem talál párt a törzsben, akkor nullát írjunk a D oszlopba, különben pedig a megtalált párhoz tartozó egységárat. Ezt Excel2003-ig a HA(HIBÁS(FKERES(… verzióval lehetett megcsinálni.
Az Excel 2007-ben megjelent a HAHIBA (IFERROR) függvény, ami a hasonló dolgokat sokkal jobban kezeli. Az Excel-Bázison is írtam róla korábban.
Ennek megfelelően a D2-es cella képletét kicsit továbbfejlesztjük, és azt kérjük, hogy ha az FKERES nem találja a kódot a törzsben, akkor adjon vissza nullát.
=HAHIBA(FKERES(C2;torzs!$A$2:$C$46;3;0);0)
Ezután az „F” oszlop is nullákat fog tartalmazni a megfelelő helyeken a hibaüzenetek helyett, és a SZUM is meggyógyul.
2. A SZUM javítása
Egyszerű feltételes összegzés
Mi lenne, ha SZUM helyett mást használnánk, amivel kihagyhatnánk az összegzésből a hibaüzenetet tartalmazó cellákat? Van nekünk SZUMHA (SUMIF) függvényünk. Az „F18”-ba, a SZUM fv helyett írjuk ezt:
=SZUMHA(F2:F17;"<>#HIÁNYZIK")
Egyszerű. A hibaüzenetes cellákat egyszerűen kihagyja az összegzésből. A baj csak az, hogy bele kell drótozni a hibaüzenet konkrét szövegét. Igaz, ezt még vehetnénk valami cellából is, de azzal már nehezebben kezdenénk valamit, ha a fájlt más nyelvű Excellel nyitnánk meg. Ott a hibaüzenet is más szövegű.
Általános megoldás tömbképlettel
F18-ba próbáljuk meg a következő tömbképletet írni:
=SZUM(HA(HIBÁS(F2:F17);0;F2:F17))
A képletet Ctrl+Shift+Enter-el kell lezárni (CSE), mivel tömbképlet. A működését a videóban magyarázom el.
Letölthető gyakorló munkafüzet: Szumma-hibauzenetekkel
3. SZUM helyett használjunk ÖSSZESÍT függvényt
Egy külön anyagot szenteltem az ÖSSZESÍT (AGGREGATE) függvénynek, ami csak az Excel 2010-es verziójától létezik. Ezzel nagyon egyszerűen lehet összesíteni egy tartományt a hibaértékek kihagyásával. Az egyik argumentum pont erre való.
=ÖSSZESÍT(9;6;F2:F17)
A kilences azt mondja meg, hogy a SZUM függvényt szeretném használni, a hatos pedig azt, hogy a hibaüzeneteket ne vegye figyelembe.
Mindez működés közben
A Videóban az ÖSSZESÍT függvény nincs benne, azt a hivatkozott oktatóanyagnál lehet megnézni.
Hibaüzenetek kizárása az összegzésből
Vélemény, hozzászólás?
Hozzászólás küldéséhez be kell jelentkezni.