double arrow

Тема 10.3. Использование табличного процессора Excel для решения задач прогнозирования


Цель занятия – освоить приёмы использования Excel для решения задач прогнозирования: пересчитывать таблицу данных при изменении одного и двух параметров; осуществлять подбор параметра, обеспечивающего заданный конечный результат, а также находить решение задач линейного программирования.

Теоретические сведения

Действенным подспорьем при прогнозировании возможных изменений интересующих показателей является набора команд, которые называются средствами анализа «что если». Анализ «что если» предполагает изменение значений ячеек для выяснения того, как эти изменения повлияют на результаты выполнения формул на листе.

В Excel 2010 предусмотрены средства анализа «что если» трёх типов: сценарии, таблицы данных и подбор параметров. В сценарии и таблицы данных передаются наборы входных значений, после чего определяются возможные результаты. Средство подбора параметров работает иначе, чем сценарии и таблицы данных: ему передаётся результат, после чего определяются возможные входные значения, обеспечивающие получение этого результата.

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

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

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

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


Сейчас читают про: