Jak zkontrolovat, zda je datum státním svátkem a počítat dny kromě svátků v aplikaci Excel?
Například máte seznam dat a chcete zkontrolovat, zda v seznamu existují nějaké svátky, jak byste to mohli udělat rychle? V tomto článku vám ukážu způsoby, jak vypočítat všechna data amerických státních svátků v daných letech, a poté snadno zkontrolovat, zda je datum americkým státním svátkem v aplikaci Excel.
- Část 1: Výpočet státních svátků v daném roce v aplikaci Excel
- Část 2: Zkontrolujte, zda je datum v aplikaci Excel svátkem
- Část 3: Počítání dnů mezi dvěma dny kromě víkendů a svátků v aplikaci Excel
Část 1: Výpočet státních svátků s daným rokem v aplikaci Excel
Než zkontrolujete, zda je datum státním svátkem, musíte v aplikaci Excel uvést seznam všech svátků za dané roky. Proto vám příprava tabulky podle následujícího snímku obrazovky usnadní práci.
Existují tři druhy amerických státních svátků:
(1) Prvním druhem je státní svátek ve stanovený den, například Nový rok je 1. ledna. můžeme snadno vypočítat Nový rok podle vzorce = DATUM (daný rok, 1,1);
(2) Druhým druhem je státní svátek ve stanovený pracovní den, například Den prezidenta. Den prezidenta můžeme snadno vypočítat podle vzorce = DATUM (daný rok, 1,1) + 14 + VÝBĚR (WEEKDAY (DATUM (daný rok, 1,1)), 1,0,6,5,4,3,2);
(3) A posledním druhem je Memorial Day, můžeme snadno vypočítat Memorial Day podle vzorce = DATE (daný rok, 6,1) -WEEKDAY (DATE (daný rok, 6,6)).
Zde uvádíme seznam vzorců pro výpočet všech svátků v následující tabulce. Stačí zadat vzorce do správné buňky a stisknout vstoupit klíč jeden po druhém.
| Dovolená | Buňka | Vzorce |
| Nový rok | C2 | = DATUM (C1,1,1) |
| Martin Luther King Jr. den | C3 | = DATUM (C1,1,1) + 14 + VÝBĚR (WEEKDAY (DATUM (C1,1,1)), 1,0,6,5,4,3,2) |
| Den prezidenta | C4 | = DATUM (C1,2,1) + 14 + VÝBĚR (WEEKDAY (DATUM (C1,2,1)), 1,0,6,5,4,3,2) |
| Memorial Day | C5 | = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6)) |
| Den nezávislosti | C6 | = DATUM (C1,7,4) |
| Svátek práce | C7 | = DATUM (C1,9,1) + VÝBĚR (WEEKDAY (DATUM (C1,9,1)), 1,0,6,5,4,3,2) |
| Columbus Day | C8 | = DATUM (C1,10,1) + 7 + VÝBĚR (WEEKDAY (DATUM (C1,10,1)), 1,0,6,5,4,3,2) |
| Den veteránů | C9 | = DATUM (C1,11,11) |
| Den díkůvzdání | C10 | = DATUM (C1,11,1) + 21 + VÝBĚR (WEEKDAY (DATUM (C1,11,1)), 4,3,2,1,0,6,5) |
| Štědrý den | C11 | = DATUM (C1,12,25) |
Poznámka: Ve vzorcích v tabulce výše je C1 referenční buňkou, která lokalizuje daný rok. V našem příkladu to znamená rok 2015 a můžete jej změnit podle svých potřeb.
S těmito vzorci můžete snadno vypočítat data svátků s danými roky. Viz snímek obrazovky níže:
Uložte rozsah jako položku automatického textu (zbývající formáty buněk a vzorce) pro budoucí použití
Musí být velmi zdlouhavé odkazovat buňky a používat vzorce pro výpočet každé dovolené. Kutools pro Excel poskytuje roztomilé řešení Automatický text nástroj pro uložení rozsahu jako položku automatického textu, která může zůstat formátem buňky a vzorci v rozsahu. A pak tento rozsah znovu použijete jediným kliknutím. Práce je snadná pouhým jedním kliknutím, vložíte tuto tabulku a změníte rok v této tabulce!
Kutools pro Excel - Supercharge Excel s více než 300 základními nástroji. Užijte si plnohodnotnou 30denní zkušební verzi ZDARMA bez nutnosti kreditní karty! Get It Now
Část 2: Zkontrolujte, zda je datum v aplikaci Excel svátkem
Po uvedení všech dat svátků konkrétních let můžeme pomocí vzorců v aplikaci Excel snadno zkontrolovat, zda je datum svátkem nebo ne. Předpokládejme, že máte seznam dat, jak ukazuje následující snímek obrazovky, a já vám představím způsoby, jak to snadno udělat.
Vyberte prázdnou buňku kromě seznamu dat, říká buňka B18, zadejte vzorec = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Prázdniny", "Ne") do něj a poté přetáhněte rukojeť výplně do požadovaného rozsahu. Viz snímek obrazovky výše:
Poznámky:
(1) Ve vzorci = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Prázdniny", "Ne"), je $ C $ 2: $ D $ 11 rozsah státních svátků v konkrétních letech a A18 je buňka s datem, které chcete zkontrolovat, zda je svátek, a můžete je změnit podle svých potřeb. A tento vzorec vrátí „svátek“, pokud je konkrétním datem státní svátek, a vrátí „ne“, pokud tomu tak není.
(2) Můžete také použít tento vzorec pole = IF (NEBO ($ C $ 2: $ D $ 11 = A18), "Prázdniny", "NE") zkontrolovat, zda je odpovídající datum svátek nebo ne.
Část 3: Počítání dnů mezi dvěma dny kromě víkendů a svátků v aplikaci Excel
V části 1 jsme uvedli všechny svátky v daném roce a nyní vás tato metoda provede počítáním počtu dnů kromě všech víkendů a svátků v časovém období.
Vyberte prázdnou buňku, kterou vrátíte počet dní, a zadejte vzorec = NETWORKDAYS (E1, E2, B2: B10) do něj a stiskněte vstoupit klíč.
Poznámka: Ve výše uvedených buňkách je E1 počátečním datem zadaného časového období, E2 je koncovým datem a B2: B10 je seznam svátků, který počítáme v části 1.
Nyní získáte počet dní kromě víkendů a svátků ve stanoveném časovém období.
Kopírujte vzorce přesně / staticky bez změny odkazů na buňky v aplikaci Excel
Kutools pro Excel Přesná kopie nástroj vám pomůže snadno kopírovat více vzorců přesně bez změny odkazů na buňky v aplikaci Excel, což zabrání automatické aktualizaci relativních odkazů na buňky.
Kutools pro Excel - Supercharge Excel s více než 300 základními nástroji. Užijte si plnohodnotnou 30denní zkušební verzi ZDARMA bez nutnosti kreditní karty! Get It Now
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
