Размер оклада каждого сотрудника с помощью определенного коэффициента привязывается к зарплате ведущего специалиста (например, директора или начальника отдела). Допустим, оклад начальника отдела реализации составляет 1400. Новая зарплата начальников других отделов определяется умножением их прежних окладов на некоторый заранее установленный коэффициент. А заработная сотрудников этих отделов вычисляется путем умножения оклада начальника на заданный коэффициент. Алгоритм расчета представлен на рис. 6.
Рисунок 6. Алгоритм расчета окладов
Внесем изменения в рабочий лист. В ячейку G3 введите сумму прежнего оклада начальника отдела реализации (1400), а в ячейку G4 – коэффициент, на который увеличиваются все оклады. Будущий оклад начальника отдела реализации будет вычисляться в ячейке G5 по формуле:
=ОКРУГЛ (G3*(1 + G4);0)
В столбец Н занесите все коэффициенты, используемые при перерасчете окладов, а в диапазон Е2:Е11 – формулы, по которым рассчитывается размер будущего оклада (рисунок 7).
Рисунок 7. Расчет окладов с применением коэффициента
Соединение двух списков
Чтобы рассчитать премию за выслугу лет, необходимо знать стаж работы каждого служащего на предприятии и его должностной оклад. Эти сведения хранятся в разных местах. Для решения задачи необходимо связать базу данных сотрудников и список с окладами.
Имеются два ранее созданные документы:
* рабочий лист, где содержатся список сотрудников и их оклады (присвоим этому листу имя Оклады, а файл, в котором он находится, назовем Штат)
* рабочий лист с базой данных сотрудников.
Для соединения двух таблиц откройте две книги, содержащие списки. В книге База на листе Сотрудники выделите столбцы A:U и скопируйте их в буфер обмена. Перейдите в книгу Штат и, выделив столбец G на листе Оклады, произведите вставку из буфера. База данных сотрудников разместится в диапазоне G1:AA11. теперь из таблицы необходимо удалить все лишние данные (рисунок 8).
|
|
Данные расположены в строках двух таблиц в разной последовательности. Необходимо выполнить сортировку (рис. 9).
Рисунок 8. Фрагмент листа Сотрудники
Рисунок 9 Сортировка данных
Проверка данных
В штатном расписании есть сотрудник, который уже уволился. Кроме того, в рабочих листах внесены ошибки: для одних и тех же сотрудников указаны разные табельные номера.
Формула, с помощью которой можно определить, числился ли работник на момент начисления премии, основана на функции ЕПУСТО (категория Проверка свойств и значений).
Данная функция проверяет содержимое ячейки и, если ячейка ничего не содержит, возвращает логическое значение ИСТИНА.
В ячейку N2 ведите формулу = ЕПУСТО(L2) и скопируйте ее в остальные ячейки.
Сравнение табельных номеров
Проверим значения табельных номеров сотрудников в таблицах.
В ячейку О2 введем формулу 
и скопируем в остальные ячейки (рисунко 10).
Сравнение фамилий
В базе данных указаны фамилия, имя и отчество, а в штатном расписании – фамилия и инициалы.
1. Определим количество знаков, предшествующих первому пробелу. В ячейку Р2 введем формулу = НАЙТИ(“ ”;С2) и скопируем ее.
2. Применим функцию =ЛЕВСИМВ(С2; Р2-1) (ячейка Q2)
3. В ячейку R2 введем формулу для сравнения фамилий
=ЕСЛИ (Н2=Q2;ИСТИНА;ЛОЖЬ)
|
|
4. Чтобы определить выполняются ли все три условия, применим логическую функцию И. (ячейка S2)
= И(N2;Q2;R2)
Фрагмент рабочего листа приведен на рисунке 11.
Задание: Создать вложенную формулу (рисунок 12)
Рисунок 12. Фрагмент рабочего листа
Рисунок 10. Результат применения функций ЕПУСТО и ЕСЛИ
Рисунок 11. Фрагмент рабочего листа
Лабораторная работа