Az Excel szummázás megbolondulni látszik, ha logikai értékekről van szó

What is happening here?

2016. január 23. - Office Guru

A mai napi posztban továbbra is maradunk az Excelnél (mint a leggyakrabban használt Office-családtag, ez nem is meglepő), de ezúttal is maradunk az érdekességek vonalán, logikai értékek (Boolean - True vagy False) összegzésének furcsaságait fogom fejtegetni a következőkben, remélve, hogy bár Excelről beszélünk, tudok egy-két vidám percet okozni a kíváncsiskodóknak.

Adott a következő három logikai érték, három különböző cellában (beírhatjuk egyszerűen őket, de akár kalkulált értékek is lehetnek, mindegy jelen esetben):

910.jpgMi történik, ha szimplán =A1+A2+A3-ként összegezzük?

911.jpgNem kérdés, az eredmény kettő lesz, hiszen két TRUE és egy FALSE értékünk van.

Bonyolítsuk meg egy kicsit, mi történik, ha most a SUM függvényt használjuk, azaz =SUM(A1:A3) képlettel próbáljuk meghatározni a három logikai értékünk összegét?

912.jpgBár nem erre számítanánk, defíníció szerint nem kétséges, hogy az eredményünk nulla lesz, hiszen a SUM függvény leírása szerint, ha tömböt összegzünk, csak a számok vagy az arra való hivatkozások kerülnek összesítésre, a logikai értékek, szövegek, hibaüzenetek természetesen nem.

Viszont a defíníció másik részéből az is kiderül, hogy a SUM függvény mégiscsak alkalmas lehet a példánkban szereplő két TRUE és egy FALSE megfelelő összegzésére, csak ehhez a =SUM(A1:A3) helyett a =SUM(TRUE,TRUE,FALSE) képletet kell alkalmaznunk. Gyorsan belátható, hogy nagyobb méretű tömbök, tartományok esetén ez a megoldás nem igazán használható, ezért logikai értékek összegzésénél a SUM nem megfelelő megoldás. Használjunk inkább SUMPRODUCT vagy COUNTIF függvényeket.

913.jpgNa de mi történik, ha VBA-ban próbáljuk összehozni az eredményt? Menjünk át a VBA-editorba és szúrjunk be egy új modult - ebben fogunk gyorsan egy szumma funkciót definiálni.

914.jpgMost tehát nem szubrutinról van szó, hanem funkcióról szó, ezt ne feledjük. Definiálunk tehát egy funkciót (legyen a neve SAJATSZUMMA), amelynek paramétere a tartomány lesz, amit összegezni szeretnénk:

915.jpgMielőtt továbbolvasna bárki is, azt javaslom a VBA-ban kevésbé járatos Olvasóknak, hogy próbálkozzanak meg ezzel saját maguk, hiszen alapvetően nem komplikált kódról van szó. Szóval szükségünk lesz egy cell névre hallgató változóra, amit Range típussal definiálunk, hiszen tartományokat fogunk összegezni, illetve még arra is szükségünk lesz első lépésként, hogy SAJATSZUMMA funkciónk/formulánk kezdőértékét nullára állítsuk:

916.jpgEzután már csak egy For..Next ciklusra van szükségünk, ahol meghatározzuk, hogy minden egyes cella esetében a paraméterként megadott tartományunkban hajtsa végre az adatok összegzését, valahogy így:

917.jpgSAJATSZUMMA funkciónk eredménye tehát nulláról indul, majd ehhez hozzáadja tartományunk első cellájának értékét, majd ahhoz a következőt és így tovább, tehát alapból ez egy jól működő funkció lesz.

Nézzük azonban meg mi történik, ha használni is fogjuk a fenti példa esetében:

918.jpgAz eredmény mínusz kettő lett! A Microsoft leírása azért nagyjából megmagyarázza ezt a furcsaságot is, elsőként persze hangsúlyozva, hogy Boolean értékeket nem igazán célszerű számként használni/összegezni. A lényeg viszont az, hogy alapesetben ha nem konvertálja az ember ezeket az értékeket, akkor a False 0, a True pedig -1 értékkel kerül majd összegzésre.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr298303820

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Nincsenek hozzászólások.