Инструмент подбор параметра в excel

Анализ “что если” в Excel

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

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

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

Как использовать Подбор параметра (пример 1):

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

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

Выберите ячейку, значение которой необходимо получить. Каждый раз при использовании инструмента Подбор параметра, Вам необходимо выбирать ячейку, которая уже содержит формулу или функцию. В нашем случае мы выберем ячейку B7, поскольку она содержит формулу =СРЗНАЧ(B2:B6).
На вкладке Данные выберите команду Анализ “что если”, а затем в выпадающем меню нажмите Подбор параметра.
Появится диалоговое окно с тремя полями:
- Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем случае это ячейка B7 и мы уже выделили ее.
- Значение – требуемый результат, т.е. результат, который должен получиться в ячейке B7. В нашем примере мы введем 70, поскольку нужно набрать минимум 70 баллов, чтобы поступить.
- Изменяя значение ячейки – ячейка, куда Excel выведет результат. В нашем случае мы выберем ячейку B6, поскольку хотим узнать оценку, которую требуется получить на последнем задании.
Выполнив все шаги, нажмите ОК.
Excel вычислит результат и в диалоговом окне Результат подбора параметра сообщит решение, если оно есть. Нажмите ОК.
Результат появится в указанной ячейке. В нашем примере Подбор параметра установил, что требуется получить минимум 90 баллов за последнее задание, чтобы пройти дальше.

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

Выделите ячейку, значение которой необходимо изменить. В нашем случае мы выделим ячейку B4.
На вкладке Данные выберите команду Анализ “что если”, а затем в выпадающем меню нажмите Подбор параметра.
Появится диалоговое окно с тремя полями:
- Установить в ячейке – ячейка, которая содержит требуемый результат. В нашем примере ячейка B4 уже выделена.
- Значение – требуемый результат. Мы введем 500, поскольку допустимо потратить $500.
- Изменяя значение ячейки – ячейка, куда Excel выведет результат. Мы выделим ячейку B3, поскольку требуется вычислить количество гостей, которое можно пригласить, не превысив бюджет в $500.
Выполнив все пункты, нажмите ОК.
Диалоговое окно Результат подбора параметра сообщит, удалось ли найти решение. Нажмите OK.
Результат появится в указанной ячейке. В нашем случае Подбор параметра вычислил результат 18,62. Поскольку мы считаем количество гостей, то наш окончательный ответ должен быть целым числом. Мы можем округлить результат в большую или меньшую сторону. Округлив количество гостей в большую сторону, мы превысим заданный бюджет, значит, остановимся на 18-ти гостях.

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

Другие типы анализа “что если”

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

Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.
Таблицыданных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а полученные результаты свести в таблицу. Этот инструмент обладает широчайшими возможностями, поскольку выводит сразу множество результатов, в отличие от Диспетчера сценариев или Подбора параметра. В следующем примере видно 24 возможных результата по ежемесячным платежам за кредит:

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

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

Суть функции

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще: Excel удаление пробелов в конце строки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще: Автоматическая группировка строк в excel

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

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

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

Трансцендентные уравнения? «Подбор параметра» в Excel!

Нелинейные, трансцендентные уравнения функции одной переменной – это уравнения вида f (x) = 0, в которых нельзя найти алгебраическими методами корни. Функция f (x) – это, как правило, достаточно сложная и громоздкая функция, содержащая в своем составе.

. тригонометрические, логарифмические, степенные и иные нелинейные функции с различной глубиной вложенности. Например: f (x) = sin (3,14^x) + cos (x) = 0. Уравнения такого вида решаются численными методами.

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

Чуть-чуть истории и теории.

Вы задумывались когда-нибудь — откуда и зачем в головах людей, живших в XVI…XVII веках, родились понятия дифференциалов, производных, интегралов? Объяснение, в общем-то, достаточно простое и понятное – эти ученые искали аналитические пути решения прикладных практических задач. И успешно находили.

Мне сегодня видится приблизительно такая «лестница» с качественными «ступенями инструментов» математики для решения практических и научных задач, которую изобрело человечество:

1. Арифметика — сложение, вычитание, умножение, деление.

2. Алгебра – применение элементарных функций (степенной, логарифмической, тригонометрической, …) и алгебраических уравнений функции одной переменной.

3. Гауссовские системы линейных уравнений.

4. Численные методы решения трансцендентных уравнений.

5. Численные методы решения систем трансцендентных уравнений функций нескольких переменных.

6. Дифференцирование и интегрирование функций одной переменной.

7. Дифференцирование и интегрирование функций нескольких переменных.

8. Системы дифференциальных и интегральных уравнений.

9. Масса разнообразных новых и старых специальных методик и подходов мне не известных и известных, но, безусловно, существующих и работающих.

Предлагаю остановиться и разобраться с достаточно высокой четвертой ступенью «лестницы».

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

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

Трансцендентные уравнения. Два метода решения в Excel.

Если у вас на компьютере нет программы MS Excel, то расчеты можно выполнить в программе OOo Calc из бесплатного пакета Open Office.

Задач, которые требуют для получения ответа составления и решения трансцендентных уравнений, вокруг нас очень много. Это — задачи и физики, и теплотехники, и астрономии, и элементарной геометрии в обычной жизни… Инженерам-конструкторам и программистам в повседневной работе необходимо уметь составлять и быстро решать численными методами нелинейные уравнения. На мой взгляд — это один из критериев профессионализма. Более того, уравнения, которые решаются аналитически, сегодня иногда гораздо проще и быстрее при наличии вычислительной техники решить численными методами, поэтому нужно уметь это делать.

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

Рассмотрим пример из статьи «Расчет геометрии зубчатой передачи». Необходимо найти угол зацепления зубчатой передачи atw . Я обещал в той статье рассказать, как это делается. Выполняю обещание.

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

inv ( atw )=tg ( atw ) — atw =2* xs *tg ( a )/( z2 + T * z1 )+ tg ( at ) — at

Подставив данные из примера, рассмотренного в вышеупомянутой статье, получим после преобразований следующее уравнение:

inv ( atw )=0,020910

f ( atw )=tg ( atw )— atw -0,020910=0

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

f’( atw )=1/(cos ( atw ))^2—1

atw (i+1) = atw i — f ( atw ) i/ f’( atw ) i

Открываем файл Excel и начинаем работу.

Исходные данные будем традиционно писать в ячейки со светло-бирюзовой заливкой. Результаты расчетов будем считывать в ячейках со светло-желтой заливкой.

1. Инволюту угла зацепления inv( atw ) заносим

в ячейку D3: 0,020910

2. Значение угла зацепления в нулевом приближении atw 0 в радианах записываем

3. Итерационную формулу atw (i+1)= atw i— f( atw )i/ f’( atw )i заносим

в D5: =D4- (TAN (D4) -D4-$D$3)/(1/(COS (D4))^2-1) =0,591706

atw 1= atw 0- (tg ( atw 0) — atw 0- inv ( atw ))/(1/(cos ( atw 0))^2-1)

и копируем в ячейки D6… D14

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

atw =D13- (TAN (D13) -D13-$D$3)/(1/(COS (D13))^2-1) =0,389140

Решение найдено, расчет в Excel завершен!

Решение задачи ландшафтного дизайна с помощью сервиса «Подбор параметра» в Excel

Задача:

Вдоль отмостки стены дома длиной 14 метров необходимо разбить цветник в виде сегмента круга площадью ровно 16 квадратных метров. На сколько метров цветник будет отстоять от края отмостки по центру стены? Каким радиусом необходимо выполнить границу цветника?

1. Длину отмостки стены дома — хорды сегмента круга x в метрах записываем

в ячейку D17: 14,000

2. Площадь цветника – сегмента круга S в квадратных метрах вписываем

в D18: 16,000

3. Предположительное произвольное (не нулевое) значение центрального угла сегмента a в радианах пишем

Трансцендентное уравнение a / sin( a /2 ) -2*cos ( a /2) — (8* S / x ^2) *sin( a /2)=0 вводим

в объединенную ячейку E19F19: =D19/SIN (D19/2) -2*COS (D19/2) — (8*D18/D17^2)*SIN (D19/2)

Включаем сервис «Подбор параметра» в Excel: «Сервис» – «Подбор параметра». Пишем в появившемся окне все как на рисунке слева и нажимаем кнопку OK.

В появившемся новом окне видим, что решение найдено, снова нажимаем на кнопку OK.

Считываем искомое значение центрального угла сегмента a в радианах

в D19: 0,950057

При этом видим, что значение трансцендентного уравнения равно нулю; считываем

в объединенной ячейке E19F19: =D19/SIN (D19/2) -2*COS (D19/2) — (8*D18/D17^2)*SIN (D19/2) =0

4. Радиус наружной границы цветника – радиус сегмента круга r в метрах рассчитывается

в D20: =D17/2/SIN (D19/2) =15,305

r = x /2/sin( a /2)

5. Максимальная ширина цветника – высота сегмента круга h в метрах рассчитывается

в ячейке D21: =D20*(1-COS (D19/2)) =1.695

h = r *(1- cos( a /2))

Ответы получены, вторая задача успешно решена!

Я не приводил вывода использованных формул потому, что это не по теме поста, и, думаю, с геометрией и тригонометрией вы легко разберетесь. Будут вопросы – обращайтесь.

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

Краткие выводы

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

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

3. Использование инструмента «Подбор параметра» в Excel является сегодня, безусловно, наиболее оптимальным и эффективным методом решения нелинейных, трансцендентных уравнений функций одной переменной, а также проведения анализа типа «Что будет? Если…».

Умение применять в работе сервис «Подбор параметра» существенно повышает ваш уровень, как специалиста вообще, так и как пользователя Excel – в частности.

Читать еще: Сервис параметры в excel

Буду очень рад увидеть ваши комментарии к статье, уважаемые читатели!

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

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

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

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

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

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

Подготовка листа

Откройте новый пустой лист.

Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.

В ячейку A1 введите текст Сумма займа.

В ячейку A2 введите текст Срок в месяцах.

В ячейку A3 введите текст Процентная ставка.

В ячейку A4 введите текст Платеж.

Затем добавьте известные вам значения.

В ячейку B1 введите значение 100 000. Это сумма займа.

В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

Примечание: Хотя вам известна необходимая сумма платежа, не вводите ее как значение, поскольку она получается в результате вычисления формулы. Вместо этого добавьте формулу на лист и укажите значение платежа на более позднем этапе при использовании средства подбора параметров.

Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.

В ячейке B4 введите =ПЛТ(B3/12;B2;B1). Эта формула вычисляет сумму платежа. В данном примере вы хотите ежемесячно выплачивать 900 ₽. Это значение здесь не вводится, поскольку вам нужно определить процентную ставку с помощью средства подбора параметров, а для этого требуется формула.

Формула ссылается на ячейки B1 и B2, значения которых вы указали на предыдущих этапах. Она также ссылается на ячейку B3, в которую средство подбора параметров поместит процентную ставку. Формула делит значение из ячейки B3 на 12, поскольку был указан ежемесячный платеж, а функция ПЛТ предусматривает использование годовой процентной ставки.

Поскольку в ячейке B3 нет значения, Excel полагает процентную ставку равной 0 % и в соответствии со значениями из данного примера возвращает сумму платежа 555,56 ₽. Пока вы можете игнорировать это значение.

Использование средства подбора параметров для определения процентной ставки

На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что если» и выберите команду Подбор параметра.

В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула. В данном примере это ячейка B4.

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

В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение. В данном примере это ячейка B3.

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

Нажмите кнопку ОК.

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

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

На вкладке Главная в группе Число нажмите кнопку Процент.

Чтобы задать количество десятичных разрядов, нажмите кнопку Увеличить разрядность или Уменьшить разрядность.

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

Пошаговый анализ примера

Рассмотрим предыдущий пример шаг за шагом.

Подготовка листа

Откройте новый пустой лист.

Прежде всего добавьте в первый столбец эти подписи, чтобы сделать данные на листе понятнее.

В ячейку A1 введите текст Сумма займа.

В ячейку A2 введите текст Срок в месяцах.

В ячейку A3 введите текст Процентная ставка.

В ячейку A4 введите текст Платеж.

Затем добавьте известные вам значения.

В ячейку B1 введите значение 100 000. Это сумма займа.

В ячейку B2 введите значение 180. Это число месяцев, за которое требуется выплатить ссуду.

Теперь добавьте формулу, результат которой вас интересует. Например, используйте функцию ПЛТ.

Использование средства подбора параметров для определения процентной ставки

Выполните одно из следующих действий:

В Excel 2016 для Mac: на вкладке данные щелкните анализ » что если«, а затем — Подборпараметров.

В Excel для Mac 2011: на вкладке данные в группе Работа с данными нажмите кнопку анализ » что если«, а затем выберите вариант Подборпараметров.

Нажмите кнопку ОК.

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

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

В Excel 2016 для Mac: на вкладке Главная нажмите кнопку Увеличить разрядность или Уменьшить разрядность .

В Excel для Mac 2011: на вкладке Главная в группе число нажмите кнопку Увеличить разрядность или Уменьшить разрядность , чтобы установить число десятичных разрядов.

0 0 голоса

Рейтинг статьи