Функция Microsoft Excel: поиск решения

Одной из самых увлекательных функций в программке Microsoft Excel является Поиск решения. Совместно с тем, необходимо подчеркнуть, что данный инструмент недозволено отнести к самым пользующимся популярностью посреди юзеров в данном приложении. А напрасно. Ведь эта функция, используя начальные данные, методом перебора, находит более наилучшее решение из всех имеющихся. Давайте выясним, как применять функцию Поиск решения в программке Microsoft Excel.

Включение функции

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

Для того, чтоб произвести активацию Поиска решений в программке Microsoft Excel 2010 года, и наиболее поздних версий, перебегаем во вкладку «Файл». Для версии 2007 года, следует надавить на клавишу Microsoft Office в левом верхнем углу окна. В открывшемся окне, перебегаем в раздел «Характеристики».

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

Раскрывается окно с надстройками. Ставим галочку напротив наименования подходящей нам надстройки – «Поиск решения». Нажимаем на клавишу «OK».

Опосля этого, клавиша для пуска функции Поиска решений покажется на ленте Excel во вкладке «Данные».

Подготовка таблицы

Сейчас, опосля того, как мы активировали функцию, давайте разберемся, как она работает. Легче всего это представить на определенном примере. Итак, у нас есть таблица зарплаты работников компании. Нам следует высчитать премию всякого работника, которая является произведением зарплаты, обозначенной в отдельном столбце, на определенный коэффициент. При всем этом, общая сумма валютных средств, выделяемых на премию, равна 30000 рублей. Ячейка, в которой находится данная сумма, имеет заглавие мотивированной, потому что наша цель подобрать данные конкретно под это число.

Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить при помощи функции Поиска решений. Ячейка, в которой он размещается, именуется разыскиваемой.

Мотивированная и разыскиваемая ячейка должны быть связанны вместе при помощи формулы. В нашем определенном случае, формула размещается в мотивированной ячейке, и имеет последующий вид: «=C10*$G$3», где $G$3 – абсолютный адресок разыскиваемой ячейки, а «C10» — общая сумма зарплаты, от которой делается расчет премии работникам компании.

Пуск инструмента Поиск решения

Опосля того, как таблица подготовлена, находясь во вкладке «Данные», нажимаем на клавишу «Поиск решения», которая размещена на ленте в блоке инструментов «Анализ».

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

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

Под окном с адресом мотивированной ячейки, необходимо установить характеристики значений, которые будут находиться в ней. Это быть может максимум, минимум, либо конкретное значение. В нашем случае, это будет крайний вариант. Потому, ставим переключатель в позицию «Значения», и в поле слева от него прописываем число 30000. Как мы помним, конкретно это число по условиям составляет общую сумму премии для всех работников компании.

Ниже размещено поле «Изменяя ячейки переменных». Здесь необходимо указать адресок разыскиваемой ячейки, где, как мы помним, находится коэффициент, умножением на который главный зарплаты будет рассчитана величина премии. Адресок можно прописать теми же методами, как мы это делали для мотивированной ячейки.

В поле «В согласовании с ограничениями» можно выставить определенные ограничения для данных, к примеру, создать значения целыми либо неотрицательными. Для этого, нажимаем на клавишу «Добавить».

Опосля этого, раскрывается окно прибавления ограничения. В поле «Ссылка на ячейки» прописываем адресок ячеек, относительно которых вводится ограничение. В нашем случае, это разыскиваемая ячейка с коэффициентом. Дальше проставляем подходящий символ: «меньше либо равно», «больше либо равно», «равно», «целое число», «бинарное», и т.д. В нашем случае, мы выберем символ «больше либо равно», чтоб создать коэффициент положительным числом. Соответственно, в поле «Ограничение» указываем число 0. Если мы желаем настроить ещё одно ограничение, то нажимаем на клавишу «Добавить». В оборотном случае, нажимаем на клавишу «OK», чтоб сохранить введенные ограничения.

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

Доп опции можно задать, кликнув по кнопочке «Характеристики».

Тут можно установить точность ограничения и пределы решения. Когда нужные данные введены, нажимайте на клавишу «OK». Но, для нашего варианта, изменять эти характеристики не надо.

Опосля того, как все опции установлены, нажимаем на клавишу «Отыскать решение».

Дальше, программка Эксель в ячейках делает нужные расчеты. Сразу с выдачей результатов, раскрывается окно, в котором вы сможете или сохранить отысканное решение, или вернуть начальные значения, переставив переключатель в подобающую позицию. Независимо от избранного варианта, установив галочку «Возвратятся в диалоговое окно характеристик», вы сможете снова перейти к настройкам поиска решения. Опосля того, как выставлены галочки и переключатели, нажимаем на клавишу «OK».

Если по какой-нибудь причине поисковые результаты решений вас не удовлетворяют, либо при их подсчете программка выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше методом, в диалоговое окно характеристик. Пересматриваем все введенные данные, потому что может быть кое-где была допущена ошибка. В случае, если ошибка найдена не была, то перебегаем к параметру «Изберите способ решения». Здесь предоставляется возможность выбора 1-го из 3-х методов расчета: «Поиск решения нелинейных задач способом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По дефлоту, употребляется 1-ый способ. Пробуем решить поставленную задачку, выбрав хоть какой иной способ. В случае беды, повторяем попытку, с внедрением крайнего способа. Метод действий всё этот же, который мы обрисовывали выше.

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

Мы рады, что смогли посодействовать Для вас в решении трудности.

Кроме данной нам статьи, на веб-сайте еще 12327 инструкций.
Добавьте веб-сайт Lumpics.ru в закладки (CTRL+D) и мы буквально еще пригодимся для вас.

Отблагодарите создателя, поделитесь статьей в соц сетях.

Опишите, что у вас не вышло. Наши спецы постараются ответить очень стремительно.

ITGuides.ru

Вопросцы и ответы в сфере it технологий и настройке ПК (Персональный компьютер — компьютер, предназначенный для эксплуатации одним пользователем)

Как воспользоваться функциями подбора параметра и поиска решения в Excel

Функция поиска решения понадобится по мере необходимости найти неведомую величину

Табличный микропроцессор Microsoft Excel может делать не только лишь обыкновенные операции с числами (сложение, умножение), расчет суммы либо среднего значения. У данной нам программки имеется весьма мощнейший функционал, который дозволяет решать задачки разной трудности. К примеру, Эксель может улучшить значения в таблице, подставляя их таковым образом, чтоб они удовлетворяли определенным аспектам. Для этого программка вооружена особыми средствами для анализа данных: 1-ый — это подбор параметра, а 2-ой — поиск решения.

Видео пример поиска решения в Excel

Функция «Подбор параметра»

Подбор параметра в Excel дозволяет подобрать некий определенный параметр, значение которого непонятно. Чтоб было понятней, можно привести таковой пример. Допустим, есть прямоугольник со сторонами A и B. Понятно, что общая площадь данной нам фигуры составляет 400 квадратных метров, а сторона B — 40 метров. Сторона A неведома и, соответственно, необходимо ее отыскать. Для решения таковой задачки нужно заполнить рабочий лист программки теми данными, которые уже известны. Для этого необходимо сделать таблицу с 2 колонками и 3 строчками (спектр ячеек A1:B3).

1-ый столбец будет содержать заглавие сторон прямоугольника и буковку, обозначающую его площадь (т.е. A, B и S). А во 2-м столбце нужно указать известные значения:

• в примыкающей ячейке для стороны B (ячейка B2) написать — 40 (значение для стороны А остается пустым);
• а в примыкающем поле для площади прямоугольника (поле B3) написать последующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было изготовлено верно, то в поле B3 обязано быть значение 0. Потом нужно выделить эту ячейку и избрать в панели меню пункты: «Сервис — Подбор параметра». В показавшемся окне необходимо указать то значение, которое обязано быть получено в итоге, т.е. 400. В строке «Установить в ячейке» будет обозначено поле «B3»: поменять его не надо, так и обязано быть (сюда будет выведен итог). А в строке «Изменяя значение» нужно избрать неведомый параметр, т.е. поле B1. Опосля нажатия клавиши «ОК» программка выдаст итог: сторона А — 10 метров, а в поле общей площади прямоугольника будет обозначено число 400.

Это была весьма обычная задачка на уровне 3 класса, но при помощи таковой функции можно решать и наиболее сложные задачки. К примеру, вы решили приобрести для себя кар в кредит. Вы буквально понимаете, что можете выплачивать каждомесячную выплату в размере 1000 $ (но не больше), также, что банк выдает автокредит с процентной ставкой 6,5%. Сущность задачки заключается в последующем: «Какова наибольшая сумма машинки, которую можно взять в кредит на таковых критериях?». Другими словами сейчас программка будет находить стоимость кара, отталкиваясь от того, что каждомесячный платеж не должен превосходить 1000 $. Таковой пример является уже наиболее сложным, также наиболее удобным, нежели расчет площади прямоугольника.

Надстройка «Поиск решения»

Характеристики инструмента поиск решения

Еще одним средством анализа данных в Экселе, при помощи которого решают похожие задачки, является надстройка«Поиск решения». Если в первом случае Excel мог подбирать значение лишь в одной ячейке, то при помощи данной нам надстройки можно улучшить сразу несколько значений. Эта функция имеется во всех версиях Excel, но по дефлоту она отключена. Чтоб включить эту надстройку в Excel 2003 версии, нужно в панели меню избрать пункты «Сервис — Надстройки» и поставить галочку напротив пт «Поиск решения». Опосля этого эту надстройку можно вызвать через тот же пункт «Сервис». В новейших версиях существует иной метод: нужно щелкнуть пункты «Файл — Характеристики — Надстройки», потом избрать «Надстройки Excel — Перейти» и поставить галочку напротив подходящей строчки.

Поиск рационального решения в Excel

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

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

Поиск решения задач в Excel с примерами

Юзеры Excel издавна и удачно используют программку для решения разных типов задач в различных областях.

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

Решение задач оптимизации в Excel

Оптимизационные модели используются в экономической и технической сфере. Их цель – подобрать равновесное решение, наилучшее в определенных критериях (количество продаж для получения определенной выручки, наилучшее меню, число рейсов и т.п.).

В Excel для решения задач оптимизации употребляются последующие команды:

• Подбор характеристик («Данные» — «Работа с данными» — «Анализ «что-если»» — «Подбор параметра») – находит значения, которые обеспечат подходящий итог.
• Поиск решения (надстройка Microsoft Excel; «Данные» — «Анализ») – рассчитывает лучшую величину, беря во внимание переменные и ограничения. Перейдите по ссылке и узнайте как подключить настройку «Поиск решения».
• Диспетчер сценариев («Данные» — «Работа с данными» — «Анализ «что-если»» — «Диспетчер сценариев») – анализирует несколько вариантов начальных значений, делает и оценивает наборы сценариев.

Для решения простых задач применяется команда «Подбор параметра». Самых сложных – «Диспетчер сценариев». Разглядим пример решения оптимизационной задачки при помощи надстройки «Поиск решения».

Условие. Компания производит несколько видов йогурта. Условно – «1», «2» и «3». Реализовав 100 баночек йогурта «1», предприятие получает 200 рублей. «2» — 250 рублей. «3» — 300 рублей. Сбыт, налажен, но количество имеющегося сырья ограничено. Необходимо отыскать, какой йогурт и в каком объеме нужно созодать, чтоб получить наибольший доход от продаж.

Известные данные (в т.ч. нормы расхода сырья) занесем в таблицу:

На основании этих данных составим рабочую таблицу:

1. Количество изделий нам пока непонятно. Это переменные.
2. В столбец «Прибыль» внесены формулы: =200*B11, =250*В12, =300*В13.
3. Расход сырья ограничен (это ограничения). В ячейки внесены формулы: =16*B11+13*B12+10*B13 («молоко»); =3*B11+3*B12+3*B13 («закваска»); =0*B11+5*B12+3*B13 («амортизатор») и =0*B11+8*B12+6*B13 («сахар»). Другими словами мы норму расхода помножили на количество.
4. Цель – отыскать очень вероятную прибыль. Это ячейка С14.

Активизируем команду «Поиск решения» и вносим характеристики.

Опосля нажатия клавиши «Выполнить» программка выдает свое решение.

Лучший вариант – сконцентрироваться на выпуске йогурта «3» и «1». Йогурт «2» создавать не стоит.

Решение денежных задач в Excel

Почаще всего для данной нам цели используются денежные функции. Разглядим пример.

Условие. Высчитать, какую сумму положить на вклад, чтоб через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годичных. Проценты начисляются ежеквартально.

Оформим начальные данные в виде таблицы:

Потому что процентная ставка не изменяется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

1. Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
2. Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
3. Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
4. Тип – 0.
5. БС – сумма, которую мы желаем получить в конце срока вклада.

Вкладчику нужно вложить эти средства, потому итог отрицательный.

Для проверки корректности решения воспользуемся формулой: ПС = БС / (1 + ставка) кпер . Подставим значения: ПС = 400 000 / (1 + 0,05) 16 = 183245.

Решение эконометрики в Excel

Для установления количественных и высококачественных взаимосвязей используются математические и статистические способы и модели.

Дано 2 спектра значений:

Значения Х будут играться роль факторного признака, Y – действенного. Задачка – отыскать коэффициент корреляции.

Для решения данной нам задачки предусмотрена функция КОРРЕЛ (массив 1; массив 2).

Решение логических задач в Excel

В табличном микропроцессоре есть интегрированные логические функции. Неважно какая из их обязана содержать хотя бы один оператор сопоставления, который обусловит отношение меж элементами (=, >, =, Пример задачки. Ученики сдавали зачет. Любой из их получил отметку. Если больше 4 баллов – зачет сдан. Наименее – не сдан.

1. Ставим курсор в ячейку С1. Жмем значок функций. Избираем «ЕСЛИ».
2. Заполняем аргументы. Логическое выражение – B1>=4. Это условие, при котором логическое значение – ИСТИНА.
3. Если ИСТИНА – «Зачет сдал». ЛОЖЬ – «Зачет не сдал».

Решение математических задач в Excel

Средствами программки можно решать как простые математические задачи, так и наиболее сложные (операции с функциями, матрицами, линейными уравнениями и т.п.).

Условие учебной задачки. Отыскать оборотную матрицу В для матрицы А.

1. Делаем таблицу со значениями матрицы А.
2. Выделяем на этом же листе область для оборотной матрицы.
3. Жмем клавишу «Вставить функцию». Категория – «Математические». Тип – «МОБР».
4. В поле аргумента «Массив» вписываем спектр матрицы А.
5. Жмем сразу Shift+Ctrl+Enter — это непременное условие для ввода массивов.

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