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

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

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

С Goal Seek и надстройкой Solver это возможно. И мы покажем вам, как это сделать. Читайте полное руководство о том, как найти одну ячейку с помощью Goal Seek или более сложное уравнение с помощью Solver.

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

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

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

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

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

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

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

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

как сделать загрузочный компакт-диск

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

Что делает решатель Excel?

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

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

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

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

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

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

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

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

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

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

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

Если вы уже использовали Пакет инструментов для анализа данных , вы увидите кнопку Анализ данных. В противном случае Solver появится сам по себе.

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

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

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

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

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

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

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

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

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

<= (меньше или равно)
знак равно (равно)
=> (больше или равно)
int (должно быть целым числом)
являюсь (должно быть либо 1, либо 0)
Все разные

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Решатель нашел решение! Как вы можете видеть слева в окне выше, наши доходы увеличились на 130 долларов. И все ограничения были соблюдены.

как сделать фотоальбомы приватными на фейсбуке

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

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

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

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

Дополнительные параметры решателя

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

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

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

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

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

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

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

Goal Seek может помочь вам сэкономить время, выполняя некоторые вычисления быстрее, а Solver добавляет огромную мощность к Расчетные способности Excel .

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

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

Делиться Делиться Твитнуть Эл. адрес 5 советов, как зарядить ваши машины VirtualBox Linux

Устали от низкой производительности виртуальных машин? Вот что вам нужно сделать, чтобы повысить производительность VirtualBox.

Читать далее Похожие темы

Продуктивность
Таблица
Майкрософт Эксель
Советы по Microsoft Office

Об авторе Затем Олбрайт(Опубликовано 506 статей)

Данн - консультант по контент-стратегии и маркетингу, который помогает компаниям генерировать спрос и потенциальных клиентов. Он также ведет блог о стратегии и контент-маркетинге на dannalbright.com.

Ещё от Dann Albright

Подписывайтесь на нашу новостную рассылку

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

Нажмите здесь, чтобы подписаться