Servisneva.ru

Сервис Нева
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Надстройка Microsoft Excel; Поиск решения

Для получения заданного результата по формуле, процедура изменяет значения во влияющих ячейках. Для уменьшения интервала значений, используемых в модели, используются ограничения значений. Надстройка поиск решений является стандартной надстройкой Microsoft Office Excel и доступна сразу при установке Microsoft Office в целом или Microsoft Excel в частности.

Надстройку «Поиск решения» можно установить двумя способами. Стандартные надстройки, такие как «Поиск решения» и «Пакет анализа» устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки «Поиск решения» на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.

Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию «Добавить или удалить компоненты».

kak-ustanovit-nadstrojku-poisk-resheniya

Далее, нажимаем кнопку «Продолжить», в параметрах установки находим приложение Microsoft Excel, в компонентах этого приложения находим раздел «Надстройки», выбираем надстройку «Поиск решения» и устанавливаем параметр «Запускать с моего компьютера».

ustanovka-nadstrojki-poisk-resheniya

Опять жмем кнопку «Продолжить» и ожидаем пока надстройка установится.

Средство Excel «Поиск решения»

Решение задач прикладной информатики в менеджменте.


Практическое занятие 5.


Средство Excel «Поиск решения»

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

Задачи оптимизации параметров объекта исследования

Оптимизационные модели служат для поиска наилучших, в определенном смысле, вариантов. В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые параметры X . Среди выходных характеристик Y выделяют такую, которая позволяет оценить качество объекта – критерий оптимальности Q k .

С учетом введенных обозначений задача оптимизации формализуется следующим образом:

Q * k = extr Q k
X
Y j ( х ) j max

В зависимости от особенностей реального объекта, характера зависимости критерия оптимальности от независимых переменных и целей исследования различают задачи:

условной (при наличии дополнительных ограничений) и безусловной (без дополнительных условий) оптимизации;

одномерной (при одном управляемом параметре) и многомерной (несколько управляемых параметров) оптимизации;

линейной (при линейной зависимости критерия качества от параметров) и нелинейной оптимизации;

локальной (существует единственный экстремум) и глобальной (существуют несколько экстремумов) оптимизации.

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

Существуют многочисленные программы, предназначенные для решения оптимизационных задач. Одной из таких программ является средство Excel «Поиск решения».

Средство Excel «поиск решения»

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

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

Для выполнения операции Поиск решения использует команду меню Сервис – Поиск решения. Команда может отсутствовать в меню сервис. В этом случае нужно в меню Сервис выбрать команду Надстройки и установить в списке включенных надстроек нужный флажок:

Рисунок 1 Список подключенных настроек

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

Для выполнения операции Поиск решения нужно выполнить команду Сервис – Поиск решения и в диалоговом окне Поиск решения задать параметры решения:

адрес целевой ячейки, в которой будет подбираться значение;

критерий оптимальности (максимальное или минимальное значение) или значение, которое следует найти;

адреса изменяемых ячеек; при этом адреса отдельных ячеек или диапазонов разделяются запятыми; кнопка «Предположить» служит для автоматического выделения ячеек, влияющих на целевую;

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

Рисунок 2 Диалог «Поиск решения»

Кнопка «Параметры» позволяет изменить параметры поиска: способ поиска решения, время вычислений, точность определения результатов.

Рисунок 3 Диалог установки параметров поиска решения

В большинстве случаев достаточно использовать настройки по умолчанию. Это окно позволяет так же сохранить модель поиска или загрузить ранее сохраненную модель.

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

сохранить найденной решение в исходной таблице;

восстановить исходные значения;

сохранить результаты в виде сценария;

сформировать отчет по результатам выполнения операции.

Рисунок 4 Диалог «Результаты поиска решения»

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

В программе Excel-2007 средство «Поиск решения» вызывается пиктограммой из группы Анализ , расположенной на вкладке Данные.

Определение критического объема реализации с помощью поиска решений

Рассмотрим использование Поиска решений для решения уравнения.

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

Читать еще:  Как поставить скобки в документ Word

Скопируйте заголовок и первую строку таблицы анализа затрат – доходов:

Объем
реализации
Q к

В столбце «Баланс» введите формулу: Прибыль от реализации — Затраты

С помощью средства «Поиск решения» определите величину Объема реализации, обеспечивающую нулевой баланс.

Указания.
1) Целевая ячейка в нашем случае – ячейка, в которой вычислен баланс; требуется установить в ней нулевое значение путем изменения ячейки с объемом реализации.

2) Математическая модель рассматриваемой задачи линейна.

3) Задача без ограничений.

Замечание. Иногда требуется проверить, какие ячейки влияют на вычисление значение в другой ячейке. Чтобы наглядно увидеть взаимное влияние ячеек, можно использовать команду меню Сервис – Зависимости формул – Влияющие ячейки (Зависимые ячейки).

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

Методы решения, используемые Solver

Вы можете выбрать один из следующих трех методов решения, которые поддерживает Excel Solver, в зависимости от типа проблемы:

LP Simplex

Используется для линейных задач. Модель Солвера является линейной при следующих условиях:

Целевая ячейка вычисляется путем сложения членов формы (изменяющаяся ячейка) * (постоянная).

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

Целевая ячейка вычисляется путем сложения членов формы (изменяющаяся ячейка) * (постоянная).

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

Обобщенный редуцированный градиент (GRG) нелинейный

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

эволюционный

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

Понимание оценки Солвера

Для Солвера требуются следующие параметры —

  • Ячейки с переменными решениями
  • Клетки ограничения
  • Объективные Клетки
  • Метод решения

Оценка решателя основана на следующем:

Значения в ячейках переменных решения ограничены значениями в ячейках ограничений.

Вычисление значения в целевой ячейке включает значения в ячейках переменных решения.

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

Значения в ячейках переменных решения ограничены значениями в ячейках ограничений.

Вычисление значения в целевой ячейке включает значения в ячейках переменных решения.

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

Определение проблемы

Предположим, вы анализируете прибыль, полученную компанией, которая производит и продает определенный продукт. Вас просят найти сумму, которая может быть потрачена на рекламу в следующие два квартала, но не более 20 000. Уровень рекламы в каждом квартале влияет на следующее —

  • Количество проданных единиц, косвенно определяющих сумму выручки от продаж.
  • Сопутствующие расходы и
  • Прибыль

Вы можете приступить к определению проблемы как —

  • Найти стоимость единицы.
  • Найти стоимость рекламы на единицу.
  • Найти цену за единицу.

Определение проблемы

Затем установите ячейки для необходимых расчетов, как указано ниже.

Set Cells

Как вы можете заметить, расчеты сделаны для квартала 1 и квартала 2, которые рассматриваются:

Количество единиц, доступных для продажи в квартале 1, составляет 400, а в квартале 2 — 600 (ячейки — C7 и D7).

Начальные значения для рекламного бюджета установлены как 10000 за квартал (ячейки — C8 и D8).

Количество проданных единиц зависит от стоимости рекламы на единицу и, следовательно, является бюджетом на квартал / Adv. Стоимость за единицу. Обратите внимание, что мы использовали функцию Min, чтобы убедиться, что нет. единиц, проданных в <= нет. из доступных единиц. (Клетки — C9 и D9).

Выручка рассчитывается как цена за единицу * Количество проданных единиц (ячейки — C10 и D10).

Расходы рассчитываются как стоимость единицы * Количество доступных единиц + Adv. Стоимость за этот квартал (Клетки — C11 и D12).

Прибыль — это доход — расходы (ячейки C12 и D12).

Общая прибыль — это прибыль за квартал 1 + прибыль за квартал 2 (ячейка — D3).

Количество единиц, доступных для продажи в квартале 1, составляет 400, а в квартале 2 — 600 (ячейки — C7 и D7).

Начальные значения для рекламного бюджета установлены как 10000 за квартал (ячейки — C8 и D8).

Количество проданных единиц зависит от стоимости рекламы на единицу и, следовательно, является бюджетом на квартал / Adv. Стоимость за единицу. Обратите внимание, что мы использовали функцию Min, чтобы убедиться, что нет. единиц, проданных в <= нет. из доступных единиц. (Клетки — C9 и D9).

Выручка рассчитывается как цена за единицу * Количество проданных единиц (ячейки — C10 и D10).

Средство Excel «Поиск решения»

Решение задач прикладной информатики в менеджменте.


Практическое занятие 5.


Средство Excel «Поиск решения»

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

Читать еще:  Как удалить лишнюю или пустую страницу в MS Word

Задачи оптимизации параметров объекта исследования

Оптимизационные модели служат для поиска наилучших, в определенном смысле, вариантов. В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые параметры X . Среди выходных характеристик Y выделяют такую, которая позволяет оценить качество объекта – критерий оптимальности Q k .

С учетом введенных обозначений задача оптимизации формализуется следующим образом:

Q * k = extr Q k
X
Y j ( х )<= Z j max

В зависимости от особенностей реального объекта, характера зависимости критерия оптимальности от независимых переменных и целей исследования различают задачи:

условной (при наличии дополнительных ограничений) и безусловной (без дополнительных условий) оптимизации;

одномерной (при одном управляемом параметре) и многомерной (несколько управляемых параметров) оптимизации;

линейной (при линейной зависимости критерия качества от параметров) и нелинейной оптимизации;

локальной (существует единственный экстремум) и глобальной (существуют несколько экстремумов) оптимизации.

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

Существуют многочисленные программы, предназначенные для решения оптимизационных задач. Одной из таких программ является средство Excel «Поиск решения».

Средство Excel «поиск решения»

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

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

Для выполнения операции Поиск решения использует команду меню Сервис – Поиск решения. Команда может отсутствовать в меню сервис. В этом случае нужно в меню Сервис выбрать команду Надстройки и установить в списке включенных надстроек нужный флажок:

Рисунок 1 Список подключенных настроек

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

Для выполнения операции Поиск решения нужно выполнить команду Сервис – Поиск решения и в диалоговом окне Поиск решения задать параметры решения:

адрес целевой ячейки, в которой будет подбираться значение;

критерий оптимальности (максимальное или минимальное значение) или значение, которое следует найти;

адреса изменяемых ячеек; при этом адреса отдельных ячеек или диапазонов разделяются запятыми; кнопка «Предположить» служит для автоматического выделения ячеек, влияющих на целевую;

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

Рисунок 2 Диалог "Поиск решения"

Кнопка «Параметры» позволяет изменить параметры поиска: способ поиска решения, время вычислений, точность определения результатов.

Рисунок 3 Диалог установки параметров поиска решения

В большинстве случаев достаточно использовать настройки по умолчанию. Это окно позволяет так же сохранить модель поиска или загрузить ранее сохраненную модель.

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

сохранить найденной решение в исходной таблице;

восстановить исходные значения;

сохранить результаты в виде сценария;

сформировать отчет по результатам выполнения операции.

Рисунок 4 Диалог "Результаты поиска решения"

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

В программе Excel-2007 средство «Поиск решения» вызывается пиктограммой из группы Анализ , расположенной на вкладке Данные.

Определение критического объема реализации с помощью поиска решений

Рассмотрим использование Поиска решений для решения уравнения.

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

Скопируйте заголовок и первую строку таблицы анализа затрат – доходов:

Объем
реализации
Q к

В столбце «Баланс» введите формулу: Прибыль от реализации — Затраты

С помощью средства «Поиск решения» определите величину Объема реализации, обеспечивающую нулевой баланс.

Указания.
1) Целевая ячейка в нашем случае – ячейка, в которой вычислен баланс; требуется установить в ней нулевое значение путем изменения ячейки с объемом реализации.

2) Математическая модель рассматриваемой задачи линейна.

3) Задача без ограничений.

Замечание. Иногда требуется проверить, какие ячейки влияют на вычисление значение в другой ячейке. Чтобы наглядно увидеть взаимное влияние ячеек, можно использовать команду меню Сервис – Зависимости формул – Влияющие ячейки (Зависимые ячейки).

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

Как с этим работать?

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

Итак, как же работает «поиск решения» в Excel 2010? Предположим, что перед вами стоит задача — распределение премии в вашей организации. Для простоты решения задачи предположим, что вам предстоит распределить премию между всеми работниками какого-то филиала. Размер премиального бюджета – 100 тысяч рублей. Сделать это можно пропорционально распределив размер премиальной выплаты размеру оплаты труда каждого конкретного работника.

Читать еще:  Рабочие способы, как поставить знак степени в Ворде (Word)

Применение надстройки «Поиск решения» в Excel: Методические указания и задания к лабораторной работе по курсу «Информатика»

предполагаемое значение и повторно запустить надстройку Поиск решения.

Решение системы нелинейных уравнений

Необходимо решить систему уравнений

Система содержит уравнение окружности и уравнение прямой. Решением этой системы являются точки пересечения окружности с прямой. При этом прямая может пересечь окружность в двух точках, коснуться окружности в одной точке или не пересечь окружность вовсе. Таким образом, решений этой системы может быть не более двух или не быть вообще. Пара (х, у) является решением системы уравнения в том случае, если она является решением уравнения с двумя неизвестными:

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

Рис. 8 Решение системы нелинейных уравнений

На рис. 8 в диапазоне A2:J11 представлена таблица значений функции двух переменных. В столбце А этого диапазона находится аргумент х, в строке 2 — аргумент у. Значения функции равные 1 (при х = 1 и у = -1) и 1,06 (при х = -1 и у = 1,5) расположены ближе всего к значению 0 (по сравнению с другими значениями функции). Эти две пары были взяты за первое приближение корней. В диапазон В14:С14 была помещена первая пара, а в диапазон В15:С15 — вторая В диапазоне D14:D15 введены формулы, реализующие левую часть уравнения, формула в ячейке D14 использует значения из диапазона В14:С14, а формула в ячейке D15 -из диапазона В15:С15

Чтобы найти первый корень, нужно выделить ячейку D14, вызвать окно Поиск решения и заполнить его, как показано на рис. 9.

Рис. 9 Поиск решения системы нелинейных уравнений

В рассмотренной задаче ограничения отсутствуют. В диалоговом окне Параметры поиска решения флажок Линейная модель должен быть сброшен. После нажатия кнопки Выполнить будет найден первый корень. Чтобы найти второй корень, потребуется вызвать Поиск решения для второй пары. На рис. 10 в диапазоне B13.D15 приведен результат решения задачи.

Рис. 10 Результат решения системы уравнений

Оптимизация с помощью надстройки Поиск решения

Надстройку Поиск решения можно использовать для решения задач оптимизации. В качестве простого примера рассмотрим уравнение

На рис. 11 представлен график функции, определяемой уравнением. Эта функция имеет максимум равный 26 при x = 4.

Рис. 11 График функции

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

Рис. 12 Задание начальных значений

Задав в диалоговом окне Поиск решения указанные на рис. 13 параметры, найдем максимум. Соответствующее значение переменной x: отображается в ячейке ВЗ (рис. 13).

Рис. 13 Окно Поиска решений

Рис. 14 Результаты поиска решения

В результате работы надстройки Поиск решения получилось значение 3,99999997656181, несколько отличающееся от 4 (рис. 14). Надстройка прекращает работу, когда достигнута заданная точность (precision). Чтобы установить необходимую точность, воспользуйтесь кнопкой Параметры, расположенной в диалоговом окне Поиск решения.

Поиск экстремума функции двух переменных

В области, заданной условиями и , функция имеет максимум в точке x=1.5708 (или ) и y=0.

Чтобы найти с помощью надстройки Поиск решения координаты x и y максимума функции f(x,y), введите в ячейки предполагаемые значения координат и рассматриваемую функцию (рис. 15).

Задайте в диалоговом окне Поиск решения параметры, необходимые для нахождения значений x и y, в которых формула в ячейке B5 имеет максимум. Так как у рассматриваемой функции бесконечное количество максимумов, необходимо ограничить область поиска условиями и (рис. 16).

Результаты поиска решения верны (рис. 16).

В заданной области у рассматриваемой функции имеется также локальный максимум в точке x=-1 y=2. Если задать в качестве нулевого приближения точки около этого локального максимума (например x=-0.9 y=1.8), то надстройка найдет значение этого локального максимума.

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

Работа с данными Excel

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

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector