Как включить «Поиск Решения» в Excel: урок от Wi-Fi-гида

Всем привет! Сегодня в уроке мы разберем «Поиск решения» в программе Microsoft Excel. Это одна из самых полезных функций, которая позволяет находить нужные значения для отдельных математических, финансовых и производственных задач. Простыми словами, вы говорите Excel: «Вот итоговая ячейка, вот ячейки, которые можно менять, а вот ограничения». После этого программа сама подбирает такие значения, чтобы получить нужный результат.

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

Сразу коротко: «Поиск решения» находится на вкладке «Данные» в разделе «Анализ». Если кнопки нет, нужно включить надстройку через «Файл» – «Параметры» – «Надстройки» – «Надстройки Excel» – «Перейти» – «Поиск решения». В Excel для браузера стандартная настольная надстройка может быть недоступна или работать иначе, поэтому лучше использовать обычную программу Excel на компьютере.

ШАГ 1: Активация инструмента

Проблема в том, что сама функция «Поиск решения» по умолчанию часто выключена, поэтому вы не можете найти ее ни на панели задач, ни на панели управления программы. Но переживать не стоит, так как ее нужно просто включить в настройках Excel. Точнее, мы подключим встроенную надстройку. В большинстве обычных установок Microsoft Excel она уже есть в комплекте, просто не загружена. Иногда программа может попросить подтвердить установку компонента – в таком случае соглашаемся, ничего отдельно с сайтов скачивать не нужно.

ВНИМАНИЕ! Не скачивайте «Поиск решения» с непонятных сайтов. Официальная надстройка включается внутри Excel. Если в интернете вам предлагают отдельный EXE-файл, «ускоритель Excel» или неизвестный архив с надстройкой, лучше это не устанавливать.

Давайте посмотрим, как включить поиск решения в Excel.

1. В новых версиях Microsoft Excel и Microsoft 365 нажмите по надписи «Файл» в левом верхнем углу окна.

ПРИМЕЧАНИЕ! Если у вас старая версия Excel с круглой кнопкой Office в левом верхнем углу, нажимаем по этой кнопке и ищем параметры программы там.

2. Теперь находим раздел «Параметры».

3. В левом блоке находим «Надстройки» и теперь напротив «Управления» жмем по кнопке «Перейти», чтобы открыть список дополнительных пакетов. Убедитесь также, чтобы стояло значение «Надстройки Excel».

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

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

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

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

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

ШАГ 2: Знакомство с примером и создание таблицы

Итак, саму функцию мы подключили, но как же теперь с ней работать? Давайте разберемся на конкретном простом примере. Представим себе, что у нас есть компания, в которой работает ряд сотрудников. Ежемесячно они получают фиксированную заработную плату. Наступил декабрь, и скоро новый год. Начальник поручил нам выделить дополнительную премию к ЗП. Премия будет добавлена относительно определенного коэффициента, то есть заработная плата за месяц умножается на сам коэффициент.

Но есть проблема – бюджет компании не резиновый, поэтому на все премии начальник готов потратить не больше 40 000 рублей. Вот в таком случае нам и поможет функция «Поиск решения».

Посмотрите на табличку выше. У нас есть несколько данных, которыми мы будем оперировать через функцию:

• Целевая ячейка – это итоговая сумма всех премий. Именно ее Excel будет пытаться привести к нужному значению.
• Искомая ячейка – это коэффициент, на который будет умножаться ЗП. Именно это число нам и нужно будет найти.
• Ограничения – это правила, которые нельзя нарушать. В нашем примере коэффициент не должен быть отрицательным, а сумма премий должна уложиться в бюджет.

Простыми словами: целевая ячейка отвечает на вопрос «какой итог мне нужен?», изменяемая ячейка отвечает на вопрос «что Excel может менять?», а ограничения отвечают на вопрос «за какие границы выходить нельзя?».

Две эти ячейки должны быть связаны между собой формулами. Как вы уже поняли, мы будем использовать произведение ячейки ЗП и коэффициента. Для того чтобы коэффициент не изменялся при протягивании формулы вниз, установите знак доллара ($) перед буквой и цифрой:

=C11*$G$3

Здесь C11 – это зарплата конкретного сотрудника, а $G$3 – ячейка с коэффициентом. Знаки доллара делают ссылку абсолютной. Это значит, что при копировании формулы вниз Excel будет менять адрес зарплаты, но не будет сдвигать адрес коэффициента. Если убрать доллары, формула в следующих строках начнет ссылаться не туда, и расчет получится неправильным.

Теперь растяните формулу на все ячейки столбца «Премия». Посмотрите, чтобы адреса были правильными. Теперь переходим к следующей главе.

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

ШАГ 3: Использование функции и настройка

1. Переходим в «Данные» и в разделе «Анализ» нажимаем по нужной функции. Откроется дополнительное окно.

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

3. Теперь выбираем ее, нажав по ней левой кнопкой мыши.

4. Теперь ниже нам нужно установить само значение, то есть сколько компания выделила нам денег на премии. Можно поставить режим «Максимум» и добавить ограничение сверху, но так как работники работали хорошо, давайте распределим весь бюджет полностью. Поэтому устанавливаем конкретное значение 40 000 рублей.

5. Ниже указываем адрес ячейки, где будет расположен сам коэффициент. Опять же вы можете вписать его руками или выбрать из таблицы – вы уже знаете, как это можно сделать. После этого нам нужно «Добавить» ограничение.

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

>=0

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

Итоговая_сумма_премий <= 40000

В нашем примере мы и так устанавливаем целевую ячейку в конкретное значение 40 000, но в реальных задачах часто используют именно ограничения. Например, «расходы не больше бюджета», «количество товара не меньше нуля», «количество работников только целое число», «прибыль максимальная». Чем точнее вы опишете задачу, тем выше шанс, что Excel найдет нормальное решение.

7. Давайте еще зайдем в «Параметры».

8. В параметрах можно изменить некоторые настройки «Поиска решения». Например, вы можете изменить точность ограничения, ограничить время расчета или выбрать метод решения. В нашем простом примере нам хватит настроек по умолчанию. Жмем «Отмена».

Здесь есть три основных метода:

• Симплекс-метод – подходит для линейных задач, где формулы состоят из обычных сложений, вычитаний, умножений на коэффициенты и ограничений без сложных нелинейных зависимостей.
• ОПГ нелинейный или GRG Nonlinear – используется для гладких нелинейных задач, где есть степени, деление, проценты, сложные формулы, но без резких скачков.
• Эволюционный – нужен для более сложных задач, где могут быть целые числа, логические условия, нечеткие зависимости или несколько локальных вариантов решения.

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

9. Теперь жмем по кнопке «Найти решение».

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

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

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

Самая частая ошибка: целевая ячейка должна содержать формулу, а изменяемая ячейка должна реально влиять на эту формулу. Если итоговая ячейка никак не зависит от коэффициента, «Поиск решения» ничего нормального не найдет.

Когда нужен «Поиск решения», а когда хватит «Подбора параметра»

В Excel есть похожая функция – «Подбор параметра». Она проще и подходит, когда у вас есть одна формула, одно нужное значение и одна изменяемая ячейка. Например, вы хотите узнать, какая должна быть зарплата, чтобы премия стала ровно 30 000 рублей. В такой простой ситуации «Подбор параметра» может быть быстрее и понятнее.

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

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

Полезные советы перед запуском

Перед тем как запускать «Поиск решения», я советую проверить саму таблицу:

• В целевой ячейке должна быть формула, а не просто введенное число.
• Изменяемые ячейки должны участвовать в расчетах целевой ячейки.
• Все числа должны быть настоящими числами, а не текстом с пробелами, буквами или лишними символами.
• Ограничения должны быть логичными. Например, нельзя требовать одновременно «больше 100» и «меньше 50» для одной и той же ячейки.
• Перед расчетом лучше сохранить файл, чтобы можно было быстро вернуться к исходной версии.

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

Примеры задач, где пригодится «Поиск решения»

«Поиск решения» можно применять не только для премий. Вот несколько простых примеров:

• Бюджет – распределить деньги по статьям расходов так, чтобы не выйти за лимит.
• Производство – посчитать, сколько товаров выпускать, чтобы получить максимальную прибыль при ограничении материалов.
• Закупки – подобрать количество позиций так, чтобы уложиться в бюджет и закрыть минимальные потребности.
• Логистика – подобрать объемы поставок с учетом ограничений по складам и спросу.
• Расписание – найти допустимое распределение смен, если есть ограничения по часам и сотрудникам.

В таких задачах часто приходится умножать массивы чисел: цену на количество, ставку на часы, расход на объем. Для этого иногда удобна функция СУММПРОИЗВ. Если хотите глубже понять похожие расчеты, посмотрите урок про СУММПРОИЗВ в Excel.

Главное – не пытайтесь сразу решать огромную таблицу. Сначала соберите маленькую модель на 3-5 строках и проверьте, что Excel считает правильно. Когда логика заработает, можно расширять таблицу до реальной задачи.

Что делать, если решение не найдено

Иногда Excel пишет, что решение не найдено, ограничения не выполняются или расчет остановлен. Это не всегда значит, что программа сломалась. Чаще всего проблема в самой модели. Например, вы задали слишком жесткие ограничения или указали ячейку, которая не связана с итоговой формулой.

Проверьте по порядку:

1. Целевая ячейка содержит формулу?
2. Изменяемая ячейка реально влияет на целевую ячейку?
3. Нет ли в расчетах ошибок типа #ДЕЛ/0!, #ЗНАЧ! или пустых ссылок?
4. Не противоречат ли ограничения друг другу?
5. Правильный ли выбран метод решения?
6. Нет ли в числах лишних пробелов, букв, валютных знаков внутри ячейки или текстового формата?

Если задача сложная, попробуйте временно убрать часть ограничений и запустить расчет еще раз. Так можно понять, какое именно условие мешает найти решение. Потом ограничения добавляются обратно по одному. Это обычный прием: не нужно сразу пытаться чинить всю модель, проще найти одно проблемное место.

Частые вопросы

Где находится «Поиск решения» в Excel?
После подключения надстройки кнопка находится на вкладке «Данные» в группе «Анализ». Если кнопки нет, откройте «Файл» – «Параметры» – «Надстройки», выберите «Надстройки Excel», нажмите «Перейти» и поставьте галочку напротив «Поиск решения».

 

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

 

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

 

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

 

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

 

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

 

Можно ли использовать «Поиск решения» для целых чисел?
Да, для этого в ограничениях можно указать, что изменяемая ячейка должна быть целым числом. Это полезно, когда нельзя получить 2,5 сотрудника, 3,7 станка или 10,2 коробки товара. Но такие задачи обычно считаются сложнее, и Excel может искать решение дольше.

 

Нужно ли сохранять найденное решение?
Если результат вас устраивает, выбирайте «Сохранить найденное решение». Тогда Excel оставит подобранные значения в таблице. Если хотите вернуться к исходным данным, выбирайте возврат к начальным значениям или заранее сохраните копию файла перед запуском.