4.7 Лабораторная работа Подбор параметра

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

Задача. Фирма занимается продажей оргтехники. С 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. ОК.