Resumé
For at oprette en dynamisk månedskalender ved hjælp af en enkelt formel, kan du bruge funktionen SEKVENS, med hjælp fra funktionerne VÆLG og UGEDAG. I det viste eksempel er formlen i B6:
=SEKVENS(6;7;Start-VÆLG(UGEDAG(Start);0;1;2;3;4;5;6))
hvor Start er det navngivne område J2. I det viste eksempel bruges betinget formatering til at fremhæve den aktuelle dato og helligdage og lysere dage i andre måneder. Se nedenfor for en fuldstændig forklaring.
Bemærk: Dynamiske array-funktioner er kun tilgængelige i Excel 365 og 2021.
Forklaring
Bemærk: Dette eksempel antager, at startdatoen vil blive angivet som den første i måneden. Se nedenfor for en formel, der automatisk returnerer den første dag i den aktuelle måned.
I dette eksempel er målet at generere en dynamisk kalender for en given måned, baseret på en startdato indtastet i celle J2, som er navngivet “Start” Vi antager, at Start er en gyldig første-i-måned-dato som 01-01-22, 01-02-22, 01-03-22, 01-04-22 osv. Den endelige kalender skal placere hver dag i måneden i et skema, hvor hver uge starter på søndag, som det ses i eksemplet. Løsningen forklaret nedenfor er baseret på funktionen SEKVENS. SEKVENS er en af de originale dynamiske array-funktioner i Excel og passer perfekt til denne opgave.
Kort version
Forklaringen nedenfor er ret lang. Den korte version er, at SEKVENS funktionen genererer en 6 x 7 matrix med 42 datoer i et kalendergitter, formateret til kun at vise dagen. Dette virker, fordi Excel-datoer kun er serietal. Den største udfordring med dette problem er at finde ud af, hvilken dato man skal starte med for en given måned, som altid er en søndag. Dette håndteres med funktionerne VÆLG og UGEDAG. Betinget formatering bruges til at fremhæve den aktuelle dato og helligdage og lysere dage i andre måneder.
Grundsekvens
Funktionen SEKVENS kan bruges til at generere numeriske sekvenser. For eksempel, for at generere tallene 1 til 10 i ti rækker, kan du bruge SEKVENS sådan her:
=SEKVENS(10) // returnerer {1;2;3;4;5;6;7;8;9;10}
Resultatet er en matrix, der indeholder tallene 1-10. Arrayet vises i et lodret område på ti celler. SEKVENS kan generere arrays i rækker og kolonner. For eksempel opretter følgende formel med tallene 1-10 i en matrix med 5 rækker og 2 kolonner:
=SEKVENS(5;2)
Og formlen nedenfor vil fylde et 7 x 6 gitter af celler med tallene 1-42:
=SEKVENS(6;7)
Skærmbilledet nedenfor viser, hvordan disse formler opfører sig i et regneark:
Det er kun tal, ikke datoer, men du kan se kernekonceptet.
SEKVENS med datoer
Fordi Excel-datoer kun er store serienumre, kan funktionen SEKVENS nemt bruges til at generere arrays af datoer. For eksempel vil formlen nedenfor generere de første datoer for de 31 dage i januar 2023:
=SEKVENS(31;1;DATO(2022;1;1))
Bemærk: funktionen DATO er en mere sikker måde at fastkode datoer i formler, da datoer indtastet som tekst kan misfortolkes.
For at oversætte: vi beder om 31 tal, i et 31 x 1 array, startende med 1. januar 2022. SEKVENS indstiller automatisk til en trinværdi på 1, så resultatet er en liste over serietal, der starter med 44265. Det er klart, at vi ønsker ikke at vise serietal i vores kalender, vi ønsker at vise dage. For at gøre det kan vi bruge det brugerdefinerede talformat “d”. Det vil få Excel til kun at vise dagtallene. Skærmbilledet nedenfor viser før og efter:
Lad os nu se, hvad der sker, hvis vi beder om 6 x 7 gitter, startende med 1. januar 2022:
=SEKVENS(6;7;DATO(2022;1;1))
Når vi først har formateret outputtet med det brugerdefinerede talformat “d”, ser vi i alt 42 tal, begyndende med 1. januar. I slutningen af januar ændres måneden til februar, og dagen bliver igen 1:
Vi har stadig ikke en brugbar kalender, men vi nærmer os!
For at lave en ordentlig kalender, skal den første dag i vores skema starte på søndag. Hvis den første dag i en måned ikke er en søndag, skal vi starte skemaet den sidste søndag i den foregående måned. Hvordan kan vi beregne den sidste søndag i den foregående måned? Før vi kommer ind på specifikke funktioner, lad os præcisere målet.
Første søndag
Hvis den første i en måned tilfældigvis er en søndag, er vi færdige. Der er ingen grund til at gøre noget. Den første i måneden er vores startdato. Men hvis den første i måneden ikke er en søndag, skal vi “rulle tilbage” et antal dage til den foregående søndag. Hvor mange dage skal vi rulle tilbage? Dette afhænger af, hvilken ugedag den første dag i en måned lander på. For eksempel, hvis den første er en tirsdag, skal vi rulle 2 dage tilbage. Hvis den første er en fredag, skal vi rulle 5 dage tilbage. Og hvis den første allerede er en søndag, skal vi rulle 0 dage tilbage.
Nu har vi en ret god idé om, hvad vi skal gøre, vi skal bare implementere den adfærd i en formel. Det er her, formlen bliver lidt tricky, fordi vi skal kombinere to funktioner, UGEDAG og VÆLG, på en måde, som de fleste brugere ikke vil genkende.
Funktionen UGEDAG
For at finde ud af ugedagen bruger vi funktionen UGEDAG. UGEDAG returnerer et tal for hver dag i ugen. Som standard returnerer UGEDAG 1 for søndag og 7 for lørdag. For eksempel returnerer UGEDAG 1 for 1. januar 2023, da den første er en søndag:
=UGEDAG(DATO(2023;1;1)) // returnerer 1
For den 2. januar 2022 returnerer UGEDAG 2, da den anden er en mandag:
=UGEDAG(DATO(2023;1;2)) // returnerer 2
For at opsummere vil UGEDAG give os et tal mellem 1-7 for hver dag i ugen, og vi kan bruge resultatet til at finde ud af, hvor mange dage vi skal rulle tilbage.
Funktionen VÆLG
Funktionen VÆLG bruges til at vælge vilkårlige værdier efter numerisk position. For eksempel, hvis vi har farverne “rød”, “blå” og “grøn”, kan vi bruge VÆLG på denne måde:
=VÆLG(1;"rød";"blå";"grøn") // returnerer "rød"
=VÆLG(2;"rød";"blå";"grøn") // returnerer "blå"
=VÆLG(3;"rød";"blå";"grøn") // returnerer "grøn"
VÆLG er en fleksibel funktion og accepterer en liste af tekstværdier, tal, cellereferencer i enhver kombination.
VÆLG + UGEDAG
Dernæst vil vi kombinere VÆLG og UGEDAG for at give os det korrekte tal der skal rulles tilbage, som dette:
=VÆLG(UGEDAG(Start);0;1;2;3;4;5;6)
Argumentet serienr leveres af funktionen UGEDAG. De andre individuelle værdier, der gives til VÆLG, er tallene der skal rulles tilbage, en for hver dag i ugen. UGEDAG returnerer et tal mellem 1-7, og funktionen VÆLG bruger tallet fra UGEDAG til at vælge et tal fra listen over tal. For eksempel, hvis UGEDAG returnerer 3 (tirsdag), returnerer VÆLG 2:
VÆLG(3;0;1;2;3;4;5;6) // returnerer 2
Nu er vi endelig klar til at bruge dette tilbagerulningstal til at beregne den første søndag i skemaet. Fordi datoer kun er tal i Excel, er opgaven enkel – vi skal bare trække tilbagerulningstallet fra startdatoen:
Start-VÆLG(UGEDAG(Start);0;1;2;3;4;5;6) // første søndag
Resultatet er en gyldig dato, der repræsenterer den første søndag i kalenderskemaet.
Vi samler det hele
Nu skal vi kombinere ideerne forklaret ovenfor i en enkelt formel baseret på SEQUENCE-funktionen. Vi starter med at bede om et 6 x 7 array af tal som dette:
=SEKVENS(6,7 // 6 rækker, 7 kolonner
Så, til startargumentet, leverer vi blot den kode, vi udarbejdede ovenfor:
=SEKVENS(6;7;Start-VÆLG(UGEDAG(Start);0;1;2;3;4;5;6))
Resultatet er et komplet gitter med 42 datoer, der kan vises som en månedlig kalender. Hvis startdatoen i J2 ændres til en anden første i månedsdato, opdateres skemaet automatisk.
Regler for betinget formatering
De betingede formateringsregler for at fremhæve den aktuelle dato og helligdage og lysere dage i andre måneder er angivet nedenfor:
Formlen for den aktuelle dato er:
=B2=IDAG()
Formlen til at fremhæve helligdage er baseret på funktionen COUNTIF:
=TÆL.HVIS(Helligdage;B2)
Hvis tallet er andet end nul, skal datoen være en helligdag. Helligdage skal være et interval, der indeholder gyldige Excel-datoer, der repræsenterer ikke-arbejdsdage. I det viste eksempel er helligdage det navngivne område L6:L8. Du kan tilføje flere helligdage til denne liste, som du vil, men glem ikke at opdatere det navngivne område. Alternativt kan du definere helligdage som en Excel-tabel, så intervallet opdateres automatisk.
Formlen til dage i andre måneder er baseret på funktionen MÅNED:
=MÅNED(B6)<>MÅNED(Start)
Hvis måneden for den aktuelle dato er forskellig fra måneden for datoen i J2, skal du udløse reglen.
Kalender titel
Formlen til at udskrive kalendertitlen i celle B4 er baseret på TEKST-funktionen:
=TEKST(Start;"mmmm åååå")
Titlen er centreret over kalenderskemaet med centreret på tværs af markeringen. Vælg B1:H1, og brug Ctrl + 1 til at åbne Formater celler, og vælg derefter “Centreret” fra rullemenuen Vandret tekstjustering. Dette er en bedre mulighed end at flette celler, da det ikke ændrer gitterstrukturen i regnearket.
Evighedskalender med aktuel dato
For at oprette en evighedskalender, der opdateres automatisk baseret på den aktuelle dato, skal vi opdatere formlen for at generere den første dag i den aktuelle måned med det samme. Den første dag i den aktuelle måned kan beregnes med funktionen SLUT.PÅ.MÅNED på denne måde:
=SLUT.PÅ.MÅNED(IDAG();-1)+1
Du kan bruge denne formel direkte i celle J2, og kalenderen forbliver altid opdateret. For en alt-i-en-formel kan vi tilføje LET-funktionen som denne:
=LET(start; SLUT.PÅ.MÅNED(IDAG();-1)+1;SEKVENS(6;7;Start-VÆLG(UGEDAG(Start);0;1;2;3;4;5;6)))
Her bruger vi funktionen LET til at definere “Start” som den første dag i den aktuelle måned, hvorefter vi kører den oprindelige formel uændret. Den lokale variabel “Start” tilsidesætter den navngivne områdestart i regnearket, som kan slettes, hvis det ønskes.
Mandag
For at generere en kalender, der starter mandag i stedet for søndag, kan du bruge følgende kode inde i SEKVENS som startargument:
=Start-VÆLG(UGEDAG(Start);6;0;1;2;3;4;5)
Ved at bruge den samme logik forklaret ovenfor, ruller denne kode startdatoen tilbage efter behov for at begynde kalenderen på en mandag.
