Студентам ИТ / 3 ЛП_ИТ / ИТ_обраб_мног_данных / ИТ_обраб_мног_данных_Excel

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

МОСКОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ТЕХНОЛОГИЙ И УПРАВЛЕНИЯ им. К.Г.РАЗУМОВСКОГО

(Первый казачий университет)

__________________________________________________________________

Кафедра Информационных технологий

Краснов А.Е., Николаева С.В., Феоктистова Н.А.

Информационные технологии обработки многомерных данных в EXCEL

Лабораторный практикум

Москва – 2014

2

УДК 62-50

ББК 65.26с.я73

Краснов А.Е., Николаева С.В., Феоктистова Н.А.

Информационные технологии обработки многомерных данных в Excel:

лабораторный практикум. - М.: МГУТУ, 2014. - 22 с.

Лабораторный практикум предназначен для подготовки бакалавров и магистров всех направлений.

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

Пособие предназначено также и для аспирантов / соискателей всех специальностей.

Лабораторный практикум рекомендован Ученым советом института Системной автоматизации и инноватики для электронного издания.

Рецензенты:	Жуковский В.И.,	д.ф.-м.н., профессор МГУ;
	Вольфенгаген В.Э.	д.т.н., профессор МИФИ.
Редактор:	Феоктистова Н.А.,	к.т.н., доцент МГУТУ.

Краснов А.Е., Николаева С.В., Феоктистова Н.А.

МГУТУ им. К.Г. Разумовского, 2014. 109004, Москва, Земляной вал, 73.

3

СОДЕРЖАНИЕ

		Стр.
1.	Выполнение операций векторной алгебры ………………………………....	4
1.1.	Скалярное произведение векторов ……………………………………………...	4
1.2.	Транспонирование векторов …………………………………………………….	5
1.3.	Циклическая свертка последовательностей ……………………………………	5
1.4.	Образование матриц с помощью внешнего произведения векторов …………	6
1.5.	Матричные функции ……………………………………………………………..	6
1.6.	Решение систем линейных уравнений путем обращения матриц …………….	7
2.	Моделирование и визуализация зависимостей ……………………………..	8
2.1.	Моделирование и графическое отображение аналитических зависимостей: .	8
	- моделирование линейной зависимости ……………………………………….	8
	- моделирование квадратичной зависимости …………………………………..	11
	- моделирование двумерных зависимостей …………………………………….	12
3.	Решение уравнений …………………………………………………………….	16
3.1.	Решение уравнений с помощью встроенных инструментов Exсel
	(«Подбор параметра» и «Поиск решения»): ……………………………………	16
	- решение нелинейных уравнений ………………………………………………	16
	- решение систем линейных уравнений ………………………………………...	18
	- решение систем нелинейных уравнений ……………………………………...	19
4.	Собственные векторы и собственные значения матрицы ………………..	21
	Литература ………………………………………………………………………	22

4

1.Выполнение операций векторной алгебры

1.1.Скалярное произведение векторов.

Вычислите скалярное произведение (S+1 S2) произвольных пятимерных векторов S1

иS2. Для этого выполните последовательно следующие действия.

1.Откройте Лист1 ЭТ Excel и введите, например, в ячейки B5:B9 и ячейки E5:E9 компоненты векторов-столбцов S1 и S2, как показано в таблице 1.1.

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

Таблица 1.1

H5		=	=СУММПРОИЗВ(B5:B9;E5:E9)
		A		B		C			D			E			F		G	H
	.		.		.			.			.			.			.	.
.
																	(S+1 S2) =
5		S1 =		1					S2 =		6							130
6				2							7
7				3							8
8				4							9
9				5							10
.	.		.		.			.			.			.			.	.
				Лист1		Лист2			Лист3			Лист4			Лист5

Активизируйте ячейку H5 щелчком левой кнопки мышки (щ.л.к.м.), наведите курсор (белый крест) на пиктограмму fx (вставка функций) панели инструментов и щ.л.к.м. откройте «висящее» меню Мастер функций — шаг 1 из 2.

3.В разделе Категория: (левая панель меню) активизируйте (наведение курсором и щ.л.к.м.) класс функций Математические, а в разделе Функция функцию СУММПРОИЗВ. В нижней части «висящего» меню отображается формат функции СУММПРОИЗВ(массив1; массив2; массив3; …) и описано действие заказанной функции

Возвращает сумму произведений соответствующих элементов массивов. Нажмите кнопку ОК (правый нижний угол «висящего» меню). Появится «висящий» шаблон функции СУММПРОИЗВ.

4.В «окно» Массив1 шаблон функции СУММПРОИЗВ введите компоненты

транспонируемого вектора S1. Для этого достаточно «схлопнуть» на время шаблон, нажав на пиктограмму с красной стрелкой (в правой части окна Массив1), навести курсор на ячейку B5, нажать л.к.м. и, не отпуская ее, провести курсор включительно до ячейки B9. «Расхлопнув» шаблон с помощью пиктограммы с красной стрелкой, Вы увидите, что в окне Массив1 появились адреса B5:B9 компонентов вектора S1. Переместите курсор

редактора (мигающая вертикальная черта « ») в окно Массив2 и описанным выше приемом введите компоненты вектора-столбца S2. Нажав кнопку ОК, Вы получите в ячейке H5 результат 130. При этом, в строке формул (над буквенными адресами ячеек ЭТ, как показано в таблице 1.2) появится надпись СУММПРОИЗВ(B5:B9;E5:E9), соответствующая формату заказанной формулы.

Для удобства последующего понимания записей в ЭТ введите буквенные обозначения векторов и результатов действий над ними (см. таблицу 1.1).

5

1.2. Транспонирование векторов.

1.Откройте Лист2 ЭТ и снова введите компоненты вектора S1.

2.Закрасьте, н.л.к.м., ячейки D5:H5 и введите с помощью пиктограммы fx (вставка

функций) функцию транспонирования ТРАПСП(B5:B9), используя приемы предыдущего пункта.

Однако для завершения ввода не торопитесь нажимать кнопку ОК!

В данном случае нам необходимо воспользоваться встроенным макросом «Формулы массива» для того, чтобы последовательно (5 раз) выполнить одну и ту же операцию перенос компонентов B5:B9 вектора S1 из столбца в соответствующие ячейки D5:H5 строки.

3. Для активизации формулы массивов применительно к функции ТРАНСП нажмите комбинацию клавиш «Shift + Ctrl + Enter». В результате в ячейках D5:H5 появятся

компоненты транспонированного вектора S+1,							а в	строке	состояний нотация
({=ТРАНСП(B5:B9)} заказанной формулы массивов (см. таблицу 1.2).
								Таблица 1.2
	D5	=		{=ТРАНСП(B5:B9)}
		A		B	C	D	E	F	G	H
	5	S1 =		1	S+1 =	1	2	3	4	5
	6			2
	7			3
	8			4
	9			5
	.	.		.	.	.	.	.	.	.
				Лист1	Лист2	Лист3	Лист4	Лист5

Аналогичные действия возможно произвести, транспонируя вектор-строку в векторстолбец.

1.3. Циклическая свертка последовательностей.

1.Откройте Лист3 ЭТ и введите в ячейки B5:D5 и B7:D7 две последовательности векторы-строки S+1 и S+2, как показано в таблице 1.3.

2.Выделите ячейки B5:D5 с элементами первой последовательности и с помощью

механизма копирования («наведение курсора на границу выделенного блока ячеек + Ctrl + н.л.к.м.») повторите последовательность S+1, как показано в таблице 1.3.

3.Введите в ячейку B9 формулу СУММПРОИЗВ(B5:D5;$B$7:$D$7).

Внимание! На ячейки B7:D7 производится ссылка при их абсолютной адресации, что обозначается знаком «$».

4. Скопируйте результат из ячейки B9 в ячейки С9 и D9, с помощью механизма копирования формул («наведение курсора на правый нижний угол ячейки B9 + н.л.к.м.»).

В результате в ячейках B9, С9, D9 будут отображены компоненты циклической свертки S+1 S+2 исходных последовательностей.

6

Таблица 1.3

			=
	A				B		C	D	E	F	G		H
5	S+1 =			1			2	3	1	2		3
6
7	S+2 =			4			5	6
8
9	S+1 S+2=			32			29	29
					Лист1		Лист2	Лист3	Лист4	Лист5

Проверьте, что результат не зависит от того, какая из последовательностей циклически размножается перед вычислением функции СУММПРОИЗВ.

1.4. Образование матриц с помощью внешнего произведения векторов.

1. Откройте Лист4 ЭТ и запишите элементы вектора S1 и транспонированного вектора S+2 в ячейки B6:B9 и C5:F5 соответственно, как показано в таблице 1.4.

Таблица 1.4

		=	{=B6:B9*C5:F5})
		A	B		C			D		E	F		G	H
5			S+2 =	5			6			7	8
6		S1 =	1	5			6			7	8
7			2	10			12			14	16
8			3	15			18			21	24
9			4	20			24			28	32
			Лист1		Лист2			Лист3		Лист4	Лист5

2. Выделите блок ячеек С6:F9 и в первую ячейку С6 блока введите формулу массива

({=B6:B9*C5:F5}). Завершите вычисления, нажав комбинацию «Shift + Ctrl + Enter».

В результате в блоке ячеек С6:F9 будут размещены элементы матрицы S1 S+2.

При S2 = S1 с помощью рассмотренных операций возможно формировать автоковариационные функции S1 S+1.

1.5. Матричные функции.

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

МУМНОЖ(<матрица1>;<матрица2>) возвращает произведение матриц. Число столбцов матрицы <матрицы1> должно совпадать с числом строк <матрицы2>. Результирующая матрица будет иметь столько же строк, сколько <матрица1>, и столько же столбцов, сколько <матрица2>.

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

7

ошибке #ЧИСЛО!). Перемножение обратной и прямой матриц даст единичную матрицу (матрицу, у которой на главной диагонали находятся единицы, а остальные элементы нули).

ТРАНСП(<матрица>) транспонирует исходную прямоугольную <матрицу>, поворачивая ее относительно главной диагонали.

МОПРЕД(<матрица>) вычисляет определитель исходной прямоугольной матрицы.

Все функции, результатами которых являются матрицы (МУМНОЖ, МОБР, ТРАНСП), должны быть введены как формулы массива в следующей последовательности:

–выделяется блок ячеек, где будет размещен результат;

–в текущую (первую) ячейку блока вводится функция;

–нажимается комбинация клавиш «Shift + Ctrl + Enter» (при этом введенная формула автоматически обрамляется фигурными скобками).

В таблице 1.5 даны примеры использования перечисленных функций на Лист5 ЭТ.

Таблица 1.5

		=
	A			B			C			D			E		F	G	H
5	Матрица								Обращение							Умножение
6		4	3						1				–2			1	0
7		2	2							–1			2			0	1
8									Транспонирование							Определитель
9									4				2			2
10									3				2
				Лист1			Лист2			Лист3			Лист4		Лист5

С помощью рассмотренных операций над векторами и матрицами возможно решать ряд прикладных задач по анализу векторов состояний ТЕХ.

1.6. Решение систем линейных уравнений путем обращения матриц.

В качестве примера рассмотрим следующую систему линейных уравнений

–8 x1 + 1x2 + 2 x3 = 0, 5 x1 + 7x2 – 3 x3 = 10, 2 x1 + 1x2 – 2 x3 = –2.

Она имеет решение x1 = 1, x2 = 2, x3 = 3.

Такую систему можно решить на листе электронной таблицы. Для этого необходимо проделать следующее.

1.Создайте новый Лист6.

2.В ячейку A1 введите Решение систем уравнений обращением матриц.

3.В ячейку A3 введите Ax = b.

4.Введите матрицу коэффициентов A и вектор правой части b:

–в ячейку A4 введите Исходная матрица (A);

–в ячейки B4:D6 введите элементы матрицы A, как показано в таблице 1.6.

8

Таблица 1.6

	=
	A		B		C	D		E		F	G	H
1	Решение систем уравнений обращением матриц
2
3	Ax = b
4	A =		–8	1		2				b =	0
5			5	7		–3					10
6			2	1		–2					–2
7
8	1/A =		–0,15	0,05		–0,23				x = (1/A)b =	1
9			0,05	0,16		–0,19					2
10			–0,12	0,14		–0,82					3
			Лист1		Лист2	Лист3		…		Лист6

5.В ячейку F4 введите (вектор правой части уравнения) «b =».

6.В ячейки G4:G6 введите компоненты вектора правой части, как показано в таблице 1.6.

7.Далее для решения уравнений необходимо обратить матрицу A и умножить вектор b на матрицу, обратную к A. Применяемая для этого функция МОБР возвращает массив значений, который вставляется сразу в столбцы ячеек. Введите в ячейку A8 Обратная матрица (1/A).

8.Выделите ячейки В8:D10 и с помощью пиктограммы fx (Вставка функций) введите =МОБР(B4:D6) и нажмите клавиши «Shift + Ctrl + Enter» для вставки этой формулы во все выбранные ячейки.

В выделенных ячейках появятся элементы обращенной матрицы 1/A.

9.В ячейку F8 введите Вектор решения x = (1/A)b.

10.Выделите ячейки G8:G10 и введите =МУМНОЖ(B8:D10; G4:G6), а затем нажмите клавиши «Shift + Ctrl + Enter» для вставки формулы во все выбранные ячейки.

В выделенных ячейках появятся элементы искомого вектора x.

2.Моделирование и визуализация зависимостей

2.1. Моделирование и графическое отображение аналитических зависимостей.

Моделирование линейной зависимости

1.В ячейку А1 ЭТ Excel, используя Лист1, шрифтом «Arial Cyr» № 10 (обычно устанавливается по умолчанию) впишите заглавие «Моделирование и графическое отображение аналитических зависимостей».

2.С помощью меню Файл – Сохранить как сохраните информацию по адресу: «Мои документы / Моделирование / Моделирование.xls».

После Сохранить как появляется таблица Сохранение документа. Необходимо в

строке Папка: с помощью кнопки « » установить Мои документы, с помощью третьей кнопки «Создать папку» (желтый портфель со звездочкой в правом верхнем углу) создать новую папку и присвоить ей имя «Моделирование», войти в эту папку двойным щелчком левой кнопки мыши (имя папки появляется в строке Папка:) и в строке таблицы Имя файла: набрать «Моделирование.xls». При необходимости исправить имя папки — щелкнуть по нему правой кнопкой мыши и в появившемся «висящем» меню выбрать опцию Переименовать. При исправлении имени файла — повторить те же самые операции

9

(устанавливается курсор редактирования текста). Для завершения сохранения информации — щелкнуть левой кнопкой мыши по кнопке Сохранить в правом углу таблицы Сохранение документа. После этого в верхней строке Excel появится имя файла.

Внимание! В ходе дальнейшего создания документа во избежание потери введенных данных не забывайте время от времени сохранять их (по заданному адресу), щелкая левой кнопкой мыши по пиктограмме Сохранить (рисунок дискетки).

3.Введите в ячейку A3 название «Моделирование зависимостей».

4.Введите в ячейку A5 название линейной зависимости «Линейная:», а в ячейку A6

вид соответствующего уравнения «y = a*x + b». В ячейки A7 A10 введите обозначения для всех констант и переменных, т.е. «a = », «b = », «x = », «y = ».

5.В ячейки B7 и B8 введите численные значения 2 и 1 для констант a и b соответственно. Задайте числовой формат для этих ячеек с точностью до двух десятичных знаков (закрашивание ячеек с помощью левой кнопки мышки, щелчок правой кнопкой мышки, раздел Формат ячеек всплывшего меню, выбор закладки Числовой).

6.В ячейку С9 введите начальное значение «–10» независимой переменной «x» и задайте ей числовой формат с точностью до двух десятичных знаков.

7.Активизируйте ячейку С9 (щелчок левой кнопкой мышки по ячейке) и войдите в режим Правка главного меню команд. Выберите раздел Заполнить, пункт Прогрессия. Во всплывшем меню Прогрессия задайте арифметическую прогрессию (задается по умолчанию) с шагом 1 и предельным значением 10. Нажмите кнопку ОК. В результате все

ячейки D9 W9 будут заполнены нарастающими значениями независимой переменной.

8. Введите в ячейку С10 программу, соответствующую формуле «y = a*x + b», ссылаясь на конкретные числовые значения 2, 1, –10 констант и переменной. Для этого активизируйте ячейку С10, наведите курсор на знак «=» панели отображений формул и щелкните левой кнопкой мышки. После этого введите формулу, указывая на соответствующие ячейки курсором и производя щелчок левой кнопкой мышки. Операторы умножения « » и сложения «+» вводите с помощью цифровой части клавиатуры или алфавитно-цифровой части, используя верхний регистр (над цифрой 8 и знаком = соответственно). После набора формулы нажмите кнопку с зеленой пиктограммой « » слева от кнопки «=» панели отображения формул. В результате в ячейке С10 должно появиться число –19, а в панели отображения формул выражение

«=B7*C9 + B8».

9.Скорректируйте формулу в ячейке С10, задав абсолютную адресацию ячеек B7 и B8. Для этого вставьте знак $ в формулу «=$B$7*C9 + $B$8». Задайте числовой формат ячейке С10 с двумя десятичными знаками.

10.Произведите копирование формулы во все ячейки D10 W10. Для этого наведите курсор на правый нижний угол ячейки С10 (курсор приобретает вид +) и, держа нажатой левую кнопку мышки, протащите курсор до ячейки W10. В результате в ячейках, соответствующих зависимой переменной «y», появятся числовые значения (последнее значение 21 в ячейке W10).

11.Подготовьте данные для графического отображения построенной зависимости.

Для этого активизируйте блок ячеек В9 W10, проведя по ним курсором при нажатой левой кнопке мышки. Блок ячеек выделится темным цветом, кроме первой ячейки B9.

12. После выделения блока ячеек щелкните левой кнопкой мышки по пиктограмме Мастер диаграмм («изображение цветной гистограммы») на панели инструментов. В открывшемся меню Мастер диаграмм (шаг 1 из 4:) выберите режим Стандартные (автоматически устанавливается по умолчанию) и щелкните левой кнопкой мышки по диаграмме График опции Тип:. Справа в опции Вид: темной заливкой будет отмечен вид графика, а внизу будет дано его краткое описание. После этого щелкните левой кнопкой мышки по кнопке Готово (нижний правый угол меню Мастер диаграмм). Сразу же появляется уменьшенное окно с отображаемой диаграммой. Наличие черных квадратиков

10

по сторонам и углам рамки окна (границ диаграммы) говорит о том, что диаграмма активизирована. Поместите курсор в область диаграммы на чистое поле, и он примет вид стрелки. При этом под стрелкой появится подпись Область диаграммы. Последовательно наведите стрелку на различные части области диаграммы и ознакомьтесь с появляющимися подписями: темное поле диаграммы (Область построения диаграммы, цифры у оси ординат (Ось значений), подписи под осью «абсцисс» (Ось категорий), прямоугольник справа от области построения диаграммы (Легенда), подпись над областью построения диаграммы (Заголовок диаграммы).

Для активизации основного документа электронной таблицы установите курсор вне области диаграммы и щелкните левой кнопкой мышки.

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

14.Создайте надписи на осях диаграммы. Для этого щелкните правой кнопкой мышки по свободному полю области диаграммы. В появившемся «висящем» меню выберите опцию Параметры диаграммы, щелкнув по ней левой кнопкой мышки. В открывшейся таблице Параметры диаграммы в режиме Заголовки (устанавливается по умолчанию) поместите курсор (щелчок левой кнопкой мышки) в окно под подписью Ось X (категорий): и напишите «X». Аналогично под подписью Ось Y (ось значений): напишите «Y», а под подписью Название диаграммы напишите «Линейная». Щелкните левой кнопкой мышки по кнопке ОК (правый нижний угол) таблицы Параметры диаграммы.

15.Отредактируйте надписи диаграммы. Для этого активизируйте надпись «Линейная» диаграммы, наведя на нее курсор и щелкнув левой кнопкой мышки (появляется серая рамка). Щелкнув один раз левой кнопкой мышки по надписи,

установите курсор (вертикальная мигающая черточка « ») в ее конец и добавьте слово «зависимость» в заглавие диаграммы. Для перемещения курсора в процессе редактирования заглавия возможно также использовать стрелки ( влево, вправо) консоли.

Активизируйте надпись «X», наведите курсор на границу серой рамки и, нажав левую кнопку мышки, «отбуксируйте» надпись к правому концу оси абсцисс.

Наведите курсор на надпись «Y» и щелкните правой кнопкой мышки. В появившемся «висящем» меню щелкните левой кнопкой мышки по опции Формат оси …. В открывшейся таблице Формат оси активизируйте режим Выравнивание и в его опции Ориентация (рисунок полуокружности с угловыми делениями) наведите курсор на отметку, соответствующую «0 градусов», и щелкните левой кнопкой мышки. После этого щелкните левой кнопкой мышки по кнопке ОК таблицы Формат оси. Отбуксируйте название «Y» к верхнему концу оси ординат, активизируя надпись, наведя курсор на границу ее рамки и нажав левую кнопку мышки.

Активизируйте надпись, соответствующую легенде диаграммы (справа от области ее построения). С помощью курсора и мыши уменьшите область Легенда и отбуксируйте ее в «приемлемую позицию».

Активизируйте область построения диаграммы, наведя курсор на темное поле с графиком и щелкнув левой кнопкой мыши. Установите необходимый размер области