Vytvorte kalendár v programe Excel pomocou jedného vzorca pomocou vzorca zadaného do poľa.
Pozrite sa na tento obrázok:
Tento vzorec =Coolje rovnaký v každej bunke od B5: H10! Pozri:
Do poľa sa vstupovalo hneď po prvom výbere B5: H10. V tomto článku uvidíte, čo sa za týmto vzorcom skrýva.
Mimochodom, existuje bunka, ktorá ešte nie je zobrazená a je to mesiac, ktorý sa má zobraziť. To znamená, že bunka J1 obsahuje =TODAY()(a píšem to v decembri), ale ak ju zmeníte na 8. 8. 2012, uvidíte:
Toto je máj 2012. Dobre, určite super! Začnite od začiatku a v kalendári sa prepracujte k tomuto vzorcu a uvidíte, ako to funguje.
Predpokladajme tiež, že dnes je 8. máj 2012.
Najprv sa pozrite na tento obrázok:
Vzorec naozaj nedáva zmysel. Bolo by, keby bolo obklopené =SUM, ale chcete vidieť, čo sa za vzorcom skrýva, takže ho rozšírite výberom a stlačením klávesu F9.
Obrázok hore sa stane obrázkom dole, keď stlačíte kláves F9.
Všimnite si, že za trojkou je bodkočiarka - znamená to nový riadok. Nové stĺpce sú reprezentované čiarkou. Takže to využijete.
Počet týždňov v mesiaci sa líši, ale žiadny kalendár nemusí predstavovať viac ako šesť riadkov, aby predstavoval akýkoľvek mesiac, a samozrejme, všetky majú sedem dní. Pozrite sa na tento obrázok:
Ručne zadajte hodnoty 1 až 42 v B5: H10. Ak vstúpite =B5:H10do bunky a potom rozbalíte riadok vzorcov, uvidíte, čo sa tu zobrazuje:
Všimnite si umiestnenie bodkočiarok - po každom násobku 7 - označujúcich nový riadok. Toto je začiatok vzorca, ale namiesto tak dlhého môžete použiť tento kratší vzorec. Vyberte B5: H10. Typ
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
ako vzorec, ale nestláčajte kláves Enter.
Ak chcete programu Excel povedať, že ide o maticový vzorec, musíte ľavou rukou podržať Ctrl + Shift. Zatiaľ čo držíte Ctrl + Shift, stlačte pravú ruku Enter. Potom uvoľnite klávesy Ctrl + Shift. Po zvyšok tohto článku sa táto sada klávesových skratiek bude nazývať Ctrl + Shift + Enter.
Ak ste Ctrl + Shift + Enter zadali správne, okolo vzorca na paneli vzorcov sa objavia zložené zátvorky a čísla 1 až 42 sa zobrazia v B5: H10, ako je to znázornené tu:
Všimnite si, že čísla 0 až 5 oddeľujete bodkočiarkami (každý nový riadok) a vynásobíte ich číslom 7, čím dáte toto:
Vertikálna orientácia týchto hodnôt pridaná k horizontálnej orientácii hodnôt 1 až 7 vedie k rovnakým hodnotám, ako sú zobrazené. Rozšírenie je rovnaké ako to, ktoré ste mali predtým. Predpokladajme, že teraz k týmto číslam pridáte DNES?
Poznámka: Úpravy existujúceho vzorca poľa sú veľmi zložité. Opatrne postupujte podľa týchto krokov: Vyberte B5: H10. Kliknutím na panel vzorcov upravte existujúci vzorec. Napíšte + J1, ale nestláčajte kláves Enter. Upravený vzorec prijmete stlačením klávesov Ctrl + Shift + Enter.
Výsledok k 8. máju 2012 je:
Tieto čísla sú poradové čísla (počet dní od 1. 1. 1900). Ak ich formátujete ako krátke dátumy:
Jednoznačne nie je v poriadku, ale dostanete sa tam. Čo ak ich naformátujete pre deň v mesiaci ako „d“:
Vyzerá takmer ako mesiac, ale žiaden mesiac nezačína deviatou v mesiaci. Aha, tu je jeden problém. Použili ste J1, ktorý obsahuje 8. 8. 2012, a naozaj musíte použiť dátum prvého v mesiaci. Predpokladajme teda, že ste vložili =DATE(YEAR(J1),MONTH(J1),1)J2:
Bunka J1 obsahuje 8. 8. 2012 a bunka J2 to zmení na prvú v mesiaci toho, čo je zadané v J1. Takže ak zmeníte J1 vo vzorci kalendára na J2:
Bližšie, ale stále nie v poriadku. Je potrebná ešte jedna úprava, a to musíte odpočítať všedný deň prvého dňa. To znamená, že bunka J3 obsahuje =WEEKDAY(J2). 3 predstavuje utorok. Takže teraz, ak od tohto vzorca odrátate J3, získate:
A to je vlastne to pravé pre máj 2012!
Dobre, si skutočne blízko. Čo stále nie je v poriadku, je, že 29. a 30. apríl sa zobrazuje v májovom kalendári a 1. júna až 9. júna. Musíte ich vyčistiť.
Pre ľahšiu orientáciu môžete vzorec pomenovať. Môžete to nazvať „Cal“ (zatiaľ nie „v pohode“). Pozri tento obrázok:
Potom môžete zmeniť vzorec tak, aby bol jednoducho =Cal(stále Ctrl + Shift + Enter):
Teraz môžete zmeniť vzorec tak, aby znel, že ak je výsledok v riadku 5 a výsledok je napríklad nad 20, potom by mal byť tento výsledok prázdny. Riadok 5 bude obsahovať prvý týždeň ľubovoľného mesiaca, takže by ste nikdy nemali vidieť žiadne hodnoty nad 20 (alebo akékoľvek číslo nad sedem by bolo nesprávne - číslo ako 29, ktoré vidíte v bunke B5 na obrázku vyššie, je z predchádzajúceho mesiaca). Môžete teda použiť =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Najskôr si všimnite, že bunky B5: D5 sú prázdne. Vzorec teraz znie „ak je to riadok 5, potom ak je DEŇ výsledku nad 20, zobraziť prázdne“.
Môžete pokračovať v odstraňovaní nízkych čísel na konci - hodnoty z budúceho mesiaca. Tu je postup, ako to ľahko urobiť.
Upravte vzorec a vyberte konečný odkaz na „Cal“.
Začnite písať IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), aby ste nahradili konečný Cal.
Konečný vzorec by mal byť
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Stlačte Ctrl + Shift + Enter. Výsledkom by malo byť:
Zostávajú dve veci. Môžete použiť tento vzorec a dať mu názov „Cool“:
Potom to použite vo vzorci zobrazenom tu:
Mimochodom, s definovanými názvami sa zaobchádza, akoby sa zadávali do poľa.
Čo zostáva urobiť, je naformátovať bunky a vložiť Dni v týždni a názov mesiaca. Takže rozširujete stĺpce, zväčšujete výšku riadku, zväčšujete veľkosť písma a zarovnávate text:
Potom ohraničte bunky:
Zlúčiť a vycentrovať mesiac a rok a naformátovať ho:
Potom vypnite mriežku a voila:
Tento článok je hosťom programu Excel MVP Boba Umlasa. Je to z knihy Excel mimo krabice. Ak si chcete pozrieť ďalšie témy v knihe, kliknite sem.
