Табличные формулы

Цели:

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

Введение

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

Представленные примеры, упражнения и задачи предназначены для углубленного изучения возможностей процессора электронных таблиц Excel и для получения представления о матрице. Учащиеся должны понимать, что многие прикладные задачи в технике, экономике и др.областях сводятся к решению системы линейных уравнений и что важно уметь их решать.

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

К записи алгоритмов на языке программирования можно приступить на втором этапе, или вообще изучать язык программирования не в базовом курсе, а в курсе профильной подготовки.

Табличные формулы (array formula) называют также формулами массива. Хотя второе название является дословным переводом английского термина и используется в Справке, первое название представляется более благозвучным. Табличные формулы — очень мощное средство Excel, позволяющее в формулах обращаться с блоками, как с обычными ячейками. И хотя механизм работы табличных формул усваивается с некоторыми затруднениями, эти формулы позволяют давать удивительно компактные решения сложных задач. В некоторых задачах вообще невозможно обойтись без табличных формул.

Excel позволяет ввести формулу, которая будет выполняться не в одной ячейке, а в прямоугольной области ячеек, вычисляя сразу несколько зависимых значений. Причем в качестве аргументов такая формула может принимать не одно значение, а опять же - массив. Чтобы ввести такую формулу, надо выделить прямоугольный диапазон, в строке формул набрать выражение формулы и нажать клавиши CTRL+SHIFT+ENTER.

ТРАНСПОНИРОВАНИЕ МАТРИЦЫ

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

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

Пример 1

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

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

Решение.

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

Нажмите Вставка функции f_x

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

В рабочее поле Массив введите диапазон исходной матрицы А1:Е2.

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

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

ВЫЧИСЛЕНИЕ ОПРЕДЕЛИТЕЛЯ МАТРИЦЫ

Возвращает определитель матрицы (матрица хранится в массиве).

МОПРЕД(массив)

Массив — числовой массив с равным количеством строк и столбцов.

Определитель матрицы — это число, вычисляемое на основе значений элементов массива. Для массива A1:C3, состоящего из трех строк и трех столбцов, определитель вычисляется следующим образом: МОПРЕД(A1:C3) равняется A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)

Заметки

Массив может быть задан как интервал ячеек, например A1:C3, или как массив констант, например {1;2;3:4;5;6:7;8;9}, или как имя, именующее интервал или массив.

Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвращает значение ошибки #ЗНАЧ!.

МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов.

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

МОПРЕД производит вычисления с точностью примерно 16 значащих цифр, что может в некоторых случаях приводить к небольшим численным ошибкам.

Пример 2

Предположим, что в диапазон ячеек А1:С3 введена матрица размера 3х3 .

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

Решение.

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

Нажмите Вставка функции f_x

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

В рабочее поле Массив введите диапазон исходной матрицы А1:С3.

Нажмите ОК. В ячейке А4 появится значение определителя матрицы – 6.

НАХОЖДЕНИЕ ОБРАТНОЙ МАТРИЦЫ

МОБР(массив) - возвращает обратную матрицу для матрицы, хранящейся в массиве.

Массив — числовой массив с равным количеством строк и столбцов.

Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную — это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0.

В качестве примера того, как вычисляется обратная матрица, рассмотрим массив из двух строк и двух столбцов A1:B2, который содержит буквы a, b, c и d, представляющие любые четыре числа. В следующей таблице приведена обратная матрица для A1:B2:

Некоторые квадратные матрицы не могут быть обращены, в таких случаях функция МОБР возвращает значение ошибки #ЧИСЛО!. Определитель такой матрицы равен 0.

Пример 3

Предположим, что в диапазон ячеек А1:С3 введена матрица размера 3х3

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

Решение.

Выделите блок ячеек под обратную матрицу, например блок ячеек А5:С7.

Нажмите Вставка функции f_x

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

В рабочее поле Массив введите диапазон исходной матрицы А1:С3.

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

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

СЛОЖЕНИЕ И ВЫЧИТАНИЕ МАТРИЦ

Складывать (вычитать) можно матрицы одного размера. Суммой матриц А=(a_ij) и В=(b_ij) размера mхn называется матрица С=А+В, элементы которой c_ij= a_ij+ b_ij для i=1,2, ...,m; j=1,2, ..., n (т.е. матрицы складываются поэлементно).

Пример 4

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

Необходимо найти матрицу С, являющуюся их суммой C=A+B.

Необходимо найти матрицу D, являющуюся их разностью D=A-C.

УМНОЖЕНИЕ МАТРИЦЫ НА ЧИСЛО

Произведением матрицы на число k является матрица B=kA, элементы которой b_ij=kb_ij для i=1,2, ...,m; j=1,2, ..., n (т.е. каждый элемент матрицы умножается на эту постоянную.

Пример 5

Введите матрицу А в диапазон А1:С2.

Необходимо найти матрицу C=3A.

Необходимо найти матрицу D=1/2хA.

УМНОЖЕНИЕ МАТРИЦ

Возвращает произведение матриц. Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2.

МУМНОЖ(массив1;массив2)

Массив1, массив2 — перемножаемые массивы.

Пусть А=(a_ij) размерности m?n, B=(b_ij) размерности n?p, тогда размерность произведения А х В равна m х p.

Матрица С называется произведением матриц А и В, если каждый ее элемент с_ij равен сумме произведений элементов i-той строки матрицы А на соответствующие элементы j-того столбца матрицы В:

Таким образом, перемножение матриц осуществляется по следующему правилу:

 

Из этого правила следует: что всегда можно перемножать две квадратные матрицы одного порядка, в результате получим квадратную матрицу того же порядка. В частности, квадратную матрицу всегда можно умножить саму на себя, т.е. возвести в квадрат.

Другим важным случаем является умножение матрицы–строки на матрицу–столбец, причём ширина первой должна быть равна высоте второй, в результате получим матрицу первого порядка (т.е. один элемент). Действительно,

Пример 6

Пусть матрица А из рассмотренного примера введена в диапазон A1:D3, а матрица В - в диапазон А4:В7. Необходимо найти произведение этих матриц С.

Решение.

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

Нажмите Вставка функции f_x

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

В рабочее поле Массив1 введите диапазон исходной матрицы А - А1:D3, а в рабочее поле Массив2 введите диапазон исходной матрицы B – А4:B7

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

Многие свойства, присущие операциям над числами, справедливы и для операций умножения матриц.

А(ВС)=(АВ)С – ассоциативность

А(В+С)=АВ+АС – дистрибутивность

(kA)B=A(kB)=k(AB)

Однако имеются и специфические свойства операций умножения матриц

Умножение матриц некоммутативно - АВ ВА

Если Е – единичная матрица, то ЕА=А; ВЕ=В. Таким образом, единичная матрица играет при умножении ту же роль, что и число 1 при умножении чисел.

Из того, что АхВ=0, не следует, что А=0 или В=0

В алгебре матриц нет действия деления. Выражение А/В не имеет смысла. Его заменяют два различных выражения В^-1 х А и А х В^-1 если существует В^-1. Для квадратных матриц возможна операция возведения в степень. По определению полагают, что А⁰=Е и А¹=А. Целой положительной степенью А^т(т > 1) квадратной матрицы А называется произведение т матриц, равных А, то есть:

Упражнения

Найдите произведение матриц А х В, где А=(1 2 3),

Найдите произведение матриц С = А х Е, где

Найдите матрицу, обратную данной:

Воспользуйтесь определением обратной матрицы (А х А^-1 = А^-1 х А = Е) и проверьте, верно ли найдена обратная матрица в упражнении 3: для этого найдите произведение матрицы А в упражнении 3 на обратную матрицу. Таким же способом проверьте, что А^-1 х   А = Е.

Какие из матриц можно перемножить? Найдите эти произведения.

Вычислите: (А х В) х С; А х (В х С).

Покажите вычислением, что для указанных матриц верно утверждение: (А+ В)С = АС + ВС.

Вычислите:

Какое свойство умножения матриц демонстрирует данный пример?

Найдите матрицы, обратные данным:

РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ УРАВНЕНИИ

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

Система п линейных уравнений с п неизвестными.

Пусть дана линейная система п уравнений с п неизвестными, где a_ip b_t (i = 1, 2,..., п; j= 1, 2, ...,n) — произвольные числа, называемые, соответственно, коэффициентами при переменных и свободными членами уравнений.

Такая запись называется системой линейных уравнений в нормальной форме.

Решением системы называется такая совокупность п чисел (х₁ = k₁, x₂ = k₂,..., х_n = k_n), при подстановке которых каждое уравнение системы обращается в верное равенство.

Система уравнений совместна, если она имеет хотя бы одно решение и несовместна, если она не имеет решений.

Если совместная система уравнений имеет единственное решение, она называется определенной; напротив, система уравнений называется неопределенной, если она имеет более одного решения.

Две системы уравнений являются равносильными или эквивалентными, если они имеют одно и то же множество решений. Система, равносильная данной может быть получена с помощью элементарных преобразований данной системы. Систему можно также записать в виде матричного уравнения: А?Х = В,

где А — матрица коэффициентов при переменных, или матрица системы:

 

X — матрица-столбец (вектор) неизвестных:

В — матрица-столбец (вектор) свободных членов:

В развернутом виде систему можно представить следующим образом:

Существует ряд методов решения системы, ориентированных на вычисления вручную: методы Крамера, Гаусса и т. д. Предполагая использование компьютера для проведения вычислений, наиболее целесообразно рассмотреть решение системы в общем виде (метод обратной матрицы). Будем считать, что квадратная матрица системы А_nn является невырожденной, то есть ее определитель |А| 0. В этом случае существует обратная матрица A^-1.

Решением системы методом обратной матрицы будет матрица-столбец.

Пример 8

Пусть необходимо решить систему: 3x+2y=7

4x-5y=40

Решение

Введите матрицу (в данном случае размера 2 х 2) в диапазон А1:В2

Вектор В = (7 40) введите в диапазон С1:С2. Блок ячеек под обратную матрицу АЗ:В4

Найдите обратную матрицу А^-1.

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

Умножением обратной матрицы на вектор В найдите вектор X. Выделите блок ячеек под результирующую матрицу (под вектор X). Ее размерность будет т х р, в данном примере 2 x 1. Например, выделите блок ячеек СЗ:С4;

В результате в диапазоне СЗ:С4 появится вектор X. Причем х = 5 будет находиться в ячейке СЗ, а у = -4 в ячейке С4.

Можно осуществить проверку найденного решения. Для этого найденный вектор Х необходимо подставить в исходное матричное уравнение А x Х=В

Выделите блок ячеек под результирующую матрицу (под вектор В). Ее размерность будет т x р, в данном примере 2 x 1. Например, выделите блок ячеек D1:D2

В результате в диапазоне D1:D2 появится вектор В, и, если система решена правильно, появившийся вектор будет равен исходному В = (7 40).

Пример 9

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

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

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

Решаем систему аналогично решению примера 8.

Ответ.

x_l = 200, х₂ = 300, х₃ = 200

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

Упражнения

Решите самостоятельно следующие примеры.