Диаграммы в Microsoft Excel. Финансы в Excel

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

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

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

Чтобы создать таблицу подстановки, выделите диапазон, включающий:

  • списки значений;
  • ячейки, содержащие формулы подстановки;
  • диапазон, в который будет помещен результат.
После этого воспользуйтесь командой Table (Таблица подстановки) меню Data (Данные) . В появившемся диалоговом окне следует указать ячейку ввода в поле , если список значений находится в строке или в поле в противном случае. Для таблицы подстановки с двумя переменными следует указать обе ячейки ввода.

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

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

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

Для таблицы подстановки с одной переменной требутся список значений, расположенный в отдельной строке или отдельном столбце. Формул подстановки может быть несколько, но ссылаться они должны на одну и ту же ячейку ввода. Построим источник данных для графика функции y = x 2 , -5 <= x <= 5:

  • В ячейку E1 введем формулу =D1*D1 , которая ссылается на ячейку ввода D1 .
  • В столбец левее и ниже формулы введем значения подстановки для переменной X .
  • Выделим диапазон ячеек, содержащий формулу и набор данных подстановки (D1:E12 ).
  • Активизируем диалоговое окно Table (Таблица подстановки) меню Data (Данные) .
  • В поле Column input cell: (Подставлять значения по строкам в) D1 ).

В результате подстановки будет заполнен массив значений. Выделив диапазон, включающий список значений и массив значений и выбрав тип диаграммы XY Scatter (Точечный) легко можно построить требуемый график.

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

Чтобы построить таблицу подстановки с двумя переменными, следует ввести два списка значений: в отдельный столбец и отдельную строку. Необходимы также две ячейки ввода, располагающихся вне таблицы данных. Рассчитаем массив значений для построения поверхности z = x 2 - y 2 , -5 <= x <= 5, -5 <= y <= 5 :


  • В ячейку A1 введем формулу =A13*A13 - A14*A14 , которая ссылается на две ячейки ввода A13 и A14 .
  • В тот же столбец ниже формулы введем значения подстановки для первой переменной (значения аргумента X ). Значения подстановки для второй переменной (Y ) введем в строку правее формулы.
  • Выделим диапазон ячеек, содержащий формулу и оба набора данных подстановки (A1:L12 ). Ячейки ввода в диапазон входить не должны.
  • Активизируем диалоговое окно Table (Таблица подстановки) меню Data (Данные) .
  • В поле Row input cell: (Подставлять значения по столбцам в) введем ссылку на ячейку ввода для значений подстановки в строке (A14 ).
  • В поле Column input cell: (Подставлять значения по строкам в) введем ссылку на ячейку ввода для значений подстановки в столбце (A13 ).
В результате подстановки будет заполнен массив значений, выделив который, легко можно построить требуемую поверхность.

Рассмотрим подбор параметра с одной переменной на примере таблицы выплат различных процентов с определенной суммы.

Дана таблица, содержащая в ячейке А3 значение дохода (100 000 руб.) и в ячейке В3 процент выплаты 13 %.
Необходимо вычислить сумму выплат при различных процентах (10 %, 15 % и 18 %).

  1. В окне открытого листа создайте диапазон значений, которые будут подставляться в ячейку ввода таблицы, в отдельный столбец или строку.
    В нашем примере это диапазон С3:С5 , в который введены значения 10 %, 15 % и 18 %.
  2. Выделите ячейку для создания формулы, расположенную на одну строку выше и на одну ячейку правее первого значения созданного диапазона (так как значения в таблице подстановки располагаются столбцом).
    В нашем примере это ячейка D2 .
  3. Выделите ячейку для создания формулы, расположенную на один столбец левее и на одну строку ниже первого значения, если значения в таблице подстановки располагаются строкой.

  4. Введите в ячейку D2 формулу для вычисления суммы выплаты:
    =А3*В3 (Доход*Процент_выплаты). В ячейке D2 отобразится число 13 000 (рис. 5.108).

  5. Выделите диапазон ячеек, содержащих формулы и значения подстановки. В нашем примере это С2:D5 .
  6. Перейдите к вкладке «Данные» и в группе «Работа с данными» раскройте меню кнопки «Анализ что-если».
  7. В списке команд выберите пункт «Таблица данных» (рис. 5.109).
  8. Так как значения в таблице расположены по столбцам, то в окне «Таблица данных» в графе «Подставлять значения по строкам в» введите адрес ячейку ввода в таблице. В нашем примере это ячейка В3 (рис. 5.110).

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

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

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

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

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

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

Способ 1: применение инструмента с одной переменной

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

Итак, в настоящее время нам предлагаются следующие условия кредитования:

  • Срок кредитования – 3 года (36 месяцев);
  • Сумма займа – 900000 рублей;
  • Процентная ставка – 12,5% годовых.

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


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

ПЛТ(ставка;кпер;пс;бс;тип)

«Ставка» — аргумент, определяющий процентную ставку кредитных выплат. Показатель выставляется за период. У нас период выплат равен месяцу. Поэтому годовую ставку в 12,5% следует разбить на число месяцев в году, то есть, 12.

«Кпер» — аргумент, определяющий численность периодов за весь срок предоставления кредита. В нашем примере период равен одному месяцу, а срок кредитования составляет 3 года или 36 месяцев. Таким образом, количество периодов будет рано 36.

«ПС» — аргумент, определяющий приведенную стоимость кредита, то есть, это размер тела кредита на момент его выдачи. В нашем случае этот показатель равен 900000 рублей.

«БС» — аргумент, указывающий на величину тела кредита на момент его полной выплаты. Естественно, что данный показатель будет равен нулю. Этот аргумент не является обязательным параметром. Если его пропустить, то подразумевается, что он равен числу «0».

«Тип» — также необязательный аргумент. Он сообщает о том, когда именно будет проводиться платеж: в начале периода (параметр – «1» ) или в конце периода (параметр – «0» ). Как мы помним, у нас платеж проводится в конце календарного месяца, то есть, величина этого аргумента будет равна «0» . Но, учитывая то, что этот показатель не является обязательным, и по умолчанию, если его не использовать, значение и так подразумевается равным «0» , то в указанном примере его вообще можно не применять.

  1. Итак, приступаем к расчету. Выделяем ячейку на листе, куда будет выводиться расчетное значение. Клацаем по кнопке «Вставить функцию» .

  2. Запускается Мастер функций . Производим переход в категорию «Финансовые» , выбираем из перечня наименование «ПЛТ» и клацаем по кнопке «OK» .

  3. Вслед за этим происходит активация окошка аргументов вышеуказанной функции.

    Ставим курсор в поле «Ставка» , после чего кликаем по ячейке на листе со значением годовой процентной ставки. Как видим, в поле тут же отображаются её координаты. Но, как мы помним, нам нужна месячная ставка, а поэтому производим деление полученного результата на 12 (/12 ).

    В поле «Кпер» таким же образом вносим координаты ячеек срока кредита. В этом случае делить ничего не надо.

    В поле «Пс» нужно указать координаты ячейки, содержащей величину тела кредита. Выполняем это. Также ставим перед отобразившемся координатами знак «-» . Дело в том, что функция ПЛТ по умолчанию выдает итоговый результат именно с отрицательным знаком, справедливо считая ежемесячный кредитный платеж убытком. Но нам для наглядности применения таблицы данных нужно, чтобы данное число было положительным. Поэтому мы и ставим знак «минус» перед одним из аргументов функции. Как известно, умножение «минус» на «минус» в итоге дает «плюс» .

    В поля «Бс» и «Тип» данные вообще не вносим. Клацаем по кнопке «OK» .


  4. После этого оператор производит подсчет и выводит в заранее обозначенную ячейку результат общего ежемесячного платежа – 30108,26 рублей. Но проблема состоит в том, что заёмщик в состоянии платить максимум 29000 рублей в месяц, то есть, ему следует либо найти банк, предлагающий условия с более низкой процентной ставкой, либо уменьшить тело займа, либо увеличить срок кредитования. Просчитать различные варианты действий нам поможет таблица подстановок.

  5. Для начала используем таблицу подстановок с одной переменной. Посмотрим, как будет изменяться величина обязательного месячного платежа при различных вариациях годовой ставки, начиная от 9,5% годовых и заканчивая 12,5% годовых с шагом 0,5% . Все остальные условия оставляем неизменными. Чертим табличный диапазон, наименования колонок которого будут соответствовать различным вариациям процентной ставки. При этом строку «Ежемесячные выплаты» оставляем так, как есть. В первой её ячейке должна содержаться формула, которую мы рассчитали ранее. Для большей информативности можно добавить строки «Общая сумма кредита» и «Общая сумма процентов» . Столбец, в котором находится расчет, делаем без заголовка.

  6. Далее рассчитаем общую сумму займа при текущих условиях. Для этого выделяем первую ячейку строки «Общая сумма кредита» и умножаем содержимое ячеек «Ежемесячный платеж» и «Срок кредита» . После этого щелкаем по клавише Enter .

  7. Для расчета общей суммы процентов при текущих условиях аналогичным образом отнимаем от общей суммы займа величину тела кредита. Для вывода результата на экран щелкаем по кнопке Enter . Таким образом мы получаем сумму, которую переплачиваем при возврате займа.

  8. Теперь настало время применить инструмент «Таблица данных» . Выделяем весь табличный массив, кроме наименований строк. После этого переходим во вкладку «Данные» . Щелкаем по кнопке на ленте «Анализ «что если»» , которая размещена в группе инструментов «Работа с данными» (в Excel 2016 группа инструментов «Прогноз» ). Затем открывается небольшое меню. В нем выбираем позицию «Таблица данных…» .

  9. Открывается небольшое окошко, которое так и называется «Таблица данных» . Как видим, у него имеется два поля. Так как мы работаем с одной переменной, то нам понадобится только одно из них. Так как у нас изменения переменной происходит по столбцам, то мы будем использовать поле «Подставить значения по столбцам в» . Устанавливаем туда курсор, а затем кликаем по ячейке в исходном наборе данных, которая содержит текущую величину процентов. После того, как координаты ячейки отобразились в поле, жмем на кнопку «OK» .

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


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


Проанализировав данный табличный массив, следует сказать, что, как видим, только при ставке 9,5% годовых получается приемлемый для нас уровень ежемесячного платежа (менее 29000 рублей).


Способ 2: использование инструмента с двумя переменными

Конечно, отыскать в настоящее время банки, которые выдают кредит под 9,5% годовых, очень сложно, если вообще реально. Поэтому посмотрим, какие варианты существуют вложиться в приемлемый уровень ежемесячного платежа при различных комбинациях других переменных: величины тела займа и срока кредитования. При этом процентную ставку оставим неизменной (12,5%). В решении данной задачи нам поможет инструмент «Таблица данных» с использованием двух переменных.

  1. Чертим новый табличный массив. Теперь в наименованиях столбцов будет указываться срок кредитования (от 2 до 6 лет в месяцах с шагом в один год), а в строках — величина тела кредита (от 850000 до 950000 рублей с шагом 10000 рублей). При этом обязательным условием является то, чтобы ячейка, в которой находится формула расчета (в нашем случае ПЛТ ), располагалась на границе наименований строк и столбцов. Без выполнения данного условия инструмент при использовании двух переменных работать не будет.

  2. Затем выделяем весь полученный табличный диапазон, включая наименование столбцов, строк и ячейку с формулой ПЛТ . Переходим во вкладку «Данные» . Как и в предыдущий раз, щелкаем по кнопке «Анализ «что если»» , в группе инструментов «Работа с данными» . В открывшемся списке выбираем пункт «Таблица данных…» .

  3. Запускается окно инструмента «Таблица данных» . В данном случае нам потребуются оба поля. В поле «Подставлять значения по столбцам в» указываем координаты ячейки, содержащей срок кредита в первичных данных. В поле «Подставлять значения по строкам в» указываем адрес ячейки исходных параметров, содержащей величину тела кредита. После того, как все данные введены. Клацаем по кнопке «OK» .

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

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

  6. Перемещаемся во вкладку «Главная» и клацаем по значку «Условное форматирование» . Он расположен в блоке инструментов «Стили» на ленте. В раскрывшемся меню выбираем пункт «Правила выделения ячеек» . В дополнительном списке кликаем по позиции «Меньше…» .

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

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


Проанализировав табличный массив, можно сделать некоторые выводы. Как видим, при существующем сроке кредитования (36 месяцев), чтобы вложиться в выше обозначенную сумму ежемесячного платежа, нам нужно взять заём не превышающий 860000,00 рублей, то есть, на 40000 меньше первоначально запланированного.


Если же мы все-таки намерены брать кредит размером 900000 рублей, то срок кредитования должен составлять 4 года (48 месяцев). Только в таком случае размер ежемесячного платежа не превысит установленную границу в 29000 рублей.


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

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

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

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

Скачать заметку в формате или , примеры в формате

Анализ «что если» на основе Таблицы с одной переменной

На рис. 21.1 в ячейки В6 используется функция ПЛТ, косвенно зависящая от значения ячейки В2. Если вы измените годовую ставку ставка, функция ПЛТ обновит значение в ячейке В6. Цель состоит в том, чтобы одновременно увидеть, как месячный платеж будет меняться при пяти различных годовых ставках. Хотя это можно сделать путем написания формулы, функция Таблица может быть полезна по двум причинам:

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

Чтобы создать таблицу данных:

  1. Создайте заголовки А9:В9. В ячейке В10 введите формулу =В6. В ячейки А11:А15 введите значения годовой ставки для анализа. Выделите диапазон А10:В15.
  2. Пройдите по меню ДАННЫЕ –> Анализ «что если» –> Таблица данных , чтобы открыть диалоговое окно Таблица данных , или нажав и удерживая клавишу Alt, последовательно нажмите Ы, Ё, Т (после нажатия Alt в меню будут появляться подсказки).
  3. Поскольку вы анализируете влияние годовой ставки, укажите ссылку на нее в поле Подставлять значение по строкам в (рис. 21.2). Вы говорите Таблице данных , заменить значение из ячейки В2 в процессе расчета ПЛТ и вместо него подставить в формулу значения из диапазона А11:А15.
  4. Нажмите ОК.

Рис. 21.2. Диалоговое окно Таблица данных

Если вы выделите диапазон В11:В15 и взглянете на строку формул, то увидите формулу массива Таблица со ссылкой на ячейку В2. Функцию Таблица нельзя ввести с клавиатуры; она автоматически создается при использовании диалогового окна Таблица данных .

Рис. 21.3. Функцию Таблица можно ввести только с помощью диалогового окна Таблица данных

На рис. 21.4 ячейки в диапазон E3:I3 содержат различные формулы, которые прямо или косвенно ссылаются на число проданных штук (в ячейке В3). Используя Таблицу данные можно выполнить анализ «что если» для пяти формул. Причем все они основываются на одной и той же переменной, расположенной в диапазоне D4:D12.


Рис. 21.4. Одна переменная Таблицы данных может работать на нескольких формулах; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

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

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


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

Второй пример (рис. 21.6) вы уже видели в . Там использовалась формула массива. Например, в ячейке F9: =ИНДЕКС($C$2:$C$15;ПОИСКПОЗ($E9&F$8;$A$2:$A$15&$B$2:$B$15;0)). Решение на основе Таблицы данных проще, и работает быстрее.


Рис. 21.6. Использование Таблицы данных , как альтернатива ВПР по двум параметрам

Одно заключительное замечание по поводу Таблицы данных : существует параметр, который позволяет отключить автоматическое обновление Таблиц данных , при этом другие формулы будут пересчитываться автоматически. Если ваш файл «тормозит», пройдите по меню ФАЙЛ –> Параметры , перейдите на вкладку Формулы , и выберите опцию автоматически, кроме таблиц данных (рис. 21.7). Когда вы всё же захотите обновить вычисления в Таблице данных , нажмите F9.


Рис. 21.7. Отключение автоматического вычисления Таблиц данных