Как таблица данных в Microsoft Excel позволяет сравнивать результаты

Дядя Влад

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

Анализ «что если» в Excel: таблица данных

Инструменты анализа «что, если» в Excel представляют собой отличный набор функций, которые позволяют прогнозировать изменение выходных данных относительно изменения значений. В Excel есть три инструмента анализа «что, если»: «Диспетчер сценариев», «Поиск цели» и «Таблица данных».

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

Как работает таблица данных

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

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

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

Связанный: Как создать сводную таблицу в Excel

Пример 1: Таблица данных с двумя переменными

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

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

Итак, в целом для этой таблицы данных у вас будет два входных параметра: количество и цена. Сначала создадим формулу:

  1. В ячейках A1 , B1 , C1 и D1 введите цену , количество , налог и доход соответственно.
  2. Выделите ячейки A1 и D1 .
  3. На вкладке « Главная » в разделе « Числа » щелкните символ $, чтобы изменить числовое форматирование этих ячеек на « Учет» (это связано с тем, что в этих ячейках будут храниться бухгалтерские значения).
  4. Выберите ячейку C2 .
  5. На вкладке « Главная » в разделе « Числа » щелкните символ %, чтобы изменить числовое форматирование этой ячейки на « Процент» .
  6. Выберите ячейку D2 в разделе «Доход», введите следующую формулу в строке формул и нажмите Enter:
     =(A2*B2)-(A2*B2*C2)

    Эта формула умножит цену проданных единиц (A2) на их количество (B2), а затем вычтет из нее налоговую стоимость (A2 * B2 * C2).

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

Связанный: Как скрыть или показать столбцы и строки в Excel

Таблица различных входных данных Excel

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

  1. Выберите ячейку G2 и введите формулу ниже в строке формул:
     =D2

    В результате в ячейке G2 будет установлена ​​формула, созданная вами ранее.

  2. В ячейках под G2 (столбец G) введите возможные количества проданных штук. В этом примере это 5, 10, 15, 20, 25 и 30.
  3. В ячейках рядом с G2 (строка 2) введите цены на каждую штуку. В этом примере цены 10, 20, 30, 40, 50 и 60.
  4. Выберите ячейки, в которые вы вставили цены, и ячейки ниже, в которых будет отображаться возможная выручка, и измените их числовое форматирование на « Учет» .

Наконец, теперь, когда у вас есть набор строк и столбцов, пора превратить эту таблицу в таблицу данных.

  1. Выберите таблицу, щелкнув G1 и перетащив ее до M7 .
  2. Перейдите на вкладку «Данные» и в разделе «Прогноз» щелкните « Анализ« что, если »» . Появится список из трех пунктов.
  3. Из списка выберите Таблица данных . Откроется диалоговое окно "Таблица данных".
  4. В ячейке ввода Row введите A2 . Строка таблицы содержит цены, а ввод цены в исходной формуле – ячейка A2.
  5. В ячейке ввода столбца введите B2 . В столбце таблицы указано количество проданных штук.
  6. После того, как вы установили два входа, нажмите OK . Excel теперь сгенерирует таблицу данных.

Теперь у вас есть таблица данных для вашей формулы! Таблица данных предоставляет вам ценную информацию о ваших потенциальных продажах.

Например, теперь вы можете увидеть, как вы можете получить доход в 1000 долларов или больше, посмотрев на шесть ячеек в правом нижнем углу. Или вы можете сравнить и знать, что продажа 25 игрушечных машинок по 20 долларов принесет вам больший доход, чем продажа 15 игрушечных машинок по 30 долларов.

Пример 2: Таблица данных с одной переменной

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

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

Метод тот же, что и для таблицы данных с двумя переменными, хотя расположение немного отличается.

Связанный: Как копировать формулы в Microsoft Excel

Чтобы приступить к созданию таблицы данных, вам необходимо создать формулу. Формула для этого примера такая же, как и в предыдущем. Когда у вас будет готовая формула, самое время изложить данные.

  1. Выберите ячейку H1 и в строке формул введите формулу ниже и нажмите Enter :
     =D2
  2. Введите числа в ячейки G2 и ниже. В этом примере введите 5, 10, 15, 20, 25 и 30 в ячейки с G2 по G7 .

Пришло время создать таблицу данных.

  1. Выберите таблицу, щелкнув G1 и перетащив ее на H7 .
  2. Перейдите на вкладку «Данные» и в разделе «Прогноз» щелкните « Анализ« что, если »» .
  3. В списке «Анализ« что, если »» выберите « Таблица данных» .
  4. В диалоговом окне Таблица данных щелкните ячейку ввода столбца и введите B2 .
  5. Оставьте ячейку ввода Row пустой.
  6. Поскольку цель состоит в том, чтобы получить таблицу данных о доходах по фиксированной цене, вам нужно указать в таблице данных только количество проданных игрушечных машинок, а не их цены. Количество указывается в строке таблицы данных, а вводом для него в формуле является ячейка B2.
  7. Щелкните ОК . Excel создаст таблицу данных с одной переменной.

Итак, теперь вы можете выяснить, какой доход вы можете получить, продавая игрушечные машинки за 50 долларов, конечно, за вычетом налогов.

Изложите возможности в таблице

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

Таблица данных – это только один из инструментов анализа «что, если» в Excel. Если вы хотите ответить на все вопросы о том, что, если с помощью Excel, вам нужно узнать больше.