Надстройка Поиска решения в Microsoft Excel
Содержание
Программа MS Excel активно используется для различных математических и финансовых задач. Однако иногда пользователи сталкиваются с ситуациями, когда выполнить определенную вычислительную операцию с помощью стандартных формул не представляется возможным. В данном случае им может помочь «Поиск решения» в программе Excel — специальная надстройка, которая используется для решения уравнений и задач, связанных с оптимизацией. В данном материале рассмотрим, как работает данный инструмент на примере.
Как включить надстройку
Сразу же отметим, что плагин «Поиск решения» доступен не во всех версиях программы. Впервые надстройку добавили в Excel 2007 и 2010, однако старая версия этого инструмента отличается от современного. В нынешнем виде инструмент доступен в Экселе 2013 и более новых версиях софта. Однако несмотря на целый ряд преимуществ, надстройка довольно редко используется пользователями на практике. Это связано с более сложным для восприятия интерфейсом по сравнению с традиционными формулами и функциями.
Обычно «Поиск решения» используется тогда, когда необходимо выполнить некоторую вычислительную операцию, но при этом учитывать возможные ограничения. Инструмент работает с группами ячеек, меняя текущие значения или генерируя новые на основе заданных пользователем параметров. Прежде чем перейти к рассмотрению примера, выясним, как активировать эту надстройки.
Включение через параметры редактора
Если ранее вы не сталкивались с необходимостью использования надстроек в Эксель, возможно вам даже неизвестно, как они включаются в программе. С учетом того, что «Поиск решения» относится к числу встроенных инструментов программы, вам не придется ничего загружать на свой ПК. Необходимо только включить плагин, чтобы новые функции появились в панели MS Excel. Как включить надстройку через параметры:
- Откройте программу Excel вкладку «Файл». Далее через меню, которое находится слева, проследуйте в раздел «Параметры».
Переходим в параметры - В меню слева нажмите «Надстройки». Внизу открывшегося окна параметров напротив строки «Управление» выберите в списке «Надстройки Excel». После этого нажмите .
Переходим к надстройкам - В открывшемся окошке поставьте галочку напротив строчки «Поиск решения». После этого нажмите .
Активируем надстройку
Если надстройка «Поиск решения» присутствует в списке, но при этом не активируется — это указывает на то, что она не загружена на ПК. В данном случае на экране появится окно с предложением выполнить загрузку и установить плагин на устройство.
Активация через Панель разработчика
Управлять надстройками можно через инструмент, который расположен во вкладке «Разработчик». По умолчанию данная вкладка отключена, поэтому ее нужно включить в настройках. Для этого переходим в параметры Excel и в меню слева выбираем «Настроить ленту». В правой стороне окна находим строчку «Разработчик», ставим галочку напротив нее и нажимаем .
Выполнив это простое действие, остается только добавить надстройку «Поиск решений» в Excel. Для этого перейдите во вкладку «Разработчик» и нажмите «Надстройки». Поставьте отметку напротив нужного инструмента и подтвердите операцию нажатием .
Как пользоваться надстройкой «Поиск решения»
Мы выяснили ранее, как активировать надстройку в программе Excel. Однако теперь необходимо разобраться, как искать решение с ее помощью. Проще всего определить базовые принципы работы с данным инструментом на подробном примере. Пример использования данной надстройки будет рассмотрены ниже в данном материале.
Оформление исходной таблицы
Рассмотрим базовый пример ситуации, когда требуется найти решение без помощи стандартных формул. В нашей таблице приведен список сотрудников и данные об их заработной плате. По условию задачи, необходимо рассчитать размер премии для каждого специалиста из списка. При этом максимальная сумма для выплаты всем сотрудникам ограничена и составляет 50 000 руб.
Размер премии будет определяться на основе коэффициента. Он равнозначный для всех сотрудников. При умножении коэффициента на заработную плату мы получим значение премиальных выплат. На первый взгляд может показаться, что все просто и решить задачу получится с помощью обычных формул. Однако, как уже было отмечено выше, общая сумма на выплату премиальных средств ограничена. Поэтому задача состоит в том, чтобы найти такой коэффициент, при котором каждый получит дополнительную премию и при этом общая сумма выплат не будет превышать 50 000 рублей.
Ключевое значение при решении данной задачи имеют 2 параметра:
- Целевая ячейка. В нашем случае это диапазон ячеек, где будут записаны размеры премий.
- Искомая ячейка. Это коэффициент, на который умножается значение заработной платы с целью расчет премиальных.
На основании имеющихся данных уже можно сделать первое действие, чтобы найти решение. В ячейку, где записана общая сумма премиальных, вводится функция, которая связывается целевую и искомую ячейку. Ссылка на диапазон при этом должна быть абсолютной, поэтому выделяется специальным символом $.
Далее следует завершающий этап подготовки таблицы. Введенную формулу применяем к остальным целевым столбцам. Так как коэффициенты еще не установлены, значения в диапазоне будут нулевыми.
Применяем надстройку для поиска оптимального значения
Таблица готова к последующим расчетам. Для этого используют надстройку, которая была установлена ранее. Чтобы открыть ее, необходимо перейти во вкладку «Данные» и нажать «Поиск решения» в правой части Панели инструментов.
При запуске надстройки на экране открывается окно параметров. Здесь необходимо правильно задать условия для расчетных операций, чтобы сервис смог искать оптимальное решение для нашего конкретного случая. Общий алгоритм действий следующий:
- Сначала необходимо задать целевую функцию. Это та формула, которая отвечает за расчет премиальных выплат. В нашем примере функция находится в ячейке D Используем специальный символ $, чтобы создать неизменяемую ссылку на значение.
Вводим ссылку на целевую функцию - Ниже ищем параметр «До». Ставим отметку напротив строки «Значения» и вписываем в нее нашу предельную сумму для всех премиальных выплат — 50 000.
Устанавливаем предельное значение - На следующем этапе указываем адрес искомой ячейки. Это место, где будет записан коэффициент для расчета премиальных. Здесь также используем специальный символ $. После этого в поле «В соответствии с ограничениями» нажимаем .
Вводим адрес искомой ячейки - В первой строке указываем адрес искомой ячейки — клетка с коэффициентом. Далее нужно задать условие «>=», так как коэффициент не может быть отрицательным. Задав условие, нажимаем .
Задаем ограничение - После того как ограничение добавлено, программа вернется к окну основных параметров. Здесь можно убедиться в том, что ограничение действует — оно будет отображаться в соответствующем поле. После этого нажимаем .
Нажимаем найти решение - Если все условия и параметры заданы правильно, надстройка быстро решит задачу. На экране отобразится уведомление о том, что решение найдено. Пользователь может сохранить решение либо перейти к просмотру таблицы.
Сообщение о найденном решении
В результате программа нашла коэффициент, применение которого позволило рассчитать размер премии для каждого сотрудника с учетом ограничения в 50 000 рублей. Соответствующие значения целевой и искомой ячеек проставляются автоматически, так как ранее был использован маркер автозаполнения.
Подводим итоги
Надстройка «Поиск решения» в Excel — это полезный инструмент, который позволяет выполнять быстрые вычисления с целью поиска различных неизвестных значений. Данный способ расчетов имеет свои особенности, а потому лучше всего подходит для использования опытными пользователями. Для получения корректных результатов необходимо правильно задавать условия и параметры, что в отдельных случаях может оказаться довольно сложным, особенно если переменных достаточно много. Тем не менее данный инструмент может рассматриваться, как хорошая альтернатива стандартным формулам Майкрософт Эксель.
Загрузка...
