Top-office11.ru

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

Решение задач Подбор параметра

Решение задач Подбор параметра

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

В Excel встроены инструменты для решения задач статистического и инженерного анализа, сложных задач со многими неизвестными и ограничениями, в частности, решения уравнений и задач оптимизации. Эти инструменты поставляются в виде надстроек Поиск решения и Пакет анализа ; устанавливаются через пункт меню Сервис  Надстройки…, далее пометить пункты Поиск решения и Пакет анализа . Смотрите справку – клавиша F1.

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

В качестве примера разберем расчет прибыли от продаж, показанный на рис. 1.20. В ячейках А3, А4 и А7 записаны формулы – формулы для наглядности продублированы в скобках. Поскольку Другие затраты и Количество здесь постоянны, то нужную прибыль можно получить подбором Цены единицы или Себестоимости единицы товара. Попробуем получить прибыль 20000, изменяя Цену единицы . Проделайте следующие действия:

Встаньте на целевую ячейку А7. Выберите пункт меню С е рвис  П одбор параметра… . Введите значение ожидаемой прибыли. Для ввода адреса изменяемой ячейки, перейдите в нижнее поле и встаньте на ячейку А9 в таблице – адрес будет записан автоматически в поле. Нажмите ОК.

В следующем окне можно либо принять, либо отменить результат подбора параметра. Результат:

Самостоятельно попробуйте подобрать Себестоимость единицы , чтобы получить прибыль 20000 при Цене единицы =200.

Далее приводятся другие применения инструмента Подбор параметра .

Задачи оптимизации с одним неизвестным

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

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

Инструмент Подбор параметра был рассмотрен в предыдущем разделе для расчета прибыли от продаж. Здесь рассмотрим его применение для решения некоторого уравнения, например 2х 3 -3х 2 +х-5=0.

Решение задачи состоит из двух этапов. На первом этапе следует ввести уравнение в ячейку (В2), а в другую ячейку (А2) ввести некоторое ориентировочное значение, здесь 1:

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

Встаньте на ячейку В2 и вызовите его – С е рвис  П одбор параметра… .

В открывшемся окне введите искомое значение функции и адрес изменяемой ячейки:

После нажатия кнопки ОК Вы получите следующее решение:

Найденное решение приближенное, поэтому можно считать, что при х=1,918578609 значение уравнения 2х 3 -3х 2 +х-5 стремится к нулю, т.е. к 0,000107348. Смело установите для ячеек А2 и В2 числовой формат отображения данных и получите следующее:

Следует отметить, что уравнение может иметь более одного решения. Поэтому рекомендуется выполнить подбор параметра для разных начальных значений Х, указывая положительные, отрицательные, большие и малые значения. В нашем примере установите начальное значение А2=–1 и повторите подбор. Решение будет таким же.

Задача оптимизации затрат

С помощью подбора параметров можно решать задачи оптимального распределения ресурсов следующего плана. Требуется закупить составляющие (конфета карамельная, конфета шоколадная, упаковка печенья и мармелада) для комплектования подарочных наборов так, чтобы цена набора не превышала 100 руб. При этом известны соотношения цен относительно одной из компонент (здесь карамели): цена шоколадной конфеты в 2.5 раза выше цены карамели, цена печенья на 10 руб. больше карамели и цена мармелада в 8.5 раза выше цены карамели. В наборе должно быть 5-10 конфет карамель, 4-6 шоколадных конфет, 1-2 упаковки печенья и 1 упаковка мармелада. Рассчитать закупочные цены для разных комплектов.

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

В ячейки В4:В6 запишите формулы: =B3*2.5, =B3+10 и =B3*8.5. В колонки D3:D6 поставьте формулы вычисления сумм по строкам, а в D7 вычислите общую сумму: =СУММ(D3:D6). В итоге целевая функция – стоимость набора D7 есть C3* x +C4*2.5* x +C5*( x +10)+C6*8.5* x , где x =B3 цена одной компоненты – конфеты карамель. Параметры инструмента Подбор параметра заполните так:

Читать еще:  Графический драйвер не обнаружил совместимого графического оборудования

Подбор параметра и таблицы подстановки

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

Рассмотрим пример. Фирма производит изделия и продает их по цене 90руб. Ежемесячные постоянные затраты составляют 5000руб., переменные затраты на единицу изделия – 30руб. Необходимо определить точку безубыточности, т.е. вычислить количество изделий, при котором прибыль равна 0. Кроме того, требуется определить изменение прибыли для 10 следующих значений количества с шагом 5, а также прибыль при этих значениях количества для цен 80, 85, 95 и 100руб.

Решение. Введите в Excel исходные данные, приведенные ниже:

В точке безубыточности валовая прибыль равна валовым затратам, т.е. (В3*В4)-(В1+В2*В4)=0. Вызовите Подбор параметра , заполните параметры и нажмите ОК – в ячейке В4 будет вычислено значение 83.33 (рис. 17).

На следующем шаге рассчитаем 10 значений прибыли для следующих значений количества с шагом 5. Используем для этого таблицу подстановки с одним изменяемым параметром. Подготовьте исходные данные (рис. 2.35) : в ячейки C4:C13 запишите значения количества с шагом 5, а в колонке справа в строке выше (ячейка D3) — формулу из ячейки В7.

Примените инструмент Таблица подстановки к подготовленным данным: выделите диапазон C3:D13 (рис. 2.36), вызовите пункт Д анные  Т аблица подстановки… , укажите изменяемую ячейку ($B$4) и порядок расположения исходных данных (в строках) в окне запроса – рис. 2.36. После нажатия ОК в ячейках D4:D13 будут рассчитаны значения прибыли.

На последнем шаге рассчитаем значения прибыли для тех же значений количества при ценах 80, 85, 95 и 100руб. Используем для этого таблицу подстановки с двумя изменяемыми параметрами. Результат показан на рис. 2.37.

Для его получения подготовьте исходные данные : в ячейки F4:F13 запишите значения количества, в строке G3:J3 запишите значения цен, на пересечении строки и столбца с данными в ячейке F3 запишите формулу из ячейки В7. Примените инструмент Таблица подстановки к подготовленным данным: выделите диапазон F3:F13 (рис. 2.37), вызовите пункт Д анные  Т аблица подстановки… , укажите изменяемую ячейку по строкам ($B$4) и по столбцам ($B$3) в окне запроса – рис. 2.38. После нажатия ОК в ячейках G4:J13 будут рассчитаны значения прибыли.

Задания для самостоятельного выполнения:

Найдите решение уравнения х 3 +5х 2 -х+1=0

Найдите два решения уравнения х 2 -3х+2=0.

Найдите решение уравнения е х =20000.

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

Расчет суммы займа

Одна из наиболее востребованных задач, которую помогает решать этот модуль, — расчет возможной суммы займа или банковского кредита, исходя из ежемесячных платежей, срока и процентной ставки. Предположим, процентная ставка по кредиту составляет 10%, мы хотим взять деньги в долг на 1 год и можем платить 7 тыс. рублей в месяц.

В «Эксель» 2007 есть подходящая функция для расчета ежемесячных платежей по займу с известными процентами и сроком. Она называется ПЛТ. Синтаксис команды:

ПЛТ(ставка; кпер; пс; [бс]; [тип]), где:

  • Ставка – проценты по займу.
  • Кпер – число оплат (для годового кредита в случае ежемесячной оплаты это 12 раз).
  • ПС – первоначальная сумма.
  • БС – будущая стоимость (если вы намерены выплатить не всю сумму, а лишь ее часть, здесь указывается, какой долг должен остаться). Это необязательный аргумент, по умолчанию он равен 0.
  • Тип – когда производится оплата – в начале месяца или в конце. Этот параметр не обязательно указывать, если он не заполнен, принимается равным 0, что означает оплату в конце месяца.

Следует учитывать, что в атрибуте «Ставка» указывается не годовой процент, а ежемесячный, поэтому известную нам процентную ставку нужно разделить на количество платежей в году – 12.

Внесем на лист «Эксель» 2007 нужные данные. В качестве первоначальной суммы пропишем пока условные 1 00 000 рублей и займемся нахождением реальной суммы. Вызываем диалоговое окно подбора параметра. Отправной точкой для нахождения является сумма ежемесячного платежа. Формула ПЛТ возвращает отрицательные данные, так что мы вводим число со знаком «минус»: — 7 000 рублей в поле «Значение». Эту сумму мы должны получить в ячейке с платежом, меняя информацию в поле с займом.

Читать еще:  Восстановление загрузчика в Windows (Виндовс) 7

Прописываем все это в окне и запускаем подбор параметра «Эксель». В результате функция рассчитала, какой заем мы можем себе позволить — 79 621,56 руб.

Определение процентной ставки

Рассмотрим теперь обратную задачу. Банк выдает ссуду в 100 тыс. рублей на 2 года и хочет получить доход в 10 тыс. рублей. Какую минимальную процентную ставку нужно установить для получения такой прибыли?

К уже имеющейся на листе информации добавляем строку »Прибыль». Она рассчитывается по формуле:

Устанавливаем срок 24 месяца. Обратите внимание на поле «Ставка». Числовое значение должно выражаться в процентах. Для этого выберите числовой формат «Процент» в Excel: вкладка «Главная» — панель инструментов «Число» — кнопка с изображением процента.

Нахождение процентов

Вызываем функцию подбора и задаем ее аргументы. Ожидаемый результат будет записан в поле «Прибыль» изменением значения в ячейке «Ставка» и составлять 10 000 рублей. После запуска программа показывает необходимый процент, равный 9,32354423334073 %.

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

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

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

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

Уравнение в Microsoft Excel

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

Переход к подбору параметра для уравнения в Microsoft Excel

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

Подбор параметра для уравнения в Microsoft Excel

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

Решение уравнения в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12368 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

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

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

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

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

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

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

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

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

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

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

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

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

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

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

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

Читать еще:  Скачать программу Reason

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

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

Видео пример поиска решения в Excel

Подбор параметра в Excel позволяет подобрать какой-то определенный параметр, значение которого неизвестно. Чтобы было понятней, можно привести такой пример. Допустим, есть прямоугольник со сторонами A и B. Известно, что общая площадь этой фигуры составляет 400 квадратных метров, а сторона B 40 метров. Сторона A неизвестна и, соответственно, нужно ее найти. Для решения такой задачи необходимо заполнить рабочий лист программы теми данными, которые уже известны. Для этого нужно создать таблицу с 2 колонками и 3 строками (диапазон ячеек A1:B3).

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

  • в соседней ячейке для стороны B (ячейка B2) написать 40 (значение для стороны А остается пустым),
  • а в соседнем поле для площади прямоугольника (поле B3) написать следующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было сделано правильно, то в поле B3 должно быть значение 0. Затем надо выделить эту ячейку и выбрать в панели меню пункты: «Сервис Подбор параметра». В появившемся окне нужно указать то значение, которое должно быть получено в результате, т.е. 400. В строке «Установить в ячейке» будет указано поле «B3»: менять его не нужно, так и должно быть (сюда будет выведен результат). А в строке «Изменяя значение» необходимо выбрать неизвестный параметр, т.е. поле B1. После нажатия кнопки «ОК» программа выдаст результат: сторона А 10 метров, а в поле общей площади прямоугольника будет указано число 400.

Это была очень простая задача на уровне 3 класса, но с помощью такой функции можно решать и более сложные задачи. Например, вы решили приобрести себе автомобиль в кредит. Вы точно знаете, что сможете выплачивать ежемесячную выплату в размере 1000 $ (но не больше), а также, что банк выдает автокредит с процентной ставкой 6,5%. Суть задачи заключается в следующем: «Какова максимальная сумма машины, которую можно взять в кредит на таких условиях?». То есть теперь программа будет искать стоимость автомобиля, отталкиваясь от того, что ежемесячный платеж не должен превышать 1000 $. Такой пример является уже более сложным, а также более практичным, нежели расчет площади прямоугольника.

Заключение

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

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

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

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