Kalendar u Excelu s jednom formulom (uneseni niz, naravno!) - Savjeti za Excel

Stvorite kalendar u Excelu s jednom formulom pomoću formule unesene u niz.

Pogledajte ovu brojku:

Kalendar u Excelu - prosinac

Ta je formula =Cool,, ista formula u svakoj ćeliji od B5: H10! Izgled:

Osnovna formula kalendara

Upisan je u polje nakon što je prvi put odabran B5: H10. U ovom ćete članku vidjeti što stoji iza formule.

Usput, postoji ćelija koja još nije prikazana koji je mjesec za prikaz. Odnosno, stanica J1 sadrži =TODAY(), (i ovo pišem u prosincu), ali ako je promijenite u 8.5.2012, vidjet ćete:

Mjesec se promijenio u svibanj

Ovo je svibanj 2012. OK, definitivno cool! Počnite od početka i krenite do ove formule u kalendaru i pogledajte kako ona funkcionira.

Također, pretpostavimo da je danas 8. svibnja 2012.

Prvo pogledajte ovu brojku:

Uzorak formule

Formula zapravo nema smisla. Bilo bi, da je okružen =SUM, ali želite vidjeti što stoji iza formule, pa ćete ga proširiti odabirom i pritiskom na tipku F9.

Odaberite formulu

Gornja slika postaje tipkom dolje kada se pritisne tipka F9.

Što stoji iza formule

Primijetite da nakon tačke 3 stoji dvotačka - to ukazuje na novi red. Novi stupci predstavljaju se zarezom. Dakle, iskoristit ćete to.

Broj tjedana u mjesecu varira, ali nijednom kalendaru nije potrebno više od šest redaka da predstavlja bilo koji mjesec, i naravno, svi oni imaju sedam dana. Pogledajte ovu brojku:

Raspon kalendara

Ručno unesite vrijednosti od 1 do 42 u B5: H10, a ako unesete =B5:H10ćeliju, a zatim proširite traku s formulama, vidjet ćete što je ovdje prikazano:

Proširite formulu na traci s formulama

Obratite pozornost na mjesto zareza - nakon svakog višekratnika od 7 - označavajući novi redak. Ovo je početak formule, ali umjesto tako duge, možete koristiti ovu kraću formulu. Odaberite B5: H10. Tip

=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)

kao formulu, ali ne pritiskajte Enter.

Da biste Excelu rekli da je ovo formula niza, morate lijevom rukom pritisnuti Ctrl + Shift. Dok držite Ctrl + Shift, desnom tipkom pritisnite Enter. Zatim otpustite Ctrl + Shift. U ostatku ovog članka ovaj će se skup tipki nazivati ​​Ctrl + Shift + Enter.

Ako ste pravilno učinili Ctrl + Shift + Enter, kovrčave zagrade pojavit će se oko formule na traci s formulama, a brojevi od 1 do 42 pojavit će se u B5: H10, kao što je ovdje prikazano:

Kovrčaste zagrade oko formule

Primijetite da uzimate brojeve od 0 do 5 odvojene zarezom (novi red za svaki) i množite ih sa 7, efektivno dajući ovo:

Proširi više - indeks reda pomnožen sa 7

Vertikalna orijentacija ovih vrijednosti dodana vodoravnoj orijentaciji vrijednosti od 1 do 7 daje iste vrijednosti kao što je prikazano. Proširenje je identično onome što ste imali prije. Pretpostavimo da sada tim brojevima dodate DANAS?

Napomena: Uređivanje postojeće formule niza vrlo je nezgodno. Slijedite ove korake pažljivo: Odaberite B5: H10. Kliknite na traku s formulama da biste uredili postojeću formulu. Upišite + J1, ali nemojte pritiskati Enter. Da biste prihvatili uređenu formulu, pritisnite Ctrl + Shift + Enter.

Rezultat za 8. svibnja 2012. godine je:

Rezultat za 8. svibnja 2012

Ti su brojevi serijski brojevi (broj dana od 1/1/1900). Ako ih formatirate kao kratke datume:

Formatirani raspon

Jasno da nije u redu, ali stići ćete tamo. Što ako ih formatirate kao jednostavno "d" za dan u mjesecu:

Oblikujte kao 'dan' u mjesecu

Gotovo izgleda kao mjesec, ali niti jedan mjesec ne počinje s devetim u mjesecu. Ah, ovdje je jedan problem. Koristili ste J1 koji sadrži 8.5.2012. I doista trebate upotrijebiti datum prvog u mjesecu. Pa pretpostavimo da ste stavili =DATE(YEAR(J1),MONTH(J1),1)J2:

Datum prvog u mjesecu

Ćelija J1 sadrži 8.5.2012., A ćelija J2 to mijenja na prvo u mjesecu onoga što se unese u J1. Dakle, ako promijenite J1 u formuli kalendara u J2:

Promijenite osnovni datum kao prvi datum u mjesecu

Bliže, ali još uvijek nije u pravu. Potrebno je još jedno prilagođavanje, a to je da trebate oduzeti dan u danu prvog dana. Odnosno, stanica J3 sadrži =WEEKDAY(J2). 3 predstavlja utorak. Dakle, ako od ove formule oduzmete J3, dobit ćete:

Smjena za radnim danom

I to je zapravo točno za svibanj 2012!

Dobro, stvarno ste bliski. Ono što još uvijek nije u redu jest da se 29. i 30. travnja pojavljuju u kalendaru za svibanj, a pojavljuju se i od 1. do 9. lipnja. Morate to očistiti.

Formuli možete dati naziv radi lakšeg snalaženja. Nazovite ga "Cal" (još nije "cool"). Pogledajte ovu sliku:

Stvorite imenovanu formulu

Tada formulu možete promijeniti tako da jednostavno bude =Cal(i dalje Ctrl + Shift + Enter):

Promijenite formulu polja s imenovanom formulom

Sada možete promijeniti formulu tako da glasi: ako je rezultat u retku 5 i ako je rezultat preko 20, recimo, taj rezultat treba biti prazan. Redak 5 sadržavat će prvi tjedan u bilo kojem mjesecu, tako da nikada ne biste trebali vidjeti vrijednosti veće od 20 (ili bi bilo koji broj preko sedam bio pogrešan - broj poput 29 koji vidite u ćeliji B5 na gornjoj slici je iz prethodnog mjeseca). Tako možete koristiti =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):

Datumi prethodnog mjeseca

Prvo primijetite da su stanice B5: D5 prazne. Formula sada glasi "ako je ovo redak 5, ako je DAN rezultata veći od 20, prikaži prazno".

Možete nastaviti uklanjati male brojeve na kraju - vrijednosti sljedećeg mjeseca. Evo kako to lako učiniti.

Uredite formulu i odaberite konačnu referencu na "Cal"

Datumi sljedećeg mjeseca - 1

Počnite tipkati IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal) da biste zamijenili konačni Cal.

Datumi za sljedeći mjesec - 2

Konačna formula bi trebala biti

=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))

Pritisnite Ctrl + Shift + Enter. Rezultat bi trebao biti:

Rezultat-1

Preostale su dvije stvari. Možete uzeti ovu formulu i dati joj ime "Cool":

Formulu nazovite "Cool"

Zatim to upotrijebite u ovdje prikazanoj formuli:

Rezultat-2

Inače, definirana imena tretiraju se kao da su unesena u niz.

Preostalo je formatirati ćelije i unijeti dane u tjednu i naziv mjeseca. Dakle, proširite stupce, povećajte visinu retka, povećajte veličinu fonta i poravnajte tekst:

Formatirajte raspon

Zatim stavite obrube oko stanica:

Granice kalendara

Spojite i centrirajte mjesec i godinu i formatirajte ih:

Naziv mjeseca i godina

Zatim isključite rešetke i voila:

Konačni rezultat - kalendar

Ovaj gost članak je iz Excela MVP-a Bob Umlas. Iz knjige je, Excel izvan okvira. Da biste pogledali ostale teme u knjizi, kliknite ovdje.

Zanimljivi članci...