Найти решение в Excel на Mac

Обновлено: 21.11.2024

Excel Solver – это инструмент оптимизации, который можно использовать для определения того, как можно достичь желаемого результата, изменив допущения в модели. Это тип анализа «что, если». Динамический финансовый анализ Это руководство научит вас выполнять динамический финансовый анализ в Excel с использованием расширенных формул и функций. Функции ИНДЕКС, ПОИСКПОЗ и ПОИСКПОЗ ПОИСКПОЗ, объединяющие в формуле ЯЧЕЙКУ, СЧЕТЧИК, СРЕДНЕЕ и СМЕЩЕНИЕ. При использовании эти функции Excel делают анализ вашего финансового отчета более динамичным и особенно полезны при попытке определить «наилучший» результат, учитывая набор из более чем двух предположений. Изучите видеоинструкцию в расширенном курсе CFI по Excel.

Как использовать Excel Solver — пример

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

Шаг 1. Убедитесь, что надстройка Solver установлена

Первый шаг – убедиться, что Solver установлен в вашем файле Excel. Для этого перейдите в Файл -> Параметры -> Надстройки -> Управление надстройками Excel. Когда появится диалоговое окно, убедитесь, что флажок установлен, как показано ниже.

Шаг 2. Создайте модель

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

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

Учитесь с помощью видеоинструкций в расширенном курсе CFI по Excel.

Шаг 3. Используйте ленту для запуска Excel Solver

Функция Excel Solver находится на ленте данных, а сочетание клавиш в Windows — Alt, A, Y21. Когда появится диалоговое окно, вы увидите несколько параметров, с которыми вы можете работать.

Задайте ячейку «Цель». В нашем случае это Прибыль.
Установите для него что-нибудь (макс., мин. или определенное значение). В нашем случае нам нужна максимальная прибыль.
Выберите ячейки, которые вы хотите изменить, чтобы найти решение. В нашем случае мы хотим выбрать ячейку количества продавцов.
Добавьте ограничения. Если вы хотите установить ограничение (например, ячейка должна быть > или Загрузите файл Excel, используемый в этом примере, чтобы поиграть с ним. Попробуйте также создать свой собственный с нуля!

Решатель против поиска цели

Оба инструмента чрезвычайно полезны при выполнении финансового моделирования и анализа в Excel. Основное отличие:

Goal Seek позволяет изменять только одну переменную, а Solver позволяет изменять сразу несколько переменных.
Поиск решения позволяет вам найти максимальное или минимальное решение (оптимизация), а поиск цели заставляет вас выбирать конкретный результат (не обязательно оптимальное решение).

Доступ к шпаргалке CFI по ярлыкам Excel

Используйте форму ниже, чтобы загрузить бесплатную памятку CFI по ярлыкам Excel для Mac и ПК.

Сочетания клавиш

Хотите стать мастером Excel? Повысьте свою производительность с помощью подробного руководства по сочетаниям клавиш CFI.

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по проблемам оптимизации в Excel и использованию Excel Solver. Чтобы продолжить обучение и продвинуться по карьерной лестнице в качестве финансового аналитика мирового класса, эти дополнительные ресурсы CFI будут очень полезны:

Проверка данных Проверка данных в Excel Проверка данных в Excel — это существующий и доступный инструмент для обеспечения целостности данных при построении финансовой модели. Это помогает защитить входные и выходные данные модели.
Рекомендации по моделированию в Excel Рекомендации по моделированию в Excel Следующие передовые методики по моделированию в Excel позволяют пользователю обеспечить наиболее чистое и удобное моделирование. Microsoft Excel — чрезвычайно надежный инструмент. Для тех, кто работает в сфере инвестиционно-банковских услуг, корпоративных финансов и прямых инвестиций, почти обязательно научиться опытному пользователю Excel.
Сочетания клавиш Excel Сочетания клавиш Excel Сочетания клавиш Excel позволяют значительно быстрее и эффективнее перемещаться по Excel. На самом деле, использование сочетаний клавиш намного быстрее, чем использование мыши. Два распространенных примера таких ярлыков — это те, которые позволяют.
Переменные VBA, типы данных и размерные переменные VBA, типы данных и размерные переменные VBA используются для хранения фрагментов данных для использования в макросах и функциях. Эти переменные хранятся во многих типах данных и объявляются с помощью Dim. Обычно самым первым шагом после присвоения имени вашему макросу является объявление ваших переменных. Переменные — это имена для разных частей данных, которые будут использоваться макросом.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Solver — это надстройка Microsoft Excel, которую можно использовать для анализа возможных вариантов. Используйте «Поиск решения», чтобы найти оптимальное (максимальное или минимальное) значение формулы в одной ячейке, называемой целевой ячейкой, с учетом ограничений или ограничений на значения других ячеек формулы на листе. Решатель работает с группой ячеек, называемых переменными решения или просто ячейками переменных, которые используются при вычислении формул в ячейках целей и ограничений. Решатель корректирует значения в ячейках переменных решения, чтобы удовлетворить ограничения на ячейки ограничений и получить результат, который вы хотите получить для целевой ячейки.

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

Примечание. В версиях Solver до Excel 2007 целевая ячейка называлась «целевой ячейкой», а ячейки переменных решения — «изменяющимися ячейками» или «настраиваемыми ячейками». В надстройку «Поиск решения» для Excel 2010 было внесено множество улучшений, поэтому, если вы используете Excel 2007, работа будет немного отличаться.

В следующем примере уровень рекламы в каждом квартале влияет на количество проданных единиц, косвенно определяя сумму дохода от продаж, сопутствующие расходы и прибыль. Решатель может изменять ежеквартальные бюджеты на рекламу (ячейки переменных решения B5:C5) вплоть до ограничения общего бюджета в 20 000 долларов США (ячейка F5), пока общая прибыль (целевая ячейка F7) не достигнет максимально возможной суммы. Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы F7, =СУММ (Прибыль Q1:Прибыль Q2).

<р>1. Ячейки переменных

<р>2. Ограниченная ячейка

<р>3. Целевая ячейка

После запуска Solver новые значения выглядят следующим образом.

На вкладке "Данные" в группе "Анализ" нажмите "Решатель".

Примечание. Если команда «Поиск решения» или группа «Анализ» недоступны, необходимо активировать надстройку «Поиск решения». См. статью Как активировать надстройку Solver.

В поле "Установить цель" введите ссылку на ячейку или имя целевой ячейки. Целевая ячейка должна содержать формулу.

Выполните одно из следующих действий:

Если вы хотите, чтобы значение целевой ячейки было как можно больше, нажмите Макс.

Если вы хотите, чтобы значение целевой ячейки было как можно меньше, нажмите Мин.

Если вы хотите, чтобы целевой ячейкой было определенное значение, нажмите «Значение» и введите значение в поле.

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

В поле "С учетом ограничений" введите любые ограничения, которые вы хотите применить, выполнив следующие действия:

В диалоговом окне "Параметры решателя" нажмите "Добавить".

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

Нажмите на отношение ( =, int, bin или dif ), которое вы хотите между ячейкой, на которую указывает ссылка, и ограничением. Если щелкнуть int, в поле Constraint появится целое число. Если щелкнуть bin, в поле Constraint появится двоичный файл. Если вы нажмете "dif", в поле "Ограничение" появится all different.

Если вы выберете = для отношения в поле Ограничение, введите число, ссылку или имя ячейки или формулу.

Выполните одно из следующих действий:

Чтобы принять ограничение и добавить другое, нажмите "Добавить".

Чтобы принять ограничение и вернуться в диалоговое окно "Параметры решателя", нажмите кнопку "ОК".
Примечание. Отношения int, bin и dif можно применять только в ограничениях для ячеек переменных решения.

Вы можете изменить или удалить существующее ограничение, выполнив следующие действия:

В диалоговом окне "Параметры решателя" щелкните ограничение, которое вы хотите изменить или удалить.

Нажмите «Изменить», а затем внесите изменения или нажмите «Удалить».

Нажмите "Решить" и выполните одно из следующих действий:

Чтобы сохранить значения решения на листе, в диалоговом окне "Результаты решателя" нажмите "Сохранить решение решателя".

Чтобы восстановить исходные значения до того, как вы нажали "Решить", нажмите "Восстановить исходные значения".

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

Чтобы создать отчет на основе вашего решения после того, как Solver найдет решение, вы можете выбрать тип отчета в поле «Отчеты», а затем нажать «ОК». Отчет создается на новом листе в вашей книге. Если Solver не находит решения, доступны только определенные отчеты или отчеты отсутствуют.

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

После того, как вы определите проблему, нажмите «Параметры» в диалоговом окне «Параметры решателя».

В диалоговом окне "Параметры" установите флажок "Показать результаты итерации", чтобы просмотреть значения каждого пробного решения, а затем нажмите "ОК".

В диалоговом окне "Параметры решения" нажмите "Решить".

В диалоговом окне "Показать пробное решение" выполните одно из следующих действий:

Чтобы остановить процесс решения и отобразить диалоговое окно "Результаты решения", нажмите "Стоп".

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

В диалоговом окне "Параметры решателя" нажмите "Параметры".

Выберите или введите значения для любых параметров на вкладках All Methods, GRG Nonlinear и Evolutionary в диалоговом окне.

В диалоговом окне "Параметры решателя" нажмите "Загрузить/Сохранить".

Введите диапазон ячеек для области модели и нажмите "Сохранить" или "Загрузить".

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

Совет. Вы можете сохранить последние выборки в диалоговом окне "Параметры решателя" с рабочим листом, сохранив рабочую книгу. Каждый рабочий лист в рабочей книге может иметь собственный выбор Решателя, и все они сохраняются. Вы также можете определить более одной проблемы для рабочего листа, нажав Загрузить/Сохранить, чтобы сохранить проблемы по отдельности.

Вы можете выбрать любой из следующих трех алгоритмов или методов решения в диалоговом окне "Параметры решателя":

Обобщенный редуцированный градиент (GRG) Нелинейный Используйте для задач, которые являются гладкими нелинейными.

LP Simplex Используйте для линейных задач.

Эволюционное использование для негладких задач.

Важно! Сначала необходимо включить надстройку Solver. Дополнительные сведения см. в разделе Загрузка надстройки «Поиск решения».

В следующем примере уровень рекламы в каждом квартале влияет на количество проданных единиц, косвенно определяя сумму дохода от продаж, сопутствующие расходы и прибыль. Решатель может изменять ежеквартальные бюджеты на рекламу (ячейки переменной решения B5:C5) вплоть до ограничения общего бюджета в 20 000 долларов США (ячейка D5), пока общая прибыль (целевая ячейка D7) не достигнет максимально возможной суммы. Значения в ячейках переменных используются для расчета прибыли за каждый квартал, поэтому они связаны с целевой ячейкой формулы D7, =СУММ(Прибыль Q1:Прибыль Q2).

После запуска Solver новые значения выглядят следующим образом.

В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».

В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".

В поле "Установить цель" введите ссылку на ячейку или имя целевой ячейки.

Примечание. Целевая ячейка должна содержать формулу.

Выполните одно из следующих действий:

Сделайте значение целевой ячейки как можно большим

Сделайте значение целевой ячейки как можно меньше

Установите в целевой ячейке определенное значение

Нажмите "Значение" и введите значение в поле.

В поле Путем изменения ячеек переменной введите имя или ссылку для каждого диапазона ячеек переменной решения. Разделяйте несмежные ссылки запятыми.

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

В поле Тема ограничений добавьте любые ограничения, которые вы хотите применить.

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

В диалоговом окне "Параметры решателя" нажмите "Добавить".

На знаке = в поле Ограничение введите число, ссылку или имя ячейки или формулу.

Примечание. Вы можете применять отношения int, bin и dif только в ограничениях для ячеек переменных решения.

Выполните одно из следующих действий:

Принять ограничение и добавить другое

Примите ограничение и вернитесь в диалоговое окно "Параметры решателя"

Нажмите "Решить" и выполните одно из следующих действий:

Сохранить значения решения на листе

Нажмите «Сохранить решение решателя» в диалоговом окне «Результаты решателя».

Восстановить исходные данные

Нажмите "Восстановить исходные значения".

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

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

Чтобы сохранить корректирующие значения ячеек в виде сценария, который вы сможете отобразить позже, нажмите «Сохранить сценарий» в диалоговом окне «Результаты решения», а затем введите имя сценария в поле «Имя сценария».

В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».

В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".

После определения проблемы в диалоговом окне "Параметры решателя" нажмите "Параметры".

Установите флажок "Показать результаты итерации", чтобы увидеть значения каждого пробного решения, а затем нажмите "ОК".

В диалоговом окне "Параметры решения" нажмите "Решить".

В диалоговом окне "Показать пробное решение" выполните одно из следующих действий:

Остановите процесс решения и отобразите диалоговое окно "Результаты решения"

Нажмите "Стоп".

Продолжить процесс решения и отобразить следующее пробное решение

Нажмите "Продолжить".

В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».

В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".

Нажмите «Параметры», а затем в диалоговом окне «Параметры» или «Параметры решения» выберите один или несколько следующих параметров:

Установите время решения и количество итераций

На вкладке "Все методы" в разделе "Пределы решения" в поле "Максимальное время (секунды)" введите количество секунд, которое вы хотите отвести на время решения. Затем в поле Итерации введите максимально допустимое количество итераций.

Примечание. Если процесс решения достигает максимального времени или количества итераций, прежде чем Solver найдет решение, Solver отобразит диалоговое окно Show Trial Solution.

Установите степень точности

На вкладке "Все методы" в поле "Точность ограничения" введите требуемую степень точности. Чем меньше число, тем выше точность.

Установите степень сходимости

На вкладке GRG Nonlinear или Evolutionary в поле Convergence введите количество относительных изменений, которые вы хотите разрешить в последних пяти итерациях, прежде чем Solver остановится с решением. Чем меньше число, тем меньше допустимых относительных изменений.

В диалоговом окне "Параметры решения" нажмите "Решить" или "Закрыть".

В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».

В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".

Нажмите "Загрузить/Сохранить", введите диапазон ячеек для области модели, а затем нажмите "Сохранить" или "Загрузить".

Совет. Вы можете сохранить последние выборки в диалоговом окне "Параметры решателя" с листом, сохранив книгу. У каждого листа в рабочей книге могут быть собственные варианты выбора Решателя, и все они сохраняются. Вы также можете определить более одной проблемы для листа, нажав Загрузить/Сохранить, чтобы сохранить проблемы по отдельности.

В Excel 2016 для Mac: нажмите «Данные» > «Поиск решения».

В Excel для Mac 2011: перейдите на вкладку "Данные", в разделе "Анализ" выберите "Решатель".

Во всплывающем меню «Выбор метода решения» выберите один из следующих вариантов:

Метод решения

Описание

GRG (Обобщенный уменьшенный градиент) Нелинейный

Выбор по умолчанию для моделей, использующих большинство функций Excel, кроме ЕСЛИ, ВЫБРАТЬ, ПРОСМОТР и других «пошаговых» функций.

Используйте этот метод для задач линейного программирования. Ваша модель должна использовать СУММ, СУММПРОИЗВ, + - и * в формулах, которые зависят от переменных ячеек.

Этот метод, основанный на генетических алгоритмах, лучше всего подходит, когда ваша модель использует ЕСЛИ, ВЫБОР или ПРОСМОТР с аргументами, которые зависят от переменных ячеек.

Примечание. Часть программного кода Solver защищена авторскими правами Frontline Systems, Inc. в 1990–2010 годах. Авторские права на части принадлежат Optimal Methods, Inc. в 1989 году.

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

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

Дополнительная помощь по использованию Solver

Для получения более подробной помощи по Солверу обращайтесь:

Части программного кода Solver защищены авторскими правами 1990-2009 гг. Frontline Systems, Inc. Части защищены авторскими правами 1989 г. Optimal Methods, Inc.

Нужна дополнительная помощь?

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

Загрузите новое приложение "Поиск решения" для Excel 2016 и Excel Online. Это бесплатное приложение "Поиск решения" одинаково хорошо работает в настольных версиях Excel 2016 и Excel Online и обрабатывает самые маленькие и самые большие модели оптимизации. Пользователи могут сразу получить Solver из Microsoft AppSource. Более 100 000 существующих пользователей «облачного решателя» получат новую версию автоматически. Когда ваша модель станет доступной для бесплатной надстройки, свяжитесь с нами по поводу обновления.

Если вы все еще используете Excel 2008 для Mac, вы можете скачать Solver for Excel 2008 здесь, но мы настоятельно рекомендуем обновиться до Excel 2011 по многим причинам, включая лучший Solver!

Использование решателя Excel 2011 для Mac

Начиная с пакета обновления 1 (SP1) для Excel 2011 (версия 14.1.0), Solver снова входит в состав Microsoft Excel для Mac. Вам не нужно загружать и устанавливать Solver с этой страницы — просто убедитесь, что у вас установлено последнее обновление Excel 2011 (используйте Help — Check for Updates в меню Excel).

Чтобы включить Solver, нажмите «Инструменты», затем «Надстройки». В поле надстройки отметьте Solver.xlam и нажмите «ОК».

Чтобы использовать Solver, запустите Excel 2011 и создайте или откройте книгу. Когда вы будете готовы использовать Solver, нажмите кнопку Solver на вкладке Data (в комплектной версии Solver не используется меню Tools Solver). Должно появиться диалоговое окно Solver Parameters на языке вашей установки Microsoft Excel 2011. Нажмите здесь, чтобы получить справку по решению, применимую как к Excel 2010 для Windows, так и к Excel 2011 для Mac.

Используйте диалоговое окно "Параметры решателя", чтобы выбрать цель, переменные решения и ограничения. Затем нажмите кнопку «Решить». Решатель будет искать оптимальное решение проблемы. Когда он будет завершен, появится диалоговое окно «Результаты решения», а окончательные значения переменных решения появятся в вашей книге Excel. Чтобы приостановить или остановить Solver во время его решения, нажмите клавишу ESC и нажмите Stop (или Continue), когда появится диалоговое окно Trial Solution.

Внимание! Не вносите изменения в Excel или книгу, пока Solver выполняет решение. Изменения в Excel во время решения Solver будут иметь непредсказуемые результаты, включая сбои в Solver или Excel. См. часто задаваемые вопросы о Солвере как отдельном приложении.)

Ознакомьтесь с часто задаваемыми вопросами, чтобы узнать, как разрабатывался Решатель для Excel 2011, особенности использования этого Решателя по сравнению с Excel 2008 и чего ожидать в будущем.

История решателя Excel 2011 для Mac

До того, как Microsoft начала объединять Solver с Excel для Mac, компания Frontline Systems работала с подразделением Microsoft для Mac, чтобы предоставить вам более быстрый, простой в использовании и более мощный Solver. Предоставленная нами версия была бесплатной и на 100 % совместима — ваши существующие модели Solver будут работать без каких-либо изменений, будь то Excel 2004 или 2008 для Mac или Excel 2003, 2007 или 2010 для Windows.

Соответствует новому решателю для Excel 2010 для Windows

Поиск решения для Excel 2011 для Mac имеет те же новые функции и пользовательский интерфейс, что и решение для Excel 2010 для Windows, которое значительно улучшено по сравнению с решением для Excel 2007.

Включает новый эволюционный метод решения

Этот метод, основанный на генетических алгоритмах, дополняет методы линейного программирования и нелинейной оптимизации Solver. Используйте его для поиска решений для моделей с функциями ЕСЛИ и ПРОСМОТРА.

Глобальная оптимизация для нелинейных моделей

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

Задайте вопрос или присоединитесь к обсуждению всего, что связано с Excel, на нашем канале Slack.

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

Однако его возможности превосходят возможности Goal Seek. Решатель позволяет использовать до 200 переменных ячеек, чтобы найти оптимальное значение для целевой ячейки, в отличие от ограничения Goal Seek по одной переменной за раз.

Кроме того, параметр Solver «Ограничения» добавляет сложности инструменту, поскольку вы можете указать Excel, что он может и чего не может делать при поиске оптимального значения.

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

Как добавить решатель в Excel

Если надстройка "Поиск решения" уже активирована, вы увидите ее в группе "Анализ" на вкладке "Данные".

Если вы его не видите, сделайте следующее:

<р>1. На вкладке "Файл" нажмите "Параметры".

<р>2. В разделе «Надстройки» выберите «Надстройка решения» и нажмите кнопку «Перейти».

<р>3. Проверьте надстройку Solver и нажмите OK.

Команда "Решатель" теперь отображается на вкладке "Данные".

Загрузите бесплатный файл практики!

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

Как добавить решатель в Excel (Mac)

В поле «Доступные надстройки» установите флажок «Надстройка решения». Нажмите "ОК".

Если надстройка Solver не указана в списке доступных надстроек, нажмите кнопку «Обзор», чтобы найти ее.

Если вы получите сообщение о том, что надстройка Solver в настоящее время не установлена на вашем компьютере, нажмите Да, чтобы установить ее, затем закройте Excel и перезапустите.

Как использовать Solver — пример

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

Ячейка цели должна содержать формулу.
Формула в целевой ячейке должна прямо или косвенно зависеть от переменных ячеек.

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

Минимальное и/или максимальное количество часов, которое каждый сотрудник может планировать ежедневно.
Минимальное и/или максимальное количество часов, которое каждый сотрудник может отводить еженедельно.
Максимальное количество часов сверхурочной работы, которые мы хотим запланировать (т. е. коэффициент стоимости заработной платы).

Прежде чем мы начнем думать об ограничениях, какова наша цель? Мы хотим выделить определенное количество часов нашим сотрудникам для работы над имеющимися проектами на предстоящую неделю.

Предположим, что у нас есть три недели на выполнение этих проектов и 10 сотрудников. Все, что нам нужно сделать, это распределить 1065 часов на три недели и распределить эти часы между 10 сотрудниками.

Ячейки с H11 по H20 настроены для расчета количества часов работы оператора в каждой строке.

Ячейка H21 предназначена для расчета общего количества часов для всех операторов.

В качестве менеджера по персоналу вы можете поэкспериментировать с несколькими сценариями, например:

Равномерное распределение часов на три недели.
Максимальное количество часов работы агента на этой неделе (возможно, вы ожидаете дополнительных проектов в ближайшие недели и хотите выполнить их как можно быстрее).
Планирование максимально допустимого количества рабочих часов без сверхурочной работы и т. д.

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

Agt_Hrs (ячейки H11:H20) и
Сдвиги (ячейка B11:G20).

Сформулировать модель

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

Давайте определим первые три термина в этом окне.

Установить цель:

Это поле относится к ячейке, которая будет содержать один результат, поддающийся количественной оценке.

До макс./мин./значения:

Решите, хотите ли вы найти минимальное, максимальное или точное значение.

путем изменения ячеек переменных:

Это ячейки, которые Excel может изменить для достижения цели. Excel Solver позволяет указать до 200 переменных ячеек.

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

Давайте рассмотрим распределение необходимых 1065 часов примерно на три недели. Это будет означать планирование 355 часов каждую неделю. Выберите переключатель Значение: и введите 355.

Изменяя переменные ячейки: будет диапазон, который мы назвали Shifts.

Ограничения

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

Значение в этой ячейке должно быть больше или равно ….

Нажмите "Добавить", чтобы добавить другое ограничение.

Изучите другие полезные инструменты Excel с помощью нашего курса Excel Basic и Advanced уже сегодня!

Читайте также: