MS Excel 2007. Подбор параметра

Подбор параметра — это средство Excel для так называемого анализа «что, если». При этом значения ячеек-параметров изменяются так, чтобы число в целевой ячейке стало равным заданному.

Упражнение 1. Подбор параметра

В качестве примера возьмем следующую ситуацию:

Предположим, один наш сосед решил сделать подарок жене на день рождения. Да вот незадача: день рождения уже через месяц, в наличии всего 150000 рублей, а на подарок надо 500000 рублей. Пригорюнился наш герой, да увидел по телевизору рекламу АО «МММ»: цена одной акции — 15000 р., процентная ставка 5%, да еще перерасчет каждый день по формуле сложных про­центов — сказка, одним словом. Вот только не уверен сосед, что успеет 500 000 рублей за месяц накопить.

Предварительно составим таблицу данных:

• стоимость акции, количество акций, процентная ставка и срок вклада взяты по условию примера и соответственно отформатированы;

• коэффициент наращивания рассчитывается с помощью формулы сложных процентов: =(1+C4)^C5 (в этой формуле к 1 прибавляется значение ячейки С4, а результат возводится в степень, показатель которой равен количеству дней, так как перерасчет производится каждый день);

• для расчета суммы выплат используется следующая формула: =С2*Сб*СЗ.

Составив такую таблицу, мы уже видим, что, купив 10 акций АО «МММ», через 30 дней сосед получит 648 291 р. (рис.1). А сколько же нужно дней, чтобы получить 500000 р.? Обратите внимание, при изменении в таблице с исходными данными параметров стоимости, количества акций, процентной ставки или срока, соответственно из­меняется и сумма выплат. Пользуясь этой моделью, можно уловить влияние ис­ходных значений на конечный результат.

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

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

Рис.1. Подбор параметра

1. Выберите команду Данные>Работа с данными> Анализ «что- если»>Подбор параметра. На экране по­явится диалоговое окно Подбор параметра.

2. В поле Установить в ячейке: укажите целевую ячейку — ту, в которой мы хотим получить желаемое значение. В нашем случае это ячейка С8. Это можно сделать двумя способами: ввести адрес с клавиатуры или выделить ячейку на рабочем листе.

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

4. В поле Изменяя значение ячейки: укажите адрес ячейки, кото­рая должна меняться для получения желаемого результата.

5. Щелкните на кнопке ОК, чтобы запустить поиск нужного значения. На экране появится диалоговое окно Результат подбора параметра с сооб­щением о результате поиска (рис.2).

Рис.2. Результат вычислений

6. Щелкните на кнопке ОК, если полученные результаты удовлетворительны, или на кнопке Отмена, если от полученных результатов придется отказаться.

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

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

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

Упражнение 4. Таблица данных (подстановки)

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

Таблицу подстановки можно создавать с одной или двумя переменными. Значе­ния переменных задаются в виде списков. При создании таблицы подстановки список (списки) исходных значений задается в виде строки или (и) в виде столб­ца таблицы.

Это упражнение должно помочь нам научиться создавать оба типа таблицы под­становки. Для этого воспользуемся примером из предыдущего упражнения.

Таблица подстановки с одной переменной (одной ячейкой ввода) позволит нам рассчитать значения коэффициента наращивания и суммы выплат для разных сроков вкладов. В качестве базы возьмем таблицу расчета сложных процентов по акциям АО «МММ» из предыдущего упражнения, только сократим срок вклада с 30 дней до 10, так как минимальный срок вклада, который будет рассмотрен, - 10 дней. Применяемые формулы остаются неизменными (рис. 3).

1. На свободном месте создайте список значений подстановки для переменной одной или нескольких формул, это можно сделать в отдельном столбце или стро­ке. В нашем случае значения подстановки — различные сроки вклада, представ­ленные в диапазоне Е2:Е22 (см. рис.3). Значения подстановки будут пооче­редно копироваться в ячейку ввода для вычисления.

Рис.3. Рабочий лист перед созданием таблицы подстановки с одной ячейкой ввода

2. Формулу, в которую нужно подставлять значения, введите в качестве заголов­ка следующего столбца справа или в верхнюю строку правого столбца (если расчет будет начинаться именно со значения подстановки, представленного в таблице исходных данных), как в нашем примере (см. рис.3). Причем эта формула должна буква в букву копировать соответствующую формулу из таб­лицы с исходными данными. Так, формула в ячейке F2 нашего примера долж­на быть полностью идентична формуле в ячейке С6 =(1+C4)^C5.

3. При необходимости введите в следующей (справа) ячейке этой же строки до­полнительную формулу. Формула в ячейке G2 нашего примера должна быть полностью идентична формуле в ячейке С8: =С2*С6*СЗ. Точно так же можно создать столбцы для расчета еще нескольких формул.

Обратите внимание на обязательное наличие в формуле (формулах) прямых или кос­венных ссылок на ячейку, определенную в качестве ячейки ввода (в нашем случае ячейки С5).

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

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

5. Выберите команду Данные >Работа с данными>Анализ «что- если»>Таблица данных, чтобы вывести на экран диалоговое окно Таблица данных.

6. Поскольку наши значения подстановки расположены в столбце (будьте вни­мательны!), поместите курсор в поле Подставлять значения по строкам в: и укажите ячейку ввода (С5). Если значения подстановки располо­жены в строке (!), укажите соответствующую ячейку ввода в поле Подставлять значения по столбцам в: .

7. Щелкните на кнопке OK, чтобы запустить процесс создания таблицы данных (подстановки). Результат — составлена таблица данных (подстановки) с одной переменной (рис.4).

Рис.4. Созданная таблица подстановки с одной ячейкой ввода

Результатом таблицы подстановки является массив, который в нашем случае построен с помощью формулы ={ТАВЛИЦА(;С5)}(обратите внимание, формула заключена в фигур­ные скобки). Этот массив можно обрабатывать только как единое целое. Изменить от­дельные ячейки нельзя.

Чтобы дополнить уже созданную таблицу подстановки, введите дополнительные зна­чения в соответствующие ячейки, выделите диапазон, содержащий формулы и значения для подстановки и снова выберите команду Данные>Работа с данными>Анализ «что- если»>Таблица данных.

Взглянув на получившуюся таблицу подстановки, мы с уверенностью можем ска­зать, что если наш сосед продаст свои акции через 25 дней после приобретения, он получит на руки 507 953 рубля. А если он подождет до конца меся­ца, то сможет купить сапоги жене, а на оставшиеся деньги опять купить акции АО «МММ». Конечно при условии, что финансовая пирамида «МММ» к тому времени еще не рухнет...

Упражнение 5. Создание таблицы подстановки с двумя переменными

Создадим теперь таблицу подстановки с двумя переменными. С помощью этой таблицы мы сможем подсчитать сумму выплат, в зависимости от срока вклада и от количества приобретенных акций. В качестве базы опять возьмем таблицу рас­чета сложных процентов по акциям АО «МММ» из предыдущего упражнения, только внесем небольшие коррективы: стартовое количество акций будет равно пяти, а стартовый срок — 10 дням. Применяемые формулы остаются неизменны­ми (рис.5).

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

1. Введите значения подстановки для первой переменной в столбце рабочего ли­ста: диапазон F3:F23 — значения срока вклада.
2. Введите значения подстановки для второй переменной в строку, начиная с ячейки, расположенной справа сверху от верхней ячейки со значениями под­становки в столбце: диапазон G2:K2 — значения количества акций. Значения подстановки для обеих переменных будут поочередно скопированы в ячейки ввода для вычисления.

Количество значений подстановки не ограничено.

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

3. Формулу, в которую нужно подставлять значения столбца и строки, введите в ячейку пересечения столбца и строки со значениями подстановки (F2) (см. рис.5). Причем эта формула должна быть идентична формуле в ячей­ке С8: =С2*С6*СЗ.

Обратите внимание на обязательное наличие в формуле прямых или косвенных ссы­лок на ячейки, определенные как ячейки ввода (в нашем случае — ячейки СЗ и С5).

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

Выберите команду, Данные>Работа с данными>Анализ «что- если»>Таблица данных, чтобы вывести на экран диалоговое окно Таблица данных.

5. В открывшемся диалоговом окне в поле Подставлять значения по столбцам в: задайте ссылку на первую ячейку ввода, в которую будут поочеред­но вставляться значения строки (СЗ), в поле Подставлять значения по строкам в: задайте ссылку на первую ячейку ввода, в которую будут поочередно вставляться значения столбца (С5).

6. Щелкните на кнопке OK, чтобы запустить процесс создания таблицы подста­новки. Результат — это составленная таблица подстановки с двумя переменными (рис.6).

Рис.6. Созданная таблица подстановки с двумя ячейками ввода

Результатом таблицы подстановки является массив, который в нашем случае построен с помощью формулы ={ТАБЛИЦА(C3;C5)}. Этот массив можно обрабатывать только как единое целое. Изменить отдельные ячейки нельзя.

Чтобы дополнить уже созданную таблицу подстановки, введите новые значения в со­ответствующие ячейки, выделите диапазон, содержащий формулы и значения для под­становки и снова выберите команду Данные>Работа с данными>Анализ «что- если»>Таблица данных.

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

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

• можно купить всего восемь акций АО «МММ», при этом в конце месяца, про­дав их, сосед получит 518 633 р.;

• можно купить девять акций АО «МММ», при этом, продав их через 27 дней, сосед получит 504 117р.

Таким образом, весьма полезный инструмент Excel, Таблица подстановки, помог решить нам насущную проблему. Жизнь, однако, предлагает нам гораздо более изощренные таблицы подстановки!..

Подведение итогов

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

Поиск по сайту: