Формулы Excel для обработки массивов данных

Цели работы

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

2. Изучить процедуру применения табличных формул для обработки больших массивов данных в Excel.

Важно ! При обработке матриц необходимо помнить два основных правила:

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

Ввод матричной формулы завершается нажатием комбинации клавиш Ctrl + Shift + Enter, а не просто Enter, как при обычных вычислениях.

Задание 1

Рассчитать требуемые характеристики квадратной матрицы и определить решение системы линейных уравнений.

Порядок выполнения работы

1. Дать рабочему листу название «Матрицы».

2. Задать матрицы Aи B (рис. 17).

3. Вычислить определитель квадратной матрицы (числовая характеристика) с помощью функции МОПРЕД категории Математические(например, =МОПРЕД(B2:D4)).

4. Вычислить обратную матрицу для заданной с помощью функции МОБР категории Математические (заметим, что матричное произведение исходной матрицы и ее обратной матрицы дает единичную матрицу).

5. Транспонировать матрицу (поменять местами строки и столбцы) с помощью функции ТРАНСП категории Ссылки и массивы.(после транспонирования вектор-столбец преобразуется в строку, а вектор-строка – в столбец).

6. Выполнить операции сложения, вычитания, умножения и деления матрицы и числа посредством арифметических операторов: +, ‑, *, / (например, =B2:D4 * 0,5).

7. Операции поэлементного сложения, вычитания, умножения и деления применяют только к матрицам одинаковой размерности и их выполняют посредством арифметических операторов +, – , *, /. (например, =F2:F4+H2:H4).

8. Вычислить матричное произведение двух матриц с помощью функции МУМНОЖ категории Математические(число столбцов первой матрицы равно числу строк второй матрицы). В результате этой операции получается матрица, число строк которой равно числу строк первой умножаемой матрицы, а число столбцов – числу столбцов второй матрицы.

9. Решить систему линейных алгебраических уравнений (СЛАУ).

Рис. 17. Лист «Матрицы»

В алгебраической форме СЛАУ порядка n записывают в виде

.

Или в матричной форме: АХ = В, где А – матрица коэффициентов; В – вектор-столбец свободных членов; Х – вектор-столбец неизвестных:

Решение СЛАУ в матричном виде находят по формуле
Х = А^–1В, где А^–1 – матрица, обратная А.

На рабочем листе Excel записаны матрица коэффициентов А и вектор-столбец свободных членов В. Для получения решения выделить ячейки, соответствующие вектору-столбцу из n элементов для неизвестных и записать матричную формулу решения системы (например: =МУМНОЖ(МОБР(B2:D4);F2:F4))

Задание 2

Создать таблицу расчета заработной платы, используя табличные формулы Excel для расчета процентных отчислений и сумм к выдаче.

Порядок выполнения работы

1. Дать рабочему листу название «Ведомость».

2. Создать таблицу ведомости по зарплате на лист Excel (см. образец), отсортировать по алфавиту.

№ п/п	Фамилия	Размер оклада, руб.	Подоходный налог (12 % от оклада)	Отчисления в пенсионный фонд (1 % от оклада)	Профсоюзный взнос(1 % от оклада)	К выдаче
	Ушков А.С.	9 500,00
	Карпов В.А.	7 000,00
	Вилков И.И.	15 000,00
	Абрамов С.Т.	18 000,00
	Иванова С.И.	13 500,00
	Кукушкина С.А.	7 000,00
	Ларин В.Н.	10 000,00
	Машин С.И.	8 000,00
	Зуев А.И.	15 000,00
	Кошкин А.Н.	9 500,00
	Итого		0,00р.	0,00р.	0,00р.	0,00р.

3. Рассчитать итоговую сумму к выдаче (матричная формула).

4. В дополнительном столбце восстановить первоначальные величины окладов без вычетов налогов и взносов.

5. Удалить первую строку ведомости (сотрудник Абрамов С.Т.).

6. Добавить в таблицу ведомости нового работника – Юшкова А.Ф., размер оклада которого составляет 13570 руб. и рассчитать для него значения по всем столбцам ведомости.

Примечание. Табличные формулы редактируются не как обычные формулы рабочего листа Excel. Ниже приведены алгоритмы редактирования табличных формул.

Редактирование формулы

1. Выделить блок с формулой.

2. Нажать клавишу F2.

3. Внести изменения в формулу.

4. Нажать клавиши Ctrl + Shift + Enter.

Изменение размеров блока (удаление/вставка строк)

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

2. Нажать клавишу F2.

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

4. Вводим текст во все ячейки с помощью клавиш Ctrl + Enter. Табличная формула прекратила существование.

5. Очистить строку таблицы.

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

7. Нажать клавишу F2.

8. Удалить апостроф.

9. Нажать клавиши Ctrl + Shift + Enter.

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