Функция подбора параметра в MS Excel




Функция подбора параметра используется совместно с формулами. Это простой и легкий в использовании инструмент, предназначенный для вычисления одного из входных значений (параметров) некоторой функции для того, чтобы формула возвращала требуемый результат. Если Вы знаете, какой должен быть результат, программа сама найдет значение одной из нескольких ячеек, необходимое для достижения этого результата. Для применения данной функции необходимо ввести на лист MS Excel все входные параметры и выделить пустую ячейку для неизвестного входного параметра. В отдельную ячейку необходимо ввести формулу, зависящую от входных параметров, включая неизвестный параметр, т.е. в формуле будут содержаться ссылки на ячейки, содержащие входные данные, и на пустую ячейку. Далее вызывается функция подбора параметра (пункт горизонтального меню Данные / Анализ "что-если" / Подбор параметра). В всплывшем окне в поле ввода "Установить в ячейке" указывают ссылку на ячейку с формулой, в поле ввода "Значение" вводят конкретное ожидаемое числовое значение функции, а в поле ввода "Изменяя значение ячейки" указывают ссылку на пустую ячейку, в которой ожидается появление значения неизвестного параметра (рисунок 1).

Рис. 1. Окно функции подбора параметра

 

Обязательным условием применения функции подбора параметра является требование, чтобы в формуле была непосредственная или опосредованная (через другие формулы) ссылка на пустую ячейку, которая указана в поле "Изменяя значение ячейки"!

Использование функции подбора параметра рассмотрим на примере.

Пример. За какой срок денежный вклад увеличится вдвое при ставке 10% годовых по схеме простых процентов?

Решение. Наша задача – адекватно перенести все данные на лист MS Excel. Процентная ставка – 10% годовых. Коэффициент наращения A(t) = 2. Введем эти исходные данные в ячейки В2 и В3 соответственно. Ячейку В4 зарезервируем для результирующих данных, т.е. для срока вклада. Формула для расчетов: A(t) = 1 + i*t или 1 + i*tA(t) = 0. Введем вторую формулу в ячейку В6: = 1 + В2 * В4 – В3. Введенные данные представлены на рисунке 2.

Рис. 2. Компьютерная модель примера

 

После завершения ввода формулы в ячейке В6 отобразится выражение #ЗНАЧ!, т.к. в качестве операнда в ячейку В4 введен знак "? ", а не число. Введем туда временно любое числовое значение, например 1 (или 0), тогда в ячейке В6 отобразится значение –0,9. Далее для нахождения срока вклада используем функцию подбора параметра. Заполним всплывшее окно, как показано на рисунке 1. После нажатия кнопки " Ok " в ячейке В6 отобразится значение 10, это соответствует ответу на вопрос, что денежный вклад увеличится вдвое при ставке 10 % годовых по схеме простых процентов через 10 лет.

Пример 2. Какую сумму должен внести инвестор сегодня под 14 простых годовых процентов, чтобы накопить 210 тыс. руб: 1) за год; 2) за 2 года; 3) за 5 лет?

Решение. Мы построим одну модель для ответа на все три вопроса и будем последовательно менять значения в ячейке В3, содержащей срок вклада. Первоначально введем туда значение 1, соответствующее 1 году. В ячейку В4 в качестве суммы инвестиций также введем значение 1, так как мы не знаем истинного значения этой ячейки, и в процессе решения задачи с использованием функции подбора параметра оно буден найдено. Нам осталось ввести в ячейку В7 формулу зависимости накопленной суммы от суммы инвестиций, которая согласно формуле (1.5) будет иметь вид: =B4*(1+B3*B2). В ячейку D7 введем значение 210, чтобы помнить, что мы должны иметь в ячейке В7.

Как вы уже обратили внимание, большинство задач можно решить, зная формулу вычисления наращенной суммы!

Модель задачи готова и имеет вид, представленный на рисунке 3.

Рис. 3. Модель примера 9 без решения

Вызовем функцию подбора параметра (пункт горизонтального меню Данные / Анализ "что-если" / Подбор параметра), где в появившемся окне заполним: Установить в ячейке В7 Значение 210, Изменяя значение ячейки В4. MS Excel сообщит, что решение найдено, и, после нажатия клавиши Enter, оно отобразится на листе (рис. 4).

 

Рис. 4. Решение примера 2 (1) с использованием функции Подбор параметра

Далее меняем значение в ячейке В3 с 1 на 2 и повторяем операцию подбора параметра. Результат решения представлен на рисунке 5.

 

Рис. 5. Решение примера 2 (2)

Аналогично находим решение для 5 лет (рис. 6).

Рис. 6. Решение примера 2 (3)

 

Задание 1.

Решить уравнения:

 

1. 2х – ln x – 4 = 0

2. 4x = cos x

3. x3 – 5x + 0,1 = 0

 

Задание 2.

Необходимо подобрать цену обоев, чтобы общая стоимость материалов, необходимых для ремонта квартиры не превышала 80 000 руб.

 

Материал Цена, руб. Ед. изм. Количество  
Шпатлевка   кг    
Грунтовка   кг    
Водоэмульсионка   л    
Обои   рулон    
Клей   пакет    
Плинтус потолочный   м    
Линолеум   м2    
Плинтус   м    
Итого (целевая функция)  

 

Задание 3.

Необходимо распределить премию в сумме 100 000 руб. между сотрудниками отдела пропорционально их должностным окладам (подобрать коэффициент пропорциональности).

 

Фамилия Оклад, руб. Премия, руб.
Топорков А.Б. 80 000,00 0,00
Берёзкин В.Г. 60 000,00 0,00
Дубова Д.Е. 56 000,00 0,00
Рябинин И.К. 48 000,00 0,00
Вязов Л.М. 52 000,00 0,00
Ивочкина Н.О. 36 000,00 0,00
Итого (целевая функция)  

Коэффициент

 

 

 



Поделиться:




Поиск по сайту

©2015-2024 poisk-ru.ru
Все права принадлежать их авторам. Данный сайт не претендует на авторства, а предоставляет бесплатное использование.
Дата создания страницы: 2018-03-19 Нарушение авторских прав и Нарушение персональных данных


Поиск по сайту: