Microsoft Excel развивалась годами, вводя или улучшая новые функции и формулы, чтобы упростить и упростить управление данными. Это связано с тем, что мы можем столкнуться с большими объемами как числовых, так и текстовых данных или дат, когда в случае сбоя любого из них или неправильной настройки возникает ряд ошибок, которые могут привести к головной боли.
Но Microsoft Excel выходит далеко за рамки функций и формул. Для многих пользователей не является осознанным фактом, что Excel будет интегрировать практические и правдивые решения, с помощью которых можно будет выполнить гипотезу, основанную на функции введенных данных. Это что-то действительно полезное для всего, что нам нужно, чтобы быть уверенным в том, сколько мы можем потратить, продать или управлять определенным образом.
Это достигается благодаря функции Solver, которую мы можем установить в Microsoft Excel 2016 или Excel 2019, и будет очень полезен для задач такого типа.
Solvetic подробно объяснит, как использовать Solver в Excel 2019 и, таким образом, достичь еще одной точки администрирования и контроля в этом ценном приложении пакета Office. Этот процесс похож в Microsoft Excel 2016.
Что такое Солвер
Solver - это надстройка, разработанная Microsoft как надстройка для Excel, с помощью которой можно будет выполнить анализ и если (что-если). Когда мы реализуем и используем Солвер, будет возможно определить оптимальное значение, минимальное или максимальное, для формулы в ячейке. Эта ячейка (называемая целевой ячейкой) подвержена ограничениям в значениях других ячеек формулы в электронной таблице.
Основное назначение Solver - моделирование и оптимизация различных бизнес и инженерных моделей. Солвер работает с группой ячеек, называемых ячейками переменных решения, которые используются для вычисления формул в целевых ячейках.
Солвер отвечает за корректировку значений ячеек переменных решения так, чтобы они соответствовали пределам ячеек ограничения и, наконец, генерировали ожидаемый результат в целевой ячейке. В основном Solver будет отличным союзником для определения максимального или минимального значения ячейки путем изменения значения других ячеек. Солвер состоит из трех элементов:
- Переменные ячейки
- Запретная клетка
- Целевая ячейка
1. Как активировать Солвер в Excel 2016 или 2019
Шаг 1
Первым шагом, который мы должны выполнить, будет активация надстройки Solver в Microsoft Excel, для этого перейдем в меню «Файл», где мы увидим следующее:
Шаг 2
Там мы нажимаем на категорию «Опции» и в окне переходим в раздел «Дополнения», а на центральной панели выбираем «Солвер»:
Шаг 3
Внизу мы нажмем кнопку «Перейти», расположенную в поле «Управление», а во всплывающем окне мы активируем поле «Солвер»:
Шаг 4
Нажмите OK, чтобы применить изменения. Теперь в меню «Данные» группы «Анализ» мы найдем опцию «Солвер»:
2. Используйте Солвер в Excel 2016 или 2019
Шаг 1
Для использования Solver в Excel 2019 у нас есть следующая информация:
- Список систем или приложений
- Колонка с ценой каждого из них
- Список единиц каждого предмета
- Общая стоимость
Шаг 2
Теперь с этими данными в столбце Общая стоимость мы умножим цену за единицу на следующие формулы:
= B7 * C7$config[ads_text5] not found
примечание
Мы можем перетащить эту формулу во все нижние ячейки, чтобы скопировать формулы.
Шаг 3
Теперь мы добавим новую строку под названием «Общий бюджет», в которую добавим весь диапазон столбца «Всего» со следующими формулами:
= СУММА (D3: D8)
Шаг 4
Теперь мы идем в меню «Данные», «Группа анализа» и там нажимаем «Солвер», и будет отображаться следующий мастер:
Шаг 5
Там мы укажем целевую ячейку, поле «Установить цель» и в этом случае выберем ячейку C11. После этого можно будет настроить поле цели, «Максимум», максимальное, минимальное или конкретное значение в зависимости от критериев результатов, для этого примера мы активируем поле «Максимум». Следующим шагом является установление переменных ячеек, для этого мы идем в поле «Изменение ячеек переменной» и там выбираем желаемый диапазон, который будет в этом случае столбцом цены за единицу доллара США:
Шаг 6
Затем пришло время определить ограничения, для этого примера мы предполагаем, что у нас есть ограничение в 10 000 долларов США для покупок, чтобы установить это ограничение, мы нажимаем кнопку «Добавить» в поле «Подлежит ограничениям» и во всплывающем окне. Мы определяем следующее:
- В поле «Ссылка на ячейку» введите ячейку общего бюджета.
- Мы назначаем ограничение Меньше или равно (<=).
- В поле Restriction мы назначаем максимальное значение для использования в этом случае 10000.
Шаг 7
Нажмите «Добавить», чтобы применить изменения. Теперь следующим ограничением будет то, что и системы, и приложения будут проданы полностью, там мы снова нажимаем кнопку «Добавить», и на этот раз мы выбираем диапазон единиц и выбираем значение «int (integer)»:
примечание
Этот последний параметр не является обязательным.
Шаг 8
Нажмите «Добавить» и, наконец, мы определим минимальный объем каждой системы или приложения для использования, для этого у нас есть следующее:
- 3 Офис 2019
- 2 Windows 10
- 1 macOS Мохаве
- 1 Adobe Suite
- 2 Windows Server
- 2 Camtasia
Шаг 9
Для этого нажмите «Добавить» и выполните следующие действия:
- В поле «Ссылка на ячейку» мы вводим ячейку для каждой системы или приложения в столбце «Единицы», например, для Office 2019 это будет C3, для Windows 10 это будет C4 и т. Д.
- Мы присваиваем параметр, меньший или равный (<=), и назначаем максимальную сумму в поле «Ограничение».
Шаг 7
Мы повторяем этот процесс для каждого элемента. Как только этот процесс будет завершен, мы увидим нечто похожее на это:
примечание
Доступные варианты ограничения:
- <= (меньше или равно
- =: так же, как
- > =: больше или равно
- int: integer
- bin: двоичный
- Разница: разница
Шаг 8
Как только это будет определено, нажмите кнопку «Решить», чтобы выполнить анализ, и появится следующее окно:
Шаг 9
Там у нас есть следующие варианты:
- Если мы хотим сохранить значения решения в электронной таблице, мы щелкнем «Keep Solver Solution».
- Если мы хотим восстановить исходные значения, прежде чем нажать «Разрешить», мы нажмем «Восстановить исходные значения».
- Чтобы прервать процесс разрешения, мы нажимаем клавишу Esc, Excel обновит электронную таблицу последними значениями, найденными для ячеек переменной решения.
- Чтобы создать отчет на основе решения после того, как Солвер найдет решение, мы выбираем тип отчета в поле Отчеты и нажимаем ОК. Отчет создается в новой электронной таблице книги. Если Солвер не найдет решение, опция создания отчета будет недоступна.
- Чтобы сохранить значения ячейки переменной решения в качестве сценария для последующего использования, необходимо щелкнуть «Сохранить сценарий» в диалоговом окне «Результаты решателя», а затем ввести имя для сценария в поле «Имя сценария».
Шаг 10
Для этого случая мы выбираем опцию «Keep Solver Solution» и нажимаем OK, чтобы увидеть результаты:
Шаг 11
Как мы видим, Solver автоматически анализирует максимальную сумму на основе выбранных критериев. Давайте посмотрим еще один пример того, как Solver полезен для всего процесса анализа. В этом случае у нас есть следующие данные:
Шаг 12
Там у нас есть следующая информация:
- Цены на такие предметы, как процессоры, твердотельные накопители и оперативная память.
- Промежуточный итог каждой позиции был определен путем умножения количества на цену за единицу.
- Мы назначили общий объем продаж, добавив все промежуточные итоги.
- Ограничения были наложены на максимальный общий объем продаж, максимальное количество каждого товара и максимум внутренних устройств (дисков и памяти).
- В заключительной части мы добавили сумму всех элементов, которые будет рассчитывать Солвер, а также только фильтр внутренних элементов.
Шаг 13
Как и в предыдущем пункте, мы переходим в меню «Данные» и в группе «Анализ» выбираем Солвер, и там мы определяем следующие параметры:
- В поле «Установить цель» введите нужную ячейку, которая в этом случае F5 (Всего продаж).
- В поле «Изменение переменных ячеек» мы вводим каждую ячейку, назначенную для промежуточных итогов.
- В ограничения добавляем следующее.
Шаг 14
В области ограничений мы используем следующие параметры для понимания операции:
- $ B $ 13 <= $ F $ 14: там мы указываем, что объем продаваемой памяти должен быть меньше или равен количеству, указанному в ячейке F14 (максимальный объем ОЗУ).
- $ B $ 9 <= $ F $ 13: там мы указываем, что количество продаваемых дисков должно быть меньше или равно количеству, указанному в ячейке F13 (максимальное количество дисков SSD).
- $ F $ 18 <= $ F $ 11: там мы указываем, что общее количество предметов, подлежащих продаже, должно быть меньше или равно количеству, указанному в ячейке F11 (максимальное количество предметов).
- $ F $ 19 <= $ F $ 15: там мы указываем, что количество проданных внутренних предметов должно быть меньше или равно количеству, указанному в ячейке F15 (максимальное количество внутренних предметов).
Шаг 15
Нажмите «Решить», и Солвер будет отвечать за анализ, который при правильном выводит следующее сообщение:
Шаг 16
Там мы можем выбрать, хотим ли мы использовать тип отчета. Нажмите ОК, и мы увидим анализ, выполненный Солвером в Excel:
Шаг 17
Если мы решили использовать параметр отчета, он будет доступен на отдельном листе, и его формат будет следующим:
Шаг 18
У Солвера есть следующие методы разрешения:
GRG нелинейный
Этот тип метода используется для нелинейных задач, то есть когда хотя бы одно из ограничений является однородной нелинейной функцией переменных решения.
LP Simplex
Он основан на алгоритме Симплекс, разработанном американским математиком Джорджем Данцигом, этот метод используется для решения задач линейного программирования, там математические модели характеризуются линейными отношениями, то есть состоят из единственной цели, представленной линейным уравнением. который должен быть максимальным или минимизированным.
эволюционный
Он используется для решения наиболее сложного типа задач оптимизации, поскольку некоторые функции могут быть прерывистыми, и таким образом будет сложнее определить направление, в котором функция увеличивается или уменьшается.
Шаг 19
Используя любой из этих методов, мы видим, что перед ним находится кнопка «Параметры», которая позволяет нам настраивать ее переменные так, как мы считаем нужным:
Шаг 20
После выполнения решения через Солвер можно будет сохранить указанный проект или загрузить уже сохраненный, для этого мы нажимаем кнопку «Загрузить / Сохранить»:
Шаг 21
Появится следующее окно, в котором мы определяем диапазон с моделью Солвера для сохранения. Нажмите Сохранить, чтобы применить изменения.
Таким образом, мы увидели, что Solver - это более чем практическое решение для анализа и прогнозирования данных, которое будет очень необходимо для будущего управления и администрирования.
СТАТЬИ