Найти решение в Excel
Обновлено: 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 — пример сочетания продуктов
Знакомство с диалоговыми окнами решателя Excel
Чтобы сообщить решателю Excel, какие ячейки на листе представляют переменные решения, ограничения и целевую функцию, мы нажимаем кнопку «Решатель» на вкладке «Данные Excel» или кнопку «Премиум-решатель» на вкладке «Надстройки», которая отображает «Решатель». Диалог параметров. В поле «Установить цель» (или «Установить целевую ячейку») мы вводим или нажимаем на ячейку F5, целевую функцию. В поле редактирования By Changeable Variable Cells мы набираем B4:E4 или выбираем эти ячейки с помощью мыши. (Нажмите на изображение, чтобы увидеть его в полном размере.)
Чтобы добавить ограничения, мы нажимаем кнопку «Добавить» в диалоговом окне «Параметры решателя», выбираем ячейки F8:F11 в поле редактирования «Ссылка на ячейку» (левая сторона) и выбираем ячейки G8: G11 в поле редактирования "Ограничение" (правая сторона); отношение по умолчанию плюс моделирование Монте-Карло и анализ рисков, деревья решений, интеллектуальный анализ данных, интеллектуальный анализ текста и машинное обучение и многое другое! Это невероятное предложение, и вы можете попробовать его бесплатно.
Подробнее
Если вы добрались до этого места, поздравляем! Вы успешно установили и решили простую задачу оптимизации с помощью Microsoft Excel. Если хотите, вы можете посмотреть, как настроить и решить ту же проблему с набором продуктов с помощью платформы решения рисков в Excel или с помощью программы Visual Basic .NET, которая вызывает SDK платформы решения для решения Frontline. Если вы еще не читали другие части руководства, вы можете вернуться к началу руководства и прочитать обзоры «Для чего нужны решатели?», «Как определить модель?», «Какого рода Решение, которое я могу ожидать?» и "Что затрудняет решение модели?"
Это был пример задачи линейного программирования. Другие типы задач оптимизации могут включать квадратичное программирование, смешанно-целочисленное программирование, программирование с ограничениями, гладкую нелинейную оптимизацию и негладкую оптимизацию. Чтобы узнать больше, щелкните Типы задач оптимизации. Чтобы получить более подробное объяснение линейности и разреженности в задачах оптимизации, перейдите к нашему расширенному учебному пособию.
Microsoft Excel — популярный инструмент для анализа данных, повышающий производительность пользователей при работе с данными. Когда дело доходит до принятия важных решений, таких как составление бюджета, аутсорсинг, добавление или удаление линейки продуктов, нам необходимо учитывать общие затраты. Эти решения требуют знаний бухгалтеров, чтобы определить, какие действия необходимо предпринять, чтобы максимизировать результат. Таким образом, помогая нам решать бизнес-задачи наиболее эффективным способом. Здесь вступает в действие Solver.
Эта статья представляет собой простое введение в Solver в Excel, которое позволяет выполнять линейное программирование и получать наилучшие возможные результаты.
Что такое решатель в Excel?
Решатель в Excel — это инструмент, который помогает нам решать проблемы принятия решений, находя подходящие оптимальные решения. Этот Решатель относится к набору инструментов анализа «что, если», которые помогают нам тестировать различные сценарии в Excel. Они также определяют, как каждый сценарий влияет на результат на листе.
Эта тема может показаться немного сложной для новичков, но пошаговое руководство, описанное в этой статье, даст вам представление о том, как можно использовать инструмент "Поиск решения" для принятия решений. Но сначала давайте посмотрим, как включить Solver в Excel.
Последипломная программа по бизнес-анализу
Как включить решатель в Excel?
Решатель – это надстройка, доступная в Excel. Теперь давайте рассмотрим, как добавить этот инструмент.
- Если у вас есть версии Excel: - 2010, 2013, 2016 или 2019, вам нужно сначала перейти на вкладку «Файл» → «ОПЦИИ», как показано на рисунке.
Примечание. Если у вас Excel 2007, нажмите кнопку Microsoft Office → Параметры.
- Откроется окно параметров Excel. Выберите параметр «Надстройки», а затем нажмите «Перейти».
- Откроется окно надстройки с различными инструментами, которые вы можете выбрать. Отметьте опцию надстройки Solver из списка. Нажмите "ОК".
В версии Excel 2003 мы можем добавить Solver из меню Tools, чтобы найти надстройки. При нажатии на нее откроется список надстроек, из которых вы должны выбрать Solver и нажать OK.
- Теперь решатель находится в разделе "Анализ" на вкладке "Данные".
Вы можете найти новый Решатель в меню "Инструменты" версии Excel 2003.
Двигаясь вперед, давайте разберемся, как использовать инструмент "Решатель".
Как использовать решатель в Excel?
- Сформулируйте модель
- Метод проб и ошибок
- Решить модель
Мы подробно обсудим их вместе с пошаговым руководством.
1. Сформулируйте модель
Мы будем использовать следующий набор данных хранилища, чтобы продемонстрировать Solver в Excel.
Этот набор данных состоит из столбцов с названиями продуктов, общим количеством единиц, продажной ценой, себестоимостью каждой единицы, прибылью и общим доходом. В ячейке B5 указано общее количество доступных единиц, а в ячейке F5 — общий полученный доход.
Теперь давайте посмотрим на постановку задачи.
Постановка задачи
Максимальное количество единиц, которые можно заказать, равно 150. Прибыль, полученная от продажи каждой единицы, указана в таблице. Нам нужно максимизировать общую прибыль, полученную от продажи продуктов: телевизора, стереосистемы и динамиков.
Чтобы найти, сколько единиц каждого продукта требуется, чтобы прибыль была максимальной?
2. Метод проб и ошибок
Благодаря этой концепции пользователь может легко проанализировать любое пробное решение.
Предположим, мы закажем 50 единиц телевизоров, 50 единиц стереосистем и 50 единиц динамиков, при этом максимальное количество единиц, которое можно заказать, равно 150, мы получим общий доход в размере 1 25 000 рупий. Однако, чтобы максимизировать прибыль, давайте продвинемся вперед и добавим необходимые входные данные в решатель.
3. Решите модель
Чтобы решить эту проблему, мы используем Solver в Excel.Теперь мы выполним следующие шаги, чтобы найти оптимальное решение.
- Нажмите на инструмент "Решатель", который теперь добавлен в раздел "Анализ" на вкладке "Данные". Откроется вкладка "Параметры решения".
- Далее нам нужно сформулировать модель. Для этого необходимо указать три основных параметра, т. е.
- Ячейка цели
- Ячейки переменных
- Ограничения
Excel находит оптимальное значение (минимальное, максимальное, заданное) для формулы, представленной в ячейке "Цель". Это делается путем изменения значений в ячейках переменных в зависимости от ограничений, указанных в ячейках ограничений.
Теперь давайте сопоставим каждый параметр с ячейками в наборе данных, введя значения ячеек в диалоговом окне "Параметры решения".
1. Целевые ячейки
Ячейка цели содержит формулу, определяющую цель постановки задачи. Здесь ячейка F5 указана как целевая ячейка. Целью может быть поиск минимального, максимального или точного значения.
Здесь цель состоит в том, чтобы найти максимальное значение, так как мы максимизируем прибыль. Итак, мы устанавливаем значение целевой ячейки как ячейку F5 и выбираем параметр MAX.
2. Ячейки переменных
Переменные ячейки состоят из переменных данных, которые модифицируются Солвером для достижения цели.
Это ячейки, содержащие переменные данные. Значение этих ячеек необходимо изменить для оптимизации прибыли. В этом примере мы указываем ячейки с количеством единиц/продукта как ячейки переменной, которые находятся в диапазоне B2: B4.
3. Ограничения
Ограничения – это ограничения, накладываемые на Решатель. Эти ограничения аналогичны ограничениям реального мира на общую производительность продукта, требования рынка и т. д. Решение, предоставляемое Солвером, должно удовлетворять этим ограничениям. Чтобы прийти к оптимальному решению, необходимо выполнить эти условия.
В этом примере мы можем указать следующие ограничения:
- Количество единиц для каждого продукта должно быть больше или равно нулю. Следовательно, мы можем установить ограничение как $B$2 >= 0, $B$3 >= 0 и $B$4 >=0.
- Количество продаваемых единиц не может превышать общее количество единиц. Итак, $B$5
Затем откроется диалоговое окно «Добавить ограничение». Теперь введите ссылку на ячейку. Укажите необходимое значение ограничения и нажмите «Добавить».
После добавления всех ограничений закройте диалоговое окно "Добавить ограничение".
Давайте продолжим и разберемся с параметрами, доступными в разделе «Поиск решения» в Excel, которые изменяют способ поиска решений «Поиск решения».
Параметры решения
Для большинства задач нет необходимости изменять параметры Решателя по умолчанию. Однако если вы хотите изменить некоторые параметры, выполните следующие действия:
- В диалоговом окне "Параметры решения" нажмите "Параметры".
- Появится диалоговое окно "Параметры решения".
- На вкладке "Все методы" выберите один или несколько вариантов:
1. Ограничение точности
Введите необходимую степень точности в поле Точность ограничения. Это определяет точность ограничений. Вы можете указать более низкое значение (от 0 до 1), чтобы сократить время, необходимое Солверу для возврата решения.
2. Использовать автоматическое масштабирование
Установив этот флажок, результаты автоматически масштабируются при решении проблемы.
3. Показать результаты итерации
При установке этого флажка отображаются результаты итераций, использованных при решении проблемы.
4. Игнорировать целочисленные ограничения
Установив этот флажок, все ограничения, заданные для целых чисел, будут игнорироваться.
5. Целочисленная оптимальность (%)
Определяет процент целочисленных критериев оптимальности, используемых решателем для решения проблемы.
6. Максимальное время (секунды)
Указывает максимальное количество секунд, которое потребуется Солверу для поиска решения.
7. Итерации
Указывает, сколько раз Решатель будет пересчитывать проблему при поиске решения.
8. Максимальное количество подзадач
Определяет максимальное количество подзадач, разрешенных для Решателя.
9. Максимально возможные решения
Определяет максимальное количество возможных возможных решений, которые необходимы.
Нажмите OK после изменения необходимых параметров.
Наконец, нажмите кнопку "Решить" в диалоговом окне "Параметры решателя", чтобы найти оптимальное решение.
Excel найдет решение, которое максимизирует прибыль. Откроется диалоговое окно «Результаты решения», в котором говорится: «Решатель нашел решение». Вы можете выбрать отчеты, необходимые для создания отчета Solver. Затем выберите параметр «Сохранить результаты решения» и нажмите «ОК».
Оптимизированное решение будет отображаться на вашем листе. Наша цель оптимизировать решение, которое было достигнуто, и Решатель пришел к решению, указанному ниже.
Оптимальным решением будет заказать 100 единиц телевизоров и 50 единиц стереосистем. Это решение дает максимальную прибыль в размере 1 50 000 рупий.
Будет создан следующий отчет. В этом отчете указаны исходные и окончательные значения ячеек Целевая ячейка и Переменная, а также статус каждого ограничения в оптимальном решении.
Это основы понимания того, как использовать Solver в Excel, чтобы найти оптимальные решения для ваших проблем с принятием решений.
Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы для бизнес-аналитиков. Зарегистрируйтесь сейчас!Заключение
В этой статье вы узнали, как использовать Решатель в Excel для решения проблемы принятия решения, указав Целевые ячейки, Переменные ячейки и Ограничения на примере.
Если у вас есть какие-либо вопросы по поводу этой статьи, укажите их в разделе комментариев, и наши специалисты свяжутся с вами в ближайшее время.
Вы можете улучшить свои навыки работы с Excel, записавшись на сертификационный курс Business Analytics with Excel, предлагаемый Simplilearn.
Об авторе
Простое обучение
Simplilearn — один из ведущих мировых поставщиков онлайн-обучения по цифровому маркетингу, облачным вычислениям, управлению проектами, науке о данных, ИТ, разработке программного обеспечения и многим другим новым технологиям.
Читайте также: