|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
Составим математическую модель.
Очевидно, что целью задачи является экономия материала. Тогда целевая функция F зависящая от трех переменных a, b и h – это просто площадь необходимого материала S. Необходимо найти при каких значениях a, b и h целевая функция F будет минимальной.
Итак, задача может быть записана в форме математической модели: F = S → min V = 2000. Эта запись читается так: минимизировать величину S при условии, что V = 2000. Подставив значения F и S, получим: F=2∙ (a∙ b + (a + b)∙ h) → min, а т.к. V=a∙ b∙ h, то a∙ b∙ h = 2000 И, наконец, нужно добавить ограничения (граничные условия) на параметры бака: a > 0, b > 0, h > 0.
Составим таблицу для решения задачи и решим ее средствами Excel. ª Будьте внимательны: имена столбцов набираются латинскими буквами! Ввод данных
Введите текст: в ячейку А1 – Целевая функция F; в ячейку А3 – Расчетные показатели; в ячейки А4: А7 – a=, b=, h=, V=. Ячейки D1, D2, D3 будем использовать для ввода начальных значений переменных a, b и h, для дальнейшего поиска оптимального решения. Пока будем считать их равными нулю. В ячейку B1 введем для целевой функции F формулу, которая будет иметь следующий вид =2*(D1*D2+(D1+D2)*D3) В ячейки B4, B5 и B6 последовательно введем формулу со ссылкой на ячейки D1, D2, D3 (в B4 =D1, в B5 =D2 и в B6 =D3), для дальнейшего ввода ограничений на параметры бака. В ячейку B7 введите формулу =D1*D2*D3 для расчета объема бака, т.е. ограничения по объему, который должен быть равен 2000. Приступим к поиску решения задачи. Выберите команду Сервис – Поиск решения. Появится окно диалога «Поиск решения» – рис. 1.
Рис. 1 Окно диалога «Поиск решения». Указатель находится в ячейке В1.
Так как формула для целевой функции находится в ячейке B1, то нужно щелкнуть мышью на этой ячейке. В поле окна диалога «Установить целевую ячейку» появится автоматически абсолютная ссылка $B$1. (Примечание: можно набрать значение абсолютной ссылки с клавиатуры, но щелкнуть мышью проще! ) В поле «Изменяя ячейки» укажите ссылку на диапазон ячеек D1: D3. Установите переключатель «Минимальное значение». Теперь необходимо ввести ограничение для поиска решения. Нажмите кнопку Добавить, чтобы ввести информацию в поле Ограничения – рис. 2.
Рис. 2 Окно диалога «Добавить ограничения» В поле Ссылка на ячейку введите B4. В списке неравенств выберите > = – больше или равно. В поле Ограничение введите число 0. Нажмите кнопку Добавить. Введите аналогичные ограничения для ячеек B5 и B6. Введите ограничение для ячейки B7: =2000. После ввода последнего ограничения нажмите клавишу Enter или кнопку OK. Окно диалога «Поиск решения» примет следующий вид – рис. 3.
Рис. 3 Окно диалога «Поиск решения» после ввода ограничений
Решение задачи.
Теперь все параметры для поиска решения введены и, можно дать команду к выполнению поиска решения. Нажмите кнопку Выполнить. В окне Результаты поиска решения выведено сообщение «Поиск не может найти подходящего решения», т.к. в ячейках D1, D2, D3 были заданы нулевые начальные значения (рис. 4).
Рис. 4 Окно Результаты поиска решения (при нулевых начальных данных! )
При решении задач линейного программирования достаточно часто не удается получить оптимального решения. Это происходит по следующим причинам. · Неправильная математическая модель или неправильные исходные данные. · Неограниченность целевой функции. При максимизации целевая функция должна быть ограничена сверху, при минимизации – снизу. Измените начальные условия в ячейках D1: D3 на 1. Снова выберите команду Сервис – Поиск решения и нажмите кнопку Выполнить. Теперь в окне поиска решения появилось сообщение: «Решение найдено. Все ограничения и условия оптимальности выполнены». (Примечание: если в эти ячейки ввести отрицательные числа, то согласно заданным ограничениям, решение все равно будет найдено).
Рис. 5 Окно Результаты поиска решения (при ненулевых начальных данных! )
Чтобы получить отчет по найденному решению в окне Результаты поиска решения выберите Тип отчета: Результаты, нажав кнопку OK, получите лист с отчетом по результатам поиска решения. Перед тем листом, где записана постановка задачи автоматически будет вставлен лист Отчет по результатам 1. В этом отчете содержится ответ на поставленную задачу. Искомый параллелепипед оказался кубом со сторонами ≈ 12, 6. Переименуйте рабочий лист и дайте ему название Бак. Задача № 2. План выгодного производства.
Предположим, что мы решили производить несколько видов конфет. Назовем их условно " A", " B" и " C". Конфеты можно производить в любых количествах (сбыт обеспечен), но запасы сырья ограничены. Известно, что реализация 10-ти килограмм конфет " А" дает прибыль 9 р., " В" – 10 р. и " С" – 16 р. Надо определить, каких конфет и сколько десятков килограмм необходимо произвести, чтобы общая прибыль от реализации была максимальной. На другом рабочем листе, назовите его Конфеты, создайте таблицу с данными норм расхода сырья как показано на рис. 6. (ячейки B6, C6, D6 имеют формат Денежный )
Рис. 6. Таблица с данными норм расхода сырья.
Создайте и введите формулы в таблицы для нахождения решения поставленной задачи, как указано на рис 7. Примечание: Количество конфет будет измеряться в десятках килограмм, т.к. нормы расхода сырья и получаемая от этого прибыль тоже приведены из расчета на 10 кг конфет.
Рис. 7. Таблицы с формулами для Поиска решения максимальной прибыли. Обратите внимание, что в ячейках А17, В17, С17 находится формула, отражающая сумму расхода сырья на производство каждого вида конфет, при этом каждая из них имеет ограничение по количеству имеющегося сырья. В меню Сервис активизируйте команду Поиск решения и опишите его ограничения, как показано на рис 8.
Рис. 8. Вид окна Поиск решения с заданными ограничениями.
Если Вы сделали все верно, то решение будет таким, как на рис 9.
Рис. 9. Вид рабочего листа " Конфеты" после нахождения решения.
Из решения видно, что оптимальный план выпуска предусматривает изготовление 80 кг конфет " В" и 200 кг конфет " С". Конфеты " А" производить не стоит. Полученная Вами прибыль составит 400 р. При этом сырье израсходуется полностью, кроме наполнителя, расход которого составит 84 кг из имевшихся 180 кг.
Задача № 3 Транспортная задача.
Определение оптимального плана перевозок продукции со складов в пункты реализации.
Нужно перевезти с минимальными затратами весь груз из трех складов в 2 города: Озеры и Луховицы. В Озеры надо перевезти 45 контейнеров, в Луховицы – 79. На складах имеется 124 контейнера, которые распределены следующим образом: склад № 1 – 18 контейнеров, склад № 2 – 75 и склад № 3 – 31.
Задание. Присвойте рабочему листу новое имя – Перевозки.
Стоимость перевозки одного контейнера со склада в пункт назначения приведена в таблице № 1. Таблица № 1. Стоимостное определение затрат на перевозку всех
На рисунке показана эта же таблица, выполненная в Excel.
Рис. 10. Таблица с внесенными данными
В таблице № 2 приведено количество контейнеров на каждом складе и их общее количество. А также общее количество потребности в контейнерах по каждому городу.
Таблица № 2.
Создайте Таблицу № 2 в Excel, как показано на рисунке. В таблице № 3 приведены обозначения для объемов перевозок " Склад–Город". Например, переменная х1 обозначает число контейнеров, которое должно быть перевезено в г. Озеры со склада № 1 при условии выполнения поставленной задачи. Таблица № 3. Распределение контейнеров по перевозкам " Склад–Город" (обозначения для объемов перевозок даны с учетом имен ячеек)
Создайте на этом же рабочем листе Таблицу № 3, пока без внесения
Рис. 11. Таблица для нахождения оптимального распределения
Составим математическую модель задачи:
Целевую функцию J определим как сумму затрат на перевозку всех контейнеров с каждого склада по городам, с учетом стоимости перевозки каждого контейнера со склада в город при известной потребности в них.
Она имеет следующий вид и зависит от 6 переменных: J(j1, j2, j3, j4, j5, j6) = 17*C18 + 12*C19 + 9*C20 + 6*D18 + 13*D19 + 8*D20
где: числа 17, 12, 9, 6, 13 и 8 являются содержимым ячеек В4: С6 и соответствуют переменным j1, j2, j3, j4, j5, j6 (таблица №1).
Запишем уравнения для ограничений.
Тот факт, что все контейнеры вывезены со складов по городам, записывается в виде трех уравнений: х1 + у1 = 18 х2 + у2 = 75 х3 + у3 = 31 Сумма контейнеров, полностью удовлетворяющая потребность каждого города дает еще два уравнения: х1 + х2 + х3 = 45 у1 + у2 + у3 = 79
Еще два ограничения совершенно естественны. Все значения переменных должны быть целыми и неотрицательными, т.к. речь идет о количестве контейнеров.
Задача поставлена, теперь можно приступить к ее решению.
Т.к. целевая функция определяет сумму всех затрат по перевозке всех контейнеров, то она примет вид формулы со ссылкой на соответствующие ячейки: =B4*C18+B5*C19+B6*C20+C4*D18+C5*D19+C6*D20 Введите формулу целевой функции в ячейку D4.
Введите в ячейку В18 формулу для ограничения =C18+D18. Введите в ячейку В19 формулу для ограничения =C19+D19. Введите в ячейку В20 формулу для ограничения =C20+D20.
Введите в ячейку В21 формулу для ограничения =B18+B19+B20. Введите в ячейку С21 формулу для ограничения =C18+C19+C20. Введите в ячейку D21 формулу для ограничения =D18+D19+D20.
Введите в ячейки С18: D20 начальные значения переменных. В данном случае положим эти значения нулевыми. (Или выделите весь диапазон этих ячеек, введите с клавиатуры 0, а затем нажмите Ctrl+Enter. )
Объедините ячейки D4, D5, D6 в одну и задайте формат ячейки,
Если все выполнено верно, должен получиться результат как на рис. 12.
Рис. 12. Вид рабочего листа Перевозки с нулевыми значениями
Выполните команду Сервис–Поиск решения. Появится окно диалога “Поиск решения”. В поле ввода Установить целевую ячейку введите ссылку на ячейку D4.
ª Установите переключатель Минимальное значение. В поле ввода Изменяя ячейки укажите ссылки на ячейки C18: D20.
Затем необходимо ввести информацию в поле Ограничения. Нажмите кнопку Добавить. Появится окно диалога “Добавить ограничения”. В поле ввода Ссылка на ячейку введите ссылку на ячейку В18. В поле ввода Ограничение введите = и число 18. Аналогичную операцию проделайте с ячейками В19, В20, С21 и D21.
Для ввода ограничения на целочисленность переменных. В поле ввода Ограничение введите для ячеек C18: D20: цел.
Для ввода ограничения на неотрицательность переменных возможны два варианта:
1. В окне диалога “Добавить ограничения” в поле ввода Ссылка на ячейку введите ссылку на диапазон ячеек C18: D20. В поле ввода Ограничение введите > = и число 0. 2. В окне диалога " Поиск решения" нажмите кнопку Параметры и затем в окне " Параметры поиска решения" поставьте флажки у параметров Неотрицательные значения и Линейная модель.
На рис. 13 и рис. 14 представлены заполненные окна диалога
Рис. 13. Заполненное окно " Параметры поиска решения"
Рис. 14. Заполненное окно " Поиск решения" по второму варианту. Нажмите кнопку Выполнить. Результаты решения показаны на рис. 15. Вы можете вывести результаты расчетов на отдельный лист.
Рис. 15. Результат решения транспортной задачи
Мы решали транспортную задачу при условии минимума целевой функции – стоимости перевозки всего груза. С помощью Excel мы легко можем оценить преимущество расчетов.
Выберите команду Сервис – Поиск решения и установите переключатель Максимальное значение.
Результат показан на рис. 16. Видно что максимальная стоимость перевозок равна 1556 у.е. Таким образом применение методов линейного программирования в Excel дает возможность уменьшить затраты на перевозки максимум на 270 у.е.
Рис. 16. Результат решения транспортной задачи
Примечание: Решение в целых числах не всегда может быть получено. В таких случаях в окне диалога “Результаты поиска решения” выводится соответствующее сообщение. Одной из возможных причин может быть ошибка в записи ограничений.
Microsoft Excel 97. Работа № 7 Цель работы: Работа с табличными базами данных, сортировка и фильтрация данных, Нахождение промежуточных итогов. Разделение и закрепление областей рабочего листа в Excel.
Списки, или табличные базы данных – это такие таблицы, в которых все строки, за исключением заголовков, имеют одинаковую структуру и типы данных. Для работы со списками в Eхcel имеются дополнительные возможности, которые перечислены в заголовке лабораторной работы. В программе Excel они реализованы в меню Данные. Рассмотрим на примере рабочей книги (файла) Продажа быттехники такую таблицу на листе Товары все перечисленные цели работы. Запустите программу Microsoft Eхcel, затем откройте заготовку файла: Сохраните файл в свою папку. Назовите его так: Продажа быттехники затем добавьте свою фамилию.
Упражнение 1. Сортировка данных в электронных таблицах.
Проанализируем содержимое ячеек таблицы. В столбцах A, B, C, D и E внесены наименования товаров, их модели, присвоены коды по порядку, цена поступления и количество каждого товара, в столбцах F, G и H введены формулы для расчета стоимости приобретенного товара, определения цены реализации с 20% наценкой и вычисления прибыли. Теперь рассмотрим, как можно выполнить сортировку данных в таблице Товары по полю Наименование товара . Поместите курсор на первую ячейку в столбце Наименование товара . Затем на панели инструментов Стандартная нажмите кнопку
Рис. 1. Вид таблицы Товары перед началом сортировки. Результатом этой операции должна стать таблица, в которой строки будут выстроены по алфавиту для данных первого столбца (рис. 2). При этом, как видно из рисунка, данные в остальных столбцах также переместились, отражая сведения по товару, а в столбце Коды последовательность нарушилась.
Рис. 2. Вид таблицы после сортировки по столбцу Наименование товара.
Действуя по аналогии, отсортируйте данные таблицы по возрастанию значений в столбцах Количество, Цена поступления, Планируемая прибыль. Из полученного результата видно, что первую позицию занял товар с нулевой прибылью ( рис. 3).
Рис. 3. Вид списка товаров после сортировки по столбцу Планируемая прибыль. Взаключение, отсортируйте строки таблицы снова по возрастанию столбца Код. Чтобы в дальнейшем было удобней работать с таблицей, изменим в ней порядок столбцов. Поставим в таблице столбец Код первым. Для этого необходимо вначале вставить пустой столбец перед столбцом Наименование товара. Поместите курсор на любую ячейку столбца А, затем в меню Вставка выберите пункт Столбцы (рис. 4). В таблице должен появиться новый пустой столбец (столбец A ). При этом вся таблица сместилась вправо. Обратите внимание, что формулы, по которым, выполнялись расчеты в трех последних столбцах, по-прежнему работают правильно, несмотря на то, что имена столбцов изменились (с C, D и E на D, E и F). Это произошло благодаря относительным ссылкам на ячейки в формулах.
Рис. 4
Теперь, нужно выделить весь столбец D и вырезать его в буфер обмена, вызвав правой кнопкой мыши контекстное меню (рис. 5).
Рис. 5 Затем вырезанный столбец нужно вставить на место столбца A (рис. 6). Для этого выделите весь столбец А, правой кнопкой мыши вызовите контекстное меню и дайте команду Вставить.
Рис. 6
Теперь первым столбцом в таблице стал столбец Код, а столбец D остался пустой. Удалите столбец D. Для этого выделите весь столбец D и в контекстном меню выберите пункт Удалить (рис. 7).
Запомните, как можно добавить новый столбец и как удалить ненужный столбец. Аналогично можно поступать и со строками электронной таблицы.
Рис. 7 Теперь требуется отсортировать данные в таблице по Наименованию товара, не сортируя при этом столбец с кодами. Выделите все ячейки с данными, исключая Код (диапазон ячеек В2: Н24), затем вызовите диалоговое окно Сортировка в меню Данные (рис. 8).
А) первоначальный вид окна Б) окончательный вид окна
Рассмотрим содержимое диалогового окна, оно содержит несколько областей, в которых задаются (выбираются) параметры к предстоящей сортировке данных. Три области с полями для выбора критерия сортировки, причем каждая область имеет два направления сортировки – по возрастанию и по убыванию, также имеется область для выбора признака, по которому будут идентифицированы выделенные в таблице ячейки: а) по подписям первой строки выделенного диапазона ячеек (рис. 8А); б) по обозначениям столбцов листа (рис. 8Б).
В нашем случае удобнее идентифицировать поля по обозначениям столбцов листа (по названиям столбцов рабочей книги Excel) и выбрать первый критерий сортировки Столбец В, а направление сортировки – по возрастанию. Установите все параметры для сортировки как показано на рис. 8Б. Нажмите кнопку OK. В результате этой операции все товары должны " выстроиться" по алфавиту, а Коды, поскольку они не попали в выделенный диапазон ячеек, так и остались располагаться по возрастанию. Вспомните, что происходило, когда просто сортировали товары по алфавиту!
Упражнение 2. Использование фильтров в электронных таблицах. В Excel фильтры позволяют показать в таблице только нужные данные, а ненужные скрыть. Самый простой способ фильтрации списков – использование встроенного в программу Excel Автофильтра.
Отобразим в таблице Товары только Холодильники. Выделите весь столбец B с наименованиями товаров, и в меню Данные выберите пункт Фильтр–Автофильтр
Рис. 9
В результате, в правом нижнем углу в шапке таблицы Наименование товара появится маленькая кнопка, открывающая список значений,
Рис. 10. Список значений для определения критерия фильтрации.
Если мы выберем в списке значение Холодильник, то в результате получим следующую таблицу (рис. 11). Обратите внимание, что кнопка Автофильтра изменила цвет на синий.
Рис. 11. Вид таблицы после назначения фильтра.
Для закрепления навыка по использованию фильтра назначьте фильтр на другие наименования товаров. Отменить фильтр можно, выбрав в списке фильтра значение (Все).
Фильтрация данных может быть и более сложной. Выделите все столбцы таблицы и назначьте для них Автофильтр. Теперь каждый столбец с данными может быть отфильтрован самостоятельно. В фильтре Наименование товаравыберите значение Газовая плита, а в фильтре Цена поступления – значение (Условие...). Затем в открывшемся окне Пользовательский автофильтр, настройте тип условия для фильтрации цены (например, меньше или равно ) и назначьте предельное значение для этого условия – 3 000 (рис. 12).
Рис. 12. Вид окна Пользовательский автофильтр.
В полученной таблице будут работать два фильтра. Один отображает только газовые плиты, другой из этих газовых плит покажет только те, цена у которых меньше или равна выбранному значению 3 000р.(рис. 13).
Рис. 13. Вид таблицы с двумя включенными фильтрами. Задания для самостоятельного выполнения.
Задание 1. Проведите сложную фильтрацию по каким-либо полям таблицы Товары. Задание 2. Для фильтрации поля Количество назначьте два условия в Пользовательском автофильтре – первый тип условия – меньше с предельным значением 30 и второй тип условия – больше с предельным значением 10. Задание 3. Отключите режим фильтрации для выполнения следующего Упражнения.
|
Последнее изменение этой страницы: 2017-03-17; Просмотров: 495; Нарушение авторского права страницы
Сортировка по возрастанию (рис. 1).
встречающихся в данном столбце. В этом списке можно выбрать любое значение (рис. 10). По нему и будет происходить фильтрация.
