Модуль "Математические и статистические методы анализа данных в MS Excel", страница 2

Модуль "Математические и статистические методы анализа данных в MS Excel"

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

Один изменяемый параметр

Два изменяемых параметра

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

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

  нельзя удалить или отредактировать отдельный элемент массива формул,

  возможна корректировка или удаление всего массива формул целиком.

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

Массив формул сокращает объем расчетной модели.

Таблица подстановки с одним изменяемым параметром

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

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

1 вариант.

  В первую ячейку диапазона вводится начальное значение параметра.

  Выполняется команда Правка, Заполнить, Прогрессия. В диалоговом окне команды «Прогрессия» указывается расположение ячеек — по строкам или столбцам, тип и шаг прогрессии, предельное значение элемента прогрессии. Автоматически заполняется необходимое число ячеек.

2 вариант.

  В первую ячейку диапазона вводится начальное значение параметра.

  Выделяется диапазон ячеек для заполнения.

  Выполняется команда Правка, Заполнить, Прогрессия. В диалоговом окне команды «Прогрессия» указывается тип и шаг прогрессии. Можно указать предельное значение элемента прогрессии. Если предельное значение достигается до заполнения выделенного диапазона ячеек, остальные ячейки остаются пустыми.

Проанализировать влияние объема выпуска продукции (см. Пример 23) на величину затрат, расчетную прибыль и прибыль на единицу продукции. Изобразить графически влияние объема выпуска на указанные показатели.

1.  Открыть рабочую книгу MATSTAT.xls.

2.  На листе РАСЧЕТЫ установить курсор в ячейку, содержащую объем выпуска — В6.

3.  Выполнить команду Сервис, Зависимости формул, Зависимые ячейки.

Проанализировать с помощью стрелок прямую зависимость ячеек с расчетными формулами от ячейки В6.

Обратите внимание на то, что для значений «Расчетная прибыль» и «Точка безубыточности (с учетом вероятности реализации)» прямой зависимости от значения параметра в ячейке В6 не существует.

Для обеспечения прямой взаимосвязи с ячейкой В6 следует в расчетных формулах этих показателей использовать ссылку на ячейку B6.

4.  Подготовить данные согласно схеме (рис.1), ввести формулы в ячейки B21, C21, D21.

	A	B	C	D
20		Итого затраты	Расчетная прибыль	Прибыль на единицу продукции
21	Объем выпуска	=B5+(B3+B4)*B6	=B1*B6*B7/100-(B5+(B3+B4)*B6)	=(B1*B6*B7/100-(B5+(B3+B4)*B6))/B6
22	1000
23	1020
24	1040
25	1060
26	1080
27	1100
28	1120
29	1140
30	1160
31	1180
32	1200

Рис. 1

5. Выделить диапазон ячеек A21:D32, команда Данные, Таблица подстановки, указать: подставлять значения по строкам в ячейку В6 (ячейка подстановки).

Формула массива в данном примере имеет вид: {=ТАБЛИЦА(;B6)}.  
Можно изменить значение объема выпуска (ячейки А22:А32) или формулы (ячейки B21,C21,D21). 

6. Построить диаграмму (тип График) зависимости прибыли на единицу продукции от объема выпуска.

7. Добавить линию тренда, определить уравнение тренда.

8. Сохранить рабочую книгу MATSTAT.xls.

Таблица подстановки с двумя изменяемыми параметрами

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

Проанализировать влияние параметров модели: объем выпуска продукции и вероятность реализации (см. Пример 23) на величину расчетной прибыли для единицы продукции.

1.  Открыть рабочую книгу MATSTAT.xls.

2.  Перейти на лист РАСЧЕТЫ.

3.  Подготовить данные согласно схеме (рис. 2). В столбце — значения объема выпуска, в строке — вероятность реализации, %.

4.  Ввести формулу в ячейку А39: =(B1*B6*B7/100-(B5+(B3+B4)*B6))/B6

5.  Выделить диапазон ячеек A39:G50, команда Данные, Таблица подстановки. Подставлять значения по строкам — ячейка В6, по столбцам — ячейка В7.