Telefonszámunk: 1-472-0679

Összegzés a hibaüzenetek kihagyásával

2014-08-16 - horvimi - Megtekintések száma: 1,965 - Kategória: Képletek, Tömbképletek
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ó.

osszeg-hibauzenettel-1

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?