РЕФЕРАТ
«Excel: решение задач сподбором параметров»
2010г.
Содержание
Введение
1 Теоретическаячасть
1.1 Подбор параметра
1.2 Поиск параметра
2 Практическаячасть
2.1 Пример решения задач сиспользованием функции “Подбор параметра”
2.2 Задача: Анализ суммывыплат по вкладу
2.3 Задача: Расчет размерапенсионных накоплений
2.4 Применение функцииподбора параметра при работе с диаграммами
2.5 Задача: Вычислениерадиуса описанной окружности по трем сторонам треугольника
Заключение
Список используемойлитературы
Введение
Электронная таблица, так же как и обычнаятаблица, представляет собой набор числовых и текстовых данных, размещенных вячейках. Данные, находящиеся в ячейке электронной таблицы, могут быть либовведены пользователем, либо определены (вычислены) по данным из других ячеек.На основе содержимого электронных таблиц могут создаваться диаграммы, служащиеиллюстрацией числовой информации.
С помощью Ехсе1 можно создавать самые различныедокументы, выполнять самые различные задачи, например:
* составлять всевозможные списки, отчеты,ведомости, бланки;
* оперативно выполнять вычисления различнойсложности;
* по данным таблиц строить динамически связанныес ними диаграммы, графики;
* решать сложные финансовые, экономические иматематические задачи и, в том числе, задачи статистического анализа и т.д.
Электронные таблицы Ехсе1 имеют поистине огромныйнабор возможностей. Однако обычно пользователь в своей работе применяет толькоих небольшую часть. Происходит это не только от незнания, но и от отсутствия вэтом острой необходимости. И действительно, все функции, заложенные в Ехсе1,можно условно разделить на те, знание которых очень желательно или простонеобходимо, и те, изучать которые можно по мере надобности.
В данной работе представлено описание функцииподбора параметра и решение задач с помощью этой функции .
1Теоретическая часть
1.1Подбор параметра
Специальная функция подбор параметра позволяет определить параметр(аргумент) функции, если известно ее значение. При подборе параметра значениевлияющей ячейки (параметра) изменяется до тех пор, пока формула, зависящая отэтой ячейки, не возвратит заданное значение.
Когда желаемый результат одиночной формулы известен, нонеизвестны значения, которые требуется ввести для получения этого результата,можно воспользоваться средством «Подбор параметра» выбрав команду Подборпараметра в меню Сервис. При подборе параметра 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. MicrosoftOffice Excel 2003. Учебный курс / В. Кузьмин, — СПб.: Питер: Издательская группа BHV, 2004. – 493 с.
3. Excel 2003. Эффективныйсамоучитель / В.В. Серогородский, — СПб.: Наука и техника, 2005. – 400 с.
4. Excel: Сборник примеров изадач / С.М. Лавренов, — М.: Финансы и статистика, 2003. – 336 с.