Hoe maak je een dynamisch veranderende agenda in Excel

In dit artikel laten we je zien hoe je een dynamisch veranderende agenda in Excel maakt.

Download werkmap

variabelen invullen

de eerste stap is het aanmaken van de variabelen die in de formules moeten worden gebruikt. Deze variabelen moeten de huidige informatie te verstrekken, zodat de kalender kalender automatisch kan worden bijgewerkt. Hier is de lijst van variabelen:

• huidige datum
• het aantal huidige dag van de maand
• het aantal huidige maand
• Huidig jaar
• de datum van de eerste dag van de huidige maand
• het aantal huidige dag van de week
• het aantal huidige week

we gebruiken de functies vandaag, Dag, Maand, Jaar, datum, weekdag en weeknum. In het kort, terwijl de functie vandaag de datum van de huidige dag, Dag, Maand, Jaar, weekdag en WEEKNUM functies ontleedt de respectieve datumwaarden. De datum functie retourneert de datumwaarde van het gegeven jaar, maand en dag.

in de volgende schermafbeelding kunt u de namen zien die we hebben gegeven en formules die zijn gegenereerd op 20 November 2020.

we hebben elke variabele een naam gegeven om onze taak gemakkelijker te maken bij het maken van formules. De volgende stap om een agenda in Excel te maken, is het maken van een overzicht voor de kalenderinterface.

omtrek voor de agenda in Excel

voordat we de werkelijke formules maken die de dagen van de maand genereren, moeten we eerst een omtrek plaatsen om formules te helpen maken en ook visuele referentie bieden.

de dagen worden in een tabel van 7 kolommen en 6 rijen geplaatst. We moeten deze getallen ook met 2 verhogen voor titels en hulpcellen. Terwijl de kolom-en rijtitels weekdagen bevatten voor kolommen en weeknummers per rij, zijn de helpercellen opeenvolgende nummers vanaf 1.

de kalender kan er hieronder uitzien.

gebruik randen of achtergrondkleuren zoals u wilt. Het belangrijkste punt is het gebruik van een dynamisch weeknummer in de titel. Refereer naar de WEEKNUM-functie voor de eerste week en voeg 1 toe voor elke opeenvolgende week.

Laten we doorgaan met het maken van een kalender in Excel door het invullen van de dag-nummers.

dagen

Als u een soortgelijke opmaak gebruikt en u zich geen zorgen wilt maken over de details van de formule, kunt u de onderstaande formule kopiëren. Plak het vervolgens in de cel linksboven op het daggebied van uw agenda en vul de resterende cellen in. De cellen G2 en E4 verwijzen naar de eerste cellen van de helperkolommen. Daarom moet u deze verwijzingen mogelijk aanpassen, tenzij de eerste cel G4 is.

laten we nu de formule van dichterbij bekijken. Ten eerste gebruikt de formule de datumfunctie, die een datum retourneert per gegeven jaar, maand en dag. De eerste 2 argumenten worden geleverd door de variabelen jaarnummer en maandnummer die reeds zijn berekend.

het dagdeel van de formule is bedoeld om dagnummers te genereren op een 7-basis, aangezien er 7 dagen in een week zijn. 7*($E4-1) deel van de formule controleert het weeknummer in de helperkolom E en genereert een basis, beginnend met 0 en elke keer oplopend met 7.

We kunnen dit aantal vergroten of verkleinen zo veel als het verschil tussen de dagen van de week van de eerste dag van de maand van de dag van de week. Met andere woorden, we krijgen de eerste dag van de huidige maand als ankerpunt. Bijvoorbeeld, het is de 7e dag van de week (in Maandag Tot Zondag basis) voor 1 November 2020.

als de week van onze kalender ervan uitgaat dat maandag de eerste dag van de week is, moet maandag 6 dagen voor (=1-7) de eerste dag van de maand zijn. Zo krijgen we nummers zoals hieronder.

Voeg deze waarden 1 tot en met shift de nummers. Dus, zondag kan de eerste dag worden, zoals in het volgende voorbeeld.

Als gevolg daarvan hebben we 2 series voor 2 dimensies. Om een tabel te maken, moeten we beide arrays samen toevoegen.

deze getallen vertegenwoordigen de dag van de huidige maand. Er zijn nummers kleiner dan 1 of groter dan 31, wat natuurlijk geen dagen van de maand kunnen zijn.

de functie DATE verwerkt deze typen getallen door een datum te genereren in de vorige maand of de volgende. Bijvoorbeeld, -2 wordt twee dagen voor de eerste dag van de gegeven maand.

ten slotte, als u deze getallen gebruikt in de functie Datum, kunt u de werkelijke datums van de huidige maand krijgen.

opmaak

Dagnummers

laten we nu de dagen invullen. U kunt de opmaak van de waarden wijzigen zonder de werkelijke waarde te wijzigen. Deze functie wordt nummeropmaak genoemd. Volg de stappen om de nummeropmaak aan te passen om alleen de dagen weer te geven.

1. selecteer een van de datums in het daggebied
2. druk op Ctrl + 1 om het dialoogvenster Cellen opmaken te openen
3. Activeer het tabblad Nummer als het nog niet geopend is
4. selecteer Aangepast in categorielijst
5. Typ in de letter d in het vak type
6. klik OK om op te slaan
   

nu is de agenda-indeling klaar.

Als u de opvulling voor datums met één cijfer wilt verwijderen, kunt u dd gebruiken in plaats van enkele d. Voor meer informatie over nummeropmaak: nummeropmaak in Excel – alles wat u moet weten

de huidige dag markeren

tenslotte gebruiken we Voorwaardelijke opmaak om verschillende kleuren in te stellen voor dagen die niet in de huidige maand zijn en de huidige dag te markeren.

om deze voorwaardelijke opmaakopties toe te voegen;

1. Selecteer de dag bereik
2. Klik op de Voorwaardelijke Opmaak pictogram onder het tabblad start van het Lint
3. Selecteer Nieuwe Regel
   
4. Selecteer een formule Gebruiken om te bepalen welke cellen te formatteren
5. Geef een formule voor het definiëren van de regel voor het markeren van de huidige dag
   =G4=VANDAAG()
   G4: Cel linksboven van het assortiment omvat dagen. Gebruik een relatieve referentie (gebruik geen $).
6. klik op de knop Formaat in het voorbeeldvenster van het dialoogvenster Cellen opmaken
   
7. selecteer in het dialoogvenster opmaakopties die u wilt zien in de huidige cel
   we gebruikten een oranje achtergrond in dit voorbeeld.
8. Klik op OK om
   
9. Het Preview venster toont uw voorkeuren
10. Klik op OK om het toepassen van voorwaardelijke opmaak
    

Dit is hoe het eruit ziet:

De formule retourneert een Boolean-waarde. Als de celwaarde (G4) gelijk is aan het resultaat van de functie vandaag, die de huidige dag in een datumnotatie retourneert, retourneert de formule waar. Anders vals. Als het resultaat waar is, past Excel de opmaak toe in de cel.

hier zijn 2 belangrijke punten:

1. u moet een relatieve referentie gebruiken, bijvoorbeeld G4, tenzij u niet wilt dat Excel de referentie langs het bereik vult. Overweeg hoe formules veranderen wanneer u ze elders kopieert. Excel past hetzelfde toe voor voorwaardelijke opmaakformules. Voor meer informatie, zie: Hoe maak je een Excel absolute referentie en relatieve referentie
2. de dagcellen moeten de werkelijke datumwaarden retourneren. Daarom gebruiken we nummeropmaak om de daggedeelten weer te geven.