Для чего используется таблица подстановки
ТАБЛИЦЫ ПОДСТАНОВКИ
Таблицы подстановки
Задачу прогнозирования для нескольких вариантов условий можно также решить с помощью таблиц подстановки. Вообще таблицами подстановки называются такие таблицы, которые автоматически заполняются результатами расчетов по одной или нескольким формулам. Эти формулы должны ссылаться на ячейки ввода – ячейки рабочего листа, в которые Excel поочередно подставляет варианты переменной, чтобы получить результат расчета для заполнения таблицы подстановки. Существует два типа таблиц подстановки: таблицы с одним входом (одной переменной) и таблицы с двумя входами (двумя переменными).
Задание 14. Таблица с одним входом
Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами. Можно использовать любое количество ссылок на формулы. Верхняя левая ячейка таблицы не используется.
Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в ячейки соответствующего столбца.
Рис. 6. 13. Таблица подстановки с одним входом
Воспользуемся тем же примером расчета, который рассматривали при подборе параметров. Для этого откройте лист «Задание 12».
На рис. 6.5. отображены результаты расчетов при цене товара, равной 1 руб. Создайте таблицу подстановки для следующих вариантов цены: 5, 10, 15, 20, 25 и 30 руб. Для этого нужно выполнить следующие операции:
— В ячейки строки E9:J9 и столбца D2:D7 последовательно введите заданные варианты переменной (цены).
Следующие действия зависят от того, введены варианты в строку или в столбец:
Для таблицы подстановки, организованной в столбцы, следует:
— В ячейку E2 скопировать формулу прибыли (из ячейки В8). Правее этой ячейки можно ввести другие формулы, например, расчета расходов (из В3 в F2) – рис. 6.17.
— Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D2:F8 –для строк). В диапазон попадает ячейка, содержащая только текст (пустая ячейка), набор цен и формулы расчета прибыли и расходов.
Рис. 6. 14. Выбор команды подстановки таблицы данных
— Появится диалоговое окно «Таблица подстановки» (рис. 6.15), в которые нужно ввести адрес ячейки ввода с формулой расчета прибыли.
Рис. 6. 15. Окно задания параметров в вертикальную таблицу подстановки
После нажатия кнопки ОК Excel поочередно подставит в ячейку ввода варианты переменных, а результатами расчетов заполнит соответствующие ячейки таблицы подстановки (рис. 6.17).
Для таблицы подстановки, организованной в строки, следует:
— В ячейки D12 и D13 скопируйте формулу прибыли из ячейки В8 ( рис. 6.17).
— Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D11:J13).
— В диалоговом окне «Таблица подстановки» (рис. 6.16) введите адрес ячейки ввода с формулой расчета прибыли.
Рис. 6. 16. Окно задания параметров в горизонтальную таблицу подстановки
Рис. 6. 17. Результаты выполнения процедуры Таблица данных с одним входом
Для любой таблицы данных постройте график зависимости прибыли и расходов от цены (рис. 6.18). Для этого
Рис. 6. 18. График зависимости прибыли и расходов от цены (логарифмическая шкала)
Задание 15. Таблица с двумя входами
Таблица подстановки с двумя входами заполняется для двух переменных. Используя ее, можно, например, предсказать вероятную прибыль сразу для нескольких вариантов цены и количества выпускаемых изделий.
Макет таблицы (рис. 6.19) выглядит похожим на таблицу подстановки с одной ячейкой исходных данных. Однако результаты расчетов можно вести только по одной формуле. В верхней строке содержатся значения для подстановки второго входного параметра. Только в верхней левой ячейке находится ссылка на ячейку с единственной формулой.
Рис. 6. 19. Таблица подстановки с двумя входами
Рис. 6. 20. Результаты выполнения процедуры Таблица подстановки с двумя входами
— Подготовить на рабочем листе таблицу, в которую введите варианты переменных. В используемом примере (рис. 6.20), варианты цены размещены в столбец (ячейки I3:I8), а варианты количества – в строку (ячейки J2:M2).
— В угловую ячейку таблицы I2 скопируйте формулу, ссылающуюся на эти две переменные (на две ячейки ввода). В данном случае вы определяете возможную прибыль, поэтому копируйте формулу прибыли из ячейки В8.
— Выделите всю таблицу с вариантами переменных и формулой (ячейки I2:M8).
— В диалоговом окне «Таблица подстановки» (рис. 6.21) введите адреса ячеек ввода с формулой расчета прибыли.
Рис. 6. 21. Окно задания параметров для таблицы подстановки с двумя входами
— В поле «Подставлять значения по столбцам в» следует ввести адрес ячейки В1. В поле «Подставлять значения по строкам в» следует ввести адрес ячейки В2.
После нажатия кнопки ОК Excel поочередно подставит в соответствующие ячейки ввода все варианты переменных, а результаты расчетов введет в ячейки таблицы подстановки (рис. 6.20).
Для наглядности, по данным таблицы можно построить диаграмму поверхности (рис. 6.22), объединяющую все возможные варианты.
Рис. 6. 22. Диаграмма поверхности, иллюстрирующая все варианты таблицы с двумя входами (после форматирования)
НЕДОСТАТКИ АНАЛИЗА ДАННЫХ С ПОМОЩЬЮ ТАБЛИЦ ПОДСТАНОВКИ:
1. Одновременно можно анализировать данные только при изменении одного или двух исходных параметров.
2. Процесс создания таблицы подстановки интуитивно не всегда понятен.
4. Часто достаточно иметь результаты расчетов только для некоторых определенных комбинаций входных параметров, а не всю таблицу.
Таблицы подстановки в Excel
Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.
Таблицы подстановки данных можно использовать для
Изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул. Возможные значения одного или двух аргументов функции представляют в виде списка или таблицы. При использовании одного аргумента список исходных значений задается в виде строки или столбца таблицы. Excel подставляет эти значения в функцию (формулу), заданную пользователем, а затем выстраивает результаты также соответственно в строку или в столбец.
Изменения двух исходных значений, просматривая результаты только одной формулы. При использовании таблицы с двумя переменными значениями одно из них располагается в столбце, другое — в строке; результат вычислений получают на пересечении строки и столбца.
На конкретном примере
Задание: использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами. Рассчитать ежемесячные выплаты по займу и платежи по процентам. Исходные данные приведены на рисунке ниже.
Порядок работы
=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.
=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.
Краткая справка. При решении задач, связанных с использованием таблицы подстановки, рекомендуется применять в формулах абсолютную адресацию ячеек. Это способствует правильному выполнению вычислений в ячейках рабочего листа.
Результат
Для чего используется таблица подстановки
Тема 5. Создание таблиц данных (таблицы подстановок)
Создание таблицы данных с одной переменной.
Ситуация, предприниматель желает рассмотреть варианты суммы возврата вклада, в зависимости от процентной ставки, которую предлагают различные банки для одного и того же срока вклада. В разделе (Тема 4) речь шла о подборе параметра для одной переменной, которая имела функциональную связь с элементами таблицы.
Рис. 1. Таблица с исходными данными для проведения сравнительного анализа
Этапы построения таблицы данных или, как чаще говорят – таблицы подстановки.
§ Создать на листе дополнительные заголовки столбцов, которые показаны на рис. 2. Первый столбец будет иметь заголовок «Варианты процентной ставки», второй столбец будет иметь имя «Сумма возврата», Третий столбец – «Прирост за время хранения».
§ Заполните первый столбец с вариантами процентной ставки, в примере указаны значения ставок от 3% до 10% (обратите внимание, что ввод данных в этом столбце начат после оставленной пустой ячейки E 3 ).
Рис. 2. Подготовительные действия на листе Excel перед созданием таблицы данных (таблицы подстановок)
Рис. 3. Подготовка к созданию таблицы с данными
Рис. 4. Диалоговое окно Таблица данных с введённым адресом ячейки, в которой меняются значения для построенной таблицы
Рис. 5. Окончательный вариант представления таблицы данных (таблицы подстановки), в которой подставлены данные для суммы возврата средств через 5 лет в зависимости от условий годовой процентной ставки
§ Постройте график по данным созданной таблицы данных.
Создание таблиц данных с двумя переменными
Исследователи очень часто пользуются номограммами, которые строятся на основе одной и той же зависимости, но для различных входных данных. Такой же эффект можно достичь при использовании технологии таблиц данных (таблиц подстановок). Рассмотрим модель исследования поведения экономической системы.
Предположим, предприятие собирается провести рекламную компанию о реализации нового вида продукции. Реклама должна быть направлена потенциальному покупателю по почте. Известно, что не более 3,5% респондентов делают заказы на рекламируемую продукцию, остальная рассылка пропадает. Но, тем не менее, за каждый заказ предприятие получит доход в размере 22 рубля. Организация рассылки рекламы включает такие затраты, как почтовые расходы – 0,32 руб. за одно почтовое отправление и расходы на печать рекламных материалов. Предположим, что фирма, которая предоставляет полиграфические услуги на печать рекламной продукции, ведёт гибкую политику цен. Полиграфисты предлагают стоимость печатных материалов 0,20 руб. за штуку, если тираж не превышает 200000 экземпляров; 0,15 руб. за штуку для тиража от 200001 до 300000 экземпляров; 0,10 руб. за штуку, если тираж превышает 300000 экземпляров.
С позиции руководства предприятия, которое желает реализовать новую продукцию и получить при этом прибыль, необходимо воспользоваться среднестатистическими данными по отрасли, сделать заказ печатной продукции, разослать рекламу и ожидать конечных результатов. Плановый отдел получает указания, провести анализ возможных вариантов развития ситуации (которая зависит от тиража рекламной продукции, реального количества респондентов, сделавшие заказ новой продукции). Напомним, что может случиться так, что полученные доходы от реализации продукции не смогут покрыть расходов на рекламу.
Решение задачи можно осуществить различными способами, в данном случае воспользуемся средством Excel – Таблицы данных (таблицы подстановок), построим модель вычисления прибыли для изменения начальных условий в исходных данных.
Рис. 6. Представление модели вычисления прибыли от реализации новой продукции при определённых условиях
Шаг 3. Подготовка создания таблицы данных. Таблица данных – это отображение автоматического вычисления заданного параметра для изменяемых исходных данных (в рассматриваемом случае заданным параметром является Прибыль, хотя можно задать и другой параметр, например, «Суммарные расходы» и т.п.). Подготовка таблицы подразумевает создание ячейки, в которой будет определён интересующий исследователя параметр (на рис. 7 это ячейка C 18 ). По строке в ячейках D 18: I 18 указаны варианты вероятного процента респондентов, ответивших на рекламу, а по столбцу в ячейках C 19: C 28 содержатся различные варианты тиража рекламной продукции (изменение тиража осуществляется с постоянным приращением, равным 25000 единиц).
Рис. 7. Подготовка места на листе Excel для создания таблицы данных при проведении анализа данных
Рис. 8. Вход в режим работы с вариантом – Таблица данных
На экране появится диалоговое окно « Таблица данных», в которое следует ввести координаты ячеек с исходными данными (рис. 9). Следует отметить, что исходные данные были выбраны исследователем (менеджером) случайным образом, просто для проверки, что можно получить при заданных условиях. На самом деле, менеджер стремится подобрать наиболее приемлемый вариант при определённой ситуации, по этой причине его интересует просмотр нескольких вариантов решения задачи.
Рис. 9. Диалоговое окно для задания координат исходных данных
Рис. 10. Окончательные результаты, представленные в виде таблицы данных
Задача, которая была реализована с помощью инструмента – Таблица данных, имела два входа (одним входом является – количество рассылаемого материала, второй вход – вероятность получения отклика от респондентов в процентах). Всегда удобнее вести анализ результатов с использованием графиков или номограмм. В данном примере, для построения номограмм, используются координаты строк и столбцов (рис. 11). Если в качестве исходных данных выбрать только строку, например, количество экземпляров печатной продукции, то получится таблица данных с одним входом. Соответственно в качестве исследуемого параметра может быть любой элемент из таблицы с результатами расчётов (можно ввести несколько параметров). К сожалению, инструмент Таблица данных работает только с одним и двумя входами.
Рис. 11. Графическое отображение значений таблицы данных
Контрольное задание : Построить таблицу данных для проведения анализа суммарных расходов предприятия, в зависимости от тиража разосланных материалов при постоянном значении процента ответивших респондентов, например, процент составляет 1,95%, построить график, аналогично представленного на рис. 6.
Вопросы для самоконтроля
1. В чём заключается идея проведения анализа данных с помощью инструмента – Таблицы данных?
2. Какие ограничения существуют в инструменте Таблицы данных?
3. Для чего проводится разметка местоположения для таблицы данных?
Финансы в Excel
Таблицы подстановки
Microsoft Excel включает в свой состав несколько интересных средств для анализа данных. Данная статья описывает возможности одного из таких интерфейсных решений для проведения вычислений при помощи «таблицы подстановки» (в последних версиях Excel называется «таблица данных»).
Основным практическим применением таблицы подстановки является создание матриц факторного анализа показателей (анализа чувствительности) экономических моделей на изменение входящих параметров.
Для получения набора результатов функции с одним параметром необходимо сформировать 2 столбца (либо строки): ячейки параметров ячейки результатов. Ссылка на ячейку для получения базового результата анализа должна располагаться в верхнем правом углу диапазона. Там, кстати, может быть и более сложная формула, а не просто ссылка на ячейку.
После закрытия окна будут заполнены значения NPV для разных ставок дисконтирования.
Как же это работает? В момент проведения расчетов Excel последовательно подставляет значения из строк и/или столбцов заголовка матрицы к заданным в диалоге ячейкам. После завершения каждого цикла вычислений результат записывается в соответсвующую ячейку таблицы подстановки. Затем происходит переход к следующему параметру, и вычисления повторяются. И так до тех пор, пока не будут перебраны все варианты параметров.
Очевидно, что при работе с большими таблицами подстановки вычисления, производимые в цикле, будут существенно замедлять работу с файлами. Чтобы этого не происходило, в Excel имеется специальный режим расчетов «Автоматически, кроме таблиц». С данной установкой при любом изменении формул, таблицы подстановки обновляться не будет до тех пор, пока пересчет не запущен принудительно (например, по нажатию F9).
Применение таблиц подстановки оправдано только в случае сложных расчетов. Если в конечном итоге требуется рассчитать одно- или двухпараметрическую формулу, то проще ее скопировать на всю матрицу результатов, используя абсолютные и относительные ссылки. Иными словами, если имеется возможность в эту ячейку ввести расчетную формулу целиком, то вероятнее всего для расчетной модели вообще не следует использовать таблицу подстановки. В нижней части файла-примера показано как таблица подстановки может быть заменена стандартной формулой.
Таблица данных в Microsoft Excel
Довольно часто требуется рассчитать итоговый результат для различных комбинаций вводных данных. Таким образом пользователь сможет оценить все возможные варианты действий, отобрать те, результат взаимодействия которых его удовлетворяет, и, наконец, выбрать самый оптимальный вариант. В Excel для выполнения данной задачи существует специальный инструмент – «Таблица данных» («Таблица подстановки»). Давайте узнаем, как им пользоваться для выполнения указанных выше сценариев.
Использование таблицы данных
Инструмент «Таблица данных» предназначен для того, чтобы рассчитывать результат при различных вариациях одной или двух определенных переменных. После расчета все возможные варианты предстанут в виде таблицы, которую называют матрицей факторного анализа. «Таблица данных» относится к группе инструментов «Анализ «что если»», которая размещена на ленте во вкладке «Данные» в блоке «Работа с данными». До версии Excel 2007 этот инструмент носил наименование «Таблица подстановки», что даже более точно отражало его суть, чем нынешнее название.
Таблицу подстановки можно использовать во многих случаях. Например, типичный вариант, когда нужно рассчитать сумму ежемесячного платежа по кредиту при различных вариациях периода кредитования и суммы займа, либо периода кредитования и процентной ставки. Также этот инструмент можно использовать при анализе моделей инвестиционных проектов.
Но также следует знать, что чрезмерное применение данного инструмента может привести к торможению системы, так как пересчет данных производится постоянно. Поэтому рекомендуется в небольших табличных массивах для решения аналогичных задач не использовать этот инструмент, а применять копирование формул с помощью маркера заполнения.
Оправданным применение «Таблицы данных» является только в больших табличных диапазонах, когда копирование формул может отнять большое количество времени, а во время самой процедуры увеличивается вероятность допущения ошибок. Но и в этом случае рекомендуется в диапазоне таблицы подстановки отключить автоматический пересчет формул, во избежание излишней нагрузки на систему.
Главное отличие между различными вариантами применения таблицы данных состоит в количестве переменных, принимающих участие в вычислении: одна переменная или две.
Способ 1: применение инструмента с одной переменной
Сразу давайте рассмотрим вариант, когда таблица данных используется с одним переменным значением. Возьмем наиболее типичный пример с кредитованием.
Итак, в настоящее время нам предлагаются следующие условия кредитования:
Выплаты происходят в конце платежного периода (месяца) по аннуитетной схеме, то есть, равными долями. При этом, вначале всего срока кредитования значительную часть выплат составляют процентные платежи, но по мере сокращения тела процентные платежи уменьшаются, а увеличивается размер погашения самого тела. Общая же выплата, как уже было сказано выше, остается без изменений.
Нужно рассчитать, какова будет сумма ежемесячного платежа, включающего в себя погашение тела кредита и выплат по процентам. Для этого в Экселе имеется оператор ПЛТ.
ПЛТ относится к группе финансовых функций и его задачей является вычисление ежемесячного кредитного платежа аннуитетного типа на основании суммы тела кредита, срока кредитования и процентной ставки. Синтаксис этой функции представлен в таком виде
«Ставка» — аргумент, определяющий процентную ставку кредитных выплат. Показатель выставляется за период. У нас период выплат равен месяцу. Поэтому годовую ставку в 12,5% следует разбить на число месяцев в году, то есть, 12.
«Кпер» — аргумент, определяющий численность периодов за весь срок предоставления кредита. В нашем примере период равен одному месяцу, а срок кредитования составляет 3 года или 36 месяцев. Таким образом, количество периодов будет рано 36.
«ПС» — аргумент, определяющий приведенную стоимость кредита, то есть, это размер тела кредита на момент его выдачи. В нашем случае этот показатель равен 900000 рублей.
«БС» — аргумент, указывающий на величину тела кредита на момент его полной выплаты. Естественно, что данный показатель будет равен нулю. Этот аргумент не является обязательным параметром. Если его пропустить, то подразумевается, что он равен числу «0».
«Тип» — также необязательный аргумент. Он сообщает о том, когда именно будет проводиться платеж: в начале периода (параметр – «1») или в конце периода (параметр – «0»). Как мы помним, у нас платеж проводится в конце календарного месяца, то есть, величина этого аргумента будет равна «0». Но, учитывая то, что этот показатель не является обязательным, и по умолчанию, если его не использовать, значение и так подразумевается равным «0», то в указанном примере его вообще можно не применять.
Ставим курсор в поле «Ставка», после чего кликаем по ячейке на листе со значением годовой процентной ставки. Как видим, в поле тут же отображаются её координаты. Но, как мы помним, нам нужна месячная ставка, а поэтому производим деление полученного результата на 12 (/12).
В поле «Кпер» таким же образом вносим координаты ячеек срока кредита. В этом случае делить ничего не надо.
В поле «Пс» нужно указать координаты ячейки, содержащей величину тела кредита. Выполняем это. Также ставим перед отобразившемся координатами знак «-». Дело в том, что функция ПЛТ по умолчанию выдает итоговый результат именно с отрицательным знаком, справедливо считая ежемесячный кредитный платеж убытком. Но нам для наглядности применения таблицы данных нужно, чтобы данное число было положительным. Поэтому мы и ставим знак «минус» перед одним из аргументов функции. Как известно, умножение «минус» на «минус» в итоге дает «плюс».
В поля «Бс» и «Тип» данные вообще не вносим. Клацаем по кнопке «OK».
Кроме того, можно заметить, что величина ежемесячного платежа при 12.5% годовых, полученная в результате применения таблицы подстановок, соответствует величине при том же размере процентов, которую мы получили путем применения функции ПЛТ. Это лишний раз доказывает правильность расчета.
Проанализировав данный табличный массив, следует сказать, что, как видим, только при ставке 9,5% годовых получается приемлемый для нас уровень ежемесячного платежа (менее 29000 рублей).
Способ 2: использование инструмента с двумя переменными
Конечно, отыскать в настоящее время банки, которые выдают кредит под 9,5% годовых, очень сложно, если вообще реально. Поэтому посмотрим, какие варианты существуют вложиться в приемлемый уровень ежемесячного платежа при различных комбинациях других переменных: величины тела займа и срока кредитования. При этом процентную ставку оставим неизменной (12,5%). В решении данной задачи нам поможет инструмент «Таблица данных» с использованием двух переменных.
Проанализировав табличный массив, можно сделать некоторые выводы. Как видим, при существующем сроке кредитования (36 месяцев), чтобы вложиться в выше обозначенную сумму ежемесячного платежа, нам нужно взять заём не превышающий 860000,00 рублей, то есть, на 40000 меньше первоначально запланированного.
Если же мы все-таки намерены брать кредит размером 900000 рублей, то срок кредитования должен составлять 4 года (48 месяцев). Только в таком случае размер ежемесячного платежа не превысит установленную границу в 29000 рублей.
Таким образом, воспользовавшись данным табличным массивом и проанализировав «за» и «против» каждого варианта, заёмщик может принять конкретное решение об условиях кредитования, выбрав наиболее отвечающий его пожеланиям вариант из всех возможных.
Конечно, таблицу подстановок можно использовать не только для расчета кредитных вариантов, но и для решения множества других задач.
В общем, нужно отметить, что таблица подстановок является очень полезным и сравнительно простым инструментом для определения результата при различных комбинациях переменных. Применив одновременно с ним условное форматирование, кроме того, можно визуализировать полученную информацию.
Помимо этой статьи, на сайте еще 12511 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.