1111 Budapest, Bertalan Lajos utca 7-9., BME L épület
Az „őszi évad” nyitó bejegyzésében a gyakorta használt SZORZATÖSSZEG függvényt mutatjuk be, illetve egy lehetőséget arra, hogy miképpen végezhető szűrés függvényben.
SZORZATÖSSZEG
Maga a függvény (angolul SUMPRODUCT) az egyik alap eleme szinte bármely munkafüzetnek. Működése a következő: két tartomány értékeit egyesével összeszorozza, az így kapott szorzatokat pedig összegzi. Gondoljunk csak bele, ha van egy mennyiség oszlopunk és egy fajlagos árunk, akkor ki tudjuk számolni az értékesítési bevételünk értékét. Szintaxisa az alábbi:
= SZORZATÖSSZEG (tömb1; [tömb2]; [tömb3];…)
Mint látjuk, csak az első tömb1 megadása kötelező. Abban az esetben, ha így teszünk, a függvény nem tudja mivel megszorozni az egyes értékeket (hiszen nincs tömb2), így mindegyik értéket 1-el szorozza, ezen szorzatok összege pedig a tömb1 elemeinek összege lesz. Tehát mintha csak egy mezei SZUM függvényt használtunk volna.
Amennyiben kijelölünk egy másik tartományt is a tömb2 helyére, úgy az alábbi példa alapján a függvény a két tömb azonos indexű elemeit összeszorozza, az így kapott részeredményeket pedig összeadja.
Pontosan ugyan az történik, mintha a fent leírt lépéseket külön végeznénk el: először összeszoroznánk az egyes mennyiség és ár értékeket, végül az így kapott értékeket még összegeznénk.
SZŰRÉS a függvényben
Kiegészítettük a mintapéldát egy „Kategória” oszloppal, ahol – például az ABC (pareto) elemzésből származó – értékeket tűntettük fel. A feladat a következő, összegezzük az „A” kategóriájú termékekből származó bevételt. Ehhez az alábbiak szerint kell módosítani a SZORZATÖSSZEG függvényt:
De mi is történik itt? Nézzük meg a képletet jobbról-balra haladva.
- A szorzás utáni zárójeles ($E$3:$E$12=C17) rész eredménye egy tömb, amely pontosan olyan magas lesz, mint amilyen magas a lila tartomány. Egy egyszerű feltételt vizsgálunk itt, a lila tartomány minden elemére megnézzük, hogy az adott elem azonos-e a C17-es cella tartalmával, tehát az „A” értékkel. Ha azonos, akkor a tömb adott eleme egy „IGAZ” érték lesz, ha nem, akkor egy „HAMIS” érték. Tehát ezen rész eredménye egy igaz és hamis értékeket tartalmazó tömb.
- Ha ezzel a tömbbel megszorozzuk a piros színű $D$3:$D$12 tömb elemeit, akkor újfent egy tömböt kapunk eredményül. Amennyiben az árat egy „IGAZ” elemmel szorozzuk, úgy az eredmény maga az ár lesz. Amennyiben pedig egy „HAMIS” elemmel szorozzuk, úgy az eredmény 0 lesz. (Hiszen az igaz matematikai megfelelője az 1, a hamisé a 0.) Tehát kapunk egy olyan tömböt, ahol csak ott lesz az ár nagyobb, mint nulla, ahol a zárójeles részből származó tömb IGAZ értéket vesz fel, tehát amelyik sorban „A” kategóriás termék szerepel.
- Ha ezt a „$D$3:$D$12*($E$3:$E$12=C17)” nullákat és tényleges árakat tartalmazó tömböt a SZORZATÖSSZEG függvény segítségével összeszorozzuk a mennyiséggel, akkor a mennyiséget minden nem „A” sorban egy 0 értékkel fogjuk szorozni, így azokat a bevételeket nem fogjuk számításba venni.
A fenti lépéseket természetesen a függvényen kívül, egyszerűbben is el lehet végezni, csak sokkal több oszlopot kell hozzá igénybe venni:
Következő bejegyzésünkben az ELTOLÁS függvénnyel fogunk megismerkedni, ezt fogjuk kombinálni már korábban tanult függvényekkel.
