 |
Задача линейного программирования о смесях
|
|
|
|
Стандартом предусмотрено, что октановое число автомобильного бензина А-76 должно быть не ниже 76, а содержание серы в нем – не более 0,3%. Для изготовления такого бензина на заводе используется смесь из четырех компонентов. Данные о ресурсах смешиваемых компонентов, их себестоимости и их октановом числе, а также о содержании серы приведены в таблице
| Характеристика | Компонент автомобильного бензина | |||
| № 1 | №2 | № 3 | №4 | |
| Октановое число | ||||
| Содержание серы, % | 0,35 | 0,35 | 0,3 | 0,2 |
| Ресурсы, т | ||||
| Себестоимость, у.е./т |
Приказом директора завода-изготовителя установлен следующий расход каждого компонента: 1 – 550 т, 2 – 10 т, 3 – 150 т, 4 – 290 т. Требуется определить, сколько на самом деле тонн каждого компонента следует использовать для получения 1000 т автомобильного бензина А-76, чтобы его себестоимость была минимальной. Какова упущенная выгода предприятия при производстве каждых 1000 т бензина при таком решении дирекции?
Ход работы
Пусть 
– количество в смеси компонента с номером i. С учетом этих обозначений задача минимума себестоимости принимает вид
Первое функциональное ограничение отражает необходимость получения заданного количества смеси (1000 т), второе и третье – ограничения по октановому числу и содержанию серы в смеси, остальные – ограничения на имеющиеся объемы соответствующих ресурсов (компонентов). Прямые ограничения очевидны, но принципиально важны для выбора метода решения. Для решения задачи средствами Excel необходимо составить таблицу.
| Решение задачи о смесях средствами Excel | |||||
| Переменные | Зна-чения | Критерий и ограничения | Результаты расчетов | Знак отношения | Ресурс |
| X1 | Целевая функция | =40*B3+45*B4+60*B5+90*B6 | |||
| X2 | Ограничение 1 | =СУММ(B3:B6) | = | ||
| X3 | Ограничение 2 | =68*B3+72*B4+80*B5+90*B6 | => | ||
| X4 | Ограничение 3 | =0,35*B3+0,35*B4+0,3*B5+0,2*B6 | <= | ||
| Ограничение 4 | =B3 | <= | |||
| Ограничение 5 | =B4 | <= | |||
| Ограничение 6 | =B5 | <= | |||
| Ограничение 7 | =B6 | <= |
|
|
|
Для решения задачи средствами Excel нужно воспользоваться программой-надстройкой Поиск решения, расположенной в пункте меню Сервис.
В открывшемся диалоговом окне следует установить:
- адрес целевой ячейки;
- диапазон адресов изменяемых ячеек;
- систему ограничений.
Добавления, изменения и удаления ограничений производятся с помощью кнопок Добавить, Изменить, Удалить. Кнопка Параметры открывает окно, в котором следует установить флажок Неотрицательные решения. Для нахождения оптимального решения следует нажать кнопку Выполнить.
Диалоговое окно Результаты поиска решения позволяет:
- сохранить на текущем рабочем листе найденное оптимальное решение;
- восстановить первоначальные значения;
- сохранить сценарий;
- выдать отчеты по результатам, устойчивости, пределам, необходимые для анализа найденного решения.
Если щелкнуть по кнопке ОК, то на месте исходной таблицы получим таблицу с найденными оптимальными значениями.
Оптимальное решение задачи имеет вид:
.
Решение дирекции:
.
Таким образом упущенная выгода предприятия при производстве каждых 1000 т бензина при таком решении дирекции составляет 407 у.е..
Лабораторная работа №6
Модель Леонтьева
Имеется n отраслей. Рассматривается процесс производства за один год. Обозначим через хi общий (валовой) объём продукции i - той отрасли, хij - объём продукции i - той отрасли, потребляемый j – той отраслью в процессе производства, уi - объём конечного продукта i - той отрасли для непроизводственного потребления. Выполняются соотношения баланса:
|
|
|
хi = Σ хij + уi , i = 1,…, n, j = 1,…, n.
Коэффициенты прямых затрат
аij = хij / хj
показывают затраты продукции i - той отрасли на производство единицы продукции j – той отрасли. Считаем, что аij = const. Тогда хij = аij. хj , т.е.
хi = Σ аij. хj + уi .
Если обозначить вектор валового выпуска через
,
матрицу прямых затрат через
А = 
,
а вектор конечного продукта через
,
то получаем матричное уравнение: Х = АХ + У.
Матрица А ≥ 0 (все элементы неотрицательны) называется продуктивной, если для любого вектора У ≥ 0 существует решение Х ≥ 0 этого уравнения. Модель Леонтьева в этом случае также называется продуктивной.
Условие продуктивности матрицы А: наибольшая из сумм элементов в столбцах матрицы А не превосходит 1, причем в А есть хотя бы один столбец, сумма элементов которого меньше 1.
Задание: Выяснить продуктивность модели Леонтьева, учитывая схему:
Ход работы
По условию х1 = 400, х2 = 500, у1 =150, у2 = 250, х11 = 50, х12 = 200, х21 = 100,
х22 = 150.
1). Находим элементы аij = хij / хj матрицы А средствами Excel:
А = 
А = 
.
2). Вычисляем максимум сумм элементов в столбцах матрицы А:
max (0,125 + 0,25; 0,4 + 0,3) = 0,7 < 1 А - продуктивная матрица.
3). Пусть новый вектор валового выпуска
Х = 
,
тогда из равенства Х = АХ + У получаем формулу для определения соответствующего вектора конечного продукта:
У = Х – АХ = (Е – А)Х = 
Замечание: действия с матрицами выполняем средствами Excel. Для выполнения вычитания матриц, стоящих в скобках, необходимо разместить обе матрицы в некоторых диапазонах и определить диапазон для размещения результата вычислений. Затем в левую верхнюю ячейку новой матрицы ввести формулу для вычисления (например, для вычитания матриц
«=A1- A5» и скопировать ее методом протягивания в остальные ячейки диапазона, отведенного под результат. Для выполнения умножения матриц нужно выделить на рабочем листе блок ячеек под матрицу – произведение 
. Размер этой матрицы в данном случае будет 2×1. Затем следует воспользоваться функцией МУМНОЖ из категории Математические. В качестве аргументов этой функции указать диапазоны перемножаемых матриц и нажать сочетание клавиш CTRL + SHIFT + ENTER.
4). Зададим новый вектор конечного продукта
|
|
|
У = 
и найдем соответствующий вектор валового выпуска
Х = (Е – А)-1∙ У = 
.
Матрица (Е – А)-1 называется матрицей полных затрат и определяется так: на рабочем листе нужно выделить блок ячеек под обратную матрицу 
и воспользоваться встроенной функцией МОБР из категории Математические. В качестве аргумента этой функции указать диапазон исходной матрицы 
и нажать сочетание клавиш CTRL + SHIFT + ENTER.
Варианты на решение задачи о продуктивности модели Леонтьева.
| х1 | ||||||||||
| х2 | ||||||||||
| х11 | ||||||||||
| х12 | ||||||||||
| х21 | ||||||||||
| х22 |
Лабораторная работа №6
Фильтрация списков.
Задание: При работе с базами данных важно иметь возможность быстро находить нужную информацию. Допустим, у вас имеется список телефонных номеров или таблица ассортимента товаров, и вам требуется, например, выбрать номера одной АТС или все коды моделей факсов. Конечно же, вам не захочется просматривать весь список и выписывать необходимую информацию. С помощью имён в полях вашего списка вы можете автоматически отфильтровать и вывести на экран только нужные вам данные.
Ход работы
1. Подготовьте данные к фильтрации. Создайте таблицу Ассортимент. Для этого вам необходимо создать лист Microsoft Excel и заполнить поля в соответствии с рис.1.
| B | C | D | E | F | G | |
| Товар | Модель | Название | Цена (тыс.руб) | Количество | Сумма | |
| Ксерокс | C100 GLS | Персональный | 827.00 | 564.00 | 466 428.00 | |
| Ксерокс | C110 GLS | Персональный | 993.00 | 623.00 | 618 639.00 | |
| Ксерокс | C200 GLS | Персональный Плюс | 1 430.00 | 568.00 | 812 240.00 | |
| Ксерокс | C210 GLS | Персональный Плюс | 1 716.00 | 269.00 | 461 604.00 | |
| Ксерокс | C300 GLS | Деловой | 2 470.00 | 412.00 | 1 017 640.00 | |
| Ксерокс | C310 GLS | Деловой | 2 698.00 | 574.00 | 1 548 652.00 | |
| Ксерокс | C400 GLS | Профессиональный | 4 270.00 | 223.00 | 952 210.00 | |
| Ксерокс | C410 GLS | Профессиональный | 5 124.00 | 652.00 | 3 340 848.00 | |
| Ксерокс | C420 GLS | Профессиональный | 6 415.00 | 895.00 | 5 741 425.00 | |
| Факс | F100 G | Персональный | 7 378.00 | 154.00 | 1 136 212.00 | |
| Факс | F150 G | Персональный | 1 608.00 | 214.00 | 344 112.00 | |
| Факс | F200 G | Персональный Плюс | 1 840.00 | 235.00 | 432 400.00 | |
| Факс | F250 G | Персональный Плюс | 1 730.00 | 221.00 | 382 330.00 | |
| Факс | F300 G | Деловой | 2 076.00 | 541.00 | 1 123 116.00 | |
| Факс | F350 G | Деловой | 2 551.00 | 544.00 | 1 387 744.00 | |
| Факс | F400 G | Профессиональный | 2 761.00 | 53.00 | 146 333.00 | |
| Факс | F450 G | Профессиональный | 3 513.00 | 542.00 | 1 904 046.00 | |
| Факс | F500 G | Профессиональный | 4 878.00 | 325.00 | 1 585 350.00 |
Рис 1.
|
|
|
Значения колонки Сумма получаютпутём умножения значений столбца Цена на значения столбца Количество: в ячейке G2 поставьте знак =, затем выделите значение ячейки F2, поставьте знак умножения и далее значение ячейки Е2, нажмите Enter. Получив число, протяните мышкой до конечной ячейки столбца G.
Вы получили полную таблицу данных, в которой вы будете проводить фильтрацию.
2. Выделите ячейку В1. В меню Данные (Data) выберите команду Фильтр-Автофильтр (Filter-AutoFilter). В заголовках каждого столбца появятся кнопки фильтра со стрелочками вниз.
Примечание. В некоторых базах данных при этом появляется диалоговое окно, спрашивающее, хотите ли вы использовать верхнюю строку в качестве заголовка. В диалоге щёлкните на кнопке ОК.
3. Щёлкните на стрелке фильтра в ячейке D1. Это заголовок Название. В появившемся окне списка критериев необходимо выбрать нужный критерий (ключ) фильтрации.
| Название |
| (Все) |
| (Первые 10…) |
| Деловой |
| Персональный |
Критерии фильтрации находятся в списке в алфавитном порядке.
4. В качестве ключа фильтрации в ячейке D1 выделите слово Персональный.
В списке останутся только названия факсов и ксероксов, включающие слово «Персональный». Все остальные записи таблицы не удалены, а лишь временно скрыты. Порядковые номера строк в списке теперь не являются последовательными (рис. 2).
| Товар | Модель | Название | Цена (тыс.руб) | Количество | Сумма |
| Ксерокс | C100 GLS | Персональный | |||
| Ксерокс | C110 GLS | Персональный | |||
| Факс | F100 G | Персональный | |||
| Факс | F150 G | Персональный |
Рис 2.
5. Щёлкните на кнопке со стрелкой в ячейке В21 (рис 2.). Выделите в списке ключ Ксерокс. Список персональных ксероксов станет видимым на вашем рабочем листе. Чтобы снова вывести на экран полный список, нужно щёлкнуть на любой кнопке со стрелкой фильтра, и в списке критериев фильтрации выделить строку Все (All). Или в меню Данные (Data) выбрать команду Фильтр – Показать все (Filter – Show All)
6. Восстановите список. В меню Данные выберите команду Фильтр - Отобразить всё. Теперь на рабочем листе снова находится полный список товаров.
|
|
|
7. Составьте список десяти наиболее доходных видов товаров.
В поле Сумма установите критерий фильтрации Первые 10 (Тор 10). Появится диалоговое окно Наложение условия по списку (Тор10 AutoFilter).
Убедитесь, что параметры окна в группе Вывести установлены так: «10», «наибольших», «элементов списка», и щёлкните на кнопке ОК.
На рабочем листе останется список товаров, значения в поле Сумма которых составляют десять наибольших в полном списке величин.
Восстановите полный список, выбрав команду Фильтр - Отобразить всё в меню Данные.
8. Задайте свои критерии поиска данных.
В этом упражнении вы составите список ксероксов и факсов с названиями «Персональный» и «Персональный Плюс».
Щёлкните на стрелке фильтра поля Название (ячейка D1 см приложение рис.1). В списке ключей выберите Условие. Откроется диалоговое окно Пользовательский автофильтр (Custom AutoFilter). Убедитесь, что в группе Название в верхнем поле операторов находится слово «равно». Щёлкните на стрелке соседнего поля и выделите строку Профессиональный. Включите опцию ИЛИ (OR). Щёлкните на стрелке нижнего поля операторов и выделите «равно». Щёлкните на стрелке нижнего поля критериев и выделите строку Персональный Плюс. Нажмите ОК.
Литература
1. Приходько А.И. Регрессионный анализ средствами EXCEL.Учебное пособие. - Ростов -на – Дону, издательство «Феникс»,2007, 250стр.
2. Демиденко Е.З. Линейная и нелинейная регрессия. – М.: Финансы и статистика. 2001, 302 стр.
3. Шалабанов А.К., Роганов Д.А. Эконометрика. Учебно-методическое пособие. – Академия управления «Тисби», Казань, 2004, 133 стр.
4. Федосеев В.В., Гармаш А.Н., Дайитбегов Д.М., Орлова И.В., Половников В.А. Экономико-математические методы и прикладные модели. Учебное пособие. Москва. ЮНИТИ, 2002,388стр.
|
|
|
