Практическая работа
Группа: БУХ 9-8
Тема: Подбор параметра и организация обратного расчета
Цель: Изучить технологию подбора параметра при обратных расчетах.
Содержание работы:
Задача: Произвести обратный пересчет данных методом подбора параметра в таблице, в которой данные связаны формулами.
Задание 1. Используя операцию подбор параметра, определить, при каком значении % премии общая сумма заработной палаты за октябрь будет равна 250000 руб. ( на основании таблицы прошлой практической работы ).
Краткая справка. К исходным данным этой таблицы относятся значения оклада и %Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в Microsoft Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра и по этому значению подбирается некоторое удовлетворяющее заданным условиям значение исходного параметра расчета.
Ход и порядок выполнения:
1. Оформить таблицу по образцу для этого необходимо выполнить следующие расчеты:
· Премия= Оклад * % Премии (для в ячейке D5 введите формулу =$D$4*C5, так как ячейка D4 используется в виде абсолютной адресации). Скопируйте набранную формулу вниз по столбцу Автозаполнением.
· Всего начислено= оклад + премия
· Удержания = всего начислено *% удержаний ( для этого в ячейке F5 введите формулу =$F$4*E5)
· К выдаче= всего начислено- удержания
2. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доход по данным колонки «К выдаче».
3. Проверьте сортировку по фамилиям в алфавитном порядке по возрастанию.
4. Переименуйте Лист 1, присвоив ему имя Зарплата за октябрь.
5. Осуществите подбор параметра командой Данные/Анализ, что если/ Подбор параметра. Откроется окно подбор параметра. В диалоговом окне подбор параметра на первой строе в качестве подбираемого параметра укажите адрес общей итоговой суммы заработной платы (G19), на второй строке наберите значение 250000, на третей строке укажите адрес подбираемого значения- % премии ($D$4) и нажмите кнопку ОК.
6. Произойдет обратный расчет % Премии.
Задание 2. Используя режим «Подбор параметра», определите штатное расписание фирмы. Общий месячный фонд заработной платы составляет 100000 рублей. Необходимо определить, каким должны бить оклады сотрудников фирмы.
Ход и порядок выполнения работы:
1. Оформите таблицу по образцу:
2. Выделите отдельную ячейку D3 для заработной платы курьера и все расчеты задайте с учетом этого. Например, введите в ячейку D3 число 100.
3. В столбце D введите формулу для расчета заработной платы по каждой должности. Для ячейки D6 формула имеет следующий вид: =B6*$D$3+C6. Далее используйте автозаполнение для интервала D6:D13.
4. В столбце F задайте формулу расчета заработной платы для всех работающий в данной должности. Для ячейки F6 формула: =F6*E6. Далее используйте автозаполнение для интервала F6:F13.
5. В ячейке F14 функцией «Автосумма» вычислите суммарный фонд заработной платы фирмы. Полученный результат в ячейке F14 запомните.
6. Произведите подбор заработных плат сотрудников фирмы для суммарной заработной платы в сумме 100000 рублей. (Данные/Анализ, что если/ Подбор параметра.)
В поле «Установить в ячейке» появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы; в поле «Значение» наберите искомый результат 100000; в поле «Изменяя значение ячейки» введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера. Щелкните ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб.
7. Зафиксируйте какая стала зарплата курьера.
8. Назовите Лист как: Штатное расписание1
!!!Самостоятельное задание!!!
Используя таблицу расчета штатного расписания (см. задание выше), определить величину заработной платы сотрудников фирмы для ряда заданных значений фонда заработной платы
Порядок работы
1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».
2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 руб. Результаты подбора значений зарплат скопируйте в Сводную таблицу (см. ниже) (создана на отдельном листе) в виде специальной вставки.
Краткая справка. Для копирования результатов расчетов специальной вставкой в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка/Копировать), установить курсор в первую ячейку таблицы ответов соответствующего столбца, задать режим специальной вставки (Правка/ Специальная вставка), отметив в качестве объекта вставки значения (Правка/Специальная вставка/вставить — значения)
Сводная таблица
Контрольные вопросы:
Как вы считаете обратный пересчет данных удобная ли это функция в электронной таблице при расчете заработной платы сотрудника? Ответ обоснуйте.