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

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

Расчет премии за выслугу лет 141

Создание сложной формулы методом вложения

Формулы, как видите, получились довольно простыми и очень похожими. Одна-

ко работать с ними не совсем удобно. Поэтому попытаемся заменить эти шесть

простых формул одной сложной. Выполняемый при этом процесс называется

вло-

жением одной формулы в другую. От состоит в замене ссылки на ячейку содер-

жимым этой ячейки. Другими словами, если формула включает адрес ячейки,

которая, в свою очередь, содержит формулу, наша задача — вместо адреса вста-

вить формулу, находящуюся по этому адресу.

Для создания вложенной формулы выполните следующие действия.

Скопируйте из ячейки Р2 формулу без знака равенства.

2. Перейдите в ячейку Q2 и в строке формул выделите ссылку на ячейку Р2 и

произведите вставку текста из буфера, нажав комбинацию клавиш [Ctrl+V].

3. Скопируйте из ячейки Q2 формулу без знака равенства.

4. Перейдите в ячейку R2 и в строке формул выделите ссылку на ячейку Q2 и

произведите вставку текста из буфера, нажав комбинацию клавиш [Ctrl+V].

В результате этих манипуляций в ячейке R2 должна появиться такая формула:

=ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ!"

";С2)-1);ИСТИНА;ЛОЖЬ)

Аналогичным образом внесите формулы из ячеек N2, О2 и R2 в формулу, нахо-

дящуюся в ячейке S2. Полученная формула будет выглядеть так:

=И(ЕПУСТО(Ь2);ЕСЛИ(02=С2;ИСТИНА;ЛОЖЬ);

ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ("

";С2)-1)/ИСТИНА,-ЛОЖЬ))

Расчет премии за выслугу лет

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

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

правильности информации в базе данных сотрудников и в списке с окладами.

Таблица

5.1.

Размер премии за выслугу лет в % к окладу

№п/п

Стаж,

годы

Менее 1

От 1 до 3

От 3 до 5

От 5 до 10

Свыше 10

Премия, %

Не начисляется

Алгоритм вычисления премии следующий.

Проверить, числился ли данный сотрудник в списке работников предприятия

на момент начисления премии.

2. Сравнить, соответствует ли в каждой строке:

а)

табельный

номер из базы данных сотрудников табельному номеру из

штатного расписания;

142

Глава 5. Должностные оклады и премии

б) фамилия из базы данных фамилии из штатного расписания.

3. Определить общее количество проработанных на предприятии дней (для этого

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

4. Определить число отработанных сотрудником лет, разделив полученное на

предыдущем этапе количество дней на 365,25 — среднее число дней в году с

учетом високосных лет.

5. Отбросить от полученного значения дробную часть.

6. Если количество полных отработанных человеком лет составляет:

а) менее 1 года — премию не начислять;

б) от 1 до 3 лет — увеличить сумму оклада на 10%;

в) от 4 до 5 лет — увеличить сумму оклада на 20%;

г) от 6 до 10 лет — увеличить сумму оклада на 30%;

д) более

лет, увеличить сумму оклада на 40%.

7. Если табельные номера и фамилии в штатном расписании соответствуют ука-

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

если же нет — выдать сообщение об ошибке.

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

Условия начисления премии, описанные в табл.

5.1,

можно поместить в любое

место рабочего листа. Расположите таблицу с этими данными, скажем, в диапа-

зоне ячеек U1:V8 (рис. 5.23), следующим образом:

• в ячейку V2 поместите дату расчета;

• в диапазон U4:U8 — стаж работника;

• в диапазон V4:V8 — процент для начисления премии.

•

•••••"•• • -

~ .....

.....

л ч

. ,

••<: ,

,':

•*

•

..... '"

та -ИСТИНА

ложь

ИСТИНА

ИСТИН*

ИСТИНА

ложь

ложь '

Угжши.и

Дата

31.12.01

См*

20%

"«'•VI'."

Рис. 5.23. Таблица с условиями начисления премии

Расчет премии за выслугу лет

143

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

лет работы на

предприятии

Следующая наша цель — определить полное количество лет, проработанных ка-

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

нию на 31.12.2001). Вычисления будут выполнены в ячейке Х2. Занесите в эту

ячейку такую формулу:

=$V$2-K2

В данном случае мы вычитаем из даты 31.12.2001, находящейся в ячейке V2, дату

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

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

Далее это значение необходимо разделить на среднее число дней в году, которое,

как вы помните, принимается равным 365,25. Предназначенная для этой цели

формула находится в ячейке Y2. Выглядит она так:

=Х2/365,25

В ячейке Z2 дробная часть от результата деления в ячейке Y2 отбрасывается, и

в итоге мы получаем целое количество лет:

=ОТБР(У2)

Здесь применяется математическая функция ОТБР (рис. 5.24). Функция усекает

число до целого, отбрасывая дробную часть. Синтаксис этой функции имеет вид:

ОТБР(число;число_разрядов)

где число — усекаемое число, число

^разрядов

— число, определяющее точность

усечения. Значением по умолчанию аргумента

число_разрядов

является 0.

Отбраськмет

дробную

часть

числа,,

так что остается

целое

чтело,

Число

усекаемое

число,

ЗначемиегЗ

Рис. 5.24. Панель функции ОТБР

Предположим, первый аргумент нашей функции — это число

123,123.

А вот что мы

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

•

-2

— результатом вычислений будет 100,000;

• 0 (или не указано) — результатом вычислений будет 123,000;

• 2 — результатом вычислений будет

123,120.

144

Глава 5. Должностные оклады и премии

Расчет суммы

премии

На данном этапе расчет производится с использованием логических функций

ЕСЛИ. Первая формула в ячейке АА2 (рис. 5.25) создается по принципу: если

служащий проработал менее года (значение ячейки Z2 сравнивается со значени-

ем ячейки

U4),

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

ячейке Е2, на коэффициент, внесенный в ячейку V4. Таким образом, в ячейке

АА2 используется формула, приведенная ниже:

=EO1M(SZ2<$U$4;$E2*$V$4;AB2)

Так как коэффициент в ячейке V4 отсутствует, то есть равен нулю, то и размер

премии равен нулю.

Аналогичные формулы введены в ячейки АВ2, АС2 и

AD2:

=ЕСЛИ($22<$и$5;$Е2*$7$5;АС2)

=ECJIJ1($Z2<$U$6;$E2*$V$6;AD2)

=ECUW($Z2<$U$7;$E2*$V$7;AE2)

Но формула в ячейке АЕ2 несколько от них отличается:

=ECJm(.$Z2>=$U$8;$E2*$V$8;0)

Если количество проработанных лет больше либо равно количеству лет, указан-

ному в ячейке U8, то размер оклада умножается на коэффициент, указанный в

ячейке V8.

X Microsoft

Ексе!

Расчет

премии за

выслугу

лег

si г

•--:

.'I

ц;

-;,

а.-

Ш Ш

«111!,-:.

ipjipjii&ll

Формулы

Еел

-*V*24'

=JVJ2-I-

=J^^-k

=$V$2-^

=X3^65,25

=X4<303.25

j=>-e_/3e5,2!5

J=XS/3e5,25^

>X11/303

Д6

ЕС-ЕС'ЕСЛИр!

;Ag4)

;-EC/lH(JZ4<JiUI5,5Ed'jy$5;AC4)

ЕС*ЕС-ЕСЛИ(»г4>«$и$8,?Е4'

:ву58;0)

•-

bPQ

ОТБРСГ1р>.а

ОТБРСУ11Т!«

•'''•^•'^^^'^^

4.AW)

£[

"'fl)

=EC-EC-fc:nH

1iZ:5^5iU$8,?E

4;AS7)

^ECnM(lfZ7vju?5JE7-*XvJ5

AC7)

!-EC=EC=l

4.AB81

;

ECnna26^Uf5.JE8^'^5AC8)

-EC

EG*I

Рис. 5.25. Формулы для расчета суммы премии в зависимости от стажа работы

Вложение формул

Выполним операцию вложения формул из ячеек Х2 и Y2 в формулу, которая

находится в ячейке Z2:

=ОТБР(

($VS2-K2)

/365,25)

Расчет премии за выслугу лет

145

Процесс вложения

формул

с функциями

ЕСЛИ

начнем с ячейки АЕ2 — мы вкла-

дываем ее в ячейку AD2, а из ячейки AD2 — в ячейку АС2 и т. д. В ячейку АА2

введем формулу:

=ЕСЛИ($22<$и$4;$Е2*$У$4;ЕСЛИ($г2<$и$5;$Е2*$У$5;

ЕСЛИ($22<$и$б;$Е2*$У$6;

ЕСЛИ($22<$и$7;$Е2*$У$7;

ЕСЛИ($22>-$и$8;$Е2*$У$8;0)))))

Формулу из ячейки Z2 вложим вместо ссылок на эту ячейку в формулу ячей-

ки

АА2:

=ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$4;$E2*$V$4,

ЕСЛИ(ОТБР( (SVS2-K2.)

/365, 25XSUS5; $E2*$V$5;

ЕСЛИ(ОТВР(($V$2-K2)/365,25)<$U$6;$E2*$V$6;

ЕСЛИ(ОТБР((SV$2-K2)/365,25)<$U$7;$E2*$V$7;

ЕСЛИ(ОТБР(($V$2-K2)/365,25)>=$U$8;$E2*$V$8;0)))))

Теперь в расчетах участвуют только формулы, расположенные в ячейках S2 и

АА2. В ячейке

AF2

создадим формулу, основанную на логической функции ЕСЛИ:

=ЕСЛИ(S2;АА2;"Ошибка!")

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

Если это значение ИСТИНА, формула возвращает сумму премии. Если значение

ЛОЖЬ, то возвращается сообщение

Ошибка!.

Обратите внимание, что третьим аргументом формулы является текст. Это озна-

чает, что в функцию ЕСЛИ можно вводить любой текст (в частности, разного

рода сообщения).

В формулу из ячейки AF2 вместо ссылок на адреса ячеек мы введем находящиеся

в них формулы. В результате получим такую формулу:

=ЕСЛИ(И(ЕПУСТО(Ь2);ЕСЛИ(D2=G2;ИСТИНА;ЛОЖЬ);

ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ("

";С2)-1);ИСТИНА;ЛОЖЬ));

ЕСЛИ(ОТБР((SVS2-K2)/365,25)<$U$4;$E2*$V$4;

ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$5;$E2*$V$5;

ЕСЛИ(ОТБР((SVS2-K2)/365,25)<$U$6;$E2*$V$6;

ЕСЛИ

(ОТБР

( (SV$2-K2) /365, 25)

<$U$7

$E2*$V$7

;

ЕСЛИ(ОТБР(($V$2-K2)/365,25)>=$U$8;$E2*$V$8;0)))))/"Ошибка!")

Теперь рассмотрим лист Оклады целиком, со всеми используемыми для расчета

таблицами (рис. 5.26).

Лист состоит из следующих элементов:

• список с окладами (диапазон

А1:Е11);

• фрагмент базы данных сотрудников

(G1:L11);

• таблица с условиями начисления премии

(U1:V8);

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

лет(г!:гП);

• область вложенных формул

(AF2:AF11).

146

Глава 5. Должностные оклады и премии

lAriaicyr

;

tfe,

a*.

•

;

;••

•'•%,%•

jjfl

л-

sj:

*7И.9 % ,

щщ

Рис. 5.26. Структура листа Оклады после создания вложенной формулы

Скрытие столбцов

После реализации алгоритма начисления премии за выслугу лет таблицу можно

сделать более наглядной, скрыв лишние столбцы. Сделать это

достаточно

просто.

Для этого нужно лишь выделить такие столбцы и применить к ним команду Скрыть,

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

команду Формат/Столбец/Скрыть.

После скрытия дополнительных столбцов лист Оклады будет выглядеть так, как

показано на рис. 5.27.

хм

crosofl

Excel • Штат

BFI

Arial Cyr

• Ж

ЛГ

'Ш

AG9

«I

•

|*з

ШЯ«

Реализации

'еапизации

Контроля.

еализации

Снабжения

Реализации

Снабжения

Контроля.

«.;

("0101

Снабжения

Менеджер

Начальник

Менеджер

Аудитор

Секретарь

Начальник

Менеджер

Инженер

Начальник

Экспедитор

Вьюнов П.

Иваненко

П.

Иванов И. И.

Карпова Ю. М.

Лещова Т. И.

Петренко П. С.

Петров П П.

Сидоренко

Сидорова Е. С.

Щукина Р Е.

9012

3456

0123

6789

7890

4567

1234

5678

2345

8901

120.00

Ошибка!

240,00

70,00

62,00

91,00

140,00

180,00

Ошибка!

'Итого

903,00

Що.„

;

1 '

'н'м:

•

Рис. 5.27. Лист Оклады после скрытия столбцов

Формирование приказа о премии за выслугу лет

147

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

выслугу лет

Итак, расчеты выполнены. Поиск ошибок будет произведен позже. А сейчас нуж-

но подготовить приказ о выплате премии за выслугу лет. Приказ можно создать

либо на этом же листе электронных таблиц, либо в текстовом редакторе Word.

Мы выберем второй способ.

Откройте текстовый редактор и создайте

типовый

бланк приказа, оставив ме-

сто для вставки таблицы, сформированной в

Excel.

2. Перейдите в Excel, выделите диапазон

A1:AF12

и скопируйте

его

в буфер об-

мена.

3. Перейдите в текстовый редактор и, установив курсор в место вставки таблицы,

выполните команду Правка/Специальная вставка, что приведет к появлению

диалогового окна Специальная вставка (рис. 5.28).

4. В списке Как выделите элемент Лист Microsoft Excel Объект.

5. Отметьте переключатель Связать и нажмите кнопку ОК.

Специальная вставка

Источник:

<""

Вставить

Связать

ррч^пктаг

Ч '

ШЛист

Microsoft

Excel

(объект)

Текст

формате RTF

В Неформатированный текст

Рисунок

В Точечный рисунок

Word Hyperlink

Вставке

cciA'HJWiiirni

6уфе:Л!

wtiun;)

ИЦ

огл

ог

;

-л

Вставка

сеязи

ур-днавднв^ет

сея;*

файлом

данных.

Изменения

и/х^днои

файле,

будут

•

. .

Пооорх

текста

Вг.ид^

-.начко

Рис. 5.28. Диалоговое окно Специальная вставка

в текстовом редакторе Word

В результате проделанных манипуляций на странице текстового документа поя-

вится объект, связанный с документом Excel (рис. 5.29). Изменения, вносимые

в электронные таблицы, будут

отражаться

в документе Word.

Данная методика может применяться не только с целью формирования приказа

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

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

148

Глава 5. Должностные оклады и премии

Приказ

Выплатить

премию

за

выслугу

лег за 2001 год следующим работникам:

Отдел.

'еализацки

Решавших

Соктраги

4V2flWfH

Ггейжеии

Снабжения

"набжеми

ДОГШЮСТЬ.

М?нед*е|й_

М:кеи*ер

AVUKTDD

Секоепхл

Начальник

MnnVtD

Иазапьиий

Экспедитор

Фамилия,

ЭмиовП И,

Двивкко И. П.

Иванов

И.

КипыаЮ

Лешпва Т И

ЪтрвюаП.С.

1втровП П.

"ЧишгюваЁ С

ЛтамР.Е.

Табепычый

номер

9012

3456

0323

6783

7S90

4567

1234

5638

2345

8901

Стаж

(лег)

Премия

120.00

ОшиОка!

240,00

ТО

(J200

•Л.

140,00

1 80.00

Ошибка!

Итого

Директор

Петров И. И.

Рис. 5.29. Текстовый документ с приказом в режиме размет

Составление перечней

В приведенном на рис. 5.2 списке всего 10 записей, содержащих лишь 3 названия

отделов. Поэтому, если вам понадобится составить перечень отделов предпри-

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

ходится

работать

с сотнями и тысячами записей. И как в этом случае на основе

данных столбца составить перечень встречающихся в нем

элементов''

Предлагаемый нами метод решения этой задачи мы продемонстрируем на столб-

це А, который содержит названия отделов.

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

А2:А11

и скопируйте его содержимое в буфер об-

мена.

2. Поместите табличный курсор в ячейку А15 и выполните вставку из буфера

обмена. Вставку можно произвести практически в любую свободную область

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

ровать весь столбец и поместить его на чистый рабочий лист.

3. Выделите весь вставленный диапазон ячеек.

4. На стандартной панели управления нажмите кнопку Сортировка по возраста-

нию или Сортировка по убыванию. В результате этой операции все названия

отделов будут упорядочены в порядке возрастания или убывания по алфавит-

ному признаку.

Составление

перечней

149

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

и скопировать

Произвести вставку из буфера обмена

(как значения посредством команды Специальная вставка)

Произвести сортировку вставляемого

диапазона

Создать формулу на основе

функции ЕСЛИ

Скопировать диапазон с формулами и произвести

его вставку, воспользовавшись командой

Специальная вставка

Произвести сортировку вставляемого

диапазона

Рис. 5.30. Алгоритм выделения наименований

5. Как правило, повторяющиеся записи удаляют из списков вручную. Но это

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

а повторяющиеся, наоборот, остаться незамеченными. Для того чтобы про-

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

вторно. Правда, операцию по удалению дублирующих друг друга записей мож-

но выполнить и автоматически, используя логическую функцию ЕСЛИ. Для

этого необходимо, выделив диапазон

В15:В24,

ввести в ячейку В15 формулу

=ЕСЛИ(В15=В16;"ЯЯЯ";А15)

и нажать комбинацию клавиш

[Ctrl+Enter]

(в результате чего эта формула поя-

вится во всех ячейках диапазона). Таким образом, если две рядом располо-

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

вратит значение ЯЯЯ. В противном случае результатом станет значение,

находящееся в ячейке, которая расположена ниже. (Буквы «ЯЯЯ» использу-

ются потому, что наличие в списке такого наименования маловероятно.)

6. Скопируйте ячейки из диапазона В15:В24 в буфер обмена, а затем вставьте

их в предварительно выделенную ячейку С15. Эту операцию необходимо вы-

полнить посредством диалогового окна Специальная вставка, нажав в нем пе-

реключатель Значения. В диапазоне

С15:С24

теперь будут находиться не фор-

мулы, а значения.

7. Выполните сортировку указанного диапазона ячеек, нажав кнопку Сортировка

по возрастанию. Ячейки со значениями

ЯЯЯбудут

расположены в нижней час-

ти области, а названия отделов — в верхней.

150

Глава 5. Должностные оклады и премии

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

образом список в нужное место таблицы. В примере на рис. 5.2 это ячейки

G2:G4.

Microsoft

Excel - Пример извлечения

повторяющихся

наименований

|jit|

fjlJ.jEK.-.

F\n

P-

T,;,FJ',

:.,|

i|lo""||||

•

.[Реализации

[Реализации

"' .4 "Контроля.

-• $• [Реализации

•.6

[Снабжения

жжения

i^EN;';:!

Контр оля.

'0-.'

[Реализации

[Снабжения

'-;i1-T

.'Реализации

/V.m>vi;:r.

п-.

Менеджер

Начата,

юск

Начальник

Инженер

Аудитор

Секретарь

Экспедитор

Менеджер

••;•••',•'.••>

^,а^

ш%

«д.деде;

,..-^,А,

;

'i',-iMSV>H-'i

ИмимИ.И

Петров П. П.

Сидорова Е. С.

ИЕаиенкоИ.П

Петренко П. С.

Сидоренко

Карпова Ю. М.

ЛещоваТ. И.

Штата

Р. Е.

Вьюнов П. И.

::::::;i.:.:::.'..:.::.:i

.:::.::.:::::::.:

Контроля.

ЯЯЯ

[Контроля.

;1:6

;

Контроля.

j Контроля.

[Реализации

еализации

ЯЯЯ

-Снабжения

щщ

.(•

'••

{'.'•^•'

'^'

•»

0123

1234

2345

3456

4567

5678

6789

7890

8901

9012

800,00

700,00

1 050,00

1 400,00

510,00

600,00

700,00

310,00

400,00

600,00

Итого

470,00

г.

|:;|

Реализации

вРеализации

Снабжения

снабжения

....... яяя

........ яяя ...........

IIjMlI.

Реализации

........ яяя

........ яяя ...........

Снабжения

Гяяя

Тяяя

]59E

ЯЯЯ

Гяяя

•

ЯЯЯ

джиЛ i

Рис.

5.31.

Внешний вид листа после операции выборки

ПРИМЕР

В одной крупной бюджетной организации, где насчитывается до

1000

человек,

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

вить штатное расписание и произвести ряд расчетов, подобных описанным выше.

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

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

чальников, инженеров, рабочих, мужчин и женщин), а затем представить эти дан-

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

Фонда оплаты труда (ФОТ) по оплате труда рабочих, служащих и руководителей,

уложиться в отведенную сумму. Служащие планово-экономического отдела со-

ставляли все эти отчеты в Excel, применяя электронные таблицы лишь для вы-

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

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

Однако если бы сотрудники планово-экономического отдела

воспользовались

всеми

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

они, несомненно, выполнили бы менее чем за один день.