Excel очень эффективен, когда у вас есть все данные, необходимые для ваших расчетов.
Но разве не было бы хорошо, если бы это могло решить для неизвестных переменных ?
С Goal Seek и надстройкой Solver это возможно. И мы покажем вам, как. Продолжайте читать для полного руководства о том, как решить для единственной ячейки с Поиск цели или более сложное уравнение с Решателем.
Как использовать поиск цели в Excel
Поиск цели уже встроен в Excel. Он находится на вкладке « Данные » в меню « Что, если анализ» :
Для этого примера мы будем использовать очень простой набор чисел. У нас есть три четверти продаж и годовая цель. Мы можем использовать поиск цели, чтобы выяснить, какие цифры должны быть в 4 квартале, чтобы достичь цели.
Как видите, текущий общий объем продаж составляет 114 706 единиц. Если мы хотим продать 250 000 к концу года, сколько нам нужно продать в 4 квартале? Поиск цели в Excel расскажет нам.
Вот как использовать поиск цели, шаг за шагом:
- Нажмите Данные> Анализ «Что если»> «Поиск цели» . Вы увидите это окно:
- Поместите часть равенства в уравнение в поле « Задать ячейку» . Это число, которое Excel попытается оптимизировать. В нашем случае это промежуточный итог наших продаж в ячейке B5.
- Введите значение цели в поле « Значение» . Мы ищем в общей сложности 250 000 проданных единиц, поэтому мы разместим «250 000» в этой области.
- Скажите Excel, какую переменную нужно найти в поле « Изменение ячейки» . Мы хотим увидеть, какими должны быть наши продажи в 4 квартале. Поэтому мы скажем Excel, чтобы решить для ячейки D2. Когда все будет готово, это будет выглядеть так:
- Нажмите OK, чтобы решить для вашей цели. Когда все выглядит хорошо, просто нажмите ОК . Excel сообщит вам, когда Goal Seek найдет решение.
- Нажмите кнопку ОК еще раз, и вы увидите значение, которое решает ваше уравнение в ячейке, которую вы выбрали для изменения ячейки .
В нашем случае решение составляет 135 294 единицы. Конечно, мы могли бы просто найти это, вычтя промежуточную сумму из годовой цели. Но поиск цели также может использоваться в ячейке, в которой уже есть данные . И это более полезно.
Обратите внимание, что Excel перезаписывает наши предыдущие данные. Хорошей идеей будет запустить поиск цели на копии ваших данных . Также полезно записать скопированные данные, которые были созданы с помощью Goal Seek. Вы не хотите путать это с текущими, точными данными.
Итак, Goal Seek — полезная функция Excel. , но это не так впечатляет. Давайте посмотрим на инструмент, который гораздо интереснее: надстройка Solver.
Что делает Excel’s Solver?
Короче говоря, Солвер похож на многовариантную версию поиска цели . Он принимает одну переменную цели и корректирует ряд других переменных, пока не получит желаемый ответ.
Он может определить максимальное значение числа, минимальное значение числа или точное число.
И это работает в рамках ограничений, поэтому, если одна переменная не может быть изменена или может изменяться только в пределах указанного диапазона, Солвер примет это во внимание.
Это отличный способ найти несколько неизвестных переменных в Excel. Но найти и использовать это не просто.
Давайте взглянем на загрузку надстройки Solver, а затем рассмотрим, как использовать Solver в Excel 2016.
Как загрузить надстройку Solver
В Excel нет Солвера по умолчанию. Это надстройка, поэтому, как и другие мощные функции Excel, , вы должны загрузить его в первую очередь. К счастью, это уже на вашем компьютере.
Перейдите в Файл> Параметры> Надстройки . Затем нажмите « Перейти» рядом с « Управление: надстройки Excel» .
Если в этом раскрывающемся списке указано что-то отличное от «Надстройки Excel», вам необходимо изменить его:
В появившемся окне вы увидите несколько вариантов. Убедитесь, что установлен флажок рядом с надстройкой Solver , и нажмите ОК .
Теперь вы увидите кнопку Солвер в группе Анализ на вкладке Данные :
Если вы уже использовали пакет инструментов анализа данных , вы увидите кнопку анализа данных. Если нет, Солвер появится сам.
Теперь, когда вы загрузили надстройку, давайте посмотрим, как ее использовать.
Как использовать Солвер в Excel
У любого действия Солвера есть три части: цель, переменные ячейки и ограничения. Мы пройдем через каждый из шагов.
- Нажмите Данные> Решатель . Вы увидите окно Solver Parameters ниже. (Если вы не видите кнопку Solver, см. Предыдущий раздел о том, как загрузить надстройку Solver.)
- Установите цель своей ячейки и сообщите Excel свою цель. Цель находится в верхней части окна Солвера и состоит из двух частей: ячейка цели и выбор максимизации, минимизации или определенного значения.
Если вы выберете Max , Excel отрегулирует ваши переменные, чтобы получить максимально возможное число в вашей целевой ячейке. Мин — наоборот: Солвер минимизирует количество целей. Значение Of позволяет вам указать конкретное число для поиска Солвера. - Выберите ячейки переменных, которые Excel может изменить. Ячейки переменных задаются с помощью поля « Изменение ячеек переменной» . Нажмите стрелку рядом с полем, затем нажмите и перетащите, чтобы выбрать ячейки, с которыми Солвер должен работать. Обратите внимание, что это все клетки, которые могут варьироваться. Если вы не хотите менять ячейку, не выбирайте ее.
- Установите ограничения для нескольких или отдельных переменных. Наконец, мы подошли к ограничениям. Это где Solver действительно мощный. Вместо того, чтобы менять любую ячейку переменной на любое желаемое число, вы можете указать ограничения, которые должны быть выполнены. Подробнее см. Раздел о том, как установить ограничения ниже.
- Как только вся эта информация собрана, нажмите « Решить», чтобы получить ответ. Excel обновит ваши данные, чтобы включить новые переменные (поэтому мы рекомендуем сначала создать копию ваших данных).
Вы также можете создавать отчеты, которые мы кратко рассмотрим в нашем примере Solver ниже.
Как установить ограничения в Солвере
Вы могли бы сказать Excel, что одна переменная должна быть больше 200. При попытке использовать другие значения переменной Excel не будет идти ниже 201 с этой конкретной переменной.
Чтобы добавить ограничение, нажмите кнопку « Добавить» рядом со списком ограничений. Вы получите новое окно. Выберите ячейку (или ячейки) для ограничения в поле « Ссылка на ячейку» , затем выберите оператора.
Вот доступные операторы:
- <= (меньше или равно)
- = (равно)
- => (больше или равно)
- int (должно быть целым числом)
- bin (должно быть 1 или 0)
- AllDifferent
AllDifferent немного сбивает с толку. Он указывает, что каждая ячейка в диапазоне, который вы выбираете для ссылки на ячейку, должна иметь разные номера. Но это также указывает, что они должны быть между 1 и количеством ячеек. Таким образом, если у вас есть три ячейки, вы получите цифры 1, 2 и 3 (но не обязательно в этом порядке)
Наконец, добавьте значение для ограничения.
Важно помнить, что вы можете выбрать несколько ячеек для ссылки на ячейку. Например, если вы хотите, чтобы шесть переменных имели значения больше 10, вы можете выбрать их все и сообщить Solver, что они должны быть больше или равны 11. Вам не нужно добавлять ограничение для каждой ячейки.
Вы также можете использовать флажок в главном окне Солвера, чтобы убедиться, что все значения, для которых вы не указали ограничения, являются неотрицательными. Если вы хотите, чтобы ваши переменные становились отрицательными, снимите этот флажок.
Пример решателя
Чтобы увидеть, как все это работает, мы будем использовать надстройку Solver для быстрого расчета. Вот данные, с которых мы начинаем:
В нем у нас есть пять разных работ, каждая из которых платит разные ставки. У нас также есть количество часов, которое теоретический работник проработал на каждой из этих работ в течение данной недели. Мы можем использовать надстройку Solver, чтобы узнать, как максимизировать общую заработную плату, сохраняя определенные переменные в рамках некоторых ограничений.
Вот ограничения, которые мы будем использовать:
- Никакая работа не может упасть ниже четырех часов.
- Задание 2 должно быть больше восьми часов .
- Работа 5 должна быть менее одиннадцати часов .
- Общее количество отработанных часов должно быть равно 40 .
Это может быть полезно, чтобы написать ваши ограничения, как это, прежде чем использовать Solver.
Вот как мы настроили это в Солвере:
Во-первых, обратите внимание, что я создал копию таблицы, поэтому мы не перезаписываем исходную, которая содержит наше текущее рабочее время.
И во-вторых, обратите внимание, что значения в ограничениях «больше» и «меньше» на одно выше или ниже того, что я упоминал выше. Это потому, что нет вариантов больше или меньше. Есть только больше или равно или меньше или равно.
Давайте нажмем « Решить» и посмотрим, что произойдет.
Солвер нашел решение! Как видно из приведенного выше окна, наши доходы увеличились на 130 долларов. И все ограничения были выполнены.
Чтобы сохранить новые значения, убедитесь, что установлен флажок Keep Solver Solution и нажмите OK .
Если вам нужна дополнительная информация, вы можете выбрать отчет в правой части окна. Выберите все отчеты, которые вы хотите, скажите Excel, хотите ли вы, чтобы они были изложены (я рекомендую это), и нажмите ОК .
Отчеты создаются на новых листах в вашей рабочей тетради и дают вам информацию о процессе, который надстройка Solver прошла, чтобы получить ваш ответ.
В нашем случае отчеты не очень захватывающие, и там не так много интересной информации. Но если вы запустите более сложное уравнение Солвера, вы можете найти некоторую полезную отчетную информацию в этих новых рабочих листах. Просто нажмите кнопку + на стороне любого отчета, чтобы получить больше информации:
Solver Advanced Options
Если вы не очень разбираетесь в статистике, вы можете игнорировать расширенные опции Солвера и просто запустить ее как есть. Но если вы выполняете большие и сложные вычисления, вы можете посмотреть на них.
Наиболее очевидным является метод решения:
Вы можете выбрать между GRG Nonlinear, Simplex LP и Evolutionary. Excel предоставляет простое объяснение того, когда вы должны использовать каждый из них. Для лучшего объяснения требуются некоторые знания статистики. и регресс.
Чтобы настроить дополнительные параметры, просто нажмите кнопку « Параметры» . Вы можете сообщить Excel о целочисленной оптимальности, установить временные ограничения вычислений (полезно для массивных наборов данных) и настроить методы расчета GRG и Evolutionary при выполнении своих вычислений.
Опять же, если вы не знаете, что это значит, не беспокойтесь об этом. Если вы хотите узнать больше о том, какой метод решения использовать, Engineer Excel предлагает вам хорошую статью . Если вам нужна максимальная точность, возможно, Evolutionary — хороший путь. Просто знайте, что это займет много времени.
Поиск цели и решатель: вывод Excel на новый уровень
Теперь, когда вы знакомы с основами решения неизвестных переменных в Excel, для вас открыт совершенно новый мир расчета электронных таблиц.
Goal Seek может помочь вам сэкономить время, ускорив некоторые вычисления, а Solver значительно расширит вычислительные возможности Excel.
Это просто вопрос освоения с ними. Чем больше вы их используете, тем полезнее они станут.
Используете ли вы Goal Seek или Solver в своих таблицах? Какие еще советы вы можете дать, чтобы получить из них лучшие ответы? Поделитесь своими мыслями в комментариях ниже!