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

Формулы, как видите, получились довольно простыми и очень похожими. Однако работать с ними не совсем удобно. Поэтому попытаемся заменить эти шесть простых формул одной сложной. Выполняемый при этом процесс называется вложением одной формулы в другую. От состоит в замене ссылки на ячейку содержимым этой ячейки. Другими словами, если формула включает адрес ячейки, которая, в свою очередь, содержит формулу, наша задача — вместо адреса вставить формулу, находящуюся по этому адресу.
Для создания вложенной формулы выполните следующие действия.
1. Скопируйте из ячейки Р2 формулу без знака равенства.
2. Перейдите в ячейку Q2 и в строке формул выделите ссылку на ячейку Р2 и произведите вставку текста из буфера, нажав комбинацию клавиш [Ctrl+V].
3. Скопируйте из ячейки Q2 формулу без знака равенства.
4. Перейдите в ячейку R2 и в строке формул выделите ссылку на ячейку Q2 и произведите вставку текста из буфера, нажав комбинацию клавиш [Ctrl+V].
В результате этих манипуляций в ячейке R2 должна появиться такая формула:

=ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ(" ";С2)-1);
ИСТИНА;ЛОЖЬ)

Аналогичным образом внесите формулы из ячеек N2, О2 и R2 в формулу, находящуюся в ячейке S2. Полученная формула будет выглядеть так:

=И(ЕПУСТО(L2);
ЕСЛИ(D2=G2;ИСТИНА;ЛОЖЬ);
ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ(" ";С2)-1);ИСТИНА;ЛОЖЬ))

 

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

№ п/п

Стаж, годы

Премия, %

1

Менее 1

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

2

От 1 до 3

10

3

От 3 до 5

20

4

От 5 до 10

30

5

Свыше 10

40

Размер премии за выслугу лет в % к окладу
Алгоритм вычисления премии следующий.
1. Проверить, числился ли данный сотрудник в списке работников предприятия на момент начисления премии.
2. Сравнить, соответствует ли в каждой строке:
   а) табельный номер из базы данных сотрудников табельному номеру из штатного расписания;
   б) фамилия из базы данных фамилии из штатного расписания.
3. Определить общее количество проработанных на предприятии дней (для этого надо из даты начисления премии вычесть дату приема на работу).
4. Определить число отработанных сотрудником лет, разделив полученное на предыдущем этапе количество дней на 365,25 — среднее число дней в году с учетом високосных лет.
5. Отбросить от полученного значения дробную часть.
6. Если количество полных отработанных человеком лет составляет:
   а) менее 1 года — премию не начислять;
   б) от 1 до 3 лет — увеличить сумму оклада на 10%;
   в) от 4 до 5 лет — увеличить сумму оклада на 20%;
   г) от 6 до 10 лет — увеличить сумму оклада на 30%;
   д) более 10 лет — увеличить сумму оклада на 40%.
7. Если табельные номера и фамилии в штатном расписании соответствуют указанным в базе данных сотрудников, то зачесть полученную сумму премии, если же нет — выдать сообщение об ошибке.
Ввод условий для начисления премии
Условия начисления премии, можно поместить в любое место рабочего листа. Расположите таблицу с этими данными, скажем, в диапазоне ячеек U1:V8 , следующим образом:
— в ячейку V2 поместите дату расчета;
— в диапазон U4:U8 — стаж работника;
— в диапазон V4:V8 — процент для начисления премии.
Определение полного количества лет работы на предприятии
Следующая наша цель — определить полное количество лет, проработанных каждым из сотрудников на данном предприятии (расчеты производятся по состоянию на 31.12.2001). Вычисления будут выполнены в ячейке Х2. Занесите в эту ячейку такую формулу:

=$V$2-K2

В данном случае мы вычитаем из даты 31.12.2001, находящейся в ячейке V2, дату приема сотрудника на работу, указанную в базе данных (ячейка К2). В результате мы получаем общее количество дней, которые он отработал на данном предприятии.
Далее это значение необходимо разделить на среднее число дней в году, которое, как вы помните, принимается равным 365,25. Предназначенная для этой цели формула находится в ячейке Y2. Выглядит она так:

=Х2/365,25

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

=ОТБР(Y2)

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

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

где число — усекаемое число, число_разрядов — число, определяющее точность усечения. Значением по умолчанию аргумента число_разрядов является 0.
Предположим, первый аргумент нашей функции — это число 123,123. А вот что мы получим, если в качестве второго аргумента поочередно используем такие значения:
— -2 — результатом вычислений будет 100,000;
— 0 (или не указано) — результатом вычислений будет 123,000;
— 2 — результатом вычислений будет 123,120.

 

Расчет суммы премии
На данном этапе расчет производится с использованием логических функций ЕСЛИ. Первая формула в ячейке АА2 создается по принципу: если служащий проработал менее года (значение ячейки Z2 сравнивается со значением ячейки U4), то премия равна произведению значения оклада, указанного в ячейке Е2, на коэффициент, внесенный в ячейку V4. Таким образом, в ячейке АА2 используется формула, приведенная ниже:

=ЕСЛИ($Z2<$U$4;$Е2*$V$4;АВ2)

Так как коэффициент в ячейке V4 отсутствует, то есть равен нулю, то и размер премии равен нулю.
Аналогичные формулы введены в ячейки АВ2, АС2 и AD2:

=ЕСЛИ($Z2<$U$5;$Е2*$У$5;AС2) 
=ECЛM($Z2<$U$6;$E2*$V$6;AD2) 
=ECЛИ($Z2<$U$7;$E2*$V$7;AE2)

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

=ЕСЛИ($Z2>=$U$8;$E2*$V$8;0)

Если количество проработанных лет больше либо равно количеству лет, указанному в ячейке U8, то размер оклада умножается на коэффициент, указанный в ячейке V8.
Вложение формул
Выполним операцию вложения формул из ячеек Х2 и Y2 в формулу, которая находится в ячейке Z2:

=ОТБР(($V$2-K2)/365,25)

Процесс вложения формул с функциями ЕСЛИ начнем с ячейки АЕ2 — мы вкладываем ее в ячейку AD2, а из ячейки AD2 — в ячейку АС2 и т. д. В ячейку АА2 введем формулу:

=ECЛИ($Z2<$U$4;$E2*$V$4;
ECЛИ($Z2<$U$5;$E2*$V$5; 
ЕСЛИ($Z2<$U$6;$Е2*$V$6; 
EСЛИ($Z2<$U$7;$E2*$V$7; 
ЕСЛИ($Z2>=$U$8;$Е2*$V$8;0)))))

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

=ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$4;$E2*$V$4; 
ЕСЛИ(ОТБР(($VS2-K2)/365,25)<$U$5;$E2*$V$5; 
ЕСЛИ(ОТКР(($V$2-K2)/365,25)<$U$6;$E2*$V$6; 
ЕСЛИ(ОТБР(($V$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 вместо ссылок на адреса ячеек мы введем находящиеся в них формулы. В результате получим такую формулу:

=ЕСЛИ(И(ЕПУСТО(L2);ЕСЛИ(D2=G2;ИСТИНА;ЛОЖЬ);
ЕСЛИ(Н2=ЛЕВСИМВ(С2;НАЙТИ(" ";С2)-1);ИСТИНА;ЛОЖЬ));
ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$4;$E2*$V$4;
ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$5;$E2*$V$5;
ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$6;$E2*$V$6;
ЕСЛИ(ОТБР(($V$2-K2)/365,25)<$U$7;$E2*$V$7;
ЕСЛИ(ОТБР(($V$2-K2)/365,25)>=$U$8;$E2*$V$8;0)))))/"Ошибка!")

Теперь рассмотрим лист Оклады целиком, со всеми используемыми для расчета таблицами .
Лист состоит из следующих элементов:
— список с окладами (диапазон А1:Е11);
— фрагмент базы данных сотрудников (G1:L11);
— таблица с условиями начисления премии (U1:V8);
— таблица с информацией о количестве полных проработанных на предприятии лет(Z1:Z11);
— область вложенных формул (AF2:AF11).
Скрытие столбцов
После реализации алгоритма начисления премии за выслугу лет таблицу можно сделать более наглядной, скрыв лишние столбцы. Сделать это достаточно просто.
Для этого нужно лишь выделить такие столбцы и применить к ним команду Скрыть, выбрав ее из контекстного меню. Вы получите тот же эффект, активизировав команду Формат/Столбец/Скрыть.

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