Таблицы подстановки microsoft excel
Влияние одного или двух параметров на функцию можно исследовать с помощью информационной технологии таблицы подстановок: влияние одного параметра на различные функции или влияние двух параметров на одну функцию. Эта информационная технология соответствует задачам анализа типа «ЕСЛИ ТО», используемых для построения дерева решений и анализа чувствительности.
1 вариант – влияние одного параметра на различные функции.
В качестве изменяемого параметра для функций БЗ, ПЗ, НОРМА выбрана сумма периодических выплат (pmt).
Вставить новый лист – команда меню ПравкаЛист (если необходимо).
Переименовать лист в Подстановки – команда меню ФорматЛистПереименовать.
Подготовить исходные данные (рис. 11):
Ввести в ячейку А2 начальное значение параметра – 55.
Закрасить блок ячеек столбца А, начиная с А2, произвольной высоты (например, 9 строк).
Выполнить команду меню ПравкаЗаполнитьПрогрессия, установки: арифметическая прогрессия, шаг –5.
В ячейки В1, С1 ввести формулы для вычисления функций БЗ, ПЗ:
не верно!! =БЗ(B1/12;B2*12;A1;B4;0) и =ПЗ(B1/12;B2*12;A1;6436,74;0)
верно: =БЗ(18%/12;3*12;A1;-1000;0) и =ПЗ(18%/12;3*12;A1;6436,74;0) соответственно.
Все Формулы ссылаются на пустую ячейку А1, которая является ячейкой подстановки для значений параметра Выплата.
| А | В | С |
|
| =БЗ(…) | =ПЗ(…) |
| -55 |
|
|
| -50 |
|
|
| -45 |
|
|
| -40 |
|
|
| -35 |
|
|
| -30 |
|
|
| -25 |
|
|
| -20 |
|
|
Рисунок 11
Выделить блок ячеек А1:C9.
Выполнить команду меню ДанныеТаблица подстановки, указать в поле Подставлять значения по строкам в ячейку А1.
Автоматически вычисляются значения различных функций для каждого значения параметра. Microsoft Excel автоматически создает массив формул в указанных ячейках. Массив формул не редактируется на уровне отдельных ячеек, содержит общую формулу вида: {=ТАБЛИЦА(;A1)}. Результаты вычислений приведены в табл. 9.
Можно изменть значения иследуемого параметра модели непосредственно в ячейках или редактировать исходные формулы для исследуемых функций (ячейки В1, С1, …), при этом все значения функций автоматически перевычисляются.
Таблица 9
| 1 709,14р. | - 3 766,07р. |
-55 | 4 309,32р. | - 2 244,73р. |
-50 | 4 072,94р. | - 2 383,04р. |
-45 | 3 836,56р. | - 2 521,34р. |
-40 | 3 600,18р. | - 2 659,64р. |
-35 | 3 363,80р. | - 2 797,95р. |
-30 | 3 127,42р. | - 2 936,25р. |
-25 | 2 891,04р. | - 3 074,55р. |
2 вариант – влияние двух параметров на функцию.
В качестве изменяемого параметра для функции БЗ выбираем сумму периодических выплат (pmt) и размер годовой процентной ставки (r).
Схема подготовки данных (рис. 12):
Ввести в ячейку А12 начальное значение первого параметра (pmt) – суммы ежемесячных выплат, например, -55. Закрасить блок ячеек столбца, начиная с А12. Для заполнения столбца значений параметра используется команда главного меню ПравкаЗаполнитьПрогрессия, с установками: арифметическая прогрессия, шаг –50.
Ввести в ячейку В11 начальное значение второго параметра (r) – годовой процентной ставки, например, 0%. Закрасить блок ячеек строки, начиная с B11. Для заполнения строки значений параметра используется команда главного меню ПравкаЗаполнитьПрогрессия, с установками: арифметическая прогрессия, шаг 0,1.
| А | В | C | D | E | F | G |
|
|
|
|
|
|
|
|
| =БЗ(С10/12;..;В10;..;..) | 0% | 10% | 20% | 30% | 40% | 50% |
| -55 |
|
|
|
|
|
|
| -50 |
|
|
|
|
|
|
| -45 |
|
|
|
|
|
|
| -40 |
|
|
|
|
|
|
| -35 |
|
|
|
|
|
|
| -30 |
|
|
|
|
|
|
| -25 |
|
|
|
|
|
|
Рисунок 12
В ячейке А11 задается формула для вычисления функции БЗ, в которой имеются ссылки на ячейки: В10 для параметра pmt, С10 – для параметра r, например, =БЗ(C10/12;3*12;B10;-1000;0).
Закрасить блок ячеек А11:G19 и выполнить команду главного меню ДанныеТаблица подстановки, указать:
в поле Подставлять значения по столбцам в – ячейка С10.
в поле Подставлять значения по строкам в – ячейка В10.
Автоматически вычисляются значение функции для значений параметров, создается массив формул вида: {=ТАБЛИЦА(С10;В1)}, результаты вычислений приведены в табл. 10.
Можно изменть значения параметров или формулы для (ячейка А10), при этом значения функции автоматически перевычисляются. В примере (табл. 10) использована формула: =БЗ(C10/12;3*12;B10;-1000;0)
Таблица 10
1 000р. | 0% | 10% | 20% | 30% | 40% | 50% |
-55 | 2 980,00р. | 3 646,18р. | 4 496,46р. | 5 584,11р. | 6 977,83р. | 8 766,11р. |
-50 | 2 800,00р. | 3 437,27р. | 4 252,52р. | 5 297,61р. | 6 639,46р. | 8 364,41р. |
-45 | 2 620,00р. | 3 228,36р. | 4 008,58р. | 5 011,10р. | 6 301,10р. | 7 962,72р. |
-40 | 2 440,00р. | 3 019,45р. | 3 764,64р. | 4 724,59р. | 5 962,73р. | 7 561,02р. |
-35 | 2 260,00р. | 2 810,55р. | 3 520,70р. | 4 438,08р. | 5 624,36р. | 7 159,33р. |
-30 | 2 080,00р. | 2 601,64р. | 3 276,77р. | 4 151,58р. | 5 285,99р. | 6 757,63р. |
-25 | 1 900,00р. | 2 392,73р. | 3 032,83р. | 3 865,07р. | 4 947,62р. | 6 355,94р. |
- Информационные технологии анализа данных в microsoft office
- Введение
- Аналитическая обработка данных в microsoft office
- Интеграция данных в microsoft office
- Математические методы анализа данных
- Информационные технологии встроенных функций microsoft excel
- Функция бз
- Функция пз
- Функция норма
- Функция пплат
- Функция кпер
- Функции оснплат, плпроц
- Подбор параметра модели в microsoft excel
- Сценарный подход в microsoft excel
- Таблицы подстановки microsoft excel
- Оптимизационные задачи в microsoft excel
- Статистическая обработка данных в microsoft excel
- Описательная статистика
- Прогнозирование значений
- Агрегирование данных в microsoft office
- Сводная таблица Microsoft Excel
- Структурная таблица Microsoft Excel
- Консолидация данных в Microsoft Excel
- Графические методы анализа данных microsoft office
- Типы диаграмм и их свойства
- Построение диаграммы
- Линии тренда
- Примеры информационных технологий анализа данных
- Задача «Факторный анализ»
- Задача «Количественный анализ хозяйственной деятельности»
- Задача «Анализ рентабельности»
- Задача «Анализ производственной программы»
- Задача «Оптимальное размещение денежных средств на депозите»
- Задача «Анализ продаж ценных бумаг»
- Приложение. Некоторые встроенные функции microsoft excel
- Литература
- Оглавление
- 191023, Санкт–Петербург, Садовая ул., д. 21