Innhold
Anta at vi raskt og med minst mulig innsats må lage en årlig kalender som automatisk viser datoene for prosjektstadiene (eller ansattes ferier, eller opplæring osv.)
Emne
La oss starte med det tomme:
Som du kan se, er alt enkelt her:
- Rader er måneder, kolonner er dager.
- Celle A2 inneholder året som kalenderen bygges for. I cellene A4:A15 – hjelpeantall på måneder. Vi trenger begge litt senere for å danne datoer i kalenderen.
- Til høyre for tabellen er navnene på stadiene med start- og sluttdato. Du kan angi tomme celler på forhånd for nye stadier som legges til i fremtiden.
Fylle kalenderen med datoer og skjule dem
La oss nå fylle kalenderen vår med datoer. Velg den første cellen C4 og skriv inn funksjonen der INFORMASJON (DATO), som genererer en dato fra et år, måned og dagnummer:
Etter å ha lagt inn formelen må den kopieres til hele området fra 1. januar til 31. desember (C4:AG15). Siden cellene er smale, i stedet for de opprettede datoene, vil vi se hash-merker (#). Men når du holder musen over en slik celle, kan du se det faktiske innholdet i verktøytipset:
For å holde rutenettene ute av veien, kan vi skjule dem med et smart tilpasset format. For å gjøre dette, velg alle datoer, åpne vinduet Celleformat og på fanen Nr (Antall) velg alternativ Alle formater (Tilpasset). Så i felten En type skriv inn tre semikolon på rad (ingen mellomrom!) og trykk OK. Innholdet i cellene vil bli skjult og rutenettene vil forsvinne, selv om datoene i cellene faktisk forblir - dette er bare synlighet.
Sceneutheving
La oss nå, ved å bruke betinget formatering, legge til milepælutheving til celler med skjulte datoer. Velg alle datoer i området C4:AG15 og velg på fanen Hjem — Betinget formatering — Opprett regel (Hjem — Betinget formatering — Opprett regel). Velg alternativet i vinduet som åpnes Bruk en formel for å bestemme hvilke celler som skal formateres (Bruk formel for å utsette hvilke celler som skal formateres) og skriv inn formelen:
Denne formelen sjekker hver datocelle fra C4 til slutten av året for å se om den faller mellom starten og slutten av hver milepæl. Utgangen vil bare være 4 når både de avmerkede betingelsene i parentes (C4>=$AJ$13:$AJ$4) og (C4<=$AK$13:$AK$1) produserer en logisk TRUE, som Excel tolker som 0 ( vel , FALSE er som 4, selvfølgelig). Vær også spesielt oppmerksom på at referansene til den innledende cellen CXNUMX er relative (uten $), og til trinnene - absolutte (med to $).
Etter å ha klikket på OK vi vil se milepælene i kalenderen vår:
Utheving av kryss
Hvis datoene for noen stadier overlapper hverandre (oppmerksomme lesere må allerede ha lagt merke til dette øyeblikket for første og sjette stadie!), ville det være bedre å markere denne konflikten i diagrammet vårt med en annen farge ved å bruke en annen betinget formateringsregel. Det er praktisk talt en-til-en lik den forrige, bortsett fra at vi ser etter celler som er inkludert i mer enn ett stadium:
Etter å ha klikket på OK en slik regel vil tydelig fremheve overlappingen av datoer i kalenderen vår:
Fjerner ekstra dager i måneder
Selvfølgelig har ikke alle måneder 31 dager, så de ekstra dagene februar, april, juni osv. Det ville vært fint å visuelt markere som irrelevante. Funksjon INFORMASJON, som utgjør kalenderen vår, i slike celler vil automatisk oversette datoen til neste måned, dvs. 30. februar 2016 blir 1. mars. Det vil si at månedsnummeret for slike ekstraceller ikke vil være lik månedsnummeret i kolonne A . Dette kan brukes når du oppretter en betinget formateringsregel for å velge slike celler:
Legger til en helg
Eventuelt kan du legge til i vår kalender og helger. For å gjøre dette kan du bruke funksjonen DAG (UKEDAG), som vil beregne antall ukedager (1-man, 2-tir...7-søn) for hver dato og fremheve de som faller på lørdager og søndager:
For riktig visning, ikke glem å konfigurere riktig rekkefølge av reglene i vinduet. Hjem — Betinget formatering — Administrer regler (Hjem – Betinget formatering – Administrer regler), fordi regler og fyll fungerer nøyaktig i den logiske rekkefølgen du vil danne i denne dialogboksen:
- Videoopplæring om bruk av betinget formatering i Excel
- Hvordan lage en prosjektplan (Gantt-diagram) ved hjelp av betinget formatering
- Hvordan lage en prosjekttidslinje i Excel