Két egyszerű kérdés - és ismét bevetésre kerül a titkos fegyver, a SUMPRODUCT

2015. december 22. - Office Guru

A hosszabb szünet utáni visszatérésemre két egyszerűbb kérdés megválaszolását terveztem, remélhetőleg mindkettő segítség lesz azok számára, akik hasonlóval szembesültek, de nem volt kéznél megoldás vagy ötlet. Ezek közül az első egy elég egyszerű kérdés, mondjuk projektes dashboardokban vagy tervekben merülhet fel, hogyan lehet egy cellában megadott szöveges érték alapján színezni például a cella hátterét vagy a szövegünket magát. Ami itt beugrik valószínűleg mindenkinek, az a Conditional Formatting, na de a kérdés, hogyan csináljuk meg?

Adott a lenti aprócska kis táblánk, benne egy projekt néhány részfeladatával és a hozzá tartozó státusz értékekkel, amelyet egy Validation List tartalmaz:

610.jpgAzt szeretném elérni, hogy a Teljesült státusznál a háttér zöld legyen, a Folyamatban státusznál narancssárga, míg a Még nem kezdődött el státusznál legyen piros. A Ribbonunk Home füle alatt lévő Styles szekcióból tehát hívjuk meg a Conditional Formattingot és hozzunk létre egy új szabályt (New Rule):

611.jpgItt az utolsó lehetőséget, a Formula alapján történő formázási szabályt válasszuk:

612.jpgA Formula sorába írjuk be a következőt:

=$F5="Teljesült"

Majd kicsivel lentebb a Format gombra kattintsunk és az itt felugró formázási szabály közül a Fill fül alatt határozzunk meg egy színt, Teljesült státusz esetén tehát a zöldet:

613.jpgMajd okézzuk el. Ha most megnézzük a táblánkat, láthatjuk, hogy ha a listából kiválasztjuk a Teljesült státuszt, akkor a háttér értelemszerűen zöldre vált:

614.jpgA további teendőnk ismét az F5-ös cellán állva két új szabály létrehozása Conditional Formattingon belül az előbbieknek megfelelően, a narancssárga illetve a piros szín szabályaira. Ha az elkészült, akkor már csak másolnunk kell ezt a cellát a Státusz oszlopunkon belül és meg is van a végeredményünk:

615.jpgMondjuk, hogy ez inkább bemelegítő gyakorlat volt, hiszen valószínűleg sokak számára nem írtam újdonságot, úgyhogy folytassuk is a másik kérdéssel, amire választ keresünk és találunk.

Maradjunk a fentebb készített kis táblánknál, de módosítsuk annyiban a feladványt, hogy hozzunk létre egy Overall mezőt, ahol projektünk összesített státuszát vizsgáljuk:

616.jpgA feladatunk annyi lenne, hogy a Projekt állapota sorban jelenítsük meg a Folyamatban szót, ha bármelyik értékünk a feladatlistában még mindig nincs Teljesült státuszban, ha pedig minden Teljesült státuszban van, akkor legyen az összesített állapot is Teljesült. Mindenképpen szeretnénk, hogy üres cellák esetén is helyesen működjön és ne zavarja meg az adott cellák formátuma sem. Ezekkel a feltételekkel az IF használata már kicsit nehézkesebb lesz, ezért váltsunk át az én titkos kedvencemre is, a SUMPRODUCT függvényre, amelyről tudjuk az általános definíciót, azaz hogy két vagy több tömb elemeit szorozza majd adja össze, tehát a két tömb egymással egy sorban lévő elemeit összeszorozza majd az eredményeket szummázza. De bővebben róla példákkal itt:

SUMPRODUCT - A szuperfegyver

Szóval hogy folytassuk? Két dologra lesz szükségünk, elsőként egy SUMPRODUCT-ra, valahogy így:

=SUMPRODUCT(--(F5:F7="Folyamatban"))

Mit tesz ez? Fogjuk az F5:F7 tartományt, mint tömb meghivatkozzuk, majd az egyenlővé tétellel arra utasítjuk a függvényt, hogy a "Folyamatban" értéket tartalmazó cellákat keresse meg. A kötőjel (illetve a dupla kötőjel) Boolean (IGAZ,HAMIS - TRUE, FALSE) értékeket alakít egyesekké és nullákká, tehát ha talál a függvény a tartományunkban "Folyamatban" értéket, akkor kapunk egy egyest, ha nem, akkor egy nullát.

Innentől kezdve pedig már szabad az út, hiszen nem kell nagyon mást tennünk, mint két IF-et és két SUMPRODUCT-ot egymásba ágyazni:

=IF(SUMPRODUCT(--(F5:F7="Folyamatban")),"Folyamatban",IF(SUMPRODUCT(--(F5:F7="Még nem kezdődött el")),"Folyamatban","Teljesült"))

Lefordítva tehát ha az F5:F7 tartományban van Folyamatban érték, akkor kiírja, hogy projektünk még folyamatban van, ha nincs, akkor megnézi, hogy van-e "Még nem kezdődött el" érték, mert ha van, akkor még mindig folyamatban van a munkánk, ellenkező esetben viszont "Teljesült" a státusz.

617.jpg

A bejegyzés trackback címe:

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

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.

sasfészek 2015.12.23. 17:49:48

köszönöm! nagyon jó cikk!