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

СОДЕРЖАНИЕ

 

Введение  4

1  Цель и постановка задачи 6

2  Теоретическая часть использования инструмента «подбор параметра»  7

2.1 Выбор команды «подбор параметра» меню Сервис  7

2.2 Задание значений в полях "установка в ячейке", "значение" и

      "изменяя значение ячейки"  7

2.3 Вывод диалогового окна «Результат» подбора параметра  8

3  Практическое решение задач с использованием инструмента «подбор  параметра»   9

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

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

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

Заключение 16

Список используемых источников 17

 

 

ВВЕДЕНИЕ

 

Табличный процессор – это комплекс взаимосвязанных программ, предназначенный для обработки электронных таблиц. Электронная таблица – это компьютерный эквивалент обычной таблицы, состоящей из строк и столбцов, на пересечении которых располагаются клетки, в которых содержится числовая информация, формулы или текст. Это наиболее удобная форма представления данных. Особенность электронных таблиц заключается в возможности применения  формул для описания связи между значениями различных данных, расчет по которым выполняется автоматически. Изменение любого входного данного приводит к пересчету всех связанных с ним значений, при этом происходит обновление всей таблицы [1].

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

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

Наиболее распространенными средствами для работы с документами, имеющими табличную структуру, является программа Microsoft Excel.

 

 

 

 

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

 

1 Цель и постановка задачи

 

Целью данной курсовой работы является изучение инструмента «подбор параметра» в табличном процессоре продукта Microsoft Excel и овладение навыками использования инструмента «подбор параметра» на примере решения различных задач.

Для достижения поставленной цели были поставлены задачи:

-  изучить теоретическую часть  инструмента «подбор параметра»;

-  на конкретных примерах овладеть навыками практической части решения задач с использованием инструмента «подбор параметра»;

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

-  выбор команды «подбор параметра» меню Сервис;

-  задание значений в полях "установка в ячейке", "значение" и "изменяя значение ячейки";

-  вывод диалогового окна «Результат» подбора параметра.

 

 

 

 

 

 

2 Теоретическая часть использования инструмента «подбор параметра»

 

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

 

2.1 Выбор команды «подбор параметра» меню Сервис

 

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

При подборе параметра Excel использует итерационный процесс. Он проверяет для изменяемой ячейки одно - значение за другим, пока не получит нужное решение [2].

 

2.2 Задание значений в полях "установка в ячейке", "значение" и "изменяя значение ячейки"

 

Команды задания значений:

- в поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки в которой необходимо задать значение.

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

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

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

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

 

2.3 Вывод диалогового окна «Результат» подбора параметра

 

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

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

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

 

 

 

 

 

 

3 Практическое решение задач с использованием инструмента «подбор параметра»

 

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

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

 

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

 

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

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

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

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

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

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

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

 

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

 

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

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

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

 

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

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

 

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

 

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

 

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

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

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

 

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

=60-А2

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

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

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

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

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

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

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

 

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

 

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

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

 

Рисунок 6 - Результаты подбора параметра

 

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

 

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

 

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