Задача линейной оптимизации в Excel
Предприятие выпускает 3 вида изделий. Для выпуска единицы изделия необходимо сырье в количестве 3 кг для 1-го вида, 8 кг для 2-го вида и 1 кг для 3-го вида. Общий запас сырья составляет 9500 кг. Изделия по видам входят в комплект в количестве 2, 1 и 5 штук соответственно. Определить оптимальное количество выпуска изделий, при котором количество комплектов будет максимальным. Комплекты немедленно отправляются потребителю. Склад вмещает не более 20 штук лишних изделий 2-го вида.
Решение:
Пусть х – количество комплектов
Тогда 2*х – количество изделий вида1,
х – количество изделий вида2,
5*х – количество изделий вида3.
Целевая функция: х ->max.
Ограничение по сырью: 3*2*х+8*х+3*5*х≤9500
х1>=1, х2>=1, х3>=1.
Для решения задачи в Excel запишем ее в виде, представленном на рисунке:
В ячейку В3 введем формулу: =B2*2
В ячейку В4 введем формулу: =B2
В ячейку В5 введем формулу: =B2*5
В ячейку C6 введем формулу: =3*B3+8*B4+B5
В ячейку D7 введем формулу: =B2
Выберем команду Сервис-> Поиск решения. Введем параметры:
Нажмем кнопку Выполнить.
После нажатия кнопки ОК получим:
Ответ: предприятию следует выпускать в день 1000 изделий 1-го типа, 500 изделий 2-го типа и 2500 изделий 3-го типа, тогда количество комплектов будет максимально и равно 500 штук.
Задача определения количества вагонов для перевозки блоков
Завод выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ. Заказ представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Составить таблицу заказа и определить, сколько вагонов потребуется для отправки блоков заказчику.
Блоки разной марки не могут находиться в одном вагоне. Грузоподъемность одного вагона 60 тонн.
Решение:
С использованием формул, функций, абсолютных, относительных и смешанных ссылок выполним решение следующего задания в MS Excel.
Составим таблицу с характеристиками производимых блоков и таблицу заказа:
Вычислим вес одного блока. Для этого добавим в первую таблицу еще один столбец. В ячейку F3 введем формулу для расчета веса одного блока: =B3*C3*D3*E3.
Скопируем эту ячейку в ячейки F4 и F5.
В ячейке D8 рассчитаем количество вагонов. Введем формулу:
=ОКРУГЛВВЕРХ(I3*F3/6000;0)+ОКРУГЛВВЕРХ(I4*F4/6000;0)+ОКРУГЛВВЕРХ(5*F5/6000;0).
Получим, что необходимо 6 вагонов.
Таблица умножения в Excel двумя способами
Создать таблицу умножения чисел от 1 до 9 (9 строк, 9 столбцов). В ячейке, соответствующей произведению 1*1, должна быть записана формула, которая затем должна быть скопирована во все остальные 80 ячеек. Решение в двух вариантах:
1)с использованием смешанных ссылок;
2)с использованием формулы массивов.
Метод 1. С использованием смешанных ссылок.
Составим таблицу. Введем по столбцам цифры от 1 до 9 и по строкам.
В ячейку В2 введем формулу: =$A2*Bкопируем эту ячейку во все остальные ячейки диапазона В2:J10. Получим таблицу умножения:
Метод 2. С использованием формулы массивов.
В ячейки В2:J2 введем цифры от1 до 9 и в ячейки А14:А22.
Выделим ячейки В14:J22 и введем формулу =B13:J13*A14:A22. Нажмем Ctrl+shift+Enter, чтобы формула вставилась как формула массива. Получим:
Решение системы уравнений в Excel методом Крамера и обратной матрицы
Вычислить значения корней сформированной системы уравнений двумя методами: обратной матрицы и методом Крамера.
Введем данные значения в ячейки А2:С4 – матрица А и ячейки D2:D4 – матрица В.
Решение системы уравнений методом обратной матрицы
Найдем матрицу, обратную матрице А. Для этого в ячейку А9 введем формулу =МОБР(A2:C4). После этого выделим диапазон А9:С11, начиная с ячейки, содержащей формулу. Нажмем клавишу F2, а затем нажмем клавиши CTRL+SHIFT+ENTER. Формула вставится как формула массива. =МОБР(A2:C4).
Найдем произведение матриц A-1 * b. В ячейки F9:F11 введем формулу: =МУМНОЖ(A9:C11;D2:D4) как формулу массива. Получим в ячейках F9:F11 корни уравнения:
Решение системы уравнений методом Крамера
Решим систему методом Крамера, для этого найдем определитель матрицы.
Найдем определители матриц, полученных заменой одного столбца на столбец b.
В ячейку В16 введем формулу =МОПРЕД(D15:F17),
В ячейку В17 введем формулу =МОПРЕД(D19:F21).
В ячейку В18 введем формулу =МОПРЕД(D23:F25).
Найдем корни уравнения, для этого в ячейку В21 введем: =B16/$B$15, в ячейку В22 введем: = =B17/$B$15, в ячейку В23 введем: ==B18/$B$15.
Получим корни уравнения:
8. Упражнения и задания по теме“Решение задач из линейной алгебры”
В библиотеке функций Excel в категории математических есть следующие функции для выполнения операций над матрицами:
МОБР(массив) – обращение матрицы;
МОПРЕД(массив) – вычисление определителя матрицы;
МУМНОЖ(массив1;массив2) – умножение матриц;
ТРАНСП(массив) – транспонирование матриц.
8.1 Упражнение
Пусть заданы матрицы А=и В=. Требуется найти матрицу, являющуюся суммой матриц А и В.
Решение
1. В диапазон ячеек А2:C4 вводим элементы матрицы A, а в диапазон ячеек E2:G4 – элементы матрицы В (см. рис.9).
2. Выделяем диапазон, где будут размещаться элементы результирующей матрицы С, например I2:K4.
3. В выделенный диапазон вводим формулу: =А2:C4+ E2:G4.
4. Нажимаем комбинацию клавиш Ctrl + Shift + Enter. После выполнения операций в диапазоне ячеек G2:H3 будут помещены результаты
- 30 -
вычисленных значений элементов результирующей матрицы С (см. рис.19).
8.2 Упражнение
Умножить матрицу A=на число 3, то есть, получить матрицу С= А х 3.
Решение
1. В ячейки рабочего листа вводим элементы матрицы (например в диапазон А2:С4, см. рис.20).
2. Выделяем диапазон, в котором будет вычисляться результирующая матрица, например E2:G4.
3. В выделенный диапазон вводим формулу: =А2:C4*3.
4. Нажимаем комбинацию клавиш <Ctrl> + <Shif> + <Enter> (такая комбинация указывает, что должна быть выполнена операция с массивом ячеек). После выполнения операций в диапазон ячеек E2:G4 будут помещены результаты вычислений.
8.3 Упражнение
Решить систему линейных уравнений
Решение
В матричной форме система линейных уравнений записывается в виде
А х Х = В.
В частном случае, когда число уравнений в системе (m) равно числу неизвестных (n), т. е. m=n, то решение такой системы можно найти методом обратной матрицы в виде
X=A-1 х B,
где A-1 –матрица, обратная по отношению к матрице А.
В нашем случае матрица А=, Х=, В=
1. Присваиваем диапазону А3:С5 имя (например, А) и вводим в ячейки значения элементов матрицы А.
2. Присваиваем диапазону Е3:Е5 имя (например, В) и вводим значения элементов матрицы В.
3. Выделяем область G3:G5 для помещения результата решения системы и вводим в него формулу =МУМНОЖ(МОБР(А);В) (рис 21).
4. Нажимаем комбинацию клавиш <Ctrl>+<Shift>+<Enter>, в ячейках диапазона G3:G5 будет получен результат, как показано на рисунке 21.
