Hvordan lage en dynamisk skiftende kalender I Excel

i denne artikkelen skal Vi vise deg hvordan du lager en dynamisk skiftende kalender i Excel.

Last Ned Arbeidsbok

Fyll ut variabler

Første trinn er å skape variablene som skal brukes i formlene. Disse variablene må gi dagens informasjon slik at kalenderen kalenderen kan oppdateres automatisk. Her er listen over variabler:

• Nåværende dato
• tallet for den nåværende dagen i måneden
• tallet for den nåværende måneden
• Nåværende år
• datoen for den første dagen i den nåværende måneden
• tallet for den nåværende dagen i uken
• tallet for den nåværende uken i året

vi Bruker funksjonene i dag, dag, måned, år, dato, ukedag og uke. Kort, MENS I DAG funksjonen returnerer datoen for i dag, DAG, MÅNED, ÅR, UKEDAG og UKE funksjoner analysere de respektive datoverdier. DATO-funksjonen returnerer datoverdien for det gitte året, måneden og dagen.

i følgende skjermbilde kan du se navnene vi har gitt og formler som ble generert 20.November 2020.

vi kalte hver variabel for å gjøre jobben enklere når du lager formler. Neste trinn for å opprette en kalender I Excel er å lage en disposisjon for kalendergrensesnittet.

Disposisjon For Kalenderen I Excel

før du oppretter de faktiske formlene som genererer dagene i måneden, må vi først plassere en disposisjon for å lage formler og også gi visuell referanse.

dagene vil bli plassert på et bord med 7 kolonner og 6 rader. Vi må øke disse tallene med 2 for titler og hjelperceller også. Mens kolonne-og radtitlene inkluderer ukedager for kolonner og ukenummer etter rader, vil hjelpecellene være påfølgende tall fra 1.

kalenderen kan se ut nedenfor.

Bruk kantlinjer eller bakgrunnsfarger som du vil. Det viktige poenget er å bruke et dynamisk ukenummer i tittelen. Referere TIL WEEKNUM funksjon for den første uken, og legge til 1 for hver påfølgende uke.

La oss fortsette å lage en kalender i Excel ved å fylle dagnumrene.

Genererende Dager

hvis du bruker et lignende oppsett, Og ikke vil bekymre deg for detaljene i formelen, kan du kopiere formelen nedenfor. Lim deretter den inn i cellen øverst til venstre i kalenderdagsområdet, og fyll ut for de gjenværende cellene. G2-og E4-cellene refererer til de første cellene i hjelpekolonnene. Dermed må du kanskje justere disse referansene, med mindre Den første cellen Er G4.

Nå, la oss ta en nærmere titt på formelen. Først bruker formelen DATO-funksjonen, som returnerer en dato etter gitt år, måned og dag. De første 2 argumentene leveres av variablene YearNumber og MonthNumber som allerede er beregnet.

dagdelen av formelen tar sikte på å generere dagstall på en 7-base, siden det er 7 dager i uken. 7 * ($E4-1) del av formelen kontroller ukenummeret I hjelpekolonnen E, og genererer en base, starter med 0 og øker med 7 hver gang.

vi kan øke eller redusere dette tallet så mye som forskjellen mellom ukedagen på den første dagen i måneden med dagens ukenummer. Med andre ord får vi den første dagen i den nåværende måneden som et ankerpunkt. For eksempel er det den 7. dagen i uken (i mandag til søndag base) for 1. November 2020.

hvis vår kalenderuke antar at mandag er den første dagen i uken, bør mandag være 6 dager før (=1-7) den første dagen i måneden. Dermed får vi tall som nedenfor.

Legg disse verdiene til 1 for å skifte tallene. Så, søndag kan bli den første dagen som i følgende eksempel.

Som et resultat har vi 2 arrays for 2 dimensjoner. For å lage et bord må vi legge til begge arrays sammen.

disse tallene representerer dagen i den nåværende måneden. Det er tall mindre enn 1 eller større enn 31, som åpenbart ikke kan være dager i måneden.

DATO-funksjonen håndterer denne typen tall ved å generere en dato i forrige måned eller neste. For eksempel blir -2 to dager før den angitte månedens første dag.

Til slutt, hvis du bruker disse tallene I DATOFUNKSJONEN, kan du få de faktiske datoene for den nåværende måneden.

Formatering

Dagnumre

nå, la oss fylle dagene. Du kan endre formateringen av verdiene uten å endre den faktiske verdien. Denne funksjonen kalles Nummerformatering. Følg trinnene for å justere tallformateringen for å vise bare dagene.

1. Velg alle datoene i dagområdet
2. Trykk Ctrl + 1 For å åpne Dialogboksen Formater Celler
3. Aktiver Tallfanen hvis Den ikke er åpen allerede
4. Velg Egendefinert I Kategoriliste
5. Skriv inn bokstaven d i Type-boksen
6. Klikk OK for å lagre
   

nå er kalenderoppsettet ferdig.

hvis du vil fjerne polstring for ensifrede datoer, kan du bruke dd i stedet for enkelt d. For Å lære mer om tallformatering: Tallformatering I Excel – Alt Du Trenger Å Vite

Fremhever i dag

Til Slutt vil Vi bruke Betinget Formatering til å angi forskjellige farger for dager som ikke er i den nåværende måneden,og markere i dag.

for å legge til disse betingede formateringsalternativene;

1. Velg dagområdet
2. Klikk På Betinget Formatering-ikonet Under Fanen Hjem På Båndet
3. Velg Ny Regel
   
4. Velg Bruk en formel til å bestemme hvilke celler som skal formateres
5. Angi en formel For å definere regelen for å markere gjeldende dag
   =G4 = I DAG ()
   G4: cellen øverst til venstre i området inkluderer dager. Bruk en relativ referanse(ikke bruk $).
6. Klikk På Format-knappen I Forhåndsvisningsvinduet I Dialogboksen Formater Celler
   
7. i dialogboksen velger du formateringsalternativer du vil se i dagens celle
   vi brukte en oransje bakgrunn i dette eksemplet.
8. Klikk OK for å bruke
   
9. Forhåndsvisningsboksen viser innstillingene dine
10. Klikk OK for å bruke betinget formatering
    

slik ser det ut:

formelen returnerer En Boolsk verdi. Hvis celleverdien (G4) er lik resultat AV I DAG-funksjonen, som returnerer i dag i et datoformat, returnerer formelen SANN. Ellers FALSK. Hvis resultatet ER SANT, bruker Excel formateringen i cellen.

det er 2 viktige poeng her:

1. Du må bruke en relativ referanse, for Eksempel G4, med mindre Du ikke Vil At Excel skal fylle referansen langs området. Vurder hvordan formler endres når du kopierer dem andre steder. Excel gjelder det samme for betingede formateringsformler også. For mer informasjon, se: Hvordan du oppretter En Excel absolutt referanse og relativ referanse
2. dagcellene må returnere de faktiske datoverdiene. Det er derfor vi bruker tallformatering for å vise dagdelene.