Практическая
работа
Подбор параметра и
организация обратного расчета
Цель: рассчитать
заработную плату за месяц; применять абсолютной адресации ячеек для финансовых
расчетов; сортировать данные; работать с листами электронной книги; научиться
производить обратные расчеты подбором параметров; выполнять специальные вставки
данных; копировать результаты расчетов в виде значений.
Содержание работы:
Задача: Произвести
обратный пересчет данных методом подбора параметра в таблице, в которой данные
связаны формулами. Осуществить специальные вставки данных при копировании.
Вставить копируемые данные в виде значений, отвязывая их при этом от расчетных
формул.
Задание 1. Используя
операцию подбор параметра, определить, при каком значении % премии общая сумма
заработной палаты за октябрь будет равна 250000 руб.
Ход и порядок
выполнения:
1. Оформить
таблицу по образцу для этого необходимо выполнить следующие расчеты:
‒
Премия=
Оклад * % Премии (для в ячейке D5 введите формулу =$D$4*C5,
так как ячейка D4
используется в виде абсолютной адресации). Скопируйте набранную формулу вниз по
столбцу Автозаполнением.
‒
Всего
начислено= оклад + премия
‒
Удержания
= всего начислено *% удержаний (для этого в ячейке F5 введите
формулу =$F$4*E5)
‒
К
выдаче= всего начислено- удержания
2. Рассчитайте
итоги по столбцам, а также максимальный, минимальный и средний доход по данным
колонки «К выдаче».
3. Проверьте
сортировку по фамилиям в алфавитном порядке по возрастанию.
4. Переименуйте
Лист 1, присвоив ему имя Зарплата за октябрь.
5. Осуществите
подбор параметра командой Данные/Анализ, что если/ Подбор параметра.
Откроется окно подбор параметра. В диалоговом окне подбор параметра на первой
строе в качестве подбираемого параметра укажите адрес общей итоговой суммы
заработной платы (G19), на
второй строке наберите значение 250000, на третей строке укажите адрес
подбираемого значения- % премии ($D$4) и нажмите кнопку ОК.
6. Произойдет
обратный расчет % Премии. Результат полученный в ячейке D4 запишите
в тетрадь.
Задание 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 рублей. (Данные/Анализ, что если/ Подбор параметра.)
7. Полученный
результат запишите в тетради.
Самостоятельная
работа:
Для
первоночального расчета будет считать заработную плату курьера 100 рублей.
Должность
|
Вариант
1
|
Вариант
2
|
Вариант
3
|
Вариант
4
|
А
|
В
|
А
|
В
|
А
|
В
|
А
|
В
|
Курьер
|
1
|
0
|
1
|
0
|
1
|
0
|
1
|
0
|
Младший менеджер
|
1,3
|
0
|
1,3
|
700
|
1,4
|
0
|
1,45
|
500
|
Менеджер
|
2,6
|
500
|
2,7
|
700
|
2,6
|
300
|
2,5
|
1000
|
Зав. Отделом
|
3,1
|
1200
|
3,2
|
800
|
3,3
|
700
|
3,1
|
1000
|
Главный бухгалтер
|
4,1
|
1200
|
4,2
|
500
|
4,3
|
0
|
4,2
|
1200
|
Программист
|
1,6
|
800
|
1,7
|
500
|
1,6
|
1000
|
1,5
|
1300
|
Системный аналитик
|
3,6
|
500
|
3,7
|
800
|
3,6
|
1000
|
3,5
|
1500
|
Ген. директор
|
5,2
|
2000
|
5,3
|
1500
|
5,5
|
1000
|
5,4
|
3000
|
Заполните таблицу
в тетрадии в соответсии со своим вариантом.
Должность
|
Фонд
заработной платы
|
150000
|
200000
|
250000
|
300000
|
350000
|
400000
|
Курьер
|
|
|
|
|
|
|
Младший менеджер
|
|
|
|
|
|
|
Менеджер
|
|
|
|
|
|
|
Зав. Отделом
|
|
|
|
|
|
|
Главный бухгалтер
|
|
|
|
|
|
|
Программист
|
|
|
|
|
|
|
Системный аналитик
|
|
|
|
|
|
|
Ген. Директор
|
|
|
|
|
|
|
Контрольные
вопросы:
1. С какого символа начинается формула в электронной таблице Excel?
2. Для чего используется функция СУММ?
3. Что такое
абсолютная ссылка?
4. Как
производится подбор данных в электронной таблице Excel?
5. Как вы
считаете обратный пересчет данных удобная ли это функция в электронной таблице
при расчете заработной платы сотрудника? Ответ обоснуйте.
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.