Tw-city.info

IT Новости
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Подбор параметра в Excel: решаем задачки-нерешучки

Здравствуйте, уважаемые читатели! В прошлой статье мы научились моделировать результат при разных входных параметрах, выполняя анализ «что если». Сегодня же мы разберем обратную задачу, не менее частую, сложную и насущную. Пусть нам известен результат, и нужно знать, какими должны быть входные величины для его получения. То есть, нужно подобрать решение задачи. Возможно ли это в Excel? Конечно возможно, давайте разбираться!

Программа предоставляет нам два способа решения такой проблемы:

  1. Инструмент «Подбор параметра»
  2. Инструмент «Поиск решения»

Подбор параметра в Эксель

Это простой инструмент, который во многих случаях действительно выручает. Он варьирует значением в одной ячейке для получения определенного результата в другой. Как это работает?

Разберем на простом примере. Мы с Вами планируем открыть депозит с ежемесячным пополнением. Сейчас у нас на руках есть 10 тыс. у.е., но после окончания срока депозита, через 12 месяцев, хотим иметь капитал в 20 тысяч. Требуется посчитать, какую сумму нужно ежемесячно класть на депозит, чтобы через 12 месяцев накопить сумму в 20 тысяч у.е.

Вот наша таблица с расчетами:

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

Фактически нам нужно подобрать такое значение в ячейке В3, чтобы в В7 стало 20 000. Используем инструмент «Подбор параметра»:

  1. Жмем на ленте Данные – Работа с данными – Анализ «что если» — подбор параметра ;
  2. В открывшемся окне задаем данные для настройки:
    • Установить в ячейке: в этом параметре указываем ссылку на наше целевое значение, т.е. «Конечный капитал»;
    • Значение: здесь нужно указать то значение, которое должно быть в целевой ячейке, т.е. нужный результат вычислений. В нашем случае это 20 000;
    • Изменяя значение ячейки: Укажем ссылку на ячейку, значение которой нужно изменять, чтобы подбирать результат. В нашем примере это «Ежемесячный взнос»;

  1. Жмем Ок, программа будет искать решение. Когда оно будет найдено, Excel сообщит о завершении подбора. Нажимаем Ок в окне, чтобы принять найденное значение и записать его в ячейку, или Отмена, чтобы оставить все как было.

В нашем примере все сработало отлично, и мы узнали, что для получения капитала в 20 тыс, нужно ежемесячно добавлять на депозит по 736,55 у.е.

Иногда случается, что поиск решения не дал результата, тогда нужно проверить всё ли правильно:

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

  1. Осмыслите вычисления, которые предлагаете произвести программе. Точно ли заданные Вами параметры имеют решение? Если не имеют – сделайте их корректными.

Обычно этих шагов хватает, чтобы найти значение, удовлетворяющее наш запрос.

Инструмент «Поиск решения»

Как Вы убедились, подбор параметра отлично и безотказно работает практически во всех случаях. Но у него есть недостаток – он манипулирует лишь одним значением для изменения результата. А что, если нужно построить более сложную систему вычислений? Тогда используем «Поиск решения».

И снова рассмотрим на примере. Спланируем производственный процесс на месяц для получения максимальной прибыли. Вот наша таблица заготовка:

В таблице имеем такие поля:

  1. Минимальная партия – минимальное количество товара, которое нужно произвести для обслуживания уже существующих заказов;
  2. Максимальная партия – наибольшее количество товара, которое можно произвести, исходя из запасов сырья
  3. Норма рабочего времени – количество человекочасов, необходимых для производства одного изделия;
  4. Затраты рабочего времени – количество времени, которое будет затрачено на производство всего запланированного. Пусть у нас работает 20 работников по 8 часов 22 дня в месяце. Тогда сумма по этому полю должна составить 3520 ч.
  5. Себестоимость – стоимость производства одной единицы продукции
  6. Цена реализации – рыночная стоимость одной единицы продукции
  7. Валовая прибыль – прибыль, которая будет получена от реализации изготовленного товара.

Для упрощения, будем считать, что спрос на товар выше производственных возможностей, и всё произведенное будет продано. Так сколько чего нам нужно произвести, чтобы получить наибольшую выгоду, а персонал трудился ровно 3520 ч? Запускаем «Поиск решения»:

  1. Ищем на ленте Данные – Анализ – Поиск решения . Кликаем, откроется окно настройки;
  2. В поле «Оптимизировать целевую функцию» задаем ссылку на сумму по столбцу «Валовая прибыль»;
  3. В поле «До» выбираем «Максимум». В других случаях можно выбрать «минимум», или задать какое-то конкретное значение;
  4. В списке «Изменяя ячейки переменных» указываем все строки столбца «Производим»
  5. Далее нужно внести все оговоренные выше ограничения. Для этого жмем «Добавить» и в открывшемся окне выбираем ссылки на ячейки и параметры их ограничения:

Вносим все оговоренные ограничения, они отобразятся в списке окна настройки:

  1. Суммарные затраты времени должны равняться 3520 часов;
  2. Производимое количество больше или равно минимальной партии
  3. Производимое количество меньше или равно максимальной партии
  4. Производимое количество должно быть целым числом

  1. Выбираем метод решения в соответствии с рекомендациями разработчиков внизу окна настроек. Мы выберем линейный метод. Жмем «Найти решение», по завершению поиска программа сообщает о результате.

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

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

Экспериментируя с многочисленными настройками инструмента, можно детально управлять процессом поиска. На самом деле, «Поиск решения» — очень функциональная и многогранная надстройка, познать все азы которой можно на сайте разработчика: www.solver.com.

Кстати, если Вы не нашли на ленте этот инструмент – не отчаивайтесь, его просто нужно подключить. Для этого нажмите Файл – Параметры – Надстройки . Внизу в раскрывающемся списке «Управление» выберите «Надстройки Excel» и нажмите «Перейти». В открывшемся окне поставьте галку напротив «Поиск решения» и нажмите Ок. Вот и всё, он сразу же появится ленте!

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

Читать еще:  Как добавить картинку в excel

Если у Вас что-то не получилось – задавайте свои вопросы в комментариях, будем разбираться вместе. Если все вышло — сбросьте другу ссылку на эту статью. Пусть и он использует Эксель в полной мере!

Экспериментируйте, а я отправляюсь писать следующий пост. До новых встреч на страницах блога officelegko.com!

Добавить комментарий Отменить ответ

4 комментариев

Добрый день, Александр!

Есть задача которую я не могу понять с помощью какой формулы описать решение, причем прописать эти формулы в гугл таблице, но думаю суть та же будет если сделать это и в эксели
если в кратце: то например я знаю что мне надо накопить 20000, то если откладывать каждый месяц по 10 000 то через 2 месяца я добъюсь цели, как это описать формульно чтобы эксель показал что в зависимости от того сколько накапливается в месяц я смогу накопить 20000? чтобы программа показала мне время через которое я накоплю средства есть столбец месяцев с суммами того что накопил в этих столбцах при этом там есть и пустыми суммы за декабрь например. Просто бьюсь уже 5 дней не могу понять возможно ли решение для такой задачи или нет. ссылка на файл о чем речь :
https://docs.google.com/spreadsheets/d/1kyP2HwB8WFeAqJkkANC9TxQCsIv3K-44Wfe3xabfQeA/edit?usp=sharing

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

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

Даниил, в Excel есть функция, которая считает средние значения — СРЗНАЧ. Тогда формула расчета количества месяцев будет такая: =<Остаток суммы>/СРЗНАЧ<Диапазон с данными по ежемесячному внесению средств>). Естественно, в фигурных скобках я указал описания, а вы укажите соответствующие ссылки на ячейки и диапазоны ячеек

Функции программы Microsoft Excel: подбор параметра

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

Суть функции

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

Применение функции на практике

Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.

Имеем таблицу выплат заработной платы и премии работникам предприятия. Известны только премии работников. Например, премия одного из них — Николаева А. Д, составляет 6035,68 рублей. Также, известно, что премия рассчитывается путем умножения заработной платы на коэффициент 0,28. Нам предстоит найти заработную плату работников.

Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».

После этого, открывается окно подбора параметра. В поле «Установить в ячейке» нужно указать ее адрес, содержащей известные нам конечные данные, под которые мы будем подгонять расчет. В данном случае, это ячейка, где установлена премия работника Николаева. Адрес можно указать вручную, вбив его координаты в соответствующее поле. Если вы затрудняетесь, это сделать, или считаете неудобным, то просто кликните по нужной ячейке, и адрес будет вписан в поле.

В поле «Значение» требуется указать конкретное значение премии. В нашем случае, это будет 6035,68. В поле «Изменяя значения ячейки» вписываем ее адрес, содержащей исходные данные, которые нам нужно рассчитать, то есть сумму зарплаты работника. Это можно сделать теми же способами, о которых мы говорили выше: вбить координаты вручную, или кликнуть по соответствующей ячейке.

Когда все данные окна параметров заполнены, жмем на кнопку «OK».

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

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

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

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

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».

В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2). В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46. В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

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

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 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 .

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

Читать еще:  Как открыть сразу 2 файла excel

Значения параметров а и 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 параметра. Если Вам нужно найти решение от нескольких параметров, то используйте инструмент Поиск решения . Точность подбора параметра можно задать через меню Кнопка офис/ Параметры Excel/ Формулы/ Параметры вычислений . Вопросом об единственности найденного решения Подбор параметра не занимается, вероятно выводится первое подходящее решение.

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

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

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

Читать еще:  Пакет поиск решения в excel

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

Местоположение

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

Многие пользователи задают вопрос: где находится подбор параметра? Найти эту функцию не сложно. На Панели управления ищете вкладку Данные, затем в блоке Работа с данными нажимаете кнопку Анализ «что-если» и из выпадающего списка выбираете необходимый инструмент.

Использование

Окно подбора имеет несколько полей:

  1. Установить в ячейке – место, куда необходимо вставить ссылку на формулу.
  2. Значение – сюда вводится числовое значение, которое необходимо получить в ходе расчетов.
  3. Изменяя значение ячейки – ссылка на число, которое и будет решением задачи.

Важно! Отметим несколько моментов, на которые стоит обратить внимание при работе с этим инструментом:

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

Рассмотрим примеры применения подбора параметра.

Задача 1.

Найти решение уравнения с одной неизвестной 2*x^2 — x/3=12

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

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

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

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

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

Задача 2.

Рассчитать процентную ставку по кредиту в 10000$ сроком на два с половиной года.

Запишем исходные данные в таблицу.

Чтобы посчитать сумму платежа, воспользуемся встроенной функцией excel – ПЛТ. Она состоит из процентной ставки, периода выплат и величины кредита. Значением процента задаемся произвольно.

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

В итоге получаете следующие результаты:

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

Жми «Нравится» и получай только лучшие посты в Facebook ↓

Ссылка на основную публикацию
Adblock
detector