Однострочный табель

Характеристика и круг задач
Однострочный табель предназначается для использования на предприятиях, где не ведутся работы в ночное время, а также в праздничные и выходные дни. С помощью однострочного табеля должно быть обеспечено решение следующих задач:
— Автоматическое определение нормативного количества рабочих часов.
— Определение количества календарных дней в месяце.
— Определение коэффициента для начисления заработной платы в зависимости от отработанного времени.
— Вывод сообщений в случае возникновения ошибок при вводе.
Структура однострочного табеля (область ввода и расчетная область) показана на следующих двух рисунках.
Определение нормативного количества рабочих часов
Нормативное количество рабочих часов для конкретного месяца будет указано в ячейке D5, а количество календарных дней в этом месяце — в ячейке D6 табеля. Эти данные будут браться из специальной таблицы в области B29:D42 рабочего листа Праздники . Но эту таблицу необходимо еще создать.
Итак, в ячейке D5 рабочего листа Табель формула должна иметь такой вид:

=ВПР(Месяц;Праздники!В30:С41;2;ЛОЖЬ)

а в ячейке D6 — такой:

=ВПР(Месяц;Праздники!В30:D41;3;ЛОЖЬ)

В процессе вычисления представленных формул на основе названия месяца, введенного в ячейке ААЗ рабочего листа с однострочным табелем, производится поиск названия месяца на рабочем листе Праздники, после чего возвращается нормативное количество рабочих часов либо число календарных дней.
Заполнение области ввода
На примере разработки однострочного табеля продемонстрируем, как быстро и без механических ошибок заполнить ячейки, в которых должны находиться Ф.И.О. и табельные номера. Исходным файлом послужит табличная база данных сотрудников.
Связывание книг
Вы уже знаете, что при вводе формул образуются зависимые и влияющие ячейки. На ход расчетов может повлиять содержимое других ячеек — при наличии в формуле ссылок на них. Цель создания ссылок заключается в использовании содержащейся в ячейках информации. Рассматривавшиеся ранее таблицы размещались на одном рабочем листе. Если же ячейку, содержащую ссылку, перенести на другой лист или в другую книгу, ссылка не разорвется и мы получим связанные посредством формул рабочие листы или книги.
Таким образом, вместо копирования данных из одной книги в другую можно просто создавать ссылки на ячейки другой книги. Воспользуемся этим методом для ввода в табель фамилий и табельных номеров работников.
Для удобства при создании связи между файлами (книгами) разместим их в одной папке. Создайте папку под названием Табель и поместите в нее файлы Табель и База (с базой данных сотрудников).
Заполнение ячеек с названиями должностей и табельными номерами
Название должности каждого работника будет извлекаться из книги База. Реализуется это следующим образом:
1. Откройте две книги, между которыми будет установлена связь. Желательно расположить их в одном окне посредством команды Окно/Расположить.
2. Выделите диапазон ячеек D9:D18 и введите знак равенства.
3. Перейдите в книгу База и выделите ячейку G4. При появлении ссылки на другую книгу автоматически создается абсолютная ссылка. Для преобразования ее в относительную следует нажать три раза функциональную клавишу [F4].
4. В завершение нажмите комбинацию клавиш [Enter+Ctrl].
В результате диапазон D9:D18 будет заполнен такими формулами, как в ячейке D9:
=[База.хls]Сотрудники!G4
Ссылка между открытыми книгами имеет такую структуру:
[Имя_книги]Имя_листа!Адрес_ячейки
После закрытия книги, с которой установлена связь, ссылка изменится: в ней будет указан полный путь, по которому находится исходная информация:
=’С:\Отдел кадров\Табель\[База.xls]Сотрудники’!В2
ПРИМЕЧАНИЕ

При наличии пробелов в именах листов, книг или папок весь путь автоматически заключается в одинарные кавычки.

Для создания ссылки на ячейку другой книги путь можно набрать с клавиатуры, но это занимет много времени. Преимущество данного метода лишь в том, что нет необходимости открывать книгу, с которой устанавливается связь.
Для получения ссылок на табельные номера и фамилии установим связь. Например, для ячейки С9 (табельный номер) выполните следующие действия:
1. Выделите диапазон ячеек С9:С18 и введите знак равенства.
2. Перейдите в книгу База и выделите ячейку В2.
3. Нажмите комбинацию клавиш [Enter+Ctrl].
В результате формула в ячейке С9 будет иметь такой вид:
= [База.xls]Сотрудники!В2
Аналогичными формулами будет заполнен весь диапазон ячеек С9:С18.
Автоматический ввод Ф.И.О.
Формула в ячейке В9 возвращает фамилию, которая находится в ячейке С2 рабочего листа Сотрудники, и инициалы, которые берутся из ячеек D2 и Е2. Она также обеспечивает расстановку между ними пробелов и точек:
=СЦЕПИТЬ([База.xls]Сотрудники!С2;" ";
ЛЕВСИМВ([База.xls]Сотрудники!D2;1);"  ";
ЛЕВСИМВ([База.xls]Сотрудники!Е2;1))
Действие этой формулы сводится к следующему: из базы данных извлекается полная фамилия, а от имени и отчества отсекаются первые буквы, после которых ставятся точки. Кроме того, перед отчеством (или после имени) добавляется пробел.
Введем сначала простые формулы, которые впоследствии будут соединены. В ячейке В10 должна находиться формула, которая будет извлекать фамилию:
=[База.xls]Сотрудники!С2
Первую букву имени поместим в ячейку ВИ. Для этого занесем в нее такую формулу:
=ЛЕВСИМВ([База.xls]Сотрудники!D2;1)
ПРИМЕЧАНИЕ

Из панели функций можно сослаться на другие листы или книги точно так же, как из ячеек. Для этого достаточно поместить курсор в поле ввода панели функций и выделить ячейку на другом листе или в другой книге.

В ячейку В12 введите формулу, приведенную ниже. Она будет извлекать первую букву отчества.
=ЛЕВСИМВ([База.xls]Сотрудники!Е2;1)
Перейдите в ячейку В9 и, вызвав функцию СЦЕПИТЬ, установите ссылки на эти ячейки. Затем замените ссылки формулами, которые находятся в ячейках.
Расчетная область
Перечислим операции, которые выполняет разработанный нами однострочный табель:
— автоматический подсчет количества отработанных часов (столбец АО) с помощью формулы из ячейки АО9:

   =СУММ(Е9:А19)

- определение коэффициента отработанного времени для расчета заработной платы (столбец АР).
В ячейке АР9 производится деление количества отработанных часов на количество рабочих часов, положенных по норме в этом месяце:

   =AO9/$DS5

- автоматическое вычисление количества отработанных дней (столбец AJ) с помощью формулы из ячейки AJ9:

   =СЧЕТЕСЛИ(Е9:AI9;">0")

- автоматическое определение выходных дней (столбец АК) с помощью формулы из ячейки АК9:

   =СЧЕТЕСЛИ(Е9:АI9;"в")

- подсчет больничных дней (столбец AL) с помощью формулы из ячейки AL9:

   =СЧЕТЕСЛИ(Е9:АI9;"б")

- подсчет дней отпуска (столбец AM) с помощью формулы из ячейки АМ9:

   =СЧЕТЕСЛИ(Е9:АI9;"от")

- автоматический контроль правильности заполнения табеля (столбец AN) с помощью формулы из ячейки AN9:

   =ЕСЛИ(СУММ(AJ9:АМ9)=SD$ 6;СУММ(AJ9:АМ9) ;"Ошибка!")

В последней формуле логическая функция ЕСЛИ сравнивает общее количество дней, полученных в области AJ9:AM9, с количеством календарных дней в данном месяце, указанным в ячейке D6. Если условие выполняется, выдается общее количество дней, иначе — текст Ошибка!. Ошибка также может быть связана с некорректным вводом данных (например, вместо числового значения или принятых букв "от", "б" и "в" могут быть введены любые другие символы).
Если работник уволен или принят на работу в текущем месяце
В созданном нами электронном табеле нельзя автоматически определить количество рабочих дней для сотрудников, отработавших неполный месяц по той причине, что они в этом месяце уволены или только приняты на работу. Такую возможность можно обеспечить, модернизировав формулу в ячейке AN9.
В табель следует ввести еще одно текстовое обозначение — "ув". Если количество дней, указанных в области AJ9AM9, и количество дней, которые работник не числился на предприятии, не равны количеству календарных дней, выдается сообщение Ошибка!. После внесения изменений формула в ячейке AN9 примет такой вид:

=ЕСЛИ(СУММ(Аа9:АМ9)+СЧЕТЕСЛИ(Е9:AI9;"ув"}-SDS6;СУММ(AJ9:АМ9);"Ошибка!")

Автоматизация процесса создания и обработки табелей учета рабочего времени позволяет сэкономить много времени и опять-таки избежать рутинной работы. Как это сделать, рассказано в настоящей главе на примере табелей двух типов: двухстрочного и однострочного. Теперь вы сможете создать табель для своего предприятия, учитывая достоинства, присущие каждому из представленных табелей. Можно также воспользоваться готовым продуктом, который находится на дискете, прилагаемой к этой книге.
Что касается освоения приемов работы с Excel, то здесь в первую очередь следует отметить методику связывания книг и рабочих листов, а также способы их защиты от случайного изменения формул. Создание формул со ссылками на другие листы и книги — это метод, который часто применяется на практике.
Теперь вы знаете, что в Excel существует возможность присваивать ячейкам и диапазонам имена. Если имена отражают назначение данных и формул, находящихся в ячейках, то вам или другому пользователю будет легче разобраться в алгоритме расчета.

Созданная вами таблица будет более удобна в работе, если вы скроете строки и столбцы с промежуточными расчетами.