«Подбор параметра» MS Excel. Практическая работа № 24

1.

Практическая работа № 24
«Задачи с использованием
инструмента
« Подбор параметра» MS Excel »

2.

• Цель работы: изучить и освоить технологию
применения инструмента «Подбор параметра»
для решения практических задач.

3.

Краткие теоретические сведения
«Подбор параметра» – инструмент Excel, предназначенный
для вычисления неизвестного значения одной переменной
некоторой формулы, при котором формула возвращает
требуемый результат.
«Подбор параметра» можно использовать и для нахождения
большого количества неизвестных, но только в том случае, если
их можно каким-либо способом выразить через одну величину.
Чтобы применить «Подбор параметра» нужно выполнить:
«Данные / Работа с данными / Анализ «что-если» / Подбор
параметра». В открывшемся окне заполнить все поля ввода, а
затем нажать «ОК».

4.

В поле «Установить в ячейке» вводится адрес ячейки,
содержащей формулу, результат вычислений которой
требуется достигнуть.
В поле «Значение» вводится число, которое нужно
получить в результате вычисления формулы.
В поле «Изменяя значение ячейки» вводится адрес
ячейки, содержащей числовое значение переменной, через
которую выражены все остальные неизвестные (в случае,
если их несколько) и которое необходимо подобрать для
получения, требуемого по условиям задачи, результата
вычисления формулы.

5.

Задание 1. Используя инструмент «Подбор параметра», найти
корень уравнения:
, если значения переменной х принадлежат интервалу от –5 до
5(
).

6.

Выполнение
• ввести в ячейку В1 любое значение х, например: 0;
• в ячейку В2 ввести формулу:
=В1^5–4*B1^4+3*B1^3–2*B1^2+B1–1;
• выделив ячейку В2, выполнить: «Данные / Работа с
данными / Анализ «что-если» / Подбор параметра»;
• в поле «Установить в ячейке» ввести: В2;
• в поле «Значение»: 0;
• в поле «Изменяя значение ячейки»: В1 и нажать
«ОК».
Если в ячейке В2 появилось значение 0, а в ячейке В1
какое- либо число, значит подбор параметра прошел
успешно.

7.

Задание 2. Используя инструмент «Подбор параметра», найти
корень уравнения: х3 - 3∙х2 +9∙х – 8=0, если х принадлежит
интервалу от -10 до 10.
Задание 3. Известно, что длина лужайки должна быть больше
ширины в 1,38 раз. Найти, с помощью «Подбора параметра»,
такие размеры лужайки, чтобы ее площадь была равна 100м2

8.

Выполнение
• выразить все значения задачи через ширину площадки, для
этого:
• внести в ячейку В1 любое значение ширины, например: 1;
• в ячейку В2 ввести формулу: =1,38*В1;
• в ячейку В3 – формулу: =В1*В2;
• выполнить: «Данные / Работа с данными / Анализ «чтоесли» / Таблица данных»;
• в поле «Установить в ячейке» ввести: В3;
• в поле «Значение»: 100;
• в поле «Изменяя значение ячейки»: В1 и нажать «ОК».
Если в ячейке В3 появилось значение 100, а в ячейках В1 и В2
значения изменились, значит, подбор параметра прошел
успешно.

9.

Задание 4. Известно, что ширина контейнера должна быть
больше его высоты в 1,5 раза, а длина – больше ширины в 1,8
раза.
Найти, с помощью «Подбора параметра», такие размеры контейнера, чтобы его объем был равен 10 м3 .

10.

Задание 5. Решить задачу оптимизации штатного расписания
магазина, при условии, что:
• для работы требуется три уборщицы, три грузчика, два
товароведа, секретарь, пять техников, девять продавцов,
бухгалтер, директор;
• общий фонд заработной платы должен быть равен 15 000 $;
• решением учредителей установлены соотношения зарплат:
• грузчик должен получать в 1,5 раза больше уборщицы;
• товаровед – в 3 раза больше уборщицы;
• техник – на 30 $ больше, чем товаровед;
• секретарь – в 2 раза больше уборщицы;
• бухгалтер – на 40 $ больше грузчика;
• продавец – в 4 раза больше уборщицы;
• директор – на 20 $ больше продавца.

11.

Выполнение
• построить математическую модель задачи:
• выразить все оклады через наименьший оклад по формуле:
Оклад = А·Х + В,
где Х – наименьший оклад; А – коэффициент,
показывающий во сколько раз данный оклад превышает
наименьший; В – коэффициент, показывающий: на какую
величину оклад превышает наименьший;
• определить общий фонд зарплаты: =N1·(A1·Х + B1) + ... +
N8·(A8·Х+B8) = 15 000 $, где N1...N8 – количество
работников каждой категории;
• создать на листе книги Excel таблицу по образцу (табл. 1):

12.

Таблица 1 – Макет таблицы для задания 5

13.

• заполнить столбцы коэффициентов А и В в соответствии с
решением учредителей (см. выше), для уборщицы значения
коэффициентов: А = 1, В = 0 (если оклад сравнивается не с
минимальным, то коэффициенты всё равно нужно указывать
относительно него: поэтому коэффициенты А оклада, который
сравнивается, и оклада, с которым сравнивают,
перемножаются, а коэффициенты В – складываются);
• определить ячейкой, содержащей значение минимального
оклада, ячейку Н2 (в нее можно внести любую величину);
• в ячейку D2 ввести формулу =А2*$Н$2+В2 и скопировать ее
в ячейки D3:D9 при помощи маркера автозаполнения;
• в столбце Е указать количество сотрудников на должностях;

14.

• в ячейку F2 ввести формулу = оклад ∙ количество
сотрудников (= D2*Е2) и скопировать ее в ячейки F3:F9 при
помощи маркера авто-заполнения;
• просуммировать столбец F, для этого ввести в ячейку F10
формулу: = СУММ (F2:F9) – это вычисление общего фонда
заработной платы;
• выполнить: «Данные / Работа с данными / Анализ «чтоесли» / Подбор параметра»;
• в появившемся диалоговом окне указать:
в поле «Установить в ячейке»: целевую – $F$10;
в поле «Значение»: 15 000;
в поле «Изменяя ячейку»: номер ячейки с
минимальным окладом – $Н$2;

15.

• запустить процесс подбора параметра, нажав «ОК»;
• убедиться, что число, появившееся в целевой ячейке (F11),
равно 15 000, а все поля столбцов «Оклад», «Сумма» и ячейка
Н2 пере-считаны и заполнены новыми значениями.

16.

Задание 6. В штате компании состоят: пять программистов,
три менеджера, четыре аналитика, инженер, бухгалтер, два
начальника отдела и директор. Известно, что:
• программист получает зарплату в 1,5 раза больше
аналитика;
• менеджер – на 3 000 руб. больше программиста;
• инженер – в 2 раза больше аналитика;
• бухгалтер – на 4 000 рублей больше программиста;
• начальник отдела – в 3 раза больше аналитика;
• директор – на 5 000 рублей больше начальника отдела.
Необходимо определить: какими будут оклады сотрудников,
если суммарный фонд заработной платы составляет 500 000
рублей.

17.

Задание 7. Способности студентов и их трудолюбие на уроках
физкультуры приведены в таблице 2.
Таблица 2 – Исходные данные для задания 7
Необходимо определить суммарное расстояние, которое пробежал каждый студент за все занятия, если общий километраж
группы составил 500 км.

18.

Выполнение:
• составить математическую модель задачи:
• «общий километраж группы» = сумме «суммарных
расстояний каждого» = 500 км;
• «суммарное расстояние каждого» = «пробег за урок, км» ∙
«посещение»;
• подготовить лист Excel, внести данные и формулы (табл. 3);

19.

Таблица 3 – Лист Excel для задания 7

20.

• выполнить: «Данные / Работа с данными / Анализ «чтоесли» / Подбор параметра»;
• в появившемся диалоговом окне указать:
• в поле «Установить в ячейке»: целевую ячейку – $D$10;
• в поле «Значение»: 500;
• в поле «Изменяя ячейку»: номер ячейки Емели – $В$2;
• запустить процесс подбора параметра, нажав «ОК».

21.

Задание 8. Состав и соотношение стоимостей продуктов
минимальной потребительской корзины приведены в таблице 4.
Определить такую стоимость 1 кг хлеба, чтобы стоимость всей
корзины равнялась размеру минимальной пенсии: 5 200 руб.

22.

Таблица 4 – Минимальная продуктовая
потребительская корзина

23.

Контрольные вопросы:
1. Какой командой вызывается «Подбор параметра»?
2. Как задать первоначальное значение изменяемой ячейки,
если ее значение неизвестно?
3. Можно ли использовать «Подбор параметра», когда в задаче
нужно найти несколько неизвестных величин, и если «да», то
как?
4. Какие поля находятся в диалоговом окне «Подбор
параметра»?