Например, у вас есть список дат и вы хотите проверить, есть ли в нем какие-либо государственные праздники, как бы вы могли это сделать быстро? В этой статье я покажу вам способы расчета всех дат американских государственных праздников в течение заданных лет, а затем легко проверю, является ли дата американским государственным праздником в Excel.
- Часть 1. Расчет государственных праздников в определенные годы в Excel
- Часть 2. Проверка того, является ли дата выходным днем в Excel
- Часть 3. Подсчет дней между двумя днями, кроме выходных и праздников, в Excel
Вкладка Office Включает редактирование и просмотр с вкладками в Office и делает вашу работу намного проще … Подробнее … Бесплатная загрузка … Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстов особенности: Извлечь номер из текста S tring; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Подробнее … Бесплатная загрузка …
->
Содержание
- Часть 1: Расчет государственных праздников для данного года в Excel
- Сохраните диапазон как запись автотекста (оставшиеся форматы ячеек и формулы) для повторного использования в будущем
- Часть 2: Проверьте, является ли дата государственным праздником в Excel
- Часть 3: Подсчет дней между двумя днями, кроме выходных и праздников, в Excel
- Точное/статическое копирование формул без изменения ссылок на ячейки в 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)).
Здесь я перечисляю формулы для расчета всех государственных праздников в следующей таблице. Просто введите формулы в соответствующую ячейку и одну за другой нажимайте клавишу Enter .
| Holiday | Cell | Формулы |
| Новый год | C2 | = ДАТА (C1,1,1) |
| День Мартина Лютера Кинга | C3 | = ДАТА (C1,1,1) + 14 + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (C1,1,1)), 1,0,6,5,4,3,2) |
| День президента | C4 | = DATE (C1,2,1) + 14 + CHOOSE (WEEKDAY (DATE (C1, 2,1)), 1,0,6,5,4,3,2) |
| День памяти | C5 | =DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6)) |
| День Независимости | C6 | =DATE(C1,7,4) |
| День труда | C7 | = DATE (C1,9,1) + CHOOSE (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4 , 3,2) |
| День Колумба | C8 | = DATE (C1,10 , 1) + 7 + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (C1,10,1)), 1,0,6,5,4,3,2) |
| День ветеранов | C9 | =DATE(C1,11,11) |
| День Благодарения | C10 | = DATE (C1,11,1) + 21 + CHOOSE (WEEKDAY (DATE (C1,11,1)), 4,3,2,1 , 0,6,5) |
| Рождество | C11 | = DATE (C1 , 12,25) |
Примечание. В формулах в приведенной выше таблице C1 является ссылочной ячейкой. который определяет данный год. В нашем примере это означает 2015 год, и вы можете изменить его в зависимости от ваших потребностей.
С помощью этих формул вы можете легко вычислить даты государственных праздников с заданными годами. См. Снимок экрана ниже:
Сохраните диапазон как запись автотекста (оставшиеся форматы ячеек и формулы) для повторного использования в будущем
Ссылка на ячейки и применение формул для расчета каждого праздника должно быть очень утомительным. Kutools for Excel предоставляет симпатичный обходной путь утилиты AutoText , чтобы сохранить диапазон как запись автотекста, который может сохранить форматы ячеек и формулы в диапазоне. И тогда вы сможете повторно использовать этот диапазон одним щелчком мыши. Работа становится легкой: достаточно одного щелчка мышки, чтобы вставить эту таблицу и изменить год в этой таблице! Полнофункциональная бесплатная пробная версия на 30 дней!
Kutools for Excel – включает более 300 удобных инструментов для Excel.. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас
Часть 2: Проверьте, является ли дата государственным праздником в Excel
После перечисления всех дат государственных праздников определенных лет мы можем легко проверить, является ли дата государственным праздником или нет, с помощью формул в Excel . Предположим, у вас есть список дат, показанный на следующем снимке экрана, и я расскажу, как это легко сделать.
Выберите пустую ячейку помимо списка дат, – говорит ячейка B18. введите в нее формулу = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 2: $ D $ 11, A18), «Праздник», «Нет»), а затем перетащите маркер заполнения в нужный диапазон. См. Снимок экрана выше:
Примечания :
(1) В формуле = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 2: $ D $ 11, A18), «Праздник», «Нет»), $ C $ 2: $ D $ 11 – это диапазон государственных праздников в определенные годы, а A18 – это ячейка с датой, которую вы хотите проверить, является ли это государственным праздником, и вы можете изменить их в зависимости от ваших потребностей. И эта формула вернет «Праздник», если конкретная дата является государственным праздником, и «Нет», если это не так.
(2) Вы также можете применить эту формулу массива = ЕСЛИ (ИЛИ ( $ C $ 2: $ D $ 11 = A18), «Праздник», «НЕТ»), чтобы проверить, является ли соответствующая дата выходным или нет.
Часть 3: Подсчет дней между двумя днями, кроме выходных и праздников, в Excel
В Части 1 мы перечислили все праздники в данном году, и теперь этот метод поможет вам подсчитать количество дней, кроме всех выходных и праздничных дней, в диапазоне дат.
Выберите пустую ячейку, вы вернете количество дней, и введите в нее формулу = ЧИСТРАБДНИ (E1, E2, B2: B10) и нажмите клавишу Enter .
Примечание : в ячейках выше E1 – это дата начала указанного диапазона дат, E2 – дата окончания, а B2: B10 – это список праздников, который мы вычисляем в Части 1.
Теперь вы получите количество дней, кроме недели. заканчивается и праздники в указанном диапазоне дат.
Точное/статическое копирование формул без изменения ссылок на ячейки в Excel
Kutools for Excel Утилита Exact Copy может помочь вам легко скопировать несколько формул без изменения ссылок на ячейки в Excel, предотвращая автоматическое обновление относительных ссылок на ячейки. 30-дневная бесплатная пробная версия полнофункциональной версии!
Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас
Источник: