COUNT vs. SUM: a Pivot-táblák egyik idegesítő rejtélye

A megoldásra nincs funkció vagy beállítás, hagyatkozzunk adatainkra

2015. október 18. - Office Guru

Korábbi posztokban már feszegettem egy-két idegesítő, általában nem felhasználóbarát Excel-funkciót és az biztos, hogy erre a listára nálam a Pivot-táblák egyik igencsak fárasztó alapbeállítása, azaz értékeink alapból összegzése szerepel - hiszen ha csak megszámolni akarjuk az értékeket, az mindenképpen manuális állítgatást igényel. Vagy mégsem?

270.jpg

271.jpgElsőként azonban mindenképpen meg kell értenünk, hogy mi alapján defaultolódik értékeink kezelése a Pivot-táblában, hiszen ez nem beállítás függvénye, hanem adatainkon múlik. Ha tehát van egy ötvenezer számot tartalmazó listánk, akkor az a Pivotban biztos, hogy szummaként fog összegezni, ám ha akár csak egyetlen egy üres vagy szöveget tartalmazó cella is belekerül ebbe a listába, már egyből count lesz az alapbeállítás. Ugyanez igaz az errorokra (pl. mondjuk #N/A!) vagy éppen Boolean (TRUE, FALSE) értékekre is, tehát ha defaultolni akarjuk Pivot táblánkat értékkezelési metódusát, az adatainkat kell megvizsgálnunk.

272.jpg

273.jpg

VBA-ban persze meghekkelhetjük ezt a történetet is, de alapvetően fókuszáljnk az adatainkra, hiszen ez a legegyszerűbb megoldás, első körben például mindenképpen csak a konkrét adattartományunkat jelöljük ki, hiszen ha az 50 ezres listánkhoz még az oszlop további több tízezer üres sorát is hozzájelöljük, akkor egyértelmű, hogy mi lesz az alapbeállítás Pivot-táblánkban.

Legjobb fegyvertársunk ennek a problémának a felszámolásában az értékeink kijelölése után megnyomott CTRL+G parancsra előugró Go To lesz, annak is Special almenüje:

274.jpg

275.jpgTehát ha mondjuk üres celláinkat akarjuk eliminálni, akkor itt a Special képernyőn kattintsunk a Blanks rádiógombra majd okézzük le - így kijelöljük az összes üres cellánkat, majd ha beírtuk, mivel szeretnénk kitölteni őket (mondjuk egy nullával), akkor a CTRL+ENTER megnyomásával az összeset feltöltjük értékkel és máris megvan a default szummázás a Pivot-tábla értékeinél.

Ugyanígy ha a Formulas rádiógombra kattintunk a Go To Special almenüjében, akkor meghatározhatjuk, hogy mely eredményeket vizsgáljunk meg: nekünk értelemszerűen általában a hibák vagy a logikai eredmények (Igaz, Hamis) kellenek, hogy így generáljuk meg a default szummázást a Pivot-táblában.

Értelemszerűen ha a másik irányba szeretnénk eljutni, akkor egyszerűen adjunk hozzá egy plusz üres sort a kijelölésünkhöz és abból készítsünk Pivot-táblát, így pedig egyértelmű, hogy count lesz az alapbeállítás.

Biztos vagyok benne, hogy ez csak egyetlen megoldási lehetőség, hozzászólásban szívesen látok más ötleteket is!

A bejegyzés trackback címe:

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

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.

delkreta 2015.10.18. 19:56:33

Sziasztok! Jópár dolgot tanultam innen, de egy dologra régóta nem kapok választ. Hogyan nyerhetem ki egy konkrét szűrés változóját (amire szűrök...) pl. a szűrt táblázat fejlécébe kiiratásra. (esetleg további felhasználásra, mondjuk egy másik táblázat hasonló oszloptartalmú táblázatához...)
Előre is köszi a megoldásokat.

Soltész József 2015.10.19. 21:15:45

Azért az nagyon nem mindegy, hogy valami BLANK (üres), vagy 0 értéket vesz fel. Például az átlag esetében máris kiütközik a különbség.
Az pedig, hogy a COUNT ilyenkor egyből az alapértelmezett, az az excel egyik nagy hülyesége.

TZoli 2015.10.22. 10:23:13

Delkrata:
Az alábbi linken van egy VB kód, ami működik, próbláltam.
j-walk.com/ss/excel/usertips/tip044.htm

delkreta 2015.10.25. 18:44:34

@TZoli: Szia TZoli! köszönöm szépen, működik! ..))) Most már csak paraméterezgetni kell egy kicsit, hogy esetlegesen többszörös kijelöléskkel is dolgozzon... de ezen eltörpölök még egy kicsit.
Mégegyszer köszi! Péter