Разбираем таблицу данных на примере цены и объёма продаж. Настраиваем две ячейки подстановки и проверяем результат независимой формулой.
В модели всё пересчитывается, когда вы меняете цену. Но для сравнения двадцати пяти сочетаний цены и объёма приходится снова вводить числа и записывать результаты. Таблица данных позволяет получить такую матрицу из одной расчётной модели.
Самое неудобное место находится в небольшом окне настройки. Excel просит ячейку ввода по строке и ячейку ввода по столбцу. Ошибка в выборе этих двух адресов способна испортить всю матрицу. Поэтому сначала разложим данные на листе и посчитаем один результат вручную.
Модель для проверки
Все числа в примере учебные. Цена продажи составляет 1 500 рублей за единицу, объём — 1 000 единиц. Переменные расходы равны 800 рублям на единицу, постоянные — 200 000 рублей за рассматриваемый период. Налоги и финансирование в этом примере не учитываем.
Заполните входы на одном листе.
| Ячейка | Показатель | Значение |
|---|---|---|
| B5 | Цена продажи | 1500 |
| B6 | Объём продаж | 1000 |
| B7 | Переменные расходы на единицу | 800 |
| B8 | Постоянные расходы | 200000 |
В B10 запишите расчёт прибыли.
=(B5-B7)*B6-B8
Получится 500 000 рублей. Проверка на обычном калькуляторе даёт тот же ответ — разница между ценой и переменными расходами составляет 700 рублей, после умножения на объём и вычитания постоянных расходов остаётся 500 000.
Где разместить варианты
В B15 поставьте ссылку =B10. В C15, D15, E15, F15 и G15 введите цены 1 000, 1 250, 1 500, 1 750 и 2 000. В B16, B17, B18, B19 и B20 введите объёмы 600, 800, 1 000, 1 200 и 1 400.
Получается рамка для будущей матрицы. Её верхняя строка содержит цены, левый столбец — объёмы, в углу находится ссылка на результат модели. Внутренние ячейки пока оставьте пустыми. Они будут содержать прибыль для каждой пары входов.
Важно сохранить в B15 именно ссылку. Если вместо неё вписать готовые 500 000, связь с вычислением пропадёт. Таблица должна получать результат формулы при очередной подстановке.
Как заполнить окно настройки
Выделите B15:G20 и откройте «Данные», затем «Анализ “что если”» и «Таблица данных».
В поле ввода по строке укажите B5. В верхней строке стоят цены, а цена модели находится в B5. В поле ввода по столбцу укажите B6. Слева стоят объёмы, а объём модели находится в B6.
Определять адрес помогает смысл параметра. Для каждого ряда вариантов установите, какое исходное значение он заменяет. Такой порядок исключает попытку запомнить настройку по внешнему расположению полей диалога. Назначение этих полей описано в документации Microsoft по таблицам данных.
Для этого упражнения исходные ячейки и матрица находятся на одном листе. Если большая модель разнесена по книге, вынесите на лист чувствительности отдельные входы и убедитесь, что итоговая формула использует именно их.
Как проверить матрицу
При цене 1 500 и объёме 1 000 ответ должен остаться равным 500 000 рублей. В нашей раскладке это E18. Если там другое число, сравните адреса подстановки и формулу B15.
Вторая проверка — крайний сценарий. При цене 1 000 и объёме 600 результат составит минус 80 000 рублей. При цене 2 000 и объёме 1 400 он составит 1 480 000 рублей. Эти ответы легко пересчитать отдельно.
Для проверки всех сочетаний создайте рядом обычную матрицу формул. Если её заголовки находятся в J15:N15, а объёмы в I16:I20, в J16 можно записать следующее выражение и протянуть его по области.
=(J$15-$B$7)*$I16-$B$8
Ссылка на верхнюю строку сохраняет номер строки, ссылка на объём — столбец I. Постоянные параметры закреплены полностью. Сравните каждую ячейку с соответствующим результатом таблицы данных. Нулевое расхождение во всех двадцати пяти ячейках даёт более сильную проверку, чем удачно совпавший центральный сценарий.
Если значения не меняются
Поменяйте переменные расходы в B7 с 800 на 900. При исходных цене и объёме прибыль должна уменьшиться до 400 000 рублей. Если модель обновилась, а матрица осталась прежней, проверьте режим вычисления книги. Таблицы данных могут быть исключены из автоматического пересчёта. Этот режим и ручное обновление также описаны в документации Microsoft.
Одинаковые числа во всей матрице часто указывают на потерянную связь с входами. Проверьте, не зафиксирована ли цена числом внутри формулы, не скопирован ли готовый результат в угловую ячейку и не выбран ли другой лист модели.
Сама матрица показывает последствия заданных предположений. Она не сообщает вероятность сценария. В нашем примере цена и объём меняются независимо, хотя в реальном бизнесе они могут быть связаны. Для рабочего решения эту связь нужно описать отдельно.
Таблицы данных удобно осваивать на коротких моделях с проверяемым ответом. Такой разбор входит в корпоративное обучение Excel.
Практика на корпоративном обучении
Финансовые модели в Excel для экономистов и финансистов. Программа включает учебные задачи, самостоятельный расчёт и сверку результатов.