Содержание
Довольно часто требуется рассчитать итоговый результат для различных комбинаций вводных данных. Таким образом пользователь сможет оценить все возможные варианты действий, отобрать те, результат взаимодействия которых его удовлетворяет, и, наконец, выбрать самый оптимальный вариант. В Excel для выполнения данной задачи существует специальный инструмент – «Таблица данных» («Таблица подстановки»). Давайте узнаем, как им пользоваться для выполнения указанных выше сценариев.
Использование таблицы данных
Инструмент «Таблица данных» предназначен для того, чтобы рассчитывать результат при различных вариациях одной или двух определенных переменных. После расчета все возможные варианты предстанут в виде таблицы, которую называют матрицей факторного анализа. «Таблица данных» относится к группе инструментов «Анализ «что если»», которая размещена на ленте во вкладке «Данные» в блоке «Работа с данными». До версии Excel 2007 этот инструмент носил наименование «Таблица подстановки», что даже более точно отражало его суть, чем нынешнее название.
Таблицу подстановки можно использовать во многих случаях. Например, типичный вариант, когда нужно рассчитать сумму ежемесячного платежа по кредиту при различных вариациях периода кредитования и суммы займа, либо периода кредитования и процентной ставки. Также этот инструмент можно использовать при анализе моделей инвестиционных проектов.
Но также следует знать, что чрезмерное применение данного инструмента может привести к торможению системы, так как пересчет данных производится постоянно. Поэтому рекомендуется в небольших табличных массивах для решения аналогичных задач не использовать этот инструмент, а применять копирование формул с помощью маркера заполнения.
Оправданным применение «Таблицы данных» является только в больших табличных диапазонах, когда копирование формул может отнять большое количество времени, а во время самой процедуры увеличивается вероятность допущения ошибок. Но и в этом случае рекомендуется в диапазоне таблицы подстановки отключить автоматический пересчет формул, во избежание излишней нагрузки на систему.
Главное отличие между различными вариантами применения таблицы данных состоит в количестве переменных, принимающих участие в вычислении: одна переменная или две.
Способ 1: применение инструмента с одной переменной
Сразу давайте рассмотрим вариант, когда таблица данных используется с одним переменным значением. Возьмем наиболее типичный пример с кредитованием.
Итак, в настоящее время нам предлагаются следующие условия кредитования:
- Срок кредитования – 3 года (36 месяцев);
- Сумма займа – 900000 рублей;
- Процентная ставка – 12,5% годовых.
Выплаты происходят в конце платежного периода (месяца) по аннуитетной схеме, то есть, равными долями. При этом, вначале всего срока кредитования значительную часть выплат составляют процентные платежи, но по мере сокращения тела процентные платежи уменьшаются, а увеличивается размер погашения самого тела. Общая же выплата, как уже было сказано выше, остается без изменений.
Нужно рассчитать, какова будет сумма ежемесячного платежа, включающего в себя погашение тела кредита и выплат по процентам. Для этого в Экселе имеется оператор ПЛТ.
ПЛТ относится к группе финансовых функций и его задачей является вычисление ежемесячного кредитного платежа аннуитетного типа на основании суммы тела кредита, срока кредитования и процентной ставки. Синтаксис этой функции представлен в таком виде
«Ставка» — аргумент, определяющий процентную ставку кредитных выплат. Показатель выставляется за период. У нас период выплат равен месяцу. Поэтому годовую ставку в 12,5% следует разбить на число месяцев в году, то есть, 12.
«Кпер» — аргумент, определяющий численность периодов за весь срок предоставления кредита. В нашем примере период равен одному месяцу, а срок кредитования составляет 3 года или 36 месяцев. Таким образом, количество периодов будет рано 36.
«ПС» — аргумент, определяющий приведенную стоимость кредита, то есть, это размер тела кредита на момент его выдачи. В нашем случае этот показатель равен 900000 рублей.
«БС» — аргумент, указывающий на величину тела кредита на момент его полной выплаты. Естественно, что данный показатель будет равен нулю. Этот аргумент не является обязательным параметром. Если его пропустить, то подразумевается, что он равен числу «0».
«Тип» — также необязательный аргумент. Он сообщает о том, когда именно будет проводиться платеж: в начале периода (параметр – «1») или в конце периода (параметр – «0»). Как мы помним, у нас платеж проводится в конце календарного месяца, то есть, величина этого аргумента будет равна «0». Но, учитывая то, что этот показатель не является обязательным, и по умолчанию, если его не использовать, значение и так подразумевается равным «0», то в указанном примере его вообще можно не применять.
-
Итак, приступаем к расчету. Выделяем ячейку на листе, куда будет выводиться расчетное значение. Клацаем по кнопке «Вставить функцию».
Запускается Мастер функций. Производим переход в категорию «Финансовые», выбираем из перечня наименование «ПЛТ» и клацаем по кнопке «OK».
Вслед за этим происходит активация окошка аргументов вышеуказанной функции.
Ставим курсор в поле «Ставка», после чего кликаем по ячейке на листе со значением годовой процентной ставки. Как видим, в поле тут же отображаются её координаты. Но, как мы помним, нам нужна месячная ставка, а поэтому производим деление полученного результата на 12 (/12).
В поле «Кпер» таким же образом вносим координаты ячеек срока кредита. В этом случае делить ничего не надо.
В поле «Пс» нужно указать координаты ячейки, содержащей величину тела кредита. Выполняем это. Также ставим перед отобразившемся координатами знак «-». Дело в том, что функция ПЛТ по умолчанию выдает итоговый результат именно с отрицательным знаком, справедливо считая ежемесячный кредитный платеж убытком. Но нам для наглядности применения таблицы данных нужно, чтобы данное число было положительным. Поэтому мы и ставим знак «минус» перед одним из аргументов функции. Как известно, умножение «минус» на «минус» в итоге дает «плюс».
В поля «Бс» и «Тип» данные вообще не вносим. Клацаем по кнопке «OK».
После этого оператор производит подсчет и выводит в заранее обозначенную ячейку результат общего ежемесячного платежа – 30108,26 рублей. Но проблема состоит в том, что заёмщик в состоянии платить максимум 29000 рублей в месяц, то есть, ему следует либо найти банк, предлагающий условия с более низкой процентной ставкой, либо уменьшить тело займа, либо увеличить срок кредитования. Просчитать различные варианты действий нам поможет таблица подстановок.
Для начала используем таблицу подстановок с одной переменной. Посмотрим, как будет изменяться величина обязательного месячного платежа при различных вариациях годовой ставки, начиная от 9,5% годовых и заканчивая 12,5% годовых с шагом 0,5%. Все остальные условия оставляем неизменными. Чертим табличный диапазон, наименования колонок которого будут соответствовать различным вариациям процентной ставки. При этом строку «Ежемесячные выплаты» оставляем так, как есть. В первой её ячейке должна содержаться формула, которую мы рассчитали ранее. Для большей информативности можно добавить строки «Общая сумма кредита» и «Общая сумма процентов». Столбец, в котором находится расчет, делаем без заголовка.
Далее рассчитаем общую сумму займа при текущих условиях. Для этого выделяем первую ячейку строки «Общая сумма кредита» и умножаем содержимое ячеек «Ежемесячный платеж» и «Срок кредита». После этого щелкаем по клавише Enter.
Для расчета общей суммы процентов при текущих условиях аналогичным образом отнимаем от общей суммы займа величину тела кредита. Для вывода результата на экран щелкаем по кнопке Enter. Таким образом мы получаем сумму, которую переплачиваем при возврате займа.
Теперь настало время применить инструмент «Таблица данных». Выделяем весь табличный массив, кроме наименований строк. После этого переходим во вкладку «Данные». Щелкаем по кнопке на ленте «Анализ «что если»», которая размещена в группе инструментов «Работа с данными» (в Excel 2016 группа инструментов «Прогноз»). Затем открывается небольшое меню. В нем выбираем позицию «Таблица данных…».
Открывается небольшое окошко, которое так и называется «Таблица данных». Как видим, у него имеется два поля. Так как мы работаем с одной переменной, то нам понадобится только одно из них. Так как у нас изменения переменной происходит по столбцам, то мы будем использовать поле «Подставить значения по столбцам в». Устанавливаем туда курсор, а затем кликаем по ячейке в исходном наборе данных, которая содержит текущую величину процентов. После того, как координаты ячейки отобразились в поле, жмем на кнопку «OK».
Кроме того, можно заметить, что величина ежемесячного платежа при 12.5% годовых, полученная в результате применения таблицы подстановок, соответствует величине при том же размере процентов, которую мы получили путем применения функции ПЛТ. Это лишний раз доказывает правильность расчета.
Проанализировав данный табличный массив, следует сказать, что, как видим, только при ставке 9,5% годовых получается приемлемый для нас уровень ежемесячного платежа (менее 29000 рублей).
Способ 2: использование инструмента с двумя переменными
Конечно, отыскать в настоящее время банки, которые выдают кредит под 9,5% годовых, очень сложно, если вообще реально. Поэтому посмотрим, какие варианты существуют вложиться в приемлемый уровень ежемесячного платежа при различных комбинациях других переменных: величины тела займа и срока кредитования. При этом процентную ставку оставим неизменной (12,5%). В решении данной задачи нам поможет инструмент «Таблица данных» с использованием двух переменных.
-
Чертим новый табличный массив. Теперь в наименованиях столбцов будет указываться срок кредитования (от 2 до 6 лет в месяцах с шагом в один год), а в строках — величина тела кредита (от 850000 до 950000 рублей с шагом 10000 рублей). При этом обязательным условием является то, чтобы ячейка, в которой находится формула расчета (в нашем случае ПЛТ), располагалась на границе наименований строк и столбцов. Без выполнения данного условия инструмент при использовании двух переменных работать не будет.
Затем выделяем весь полученный табличный диапазон, включая наименование столбцов, строк и ячейку с формулой ПЛТ. Переходим во вкладку «Данные». Как и в предыдущий раз, щелкаем по кнопке «Анализ «что если»», в группе инструментов «Работа с данными». В открывшемся списке выбираем пункт «Таблица данных…».
Запускается окно инструмента «Таблица данных». В данном случае нам потребуются оба поля. В поле «Подставлять значения по столбцам в» указываем координаты ячейки, содержащей срок кредита в первичных данных. В поле «Подставлять значения по строкам в» указываем адрес ячейки исходных параметров, содержащей величину тела кредита. После того, как все данные введены. Клацаем по кнопке «OK».
Программа выполняет расчет и заполняет табличный диапазон данными. На пересечении строк и столбцов теперь можно наблюдать, каким именно будет ежемесячный платеж, при соответствующей величине годовых процентов и указанном сроке кредитования.
Как видим, значений довольно много. Для решения других задач их может быть ещё больше. Поэтому, чтобы сделать выдачу результатов более наглядной и сразу определить, какие значения не удовлетворяют заданному условию, можно использовать инструменты визуализации. В нашем случае это будет условное форматирование. Выделяем все значения табличного диапазона, исключая заголовки строк и столбцов.
Перемещаемся во вкладку «Главная» и клацаем по значку «Условное форматирование». Он расположен в блоке инструментов «Стили» на ленте. В раскрывшемся меню выбираем пункт «Правила выделения ячеек». В дополнительном списке кликаем по позиции «Меньше…».
Вслед за этим открывается окно настройки условного форматирования. В левом поле указываем величину, менее которой ячейки будут выделены. Как помним, нас удовлетворяет условие, при котором ежемесячный платеж по кредиту будет составлять менее 29000 рублей. Вписываем данное число. В правом поле существует возможность выбора цвета выделения, хотя можно оставить его и по умолчанию. После того, как все требуемые настройки введены, клацаем по кнопке «OK».
Проанализировав табличный массив, можно сделать некоторые выводы. Как видим, при существующем сроке кредитования (36 месяцев), чтобы вложиться в выше обозначенную сумму ежемесячного платежа, нам нужно взять заём не превышающий 860000,00 рублей, то есть, на 40000 меньше первоначально запланированного.
Если же мы все-таки намерены брать кредит размером 900000 рублей, то срок кредитования должен составлять 4 года (48 месяцев). Только в таком случае размер ежемесячного платежа не превысит установленную границу в 29000 рублей.
Таким образом, воспользовавшись данным табличным массивом и проанализировав «за» и «против» каждого варианта, заёмщик может принять конкретное решение об условиях кредитования, выбрав наиболее отвечающий его пожеланиям вариант из всех возможных.
Конечно, таблицу подстановок можно использовать не только для расчета кредитных вариантов, но и для решения множества других задач.
В общем, нужно отметить, что таблица подстановок является очень полезным и сравнительно простым инструментом для определения результата при различных комбинациях переменных. Применив одновременно с ним условное форматирование, кроме того, можно визуализировать полученную информацию.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Таблица данных — это диапазон ячеек, в котором можно изменять значения в некоторых ячейках, а также принимать различные ответы на проблемы. Хороший пример таблицы данных — функция ПЛТ с различными суммами займа и ставками по выгодным тарифам для расчета количества по доступной цене в кредитную ссуду. При использовании разных значений для просмотра соответствующих вариантов результатов используется общая задача анализа данных.
В Microsoft Excel таблицы данных являются частью набора команд, которые называются средствами анализа "что если". Если вы создаете и анализируете таблицы данных, вы выполняете анализ "что если".
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе. Например, вы можете использовать таблицу данных для изменения процентной ставки и длительности займа, чтобы оценить возможные ежемесячные платежные суммы.
Примечание: Вы можете выполнять более быстрые вычисления с помощью таблиц данных и Visual Basic для приложений (VBA). Дополнительные сведения можно найти в статье что такое таблицы данных в Excel: более быстрые вычисления с помощью VBA.
Типы анализа "что если"
Существуют три типа средств анализа "что если" в Excel: сценарии, таблицы данныхи Поиск решения. В сценариях и таблицах данных используются наборы входных значений для вычисления возможных результатов. Поиск целей отличается друг от друга, используется один результат и рассчитываются возможные входные значения, которые приводят к результату.
Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных отображаются все результаты из одной таблицы на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.
В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Несмотря на то, что для ячейки ввода строки и для ячейки ввода в ячейку может быть задано только одна или две переменные (для одной из входных строк в столбец), в таблице данных могут содержаться как угодно различные значения переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.
Ознакомьтесь с дополнительными сведениями в этой статье, посвященными анализу " что если".
Создайте таблицы данных с одной или двумя переменными, в зависимости от количества переменных и формул, которые необходимо проверить.
Таблицы данных с одной переменной
Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, можно использовать таблицу данных с одной переменной, чтобы узнать, как различные тарифы влияют на ежемесячные выплаты по закладной с помощью функции ПЛТ. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.
На приведенном ниже рисунке ячейка D2 содержит формулу оплаты = ПЛТ (B3/12; B4;-B5), которая ссылается на ячейку ввода B3.
Таблицы данных с двумя переменными
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
На приведенном ниже рисунке ячейка C2 содержит формулу оплаты = ПЛТ (B3/12; B4;-B5), в которой используются две ячейки ввода: B3 и B4.
Вычисления в таблицах данных
Каждый раз, когда лист пересчитывается, любые таблицы данных также будут пересчитаны, даже если они не были изменены. Чтобы ускорить расчет листа, содержащего таблицу данных, можно изменить параметры вычислений для автоматического пересчета листа, но не для таблиц данных. Чтобы узнать больше, ознакомьтесь с разделом повышение скорости расчета на листе, содержащем таблицы данных.
Таблица данных с одной переменной содержит входные значения либо в одном столбце (ориентированном на столбец), либо в строке (ориентированной на строку). Любая формула в таблице данных с одной переменной должна ссылаться только на один TE000127062.
Выполните указанные ниже действия:
Введите в ячейку ввода список значений, которые вы хотите подставить: либо на один столбец, либо в одну строку. Оставьте несколько пустых строк и столбцов с одной стороны значений.
Выполните одно из указанных ниже действий.
Если таблица данных ориентирована по столбцам (значения переменной находятся в столбце), введите формулу в ячейку на одну строку выше и на одну ячейку справа от столбца значения. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.
Если вы хотите проверить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
Если таблица данных ориентирована по строкам (значения переменной находятся в строке), введите формулу в ячейку один столбец слева от первого значения и одну ячейку под строкой значений.
Если вы хотите проверить влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На приведенном выше рисунке это диапазон C2: D5.
На вкладке данные щелкните анализ " что если" > таблице данных (в группе работа с данными или группа прогноза Excel_2016 ).
Выполните одно из указанных ниже действий.
Если таблица данных ориентирована по столбцам, введите TE000127027 ячейки ввода в поле ввода ячейки столбца . На рисунке выше ячейка ввода — B3.
Если таблица данных ориентирована по строкам, введите ссылку на ячейку для ячейки ввода в поле " ячейка ввода строки ".
Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результата отформатированы как денежные единицы.
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Выполните эти действия
Выполните одно из указанных ниже действий.
Если таблица данных ориентирована по столбцам, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
Если таблица данных ориентирована по строкам, введите новую формулу в пустую ячейку, расположенную под существующей формулой в первом столбце таблицы данных.
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
На вкладке данные щелкните анализ " что если "> таблице данных (в группе работа с данными или группа прогнозаExcel_2016 ).
Выполните одно из указанных ниже действий.
Если таблица данных ориентирована по столбцам, введите ссылку на ячейку для ячейки ввода в поле Подставлять значения по столбцам .
Если таблица данных ориентирована по строкам, введите ссылку на ячейку для ячейки ввода в поле Подставлять значения по строкам .
В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.
Выполните указанные ниже действия:
В ячейку на листе введите формулу, которая ссылается на две ячейки ввода.
В приведенном ниже примере значения в ячейках B3, B4 и B5 введены формулой = ПЛТ (B3/12; B4;-B5) в ячейке C2.
Введите один список входных значений в том же столбце под формулой.
В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.
Введите второй список в той же строке, где находится формула, и справа.
Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5).
В данном примере выделяется диапазон C2:E5.
На вкладке данные в группе Работа с данными или группа Прогноз (в Excel_2016 ) выберите анализ "что если" > таблице данных (в группе Работа с данными или группа прогноза Excel_2016 ).
В поле " ячейка ввода строки " введите ссылку на ячейку ввода для входных значений в строке.
Введите ячейку B4 в поле строка ввода .
В поле ввода ячейки столбца введите ссылку на ячейку ввода для входных значений в столбце.
Введите B3 в поле Вход в ячейку в столбце .
Нажмите кнопку ОК.
Пример таблицы данных с двумя переменными
Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ячейка C2 содержит формулу оплаты = ПЛТ (B3/12; B4;-B5), в которой используются две ячейки ввода: B3 и B4.
Если вы задаете этот параметр вычисления, при выполнении пересчета для всей книги не происходит вычисление таблицы данных. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9.
Чтобы повысить производительность вычислений, выполните указанные ниже действия.
Выполните одно из указанных ниже действий.
В 1st_Excel12shrt нажмите кнопку Microsoft Office 
нажмите кнопку Параметры Excelи выберите категорию формулы .
Во всех остальных версиях выберите параметры > файлов > формул.
В разделе Параметры вычислений в разделе подсчитать щелкните автоматически, кроме таблиц данных.
Совет: Кроме того, на вкладке формулы щелкните стрелку в разделе Параметры вычисленийи выберите пункт автоматически, Кроме таблиц данных (в группе Вычисление ).
Вы можете использовать несколько других средств Excel для анализа "что если", если у вас есть конкретные цели или большие наборы данных переменных.
Если вы знаете, что результат ожидается в формуле, но не знаете точно, какое значение требуется для получения этого результата, используйте функцию поиска решения. В этой статье описано использование подбора параметров для поиска нужного результата путем изменения входного значения.
Приложение Excel "Поиск решения"
С помощью надстройки Excel "Поиск решения" можно найти оптимальное значение для набора входных переменных. Поиск решения работает с группой ячеек (называемой переменными принятия решений или просто переменными ячейками), которые используются при вычислении формул в ячейках задания и ограничениях. Надстройка "Поиск решения" изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке. Дополнительные сведения в этой статье: Определение и устранение проблем с помощью поиска решения.
Подключив к ячейкам разные номера, вы можете быстро приступить к работе с разными ответами. В отличном примере используется функция ПЛТ с различными ставками и периодами займа (в месяцах), чтобы узнать, сколько ссуд вы можете позволить себе для домашнего или автомобильного автомобиля. Вы вводите номера в диапазон ячеек, которые называются таблицами данных.
Здесь таблица данных — это диапазон ячеек B2: D8. Вы можете изменить значение в ячейке B4, сумму ссуды и ежемесячные платежи в столбце г автоматически. Используя процентную ставку 3,75%, D2 возвращает ежемесячный платеж $1 042,01 с помощью этой формулы: = ПЛТ (C2/12, $B $3, $B $4).
Вы можете использовать одну или две переменные в зависимости от количества переменных и формул, которые нужно протестировать.
Используйте однострочный тест, чтобы увидеть, как разные значения одной переменной в формуле изменяют результаты. Например, вы можете изменить процентную ставку для ежемесячного платежа по закладной с помощью функции ПЛТ. Вы вводите значения переменной (ставку) в одном столбце или строке, и результаты отображаются в соседнем столбце или строке.
В этой книге в режиме реального времени ячейка D2 имеет формулу оплаты =ПЛТ (C2/12, $B $3, $B $4). Ячейка B3 — это ячейка переменной , в которой можно присоединиться к другой длительности (количество периодов ежемесячного платежа). В ячейке D2 функция ПЛТ подключается к процентной ставке 3,75%/12, 360 месяцев и $225 000 ссуды и вычисляет сумму $1 042,01 ежемесячного платежа.
Используйте двустороннюю проверку, чтобы увидеть, как разные значения двух переменных в формуле изменяют результаты. Например, вы можете протестировать различные сочетания ставок и количество ежемесячных платежных периодов, чтобы вычислить оплату по закладной.
В этой книге в режиме реального времени ячейка C3 содержит формулу оплаты, =плт ($B $3/12, $B $2, B4), в которой используются две ячейки переменных: B2 и B3. В ячейке C2 функция ПЛТ подключается к процентной ставке 3.875%/12, 360 месяцев и $225 000 ссуды и вычисляет сумму $1 058,03 ежемесячного платежа.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).
Таблица данных в Excel представляет собой диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это Анализ "что если", о котором мы говорили в одной из прошлых статей (если Вы ее не читали – очень рекомендую ознакомиться по этой ссылке), в удобном виде. Вы можете создать таблицу данных с одной или двумя переменными.
Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене – $50 и определенный % книг по более низкой цене – $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.
Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа "что если" в Excel.
Таблица данных с одной переменной.
Что бы создать таблицу данных с одной переменной, выполните следующие действия:
1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).
2. Введите различные проценты в столбце А.
3. Выберите диапазон A12:B17.
Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.
4. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.
5. Кликните в поле "Подставлять значения по строкам в: "и выберите ячейку C4.
Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.
Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода ("Подставлять значения по столбцам в: ") мы оставляем пустой.
Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.
Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.
Таблица данных с двумя переменными.
Что бы создать таблицу с двумя переменными, выполните следующие шаги.
1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).
2. Внесите различные варианты высокой цены в строку 12.
3. Введите различные проценты в столбце А.
4. Выберите диапазон A12:D17.
Мы будем рассчитывать выручку от реализации книг в различных комбинациях высокой цены и % продаж книг по высокой цене.
5. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.
6. Кликните в поле "Подставлять значения по столбцам в: " и выберите ячейку D7.
7. Кликните в поле "Подставлять значения по строкам в: " и выберите ячейку C4.
Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.
Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.
Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.
Спасибо за внимание. Теперь Вы сможете более эффективно применять один из видов анализа "что если" , а именно формирование таблиц данных с одной или двумя переменными.
Остались вопросы – задавайте их в комментариях ниже, также не забывайте подписываться на нас в социальных сетях.
