Vuoroväriset viikot Excel-kalenterissa – kaavoihin perustuvat muotoilut

Vuoroväriset viikot Excel-kalenterissa – kaavoihin perustuvat muotoilut

Esittelen tässä jutussa kaavoihin perustuvan tekniikan, jonka avulla saat aikaan dynaamisesti muuttuvia muotoiluita. Sen lisäksi, että toteutat tämän tekniikan avulla tyylikkäitä automatisoituja taulukkoratkaisuita ilman turhia makroja, säästät rutkasti aikaa. Ideaa voi hyödyntää monenlaisissa tilanteissa, mutta nyt käytän esimerkkinä kalenteria, jonka parilliset ja parittomat viikot halutaan muotoilla vuorovärein.

Kun vaihdat alla olevan taulukon aloituspäivää tai kalenterissa näytettävien viikkojen lukumäärää, huomaat että kalenterin viikot muotoutuvat automaattisesti vuorovärisiksi riippumatta siitä mistä kohdasta viikot alkavat. Kun kirjoitat Näytä viikonloput -kohtaan x:n, viikonloput näytetään harmaina.

Voit avata taulukon koko näytölle tai ladata työkirjan itsellesi yllä olevan upotusikkunan oikean alakulman painikkeilla.

Muodostin kalenterin luomalla kaavat alueelle A6:B8 ja kopioin B-sarakkeen kaavat sarakkeeseen MZ asti, jotta kalenteri pystyy näyttämään 52 viikkoa. En käy läpi kaavoja, mutta voit tutustua niihin lataamalla työkirjan itsellesi.

Silmäys: tavalliset ehdolliset muotoilut

Tavalliset yksinkertaisimmat ehdolliset muotoilut perustuvat siihen, että muotoiltavan solun sisältöä sellaisenaan verrataan haluttuun arvoon. Solu esimerkiksi muotoillaan, jos solun sisältö on suurempi kuin toisen solun sisältö (kuvassa verrataan solun B1 sisältöön).

perinteinen ehdollinen muotoilu

Kaavoihin perustuvat ehdolliset muotoilut

On tilanteita, joissa vertailua ei voi tehdä muotoiltavan solun sisällön perusteella sellaisenaan, vaan solulle pitää ensin suorittaa laskutoimitus tai funktio, jonka tuloksen perusteella päätellään muotoillaanko solu vai ei. Käytännössä tämä tarkoittaa, että pitää luoda kaava tai lauseke, jonka tulos on joko TRUE (TOSI) tai FALSE (EPÄTOSI). Jos lauseke on tosi, muotoilu suoritetaan.

Esimerkiksi kalenterin taustaväri ei perustu päivämäärään sellaisenaan vaan päivämäärästä funktion avulla johdettuun viikkonumeroon ja siihen onko viikkonumero parillinen vai pariton.

Esimerkkikalenterin alueen A6:MZ8 muotoiluissa on käytetty seuraavia kaavoja, joissa pvmsolu:n ja viikkonrosolu:n tilalla käytetään soluosoitteita:

  • Solu muotoillaan vaaleankeltaisella taustalla, jos päivämäärän perusteella määritelty viikkonumero on parillinen.
    EN: =ISEVEN(WEEKNUM(pvmsolu; 21))   FI:  =ONPARILLINEN(VIIKKO.NRO(pvmsolu; 21))
    WEEKNUM-funktio antaa viikkonumeron eurooppalaisen standardin mukaisesti (ISO 8601), kun argumenttina käytetään lukua 21, joka on ollut Excelissä versiosta 2010 lähtien. Vanhemmissa Excel-versioissa käytetään koodia 2, mutta se aloittaa tiettyinä vuosina eurooppalaisittain viikkojen numeroinnin väärin.
  • Solu muotoillaan vaaleanoranssilla taustalla, jos päivämäärän perusteella määritelty viikkonumero on pariton.
    EN: =ISODD(WEEKNUM(pvmsolu; 21))   FI: =ONPARITON(VIIKKO.NRO(pvmsolu; 21))
  • Solu muotoillaan harmaalla taustalla, jos päivämäärän perusteella määritelty viikonpäivän numero on suurempi tai yhtä suuri kuin 6 JA  _Näytä_vkonloput -solussa lukee x.
    EN: =AND(WEEKDAY(pvmsolu;2)>=6;  _Näytä_vkonloput=”x”)   FI: =JA(VIIKKO.NRO(pvmsolu;2)>=6;  _Näytä_vkonloput=”x”)
  • Solu varustetaan tummalla vasemmalla reunaviivalla, jos rivillä 6 on viikkonumero eli kyseessä on viikon ensimmäinen päivä.
    EN: =ISNUMBER(viikkonrosolu)    FI:  =ONLUKU(viikkonrosolu)

Lisäksi kalenterissa on muotoilu rivillä 9 (A9:MZ9):

  • Solu muotoillaan oranssilla taustavärillä, jos rivillä 8 on luku.
    EN: =ISNUMBER(pvmsolu)   FI:  =ONLUKU(pvmsolu)

Kaavaan perustuvan muotoilun luonti

Parillisten viikkojen muotoilu:

  1. Valitse alue, jonka haluat muotoilla (esimerkissä A6:MZ8).
  2. Valitse Home > Styles > Conditional Formatting > New Rule > Use a formula to determine which cells to format (Aloitus > Tyylit > Ehdollinen muotoilu > Uusi sääntö > Määritä kaavan avulla mitkä solut muotoillaan). Muodosta kaava ja käy määrittämässä muotoilu Format (Muotoile) -painikkeella.

kaava

HUOMAA – TÄRKEÄÄ!

Kaavan pitää antaa tulokseksi joko arvo TRUE (TOSI) tai FALSE (EPÄTOSI). Kaava voi olla funktio tai esimerkiksi vertailulauseke, kuten =A1>B1.

Kun luot kaavan, ajattele että olisit laatimassa kaavaa aktiivisena olevaan soluun (kuvassa solu A6) ja tulisit kopioimaan kaavan sille alueelle, jonka olet valinnut (kuvassa A6:MZ8). Soluviittausten $-merkkeineen pitää olla täsmälleen oikein, jotta muotoilu toimii! Käytä esimerkiksi absoluuttista viittausta $A$8, jos kaikki alueen solut muotoillaan solun A8 perusteella. Yllä olevassa esimerkissä A-sarake halutaan muotoilla tutkimalla A-sarakkeessa ja rivillä 8 olevaa päivämäärää, B-sarake halutaan muotoilla tutkimalla B-sarakkeessa ja rivillä 8 olevaa päivämäärää, C-sarake halutaan muotoilla tutkimalla C-sarakkeessa ja rivillä 8 olevaa päivämäärää. Soluviittauksessa sarakkeen pitää muuttua (A -> B -> C -> jne.), joten sen edessä ei saa olla $-merkkiä. Rivin puolestaan pitää aina olla 8, joten sen eteen tarvitaan $-merkki. Tässä esimerkissä tarvitsemme siis sekaviittauksen A$8.

Muotoiluiden muokkaaminen

Muokkaat ehdollisia muotoiluita valitsemalla Home > Styles > Conditional Formatting > Manage Rules (Aloitus > Tyylit > Ehdollinen muotoilu > Sääntöjen hallinta). Saat kaikki taulukon muotoilut esiin valitsemalla This Worksheet (Tämä laskentataulukko) ja voit muokata muotoiltavan alueen kokoa suoraan Applies to (Käytetään kohteeseen) -listalta. Jos haluat muuttaa kaavaa, valitse muotoilu ja Edit Rule (Muokkaa sääntöä).

muokkaus

Käytin ehdollisia muotoiluita myös aiemmassa Näppärä Excel-lomakalenteri -jutussani.

Jaa omat idiksesi

Tätä ratkaisua pitäisi vielä kehittää, mm. myös arkipyhät voisi halutessaan muotoilla harmaalla. Mutta jääköön se sinulle pähkäiltäväksi.

Vain mielikuvitus rajana (ja osittain Excelin mahdollisuudet), mihin kaavoihin perustuvia muotoiluita voisi käyttää. Jaa omat idiksesi ja laita käyttöskenaariosi tämän jutun kommentteihin.

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s