Текстовый формат ячейки в Excel
Стоит обратить внимание на один существенный момент. Если вы вводите в ячейку значение "01", а в ней появляется "1", значит, формат ячейки является числовым, а не текстовым. Его нужно поменять. Иначе формула работать не будет.
Выделяем нужную ячейку, вызываем правой кнопкой мыши меню и выбираем "Формат ячеек". В закладке "Число" выбираем "текстовый" формат.
После смены формата нужно еще раз ввести значение в ячейку.
Если нужно сменить формат сразу во всех ячейках поля, выделить нужно все эти поля и поменять формат одновременно во всех с помощью той же функции.
Н а заметку: если после смены формата формулы не работают, значит, в ваших параметрах обновление стоит в ручном режиме. В этом случае необходимо обновить ячейку с формулой вручную (щелкнуть по ней два раза и нажать "enter") либо нажать кнопку F9, которая одновременно обновляет весь лист Excel. Если вы хотите настроить автоматическое обновление страницы (а это просто необходимо сделать в нашем случае, иначе каждый раз придется вручную обновлять все ячейки), зайдите в "Сервис" => "Параметры" => "Вычисления" и выберите вариант "автоматически":
Итак, мы ввели в ячейку одно условие. Но как быть со всем остальным списком обозначения дней?
Все довольно-таки просто! В строке состояния копируем все выражение кроме знака "=",
ставим ";" перед последней скобкой выражения и вставляем после него скопированное выражение:
Исправляем во втором выражении "01" на "03" (работа в выходной день):
Готово! Смысл этого выражения будет звучать так: Если значение ячейки D8 соответствует “01”, то значение ячейки AV8 нужно считать за 1, если нет, то действует новое условие: [если значение ячейки D8 соответствует «03», то значение ячейки AV8 нужно считать за «1»]. И так со всеми последующими условиями.
Теперь добавим любой код дня неявки. Скопируем второе выражение, сразу захватив ";":
и вставим опять же перед всеми скобками:
Исправляем "03" на "16" (отпуск за свой счет), и "1" на "0", чтобы этот день не учитывался как отработанный:
Вот и всё! Все остальные коды проставляем аналогичным образом.
У чтем, что логический оператор "ЕСЛИ" позволяет использовать не более 7-ми аргументов, поэтому если количество используемых вами кодов больше 7-ми, можно проставить коды только для явочных дней. Все остальные коды Excel в любом случае посчитает как "0".
Копируем полученную формулу и вставляем во все ячейки, которые соответствуют ячейкам Табеля учета рабочего времени:
Теперь можно считать итоговые значения. Выделяем ячейку с итоговой суммой за первую половину месяца и с помощью автосуммы складываем значения, полученные в поле справа от Табеля учета рабочего времени:
То же самое делаем со второй половиной месяца.
Далее считаем количество часов за каждую половину месяца. Можно сделать это с помощью автосуммы, т.к. в ячейках, где указаны часы, применен числовой формат:
Однако на практике можно столкнуться с тем, что при работе с табелем (копирование, вставка, вырезание ячеек) происходит сбой формата ячеек. Поэтому отметим одну особенность подсчета суммы в Excel.
Д ля того, чтобы сосчитать значения в любом формате нужно пользоваться ручным суммированием. Автосумма считает только значения в числовом формате.
Используем в нашем табеле эту особенность:
Теперь складываем количество дней за первый и второй период, потом - количество часов.
Для этого активизируем ячейку, в которой хотим создать формулу, и пишем в ней БЕЗ ПРОБЕЛОВ соответствующие знаки или активизируем (выделяем левой клавишей мыши) соответствующие ячейки в следующем порядке:
= [Количество дней(часов) за 1 половину месяца] + [Количество дней(часов) за 2 половину месяца] ENTER
При желании можно автоматизировать столбец "Количество неявок". Для этого вне поля Табеля учета рабочего времени выделим ячейку, в которой будем проставлять количество календарных дней в текущем месяце.
С помощью абсолютной ссылки посчитаем дни неявки:
и часы неявки по формуле:
= [Количество дней в месяце] * [Количество часов в день] - [Отработанное количество часов] ENTER
Все полученные формулы копируем и вставляем в соответствующие ячейки табеля до конца списка сотрудников.
Все, что можно было автоматизировать, мы автоматизировали. Теперь можно скрыть столбцы, которые находятся справа от Табеля учета рабочего времени.
Табель с числовыми кодами готов (СКАЧАТЬ)! Пользуйтесь на здоровье.
Для создания кода с буквенным кодами (СКАЧАТЬ) нужно заменить соответствующие цифровые коды на буквенные.
Если вы найдете способ автоматизировать оставшиеся поля, напишите мне по адресу: offisny@mail.ru
Остался один маленький нюанс. В случае, если количество сотрудников превышает число строк готового табеля, необходимо скопировать готовый лист и добавить столько его копий в файл, сколько необходимо для внесения всех сотрудников.