Rivin värjääminen päivämäärän perusteella

Kalenterina toimivissa Excel-tiedostoissa on toisinaan kätevää, jos kuluvan päivän rivit saa erotettua muusta sisällöstä selkeillä väreillä. Rivien värjääminen on yksinkertaista, eikä vaadi kuin yhden pienen kaavan.

Lataa esimerkkitiedosto tästä (RivinVärjäysPvmMukaan.xlsx)

Esimerkki 1/2

Kuva 1. Klikkaa kuva suuremmaksi.

Kuvan esimerkissä on ensin maalattu se alue, johon sääntö halutaan kohdistaa (B3:sta D9:ään). Haluttu alue on kaikki kalenterin rivit ilman otsikoita. Tarkoitus ei ole kohdistaa värjäyssääntöjä koko työkirjan leveyteen, vaan vain tarvittavalle alueelle.

Rivien värjäämiseen käytetään kuvassa seuraavaa kaavaa: = $B3 = TÄMÄ.PÄIVÄ()

Kaavan selitys: Kaavassa verrataan sarakkeen B arvoja kuluvan päivän päivämäärään, joka saadaaan TÄMÄ.PÄIVÄ()-funktiolla. Kaava on tosi, kun jokin sarakkeen B arvo täsmää TÄMÄ.PÄIVÄ()-funktioon. Vertailu aloitetaan solusta B3, sillä päivämäärät ovat kuvan sarakkeessa B ja rivi 3 on kuvassa kalenterin ensimmäinen rivi. Valitse B3:n tilalle oman kalenterisi vastaavan päivämääräsarakkeen ensimmäinen solu.

Huom 1: Kaavassa on lukittu päivämäärän sisältävä sarake B, jotta koko rivin vertailu tapahtuu nimenomaan siihen. Jos saraketta ei lukitse, värjäytyy ainoastaan päivämäärän sisältävä solu. Rivinumero jätetään vapaaksi, jotta sääntö kohdistuu koko kalenteriin. Jos sääntö ei toimi, tarkista $-merkkien määrä, jotta vertailuun syöttämäsi viittaus on muotoa $B3.

Huom 2: Jos kaava on kirjoitettu oikein, mutta kalenterissa ei tapahdu mitään, varmista säännön kohdistuminen oikeaan alueeseen (katso nuoli 2 alla olevassa kuvassa 2). Sääntöä voi korjata klikkaamalla ympyröityä kohtaa ja kirjoittamalla tai maalamalla uusi alue.

Lopuksi valitaan, millaisella muotoilulla rivi värjätään. Kuvassa on valittu valkoinen teksti tummansinisellä taustalla.

Esimerkki 2/2

Päivämäärärivejä pystyy värjäämään myös muilla määrityksillä. Jos halutaan esimerkiksi värjätä kaikki kuluvan viikon rivit, voidaan tehdä hieman laajempi vertailu.

Ensin valitaan haluttu alue samaan tapaan kuin esimerkissä 1.

Kuluvan viikon rivien värjääminen on esimerkkitiedostossa toteutettu kaavalla:
= JA( VUOSI($F3) = VUOSI(TÄMÄ.PÄIVÄ()) ; VIIKKO.ISO.NRO($F3) = VIIKKO.ISO.NRO(TÄMÄ.PÄIVÄ()) )

Kaavassa tehdään kaksi vertailua – vuosi ja viikkonumero – , sillä viikkonumero toistuu vuosittain. Vertailut sidotaan toisiinsa laittamalla ne JA -funktion sisään. Vertailujärjestyksellä ei ole väliä, sillä kaava on tosi vain, jos molemmat vertailut ovat tosia.

Ensimmäiseksi vertaillaan vuotta: VUOSI($F3) = VUOSI(TÄMÄ.PÄIVÄ())

Molemmat vertailtavat arvot $F3 ja TÄMÄ.PÄIVÄ() on laitettava VUOSI-funktioiden sisään, jotta vertailtavat arvot ovat samaa lajia eli vuosilukuja. Huomioi jälleen sarakkeen lukitus $-merkillä ja rivinumeron jättäminen vapaaksi.

Seuraavaksi vertaillaan viikkoa: VIIKKO.ISO.NRO($F3) = VIIKKO.ISO.NRO(TÄMÄ.PÄIVÄ())

Ajatus on sama kuin vuoden kanssa, eli arvot laitetaan VIIKKO.ISO.NRO -funktioiden sisään, jotta vertailtavat arvot ovat viikkonumeroita. Tässä käytetään VIIKKO.ISO.NRO -funktiota, jolla saadaan suomenkielisessä Excelissä haluttu viikkonumero ilman ylimääräisiä parametrejä.

Huom 3: Kun sääntöjä on enemmän kuin yksi, niiden järjestyksellä on väliä. Esimerkkitiedostossa on laitettu päiväsääntö viikkosäännön yläpuolelle, jotta kuluva päivä värjäytyy viikkovärjäyksen päälle (nuoli 1).

 

Kuva 2. Klikkaa kuva suuremmaksi.

Lataa esimerkkitiedosto tästä (RivinVärjäysPvmMukaan.xlsx)

 


Caduseus Consulting avustaa ja kouluttaa monenlaisissa IT-tarpeissa!

Mikäli Excel-työ takkuilee ja haluatte ammattilaisapua jonkin projektin kanssa, voitte tilata asiantuntijapalvelua hintaan 45 €/h (+ alv). Kaikista toimeksiannoista voidaan antaa myös kiinteähintainen kokonaistarjous.

Katso myös hintalaskuri edullisille tilauskoulutuksille ja tilaa organisaatiollenne suosittu Excel helpoksi työelämässä-koulutus.


 
 

55 kommenttia.

  1. Angeline

    syyskuu 26, 2020 at 23:17

    I am sure this post has touched all the internet
    people, its really really fastidious paragraph on building up new weblog.

    Reply
  2. Norris

    lokakuu 4, 2020 at 06:34

    I have read so many articles regarding the blogger lovers except this article is really a fastidious article, keep it
    up.

    Reply
  3. private instagram story viewer

    lokakuu 5, 2020 at 19:02

    Great post however , I was wanting to know if you could write a
    litte more on this subject? I’d be very grateful if you could elaborate
    a little bit more. Cheers!

    Reply
  4. instagram story viewer private account

    lokakuu 5, 2020 at 21:04

    Unquestionably believe that which you stated.

    Your favorite reason seemed to be on the internet the simplest thing to understand of.
    I say to you, I definitely get irked at the same time as folks think about issues that they just do
    not know about. You managed to hit the nail upon the top as well as defined out the entire thing without having side effect , other people could take a signal.
    Will probably be again to get more. Thanks

    Reply
  5. instagram private unlocker

    lokakuu 5, 2020 at 21:25

    What’s Going down i am new to this, I stumbled upon this I’ve found It positively useful and
    it has aided me out loads. I hope to give a contribution & assist other customers like its helped me.
    Great job.

    Reply
  6. privateinstaviewer.com

    lokakuu 5, 2020 at 22:00

    Every weekend i used to pay a quick visit this site, for the
    reason that i want enjoyment, as this this site
    conations really fastidious funny material too.

    Reply
  7. instagram private profile viewer

    lokakuu 6, 2020 at 05:27

    Wonderful beat ! I wish to apprentice even as you amend your web site, how can i
    subscribe for a weblog site? The account aided me a applicable deal.
    I have been tiny bit acquainted of this your broadcast provided
    brilliant transparent idea

    Reply
  8. instagram private unlocker

    lokakuu 6, 2020 at 06:57

    I simply could not go away your website before suggesting that I actually loved the standard info a person supply to your guests?
    Is gonna be back continuously in order to inspect new posts

    Reply
  9. insta private viewer

    lokakuu 6, 2020 at 07:44

    Excellent post! We are linking to this particularly great post on our site.
    Keep up the great writing.

    Reply
  10. watchinsta

    lokakuu 6, 2020 at 09:40

    It’s hard to come by educated people on this subject, however,
    you sound like you know what you’re talking about!
    Thanks

    Reply
  11. private insta viewer

    lokakuu 6, 2020 at 12:45

    If some one wants to be updated with hottest technologies afterward he must be visit this web page and be
    up to date everyday.

    Reply
  12. instagram private viewer

    lokakuu 6, 2020 at 13:22

    Can you tell us more about this? I’d like to find out more details.

    Reply
  13. instagram private profile viewer

    lokakuu 6, 2020 at 16:12

    Awesome! Its genuinely remarkable post, I have got much clear idea about from this article.

    Reply
  14. instagram private account viewer

    lokakuu 6, 2020 at 16:54

    My brother recommended I might like this web site.
    He used to be entirely right. This publish truly made my day.
    You cann’t believe just how so much time I had spent
    for this info! Thanks!

    Reply
  15. private insta viewer

    lokakuu 6, 2020 at 18:14

    Thanks for every other fantastic article.
    Where else could anyone get that kind of info in such an ideal method of writing?
    I have a presentation next week, and I’m at the look for such info.

    Reply
  16. instagram private profile viewer

    lokakuu 6, 2020 at 18:47

    Appreciate this post. Will try it out.

    Reply
  17. private instagram story viewer

    lokakuu 6, 2020 at 20:00

    I got this web page from my pal who told me concerning this website
    and at the moment this time I am visiting this web site and reading very
    informative posts at this time.

    Reply
  18. private instagram story viewer

    lokakuu 7, 2020 at 00:25

    I’m not that much of a online reader to be honest but your blogs really nice, keep it
    up! I’ll go ahead and bookmark your website to come back down the road.
    All the best

    Reply
  19. privateinstaviewer.com

    lokakuu 7, 2020 at 02:18

    Hello, this weekend is pleasant in favor of me, as this occasion i
    am reading this enormous informative piece of writing here at my residence.

    Reply
  20. private instagram viewer

    lokakuu 7, 2020 at 04:04

    I have read so many articles concerning the blogger lovers
    but this post is really a nice article, keep it up.

    Reply
  21. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 04:18

    I read this piece of writing fully concerning the
    resemblance of hottest and previous technologies, it’s awesome article.

    Reply
  22. watchinsta

    lokakuu 7, 2020 at 04:51

    I used to be recommended this web site via my cousin. I am no longer positive whether this
    publish is written by him as no one else know such unique about my difficulty.
    You’re incredible! Thank you!

    Reply
  23. how to view private instagram profiles 2020

    lokakuu 7, 2020 at 07:25

    Heya just wanted to give you a quick heads up and let you know a few of
    the pictures aren’t loading properly. I’m not sure why but I think its a linking issue.

    I’ve tried it in two different browsers and both show
    the same outcome.

    Reply
  24. instagram private account viewer

    lokakuu 7, 2020 at 07:37

    I think everything published was very logical.
    However, what about this? what if you typed a catchier title?
    I am not suggesting your information is not solid., but what if
    you added a post title to maybe get people’s attention? I mean blog topic is kinda plain. You should peek at Yahoo’s home
    page and see how they write news headlines to grab people to open the links.
    You might try adding a video or a related pic or two to grab people excited about everything’ve written. In my opinion, it would make your website a little
    bit more interesting.

    Reply
  25. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 07:48

    I think this is among the most important info for
    me. And i’m glad reading your article. But should
    remark on some general things, The site style is great, the articles is really excellent : D.
    Good job, cheers

    Reply
  26. instagram private profile viewer

    lokakuu 7, 2020 at 09:38

    I feel this is one of the such a lot important info for me.
    And i am satisfied studying your article. But wanna commentary
    on some general issues, The web site style is wonderful,
    the articles is in point of fact great : D.
    Excellent task, cheers

    Reply
  27. private insta

    lokakuu 7, 2020 at 10:04

    These are truly great ideas in concerning blogging.
    You have touched some nice points here. Any way keep up wrinting.

    Reply
  28. private instagram viewer free

    lokakuu 7, 2020 at 11:43

    In fact no matter if someone doesn’t understand afterward its up to other
    people that they will assist, so here it takes place.

    Reply
  29. instagram private unlocker

    lokakuu 7, 2020 at 11:46

    I am regular reader, how are you everybody? This piece of writing posted at this web site is actually fastidious.

    Reply
  30. instagram private profile viewer

    lokakuu 7, 2020 at 14:51

    Hello, yeah this piece of writing is really nice and I
    have learned lot of things from it on the topic of blogging.
    thanks.

    Reply
  31. instagram private profile viewer

    lokakuu 7, 2020 at 15:33

    Fantastic beat ! I wish to apprentice whilst you amend your web site,
    how can i subscribe for a weblog site? The account aided me a
    acceptable deal. I had been a little bit familiar of this your broadcast provided brilliant
    transparent idea

    Reply
  32. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 17:10

    Hey there! Do you know if they make any plugins to protect against hackers?

    I’m kinda paranoid about losing everything I’ve worked hard on.
    Any recommendations?

    Reply
  33. instagram private viewer

    lokakuu 7, 2020 at 17:39

    Everything is very open with a very clear explanation of the
    challenges. It was definitely informative. Your website is extremely
    helpful. Thank you for sharing!

    Reply
  34. private instagram viewer

    lokakuu 7, 2020 at 17:48

    Awesome! Its genuinely amazing piece of writing, I have got
    much clear idea concerning from this article.

    Reply
  35. privateinstaviewer view private instagram profiles easily

    lokakuu 7, 2020 at 19:03

    It’s awesome for me to have a website, which is beneficial in support
    of my know-how. thanks admin

    Reply
  36. privateinstaviewer

    lokakuu 7, 2020 at 21:06

    It’s amazing to pay a quick visit this site and reading the views of all friends about this post,
    while I am also zealous of getting familiarity.

    Reply
  37. private insta viewer

    lokakuu 7, 2020 at 21:13

    Have you ever considered about adding a little bit more than just your articles?
    I mean, what you say is valuable and everything. But imagine if you added some great graphics or
    videos to give your posts more, “pop”! Your content is excellent but with images and videos, this website could certainly be one of the greatest in its niche.

    Wonderful blog!

    Reply
  38. private insta viewer

    lokakuu 7, 2020 at 21:17

    always i used to read smaller articles that as well clear their
    motive, and that is also happening with this piece of writing
    which I am reading now.

    Reply
  39. instagram private viewer

    lokakuu 8, 2020 at 00:09

    I like what you guys are up too. Such clever work and reporting!
    Keep up the very good works guys I’ve added you guys to our blogroll.

    Reply
  40. private instagram viewer

    lokakuu 8, 2020 at 01:35

    Genuinely no matter if someone doesn’t understand afterward its up
    to other users that they will help, so here it happens.

    Reply
  41. ig private unlock

    lokakuu 8, 2020 at 01:47

    Remarkable! Its in fact remarkable article, I have got much clear idea on the topic of from this article.

    Reply
  42. instagram private profile viewer

    lokakuu 8, 2020 at 01:51

    This is a topic that’s close to my heart…
    Take care! Where are your contact details though?

    Reply
  43. instagram private viewer

    lokakuu 8, 2020 at 02:16

    What a material of un-ambiguity and preserveness of precious experience regarding unpredicted feelings.

    Reply
  44. instagram private profile viewer

    lokakuu 8, 2020 at 02:56

    Ahaa, its nice conversation on the topic of this piece of writing at this place at this weblog, I have read all that, so at this time me also commenting here.

    Reply
  45. Hosea

    lokakuu 8, 2020 at 03:46

    What a material of un-ambiguity and preserveness of valuable familiarity on the topic of unpredicted emotions.

    Reply
  46. insta private viewer

    lokakuu 8, 2020 at 04:24

    Excellent web site. Plenty of helpful information here.

    I’m sending it to a few friends ans additionally
    sharing in delicious. And naturally, thanks on your sweat!

    Reply
  47. insta private viewer

    lokakuu 8, 2020 at 05:37

    Wow, incredible blog layout! How long have you been blogging for?
    you make blogging look easy. The overall look of your website is
    fantastic, let alone the content!

    Reply
  48. instagram private profile viewer

    lokakuu 8, 2020 at 07:42

    I think the admin of this web site is really working hard
    in favor of his web site, because here every information is quality based stuff.

    Reply
  49. private instaviewer

    lokakuu 8, 2020 at 08:45

    I don’t even understand how I ended up here, however I assumed
    this publish was once great. I don’t recognise who you
    are however definitely you’re going to a
    famous blogger in case you are not already.
    Cheers!

    Reply
  50. private instagram viewer

    lokakuu 8, 2020 at 09:13

    Does your blog have a contact page? I’m having a tough time locating it but, I’d like to
    send you an email. I’ve got some creative ideas for your blog you
    might be interested in hearing. Either way, great website and I look forward to
    seeing it improve over time.

    Reply

Vastaa

Sähköpostiosoitettasi ei julkaista. Pakolliset kentät on merkitty *