Функции, входящие в категорию “Статистические“

ТРАНСП(массив)

Массив— это транспонируемый массив или диапазон ячеек на рабочем листе. Транспонирование массива заключается в том, что первая строка массива становится первым столбцом нового массива, вторая строка массива становится вбторым столцом нового массива и так далее.

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

1.Что называется матрицей?

2. Какие операции над матрицами вы знаете?

3. Перечислите функции в Ms Exsel, предназначенные для выполнения операций над матрицами.

Лабораторная работа №11. Использование Ms Excel для решения задач оптимального планирования.

Цель: получение навыков решения задач линейной и целочисленной оптимизации с помощью Ms Exsel.

Основные теоретические положения.

Решение задач планирования постоянно требует учета зависимостей одних факторов от других. Любое исследование начинается с выделения количественных характеристик исследуемого объекта. Такие характеристики называются величинами.

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

Математическая модель – это совокупность количественных характеристик некоторого объекта и связей между ними представленных на языке математики.

Объектами планирования могут быть разные системы: деятельность отдельного предприятия, отрасли промышленности и т. д. Постановка задачи планирования выглядит следующим образом:

• имеются некоторые плановые показатели x,y и другие;

• имеются некоторые ресурсы R1,R2 и другие, за счет которых эти плановые показатели могут быть достигнуты. Эти ресурсы практически всегда ограничены.

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

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

В Exsel имеется надстройка “Поиск решения”, которая позволяет решать задачи оптимизации. Она находится в меню Сервис/ Надстройки.

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

Пример. Фирма производит две модели сборных книжных полок A и B. Их производство ограничено наличием сырья ( высококачественных досок) и временем машинной обработки. Для каждого изделия модели A требуется 3 м² досок, для изделия модели B – 4 м². Фирма может получать от своих поставщиков до 1700м² досок в неделю Для каждого изделия модели A требуется 12 мин машинного времени, для изделия модели B – 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий можно будет выпускать фирме в неделю, если каждое изделие модели A приносит 2 у.е. прибыли, а каждое изделие модели B – 4 у.е.

Решение. Составим математическую модель. Обозначим: x – количество изделий модели A, выпускаемое в течение недели, y – количество изделий модели B. Прибыль от этих изделий равна 2x+4y у.е. Эту прибыль нужно максимизировать. Функция, для которой ищется максимум или минимум называется целевой. Беспредельному увеличению количества изделий препятствуют ограничения. Ограничено количество материала для полок и рабочего времени машины . Кроме того, количество изделий – неотрицательное число, поэтому .

	A	B	C	D
1	Переменные	0	x
2	Изделие A	0	y
3	Изделие B
4
5	Целевая функция
6	Прибыль		=2*x+4*y
7
8	Ограничения
9	Материал		=3*x+4*y	<=1700
10	Время изготовления		=0.2*x+0.5*y	<=160

Выделите ячейку, в которой вычисляется целевая функция, и вызовите команду Сервис/ Поиск решения.

Задание: 1.Фирма производит три вида продукции(A,B,C), для выпуска каждого требуется определенное время обработки на всех четырех устройствах I, II, III,IV.

Вид продукции	Время обработки , ч.				Прибыль, у.е.
	I	II	III	IV
A	1	3	1	2	3
B	6	1	3	3	6
C	3	3	2	4	4

Пусть время работы на устройствах соответственно 84, 42, 21, 42 часа. Определите, какую продукцию, и в каких количествах стоит производить для максимизации прибыли? ( Рынок сбыта для каждого продукта неограничен).

2. Фирме требуется уголь с содержанием фосфора не более 0.03% и с примесью пепла не более 3.25%. Доступны три сорта угля A, B и C по следующим ценам ( за одну тонну )

Сорт угля	Содержание примеси фосфора, %	Содержание примеси пепла, %	Цена, у.е.
A	00.6	2.0	30
B	0.04	4.0	30
C	0.02	3.0	45

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

3. Имеются три сплава. Первый сплав содержит 70% олова и 30% свинца, второй – 80% олова и 20% цинка, третий – 50% олова и, 10% свинца и 40% цинка. Из них необходимо изготовить новый сплав, содержащий 15% свинца. Какое наибольшее и наименьше процентное содержание олова может быть в этом сплаве?

Решение. Пусть u – количество первого сплава, v – количество второго сплава, w – количество третьего сплава, взятые для изготовления нового сплава. Так как в сплаве должно быть 15% свинца, получаем уравнение:

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

, , .

Тогда получаем ограничения: , причем переменные x,y,z неотрицательные. Целевая функция имеет вид: . Заполните рабочий лист. В параметрах установите флажки “Линейная модель” и “Неотрицательные значения”. Вычислите сначала максимальное значение, сохранив результаты поиска как Сценарий. Проделайте тоже самое при вычислении минимального значения. В меню Сервис/ Сценарии воспользуйтесь командой Отчет для просмотра ваших сценариев одновременно.

4. Фирма выпускает два набора удобрений для газонов: обычный и улучшенный. В обычный набор входят 3 фунта азотных, 4 фунта фосфорных и 1 фунт калийных удобрений, а в улучшенный – 2 фунта азотных, 6 фунтов фосфорных и 2 фунта калийных удобрений. Известно, что для некоторого газона требуется по крайней мере не меньше 10 фунтов азотных, 20 фунтов фосфорных и 7 фунтов калийных удобрений. Обычный набор стоит 3 у.е., а улучшенный – 4 у.е. Сколько и каких наборов надо купить, чтобы обеспечить эффективное питание почвы и минимизировать стоимость?

5. Имеются 6 предметов, каждый из которых характеризуется весом и ценой ( приведены в таблице). Нужно выбрать из них такие предметы, чтобы их общий вес не превышал 12, а суммарная цена была максимальной. ( При составлении целевой функции и ограничений использовать функции СУММПРОИЗВ)

Предмет	Вес	Цена
1	9	20
2	8	16
3	6	11
4	5	9
5	4	7
6	1	1

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

1. Какие характеристики называют величинами?

2. Что такое математическая модель?

3. Какие вам известны формы представления зависимостей между величинами?

4. В чем состоит задача оптимального планирования?

5. Как в Ms Exsel решаются задачи планирования?