таблица данных с одним параметром
Таблицы подстановки в 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 р.
Краткая справка. При решении задач, связанных с использованием таблицы подстановки, рекомендуется применять в формулах абсолютную адресацию ячеек. Это способствует правильному выполнению вычислений в ячейках рабочего листа.
Результат
Одна таблица переменных данных в Excel | Как создать? (с примерами)
Что такое таблица данных с одной переменной в Excel?
Одна таблица данных переменных в Excel означает изменение одной переменной вместе с несколькими параметрами и получение результатов для нескольких сценариев.
Как создать таблицу данных с одной переменной в Excel? (с примерами)
Пример # 1
Вы берете ссуду в размере рупий. 2 50000 за 2 года. Вы обсуждаете процентную ставку с кредитным специалистом.
Вам необходимо проанализировать при разных процентных ставках, какой ежемесячный платеж вам нужно платить для погашения кредита. Для целей расчета возьмите базовую ставку 12% годовых.
Одна таблица переменных данных в Excel, мы всегда игнорируем либо ячейку ввода ROW, либо ячейку ввода столбца. Это зависит от нашей структуры таблицы. Если в нашей таблице сценария разные процентные ставки расположены вертикально, мы игнорируем входную ячейку строки, а если наша таблица сценария процентные ставки горизонтальные, мы игнорируем входную ячейку столбца. В этом примере я проигнорировал входную ячейку Row, потому что в таблице сценария различные процентные ставки расположены вертикально.
Теперь диапазон E10: E22 показывает некоторые новые значения. Из таблицы очень ясно, что ежемесячный EMI при процентной ставке 12,5% будет составлять 11 827 INR, а EMI при процентной ставке 13,5% будет составлять 11 944 INR и так далее.
Вот как работает одна таблица переменных данных в Excel. Вы также можете отобразить это на диаграмме.
Пример # 2
Ваша команда может обеспечить максимальный уровень эффективности 90%, и вы рассчитали общий доход при уровне эффективности 90%.
При уровне эффективности 90% ваша команда может получить общий доход в 1,83 лакха долларов США в месяц. Вам необходимо, какой должен быть уровень эффективности для достижения целевого дохода, поставленного руководством.
Создайте таблицу сценария, как показано на рисунке ниже.
В этой таблице для разных уровней эффективности показано, каков будет доход?
Для ячейки ввода столбца я дал ссылку на ячейку B10, потому что на основе различных уровней эффективности мы собираемся создавать сценарии. Теперь таблица данных понимает, что при 90% доходе будет 1,83 лакха долларов США. Точно так же он создаст сценарии для 100%, 95%, 90%, 85%, 80% и так далее.
Теперь диапазон G3: H12 показывает сценарии. Руководство поставило цель на этот месяц в 1,70 лакха долларов. Чтобы получить такой доход, ваша команда должна работать с уровнем эффективности не менее 85%.
Таким образом, вы можете использовать таблицу данных для создания различного анализа и выбора подходящего сценария для достижения целей.
data_client
Как создать таблицу данных с одной или двумя переменными в Excel
Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.
Таблица данных с одной переменной.
Что бы создать таблицу данных с одной переменной, выполните следующие действия:
1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).
2. Введите различные проценты в столбце А.
3. Выберите диапазон A12:B17.
Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.
4. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.
5. Кликните в поле «Подставлять значения по строкам в: «и выберите ячейку C4.
Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.
Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода («Подставлять значения по столбцам в: «) мы оставляем пустой.
Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.
Таблица данных с двумя переменными.
Что бы создать таблицу с двумя переменными, выполните следующие шаги.
1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).
2. Внесите различные варианты высокой цены в строку 12.
3. Введите различные проценты в столбце А.
4. Выберите диапазон A12:D17.
Мы будем рассчитывать выручку от реализации книг в различных комбинациях высокой цены и % продаж книг по высокой цене.
5. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.
6. Кликните в поле «Подставлять значения по столбцам в: » и выберите ячейку D7.
7. Кликните в поле «Подставлять значения по строкам в: » и выберите ячейку C4.
Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.
Глава 21. Таблицы данных
Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.
В этой главе рассматривается таблица данных, которая создает массив, как результат работы функции Таблица (эту функцию нельзя ввести в ячейки вручную). Таблица данных – это быстрый и простой способ выполнить анализ «что если» для сложных взаимосвязанных вычислений на основе формул. Эта функция позволяет изменять одну или две формулы входов и отображение того, что результаты. В главах 4, 5 и 10 уже были приведены примеры таблиц данных, которые использовались в сочетании с функциями базы данных.
Рис. 21.1. Ячейка В6 содержит формулу, которая косвенно зависит от значения ячейки В2
Скачать заметку в формате Word или pdf, примеры в формате Excel
Анализ «что если» на основе Таблицы с одной переменной
На рис. 21.1 в ячейки В6 используется функция ПЛТ, косвенно зависящая от значения ячейки В2. Если вы измените годовую ставку ставка, функция ПЛТ обновит значение в ячейке В6. Цель состоит в том, чтобы одновременно увидеть, как месячный платеж будет меняться при пяти различных годовых ставках. Хотя это можно сделать путем написания формулы, функция Таблица может быть полезна по двум причинам:
Чтобы создать таблицу данных:
Рис. 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) вы уже видели в главе 5. Там использовалась формула массива. Например, в ячейке F9: =ИНДЕКС($C$2:$C$15;ПОИСКПОЗ($E9&F$8;$A$2:$A$15&$B$2:$B$15;0)). Решение на основе Таблицы данных проще, и работает быстрее.
Рис. 21.6. Использование Таблицы данных, как альтернатива ВПР по двум параметрам
Одно заключительное замечание по поводу Таблицы данных: существует параметр, который позволяет отключить автоматическое обновление Таблиц данных, при этом другие формулы будут пересчитываться автоматически. Если ваш файл «тормозит», пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и выберите опцию автоматически, кроме таблиц данных (рис. 21.7). Когда вы всё же захотите обновить вычисления в Таблице данных, нажмите F9.
Рис. 21.7. Отключение автоматического вычисления Таблиц данных
1 комментарий для “Глава 21. Таблицы данных”
Спасибо за перевод!
Я не понимаю толка в ТД. Ведь она пересчитывается целиком, даже если только изменилась одна исходная ячейка — у такой же таблице, но на обычных заякоренных формулах в таком случае пересчёт будет только по зависимой строке или столбцу. Ну и зачем такая печаль-то нужна. «Ничё не понимаю»
Таблица данных в 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 рублей.
Таким образом, воспользовавшись данным табличным массивом и проанализировав «за» и «против» каждого варианта, заёмщик может принять конкретное решение об условиях кредитования, выбрав наиболее отвечающий его пожеланиям вариант из всех возможных.
Конечно, таблицу подстановок можно использовать не только для расчета кредитных вариантов, но и для решения множества других задач.
В общем, нужно отметить, что таблица подстановок является очень полезным и сравнительно простым инструментом для определения результата при различных комбинациях переменных. Применив одновременно с ним условное форматирование, кроме того, можно визуализировать полученную информацию.
Помимо этой статьи, на сайте еще 12280 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.
Отблагодарите автора, поделитесь статьей в социальных сетях.