Производительность

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

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

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

Откройте БЕСПЛАТНУЮ шпаргалку «Essential Excel Formulas» прямо сейчас!

Это подпишет вас на нашу рассылку

Введите адрес электронной почты

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

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

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

Поиск цели уже встроен в Excel. Он находится на вкладке « Данные » в меню « Что, если анализ» :

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • <= (меньше или равно)
  • = (равно)
  • => (больше или равно)
  • int (должно быть целым числом)
  • bin (должно быть 1 или 0)
  • AllDifferent

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Давайте нажмем « Решить» и посмотрим, что произойдет.

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

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

Чтобы сохранить новые значения, убедитесь, что установлен флажок Keep Solver Solution и нажмите OK .

Если вам нужна дополнительная информация, вы можете выбрать отчет в правой части окна. Выберите все отчеты, которые вы хотите, скажите 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 в своих таблицах? Какие еще советы вы можете дать, чтобы получить из них лучшие ответы? Поделитесь своими мыслями в комментариях ниже!

Похожие посты
Производительность

Excel против Google Sheets: какой из них лучше для вас?

Производительность

Как улучшить управление документами с помощью свойств в Microsoft Office

ИнтернетПроизводительность

5 инструментов для извлечения изображений из файлов PDF

AndroidПроизводительность

Adobe Reader X добавляет защищенный режим для пользователей Windows, Android получает новые функции [Новости]