Это первая статья из серии из пяти частей по анализу данных в Excel. В этом разделе я покажу вам, как создать и использовать таблицу данных с одной переменной в Excel.
Другие статьи из серии "Анализ данных":
- Таблица двух переменных данных в Excel.
- Менеджер сценариев в Excel.
- Поиск цели в Excel.
- Решатель Excel.
Посмотреть видео - Таблица данных с одной переменной в Excel
Одна таблица данных переменных в Excel больше всего подходит в ситуациях, когда вы хотите увидеть, как изменится окончательный результат при изменении одной из входных переменных. Если вы хотите изменить две переменные, используйте таблицу данных с двумя переменными или диспетчер сценариев.
Когда использовать одну таблицу переменных данных в Excel
Предположим, у вас есть набор данных, как показано ниже:
В приведенном выше наборе данных у нас есть сумма ссуды, процентная ставка и количество ежемесячных платежей.
Значение ежемесячного платежа рассчитывается на основе этих трех переменных (оно выделено красным цветом, поскольку это отток денег). Для расчета ежемесячного платежа используется следующая формула:
= PMT (B2 / 12; B3; B1)
Теперь вы можете провести анализ, чтобы увидеть, какое количество ежемесячных платежей соответствует вашему состоянию и как будет меняться ежемесячный платеж в зависимости от этого. Например, вы можете сохранить срок действия кредита (количество ежемесячных платежей) таким образом, чтобы сумма ежемесячного платежа не превышала 1000 долларов.
Быстрый способ - изменить данные в этой таблице, и формула обновится автоматически. Однако, если вы хотите проверить, есть ли для многих разное количество ежемесячных платежей, тогда вам подойдет таблица с одной переменной в Excel.
Настройка таблицы данных с одной переменной в Excel
Вот шаги, чтобы настроить таблицу данных с одной переменной в Excel:
- В столбце укажите все различные значения количества ежемесячных платежей, которые вы хотите протестировать. В этом примере мы тестируем 72, 84, 96… 240.
- Введите = B4 в ячейку E1, которая находится на одну строку выше значений в соседнем столбце справа. Это конструкция, которой необходимо следовать при работе с одной таблицей переменных данных в Excel. Также убедитесь, что значение в ячейке E1 зависит от количества ежемесячных платежей. Это не сработает, если вы вручную введете значение в ячейку E1.
В этом случае ячейка E1 относится к ячейке B4, значение которой рассчитывается с использованием формулы, в которой используются ячейки B1, B2 и B3..
- Теперь все данные настроены для использования в таблице данных с одной переменной.
- Выберите данные (D1: E16). Перейдите на вкладку «Данные» -> «Инструменты для работы с данными» -> «Что, если бы» -> «Таблица данных».
- В диалоговом окне «Таблица данных» см. Ячейку B3 в поле «Входная ячейка столбца». Поскольку мы используем таблицу данных с одной переменной, оставьте ячейку ввода Row пустой. Кроме того, мы выбрали вариант ячейки ввода «Столбец», поскольку данные структурированы в виде столбца.
- Щелкните ОК. Как только вы нажмете ОК, он автоматически заполнит ячейки рядом с переменными точками данных (количество ежемесячных платежей).
Например, если вы хотите рассчитать, какое количество ежемесячных платежей вы должны использовать, чтобы ежемесячный платеж составлял менее 1000 долларов. Вы можете легко выбрать 144 месяца (12 лет).
Примечание:
- После того, как вы рассчитали значения с помощью таблицы данных, его нельзя отменить с помощью Control + Z. Однако вы можете вручную выбрать все значения и удалить их.
- Вы не можете удалить / изменить какую-либо одну ячейку во всем наборе рассчитанных значений. Поскольку это массив, вам придется удалить все значения.
- Вы можете выполнить тот же расчет, расположив данные по горизонтали. Единственная разница будет заключаться в том, что вам придется использовать ячейку ввода строки в диалоговом окне таблицы данных (вместо ячейки ввода столбца).
