Hogyan hozzunk létre egy dinamikusan változó naptárat az Excel-ben

ebben a cikkben megmutatjuk, hogyan hozhat létre dinamikusan változó naptárat az Excel-ben.

munkafüzet letöltése

változók feltöltése

az első lépés a képletekben használandó változók létrehozása. Ezeknek a változóknak meg kell adniuk a mai információkat, hogy a Naptár Naptár automatikusan frissüljön. Itt található a változók listája:

• jelen dátum
• a hónap jelen napjának száma
• a jelen hónap száma
• jelen év
• a jelen hónap első napjának dátuma
• a hét jelen napjának száma
• az év jelen hetének száma

a ma, nap, hónap, év, dátum, hétköznap és hét függvényeket fogjuk használni. Röviden, míg a TODAY függvény a mai nap dátumát adja vissza, nap, hónap, év, hétköznap és WEEKNUM függvények elemzik a megfelelő dátumértékeket. A DATE függvény az adott év, hónap és nap dátumértékét adja vissza.

a következő képernyőképen láthatja az általunk megadott neveket és képleteket, amelyeket 20.November 2020-án generáltunk.

minden változót megneveztünk, hogy megkönnyítsük a munkánkat a képletek létrehozásakor. Következő lépés, hogy hozzon létre egy naptár Excel létrehoz egy vázlatot a naptár felület.

a naptár vázlata az Excel programban

a hónap napjait létrehozó tényleges képletek létrehozása előtt először el kell helyeznünk egy körvonalat, amely segít a képletek létrehozásában, és vizuális referenciát is biztosít.

a napok 7 oszlopból és 6 sorból álló asztalra kerülnek. Ezeket a számokat 2-vel kell növelnünk a címek és a segítő sejtek esetében is. Míg az oszlop – és sorcímek tartalmazzák az oszlopok heti napjait és a heti számokat soronként, a segítő cellák egymást követő számok lesznek 1-től kezdődően.

a naptár alább nézhet ki.

használjon szegélyeket vagy háttérszíneket tetszés szerint. A lényeg egy dinamikus hétszám használata a címben. Hivatkozzon a WEEKNUM függvényre az első héten, és adjon hozzá 1-et minden egymást követő héthez.

folytassuk a naptár létrehozását az Excelben a napi számok feltöltésével.

generáló napok

ha hasonló elrendezést használ, és nem akar aggódni a képlet részletei miatt, nyugodtan másolja az alábbi képletet. Ezután illessze be a naptár napterületének bal felső cellájába, és töltse ki a fennmaradó cellákat. A G2 és E4 cellák a helper oszlopok első celláira utalnak. Ezért előfordulhat, hogy módosítania kell ezeket a hivatkozásokat, kivéve, ha az első cella G4.

most nézzük meg közelebbről a képletet. Először a képlet a DATE függvényt használja, amely az adott év, hónap és nap dátumát adja vissza. Az első 2 argumentumot a már kiszámított YearNumber és MonthNumber változók adják meg.

a képlet Napi részének célja a napi számok generálása 7 alapon, mivel egy héten 7 nap van. 7 * ($E4-1) A képlet része ellenőrizze a hét számát A helper E oszlopban, és létrehoz egy bázist, amely 0-val kezdődik, és minden alkalommal 7-rel növekszik.

növelhetjük vagy csökkenthetjük ezt a számot, mint a hónap első napjának heti napja közötti különbséget a napi heti számmal. Más szavakkal, a jelen hónap első napját rögzítési pontként kapjuk meg. Például ez a hét 7. napja (hétfőtől vasárnapig) 1. November 2020-ig.

ha naptárunk hete feltételezi, hogy a hétfő a hét első napja, akkor a hétfőnek 6 nappal a hónap első napja előtt (=1-7) kell lennie. Így olyan számokat kapunk, mint az alábbiakban.

adja hozzá ezeket az értékeket 1-hez a számok eltolásához. Tehát a vasárnap lehet az első nap, mint a következő példában.

ennek eredményeként 2 tömbünk van 2 dimenzióhoz. Táblázat létrehozásához mindkét tömböt össze kell adnunk.

ezek a számok a jelen hónap napját jelentik. Vannak olyan számok, amelyek kevesebbek, mint 1 vagy nagyobb, mint 31, ami nyilvánvalóan nem lehet A hónap napja.

a dátum függvény az ilyen típusú számokat az előző vagy a következő hónap dátumának generálásával kezeli. Például a -2 két nappal az adott hónap első napja előtt válik.

végül, ha ezeket a számokat használja a dátum függvényben, megkaphatja az aktuális hónap tényleges dátumait.

formázás

napi számok

most töltsük fel a napokat. Az értékek formázását a tényleges érték megváltoztatása nélkül módosíthatja. Ezt a funkciót Számformázásnak hívják. Kövesse a lépéseket a szám formázásának beállításához, hogy csak a napok jelenjenek meg.

1. válassza ki a dátumokat a napi területen
2. nyomja meg a Ctrl + 1 billentyűt a Cellák formázása párbeszédablak megnyitásához
3. aktiválja a szám fület, ha még nincs megnyitva
4. válassza az Egyéni lehetőséget a Kategórialistában
5. írja be a D betűt a típus mezőbe
6. kattintson az OK gombra
   

most elkészült a naptár elrendezése.

ha el akarja távolítani a párnázást az egyjegyű dátumokhoz, használhatja a DD-t egyetlen d helyett. További információ a számformázásról: Számformázás az Excelben-minden, amit tudnia kell

a mai nap kiemelése

végül feltételes formázást fogunk használni ahhoz, hogy különböző színeket állítsunk be olyan napokra, amelyek nem a jelen hónapban vannak, és kiemeljük a mai napot.

ezen feltételes formázási beállítások hozzáadása;

1. válassza ki a napi tartományt
2. kattintson a Feltételes formázás ikonra a szalag Kezdőlap lapján
3. új szabály kiválasztása
   
4. válassza a képlet használata lehetőséget annak meghatározásához, hogy mely cellákat formázza
5. adjon meg egy képletet az aktuális nap kiemelésére vonatkozó szabály meghatározásához
   =G4=ma ()
   G4: a tartomány bal felső cellája napokat tartalmaz. Használjon relatív hivatkozást (ne használjon $ – t).
6. kattintson a Formázás gombra a Cellák formázása párbeszédablak előnézeti ablakában
   
7. a párbeszédpanelen válassza ki a formázási beállításokat, amelyeket a mai cellában szeretne látni
   ebben a példában narancssárga hátteret használtunk.
8. kattintson az OK gombra a
   
9. az előnézeti mező megjeleníti a beállításokat
10. kattintson az OK gombra a feltételes formázás alkalmazásához
    

így néz ki:

a képlet logikai értéket ad vissza. Ha a cella értéke (G4) megegyezik a TODAY függvény eredményével, amely dátumformátumban adja vissza a jelen napot, a képlet TRUE értéket ad vissza. Ellenkező esetben hamis. Ha az eredmény igaz, az Excel alkalmazza a formázást a cellába.

itt 2 fontos pont van:

1. relatív hivatkozást kell használnia, például G4, hacsak nem akarja, hogy az Excel a tartomány mentén töltse fel a hivatkozást. Fontolja meg, hogyan változnak a képletek, amikor máshová másolja őket. Az Excel ugyanezt alkalmazza a feltételes formázási képletekre is. További információ:: Excel abszolút hivatkozás és relatív hivatkozás létrehozása
2. a napi celláknak vissza kell adniuk a tényleges dátumértékeket. Ezért használjuk a számformázást a napi adagok megjelenítéséhez.