Cum se creează un calendar în schimbare dinamică în Excel

în acest articol, vă vom arăta cum să creați un calendar în schimbare dinamică în Excel.

descărcați Registrul de lucru

populați variabilele

primul pas este crearea variabilelor care vor fi utilizate în formule. Aceste variabile trebuie să furnizeze informațiile actuale, astfel încât calendarul calendaristic să se poată actualiza automat. Iată lista variabilelor:

• data actuală
• numărul zilei actuale a lunii
• numărul lunii actuale
• Anul prezent
• data primei zile a lunii actuale
• numărul zilei actuale a săptămânii
• numărul zilei actuale a săptămânii
• numărul săptămânii actuale a anului

vom folosi funcțiile astăzi, Zi, Lună, An, dată, zi lucrătoare și săptămână. Pe scurt, în timp ce funcția TODAY returnează data funcțiilor prezent day, DAY, MONTH, YEAR, WEEKDAY și WEEKNUM analizează valorile respective ale datei. Funcția dată returnează valoarea dată a anului, lunii și Zilei date.

în următoarea captură de ecran, puteți vedea numele pe care le-am dat și formulele care au fost generate pe 20 Noiembrie 2020.

am numit fiecare variabilă pentru a ne ușura munca atunci când creăm formule. Următorul pas pentru a crea un calendar în Excel este crearea unui contur pentru interfața calendar.

schiță pentru Calendar în Excel

înainte de a crea formulele reale care generează zilele lunii, trebuie mai întâi să plasăm o schiță pentru a ajuta la crearea formulelor și, de asemenea, să oferim referință vizuală.

zilele vor fi plasate pe un tabel de 7 coloane și 6 rânduri. Trebuie să creștem aceste numere cu 2 și pentru titluri și celule ajutătoare. În timp ce titlurile coloanelor și rândurilor includ zile săptămânale pentru coloane și numere Săptămânale pe rânduri, celulele helper vor fi numere consecutive începând de la 1.

calendarul ar putea arăta mai jos.

utilizați margini sau culori de fundal după cum doriți. Punctul important este utilizarea unui număr de săptămână dinamic în titlu. Referiți funcția WEEKNUM pentru prima săptămână și adăugați 1 pentru fiecare săptămână consecutivă.

să continuăm să creăm un calendar în Excel prin popularea numerelor zilei.

zile generatoare

dacă utilizați un aspect similar și nu doriți să vă faceți griji cu privire la detaliile formulei, nu ezitați să copiați formula de mai jos. Apoi lipiți-o în celula din stânga sus din zona de zi a calendarului și populați celulele rămase. Celulele G2 și E4 se referă la primele celule ale coloanelor helper. Astfel, poate fi necesar să ajustați aceste referințe, cu excepția cazului în care prima celulă este G4.

acum, să aruncăm o privire mai atentă la formulă. În primul rând, formula utilizează funcția de dată, care returnează o dată după anul, luna și ziua date. Primele 2 argumente sunt furnizate de variabilele YearNumber și MonthNumber care au fost calculate deja.

partea de zi a formulei își propune să genereze numere de zile pe o bază 7, deoarece există 7 zile într-o săptămână. 7 * ($E4-1) parte a formulei verificați numărul săptămânii din coloana helper E și generează o bază, începând cu 0 și crescând cu 7 de fiecare dată.

putem mări sau micșora acest număr la fel de mult ca diferența dintre ziua săptămânii din prima zi a lunii cu numărul săptămânii zilei. Cu alte cuvinte, primim prima zi a lunii actuale ca punct de ancorare. De exemplu, este a 7-a zi a săptămânii (în baza de luni până duminică) pentru 1 Noiembrie 2020.

dacă săptămâna calendarului nostru presupune că luni este prima zi a săptămânii, luni ar trebui să fie cu 6 zile înainte (=1-7) prima zi a lunii. Astfel, obținem numere ca mai jos.

adăugați aceste valori la 1 pentru a schimba numerele. Deci, duminica poate deveni prima zi ca în exemplul următor.

ca rezultat, avem 2 matrice pentru 2 dimensiuni. Pentru a crea un tabel, trebuie să adăugăm ambele matrice împreună.

aceste numere reprezintă ziua lunii actuale. Există numere mai mici de 1 sau mai mari de 31, care, evident, nu pot fi zile ale lunii.

funcția DATE gestionează aceste tipuri de numere generând o dată în luna precedentă sau în următoarea. De exemplu, -2 devine cu două zile înainte de prima zi a lunii date.

în cele din urmă, dacă utilizați aceste numere în funcția de dată, puteți obține datele reale ale lunii curente.

formatarea

numere de zi

acum, să populăm zilele. Puteți modifica formatarea valorilor fără a modifica valoarea reală. Această caracteristică se numește formatarea numerelor. Urmați pașii pentru a ajusta formatarea numărului pentru a afișa doar zilele.

1. selectați oricare dintre datele din zona de zi
2. apăsați Ctrl + 1 pentru a deschide dialogul Format Cells
3. activați fila Număr dacă nu este deja deschisă
4. selectați Personalizat în lista de categorii
5. tastați litera d în caseta Tip
6. Faceți clic pe OK pentru a salva
   

acum, aspectul calendarului este terminat.

dacă doriți să eliminați padding pentru datele de o singură cifră, puteți utiliza dd în loc de un singur d. Pentru a afla mai multe despre formatarea numerelor: formatarea numerelor în Excel – tot ce trebuie să știți

evidențierea zilei actuale

în cele din urmă, vom folosi formatarea condiționată pentru a seta diferite culori pentru zilele care nu sunt în luna actuală și pentru a evidenția ziua actuală.

pentru a adăuga aceste opțiuni de formatare condiționată;

1. selectați intervalul de zile
2. Faceți clic pe pictograma Formatare condiționată de sub fila Acasă a panglicii
3. selectați regulă nouă
   
4. selectați Utilizați o formulă pentru a determina ce celule să formateze
5. introduceți o formulă pentru a defini regula pentru evidențierea zilei curente
   =G4=astăzi()
   G4: celula din stânga sus a intervalului include zile. Utilizați o referință relativă (nu utilizați $).
6. Faceți clic pe butonul Format din fereastra de previzualizare a dialogului Format Cells
   
7. în dialog, Selectați opțiunile de formatare pe care doriți să le vedeți în celula actuală
   am folosit un fundal portocaliu în acest exemplu.
8. Faceți clic pe OK pentru a aplica
   
9. caseta de previzualizare va afișa preferințele dvs.
10. Faceți clic pe OK pentru a aplica formatarea condiționată
    

acesta este modul în care arată:

formula returnează o valoare booleană. Dacă valoarea celulei (G4) este egală cu rezultatul funcției TODAY, care returnează ziua prezentă într-un format de dată, formula returnează TRUE. Altfel, fals. Dacă rezultatul este adevărat, Excel aplică formatarea în celulă.

există 2 puncte importante aici:

1. trebuie să utilizați o referință relativă, de ex.G4, cu excepția cazului în care nu doriți ca Excel să populeze referința de-a lungul intervalului. Luați în considerare modul în care se schimbă formulele atunci când le copiați în altă parte. Excel aplică același lucru și pentru formulele de formatare condiționată. Pentru mai multe informații, consultați: Cum se creează o referință absolută Excel și o referință relativă
2. celulele zilei trebuie să returneze valorile reale ale datei. Acesta este motivul pentru care folosim formatarea numerelor pentru a afișa porțiunile zilei.