 |
§8. Формулы массива. Умножение матриц. Упражнение. А В С D E Модель Цена 2019 Цена 2020 Отношение % Ноутбук HP 255 G7
|
|
|
|
§8. Формулы массива
Формулы массива используются, когда необходимо произвести над данными какую-либо операцию, результатом которой является массив ячеек.
| А | В | С | D | E |
| Модель | Цена 2019 | Цена 2020 | Отношение | % |
| Ноутбук HP 255 G7, 7DF18EA 30590 | 530 | =(В2: B5-C2: C5)/ В2: B5 | =D2 | |
| Ноутбук Xiaomi Mi Notebook Air 12. 5" 2019 (Core m3 8100Y 1100 | =(В2: B5-C2: C5)/ В2: B5 | =D3 | ||
| Ноутбук Xiaomi RedmiBook 14" (Intel Core i7 8565U 1800 72990 | =(В2: B5-C2: C5)/ В2: B5 | =D4 | ||
| Ноутбук Lenovo IdeaPad S145-15API (81UT00BERU) | =(В2: B5-C2: C5)/ В2: B5 | =D5 |
Рис. 3. 23 Динамика цен на персональные компьютеры
В таблице на рис. 3. 23 проиллюстрирована динамика цен на ПК различных моделей. Во 2–м и 3–м столбцах приведены цены 2019 и 2020 годов, а в последних двух столбцах — изменение цен в абсолютных величинах и в процентах. Во всех ячейках столбца D производятся одинаковые арифметические операции. При этом можно не вписывать формулу в каждую ячейку, а выделить D2: D5 и записать формулу один раз в активную ячейку. Такая формула называется формулой массива. После набора формулы массива надо нажать одновременно 3 клавиши « Ctrl»–«Shift»–
«Enter». Тогда будут выполнены преобразования данных всех ячеек массива. То же в столбце Е.
В информационной строке формулы массива автоматически заключаются в фигурные скобки. Изменение части результирующего массива недопустимо. При возникновении ошибки нужно нажать Esc.
В простейшем случае, проиллюстрированном выше, формула массива решает практически ту же задачу, что и автозаполнение ячеек (копирование). Однако, например, в матричных операциях формулы массива незаменимы.
Умножение матриц
Для выполнения этой операции необходимо
|
|
|
1. Ввести в соответствующие поля массивы ячеек, содержащих матрицы–сомножители.
2. Выделить односвязную область, в которой должен располагаться результат.
3. Вызвать (или вписать в информационную строку) функцию МУМНОЖ с нужными аргументами.
4. Одновременно нажать клавиши « Ctrl»–«Shift»–«Enter».
(Если просто нажать кнопку Готово в окне диалога вызова функции, то на экран будет выведен только первый элемент матрицы–произведения).
Аналогичные действия следует выполнить для определения элементов матрицы, обратной заданной, с использованием функции МОБР.
Упражнение
В качестве примера рассмотрим решение системы линейных уравнений методом обратной матрицы:
Ах = в
х = А-1в.
Пусть элементы матрицы А (коэффициенты системы уравнений) записаны в диапазоне А3: E7, а элементы вектора B (свободные члены уравнений) — в диапазоне G3: G7. Для получения значений неизвестных х1, …, х5 выделим диапазон. I3: I7. Наберем в активной ячейке I3 формулу
= МУМНОЖ (МОБР(A3: E7); G3: G7) и введем ее одновременным нажатием «Ctrl»–«Shift»–«Enter».
В ячейках I3: I7 получим величины х1, …, х5 (рис. 3. 24а, 3. 24b).
Рис. 3. 24а Решение системы линейных уравнений
Упражнение
Выполним решение той же системы линейных уравнений по формулам Крамера:
j
х j ,
где - определитель системы уравнений, составленный из коэффициентов при неизвестных, а j - j-й дополнительный минор, т. е. определитель, составленный из коэффициентов при неизвестных, где j-й столбец заменен столбцом свободных членов.
Рис. 3. 24b Решение системы линейных уравнений (формулы).
Порядок решения:
• Ввести матрицу коэффициентов при неизвестных (А3: E7) и свободные члены (G3: G7) системы уравнений.
• Вычислить определитель матрицы в D8 по формуле =МОПРЕД (А3: E7). (Если определитель равен нулю, то система неразрешима).
|
|
|
• Скопировать матрицу коэффициентов в (А12: E16). Заменить первый столбец (А12: А16) свободными членами системы (G3: G7) и вычислить
1,
х1
в ячейке I10.
• Скопировать ячейку I10 и вставить (значение) в ячейку I12.
• Восстановить первый столбец матрицы коэффициентов (А12: А16) по исходным значениям (А3: А7).
• Заменить следующий столбец коэффициентов (В12: В16) свободными членами системы (G3: G7). При этом в I10 появится значение, соответствующее х2.
• Скопировать х2 и вставить его (значение) в I13.
• Аналогично получить в I10 и вставить в I14-I16 остальные неизвестные.
Покажем, как, используя формулы массива и логические функции, можно улучшить технологию решения системы линейных уравнений методом Крамера.
Наша цель автоматизировать процесс построения дополнительных миноров j , не выполняя вручную операции восстановления исходной матрицы и замены в ней очередного столбца коэффициентов столбцом свободных членов. Кроме того, мы хотим устранить копирование и вставку в область ответов очередных значений неизвестных. При этом важно соблюсти тот принцип, что при многократном использовании формулы для работы с разными данными эта формула должна вводиться один раз, а затем копироваться на нужный диапазон, после необходимого закрепления адресов.
Порядок решения:
• Ввести матрицу коэффициентов (А3: E7) при неизвестных xj и свободные члены (G3: G7) системы уравнений (рис. 3. 24a).
• Ниже, например, в А10: E10, ввести номера столбцов матрицы, а в G10 (столбец свободных членов) ввести номер j определяемого неизвестного xj (на первом шаге 1).
• Для построения дополнительных миноров выделить ячейки А12: А16. Ввести в активную ячейку А12 формулу
=Если($I10=A10; $G3: $G7; A3: A7) и нажать одновременно Ctrl+Shift+Enter. Полученная формула массива сформирует ячейки A12: A16 (рис. 3. 24b).
• Скопировать A12: A16 по столбцам в В12: E16 (рис. 3. 24b).
• Ввести в I10 формулу вычисления неизвестного Xj:
Xj = мопред(A12: E16)/ мопред(A3: E7).
• Так как в G10 стоит номер определяемого неизвестного 1, то первый столбец дополнительного минора (A12: A16 ) автоматически заменится столбцом свободных членов, а в ячейке I10 получим величину X1=0, 06993 (рис. 3. 24а).
• В ячейках G12: G16 перенумеровать искомые неизвестные 1-
|
|
|
5.
• Ввести в I12 формулу =Если (G$10 = G12; I$10; I12) и скопировать ее до I16 (рис. 3. 24b).
• Теперь, последовательно набирая и вводя в G10 номера искомых переменных, получим значения этих переменных в
I12: I16 (рис. 3. 24a).
• Отметим, что значения переменных, полученные методом обратной матрицы (рис. 3. 24a, диапазон I3: I7), полностью совпадают со значениями переменных, полученными методом Крамера (рис. 3. 24a, диапазон I12: I16),
|
|
|
