Excel_2010

Рисунок 9.4. Настройка сценария

7.Нажмите кнопку ОК. MS Excel закроет окно диалога Добавление сценариев и откроет окно

Значение ячеек сценария (Рисунок 9.5).

Рисунок 9.5. Ввод значений ячеек сценария

8.Задайте значения для всех изменяемых ячеек, следуя вашему сценарию.

9.Нажмите кнопку ОК. MS Excel сохранить заданные вами значения для изменяемых ячеек и откроет окно диалога Диспетчер сценариев (Рисунок 9.6), содержащее ваш вновь созданный сценарий.

Рисунок 9.6. Диспетчер сценариев

Если вы хотите создать более одного сценария одновременно, вам нужно повторить шаги 3-9 для каждого нового сценария.

Переключение между сценариями.

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

1.Откройте окно диалога Диспетчер сценариев: на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите команду Диспетчер сценариев.

2.В списке Сценарии выделите сценарий, «работу» которого вы хотите увидеть.

160

3. Нажмите кнопку Вывести (Рисунок 9.7).MS Excel обновит изменяемые ячейки.

Рисунок 9.7. Применение сценария

Изменение сценария

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

1.Откройте окно диалога Диспетчер сценариев: на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите команду Диспетчер сценариев.

2.В списке Сценарии выделите сценарий, который вы хотите изменить.

3.Нажмите кнопку Изменить (Рисунок 9.7). MS Excel откроет окно диалога Изменение сценария (Рисунок 9.8).

Рисунок 9.8. Изменение сценария

4.Если необходимо, измените основные параметры сценария: название, изменяемые ячейки, примечание.

5.Нажмите кнопку ОК.

6.В окне диалога Значения ячеек сценария измените значения изменяемых ячеек для данного сценария.

7.Нажмите кнопку ОК.

8.Закройте окно Диспетчера сценариев.

Удаление сценария

Если сценарий больше не нужен, вы можете его удалить. Для этого:

1.Откройте окно диалога Диспетчер сценариев: на вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите команду Диспетчер сценариев.

161

2.В списке Сценарии выделите сценарий, который вы хотите удалить.

3.Нажмите кнопку Удалить.

4.Закройте окно Диспетчер сценариев.

Создание итогового отчета

Сценарии подходят для исследования различных вариантов, но вы ограничены просмотром одного сценария в каждый момент времени. Чтобы одновременно видеть все определенные вами сценарии, вы можете сгенерировать автоматический итоговый отчет. Для этого:

1.На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и выберите команду Диспетчер сценариев.

2.В окне диалога Диспетчер сценариев нажмите кнопку Отчет.

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

В большинстве случаев установка «структура» является наиболее предпочтительной (Рисунок 9.9).

4.В поле Ячейки результата укажите ячейки, которые вы хотите включить в отчет.

Это ячейки, которые меняются в зависимости от использованных вами значений сценария.

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

Рисунок 9.9. Настройка параметров отчета

5. Нажмите кнопку ОК для создания итогового отчета.

162

Рисунок 9.10. Итоговый отчет в виде структуры

Рисунок 9.11. Итоговый отчет в виде сводной таблицы

Итоговый отчет всегда создается на новом листе. Название листа зависит от выбранного вами типа отчета: Структура сценария (Рисунок 9.10) или Сводная таблица по сценарию

(Рисунок 9.11).

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

9.1.3.Использование средства Подбор параметра для получения требуемого результата

Основные понятия

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

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

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

Известно:

сколько денег вам требуется,

на какой срок необходимо их занять

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

163

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

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

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

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

Рассмотрим простой пример. Нам необходимо выяснить, какую заработную плату нужно начислить Иванову И.И., чтобы на руки он получил 42000 рублей (Рисунок 9.12)

Рисунок 9.12. Пример исходной таблицы

В ячейке С3 введена формула: =В3-В3*$E$3.

Формула ссылается на две ячейки: В3 и E3. При этом ячейка E3 содержит значение налоговой ставки, которое не может быть изменено. Следовательно, для получения нужного результата в ячейке С3 мы можем изменять только содержимое ячейки В3.

Вопрос состоит в следующем: какое значение в ячейке В3 приведет к результату 42000 в ячейке С3?

Для ответа на этот вопрос выполните следующие действия:

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

выберите команду Подбор параметра.

2.В окне диалога Подбор параметра задайте следующие значения (Рисунок 9.13):

a.В поле Установить в ячейке: укажите адрес результирующей ячейки, содержащей расчетную формулу. В нашем случае это ячейка С3.

b.В поле Значение: введите величину, которую вы хотите получить в качестве результата вычисления формулы. В нашем случае это 42000.

164

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

3.Нажмите кнопку ОК.

Рисунок 9.13. Подбор параметра для ячейки С3

4.MS Excel отобразит окно Результат подбора параметра содержащее отчет о выполненной работе (Рисунок 9.14). При этом в исходной таблице будет изменено значение ячейкипараметра. Если вас устраивает результат работы MS Excel - нажмите кнопку ОК. Если вы хотите вернуться к исходному значению – нажмите кнопку Отмена.

Рисунок 9.14. Результат подбора параметра

9.1.4. Использование Таблиц данных для изучения влияния одной или двух переменных на формулу

Общие сведения

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

Таблица данных с переменными – это таблица, которая показывает различные результаты, основываясь на различных исходных данных.

165

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

Таблица данных не может принимать более двух переменных. Если необходимо проанализировать больше переменных, вместо таблиц данных лучше использовать сценарии. Несмотря на то, что работа с Таблицей данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам, а другая — по строкам), при этом можно использовать любое количество различных значений переменных. Сценарий же поддерживает только 32 различных значения, но количество сценариев может быть любым.

В зависимости от числа переменных и формул, которые нужно протестировать, различают два вида Таблиц данных:

Таблицы данных с одной переменной. Такую таблицу рекомендуется использовать для просмотра влияния различных значений одной переменной в одной или нескольких формулах на результаты этих формул. Значения переменной вводятся в одном столбце или строке, а результаты отображаются в соседнем столбце или смежной строке. Например, Рисунок 9.15 показывает Таблицу данных, в которой ячейка D2 содержит формулу платежа, ссылающуюся на ячейку ввода B3.

=ПЛТ(B3/12;B4;-B5

Рисунок 9.15. Пример использования Таблицы данных с одной переменной

Таблицы данных с двумя переменными. Такую таблицу можно использовать для просмотра влияния различных значений двух переменных в одной формуле на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать для оценки влияния различных сочетаний процентной ставки и срока кредитования на размер ежемесячной выплаты по закладной. Например, Рисунок 9.16 содержит таблицу данных, в которой ячейка C2 содержит формулу определения размера платежа, использующую две ячейки ввода — B3 и B4.

=ПЛТ(B3/12;B4;-B5

Рисунок 9.16. Пример использования Таблицы данных с двумя переменными

166

Расчет таблицы данных. Таблицы данных пересчитываются при каждом пересчете листа, даже если они не были изменены.

Чтобы ускорить расчет листа, содержащего таблицу данных, можно изменить параметры в группе Вычисление, чтобы автоматически пересчитывался лист, но не таблицы данных

Создание таблицы с одной переменной

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

1.Создайте базовую таблицу, включив в нее необходимую для расчета формулу–прототип

(Рисунок 9.17).

При создании формулы убедитесь, что она использует ссылку на одну ячейку ввода, значение которой должно меняться (в нашем примере – это ячейка В4 с процентной ставкой).

Рисунок 9.17. Базовая таблица

2.Измените базовую таблицу:

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

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

3.Выполните одно из следующих действий:

Если таблица данных должна быть ориентирована по столбцам (значения переменной содержатся в столбце), разместите формулу-прототип в ячейку на одну строку выше и на одну ячейку правее столбца значений (Рисунок 9.18).

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

167

Ячейка

ввода

Формулапрототип

Список

значений

Рисунок 9.18. Пример данных подготовленных для создания Таблицы данных, ориентированной по столбцам

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

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

		Список
Ячейка
ввода		значений

Формулапрототип

Рисунок 9.19. Пример данных подготовленных для создания Таблицы данных, ориентированной по строкам

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

5.На вкладке Данные в группе Работа с данными нажмите кнопку Анализ «что-если» и

выберите команду Таблица данных.

6.В окне диалога Таблица данных (Рисунок 9.20) выполните одно из следующих действий:

Если таблица данных ориентирована по столбцам, введите ссылку на ячейку ввода в

поле Подставлять значения по строкам в (Рисунок 9.20).

168

Рисунок 9.20. Формирование таблицы данных ориентированной по столбцам

Если таблица данных ориентирована по строкам, введите ссылку на ячейку ввода в

поле Подставлять значения по столбцам в (Рисунок 9.21).

Рисунок 9.21. Формирование Таблицы данных ориентированной по строкам

7.Нажмите кнопку ОК. Excel сгенерирует результаты – в данном примере величину ежемесячных платежей в зависимости от разных процентных ставок

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

169