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

Скопируйте блок А1:С5 в А11:С15. Повторим все шаги. Выделим блок D12:D15. В этом блоке активной является D12. Очистим строку формул и наберем знак равенства =. Выделим блок В12:В15, наберем знак минус -, выделим блок С12:С15, нажмем сочетание клавиш [Ctrl][Shift][Enter]. Мы получим две идентичные таблицы.

4. Выделение блока с табличной формулой

Выделите одну из ячеек блока, например D2, и нажмите клавиши [Ctrl][/]. Будет выделен весь блок. Если на основной клавиатуре не получится, нажмите [/] в этом сочетании на дополнительной клавиатуре (серая).

Есть и другой способ выделения. Нажмите клавишу [F5] (эквивалент пункта меню Правка/ Перейти В диалоговом окне Переход щелкните кнопку Выделить..., установите переключатель Текущий массив.

5. Изменение табличной формулы

Попытаемся очистить одну из ячеек, занятую табличной формулой. Например, выделим ячейку D2 и нажмем клавишу [Del]. Появится сообщение Нельзя изменять часть массива. Удалить блок можно только целиком.

Отредактировать формулу можно так: выделить блок с формулой (клавиши [Ctrl][/]., нажать функциональную клавишу [F2], внести изменения в формулу, нажать сочетание клавиш [С1г1][Shift][Enter]. В качестве упражнения измените формулу {=Приход-Расход} на {=Приход-Расход-1}, а потом восстановите ее). Если при выделенной ячейке из блока формул очистить строку формул и нажать сочетание клавиш [С1г1][Shift][Enter], то блок с формулой будет очищен. Этот прием действует в такой аварийной ситуации, когда появляется неустранимое сообщение Нельзя изменять часть массива Сохраните и закройте эту рабочую книгу.

Задание. В диапазоне А1:А4 дан вектор-столбец V с компонентами 3, -2, 4, 7. С помощью табличной формулы вычислите вектор W=2V, расположив его в соседнем столбце. Отработайте два способа решения этой задачи: не используя имен, и дав диапазону А1:А4 имя V. Еще раз решите эту задачу, но при условии, что V - вектор-строка (например, в D1:G1; результат -соответственно в D2:G2. Сохраните решение этой задачи на отдельном листе книги Массивы

Ниже рассматриваются примеры применения формул массивов (табличных формул). Сохраняйте их также на отдельных листах книги Массивы.

Упражнение 2. Подсчет распределения по диапазонам значений

Функция ЧАСТОТА(исходный_массив; массив_карманов) в категории Статистические подсчитывает, сколько чисел из исходного_массива попадает в каждый диапазон между значениями, представленными в масси-ве_карманов. Если массив_карманов - это массив чисел {a:b:c}, то числа исходного массива распределятся по промежуткам: (-, a], (a, b],(b, с], (с, ]. Обратите внимание, что промежутки полузамкнутые, т.е., например, 7(7, 8], а 8 (7, 8]. Количество промежутков на единицу больше количества элементов в массиве_карманов

Задание. Введите в блок А1:А10 числа 1, 2, 2, 3, 4, 6, 5, 4, 4, 3. Это исходный_массив. В диапазон С1:С4 введите числа 2, 3, 4, 5. Это мас-сив_карманов. В Е1:Е5 (количество ячеек на 1 больше, чем чисел в масси-ве_карманов) введите табличную формулу {=ЧАСТОТА(А1:А10;С1:С4)}. Она вернет в диапазон Е1:Е5 значения 3, 2, 3, 1, 1 .

Упражнение 3. Получение части блока

Можно получить часть блока, если воспользоваться функцией: СМЕЩ(ссылка; смещение_по_строкам; смещение_по_столбцам; высота; ширина). Эта функция расположена в категории Ссылки и массивы.

10

11

24

5

14

12

П

	6	5	10
	3	14	11
	8	14	24

усть в блоке FЗ:H5 расположена матрица, показанная на этом рисунке. Тогда для получения копии блока G4:H5, которая является частью блока FЗ:H5, нужно выделить диапазон F9:G10 (указать место, где расположится копия G4:Н5) и ввести матричную формулу {=СМЕЩ(FЗ;1;1;2;2)} Расшифруем эту формулу: от ячейки FЗ отступить вправо на бдин столбец и вниз на одну строку, взять блок из двух строк и двух столбцов. Функция СМЕЩ может быть использована внутри другой функции, например, формула =МИН(СМЕЩ(РЗ;1;1;2;2)) вернет в активную ячейку число 11. Эту формулу не нужно вводить как табличную, так как функция МИН возвращает единственное число, а не массив.

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

ИНДЕКС(массив, номер_строки, номер_столбца)

Эта функция расположена в категории Ссылки и массивы. Она выбирает из прямоугольного блока (массива) элемент, задаваемый номерами строки и столбца, причем эти номера отсчитываются от левого верхнего элемента блока. Если в функции ИНДЕКС опустить номер_строки, то функция вернет весь столбец. Если в функции ИНДЕКС опустить номер_столбца, то функция вернет всю строку.

Для нашего примера формула =ИНДЕКС(FЗ:H5;2;3) вернет в предварительно выделенную одиночную ячейку значение 11. Так как ячейка результата одиночная, то функция вводится как обычно.

Для нашего примера табличная формула {=ИНДЕКС(FЗ:Н5;;3)} вернет в предварительно выделенный столбец числа 10, 11, 24, а формула {=ИНДЕКС(FЗ:Н5;2;)} в предварительно выделенную строку числа 3, 14, 11.

Упражнение 4. Матричные операции

Сложим матрицы М и N, где

	2	-3	7
	-1	3	6

	-1	0	4
	2	-3	5

M = и N = .

Решение. Введем матрицы M и N в блоки А1:С2 и Е1:G2. В блок А4:С5 введем табличную формулу {=А1:С2+Е1:С2} Обратите внимание, что обе исходные матрицы имеют одинаковые размеры, а под результат выделен блок, имеющий те же размеры что и обе исходные матрицы.

Что произойдет, если перед вводом формулы выделить блок, больший по размерам, чем размеры исходных матриц, например А4:D6? В "лишних" ячейках появится #НД, т.е. НеДоступно. А если выделить меньшие размеры, например, А4:В5? Будет выведена только часть матрицы, без каких-либо сообщений. Проверьте.

Использование имен делает процедуру ввода табличной формулы намного проще. Дайте диапазонам А1:С2 и Е1:G2 имена М и N. В ячейку Е4:С5 введите формулу {=М+N}. Результат будет тот же.

Теперь вычислим линейную комбинацию матриц 2М - N. В блок А7:С8 введем табличную формулу {=2*М-N}. У Вас должны получиться следующие результаты

	2	-3	7
	-1	3	6

	-1	0	4
	2	-3	5

M + N = 2M - N = .

Для матричных операций в Excel предусмотрены функции, входящие в категорию Математические. Перечислим эти функции:

МОПРЕД - вычисление определителя матрицы;

МОБР - вычисление обратной матрицы;

МУМНОЖ - перемножение матриц:

ТРАНСП - транспонирование.

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

Задание.

Вычислить определитель и обратную матрицу для матрицы

	-73	78	24
	92	66	25
	-80	37	10

A = .

Проверить правильность вычисления обратной матрицы умножением ее на исходную.

Решение. Разместим исходную матрицу в блоке А1:СЗ. В ячейке В5 поместим формулу для вычисления определителя =МОПРЕД(А1:СЗ).

В блок А7:С9 введем формулу для вычисления обратной матрицы. Для этого выделим блок А7:С9 (он имеет три строки и три столбца, как и исходная матрица). Введем формулу {=МОБР(А1:СЗ)}. Даже если Вы используете мастер функций, нужно завершать ввод табличной формулы нажатием комбинации клавиш [С1г1][Shift][Enter] (вместо щелчка по кнопке “ОК”). Если Вы забыли предварительно выделить блок А7:С9, а ввели формулу в ячейку А7 как обычную формулу (закончив ввод нажатием [Enter], то не нужно вводить ее заново: выделите А7:С9, нажмите клавишу F2 (редактирование), но не изменяйте формулу, а просто нажмите [С1г1][Shift][Enter].

Для дальнейших вычислений присвоим матрицам имена: А1:СЗ - А, А7:С9 - Аinv. Чтобы в уже введенных формулах появились эти имена, выберите в меню пункт Вставка/Имя/Применить Выделите в диалоговом окне нужные имена и щелкните ОК.

Теперь проверим правильность вычисления обратной матрицы. В блок Е1:СЗ введем формулу {=МУМНОЖ(А;Аinv)}. У Вас должна получиться матрица, близкая к единичной. Результаты описанных выше матричных операций приведены ниже:

	A	B	C	D	E	F	G
1	-73	78	24		1	-2,91038E-11	-5,82077E-11
2	92	66	25		0	1	0
3	-80	37	10		0	0	1
4
5		1
6
7	-265	108	366
8	-2920	1190	4033
9	8684	-3539	-11994

Рис. 2

В матричных операциях можно использовать массивы констант. Массив констант - это заключенная в фигурные скобки запись числовых значений элементов массива, в которой элементы строк разделены точкой с запятой, а строки разделяются двоеточием. Например, определитель матрицы А можно вычислить с помощью следующей формулы: =МОПРЕД({-73;78;24:92;66;25:-80;37;10}). Числа в этой формуле - это массив констант. Формула набирается с клавиатуры в строке формул и нажимается клавиша [Enter]. После чего в активной ячейке появляется единица - значение определителя.

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

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

Вернитесь к пункту 2. Введите формулу с использованием табличных констант в следующем виде {ЧАСТОТА(А1:А10;{2:3:4:5})} При этом блок С1:С4 для массива карманов не нужен. Массив_карманов введен как табличная константа вектор-столбец потому, что исходный массив вектор-столбец.

З

	1	2	1
	2	1	2
	1	2	3

	4	1	1
	-4	2	0
	1	2	1

адача 1. Даны матрицы

P = и Q = .

Вычислить определитель коммутатора этих матриц det(РQ - QР). Все вычисления должны быть сосредоточены в одной ячейке.

Задача 2. Дана матрица

	3	2	1	2
	4	1	1	3

S =

I

Вычислить матрицу 2SS^T - Е, где Т - операция транспонирования, Е-единичная матрица.

Задача 3. Решить систему уравнений Ах = В по формуле х = А^-1 В

2 x-y+5z=14

x-3y+4z=9

3x+y-7z=-20

Задача 4. Доказать, что матрица

	2	6	5	10	10
	6	20	14	11	11
	5	14	52	12	12
	10	11	12	245	245

является положительно определённой.