Csillagos ötös - avagy a Conditional Formatting új lehetőségei Excel 2013-ban

2015. november 18. - Office Guru

Ahogy már hangsúlyoztam többször itt a blogon, az Excel 2013 a korábbi verziókhoz képest rengeteg dolgot készen ad az ember kezébe, ráadásul behozott egy-két olyan újítást is, amely valószínűleg sokak számára teszi szerethetővé ezt a verziót, és ezzel egyet is kell értenünk, hiszen aki megszokja és megismeri a 2013-as kiadást, az biztos nem fog visszavágyni korábbi típusokra.

Kapásból ilyen igencsak szerethető újítás a Conditional Formattingon belül a különféle ikonok (nyilak, csillagok, kördiagramok stb.) használata, amellyel az eddigi egyszerű színezésünk helyett rendkívül jól fel tudjuk dobni táblázatainkat, kimutatásainkat. A következőkben azt fogom bemutatni, hogyan kell egy pontszámos értékelést tartalmazó táblázatból a különféle weboldalakon is látható csillagos kimutatást elkészíteni.

Adott tehát a következő táblázat, amelynek eredményeit szeretnénk a megszokottól eltérő módon, kicsit szebb formátumban prezentálni:

430.jpgElső lépésként a táblázat melletti cellákba vázoljuk fel a tízes skálánk minden egyes számát:

431.jpgHa ez megvan, akkor most jön a mi részünk, a gondolkodós rész, amit még nem tud helyettünk az Excel megcsinálni, azaz azt kell kitalálnunk, hogyan töltsük fel ebben a tízszer nyolcas táblában a cellákat, hogy utána arra a Conditional Formattingot ráhúzva, a csillagok a valós értékelést tükrözzék.

Gondolkodjunk egyszerű logikával: ahol az értékelés pontszáma nagyobb, mint a skála éppen aktuális szintje (1-10 között), ott tele csillagot akarunk látni, ahol töredékszámunk van, ott egy félig tele csillagot, a többinél legyen üres. Ergó IF-et mindenképpen alkalmaznunk kell az összehasonlításhoz, kezdjük is el:

=IF(E$4<=$D5,1,0)

Azaz ameddig skálánk számai kisebbek, mint az aktuális értékelés, kiír egy egyest, más esetekben nullát, ami már jó is lenne, csak akkor a töredékpontokat nem tudjuk jól ábrázolni majd.

Ezért az IF harmadik paraméterénél, ahol azt adjuk meg, hogy mi történjen ha nem teljesül az első paraméter feltétele, ágyazzunk be még egy IF függvényt, hogy megvizsgáljuk a töredéket is, amihez egy ROUNDUP és MOD formulát is be fogunk vetni.

Előbbi a második paramétereként megadott tizedesig felkerekít egy adott számot, utóbbi pedig egy osztás maradékát adja meg nekünk. Mit is akarunk elérni? Ha skálánk számai kisebbek, mint az aktuális értékelés, akkor egyest írjon ki, ellenkező esetben majd kerekítse fel ezt a számot egészre és ha ez így megegyezik a skálánk éppen aktuális szintjével, akkor írja ki magát a töredéket, ellenkező esetben nullát. Hiszen így lesz egy csomó egyesünk, lesz néhány nullánk és lesz egy-két töredék értékünk is a táblázatban, azaz szépen fog menni a "csillagozás". Ez így nézne ki:

=IF(E$4<=$D5,1,IF(ROUNDUP($D5,0)=E$4,MOD($D5,1),0))

Az első rész szerintem egyértelmű, a második IF-et megvizsgálva pedig látjuk, hogy ha értékelésünket felkerekítve már a skálánk aktuális szintjével megegyezünk, akkor értékelésünk pontszámának eggyel való elosztása utáni maradékot (tehát magát a töredékpontot) fogja kiírni, ha nem egyezünk meg, mert már nagyobb szinten áll a skála, akkor nullát látunk.

434.jpgHa ezzel megvagyunk, akkor már jön az Excel által tálcán kínált lehetőség, tartományunkat továbbra is kijelölve ballagjunk szépen Ribbonunk Home füle alatt a Styles szekcióban található Conditional Formattingra és hozzunk létre egy új szabályt (New Rule):

435.jpg"Format all cells based on their values" típus kell nekünk, majd a formázási stílusnál (Format Style) Icon Sets típust válasszunk csillagos Ikon típussal, az értékeket pedig a következő módon adjuk meg:

436.jpgMi a fontos itt? Egyrészt Show Icon Only opciót válasszuk, hiszen annyira nem mutat jól, ha a számunk mellett jelenik meg a csillag, másrészt az érték típusát (Type) számra állítsuk, mert jelenleg nem százalékokkal dolgozunk. Az érték pedig adja magát, tele csillag ha 1 az érték, ha félnél nagyobb vagy egyenlő vele, akkor félig tele csillag, egyébként pedig üres.

És ezzel kész is vagyunk, íme az eredmény:

437.jpg

A bejegyzés trackback címe:

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

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.

vadi 2016.03.19. 12:38:58

Szia!

Nagyon tetszik az oldal, ez az ötlet is újdonság volt számomra.
Annyit tennék hozzá, hogy nekem a fenti megoldás 2010-es Excelben is működik.