Макросы

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

Макросы всегда выполняются в активном документе.

Макрос можно создать путем протоколирования действий пользователя.

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

Для записи макросов выполнить следующее:

1) щелкнуть по стрелке под кнопкой Макросы в одноименной группе на вкладке Вид на ленте. Выбрать команду Запись макроса в меню;

2) в диалоговом окне Запись макроса дать макросу имя, задать клавишу быстрого вызова и указать место хранения текста макроса (в личной книге макросов, в текущей или новой рабочей книги), а также ввести его описание, затем выполнить те команды, которые будут записываться в макрос;

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

С дополнительным материалом по теме можно ознакомиться в литературе [1, 3, 4, 7].

 

Лабораторная работа включает несколько заданий. Каждое задание должно быть представлено на отдельном листе рабочей книги MS Excel.

Результаты сохранить в книге с названием Лаб.работа2_№ варианта.

 

Задание № 1

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

По результатам построить гистограмму и записать макрос. Листу присвоить имя Гистограмма.

 

Задание № 2

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

Построить по данным таблицы график зависимости у от х и записать макрос. Листу присвоить имя График.

 

Варианты заданий:

№	Функция	Отрезок	Шаг
	Y=2sin(x)cos(x)	[00; 3600]	450
	Y=tg(x)	[00; 1800]	300
	Y=sin(x)+cos(x)	[-1800; 1800]	300
	Y=xsin(x)	[00; 3600]	300
	Y=xcos(x)	[-1800; 3600]	300
	Y=sin2(x)	[300; 1800]	100
	Y=cos2(x)+x	[200; 900]	50
	Y=ctg(x)	[-1800; 1800]	600
	Y=tg(x)+ctg(x)	[00; 3600]	450
	Y=x2sin(x)	[200; 900]	50
	Y=ex	[1; 5]
	Y=ln(x)	[1; 10]
	Y=xln(x)	[1; 6]
	Y= exln(x)	[2; 20]
	Y=x2ln(x)	[2; 20]

 

Задание № 3

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

Построить график зависимости результирующей функции от исходных данных.

Лист назвать Условие.

 

Варианты заданий

1. Если х> 5 или х≤ -20, вычислить у=1+tg(x), иначе вычислить у=х².

2. Задать изменение х от -100 до 100 с шагом 10.

3. Если х принадлежит интервалу (4; 20), вычислить значение функции по формуле y=х²+1, иначе вычислить значение функции по формуле y=sin(х), задать изменение х от -60 до 60 с шагом 4.

4. Если х< -15 или х> 10, вычислить у=x+5, иначе вычислить у=ln(x).

5. Задать изменение х от -40 до 40 с шагом 5.

6. Если х принадлежит отрезку [5; 8], вычислить значение функции по формуле y=cos(x), иначе вычислить значение функции по формуле y=x²+x, задать изменение х от -5 до 10 с шагом 1.

7. Если х< -1 или х< 20, вычислить у=e^x, иначе вычислить у=sin(x+4)

8. Задать изменение х от -50 до 50 с шагом 5.

9. Если х> -10 и х< 40, вычислить у=x+2, иначе вычислить у=cos(x) + x. Задать изменение х от -50 до 50 с шагом 5.

10. Если x> 0 и y< -4 вычислить произведение чисел, иначе сумму. Задать изменение х от -20 до 20 с шагом 5, у вычислить по формуле y=х-2.

11. Если х< -5 или х> 10, вычислить у=x³+5, иначе вычислить у=ln(x+2). Задать изменение х от -40 до 40 с шагом 5.

12. Если х принадлежит отрезку [3; 10], вычислить значение функции по формуле y=xcosx, иначе вычислить значение функции по формуле y=x+4, задать изменение х от -4 до 20 с шагом 2.

13. Если х> 10 или х≤ -20, вычислить у=sin(x)+cos(x), иначе вычислить у=х². Задать изменение х от -50 до 50 с шагом 10.

14. Если х принадлежит интервалу (8; 30), вычислить значение функции по формуле y=х²+1, иначе вычислить значение функции по формуле y=2sin(х+2), задать изменение х от -60 до 60 с шагом 10.

15. Если x> -6 и y< х, вычислить произведение чисел, иначе разность. Задать изменение х от -20 до 20 с шагом 5, у вычислить по формуле x².

16. Если х принадлежит интервалу (-10; 30), вычислить значение функции по формуле y=х²+1, иначе вычислить значение функции по формуле y=tg(x)+ctg(x), задать изменение х от -60 до 60 с шагом 10.

17. Если х не принадлежит отрезку [-4; 10], вычислить значение функции по формуле y=2cos(x), иначе вычислить значение функции по формуле y=x+2, задать изменение х от -10 до 20 с шагом 2.

 

Содержание отчета

Отчетом является файл Лаб.работа2_№вар.xlsx, созданный в результате выполнения задания.

 

Технология выполнения работы

Заполнение файла Лаб.работа2_№вар.xlsx по заданному варианту должно быть выполнено с использованием мастера функций и мастера диаграмм.

 

Вопросы для защиты работы

1. Назвать и охарактеризовать основные типы диаграмм.

2. Описать процесс создания диаграмм.

3. Дать определение понятиям “Ряд данных” и “категория данных”.

4. Описать синтаксис и правила использования логических функций.

5. Что такое макрос?

6. Как записать макрос?

 

Лабораторная работа № 7
Электронные таблицы MS Excel: работа с матрицами

 

Время выполнения – 2 часа.

 

Цель работы

Научиться приемам работы с матрицами.

 

Задачи лабораторной работы

После выполнения работы студент должен:

1) знать приемы работы с матрицами;

2) уметь решать системы линейных алгебраических уравнений;

3) уметь выполнять проверку решения.

 

Перечень обеспечивающих средств

Для обеспечения выполнения работы необходимо иметь компьютер со следующим обеспечением: операционная система Windows и MS Office 2007 и выше.

 

Общие теоретические сведения

Система mn чисел, расположенных в прямоугольной таблице из m строк и n столбцов, называется матрицей. Если m=n, то матрица называется квадратной, иначе прямоугольной. Над матрицами могут быть выполнены операции сложение матриц, умножение матрицы на число, на вектор.

Если матрица имеет размер 1 x n, то она называется вектором-строкой, а m x 1 – вектором-столбцом.

Если в матрице переставить строки и столбцы местами, то получим транспонированную матрицу.

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

Пример 1. Умножить матрицу А_{2, 3} на матрицу В_{3, 3} и получить матрицу С_{2, 3}.

1. Задать значения элементам матриц A_{2, 3}, В_{3, 3}.

	А	В	С	D	E	F
	A2, 3=
	В3, 3=
	С2, 3=

1. Выделить место для результирующей матрицы С_{2, 3}.

2. В строку формул записать знак равно (=).

3. С помощью мастера функций найти функцию МУМНОЖ.

4. Задать для нее исходные данные (указать с помощью мыши адреса матриц А и В).

5. Активизировать строку формул.

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

7. В результирующей матрице получим результат (рис. 1).

Рис. 1. Результат перемножения матриц

 

Пример 2. Решение системы линейных алгебраических уравнений.

Задана система линейных уравнений (1)

x1+2·x2+3·x3 = 4

4·x1+3·x2+2·x3=1 (1)

x1+3·x2+2мx3 = 4

В матричной форме система (1) имеет вид

А_{3, 3} · Х_{3, 1} =В_{3, 1} (2), где А_{3, 3}-матрица коэффициентов при неизвестных

B_{3, 1} – вектор правых частей.

Вектор неизвестных Х_{3, 1} может быть найден по формуле

Х_{3, 1}=А_{3, 3}^-1 · В_{3, 1} (5)

А_{3, 3}^-1 – обратная матрица.

Решение задачи выполнить в таблице.

	A	B	C	D	E	F	H
		Матрица исходных коэффициентов			Вектор правых частей
	А3, 3=					В3, 1=
		Обратная матрица			Вектор неизвестных
	А3, 3-1=					Х3, 1=

 

1. Ввести в таблицу значения матрицы коэффициентов А_{3, 3}.

2. Ввести в таблицу значения вектора В_{3, 1}.

3. Выделить место для обратной матрицы А_{3, 3}^-1.

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

5. Ввести в диалоговое окно параметров функции адрес исходной матрицы коэффициентов мышью. Проверить записанный адрес. Если все нормально, щелкнуть мышью по строке формул (в ней появится курсор) и нажать 3 клавиши одновременно Ctrl+Shift+Enter. В выделенных ячейках появятся значения обратной матрицы.

6. Выделить место для результата (вектор неизвестных) Х_{3, 1}.

7. С помощью мастера функций найти функцию МУМНОЖ.

8. Ввести в диалоговое окно два адреса:

– адрес обратной матрицы (массив1);

– адрес вектора правых частей (массив2).

9. Активизировать строку формул, чтобы в ней появился курсор и нажать клавиши Ctrl+Shift+Enter (рис. 2).

Рис. 2. Решение системы линейных алгебраических уравнений

 

Пример 3. Решение системы линейных алгебраических уравнений методом Крамера (через определители). Работа со склеенными листами.

Если данные для каких-то таблиц повторяются, то их лучше набирать в режиме «склеенных листов». Можно склеивать подряд стоящие листы, не подряд стоящие или вообще все листы рабочей книги.

Пусть необходимо склеить три подряд стоящих листа (Лист1, Лист2, Лист3). Для этого производим щелчок левой кнопки мыши по ярлычку Лист1, затем нажимаем клавишу Shift и производим щелчок по ярлычку Лист3. Тогда все три ярлычка станут светлее. Чтобы расклеить листы, выполнить щелчок правой кнопкой мыши по ярлычку Лист1 и выбрать команду Разгруппировать листы.

Для выполнения задания склеим 4 листа. Матрицу исходных коэффициентов и вектор правых частей поместить на склеенные листы. Затем расклеить листы. На листах 2, 3, 4 столбцы при неизвестных заменить векторами правых частей для вычисления определителей неизвестных: на листе2 в 1-ый столбец, на листе3 во 2-ой столбец и на листе4 в третий столбец. Вычислить определители на каждом листе, используя функцию МОПРЕД. Для вычисления значения неизвестных разделить определитель для соответствующей переменной на общий определитель для матрицы исходных коэффициентов, так для вычисления х1 разделить определитель на листе2 на определитель на листе1, для х2 – определитель на листе3 на определитель на листе1 и т. д. Ввод формул выполнять только в строку формул.

С дополнительным материалом по теме можно ознакомиться в литературе [1, 3, 4, 7].

 

Задание

1. Найти для матрицы A_{2, 3} транспонированную матрицу, используя функцию ТРАНСП.

2. Для матрицы В_{3, 3} найти обратную матрицу с помощью функции МОБР.

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

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

Варианты заданий

1 вариант	2 вариант
2·х1+х2-5·х3+х4=8 х1-3·х2-6·х4=9 2·х2-х3+2·х4=-5 х1+4·х2-7·х3+6·х4=0	3·х1-х2=5 -2·х1+х2+х3=0 2·х1-х2+4·х3=15
3 вариант	4 вариант
7, 9·х1+5, 6·х2+5, 7·х3-7, 2·х4=6, 68 8, 5·х1-4, 8·х2+0, 8·х3+3, 5·х4=9, 95 4, 3·х1+4, 2·х2-3, 2·х3+9, 3·х4=8, 6 3, 2·х1-1, 4·х2-8, 9·х3+3, 3·х4=1	6·х1-х2-х3=11, 33 -х1+6·х2-х3=32 -х1-х2+6·х3=42
5 вариант	6 вариант
3·х1+х2-х3+2·х4=6 -5·х1+х2+3·х3-4·х4=-12 2·х1+х3-х4=1 х1-5·х2+3·х3-3·х4=3	10·х1+х2+х3=12 2·х1+10·х2+х3=13 2·х1+2·х2+10·х3=14
7 вариант	8 вариант
2·х1-х2-х3=-3 3·х1+5·х2-2·х3=1 х1-4·х2+10·х3=0	х1-0, 2·х2-0, 2·х3=0, 6 -0, 1·х1+х2-0, 2·х3=0, 7 -0, 1·х1-0, 1·х2+х3=0, 8
9 вариант	10 вариант
3·х1-х2= 5, 2 -2·х1+х2+х3=0 2·х1-х2+4·х3=15, 4 х1+4·х2-7·х3+6·х4=0	2·х1+х2-5·х3+х4=8 х1-3·х2-6·х4=9 2·х2-х3+2·х4=-5
11 вариант	12 вариант
6·х1-х2-х3=11, 33 -х1+6·х2-х3=32 -х1-х2+6·х3=42	х1+3·х2-2·х3-2·х5=0, 5 3·х1+4·х2-5·х3+х4-3·х5=5, 4 -2·х1-5·х2+3·х3-2·х4+2·х5=5, 0 -2·х1-3·х2+2·х3+3vх4+4·х5=3, 3 х2- 2·х3+5·х4+3·х5=7, 5
13 вариант	14 вариант
3·х1+х2-х3+2·х4=6 -5·х1+х2+3·х3-4·х4=-12 2·х1+х3-х4=1 х1-5·х2+3·х3-3·х4=3	4·х1+0, 24·х2-0, 08·х3=8 0, 09·х1+3·х2-0, 15·х3=9 0, 04·х1-0, 08·х2+4·х3=20

 

Сравнить результаты вычислений.

Решение системы уравнений проверить, умножив матрицу исходных коэффициентов на вектор неизвестных, в результате должен получиться вектор правых частей. Результаты заданий сохранить в файле Матрицы_№вар_Фио_студента.xlsx.

 

Содержание отчета

Отчетом является файл Матрицы_№вар_Фио_студента.xlsx, созданный в результате выполнения задания.

 

Технология выполнения работы

Заполнение файла Матрицы_№вар_Фио_студента.xlsx по заданному варианту должно быть выполнено с использованием приемов работы с матрицами. Решение системы уравнений должно быть выполнено двумя методами с проверкой решения.

 

Вопросы для защиты работы

1. Какие СЛАУ можно решать методом обратной матрицы?

2. Какие три клавиши нужно нажать, чтобы получить результат при работе с матрицами?

3. Можно ли матрицу отнести к структурированным данным?

4. Как выполнить проверку решения СЛАУ?

5. Какие методы вы знаете для решения СЛАУ?

 

Лабораторная работа № 8
Электронные таблицы MS Excel: работа со списками

 

Время выполнения – 2часа.

 

Цель работы

Освоение приемов использования списков для анализа табличных данных.

 

Задачи лабораторной работы

После выполнения работы студент должен:

1) научиться редактировать списки;

2) уметь выполнять сортировку данных и вычисление итоговых данных в списках;

3) уметь применить фильтры для поиска данных в списках.

 

Перечень обеспечивающих средств

Для обеспечения выполнения работы необходимо иметь компьютер со следующим обеспечением: операционная система Windows и MS Office 2007 и выше.

Общие теоретические сведения

Табличный процессор MS Excel дает в руки пользователя мощные средства анализа данных, если таблица, где они хранятся, организована особым образом, называемым списком. Список – способ хранения данных в таблице, совокупность поименованных строк, содержащих однородные данные (набор строк таблицы, содержащий связанные данные).

Существует ряд требований, которым должны отвечать списки:

– на листе рабочей книги может размещаться только один
список;

– если на этом листе размещаются данные, не входящие в
список, то их должны отделять от списка не менее одного
пустого столбца и одной пустой строки;

– первая строка списка должна содержать заголовки столбцов;

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

– во всех ячейках столбца размещаются однотипные данные,
при этом используется один формат;

– перед содержимым ячейки не должно быть пробелов;

– для поиска записи, подлежащей удалению или изменению, следует нажать кнопку Критерии и ввести в соответствующие поля условия поиска. Затем с помощью кнопок Далее и Назад найти записи, соответствующие этим условиям. Для поиска необходимых данных можно, так же как и в текстовом процессоре MS Word, использовать команду Редактирование/ Найти (рис. 1).

Рис. 1. Область Редактирование на Ленте MS Excel 2007

В этом случае для организации поиска используется Раскрывающийся список Найти и выделить. Табличный процессор MS Excel позволяет производить сортировку по нескольким показателям (до трех). Очевидно, что в первую очередь сортировка производится по той категории, которая включает в себя наибольшее число записей, так как последующая сортировка осуществляется уже внутри нее. Для упорядочения данных в ячейках по значениям (без учета формата) в Microsoft Excel предусмотрен определенный порядок сортировки – по возрастанию или по убыванию, причем этот порядок зависит от типа данных.