Прежде чем проверять, является ли дата государственным праздником, вы должны указать в Excel все праздники в течение данных лет. Поэтому подготовка таблицы, как показано на следующем снимке экрана, облегчит вашу работу.

Примечание: В формулах в приведенной выше таблице C1 - это ссылочная ячейка, которая определяет данный год. В нашем примере это означает 2015 год, и вы можете изменить его в зависимости от ваших потребностей.

С помощью этих формул вы можете легко рассчитать даты государственных праздников с заданными годами. См. Снимок экрана ниже:

Сохраните диапазон как запись автотекста (остальные форматы ячеек и формулы) для повторного использования в будущем

Должно быть очень утомительно ссылаться на ячейки и применять формулы для расчета каждого праздника. Kutools for 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); «Праздник»; «НЕТ») чтобы проверить, является ли соответствующая дата праздником или нет.

В Части 1 мы перечислили все праздники в данном году, и теперь этот метод поможет вам подсчитать количество дней, кроме всех выходных и праздничных дней в диапазоне дат.

Выберите пустую ячейку, вы вернете количество дней, и введите формулу = ЧИСТРАБДНИ (E1; E2; B2: B10) в него и нажмите Enter .

Внимание: В ячейках выше E1 - это дата начала указанного диапазона дат, E2 - дата окончания, а B2: B10 - это список выходных, который мы вычисляем в Части 1.

Теперь вы получите количество дней, кроме выходных и праздников, в указанном диапазоне дат.

Копировать формулы точно / статически без изменения ссылок на ячейки в Excel

Kutools for Excel Точная копия Утилита может помочь вам легко скопировать несколько формул без изменения ссылок на ячейки в Excel, предотвращая автоматическое обновление относительных ссылок на ячейки.