Újabb SUMPRODUCT gyakorlatozás Excelben

2016. június 06. - Office Guru

Folytatva az olvasói kérdések megválaszolását, ezúttal egy egyszerűbb probléma megoldását járjuk körül, főként azért, mert az erőssége ellenére az egyik kevésbé elterjedt használattal bíró formula, a SUMPRODUCT fogja adni az én megoldásom gerincét.

A kérdés az alábbi képen tökéletesen látható is: számoljuk össze, hogy egy adott dátum után pontosan hány esemény (ezúttal értékesítés) történik.

szall01.jpgA megoldások között kapásból valószínűleg nem a SUMPRODUCT jut senki eszébe, hanem a több feltétellel működő COUNTIF, azaz a COUNTIFS és ez így is van rendjén, hiszen nagyjából ugyanazt az eredményt elérhetjük a COUNTIFS függvénnyel, amit a jelen kérdéskörben szeretnénk, viszont a SUMPRODUCT-nak a jövőre nézve nagyobb lehetőségei vannak, hiszen míg később a COUNTIFS formulát nehéz lenne kibővíteni bizonyos feltételekre, addig a SUMPRODUCT nagyjából bármit lekezel.

Hogy oldjuk meg a mostani feladatot? Nagyjából egyetlen dolognak kell eszünkbe jutnia, a * használatának, amellyel arra kényszeríthetjük az alapvetően nem ilyen célokra megalkotott SUMPRODUCT formulát, hogy több feltételnek megfelelő értékeket számoljon össze.

Hiszen ha ezt tudjuk, akkor már csak a feltételeinkre van szükségünk, egyrészt

($B$2:$B$23>=$H$2)

ami azt vizsgálja, hogy az értékesítések napjai közül mely van a megadott dátumunk után, másrészt

($A$2:$A$23=E2)

ami megvizsgálja, hogy a fentebb említett dátumvizsgálat elvégzése után kalapban maradó értékek közül mely esetén egyezik meg az A oszlopban található településnév az első városunk nevével. Aztán ezt a kettőt szépen összepréseljük:

=SUMPRODUCT(($B$2:$B$23>=$H$2)*($A$2:$A$23=E2))

Ahogy látható, a lehetőségeink innen korlátlanok, hiszen további tucatnyi cellát is beintegrálhatnánk, mint feltétel, lehetne intervallumra szűrni és így tovább.

szall02.jpgDe folytathatjuk még a SUMPRODUCT bevetési területeit hasonlóan egyszerű kérdésekkel, mondjuk egy jó kis házifeladattal: anélkül, hogy továbbolvasnánk a posztban, számoljuk csak össze a SUMPRODUCT segítségével, hogy pontosan hány darab páratlan szám van a listámban.

szall03.jpgElső kérdés, amire kezdésként választ kell adnunk, az az, hogy hogyan állapítjuk meg egy számról, hogy az páratlan-e vagy sem. Bizony, elosztjuk kettővel és ha van maradék, akkor az már biztos nem páros. Erre a célra ott is van szépen a MOD függvényünk, amely az első paramétereként megadott számot elosztja a második paramétereként megadott számmal és visszaadja a maradékot - kettővel való osztás esetén páratlan számnál tehát egyet.

Ha tehát a

=MOD(5,2)

függvényt egyenlővé tesszük eggyel

=MOD(5,2)=1

akkor vagy TRUE vagy FALSE eredményt kapunk vissza, attól függően, hogy egy a maradék vagy sem. De hogy ezt összegezni tudjuk, vissza kell alakítanunk ezeket az értékeket egyesre vagy nullára, amit a korábban már megtanult kötőjel illetve dupla kötőjel segítségével tudunk megtenni.

szall04.jpgA kötőjel (illetve a dupla kötőjel) Boolean (IGAZ,HAMIS - TRUE, FALSE) értékeket alakít egyesekké és nullákká, sőt, mivel a program a kötőjelet "tagadásként" is értelmezi, még az előjelet is megfordítja, ergó egy kötőjellel egy TRUE értékből -1 lenne (a nulla nem változik), azaz még egy kötőjelre van szükségünk, hogy megkapjuk a vágyott egyesünket.

--(MOD(5,2)=1)

Ezután már csak az ötös számot kell lecserélnünk a tartományunkra, majd a SUMPRODUCT segítségével összegeznünk kell és kész is vagyunk az eredménnyel.

=SUMPRODUCT(--(MOD($B$3:$B$16,2)=1))

szall05.jpg

A bejegyzés trackback címe:

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

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.

IT jegyzet (törölt) · http://itjegyzet.blog.hu 2016.06.07. 12:17:50

Off: libreoffice-t is ismered?

Office Guru 2016.06.07. 20:44:31

@IT jegyzet: Nem túlságosan, felhasználóként egyszer-kétszer találkoztam vele, de ennyi. Az elődjének is tartott OpenOffice viszont a mai napig szép emlék, azzal jó sokat dolgoztam.

IT jegyzet (törölt) · http://itjegyzet.blog.hu 2016.06.07. 20:59:00

@Office Guru: szerintem az OpenOffice emlékeid is elégségesek lehetnek. ;)
Olyat szeretnék, hogy csak a numerikus billentyűzetet használva töltsek fel egy táblát, ami négy oszlopot tartalmaz: időpont (hh:mm), dátum (dd-mm), egy numerikus érték 1.0-30.0 között és az utolsó egy jelzés (-/+/* vagy üres)
Nekem úgy tűnik, hogy a Form... csak a mezőket kínálja fel, formázni nem hajlandó. Erre kellene valami tipp, mert az idő és a dátum bevitele elég nehézkesnek tűnik.

G. Wolf 2016.06.08. 19:09:26

Szia!

Excelbibla 2013 -ról mi a véleményed? Cél hogy nagyon alaposan el tudjak mélyülni az excel minden rejtelmeiben. (Vagy esetleg más olyan irodalom ami valóban jól összefoglalja A-tól Z-ig az egészet, netről nem akarom egyesével összefogdosni az anyagokat inkább valami "kész" de valóban mély és használható írást keresek)

Köszi a választ. :)