Функция в Excel: поиск решения
Наряду со множеством других возможностей, в Microsoft Excel есть одна малоизвестная, но очень полезная функция под названием “Поиск решения”. Несмотря на то, что найти и освоить ее, может быть, непросто, ее изучение и применение может помочь в решении огромного количества задач. Функция берет данные, перебирает их и выдает самое оптимальное решение из возможных. Итак, давайте разберемся, как именно работает поиск решения и попробуем применить данную функцию на практике
Как включить функцию “Поиск решения”
Несмотря на свою эффективность, функция “Поиск решения” не находится в первых рядах панели инструментов или контекстного меню. Многие пользователи, работающие в Excel годами, даже не подозревают о ее существовании. Дело в том, что по умолчанию она вообще отключена и для ее добавления на ленту нужно проделать следующие шаги:
- Открываем меню “Файл”, кликнув по соответствующему названию.
- Кликаем по разделу “Параметры”, который находится внизу вертикального перечня с левой стороны.
- Далее щелкаем по подразделу “Надстройки”. Здесь отображаются все надстройки программы, а внизу будет надпись “Управление”. Справа от нее представлено выпадающее меню, в котором должны быть выбраны “Надстройки Excel”, обычно уже установленные по умолчанию. Нажимаем кнопку “Перейти”.
Подготовительный этап
Добавить функцию на ленту программы – половина дела. Нужно еще понять принцип ее работы.
Итак, у нас есть данные про продаже товаров, представленные в табличном виде.
И перед нами стоит задача – назначить каждому товару скидку таким образом, чтобы сумма по всем скидкам составила 4,5 млн. рублей. Она должна отобразиться в отдельной ячейке, которая называется целевой. Ориентируясь на нее мы должны рассчитать остальные значения.
Наша задача – вычислить скидку, на которую будут умножены все суммы по продажам всех наименований. Она и будет найдена с помощью функции “Поиск решения”, а ячейка с этой скидкой будет называется искомой.
Данные ячейки (искомая и целевая) связываем вместе формулой, которую пишем в целевой ячейке следующим образом: =D13*$G$2, где ячейка D13 содержит итоговую сумму по продажам всех товаров, а ячейка $G$2 – абсолютные (неизменные) координаты искомой ячейки.
Применение функции и ее настройка
Формула готова. Теперь нужно применить саму функцию.
- Переключаемся во вкладку “Данные” и нажимаем кнопку “Поиск решения”.
- Откроются “Параметры”, где необходимо задать нужные настройки. В поле “Оптимизировать целевую функцию:” указываем адрес целевой ячейки, где планируется вывести сумму по всем скидкам. Можно прописать координаты вручную, либо выбрать из таблицы, для чего сначала кликаем по области ввода, затем – по нужной ячейке.
Заключение
Таким образом, функция “Поиск решения” в Эксель может помочь в решении определенных задач, которые достаточно сложно или невозможно решить простыми методами. Однако, проблема в использовании данного способа заключается в том, что по умолчанию данная функция скрыта в программе, из-за чего многие пользователи не догадываются о ее существовании. Также функция довольно трудна в освоении и использовании, но при ее должном изучении, она может принести значительную пользу и облегчить работу.
Использование поиска решений в Excel 2010 для решения сложных задач
Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.
«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.
Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»
Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».
После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке
Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.
Пример первый.
Допустим, что вы занимаете пост начальника крупного отдела производства и необходимо правильно распределить премии сотрудникам. Допустим, общая сумма премий составляет 100 000 рублей, и необходимо, чтобы премии были пропорциональны окладам.
То есть, сейчас нам необходимо подобрать правильный коэффициент пропорциональности, чтобы определить размер премии относительно оклада.
В первую очередь необходимо быстро составить (если ее еще нет) таблицу, где будут хранится исходные формулы и данные, согласно которым и можно будет получить желаемый результат. Для нас этот результат – суммарная величина премии. А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично. В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).
Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.
Под «1» обозначена наша целевая ячейка. Она может быть только одна.
«2» — это возможные варианты оптимизации. Всего можно выбрать «Максимальное», «Минимальное» или «Конкретное» возможные значения. И если вам необходимо именно конкретное значение, то его нужно указать в соответствующей графе.
«3» — изменяемых ячеек может быть несколько (целый диапазон или же отдельно указанные адреса). Ведь именно с ними и будет работать Excel, перебирая варианты так, чтобы получилось значение, заданное в целевой ячейке.
«4» — Если понадобиться задать ограничения, то стоит воспользоваться кнопкой «Добавить», но мы это рассмотрим чуть позже.
«5» — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.
Но теперь вернемся к возможности изменять наше задание, воспользовавшись кнопкой «Добавить». Данный этап является довольно ответственным (не менее чем построение формул), поскольку именно ограничение позволяют получить правильный результат на выходе. Здесь все сделано максимально удобно, так что задать их вы сможете не только для всего диапазона сразу, но и для определенных ячеек.
Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», « 3 досок, а модель «В» — на 1 м 3 больше (то есть – 4). От своих поставщиков вы за неделю получаете максимум 1700 м 3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.
Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полочка «А» дает 60 рублей прибыли, а «В» — 120?
Поскольку порядок действия известен, то начинаем создавать необходимую нам таблицу с данными и формулами. Расположение ячеек, как и ранее, вы можете установить на свое усмотрение. Или же воспользоваться нашим
Любым удобным способом запускаем наш «Поиск решений», вводим данные, производим настройку.
Итак, рассмотрим то, что мы имеем. В целевой ячейке F7 содержится формула, которая и рассчитает прибыль. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).
Конечно, в этом случае можно было не указывать адреса ячеек, а напрямую прописать необходимые цифровые значения, однако если использовать адреса, то изменения ограничений можно будет проводить и в таблице, что поможет рассчитывать прибыль этого предприятия в будущем, при смене исходных данных.
Активируем программу, и она подготавливает решение.
Впрочем, это не единственное решение и у вас вполне может выскочить другой результат. Это может произойти даже в том случае, если все данные были указаны верно и ошибок в формулах тоже не было
Да. Это может произойти даже в том случае, если мы сказали программе искать целое число. И если это вдруг произошло, то необходимо просто провести дополнительную настройку «Поиска решений». Открываем окно «Поиска решений» и входим в «Параметры».
Наш верхний параметр отвечает за точность. Чем он меньше, тем выше точность и в нашем случае это значительно повышает шансы получить целое число. Второй параметр («Игнорировать целочисленные ограничения») и дает ответ на вопрос, как мы смогли получить такой ответ с тем, что в запросе явно указали целое число. «Поиск решений» просто проигнорировал это ограничение в связи с тем, что так ему сказали расширенные настройки.
Так что будьте предельно внимательны в будущем.
Третий и, пожалуй, последний пример. Попробуем минимизировать затраты транспортной компании используя поиск решений в Excel 2010.
Итак, строительная компания дает заказ на перевозку песка, который берется от 3 поставщиков (карьеров). Его необходимо доставить 5 разным потребителям (которыми выступают строительные площадки). Стоимость доставки груза включена в себестоимость объекта, так что наша задача обеспечить доставку груза на стройплощадки с минимальными затратами.
Мы имеем – запас песка в карьере, потребность стройплощадок в песке, затрату на транспортировку «поставщик-потребитель».
Необходимо найти схему оптимальной перевозки груза (куда и откуда), при которой общая затрата на перевозку была бы минимальной.
Серые ячейки нашей таблицы содержат формулы суммы по столбцам и строкам, а целевая ячейка – формула для общего подсчета затраты на доставку груза. Запускаем наш «Поиск решения» и вносим необходимые настройки
После этого приступаем к поиску решения этой задачки
Впрочем, не будем забывать, что достаточно часто транспортные задачи могут быть усложнены некоторыми дополнительными ограничителями. Допустим, возникло осложнение на дороге и теперь из карьера 2 просто технически невозможно доставить груз на стройплощадку 3. Чтобы учесть это, необходимо просто дописать дополнительное ограничение «$D$13=0». И если теперь запустить программу, то результат будет иным
Напоследок осталось сказать только о выборе метода решения. И если задачка действительно очень сложная, то чтобы получить необходимый результат, скорее всего, понадобиться подобрать необходимый метод решения.
Вот и все по данному вопросу.
Мы выполнили поиск решений в Excel 2010 — для решения сложных задач
