Folytatva a pár hónappal ezelőtt elkezdett sorozatot, ismét az Excel egyik titkos fegyveréről, a szinte mindenre képes SUMPRODUCT függvényről lesz szó, annak egy újabb felhasználási módjáról. Az alapdefiníció és funkcióleírás valószínűleg mindenkinek a könyökén jön ki: két vagy több tömb elemeit szorozza majd adja össze, azaz két tömb egymással egy sorban lévő elemeit összeszorozza majd az eredményeket szummázza.

Na de ez csak a legalapvetőbb felhasználási módja, a múltban írtam már egyéb lehetőségekről is, ma pedig ismét bővítjük ezen tudásanyagot. A következő példatáblában tegyük fel, hogy az elmúlt két hétben egyik kollégánk ledolgozott óráinak számát látjuk, a munkaidő következetesen nyolc óra, ha valaki ennél többet dolgozik, az nem számít, ha kevesebbet, akkor értelemszerűen a kevesebb óra kerül majd figyelembe vételre az elszámolásnál:

A fenti definícióval egyébként meg is határoztam a feladatot: hogyan lehet egyetlen képlettel (VBA-val még egyszerűbb amúgy) meghatározni, hogy pontosan mennyi óra kerül elszámolásra? Ismétlem, a 8 óra feletti időtartam nem számít, ha viszont kevesebbet dolgoztunk, akkor a ledolgozott órák számítanak.

Ha simán szummázunk, látjuk, hogy összesen 86 óra 32 percet dolgozott a kolléga, de valójában ennél jóval kevesebbet fog majd az elszámolásában látni, hiszen a 8 óra feletti rész nem számít.

Ha pontosan akarjuk kiszámolni, láthatjuk, hogy így valójában csak 77 óra 38 perce kerül majd elszámolásra:

A probléma megoldása ott rejlik a manuálisabb, segédoszlopos megoldásban, hiszen azt a képletet kell összehoznunk egy cellába, ami az összes ledolgozott idő szummájából levonja az érintett sorok nyolc órán felüli részét.

Tehát kezdjük a képletünket egy egyszerű szummával, hiszen ebből vonjuk majd le a többletidőt:

A következő lépésben azt a kérdést kell megválaszolnunk, hogy hogyan tudjuk meghatározni egy tömbre vonatkozóan a tömb összes eleme és a 8 óra közötti differenciát. A TIME függvényt fogjuk bevetni, ami az első, második és harmadik paramétereként megadott óra, perc, másodperc értékeket alakítja számmá, azaz TIME(8,0,0) 0.33-at fog eredményül adni, hiszen ez pont egy nap harmadrésze. Minden egyes sor esetében ebből a meghatározott számból vonjuk ki az aznapi ledolgozott időt (tehát tömbfüggvényként fogjuk kezelni ezt a formulát) és ha ez a különbség kisebb, mint nulla, akkor TRUE, ellenkező esetben FALSE értéket dobjon vissza. CTRL+SHIFT+ENTER lenyomásával tömbfüggvényként használjuk.

{=(TIME(8,0,0)-B2:B11<0)}

A következő kép részkalkulációjában látható, hogy milyen tömböt kapunk eredményként:

TRUE értéket látunk majd a tömbben ott, ahol 8 óránál többet dolgozott az illető, FALSE értéket pedig ott, ahol kevesebbet.

Ne felejtsük el, hogy ez még csak a végső tömbfüggvényünk második része (az első a ledolgozott idő sima szummája volt), úgyhogy jön a harmadik fázis, ahol az is kiderül, hogy mire fogjuk a FALSE/TRUE értékeket felhasználni. Azt már korábbi posztokban taglaltam és fel is használtam, hogy mi történik akkor, ha a TRUE illetve FALSE értéket megszorozzuk egy számmal: TRUE esetén a számot, FALSE esetén nullát kapunk vissza.

Ergó most van egy tömbünk TRUE és FALSE értékekkel, tehát azoknál a soroknál, ahol TRUE értéket kaptunk eredményül, meg tudjuk határozni, hogy pontosan számértékben mekkora időmennyiséggel dolgozott többet a kolléga. Ehhez nem teszünk mást, mint simán összeszorozzuk a TRUE/FALSE tömbünket az egyes sorokra kiszámolt időkülönbséggel (ami 8 órához képest többlet esetleg hiány):

{=(TIME(8,0,0)-B2:B11<0)*(TIME(8,0,0)-B2:B11)}

A lenti segédkalkulációban látszik, hogy jelenleg milyen tömbünk van, TRUE és FALSE értékek helyett a pontos számértékként megjelenő időtöbbleteket látjuk:

A SUMPRODUCT függvényt most azért fogjuk bevetni, hogy összeadjuk ezeket a differenciákat a tömbben, majd az egész eredményt szépen hozzáadjuk az eredeti ledolgozott idő összegünkhöz. Azért hozzáadjuk, mert a SUMPRODUCT összegzésünk eredménye egy negatív szám, ergó ha kivonnánk, akkor a -- okán hozzáadnánk, ami nem a valós eredményt tükrözné, hiszen duplán elszámolnánk a többletmunkát. A helyes eredmény tehát: