Перенос выходных дней

На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос .
Даты дней, которые вследствие переноса стали выходными, добавим в список праздников, находящийся в диапазоне A15:G16 листа Праздники .
Теперь в табеле нам нужна формула, которая будет определять, не перенесен ли данный выходной. Для ее получения доработаем формулу из ячейки С27 табеля.
Скопируйте ее из строки формул, вставьте в ячейку С31, замените имя Да-таПраздн именем ДатаПеренос и удалите функцию НЕ.
Результирующая формула в ячейке С31 должна иметь следующий вид:
=ЕНД(ВПР(С9;ДатаПеренос;1;ЛОЖЬ))
Формула возвращает значение ЛОЖЬ, если находит дату среди перенесенных, или значение ИСТИНА, если не находит. Таким образом, эта ячейка будет содержать значение ЛОЖЬ, если день вследствие переноса стал рабочим. Если же ячейка содержит значение ИСТИНА, значит, статус дня не изменился.
Теперь мы должны проанализировать значения ячеек С3О и С31. Первая содержит логическое значение, которое указывает на то, является ли день выходным либо праздничным. Логическое значение во второй ячейке отражает факт изменения статуса дня (то есть не стал ли он вследствие переноса выходных рабочим днем).
Формула для анализа должна работать так. День является выходным, если его статус не изменился (С31 содержит значение ИСТИНА), и он является праздничным, субботним или воскресным (СЗО содержит значение ИСТИНА). Во всех остальных случаях день будет рабочим.
Составить такую формулу можно с помощью функции И. Она возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Данная функция принадлежит к категории Логические и имеет следующий синтаксис:
И(логическое_значение1;логическое_значение2; …)
Здесь логическое_значение1, логическое_значение2, … — это от I до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.
Введите в ячейку С32 такую функцию:
=И(С30;С31)
В данную функцию надлежит вложить формулы из ячеек СЗО и С31. Ниже показано, как выглядит результирующая формула в ячейке С32:
=И(ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7; ИСТИНА;
ЕСЛИ(ДБНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ));С27); ЕНД(ВПР(А9;ДатаПеренос;1;ЛОЖЬ)))
Скопируйте формулы из ячеек С27 и С32 в ячейки С10 и С11.
Определение часов, отработанных в выходные и праздничные дни
В нашем табеле количество часов, отработанных в выходные и праздничные дни, определяется в ячейке AJ12. Эта задача решается с помощью следующей формулы:

=СУММЕСЛИ(C$11:AG12;ИСТИНА;C12:AG12)

В формуле используется функция СУММЕСЛИ. Эта функция аналогична функции СУММСЧЕТ, но последняя подсчитывает количество ячеек, которые соответствуют заданному критерию, а функция СУММЕСЛИ суммирует значения ячеек, если их содержимое соответствует указанному критерию. Проанализируем синтаксис функции СУММЕСЛИ:

СУММЕСЛИ(интервал;критерий;сумм_интервал)

Здесь интервал — это диапазон ячеек, задействованных в вычислениях, а критерий может быть числом, выражением или текстом, по которому программа определяет, следует ли добавлять содержимое данной ячейки.
Аргумент сумм_интервал — это фактические ячейки для суммирования. В диапазоне сумм_интервал суммируются только те ячейки, которые находятся в той же строке или столбце, что и ячейки, заданные в аргументе интервал и удовлетворяющие критерию. Если аргумент сумм_интервал опущен, суммируются значения ячеек, заданных аргументом интервал.
В табеле описываемая функция работает по следующей схеме. В ячейке AJ12 суммируются значения, находящиеся в ячейках области C12:AG12, но только в том случае, если в ячейке соответствующего столбца области Cl I :AG11 находится значение ИСТИНА (то есть день является выходным).
Удобочитаемость табеля: управление отображением строк и столбцов
Обеспечить максимальное удобство работы с табелем можно за счет удаления с экрана его отдельных элементов. Так, после ввода формул желательно скрыть строки 7:11, чтобы они не портили вид табеля. Для этого выделите строки 7:11, откройте контекстное меню и активизируйте в нем команду Скрыть. Аналог указанного средства — команда Формат/Строка/Скрыть. Подобным образом отменяется и отображение столбцов.
Восстановить отображение скрытых элементов, столбцов и строк, несложно. Достаточно выделить столбцы или строки, граничащие со скрытыми, и, вызвав контекстное меню, активизировать команду Отобразить. Альтернативный способ — выполнение команды Формат/Столбец (Строка)/Отобразить.
Защита листа
В процессе заполнения таблиц, подобных нашей, существует риск испортить введенные формулы. В Excel предусмотрена защита формул от несанкционированного доступа. Для ее обеспечения выполните следующие действия:
1. Выделите всю область рабочего листа.
2. Откройте диалоговое окно Формат ячеек и перейдите на вкладку Защита .
3. Убедитесь в том, что установлен флажок Защищаемая ячейка. Это установка по умолчанию. Также надлежит активизировать флажок Скрыть формулы. Нажмите кнопку ОК.
4. Выделите диапазон ячеек, в которые будут вноситься данные. На рабочем листе Табель таковым является диапазон C12:AG23.
5. Откройте повторно вкладку Защита диалогового окна Формат ячеек.
6. Отключите флажки Защищаемая ячейка и Скрыть формулы.
7. Чтобы обеспечить защиту рабочего листа, вызовите команду Сервис/Защита/Защитить рабочий лист и в появившемся диалоговом окне Защитить лист нажмите кнопку ОК. В указанном окне можно ввести пароль, если это необходимо. После нажатия кнопки ОК будет установлена защита для всех областей рабочего листа, где производится расчет.
8. При вводе пароля Excel попросит подтвердить его в появившемся диалоговом окне Подтверждение пароля.
В случае попытки ввести значения в защищенную область Excel выдаст сообщение о том, что изменить содержимое ячеек невозможно. Отменить защиту листа позволяет команда Сервис/Защита/Снять защиту листа.
Заполнение табеля

Рассмотрим процесс заполнения табеля методом регистрации отклонений. Начнем с того, что в строке списка сотрудников, отведенной для Иванова Ивана Ивановича, отметим все выходные дни и праздники. Для этого введем в соответствующие ячейки букву "в". Изначально мы будем исходить из того, что данный сотрудник отработает весь месяц, поэтому введем цифры "8" (продолжительность рабочего дня) в ячейки, соответствующие рабочим дням. Для их заполнения воспользуемся методом копирования. Заполнив область ввода для одного сотрудника, скопируем те же данные в области других работников. Это облегчит дальнейшую работу: мы сможем ограничиться редактированием отдельных значений, применяя метод регистрации отклонений (например, если человек в какой-либо день болел, восьмерку можно заменить буквой "б").