Как сделать подбор параметра в excel

Обновлено: 07.07.2024

Аннотация: Цель работы: практическое освоение методов решения уравнений с помощью средств Microsoft Excel. Содержание работы: Анализ данных с помощью инструмента ExcelПодбор параметра. Нахождение значения аргумента (параметра) функции, соответствующего определённому значению функции (в том числе 0). Нахождение значений аргумента (параметра) функции при изменении вида её графика. Решение уравнений с использованием функции Подбор параметра. Порядок выполнения работы: Изучить методические указания. Выполнить задания методических указаний и варианта с использованием средств MS Excel. Оформить отчет, сделав выводы по заданиям.

МЕТОДИЧЕСКИЕ РЕКОМЕНДАЦИИ

При решении экономических и финансовых задач достаточно часто решение задачи приобретает вид математической модели (формулы), построенной на основе выявленных связей между изучаемыми элементами явления (результативного показателя) и самим явлением в целом. Изменяя составляющие элементы, можно проводить анализ изменения явления в целом. Таким образом, изучаются свойства и значения функции (явления) в тех или иных условиях. Этот анализ позволяет проводить инструмент MS Excel Подбор параметра. Рассмотрим возможности его применения на конкретных примерах.

Нахождение значения аргумента функции, соответствующего определённому значению функции

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

Например, одна ячейка содержит формулу, в которой есть ссылки на другую ячейку.

Значение в ячейке С1 представляет собой среднее арифметическое значение в ячейках А1 и В1:

Допустим, что для целей исследования необходимо найти значение, которое должна принять ячейка А1, для того чтобы ячейка С1 приняла значение 855.

Безусловно, можно самостоятельно путём перебора значений в ячейке А1 достичь необходимый результат. Однако, в целях минимизации затрат времени следует воспользоваться функцией Подбор параметра.

Для этого необходимо:

1) выполнить команду Подбор параметра из меню Данные > Анализ "что-если".

В результате появится запрос Подбор параметра:

2) в поле Установить в ячейке ввести ссылку или имя ячейки, содержащую формулу, для которой следует подобрать параметр. Автоматически в поле Установить в ячейке отображается имя ячейки, которая была активной на момент выполнения команды Подбор параметра. Кнопка свёртывания окна диалога, расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует нажать кнопку для вывода на экран диалогового окна.

3) в поле Значение ввести число, которое должно возвращать формула с искомым значением параметра. Например, 855.

4) в поле Изменяя значение ячейки указать ссылку на ячейку, содержащую параметр, значение которого требуется подобрать для получения требуемого результата. На эту ячейку прямо или косвенно должна ссылаться формула, содержащаяся в ячейке, адрес которой указан в поле Установить в ячейке. В нашем случае это А1.

В итоге диалоговое окно примет следующий вид:

5) нажать кнопку ОК для закрытия диалогового окна. После выполнения этого действия появляется запрос Результат подбора параметра, а искомое значение параметра отображается в ячейке А1:

Использование функции Подбор параметра для нахождения значения аргумента функции при изменении вида ее графика

Допустим, что для решения поставленной задачи нам предстоит проанализировать построенный в Ms Excel график функции y = 3x-5 в диапазоне аргумента от –3 до 6.

Для этого следует:

$3\dot А1-5$

1) в ячейки А1-А10 ввести значения от –3 до 6 с шагом 1; в ячейку В1 – ввести формулу и путём перетаскивания маркера заполнения скопировать эту формулу на ячейки В2-В10. В результате соответствующий участок листа примет следующий вид:

2) выделив диапазон В1-В10, выберите тип диаграммы "График" на вкладке Вставить в группе Диаграммы.

3) На вкладке Макет в группе Подписи нажмите кнопку Подписи данных, а затем выберите нужный параметр отображения.

4) На вкладке Конструктор в группе Данные нажмите Выбрать данные.

5) В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон А1-А10.

В результате должен быть построен график функции:

Далее предположим, что необходимо узнать значение аргумента данной функции, при котором значение самой функции будет равно 0.

Чтобы решить эту задачу с помощью построенного графика и функции Подбор параметра необходимо:

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

а затем выделить щелчком сам маркер

2) в меню Данные > Анализ выбрать функцию Поиск решения

3) если значение маркера данных получено из формулы, появится диалоговое окно Подбор параметра:

4) в поле Оптимизировать целевую функцию отображается ссылка на ячейку, содержащую формулу, в поле Значения – требуемая величина. Так, в данном случае следует указать ячейку В6 и значение "0" соответственно и нажать кнопку ОК.

При поиске решения можно изменять только одну ячейку.

При этом исходное значение аргумента в ряде данных сменится на значение, полученное в результате поиска решения 1,666667 (рис. 11. рис. 11.11).

Решение уравнений

$y=2\cdot x^<2> Поиск решения позволяет находить одно значение аргумента, соответствующее заданному значению функции (например, 0). Однако часто функция может принимать одно значение при нескольких значениях аргументов. То есть уравнение может иметь несколько корней. Например, функция -9$
может принимать значение 0 при двух значениях аргументов.

Однако, функция Поиск решения найдет только один корень уравнения – самый близкий к значению в ячейке, указанной в поле Оптимизировать целевую функцию.

Так, если попытаться решить указанное выше уравнение с помощью Ms Excel и встроенной в него функции Подбор параметра, то исходные данные можно представить в следующем виде:

Выполнив команду Подбор параметра из меню Данные > Анализ "Что-если", необходимо заполнить поля диалогового окна следующим образом:

В результате найденным корнем уравнения будет значение 2,121343 в ячейке А4 (рис. 11. рис. 11.14).

$y=2\cdot x^<2> Однако, это не единственный корень. В этом можно убедиться, решив уравнение или построив график функции -9$
.

Для построения графика следует:

в ячейки С4-С24 ввести значения от –10 до 10 с шагом 1; в ячейку D4 – ввести формулу 2*C4*C4-9 и путём перетаскивания маркера заполнения заполнить этой формулой ячейки D5-D24 ;
выделив диапазон D4-D24 , выбрать тип диаграммы График в меню Вставка > Диаграммы;
На вкладке Конструктор в группе Данные нажмите Выбрать данные.
В появившемся окне Выбор источника данных выберите Подписи горизонтальной оси. Задайте диапазон подписей оси диапазон С4-С24.

В результате должен быть построен график функции:

$2\cdot x^<2> Из графика видно, что уравнение -9=0$
имеет 2 корня, к тому же эти корни примерно равны –2 и 2. Одни корень 2,121343 нам уже известен.

Для поиска второго корня можно поступить двояко, используя пункт А или Б методических указаний ниже:

А. Изменим значение, например, в ячейке С12 (более близкое к ожидаемому корню). Выделим ячейку D12 и выполним команду Подбор параметра из меню Данные > Анализ "Что-если". Заполним поля запроса:

и после щелчка по кнопке ОК в ячейке С12 получим значение второго корня -2,12125:

Б. Построим график функции в интервале от -10 до 10.

Щелчком левой кнопки мыши на графике выделим ряд данных, содержащий маркер данных, близкий ко второму корню:

Сделаем активной ячейку D11 со значением функции, равным 9 и в меню Данные > Анализ "что-если" выберем Подбор параметра. Заполним поле Изменяя значение ячейки запроса:

и щелкнув по кнопке ОК, в ячейке С11 получим значение второго корня -2,1213207:

Следует обратить внимание, что значения корня, полученные в п.А и п.Б имеют несущественное отличие. Это вызвано следующим обстоятельством. По умолчанию команда Подбор параметра прекращает итерационные вычисления, когда выполняется 100 итераций, либо при получении результата, который находится в пределах 0,001 от заданного целевого значения. Если нужна большая точность, можно изменить используемые по умолчанию параметры командой Параметры меню Файл > Формулы. Затем на вкладке Параметры вычислений в поле Предельное число итераций введите значение больше 100, а в поле Относительная погрешность – значение меньше 0,001.

Если Ms Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне запроса Результат подбора параметра и прервать вычисления, а затем нажать кнопку Шаг, чтобы просмотреть результаты каждой последовательной итерации. Когда Вы решаете задачу в пошаговом режиме, в этом окне запроса появляется кнопка Продолжить. Нажмите ее, когда решите вернуться в обычный режим подбора параметра.

Задание

Решить уравнение с использованием инструмента Excel Подбор параметра двумя способами:

подбором аргумента для конкретного значения функции;
посредством изменения графика функции.

Удостовериться с помощью построения графика в количестве корней уравнения. Определить все корни.

Функция подбора параметра в программе Excel является одной из самых полезных, так как позволяет автоматически подобрать исходное значение для конечного результата. Это очень удобно, если в таблице у вас заполнены ячейки с результатами, но исходные данные известны не полностью. К сожалению, не все пользователю знают о наличии данного инструмента, а тем более как с ним работать.

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

Далее рассмотрим, как применить данную функцию на практике.

Пример применения на практике

Что за функция, зачем нужна?

Метод предназначен для случаев, когда нужно найти неизвестное значение в одиночной формуле, исходя из уже известного результата. То есть все составные части формулы (результат и входные данные) известны, при этом решение не может быть сформулировано полностью из-за отсутствия одного из входных параметров.

Функционал по поиску решений подобного рода задач встроен в программу Excel в качестве стандартного расширения, поэтому пользователю не нужно использовать сторонние библиотеки. Достаточно будет сделать несколько кликов по кнопкам расположенным на главной панели и программа сама определит недостающий элемент.

Использование функции

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

Третий шаг это подтверждение операции и вывод результата.

Пример использования функции

Для большей наглядности функцию подбора параметров в Экселе лучше сразу рассматривать на примере.

Для подтверждения операции следует кликнуть по соответствующей кнопке.

Функция подбора неизвестного параметра будет перебирать значение искомого элемента до момента получения результата формулы. Команда выдаст только одно решение.

Решение уравнений

Подбор параметра также используют, если нужно найти какое-либо из значений в заданном уравнении. В качестве примера воспользуемся следующим выражением: 2*а+3*b=x, где x=21, а=3, неизвестная переменная — b.

Для начала нужно заполнить таблицу.

Параметры а и b следует вводить в ячейки B2 и B3 соответственно. Табличный элемент B4 отведен для формулы =2*B2+3*B3. Переменная x в ячейке B5 указана в качестве примечания.

Необходимо выделить ячейку, в которой вписана формула (B4) и вызвать целевую функцию по определению параметра (инструкция вызова подробно изложена в предыдущем примере).

Затем вписать во второе поле (значение) результат (21), а в третье адрес ячейки B3, поскольку именно она будет изменяться.

Подтвердить действие кликом по соответствующей кнопке.

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

Конечно, искомый параметр можно определить в ручном режиме, путем последовательного ввода в ячейку B3 значений до момента пока оно не совпадет с целевым. Однако зачастую формулы имеют более сложную структуру, поэтому решить уравнение не используя автоматический подбор, будет сложно.

Для закрепления материала решим еще одно уравнение – 15*x+18*x=46. Для начала нужно записать формулу в ячейку B2. Вместо x необходимо указать ссылку на табличный элемент, где будет отображен результат, в данном случае A2.

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

Во всплывающем окне, в первом верхнем текстовом поле нужно вписать ссылку ячейки, содержащей формулу (B2). Во втором поле — число из уравнения после знака равно, то есть 46. В третьем поле должна быть ссылка на ячейку со значением x, в данном случае это A2.

После того как все поля заполнены, нужно подтвердить операцию. На экране в новом всплывающем окне отобразиться правильное решение уравнения. Значение x будет равно 1,39393939393939.

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

Чтобы применить средство Подбор параметра, выполните команду Данные → Работа с данными → Подбор параметра. Откроется одноименное диалоговое окно, в котором надо заполнить все поля ввода, а затем щелкнуть на кнопке ОК. В результате появится диалоговое окно Результат подбора параметра.

Диалоговое окно Подбор параметра очень просто в использовании — в нем надо заполнить всего три поля ввода: Установить в ячейке, Значение и Изменяя значение ячейки, которые показаны на рис. 1.4.

Рис. 1.4. Диалоговое окно Подбор параметра

Вот какую последовательность действий надо выполнить в открытом диалоговом окне Подбор параметра.

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

Заполнив все три поля ввода диалогового окна Подбор параметра, для начала работы данного средства щелкните в этом окне на кнопке ОК. После этого появится диалоговое окно Результат подбора параметра, которое сообщит, что решение найдено. Обратите внимание на два числа, отображаемые в этом окне как Подбираемое значение и Текущее значение.

Подбираемое значение, — это то значение, которое вы указали в поле Значение диалогового окна Подбор параметра, а Текущее значение — то значение, которое Excel смогла добиться от формулы (указанной в поле Установить в ячейке диалогового окна Подбор параметра) при подборе параметра, заданного в поле Изменяя значение ячейки того же окна Подбор параметра. Если числа Подбираемое значение и Текущее значение совпадают, это означает, что Excel действительно нашла решение задачи.

Рис. 1.5. Преобразование значения температуры по Фаренгейту в значение температуры по Цельсию

Чтобы удовлетворить свое любопытство, вы должны выполнить такие действия.

Выберите команду Данные → Работа с данными → Подбор параметра. Откроется диалоговое окно Подбор параметра.
В поле ввода Установить в ячейке введите А2 или щелкните на ячейке А2.
В поле ввода Значение введите число 20.
В поле ввода Изменяя значение ячейки введите А1 или щелкните на ячейке А1.
Щелкните на кнопке ОК.

После этих действий откроется диалоговое окно Результат подбора параметра, где оба значения, Подбираемое значение и Текущее значение, будут равняться числу 20. Таким образом, Excel найдет искомое решение, которое будет отображаться в ячейке А1 как число 68.

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

Читайте также: