Как использовать Excels Goal Seek и Solver для поиска неизвестных переменных

Excel очень эффективен, когда у вас есть все данные, необходимые для ваших расчетов.

Но было бы неплохо, если бы решить для неизвестных переменных?

С Goal Seek и надстройкой Solver это возможно. И мы покажем вам, как. Продолжайте читать для полного руководства о том, как решить для единственной ячейки с Поиск цели или более сложное уравнение с Решателем.

Как использовать поиск цели в Excel

Поиск цели уже встроен в Excel. Это под Данные вкладка, в Что, если анализ меню:

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

Для этого примера мы будем использовать очень простой набор чисел. У нас есть три четверти продаж и годовая цель. Мы можем использовать поиск цели, чтобы выяснить, какие цифры должны быть в 4 квартале, чтобы достичь цели.

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

Как видите, текущий общий объем продаж составляет 114 706 единиц. Если мы хотим продать 250 000 к концу года, сколько нам нужно продать в 4 квартале? Excel Goal Seek расскажет нам.

Вот, как использовать Goal Seek, шаг за шагом:

  1. Нажмите Данные> Что-если анализ> Поиск цели. Вы увидите это окно:
    Excel цель искать решить для неизвестных переменных
  2. Поместите «равную» часть вашего уравнения в Установить ячейку поле. Это число, которое Excel попытается оптимизировать. В нашем случае это общее количество продаж в ячейке B5.
  3. Введите значение вашей цели в Ценить, оценивать поле. Мы ищем в общей сложности 250 000 проданных единиц, поэтому мы разместим «250 000» в этой области.
  4. Скажите Excel, какую переменную нужно найти в Изменяя клетку поле. Мы хотим увидеть, какими должны быть наши продажи в 4 квартале. Поэтому мы скажем Excel решить для ячейки D2. Когда все будет готово, это будет выглядеть так:
    Excel цель искать решить для неизвестных переменных
  5. Удар Хорошо решить для вашей цели. Когда все выглядит хорошо, просто нажмите Хорошо. Excel сообщит вам, когда Goal Seek найдет решение.
    Excel цель искать решить для неизвестных переменных
  6. Нажмите Хорошо снова, и вы увидите значение, которое решает ваше уравнение в ячейке, которую вы выбрали для Изменяя клетку.
    Excel цель искать решить для неизвестных переменных

В нашем случае решение составляет 135 294 единицы. Конечно, мы могли бы просто найти это, вычтя промежуточную сумму из годовой цели. Но поиск цели также может быть использован в ячейке, уже есть данные в нем. И это более полезно.

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

Итак, Goal Seek — полезная функция Excel

, но это не так уж и впечатляет. Давайте взглянем на инструмент, который гораздо интереснее: надстройка Solver.

Что делает Excel’s Solver?

Короче Солвер это как многомерная версия Goal Seek. Он принимает одну переменную цели и корректирует ряд других переменных, пока не получит желаемый ответ.

Он может определить максимальное значение числа, минимальное значение числа или точное число.

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

Это отличный способ найти несколько неизвестных переменных в Excel. Но найти и использовать это не просто.

Давайте взглянем на загрузку надстройки Solver, а затем рассмотрим, как использовать Solver в Excel 2016.

Как загрузить надстройку Solver

В Excel по умолчанию нет Солвера. Это надстройка, так что, как и другие мощные функции Excel

, вы должны загрузить его первым. К счастью, он уже на вашем компьютере.

Голова к Файл> Параметры> Надстройки. Затем нажмите на Идти рядом с Управление: надстройки Excel.

Если в этом раскрывающемся списке указано что-то отличное от «Надстройки Excel», вам нужно изменить его:

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

В появившемся окне вы увидите несколько вариантов. Убедитесь, что поле рядом с Solver Add-In проверен и ударил Хорошо.

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

Теперь вы увидите решающее устройство кнопка в Анализ группа из Данные вкладка:

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

Если вы уже использовали пакет анализа данных

, вы увидите кнопку Анализ данных. Если нет, Солвер появится сам.

Теперь, когда вы загрузили надстройку, давайте посмотрим, как ее использовать.

Как использовать Солвер в Excel

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

  1. Нажмите Данные> Солвер. Вы увидите окно Solver Parameters ниже. (Если вы не видите кнопку Solver, см. Предыдущий раздел о том, как загрузить надстройку Solver.)
    Excel цель искать решить для неизвестных переменных
  2. Установите цель своей ячейки и сообщите Excel свою цель. Цель находится в верхней части окна Солвера и состоит из двух частей: ячейка цели и выбор максимизации, минимизации или определенного значения.
    Excel цель искать решить для неизвестных переменных
    Если вы выберете Максимум, Excel скорректирует ваши переменные, чтобы получить максимально возможное число в вашей целевой ячейке. Min наоборот: Солвер минимизирует количество целей. Ценность позволяет указать конкретный номер для поиска Солвера.
  3. Выберите ячейки переменных, которые Excel может изменить. Ячейки переменной устанавливаются с помощью Изменяя переменные ячейки поле. Нажмите стрелку рядом с полем, затем нажмите и перетащите, чтобы выбрать ячейки, с которыми Солвер должен работать. Обратите внимание, что это все клетки это может варьироваться. Если вы не хотите, чтобы ячейка менялась, не выбирайте ее.
    Excel цель искать решить для неизвестных переменных
  4. Установите ограничения для нескольких или отдельных переменных. Наконец, мы подошли к ограничениям. Здесь Солвер действительно силен. Вместо того, чтобы менять любую ячейку переменной на любое желаемое число, вы можете указать ограничения, которые должны быть выполнены. Подробнее см. Раздел о том, как установить ограничения ниже.
  5. Как только вся эта информация будет на месте, нажмите Решать чтобы получить ответ. Excel обновит ваши данные, чтобы включить новые переменные (поэтому мы рекомендуем сначала создать копию ваших данных).

Вы также можете создавать отчеты, которые мы кратко рассмотрим в нашем примере Solver ниже.

Как установить ограничения в Солвере

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

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

Чтобы добавить ограничение, нажмите добавлять Кнопка рядом со списком ограничений. Вы получите новое окно. Выберите ячейку (или ячейки) для ограничения в Ссылка на ячейку поле, затем выберите оператора.

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

Вот доступные операторы:

  • <= (меньше или равно)
  • знак равно (равно)
  • => (больше или равно)
  • ИНТ (должно быть целым числом)
  • бункер (должно быть 1 или 0)
  • AllDifferent

AllDifferent немного сбивает с толку. Он указывает, что каждая ячейка в диапазоне, который вы выбираете для Ссылка на ячейку должен быть другой номер. Но это также указывает, что они должны быть между 1 и количеством ячеек. Поэтому, если у вас есть три ячейки, вы получите цифры 1, 2 и 3 (но не обязательно в этом порядке)

Наконец, добавьте значение для ограничения.

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

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

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

Пример решателя

Чтобы увидеть, как все это работает, мы будем использовать надстройку Solver для быстрого расчета. Вот данные, с которых мы начинаем:

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

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

Вот ограничения, которые мы будем использовать:

  • Нет работы может упасть ниже четырех часов.
  • Работа 2 должна быть больше восьми часов.
  • Работа 5 должна быть менее чем за одиннадцать часов.
  • Общее количество отработанных часов должно быть равно 40.

Это может быть полезно, чтобы написать ваши ограничения, как это, прежде чем использовать Solver.

Вот как мы настроили это в Solver:

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

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

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

Давайте хит Решать и посмотрим, что получится.

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

Солвер нашел решение! Как видно из приведенного выше окна, наши доходы увеличились на 130 долларов США. И все ограничения были выполнены.

Чтобы сохранить новые значения, убедитесь, что Keep Solver Solution проверен и ударил Хорошо.

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

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

В нашем случае отчеты не очень захватывающие, и там не так много интересной информации. Но если вы запустите более сложное уравнение Солвера, вы можете найти некоторую полезную отчетную информацию в этих новых рабочих листах. Просто нажмите на + Кнопка на стороне любого отчета, чтобы получить больше информации:

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

Solver Advanced Options

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

Наиболее очевидным является метод решения:

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

Вы можете выбрать между GRG Nonlinear, Simplex LP и Evolutionary. Excel предоставляет простое объяснение того, когда вы должны использовать каждый из них. Лучшее объяснение требует некоторых знаний статистики

и регресс.

Чтобы настроить дополнительные параметры, просто нажмите Опции кнопка. Вы можете сообщить Excel о целочисленной оптимальности, установить временные ограничения вычислений (полезно для массивных наборов данных) и настроить методы расчета GRG и Evolutionary при выполнении своих вычислений.

Опять же, если вы не знаете, что это означает, не беспокойтесь об этом. Если вы хотите узнать больше о том, какой метод решения использовать, в Engineer Excel есть хорошая статья, которая излагает его для вас. Если вам нужна максимальная точность, возможно, Evolutionary — хороший путь. Просто знайте, что это займет много времени.

Поиск цели и решатель: вывод Excel на новый уровень

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

Goal Seek может помочь вам сэкономить время, ускорив некоторые вычисления, а Solver значительно расширит возможности вычислений в Excel

,

Это просто вопрос освоения с ними. Чем больше вы их используете, тем полезнее они станут.

Используете ли вы Goal Seek или Solver в своих таблицах? Какие еще советы вы можете дать, чтобы получить лучшие ответы из них? Поделитесь своими мыслями в комментариях ниже!

Ссылка на основную публикацию
Adblock
detector