Анализ «Что, если» с помощью таблиц данных в Excel

Анализ «Что, если» — это опция, доступная в «Данных». В анализе «Что, если», изменяя входное значение в некоторых ячейках, вы можете увидеть влияние на результат. В нем показывается о взаимосвязи между входными и выходными значениями.

Что такое анализ «Что, если»?

Анализ «Что, если» — это процедура в Excel, в которой мы работаем с данными в табличной форме. В анализе «Что, если» в ячейке листа Excel были различные значения, чтобы увидеть результат по-разному, не создавая разные таблицы. Существует три инструмента анализа «Что, если».

Инструменты анализа «Что, если»

В анализе «Что, если» есть три инструмента:

  1. Поиск цели
  2. Менеджер сценариев
  3. Таблица данных

Поиск цели

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

Шаг 1: Запишите все предметы и их оценки на листе Excel и подсчитайте сумму , применив формулу sum.

Шаг 2: Перейдите на вкладку «Данные» панели инструментов.

Шаг 3: В разделе «Таблица данных» выберите анализ «Что, если».

Шаг 4: Появится выпадающий список. Выберите цель поиска.

Шаг 5: Появится диалоговое окно, в первом столбце введите название ячейки, в которой вы применяете формулу sum. Введите D10 в заданную ячейку.

Шаг 6: Во втором столбце напишите значение целевого параметра. Целевое значение для этого примера равно 440.

Шаг 7: В третьем столбце напишите название ячейки, в которой вы хотите получить оценки на английском языке. Укажите абсолютную ссылку на ячейку, т.е. $ D $ 5.

Шаг 8: Нажмите ok и посмотрите результат. Предполагаемые оценки по английскому составляют 71.

Диспетчер сценариев

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

Шаг 1: Задан набор данных для выручки за январь, в столбцах которого указаны расходы и Себестоимость .
набор данных для диспетчера сценариев в анализе "Что, если"
Шаг 2: Выберите ячейку с числовым значением и перейдите к данным.

Шаг 3: В разделе «Прогноз » нажмите на анализ «Что, если».

Шаг 4: Появится выпадающий список. Выберите диспетчер сценариев.

Шаг 5: В диалоговом окне выберите опцию добавления появится диалоговое окно.

Шаг 6: Появится новое диалоговое окно для ввода названия нового сценария в первом столбце. Под названием сценария напишите “Доход за февраль”.

Шаг 7: Во втором столбце выберите изменяющуюся ячейку. Изменяющиеся ячейки в этом примере составляют $ E $ 5:$ E $ 9.

Шаг 8: Появится новое диалоговое окно с названием «Значения сценария» для записи измененного значения в поле. Введите значения, как показано на рисунке. Нажмите Ok.

Шаг 9: Повторите шаг5, шаг6 и шаг8.



Шаг 10: Нажмите Ok , затем выберите сводка.

Шаг 11: Появится новое диалоговое окно с названием «Сводка сценария». Выберите ячейки результата: 10 долларов СШАE$.

Шаг 12: Посмотрите результат.

Таблица данных

В data мы создаем таблицу с различными входными значениями для одних и тех же переменных. Это одна из наиболее полезных функций в анализе «Что, если». В x можно изменять разные значения и соответственно получать разные результаты как для исследовательских, так и для бизнес-целей.

Таблица данных бывает двух типов:

Таблица данных в одной переменной

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

Шаг 1: Создайте таблицу затрат на выручку.

Шаг 2: Скопируйте последнюю ячейку, в которой вы получили выходные данные, в другую ячейку. Для этого примера используйтеD7 .
скопируйте последнюю ячейку, чтобы добавить таблицу данных в одну переменную
Шаг 3: Запишите значения в ячейку, для которой вы хотите внести изменения, в столбец или в строки.

Шаг 4: Перейдите на вкладку » Данные» панели инструментов.

Шаг 5: В разделе «Таблица данных » выберите анализ «Что, если».

Шаг 6: Появится выпадающий список. Выберите таблицу данных.

Шаг 7: Появится диалоговое окно с названием «Таблица данных», затем выберите ячейку, в которой вы хотите изменить входное значение в строке или в столбце. Введите значение в ячейку ввода столбца, равное $ D $ 3. Нажмите Ok. Ваша таблица данных готова.

Таблица данных в двух переменных

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

Шаг 1: Создайте таблицу для поиска PMT.

Шаг 2: Скопируйте последнюю ячейку, в которой вы получили выходные данные, в другую ячейку

Шаг 3: Запишите оба значения, которые вы хотите изменить, как в столбцах, так и в строках.

Шаг 4: Перейдите на вкладку «Данные» панели инструментов.

Шаг 5: Выберите анализ «Что, если».

Шаг 6: Выберите таблицу данных.

Шаг 7: Появится диалоговое окно, в котором вам нужно выбрать ячейку, в которой вы хотите изменить значение как в строке, так и в столбце. Значение ячейки для ввода строки равно $ D $ 5 , а значение ячейки для ввода столбца равно $ D $ 6.

Шаг 8: Нажмите ok и посмотрите результат.

Автор А. Днепров

Обучаю основам работы с офисными программами.

Анализ «Что, если» с помощью таблиц данных в Excel
Функция VSTACK в Excel