Как в экселе найти таблицу подстановки - Исправление недочетов и поиск решений вместе с Examum.ru

Таблицы подстановки

Подробности: Создано 27 Март 2011

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

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

В файле-примере к данной статье построена микро-модель условного инвестиционного проекта. Анализ чувствительности проводится сначала для одного параметра — ставки дисконтирования. Во втором случае подставляется два параметра: ставка дисконтирования и сумма первоначальных инвестиций. Задача является типичной для проведения экономического анализа чувствительности. В условном примере вычисляния достаточно простые, поэтому вместо таблицы подстановки можно использовать стандартные формулы. В реальных экономических моделях вычисления гораздо сложнее, и реализовать матрицу чувствительности через формулы не удастся.

Для получения набора результатов функции с одним параметром необходимо сформировать 2 столбца (либо строки): ячейки параметров ячейки результатов. Ссылка на ячейку для получения базового результата анализа должна располагаться в верхнем правом углу диапазона. Там, кстати, может быть и более сложная формула, а не просто ссылка на ячейку.

Затем следует выделить область таблицы, включая ячейку с формулой (в примере B10:C14), и вызвать диалог формирования таблицы подстановки. В Excel2007-2013 — через Данные Работа с данными Анализ «что-если» Таблица данных, в Excel 97-2003 через меню Data Table. В диалоге необходимо указать ячейку, в которую следует подставлять указанные в таблице параметры. В примере варианты ставки дисконтирования располагаются по строкам, поэтому заполняем поле диалога «Подставлять значения по СТРОКАМ в:». Указываем ссылку на ячейку с рабочей ставкой дисконтирования, которая применяется в основных расчетах — $B$4.

После закрытия окна будут заполнены значения NPV для разных ставок дисконтирования.

Похожие действия необходимо произвести в случае двухмерной таблицы подстановки (матрицы). В диалоговом окне, кроме ссылки на параметр в строках требуется заполнить поле «Подставлять значения по СТОЛБЦАМ в:». Там указываем ссылку на рабочую ячейку с начальными инвестициями — $B$3. В отличие от вектора при использовании матрицы ссылка на результат должна располагаться в верхнем левом углу таблицы.

Как же это работает? В момент проведения расчетов Excel последовательно подставляет значения из строк и/или столбцов заголовка матрицы к заданным в диалоге ячейкам. После завершения каждого цикла вычислений результат записывается в соответсвующую ячейку таблицы подстановки. Затем происходит переход к следующему параметру, и вычисления повторяются. И так до тех пор, пока не будут перебраны все варианты параметров.

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

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

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

Смотри также

» Объединение строк

Функция efSumText возвращает объединенный текст с указанным разделителем.

» Финансовые функции

Microsoft Excel поддерживает множество функций, облегчающих финансовые вычисления. Целью данной статьи не является полный обзор функций, относящихся к финансовому разделу. Такое описания представлено в справочной системе Excel и других интернет-ресурсах. Следует также заметить, что некоторые финансовые функции имеют достаточно специфическую локальную направленность, другие сохраняются в целях обратной совместимости со старыми версиями Excel (и Lotus 1-2-3). Некоторые функций не включены в ядро Excel, а подключается только при активизации надстройки «Пакет анализа» (Analysis ToolPak).Для понимания и эффективного использования большинства финансовых функций бывает полезно добиться результата через построение экономических моделей простыми средствами электронных таблиц. Во-первых, таким образом вы сможете убедиться в…

Источник

Для анализа данных при выборе оптимального варианта финансового решения зачастую применяются Таблицы подстановки в Excel.Они позволяют проводить анализ изменения результата при произвольном диапазоне исходных данных. На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные. Данный пример подходит для версий программы Microsoft Office Excel версий 2007, 2010 и 2013.

Таблицы подстановки данных можно использовать для

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

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

На конкретном примере

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

Порядок работы

Запустите MicrosoftExcel и создайте новую электронную книгу.
Создайте таблицу ежемесячных выплат по займу и платежей по процентам по образцу.

Таблица — заготовка для решения
Расчет ежемесячных выплат по займу происходит с помощью функции ПЛТ (). В ячейку В5 введите формулу:

=ПЛТ ($В$4/12;$В$3*12;$В$2). Ежемесячная выплата составит 10178,42 р.

Расчет платежей по процентам происходит с помощью функции ПРОЦПЛАТ (). В ячейку D6 введите формулу:

=ПРОЦПЛАТ ($B$4;$D$5;$D$3;$D$2). Платежи по процентам составят 1350 р.

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

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

Расчет платежей

Далее вам нужно выделить диапазон ячеек A9:C18 , после чего перейти на вкладку данные, «анализ что-если» таблица данных. Первое поле «подставлять значения по столбцам в» оставить пустым, а в поле «подставлять значения по строкам в» указать ячейку с величиной процентной ставки зафиксировав ее знаками доллара $B$4.

Подстановка данных

Результат расчетов:

Результат

Используя сайт abuzov.ru, вы даете согласие на работу с cookie, Яндекс.Метрикой, Google Analytics для сбора технических данных.OKПодробнее

Источник

Таблицы подстановки данных можно использовать для

На конкретном примере

Порядок работы

Запустите 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).

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

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

Источник

Формирование данных с помощью таблиц подстановки.

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

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

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

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

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

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

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

П
осле
этого воспользуйтесь командой Table
(Таблица подстановки)
меню Data
(Данные).
В появившемся диалоговом окне следует
указать ячейку ввода в поле Row
input cell: (Подставлять значения по столбцам
в),
если список значений находится в строке
или в поле Column
input cell: (Подставлять значения по строкам
в) в
противном случае. Для таблицы подстановки
с двумя переменными следует указать
обе ячейки ввода.

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

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

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

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

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

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

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

Чтобы построить
таблицу подстановки с двумя переменными,
следует ввести два списка значений: в
отдельный столбец и отдельную строку.
Необходимы также две ячейки ввода,
располагающихся вне таблицы данных.
Рассчитаем массив значений для построения
поверхности z
= x²
— y²,
-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).

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

Диаграммы
в Microsoft Excel.

Вопросы для проверки.

Что
такое ось
категорий, ось значений, ось рядов
в диаграммах Microsoft Excel?
Какой инструмент
Microsoft Excel служит для построения диаграмм?
Как его вызвать?
Сколько шагов
необходимо для построения диаграммы?
Какие операции выполняются на каждом
шаге?
Какие типы диаграмм
отображают изменения показателей через
регулярные промежутки времени или по
категориям? Через нерегулярные интервалы?
Вы хотите построить
график неоднозначной функции. Какой
тип диаграммы Вы выберете?
На каком шаге
построения диаграммы Вы можете удалить
ненужные ряды данных или добавить
недостающие?
Какие существуют
возможности размещения диаграммы?
Существует ли
панель инструментов, предназначенная
для работы с диаграммами? Как она
называется? Как ее вызвать?
Предположим,
Вы создали диаграмму, а меню Chart
(Диаграмма)
отсутствует. Почему?
Как можно выделить
элемент диаграммы? Как его отформатировать?
Можно ли изменить
ориентацию объемной диаграммы? Если
да, то как?
Как можно добавить
данные в диаграмму?
Что такое таблица
подстановки?
Какие объекты
необходимы для того, чтобы создать
таблицу подстановки с одной переменной?
С двумя переменными?
Какие элементы
можно менять в таблице подстановки?
Какие нельзя?

Диаграммы
в Microsoft Excel.

Практическое задание.

С
помощью таблицы подстановки с одной
переменной создайте источник данных
для графика функции, заданной
параметрически: x
= Cos(t), y = Sin(t), 0 <= t <= 6.5.
Постройте график,
используя результаты предыдущего
упражнения.
С
помощью таблицы подстановки с двумя
переменными создайте таблицу данных
для построения поверхности z
= x⁴
— y⁴
, -5 <= x <= 5, -5 <= y <= 5.
Постройте
поверхность, используя результаты
предыдущего упражнения.
Замените
формулу подстановки на z
= Cos(x) + Cos(y).
Определите
новый диапазон данных для поверхности:
-2
<= x <= 2, -2 <= y <= 2.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

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

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

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

[stextbox id=»warning»]Выделите ячейку для создания формулы, расположенную на один столбец левее и на одну строку ниже первого значения, если значения в таблице подстановки располагаются строкой.[/stextbox]

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

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

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

Рис. 5.109. Вкладка «Данные». Меню кнопки «Анализ что-если». Пункт «Таблица данных»

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

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

[stextbox id=»warning»]При ориентации значения по строке необходимо выбрать графу «Подставлять значения по столбцам в».[/stextbox]

Закройте окно кнопкой «ОК». Исходная таблица примет следующий вид (рис. 5.111).

рис. 5.111

Источник