Lag kalender i Excel med en formel ved å bruke formel som er angitt i matrisen.
Se på denne figuren:
Den formelen, =Cooler den samme formelen i hver celle fra B5: H10! Se:
Den ble matrikulert når B5: H10 først ble valgt. I denne artikkelen vil du se hva som ligger bak formelen.
Forresten, det er en celle som ikke vises enda som er måneden som skal vises. Det vil si at celle J1 inneholder =TODAY()(og jeg skriver dette i desember), men hvis du endrer den til 5/8/2012, vil du se:
Dette er mai 2012. OK, absolutt kult! Start fra begynnelsen, og arbeid deg opp til denne formelen i kalenderen og se hvordan den fungerer.
Anta også at i dag er 8. mai 2012.
Se først på denne figuren:
Formelen gir egentlig ikke mening. Hvis den var omgitt av =SUM, men du vil se hva som ligger bak formelen, vil du utvide den ved å velge den og trykke på F9-tasten.
Figuren over blir figuren nedenfor når du trykker på F9-tasten.
Legg merke til at det er en semikolon etter 3 - dette indikerer en ny rad. Nye kolonner er representert med komma. Så du kommer til å dra nytte av det.
Antall uker i en måned varierer, men ingen kalendere trenger mer enn seks rader for å representere en måned, og selvfølgelig har de alle syv dager. Se på denne figuren:
Skriv inn verdiene 1 til 42 manuelt i B5: H10, og hvis du skriver =B5:H10inn en celle og deretter utvider formellinjen, ser du hva som vises her:
Legg merke til plasseringen av semikolonene - etter hvert multiplum av 7 - som indikerer en ny rad. Dette er starten på formelen, men i stedet for en så lang, kan du bruke denne kortere formelen. Velg B5: H10. Type
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
som formel, men ikke trykk Enter.
For å fortelle Excel at dette er en matriseformel, må du holde Ctrl + Shift nede med venstre hånd. Mens du holder Ctrl + Shift, trykker du Enter med høyre hånd. Slipp deretter Ctrl + Shift. For resten av denne artikkelen vil dette settet med tastetrykk kalles Ctrl + Shift + Enter.
Hvis du gjorde Ctrl + Shift + Enter riktig, vil krøllete bukseseler vises rundt formelen i formellinjen, og tallene 1 til 42 vil vises i B5: H10 som vist her:
Legg merke til at du tar tallene 0 til 5 atskilt med semikolon (ny rad for hver) og multipliserer dem med 7, og effektivt gir dette:
Den vertikale orienteringen av disse verdiene lagt til den horisontale retningen av verdiene 1 til 7 gir de samme verdiene som vist. Utvidelsen av dette er identisk med det du hadde før. Anta at du nå legger til I DAG i disse tallene?
Merk: Det er veldig vanskelig å redigere en eksisterende matriseformel. Følg disse trinnene nøye: Velg B5: H10. Klikk i formellinjen for å redigere den eksisterende formelen. Skriv + J1, men ikke trykk Enter. For å godta den redigerte formelen, trykk Ctrl + Shift + Enter.
Resultatet for 8. mai 2012 er:
Disse tallene er serienumre (antall dager siden 1/1/1900). Hvis du formaterer disse som korte datoer:
Klart ikke riktig, men du kommer dit. Hva om du formaterer disse som bare "d" for månedsdagen:
Ser nesten ut som en måned, men ingen måneder starter med den niende i måneden. Ah, her er ett problem. Du brukte J1 som inneholder 5/8/2012, og du må virkelig bruke datoen for den første i måneden. Så antar at du legger =DATE(YEAR(J1),MONTH(J1),1)inn J2:
Cell J1 inneholder 5/8/2012 og celle J2 endrer det til den første i måneden av det som er angitt i J1. Så hvis du endrer J1 i formelen til kalenderen til J2:
Nærmere, men fortsatt ikke riktig. En ytterligere justering er nødvendig, og det er at du trenger å trekke ukedagen den første dagen. Det vil si at celle J3 inneholder =WEEKDAY(J2). 3 representerer tirsdag. Så hvis du trekker J3 fra denne formelen, får du:
Og det er faktisk riktig for mai 2012!
Ok, du er virkelig nær. Det som fremdeles er galt er at 29. og 30. fra april dukker opp i mai-kalenderen, og 1. juni til og med 9. dukker også opp. Du må fjerne disse.
Du kan gi formelen et navn for lettere referanse. Kall det "Cal" (ikke "kult" ennå). Se denne figuren:
Deretter kan du endre formelen til å være =Cal(fortsatt Ctrl + Shift + Enter):
Nå kan du endre formelen for å lese at hvis resultatet er i rad 5 og resultatet er over 20, si at resultatet skal være tomt. Rad 5 vil inneholde den første uken i en måned, så du bør aldri se noen verdier over 20 (eller noe over syv vil være galt - et tall som 29 som du ser i celle B5 i figuren ovenfor er fra forrige måned). Så du kan bruke =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Legg først merke til at celler B5: D5 er tomme. Formelen lyder nå "Hvis dette er rad 5, så hvis DAGEN for resultatet er over 20, vis blank".
Du kan fortsette å fjerne de lave tallene på slutten - neste måneds verdier. Slik gjør du dette enkelt.
Rediger formelen og velg den endelige referansen til "Cal"
Begynn å skrive IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) for å erstatte den endelige Cal.
Den endelige formelen skal være
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Trykk Ctrl + Shift + Enter. Resultatet skal være:
To ting igjen å gjøre. Du kan ta denne formelen og gi den navnet "Cool":
Bruk det deretter i formelen vist her:
Forresten blir definerte navn behandlet som om de er matriksoppgitt.
Det som gjenstår å gjøre er å formatere cellene og legge inn ukedagene og navnet på måneden. Så du utvider kolonnene, øker radhøyden, øker skriftstørrelsen og justerer teksten:
Sett deretter rammer rundt cellene:
Slå sammen og sentrer måneden og året og formater det:
Slå deretter av rutenettlinjer, og voila:
Denne gjesteartikkelen er fra Excel MVP Bob Umlas. Det er fra boken, Excel utenfor boksen. For å se de andre emnene i boka, klikk her.
