2015-05-10
612
Лабораторная работа № 4
Подбор параметров
Введение
При решении различных задач часто приходится заниматься проблемой подбора одного значения путем изменения другого. Для этой цели весьма эффективно используется инструмент Excel Подбор параметра.
Инструмент Подбор параметра позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат.
При подборе параметра Excel использует итерационный процесс. Он проверяет для изменяемой ячейки одно значение за другим, пока не получит нужное решение.
По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций, или при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры.
Инструмент Подбор параметра находит только одно решение, даже если задача имеет несколько решений.
Инструмент Подбор параметра можно использовать в том случае, если результат вычисления при помощи формулы уже известен, но неизвестны значения, которые используются для получения данного результата.
Задание 1
Создайте новую электронную книгу в приложении MS Excel.
Подбор параметров для решения уравнений
Когда желаемый результат вычислений по формуле известен, но неизвестны значения, необходимые для получения этого результата, можно воспользоваться средством Подбор параметра. При подборе параметра Excel изменяет значение в одной конкретной ячейке до тех пор, пока вычисления по формуле, ссылающейся на эту ячейку, не дадут нужного результата.
Возьмем в качестве примера самое простое выражение, например, с=a+b. Нам известно, что а=20, а с=100. Необходимо вычислить b. Выражение на столько простое, что подсчитать чему равно b мы можем в уме, но давайте используем для этого инструмент Excel Подбор параметра.
Задание 2
С помощью инструмента Подбор параметра и известных значений а и с, вычислите значение b. Для этого:
1) проведите предварительную подготовку перед проведением вычислений и введите в ячейки А1, А2 и А3 подписи данных, чтобы нам было понятно где какие данные у нас находятся и какие вычисления производятся (рис. 1);
Рисунок 1 – Подписи данных
2) в ячейку В3 введите формулу для вычисления =В1+В2, а в ячейку В1 введите значение переменной а, которое нам известно, это значение 20;
3) перейдите на вкладку Данные и в группе инструментов Работа с данными, воспользовавшись выпадающим меню инструмента Анализ «что если», выберите инструмент Подбор параметра;
4) в диалоговом окне Подбор параметра в поле Установить в ячейке введите ссылку на ячейку с формулой В3, в поле Значение укажите ожидаемый результат (в нашем случае результат вычисления выражения нам известен и равен 100), в поле Изменяя значения ячейки укажите ссылку на ячейку, в которой будет храниться значение подбираемого параметра – В2 (рис. 2);
Рисунок 2 – Диалоговое окно Подбор параметра
5) после нажатия на кнопку ОК Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на ОК, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления первоначальных значений, которое были в ячейках до использования команды Подбор параметра, нажмите кнопку Отмена. В данном случае нажмите на кнопку ОК. Вы должны получить результат b=80.
В обычной практике инструмент Подбор параметра используется для более сложных вычислений. С его помощью можно находить решение уравнений или проводить анализ прибыли организации.
