Файл: Анализ данных в экономических информационных системах.doc

СРС№1

по дисциплине «Анализ данных в экономических информационных системах»

Выполнил Выскирко Андрей

Группа ИС 432

Специальность 5В070300 Информационные системы

Номер зачетной книжки (студенческого билета)___________________

Проверил(а) ____________________________________________________

Анализ данных на основе использования таблицы подстановки
Содержание
1. Средства Excel для проведения анализа «Что-если»

2. Анализ данных на основе использования таблицы подстановки

3. Методы использования таблицы подстановки данных

1. Средства Excel для проведения анализа «Что-если»

К средствам Excel, предназначенным для анализа данных «Что-если», относятся следующие.

Средство Подбор параметра. Если вы знаете, каков должен быть результат вычисления формулы, но не знаете, при каком исходном значении параметра, от которого зависит формула, достигается этот желаемый результат, тогда следует использовать средство Подбор параметра. Excel переберет различные возможные значения этого параметра и найдет то значение, при котором в процессе вычисления указанной формулы будет получен нужный результат. Например, в нашем примере езды на велосипеде со скоростью 20 км в час для определения расстояния, которое можно проехать за 45 минут, вы указываете 45 минут как желаемый результат вычисления формулы (в этой формуле количество километров умножается на скорость). При этом указываете, что входным параметром для формулы является ячейка, содержащая количество километров. Excel, изменяя значение количества километров, найдет такое значение, при котором в ячейке с формулой результат вычисления будет равен 45 минутам.
Таблицы подстановки. Это диапазон ячеек, где отображаются значения изменяемых параметров и результаты вычисления определенных формул, зависящие от этих параметров. Таблицы подстановки дают возможность просмотреть в виде таблицы результаты вычисления одной или нескольких формул в зависимости от заданных значений определенных параметров, на основе которых вычисляются эти формулы. В примере с велосипедом можно создать таблицу, отображающую количество километров, которые можно преодолеть в зависимости от разных значений скорости езды и времени путешествия.
Сценарии. Excel позволяет сохранять наборы значений и затем автоматически подставлять их в ячейки рабочего листа для просмотра выходных результатов определенных вычислений. Можно создать и сохранить различные сценарии, чтобы затем переключаться между этими сценариями для сравнения вычисленных результатов. Для «велосипедного» примера можно создать несколько сценариев, вычисляющих расстояния для различных значений скорости и времени.
Средство Поиск решения. С помощью этого средства можно найти оптимальное значение для формулы, записанной в так называемой целевой ячейке рабочего листа. Поиск решения работает с группой ячеек, влияющих на формулу в целевой ячейке. Это средство варьирует значения в изменяемых ячейках для того, чтобы получить желаемый результат, который вы указали для целевой ячейки. На значения изменяемых ячеек можно налагать различные ограничения, в частности, можно указать интервалы их возможных изменений. В примере с велосипедом для определения времени путешествия можно указать верхнюю и нижнюю границы для расстояния при условии постоянства скорости.

Суммируя сказанное, определим назначение каждого описанного средства.

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

2. Анализ данных на основе использования таблицы подстановки
Microsoft Excel включает в свой состав несколько интересных средств для анализа данных. Данная статья описывает возможности одного из таких интерфейсных решений для проведения вычислений при помощи "таблицы подстановки" (в последних версиях Excel называется "таблица данных").

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

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

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

Затем следует выделить область таблицы, включая ячейку с формулой (в примере B10:C14), и вызвать диалог формирования таблицы подстановки. В Excel2007-2013 - через Данные \ Работа с данными \ Анализ «что-если» \ Таблица данных, в Excel 97-2003 через меню Data \ Table. В диалоге необходимо указать ячейку, в которую следует подставлять указанные в таблице параметры. В примере варианты ставки дисконтирования располагаются по строкам, поэтому заполняем поле диалога "Подставлять значения по СТРОКАМ в:". Указываем ссылку на ячейку с рабочей ставкой дисконтирования, которая применяется в основных расчетах - $B$4.

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

Похожие действия необходимо произвести в случае двухмерной таблицы подстановки (матрицы). В диалоговом окне, кроме ссылки на параметр в строках требуется заполнить поле "Подставлять значения по СТОЛБЦАМ в:". Там указываем ссылку на рабочую ячейку с начальными инвестициями - $B$3. В отличие от вектора при использовании матрицы ссылка на результат должна располагаться в верхнем левом углу таблицы.

Как же это работает? В момент проведения расчетов Excel последовательно подставляет значения из строк и/или столбцов заголовка матрицы к заданным в диалоге ячейкам. После завершения каждого цикла вычислений результат записывается в соответсвующую ячейку таблицы подстановки. Затем происходит переход к следующему параметру, и вычисления повторяются. И так до тех пор, пока не будут перебраны все варианты параметров.

Очевидно, что при работе с большими таблицами подстановки вычисления, производимые в цикле, будут существенно замедлять работу с файлами. Чтобы этого не происходило, в Excel имеется специальный режим расчетов "Автоматически, кроме таблиц". С данной установкой при любом изменении формул, таблицы подстановки обновляться не будет до тех пор, пока пересчет не запущен принудительно (например, по нажатию F9).

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

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

3. Методы использования таблицы подстановки данных

Добавление формулы в таблицу подстановки с одной переменной

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

Выполните одно из следующих действий:

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

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

В меню Данные выберите команду Таблица.

Выполните одно из следующих действий:

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

Удаление таблицы подстановки данных

Удаление всей таблицы

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

Удаление рассчитанных значений из таблицы подстановки данных

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

Выделите в таблице данных все рассчитанные значения.
Выберите пункт Очистить в меню Правка, а затем – команду Содержимое.

Преобразование рассчитанных значений таблицы подстановки данных в константы

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

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

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

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

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

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