<<
>>

3.10. Анализ «что-если» 3.10.1. Таблицы данных

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

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

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

Таблицы данных для одной переменной

Предположим, что вы оцениваете выгодность долговременных вложений (инвестиции).

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

Чтобы создать такую таблиц»', выполните следующие действия; 1.

Введите интересующие Вас годовые процентные ставки (для этого примера 4,5, 5, 5,5, б, 6,5, 7, 7,5 к 8 процентов) в ячейки В2:В9. Этот диапазон является входным диапазоном, так как содержит входные значения, для которых мы хотим сделать анализ. 2.

Введите формулу, которая будет использовать входную переменную. Для данной задачи введите в ячейку С1 функцию:

=ПЗ(А1:5;;500000000)

где

А1 - годовая процентная ставка;

5 - общее число периодов выплат;

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

=ПЗ(Й1;5;.500000000) srft WUf п ч Г . %

[-500 ООО 000.00 р.

%! %

% I

ІОУо |

Ч.' %j

%!"

Рис. 3 .20. Входной диапазон и формула таблицы данных для анализа выгодности инвестиции 1.

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

В меню Данные выберите команду Таблица подстановки и в диалоговом окне Таблица подстановки задайте в поле Подставлять значения по строкам в (если входной диапазон расположен в столбце) или Подставлять значения по столбцам в (если входной диапазон расположен в строке).

Рис. 3.21. Диалоговое окно Таблица подстановки

Входная ячейка - это ячейка, на которую ссылается формула. В данном случае в поле Подставлять значения но строкам в введите $А$1.

1. Нажмите кнопку ОК. Excel выведет результат функции для каждого значения из входного диапазона. В данном примере результаты будут выведены в диапазон ячеек С2:С9 (диапазон результатов) как показано на рис. 3.22.

3.10.2. Таблица с несколькими формулами

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

Пусть Вы оцениваете выгодность инвестиции в 400 млн. р. при получении дохода в 500 млн. р. при различных процентных ставках. При этом вы хотите сравнить результаты при единовременной выплате через пять лет всей суммы и периодических выплатах ежегодно 100 млн. р. в течение пяти лет.

Можно в таблицу данных на рис. 3.21 включить еще одну формулу: 1.

В ячейку D1 введите функцию:

=ПЗ(А1;5; 100000000) 2.

Выделите диапазон таблицы, для нашего примера В1 :D9. 3.

Выберите команду Таблица подстановки из меню Данные и введите ссылку на входную ячейку $А$1 в поле Подставлять значения но строкам в. 4.

Нажмите кнопку ОК.

Рис. 3.22. Таблица данных для анализа выгодности инвестиции при единовременной выплате и при периодических выплатах

Таблицы данных для двух переменных

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

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

р. при годовой процентной ставке 30%, при ежемесячных выплатах, выплатах один раз в два месяца и ежеквартальных выплатах по 1 млн. р.; 2 млн. р. и 5 млн. р.

Для создания такой таблицы необходимо выполнить следующие действия: 1.

Введите первое множество входных значений - множество выплат в один столбец, например ВЗ:В5. 2.

Введите второе множество входных значений - множество периодических и выплат в одну строку выше и правее на одну ячейку от начала первого входного диапазона, т.е. в ячейки С2:Е2. 3.

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

=КПЕР(30%/А2;-В 1 ;20000000)

В2

=КПЕР(30%/А2;-В1;20000000)

— #Д ЕЛ/01 12 4І 1000000 _2000000 5000000 Рис. 3.23. Формула возвращает ошибочное значение, так как в вычислениях изначально используются пустые ячейки и полученный результат невозможно представить 1.

Выведите прямоугольный диапазон ячеек, вмещающий в себя входные значения и формулу. В нашем примере В2:Е4. 2.

Выберите в меню Данные команду Таблица подстановки и задайте входные ячейки следующим образом - в поле Подставлять значения но строкам в введите ссылку на ячейку $В$1, в поле Подставлять значения но столбцам в введите ссылку на ячейку $А$2. 3.

Нажмите кнопку ОК.

Результат представлен на рис. 3.24. 4,931854

19,16872

#ЧИСЛОІ

=КПЕР(30%/А2;-В1.20000000)

11

Редактирование таблиц данных

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

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

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

<< | >>
Источник: А.Н. Романов и А.И. Змитрович. Информационные технологии в экономике: Учебное пособие для вузов. В 2 кн. Кн. 1. / Под ред.. - Мн.: ЗАО "Веды". 240 е.: ил.. 1998

Еще по теме 3.10. Анализ «что-если» 3.10.1. Таблицы данных:

  1. 13. ОПИСАНИЕ ДАННЫХ: ПОСТРОЕНИЕ ТАБЛИЦ, ДИАГРАММ, ГИСТОГРАММ
  2. Глава 9 Что делать, если нужно быть экстравертом?
  3. СБОР И АНАЛИЗ ДАННЫХ
  4. АНАЛИЗ ДАННЫХ
  5. 2.5.5. Анализ и интерпретация полученных данных ?
  6. ВТОРИЧНЫЙ АНАЛИЗ ДАННЫХ ОПРОСА
  7. 3.1 АНАЛИЗ АЛГОРИТМОВ И СТРУКТУР ДАННЫХ
  8. РАЗДЕЛ 0. У БАРБОСА ЕСТЬ ВОПРОСЫ. Что можно предпринять, если цена не в состоянии скоординировать действия на рынке?
  9. 2.7. Качественный анализ экспериментальных данных
  10. АНАЛИЗ ТАБЛИЦ
  11. ГЛАВА II О ТОМ, ЧТО ЕСЛИ ЖИВОТНЫЕ ОЩУЩАЮТ, ТО ОНИ ОЩУЩАЮТ ТАК ЖЕ, КАК МЫ
  12. ГЛАВА IV О ТОМ, ЧТО ЕСЛИ ИСХОДИТЬ ИЗ ПРЕДПОЛОЖЕНИЯ, СОГЛАСНО КОТОРОМУ ЖИВОТНЫЕ ОКАЗЫВАЮТСЯ ОДНОВРЕМЕННО И ЧИСТО МАТЕРИАЛЬНЫМИ, И ОБЛАДАЮЩИМИ ОЩУЩЕНИЯМИ СУЩЕСТВАМИ, ТО ОНИ НЕ МОГЛИ БЫ ЗАБОТИТЬСЯ О САМОСОХРАНЕНИИ, НЕ ОБЛАДАЙ ОНИ СВЕРХ ТОГО СПОСОБНОСТЬЮ К ПОЗНАНИЮ
  13. ГЛАВА III О ТОМ, ЧТО АНАЛИЗ ДЕЛАЕТ УМЫ ПРАВИЛЬНЫМИ