Összegzés
Dinamikus naptárrácsot állíthat be egy Excel-munkalapon egy képletsorozattal, a cikkben leírtak szerint. A bemutatott példában a B6 képlete a következő:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
ahol a "start" a megnevezett K5 tartomány, és tartalmazza a 2018. szeptember 1-jei dátumot.
Magyarázat
Megjegyzés: Ez a példa feltételezi, hogy a kezdő dátumot a hónap első számaként kell megadni. Az alábbiakban talál egy képletet, amely dinamikusan adja vissza az aktuális hónap első napját.
A rácsnak az ábrán látható elrendezésével a fő probléma a dátum kiszámítása a naptár első cellájában (B6). Ez a következő képlettel történik:
=start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6)
Ez a képlet a hónap első napját megelőző vasárnapot számítja ki a KIVÁLASZTÁS funkció használatával, hogy az előző vasárnaphoz megfelelő számú napot "visszagörget". A CHOOSE tökéletesen működik ebben a helyzetben, mert tetszőleges értékeket engedélyez a hét minden napjára. Ezt a funkciót nulla nap visszagörgetésére használjuk, amikor a hónap első napja vasárnap. További részletek erről a problémáról itt találhatók.
A B6 első napjának megállapításával a rács többi képlete egyszerűen növeli az előző dátumot eggyel, kezdve a C6 képlettel:
=IF(B6"",B6,$H5)+1
Ez a képlet a cellát azonnal balra teszteli értékre. Ha nem található érték, akkor a fenti H oszlopból vesz ki egy értéket. Megjegyzés: A $ H5 vegyes hivatkozás, az oszlop zárolásához, mivel a képlet az egész rácsra másolódik. Ugyanezt a képletet alkalmazzák a B6 kivételével az összes cellában.
Feltételes formázási szabályok
A naptár feltételes formázási képletekkel módosítja a formázást az előző és a következő hónapok árnyékolására, valamint az aktuális nap kiemelésére. Mindkét szabály a teljes rácsra vonatkozik. Az előző és a következő hónapokra a képlet a következő:
=MONTH(B6)MONTH(start)
Az aktuális napra a képlet a következő:
=B6=TODAY()
További részletek: Feltételes formázás képletekkel (10 példa)
Naptár címsor
A naptári címet - hónap és év - ezzel a képlettel számoljuk ki a B4 cellában:
=start
Az "mmmm yyyy" egyéni számformátummal formázva. A cím naptár feletti középre állításához a B4: H4 tartomány vízszintes igazításának beállítása "középre a kijelölés közepén". Ez jobb megoldás, mint a cellák egyesítése, mivel nem változtatja meg a munkalap rácsszerkezetét.
Örök naptár az aktuális dátummal
Az aktuális dátum alapján automatikusan frissülő naptár létrehozásához használhatja a következő képletet a K5-ben:
=EOMONTH(TODAY(),-1)+1
Ez a képlet megkapja az aktuális dátumot a TODAY függvénnyel, majd megkapja az aktuális hónap első napját az EOMONTH függvény használatával. Cserélje le a TODAY () mezőt egy adott dátumra, ha egy másik hónapban szeretne naptárat készíteni. További részletek a EOMONTH működéséről itt.
Létrehozás lépései
- Rácsvonalak elrejtése (opcionális)
- Szegély hozzáadása a B5-höz: H11 (7R x 7C)
- Nevezze meg a K5 nevet "kezdet" és írja be a dátumot, például "2018. szeptember 1."
- Képlet B4-ben = kezdet
- A B4 formátum "mmmm yyyy"
- Válassza a B4: H4 elemet, állítsa be az igazítást "Középre a kijelölés közepén"
- A B5: H5 tartományba írja be a napi rövidítéseket (SMTWTFS)
- B6 képlet = start-CHOOSE (WEEKDAY (start), 0,1,2,3,4,5,6)
- Válassza a B6: H11 elemet, alkalmazza a "d" egyéni számformátumot
- Képlet C6-ban = IF (B6 "", B6, $ H5) +1
- Másolja a C6 képletet a naptárrács többi cellájába
- Előző / Következő feltételes formázási szabály hozzáadása (lásd a fenti képletet)
- Aktuális feltételes formázási szabály hozzáadása (lásd a fenti képletet)
- Módosítsa a dátumot a K5-ben egy másik "hónap első" dátumra a teszteléshez
- Az öröknaptár esetében a képlet K5-ben = EGYEDI (TODAY (), - 1) +1
