Skapa kalender i Excel med en formel med hjälp av matrisinmatad formel.
Titta på den här siffran:
Den formeln =Coolär samma formel i varje cell från B5: H10! Se:
Det matrisinmatades när B5: H10 valdes först. I den här artikeln kommer du att se vad som ligger bakom formeln.
Förresten, det finns en cell som ännu inte visas vilken månad som ska visas. Det vill säga, cell J1 innehåller =TODAY(), (och jag skriver detta i december), men om du ändrar det till 2012-05-08, skulle du se:
Det här är maj 2012. OK, definitivt coolt! Börja från början och arbeta dig upp till denna formel i kalendern och se hur den fungerar.
Antag också att idag är 8 maj 2012.
Titta först på den här siffran:
Formeln är inte riktigt vettig. Det skulle, om det var omgivet av =SUM, men du vill se vad som ligger bakom formeln, så du kommer att utöka det genom att välja det och trycka på F9-tangenten.
Bilden ovan blir bilden nedan när du trycker på F9-tangenten.
Lägg märke till att det finns en semikolon efter 3 - detta indikerar en ny rad. Nya kolumner representeras av ett komma. Så du kommer att dra nytta av det.
Antalet veckor i en månad varierar, men ingen kalender behöver mer än sex rader för att representera någon månad, och de har naturligtvis alla sju dagar. Titta på den här siffran:
Ange värdena 1 till 42 manuellt i B5: H10, och om du anger =B5:H10i en cell och sedan expanderar formelfältet ser du vad som visas här:
Lägg märke till placeringen av semikolon - efter varje multipel av 7 - vilket indikerar en ny rad. Det här är början på formeln, men istället för en så lång kan du använda den kortare formeln. Välj B5: H10. Typ
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
som formel, men tryck inte på Enter.
För att berätta för Excel att detta är en matrisformel måste du hålla ned Ctrl + Skift med vänster hand. Håll Ctrl + Shift intryckt och tryck på Enter med din högra hand. Släpp sedan Ctrl + Skift. För resten av den här artikeln kommer denna uppsättning tangenttryckningar att kallas Ctrl + Skift + Enter.
Om du gjorde Ctrl + Shift + Enter korrekt visas lockiga hakparenteser runt formeln i formelfältet och siffrorna 1 till 42 visas i B5: H10 som visas här:
Observera att du tar siffrorna 0 till 5 åtskilda av semikolon (ny rad för varje) och multiplicerar dem med 7, vilket ger detta:
Den vertikala orienteringen av dessa värden som läggs till den horisontella orienteringen av värdena 1 till 7 ger samma värden som visas. Expansionen av detta är identisk med vad du hade tidigare. Anta att du nu lägger till idag i dessa siffror?
Obs! Det är mycket svårt att redigera en befintlig matrisformel. Följ dessa steg noggrant: Välj B5: H10. Klicka i formelfältet för att redigera den befintliga formeln. Skriv + J1 men tryck inte på Enter. För att acceptera den redigerade formeln, tryck Ctrl + Skift + Enter.
Resultatet för 8 maj 2012 är:
Dessa nummer är serienummer (antalet dagar sedan 1/1/1900). Om du formaterar dessa som korta datum:
Klart inte rätt, men du kommer dit. Vad händer om du formaterar dessa som helt enkelt "d" för månadsdagen:
Ser nästan ut som en månad, men ingen månad börjar med den nionde i månaden. Ah, här är ett problem. Du använde J1 som innehåller 2012-05-08 och du måste verkligen använda datumet för den första i månaden. Så antar att du lägger =DATE(YEAR(J1),MONTH(J1),1)in J2:
Cell J1 innehåller 2012-8-8 och cell J2 ändrar det till den första i månaden av det som anges i J1. Så om du ändrar J1 i kalenderns formel till J2:
Närmare, men fortfarande inte rätt. Ytterligare en justering behövs, och det är att du måste subtrahera veckodagen den första dagen. Det vill säga cell J3 innehåller =WEEKDAY(J2). 3 representerar tisdag. Så nu om du subtraherar J3 från den här formeln får du:
Och det är faktiskt rätt för maj 2012!
Okej, du är riktigt nära. Vad som fortfarande är fel är att 29 och 30 från april dyker upp i maj-kalendern, och 1 juni till 9 visas också. Du måste rensa dessa.
Du kan ge formeln ett namn för enklare referens. Kalla det "Cal" (inte "cool" än). Se den här figuren:
Då kan du ändra formeln till att helt enkelt vara =Cal(fortfarande Ctrl + Skift + Enter):
Nu kan du ändra formeln för att läsa att om resultatet är i rad 5 och resultatet är över 20, säg, då ska resultatet vara tomt. Rad 5 kommer att innehålla den första veckan i vilken månad som helst, så du bör aldrig se några värden över 20 (eller något nummer över sju skulle vara fel - ett nummer som 29 som du ser i cell B5 i figuren ovan är från föregående månad). Så du kan använda =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Lägg först märke till att cellerna B5: D5 är tomma. Formeln lyder nu "om detta är rad 5, så om DAGEN för resultatet är över 20, visa tomt".
Du kan fortsätta att ta bort de låga siffrorna i slutet - nästa månads värden. Så här gör du det enkelt.
Redigera formeln och välj den slutliga referensen till "Cal"
Börja skriva IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) för att ersätta den sista kal.
Den slutliga formeln bör vara
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Tryck på Ctrl + Skift + Enter. Resultatet bör vara:
Två saker kvar att göra. Du kan ta den här formeln och ge den ett namn, "Cool":
Använd sedan det i formeln som visas här:
Förresten behandlas definierade namn som om de matats in.
Vad som återstår att göra är att formatera cellerna och lägga in veckodagarna och månadens namn. Så du utvidgar kolumnerna, ökar radhöjden, ökar teckenstorleken och justerar texten:
Sätt sedan kantar runt cellerna:
Slå samman och centrera månaden och året och formatera det:
Stäng sedan av rutnät och voila:
Den här gästartikeln kommer från Excel MVP Bob Umlas. Det är från boken, Excel utanför lådan. Klicka här för att se de andra ämnena i boken.
