В ходе поиска не удалось найти допустимого решения excel
Мощным средством анализа данных Excel является надстройка Solver (Поиск решения). С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета.
Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года.
Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:
- количество неизвестных (decision variable) – 200;
- количество формульных ограничений (explicit constraint) на неизвестные – 100;
- количество предельных условий (simple constraint) на неизвестные – 400.
Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3).
Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса.
По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.
В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).
Процедура поиска решения
1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.
2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду:
- В Excel 2007 Данные/Анализ/Поиск решения;
- В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки.
3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, — Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа.
4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата.
5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.
6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)
7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.
8. Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения).
9. Щелкните на кнопке ОК.
Параметры средства Поиск решения
Максимальное время - служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.
В этом руководстве описывается, как можно быстро решить проблему, при которой функция Excel ВПР() показывает пользователю ошибку в разных версиях программы электронных таблиц. Также будут описаны ошибки, получившие наибольшее распространение, а также будут приведены рекомендации, как можно обойти ограничения этой функции.
Раньше вы могли уже читать о том, для чего предназначена функция ВПР, и где ее можно применять. Особенно, если вы до этого активно интересовались функциями программы электронных таблиц Excel. В таком случае вы уже должны стать экспертом в этой области.
Но даже если вы слышали об этой функции, вы все равно можете столкнуться со сложностями. По разным причинам эта функция считается самой сложной в программе электронных таблиц. Для ее использования есть множество ограничений, а также незаметных с первого взгляда нюансов, которые являются источником большого количества проблем и ошибок.
В формуле = ВПР эта ошибка расшифровывается, как «Нет данных». Простыми словами, у электронной таблицы не получается отыскать значение, которое необходимо пользователю. Есть множество причин, по которым эта проблема может появляться.
Неправильный ввод искомого значения
Наиболее частая причина возникновения этой ошибки – ввод значения с опечаткой. Например, случайно была написана буква вместо цифры. Особенно часто эта ошибка появляется, если обрабатываются огромные массивы данных.
Если ищется приближенное соответствие
- Если значение, которое необходимо найти в конкретном диапазоне, меньше, чем самое крошечное в анализируемой совокупности данных.
- Если перед введением функции пользователь не упорядочил по возрастанию связанную с ней колонку.
При поиске точного соответствия введенному запросу
Если значение, которое пользователь пытается найти, ищется с помощью формулы, и найти его не удалось, это тоже может быть причиной возникновения этой ошибки.
На данный момент столбец не является крайним левым
Наиболее существенным ограничением при использовании формулы ВПР – невозможность применить ее к столбцу, который не является крайним левым. Обычно пользователь забывает об этом, и в результате формула выдает описанную выше ошибку.
Преодоление этой трудности заключается в следующем: если по какой-то причине не получается переместить столбец влево, необходимо использовать сразу две функции Excel: ИНДЕКС(), ПОИСКПОЗ().
Числовые значения отформатированы, как текстовые
Это также часто встречаемый источник проблем с формулой ВПР() . Нередко пользователь может не замечать, что числовые значения отформатированы как текстовые. Часто такая проблема может встретиться, если информация копируется из других источников.
Еще одна причина подобной ошибки – пользователь забыл о том, что он поставил апостроф перед числом, чтобы сохранить ноль, который находится перед значением. Например, может быть так, как показано на следующей картинке.
Еще одна причина ошибки – формат может быть обозначен, как «Общий». Определить это можно по расположению значения внутри ячейки. Если они выравниваются по левому краю, это означает, что формат выбран как «Общий».
Если эта ошибка вызвана несколькими ячейками с числовыми значениями, их необходимо выделить, после чего нужно кликнуть по соответствующей области правой кнопкой мыши. В ответ на это действие появится контекстное меню, в котором нужно выбрать опцию «Формат ячеек», далее нужно будет нажать на «Число» и выбрать числовой формат. Последнее действие – нажатие кнопки «ОК».
Если стоит пробел в начале или в конце содержимого
Причина возникновения этой ошибки наименее заметна. Если таблица существенно большая, тяжело увидеть, в каких клетках располагаются пробелы. Особенно если часть ячеек оказывается за пределами экрана.
Решение номер 1: Если пробелы находятся в таблице, к которой применяется функция ВПР
Если в основной таблице обнаружены пробелы, можно применить функцию СЖПРОБЕЛЫ в аргументе «Искомое значение». Значительно проще это продемонстрировать на примере.
=ВПР(СЖПРОБЕЛЫ($F2),$A$2:$C$10,3,FALSE)
Решение номер 2: Если лишние пробелы находятся в столбце или таблице поиска
В таком случае не получится легко предотвратить ошибку. Здесь необходимо использовать несколько функций: ИНДЕКС(), ПОИСКПОЗ(), СЖПРОБЕЛЫ().
В результате получится формула массива, для правильного ввода которой нужно нажать на комбинацию клавиш Ctrl+Shift+Enter.
В качестве альтернативного способа решения этой проблемы можно воспользоваться дополнением «Trim Spaces for Excel», которое позволяет убрать ненужные пробелы в формулах как в главной таблице, так и в таблице поиска. Это бесплатный инструмент, который можно скачать по .
В целом, Excel отображает эту ошибку, если в формуле используется неправильный тип данных. Выделяется несколько причин этой ошибки.
Причина 1: значение, которое ищется, содержит больше 255 знаков
Чтобы решить эту проблему, необходимо использовать связку формул ИНДЕКС()+ПОИСКПОЗ() . Вот пример формулы, демонстрирующий на практике реализацию этой задачи:
Причина 2: не прописан полный путь к рабочей книге, используемой для поиска
При указании данных из другого файла, необходимо ввести полный путь к нему. Проще говоря, необходимо указать как его имя, так и расширение, после чего ввести имя листа и не забыть про восклицательный знак. Лучше всего это все заключить в апострофы для предотвращения возникновения ошибок из-за наличия пробелов в папке к файлу или в самой книге Excel.
Вот как эта формула выглядит:
=ВПР(искомое_значение;'[имя_книги]имя_листа’!таблица;номер_столбца;ЛОЖЬ)
А вот пример использования этой формулы на практике:
=ВПР($A$3;'[New Price.xls]Sheet2′!$B:$C;3;ЛОЖЬ)
Здесь программа будет искать содержимое ячейки A3 столбца c листа Sheet2 файла «NewPrice». Данные формула будет извлекать из столбца C.
Причина 3: пользователь ввел значение меньше 1 в аргумент «Номер столбца»
Тяжело вообразить, что кто-то может вводить для обозначения столбца значение меньше единицы. Но такая ситуация бывает, если в аргументе прописана переменная, значение которой вычисляется другой функцией Excel.
Почему еще может не работать функция ВПР?
Функция ВПР() имеет довольно сложный синтаксис. Но не только он является причиной сложностей в работе с этой формулой. В ходе пользования программой может появиться множество неприятностей даже в, казалось бы, простых случаях. Далее приводятся самые распространенные ситуации, когда ВПР() может выдавать ошибку. Также описываются ограничения, которые нужно учитывать.
Нечувствительность к регистру
Эта функция не может понять, где большая буква, а где – маленькая. Поэтому в наборе данных, где есть аналогичные за исключением регистра символов элементы, она вернет первый попавшийся, без учета регистра.
Чтобы решить эту проблему, можно использовать другую функцию Excel, которая может искать нужное значение в столбце (ИНДЕКС(), ПОИСКПОЗ(), ПРОСМОТР() и другие) вместе с СОВПАД(). Последняя функция может различать регистр.
Возвращение первого найденного значения
Кроме использования другой функции для поиска значения с учетом регистра, можно использовать другую формулу, если точно известно, какое по очередности значение нужно найти. Для этого необходимо использовать функции ИНДЕКС(), НАИМЕНЬШИЙ() и СТРОКА(). Так можно будет выбирать 2, 3, 4 или любое другое требуемое значение.
Новая колонка была вставлена в таблицу или убрана из таблицы
К сожалению, формула ВПР() перестает работать при каждом добавлении в таблицу столбца. Все потому, что ее синтаксис требует введения всего массива ячеек, в которых осуществляется поиск информации. Конечно, ситуация меняется, если туда вставляется столбец.
Здесь также нужно использовать функции ИНДЕКС() и ПОИСКПОЗ(). Они позволяют указать не массив всего диапазона, а лишь требуемые столбцы, и поэтому можно редактировать все остальные без необходимости обновлять связанные с ними формулы.
Искажение ссылок на ячейки при копировании функции
Вам сразу стало понятно, в чем проблема, верно? Решить ее просто: используя абсолютные ссылки на те ячейки, которые нужно оставить стабильными при копировании функции. Для этого необходимо перед названием столбца или строки поставить значок доллара ($). Например, прописать диапазон таким образом: $A$2:$C$100. Более простой вариант: $A:$C. С помощью клавиши F4 можно оперативно изменять тип адреса ячейки.
Обработка ошибок при использовании функции ВПР
Использование функции ЕСЛИОШИБКА
Для этой формулы характерен простой синтаксис:
ЕСЛИОШИБКА(значение; значение_если_ошибка)
Таким образом, на месте первого аргумента вставляется значение, которое тестируется на наличие ошибки, а на месте второго необходимо прописать текст или число, выдаваемые при возникновении ошибки.
Вот пример использования функции на практике:
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);»»)
В этом случае будет возвращаться пустая ячейка, если обнаруживается ошибка.
Можно в кавычки написать любую другую фразу. Например, ту, которая показана на скриншоте.
Использование функции ЕОШИБКА
Описанная выше функция впервые появилась в Excel 2007 версии, поэтому, если используются более старые версии программы (например, если компьютер имеет небольшую производительность, но на нем нужно обрабатывать большие объемы данных), нужно использовать такую формулу:
На практике формула будет выглядеть следующим образом:
Только недавно мне помогли с аналогичной проблемой, и вот опять возникает похожая, которая почему-то не решается предыдущим способом.
У меня есть таблица. В ней отдельно оптимизируются ячейки в двух столбцах. На изменяемые ячейки наложены два ограничения на минимум и максимум (для всех ячеек одного столбца ограничения идентичны), при этом имеются зависящие от них ячейки, которые имеют ровно аналогичные ограничения. Для решения задачи используется макрос.
В результате поиска решений для изменяемой ячейки берется нижнее значение допустимого диапазона, что не соответствует качеству оптимизации. Зависящая от нее ячейка выходит за пределы ограничений на нее наложенных. Оптимизируемая функция оптимизируется неверно.
Прошу у вас, друзья, помощи в решении данной проблемы.
Только недавно мне помогли с аналогичной проблемой, и вот опять возникает похожая, которая почему-то не решается предыдущим способом.
У меня есть таблица. В ней отдельно оптимизируются ячейки в двух столбцах. На изменяемые ячейки наложены два ограничения на минимум и максимум (для всех ячеек одного столбца ограничения идентичны), при этом имеются зависящие от них ячейки, которые имеют ровно аналогичные ограничения. Для решения задачи используется макрос.
В результате поиска решений для изменяемой ячейки берется нижнее значение допустимого диапазона, что не соответствует качеству оптимизации. Зависящая от нее ячейка выходит за пределы ограничений на нее наложенных. Оптимизируемая функция оптимизируется неверно.
Прошу у вас, друзья, помощи в решении данной проблемы. kazar
У меня есть таблица. В ней отдельно оптимизируются ячейки в двух столбцах. На изменяемые ячейки наложены два ограничения на минимум и максимум (для всех ячеек одного столбца ограничения идентичны), при этом имеются зависящие от них ячейки, которые имеют ровно аналогичные ограничения. Для решения задачи используется макрос.
В результате поиска решений для изменяемой ячейки берется нижнее значение допустимого диапазона, что не соответствует качеству оптимизации. Зависящая от нее ячейка выходит за пределы ограничений на нее наложенных. Оптимизируемая функция оптимизируется неверно.
Прошу у вас, друзья, помощи в решении данной проблемы. Автор - kazar
Дата добавления - 23.05.2019 в 18:15
Задав все необходимые параметры поиска решения, вы можете приступать к вычислениям. Для этого в Excel 2010 щелкните кнопку Найти решение, а в Excel предыдущих версии — кнопку Выполнить. На листе появятся результаты вычислений и одновременно — окно Результаты поиска решения с сообщением о том, найдено решение или нет (рис. 1.28 или 1.29). (Для больших моделей процесс оптимизации может длиться достаточно долго. В этом случае в строке состояния можно увидеть информацию о ходе вычислений: выполненное число итераций и текущее значение целевой функции.)
Решение найдено. Все ограничения и условия зптимальности выполнены. | Отчеты |
Результаты | |
0Сохранить найденное решение | Устойчивость |
Пределы | |
ОВосстановить исходные значения |
1~1Вернуться в диалоговое окно параметров
Решение найдено. Все ограничения и условия оптимальности выполнены.
Если используется модуль ОПГ, то найдено по крайней мере локально оптимальное решение. Если используется модуль поиска решений линейных задач симплекс-методом, то найдено глобально оптимальное решение.
Результаты поиска решения
Решение найдено. Eice ограничения и условия оптимальности выполнены.
(S) Сохранить найденное решение © Восстановить исходные значения
1.73.Сохранить найденное решение — для сохранения на листе найденного решения.
1.74.Восстановить исходные значения — для восстановления исходного вида листа.
1.75.Вернуться в диалоговое окно параметров (только в Excel 2010) — чтобы вернуться в окно поиска решения, изменить параметры и повторить вычисления.
1.76.Отчеты со (только в Excel 2010) — для выдачи отчетов в компактном виде. Чтобы отобразить подробную информацию в таких отчетах нужно нажать кнопку (+), а чтобы скрыть — кнопку (-).
Если решение найти не удалось, то в Excel 2003 и 2007 отчеты недоступны. В Excel 2010 в этом случае можно выбрать отчеты другого типа, помогающие выяснить, где допущена ошибка.
В ходе поиска не удалось найти допустимого решения.
0 Сохратггь найденное решение ОВосстановить исходные значения
Допустимость Границы допустимости
П Вернуться в диалоговое окно параметров □ Ответы со
В ходе поиска не удалось найти допустимого решения.
В коде поиска решения не удается найти точку, для которой выполняются все ограничения.
Результаты поиска решения
Условия линейности, необходимые для этой
линейной оптимизации, не выполнены. Отчеты
0 Сохранить найденное решение ОВосстановить исходные значения
1.77.Вернуться в диалоговое окно параметров
Условия линейности, необходимые для этой линейной а оптимизации, не выполнены.
У Создайте отчет о линейности, чтобы выяснить, где затруднение, или « переключитесь на модуль ОПГ.
§ Решение найдено. Все ограничения и условия оптимальности выполнены.
Все ограничения соблюдены с установленной точностью и найдено заданное значение целевой ячейки.
§ Поиск свелся к текущему решению. Все ограничения выполнены.
Относительное изменение значения в целевой ячейке за последние пять итераций стало меньше установленного значения параметра Сходимость в диалоговом окне Параметры поиска решения. Чтобы найти более точное решение, установите меньшее значение параметра Сходимость, но это займет больше времени.
§ Поиск не может улучшить текущее решение. Все ограничения выполнены.
В процессе поиска решения нельзя найти такой набор значений влияющих ячеек, который был бы лучше текущего решения. Приблизительное решение найдено, но либо дальнейшее уточнение невозможно, либо заданная погрешность слишком высока. Измените погрешность на меньшее число и запустите процедуру поиска решения снова.
§ Поиск остановлен (истекло заданное на поиск время). Время, отпущенное на решение задачи, исчерпано, но достичь удовлетворительного решения не удалось. Чтобы при следующем запуске процедуры поиска решения не повторять выполненные вычисления, установите переключатель Сохранить найденное решение или Сохранить сценарий.
§ Поиск остановлен (достигнуто максимальное число итераций). Произведено разрешенное число итераций, но достичь удовлетворительного решения не удалось. Увеличение числа итераций может помочь, однако следует рассмотреть результаты, чтобы понять причины остановки. Чтобы при следующем запуске процедуры поиска решения не повторять выполненные вычисления, установите переключатель Сохранить найденное решениеили нажмите кнопку Сохранить сценарий.
§ Значения целевой ячейки не сходятся. Значение целевой ячейки неограниченно увеличивается (или уменьшается), даже если все ограничения соблюдены. Возможно, следует в задаче снять одно ограничение или сразу несколько. Изучите процесс расхождения решения, проверьте ограничения и запустите задачу снова.
§ Поиск не может найти подходящего решения. В процессе поиска решения нельзя сделать итерацию, которая удовлетворяла бы всем ограничениям при заданной точности. Вероятно, ограничения противоречивы. Исследуйте лист на предмет возможных ошибок в формулах ограничений или в выборе ограничений.
§ Поиск остановлен по требованию пользователя. Нажата кнопка Стоп в диалоговом окне Текущее состояние поиска решения после прерывания поиска решения в процессе выполнения итераций.
§ При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения. При пересчете значений ячеек обнаружена ошибка в одной формуле или в нескольких сразу. Найдите целевую ячейку или ячейку ограничения, порождающие ошибку, и измените их формулы так, чтобы они возвращали подходящее числовое значение.
Набрано неверное имя или формула в окне Добавить ограничение или окне Изменить ограничение или в поле Ограничение были заданы целое или двоичное ограничение. Чтобы ограничить значения ячейки множеством целых чисел, выберите оператор целого ограничения в списке условных операторов. Чтобы установить двоичное ограничение, выберите оператор для двоичного ограничения.
§ Мало памяти для решения задачи. Система не смогла выделить память, необходимую для поиска решения. Закройте некоторые файлы или приложения, и попытайтесь снова выполнить процедуру поиска решения.
§ Другой экземпляр Excel использует SOLVER.DLL. Запущено несколько копий Microsoft Excel, в одном из которых используется файл Solver.dll.
§ Оптимальное решение не найдено.
Поиск решения может остановиться до достижения оптимального решения по следующим причинам:
· Пользователь прервал процесс поиска.
· Команда Показывать результаты итераций в диалоговом окне Параметры поиска решения выбрана перед Выполнить.
· Пользователь нажал кнопку Стоп в режиме пошагового выполнения итераций, по истечении времени, отведенного на работу процедуры, или после выполнения заданного числа итераций.
· Установлен флажок Линейная модель в диалоговом окне Параметры поиска решения, в то время как решаемая задача не линейна.
· Значение, заданное в поле Установить диалогового окна Поиск решения, неограниченно увеличивается или уменьшается.
· Необходимо изменить значения полей Максимальное время или Итерации в диалоговом окне Параметры поиска решения.
· В случае задач, значения в которых ограничены множеством целых чисел, необходимо уменьшить значение в поле Допустимое отклонение диалогового окна Параметры поиска решения, что позволит найти лучшее решение.
· В случае нелинейных задач необходимо уменьшить значение в поле Сходимость диалогового окна Параметры поиска решения, что позволит продолжать поиск решения, когда значение в целевой ячейке изменяется медленно.
· Необходимо установить флажок Автоматическое масштабирование в диалоговом окне Параметры поиска решения, если значения влияющих ячеек или значения влияющей и целевой ячеек различаются на несколько порядков.
После остановки на экране отображается диалоговое окно Результаты поиска решения. Установите переключатель в положение Сохранить найденное решение или Восстановить исходные значения, внесите нужные изменения и запустите процедуру поиска решения снова.
Читайте также: