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