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

Цель работы:

1. Решить задачу: по четырём заданным координатам точек в пространстве, определить, образуют ли они пирамиду, и если да, вычислить объём пирамиды, площадь её полной поверхности и высоты.

2. Решить задачу: по четырём заданным координатам точек в пространстве, определить, является ли четырёхугольник параллелограммом, и найти проекции его сторон на диагональ АС и угол между диагоналями. A(2; -1; -2),B(-2; 1; 1) ,C(3; 4; 2),D(7; 2; -1)

Задание:

1. Создать файл с именем «Аналитическая геометрия»

2. Переименовать лист 1 в «Пирамида»

3. На листе Пирамида оформить решение задачи 1:

4. Сформулировать задачу, создать рисунок к задаче с использованием инструментов панели Рисование. Серым цветом выделить область для ввода координат вершин.

5. Выделить цветом области, где поместить формулы, выполняющие следующие действия:

   1. Определение существования пространственного тела (доказательство того, что одна из заданных точек лежит вне плоскости заданной тремя другими).

   2. Вычисление объёма тела (используйте смешанное произведение векторов).

   3. Вычисление площадей граней (используйте векторное произведение векторов и вычисление длины вектора в координатах).

   4. Вычисление высот (используйте известный объём пирамиды и площади граней).

6. Переименовать лист 2 в «Параллелограмм».

7. На листе Параллелограмм оформить решение задачи 2:

8. Сформулировать задачу, создать рисунок к задаче с использованием инструментов панели Рисование. Серым цветом выделить область для ввода координат вершин.

9. Оформить решение задачи, самостоятельно выбрав план решения.

Результаты выполнения работы должны выглядеть приблизительно как на рисунке:

Рекомендации к выполнению работы (решение задачи 1):

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

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

Для определения компланарности векторов составьте матрицу из векторов образующих пирамиду и вычислите её определитель. Определитель матрицы вычисляется математической функцией МОПРЕД. Это функция работы с массивами (ввод формулы Ctrl+Shift+Enter).

Для вычисления скалярного произведения векторов используйте формулу с относительными адресами координат векторов.

Для вычисления векторного произведения векторов составьте матрицу, в первой строке которой находятся i,jиk, а во второй и третьей координаты перемножаемых векторов. Составьте разложение определителя этой матрицы по первой строке, для вычисления коэффициентов приi,jиkиспользуйте формулы с относительными адресами координат векторов.

При вычислении длины вектора используйте функцию КОРЕНЬ, для вычисления квадрата используйте ^2.

Отформатируйте области с вычислениями форматом «Дробный» с двумя цифрами в знаменателе.

Практическая работа №6. Решение задач линейного программирования в электронных таблицах excel с помощью надстройки «поиск решений».

Цель работы: Решить задачи оптимизации, сохранить несколько вариантов решения задачи подготовить отчёт по найденным решениям.

1. Для изготовления трех видов изделий А, В и С используется токарное, фрезерное, сварочное и шлифовальное оборудование. Затраты времени на обработку одного изделия для каждого из типов оборудования указаны в таблице. В ней же указан общий фонд рабочего времени каждого из типов используемого оборудования, а также прибыль от реализации одного изделия каждого вида.

Тип оборудования	Затраты времени (станко-часы) на обработку одного изделия каждого вида			Общий фонд рабочего времени оборудования (часы)
	А	В	С
Фрезерное	2	4	5	120
Токарное	1	8	6	280
Сварочное	7	4	5	240
Шлифовальное	4	6	7	360
Прибыль (руб.)	10	14	12

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

2. Продукцией городского молочного завода являются: молоко, кефир и сметана, расфасованные в бутылки. На производство 1 т молока, кефира и сметаны требуется соответственно 1010, 1010 и 9450 кг молока. При этом затраты рабочего времени при разливе 1 т молока и кефира составляют 0,18 и 0,19 машино-часов. На расфасовке 1 т сметаны заняты специальные автоматы в течение 3,25 часов. Всего для производства цельномолочной продукции завод может использовать 136000 кг молока. Основное оборудование может быть занято в течение 21,4 машино-часов, а автоматы по расфасовке сметаны – в течение 16,25 часов. Прибыль от реализации 1 т молока, кефира и сметаны соответственно равна 30, 22 и 136 тыс. руб. Завод должен ежедневно производить не менее 100 т молока, расфасованного в бутылки. На производство другой продукции не имеется никаких ограничений. Требуется определить, какую продукцию и в каком количестве следует ежедневно изготовлять заводу, чтобы прибыль от ее реализации была максимальной.

3. Имеется два пункта производства продукции: "Северный" и "Южный". Эти пункты способны производить ежемесячно 1,5 тыс. тонн и 2 тыс. тонн продукции соответственно. Имеется три пункта потребления этой продукции: "Горный", "Озерный" и "Лесной". Ежемесячные потребности этих пунктов в продукции составляют соответственно 0,8 тыс. тонн, 1,6 тыс. тонн и 1 тыс. тонн. Стоимость транспортирования 1 тонны груза от пункта производства к пункту потребления представлена в таблице.

Пункт назначения Пункт отправления	1. "Горный"	2. "Озерный"	3. "Лесной"
1. Северный	90	80	50
2. Южный	100	120	110

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

Задание:

1. Создать файл с именем «Линейное программирование». Переименовать «Лист 1» в «Детали». На листе «Детали» оформить решение задачи:

   1. Сформулировать математическую постановку задачу.

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

2. Подобрать допустимые значения плановых величин, попытаться максимизировать целевую функцию.

   1. Сохранить результаты подбора, создав 3 сценария по планируемым величинам, которые удовлетворяют граничным условиям

   2. Создать отчёт по этим сценариям.

3. Решить задачу с помощью команды «Сервис/Поиск решений».

   1. Максимизировать целевую функцию.

   2. Задать в качестве изменяемых ячеек адреса плановых величин

   3. Задать граничные условия.

   4. Сохранить полученное решение в виде сценария с именем «Оптимальный»

   5. Создать отчёт по результатам.

4. С помощью команды «Сервис/Поиск решений» решить задачу 2 и 3 самостоятельно создав таблицы и формулы для целевой функции и граничных условий (математическую модель).

Задача 1.

На листе EXCEL постройте информационную модель задачи. Расположите исходные данные в виде таблицы, выделите место для значений, которые будут являться решением задачи (количество деталей А, количество деталей В, количество деталей С), добавьте столбец для расчёта используемого рабочего времени по каждому оборудованию, добавьте ячейку для расчёта общей прибыли. Можно использовать функцию СУММАПРОИЗВ(), которая возвращает сумму произведений соответствующих элементов диапазонов-параметров. Ячейка В11 содержит формулу = СУММАПРОИЗВ(B5:D5;B10:D10)

Информационная модель может выглядеть, например, так как на рисунке:

Используйте информационную модель задачи для подбора необходимых количеств деталей (меняйте значения в диапазоне B5:D5).

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

Задача 2.

Результаты выполнения работы должны выглядеть приблизительно как на рисунке:

Рекомендации к выполнению работы:

1. Для построения сценариев с помощью команды «Сервис/Сценарии» в диалоговом окне «Диспетчер сценариев» нажать кнопку «Добавить» и ввести необходимые данные:

   1. В окне добавить сценарий ввести в поле имя сценария его имя (например, Подбор 1);

   2. В поле изменяемые ячейки адрес диапазона плановых величин;

   3. В окне значения сценария ввести подобранные значения для каждого параметра (например, количества единиц каждого вида продукции);

   4. Повторить указанные действия для создания ещё нескольких сценариев;

   5. В окне «Диспетчер сценариев» нажать кнопку «Отчёт», в окне « Отчёт по сценарию» выбрать тип отчёта «Структура», в поле «Ячейки результата» установить адреса целевой функции и плановых величин и «Ok».

   6. Ознакомиться с отчётом по сценариям, помещённом на отдельном листе.

2. Включить команду «Поиск решений» в меню Сервис с помощью команды «Сервис/ Надстройки…», установив флажок около Поиск решений.

3. Для решения задачи с помощью команды «Сервис/Поиск решений» в диалоговом окне «Поиск решений» ввести необходимые данные:

   1. в поле «Установить целевую ячейку» указать адрес ячейки, где вычисляется целевая функция;

   2. установить переключатель «Равной …. максимальному значению»;

   3. в поле «Изменяя ячейки» поместить адрес диапазона плановых величин;

   4. в поле «Ограничения» по одному добавить все ограничения, указывая адреса вычисляемых значений и условий, а также устанавливая необходимый знак неравенства;

   5. в диалоговом окне «Поиск решения» щёлкнуть кнопку «Выполнить»;

   6. в диалоговом окне «Результаты поиска решения» установить переключатель «Сохранить найденное решение», в окне тип отчёта выбрать «Результаты» и «Ok»;

   7. ознакомиться с отчётом по результатам, помещённом на отдельном листе.

14