70138

ЭЛЕКТРОННАЯ ТАБЛИЦА EXCEL. ПОИСК РЕШЕНИЯ. ПОДБОР ПАРАМЕТРА

Лабораторная работа

Информатика, кибернетика и программирование

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

Русский

2014-10-16

383 KB

75 чел.

ЛАБОРАТОРНАЯ РАБОТА

ЭЛЕКТРОННАЯ ТАБЛИЦА EXCEL. ПОИСК РЕШЕНИЯ. ПОДБОР ПАРАМЕТРА.

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

Методические указания

1. Решение задач оптимизации

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

Необходимо:

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

2. Освоить методику и технологию оптимизации планов производства продукции в табличном процессоре Excel с помощью программы Поиск решения (Solver).

Рассмотрим пример. (пример необходимо выполнить)

Постановка задачи:

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

Запас комплектующих на складе ограничен.

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

Табличная модель.

В таблице1. приведены исходные данные

Таблица 1

Для решения задачи необходимо внести исходные данные и формулы для вычислений

Запустите Excel, переименуйте один из рабочих листов и присвойте ему имя Оптимизация_1. Исходные данные рассматриваемой задачи разместите на этом листе.

   Сохраните рабочую книгу в рабочей папке. Имя файла выберите самостоятельно.

Внесение исходных данных и формул.

При составлении таблицы необходимо ввести исходные данные и формулы для вычислений. Представление формул и исходных данных дано в таблице 2.

Примечание:

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

Таблица 2.

В колонке С (плановый расход комплектующих) введены формулы вычисления суммы произведений норм расхода ресурсов на плановое количество продукции.

В строке 13 (Прибыль по видам изделий) прибыль на единицу продукции умножается на количество изделий по плану В строке 14 суммируется прибыль по всей продукции из строки 14.

Решение задачи

1. Программа оптимизации Поиск решения (Solver)

Для вызова программы оптимизатора выберите команду меню Сервис>Поиск решения. Если команда Поиск решения отсутствует в меню Сервис, то надо установить эту надстройку.

1.1. Установка программы Поиск решения

В меню Сервис выберите команду Надстройки. В диалоговом окне Надстройки установите флажок Поиск решения. Если диалоговое окно Надстройки не содержит команды Поиск решения, нажмите кнопку Обзор и укажите диск и папку, в которой содержится файл надстройки Solver.xla (как правило, это папка Library\Solver folder) или запустите программу Setup, если найти файл не удается.

Надстройка, указанная в диалоговом окне Надстройки, остается активной до тех пор, пока она не будет удалена.

1.2. Настройка математической модели

Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Поиск решения (рис. 1) и настроить математическую модель.

Диалоговое окно Поиск решения

Окно Поиск решения  вызывается командой меню Сервис Поиск решения.

Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу. В нашем примере — это ячейка D14 (прибыль).

Рис 1

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

Поле Изменяя ячейки служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле Установить целевую ячейку. В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек, разделяя их запятыми. В нашем примере введен диапазон ячеек D4:F4, содержащий искомые величины плана производства продукции. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек.

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

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

Команда Добавить служит для отображения диалогового окна Добавить ограничение.

Команда Изменить служит для отображения диалоговое окна Изменение ограничения.

Команда Удалить служит для снятия указанного курсором ограничения.

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

Команда Закрыть служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Добавить, Изменить или Удалить.

Кнопка Параметры служит для отображения диалогового окна Параметры поиска решения, в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения.

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

Ввод и редактирование ограничений

Диалоговые окна изменения и добавления ограничений одинаковы см. рис. 2.

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

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

Рис. 2

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

Чтобы приступить к набору нового условия, нажмите кнопку Добавить.

Чтобы вернуться в диалоговое окно Поиск Решения, нажмите кнопку ОК.

Условные операторы целого и двоичного типа можно применять только при наложении ограничений на изменяемые ячейки.

Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения.

По окончании счета появляется таблица и диалоговое окно Результаты поиска решения, рис. 3

После нажатия клавиши OK отображается таблица с исходными данными и результами решения

Рис. 3

Решить самостоятельно

Переименуйте один из рабочих листов и присвойте ему имя Оптимизация_2. Исходные данные задачи разместите на этом листе.

Задача. Завод производит электронные приборы трех видов (прибор А, прибор В и прибор С), используя при сборке микросхемы трех видов (тип 1, тип 2 и тип 3). Расход микросхем задается следующей таблицей:

Прибор А

Прибор В

ПриборС

ПриборD

Тип 1

2

5

1

3

Тип 2

2

0

4

1

Тип З

2

1

1

4

Стоимость

60

40

25

35

Ежедневно на склад завода поступает 500 микросхем типа 1 и по 400 микросхем типов 2 и 3. Каково оптимальное соотношение дневного производства приборов различного типа, если производственные мощности завода позволяют использовать запас поступивших микросхем полностью?

2. Решение уравнений средствами программы Excel

Задача. Найти решение уравнения  x3 - 3x2 + х = -1.

1. Создайте новый рабочий лист (Вставка > Лист), дважды щелкните на его ярлычке и присвойте ему имя Уравнение.

2. Занесите в ячейку А1 значение 0.

3. Занесите в ячейку В1 левую часть уравнения, используя в качестве  переменной x ссылку на ячейку А1. Соответствующая формула может, например, иметь вид

 =А1 ^3-3*A1 ^2+А1.

4. Дайте команду Сервис > Подбор параметра.

5. В поле Установить в ячейке укажите В1, в поле Значение задайте -1, в поле Изменяя значение ячейки укажите А1.

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

7. Повторите расчет, задавая в ячейке А1 другие начальные значения, например 0,5 или 2. Совпали ли результаты вычислений? Чем можно объяснить различия?

  

Выполнить задания.

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

1) x3 + 8x - 15=0  4) 3x - 3 x-2 = 72

2) 9*5 x+1 -  5x = 5500 5) x3 + 8x - 9 =0

3) 3 3x+1 - 2*33x = 27  6) 2x2 - 8x+ 8 =0

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

 3 x1 - 5 x2 =13 3 x1 - 4x2 = -6

 2 x1+ 7x2 =81       3 x1 + 4x2 =81

5 x1 + 8x2+ x3 = 2  2x1 - 3x2 +  x3  = -7

3x1 - 2x2+6x3= -7  x1 + 4x2 + 2x3 = -1

2 x1 + x2 - x3= -5   x1 - 4x2              = -5

2 x - 4 y +  9 =28  5x1 + 8x2 +  x3   = 2

7 x+3 y – 6   = -1  3x1 - 2x2 +  6x3 = -7

7 x+9 y – 9   = 5  2x1 + x2 -  x3       = -5

2 x - 7 y + z = - 4  2 x - 4 y + 9 z = 28

3 x +  y – z   = 17  7 x + 3 y - 6z  = - 1

x -  y +3z     = 3  7x +  9y - 9z   = 5

Решение выполнить с использованием трех методов.

Используя Поиск решения, матричное уравнение (AX = B  X =  A-1B)  и применяя метод Крамера.

Расчетные таблицы для разных методов разместите на различных листах рабочей книги.

Варианты выполняемых заданий согласовать с преподавателем

Решение с использованием матричного уравнения

X =  A-1B– матричная запись решения рассматриваемой системы

А – это основная матрица системы

A-1обратная матрица А.

X  и  B – это вектор столбец независимых переменных и вектор столбец свободных членов

Решение.

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

1. Исходные данные внести на рабочий лист.

2. Для расчета обратной матрицы выделим диапазон ячеек, совпадающий по размеру с основной матрицей.

3. Активизируем верхнюю левую ячейку диапазона, создаваемой обратной матрицы и вызываем функцию МОБР.

 

4. В диалоговом окне вводим диапазон, в котором размещена основная матрица системы. Для ввода выделяем мышью диапазон в таблице исходных данных.

5. CTRL + SHIFT + ENTER.

6. Для определения вектора столбца независимых переменных выделим диапазон.

7. Активизируем верхнюю ячейку диапазона, и вызываем функцию МУМНЖ.

8. В диалоговом окне указываем параметры перемножаемых массивов

9. CTRL + SHIFT + ENTER

Метод Крамера

Метод   Крамера  (хi =i / ,  

 где      -   определитель   основной   матрицы,   

         I    -  определитель полученный из основного подстановкой вместо j-ro столбца столбец свободных членов.

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

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


 

А также другие работы, которые могут Вас заинтересовать

42923. Компьютерные вирусы: классификация, проблемы борьбы с ними 613.68 KB
  Web 16 О Доктор Веб 16 Основной логотип Dr.origin Вредоносная программа детектированная с помощью технологии Origins Trcing технология несигнатурного поиска разработанная специалистами Доктор Веб Вирусы для разных ОС В данную группу объединяют вредоносные программы разработанные для определенных операционных систем ОС.Web О Доктор Веб Доктор Веб российский разработчик средств информационной безопасности. Антивирусная продукция компании Доктор Веб создана на основе собственной технологии.
42924. Синтезирование и моделирование широкополосного трансформатора сопротивления, выполненного на четвертьволновых линиях передачи 222.05 KB
  В результате получить конструкцию трансформатора сопротивлений соответствующую требованиям установленным заданием и ОСТом. Этапы выполнения работы: Синтез трансформатора сопротивлений в распределенном электрическом элементном базисе. Моделирование Подстройка трансформатора сопротивлений согласно требованиям задания. Подпись Дата Лист 4 НГТУ Rг = 10 Ом внутреннее сопротивление источника сигнала; Rн = 50 Ом сопротивление нагрузки; fн = 1ГГц fв = 2ГГц диапазон частот; KстU = 12 коэффициент стоячей волны напряжения на...
42925. Государственное регулирование рыночной экономики 113.59 KB
  Необходимость вмешательства национального государства в рыночную экономику. Подходы российского государства к макроэкономической политике стратегия тактика. Во всем мире функции государства в управлении экономикой значительно расширились в течение ХХ века особенно в промышленно развитых странах. Необходимость вмешательства национального государства в рыночную экономику.
42926. Сестринский процесс при ревматизме 644 KB
  На данный момент по данным статистики ВОЗ ревматизм остается одной из главных причин инвалидизации трудоспособного населения во многих развивающихся странах, тогда как в развитых странах частота встречаемости заболевания постепенно снижается. По данным статистики: за 2008 год на территории РФ было зарегистрировано 1.929 чел. с острой ревматической лихорадкой, 12.291 чел. с хроническими ревматическими заболеваниями сердца, 8.831 чел. с ревматическими пороками клапанов.
42927. Управление проектами 61.84 KB
  Общая характеристика управления проектами 10 2 2 6 2. Обоснование целесообразности проекта 10 8 2 6 6 3. Основные формы организационной структуры проекта 10 4 4 6 4.
42928. Краткая характеристика предприятия ОАО «Германий» 111.27 KB
  За эти годы предприятие прошло путь от поставщика продукции на внутренний рынок до экспортера всей номенклатуры выпускаемой продукции в США Израиль Японию страны Европы и Азии. В настоящее время предприятие ГЕРМАНИЙ единственное в России имеющее полный цикл переработки широкую номенклатуру продукции и большие производственные мощности. Команда профессионалов создает продукцию и услуги индивидуально учитывая пожелания потребителей по срокам поставки и качеству продукции ориентируясь на их планы в области бизнеса. до готовой...
42929. Сестринский процесс при стенокардии 173.99 KB
  Предмет изучения: сестринский процесс при стенокардии. Цель исследования: изучение сестринского процесса при стенокардии. Для достижения поставленной цели исследования необходимо изучить: Этиологию и предрасполагающие факторы стенокардии; Клиническую картину и особенности диагностики; Методы исследования и подготовку к ним; Принципы лечения и профилактику стенокардии; Манипуляции выполняемые медицинской сестрой; Особенности сестринского процесса при стенокардии. Нестабильная стенокардия: впервые возникшая стенокардия;...
42930. Расчет двухкаскадного резистивного усилителя на биполярных транзисторах 1.87 MB
  Расчет двухкаскадного резистивного усилителя на биполярных транзисторах пояснительная записка к курсовой работе по электронике Студент гр.130601 Аннотация Данная пояснительная записка написана к курсовой работе по дисциплине Электроника для варианта 03 и содержит в себе результаты расчета резистивного усилителя на биполярных транзисторах. В качестве анализируемого усилителя выступает двухкаскадный усилитель на кремниевых биполярных транзисторах основные параметры которого рассчитываются в одной из...
42931. Анализ организационно-правовых форм предприятий и их особенностей 69.31 KB
  Центральным звеном рыночной экономики в котором принимаются и осуществляются решения об использовании ограниченного количества благ с учётом обстоятельств внешней среды которые не могут быть изменены по воле принимающих решения лиц выбора вариантов решения проблем альтернатив развития или независимых друг от друга вариантов действия направленных на достижение желаемых конечных результатов системы целей являются хозяйствующие субъекты организации предприятия домашние хозяйства...