Asiakas kaipasi kaaviota, jonka pylväiden väri määräytyy sen perusteella onko tavoite saavutettu (vihreä pylväs) vai ei (punainen pylväs). Lisäksi hän halusi, että punaisten pylväiden yläpuolella näytetään tavoitearvo.
Vaikka Excelissä on voinut muotoilla soluja ehdollisesti jo viime vuosituhannelta lähtien, kaavioiden ehdolliseen muotoiluun ei vieläkään ole valmiita komentoja. Muotoilut pitää toteuttaa kekseliäästi kikkailemalla – katso jutun lopussa oleva video!
Voit kokeilla ehdottamaani ratkaisua vaihtamalla alla olevan taulukon lukemia sekä tavoitearvoja. Jep – voit syöttää lukuja suoraan jutussa olevaan taulukkoon! Toimii ainakin yleisimmissä selaimissa, tosin mobiilisti taulukon koko voi olla niin pieni ettei testailu onnistu. Voit myös ladata työkirjan itsellesi esimerkkitaulukon oikeassa alakulmassa olevalla Excel-painikkeella.
Miten kaavio tehdään?
Kerron lyhyesti parin kuvan kuvan avulla toteutusidean.
- Luo sarakkeet sekä saavutettuja että saavuttamattomia lukusarjoja varten (kuvassa D ja E). Tee molempiin sarakkeisiin kaava, joka joko noutaa luvun tai luo virheilmoituksen. Idea on se, että Excel ei näytä arvosarjassa koskaan virheilmoituksia. Tee A, D ja E -sarakkeista pinottu pylväskaavio (stacked column) ja vaihda pylvässarjojen värit punaiseksi ja vihreäksi. Käytä suomenkielisessä versiossa kaavaa =JOS(B2>=C2; B2; PUUTTUU()). Videolla kerron myös miksi soluihin muodostetaan virheilmoituksia eikä esimerkiksi nollia (0) tai tyhjiä arvoja (””).
- Lisää vielä kolmas kaavasarake ja lisää se kolmantena arvosarjana kaavioon. Muuta sarja viivaksi, jossa näytetään arvojen kohdalla merkit (markers).
- Tee vielä viimeiset viilaukset:
- Piilota viivasarjasta viiva, jätä näkyviin vain merkki (marker). Vaihda halutessasi merkkiä ja muotoile se.
- Tuo esiin arvopisteiden otsikot (data labels) ja muotoile ne.
- Tee muut haluamasi muut muotoilut.
Tarkemmat ohjeet voit katsoa alla olevasta videoklipistä:
Ja kyllä – ratkaisu kelpasi asiakkaalleni.
HExcelligent.fi 
Tämä on aika innovatiivinen. Kikkailu kaavion ja Excelin ominaisuuksilla muistuttaa etäisesti GANTT-kaavion tekemistä. Se hetki kun tajuaa mitä tässäkin kaaviossa tapahtuu on mahtavaa. ”Aaa, eiku…OOOH!”
TykkääTykkää
Kun vielä keksisin keinon esittää tämä havainnollisesti jotenkin muuten kuin punaisella ja vihreällä. 8 % miehistä on puna-vihersokeita ja 0,4 % naisista. Kokeilin esimerkiksi pinota joko kuittausmerkkejä tai rasteja pylväisiin, mutta käytinpä mitä tahansa symbolia, lopputulos oli tosi sotkuinen. Mikään rasterikuviokaan ei kerro katsojalle asiaa yhtä hyvin kuin punainen ja vihreä väri. Ideoita?
TykkääTykkää
Mitä jos pylväät täyttäisi kuvalla? Sullahan on kuitenkin kaksi erillistä sarjaa tuossa. Teksti ”punainen” ja ”vihreä” niillä väreillä tai vastaavat liikennevalot. Tai jotkut symbolit, esim risti ja hyväksytty-pukki. Valot saattaa muuten venähtää muodottomiksi, mutta jokainen tajuaa mikä valo palaa vaikka olisikin värisokea.
TykkääTykkää
Erilaisia kuvatäyttöjä juuri kokeilin ja mm. noita ristejä ja hyväksytty-pukkeja, mutta mikään ei näyttänyt hyvältä. Ehkä tässä pitää vain pyytää puolisolta jokin tähän tarkoitukseen muokattu kuva. 🙂 Jos kuva on kovin selvä, pylvään päälle tuleva numeroarvo ei näy. Pinotuissa pylväissä ei nimittäin pysty sijoittamaan arvoa pylvään yläpuolelle.
TykkääTykkää
😀 😀
Hmm, ei ehkä noi ristit ja pukit sittenkään. Mutta mitä jos muotoilet sen arvon laatikon näkyväksi, täyttöväriksi tiukka kontrasti pylväälle (mieluiten valkoinen) ja tuot sen aina edessä olevaksi. Muistaakseni mä oon joskus kikkaillut arvojen kanssa noin.
Toinen on tehdä näkymätön lisäpylväs josta näytetään vain arvo, joka on sama kuin näkyvän pinotun pylvään arvo.
TykkääTykkää
Tuota kun jaksaisin jatkojalostaa niin, että kun alhaalta lähestytään tavoitetta, niin sitä punaisemmaksi palkki muuttu ja kun mennää yli, niin vaaleen vihreä ja kun nouseen, niin tummenee vihreys. Ja se tavoite näkyisi läpi missä se oli ja tietty arvot näkyisi myös.. Lähinnä tuo palkin värin muuttuminen tulee antamaan harmaita hiuksia minulle lisää 🙂
TykkääTykkää
Palkin värin muuttumista ei taida saada toteutettua kovin helposti siten, että se osaisi reagoida suhteessa tavoitteeseen. Todennäköisesti sinun pitää tehdä kaavojen avulla apusarakkeet eli -sarjat: yksi arvosarja tavoitteeseen asti ja toinen tavoitteesta ylöspäin. Ekaan palkkiin punainen liukuväri ja toiseen vihreä ja lopulta palkit pinoon. Ehkäpä testailen minäkin.
TykkääTykkää
Huomenna testaan tuota sinun tekemääsi tapaa ja laitan tavoitteen näkyville vaikka se olisi saavutettu jo.
Taistelen tällä hetkellä ison taulukon kanssa jossa A-sarakkeessa kuukaudet 5 vuodelta ja seuraavissa kuukausien myyntilukuja eri tuotteille. Pitäisi saada vertailukaavio jossa näkyisi viimeisen täyden kuukauden myynnit 12 kk taaksepäin ja sitä verrataan siitä kuukaudesta edelliseen 12 kuukauteen. Esim touko2014-huhti2015 välissä olevia kuukausia verrataan touko2013-huhti2014 välissä oleviin kuukausiin keskenään. Ja kun toukokuun 2015 tulokset tulee, niin 24 kk (12kk+12kk) aikaikkuna siirtyy.
Alekkain kun kaikki lukemat, niin aputaulukkoja käyttämällä saan tuon vertailun tehtyä ja käsin päivitän ne.
Aloin muuten käyttämään Pivottia ja taulukoita about kk sitten ja aivan älyttömästi oppinut tässä ajassa, pelkästään blogeja, vinkkejä ja youtubea katselemalla ja kokeilemalla mutta paljon opittavaa. VBA pitäisi ottaa jotenkin hanskaan, niin saisi kaikkea kivaa rakennettua.
ps. niin, taulukoissa/pivoteissa se huono puoli, että ei voi jakaa monelle käytettäväksi yhtäaikaa 🙂
TykkääTykkää
Sun pitääkin seuraavaksi ryhtyä opiskelemaan Power Pivotia. Power Pivotissa on juuri tuollaisia time intelligence -funktioita, joiden avulla saa tehtyä aikaan sidottuja vertailuita. 😉
TykkääTykkää