Microsoft Excel

Задание 1. Предположим, что в диапазон ячеек А1:Е2 введена матрица размера 2×5:

  . Необходимо получить транспонированную матрицу.

1.1. Выделите блок ячеек под транспонированную матрицу (5 × 2). Например, А4:В8.

1.2. Нажмите в строке формул кнопку Мастер функций.

1.3. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Ссылки и массивы, а в рабочем поле Выберите Функцию — имя функции ТРАНСП. После этого щелкните на кнопке Далее.

1.4. Появившееся диалоговое окно ТРАНСП мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:Е2 в рабочее поле Массив. После чего нажмите сочетание клавиш CTRL+SHIFT+ENTER.

1.5. Если транспонированная матрица не появилась в диапазоне А4:В8, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне А4:В8 появится транспонированная матрица:

Задание 2. В диапазон ячеек А10:С12 введена матрица:. Необходимо вычислить определитель этой матрицы.

2.1. Табличный курсор поставьте в ячейку, в которой требуется получить значение определителя, например, в А14.

2.2. Нажмите в строке формул кнопку Мастер функций.

2.3. В появившемся диалоговом окне Мастер функций в рабочем поле Категория => Математические, а в рабочем поле Функция — имя функции МОПРЕД. После этого щелкните на кнопке Далее.

2.4. Появившееся диалоговое окно МОПРЕД мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А10:С12 в рабочее поле Массив. Нажмите кнопку ОК.

В ячейке А14 появится значение определителя матрицы — 6.

Задание 3. В диапазон ячеек А16:С18 введена матрица   . Необходимо получить обратную матрицу.

3.1. Выделите блок ячеек под обратную матрицу, например, блок ячеек D16:F18

3.2. Нажмите в строке формул кнопку Мастер функций.

3.3. В появившемся диалоговом окне в рабочем поле Категория выберите Массивы, а в рабочем поле Функция — имя функции МОБР. Щелкните на кнопке Далее.

3. В появившееся диалоговое окно МОБР в рабочее поле  Массив введите диапазон исходной матрицы А16:С18.

4. Нажмите сочетание клавиш CTRL+SHIFT+ENTER.

В результате в диапазоне D16:F18 появится обратная матрица:

.

Задание 4. Пусть матрица А из рассмотренного выше примера, введена в диапазон А16:С18, а матрица В — в диапазон D16:F18. Элементы матрицы В задать самостоятельно. Необходимо найти матрицу С=А+В.

Решение.

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

4.2. Введите формулу для вычисления первого элемента результирующей матрицы =А16 + D16.

4.3. Скопируйте введенную формулу в остальные ячейки результирующей матрицы. В результате в ячейках H16:J8 появится матрица, равная сумме исходных матриц.

Подобным же образом вычисляется разность матриц, только в формуле для вычисления первого элемента вместо знака + ставится знак -.

Задание 5. Пусть, как и в предыдущем примере матрица А введена в диапазоны А16:С18. Необходимо получить матрицу С= 3×А.

Решение.

5.1. Табличный курсор поставьте в левый верхний угол результирующей матрицы например в А20.

5.2. Введите формулу для вычисления первого элемента результирующей матрицы =3*А20

5.3. Скопируйте введенную формулу в остальные ячейки результирующей матрицы. В результате в ячейках А20:С22 появится матрица, равная исходной матрице, умноженной на постоянную 3.

.

Задание 6. Предприятие выпускает продукцию трех видов: Р1, Р2, РЗ и использует сырье двух типов S1 и S2. Нормы расхода сырья характеризуются матрицей

 ,где каждый элемент показывает, сколько единиц сырья j-го типа расходуется на производство единицы продукции. План выпуска продукции задан матрицей-строкой В = (100, 130, 90). Необходимо определить затраты сырья для планового выпуска продукции.

Решение. Для нахождения затрат сырья необходимо вычислить произведение матриц В×A.

6.1. Создайте новый лист.

6.2. Введите матрицу А в диапазон А1:ВЗ, а матрицу В - в диапазон А4:С4.

6.3. Выделите блок ячеек под результирующую матрицу. Ее размерность будет т × р, в данном примере 1 × 2. Например, выделите блок ячеек F1:G1.

6.4. Нажмите в строке формул кнопку Мастер функций.

6.5. В появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите Массивы, а в рабочем поле Функция — имя функции функции МУМНОЖ. После этого щелкните на кнопке Далее.

6.7. В появившемся диалоговом окне МУМНОЖ в рабочее поле Массив 1 ведите диапазон исходной матрицы В — А4:С4, а диапазон матрицы А—А1:ВЗ введите в рабочее поле Массив 2. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER.

6.8. Если произведение матриц В×А не появилось в диапазоне F1:G1, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне F1:G1 появятся затраты сырья для планового выпуска продукции (то есть произведение матриц) В×А = (880, 900). Таким образом, для выполнения плана необходимо S1 = 880 единиц сырья первого типа и S2 = 900 единиц сырья второго типа.

Задание 7. Ресторан специализируется на выпуске трех видов фирменных блюд: В1, В2, ВЗ, при этом используются ингредиенты трех типов Sl, S2, S3. Нормы расхода каждого из них на одно блюдо и объем расхода ингредиентов на 1 день заданы таблицей:

Нужно найти ежедневный объем выпуска фирменных блюд каждого вида.

Пусть ежедневно ресторан выпускает х₁, блюд вида В1, х₂ блюд вида B2 и х₃ — блюд вида ВЗ. Тогда в соответствии с расходом ингредиентов каждого типа имеем систему:

7.1. Создайте новый лист в рабочем документе.

7.2. Введите матрицу А (в данном случае размера 3 × 3) в диапазон А1:СЗ.

Вектор  введите в диапазон E1:E3.

7.3. Найдите определитель матрицы А, для чего в ячейку В5 введите формулу =МОПРЕД(A1:C3).

7.4. Заменим в исходной матрице А коэффициенты при х₁ столбцом свободных членов и сформируем новую матрицу. В ячейку Е7 введем формулу =МОПРЕД(A7:C9). Таким образом, найдем определитель ∆х₁. Для нахождения коэффициента х₁ разделим ∆х₁на ∆. В G7 введем =E7/B5. Получим 200.

Аналогичным образом найдем х₂ и х₃. Результаты решения можно увидеть на рис. 8.1.

7.5. Рекомендуется сделать проверку, подставив найденные значения в уравнение системы. Для этого найденный вектор Х необходимо подставить в исходное матричное уравнение А × Х = В.

Для этого выделите диапазон ячеек F12:F14, введите формулу =МУМНОЖ(A1:C3;G7:G9) и для распространения на выделенные ячейки нажмите CTRL+SHIFT+ENTER.

Рис. 8.1. Решение Задания 7 методом Крамера

Решим Задание 7 методом обратной матрицы.

7.6. На этом же листе найдем обратную матрицу А^-1. Для этого:

–      выделите блок ячеек под обратную матрицу A20:C22;

–      нажмите на панели инструментов Стандартная кнопку Вставка функции;

–      в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите пункт Математические, а в рабочем поле Функция — имя функции МОБР. Щелкните на кнопке Далее;

–      появившееся диалоговое окно МОБР мышью отодвиньте в сторону от исходной матрицы и введите диапазон исходной матрицы А1:СЗ в рабочее поле Массив. Нажмите сочетание клавиш CTRL+SHIFT+ENTER;

–            если обратная матрица не появилась в диапазоне А20:С22, то следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ +ENTER. В результате появится обратная матрица.

7.4. Умножением обратной матрицы А^-1 на вектор В найдите вектор X. Для этого:

–      выделите блок ячеек под результирующую матрицу (под вектор X). Ее размерность будет т × р, в данном примере 3 × 1. Выделите блок ячеек Е20:Е22 (указателем мыши при нажатой левой кнопке);

–      нажмите на панели инструментов Стандартная кнопку Вставка функция;

–      в появившемся диалоговом окне Мастер функций в рабочем поле Категория выберите пункт Математические, а в рабочем поле Функция — имя функции МУМНОЖ. Щелкните на кнопке Далее; в появившемся диалоговом окне МУМНОЖ в рабочее поле Массив1 введите диапазон обратной матрицы А^-1 — А20:С22, а в рабочее поле Массив2 диапазон матрицы В — Е1:Е3. После этого нажмите сочетание клавиш CTRL+SHIFT+ENTER;

7.5. если вектор Х не появился, следует щелкнуть указателем мыши в строке формул и повторить нажатие CTRL+SHIFT+ENTER.

В результате в диапазоне D4:D6 появится вектор Х причем х₁ = 100 будет находиться в ячейке D4, х₂ =600— в ячейке D5, а х₃ = 100  в ячейке D6.

7.6. Сделайте проверку, умножив матрице коэффициентов А на матрицу найденных неизвестных. Результат решения можно увидеть на рис. 8.2.

Рис. 8.2. Решение Задания 7 методом обратной матрицы