Så här skapar du en dynamiskt föränderlig kalender i Excel

i den här artikeln kommer vi att visa dig hur du skapar en dynamiskt föränderlig kalender i Excel.

ladda ner arbetsbok

fyll i variabler

första steget är att skapa de variabler som ska användas i formlerna. Dessa variabler måste tillhandahålla dagens information så att kalenderkalendern kan uppdateras automatiskt. Här är listan över variabler:

• nuvarande datum
• antalet nuvarande dag i månaden
• antalet nuvarande månad
• nuvarande år
• datumet för den första dagen i nuvarande månad
• antalet nuvarande dag i veckan
• numret på årets nuvarande vecka

vi kommer att använda funktionerna idag, Dag, Månad, År, datum, veckodag och weeknum. Kortfattat, medan idag funktionen returnerar Datum för nutid, dag, månad, år, veckodag och WEEKNUM funktioner tolka respektive datumvärden. Funktionen datum returnerar datumvärdet för det angivna året, månaden och dagen.

i följande skärmdump kan du se namnen vi har gett och formler som genererades den 20 November 2020.

vi namngav varje variabel för att göra vårt jobb enklare när vi skapar formler. Nästa steg för att skapa en kalender i Excel är att skapa en kontur för kalendergränssnittet.

disposition för kalendern i Excel

innan vi skapar de faktiska formlerna som genererar månadens dagar måste vi först placera en disposition för att hjälpa till att skapa formler och även ge visuell referens.

dagarna kommer att placeras på ett bord med 7 kolumner och 6 rader. Vi måste öka dessa siffror med 2 för titlar och hjälparceller också. Medan kolumnrubrikerna och radrubrikerna innehåller veckodagar för kolumner och veckonummer efter rader, kommer hjälpcellerna att vara på varandra följande nummer från och med 1.

kalendern kan se ut nedan.

använd gränser eller bakgrundsfärger som du vill. Den viktiga punkten är att använda ett dynamiskt veckonummer i titeln. Referera till WEEKNUM-funktionen för den första veckan och Lägg till 1 för varje vecka i följd.

Låt oss fortsätta skapa en kalender i Excel genom att fylla i dagnumren.

genererande dagar

om du använder en liknande layout och inte vill oroa dig för detaljerna i formeln, kopiera gärna formeln nedan. Klistra sedan in den i den övre vänstra cellen i kalenderns dagområde och fyll i de återstående cellerna. G2-och E4-cellerna hänvisar till de första cellerna i hjälpkolumnerna. Således kan du behöva justera dessa referenser, såvida inte den första cellen är G4.

låt oss nu titta närmare på formeln. Först använder formeln funktionen datum, som returnerar ett datum med det angivna året, månaden och dagen. De första 2 argumenten levereras av variablerna årtal och Månadtal som redan har beräknats.

dagdelen av formeln syftar till att generera dagnummer på en 7-bas, eftersom det finns 7 dagar i veckan. 7 * ($E4-1) en del av formeln kontrollera veckonumret i hjälparkolumnen E och genererar en bas, som börjar med 0 och ökar med 7 varje gång.

vi kan öka eller minska detta antal så mycket som skillnaden mellan veckodag den första dagen i månaden med dagens veckonummer. Med andra ord får vi den första dagen i den nuvarande månaden som en ankarpunkt. Till exempel är det den 7: e dagen i veckan (i måndag till söndag bas) för 1 November 2020.

om vår kalender vecka antar att måndag är den första dagen i veckan, måndag bör vara 6 dagen före (=1-7) den första dagen i månaden. Således får vi siffror som nedan.

Lägg till dessa värden till 1 för att flytta siffrorna. Så söndag kan bli den första dagen som i följande exempel.

som ett resultat har vi 2 arrays för 2 dimensioner. För att skapa ett bord måste vi lägga till båda arraysna tillsammans.

dessa siffror representerar dagen för den aktuella månaden. Det finns siffror mindre än 1 eller större än 31, vilket uppenbarligen inte kan vara dagar i månaden.

DATUMFUNKTIONEN hanterar dessa typer av nummer genom att generera ett datum i föregående månad eller nästa. Till exempel blir -2 två dagar före den angivna månadens första dag.

slutligen, om du använder dessa siffror i DATUMFUNKTIONEN, kan du få de faktiska datumen för den aktuella månaden.

formatering

dagnummer

låt oss nu fylla i dagarna. Du kan ändra formateringen av värdena utan att ändra det faktiska värdet. Den här funktionen kallas nummerformatering. Följ stegen för att justera nummerformateringen så att den bara visar dagarna.

1. Välj alla datum i dagområdet
2. tryck Ctrl + 1 för att öppna dialogrutan Formatera celler
3. aktivera fliken Nummer om den inte redan är öppen
4. Välj anpassad i kategorilistan
5. skriv in bokstaven d i rutan Typ
6. klicka på OK för att spara
   

nu är kalenderlayouten klar.

om du vill ta bort stoppningen för ensiffriga datum, du kan använda dd istället för enkel d. För att lära dig mer om talformatering: talformatering i Excel – allt du behöver veta

markera nutiden

slutligen kommer vi att använda villkorlig formatering för att ställa in olika färger för dagar som inte är i den aktuella månaden och markera nutiden.

för att lägga till dessa villkorsstyrda formateringsalternativ;

1. Välj dagsintervallet
2. klicka på ikonen för villkorlig formatering under fliken Hem i menyfliksområdet
3. Välj Ny regel
   
4. välj Använd en formel för att bestämma vilka celler som ska formateras
5. ange en formel för att definiera regeln för att markera den aktuella dagen
   =G4 = idag()
   G4: övre vänstra cellen i intervallet inkluderar dagar. Använd en relativ referens (använd inte $).
6. klicka på knappen Format i förhandsgranskningsfönstret i dialogrutan Formatera celler
   
7. i dialogrutan väljer du formateringsalternativ som du vill se i dagens cell
   vi använde en orange bakgrund i det här exemplet.
8. klicka på OK för att tillämpa
   
9. förhandsgranskningsrutan visar dina inställningar
10. klicka på OK för att tillämpa villkorlig formatering
    

så här ser det ut:

formeln returnerar ett booleskt värde. Om cellvärdet (G4) är lika med resultatet av funktionen idag, som returnerar nutiden i ett datumformat, returnerar formeln sant. Annars falskt. Om resultatet är sant tillämpar Excel formateringen i cellen.

det finns 2 viktiga punkter här:

1. du måste använda en relativ referens, t.ex. G4, om du inte vill att Excel ska fylla referensen längs intervallet. Tänk på hur formler ändras när du kopierar dem någon annanstans. Excel gäller samma för villkorliga formateringsformler också. För mer information, se: Så här skapar du en Excel-absolut referens och relativ referens
2. dagcellerna måste returnera de faktiska datumvärdena. Det är därför vi använder nummerformatering för att visa dagdelarna.