Ячейка должна содержать значение подбор параметра как исправить

Ошибки при работе со средством «Подбор параметра»

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

Ячейка
должна содержать формулу.
Это сообщение об ошибке появляется
тогда, когда ячейка, адрес которой
указан в поле ввода Установить
в ячейке диалогового окна Подбор
параметра, не содержит формулы.
Чаще всего причиной этой ошибки является
то, что вы в этом поле ввели адрес ячейки,
который должен быть указан в поле ввода
Изменяя значение ячейки. Чтобы исправить
эту ошибку, закройте
сначала сообщение об ошибке, а затем
введите в поле Установить в ячейке
адрес «правильной» ячейки, содержащей
формулу. Затем сно­ва
щелкните на кнопке ОК.

Введено
недопустимое значение. Следует ввести
число.
Это сооб­щение
об ошибке появляется тогда, когда вы в
поле ввода Значение кроме
числа ввели еще какие-то символы, которые
Excel
не может распознать
как числа. Чтобы исправить эту ошибку,
закройте сначала сообщение
об ошибке, а затем введите в поле Значение
правильное число (целое или десятичное).
Затем снова щелкните на кнопке ОК.

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

Ячейка
должна содержать значение.
Это сообщение об ошибке появляется
тогда, когда ячейка, адрес которой
указан в поле ввода Изменяя
значение ячейки
диалогового окна Подбор
параметра,
не со­держит
числового значения (а содержит, например,
текст или фор­мулу).
Чтобы исправить эту ошибку, закройте
сначала сообщение об ошибке,
а затем введите в поле Изменяя
значение ячейки адрес
«пра­вильной»
ячейки, содержащей числовое значение.
Затем снова щел­кните
на кнопке ОК.

Введенный
текст не является правильной ссылкой
или именем.
Это сообщение
об ошибке появляется тогда, когда в
поле ввода Установить
в
ячейке
или в поле ввода Изменяя
значение ячейки
диалогового окна Подбор
параметра
введено нечто, что Excel
не может распознать как ссылку
на ячейку. Чаще всего такая ошибка
возникает тогда, когда вы вручную
вводите адрес ячейки, а не указываете
ячейку путем щелчка на
ней. (Например, если вы вводите адрес
ячейки «русскими» бук­вами.)
Чтобы исправить эту ошибку, закройте
сначала сообщение об ошибке,
а затем введите в поле ввода правильный
адрес ячейки. Затем снова
щелкните на кнопке ОК.

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

Поиск
решения

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

Назначение
средства «Поиск решения»

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

Для
примера рассмотрим задачу вычисления
оптимальных цен и
количеств театральных билетов разной
категории таким образом, чтобы
стоимость всех проданных билетов
достигала определенной величины.
В
театре имеются билеты трех категорий:
детские, для взрослых и льготные (рис.
4.1).

В
целевой ячейке В6 подсчитывается сумма
стоимостей билетов всех категорий
(для каждой категории билетов их
стоимость подсчитыва­ется как
произведение количества билетов и их
цены).

Рис.
4.1.
Рабочий
лист для поиска оптимальной цены
театральных билетов

В
главе 1 при использовании средства
Подбор
параметра за
один раз
мы могли изменять значение только одной
переменной: значение
цены или количество билетов одной
категории. Средство Поиск
решения также
может изменять значения только одной
переменной (т.е.
может работать в режиме подбора
параметра). Однако, чтобы оценить
гибкость и мощь этого средства, следует
использовать его для
изменения нескольких переменных. Кроме
того, это средство мо­жет
добиться от целевой ячейки (точнее, от
формулы, содержащейся в
этой ячейке) не только определенного
заранее значения, но и максимально
или минимально возможного (для этой
формулы) значения.

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

• Цена
  детских билетов постоянна и равна 60
  руб.

• Цена
  билетов для взрослых постоянна и равна
  100 руб.

• Цена
  льготных билетов постоянна и равна 90
  руб.

• Билетов
  каждой категории можно продать не
  более 100 шт.

• В
  театре может быть аншлаг, но «лишних
  билетиков» нет.

Поиск
решения немедленно
найдет решение этой задачи: для того,
чтобы
в кассе оказалось ровно 24 тыс. руб.,
необходимо продать 100
детских, 90 для взрослых и 100 льготных
билетов. Поиск
решения по
специальному алгоритму перебирает
возможные комбинации зна­чений
количеств билетов разной категории
для того, чтобы найти искомое
решение.

Другой
пример решения задачи с ограничениями
показан на рис.
4.2. Здесь некий завод производит три
вида изделий, но может производить
в смену суммарно не более 300 изделий.
Необходимо определить при этом
ограничении, а также при условии, что
должно производиться
не менее 30 шт. любого изделия, количество
выпуска­емых изделий таким образом,
чтобы получить максимальный доход.
Ответ,
который дает Поиск решения, показан на
том же рис. 4.2.

Рис.
4.2.
Определение оптимальной структуры
производства

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить обратную задачу: подобрать такое значение параметра, при котором уравнение возвращает желаемый целевой результат X. Например, при a=3, требуется найти такое значение параметра b, при котором X равен 21 (ответ b=5). Подбирать параметр вручную — скучное занятие, поэтому в MS EXCEL имеется инструмент Подбор параметра

.

В MS EXCEL 2007-2010 Подбор параметра находится на вкладке

Данные,

группа

Работа с данным

.

Простейший пример

Найдем значение параметра

b

в уравнении

2*а+3*b=x

, при котором

x=21

, параметр

а=

3

.

Подготовим исходные данные.

Значения параметров

а

и

b

введены в ячейках

B8

и

B9

. В ячейке

B10

введена формула

=2*B8+3*B9

(т.е. уравнение

2*а+3*b=x

).

Целевое значение x

в ячейке

B11

введено

для информации.

Выделите ячейку с формулой

B10

и вызовите

Подбор параметра (на вкладке

Данные

в группе

Работа с данными

выберите команду

Анализ «что-если?»

, а затем выберите в списке пункт

Подбор параметра

…)

.

В качестве целевого значения для ячейки

B10

укажите 21, изменять будем ячейку

B9

(параметр

b

).

Нажмите ОК.

Инструмент

Подбор параметра

подобрал значение параметра

b

равное 5.

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

B9

последовательно вводить значения и смотреть, чтобы х текущее совпало с Х целевым. Однако, часто зависимости в формулах достаточно сложны и без

Подбора параметра

параметр будет подобрать сложно

.

Примечание

: Уравнение

2*а+3*b=x

является линейным, т.е. при заданных

a

и

х

существует только одно значение

b

, которое ему удовлетворяет. Поэтому инструмент

Подбор параметра

работает (именно для решения таких линейных уравнений он и создан). Если пытаться, например, решать с помощью Подбора параметра квадратное уравнение (имеет 2 решения), то инструмент решение найдет, но только одно. Причем, он найдет, то которое ближе к начальному значению (т.е. задавая разные начальные значения, можно найти оба корня уравнения). Решим квадратное уравнение x^2+2*x-3=0 (уравнение имеет 2 решения: x1=1 и x2=-3). Если в изменяемой ячейке введем -5 (начальное значение), то

Подбор параметра

найдет корень = -3 (т.к. -5 ближе к -3, чем к 1). Если в изменяемой ячейке введем 0 (или оставим ее пустой), то Подбор параметра найдет корень = 1 (т.к. 0 ближе к 1, чем к -3). Подробности в

файле примера

на листе

Простейший

.

Еще один путь нахождения неизвестного параметра b в уравнении 2*a+3*b=X — аналитический. Решение b=(X-2*a)/3) очевидно. Понятно, что не всегда удобно искать решение уравнения аналитическим способом, поэтому часто используют метод последовательных итераций, когда неизвестный параметр подбирают, задавая ему конкретные значения так, чтобы полученное значение х стало равно целевому X (или примерно равно с заданной точностью).

Калькуляция, подбираем значение прибыли

Еще пример. Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.

Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка

С13

). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли (

С8

), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение (

С14

) равно 0.

В структуре цены в ячейке

С9

(Цена продукции) введена формула Собственные расходы + Прибыль (

=С7+С8

). Стоимость договора (ячейка

С11

) вычисляется как Цена продукции + НДС (=

СУММ(С9:C10)

).

Конечно, можно подобрать значение вручную, для чего необходимо уменьшить значение прибыли на величину расхождения без НДС. Однако, как говорилось ранее, зависимости в формулах могут быть достаточно сложны. В этом случае поможет инструмент

Подбор параметра

.

Выделите ячейку

С14

, вызовите

Подбор параметра

(на вкладке

Данные

в группе

Работа с данными

выберите команду

Анализ «что-если?»

, а затем выберите в списке пункт

Подбор параметра

…). В качестве целевого значения для ячейки

С14

укажите 0, изменять будем ячейку

С8

(Прибыль).

Нажмите ОК.

Теперь, о том когда этот инструмент работает. 1. Изменяемая ячейка не должна содержать формулу, только значение.2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0

Примечание

: В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.

Подбор суммы кредита

Предположим, что нам необходимо

определить максимальную сумму кредита

, которую мы можем себе позволить взять в банке. Пусть нам известна сумма ежемесячного платежа в рублях (1800 руб./мес.), а также процентная ставка по кредиту (7,02%) и срок на который мы хотим взять кредит (180 мес).

В EXCEL существует функция

ПЛТ()

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

статьи про аннуитет

). Но эта функция нам не подходит, т.к. сумму ежемесячного платежа мы итак знаем, а вот сумму кредита (параметр функции

ПЛТ()

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

Подбор параметра

сумму займа пришлось бы подбирать в ручную с помощью функции

ПЛТ()

или использовать соответствующую формулу.

Введем в ячейку

B

6

ориентировочную сумму займа, например 100 000 руб., срок на который мы хотим взять кредит введем в ячейку

B

7

, % ставку по кредиту введем в ячейку

B8,

а формулу

=ПЛТ(B8/12;B7;B6)

для расчета суммы ежемесячного платежа в ячейку

B9

(см.

файл примера

).

Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:

• на вкладке
  
  Данные
  
  в группе
  
  Работа с данными
  
  выберите команду
  
  Анализ «что-если?»
  
  , а затем выберите в списке пункт
  
  Подбор параметра
  
  …;
• в поле
  
  Установить
  
  введите ссылку на ячейку, содержащую формулу. В данном примере — это ячейка
  
  B9
  
  ;
• введите искомый результат в поле
  
  Значение
  
  . В данном примере он равен
  
  -1800
  
  ;
• В поле
  
  Изменяя значение ячейки
  
  введите ссылку на ячейку, значение которой нужно подобрать. В данном примере — это ячейка
  
  B6
  
  ;
• Нажмите ОК

Что же сделал

Подбор параметра

? Инструмент

Подбор параметра

изменял по своему внутреннему алгоритму сумму в ячейке

B6

до тех пор, пока размер платежа в ячейке

B9

не стал равен 1800,00 руб. Был получен результат — 200 011,83 руб. В принципе, этого результата можно было добиться, меняя сумму займа самостоятельно в ручную.

Подбор параметра

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

инструмент

Поиск решения

. Точность подбора параметра можно задать через меню

.

Вопросом об единственности найденного решения

Подбор параметра

не занимается, вероятно выводится первое подходящее решение.

Иными словами, инструмент

Подбор параметра

позволяет сэкономить несколько минут по сравнению с ручным перебором.

Подбор параметра. Поиск решения.

Подбор параметра 1. Подбор параметров для нахождения значения, приводящего к требуемому результату. 2. Надстройка Поиск решения для расчета оптимальной величины по нескольким переменным и ограничениям; 3. Диспетчер сценариев для создания и оценки наборов сценариев «что – если» с несколькими вариантами исходных данных.

Использование средства Подбор параметров: — находит такое значение параметра, которое обеспечит требуемое значение, вычисленное по формуле, зависящей от этого параметра. — применяется тогда, когда вы знаете значение, которое должна возвращать формула, но не знаете входное значение для формулы, обеспечивающее желаемый результат вычислений. Чтобы применить средство Подбор параметра, на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите Подбор параметра (рис. 1). Рис. 1. Открытие средства Подбор параметра

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились: • формула для расчета; • пустая ячейка для искомого значения; • другие величины, которые используются в формуле. Ссылка на пустую ячейку должна обязательно присутствовать в формуле, так как она является переменной, значение которой ищет Excel. Пример 1. Определить количество книг по цене 23, 75 грн. , которые необходимо продать, чтобы объем продаж составил 10000, 00 грн.

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

Вычисление корней алгебраических уравнений. Пример 2. Для алгебраического выражения ax+ by+cz=d найти значение переменной с, если известны значения переменных: а=1; b=2; d=12; x=1; y=2; z=1. Ответ: с = 7

Использование средства Подбор параметров для решения экономических задач. Пример 3. Кредит берется на 15 лет с процентной ставкой 5, 75% при условии, что сумма ежемесячных платежей не должна превышать 11 000 у. е. Какова максимальная сумма кредита? Финансовая функция: ПЛТ(Ставка/12; Кпер; Пс).

Ответ: Максимально возможный кредит составит 1 324 646, 72 у. е. Решить и оформить задачи!!!!!

Ошибки при работе со средством Подбор параметра Ячейка должна содержать формулу. Это сообщение об ошибке появляется тогда, когда ячейка, адрес которой указан в поле ввода Установить в ячейке диалогового окна Подбор параметра, не содержит формулы. Чаще всего в этом поле введён адрес ячейки, который должен быть указан в поле ввода Изменяя значение ячейки. Закройте сообщение об ошибке, а затем введите в поле Установить в ячейке адрес ячейки, содержащей формулу. Введено недопустимое значение. Это сообщение появляется тогда, когда в поле ввода Значение кроме числа введены еще какие-то символы, которые Excel не может распознать как числа. Закройте сообщение об ошибке, а затем введите в поле Значение правильное число (целое или десятичное). К числу можно добавить знак денежной единицы или процента. Ячейка должна содержать значение. Это сообщение появляется тогда, когда ячейка, адрес которой указан в поле ввода Изменяя значение ячейки, не содержит числового значения (а содержит, например, текст или формулу). Чтобы исправить эту ошибку, введите в поле Изменяя значение ячейки адрес ячейки, содержащей числовое значение.

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

Поиск решения Для решения сложных задач, требующих применения линейного и нелинейного программирования, а также методов исследования операций применяется надстройка — Поиск решения. Чтобы использовать надстройку Поиск решения не обязательно знать методы программирования и исследования операций, но необходимо определять, какие задачи можно решать этими методами. Общие свойства для задач, решаемых с помощью Поиск решения: 1. Существует единственная целевая ячейка, содержащая формулу, значение которой должно быть максимальным, минимальным или равным, какому-то конкретному значению. 2. Формула в этой целевой ячейке содержит ссылки на ряд изменяемых ячеек. Поиск решения заключается в том, чтобы подобрать такие значения переменных в изменяемых ячейках, которые бы обеспечили оптимальное значение для формулы в целевой ячейке. 3. Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.

Терминология: Целевая ячейка – ячейка с формулой, в которой Поиск решения установит заданное значение или для которой найдет минимально или максимально возможные значения. Целевая функция – это термин из теории оптимизации, описывающий цель, которую мы хотим достичь, решая данную задачу (и используя для этого Поиск решения). Здесь «цель» заключается в том, чтобы формула в целевой ячейке достигла определенного значения. Изменяемые ячейки – ячейки, значения в которых будет варьировать Поиск решения для того, чтобы достичь требуемого значения целевой функции. Ограничения – условия, налагаемые на возможные значения изменяемых ячеек. Модель – совокупность адресов целевой и изменяемых ячеек, а также всех ограничений, используемых средством Поиск решения для решения текущей задачи, которые оно сохранило как единое целое.

Пример 4. Ширина параллелепипеда равна 4 и объём равен 80. необходимо найти длину и высоту параллелепипеда при условии, что все его параметры выражаются целыми числами.

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

Зачем нужна функция

Как было уже выше упомянуто, задача функции Подбор параметра состоит в нахождении начального значения, из которого можно получить заданный конечный результат. В целом, эта функция похожа на Поиск решения (подробно вы можете с ней ознакомиться в нашей статье – “Поиск решения в Excel: пример использования функции”), однако, при этом является более простой.

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

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

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

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

Итак, алгоритм действий следующий:

1. Переходим во вкладку “Данные”, в которой нажимаем на кнопку “Анализ “что если” в группе инструментов “Прогноз”. В раскрывшемся списке выбираем “Подбор параметра” (в ранних версиях кнопка может находиться в группе “Работа с данными”).
2. На экране появится окно для подбора параметра, которе нужно заполнить:
   • в значении поля “Установить в ячейке” пишем адрес с финальными данными, которые нам известны, т.е. это ячейка с суммой скидки. Вместо ручного ввода координат можно просто щелкнуть по нужной ячейке в самой таблице. При этом курсор должен быть в соответствующем поле для ввода информации.
   • В качестве значения указываем сумму скидки, которая нам известна – 560 руб.
   • В поле “Изменяя значение ячейки” вручную или посредством клика мышью указываем координаты ячейки (должна участвовать в формуле расчета суммы скидки), в которой планируем вывести начальное значение.
   • по готовности нажимаем OK.
3. Программа выполнит расчеты и выдаст результат в небольшом окошке, которое можно закрыть, нажав кнопку OK. Также найденные значения автоматически появятся в заданных ячейках таблицы.
4. Аналогичным образом можно посчитать цену без скидки для других товаров, если нам известна точная сумма скидки по каждому из них.

Решение уравнений с помощью подбора параметра

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

Например, нам нужно решить уравнение: 7x+17x-9x=75.

1. Пишем выражение в свободной ячейке, заменив символ x на адрес ячейки, значение которой нужно найти. В итоге формула выглядит так: =7*D2+17*D2-9*D2.
2. Щелкаем Enter и получаем результат в виде числа 0, что вполне логично, так как нам только предстоит вычислить значение ячейки D2, которе и является “иксом” в нашем уравнении.
3. Как было описано в первом разделе статьи, во вкладке “Данные” нажимаем кнопку “Анализ “что если” и выбираем “Подбор параметра”.
4. В появившемся окошке заполняем параметры:
   • В значении поля “Установить в ячейке” указываем координаты ячейки, в которой мы написали уравнение (т.е. B4).
   • В значении, согласно уравнению, пишем число 75.
   • В поле “Изменяя значения ячейки” указываем координаты ячейки, значение которой нужно найти. В нашем случае – это D2.
   • Когда все готово, нажимаем OK.
5. Как и в примере, рассмотренном выше, будут произведены вычисления и получен результат, о чем будет свидетельствовать небольшой окошко.
6. Таким образом, нам удалось решить уравнение и найти значение x, которое оказалось равным числу 5.

Заключение

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

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

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

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

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

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

В Microsoft Excel подобные задачи решаются с помощью стандартного меню. Подбор параметра в Excel 2007, 2010, 2013 и новее находится во вкладке «Данные», в группе «Работы с данными».

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

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

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

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

Определить — какая будет процентная ставка (по займу). Входными данными являются срок (36) и сумма (150000). Для начала их нужно отобразить в табличном представлении.

Не определена только процентная ставка. Чтобы посчитать ежемесячный, платеж следует воспользоваться функцией подбора параметров. После внесения всех данных задачи в таблицу, нужно переключиться во вкладку, отвечающую за работу с данными. Затем найти группу инструментов по работе с данными и в выпадающем списке «Анализ “что если”» выбрать опцию соответствующую подбору параметра.

Во всплывающем окошке в поле «Установить в ячейке» должна быть указана ссылка ячейки, в которой содержится основная формула (B4). В текстовое поле «Значение» необходимо ввести предположительную сумму ежемесячного платежа. К примеру, -5 000 (знак «минус» обозначает, что денежная сумма будет отдана). В третьем поле «Изменяя значение ячейки» – следует списать ссылку табличного элемента, в которой будет выведен искомый параметр ($B$3).

После клика по кнопке «ОК» в новом окне отобразится результат подсчета.

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

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

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

Подбор параметра также используют, если нужно найти какое-либо из значений в заданном уравнении. В качестве примера воспользуемся следующим выражением: 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 будет очень просто определять недостающие элементы. С помощью этой функции можно производить как табличные вычисления, так и решать уравнения с одним неизвестным.