Финансовые расчеты в Excel
         

Двухстрочный табель



Двухстрочный табель

Двухстрочные табели рассчитаны на предприятия, график которых предусматривает ночные смены, сверхурочные часы и т. п.

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

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



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

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

Для вставки имени нужно выделить в формуле адрес ячейки и выполнить команду Вставка/Имя/Вставить или нажать клавишу [F3]. В результате появится диалоговое окно Вставка имени (рис. 7.6), в котором следует выбрать нужное имя, а затем нажать кнопку ОК.


Функции двухстрочного табеля



Функции двухстрочного табеля

С помощью двухстрочного табеля должно быть обеспечено решение следующих задач:
1. Автоматический расчет отработанного времени в часах, в том числе: П всего отработанного времени;
   - времени, отработанного в выходные и праздничные дни (является день праздничным или выходным, также определяется автоматически);
   - времени, отработанного ночью.
2. Учет времени в днях, включая:
   - отработанные дни;
   - дни, которые сотрудник провел в командировке;
   - дни, когда сотрудник был в отпуске;
   - дни, когда сотрудник был в учебном отпуске;
   - дни, пропущенные из-за болезни;
   - дни неявки на работу по неуважительной причине;
   - выходные дни.

Электронный табель учета рабочего времени



Электронный табель учета рабочего времени



Понятие табеля Двухстрочный табель Однострочный табель Данная глава посвящена созданию в Excel электронного табеля учета рабочего времени (в дальнейшем "табель"). Табель не является обособленным документом: на основе содержащихся в нем данных производится расчет заработной платы, этот документ обычно связывают с базой данных сотрудников, а также с ведомостью расчета заработной платы.

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

Характеристика и круг задач



Характеристика и круг задач

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

Структура однострочного табеля (область ввода и расчетная область) показана на следующих двух рисунках.


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



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

В нашем табеле количество часов, отработанных в выходные и праздничные дни, определяется в ячейке AJ12. Эта задача решается с помощью следующей формулы:
=СУММЕСЛИ(C$11:AG12;ИСТИНА;C12:AG12)

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

Здесь интервал - это диапазон ячеек, задействованных в вычислениях, а критерий может быть числом, выражением или текстом, по которому программа определяет, следует ли добавлять содержимое данной ячейки.

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


Определение нормативного количества рабочих часов



Определение нормативного количества рабочих часов

Нормативное количество рабочих часов для конкретного месяца будет указано в ячейке D5, а количество календарных дней в этом месяце - в ячейке D6 табеля. Эти данные будут браться из специальной таблицы в области B29:D42 рабочего листа Праздники (рис. 7.20). Но эту таблицу необходимо еще создать.

Итак, в ячейке D5 рабочего листа Табель формула должна иметь такой вид:
=ВПР(Месяц;Праздники!В30:С41;2;ЛОЖЬ)

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

В процессе вычисления представленных формул на основе названия месяца, введенного в ячейке ААЗ рабочего листа с однострочным табелем, производится поиск названия месяца на рабочем листе Праздники, после чего возвращается нормативное количество рабочих часов либо число календарных дней.


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



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

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



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



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

Для определения выходных дней (суббот и воскресений) воспользуйтесь функцией ДЕНЬНЕД, которая устанавливает номер дня недели. Эту функцию введите в ячейку С28:
=ДЕНЬНЕД(С9)

В следующую ячейку - С29 - введите функцию ЕСЛИ:
=ЕСЛИ(С28=7;ИСТИНА;ЕСЛИ(С28=1;ИСТИНА;ЛОЖЬ))

Функция ЕСЛИ действует по описанной ниже схеме:
- День недели, определенный в ячейке С28, - суббота?
- Если да, возвращается значение ИСТИНА.
- Если нет, переходим к следующему ЕСЛИ.
- Если днем недели является воскресенье, возвращается значение ИСТИНА.
- В противном случае возвращается значение ЛОЖЬ.

Учет выходных и праздничных дней

На следующем этапе нам предстоит создать формулу, которая при соблюдении одного из условий (праздничный день, выходной день или праздничный день, выпадающий на выходной) возвращает значение ИСТИНА. Мы предполагаем разместить эту формулу в ячейке СЗО.

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

Функция ИЛИ имеет следующий синтаксис:
ИЛИ(логическое_значение1;логическое_значение2; ...)

Здесь логииеское_значение1, логическое_значение2, ... - это от 1 до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.


Понятие табеля



Понятие табеля

Табель представляет собой именной список сотрудников подразделения (например, цеха, отдела, участка), в котором учитывается отработанное каждым сотрудником время. В табель заносятся данные о каждом дне, а в качестве итога подсчитывается время за месяц.

Табель обычно включает такие реквизиты, как цех (отдел), бригада, фамилия, имя, отчество, профессия (должность), табельный номер и т. д.

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

Учет использования рабочего времени в табеле осуществляется либо методом сплошной регистрации, согласно которому для каждого лица фиксируется время прибытия, опоздания и т. д., либо путем регистрации отклонений (опозданий, неявок и т. п.).

Для учета того, как используется рабочее время служащими всех категорий, для контроля за соблюдением ими установленного режима, с целью получения данных об отработанном времени, для расчета заработной платы, составления отчетов о проделанной работе применяются типовые формы табелей, разработанные государственными органами (например, министерством статистики).



Расчетная область



Расчетная область

Область табеля с формулами, в которой производятся расчеты, находится в диапазоне AI:AS23 (рис. 7.3). На примере формул для первого служащего рассмотрим, какие величины определяются в расчетной области.

Расчет отработанного времени в часах

Сумма отработанных часов определяется в ячейке АПЗ с помощью следующей формулы:
=СУММ(С12:АG12)

В данном случае мы производим суммирование значений, указанных в диапазоне C12:AG12 (первая строка для первого служащего).


Расчетная область



Расчетная область

Перечислим операции, которые выполняет разработанный нами однострочный табель:
- автоматический подсчет количества отработанных часов (столбец АО) с помощью формулы из ячейки АО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 существует возможность присваивать ячейкам и диапазонам имена. Если имена отражают назначение данных и формул, находящихся в ячейках, то вам или другому пользователю будет легче разобраться в алгоритме расчета. Созданная вами таблица будет более удобна в работе, если вы скроете строки и столбцы с промежуточными расчетами.

Область ввода двухстрочного табеля



Рис. 7.1. Область ввода двухстрочного табеля


Одним из главных параметров в табеле является период его заполнения: год, месяц и дни. Эти сведения располагаются в шапке табеля.

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

Для присвоения имени ячейке, в которую введено название месяца, поместите табличный курсор в ячейку AF3 и активизируйте команду Вставка/Имя/Присвоить или нажмите комбинацию клавиш [Ctrl+F3]. В результате отобразится диалоговое окно Присвоение имени (рис. 7.2). Введите в соответствующее поле имя (например, Месяц). В поле Формула будет сформирована запись, указывающая, какой ячейке присваивается имя:
=Табель!$АF$3

Завершите операцию присвоения имени нажатием на кнопку ОК и закройте диалоговое окно.


Диалоговое окно Присвоение имени



Рис. 7.2. Диалоговое окно Присвоение имени


Присвойте имя ячейке AJ3, в которой указан год, а также диапазону C6:AG6, где приведены числа месяца. Когда имя назначается диапазону ячеек, последовательность действий та же, только выделяется не одна ячейка, а весь диапазон.

Расчетная область



Рис. 7.3. Расчетная область


Количество часов, отработанных в ночное время, вычисляется в ячейке АК13 как сумма значений во второй строке, где фиксируются эти часы:
=СУММ(С13:АG13)

Расчет отработанного времени в днях

Количество отработанных дней определяется в ячейке AL13:
=СЧЕТЕСЛИ(С12:АG12;">0")

Формула создана на основе функции СЧЕТЕСЛИ (см. главу 5, рис. 5.3), которая подсчитывает количество ячеек внутри интервала, удовлетворяющих заданному критерию. Критерием могут служить как цифры (количество отработанных часов), так и буквы (рассмотренные выше условные обозначения, такие как "от" - отпуск, "б" - дни временной нетрудоспособности и т. д.). Формула, приведенная в качестве примера, предназначена для подсчета количества ячеек, в которые введены цифры.

Количество дней, проведенных служащим в командировке, подсчитывается в ячейке AM13 (рис. 7.4):
=СЧЕТЕСЛИ(С12:АG12;"к")

Критерием является буква "к", которая вносится в табель, если работник находится в командировке.

Подсчет дней, в течение которых работник был в отпуске, производится в ячейке AN13:
=СЧЕТЕСЛИ(С12:АG12;"от")

Количество дней учебного отпуска определяется в ячейке АO13:
=СЧЕТЕСЛИ(С12:AG12;"у")

Подсчет дней, в течение которых работник болел, производит формула в ячейке АР13:
=СЧЕТЕСЛИ(С12:AG12;"б")

Количество дней, в течение которых работник отсутствовал на работе без уважительных причин, подсчитывается с помощью формулы из ячейки AQ13:
=СЧЕТЕСЛИ(С12:AG12;"п")

Счет праздничных и выходных дней ведется в ячейке AR13:
=СЧЕТЕСЛИ(С12:AG12;"в")

Наконец, общее количество протабелированных дней определяется в ячейке AS 13 с помощью такой формулы:
=СУММ(АL13:АR13)


Формулы в области вычислений



Рис. 7.4. Формулы в области вычислений


Мы описали все формулы, кроме той, которая находится в ячейке AJ13 и рассчитывает количество часов, отработанных в выходные и праздничные дни. Эта формула требует отдельного анализа.

Формирование дат праздников

Для определения чисел, на которые приходятся выходные дни, суббота и воскресенье, можно воспользоваться функциями Excel. А вот чтобы компьютер мог установить, какой день праздничный, а какой нет, он должен иметь соответствующую информацию.

Введем даты праздников в нашу рабочую книгу на отдельном листе, которому присвоим имя Праздники. Дату праздников можно указать в формате Excel. Однако существуют праздники, которые каждый год приходятся на разные даты (к этой категории принадлежат Пасха и Троица). Если праздник выпадает на выходной день, то выходной переносится на следующий будний день. Поэтому перенесенный выходной тоже может быть представлен как праздник.

Итак, наименования праздников введите в ячейки столбца В. Месяцы и дни укажите раздельно (в столбцах С и D), а номер текущего года - в ячейке С2 (рис. 7.5).


Даты официальных праздников на листе Праздники



Рис. 7.5. Даты официальных праздников на листе Праздники


Преобразование элементов введенных дат в формат Excel можно произвести при помощи модуля, созданного в главе 6. Для его внедрения в рабочий лист Праздники выполните следующие действия:
1. Откройте файл с модулем формирования даты в формате Excel (рис. 6.12).
2. Выделите диапазон ячеек С5:С7 модуля и скопируйте его в буфер обмена.
3. Перейдите на лист Праздники и активизируйте ячейку Е3.
4. Выполните вставку из буфера, для чего откройте диалоговое окно Специальная вставка и установите флажок Транспонировать. Модуль поменяет ориентацию на рабочем листе и его ячейки будут расположены в одной строке.
5. В ячейку Е3 введите ссылку на адрес ячейки С3 (содержит название месяца).
6. В формуле, которая содержится в ячейке G3, замените адрес ячейки С3 (содержит год в модуле) адресом $C$2, а адрес С4 (это день) - адресом D3.
7. Скопируйте диапазон ячеек E3:G3, в котором находится модуль, и выполните вставку из буфера, выделив область Е3:Е14.

В результате этих наших действий все даты (в формате Excel), на которые припадают праздники, будут расположены в диапазоне ячеек G3:G14. Выделите этот диапазон и присвойте ему имя ДатаПраздн.

Диалоговое окно Вставка имени



Рис. 7.6. Диалоговое окно Вставка имени


В результате замены в ячейке С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.


Фрагмент рабочего листа...



Рис. 7.7. Фрагмент рабочего листа с формулами после вставки модуля формирования даты и замены адресов именами




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



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


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

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

Синтаксис функции имеет такой вид:

ВПР(искомое_значение;инфо_таблица;

номер_столбца;интервальный_просмотр)

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

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

Результат поиска может оказаться некорректным, если диапазон ячеек, указанный в функции ВПР, находится на другом рабочем листе. Поэтому желательно действовать следующим образом:
- В поле Табл_массив окна функции ВПР введите ссылку на диапазон Праздники!$С$3:$С$18:
  =ВПР (С9;Праздники!SC53:$C$18;1;ЛОЖЬ)
- После ввода формулы присвойте диапазону Праздники!$С$3:$С$18 имя ДатаПраздн.
- Активизируйте ячейку С26 и замените в формуле адрес диапазона соответствующим именем.

Формула в ячейке С25 будет следующей:
=ВПР(С9;ДатаПраздн;1;ЛОЖЬ)

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

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


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



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


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

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

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

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

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

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


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



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




Панель функции ИЛИ



Рис. 7.12. Панель функции ИЛИ


В первом аргументе нам необходимо указать адрес ячейки С29, в которой определяется день недели, а во втором аргументе - адрес ячейки С27, где устанавливаются даты праздников.

Формула в ячейке СЗО должна быть такой:
=ИЛИ(С29;С27)

Произведем вложение функций:
- Формулу из ячейки С25 вложим в формулу из ячейки С26:


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

- Полученную формулу вложим в формулу из ячейки С27:


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

-Формулу из ячейки С28 вложим в формулу из ячейки С29:
   =ЕСЛИ(ДЕНЬНЕД(С9)=7/ИСТИНА;ЕСЛИ

(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ))

- Полученную формулу вложим в формулу из ячейки СЗО:
   =ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7;:ИСТИНА;

ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ)) ;С27)

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

На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос (рис. 7.13).

Даты дней, которые вследствие переноса стали выходными, добавим в список праздников, находящийся в диапазоне A15:G16 листа Праздники (рис. 7.5).

Теперь в табеле нам нужна формула, которая будет определять, не перенесен ли данный выходной. Для ее получения доработаем формулу из ячейки С27 табеля.


Список с перемещенными датами



Рис. 7.13. Список с перемещенными датами


Скопируйте ее из строки формул, вставьте в ячейку С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.

Панель функции СУММЕСЛИ



Рис. 7.14. Панель функции СУММЕСЛИ


В табеле описываемая функция работает по следующей схеме. В ячейке AJ12 суммируются значения, находящиеся в ячейках области C12:AG12, но только в том случае, если в ячейке соответствующего столбца области Cl I :AG11 находится значение ИСТИНА (то есть день является выходным).

Вкладка Защита диалогового окна Формат ячеек



Рис. 7.15. Вкладка Защита диалогового окна Формат ячеек



3. Убедитесь в том, что установлен флажок Защищаемая ячейка. Это установка по умолчанию. Также надлежит активизировать флажок Скрыть формулы. Нажмите кнопку ОК.
4. Выделите диапазон ячеек, в которые будут вноситься данные. На рабочем листе Табель (рис. 7.1) таковым является диапазон C12:AG23.
5. Откройте повторно вкладку Защита диалогового окна Формат ячеек.
6. Отключите флажки Защищаемая ячейка и Скрыть формулы.
7. Чтобы обеспечить защиту рабочего листа, вызовите команду Сервис/Защита/Защитить рабочий лист и в появившемся диалоговом окне Защитить лист (рис. 7.16) нажмите кнопку ОК. В указанном окне можно ввести пароль, если это необходимо. После нажатия кнопки ОК будет установлена защита для всех областей рабочего листа, где производится расчет.


Диалоговое окне Защитить лист



Рис. 7.16. Диалоговое окне Защитить лист



8. При вводе пароля Excel попросит подтвердить его в появившемся диалоговом окне Подтверждение пароля.


Диалоговое окно Подтверждение пароля



Рис. 7.17. Диалоговое окно Подтверждение пароля


В случае попытки ввести значения в защищенную область Excel выдаст сообщение о том, что изменить содержимое ячеек невозможно. Отменить защиту листа позволяет команда Сервис/Защита/Снять защиту листа.

Левая часть однострочного табеля



Рис. 7.18. Левая часть однострочного табеля





Правая часть однострочного табеля



Рис. 7.19. Правая часть однострочного табеля




В таблице указано нормативное...



Рис. 7.20. В таблице указано нормативное количество рабочих часов, а также число календарных дней в месяце




Фрагмент однострочного...



Рис. 7.21. Фрагмент однострочного табеля с формулами для ввода порядкового номера, Ф.И.О., табельного номера и должности


Автоматический ввод Ф.И.О.

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

Действие этой формулы сводится к следующему: из базы данных извлекается полная фамилия, а от имени и отчества отсекаются первые буквы, после которых ставятся точки. Кроме того, перед отчеством (или после имени) добавляется пробел.

Введем сначала простые формулы, которые впоследствии будут соединены. В ячейке В10 должна находиться формула, которая будет извлекать фамилию:
=[База.xls]Сотрудники!С2

Первую букву имени поместим в ячейку ВИ. Для этого занесем в нее такую формулу:
=ЛЕВСИМВ([База.xls]Сотрудники!D2;1)

ПРИМЕЧАНИЕ

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

В ячейку В12 введите формулу, приведенную ниже. Она будет извлекать первую букву отчества.
=ЛЕВСИМВ([База.xls]Сотрудники!Е2;1)

Перейдите в ячейку В9 и, вызвав функцию СЦЕПИТЬ, установите ссылки на эти ячейки. Затем замените ссылки формулами, которые находятся в ячейках.

Фрагмент однострочного...



Рис. 7.22. Фрагмент однострочного табеля с формулами для подсчета отработанного времени в часах и днях




Создание бланка табеля



Создание бланка табеля

Первый этап - создание бланка для табеля на рабочем листе электронной таблицы. Сформируйте систему заголовков табеля, как показано на рис. 7.1.

Данные о сотрудниках (фамилии и табельные номера) могут быть введены одним из трех способов:
- с клавиатуры;
- путем копирования из других электронных документов (например, из табеля за прошлый месяц);
- с помощью формул, позволяющих взять информацию из других таблиц.

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

Процесс форматирования рассматривался в предыдущих главах и не должен вызвать затруднений.

Для столбцов C:AG необходимо подобрать такую ширину, чтобы в строке 6 дни месяца отображались полностью как на экране монитора, так и при выводе на печать. Для достижения желаемого результата выделите столбцы C:AG и поэкспериментируйте с полем Ширина столбца одноименного диалогового окна.

Удобочитаемость табеля: управление отображением строк и столбцов



Удобочитаемость табеля: управление отображением строк и столбцов

Обеспечить максимальное удобство работы с табелем можно за счет удаления с экрана его отдельных элементов. Так, после ввода формул желательно скрыть строки 7:11, чтобы они не портили вид табеля. Для этого выделите строки 7:11, откройте контекстное меню и активизируйте в нем команду Скрыть. Аналог указанного средства - команда Формат/Строка/Скрыть. Подобным образом отменяется и отображение столбцов.

Восстановить отображение скрытых элементов, столбцов и строк, несложно. Достаточно выделить столбцы или строки, граничащие со скрытыми, и, вызвав контекстное меню, активизировать команду Отобразить. Альтернативный способ - выполнение команды Формат/Столбец (Строка)/Отобразить.

Заполнение области ввода



Заполнение области ввода

Область ввода табеля занимает ячейки C12:AG23 (рис. 7.1). Для каждого сотрудника отведено две строки: в нижней указывается количество часов, отработанных в ночное время, а в верхней осуществляется ввод остальных данных. Информация может быть представлена в числовом (количество отработанных часов) или текстовом виде. Текст (обычно одна или две буквы) является условным обозначением, например: "к" - командировка, "от" - отпуск, "у" - учебный отпуск, "б" - дни временной нетрудоспособности, "п" - дни неявки на работу по неуважительной причине, "в" - выходные дни.


Заполнение области ввода



Заполнение области ввода

На примере разработки однострочного табеля продемонстрируем, как быстро и без механических ошибок заполнить ячейки, в которых должны находиться Ф.И.О. и табельные номера. Исходным файлом послужит табличная база данных сотрудников.

Связывание книг

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

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

Для удобства при создании связи между файлами (книгами) разместим их в одной папке. Создайте папку под названием Табель и поместите в нее файлы Табель и База (с базой данных сотрудников).

Заполнение ячеек с названиями должностей и табельными номерами

Название должности каждого работника будет извлекаться из книги База. Реализуется это следующим образом:
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.


Заполнение табеля



Заполнение табеля

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



Защита листа



Защита листа

В процессе заполнения таблиц, подобных нашей, существует риск испортить введенные формулы. В Excel предусмотрена защита формул от несанкционированного доступа. Для ее обеспечения выполните следующие действия:
1. Выделите всю область рабочего листа.
2. Откройте диалоговое окно Формат ячеек и перейдите на вкладку Защита (рис. 7.15).