В этом примере мы расскажем, как вычислить в Excel дату праздника для любого года.
Прежде, чем мы начнем, давайте заострим внимание на 2-х моментах:
- Функция CHOOSE (ВЫБОР) возвращает значение, зависящее от порядкового номера позиции. Например: =CHOOSE(3,”Car”,”Train”,”Boat”,”Plane«) возвращает «Boat».
- Функция WEEKDAY (ДЕНЬНЕД) возвращает число от 1 (воскресенье) до 7 (суббота), представляющее номер дня недели.
На рисунке ниже вы видите, как выглядит готовая таблица. Если ввести год в ячейку C2, Excel выводит все праздники этого года. Конечно же, Новый год, День независимости, День ветеранов и Рождество рассчитать легко.
Все другие праздники можно описать подобным образом: n-й день месяца (за исключением Дня памяти, который немного отличается). Давайте взглянем на День благодарения 2015 года. Если вы поймёте, как вычислить День благодарения, то разберётесь и с остальными праздниками. День благодарения празднуется каждый четвёртый четверг ноября.
=DATE(C2,11,1)+21+CHOOSE(WEEKDAY(DATE(C2,11,1)),4,3,2,1,0,6,5) =ДАТА(C2;11;1)+21+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;11;1));4;3;2;1;0;6;5)
Календарь ниже поможет понять, когда будет День благодарения в 2015 году:
Пояснение:
- Выражение ДАТА(C2;11;1) — это 1 ноября 2015 года.
- Выражение ДЕНЬНЕД(ДАТА(C2;11;1)) возвращает 1, т.е. воскресенье.
- ВЫБOP(1;4;3;2;1;0;6;5) возвращает 4 (от воскресенья до четверга 4 дня).
- Получаем: 01.11.2015 + 21 + 4 = 26.11.2015
Примечание: Нам нужны 4 дополнительных дня, поскольку до первого четверга в ноябре не хватает 4 дня. К этой дате нужно добавить еще 21 день (3 недели), чтобы получить 4-й четверг ноября.
Теперь давайте взглянем на День Мартина Лютера Кинга, который отмечается ежегодно в третий понедельник января:
=DATE(C2,1,1)+14+CHOOSE(WEEKDAY(DATE(C2,1,1)),1,0,6,5,4,3,2) =ДАТА(C2;1;1)+14+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;1;1));1;0;6;5;4;3;2)
Эта формула почти идентична предыдущей. В этот раз:
- Функция DATE (ДАТА) возвращает 1-е января.
- В аргументах функции CHOOSE (ВЫБОР) позиция со значением 0 находится на втором месте в списке значений, т.к. мы ищем понедельник.
- Если 1 января выпадает на понедельник, получаем: 14 + ВЫБОР(2;1;0;6;5;4;3;2) = 14 + 0 = 14 дней (или 2 недели) до 3-го понедельника января.
Урок подготовлен для Вас командой сайта office-guru.ru Источник: https://www.excel-easy.com/examples/holidays.html Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel
