Maak een kalender in Excel met één formule met behulp van een matrix die is ingevoerd.
Kijk eens naar deze figuur:
Die formule =Coolis dezelfde formule in elke cel van B5: H10! Kijken:
Het werd een array ingevoerd zodra B5: H10 voor het eerst was geselecteerd. In dit artikel zie je wat er achter de formule zit.
Overigens is er een cel die nog niet wordt weergegeven, de maand die moet worden weergegeven. Dat wil zeggen, cel J1 bevat =TODAY(), (en ik schrijf dit in december) maar als je het verandert in 5/8/2012, zou je zien:
Dit is mei 2012. OK, absoluut cool! Begin bij het begin en werk je omhoog naar deze formule in de kalender en kijk hoe het werkt.
Stel ook dat het vandaag 8 mei 2012 is.
Kijk eerst naar deze figuur:
De formule klopt niet echt. Het zou, als het omringd was door =SUM, maar u wilt zien wat er achter de formule zit, dus u breidt het uit door het te selecteren en op de F9-toets te drukken.
De bovenstaande afbeelding wordt de onderstaande afbeelding wanneer de F9-toets wordt ingedrukt.
Merk op dat er een puntkomma achter de 3 staat - dit geeft een nieuwe rij aan. Nieuwe kolommen worden weergegeven door een komma. Dus daar ga je je voordeel mee doen.
Het aantal weken in een maand varieert, maar geen kalender heeft meer dan zes rijen nodig om een maand weer te geven, en ze hebben natuurlijk allemaal zeven dagen. Kijk eens naar deze figuur:
Voer handmatig de waarden 1 tot 42 in B5: H10 in, en als u =B5:H10een cel invoert en vervolgens de formulebalk uitvouwt, ziet u wat hier wordt weergegeven:
Let op de plaatsing van de puntkomma's - na elk veelvoud van 7 - die een nieuwe rij aangeven. Dit is het begin van de formule, maar in plaats van zo'n lange formule kun je deze kortere formule gebruiken. Selecteer B5: H10. Type
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
als de formule, maar druk niet op Enter.
Om Excel te vertellen dat dit een matrixformule is, moet u Ctrl + Shift ingedrukt houden met uw linkerhand. Houd Ctrl + Shift ingedrukt en druk met uw rechterhand op Enter. Laat vervolgens Ctrl + Shift los. In de rest van dit artikel wordt deze reeks toetsaanslagen Ctrl + Shift + Enter genoemd.
Als u Ctrl + Shift + Enter correct hebt uitgevoerd, verschijnen accolades rond de formule in de formulebalk en verschijnen de nummers 1 tot 42 in B5: H10, zoals hier wordt weergegeven:
Merk op dat u de nummers 0 tot en met 5 gescheiden door puntkomma's (nieuwe rij voor elk) neemt en ze vermenigvuldigt met 7, waardoor u in feite het volgende krijgt:
De verticale oriëntatie van deze waarden opgeteld bij de horizontale oriëntatie van de waarden 1 tot en met 7 levert dezelfde waarden op als weergegeven. De uitbreiding hiervan is identiek aan wat je daarvoor had. Stel dat u nu TODAY aan deze nummers toevoegt?
Opmerking: het bewerken van een bestaande matrixformule is erg lastig. Volg deze stappen zorgvuldig: Selecteer B5: H10. Klik op in de formulebalk om de bestaande formule te bewerken. Typ + J1 maar druk niet op Enter. Om de bewerkte formule te accepteren, drukt u op Ctrl + Shift + Enter.
Het resultaat voor 8 mei 2012 is:
Deze nummers zijn serienummers (het aantal dagen sinds 1/1/1900). Als u deze opmaakt als korte datums:
Duidelijk niet goed, maar je komt er wel. Wat als u deze gewoon opmaakt als "d" voor de dag van de maand:
Het lijkt bijna een maand, maar geen enkele maand begint met de negende van de maand. Ah, hier is een probleem. Je hebt J1 gebruikt die 5/8/2012 bevat, en je moet echt de datum van de eerste van de maand gebruiken. Dus stel dat je =DATE(YEAR(J1),MONTH(J1),1)J2 invoert:
Cel J1 bevat 5/8/2012 en cel J2 verandert dat in de eerste van de maand van wat er in J1 is ingevoerd. Dus als je J1 in de formule van de kalender verandert in J2:
Dichterbij, maar nog steeds niet goed. Er is nog een aanpassing nodig, en dat is dat u de weekdag van de eerste dag moet aftrekken. Dat wil zeggen, cel J3 bevat =WEEKDAY(J2). 3 staat voor dinsdag. Dus als je nu J3 van deze formule aftrekt, krijg je:
En dat klopt eigenlijk voor mei 2012!
Oké, je bent heel dichtbij. Wat er nog steeds mis is, is dat de 29 en 30 van april verschijnen in de kalender van mei, en 1 juni tot en met 9 verschijnen ook. U moet deze wissen.
U kunt de formule een naam geven om deze gemakkelijker te kunnen raadplegen. Noem het "Cal" (nog niet "cool"). Zie deze figuur:
Vervolgens kunt u de formule wijzigen in =Cal(nog steeds Ctrl + Shift + Enter):
Nu kunt u de formule wijzigen om te lezen dat als het resultaat zich in rij 5 bevindt en het resultaat bijvoorbeeld meer dan 20 is, dat resultaat leeg moet zijn. Rij 5 bevat de eerste week van een maand, dus je zou nooit waarden boven de 20 moeten zien (of een getal boven de zeven zou verkeerd zijn - een getal als 29 dat je ziet in cel B5 van de bovenstaande afbeelding is van de vorige maand). U kunt dus gebruik maken van =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Merk allereerst op dat de cellen B5: D5 leeg zijn. De formule luidt nu "als dit rij 5 is, laat dan leeg zien als de DAG van het resultaat meer dan 20 is".
U kunt doorgaan met het verwijderen van de lage cijfers aan het einde - de waarden van volgende maand. Hier is hoe u dit gemakkelijk kunt doen.
Bewerk de formule en selecteer de laatste verwijzing naar "Cal"
Typ IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) om de laatste Cal te vervangen.
De uiteindelijke formule zou moeten zijn
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Druk op Ctrl + Shift + Enter. Het resultaat zou moeten zijn:
Er zijn nog twee dingen te doen. U kunt deze formule nemen en deze een naam geven, "Cool":
Gebruik dat dan in de hier getoonde formule:
Overigens worden gedefinieerde namen behandeld alsof ze in een array zijn ingevoerd.
Wat u nog moet doen, is de cellen opmaken en de dagen van de week en de naam van de maand invoeren. Dus je verbreedt de kolommen, vergroot de rijhoogte, vergroot de lettergrootte en lijnt de tekst uit:
Plaats vervolgens randen rond de cellen:
Voeg de maand en het jaar samen, centreer ze en formatteer deze:
Schakel vervolgens rasterlijnen uit en voila:
Dit gastartikel is van Excel MVP Bob Umlas. Het komt uit het boek Excel Outside the Box. Om de andere onderwerpen in het boek te zien, klik hier.
