Формирование дат в формате Excel в табеле

В табеле компоненты даты (день, месяц и год) указаны в отдельных ячейках. Чтобы в последующем мы имели возможность путем сопоставления дат определить, является ли день праздничным, необходимо для каждого дня сформировать дату в формате Excel. В этом нам опять-таки поможет модуль формирования даты. Скопируйте ячейки С6 и С7 модуля и вставьте их, установив табличный курсор в ячейку С8 рабочего листа Табель. В формулах ячеек С8 и С9 произведите такую замену:
— адрес ячейки С5 замените именем Год;
— адрес ячейки С7 — именем Месяц;
— адрес ячейки С6 — именем День.
Для вставки имени нужно выделить в формуле адрес ячейки и выполнить команду Вставка/Имя/Вставить или нажать клавишу [F3]. В результате появится диалоговое окно Вставка имени , в котором следует выбрать нужное имя, а затем нажать кнопку ОК.
В результате замены в ячейке С8 появится следующая формула:

=ЕСЛИ(Месяц="Май";5;ЕСЛИ(Месяц="Июнь";6;ЕСЛИ(Месяц="Июль";7;
ЕСЛИ(Месяц="Август";8; ЕСЛИ (Meсяц=''Сентябрь";9; 
ЕСЛИ(Месяц="Октя6рь";10; ЕСЛИ(Месяц="Ноябрь";11; 
ЕСЛИ(Месяц="Декабрь";12; "Проверьте внесенный текст!"))))))))

Формула в ячейке С9 приобретет вид

=ДАТА(Год;ЕСЛИ(Месяц="Январь";1;ЕСЛИ(Месяц="Февраль";2; 
ЕСЛИ(Месяц="Март";3;ЕСЛИ(Месяц="Апрель";4;С8))));День)

Теперь в ячейке С9 должна формироваться дата в формате Excel. Например: 1 января 2002 года — 01.01.02.
Скопируйте формулы из ячеек С8 и С9 в диапазон C8:AG9.
Определение праздничных дней в табеле
Чтобы выявить в табеле праздничные дни, нужно проверить, присутствуют ли в перечне праздничных дней на листе Праздники даты, сформированные в ячейках C9:AG9, Эту задачу легко выполнить при помощи функции ВПР, которая принадлежит к категории Ссылки и массивы .
Функция ВПР ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе. Возвращает она значение из той же строки, но находящееся в столбце, который задан в третьем аргументе. Синтаксис функции имеет такой вид:
ВПР(искомое_значение;инфо_таблица;номер_столбца;интервальный_просмотр)
Здесь искомое_значение — это искомое значение. Им может быть число, ссылка или текстовая строка. Аргумент инфо_таблица — это таблица, в которой производится поиск данных. Значения в первом ее столбце могут быть текстовыми строками, числами или логическими значениями. Регистр при поиске не учитывается (то есть строчные и заглавные буквы не различаются). Аргумент номер_ столбца — это номер столбца в диапазоне инфо_таблица, из которого выбирается возвращаемое значение. Аргумент интервальный_просмотр — это логическое значение, которое определяет, должна ли функция ВПР искать точное соответствие. Если он не задан, то ищется точное соответствие. При отсутствии искомого значения возвращается ошибка #H/Д
Сначала создадим в произвольном месте листа ряд простых формул, осуществляющих поиск даты и анализ полученной информации. Затем мы произведем операцию вложения формул и перенесем результирующую формулу в область табеля. Начнем с ячейки С25.
1. Активизируйте ячейку С25 и отобразите панель функции ВПР.
2. Убедитесь в том, что курсор установлен в поле Искомое_значение, и выделите ячейку С9.
3. Поместите курсор в поле Табл_массив.
4. Ввод имени диапазона ДатаПраздн в поле панели функций можно произвести с клавиатуры или в диалоговом окне Вставка имени. Для вызова диалогового окна следует выполнить команду Вставка/Имя/Вставить или нажать функциональную клавишу [F3]. Выделите в поле Имя элемент ДатаПраздн и нажмите кнопку ОК.
5. В поле Номер_индекса_столбца введите значение 1, а в поле Диапазон_просмотра — значение ЛОЖЬ.
6. В завершение нажмите кнопку ОК.
Результат поиска может оказаться некорректным, если диапазон ячеек, указанный в функции ВПР, находится на другом рабочем листе. Поэтому желательно действовать следующим образом:
— В поле Табл_массив окна функции ВПР введите ссылку на диапазон Праздники!$С$3:$С$18:
  =ВПР (С9;Праздники!SC53:$C$18;1;ЛОЖЬ)
— После ввода формулы присвойте диапазону Праздники!$С$3:$С$18 имя ДатаПраздн.
— Активизируйте ячейку С26 и замените в формуле адрес диапазона соответствующим именем.
Формула в ячейке С25 будет следующей:

=ВПР(С9;ДатаПраздн;1;ЛОЖЬ)

Если функция ВПР не найдет в списке с праздниками указанную дату, она выдаст ошибку #Н/Д. Это значение попадет в последующие формулы, и они, в свою очередь, также возвратят ошибку #Н/Д. Нам же нужно получить либо значение ИСТИНА, если день, указанный в табеле, является праздничным, либо значение ЛОЖЬ, если этот день не числится в списке праздников.
Избавиться от ошибки #Н/Д позволяет функция ЕНД , которая принадлежит к категории Проверка свойств и значений. Данная функция возвращает значение ИСТИНА, если значение предыдущих вычислений является ошибочным. Функция ЕНД имеет такой синтаксис:

ЕНД(Значение)

Активизируйте ячейку С26 и, загрузив панель функции ЕНД, введите в поле Значение ссылку на ячейку С25. По завершении операции вы должны получить такую формулу:

=ЕНД(С25)

Однако ее результат нас не совсем устраивает. Нам нужно, чтобы в случае отсутствия даты в списке праздников возвращалось значение ЛОЖЬ, а не ИСТИНА.
Поэтому воспользуемся логической функцией НЕ , которая относится к категории Логические и имеет следующий синтаксис:

НЕ(логическое_значение)

Здесь логическое_значение — это значение или выражение, в результате вычисления которого получается значение ИСТИНА или ЛОЖЬ. Если параметр логиче-ское_значение имеет значение ЛОЖЬ, функция НЕ возвращает значение ИСТИНА, в противном случае данная функция возвращает значение ЛОЖЬ.
Активизируйте ячейку С27 и загрузите панель функции НЕ. Укажите в поле Флаг панели адрес ячейки С26. Вот формула, которая должна быть в ячейке С27:

=НЕ(С26)

Проанализируем созданные формулы. Если дата в формате Excel, указанная в ячейке С9, найдена в списке праздников на рабочем листе Праздники, ячейка С27 будет содержать значение ИСТИНА. Когда дата не найдена, функция ВНР возвращает значение #Н/Д, а функция ЕНД исправляет ошибку, выдавая значение ИСТИНА. Функция НЕ инвертирует это значение, вследствие чего мы получим в ячейке С27 значение ЛОЖЬ.
Определение выходных дней в табеле
Для определения выходных дней (суббот и воскресений) воспользуйтесь функцией ДЕНЬНЕД, которая устанавливает номер дня недели. Эту функцию введите в ячейку С28:
=ДЕНЬНЕД(С9)
В следующую ячейку — С29 — введите функцию ЕСЛИ:
=ЕСЛИ(С28=7;ИСТИНА;ЕСЛИ(С28=1;ИСТИНА;ЛОЖЬ))
Функция ЕСЛИ действует по описанной ниже схеме:
— День недели, определенный в ячейке С28, — суббота?
— Если да, возвращается значение ИСТИНА.
— Если нет, переходим к следующему ЕСЛИ.
— Если днем недели является воскресенье, возвращается значение ИСТИНА.
— В противном случае возвращается значение ЛОЖЬ.
Учет выходных и праздничных дней
На следующем этапе нам предстоит создать формулу, которая при соблюдении одного из условий (праздничный день, выходной день или праздничный день, выпадающий на выходной) возвращает значение ИСТИНА. Мы предполагаем разместить эту формулу в ячейке СЗО.
Для решения задачи воспользуемся логической функцией ИЛИ. Эта функция возвращает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.
Функция ИЛИ имеет следующий синтаксис:
ИЛИ(логическое_значение1;логическое_значение2; …)
Здесь логииеское_значение1, логическое_значение2, … — это от 1 до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.
В первом аргументе нам необходимо указать адрес ячейки С29, в которой определяется день недели, а во втором аргументе — адрес ячейки С27, где устанавливаются даты праздников.
Формула в ячейке СЗО должна быть такой:
=ИЛИ(С29;С27)

Произведем вложение функций:
- Формулу из ячейки С25 вложим в формулу из ячейки С26:
   =ЕНД(ВПР(С9;ДатаПраздн;1;ЛОЖЬ))
- Полученную формулу вложим в формулу из ячейки С27:
   =НЕ(ЕНД(ВПР(С9;ДатаПраздн;1;ЛОЖЬ)))
- Формулу из ячейки С28 вложим в формулу из ячейки С29:
   =ЕСЛИ(ДЕНЬНЕД(С9)=7/ИСТИНА;ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ))
- Полученную формулу вложим в формулу из ячейки СЗО:
   =ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7;:ИСТИНА;ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ)) ;С27)