Задание. 1. Создать исходный список, содержащий следующие столбцы: Имя, Пол, Адрес (можно ограничиться городом)

1. Создать исходный список, содержащий следующие столбцы: Имя, Пол, Адрес (можно ограничиться городом), Возраст, Степень родства, Профессия, Трудовой стаж. Заполнить список данными (15 строк) по своим родственникам.

2. Построить сводные таблицы в соответствии с заданиями и по возможности разместить их на одном листе с исходной таблицей:

а) определить средний трудовой стаж у женщин;

б) решить ту же задачу для женщин, живущих в Калининграде;

в) определить суммарный трудовой стаж по каждой профессии;

г) определить суммарный трудовой стаж по каждой профессии отдельно для мужчин и женщин;

д) определить по каждой группе родственников с одинаковой степенью родства их средний возраст;

е) определить число родственников, имеющих одинаковые имена (по каждому имени отдельно).

3. Показать результат преподавателю.

Лабораторная работа 8. Подбор параметра

 

Подбор параметра является частью блока задач, который используется тогда, когда желаемый результат известен, но неизвестны значения, которые требуется ввести для получения этого результата. Подбор параметра – это способ поиска определенного значения ячейки путем изменения значения в другой ячейке. При подборе параметра значение в ячейке изменяется до тех пор, пока формула, зависящая от этой ячейки, не вернет требуемый результат.

С помощью такой технологии можно, например, решить математическое уравнение. Процесс решения с помощью данного метода распадается на два этапа:

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

2. Ввод адресов влияющих и целевой ячеек в диалоговое окно Подбор параметра и получение ответа (или сообщения о его отсутствии или невозможности найти).

Пусть надо найти решение уравнения x³-3x²+x=-1. Алгоритм решения задачи:

1. Занести в ячейку А1 (играет роль переменной х) значение 0.

2. Занести в ячейку В1 левую часть уравнения, используя в качестве независимой переменной ссылку на ячейку А1: иметь вид =А1^3-3*A1^2+A1.

3. Вызвать команду Подбор параметра через вкладку Данные, группу опций Работа с данными, опцию Анализ « что-если ». В поле Установить в ячейке указать В1, в поле Значение задать значение –1 – это константа из правой части уравнения, в поле Изменяя значение ячейки указать А1, ОК. Табличный процессор будет менять значение переменной х и по формуле в ячейке В1 рассчитывать значение функции, стремясь достичь значение -1.

4. Посмотреть на результат подбора, отображаемый в диалоговом окне Результат подбора параметра. Нажать ОК, чтобы сохранить полученные значения ячеек, участвовавших в операции.

Таким образом, полученное значение в ячейке А1 – требуемое решение уравнения.

Можно такжерешать экономические задачи.

Задача 1. Используя режим Подбора параметра, надо определить, при каком зна­чении процента премии общая сумма месячной заработной платы всех сотрудников организации, предназначенная к выдаче, будет равна 250 000 руб.

Исходные данные этого примера приведены ниже на рисунке, где знаком «?» отмечены ячейки с расчетны­ми данными.

 

Для решения задачи, прежде всего, необходимо произвести расчёты во всех столбцах таблицы:

1. Для расчета премии использовать зафиксированный в ячейке d5 процент ее начисления, который может меняться со временем: Премия = Оклад * % Премии,

2. Всего на­числено = Оклад + Премия.

3. Удержания = Всего начислено * % Удержаний (введен в ячейку f5),

4. К выдаче = Все­го начислено — Удержания.

5. Подвести итоги по столбцам в строке Всего.

6. По столбцу К выдаче рассчитать среднее, максимальное и минимальное значения (в ячейках с22-с24).

Из расчетов видно, что общая сумма к выдаче при указанных окладах и пре­мии в размере 27 % составляет 104 799,77 руб. чтобы достичь суммы к выдаче в пределах до 250 000 рублей (как задано в задаче), воспользуемся подбором параметра, для чего установим курсор в ячейке об­щей суммы К выдаче и обратимся к команде Подбор параметра.

В диалоговом окне «Подбор параметра» в строке Установить в ячейке в качестве подбираемого параметра должен нахо­диться адрес той ячейки, в которой необходимо получить заданное значение. В данном случае это общая итоговая сумма зарплаты к выдаче (ячей­ка G20, на которой установлен курсор). В строке «Значение» вводим желаемое значение параметра, в данном примере это - 250 000, в строке «Изменяя значение ячейки» указываем адрес подбираемого значения «% Премии» (ячейка D5), ОК.

Произойдёт почти моментальный пересчёт всей таблицы, и от­кроется окно «Результат подбора параметра», в котором даем подтверждение подобранному параметру нажатием ОК.

Итак, произошёл обратный пересчёт «% Премии». Результа­ты подбора выглядят так: если сумма к выдаче равна 250 000 руб., то процент премии должен быть 203%.

 

Задача 2. Используя режим подбора параметра, определить штатное распи­сание фирмы. Исходные данные приведены на рисунке ниже :

 

 

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

Известно, что каждый оклад является линейной функцией от оклада курье­ра, а именно: зарплата сотрудника рассчитывается по формуле А*Z+В, где Z — оклад курьера; А и В — коэффициенты, показывающие соответственно, во сколь­ко раз превышается значение Z и на сколько превышается значе­ние Z.

Не­обходимо узнать, какими должны быть оклады сотрудников фирмы.

Для решения задачи необходимо создать таблицу штатного расписания фирмы по приведён­ному образцу, выполнив все необходимые расчеты:

1. В столбце Зарплата сотрудника ввести формулу для расчёта заработной платы по каждой должности.

2. В столбце Суммарная зарплата определить заработную плату всех работающих в данной должности путем умножения заработной платы работника на количество работающих в этой должности.

3. В ячейке G12 вычислить суммарный фонд заработной платы фирмы.

4. Произвести подбор зарплат сотрудников фирмы для сум­марной заработной платы в размере 100 000 руб. с помощью команды Подбор параметра:

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

· в поле Значение ввести искомый результат — 100000;

· в поле Изменяя значение ячейки ввести ссылку на из­меняемую ячейку D17, в которой находится значение зарплаты курьера,

· щелкнуть ОК.

Произойдёт обратный расчет зарплаты сотрудников по заданному условию при фонде зарпла­ты, равном 100 000 руб. Итог обработанного пересчета таков: при фонде зарплаты, равном 100 000 руб., зарплата курьера должна составлять 1289,66 руб.

Следует заметить, что если возникает необходи­мость копировать результаты, полученные подбо­ром параметра, то следует производить копиро­вание полученных данных в виде значений с ис­пользованием Специальной вставки.