Исследовать графическое отображение зависимости ячеек друг от друга.

1. Скопируйте содержимое листа «Зарплата январь» на новый лист электронной книги; копии присвойте имя «Зависимости»;® откройте панель Зависимости:

Устанавливайте курсор на ячейки в каждом столбце и вызывайте зависимости кнопками Влияющие ячейки и Зависимые ячейки панели Зависимости. Появятся стрелки, указывающие на зависимость ячейки от других ячеек и её влияние на другие ячейки.

Примерный вид таблицы

Практическая работа № 6

«Подбор параметра. Организация обратного расчета».

 

Задание

1. Используя режим подбора параметра, определить, при каком значении % Премии общая сумма заработной платы за январь будет равна 250 000 р. (на основании файла «Зарплата», созданного ранее).

Порядок выполнения работы

К исходным данным этой таблицы относятся значения Оклада и % Премии, одинакового для всех сотрудников. Результатом вычислений являются ячейки, содержащие формулы, при этом изменение исходных данных приводит к изменению результатов расчетов. Использование операции «Подбор параметра» в MS Excel позволяет производить обратный расчет, когда задается конкретное значение рассчитанного параметра, и по этому значению подбирается некоторое удовлетворяющее заданным условиям, значение исходного параметра расчета.

1. Запустите редактор электронных таблиц Microsoft Excel и откройте созданный в Лабораторной работе №4 файл «Зарплата».

2. Скопируйте содержимое листа «Зарплата январь» на новый лист электронной книги. Не забудьте для копирования поставить галочку в окошке Создавать копию. Присвойте скопированному листу имя «Подбор параметра».

3. Осуществите подбор параметра командой Данные → Подбор параметра:

В диалоговом окне Подбор параметра на первой строке в качестве подбираемого параметра укажите адрес общей итоговой суммы зарплаты (ячейка G19), на второй строке наберите заданное значение 250 000, на третьей строке укажите адрес подбираемого значения % Премии (ячейка D4), затем нажмите кнопку ОК. В окне Результат подбора параметра дайте подтверждение подобранному параметру нажатием кнопки ОК.

Произойдет обратный пересчет % Премии, если сумма к выдаче равна 250 000 р., то % Премии должен быть 203 %. Результаты подбора:

 

задания №1

1. Используя режим подбора параметра, определить штатное расписание фирмы.

2. Известно, что в штате фирмы состоит:

· 6 курьеров;

· 8 младших инженеров;

· 10 менеджеров;

· 3 заведующих отделами;

· 1 главный бухгалтер;

· 1 программист;

· 1 системный аналитик;

· 1 генеральный директор фирмы.

Общий месячный фонд зарплаты составляет 100 000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада курьера, а именно:

зарплата = А_i *x + B_i,

где х - оклад курьер;

А_i и B_i - коэффициенты, показывающие: А_i – во сколько раз превышается значение х;

B_i – на сколько превышается значение х.

Порядок выполнения работы

1. Запустите редактор электронных таблиц Microsoft Excel.

2. Создайте таблицу штатного расписания фирмы по приведенному образцу:

3. Выделите отдельную ячейку D3 для зарплаты курьера (переменная «х») и все расчеты задайте с учетом этого. В ячейку D3 временно введите произвольное число (например, 1500).

4. В столбце D введите формулу для расчета заработной платы по каждой должности. Например, для ячейки D6 формула расчета имеет следующий вид: = B6*$D$3+C6 (ячейка D3 задана в виде абсолютной адресации). Далее скопируйте формулу из ячейки D6 вниз по столбцу автокопированием.

В столбце F задайте формулу расчета заработной платы всех работающих в данной должности. Например, для ячейки F6 формула расчета имеет вид: =D6*E6. Далее скопируйте формулу из ячейки F6 вниз по столбцу автокопированием.

В ячейке F14 автосуммированием вычислите суммарный фонд заработной платы фирмы:

5. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы, равной 100 000 р. Для этого в меню Главная→Данные активизируйте команду Подбор параметра:

в поле Установить в ячейке появившегося окна введите ссылку на ячейку F14, содержащую формулу расчета фонда заработной платы;

в поле Значение наберите искомый результат 100 000;

в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D3, в которой находится значение зарплаты курьера, и щелкните по кнопке ОК. Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 р:

 

6. Присвойте рабочему листу имя «Штатное расписание 1». Сохраните созданную электронную книгу под именем «Штатное расписание» в своей папке.

Анализ задач показывает, что с помощью MS Excel можно решать линейные уравнения. Выше решенные задания показывают, что поиск значения параметра формулы – это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel, можно решать любые уравнения с одной переменной.

№ 2

Используя режим подбора параметра и таблицу расчета штатного расписания (см. доп. задание №1), определить заработные платы сотрудников фирмы для ряда заданных значений фонда заработной платы.

 

Порядок выполнения работы

1.

1. Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2». Выберите коэффициенты уравнений для расчета согласно таблице (один из пяти вариантов расчетов):

 

Должность

Вариант 1

Вариант 2

Вариант 3

Вариант 4

Вариант 5

Коэффициент А

Коэффициент В

Коэффициент А

Коэффициент В

Коэффициент А

Коэффициент В

Коэффициент А

Коэффициент В

Коэффициент А

Коэффициент В

Курьер

1

0

1

0

1

0

1

0

1

0

Младший менеджер

1,2

500

1,3

0

1,3

700

1,4

0

1,45

500

Менеджер

2,5

800

2,6

500

2,7

700

2,6

300

2,5

1000

Зав.

отделом

3

1500

3,1

1200

3,2

800

3,3

700

3,1

1000

Главный бухгалтер

4

1000

4,1

1200

4,2

500

4,3

0

4,2

1200

Программист

1,5

1200

1,6

800

1,7

500

1,6

1000

1,5

1300

Системный аналитик

3,5

0

3,6

500

3,7

800

3,6

1000

3,5

1500

Ген.

директор

5

2500

5,2

2000

5,3

1500

5,5

1000

5,4

3000

2. Методом подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100 000, 150 000, 200 000, 250 000, 300 000, 350 000, 400 000 р. Результаты подбора значений зарплат скопируйте в таблицу в виде специальной вставки:

Фонд заработной платы

100 000

150 000

200 000

250 000

300 000

350 000

400 000

Должность

Зарплата сотрудника

Зарплата сотрудника

Зарплата сотрудника

Зарплата сотрудника

Зарплата сотрудника

Зарплата сотрудника

Зарплата сотрудника

Курьер

?

?

?

?

?

?

?

Младший менеджер

?

?

?

?

?

?

?

Менеджер

?

?

?

?

?

?

?

Зав.

отделом

?

?

?

?

?

?

?

Главный

бухгалтер

?

?

?

?

?

?

?

Программист

?

?

?

?

?

?

?

Системный аналитик

?

?

?

?

?

?

?

Ген. дир

?

?

?

?

?

?

?

Для копирования результатов расчетов в виде значений необходимо выделить копируемые данные, произвести запись в буфер памяти (Правка → Копировать), установить курсор в соответствующую ячейку таблицы ответов, задать режим специальной вставки

(Вставить → Специальная вставка), отметив в качестве объекта вставки – значения (Вставить → Специальная вставка → вставить - значения):

Практическая работа № 7