Hogyan ellenőrizhető, hogy a dátum munkaszüneti nap-e, és az Excel-ben a napokat számolja-e a szünnapok kivételével?
Például van egy listája a dátumokról, és szeretné ellenőrizni, hogy vannak-e munkaszüneti napok a listában, hogyan tudná ezt gyorsan elvégezni? Ebben a cikkben megmutatom, hogyan lehet kiszámolni az amerikai munkaszüneti napok összes dátumát az adott éven belül, majd ellenőrizni, hogy egy dátum amerikai munkaszüneti nap az Excel-ben.
- 1. rész: Számítsa ki az adott éveken belüli munkaszüneti napokat az Excel programban
- 2. rész: Ellenőrizze, hogy a dátum munkaszüneti nap-e az Excelben
- 3. rész: Számolja meg a két nap közötti napokat, kivéve a hétvégéket és az ünnepeket az Excel programban
1. rész: Számolja ki az adott év munkaszüneti napjait az Excel programban
Mielőtt ellenőrizné, hogy egy dátum munkaszüneti nap-e, fel kell sorolnia az Excelben az adott éveken belüli összes ünnepnapot. Ezért a táblázat elkészítése az alábbi képen látható módon megkönnyíti a munkáját.
Háromféle amerikai munkaszüneti nap létezik:
(1) Az első fajta munkaszüneti nap egy meghatározott időpontban, például az újév napja január 1-jén van. könnyen kiszámíthatjuk az újév napját a képlettel = DÁTUM (adott év, 1,1);
(2) A második fajta egy meghatározott hétköznapi munkaszüneti nap, például az elnök napja. Könnyen kiszámíthatjuk az elnök napját a képlettel = DÁTUM (adott év, 1,1) + 14 + VÁLASZTÁS (WEEKDAY (DÁTUM (adott év, 1,1)), 1,0,6,5,4,3,2);
(3) És az utolsó fajta az emléknap, könnyen kiszámíthatjuk az emléknapot a képlettel = DÁTUM (adott év, 6,1) - WEEKDAY (DÁTUM (adott év, 6,6)).
A következő táblázatban felsorolom az összes munkaszüneti nap kiszámításához szükséges képleteket. Csak írja be a képleteket a megfelelő cellába, és nyomja meg a gombot belép kulcs egyenként.
Ünnep | Sejt | képletek |
Újév napja | C2 | = DÁTUM (C1,1,1) |
Martin Luther King Jr. nap | C3 | = DÁTUM (C1,1,1) + 14 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,1,1)), 1,0,6,5,4,3,2) |
Elnök napja | C4 | = DÁTUM (C1,2,1) + 14 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,2,1)), 1,0,6,5,4,3,2) |
Háborús hősök emléknapja | C5 | = DÁTUM (C1,6,1) -HETI NAP (DÁTUM (C1,6,6)) |
Independence Day | C6 | = DÁTUM (C1,7,4) |
Labor Day | C7 | = DÁTUM (C1,9,1) + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,9,1)), 1,0,6,5,4,3,2) |
Columbus Day | C8 | = DÁTUM (C1,10,1) + 7 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,10,1)), 1,0,6,5,4,3,2) |
Veteránok Napja | C9 | = DÁTUM (C1,11,11) |
Hálaadás napja | C10 | = DÁTUM (C1,11,1) + 21 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,11,1)), 4,3,2,1,0,6,5) |
Karácsony | C11 | = DÁTUM (C1,12,25) |
Jegyzet: A fenti táblázat képleteiben a C1 az a referenciacella, amely az adott évet megtalálja. Példánkban ez a 2015-ös évet jelenti, és igényei szerint módosíthatja.
Ezekkel a képletekkel könnyedén kiszámíthatja a munkaszüneti napok dátumát adott évekkel. Lásd az alábbi képernyőképet:
Mentse el a tartományt AutoText bejegyzésként (a fennmaradó cellaformátumok és képletek) a későbbi újrafelhasználáshoz
Nagyon unalmas lehet a cellákra hivatkozni és képleteket alkalmazni az egyes ünnepnapok kiszámításához. A Kutools for Excel aranyos megoldást kínál a Auto szöveg segédprogrammal mentheti a tartományt AutoText bejegyzésként, amely megtarthatja a tartomány cellaformátumait és képleteit. És akkor egyetlen kattintással újra felhasználhatja ezt a tartományt. A munka egyszerűvé válik, ha csak egy kattintással beszúrja ezt a táblázatot és megváltoztatja az évet ebben a táblázatban!
Kutools az Excel számára - Töltse fel az Excelt több mint 300 alapvető eszközzel. Élvezze a teljes funkcionalitású 30 napos INGYENES próbaverziót hitelkártya nélkül! Szerezd meg most
2. rész: Ellenőrizze, hogy a dátum munkaszüneti nap-e az Excelben
Miután felsorolta az egyes évek munkaszüneti napjainak dátumát, az Excel képleteivel könnyen ellenőrizhetjük, hogy egy dátum munkaszüneti nap-e vagy sem. Tételezzük fel, hogy van egy dátumlista, amint az a következő képernyőképen látható, és bemutatom a könnyű megvalósítás módjait.
Válasszon egy üres cellát a dátumlista mellett - írja a B18 cella - írja be a képletet = HA (COUNTIF ($ C $ 2: $ D $ 11, A18), "Ünnep", "Nem") bele, majd húzza a Fill Handle-t a kívánt tartományba. Lásd a fenti képernyőképet:
Megjegyzések:
(1) Az = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") képletben a $ C $ 2: $ D $ 11 az adott évek munkaszüneti napjai tartománya, és Az A18 az a cella, amelynek dátumával ellenőrizni szeretné, hogy munkaszüneti nap-e, és az igényei szerint módosíthatja őket. Ez a képlet pedig "Holiday" -ot ad vissza, ha a konkrét dátum munkaszüneti nap, és ha nem, akkor a "No" -ot adja vissza.
(2) Ezt a tömbképletet is alkalmazhatja = HA (VAGY ($ C $ 2: $ D $ 11 = A18), "Ünnep", "NEM") annak ellenőrzése, hogy a megfelelő dátum ünnep-e vagy sem.
3. rész: Számolja meg a két nap közötti napokat, kivéve a hétvégéket és az ünnepeket az Excel programban
Az 1. részben felsoroltuk az adott év összes ünnepét, és most ez a módszer arra készteti Önt, hogy számlálja a napok számát az összes hétvége és ünnepnap kivételével.
Válasszon egy üres cellát, amellyel megkapja a napok számát, és adja meg a képletet = HÁLÓZATI NAPOK (E1, E2, B2: B10) bele, és nyomja meg a gombot belép kulcs.
Megjegyzések: A fenti cellákban E1 a megadott dátumtartomány kezdő dátuma, E2 a befejezés dátuma és B2: B10 az ünnepi lista, amelyet az 1. részben kiszámítottunk.
Most megkapja a megadott dátumtartományban a hétvégék és ünnepek kivételével a napok számát.
Pontosan / statikusan másolja a képleteket az Excel cellahivatkozásainak megváltoztatása nélkül
Kutools az Excel számára Pontos másolás segédprogram segítségével könnyedén másolhat több képletet pontosan az Excel cellahivatkozásainak módosítása nélkül, megakadályozva a relatív cellahivatkozások automatikus frissítését.
Kutools az Excel számára - Töltse fel az Excelt több mint 300 alapvető eszközzel. Élvezze a teljes funkcionalitású 30 napos INGYENES próbaverziót hitelkártya nélkül! Szerezd meg most
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!