Одна таблица переменных данных в Excel | Как создать? (с примерами)

Что такое таблица данных с одной переменной в Excel?

Одна таблица данных переменных в Excel означает изменение одной переменной вместе с несколькими параметрами и получение результатов для нескольких сценариев.

Как создать таблицу данных с одной переменной в Excel? (с примерами)

Вы можете скачать этот шаблон Excel для таблицы с одной переменной здесь - Шаблон для таблицы с одной переменной для Excel

Пример # 1

Вы берете ссуду в размере рупий. 2 50000 за 2 года. Вы обсуждаете процентную ставку с кредитным специалистом.

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

  • Шаг 1: Рассчитайте ежемесячный EMI с помощью функции PMT.

  • Шаг 2: Теперь создайте таблицу сценария, как показано на рисунке ниже.

  • Шаг 3: Выберите ячейку E9 и дайте ссылку на ячейку B6 (EMI Amount). Теперь ячейка E9 показывает ежемесячный EMI.

  • Шаг 4: Выберите диапазон от D9 до E22.

  • Шаг 5. Щелкните вкладку «Данные», затем «  Что, если-анализ» и « Таблица данных».

  • Шаг 6: Появится диалоговое окно таблицы данных. В ячейке ввода столбца выберите ячейку B5 (которая содержит базовую процентную ставку по кредиту).

Одна таблица переменных данных в Excel, мы всегда игнорируем либо ячейку ввода ROW, либо ячейку ввода столбца. Это зависит от нашей структуры таблицы. Если в нашей таблице сценария разные процентные ставки расположены вертикально, мы игнорируем входную ячейку строки, а если наша таблица сценария процентные ставки горизонтальные, мы игнорируем входную ячейку столбца. В этом примере я проигнорировал входную ячейку Row, потому что в таблице сценария различные процентные ставки расположены вертикально.

  • Шаг 7: Теперь нажмите кнопку ОК, чтобы создать различные сценарии.

Теперь диапазон E10: E22 показывает некоторые новые значения. Из таблицы очень ясно, что ежемесячный EMI при процентной ставке 12,5% будет составлять 11 827 INR, а EMI при процентной ставке 13,5% будет составлять 11 944 INR и так далее.

Вот как работает одна таблица переменных данных в Excel. Вы также можете отобразить это на диаграмме.

Пример # 2

Предположим, вы - менеджер по продажам компании. От руководства вы получили от вашей команды ежемесячный план продаж в размере 1 70 000 долларов США. В таблице ниже показан план продаж для 6 членов. Вам необходимо проанализировать, каким должен быть уровень их эффективности, чтобы достичь цели 1,7 лакха долларов США в месяц.

Общая цель ваших команд - 2,04 лакха. Вы не уверены, какой процент эффективности они должны внести в таблицу, чтобы достичь цели, поставленной руководством.

Ваша команда может обеспечить максимальный уровень эффективности 90%, и вы рассчитали общий доход при уровне эффективности 90%.

При уровне эффективности 90% ваша команда может получить общий доход в 1,83 лакха долларов США в месяц. Вам необходимо, какой должен быть уровень эффективности для достижения целевого дохода, поставленного руководством.

Создайте таблицу сценария, как показано на рисунке ниже.

  • Шаг 1: Под таблицей вам нужно создать файл Excel.

В этой таблице для разных уровней эффективности показано, каков будет доход?

  • Шаг 2: Выберите ячейку H3 и дайте ссылку на ячейку B11 (ячейка дохода с уровнем эффективности 90%). Теперь ячейка H3 показывает доход с уровнем эффективности 90%.

  • Шаг 3: Выберите диапазон от G3 до H12

  • Шаг 4: Теперь найдите таблицу данных в разделе «Что, если».

  • Шаг 5: После того, как вы нажмете на таблицу данных, нам нужно дать ссылку в ячейке ввода столбца , выберите ячейку B10 (которая содержит процент эффективности).

Для ячейки ввода столбца я дал ссылку на ячейку B10, потому что на основе различных уровней эффективности мы собираемся создавать сценарии. Теперь таблица данных понимает, что при 90% доходе будет 1,83 лакха долларов США. Точно так же он создаст сценарии для 100%, 95%, 90%, 85%, 80% и так далее.

  • Шаг 6: Нажмите ОК, чтобы создать разные сценарии.

Теперь диапазон G3: H12 показывает сценарии. Руководство поставило цель на этот месяц в 1,70 лакха долларов. Чтобы получить такой доход, ваша команда должна работать с уровнем эффективности не менее 85%.

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

То, что нужно запомнить

  • Как только сценарии созданы, мы не можем отменить действие. Он останется прежним.
  • Мы даже не можем изменить значения ячеек, потому что это становится формулой массива excel, и вам нужно удалять все, а не по одному.
  • В одной таблице переменных данных всегда оставляйте ячейку ввода Row, если сценарий должен отображаться в вертикальной форме. Если сценарий должен отображаться в горизонтальной форме, оставьте ячейку ввода Столбец.
  • Вы можете изменить значение в основной базе данных, чтобы увидеть результаты в реальном времени при различных типах альтернатив.