Решение уравнений в Excel

Обновлено: 21.11.2024

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 — мощный инструмент, когда у вас есть полные данные. Но было бы неплохо, если бы он мог найти неизвестные переменные?

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

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

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

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

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

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

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

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

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

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

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

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

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

Это отличный способ найти несколько неизвестных переменных в Excel. Но найти и использовать его не так просто. Давайте взглянем на загрузку надстройки «Поиск решения», а затем перейдем к тому, как использовать «Поиск решения» в текущей версии Microsoft 365 Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

(больше или равно)
int (должно быть целым числом)
bin (должно быть равно 1 или 0)
Все разные

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

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

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

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

Пример решения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Просто нужно привыкнуть к ним. Чем больше вы их используете, тем полезнее они становятся.

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

Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.

Пример: чтобы получить прибыль в виде дополнительных 10 %, сколько единиц необходимо продать или какие точные оценки необходимо получить за последний семестр выпускных экзаменов, чтобы получить отличие.

Вышеупомянутый расчет или уравнения можно рассчитать с помощью надстройки Solver с определенными критериями.

Определение решения уравнения в Excel

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

Он содержит следующие параметры.

Как добавить надстройку Solver?

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

Чтобы добавить надстройку Solver, выполните следующую процедуру:

Обучающий пакет MS Excel 13 онлайн-курсов | 100+ часов | Поддающиеся проверке сертификаты | Срок действия
4,5 (303 оценки)

Нажмите кнопку «Файл» или кнопку Office; затем вам нужно нажать «Параметры Excel».

Теперь появится диалоговое окно параметров Excel; в разделе "Надстройки" выберите "Надстройка решения" в списке неактивных надстроек приложения и нажмите "Перейти".

Откроется окно надстроек, в котором можно просмотреть список активных параметров надстроек. Отметьте надстройку Solver и нажмите кнопку "ОК".

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

Пример решения уравнений с помощью надстройки Solver

Чтобы рассчитать значения переменных для максимизации прибыли в % с помощью надстройки Solver.

В приведенной выше таблице ежемесячные данные о продажах по цене за единицу, содержащие стоимость или самую низкую цену за единицу и цену продажи за единицу для клиентов. Теперь у меня есть месяц апрель и май с процентной прибылью на каждую единицу, то есть 13,33% и 15,38% соответственно.

Здесь B4 и C4 – это процентная прибыль за апрель и май 2019 года, которая рассчитывается по приведенной ниже формуле.

Формула для определения процентной прибыли:

((Продажная цена за единицу – Цена на складе за единицу)/ Цена на складе за единицу) *100

Переменные (B2, B3 и C2, C3): здесь переменными являются стоимость или самая низкая цена за единицу и цена продажи за единицу для клиентов, которая продолжает меняться из месяца в месяц.

Цель и ограничения

Теперь моя цель – довести процентную прибыль (%) на единицу до 20 %.Итак, для этого мне нужно узнать стоимость или самую низкую цену за единицу и цену продажи за единицу для клиентов, необходимую для получения прибыли в размере 20%.

Целевая ячейка: D4 (прибыль %) должна давать прибыль 20%.
Ячейки переменных: C2 (Себестоимость или самая низкая цена за единицу) и C3 (Цена продажи за единицу покупателям).
Ограничения: D2 должно быть >= 16 000, а D3 должно быть = 16 000)

С математической точки зрения система уравнений представляет собой набор из двух или более уравнений с одинаковым набором неизвестных переменных, таких как x, y, z, где нам нужно найти значения неизвестных переменных для решения этих уравнений. Чтобы решить систему уравнений, мы можем использовать функции и инструмент для решения уравнений.

Рисунок 1. Как решить систему уравнений

Использование функций Excel

Мы можем решить систему уравнений, используя математические функции MINVERSE и MMULT. Предположим, у нас есть три уравнения в нашей системе уравнений в нашем примере. Чтобы решить уравнения с помощью функций, нам нужно настроить данные следующим образом:

Рисунок 2. Настройка данных системы уравнений

В матричной записи это можно записать как;

И чтобы найти значение неизвестной переменной X, нам нужно найти обратную матрицу A ( A-1 ) и умножить ее на матрицу B , например;

Х= А-1В

Чтобы найти обратную матрицу A ( A-1 ), нам нужно использовать функцию MINVERSE следующим образом:

Выберите диапазон ячеек в соответствии с макетом матрицы A, например C10: E12.

Рисунок 3. Выбор диапазона ячеек

Перейдите к строке формул и вставьте формулы MINVERSE как =MINVERSE(C6: E8) и нажмите Ctrl + Shift + Enter вместо нажатия клавиши Enter, чтобы сделать формулу массива, чтобы возвращать массив значений как обратную матрице А.

Рисунок 4. Нахождение обратной матрицы A

Теперь нам нужно найти значения неизвестных переменных в матрице X (x, y, z) в столбце H, чтобы решить систему уравнений с помощью функции Excel МУМНОЖ следующим образом;

Выберите диапазон ячеек H6:H8 матрицы X.

Рисунок 5. Выбор диапазона ячеек матрицы X

В строке формул вставьте формулу МУМНОЖ как =МУМНОЖ(C10: E12, K6: K8), чтобы получить произведение двух матриц A-1 (обратная матрица A) и B с использованием ее массивов, и нажмите Ctrl + Shift + Enter, чтобы сделать его формулой массива. Эта формула возвращает значения неизвестных переменных и решает систему уравнений.

Рисунок 6. Решение системы уравнений

Использование надстройки Excel Solver

Еще один подход к решению системы уравнений — надстройка Excel Solver. Нам нужно установить эту надстройку из доступных надстроек из «Параметры»> «Надстройки». Для Excel Solver нам нужно настроить данные системы уравнений следующим образом;

Для матрицы A нам нужно ввести формулу для каждого из уравнений в столбце C, как показано.
Вставьте значения матрицы B в столбец D.
Оставьте ячейки матрицы X пустыми в столбце E, чтобы вернуть значения неизвестных переменных x, y, z .

Рисунок 7. Настройка данных уравнений для решателя

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

Рис. 8. Окно параметров решателя

В поле «Установить цель» вставьте ссылку на ячейку C7 , первую ячейку матрицы A, где мы установили формулу для первого уравнения системы.
Выберите цель как Макс.
Вставьте диапазон ссылок на ячейки E7: E9 матрицы X в поле "Изменение переменных ячеек".

Рисунок 9. Настройка параметров решателя

В поле Subject to Constraints нам нужно добавить ограничения для каждого из уравнений, нажав кнопку «Добавить».
В окне «Добавить ограничения» нам нужно ввести ограничения для всех уравнений одно за другим, нажав кнопку «Добавить». Когда мы закончим добавлять все ограничения, нажмите кнопку OK.

Рис. 10. Добавление ограничений в решателе

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

Рисунок 11. Решение уравнений

В окне "Результаты решателя" выберите "Сохранить решения решателя" и нажмите кнопку "ОК".

Рисунок 12. Результаты решателя

Поиск решения возвращает значения неизвестных переменных для матрицы X в столбце E.

Рисунок 13. Решатель уравнений

Мгновенное подключение к эксперту через нашу службу Excelchat

В большинстве случаев проблема, которую вам нужно решить, будет более сложной, чем простое применение формулы или функции. Если вы хотите сэкономить часы исследований и разочарований, попробуйте наш онлайн-сервис Excelchat! Наши эксперты по Excel доступны круглосуточно и без выходных, чтобы ответить на любой вопрос, который у вас может возникнуть. Мы гарантируем подключение в течение 30 секунд и индивидуальное решение в течение 20 минут.

Поиск цели — это встроенная в Excel функция, которая ищет решение для модели/формулы путем многократного перебора значений исходных ячеек до тех пор, пока не будет найдено решение.

Прежде чем мы начнем, Excel не понимает понятия x и y , но вместо этого мы можем использовать для них ячейки

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

Начните новый файл и в C3 (наша ячейка y) введите: = 2*10^7*B3^-2.146

В ячейке B3 (наша ячейка x): введите значение, скажем, 10

Обратите внимание, что C3 покажет решение формулы для случая, когда x = 10 или = 2*10^7* 10 ^-2,146 = 142 899,277

Использование поиска цели

Чтобы использовать поиск цели, чтобы определить, какое значение x (B3) приведет к y (C3) = 60,

Выберите C3

Перейти к меню "Данные", "Анализ возможных вариантов", "Поиск цели"

Установить ячейку: C3 — это наша ячейка со значением Y

До значения: 60 Это значение, которое мы хотим получить

Изменив ячейку: B3 — это наша ячейка со значением x

Нажмите OK, когда будете готовы

Excel показывает, что решение найдено и что y (C3) = 60, когда x (B3) = 374,60

Нажмите OK, чтобы сохранить результат

Нажмите «Отмена», чтобы вернуться к предыдущему значению.

Как вы решали формулы с помощью Excel или других методов

Как вы решали формулы с помощью Excel или других методов?

Сообщите нам об этом в комментариях ниже:

Подробнее о поиске цели и решателе:

Привет, круто.

Меня зовут Чанду. Спасибо, что заглянули. Моя миссия - сделать вас потрясающими в Excel и вашей работе. Я живу в Веллингтоне, Новая Зеландия. Когда я не работаю со своими формулами, я катаюсь на велосипеде, готовлю или играю в лего со своими детьми. Узнайте больше обо мне.

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

Спасибо и до встречи.

Статьи по теме:

30 ответов на вопрос «Как решить уравнение в Excel»

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

Да! Я решил множество уравнений, используя различные методы. Однако мне нравится демонстрировать решение относительно сложных одновременных уравнений с использованием

Функции =MINVERSE() и =MMULT()

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

Я попробовал два альтернативных подхода:

во-первых, старая добрая алгебра (мои дети сейчас много переставляют выражения в школе).

Итак, x = (y / 2*10E+07) ^ (-1/2,146)

что позволяет мне использовать оператор "вычислить" "=>". Я набираю "x =>" (без кавычек), и calca добавляет то, что знает:

.. поэтому он переформулирует выражение в терминах y. Довольно круто. Для решения я набрал

а затем запросил x:

Хотя я ценю хитрость обучения использованию функции поиска цели Excel для решения уравнения, я нахожу это несколько опасным. Я знаю, что в вопросе прямо предлагалось использовать для этого Excel, но я бы сравнил ответ с обучением голодного человека ловить рыбу с помощью динамита!

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

Как же найти решение, используя школьную алгебру?

Цель состоит в том, чтобы x стоял с одной стороны, а остальные — с другой.

Начиная с
y = 2*10^7*x^-2,146,
множитель "2*10^7" можно переместить влево, разделив на 2*10^2:
y/(2*10^7) = (2*10^7*x^-2,146)/(2*10^7)
y/(2*10^7) = x^ -2,146
Следующий шаг немного сложнее. Вам нужно избавиться от показателя степени "-2,146" с правой стороны. Если показатель степени равен «2», мы знаем, что можем просто извлечь квадратный корень. Аналогично, в нашем случае нам нужно было бы извлечь корень "-2,146"-й. Но нет функции извлечения произвольного корня. Нам нужно знать, что в примере с квадратными корнями «извлечение квадратного корня из значения» равносильно «увеличению значения до 1/2». Короче говоря, sqrt(4)=4^(1/2). Соответственно, «извлечение корня '-2,146» равнозначно возведению в «1/-2,146», что равно -1/2,146. Вот что нам нужно сделать, чтобы избавиться от экспоненты в правой части:
(y/(2*10^7))^(-1/2,146) = (x^-2,146)^(-1 /2.146)
(y/(2*10^7))^(-1/2.146) = x
Поменяйте местами левую и правую части, и мы получим:
x = (y/( 2*10^7))^(-1/2,146)

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

Я научился решать одновременные уравнения в Excel из постов Алана Бебана много лет назад, используя MINVERSE и MMULT (то же самое, что описывает Дункан). Я использовал его несколько раз в своей работе.

Отличный материал! Я очень ценю шаг за шагом с изображениями!! Супер полезно!!

использовала функцию поиска цели для решения уравнения
a= CF*1/(1+x)^1+CF*1/(1+x)^2+. +CF*1/(1+x)^n для изменения «а».
Довольно чисто и полезно.
Спасибо!

Это срочно. Если вы можете помочь!

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

Извините. Я не знаю, как разместить свой лист excel здесь, например. Я сделал .jpg, но как разместить?

Отправьте его мне по адресу duncanwil at gmail dot com, и я отчитаюсь здесь

Прикрепив образец файла, вы получите ответ быстрее

Я ответил Рахулу вне списка, но пока не получил ответа.

Я согласен с тем, что Рахул мог загрузить сюда свою работу, но, поскольку он этого не сделал, думаю, все в порядке.

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

Я ответил вам и Рахулу напрямую по электронной почте
без ответа?

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

Моя проблема решена другим способом.

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

Пожалуйста, прикрепите образец файла, чтобы дать более целенаправленный ответ

Некоторые поистине фантастические сообщения на этом веб-сайте, спасибо за участие.

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