Excel: решение задач с подбором параметров

БОЛЬШАЯ ЛЕНИНГРАДСКАЯ БИБЛИОТЕКА - РЕФЕРАТЫ - Excel: решение задач с подбором параметров

Excel: решение задач с подбором параметров

РЕФЕРАТ

«Excel: решение задач с подбором параметров»

2010г.

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

Рисунок 1 - Изменение процентной ставки

Например, средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р. Рисунок 1

1.2 Поиск параметра

Можно рассмотреть процедуру поиска параметра на простом примере: нужно решить уравнение 10 * x - 10 / x = 15. Здесь параметр (аргумент) - x. Пусть это будет ячейка A3. Нужно ввести в эту ячейку любое число, лежащее в области определения функции (в данном примере это число не может быть равно нулю). Это значение будет использовано в качестве начального. Пусть это будет 3. Нужно ввести формулу =10*A3-10/A3, по которой должно быть получено требуемое значение, в какую-либо ячейку, например, B3. Теперь можно запустить функцию поиска параметра, выбрав команду Подбор параметра в меню Сервис. Вводимые параметры поиска:

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

· Ввести искомый результат в поле Значение.

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

· Кликнуть на клавише OK.

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

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

Задачу поиска параметра при налагаемых граничных условиях поможет решить специальная надстройка Microsoft Excel Поиск решения.

2 Практическая часть

2.1 Пример решения задач с использованием функции “подбор параметра”

Как известно, формулы в Microsoft Excel позволяют определить значение функции по ее аргументам. Однако может возникнуть ситуация, когда значение функции известно, а аргумент требуется найти (т.е. решить уравнение). Для решения подобных проблем предназначена специальная функция Подбор параметра. Рисунок 2

Рисунок 2 - Подбор параметра

Если в качестве начального значения в данном примере указать -3, тогда будет найдено второе решение уравнения: -0,5.

2.2 Задача: Анализ суммы выплат по вкладу

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

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

1. Нужно создать новой лист с именем Вклад.

2. В ячейку В4 ввести текст Размер вклада, а в С4 его значение 150000р.

3. В ячейку В6 ввести текст Срок вклада, а в С6 его значение 20.

4. В ячейку В8 ввести текст Процентная ставка, а в С8 его значение 5%.

5. В ячейку В10 ввести текст Коэффициент наращения, а в С10 формулу его вычисления =(1+С8)^С6.

6. В ячейку В13 ввести текст Сумма выплат, а в С13 формулу его вычисления =С10*С4.

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

Рисунок 3 - Анализ суммы выплат по вкладу

Используя Подбор параметра можно упростить процесс получения требуемого результата:

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

В поле Значение нужно ввести целевое значение 500 000, а в поле Изменяя значение ячейки ссылку на ячейку С4 и нажать ОК.

Рисунок 4 - Окно с результатами расчета

Появится окно с результатами расчета, которые после нажатия кнопки ОК будут внесены в таблицу. Рисунок 4. Как видно для получения суммы выплат в 500 000 руб. при 5% годовых за 20 лет требуется положить 188445 руб. Результат выполнения задачи показан на рисунке 5

Рисунок 5 - Результат выполнения задачи

2.3 Задача: Расчет размера пенсионных накоплений

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

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

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

Рис. 6 - Таблица для расчета размера пенсионных накоплений

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

=60-А2

то есть предполагается, что речь идет о мужчине, который выйдет на пенсию в 60 лет (С2), а также величина процентной ставки (D2).

Сумма накоплений рассчитывается с помощью функции по следующей формуле:

=БС(D2;C2;-B2*12; 0;1)

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

БС (ставка; кпер: плата; нз: тип)

Аргументы функции: ставка -- размер процентной ставки за период; кпер - общее число периодов выплат годовой ренты; плата - выплата, производимая в каждый период (это значение не может меняться на протяжении всего времени выплат), причем обычно плата состоит из основного платежа и платежа по процентам; нз -- текущая стоимость или общая сумма всех будущих платежей, начиная с настоящего момента (по умолчанию -- 0); тип -- число, которое определяет, когда должна производиться выплата (0 -- в конце периода, задается по умолчанию, 1 -- в начале периода).

Формула имеет такой вид, так как предполагается, что проценты начисляются не ежемесячно, а в начале, следующего года за предыдущий год. Допустим, необходимо определить, в каком возрасте будущему пенсионеру надо начинать выплаты, чтобы потом получить прибавку к пенсии в размере 1000 руб. Для этого следует выделить ячейку, отведенную для представления результата (в данном случае F2), и вызвать команду “Сервис » Подбор параметра”. Когда появится диалоговое окно Подбор параметра (рис. 7), адрес выделенной ячейки будет автоматически вставлен в поле “Установить в ячейке”. Нужно указать в поле “Значение” целевое значение - 1000. Нужно поместить курсор ввода в поле “Изменяя значение ячейки” и выделить ячейку А2, после чего ее адрес отобразится в указанном поле.

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

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

После выполнения всех установок нужно нажать кнопку К, и поиск нужного значения будет начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а также в исходной таблице (рис, 8). После нажатия кнопки 0К полученные значения будут вставлены в таблицу.

Рис. 8 - Результаты подбора параметра

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

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

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

2. Вызвать команду “Сервис » Подбор параметра”. В поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки.

3. Задать в поле “Значение” значение, которое должна содержать целевая ячейка

4. Указать в поле “Изменяя значение ячейки” адрес ячейки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение.

5. Нажать кнопку ОК, и нужный параметр будет подобран в диалоговом окне “Результат подбора параметра”. По окончании этого процесса в нем отобразятся результаты.

6. Нажать кнопку ОК, если вы хотите заменить значения в ячейках на рабочем листе новыми, или кнопку “Отмена” в противном случае.

2.4 Применение функции подбора параметра при работе с диаграммами

Средство подбора параметра применяется и при работе с диаграммами. Как это делается, показано в следующем примере.

На основании данных о суммах выручки от продажи изделий в три региона нужно построить с помощью мастера диаграмм гистограмму (рис. 8) В интерактивном режиме с использованием мыши нужно настроить высоту полосы «Среднее», и посмотреть, как Ехсе1 изменит высоту полосы «Северск» для получения целевого значения. Далее необходимо дважды щелкнуть на последней полосе (один раз - для выбора ряда, а второй -- для выбора полосы из ряда), которая представляет средние значения, и увеличить ее высоту путем перетаскивания маркеров размеров.

Когда кнопка мыши будет отпущена, откроется диалоговое окно “Подбор параметра. В поле “Установить в ячейке” появится имя ячейки В5, а в поле “Значение” -- число, соответствующее последнему значению, которое отображалось в поле подсказки. Курсор ввода будет находиться в поле “Изменяя значение ячейки”, поэтому остается лишь ввести в данное поле значение В2. Нужно щелкнуть на кнопке ОК, после чего появится диалоговое окно “Результат подбора параметра”, в котором будет содержаться нужная информация. Щелкнуть на кнопке ОК для возврата на рабочий лист. В ячейках уже содержатся новые значения и что в соответствии с ними настроена высота полос гистограммы (рис. 9).

Рисунок 8 - Гистограмма “Выручка от продажи изделий в трех регионах”

Рисунок 9 - Гистограмма “Выручка от продажи изделий в трех регионах” c применением подбора параметров

2.5 Задача: Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Вычисляем радиус описанной окружности R по формуле:

=a*b*c_/(4*S)

где а = 2, b = 4, с_ = 5 - стороны треугольника

S - площадь треугольника

Площадь треугольника S вычисляется по формуле Герона:

= КОРЕНЬ(p*(p-a)*(p-b)*(p-c_))

где а = 2, b = 4, с_ = 5 - стороны треугольника

р - полупериметр треугольника

Полупериметр треугольника p вычисляем по формуле:

=(a+b+c_)/2

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

Рисунок 10 - Вычисление радиуса описанной окружности по трем сторонам треугольника

Итак, радиус описанной окружности R по трем сторонам треугольника а, b, с вычислен. Если зафиксировать длины сторон b и с, то можно считать, что R вычисляется как функция а. Но Ехсе1 дает возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, надо определить величину а при R = 3. Для это нужно выделить ячейку В9, в которой вычисляется R. В меню нужно выбрать "Сервис/ Подбор параметра". Выводится диалоговое окно "Подбор параметра". Поле "Установить в ячейке:" уже содержит адрес выделенной ячейки B9. В поле "Значение:" нужно ввести 3. В поле "Изменяя значение ячейки:" нужно ввести адрес ячейки В2, содержащей величину стороны а (если щелкнуть мышью по этой ячейке, то в поле ввода окажется адрес $В$2). После нажатия кнопки "ОК" выводится новое окно "Результаты подбора параметра". Если увеличить разрядность числа в ячейке B9, то можно увидеть, что R достигло значения 2.9999172. При этом а = 1.515753171.

Результаты выполнения показаны на рисунке 11

Рисунок 11 - Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Единственное ли значение a соответствует R = 3? На этот вопрос изложенный метод подбора параметра не дает ответа.

Заключение

Список используемой литературы

1. Справка MS Excel

2. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2004. - 493 с.

3. Excel 2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005. - 400 с.

4. Excel: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика, 2003. - 336 с.