Cамоучитель MS Excel → Электронный табель учета рабочего времени → Определение праздничных дней в табеле

Определение праздничных дней в табеле

Чтобы выявить в табеле праздничные дни, нужно проверить, присутствуют ли в перечне праздничных дней на листе Праздники даты, сформированные в ячейках C9:AG9, Эту задачу легко выполнить при помощи функции ВПР, которая принадлежит к категории Ссылки и массивы (рис. 7.8).

Macтер функций, категория Ссылки и массивы

Рис. 7.8. Macтер функций, категория Ссылки и массивы

Панель функции ВПР

Рис. 7.9. Панель функции ВПР

Функция ВПР ищет значение, заданное в первом аргументе, в крайнем левом столбце диапазона, указанного во втором аргументе. Возвращает она значение из той же строки, но находящееся в столбце, который задан в третьем аргументе. Синтаксис функции имеет такой вид: 

ВПР(искомое_значение;инфо_таблица;номер_столбца;интервальный_просмотр)

Здесь искомое_значение - это искомое значение. Им может быть число, ссылка или текстовая строка. Аргумент инфо_таблица - это таблица, в которой производится поиск данных. Значения в первом ее столбце могут быть текстовыми строками, числами или логическими значениями. Регистр при поиске не учитывается (то есть строчные и заглавные буквы не различаются). Аргумент номер_ столбца - это номер столбца в диапазоне инфо_таблица, из которого выбирается возвращаемое значение. Аргумент интервальный_просмотр - это логическое значение, которое определяет, должна ли функция ВПР искать точное соответствие. Если он не задан, то ищется точное соответствие. При отсутствии искомого значения возвращается ошибка #H/Д

Сначала создадим в произвольном месте листа ряд простых формул, осуществляющих поиск даты и анализ полученной информации. Затем мы произведем операцию вложения формул и перенесем результирующую формулу в область табеля. Начнем с ячейки С25.

  1. Активизируйте ячейку С25 и отобразите панель функции ВПР (рис.7.9).
  2. Убедитесь в том, что курсор установлен в поле Искомое_значение, и выделите ячейку С9.
  3. Поместите курсор в поле Табл_массив.
  4. Ввод имени диапазона ДатаПраздн в поле панели функций можно произвести с клавиатуры или в диалоговом окне Вставка имени. Для вызова диалогового окна следует выполнить команду Вставка/Имя/Вставить или нажать функциональную клавишу [F3]. Выделите в поле Имя элемент ДатаПраздн и нажмите кнопку ОК.
  5. В поле Номер_индекса_столбца введите значение 1, а в поле Диапазон_просмотра - значение ЛОЖЬ.
  6. В завершение нажмите кнопку ОК.

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

 =ВПР (С9;Праздники!SC53:$C$18;1;ЛОЖЬ)

Формула в ячейке С25 будет следующей: 

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

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

Избавиться от ошибки #Н/Д позволяет функция ЕНД (рис. 7.10), которая принадлежит к категории Проверка свойств и значений. Данная функция возвращает значение ИСТИНА, если значение предыдущих вычислений является ошибочным. Функция ЕНД имеет такой синтаксис:

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

Панель функции ЕНД

Рис. 7.10. Панель функции ЕНД

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

 =ЕНД(С25)

Однако ее результат нас не совсем устраивает. Нам нужно, чтобы в случае отсутствия даты в списке праздников возвращалось значение ЛОЖЬ, а не ИСТИНА.

Поэтому воспользуемся логической функцией НЕ (рис. 7.11), которая относится к категории Логические и имеет следующий синтаксис: 

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

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

Активизируйте ячейку С27 и загрузите панель функции НЕ. Укажите в поле Флаг панели адрес ячейки С26. Вот формула, которая должна быть в ячейке С27: 

 =НЕ(С26)

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

Панель функции HE

Рис. 7.11. Панель функции HE