Сценарии

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

Таблица данных с одним параметром для нескольких функций

Таблица данных с одним параметром для одной функции

Анализ данных с помощью таблиц данных

Для анализа зависимости результата от различных наборов исходных данных в Excel используются таблицы данных.

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

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

ПРИМЕЧАНИЕ: Удалять отдельные ячейки из таблицы нельзя, можно очистить всю таблицу. Для этого следует выделить вычисленные значения и выполнить команду: Правка - Очистить - Содержимое или нажать клавишу Delete.

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

§ задаются исходные данные задачи, в том числе одно из значений аргумента-параметра;

§ задается диапазон значений параметра (в виде столбца или строки);

§ выделяется место для размещения значений функции от заданных значений аргумента (столбец справа или строка ниже);

§ задается формула, определяющая зависимость функции от аргумента-параметра. Формула размещается на одну ячейку левее и ниже диапазона исходных данных при подстановке по столбцам и на одну ячейку правее и выше диапазона исходных данных, если подстановка производится по строкам

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

§ ввести адрес ячейки-параметра в одно из полей Подставлять значения по столбцам/строкам.

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

§ для каждой ячейки столбца в соседнюю ячейку строки, т.е. по строкам;

§ для каждой ячейки строки в соседнюю ячейку столбца, т.е. по столбцам.

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

Используя команду Таблица данных, можно построить таблицу с двумя параметрами. При этом значения одного из них должны располагаться в столбце, а значения другого – в строке. Формула должна находиться в левом верхнем углу блока ячеек (над столбцом значений параметра). Результат подстановки после выполнения команды будет помещен на пересечении столбца и строки.

В MS Excel существует возможность проанализировать результаты некоторых расчетов в зависимости от ряда исходных параметров, т.е. ответить на вопрос, что будет, если исходные параметры примут другие значения. Если проделывать этот анализ путем пересчета таблиц с данными при интересующих значениях параметров, то это приведет:

§ к потере исходных расчетов;

§ к громоздким вычислениям, произведенным на разных листах MS Excel;

§ к трудности сопоставления полученных результатах и т.д.

Имеющийся в MS Excel математический аппарат, использующий сценарии, позволяет избежать всех этих трудностей.

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

Для создания сценариев используется диалоговое окно Диспетчер сценариев, для его открытия на вкладке Данные в группе Средства обработки данных выберите команду Анализ "что если", а затем выберите в списке пункт Диспетчер сценариев.

В появившемся диалоговом окне Диспетчер сценариев предусмотрены возможности:

§ Добавить новый сценарий;

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

§ Изменить сценарий;

§ Вывести отчет по созданным сценариям;

§ Объединить несколько сценариев.

§ Удалить сценарий из списка;

§ Закрыть окно Диспетчер сценариев;

При создании нового сценария следует его Добавить:

§ при выборе команды Добавить появляется диалоговое окно Добавление сценариев, в котором запрашивается его название, Изменяемые ячейки с исходными параметрами, влияние которых на результаты исследуется в данном сценарии, по желанию добавляется примечание к сценарию и устанавливается флажок при необходимости Скрыть или Запретить изменения во вновь создаваемом сценарии. Одновременно может быть создано несколько сценариев;

§ после нажатия кнопки ОК предлагается ввести значения исходных параметров, заданных в окне Изменяемые ячейки. При нажатии кнопки Добавить осуществляется переход в окно Добавление сценария, а при нажатии кнопки ОК – в окно Диспетчер сценариев;

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

ПРИМЕЧАНИЕ: Для удобства анализа отчетов ячейкам с исходными параметрами и результатами вычислений следует присвоить имена в команде Вставка – Имя – Создать.

Дата добавления: 2014-01-11