Cellák színének felhasználása formulákban

2016. augusztus 15. - Office Guru

Annak ellenére, hogy egyes Olvasói kérdések feldolgozásával és megválaszolásával még el vagyok maradva, a mai posztban egy egészen friss kérdésre próbálom megadni a választ vagyis inkább az egyik lehetséges választ.

Nem túlságosan nehéz elmagyarázni, hogy mi a felvetés, azt szeretnénk elérni a lenti példatáblában, hogy azon cella értéke, amelyet megjelölünk valamilyen színnel, jelenjen meg a Megjelölt összeg névre hallgató oszlop adott celláiban.

Kiinduló tábla:

cuba1.jpgElérendő állapot:

cuba2.jpgSajnálatos módon az általam ismert lehetséges megoldások egyikével sem lehet ezt csak formulából megoldani, mindenképpen szükség van VBA-ra, bár túl hosszú kódra nem kell gondolnunk, szimplán csak a meghatározott cellánk színét szeretnénk visszanyerni.

Ehhez egy funkciót fogunk létrehozni VBA-editorban, úgyhogy most ALT+F11 lenyomásával menjünk is át arra a felületre. Mivel funkcióról van szó, itt hozzunk létre egy új Modult:

cuba3.jpgEbben a modulban fogjuk felépíteni a kis kódunkat, ami három részből áll. Először is nevezzük el a funkciónkat valamilyen néven és adjunk neki paramétereket is - a név segítségével tudjuk a formulák közül előszedni ezt a funkciót, a paraméterek pedig a formulák többségénél megszokott inputokat jelentik.

A mi esetünkben legyen a funkció neve CellColor, paramétere pedig a cella, amelynek színére vagyunk kíváncsiak:

cuba4.jpgAnnak érdekében, hogy legalább akkor újrakalkulálódjon a funkciónk, ha bármilyen cella értéke megváltozik a munkalapon vagy a munkafüzetben, esetleg újra megnyitjuk a fájlunkat, be kell szúrnunk egy Application.Volatile névre hallgató metódust, amelynek segítségével ezt elérhetjük. Fontos kiemelni, hogy még ez sem garantálja a tökéletes megoldást, hiszen ha majd csak szimplán színt változtatunk bármi egyéb módosítása nélkül, akkor még nem változik meg az eredményünk!

Biztonsági okokból sokan szoktak alkalmazni egy olyan kitételt, hogy ha esetleg tartományt adna meg a felhasználó akkor álljon le a funkció és kizárólag csak cellára legyen érvényes az elemzés. Ezt egy sima IF segítségével érjük el, azaz ha a megadott Range celláinak száma nagyobb mint 1, álljon le a funkció:

If r.Cells.Count > 1 Then Exit Function

Utolsó lépésünk pedig értelemszerűen a cella színének kinyerése, ehhez könnyedén használjuk csak fel a Microsoft oldalán is szépen listázott és bemutatott Interior.ColorIndex objektumtulajdonságot, ami az Interior objektum (a cella színe, pl. a Font objektum a Font színe lenne) színét fogja visszaadni eredményként.

CellColor = r.Interior.ColorIndex

cuba5.jpgEzután pedig ezt a formulát könnyedén felhasználhatjuk egy elemzésre, most már az eredeti munkalapon. Többféle megoldást el bírok képzelni, de a mostani példánál én három darab IF függvényt fogok CellColor funkcióval együtt egybeágyazni, hogy elérjem a kívánt eredményt:

=IF(CellColor(E4)>0,E4,IF(CellColor(F4)>0,F4,IF(CellColor(G4)>0,G4,"")))

Azaz megnézzük, hogy az első cella színkódja nagyobb-e mint nulla (-4142 a "színnélküliséget" jelenti, de akár nullára is írhatjuk ezt a feltételt), ha igen, akkor az első cella értékét adja vissza, ha nem nagyobb mint nulla, tehát nincs színe, akkor nézze meg a következő cellát a sorban, majd a következőt. Ezzel a megoldással én feltételeztem azt, hogy nem lesz több színes cella a sorban, de igazából azt már nem nagy kunszt beletenni.

cuba6.jpgAhogy korábban, minden építő jellegű kritikának, ötletnek, javaslatnak és főként más megoldásnak nagyon örülök!

Function CellColor(r As Range)
Application.Volatile
If r.Cells.Count > 1 Then Exit Function
CellColor = r.Interior.ColorIndex
End Function

A bejegyzés trackback címe:

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

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.

Nincsenek hozzászólások.