Сценарии

Лабораторная работа №10

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

Эта возможность позволяет определить, как должна измениться величина, чтобы результатом формулы, где она используется, было бы требуемое значение, то есть, ПОДОБРАТЬ ПАРАМЕТР.

Задача. Фирма занимается продажей оргтехники. С 1999 по 2000 гг. объем продаж вырос 128526р. до 131135р., т.е. на 2,03%. В 2001 году планируется достичь объема продаж в 140000р. Определить, каков должен быть процент прироста объема продаж для получения такого результата.

Рассмотрим решение этой задачи:

1. На рабочем листе Excel введите данные в следующем виде:

Исходные данные	Рост объема продаж	2,03%
Продажи	Торговый агент	1999год	2000год	2001год
	Иванов	22 592р.	23 051р.
	Петров	21 383р.	21 817р.
	Сидоров	26 698р.	27 240р.
	Павлов	29 454р.	30 052р.
	Ильин	28 399р.	28 976р.
	Общий объем продаж	128 526р.	131 136р.

2. Столбец «2001год» заполните по формуле: = объем продаж предыдущего года * рост объема продаж + объем продаж предыдущего года.

3. Установить курсор на ячейке, где содержится формула, значение которой нужно установить. Нам нужно установить объем продаж, равный 140000р., значит, курсор устанавливаем на ячейке общего объема продаж за 2001год.

4. В меню Сервис выбрать команду Подбор параметра. Откроется диалоговое окно.

5. В первой полосе окна «Установить в ячейке» уже содержится адрес нужной ячейки с формулой, так как на ней был уже установлен курсор (шаг 3).

6. В следующей полосе ввода «Значение» ввести нужное значение формулы. В нашем случае – это 140000.

7. В полосе ввода «Изменяя значение ячейки» ввести адрес ячейки, в которой содержится изменяемый параметр. Для рассматриваемой задачи – это ячейка, содержащая значение роста объема продаж, т.е. 2,03%.

8. ОК. Появится окно, где сообщаются результаты подбора параметра. Если они нас устраивают – ОК.

Таблица подстановки

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

Задача. Изменим предыдущую задачу: условия те же, задание – построить таблицу подстановки роста дохода при изменении роста объемов продаж от 3% до 10% с шагом в 1%.

Рассмотрим решение этой задачи.

1. На этом же листе, не удаляя исходные данные, составьте таблицу:

Рост объема продаж	В этой ячейке поместить формулу, вычисляющую общий объем продаж за 2001год.
3%
4%
5%
6%
7%
8%
9%
10%

2. Выделить эту таблицу вместе с пустыми ячейками.

3. В меню Данные выбрать команду Таблица подстановок. Откроется диалоговое окно.

4. В окне две полосы ввода: «Подставлять значения по столбцам в» и «Подставлять значения по строкам в». Для рассматриваемой задачи мы внесли значения по строкам, значит, в полосе «Подставлять значения по строкам в» нужно внести адрес ячейки с первоначальным значением параметра, то есть, роста объема продаж (адрес ячейки, где сначала стояло значение 2,03%).

5. ОК.

Сценарии

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

Задача. Изменим предыдущую задачу: условия те же, задание – составить сценарии для сравнения объемов продаж каждого торгового агента при изменении процента прироста объема продаж: 1-ый сценарий для низкого (3%) прироста, 2-ой сценарий для среднего (5%) прироста, 3-ий сценарий для высокого (10%) прироста.

Рассмотрим решение этой задачи. (Удалите таблицу подстановки, составленную для предыдущей задачи.)

1. В меню Сервис выбрать команду Сценарии. Открылось диалоговое окно Диспетчер сценариев, где сообщается, что сценарии не определены.

2. Щелкнуть по кнопке Добавить. Открылось окно Изменение сценария.

3. В поле ввода «Название сценария» наберите название первого сценария, например, «Минимальный».

4. В поле «Изменяемые ячейки» ввести адрес ячейки, содержащей изменяемый параметр. Для нашей задачи – адрес ячейки, в которой указан процент роста объема продаж. ОК.

5. Открылось окно Значение ячеек сценария. Ввести нужное значение, то есть, 3% или 0,03. ОК.

6. Аналогично создать второй и третий сценарии, назвать их можно «Средний» и «Максимальный».

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

Чтобы сравнить сценарии, можно в том же окне Диспетчер сценариев использовать кнопки Объединить или Отчет.

Дата добавления: 2015-08-05