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

Профессионалы многих отраслей используют Microsoft Excel для выполнения больших расчетов. Один из способов сделать это – создать таблицы данных с двумя переменными, чтобы сравнить переменные между собой и то, как они влияют на результаты проектов. Знание того, как создать таблицу данных с двумя переменными в Excel, поможет вам повысить свою грамотность в Excel и поможет быстрее выполнять работу.

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

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

Таблица данных с двумя переменными – это способ отображения нескольких переменных с помощью осей x и y вашего графика. Вы используете этот тип таблицы в Excel, когда хотите сравнить вариации между двумя переменными, чтобы найти наилучший исход события. Например, вы можете посмотреть на различные автомобили в автосалоне и увидеть, что у них разные суммы кредита и количество ежемесячных платежей. Чтобы найти комбинацию, которая лучше всего подходит для ваших нужд, вы можете создать таблицу данных с двумя переменными и сравнить результаты каждой комбинации значений переменных.

Когда вы планируете создать таблицу данных с двумя переменными, вы можете посчитать полезным найти значение, которое представляет обе переменные. Например, у вас может быть информация об общей стоимости кредита, общем количестве ежемесячных платежей, которые вы сделаете до погашения кредита, процентной ставке и сумме ежемесячного платежа. Размер ежемесячного платежа важен, поскольку он определяет как общую сумму кредита, так и количество платежей, которые вы планируете сделать. Это значение зависит от обеих переменных и может помочь вам создать таблицу данных с двумя переменными.

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

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

1. Соберите информацию о переменных, которые вы хотите протестировать

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

Как только вы соберете эту информацию, внесите ее в ячейки с A1 по A5 и с B1 по B5, как показано в примере ниже. Обратите внимание на формулу в ячейке B5, поскольку она является неотъемлемой частью вашей таблицы с двумя переменными данными, а также на то, что B3 отрицательный, чтобы показать, что счет начисляет проценты, а не теряет стоимость. Вот пример таблицы:

2. Поместите тестовые значения для первой переменной в столбец вашей электронной таблицы Excel

После того, как у вас есть формула в ячейке B5, вы можете перейти к другим этапам этого процесса. Начните с перечисления тестовых значений в столбце C, начиная с ячейки C2, поскольку ячейка C1 понадобится вам для последующих шагов. Для примера с процентами вы можете проверить переменную, например, сумму первоначальных инвестиций, чтобы увидеть, как она повлияет на ваши данные. Таким образом, вы можете перечислить значения от $1 000 до $10 000 с шагом в $500. Это может помочь вам показать, есть ли убывающая отдача от ваших первоначальных инвестиций, когда вы сравниваете эту первую переменную со второй переменной.

3. Поместите тестовые значения для второй переменной в строку таблицы Excel

Аналогично размещению тестовых значений для первой переменной, размещение тестовых переменных для второй формулы дает вам множество сравнений, которые вы можете сделать с помощью программы Excel. Для примера с процентами вы можете разместить значения по всей строке 1, начиная со столбца D, которые представляют количество лет, в течение которых на ваш счет начисляются проценты. Например, вы можете протестировать значения, начиная с одного года процентов и до 10 лет процентов. Важно оставить ячейку C1 пустой для последующих шагов процесса, потому что эта ячейка поможет вам заполнить остальную часть таблицы значениями.

4. Используйте формулу переменной с двойной зависимостью

Теперь, когда вы создали два набора переменных, осталось сделать последний шаг перед созданием таблицы. Перейдите в ячейку C1, которая пуста, и введите =B5 и нажмите кнопку Введите на клавиатуре. Когда вы это делаете, вы позволяете себе получить доступ к этому значению и остальным данным с помощью функции Excel щелкни и перетащи . Это важно, так как поможет вам создать таблицу, в которой вы уже разместили данные.

Примечательно, что вы хотите, чтобы ячейка C1 была равна B5, а не значению в B5, потому что Excel применяет формулу к созданным вами переменным, а не заменяет их точным значением ячейки B5. Это формула, которая создает вычисления для вашей таблицы с двумя переменными данными без необходимости выполнять несколько утомительных вычислений самостоятельно или вводить слишком много формул в каждую ячейку.

5. Создайте таблицу данных

Последним шагом для создания фактической таблицы данных с двумя переменными является создание самой таблицы. Сначала выделите весь диапазон ячеек, включая ячейку C1, и дотянитесь до последней ячейки, где обе переменные совпадают. Для этого щелкните в ячейке C1 и перетащите мышь в правую нижнюю часть ваших данных. Затем выполните следующие шаги, чтобы создать таблицу с использованием ваших переменных данных:

1. Войдите в Данные вкладка на ленте в верхней части окна Excel.

2. Нажмите кнопку Анализ что-если кнопку из инструментов в разделе Данные вкладка.

3. Выберите Таблица данных… из опций.

4. Введите значение в ячейку B4 в Ячейка ввода строк вариант, используя формат $B$4.

5. Введите значение в ячейку B1 в Ячейка ввода столбца с использованием формата $B$1.

6. Выберите Ok чтобы позволить Excel создать ваши двухпеременные данные.

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

Обратите внимание, что ни одна из компаний или продуктов, упомянутых в этой статье, не связана с Indeed.

Таблица данных в Excel представляет собой диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это Анализ “что если”, о котором мы говорили в одной из прошлых статей (если Вы ее не читали – очень рекомендую ознакомиться по этой ссылке), в удобном виде. Вы можете создать таблицу данных с одной или двумя переменными.

Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене – $50 и определенный % книг по более низкой цене – $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа “что если” в Excel.

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

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

1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).

2. Введите различные проценты в столбце А.

3. Выберите диапазон A12:B17.

Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.

4. На вкладке Данные, кликните на Анализ “что если” и выберите Таблица данных из списка.

5. Кликните в поле “Подставлять значения по строкам в: “и выберите ячейку C4.

Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.

Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода (“Подставлять значения по столбцам в: “) мы оставляем пустой.

6. Нажмите ОК.

Результат.

Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.

Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.

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

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

1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).

2. Внесите различные варианты высокой цены в строку 12.

3. Введите различные проценты в столбце А.

4. Выберите диапазон A12:D17.

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

5. На вкладке Данные, кликните на Анализ “что если” и выберите Таблица данных из списка.

6. Кликните в поле “Подставлять значения по столбцам в: ” и выберите ячейку D7.

7. Кликните в поле “Подставлять значения по строкам в: ” и выберите ячейку C4.

Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.

8. Нажмите ОК.

Результат.

Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.

Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.

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

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

Таблица данных в Excel — это диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это анализ «Что, если», о котором мы говорили в одной из предыдущих статей (если вы его еще не читали, настоятельно рекомендую ознакомиться с этой ссылкой) в удобной форме. Вы можете создать таблицу параметров с одной или двумя переменными.

Допустим, у вас есть книжный магазин, и есть 100 книг на продажу. Вы можете продать определенный процент книг по высокой цене — 50 долларов и определенный процент книг по более низкой цене — 20 долларов. Если вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общий доход от форум 60 * 50 + 40 * 20 долларов = 3800 долларов.

Вы можете скачать рассмотренный пример по этой ссылке: Пример анализа «что, если» в Excel.
 

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

Чтобы создать таблицу параметров с переменной, выполните следующие действия:

1. Выберите ячейку B12 и введите = D10 (ссылка на общий доход).

2. Введите другой процент в столбце A.

3. Выберите диапазон A12: B17.

Мы рассчитаем общий доход, если вы продадите 60% книг по высокой цене, 70% книг по высокой цене и т.д.

4. На вкладке «Данные» щелкните «Анализ« Что, если »» и выберите в списке «Таблица данных.

5. Щелкните поле «Заменить значения строки в:» и выберите ячейку C4.

   
Мы выбрали ячейку C4, потому что проценты относятся к этой ячейке (% книг, проданных по самой высокой цене). Помимо формулы в ячейке B12, Excel теперь знает, что для расчета общего дохода необходимо заменить значение в ячейке C4 на 60%, 70% и т.д.

Примечание. Поскольку мы создаем таблицу параметров с одной переменной, мы оставляем вторую ячейку ввода пустой («Заменить значения столбцами в.

6. Щелкните ОК.

Результат.

   
Итог: если вы продадите 60% книг по высокой цене, вы получите общий доход в размере 3800 долларов, если вы продадите 70% книг по высокой цене, вы получите 4100 долларов и так далее.

Примечание. Строка формул указывает на то, что ячейки содержат формулу массива. Следовательно, удалить результат невозможно. Чтобы удалить результаты, выберите диапазон B13: B17 и нажмите «Удалить.
 

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

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

1. Выберите ячейку A12 и введите = D10 (ссылка на общий доход).

2. Введите различные варианты максимальной цены в строке 12.

3. Введите другой процент в столбце A.

4. Выберите диапазон A12: D17.

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

5. На вкладке «Данные» щелкните «Анализ« что, если »» и выберите в списке «Таблица данных.

6. Щелкните поле «Заменить значения столбца в:» и выберите ячейку D7.

7. Щелкните поле «Заменить значения строки в:» и выберите ячейку C4.

Мы выбрали ячейку D7, потому что в этой ячейке указана самая высокая цена на книги. Мы выбрали ячейку C4, потому что в ней указан процент продаж с максимальной ценой. Наряду с формулой в ячейке A12 Excel теперь знает, что он должен заменить значение в ячейке D7, начиная с 50 долларов, и в ячейке C4, начиная с 60%, чтобы рассчитать общий доход, до 70 и 100 долларов соответственно.

8. Щелкните ОК.

Результат.

Итог: если вы продадите 60% книг по высокой цене в 50 долларов, вы получите общий доход в размере 3800 долларов, если вы продадите 80% книг по высокой цене в 60 долларов, вы получите 5200 долларов, и так далее.

Примечание. Строка формул указывает, что ячейки содержат формулу массива. Следовательно, невозможно удалить один результат. Чтобы удалить результаты, выберите диапазон B13: D17 и нажмите «Удалить.

Спасибо за внимание. Теперь вы можете более эффективно применять один из видов анализа «что если» — формирование таблиц данных с одной или двумя переменными.