Як перевірити, чи є дата святковим днем, і чи вважати дні, крім святкових днів, у Excel?
Наприклад, у вас є список дат і ви хочете перевірити, чи є в цьому списку якісь святкові дні, як ви могли це зробити швидко? У цій статті я покажу вам способи обчислення всіх дат американських святкових днів протягом певних років, а потім перевіряю, чи є ця дата американським святковим днем у Excel.
- Частина 1: Обчислити державні свята протягом певного року в Excel
- Частина 2: Перевірте, чи є дата в Excel святковим днем
- Частина 3. Підрахуйте дні між двома днями, крім вихідних та святкових днів у програмі Excel
Частина 1: Обчислити державні свята із заданим роком у Excel
Перш ніж перевіряти, чи є дата святковим днем, ви повинні перерахувати всі свята протягом певного року в Excel. Тому підготовка таблиці, як показано на наступному знімку екрану, полегшить вам роботу.
Є три види американських святкових свят:
(1) Перший вид - це державне свято у визначену дату, наприклад, Новий рік - 1 січня. ми можемо легко обчислити Новий рік за формулою = ДАТА (Даний рік, 1,1);
(2) Другий вид - це державне свято у встановлений робочий день, наприклад, День Президента. Ми можемо легко розрахувати день Президента за формулою = ДАТА (Даний рік, 1,1) + 14 + ВИБІР (ВИХОДНИЙ (ДАТА (Даний рік, 1,1)), 1,0,6,5,4,3,2);
(3) І останній вид - День пам’яті, ми можемо легко обчислити День пам’яті за формулою = ДАТА (Даний рік, 6,1) -ТИЖДЕНЬ (ДАТА (Даний рік, 6,6)).
Тут я перелічую формули для розрахунку всіх святкових днів у наступній таблиці. Просто введіть формули у відповідну комірку та натисніть Що натомість? Створіть віртуальну версію себе у клавіша по черзі.
| свято | Осередок | Формули |
| Новий рік | C2 | = ДАТА (C1,1,1) |
| День Мартіна Лютера Кінга | C3 | = ДАТА (C1,1,1) + 14 + ВИБІР (ВИХОДНИЙ (ДЕНЬ (C1,1,1)), 1,0,6,5,4,3,2) |
| День Президента | C4 | = ДАТА (C1,2,1) + 14 + ВИБІР (ВИХОДНИЙ (ДЕНЬ (C1,2,1)), 1,0,6,5,4,3,2) |
| День поминання | C5 | = ДАТА (C1,6,1) -ТИЖДЕНЬ (ДАТА (C1,6,6)) |
| день незалежності | C6 | = ДАТА (C1,7,4) |
| День праці | C7 | = ДАТА (C1,9,1) + ВИБІР (ВИХОДНИЙ (ДЕНЬ (C1,9,1)), 1,0,6,5,4,3,2) |
| День Колумба | C8 | = ДАТА (C1,10,1) + 7 + ВИБІР (ВИХОДНИЙ (ДЕНЬ (C1,10,1)), 1,0,6,5,4,3,2) |
| День ветеранів | C9 | = ДАТА (C1,11,11) |
| День подяки | C10 | = ДАТА (C1,11,1) + 21 + ВИБІР (ВИХОДНИЙ (ДЕНЬ (C1,11,1)), 4,3,2,1,0,6,5) |
| Різдво | C11 | = ДАТА (C1,12,25) |
Примітка: У формулах, наведених у наведеній вище таблиці, C1 - це еталонна комірка, яка визначає даний рік. У нашому прикладі це означає 2015 рік, і ви можете змінити його відповідно до своїх потреб.
За допомогою цих формул ви можете легко розрахувати дати святкових днів із заданими роками. Дивіться знімок екрана нижче:
Збережіть діапазон як запис автотексту (інші формати комірок та формули) для повторного використання в майбутньому
Посилання на комірки та застосування формул для розрахунку кожного свята має бути дуже нудним. Kutools для Excel пропонує гарний обхідний шлях Автотекст утиліта для збереження діапазону як запису автотексту, який може залишати формати комірок та формули в діапазоні. І тоді ви повторно використаєте цей діапазон лише одним клацанням миші. Робота стає простою, лише одним клацанням миші можна вставити цю таблицю та змінити рік у цій таблиці!
Kutools для Excel - Доповніть Excel понад 300 основними інструментами. Насолоджуйтесь повнофункціональною 30-денною БЕЗКОШТОВНОЮ пробною версією без кредитної картки! Get It Now
Частина 2: Перевірте, чи є дата в Excel святковим днем
Перерахувавши всі дати державних свят певних років, ми можемо легко перевірити, чи є дата святковим чи ні, за допомогою формул у Excel. Припустимо, у вас є список дат, як показано на наступному знімку екрана, і я представив способи, як легко це зробити.
Виберіть порожню клітинку, крім списку дат, говорить комірка B18, введіть формулу = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Свято", "Ні") в нього, а потім перетягніть маркер заповнення до потрібного діапазону. Дивіться знімок екрана вище:
примітки:
(1) У формулі = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No"), $ C $ 2: $ D $ 11 - діапазон державних свят у певні роки, і A18 - це клітина з датою, яку ви хочете перевірити, чи це державне свято, і ви можете змінити їх відповідно до своїх потреб. І ця формула поверне "Свято", якщо конкретна дата є святковим днем, і поверне "Ні", якщо це не так.
(2) Ви також можете застосувати цю формулу масиву = ЯКЩО (АБО (АБО ($ C $ 2: $ D $ 11 = A18), "Свято", "НІ") щоб перевірити, чи є відповідна дата святковим чи ні.
Частина 3. Підрахуйте дні між двома днями, крім вихідних та святкових днів у програмі Excel
У першій частині ми перерахували всі свята в певному році, і тепер цей метод допоможе вам підрахувати кількість днів, крім усіх вихідних та святкових днів у діапазоні дат.
Виберіть порожню комірку, для якої ви повернете кількість днів, і введіть формулу = МЕРЕЖІ (E1, E2, B2: B10) в неї та натисніть Що натомість? Створіть віртуальну версію себе у ключ
примітки: У клітинках вище E1 - дата початку зазначеного діапазону дат, E2 - дата закінчення, а B2: B10 - список відпусток, який ми обчислюємо в Частині 1.
Тепер ви отримаєте кількість днів, крім вихідних та святкових днів у вказаному діапазоні дат.
Копіюйте формули точно / статично, не змінюючи посилання на комірки в Excel
Kutools для Excel Точна копія Утиліта може допомогти вам легко точно скопіювати кілька формул, не змінюючи посилання на комірки в Excel, запобігаючи автоматичному оновленню відносних посилань на комірки.
Kutools для Excel - Доповніть Excel понад 300 основними інструментами. Насолоджуйтесь повнофункціональною 30-денною БЕЗКОШТОВНОЮ пробною версією без кредитної картки! Get It Now
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
