Opret kalender i Excel med en formel ved hjælp af en array-indtastet formel.
Se på denne figur:
Denne formel =Cooler den samme formel i hver celle fra B5: H10! Se:
Det blev array-indtastet, når B5: H10 først blev valgt. I denne artikel vil du se, hvad der ligger bag formlen.
Forresten er der en celle, som endnu ikke vises, hvilket er den måned, der skal vises. Det vil sige, celle J1 indeholder =TODAY(), (og jeg skriver dette i december), men hvis du ændrer den til 5/8/2012, vil du se:
Dette er maj 2012. OK, helt sikkert sejt! Start fra begyndelsen, og arbejd dig op til denne formel i kalenderen og se, hvordan den fungerer.
Antag også, at i dag er 8. maj 2012.
Se først på dette tal:
Formlen giver ikke rigtig mening. Det ville, hvis det var omgivet af =SUM, men du vil se, hvad der ligger bag formlen, så du udvider det ved at vælge det og trykke på F9-tasten.
Figuren ovenfor bliver figuren nedenfor, når der trykkes på F9-tasten.
Bemærk, at der er en semikolon efter 3 - dette indikerer en ny række. Nye kolonner er repræsenteret med et komma. Så du vil drage fordel af det.
Antallet af uger i en måned varierer, men ingen kalendere har brug for mere end seks rækker for at repræsentere en måned, og selvfølgelig har de alle syv dage. Se på denne figur:
Indtast manuelt værdierne 1 til 42 i B5: H10, og hvis du indtaster =B5:H10en celle og derefter udvider formellinjen, ser du, hvad der vises her:
Bemærk placeringen af semikolonerne - efter hvert multiplum af 7 - hvilket indikerer en ny række. Dette er starten på formlen, men i stedet for en så lang en kan du bruge denne kortere formel. Vælg B5: H10. Type
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
som formlen, men tryk ikke på Enter.
For at fortælle Excel, at dette er en matrixformel, skal du holde Ctrl + Shift nede med din venstre hånd. Mens du holder Ctrl + Shift nede, skal du trykke på Enter med din højre hånd. Slip derefter Ctrl + Shift. I resten af denne artikel kaldes dette sæt tastetryk Ctrl + Shift + Enter.
Hvis du gjorde Ctrl + Shift + Enter korrekt, vises krøllede seler omkring formlen i formellinjen, og tallene 1 til 42 vises i B5: H10 som vist her:
Bemærk, at du tager tallene 0 til 5 adskilt af semikolon (ny række for hver) og ganger dem med 7, hvilket effektivt giver dette:
Den lodrette orientering af disse værdier tilføjet til den vandrette orientering af værdierne 1 til 7 giver de samme værdier som vist. Udvidelsen af dette er identisk med det, du havde før. Antag nu at du tilføjer I DAG til disse numre?
Bemærk: Det er meget vanskeligt at redigere en eksisterende matrixformel. Følg disse trin forsigtigt: Vælg B5: H10. Klik i formelbjælken for at redigere den eksisterende formel. Skriv + J1, men tryk ikke på Enter. For at acceptere den redigerede formel skal du trykke på Ctrl + Shift + Enter.
Resultatet for 8. maj 2012 er:
Disse numre er serienumre (antallet af dage siden 1/1/1900). Hvis du formaterer disse som korte datoer:
Klart ikke rigtigt, men du kommer derhen. Hvad hvis du formaterer disse som blot "d" for månedsdagen:
Næsten ligner en måned, men ingen måneder starter med den niende i måneden. Ah, her er et problem. Du brugte J1, som indeholder 5/8/2012, og du skal virkelig bruge datoen for den første i måneden. Så antag, at du satte =DATE(YEAR(J1),MONTH(J1),1)J2 i:
Celle J1 indeholder 5/8/2012, og celle J2 ændrer det til den første i måneden for det, der er angivet i J1. Så hvis du ændrer J1 i formlen i kalenderen til J2:
Tættere, men stadig ikke rigtigt. En yderligere justering er nødvendig, og det er, at du skal trække ugedagen på den første dag. Det vil sige, celle J3 indeholder =WEEKDAY(J2). 3 repræsenterer tirsdag. Så nu, hvis du trækker J3 fra denne formel, får du:
Og det er faktisk rigtigt i maj 2012!
Okay, du er virkelig tæt på. Hvad der stadig er galt, er, at 29. og 30. fra april vises i maj-kalenderen, og 1. juni til 9. vises også. Du skal rydde disse.
Du kan give formlen et navn til lettere reference. Kald det "Cal" (ikke "cool" endnu). Se denne figur:
Derefter kan du ændre formlen til simpelthen at være =Cal(stadig Ctrl + Skift + Enter):
Nu kan du ændre formlen for at læse, at hvis resultatet er i række 5, og resultatet er over 20, siger, så skal resultatet være tomt. Række 5 indeholder den første uge i enhver måned, så du bør aldrig se nogen værdier over 20 (eller noget nummer over syv ville være forkert - et tal som 29, som du ser i celle B5 i figuren ovenfor, er fra den foregående måned). Så du kan bruge =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Bemærk først, at celler B5: D5 er tomme. Formlen lyder nu "hvis dette er række 5, så hvis resultatets DAG er over 20, skal du vise tomt".
Du kan fortsætte med at fjerne de lave tal i slutningen - næste måneds værdier. Sådan gør du det let.
Rediger formlen, og vælg den endelige reference til "Cal"
Begynd at skrive IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) for at erstatte den endelige Cal.
Den endelige formel skal være
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Tryk på Ctrl + Skift + Enter. Resultatet skal være:
To ting tilbage at gøre. Du kan tage denne formel og give den et navn "Cool":
Brug derefter det i formlen vist her:
Forresten behandles definerede navne som om de er indtastet i array.
Hvad der er tilbage at gøre er at formatere cellerne og indsætte ugedagene og månedens navn. Så du udvider kolonnerne, øger rækkehøjden, øger skriftstørrelsen og justerer teksten:
Sæt derefter kanter omkring cellerne:
Flet og centrer måned og år, og formater det:
Deaktiver derefter gitterlinjer, og voila:
Denne gæsteartikel er fra Excel MVP Bob Umlas. Det er fra bogen, Excel uden for boksen. Klik her for at se de andre emner i bogen.
