Учебный модуль

Изучив данный модуль:

Вы будете знать:

· В каких случаях можно использовать инструмент подбор параметра для решения задач.

· Алгоритм использования подбора параметра.
Вы будете уметь:

· Определять тип задач, в которых можно применить подбор параметра.

· Использовать инструмент подбор параметра для решения задачи.

 

Разберем назначение и способ применения инструмента анализа «Подбор параметра», решив следующую задачу:

Группе студентов за активное участие в спортивных мероприятиях выделена премия в размере 9500р. Эта сумма должна быть поделена между всеми студентами группы с учетом их вклада. Для этого каждому студенту присваивается коэффициент вклада, который задается в интервале от 0 до 1. Создадим модель задачи в электронной таблице:

Рисунок 1

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

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

Рисунок 2

Рисунок 3

Общий фонд получился равным 1410 руб, что значительно меньше выделенной суммы. Попробуем изменить ставку коэффициента со 150 рублей на 550рублей. Посмотрим на размер вычисленного премиального фонда:

Рисунок 4

Общий фонд получился равным 5170 руб, что опять меньше выделенной суммы, поэтому увеличим ставку коэффициента до 900 рублей.

Рисунок 5

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

Чтобы избежать столь длительного процесса Excel предлагает специальный инструмент – «Подбор параметра», который позволяет подобрать параметр, удовлетворяющий заданному условию в автоматическом режиме.

Для этого выделим сумму премиального фонда – ячейку D25 и выполним команду Данные-Анализ что-если--Подбор параметра. В диалоговом окне установим следующие параметры:

Рисунок 6

Получим результат вычислений, по которому за ставку коэффициента следует принять сумму 1010,64 рублей. На основе этого значения вычисляются премиальные суммы для каждого студента. Итак, решение найдено.

Рисунок 7

Сделаем вывод:

· использование инструмента «Подбор параметра» позволит избежать проведения анализа формулы методом проб и ошибок,

· позволяет прогнозировать результат на основе известных исходных значений или, наоборот определить, какими должны быть исходные значения для получения указанного результата.

Для применения инструмента «Подбор параметра» необходимо:

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

2. Выполнить команду Меню Данные--Анализ что-если -- Подбор параметра.

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

4. В поле " Значение " указать устанавливаемое числовое значение.

5. Установив курсор в поле " Изменяя значение ячейки ", выделить в таблице ячейку с изменяемым значением. В поле " Изменяя значение ячейки " будет представлен адрес изменяемой ячейки. Изменяемая ячейка не должна содержать формулу.

После задания всех необходимых данных нажатием кнопки ОК следует запустить поиск нужного значения. После нажатия кнопки ОК указанные значения будут вставлены в таблицу. Если необходимо повторить подбор параметра с использованием других значений, следует нажать кнопку "Отмена";. Значения в таблице в этом случае не изменятся.

Если поиск нужного значения продолжается слишком долго, то прервать его на время можно нажатием кнопки "Пауза". Щелчком на кнопке "Шаг"; задается режим отображения промежуточных результатов вычисления в диалоговом окне.

Пример 2: необходимо решить следующую задачу:

B=a*2; c=b-2; d=c*3. Найти значения a, b, c, d при условии a + b + c + d=100.

1. Для решения задачи зададим произвольное значение параметру a=2 и создадим таблицу согласно исходным данным:

Рисунок 8

Рисунок 9

2. Выделив ячейку с итоговой суммой, выполним команду Подбор параметра из меню Данные:

Рисунок 10

1. Искомые значения параметров найдены:

Рисунок 11

Замечание: Если в условии задачи неизвестно первоначальное значение изменяемого параметра, то его можно задать произвольным значением, например =1. В ходе подбора параметра эта ячейка примет искомое значение.

Пример 3: Разберем решение еще одной задачи с применением инструмента «Подбор параметра». Минимальная продуктовая потребительская корзина состоит:

Рисунок 12

Известно что:

Картофель - в два раза дешевле хлеба

Крупа - на два рубля дешевле хлеба

Масло растительное - в семь раз дороже хлеба

Масло сливочное - в шесть раз дороже хлеба

Молоко - на один рубль дороже хлеба

Мясо - в десять раз дороже хлеба

Рыба - в три раза дороже хлеба

Сахар - на два рубля дороже хлеба

Сыр - на пять рублей дороже мяса

Фрукты - на три рубля дороже рыбы

Макаронные изделия - на четыре рубля дешевле рыбы

Соль - в два раза дешевле хлеба

Овощи - на рубль дешевле хлеба

необходимо вычислить стоимость 1 кг хлеба так, чтобы стоимость продуктовой корзины равнялась размеру минимальной зарплаты 840 руб.

Ход решения задачи:

1. Создадим фрагмент электронной таблицы, приведенной на Рисунок 12. Столбец «Количество» содержит числовые данные. Для добавления к ним единиц измерения необходимо задать формат числа из контекстного меню: Формат ячеек-Число-Все форматы-Основной. К формату Основной необходимо добавить с клавиатуры единицу измерения, заключив ее в кавычки:

Рисунок 13

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

Рисунок 14

3. Создадим столбец «Стоимость заданного количества кг», в котором найдем произведение стоимости 1 кг продукта и количества данного продукта, а также найдем суммарную стоимость продуктовой корзины:

Рисунок 15

Рисунок 16

4. Применив подбор параметра со следующими значениями:

Рисунок 17

получим решение задачи:

Рисунок 18