Таблицы подстановки в Excel
Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.
Таблицы подстановки данных можно использовать для
Изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул. Возможные значения одного или двух аргументов функции представляют в виде списка или таблицы. При использовании одного аргумента список исходных значений задается в виде строки или столбца таблицы. Excel подставляет эти значения в функцию (формулу), заданную пользователем, а затем выстраивает результаты также соответственно в строку или в столбец.
Изменения двух исходных значений, просматривая результаты только одной формулы. При использовании таблицы с двумя переменными значениями одно из них располагается в столбце, другое — в строке; результат вычислений получают на пересечении строки и столбца.
На конкретном примере
Задание: использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами. Рассчитать ежемесячные выплаты по займу и платежи по процентам. Исходные данные приведены на рисунке ниже.
Порядок работы
- Запустите MicrosoftExcel и создайте новую электронную книгу.
- Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.
=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.
- Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:
=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.
Краткая справка. При решении задач, связанных с использованием таблицы подстановки, рекомендуется применять в формулах абсолютную адресацию ячеек. Это способствует правильному выполнению вычислений в ячейках рабочего листа.
Где находится таблица подстановки в excel
На странице Новое настраиваемое поле в Microsoft Project Server 2010 можно указать параметры для настраиваемого поля. Ниже описаны действия для создания нового или изменения существующего корпоративного настраиваемого поля.
Чтобы создать или изменить корпоративные настраиваемые поля, сделайте следующее:
На странице параметров сервера PWA выберите Корпоративные настраиваемые поля и таблицы подстановки.
В разделе Корпоративные настраиваемые поля выберите поле, которое хотите изменить, или команду Создать поле, чтобы создать новое поле.
На странице Настраиваемое поле укажите нужные параметры настраиваемого поля. Описания всех полей см. в последующих разделах.
Нажмите кнопку Сохранить.
Название и описание
В полях Название и Описание укажите имя и описание настраиваемого поля. В таблице ниже описаны поля названия и описания.
Имя настраиваемого поля.
Описание настраиваемого поля.
Объект и тип
В полях Объект и Тип укажите объект (проект, ресурс или задача), для которого хотите создать настраиваемое поле, а также тип данных для этого поля.
В таблице ниже описаны все доступные объекты.
Используется для создания корпоративных настраиваемых полей, применяемых на уровне проекта.
Используется для создания корпоративных настраиваемых полей, применяемых на уровне ресурсов.
Используется для создания корпоративных настраиваемых полей, применяемых на уровне задач.
Значение поля Тип определяет тип данных в настраиваемом поле. От выбранного здесь значения зависят доступные параметры в разделах Настраиваемые атрибуты, Вычисления для суммарных строк и Поведение. В таблице ниже описаны доступные типы данных настраиваемого поля.
Настраиваемые поля затрат используются для определения денежных данных. Например, можно использовать настраиваемое поле затрат для определения утвержденного бюджета проекта.
Настраиваемые поля даты используются для указания данных на основе даты. Например, можно создать корпоративное настраиваемое поле под названием Дата утверждения проекта и использовать его для записи даты утверждения проекта.
Настраиваемые поля длительности используются для определения длительности. Они часто определяются как вычисления с использованием настраиваемых формул. Например, с помощью настраиваемого поля длительности руководитель проекта в вашей организации может показывать и хранить разницу между исходным и фактическим графиками проекта.
Настраиваемые поля флагов используются для определения параметров, имеющих всего два варианта для указания данных. Например, с помощью поля флага можно определить, какое действие следует выполнить: отобразить поле или включить макрос, который обнаруживает доступность конкретного набора данных в проекте.
Настраиваемые числовые поля позволяют определить любой набор числовых данных или выполнить вычисление с использованием настраиваемой формулы. Например, можно использовать поле на уровне задач для записи оцененных строк программы в проекте разработки программного обеспечения или сравнивать прогнозируемые и фактические затраты проекта.
Настраиваемые текстовые поля используются для определения простых буквенно-цифровых данных без иерархии. Например, можно создать настраиваемое текстовое поле с названием Состояние проекта и такими вариантами значений, как Начат, Утвержден, Выполняется, Приостановлен, Отменен и Закрыт.
Настраиваемые атрибуты
При выборе настраиваемого поля Текст проекта вы можете задать одну или несколько строк текста.
В таблице ниже описаны параметры настраиваемого текстового поля.
Выберите этот параметр, если хотите, чтобы настраиваемое поле могло содержать одну строку текста. Этот параметр доступен только для полей «Текст проекта».
Выберите этот параметр, если хотите, чтобы настраиваемое поле могло содержать несколько строк текста. Этот параметр доступен только для полей «Текст проекта». Поле проекта, при создании которого выбран этот параметр, не отображается на вкладке сведений о проекте в Project профессиональный 2010. Но его можно развернуть при использовании веб-страницы сведений о проекте.
Вы можете указать, должна ли таблица подстановок предоставлять значения для настраиваемого поля. Это позволит вам контролировать выбор значений для настраиваемого поля. Вы можете:
выбрать, должно ли использоваться значение по умолчанию, если не выбрано другое значение;
выбрать, можно ли выбирать несколько значений из таблицы подстановки;
выбрать, нужно ли ограничить доступные варианты для тех значений в таблице, у которых нет подчиненных значений.
Параметр таблицы подстановки доступен при выборе поля типа Текст.
В таблице ниже описаны параметры таблицы подстановки для настраиваемых полей.
Название корпоративной настраиваемой таблицы подстановки, которую вы хотите связать с настраиваемым полем. После того как вы свяжете таблицу подстановки с полем и сохраните его, удалить эту связь вы уже не сможете. Поэтому убедитесь, что таблица подстановки вам нужна, прежде чем создавать связь.
Выберите значение для использования по умолчанию при добавлении новых элементов
Если вам нужно, чтобы в настраиваемом поле было значение по умолчанию на случай, если пользователь не указывает другого значения, установите этот флажок и выберите значение по умолчанию.
Значение по умолчанию
Это значение будет использоваться по умолчанию в поле, если пользователь не укажет никакого значения. Чтобы указать значение, нажмите кнопку обзора и выберите нужное значение.
Разрешить только коды без подчиненных значений
Выберите этот параметр, если вам нужно, чтобы в таблице подстановок были только значения без подчиненных значений (то есть значений на нижнем уровне каждой ветви).
Разрешить выбор нескольких значений из таблицы подстановки
Выберите этот параметр, чтобы позволить пользователю выбирать несколько значений из таблицы подстановки. После выбора и сохранения его уже нельзя будет изменить.
Примечание: Если вы планируете создать настраиваемое поле, которое будет ссылаться на таблицу подстановок, создайте таблицу подстановок раньше настраиваемого поля.
Вы можете использовать формулы для задания собственных параметров, определяющих, как должны оцениваться данные корпоративных настраиваемых полей, или предоставляющих информацию о том, когда они используются в проекте. Формулы можно использовать не во всех типах корпоративных настраиваемых полей.
Параметр формулы доступен для всех типов полей.
Примечание: Формулу, связанную с настраиваемым полем, можно изменить, но нельзя удалить.
Чтобы использовать уже известную формулу, введите ее в окне Изменение формулы.
Чтобы добавить поле в формулу, щелкните Выбрать поле, выберите тип поля, а затем — имя поля, на которое хотите создать ссылку. Чтобы создать ссылку на существующее корпоративное настраиваемое поле, наведите указатель мыши на тип поля, затем — на тип настраиваемого поля (например, Настраиваемая дата или Настраиваемое окончание), а затем выберите нужное корпоративное настраиваемое поле.
Чтобы использовать функцию в формуле, щелкните Выбрать функцию, выберите тип функции, а затем — нужную функцию. Каждая функция содержит аргументы-заполнители, вместо которых вам нужно указать нужные поля и значения.
Чтобы использовать стандартные операторы при создании формулы, щелкните Выбрать оператор и выберите нужный оператор. В формуле можно использовать функции, литералы и ссылки на поля.
В таблице ниже описаны параметры формулы.
Формула, которую вы хотите использовать.
Вставка поля (затраты, дата, длительность, флаг, число или текст) в формулу.
Вставка функции (преобразование, дата и время, общая, математическая, Microsoft Project или текст) в формулу.
Вставка оператора (математического или логического) в формулу.
Отдел
Вы можете выбрать отдел и связать его с настраиваемым полем. Если вы выберете отдел, то видеть настраиваемое поле смогут только пользователи из этого отдела. Если отдел не указан, настраиваемое поле могут видеть все пользователи.
Доступное значение параметра Отдел указывается в настраиваемой таблице подстановки Отдел.
Вычисления для суммарных строк
Параметры вычисления суммарных строк можно выбрать для объектов типа Ресурс или Задача.
Для поля типа Текст вычисление суммарных строк недоступно.
В таблице ниже описаны параметры для вычисления суммарных строк.
Выберите Нет, если не хотите, чтобы настраиваемое поле применялось к суммарным строкам (в том числе суммарным строкам групп).
Выберите Сведение, чтобы свести отдельные строки в суммарную строку.
Выберите Использовать формулу, чтобы использовать указанную формулу для вычисления суммарной строки. Формулу необходимо указать в разделе Настраиваемые атрибуты.
Вычисления для строк назначений
Для объектов типа Ресурс или Задача вы можете выбрать развертывание вычислений для строк назначений.
В таблице ниже описаны параметры для вычисления строк назначений.
Выберите Нет, если не хотите развертывать строки назначений.
Развертывание при отсутствии данных, введенных вручную
Выберите Развертывание, если хотите, чтобы данные, введенные на уровне ресурсов или задач, развертывались и копировались в каждое назначение с тем же значением.
Отображаемые значения
Вы можете выбрать отображение данных без обработки или их графическое представление.
Если вы выберете Графические индикаторы, то сможете выбрать различные условия для несуммарных и суммарных строк, а при использовании объекта типа Проект и для суммарной задачи проекта.
Когда вы выбираете параметр, для него отображаются параметры настройки.
В таблице ниже описаны параметры для графических индикаторов.
Выберите Несуммарные строки, чтобы указать условия графического представления строк данных, не являющихся суммарными строками.
Выберите Суммарные строки, чтобы указать условия графического представления суммарных строк.
Сводка по проекту
Выберите Сводка по проекту, чтобы указать условия графического представления суммарной задачи проекта.
При настройке графических индикаторов можно указать точные значения и параметры сравнения, определяющие, когда используется определенная графика. Доступны следующие параметры сравнения (условия проверки):
Больше или равно
Меньше или равно
В пределах (внутри)
Вне пределов (вне)
С их помощью значения данных сравниваются с указанным вами пороговым значением, чтобы определить, какую графику отображать. Например, можно настроить отображение зеленого индикатора для значений, которые больше или равны 50, и красного индикатора для значений, которые меньше 50.
Вы можете указать любое количество изображений для различных значений. Для каждого сравнения условия проверки и значения добавляйте новую строку в таблицу. Строки в таблице проверяются сверху вниз, и отображается изображение, связанное с первой строкой, в которой истинно сочетание условия проверки и значения.
В таблице ниже описаны параметры графических индикаторов для несуммарных строк.
Выберите оператор (равно, меньше и т. д.), который будет применяться к значению поля для определения нужного изображения.
Введите значение поля или ссылку на поле (например, [затраты]), которое в сочетании с оператором из столбца Проверка определяет, когда нужно использовать изображение из столбца Рисунок.
Выберите изображение, которое будет отображаться в случае истинности сочетания условия проверки и значения.
С помощью стрелок переместите строку вверх или вниз в таблице.
Показывать значения данных во всплывающих подсказках
Выберите этот атрибут, чтобы значение поля отображалось в подсказках, связанных с изображением.
При использовании графических индикаторов для суммарных строк вы можете выбрать наследование параметров графических индикаторов, заданных для несуммарных строк.
Если при настройке графических индикаторов для суммарных строк вы установите флажок Наследовать условия от несуммарных строк, будут использоваться параметры графических индикаторов, которые вы настроили для несуммарных строк.
Если при настройке графических индикаторов для суммарной задачи проекта вы установите флажок Наследовать условия от суммарных строк, будут использоваться параметры графических индикаторов, которые вы настроили для суммарных строк.
Поведение
Вы можете сделать так, чтобы настраиваемое поле управлялось рабочим процессом или чтобы в нем обязательно было указано значение.
Если вы укажете, что настраиваемым полем должен управлять рабочий процесс, параметр «Обязательное поле» будет недоступен, поскольку поведение поля будет определять рабочий процесс.
В таблице ниже описаны параметры для настройки поведения настраиваемого поля.
Поведение управляется рабочим процессом
Установите этот флажок, если хотите, чтобы поведение настраиваемого поля определял рабочий процесс.
Сделать это поле обязательным к заполнению
Выберите этот параметр, если вам нужно, чтобы поле было обязательным (то есть поле не должно оставаться пустым). Этот параметр недоступен, если установлен флажок Поведение управляется рабочим процессом.
Таблица данных в Microsoft Excel
Довольно часто требуется рассчитать итоговый результат для различных комбинаций вводных данных. Таким образом пользователь сможет оценить все возможные варианты действий, отобрать те, результат взаимодействия которых его удовлетворяет, и, наконец, выбрать самый оптимальный вариант. В Excel для выполнения данной задачи существует специальный инструмент – «Таблица данных» («Таблица подстановки»). Давайте узнаем, как им пользоваться для выполнения указанных выше сценариев.
Использование таблицы данных
Инструмент «Таблица данных» предназначен для того, чтобы рассчитывать результат при различных вариациях одной или двух определенных переменных. После расчета все возможные варианты предстанут в виде таблицы, которую называют матрицей факторного анализа. «Таблица данных» относится к группе инструментов «Анализ «что если»», которая размещена на ленте во вкладке «Данные» в блоке «Работа с данными». До версии Excel 2007 этот инструмент носил наименование «Таблица подстановки», что даже более точно отражало его суть, чем нынешнее название.
Таблицу подстановки можно использовать во многих случаях. Например, типичный вариант, когда нужно рассчитать сумму ежемесячного платежа по кредиту при различных вариациях периода кредитования и суммы займа, либо периода кредитования и процентной ставки. Также этот инструмент можно использовать при анализе моделей инвестиционных проектов.
Но также следует знать, что чрезмерное применение данного инструмента может привести к торможению системы, так как пересчет данных производится постоянно. Поэтому рекомендуется в небольших табличных массивах для решения аналогичных задач не использовать этот инструмент, а применять копирование формул с помощью маркера заполнения.
Оправданным применение «Таблицы данных» является только в больших табличных диапазонах, когда копирование формул может отнять большое количество времени, а во время самой процедуры увеличивается вероятность допущения ошибок. Но и в этом случае рекомендуется в диапазоне таблицы подстановки отключить автоматический пересчет формул, во избежание излишней нагрузки на систему.
Главное отличие между различными вариантами применения таблицы данных состоит в количестве переменных, принимающих участие в вычислении: одна переменная или две.
Способ 1: применение инструмента с одной переменной
Сразу давайте рассмотрим вариант, когда таблица данных используется с одним переменным значением. Возьмем наиболее типичный пример с кредитованием.
Итак, в настоящее время нам предлагаются следующие условия кредитования:
- Срок кредитования – 3 года (36 месяцев);
- Сумма займа – 900000 рублей;
- Процентная ставка – 12,5% годовых.
Выплаты происходят в конце платежного периода (месяца) по аннуитетной схеме, то есть, равными долями. При этом, вначале всего срока кредитования значительную часть выплат составляют процентные платежи, но по мере сокращения тела процентные платежи уменьшаются, а увеличивается размер погашения самого тела. Общая же выплата, как уже было сказано выше, остается без изменений.
Нужно рассчитать, какова будет сумма ежемесячного платежа, включающего в себя погашение тела кредита и выплат по процентам. Для этого в Экселе имеется оператор ПЛТ.
ПЛТ относится к группе финансовых функций и его задачей является вычисление ежемесячного кредитного платежа аннуитетного типа на основании суммы тела кредита, срока кредитования и процентной ставки. Синтаксис этой функции представлен в таком виде
«Ставка» — аргумент, определяющий процентную ставку кредитных выплат. Показатель выставляется за период. У нас период выплат равен месяцу. Поэтому годовую ставку в 12,5% следует разбить на число месяцев в году, то есть, 12.
«Кпер» — аргумент, определяющий численность периодов за весь срок предоставления кредита. В нашем примере период равен одному месяцу, а срок кредитования составляет 3 года или 36 месяцев. Таким образом, количество периодов будет рано 36.
«ПС» — аргумент, определяющий приведенную стоимость кредита, то есть, это размер тела кредита на момент его выдачи. В нашем случае этот показатель равен 900000 рублей.
«БС» — аргумент, указывающий на величину тела кредита на момент его полной выплаты. Естественно, что данный показатель будет равен нулю. Этот аргумент не является обязательным параметром. Если его пропустить, то подразумевается, что он равен числу «0».
«Тип» — также необязательный аргумент. Он сообщает о том, когда именно будет проводиться платеж: в начале периода (параметр – «1») или в конце периода (параметр – «0»). Как мы помним, у нас платеж проводится в конце календарного месяца, то есть, величина этого аргумента будет равна «0». Но, учитывая то, что этот показатель не является обязательным, и по умолчанию, если его не использовать, значение и так подразумевается равным «0», то в указанном примере его вообще можно не применять.
-
Итак, приступаем к расчету. Выделяем ячейку на листе, куда будет выводиться расчетное значение. Клацаем по кнопке «Вставить функцию».
Ставим курсор в поле «Ставка», после чего кликаем по ячейке на листе со значением годовой процентной ставки. Как видим, в поле тут же отображаются её координаты. Но, как мы помним, нам нужна месячная ставка, а поэтому производим деление полученного результата на 12 (/12).
В поле «Кпер» таким же образом вносим координаты ячеек срока кредита. В этом случае делить ничего не надо.
В поле «Пс» нужно указать координаты ячейки, содержащей величину тела кредита. Выполняем это. Также ставим перед отобразившемся координатами знак «-». Дело в том, что функция ПЛТ по умолчанию выдает итоговый результат именно с отрицательным знаком, справедливо считая ежемесячный кредитный платеж убытком. Но нам для наглядности применения таблицы данных нужно, чтобы данное число было положительным. Поэтому мы и ставим знак «минус» перед одним из аргументов функции. Как известно, умножение «минус» на «минус» в итоге дает «плюс».