Ustvarite koledar v Excelu z eno formulo z uporabo vnesene matrične formule.
Poglejte to sliko:
Ta formula,, =Coolje enaka formuli v vsaki celici od B5: H10! Poglej:
Ko je bil prvič izbran B5: H10, je bil vnesen v matriko. V tem članku boste videli, kaj je v ozadju formule.
Mimogrede, obstaja celica, ki še ni prikazana, kateri mesec naj bo prikazan. To pomeni, da celica J1 vsebuje =TODAY()(in to pišem decembra), če pa jo spremenite v 8.5.2012, boste videli:
To je maj 2012. OK, vsekakor kul! Začnite od začetka in se v koledarju pomaknite do te formule in si oglejte, kako deluje.
Predpostavimo tudi, da je danes 8. maj 2012.
Najprej si oglejte to sliko:
Formula v resnici nima smisla. Bi, če bi bil obkrožen z =SUM, vendar želite videti, kaj stoji za formulo, zato jo boste razširili tako, da jo izberete in pritisnete tipko F9.
Zgornja slika ob pritisku tipke F9 postane spodnja slika.
Upoštevajte, da je po 3 podpičje - to pomeni novo vrstico. Novi stolpci so predstavljeni z vejico. Torej boste to izkoristili.
Število tednov v mesecu se spreminja, vendar noben koledar ne potrebuje več kot šest vrstic za predstavitev katerega koli meseca, seveda pa imajo vsi sedem dni. Poglejte to sliko:
Ročno vnesite vrednosti od 1 do 42 v B5: H10 in če vnesete =B5:H10celico in nato razširite vrstico s formulami, vidite, kaj je prikazano tukaj:
Upoštevajte postavitev podpičja - za vsakim večkratnikom 7 -, ki označuje novo vrstico. To je začetek formule, vendar namesto tako dolge lahko uporabite to krajšo formulo. Izberite B5: H10. Tip
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
kot formulo, vendar ne pritiskajte Enter.
Če želite Excelu povedati, da je to matrična formula, morate z levo roko pritisniti Ctrl + Shift. Medtem ko držite Ctrl + Shift, z desno roko pritisnite Enter. Nato spustite Ctrl + Shift. V nadaljevanju tega članka se bo ta niz pritiskov tipk imenoval Ctrl + Shift + Enter.
Če ste pravilno storili Ctrl + Shift + Enter, se bodo okrog formule v vrstici s formulami pojavile skodrane oklepaje, številke od 1 do 42 pa bodo prikazane v B5: H10, kot je prikazano tukaj:
Upoštevajte, da jemljete številke od 0 do 5, ločene s podpičji (nova vrstica za vsako) in jih pomnožite s 7, kar dejansko pomeni:
Navpična usmerjenost teh vrednosti, dodana vodoravni usmeritvi vrednosti od 1 do 7, daje enake vrednosti, kot je prikazano. Razširitev tega je enaka kot prej. Recimo, da zdaj tem številkam dodate DANES?
Opomba: Urejanje obstoječe formule matrike je zelo zapleteno. Previdno sledite tem korakom: Izberite B5: H10. Kliknite vrstico s formulami, če želite urediti obstoječo formulo. Vnesite + J1, vendar ne pritiskajte Enter. Če želite sprejeti urejeno formulo, pritisnite Ctrl + Shift + Enter.
Rezultat za 8. maj 2012 je:
Te številke so serijske številke (število dni od 1. 1. 1900). Če jih oblikujete kot kratke datume:
Jasno je, da ni v redu, vendar boste prišli tja. Kaj pa, če jih za dan v mesecu oblikujete preprosto kot "d":
Skoraj videti kot mesec, vendar se noben mesec ne začne z devetim v mesecu. Ah, tukaj je en problem. Uporabili ste J1, ki vsebuje 8. 5. 2012, in res morate uporabiti datum prvega v mesecu. Recimo, da vstavite =DATE(YEAR(J1),MONTH(J1),1)J2:
Celica J1 vsebuje 8. 5. 2012, celica J2 pa to spremeni na prvi v mesecu, kar koli je vneseno v J1. Torej, če spremenite J1 v formuli koledarja v J2:
Bliže, a vseeno ne prav. Potrebna je še ena prilagoditev, to je, da morate prvi dan odšteti delovni dan. To pomeni, da celica J3 vsebuje =WEEKDAY(J2). 3 predstavlja torek. Torej, če od te formule odštejemo J3, dobimo:
In to pravzaprav drži za maj 2012!
V redu, zelo blizu si. Še vedno je narobe 29. in 30. april, ki je prikazan v majskem koledarju, od 1. do 9. junija pa tudi. Te morate počistiti.
Formuli lahko date ime za lažje sklicevanje. Pokličite ga "Cal" (še ne "kul"). Glej to sliko:
Nato lahko formulo spremenite v preprosto =Cal(še vedno Ctrl + Shift + Enter):
Zdaj lahko spremenite formulo tako, da se glasi: če je rezultat v vrstici 5 in je rezultat več kot 20, recimo, mora biti ta rezultat prazen. Vrstica 5 bo vsebovala prvi teden v katerem koli mesecu, zato nikoli ne smete videti vrednosti nad 20 (ali katero koli število nad sedem bi bilo napačno - število, kot je 29, ki ga vidite v celici B5 na zgornji sliki, je iz prejšnjega meseca). Tako lahko uporabite =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Najprej opazite, da so celice B5: D5 prazne. Formula se zdaj glasi "če je to vrstica 5, če je DAN rezultata več kot 20, pokaži prazno".
Nizke številke lahko še naprej odstranjujete na koncu - vrednosti naslednjega meseca. Tukaj je opisano, kako to enostavno narediti.
Uredite formulo in izberite končni sklic na "Cal"
Začnite vnašati IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), da zamenjate končni Cal.
Končna formula bi morala biti
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Pritisnite Ctrl + Shift + Enter. Rezultat bi moral biti:
Preostane še dve stvari. Lahko vzamete to formulo in ji daste ime "Cool":
Nato uporabite to v tukaj prikazani formuli:
Mimogrede, definirana imena se obravnavajo, kot da so vnesena v matriko.
Preostane le še formatiranje celic in vpisovanje dni v tednu in imena meseca. Torej razširite stolpce, povečate višino vrstice, povečate velikost pisave in poravnate besedilo:
Nato postavite obrobe okoli celic:
Mesec in leto združite in centrirajte ter oblikujte:
Nato izklopite mrežne črte in voila:
Ta gostujoči članek je iz Excelovega MVP-ja Bob Umlas. Je iz knjige Excel Outside the Box. Če si želite ogledati druge teme v knjigi, kliknite tukaj.
