Vytvořte kalendář v aplikaci Excel pomocí jednoho vzorce pomocí vzorce zadaného do pole.
Podívejte se na toto číslo:
Tento vzorec =Coolje stejný vzorec v každé buňce od B5: H10! Koukni se:
Bylo zadáno do pole, jakmile byla poprvé vybrána B5: H10. V tomto článku uvidíte, co je za tímto vzorcem.
Mimochodem, existuje buňka, která ještě není zobrazena, což je měsíc, který se má zobrazit. To znamená, že buňka J1 obsahuje =TODAY()(a píšu to v prosinci), ale pokud ji změníte na 5/8/2012, uvidíte:
To je květen 2012. Dobře, rozhodně super! Začněte od začátku a v kalendáři se dopracujte k tomuto vzorci a podívejte se, jak funguje.
Předpokládejme také, že dnes je 8. května 2012.
Nejprve se podívejte na tento obrázek:
Vzorec ve skutečnosti nedává smysl. Bylo by to, kdyby to bylo obklopeno =SUM, ale chcete vidět, co je za vzorcem, takže jej rozbalíte výběrem a stisknutím klávesy F9.
Obrázek nahoře se po stisknutí klávesy F9 změní na obrázek níže.
Všimněte si, že za 3 je středník - to označuje nový řádek. Nové sloupce jsou reprezentovány čárkou. To tedy využijete.
Počet týdnů v měsíci se liší, ale žádný kalendář nepotřebuje více než šest řádků, aby představoval jakýkoli měsíc, a samozřejmě mají všechny sedm dní. Podívejte se na toto číslo:
Ručně zadejte hodnoty 1 až 42 v B5: H10 a pokud zadáte =B5:H10do buňky a poté rozbalíte lištu vzorců, uvidíte, co se zde zobrazuje:
Všimněte si umístění středníků - po každém násobku 7 - označujícím nový řádek. Toto je začátek vzorce, ale místo tak dlouhého můžete použít tento kratší vzorec. Vyberte B5: H10. Typ
=(0;1;2;3;4;5)*7+(1,2,3,4,5,6,7)
jako vzorec, ale nestiskněte Enter.
Chcete-li aplikaci Excel říct, že se jedná o maticový vzorec, musíte levou rukou podržet Ctrl + Shift. Zatímco držíte Ctrl + Shift, stiskněte pravou ruku Enter. Poté uvolněte Ctrl + Shift. Pro zbytek tohoto článku se tato sada klávesových zkratek bude jmenovat Ctrl + Shift + Enter.
Pokud jste Ctrl + Shift + Enter zadali správně, objeví se složené závorky kolem vzorce v řádku vzorců a čísla 1 až 42 se objeví v B5: H10, jak je znázorněno zde:
Všimněte si, že berete čísla 0 až 5 oddělená středníky (každý nový řádek) a vynásobíte je 7, což dává toto:
Vertikální orientace těchto hodnot přidaná k horizontální orientaci hodnot 1 až 7 přináší stejné hodnoty, jak je znázorněno. Rozšíření je stejné jako to, které jste měli dříve. Předpokládejme, že nyní k těmto číslům přidáte DNES?
Poznámka: Úpravy existujícího vzorce pole jsou velmi složité. Opatrně postupujte podle těchto kroků: Vyberte B5: H10. Kliknutím na lištu vzorců upravte stávající vzorec. Zadejte + J1, ale nestiskněte Enter. Upravený vzorec přijmete stisknutím Ctrl + Shift + Enter.
Výsledek pro 8. května 2012 je:
Tato čísla jsou pořadová čísla (počet dní od 1. 1. 1900). Pokud je naformátujete jako krátká data:
Zjevně to není správné, ale dostanete se tam. Co když je naformátujete jednoduše „d“ pro den v měsíci:
Skoro to vypadá jako měsíc, ale žádný měsíc nezačíná devátým měsícem. Aha, tady je jeden problém. Použili jste J1, který obsahuje 5/8/2012, a opravdu musíte použít datum prvního v měsíci. Předpokládejme tedy, že vložíte =DATE(YEAR(J1),MONTH(J1),1)J2:
Buňka J1 obsahuje 8/8/2012 a buňka J2 to mění na první v měsíci toho, co je zadáno v J1. Pokud tedy změníte J1 ve vzorci kalendáře na J2:
Bližší, ale stále není v pořádku. Je zapotřebí ještě jedna úprava, a to musíte odečíst den v týdnu prvního dne. To znamená, že buňka J3 obsahuje =WEEKDAY(J2). 3 představuje úterý. Takže pokud odečtete J3 od tohoto vzorce, dostanete:
A to je to pravé pro květen 2012!
Dobře, jsi si opravdu blízký. Stále je špatně, že se v květnovém kalendáři objevují dubnové 29 a 30 a také se objeví 1. až 9. června. Musíte je vyčistit.
Vzorec můžete pojmenovat pro snadnější použití. Říkejte tomu „Cal“ (zatím ne „cool“). Viz tento obrázek:
Potom můžete změnit vzorec tak, aby byl jednoduše =Cal(stále Ctrl + Shift + Enter):
Nyní můžete změnit vzorec tak, aby četl, že pokud je výsledek v řádku 5 a výsledek je přes 20, řekněme, pak by měl být tento výsledek prázdný. Řádek 5 bude obsahovat první týden kteréhokoli měsíce, takže byste nikdy neměli vidět žádné hodnoty nad 20 (nebo jakékoli číslo nad sedm by bylo špatné - číslo jako 29, které vidíte v buňce B5 výše uvedeného obrázku, je z předchozího měsíce). Můžete tedy použít =IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),Cal):
Nejprve si všimněte, že buňky B5: D5 jsou prázdné. Vzorec nyní zní „pokud se jedná o řádek 5, pak je-li DEN výsledku větší než 20, zobrazí se prázdné“.
Můžete pokračovat v odstraňování nízkých čísel na konci - hodnoty příštího měsíce. Zde je postup, jak to snadno udělat.
Upravte vzorec a vyberte konečný odkaz na „Cal“
Začněte psát IF (ROW ()> 8, IF (DAY (Cal) <15, "", Cal), Cal), abyste nahradili konečný Cal.
Konečný vzorec by měl být
=IF(ROW()=5,IF(DAY(Cal)>20,"",Cal),IF(ROW()>8,IF(DAY(Cal)<15,"",Cal),Cal))
Stiskněte Ctrl + Shift + Enter. Výsledkem by mělo být:
Zbývají dvě věci. Můžete si vzít tento vzorec a dát mu název „Cool“:
Použijte to ve vzorci zobrazeném zde:
Mimochodem, s definovanými jmény se zachází, jako by byly zadány do pole.
Zbývá jen zformátovat buňky a vložit Dny v týdnu a název měsíce. Takže rozšíříte sloupce, zvětšíte výšku řádku, zvětšíte velikost písma a zarovnáte text:
Potom umístěte ohraničení kolem buněk:
Sloučit a vycentrovat měsíc a rok a formátovat jej:
Poté vypněte mřížku a voila:
Tento článek pro hosta je od Excel MVP Boba Umlasa. Je to z knihy Excel mimo krabici. Chcete-li zobrazit další témata v knize, klikněte sem.
