Как включить поиск решений в Excel 🚩 Программное обеспечение
Вам понадобится
- Табличный редактор Microsoft Office Excel 2007 или 2010.
Инструкция
Запустите табличный редактор и раскройте главное меню. В версии Excel 2007 для этого надо кликнуть мышкой большую круглую кнопку Office в левом верхнем углу окна, а в Excel 2010 — синюю кнопку с надписью «Файл», размещенную примерно в том же месте. Можно раскрыть его и без мышки — нажмите сначала клавишу Alt (один или два раза), а затем введите «Ф».
Откройте список настроек редактора. В версии 2007 года для этого предназначена кнопка «Параметры Excel» у правого нижнего края главного меню, а в Excel 2010 пункт «Параметры» добавлен в список команд в левой колонке — он второй снизу.
Окно с установками табличного редактора обеих версий разбито на два вертикальных фрейма: в левый помещен список разделов, а в правый — относящиеся к разделу настройки. В списке найдите и кликните строку «Надстройки».
В правом фрейме, в списке «Неактивные надстройки приложений», выберите строку, которая начинается с текста «Поиск решения». Нажмите кнопку OK и надстройка будет активирована, но в меню Excel пока еще не появится.Перейдите на вкладку «Разработчик» в меню табличного редактора. Если ее нет, сначала кликните правой кнопкой свободное от кнопок пространство в любом разделе меню и выберите пункт «Настройка ленты». Затем в списке «Основные вкладки» найдите строку «Разработчик», поставьте рядом с ней отметку и нажмите кнопку OK — вкладка добавится на «ленту» меню.
Щелкните по пиктограмме «Надстройки» и в списке «Доступные надстройки» выставьте метку в поле «Поиск решения». Нажмите кнопку OK и на вкладке «Данные» появится дополнительная группа команд с названием «Анализ». В нее и будет помещена кнопка «Поиск решения».
Вкладка «Разработчик» не нужна для работы этой надстройки, поэтому ее можно убрать из меню — отключите ее отображение тем же способом, которым и включали (см. пятый шаг).
www.kakprosto.ru
Надстройка Microsoft Excel «Поиск решения»
Надстройка Microsoft Excel «Поиск решения» является мощным средством поиска решений и применяется при решении задач оптимизации. Процедура поиска решения позволяет находить оптимальное значение формулы содержащейся в ячейке, которую называют целевой. Процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке.
Краткое описание надстройки
Для получения заданного результата по формуле, процедура изменяет значения во влияющих ячейках. Для уменьшения интервала значений, используемых в модели, используются ограничения значений. Надстройка поиск решений является стандартной надстройкой Microsoft Office Excel и доступна сразу при установке Microsoft Office в целом или Microsoft Excel в частности.
Как установить надстройку?
Надстройку «Поиск решения» можно установить двумя способами. Стандартные надстройки, такие как «Поиск решения» и «Пакет анализа» устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки «Поиск решения» на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.
Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию «Добавить или удалить компоненты».
Далее, нажимаем кнопку «Продолжить», в параметрах установки находим приложение Microsoft Excel, в компонентах этого приложения находим раздел «Надстройки», выбираем надстройку «Поиск решения» и устанавливаем параметр «Запускать с моего компьютера».
Опять жмем кнопку «Продолжить» и ожидаем пока надстройка установится.
Как подключить надстройку?
Перед использованием необходимо предварительно включить надстройку, поставив галочку перед ее названием в списке доступных надстроек диалогового окна «Надстройки».
Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье «Как установить надстройку для Excel 2003/2007/2010?» со скриншотами для каждой из трех версий приложения Excel, поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка «Поиск решения») и произвести установку в соответствии с описанием по ссылке выше.
Где найти надстройку «Поиск решения» в Excel 2003/2007/2010?
После установки и подключения надстройки в Excel 2007/2010 на вкладке «Данные» появляется группа «Анализ» с новой командой «Поиск Решения». В Excel 2003 — появляется новый пункт меню «Сервис» с одноименным названием. Поиск решения — стандартная надстройка, существуют также и другие надстройки для Excel, служащие для добавления в MS Excel различных специальных возможностей.
Оптимизация бизнес-модели
Постановка задачи
Предположим, что компания, где вы работаете, имеет два складских помещения, откуда товар поступает в пять ваших магазинов, разбросанных по всей Москве.
Каждый магазин в состоянии реализовать определенное, известное нам количество товара. Каждый из складов имеет ограниченную вместимость. Задача состоит в том, чтобы рационально выбрать – с какого склада в какие магазины нужно доставлять товар, чтобы минимизировать общие транспортные расходы.
Перед началом оптимизации необходимо будет составить несложную таблицу на листе Excel – нашу математическую модель, описывающую ситуацию:
Подразумевается, что:
- Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.
- Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
- Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.
- Желтые (C12:G12) и синие (h20:h21) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
- Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.
Таким образом, наша задача сводится к подбору оптимальных значений зеленых ячеек. Причем так, чтобы общая сумма по строке (синие ячейки) не превышала вместимости склада (красные ячейки), и при этом каждый магазин получил необходимое ему количество товаров на реализацию (сумма по каждому магазину в желтых ячейках должна быть как можно ближе к требованиям – лиловым ячейкам).
Решение
В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно же, давно разработаны способы их решения. Excel предоставляет пользователю один из них – с помощью мощной надстройки Поиск решения (Solver) , доступной в Excel 2003 через в меню Сервис (Tools) или с вкладки Данные (Data) в новых версиях Excel.
Если в меню Сервис или на вкладке Данные вашего Excel такой команды нет – ничего страшного — значит надстройка просто еще не подключена. Для ее подключения:
- в Excel 2003 и старше — откройте меню Сервис – Надстройки (Tools – Add—Ins), в появившемся окне установите флажок Поиск решения (Solver) и нажмите ОК. Excel активирует выбранную надстройку и в меню Сервис (Tools) появится новая команда – Поиск решения (Solver).
- в Excel 2007 и новее — нажать кнопку Офис, далее выбрать Параметры Excel – Надстройки – Перейти (Excel Options — Add-Ins — Go To).
Запустим надстройку. Откроется вот такое окно:
В этом окне нужно задать следующие настройки:
- Целевая ячейка (Target cell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (E17). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданной константе.
- Изменяемые ячейки (By changing cells) – здесь укажем зеленые ячейки (C10:G11), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.
- Ограничения (Subject to the Constraints) – список ограничений, которые надо учитывать при проведении оптимизации. В нашем случае это ограничения на вместимость складов и потребности магазинов. Для добавления ограничений в список нужно нажать кнопку Добавить (Add) и ввести условие в появившееся окно:
Кроме очевидных ограничений, связанных с физическими факторами (вместимость складов и средств перевозки, ограничения бюджета и сроков и т.д.) иногда приходится добавлять ограничения «специально для Excel». В нашем случае, например, нужно будет добавить вот такое ограничение:
Оно дополнительно уточнит, что объем перевозимого товара (зеленые ячейки) не может быть отрицательным – для человека такое само собой очевидно, но для компьютера это надо прописать явно.
После настройки всех необходимых параметров окно должно выглядеть следующим образом:
Теперь, когда данные для расчета введены, нажмем кнопку Выполнить (Solve), чтобы начать оптимизацию. В тяжелых случаях с большим количеством изменяемых ячеек и ограничений нахождение решения может занять продолжительное время, но наша задача для Excel проблемы не составит – через пару мгновений мы получим следующие результаты:
Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.
Если найденное решение нам подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий
Бывают, однако, ситуации, когда Excel не может найти подходящего решения. Имитировать такой случай можно, если указать в нашем примере требования магазинов в сумме большие, чем общая вместимость складов. Тогда при выполнении оптимизации Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение. Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.
Рассмотренный пример, конечно, является относительно простым, но легко масштабируется под решение гораздо более сложных нелинейных задач. Например:
- Оптимизация распределения финансовых средств по статьям расходов в бизнес-плане или бюджете проекта. Ограничениями, в данном случае, будут являться объемы финансирования и сроки выполнения проекта, а целью оптимизирования – максимизация прибыли и минимизация расходов на проект.
- Оптимизация расписания сотрудников с целью минимизации фонда заработной платы предприятия. Ограничениями, в этом случае, будут пожелания каждого сотрудника по графику занятости и требования штатного расписания.
- Оптимизация инвестиционных вложений – необходимость грамотно распределить средства между несколькими банками, ценными бумагами или акциями предприятий с целью, опять же, максимизации прибыли или (если это более важно) минимизации рисков.
В любом случае, надстройка Поиск решения (Solver) является весьма мощным и красивым инструментом Excel и достойна того, чтобы вы обратили на нее свое внимание, поскольку может выручить во многих сложных ситуациях, с которыми приходится сталкиваться в современном бизнесе.
www.planetaexcel.ru
Excel 2007. Поиск решений в Excel 2007
Надстройка Поиск решений в Excel 2007 не является стандартной. Она предназначается для сложных вычислений, когда имеется больше одной неизвестной. Поэтому она не включается в обычный набор параметров программы. Но если в ней есть необходимость, то она способна предложить пользователю эффективную работу и высокую продуктивность.
Что такое Поиск решений?
Поиск решений в Excel 2007 является надстройкой программы. Это означает, что в обычной конфигурации, выпускаемой производителем, этот пакет не устанавливается. Его нужно загружать и настраивать отдельно. Дело в том, что чаще всего пользователи обходятся без него. Также надстройку нередко называют «Решатель», поскольку она способна вести точные и быстрые вычисления, зачастую независимо от того, насколько сложная задача ей представлена.
Если версия Microsoft Office является оригинальной, тогда проблем с установкой не возникнет. Пользователю нужно сделать несколько переходов:Параметры→Сервис→Надстройки→Управление→Надстройки Excel.
Откроется окно, в котором есть кнопка перехода. После клика на нее на экране появится список всех предлагаемых надстроек, как установленных, так и неиспользуемых. Теперь нужно найти Поиск решений, затем поставить галочку. Инструмент активизирован, можно пользоваться им в любое время.
Зачем нужен Решатель?
Для чего можно использовать Поиск решений в Excel 2007, и стоит ли вообще его устанавливать? Когда у пользователя присутствует целевая функция, зависящая от нескольких параметров, надстройка будет подбирать решения задачи в соответствии с исходными данными. Таковыми может оказаться переменная, неизвестная или, например, итоговое значение. То есть, пользователь может иметь начальные характеристики и ответ, а программа подберет ход решения, предоставит формулу.
Таким образом, посредством надстройки можно найти:- Удачное распределение рабочих ресурсов, чтобы достичь максимальной прибыли в ходе деятельности компании или отдельного отдела, филиала.
- Распределение вложений при минимизированных рисках.
- Решение задач, где есть больше одной неизвестной (будет предложено несколько вариантов ответов, из которых пользователь сам подберет наиболее подходящий).
- Сохранение и загрузка модели решения. Оптимальный вариант для сотрудников, которые вынуждены постоянно менять компьютер или ноутбук.
- Решение сразу нескольких задач с разными переменными, неизвестными, формулами и интегралами.
Программа открывает большие возможности, но ею нужно научиться правильно пользоваться.
Как работает Решатель?
Помимо решателя, в Excel есть такая функция, как подбор параметра. Она рекомендована к использованию в случаях, когда имеется только одно неизвестное значение. Эта возможность программы требует намного меньше ресурсных компьютерных затрат, поэтому быстрее выдаст результат.
Поиск решений в Excel 2007 применяется для самых сложных задач, где имеется несколько неизвестных, часто встречаются переменные. В общей постановке их можно сформулировать следующим образом:- Найти неизвестные→несколько «x».
- При условии, что→формула или функция.
- При ограничениях→здесь обычно указывается неравенство, либо минимальные/максимальные значения.
Также нужно указать на ячейки, с которыми следует проводить вычисления. Есть возможность решать несколько разных задач, если задать программе соответствующие параметры.
Настройка параметров Поиска решений
Чтобы функция Поиска решений в Excel 2007 работала так, как необходимо пользователю, нужно ввести правильные параметры. Обычно они ограничиваются 1-3 характеристиками, но с более сложными задачами потребуется глобальная настройка.
Параметры в Поиске решений программы Office Excel 2007 могут быть следующими:- Максимальное время – количество секунд, которые пользователь выделяет программе на решение. Оно зависит от сложности задачи.
- Максимальное число интеграций. Это количество ходов, которые делает программа на пути к решению задачи. Если оно увеличивается, то ответ не будет получен.
- Погрешность или точность, чаще всего применяется при решении десятичных дробей (к примеру, до 0,0001).
- Допустимое отклонение. Используется при работе с процентами.
- Неотрицательные значения. Применяется тогда, когда решается функция с двумя правильными ответами (например, +/-X).
- Показ результатов интеграций. Такая настройка указывается в случае, если важен не только результат решений, но и их ход.
- Способ поиска – выбор оптимизационного алгоритма. Обычно применяется «метод Ньютона».
После того как все настройки выбраны, обязательно нужно нажать кнопку сохранения.
Параметры задачи в функции Поиска решений
Работа такой надстройки, как Поиск решения в Excel, осуществляется в соответствии с заданными характеристиками вычисления. Наиболее важной из них является метод. Есть два их варианта. «Метод Ньютона» является настройкой по умолчанию. Он способен работать с большей памятью, но меньшими интеграциями. Поэтому для стандартных и не особо сложных уравнений он вполне подойдет.
Также есть «метод сопряженных градиентов». Здесь запрашивается меньше памяти, но требуется больше интеграций. Следовательно, при его использовании можно решать самые сложные уравнения, использовать масштабные формулы и функции.
Формула в Excel
Есть обязательный элемент, без которого не сможет функционировать надстройка Поиска решений в программе Excel 2007 – формулы. Они представляют собой такое выражение, которое выполняет то или иное вычисление. Без равенства формул не существует. Поэтому программа не начнет распознавать таковую, когда отсутствует соответствующий знак.
Формула может включать в себя следующее:- Функция. Это стандартная формула, где присутствует определенный и конкретный порядок действий, поменять который не удастся.
- Ссылка. Она указывает на количество клеток, которые нужно решить. При этом ячейки могут располагаться хаотично или в определенном порядке.
- Оператор. Это символ, который задает тип вычисления (+ – сложение, * – умножение и т.д.).
- Константа. Постоянное значение, которое никогда не меняется. Также для его получения не нужно производить вычисления.
Решение формул осуществляется слева направо при соблюдении всех математических правил.
Создание формулы
Формулы являются уравнениями, которые способствуют выполнению вычислений программы. Если таковые не вводить, то не будет работать Поиск решения в Excel. Задачи, соответственно, тоже не станут решаться. Поэтому для удачного выполнения поставленного задания необходимо правильно ввести формулу.
Вычисление начинается со знака равенства. К примеру, если в ячейке указывается «=КОРЕНЬ(номер клетки)», то будет использована соответствующая функция.
После того как была напечатана основная формула со знаком «=», нужно указать на данные, с которыми она будет взаимодействовать. Это может быть одна или несколько ячеек. Если формула подходит для 2-3 клеток, то объединить их можно, используя знак «+».Чтобы найти нужную информацию, можно воспользоваться функцией поиска. Например, если нужна формула с буквой «A», то ее и надо указывать. Тогда пользователю будут предложены все данные, ее в себя включающие.
Заключение
В заключении в программе Excel 2007 нужно сохранить заданные параметры решения задач. Сделать это можно несколькими способами. Стандартный вариант с кликом на соответствующую кнопку подойдет в том случае, если для всех данных используется один метод вычислений.
Когда нужно решить сразу несколько уравнений, к примеру, найти минимум и максимум функций, то нужно сохранять не все вычисление, а его модели. Затем пользователь сможет применить их к тому или иному решению.
fb.ru
Поиск решения в Excel
ПОИСК РЕШЕНИЯ В EXCEL
Программа Microsoft Office Excel предназначена и широко используется для вычислений, предполагающих представление данных в табличном виде.
На уроках алгебры часто приходится решать квадратные, кубические уравнения, системы уравнений.
Цель — решать уравнения n-ой степени и системы уравнений с помощью Excel.
Для достижения данной цели поставим следующие задачи:
изучить возможности инструмента «Поиск решения»;
создать в Excel шаблоны для решения различных типов задач;
разработать инструкции нахождения решений;
Программа «Поиск решения» позволяет получить результат на основе изменения значения нескольких ячеек. Кроме того, при выполнении поиска решения можно задать условия – ввести ограничения. Эти возможности позволяют использовать программу Excel для решения системы уравнений и уравнений, при решении которых необходимо учитывать область допустимых значений, для нахождения точек, в которых достигается максимум или минимум значения целевой функции нескольких переменных, определенных на множестве с линейными и нелинейными ограничениями. Другими словами – находить оптимальное решение задачи с ограничениями.
Модели всех задач на оптимизацию состоят из следующих элементов:
Переменные — неизвестные величины, которые нужно найти при решении задачи.
Целевая функция — величина, которая зависит от переменных и является целью, ключевым показателем эффективности или оптимальности модели.
Ограничения — условия, которым должны удовлетворять переменные.
Поиск решения рассмотрим на примерах.
Пример 1. (1; -9)
Найти минимальное значение функции .
В
данном случае минимальное значение функции также очень быстро можно найти с помощью инструмента Поиск решения, заполнив поля, как показано на рис. 2.
Рис. 2
Рис. 1
Получен результат: минимальное значение функции y = -9 при x = 1. Так как исследована квадратичная функция, графиком которой является парабола, ветви направлены вверх, тогда точка (1, -9) является вершиной параболы. Значит, с помощью инструмента Поиск решения также можно найти и координаты вершины параболы, что в свою очередь сокращает время в их нахождении.
Пример 2. (-1; -1)
Найти максимальное значение функции .
Пример 3. x= — 0,5 и y = 2,5
Решить уравнение
Рис. 3 Рис.4
Рис. 5
Таким образом, с помощью Excel можно решать квадратные уравнения, допускающие два решения. Рассмотрена задача поиска значения параметра, позволяющего достичь конкретной цели. Но решаемые задачи могут быть более сложными. Например, поиск нескольких параметров, обеспечивающих некоторый, наперед заданный результат.
Пример 4. x= 12, y = 6 и z =3
Решить систему уравнений
Рис. 6
Рис.7
Пример 5. , ,
Решить кубическое уравнение .
Воспользовавшись инструментомПоиск решения, получены корни.
.
Рис. 8
Пример 6. x = 0,5
5x – 8 lnx = 8
Самостоятельная работа
Пример 7. x = — 1, y = — 1, z = — 1
Решить систему уравнений
x + 5y + z = — 7,
2x – y – z = 0,
x – 2y – z = 0.
Пример 8. x = 2, y = 3
Решить уравнение x² + 5x + 6 = 0
Пример 9. x = 2, y = 28
Решить уравнение x² + 30x + 56 = 0
Решение разнообразных задач позволяет расширить представление о возможностях электронных таблиц, продемонстрировать практическое применение программы Excel на уроках математики.
3
videouroki.net