Darbe.ru

Быт техника Дарби
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Задачи на подбор параметра в excel

Задачи на подбор параметра в excel

Применение инструмента Подбор параметра

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

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

1) Что будет, если потребуется приобретать не 15, а 19 единиц оборудования?

2) Как изменится платеж по кредиту, если платежи осуществлять в начале периода?

3) Какие возможности у компании есть, если стоимость единицы оборудования будет колебаться в некоторых пределах?

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

1) Снизить размер кредита за счет уменьшения количества закупаемого оборудования.

2) Продлить срок выплаты кредитной задолженности.

3) Добиться снижения кредитной ставки.

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

· Вызвать надстройку Подбор параметра. Сервис-Подбор параметра. В Excel 2007 (Данные-Анализ «что-если»-Подбор параметра)

· Ввести в диалоговое окно условия, которые удовлетворяют заемщика, как это показано на рис. 1.

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

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

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

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

Вопросы для самопроверки

1. Какое основное правило следует помнить при использовании инструмента Подбор параметра?

2. Как вызвать инструменты Подбор параметра на экран пользователя?

3. Для чего используют строку «Значение:» в окне Подбор параметра?

4. Как можно увидеть результаты после задания необходимых значений в окне Подбор параметра?

Задачи на подбор параметра в excel

На этом шаге мы рассмотрим анализ данных: подбор параметра .

Рассмотрим следующий типичный вопрос, анализа "что-если" : "Каким станет общий доход, если объем продаж возрастет на 20%?" Если рабочий лист создан правильно, то, изменив значение в одной из ячеек, Вы увидите, что получится в ячейке, содержащей значение дохода. При выполнении процедуры подбора параметров используется противоположный подход. Если вы знаете, каким должен быть результат вычисления по формуле, то Excel подскажет Вам значения одного или нескольких входных параметров, которые позволят получить нужный результат. Другими словами, вы можете задать вопрос такого типа: "Какой рост продаж необходим для получения дохода в 1 200 000 руб.?" В Excel для этой цели предусмотрено два средства:

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

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

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

Рис. 1. Рабочий лист, иллюстрирующий использование процедуры подбора параметров

Вам известно, что в месяц Вы в состоянии погашать не больше 1 200 взятой ссуды. Вы также знаете, что кредитор даст вам ссуду под фиксированный процент (скажем 8,25%), рассчитывая на то, что Вы должны погасить за определенное время 80% ссуды (т.е. первоначальный взнос составляет 20%). Вопрос состоит в следующем: «Какова максимальная стоимость покупки, которую Вы себе можете позволить?» Другими словами, какое значение должно быть в ячейке С4, чтобы результат в ячейке С11 равнялся 1 200? Один способ решения — изменять значения в ячейке С4 до тех пор, пока значение в ячейке С11 не станет равным 1200. Более эффективный способ — позволить Excel найти ответ, то есть использовать процедуру подбора параметра.

Чтобы ответить на этот вопрос, выберите команду Сервис | Подбор параметра . Появится диалоговое окно, показанное на рис. 2.

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

Заполнение этого диалогового окна подобно составлению предложения: нужно получить 1200 в ячейке С11, изменяя значение в ячейке С4. Ввести эту информацию в диалоговое окно Подбор параметра можно, либо непосредственно набрав адреса ячеек с клавиатуры, либо щелкнув указателем мыши на нужных ячейках. Чтобы начать процесс подбора параметра, щелкните на кнопке OK . Через секунду Excel объявит, что решение найдено, и выведет окно Результат подбора параметра (рис. 3).

Рис. 3. Диалоговое окно Результат подбора параметра

В этом диалоговом окне будет отображено подбираемое значение и значение, предложенное Excel . В данном случае программа нашла точное значение. В ячейке С4 рабочего листа теперь будет находиться искомое значение ($199 663). Взяв такую ссуду, в месяц Вы должны будете погашать 1 200. На данном этапе у Вас есть две возможности:

  • Щелкнуть на кнопке OK , чтобы заменить прежнее значение найденным.
  • Щелкнуть на кнопке Отмена , чтобы вернуть рабочий лист в прежнее состояние — как до выполнения команды Сервис | Подбор параметра .

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

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

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

Графический подбор параметра

Excel предоставляет проведение подбора параметра с помощью манипулирования диаграммами. На рис. 4 показан рабочий лист, отображающий предполагаемый объем продаж развивающейся компании. Предположим, что из опыта известно, что рост объема продаж компаний, работающих в этой отрасли, может увеличиваться по показательному закону: =y*(b^x)

В таблице 1 перечислены и описаны все переменные этой формулы.

Рис. 4. Рабочий лист, иллюстрирующий рост объема продаж по показательному закону

Менеджеры компании знают, что объем продаж за первый год будет составлять 250000 и хотят довести его к 2010 г. до 10000000. Таким образом, для построения финансовой модели нужно знать точный коэффициент роста, ведущий к заданному объему продаж. В рабочем листе (рис. 4) находятся формулы для прогнозирования ежегодного объема продаж с использованием коэффициента роста в ячейке B1, а также диаграмма, отображающая ежегодный объем продаж.

Предположительным значением коэффициента роста было число 1,40. Как видно из графика, это число слишком мало, поскольку в результате объем продаж в 2010 г. составит только 7231366. Хотя для получения точного значения коэффициента роста можно использовать команду Сервис | Подбор параметра , есть и другой способ.

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

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

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

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

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

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

Пример 1. Решить уравнение 2х 3 -3х 2 +х-5=0.

1. Ячейку А2 используем для хранения неизвестного Х и запишем в нее 0. В ячейку В2 запишем уравнение, как показано ниже:

2. Встаньте на ячейку с формулой В2 и вызовите инструмент вызовите его – СервисàПодбор параметра…В открывшемся окне введите адрес изменяемой ячейки $A$2 и искомое значение функции 0, как показано:

3. После нажатия ОК Вы получите решение:

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

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

Пример 2. Фирма производит изделия и продает их по цене 90руб. Ежемесячные постоянные затраты составляют 5000руб., переменные затраты на единицу изделия – 30руб. Требуется:

a)определить количество изделий для получения прибыли 3000руб.;

b)определить точку безубыточности, т.е. вычислить количество изделий, при котором прибыль равна 0;

c)определить изменение прибыли для 10 следующих значений количества с шагом 5, а также прибыль при этих значениях количества для цен 80, 85, 95 и 100руб.

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

В ячейках В5, В6 и В7 записаны формулы. Для решения п. а) задачи, встаньте на ячейку с формулой прибыли В7 и запустите инструмент Подбор параметра:

Результат подбора будет выглядеть так – т.е. для получения прибыли в 3000руб/мес необходимо изготовить и продать 133 изделия в месяц:

Решение п. b) задачи также находится просто. Поскольку в точке безубыточности вся Прибыль равна нулю (весь доход равен всем затратам), то в качестве целевого значения ячейки В7 в Подборе параметра следует ввести 0 – Значение количества получим 83 шт/мес:

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

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

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

Подготовьте исходные данные: в ячейки C4:C13 запишите значения количества, в строке D3:G3 запишите значения цен, на пересечении строки и столбца с данными в ячейке C3 запишите формулу из ячейки В7:

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

Часть 9. Поиск решения

Для численного решения уравнений со многими неизвестными и ограничениями в Excel включен инструмент Поиск решения.

По умолчанию инструмент не установлен и его следует установить: вставьте дистрибутивный CD-диск и выберите в списке надстроек СервисàНадстройки… соответствующий флажок.

Если целевая функция и ограничения линейны, то решение состоит в нахождении множества чисел (х1, х2, … хn), минимизирующих (максимизирующих) линейную целевую функцию f(х1, х2, … хn)= c1х1+c2х2+… +cnхn при m<n линейных ограничениях-равенствах аi1х1i2х2+… +аinхn (где i=1,2, … m) и n линейных ограничениях-неравенствах хk>=0 (где k=1, 2, … n). Инструмент Поиск решения обеспечивает максимум 200 изменяемых ячеек хi при поиске решения (nмах=200).

В качестве содержательного примера рассмотрим задачу оптимизации туристических групп (экскурсионных пакетов). Российская туристическая фирма ежедневно отправляет в три отеля Анталии, Кемера и Мармариса (Турция) соответственно 30, 20 и 16 человек. Экскурсионная программа каждой группы состоит из рафтинга (спуск по горной реке на плоту), яхт-тура вдоль побережья и путешествия джип-сафари в турецкую глубинку. Стоимость экскурсий с трансфером на человека для отелей разных городов следующая:

РафтингЯхт-турДжип-сафари
Анталия
Кемер
Мармарис

При этом существуют ограничения на количество человек в экскурсии: рафтинг – 25 чел., яхт-тур – 20 чел., джип-сафари – 30 чел. От каждого отеля на каждую экскурсию должно быть послано не менее 5 чел.

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

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

РафтингЯхт-турДжип-сафари
Анталиях1х2х3
Кемерх4х5х6
Мармарисх7х8х9

В процессе решения надо найти такие х1…х9, чтобы получить при существующих ограничениях минимум целевой функции (стоимости), которая запишется так:

Запишем ограничения в виде формул. Ограничения на ежедневное количество человек в экскурсиях по городам:

Ограничения на ежедневное количество мест по видам экскурсий:

Другие ограничения – количество туристов от каждого отеля на экскурсию неделимо и больше 5: (х1, х2, … х9) >= 5 и (х1, х2, … х9) – целые числа

Теперь введем условие задачи – оптимизируемую модель в Excel в виде, наиболее удобном для дальнейших вычислений, как показано ниже:

Здесь, в ячейках G1:G9 размещены начальные значения неизвестных (х1, х2, … х9)=0. В ячейках С3:С8 записаны граничные значения числа туристов от отелей и в экскурсиях. В ячейках D3:D8 записаны формулы-заготовки для ограничений: =G1+G2+G3, =G4+G5+G6, =G7+G8+G9, =G1+G4+G7, =G2+G5+G8, =G3+G6+G9. Целевая функция записана в ячейке В1: =55*G1+20*G2+35*G3+65*G4+35*G5+20*G6+60*G7+25*G8+25*G9.

Нам осталось запустить поиск решения СервисàПоиск решения… и ввести адреса ячеек и ограничения, как на рисунке:

Результат поиска решения выглядит так:

Здесь в ячейках G1:G9 подобрано оптимальное количество туристов, дающее минимальную стоимость расходов, равную 2295$. Проанализируйте полученное решение.

Поэкспериментируйте: попробуйте вручную изменить подобранные значения, оцените значения целевой функции. Повторно вызовите инструмент Поиск решения, удалив условие (х1, х2, … х9) >= 5 или добавив новое условие, и выполните подбор.

Обобщим проделанную работу и выделим этапы решения задачи оптимизациисо многими неизвестными в Excel с помощью инструмента Поиск решения:

· анализ задачи, выделение свойств, параметров, ограничений;

· математическое описание оптимизируемой модели – введение обозначений, ограничений и создание целевой функции;

· грамотное размещение модели и поиск решения в Excel.

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

В состав Excel входит файл с примерами использования инструмента Поиск решения Solvsamp.xls. Он обычно расположен в папке Program FilesMicrosoft OfficeOffice11Samples. Каждый лист содержит один из шести примеров — "Структура производства", "Транспортная задача", "График занятости", "Управление капиталом", "Портфель ценных бумаг" и "Проектирование цепи". В примерах уже подобраны целевая и влияющие ячейки, а также ограничения. Примеры из Solvsamp.xls помогут разрешить ваши вопросы.

Отметим, что инструмент Поиск решения работает аналогичным образом в программе Calc OpenOffice.

Задачи на подбор параметра в excel

Задание 4. Использование функции “Подбор параметра” в Excel

Цель работы: изучение технологии использования табличного процессора MS Excel для поиска нужного решения.

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

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

Постановка задачи:
необходимо определить размер ежегодных отчислений на банковский счет так, чтобы на нем за 5 лет накопилось 25000 р. при условии 9% годовых.

Порядок выполнения
1. Задать исходные значения для решения задачи: “Ставка” (процентная ставка начислений по вкладу) и “Количество периодов” (количество лет) (Рис.1).

2. Определить ячейку “Плата” для подбора значения ежегодных отчислений, не вводя в нее конкретных данных.

3. Записать в ячейку “Размер вклада” финансовую функцию БЗ (возвращает будущее значение вклада на основе периодических постоянных платежей и постоянной процентной ставки), используя в качестве параметров ячейки “Ставка”, “Количество периодов” и “Плата”.

Синтаксис использования функции:
БЗ( ставка;кпер;плата; нз;тип)

ставка – процентная ставка за период;

кпер – общее число периодов выплат годовой ренты;

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

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

тип – число “0” или “1”, обозначающее, когда должна производиться выплата. Если аргумент тип опущен, то он полагается равным “0” (“0” – в конце периода; “1” – в начале периода).

4. Инициировать функцию “Подбор параметра” и заполнить диалоговое окно, указав в качестве целевой ячейки ячейку, в которой записана функция БЗ, а в качестве изменяемой – ячейку “Плата”.

5. Сохранить полученное решение.

6. Составить отчет.

Рис. 1. Использование метода “Подбор параметра”.

7. Отправить его преподавателю по электронной почте в виде вложенного файла или переписать файл преподавателю со своего носителя (флэш-накопителя, компакт-диска) или сдать работу преподавателю в распечатанном и скреплённом виде.

Задачи на подбор параметра в excel

Цель выполнения работы : освоить технологию работы со средством Excel – Подбор параметра для решения экономических и математических задач.

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

· в установленной ячейке должно быть число;

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

Общий подход при решении задач с помощью процедуры Подбор параметра.

Пример проведения финансового анализа с помощью надстройки Excel — Подбор параметра.

Предположим, что клиент банка желает положить на депозит некоторую сумму S на несколько Y лет. Естественно интересно узнать, каков будет коэффициент увеличения вклада Квк , и сколько Sy по истечении заданного периода будет лежать на счету клиента? Чтобы ответить на поставленные вопросы, понадобится выяснить условия банка, т.е. какова годовая процентная ставка. Тогда можно вычислить коэффициент увеличения вклада по формуле:

Квк = (1+ Проц ) Y , где

Квк – Коэффициент увеличения вклада;

Проц – Годовая процентная ставка;

Y – Срок вклада (количество лет, на которые вклад помещается в банке на хранение).

Тогда сумма возврата будет вычисляться по формуле: Sy = S * Квк

Установив начальные данные размера начального вклада, срока, на который помещается вклад в банке и условия банка (процентная ставка), можно заполнить таблицу в Excel , как показано на рис.1.

Рис. 1. Исходная таблица с условиями получения вклада через 5 лет

Клиент может задаться несколькими вопросами, «Что необходимо предпринять, чтобы, сумма возврата составляет 8000 руб.?». По всей вероятности, придётся либо увеличить срок хранения вклада, либо подобрать другой банк, в котором предлагают более высокую процентную ставку. Для того , чтобы получить ответы на, поставленные вопросы, можно воспользоваться приложением Excel – Подбор параметра, обращение к которому осуществляется на вкладке Данные из группы Работа с данными через пиктограмму . Предварительно целесообразно скопировать таблицу с данными на новый лист книги Excel , который можно назвать – Подбор параметра. После того, как будет заполнено диалоговое окно Подбор параметра (на рис 2 окно показано справа от таблицы), и нажата кнопка ОК , будет получен конечный результат.

Рис. 2. Последовательность выполнения операции Подбор параметра

Следует обратить внимание на то, что при изменении процентной ставки, автоматически меняется коэффициент увеличения вклада, это происходит от того, что эти два параметра связаны между собой функционально. Если в таблице значению процентной ставки задать формат представления, как целое число, то в ячейке C 5 будет отображаться число, округлённое до ближайшего целого, хотя точное значение рассчитанной процентной ставки составляет: 9,85605433071745% .

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

Рис. 3. Заполненное окно Подбор параметра

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

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

1) Снизить размер кредита за счет уменьшения количества закупаемых деталей.

2) Продлить срок выплаты кредитной задолженности.

3) Добиться снижения кредитной ставки.

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

Задание 1 . Рассмотреть два альтернативных варианта обращения в банк для получения кредита. 1) определить сумму кредита, который следует получить в банке, чтобы ежемесячные выплаты по кредиту не превышали 1700 рублей при соблюдении условий банка, и вычислить, какое количество деталей можно будет приобрести на полученную сумму кредита; 2) на сколько месяцев можно взять кредит, чтобы выполнить договор с заказчиком (выпустить 15 комплектов изделий), а ежемесячно выплачивать не более 1700 рублей на погашение кредита.

Для решения поставленной задачи, в качестве отправной точки, воспользуемся решённым примером (рис. 4), в котором рассмотрена задача по использованию функции ПЛТ (Платежи).

Рис. 4. Начальные данные для проведения анализа данных с использованием инструмента – Подбор параметра

· Создать таблицу с исходными данными (рис. 4) в новую книгу Excel .

· Вызвать инструмент Подбор параметра. Вкладка «Данные» Анализ «что — если» — Подбор параметра.

· Ввести в диалоговое окно «Подбор параметра» условия, ячейка C 12 должна содержать фиксированное значение (в рассматриваемой задаче, устанавливается минус 1700 руб.), а изменяемой ячейкой будет С 4 (это та ячейка, в которой должен отразится результат), как это показано на рис. 5.

Рис. 5. Так заполняется диалоговое окно Подбор параметра

После того как будет нажата кнопка ОК в окне Подбор параметра, произойдёт изменения значений данных во всех ячейках, которые связаны между собой формулами (функционально). В рассматриваемом примере, на рис. 6 в таблицах Потребности деталей и Платежи в ячейках C 4 , C 5 , C 8 , C 12 изменились значения. Посмотрите на окно «Результаты подбора параметра», в котором написано, что решение найдено. В отдельных случаях система будет сигнализировать, что не может найти решения, тогда потребуется изменить исходные данные.

Рис. 6. Результат подбора параметра при заданных условиях

Менеджер, который воспользовался инструментом Подбор параметра для решения поставленного задания, видит, что при фиксированном значении оплаты кредита (1700 руб. ежемесячно) на условиях банка, можно получить сумму в размере 38548, 65 руб., на которую можно закупить не более 11 деталей. Следовательно, составляя договор с заказчиком о выпуске оборудования, придётся учесть результаты проведённых вычислений с учётом возможности предприятия.

Решение математической задачи методом подбора параметра.

Задание 2 . Построить график функции y = 2,34 x 2 – 3,12 x + 1 в диапазоне изменения неизвестной x от 0,5 до 0,85, и найти (Вычислить) корни квадратного уравнения.

Известно, что уравнение описывает зависимость одной переменной (функции) от второй, независимой, называемой аргументом. Корнем уравнения называется такое значение аргумента, при котором функция равняется 0. На графике функции корням уравнения соответствуют точки пересечения функции с осью абцисс .

Для нахождения всех корней уравнения путём подбора параметра целесообразно выполнить следующие действия:

· Создать таблицу значений функции для n значений аргумента в заданном диапазоне. Для рассматриваемого примера, выбираем шаг изменения переменной равный 0,05, тогда получим результат в Excel , как показано на рис. 7.

Рис. 7. Таблица значений и график функции

· Выбрать в созданной таблице ячейку (или ячейки), содержащую значение, наиболее близкое к точке перемены знака (от положительных значений к отрицательным или наоборот). В таблице на рис. 4 знак результата вычисления функции меняется в ячейке C 5 и C 9 (можно выбрать и ячейку C 10).

· Выбрать команду Подбор параметра, и занести в диалоговое окно адрес C 5 – ячейка, в которой значение должно принимать ноль, а непосредственно подбор величины искомого корня уравнения должен быть в ячейке B 5 , как это показано на рис. 8.

Рис. 8. Диалоговые окна подбора параметра при задании условий и поиске решения

· Для поиска второго значения корня квадратного уравнения, потребуется провести аналогичные действия с ячейками C 9 , B 9 . В итоге будут получены два значения для корней квадратного уравнения x 1 = 0,5535417, x 2 = 0,7973565 .

голоса
Рейтинг статьи
Читайте так же:
Как в фотошопе сделать круг нужного диаметра
Ссылка на основную публикацию
Adblock
detector