Решение геометрических задач в Excel

Обновлено: 24.11.2024

В этом руководстве объясняется, как добавить и где найти Решатель в разных версиях Excel с 2016 по 2003 год. В пошаговых примерах показано, как использовать Решатель Excel для поиска оптимальных решений для линейного программирования и других задач. проблемы.

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

В этом руководстве мы рассмотрим все основные аспекты надстройки Excel Solver и предоставим пошаговое руководство по ее наиболее эффективному использованию.

Что такое Excel Solver?

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

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

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

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

Надстройка "Поиск решения" включена во все версии Microsoft Excel, начиная с 2003, но по умолчанию она не включена.

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

Чтобы установить Solver в Excel 2003, перейдите в меню Инструменты и выберите Надстройки. В списке Надстройки доступные установите флажок Надстройка Solver и нажмите ОК. р>

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

Где находится Solver в Excel 2019, 2016, 2013, 2010 или 2007?

В современных версиях Excel кнопка "Решатель" отображается на вкладке Данные в группе Анализ:

Где находится Solver в Excel 2003?

После загрузки надстройки "Поиск решения" в Excel 2003 ее команда добавляется в меню Инструменты:

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

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

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

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

Проблема. Предположим, вы владелец салона красоты и планируете предоставлять своим клиентам новую услугу. Для этого вам необходимо купить новое оборудование стоимостью 40 000 долларов США, которое необходимо оплатить в рассрочку в течение 12 месяцев.

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

Для этой задачи я создал следующую модель:

А теперь давайте посмотрим, как Excel Solver может найти решение этой проблемы.

1. Запустить решатель Excel

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

2. Определите проблему

Откроется окно Параметры решателя, в котором необходимо настроить 3 основных компонента:

Ячейка цели
Ячейки переменных
Ограничения

Что именно делает Excel Solver с указанными выше параметрами? Он находит оптимальное значение (максимальное, минимальное или заданное) для формулы в ячейке Цель путем изменения значений в ячейках Переменная и с учетом ограничений в ячейке Ограничения ячеек.

Цель

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

В этом примере целевой ячейкой является B7, которая рассчитывает срок платежа по формуле =B3/(B4*B5), и результат формулы должен быть равен 12:

Ячейки переменных

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

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

Прогнозируемое количество клиентов в месяц (B4) должно быть меньше или равно 50; и
Стоимость услуги (B5), которую мы хотим рассчитать с помощью Excel Solver.

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

Ограничения

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

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

Нажмите кнопку "Добавить" справа от поля "С учетом ограничений".

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

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

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

Меньше или равно, равно и больше или равно. Вы устанавливаете эти отношения, выбирая ячейку в поле Ссылка на ячейку, выбирая один из следующих знаков: =, а затем вводя число, ссылку на ячейку/имя ячейки или формулу в поле Ограничение. (см. скриншот выше).
Целое число. Если указанная ячейка должна быть целым числом, выберите целое число, и целое слово появится в поле Ограничение.
Разные значения. Если каждая ячейка в диапазоне, на который указывает ссылка, должна содержать разные значения, выберите dif, и слово AllDifferent появится в поле Ограничение.
Двоичный. Если вы хотите ограничить ссылочную ячейку значением 0 или 1, выберите bin, и в поле Ограничение появится слово binary.

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

В диалоговом окне Параметры решателя щелкните ограничение.
Чтобы изменить выбранное ограничение, нажмите Изменить и внесите нужные изменения.
Чтобы удалить ограничение, нажмите кнопку Удалить.

B3=40000 – стоимость нового оборудования составляет 40 000 долларов США.
Ячейки переменных B4 (B7:E8).
Каковы ограничения? Запасы, доступные на каждом складе (I7:I8), не могут быть превышены, и должно быть доставлено количество, заказанное каждым клиентом (B10:E10). Это ограниченные ячейки .
Какова цель? Минимальная общая стоимость доставки. А это наша ячейка цели (C12).

Следующее, что вам нужно сделать, это рассчитать общее количество товаров, отгруженных с каждого склада (G7:G8), и общее количество товаров, полученных каждым клиентом (B9:E9). Вы можете сделать это с помощью простых формул суммы, показанных на снимке экрана ниже. Кроме того, вставьте формулу СУММПРОИЗВ в C12, чтобы рассчитать общую стоимость доставки:

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

td> < /tr>

Имя диапазона	Ячейки	Параметр решателя
Products_shipped	B7:E8	Ячейки переменных
Доступно	I7:I8	Ограничение
Total_shipped	G7:G8	Ограничение
По порядку	B10:E10	Ограничение
Всего_получено	B9:E9	Ограничение
Стоимость_доставки	C12	Цель

Последнее, что вам осталось сделать, это настроить параметры Excel Solver:

Цель: Shipping_cost установить на минимум
Ячейки переменных: Products_shipped
Ограничения: Total_received = Ordered and Total_shipped

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

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

Как написать простое уравнение в Excel? Как добавить столбец чисел? Это первые вопросы, которые задают новые пользователи Excel.

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

5 правил написания формул в Excel

Примеры математических операций в Excel

Порядок действий

Чтобы правильно писать формулы Excel, необходимо понимать этот порядок! Дополнительные примеры см. в нашем руководстве по математическому порядку операций.

Вычисление процентов в Excel

Есть два способа расчета процентов в Excel, в зависимости от того, как спроектирован рабочий лист (электронная таблица).

Чтобы вычислить процент и отобразить знак процента в ячейке, просто введите формулу в ячейку и отформатируйте ячейку как процент. Пример: формула в ячейке C2: =A2/A3. Если A2=50 и B2=100, то 50÷100=0,5 и 0,5 обычно отображаются. Но если мы отформатируем ячейку C2 как процентное значение, вместо нее будет отображаться 50%.

Как мы узнали из нашего руководства для начинающих, Excel Made Easy, чтобы отформатировать ячейку или группу ячеек, щелкните правой кнопкой мыши в ячейке и выберите «Формат ячеек». Нажмите «Процент» на вкладке «Число», укажите количество десятичных точек и нажмите "ОК".

Значок формата также можно найти на ленте в более новых версиях Excel.

Возможно, вам нужен столбец с пометкой "процент" и вы не хотите, чтобы отображался знак процента. Это просто. Просто умножьте формулы на 100, чтобы отобразить число, равное процентному числу.

См. образец рабочего листа ниже: ячейка C2 содержит формулу для расчета процента A2 (50), деленного на A3 (100). Формула =A2/A3. Как видите, мы отформатировали C2 для отображения процентов, и это так.

Мы ввели ту же формулу в D2, но отформатировали ячейку для отображения числа с двумя десятичными точками, поэтому Excel отображает 0,5, что является десятичным эквивалентом 50%.

И в ячейке E2 мы также отформатировали ячейку для отображения числа, но мы умножили формулу на 100, как показано в строке формул, чтобы отображались проценты вместо десятичной дроби. Затем мы назвали наш столбец «Процент», и все в порядке! (Новая формула =A2/A3*100).

Копирование и вставка формул

Чтобы скопировать содержимое ячейки, щелкните ячейку, щелкните правой кнопкой мыши и выберите "Копировать". (Или используйте сочетание клавиш Ctrl+C.) Затем поместите курсор в принимающую ячейку, щелкните правой кнопкой мыши и выберите «Вставить». (Или используйте сочетание клавиш Ctrl+V.)

Чтобы удалить анимированную рамку в исходной ячейке, нажмите клавишу ВВОД или клавишу Esc либо щелкните другую ячейку и начните вводить текст.

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

При копировании и вставке формул Excel предполагает, что вы хотите, чтобы адреса ячеек (например, B8) были изменены, чтобы соответствовать любой строке или столбцу, в которых вы находитесь. Это потому, что большую часть времени мы копируем и вставляем уравнения, это то, что мы хотим. Например, возможно, мы находим сумму многих столбцов рабочего листа. Если вы не хотите, чтобы Excel менял адреса ячеек, вам необходимо использовать абсолютные ссылки на ячейки. Мы расскажем об этом во Все о ссылках на ячейки.

Блокировка ячеек для защиты

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

Дополнительную информацию и инструкции по защите рабочего листа или книги можно найти в нашем руководстве Блокировка ячеек и защита рабочих листов.

Заключительные мысли

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

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

В деловом мире эти головоломки более сложны. Что мы можем сделать?

Написать алгебраическое уравнение?
Позвонить специалисту по данным?

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

Что мы решаем?

Мы решаем: сколько сейчас лет Лизе?

Что мы знаем?

Начнем с фактов. Мы пока не знаем, сколько Лизе лет, но знаем:

Папе в 7 раз больше, чем Лизе сейчас
Через 20 лет Лиза будет вдвое моложе отца.

Сейчас мы просто введем 7 и 20, как показано ниже.

Добавить простые формулы

Я поставил 9 в ячейку L12, чтобы формулы заполнялись.

Ячейки L17, L18 и L19 имеют следующие формулы:

=ЕСЛИ(L12=""",",",L12*L14)
=ЕСЛИ(L12=""",",L12+L15)
=ЕСЛИ(L12=""",",L17+L15)

Магия решения

Посмотрите на красную ячейку L21. Должно быть 2 (будущий возраст папы вдвое больше будущего возраста Лизы).

Да, мы можем изменить текущий возраст Лизы в ячейке L12, но в более сложных случаях это может занять несколько часов!

Давайте использовать Солвер! (Никогда не использовали решатель? См. ссылку на учебник по решателю ниже)

результатом формулы для ячейки L21 должно быть число 2 (возраст отца в два раза больше возраста Лизы)
изменить текущий возраст Лизы (ячейка L12), пока ячейка L21 не станет равной 2
ячейка L12 должна быть целым числом (целым числом)

Результаты решения!

Решатель решил для нас головоломку! Нажмите «ОК», чтобы принять решение!

Итак… Сейчас Лизе 4 года!

Учебник по решателю !

Поиск решения — одно из секретных оружий Excel!

Обо мне

Меня зовут Кевин Лербасс. Я аналитик данных из Маркхэма, Онтарио. Это был я в Монреале в хорошем польском ресторане Stash Cafe .

С тех пор, как я начал работать с данными в 2001 году, инструменты работы с данными сильно изменились. В Excel есть DAX и M (также используемые в Power BI!), и только на этой неделе я узнал о некоторых потрясающих новых функциях Excel, которые называются динамическими массивами! Прочтите сообщение Билла Джелена «Фильтрация данных Excel с помощью функции» и получите его бесплатный PDF-файл!

У меня есть три кривые, A, B и C, представленные координатами X-Y на следующем графике. Исходные точки находятся в точке (2,5 100) по оси ординат (X,Y).

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

Если заданное значение X,Y находится слева от кривой-A, вернуть вывод "A"
Если заданное значение X,Y находится слева от кривой-B и справа от кривой-A вернуть вывод "B"
Если заданное значение X,Y находится слева от кривой-C и справа от кривой-B, вернуть вывод "C"
Если заданное значение X,Y равно справа от кривой-C вернуть вывод "FALSE"

Спасибо в ожидании.

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

Сообщить о нарушении

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

Ответы (4) 

Вот список доступных функций Excel:

Если это не поможет, возможно, вам придется написать сценарий VB для получения желаемых результатов.

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

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

y = 100 + A1 (x – 2,5) + A2 (x – 2,5)^2

и так далее для параметров B1, B2, C2, C3. (Они не должны пересекаться!) Для каждой из ваших тестовых пар x, y вы должны оценить xA, xB и xC из этих трех уравнений (очень легко, если у вас есть обратная явная форма x = function(y), в противном случае вы можете использовать квадратные формулы, вы можете даже использовать Solver), и теперь, если x

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

Сообщить о нарушении

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

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

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

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

Что необходимо - и это действительно конкретная помощь, которую я ищу, в отношении прикрепленного файла:

<р>1. В строке $3 определите минимальные данные НЕ МЕНЕЕ 133,7 (заранее заданное значение Y) и найдите соответствующее значение в строке $2

<р>2. Определите данные чуть ниже указанных выше идентифицированных данных и найдите соответствующее значение в строке $2

<р>3. С помощью линейной интерполяции вычислите значение X, соответствующее Y=133,7. Это должно быть НЕ МЕНЕЕ 5,27, предопределенное значение X

Учитывая характер кривых, одного этого достаточно, чтобы вернуть "A".

Аналогичная работа требуется между строками $2 и $4, а также между строками $4 и $6. Для строк $2 и $4 значение X, соответствующее Y=133,7, т. е. 5,27, должно быть НЕ МЕНЬШЕ значения x в строке $2 и НЕ БОЛЬШЕ значения x в строке $4. Это должно вернуть "B" и т. д.

Сообщить о нарушении

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

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