Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel

Подождите немного. Документ загружается.

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

В данную функцию надлежит вложить формулы из ячеек СЗО и

С31.

Ниже по-

казано, как выглядит результирующая формула в ячейке С32:

-И(ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7;ИСТИНА;

ЕСЛИ

(ДЕНЬНЕД(С9)=1,-ИСТИНА;

ЛОЖЬ)

)

;С27)

ЕНД

(ВПР

<А9;

ДатаПереное;

ЛОЖЬ)

) )

Скопируйте формулы из ячеек С27 и С32 в ячейки СЮ и

С11.

Определение

часов,

отработанных

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

В нашем табеле количество часов, отработанных в выходные и праздничные дни,

определяется в ячейке

AJ12.

Эта задача решается с помощью

следующей

формулы:

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

В формуле используется функция СУММЕСЛИ. Эта функция аналогична функ-

ции

СУММСЧЕТ,

но последняя подсчитывает количество ячеек, которые соот-

ветствуют заданному критерию, а

функция

СУММЕСЛИ суммирует значения

ячеек, если их содержимое соответствует указанному критерию.

Проанализируем

синтаксис функции СУММЕСЛИ:

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

Здесь интервал — это диапазон ячеек, задействованных в вычислениях, а крите-

рий может быть числом, выражением или текстом, по которому

программа

оп-

ределяет,

следует ли добавлять содержимое данной ячейки.

Аргумент

сумм_интервал

— это фактические ячейки для суммирования. В диа-

пазоне

сумм_интервал

суммируются только те ячейки, которые находятся в той

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

творяющие критерию. Если аргумент

сумм_интервал

опущен, суммируются зна-

чения ячеек, заданных аргументом интервал.

СУММЕ СПИ

'Условие

|ИСТИНА

•....

У; ......

;,,,,;.;i

M,I,,,,^M,,:VI:;

......

„...,,

••'•;

',;,

••••',

,„

— — i — ..........

гаЦ = {"от";0;"от";"от";"о'

•'.'.умшфуе

>ччбйк.и,

.шецифициромннь»

задлннын

-критерием.

Рис. 7.14.

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

СУММЕСЛИ

В табеле описываемая функция работает по следующей схеме. В ячейке

AJ12

сум-

мируются значения, находящиеся в ячейках области

C12:AG12,

но только в том

196

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

случае, если в ячейке соответствующего столбца области С1

!:AG11

находится зна-

чение ИСТИНА (то есть день является выходным).

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

отображением строк и столбцов

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

экрана его отдельных элементов. Так, после ввода формул желательно скрыть

строки

7:11,

чтобы они не портили вид табеля. Для этого выделите строки

7:11.

откройте контекстное меню и активизируйте в нем команду Скрыть. Аналог ука-

занного средства — команда Формат/Строка/Скрыть. Подобным образом отме-

няется и отображение столбцов.

Восстановить отображение скрытых элементов, столбцов и строк, несложно. Дос-

таточно выделить столбцы или строки, граничащие со скрытыми, и, вызвав кон-

текстное меню, активизировать команду Отобразить.

Альтернативный

способ -

выполнение команды Формат/Столбец

(Строка)/Отобразить.

Защита листа

В процессе заполнения таблиц, подобных

нашей,

существует риск испортить вве-

денные формулы. В Excel предусмотрена защита формул от несанкционирован-

ного доступа. Для ее обеспечения выполните следующие действия:

Выделите всю область рабочего листа.

2. Откройте диалоговое окно Формат ячеек и перейдите на вкладку Защита

(рис. 7.15).

формат

ячеек

:'IH,

1C!

•:

.-i

.illl.i

ИНГ

1.1

...il

, • •

•

Стена

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

ячеек

Убедитесь в том, что установлен флажок Защищаемая ячейка. Это установка

по умолчанию. Также надлежит активизировать флажок Скрыть формулы. На-

жмите кнопку ОК.

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

197

4. Выделите диапазон ячеек, в которые будут вноситься данные. На рабочем лис-

те Табель (рис. 7.1) таковым является диапазон C12:AG23.

5. Откройте повторно вкладку Защита диалогового окна Формат ячеек.

6. Отключите флажки Защищаемая ячейка и Скрыть формулы.

7. Чтобы обеспечить защиту рабочего листа, вызовите команду Сервис/Защита/

Защитить рабочий лист и в появившемся диалоговом окне Защитить лист

(рис.

7.16)

нажмите кнопку ОК. В указанном окне можно ввести пароль, если

это необходимо. После нажатия кнопки ОК будет установлена защита для всех

областей рабочего листа, где производится расчет.

Защитить

лист

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

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

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

[Подтверждение

пароля

.•-..-:••

пшз]

ОК

]

•inf.'Hrt |

. • •

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

В случае попытки ввести значения в защищенную область Excel выдаст сообще-

ние о том, что изменить содержимое ячеек невозможно. Отменить защиту листа

позволяет команда Сервис/Защита/Снять защиту листа.

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

Рассмотрим процесс заполнения табеля методом регистрации отклонений. Нач-

нем с того, что в строке списка сотрудников, отведенной для Иванова Ивана

Ивановича (рис. 7.1 и 7.2), отметим все выходные дни и праздники. Для этого

введем в соответствующие ячейки букву «в». Изначально мы будем исходить из

того, что данный сотрудник отработает весь месяц, поэтому введем цифры «8»

198

Глава 7. Электронный табель учета рабочего

времени

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

Для их заполнения воспользуемся методом копирования. Заполнив область ввода

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

Это облегчит

дальнейшую

работу: мы сможем ограничиться редактированием от-

дельных значений, применяя метод регистрации отклонений (например, если че-

ловек в какой-либо день болел, восьмерку можно заменить буквой

«б»).

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

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

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

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

однострочного табеля должно быть обеспечено решение следующих задач:

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

• Определение количества календарных дней в месяце.

• Определение коэффициента для начисления заработной платы в зависимости

от отработанного времени.

• Вывод сообщений в случае возникновения ошибок при вводе.

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

на следующих двух рисунках.

X Microsoft Excel -

Однострочный

табели

:*>]<1.>ил

Пг-!Ь»

[!<;i«).'.J

*.S|V.;M

"(;

1 ftflrtl

?"'•#

~ЦЛК<*1"

•

Ж К

l::

;

i(JOO

"Тюльпанчик"

;

В :

-Р"~

г.-

8||

: :

ЦЙ

.Всего

раб.

часов

151

Sfl;

:Всегодаей

;

."ъ"':»""'.

•

.12

••

14:

«;;

[}'

И.О

Иванов

И. И

Петров

П.

Иваненко И. П

Сидорова Е. С

Петренко

Сидоренко

КарповаЮ

Ледова

Т. И

Щукина

Вьюнов П. И

пм;ич<

0123

1234

2345

3456

4367

}б78

0789

7890

8901

9012

;Viii'*:H'n-o-.

Менеджер

Начальник

Инженер

Аудитор

Экспедитор

Менеджер

;

:Итого:

•

Й||

;

1§Ш;;М;Х»ДЖМ>^''

Г«Тиьи

«S

табель

Х^тЩр

tf.

ж вд Ш i

:й

-а

:Таоель

учета

раоочего

врел

Утверждаю

;

1ени

ЯмИщЯШ!

"Т"

ггт

г г

Яиларь

-ОС||

^Дирекго^

;

' .

г.

от

' V

•:

ОТ

•

П-

Е.

от

,,'•

•'.

[

от

'•

от

8(8

•

м-

•

;

•

::.

от

•

от

-ч

•

•т

•:

„

•

....i.-G...:

;

| |

"""";"

""-"""""

""""

•"

' .

JJUM

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

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

199

Microsoft

Excel

Однострочный

табель

:OOO

"Тюльпанчик"

[Всего

раб.

часов

[Всего

Январь

151

Таб..

номер

Дйлжность

Иъ1НО!.ЮЛ.

).t

'I",

ИЧШ.И!

_дии

От

Нген

Иванов

И.

И 0123 Менеджер

0,477

Петров

П 1234

Менеджер

0376

Иваненко

И.

2345

Начальник

.51

0,417

/идороваЕ.

3456

Начат

ник

151

Петренко П. С

4i67

Начальник

Сидоренко С. И

5678

Инженер

0,6.16

Карпова

Ю.

Аудтор

•

17 !

ЛещоваТ.

151

ЩупдиР.Е

Экспедпор

151

Рис. 7.19.

Правая

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

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

рабочих часов

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

ячейке D5, а количество календарных дней в этом месяце — в ячейке D6 табеля.

Эти данные будут браться из специальной таблицы в области B29:D42 рабочего

листа Праздники (рис. 7.20). Но эту таблицу необходимо еще создать.

Итак, в ячейке D5 рабочего листа Табель формула должна иметь такой вид:

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

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

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

В процессе вычисления представленных формул на основе названия месяца, вве-

денного в ячейке ААЗ рабочего листа с однострочным табелем, производится по-

иск названия месяца на рабочем листе

Праздники,

после чего

возвращается

нор-

мативное количество рабочих часов либо число календарных дней.

200

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

X Microsoft Excel - Таблица с указанием нормы рабочих часов

календарных

дней в

месяце

fjf

!,.,!,.,

JtU

! . .

•.,'-.,•

. .

l"™*^^

-I

i.:

] |

;v;

>:!

•

•iii

Мгт'ЛЦ

Январь

Февраль

Март

Апрель

Май

Июнь

Июль

Август

Сентябрь

Октябрь

Ноябрь

Декабрь

Итого

151

168

175

160

151

159

168

175

168

176

159

168

1978

!',!.'

И>)|Д,!рЦК!.,.

ДН'Н'

Г.

365

;

••**'•:

•'•;

•<••"•••

•

i т

•'«*••;>

.--..iv.,,

/i№i

s и

1 ' 1 . ' 1

;

•

\ \ \

•

; ;

'Щщ-

I i

!^i

;

йШ:

Г~1

i Г" | ' i "т

"•""

j '

/"}.'"

••".

«jMi

j |

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

а также число календарных дней в месяце

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

На примере разработки однострочного табеля продемонстрируем, как быстро и

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

и табельные номера. Исходным файлом послужит табличная база данных сотруд-

ников.

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

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

На ход расчетов может повлиять содержимое других ячеек — при наличии в фор-

муле ссылок на них. Цель создания ссылок заключается в использовании содер-

жащейся в ячейках информации. Рассматривавшиеся ранее таблицы размеша-

лись на одном рабочем листе. Если же ячейку, содержащую ссылку, перенести

на другой лист или в другую книгу, ссылка не разорвется и мы получим связанные

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

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

201

Таким образом, вместо копирования данных из одной книги в другую можно

просто создавать ссылки на ячейки другой книги. Воспользуемся этим методом

для ввода в табель фамилий и табельных номеров работников.

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

папке. Создайте папку под названием Табель и поместите в нее файлы Табель и

База (с базой данных сотрудников).

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

и табельными номерами

Название должности каждого работника будет извлекаться из книги База. Реа-

лизуется это следующим образом:

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

расположить их в одном окне посредством команды Окно/Расположить.

2. Выделите диапазон ячеек

D9:D18

и введите знак равенства.

3. Перейдите в книгу База и выделите ячейку G4. При появлении ссылки на

другую книгу автоматически создается абсолютная ссылка. Для преобразова-

ния ее в относительную следует нажать три раза функциональную клавишу [F4].

4. В завершение нажмите комбинацию клавиш

[Enter+Ctrl].

В результате диапазон

D9:D18

будет заполнен такими формулами, как в ячейке D9:

=[База.х1s]Сотрудники!G4

Ссылка между открытыми книгами имеет такую структуру:

[Имя

книги]Имя

листа!Адрес

ячейки

После закрытия книги, с которой установлена связь, ссылка изменится: в ней

будет указан полный путь, по которому находится исходная информация:

='С:\Отдел

кадров\Табель\[База.xls]Сотрудники'!В2

ПРИМЕЧАНИЕ-

При наличии пробелов в именах листов, книг или папок весь путь автоматически за-

ключается

в одинарные кавычки.

Для создания ссылки на ячейку другой книги путь можно

набрать

с клавиатуры,

но это занимет много времени. Преимущество данного метода лишь в том, что

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

Для получения ссылок на табельные номера и фамилии установим связь. Напри-

мер, для

ячейки

С9 (табельный номер) выполните следующие действия:

Выделите диапазон ячеек

С9:С18

и введите знак равенства.

2. Перейдите в книгу База и выделите ячейку В2.

3. Нажмите комбинацию клавиш [Enter+Ctrl].

В результате формула в ячейке С9 будет иметь такой вид:

=[База.xls]Сотрудники!В2

Аналогичными формулами будет заполнен весь диапазон ячеек С9:С18.

202

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

pab

часов

!=ВПР(Месяц;Праздники!ВЗО:С41

;2,ЛОЖЬ)

-а

'Е^его

даем

:«ВПР(Месяц;ПраздкикиШЗО£И1

;3;ЛОЖЬ)

-СЦЕПИТЬ([База.х18]Сотрудвики!С2;"

ЛЕВСИМВ([База.х18]Со11)удники!П2,1);"

Таб.

номер

[Баз

а.х!5]Сотрудга1ки!

В2

=[База.х1з]Согрудники!О2

=СЦЕЛИТЬ([Бша.хЬ]Сотрудники!СЗ;"

ЛЕВСИМВ([База.х15]СотрудШ1Ки!03;1),"

ЛЕВСИМВ([База.х15]Сотрудники!ЕЗ;1))

[База.х1з]Сотрудники!ВЗ

[Б

аз a

xls]C

отрудники!

Щ[=СЦЕЛИТЬ[;[База.х15]Сотрудникн!С4,"

";ЛЕВС

=[База.х15]Сотрудники'В4

!'.Г

Рис.

7.21.

Фрагмент однострочного табеля с формулами

для

ввода

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

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

Формула в ячейке В9 (рис.

7.21)

возвращает фамилию, которая находится в ячей-

ке С2 рабочего листа Сотрудники, и инициалы, которые берутся из ячеек D2 и

Е2. Она также обеспечивает расстановку между ними пробелов и точек:

=СЦЕПИТЬ([База.xls]Сотрудники!С2;"

";ЛЕВСИМВ([База.xls]Сотрудники!D2;1);".

";ЛЕВСИМВ([База.xls]Сотрудники!Е2;1))

Действие этой формулы сводится к следующему: из базы данных извлекается полная

фамилия, а от имени и отчества отсекаются первые буквы, после которых ста-

вятся точки. Кроме того, перед отчеством (или после имени) добавляется пробел.

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

ячейке

В10 должна находиться формула, которая будет извлекать фамилию:

=[База.xls]Сотрудники!С2

Первую букву имени поместим в ячейку

В11.

Для этого занесем в нее такую

формулу:

КПЕВСИМВ([База.xls]Сотрудники!D2;1)

ПРИМЕЧАНИЕ-

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

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

лить ячейку на другом листе или в другой книге.

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

В ячейку В12 введите формулу, приведенную ниже. Она будет извлекать первую

букву отчества.

=ЛЕВСИМВ([База.xls]Сотрудники!Е2;1)

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

эти ячейки. Затем замените ссылки формулами, которые находятся в ячейках.

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

Перечислим

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

табель:

• автоматический подсчет количества отработанных часов (столбец АО) с по-

мощью

формулы из ячейки АО9:

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

• определение коэффициента отработанного времени для расчета заработной

платы (столбец АР).

В ячейке АР9 производится деление количества отработанных часов на коли-

чество рабочих часов, положенных по норме в этом месяце:

=A09/SDS5

• автоматическое вычисление количества отработанных дней (столбец AJ) с по-

мощью формулы из ячейки AJ9:

-СЧЕТЕСЛИ(Е9:AI9;

">0")

автоматическое определение выходных дней (столбец

АК)

с помощью фор-

мулы из ячейки

АК9:

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

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

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

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

-СЧЕТЕСЛИ(Е9:А19;"от")

автоматический контроль правильности заполнения

табеля

(столбец AN) с по-

мощью формулы из ячейки AN9:

=ECJW(CyMM(AJ9:AM9)=SDS6;СУММ(AJ9:АМ9);"Ошибка!")

В последней формуле логическая

функция

ЕСЛИ сравнивает общее количество

дней, полученных в области

AJ9:AM9,

с количеством календарных дней в данном

месяце, указанным в ячейке D6. Если условие выполняется, выдается общее ко-

личество дней, иначе — текст

Ошибка!.

Ошибка также может быть связана с не-

корректным вводом данных (например, вместо числового значения или приня-

тых букв «от», «б» и «в» могут быть введены любые другие символы).

204

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

Если работник уволен или принят на работу в текущем месяце

В созданном нами электронном табеле нельзя автоматически определить коли-

чество рабочих дней для сотрудников, отработавших неполный месяц

по

той при-

чине, что они в этом месяце уволены или только приняты на работу. Такую

воз-

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

В табель следует ввести еще одно текстовое обозначение —

«ув».

Если количество

дней, указанных в области

AJ9:AM9,

и количество дней, которые работник не

числился на предприятии, не равны количеству календарных

дней.,

выдается со-

общение

Ошибка!.

После внесения изменений формула в ячейке AN9 примет та-

кой

вид:

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

Micmsoll

Excel -

Формчлы

• *

кй-'i

1Г"

ЙИгсго

раб

|Щ

-СЧЁТЕСЛИ

(Е9:АЮ,">0")

"СЧЁТЕСЛИ

(Е10:А1Ш;">0")

-СЧЁТЕСЛИ(Е!

=СЧЕТЕСЛИ(Н

«СЧЁТЕСЛИ

(Е5А»;"в")

-СЧЁТЕСЛИ

(Е10:А1Ш;"О

-СЧЁТЕСЛИ(Е

«СЧЁТЕСЛИСЕ

• •

.:]

1ПИ

:<•!

КГ

И''

;

-ЧАИНКИ

Ходност

|jo

[.«г»

ЕТЕСЛИ(Е11:АП1;"от

)

«СЧЁТЕСЛИ

(Е9:АВ;"6")

-СЧЁГЕСЛИ

(ЕШ:АПО,

6")

-СЧ£ГЕСЛИ(Е

-СЧЁТЕСЛИСЕ

=ГиРТРГПИ|Т

ЧНЫЙ

l.lfjl'llb

А"

Cilli

;,'.'!

-СЧЁТЕСЛИ

(Е9:А19;"от")

-СЧЁТЕСЛИ

(Е10:А1Ш;"от")

=СЧЁТЕСЛИ(Е1

-•

=ЕСЛИ(СУРЛМ(А19:АМ9)

-ЮЧЁТЕСЛИ(Е9:А19;"ув")

Ю1й;

СУММ(АК:АМ?),"О1ии6га!

-ЕСЛИ(СУММ(;АЛО:АМ10)

-ЮЧЁТЕСЛИ(Е10:АПО;"уЕ")=Ю$6;

СУММ(АЛО;АМ10),"<Зши6к»!")

»ЕСЛИ(СУММ(АЛ

1 :АМ1

П+СЧЁТ^

=СЧЁТЕСЛИГЕ1!=ЕСЛИ(СУММ(АЛ2:АМ12)+СЧЁТ1

;

[П ' р

ЩИ

т«ст:

.'г

•>•.•:.,'

i*.j

ifl.'llil

Ч..1.Л.Ч.

•СУММ

(Е9-.А17)

-СУММ

(ЕШАИО)

=СУММ(ЕП:А11

-СУММ(Е12:А11

г;чч

,л?

Кимф

=A09/$DlJ

=AO10,1Dt5

=A01WDti

«A012/$D$j

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

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

Резюме

Автоматизация процесса создания и обработки табелей учета рабочего времени

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

Как это сделать, рассказано в настоящей главе на примере табелей двух типов:

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

предприятия, учитывая достоинства, присущие каждому из представленных та-

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

дискете, прилагаемой к этой книге.

Что касается освоения приемов работы с Excel, то здесь в первую очередь следует

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

щиты от случайного изменения формул. Создание формул со ссылками на другие

листы и книги — это метод, который часто применяется на практике.