Задание 1. Необходимо определить размеры окладов всех сотрудников при условии, что общий месячный фонд зарплаты составляет 100 000 руб.
Ваши действия:
1. Откройте файл Лабораторные работы. xlsx.
2. Добавьте в книгу новый лист с именем Лаб.10.
3. Исходные данные приведены на рис.10.15 и выделены полужирным начертанием.
Рис.10.15. Исходные и расчетные данные
Каждый оклад является линейной функцией от оклада курьера, а именно:
Зарплата сотрудника = Аi*х + Вi,
где х — оклад курьера; Аi – коэффициент, показывающий, во сколько раз превышается значение х; Bi – коэффициент, показывающий, на сколько превышается значение х.
4. Ячейку D2 для зарплаты курьера (переменная «х») НЕ заполняйте.
5. В ячейке D4 введите формулу для расчета заработной платы по каждой должности с учетом значения ячейки D2 ( ячейка D2 задана в виде абсолютной ссылки ):
= B4*$D$2+С4
6. Скопируйте формулу из ячейки D4 вниз по столбцу в интервале ячеек D5:D11, используя маркер заполнения.
7. В ячейке F4 задайте формулу расчета заработной платы всех работающих в данной должности:
= D4*E4
8. Скопируйте формулу из ячейки F4 вниз по столбцу с помощью маркера заполнения в интервале ячеек F5:F11.
9. В ячейке F12 вычислите суммарный фонд заработной платы фирмы:
= СУММ(F4:F11)
10. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы в сумме 100 000 руб. Для этого используйте команду Данные, Работа с данными, Анализ «что-если».
11. В появившемся диалоговом окне Подбор параметра (рис.10.16) выполните следующие действия:
- в поле Установить в ячейке введите ссылку на ячейку F12, содержащую формулу расчета фонда заработной платы;
- в поле Значение введите искомый результат 100 000;
- в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D2, в которой должно находится значение зарплаты курьера, и нажмите на кнопку ОК.
Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб.
Рис.10.16. Диалоговое окно Подбор параметра
Задание 2. Рассчитать зарплату сотрудников при различных фондах заработной платы.
Методом Подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000р., 150000р., 200000р., 250000р., 300000р. Результаты подбора значений зарплат скопируйте в таблицу, расположенную на рис.3.5 в виде специальной вставки.
Ваши действия:
1. Выделите диапазон ячеек F4:F11.
2. Скопируйте выделенные данные в Буфер Обмена ( команда Копировать).
3. Установите курсор в первую ячейку I4 таблицы ответов соответствующего столбца правой кнопкой мыши.
4. Выполните команду Специальная вставка в контекстном меню, отметив в качестве объекта вставки – значения (рис.10.17).
Специальная вставка данных в виде значений позволяет копировать данные, полученные в результате расчетов, без дальнейшей зависимости их от пересчета формул.
Рис.10.17. Диалоговое окно Специальная вставка
5. Повторите п.п.1-4 для каждого следующего значения фонда заработной платы. Каждый новый полученный результат последовательно копируйте с помощью команды Специальная вставка в новую таблицу (рис.10.18).
Рис.10.18. Таблица зависимости зарплаты сотрудников от фонда заработной платы
Задание 3. Рассчитать зарплату менеджера в зависимости от коэффициентов А и В.
Ваши действия:
1. Создайте таблицу с двумя переменными (рис.10.19).
Рис.10.19. Таблица расчета зарплаты менеджера
2. В строке 2 введите значения коэффициента А от 2,5 до 3 с шагом 0,1.
3. В столбце О введите значения коэффициента В от 0 до 1000 с шагом 100.
4. В ячейке О2 на пересечении значений коэффициентов А и В ведите ссылку на формулу, позволяющую рассчитать зарплату менеджера (см. рис.10.15):
=D6
5. Выделите диапазон ячеек О2: U13 и выполните команду Данные, Работа с данными, Анализ «что-если», Таблица данных.
6. В появившемся диалоговом окне (рис.10.20) в поле Подставлять значения по столбцам в введите ссылку на ячейку В6, т.е. ту ячейку, в которую будут подставляться все перечисленные значения коэффициента А.
7. В поле Подставлять значения по строкам в введите ссылку на ячейку С6, куда будут подставляться последовательно все значения коэффициента В. Затем нажмите на кнопку ОК. Таблица автоматически будет заполнена.
Рис.10.20. Диалоговое окно Таблица данных
8. Самостоятельно с помощью механизма таблицы подстановки рассчитайте зарплату программиста в зависимости от коэффициентов А и В. Данные поместите в таблицу (рис.10.21).
Рис.10.21. Таблица расчета зарплаты программиста
Вопросы для самоконтроля
1. Какие средства используются для анализа данных в Excel?
2. Какие таблицы данных можно создать в Excel?
3. Какое количество переменных можно проанализировать с помощью Диспетчера сценариев?
Тесты
1. Вы хотите подобрать значение одной ячейки, изменяя значения влияющей на нее ячейки. Какое средство Microsoft Excel позволит Вам это сделать?
A. Таблица данных.
B. Макросы.
C. Подбор параметра.
2. Таблица подстановки может быть создана для …
A. Одной переменной.
B. Для двух переменных.
C. Для трех переменных.
3. Какие средства используются для анализа данных в Excel?
A. Подбор параметра.
B. Диспетчер сценариев.
C. Таблица данных.
D. Проверка данных.
