Тема 3: Транспортная задача в MS Excel
3.1 Предполагается, что с двух складов развозят товары по трём магазинам, стоимости перевозок единицы продукции заданы в виде таблицы (руб.)
| Магазин № 1 | Магазин № 2 | Магазин № 3 | |
| Склад № 1 | |||
| Склад № 2 |
На 1-м складе хранится 100 ед. продукции, на 2-м -150 единиц, в 1-й магазин требуется доставить 70 единиц продукции, во 2-ой – 80, в 3-й – 100 единиц продукции, соответственно. Как следует транспортировать товары для минимизации цен на перевозки?
3.2 Компания владеет двумя фабриками F1 и F2, производящими электронное оборудование. Фабрики в течении некоторого периода выпускают 16 и 12 тыс. изделий, соответственно. Компания снабжает трёх потребителей С1, С2 и С3, потребности которых в течение одного и того же периода составляют, соответственно, 10, 13 и 7 тыс. изделий. Стоимость перевозок 1 тыс. изделий потребителю с фабрик приведена в таблице:
| Потребитель | |||
| С1 | С2 | С3 | |
| F1 | |||
| F2 |
Составьте план перевозок, чтобы затраты были наименьшими.
3.3 Заводы фирмы расположены в городах Лидсе и Кардиффе. Они доставляют товары на склады городов Манчестер, Бирмингем и Лондон. Расходы на транспортировку товара между этими городами приведены в таблице:
| Манчестер | Бирмингем | Лондон | |
| Лидс | |||
| Кардифф |
Завод в г. Лидсе выпускает в год 800 т товаров, в г. Кардиффе – 500 т. Манчестерский склад вмещает 400 т, бирмингемский – 600 т, а лондонский – 300 т. Как следует транспортировать товары для минимизации цен на перевозки?
3.4 Предполагается, что с двух складов развозят товары по трём магазинам, стоимости перевозок единицы продукции заданы в виде таблицы (руб.)
| Магазин № 1 | Магазин № 2 | Магазин № 3 | |
| Склад № 1 | |||
| Склад № 2 |
На 1-м складе хранится 200 ед. продукции, на 2-м -350 единиц, в 1-й магазин требуется доставить 100 единиц продукции, во 2-ой – 280, в 3-й – 170 единиц продукции, соответственно. Как следует транспортировать товары для минимизации цен на перевозки?
3.5 Компания владеет двумя фабриками F1 и F2, производящими электронное оборудование. Фабрики в течение некоторого периода выпускают 20 и 18 тыс. изделий, соответственно. Компания снабжает трёх потребителей С1, С2 и С3, потребности которых в течение одного и того же периода составляют, соответственно, 10, 12 и 16 тыс. изделий. Стоимость перевозок 1 тыс. изделий потребителю с фабрик приведена в таблице:
| Потребитель | |||
| С1 | С2 | С3 | |
| F1 | |||
| F2 |
Составьте план перевозок, чтобы затраты были наименьшими.
3.6 Заводы фирмы расположены в городах Лидсе и Кардиффе. Они доставляют товары на склады городов Манчестер, Бирмингем и Лондон. Расходы на транспортировку товара между этими городами приведены в таблице:
| Манчестер | Бирмингем | Лондон | |
| Лидс | |||
| Кардифф |
Завод в г. Лидсе выпускает в год 1000 т товаров, в г. Кардиффе – 1500 т. Манчестерский склад вмещает 600т, бирмингемский – 1100 т, а лондонский – 800 т. Как следует транспортировать товары для минимизации цен на перевозки?
3.7 Предполагается, что с двух складов развозят товары по трём магазинам, стоимости перевозок единицы продукции заданы в виде таблицы (руб.)
| Магазин № 1 | Магазин № 2 | Магазин № 3 | |
| Склад № 1 | |||
| Склад № 2 |
На 1-м складе хранится 200 ед. продукции, на 2-м -300 единиц, в 1-й магазин требуется доставить 240 единиц продукции, во 2-ой – 140, в 3-й – 120 единиц продукции, соответственно. Как следует транспортировать товары для минимизации цен на перевозки?
3.8 Компания владеет двумя фабриками F1 и F2, производящими электронное оборудование. Фабрики в течении некоторого периода выпускают 32 и 25тыс. изделий, соответственно. Компания снабжает трёх потребителей С1, С2 и С3, потребности которых в течение одного и того же периода составляют, соответственно, 20, 22 и 15 тыс. изделий. Стоимость перевозок 1 тыс. изделий потребителю с фабрик приведена в таблице:
| Потребитель | |||
| С1 | С2 | С3 | |
| F1 | |||
| F2 |
Составьте план перевозок, чтобы затраты были наименьшими.
3.9 Заводы фирмы расположены в городах Лидсе и Кардиффе. Они доставляют товары на склады городов Манчестер, Бирмингем и Лондон. Расходы на транспортировку товара между этими городами приведены в таблице:
| Манчестер | Бирмингем | Лондон | |
| Лидс | |||
| Кардифф |
Завод в г. Лидсе выпускает в год 1000 т товаров, в г. Кардиффе – 1200 т. Манчестерский склад вмещает 800 т, бирмингемский – 650 т, а лондонский – 750 т. Как следует транспортировать товары для минимизации цен на перевозки?
3.10 Предполагается, что с двух складов развозят товары по трём магазинам, стоимости перевозок единицы продукции заданы в виде таблицы (руб.)
| Магазин № 1 | Магазин № 2 | Магазин № 3 | |
| Склад № 1 | |||
| Склад № 2 |
На 1-м складе хранится 150ед. продукции, на 2-м -250 единиц, в 1-й магазин требуется доставить 150 единиц продукции, во 2-ой – 100, в 3-й – 150 единиц продукции, соответственно. Как следует транспортировать товары для минимизации цен на перевозки?
3.11 Компания владеет двумя фабриками F1 и F2, производящими электронное оборудование. Фабрики в течение некоторого периода выпускают 30 и 25 тыс. изделий, соответственно. Компания снабжает трёх потребителей С1, С2 и С3, потребности которых в течение одного и того же периода составляют, соответственно, 15, 30 и 10 тыс. изделий. Стоимость перевозок 1 тыс. изделий потребителю с фабрик приведена в таблице:
| Потребитель | |||
| С1 | С2 | С3 | |
| F1 | |||
| F2 |
Составьте план перевозок, чтобы затраты были наименьшими.
3.12 Заводы фирмы расположены в городах Лидсе и Кардиффе. Они доставляют товары на склады городов Манчестер, Бирмингем и Лондон. Расходы на транспортировку товара между этими городами приведены в таблице:
| Манчестер | Бирмингем | Лондон | |
| Лидс | |||
| Кардифф |
Завод в г. Лидсе выпускает в год 1000 т товаров, в г. Кардиффе – 1500 т. Манчестерский склад вмещает 1600т, бирмингемский – 600 т, а лондонский – 300 т. Как следует транспортировать товары для минимизации цен на перевозки?
Пример решения контрольной работы № 2
Задание 2.
а) Найти параметры линейной и квадратичной зависимости методом наименьших квадратов
б) Построить графики исходной, линейной и квадратичной зависимостей
в) Оценить погрешность найденных зависимостей
| Зависимость | |||||||
| хi | 0,6 | 0,9 | 1,4 | 1,5 | 4,5 | ||
| уi | |||||||
Решение:
а) Найдём параметры линейной и квадратичной зависимости методом наименьших квадратов
Будем искать линейную зависимость в виде: 
Определить параметры линейной зависимости можно, решив систему линейных уравнений: 
Для этого построим таблицу и найдём соответствующие суммы:
| хi | уi | хi×уi | хi2 | |
| 0,6 | 3,6 | 0,36 | ||
| 0,9 | 7,2 | 0,81 | ||
| 1,4 | 9,8 | 1,96 | ||
| 1,5 | 7,5 | 2,25 | ||
| 4,5 | 40,5 | 20,25 | ||
| å | 8,9 | 68,6 | 25,63 |
Составим систему линейных уравнений и, решив её, найдём параметры линейной зависимости: 
Воспользуемся формулами Крамера:
| ∆= | 25,63 | 8,9 | = | 48,94 |
| 8,9 | ||||
| ∆a= | 68,6 | 8,9 | = | 31,5 |
| ∆b= | 25,63 | 68,6 | = | 286,51 |
| 8,9 |
| а= | 31,5 | = | 0,643645 |
| 48,94 | |||
| b= | 286,51 | = | 5,854311 |
| 48,94 |
Полученная линейная зависимость: 
Будем искать квадратичную зависимость в виде: 
Определить параметры квадратичной зависимости можно, решив систему линейных уравнений: 
Для этого построим таблицу и найдём соответствующие суммы:
| хi | уi | хi×уi | хi2 | хi3 | хi4 | хi2×уi | |
| 0,6 | 3,6 | 0,36 | 0,216 | 0,1296 | 2,16 | ||
| 0,9 | 7,2 | 0,81 | 0,729 | 0,6561 | 6,48 | ||
| 1,4 | 9,8 | 1,96 | 2,744 | 3,8416 | 13,72 | ||
| 1,5 | 7,5 | 2,25 | 3,375 | 5,0625 | 11,25 | ||
| 4,5 | 40,5 | 20,25 | 91,125 | 410,0625 | 182,25 | ||
| å | 8,9 | 68,6 | 25,63 | 98,189 | 419,7523 | 215,86 |
Составим систему линейных уравнений и, решив её, найдём параметры квадратичной зависимости: 
Воспользуемся формулами Крамера:
| 419,7523 | 98,189 | 25,63 | |||
| ∆= | 98,189 | 25,63 | 8,9 | = | 296,2079 |
| 25,63 | 8,9 | ||||
| 215,86 | 98,189 | 25,63 | |||
| ∆a= | 68,6 | 25,63 | 8,9 | = | 127,9836 |
| 8,9 | |||||
| 419,7523 | 215,86 | 25,63 | |||
| ∆b= | 98,189 | 68,6 | 8,9 | = | -496,698 |
| 25,63 | |||||
| 419,7523 | 98,189 | 215,86 | |||
| ∆b= | 98,189 | 25,63 | 68,6 | = | 2301,534 |
| 25,63 | 8,9 |
| a= | 127,9836 | = | 0,432074 |
| 296,2079 | |||
| b= | -496,698 | = | -1,67686 |
| 296,2079 | |||
| c= | 2301,534 | = | 7,769996 |
| 296,2079 |
Полученная квадратичная зависимость: 
б) Построим графики исходной, линейной и квадратичной зависимостей
Для этого найдём значения функции по приближённым формулам линейной и квадратичной зависимостей, результат оформим в виде таблицы:
| хi | уi | уприбл.лин. | уприбл.квадр. | |
| 0,6 | 6,240499 | 6,919428 | ||
| 0,9 | 6,433592 | 6,610804 | ||
| 1,4 | 6,755415 | 6,26926 | ||
| 1,5 | 6,819779 | 6,226876 | ||
| 4,5 | 8,750715 | 8,973631 |
Построим графики функций:
в) Оценим погрешность найденных зависимостей по формуле:
 |
Линейная зависимость
| хi | уi | уприбл | (уi-уприбл)2 | |
| 0,6 | 6,240499 | 0,05784 | ||
| 0,9 | 6,433592 | 2,453634 | ||
| 1,4 | 6,755415 | 0,059822 | ||
| 1,5 | 6,819779 | 3,311597 | ||
| 4,5 | 8,750715 | 0,062143 | ||
| å | 8,9 | 5,945035 |
| Погрешность | 2,438244 |
Квадратичная зависимость
| хi | уi | уприбл | (уi-уприбл)2 | |
| 0,6 | 6,919428 | 0,845348 | ||
| 0,9 | 6,610804 | 1,929865 | ||
| 1,4 | 6,26926 | 0,53398 | ||
| 1,5 | 6,226876 | 1,505225 | ||
| 4,5 | 8,973631 | 0,000695 | ||
| å | 8,9 | 4,815114 |
| Погрешность | 2,194337 |
Вывод: При нахождении значения функции использование эмпирической функции квадратичной зависимости даёт меньшую погрешность, чем использование эмпирической функции линейной зависимости.
Задание 3: Решите транспортную задачу средствами специальных средств электронного процессора Microsoft Excel (Поиск решения).
Задача:Имеются три пункта поставки однородного груза А1, А2, А3 и пять пунктов В1, В2, В3, В4, В5 потребления этого груза. На пунктах А1, А2 и А3 находится груз соответственно в количестве а1, а2 и а3 т. В пункты В1, В2, В3, В4 и В5 требуется доставить соответственно b1, b2 , b3,, b4 и b5 т. груза. Расстояние между пунктами поставки и пунктами потребления приведено в следующей матрице – таблице:
| Пункты поставки | Пункты потребления | ||||||||
| В1 | В2 | В3 | В4 | В5 | |||||
| А1 | |||||||||
| А2 | |||||||||
| А3 |
Найти такой план закрепления потребителей за поставщиками однородного груза, чтобы общие затраты по перевозкам были минимальными.
Решение:
Внесём данные задачи в таблицу по образцу и заполним соответствующими формулами:
| A | B | C | D | E | F | G | H | ||
| Потребители Поставщики | В1 | В2 | В3 | В4 | В5 | ||||
| А1 | SA1 | ||||||||
| =СУММ(C6:G6) | |||||||||
| А2 | SA2 | ||||||||
| А3 | SA3 | ||||||||
| SB1 | SB2 | SB3 | SB4 | SB5 | |||||
 =C6+C8+C10 | |||||||||
| Общие затраты = |  |  | |||||||
Общие затраты вычисляются по формуле: =C5*C6+D5*D6+E5*E6+F5*F6+G5*G6+ C7*C8+D7*D8+E7*E8+F7*F8+G7*G8+ C9*C10+D9*D10+E9*E10+F9*F10+G9*G10 (сумма произведений затрат на перевозку единицы груза на количество груза из пункта поставки Аi в пункт потребления Вj)
1. Установить курсор – подсветку в ячейку значения целевой функции С13
2. Из меню Сервискомандой Поиск решения вызывается одноимённое диалоговое окно, в котором производятся следующие установки:
a. Установить целевую ячейку $С$13 (уже будет установлена)
b. Равной минимальному значению
c. Изменяя ячейки
· на рабочем поле выделить диапазон ячеек C6:G6, который соответствует поставкам груза из пункта А1
· поставить точку с запятой (;)
· на рабочем поле выделить диапазон ячеек C8:G8, который соответствует поставкам груза из пункта А2
· поставить точку с запятой (;)
· на рабочем поле выделить диапазон ячеек C10:G10, который соответствует поставкам груза из пункта А3
d. ОграниченияДобавить
 |
| № ограничения | Ссылка на ячейку | Условие | Ограничение | |
| SA1 ($H$6) | = | А1 ($B$5) | Добавить | |
| SA2 ($H$8) | = | А2 ($B$7) | Добавить | |
| SA3 ($H$10) | = | А3 ($B$9) | Добавить | |
| SB1 ($C$12) | = | В1 ($C$4) | Добавить | |
| SB2 ($D$12) | = | В2 ($D$4) | Добавить | |
| SB3 ($E$12) | = | В3 ($Е$4) | Добавить | |
| SB4 ($F$12) | = | В4 ($F$4) | Добавить | |
| SB5 ($G$12) | = | В5 ($G$4) | Добавить | |
| Поставки груза из пункта А1 (C6:G6) | >= | Добавить | ||
| Поставки груза из пункта А2 (C8:G8) | >= | Добавить | ||
| Поставки груза из пункта А3 (C10:G10) | >= | Ок |
e. Параметры: вызывается окно Параметры поиска решения, где после проверки линейности модели нажимается кнопка ОК
3. Выполнить
После выполнения данной задачи результат будет следующим:
из А1 в В4 100 т груза; из А1 в В5 100 т груза;
из А2 в В2 5 т груза; из А2 в В3 80 т груза; из А2 в В4 90 т груза;
из А3 в В1 100 т груза; из А3 в В2 125 т груза.
Общие расходы при этом будут составлять 1610 усл. ед.
Основная литература
1. Информатика: учеб. Пособие для студ. Пед. Вузов/ А.В.Могилёв, Н.П.Пак, Е.К.Хеннер; Под ред. Е.К.Хеннера. – М.: Изд. Центр «Академия», 2000
2. Информатика: Учебник. – 3-е перераб. Изд. /Под ред. Проф. Н.В.Макаровой. – М.: Финансы и статистика, 2001.
3. Информатика для юристов и экономистов/ Симонович С.В. и др. – СПб.: Питер, 2001.
4. Острейковский В.А. Информатика: Учебник для вузов – М.: Высшая школа, 2000.
Дополнительная литература
1. Фигурнов В.Э. IBM для пользователя. Краткий курс. – ИНФА-М, 2000г.
2. Савельев А.Я. Основы информатики. Учебник для вузов – М.: 2001 г.
3. Аладьев В.З. и др. Основы информатики. Учебное пособие. М.: «Филинъ», 1999 г.
ОГЛАВЛЕНИЕ
| Стр. | ||
| Введение | ||
| 1. | Контрольная работа №1 по информатике для группы П-11З 1.1. Требования к оформлению 1.2. Варианты контрольной работы 1.3. Вопросы контрольной работы | |
| 2. | Контрольная работа №1 по информатике для группы П-11С 2.1. Требования к оформлению 2.2. Варианты контрольной работы 2.3. Вопросы контрольной работы | |
| 3. | Контрольная работа № 2 по информатике для групп П-21с, П-21з 3.1. Требования к оформлению 3.2. Разделы контрольной работы 3.3. Варианты контрольной работы 3.4. Тема 1: Табличные вычисления. 3.5. Тема 2 Аппроксимация функции: метод наименьших квадратов 3.6. Тема 4: Транспортная задача в MS Excel | |
| 4. | Пример решения контрольной работы № 2 4.1. Задание 2 4.2. Задание3 | |
| 5. | Основная литература | |
| 6. | Дополнительная литература |
Учебно-методическое издание
Ермолова
Галина Александровна
Малышева
Елена Васильевна
ИНФОРМАТИКА
Методические рекомендации и задания
для решения контрольной работы
Волгоградский государственный архитектурно-строительный университет
Себряковский филиал