Поиск

Полнотекстовый поиск:
Где искать:
везде
только в названии
только в тексте
Выводить:
описание
слова в тексте
только заголовок

Рекомендуем ознакомиться

'Программа'
Общероссийский союз общественных объединений «Всероссийский молодежный центр «ОЛИМП» совместно с ведущими вузами Москвы при поддержке Совета Федерации...полностью>>
'Документ'
09.13 ЗЛОБИН Максим Владимирович 3.09.13 ИВАКИНА Мария Юрьевна 3.09.13 ИЛЬИН Алексей Сергеевич 3.09.13 КАЗАРЯН Сергей Вартанович 3.09....полностью>>
'Реферат'
In the given course paper it is executed realisation of the digital automatic machine of Mile on elements "OR-NOT", the digital automatic machine as m...полностью>>
'Документ'
Цементировочные агрегаты - передвижные единицы, разработанные для выполнения при скважине работ по цементированию, растрескиванию, балансировке, давле...полностью>>

Главная > Решение

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

5

Практикум по Excel-2. Занятие 5

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

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

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

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

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

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

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

Q*k = extr Qk
X
Yj(х)<= Zj max

где X ={x1, x2, … xn}
Qk =Yj (X)

}

(1)

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      Отчетный
      период

      22.00

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

      Прибыль от
      реализации

      Постоянные
      затраты

      Переменные
      затраты

      Затраты

      Баланс

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

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

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

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

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

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

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

  1. Решение систем уравнений с помощью средства «Поиск решения»

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

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

x2+y2+z2-1=0

2x2+y2-2z=0

3x2-4y+z2=0

при начальных приближениях: x=1, y=1, z=1

  1. Контрольные вопросы

    1. Перечислите основные элементы оптимизационной математической модели.

    2. Опишите возможный порядок построения оптимизационной модели.

    3. Как классифицируются задачи оптимизации?

    4. Что означает понятие «линейная модель»?

    5. Какие задачи позволяет решать средство Excel Поиск решения?

    6. Каким образом можно настроить средство Поиск решения на решение линейной задачи оптимизации?

www.alural.narod.ru/inform/intro.htm Александр Ю. Алексеев



Похожие документы:

  1. Как и в других примерах, модель построена так, чтобы облегчить ее анализ и интерпре­тацию. Чтобы был понятен смысл чисел, все строки и столбцы имеют заголовки

    Документ
    ... описана надстройка Excel Поиск решения, которая лишена указанных недостатков. Это мощное средство, но при ... , которые упро­стят применение средства Excel Поиск решения; 3) использование средства Поиск реше­ния для оптимизации моделей ...
  2. Инструкция по использованию microsoft Excel для решения задач лп [5] 5 3 Одноиндексные задачи лп 6 > 3 Ввод исходных данных 6 > 3 Решение задачи 13

    Инструкция
    ... их математических моделей и поиску оптимальных решений средствами табличного редактора Microsoft Excel. В целях более эффективного ...
  3. Программа дисциплины «Методы оптимальных решений»

    Программа дисциплины
    ... . Айрис-Пресс, 2002. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. СПб: BHV – Санкт-Петербург. 1997 ... , методология. М.: Дрофа, 2001. Курицкий Б.Я. Поиск оптимальных решений средствами Excel 7.0. - СПб: BHV – Санкт-Петербург. 1997 ...
  4. Методика поиска документа в зависимости от того, какие реквизиты из­вестны. Методика поиска выбора ключевых понятий

    Документ
    ... MS Excel. Применение надстройки EXCEL: поиск решения для оптимизации перевозок. Применение надстройки EXCEL: поиск решения для ... надстройки EXCEL: поиск решения для оптимизации работы нотариальной конторы. Этапы разработки программных средств: ...
  5. 2. Диалоговое окно надстройки Поиск решения с помощью кнопки Добавить вводятся необходимые ограничения. Рис. 1

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

Другие похожие документы..