Creați calendarul în Excel cu o formulă utilizând formula introdusă în matrice.
Uită-te la această cifră:
Această formulă =Cooleste aceeași formulă în fiecare celulă din B5: H10! Uite:
A fost introdus în matrice odată ce B5: H10 a fost selectat pentru prima dată. În acest articol veți vedea ce se află în spatele formulei.
Apropo, există o celulă care nu este afișată încă, care este luna de afișat. Adică, celula J1 conține =TODAY()(și scriu asta în decembrie), dar dacă o schimbați la 8.05.2012, veți vedea:
Este luna mai 2012. OK, cu siguranță mișto! Începeți de la început și mergeți până la această formulă în calendar și vedeți cum funcționează.
De asemenea, să presupunem că astăzi este 8 mai 2012.
În primul rând, uitați-vă la această cifră:
Formula nu prea are sens. Ar fi, dacă ar fi înconjurat de =SUM, dar doriți să vedeți ce se află în spatele formulei, așa că o veți extinde selectând-o și apăsând tasta F9.
Figura de mai sus devine figura de mai jos când este apăsată tasta F9.
Observați că există un punct și virgulă după 3 - aceasta indică un rând nou. Coloanele noi sunt reprezentate printr-o virgulă. Deci, veți profita de asta.
Numărul de săptămâni dintr-o lună variază, dar niciun calendar nu are nevoie de mai mult de șase rânduri pentru a reprezenta o lună și, desigur, toate au șapte zile. Uită-te la această cifră:
Introduceți manual valorile de la 1 la 42 în B5: H10 și, dacă intrați =B5:H10într-o celulă și apoi extindeți bara de formule, vedeți ceea ce este afișat aici:
Observați plasarea punctelor și virgulelor - după fiecare multiplu de 7 - indicând un rând nou. Acesta este începutul formulei, dar în loc de una atât de lungă, puteți utiliza această formulă mai scurtă. Selectați B5: H10. Tip
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
ca formulă, dar nu apăsați Enter.
Pentru a spune Excel că aceasta este o formulă matrice, trebuie să țineți apăsat Ctrl + Shift cu mâna stângă. În timp ce țineți apăsat Ctrl + Shift, apăsați Enter cu mâna dreaptă. Apoi, eliberați Ctrl + Shift. Pentru restul acestui articol, acest set de apăsări de taste va fi numit Ctrl + Shift + Enter.
Dacă ați făcut Ctrl + Shift + Enter corect, vor apărea acolade în jurul formulei din bara de formule și numerele de la 1 la 42 vor apărea în B5: H10 așa cum se arată aici:
Observați că luați numerele de la 0 la 5 separate prin punct și virgulă (rând nou pentru fiecare) și le înmulțiți cu 7, dând efectiv acest lucru:
Orientarea verticală a acestor valori adăugată la orientarea orizontală a valorilor de la 1 la 7 produce aceleași valori ca și cele arătate. Extinderea acestui lucru este identică cu ceea ce ați avut înainte. Să presupunem că acum adăugați AZI la aceste numere?
Notă: Editarea unei formule matrice existente este foarte dificilă. Urmați cu atenție acești pași: Selectați B5: H10. Faceți clic în bara de formule pentru a edita formula existentă. Tastați + J1, dar nu apăsați Enter. Pentru a accepta formula editată, apăsați Ctrl + Shift + Enter.
Rezultatul pentru 8 mai 2012 este:
Aceste numere sunt numere de serie (numărul de zile de la 1/1/1900). Dacă le formatați ca date scurte:
În mod clar nu este corect, dar vei ajunge acolo. Ce se întâmplă dacă le formatați ca „d” pur și simplu pentru ziua lunii:
Aproape arata ca o luna, dar nici o luna nu începe cu a noua a lunii. Ah, iată o problemă. Ați folosit J1 care conține 8.05.2012 și trebuie să utilizați cu adevărat data de prima lună. Deci, să presupunem că ați introdus =DATE(YEAR(J1),MONTH(J1),1)J2:
Celula J1 conține 08.08.2012 și celula J2 modifică acest lucru în prima lună a oricărui lucru introdus în J1. Deci, dacă schimbați J1 în formula calendarului în J2:
Mai aproape, dar totuși nu este corect. Este necesară o ajustare suplimentară, și anume trebuie să scădeți ziua săptămânii din prima zi. Adică, celula J3 conține =WEEKDAY(J2). 3 reprezintă marți. Deci, acum, dacă scazi J3 din această formulă, vei obține:
Și asta este de fapt potrivit pentru mai 2012!
Bine, ești foarte aproape. Ceea ce este încă în neregulă este că 29 și 30 din aprilie se afișează în calendarul mai, iar de la 1 până la 9 iunie apare și el. Trebuie să le ștergeți.
Puteți da formulei un nume pentru o referință mai ușoară. Numiți-l „Cal” (încă nu „mișto”). Vezi această cifră:
Apoi, puteți schimba formula pentru a fi pur și simplu =Cal(încă Ctrl + Shift + Enter):
Acum puteți schimba formula pentru a citi că dacă rezultatul este în rândul 5 și rezultatul este peste 20, să zicem, atunci acel rezultat ar trebui să fie gol. Rândul 5 va conține prima săptămână a oricărei luni, deci nu ar trebui să vedeți niciodată valori peste 20 (sau orice număr peste șapte ar fi greșit - un număr ca 29 pe care îl vedeți în celula B5 din figura de mai sus este din luna precedentă). Deci, puteți utiliza =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Mai întâi, observați că celulele B5: D5 sunt goale. Formula citește acum „dacă acesta este rândul 5, atunci dacă ZIUA rezultatului este peste 20, se afișează necompletat”.
Puteți continua să eliminați numerele mici la sfârșit - valorile de luna viitoare. Iată cum puteți face acest lucru cu ușurință.
Editați formula și selectați referința finală la „Cal”
Începeți să tastați IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) pentru a înlocui Cal final.
Formula finală ar trebui să fie
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Apăsați Ctrl + Shift + Enter. Rezultatul ar trebui să fie:
Au mai rămas două lucruri de făcut. Puteți lua această formulă și dați-i un nume „Cool”:
Apoi utilizați-l în formula prezentată aici:
Apropo, numele definite sunt tratate ca și cum ar fi introduse în matrice.
Ceea ce rămâne de făcut este să formatați celulele și să introduceți Zilele săptămânii și numele lunii. Deci, lărgiți coloanele, măriți înălțimea rândului, măriți dimensiunea fontului și aliniați textul:
Apoi puneți margini în jurul celulelor:
Combinați și centrați luna și anul și formatați-le:
Apoi opriți liniile de rețea și voila:
Acest articol invitat este de la Excel MVP Bob Umlas. Este din carte, Excel Outside the Box. Pentru a vedea celelalte subiecte din carte, faceți clic aici.
