16423

СОЗДАНИЕ МАКРОСОВ-ФУНКЦИЙ В EXCEL

Лабораторная работа

Информатика, кибернетика и программирование

СОЗДАНИЕ МАКРОСОВФУНКЦИЙ В EXCEL Методические указания к выполнению лабораторных работ по дисциплине Информационные технологии для студентов технических специальностей Методические указания предназначены для проведения лабораторных работ по дисциплине Инфо

Русский

2013-06-22

162.5 KB

78 чел.

СОЗДАНИЕ МАКРОСОВ-ФУНКЦИЙ В EXCEL

Методические указания к выполнению лабораторных работ

по дисциплине «Информационные технологии»

для студентов технических специальностей

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

Печатается по решению методической комиссии факультета «Информатика и вычислительная техника»

Лабораторная работа № 1

Тема: Программирование макросов для функций, задаваемых с условиями.

Цель работы: Научиться использовать макросы Excel для упрощения вычислений.

Форма отчета: Выполнение индивидуального зачетного задания.

Примеры выполнения лабораторной работы

Пример 1. Вычислить для произвольно заданного значения переменной x значение функции

Решение с помощью формулы Excel. Создадим новый файл в программе Excel. Оформление решения показано в таблице 1.

На Лист1 в ячейках A1 и B1 введем обозначения x  и y.

Присвоим ячейке A2 имя x. Для этого выполним команду «Вставка — Имя — Присвоить — x».

Пусть значение переменной x равно 0,5. Запишем число 0,5 в ячейке A2, а значение функции y вычислим в ячейке B2. Для этого введем в B2 формулу

=ЕСЛИ(x<=1;x^3+1;ЕСЛИ(x<=3;SIN(x);EXP(-x)*x))

Таблица 1

A

B

1

x

y

2

0,5

=ЕСЛИ(x<=1;x^3+1;ЕСЛИ(x<=3;SIN(x);EXP(-x)*x))

В результате вычисления в ячейке B2 появится значение 1,125.

Введем в ячейку A2 значение 1,5. Тогда в B2 появится значение 0,997494987. Если введем в ячейку A2 значение 3,5, то в B2 появится значение 0,105690842.

Решение с помощью макроса Excel. Создадим в программе Excel пользовательскую функцию y(x) для вычисления данной функции, пользуясь встроенным языком Visual Basic.

Приведем алгоритм создания пользовательcкой функции-макроса:

1) Выполним команду меню «Сервис — Макрос — Редактор Visual Basic». Откроется окно «Microsoft Visual Basic».

2) Выполним команду меню «InsertModule» и введём текст программы-функции (Комментарий после апострофа вводить не надо)

Function y(x)           ' Заголовок описания функции y(x)

If x <= 1 Then        ' если x <= 1 то

  y = x ^ 3 + 1       '        y = x ^ 3 + 1

ElseIf x <= 3 Then   ' иначе если x <= 3 то

  y = Sin(x)             '        y = Sin(x)

Else                         ' иначе

  y = Exp(-x) * x      '        y = Exp(-x) * x

End If                      ' конец оператора если

End Function            ' конец описания функции

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

Например, введем в ячейки С2, С3 и С4 соответственно формулы   =y(0,5),  =y(1,5),   =y(3,5). В этих ячейках получим значения 1,125, 0,997495, 0,105691.

Пример 2. Построить таблицу значений и график функции из примера 2 на отрезке  с шагом 0,2.

Решение с использованием макроса. Мы будем использовать макрос-функцию y(x), созданную в примере 1, поэтому откроем файл примера 1 и перейдем на Лист2.

В ячейках A1 и B1 введем обозначения x и y.

Введем в A2, A3 значения 0 и 0,2. Выделим ячейки A2:A3 и протянем маркер заполнения (правый нижний угол) до ячейки A12.

Введем в B2 формулу =y(A2). Протянем ячейку B2 маркером заполнения вниз до  B12. Оформление решения показано в таблице 2.

Таблица 2

A

B

1

x

y

2

0

1

3

0,2

1,008

4

0,4

1,064

5

0,6

1,216

6

0,8

1,512

7

1

2

8

1,2

0,932039

9

1,4

0,98545

10

1,6

0,999574

11

1,8

0,973848

12

2

0,909297

Выделим диапазон A1:B12 и щелкнем указателем мыши на панели инструментов ярлык «Мастер диаграмм». Выберем тип «Точечная» и кнопку «Готово».

Задания к лабораторной работе №1.

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

Таблица 3

№ варианта

Функция

Отрезок

Шаг

1

[0; 3]

0,1

2

[1; 3]

0,2

3

[0; 3]

0,3

4

[1; 5]

0,2

5

[0; 3]

0,1

6

[2; 5]

0,25

Продолжение табл. 3

№ варианта

Функция

Отрезок

Шаг

7

[3; 5]

0,25

8

[0; 3]

0,1

9

[1; 3]

0,4

10

[0; 3]

0,05

11

[1; 5]

0,4

12

[0; 4]

0,2

13

[2; 5]

0,3

Окончание табл. 3

№ варианта

Функция

Отрезок

Шаг

14

[0; 5]

0,4

15

[1; 6]

0,5

16

[0; 3]

0,1

17

[1; 3]

0,2

18

[0; 3]

0,3

19

[0; 5]

0,2

20

[0; 3]

0,1

Лабораторная работа № 2

Тема: Программирование макросов для функций, параметрами и/или значениями которых являются массивы.

Цель работы: Научиться использовать макросы Excel для упрощения вычислений с матрицами и векторами.

Форма отчета: Выполнение индивидуального зачетного задания.

Пример выполнения лабораторной работы

Пример 3. Вычислить таблицу значений вектор-функции
U(t) = U(x, y, z), где x = x(t) = t 2, y = y(t) = sin t, z = z(t) = cos t,

для  значений переменной  t = 0; 0,1; …, 1.

Решение с помощью макроса. Создадим макрос-функцию U(t), которая вычисляет значение данной вектор-функции.

1) Выполним команду меню «Сервис — Макрос — Редактор Visual Basic». Откроется окно «Microsoft Visual Basic».

2) Выполним команду меню «InsertModule» и введём текст следующей программы-функции

Option Base 1       ‘ Нижнее значение индекса в массивах равно 1

Function U(t)

Dim uu(3)                 ‘ Описание вектора из трех элементов

x = t ^ 2: uu(1) = x   ‘ вычисление компонент вектор-функции

y = Sin(t): uu(2) = y

z = Cos(t): uu(3) = z

U = uu                      ‘ присваивание имени U функции вектора uu

End Function

3) Перейдем из редактора Visual Basic в Excel и введем в ячейках A1:D1 обозначения, как показано в табл. 4.

Таблица 4

A

B

C

D

1

t

x

y

z

2

0

0

0

0

3

0,1

0

0,01

0,099833

4

0,2

0

0,04

0,198669

5

0,3

0

0,09

0,29552

6

0,4

0

0,16

0,389418

7

0,5

0

0,25

0,479426

8

0,6

0

0,36

0,564642

9

0,7

0

0,49

0,644218

10

0,8

0

0,64

0,717356

11

0,9

0

0,81

0,783327

12

1

0

1

0,841471

Введем в ячейки A2, A3 значения 0 и 0,1. Выделим ячейки A2:A3 и маркером заполнения протянем вниз до A12.

Выделим ячейки B2:D2, введем формулу =U(A2) и удерживая нажатыми клавиши Ctrl и Shift нажмем Enter. В ячейках B2:D2 появятся числовые значения.

Выделим ячейки B2:D2 и маркером заполнения протянем вниз до строки B12:D12.

Пример 4. Вычислить суммы элементов строк матрицы, содержащей 4 строки и 3 столбца (табл. 5).

Таблица 5

1

-1

9

2

2

4

3

3

1

4

5

3

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

Запишем элементы данной матрицы в диапазоне A1:C4 (табл. 6).

В ячейку D1 введем формулу =СУММ(A1:C1).

Выделим ячейку D1 и протянем маркером заполнения вниз до ячейки D4. В столбце D1:D4 получим суммы элементов соответствующих строк матрицы.

Решение с помощью макроса. Создадим макрос-функцию Sum_str(x), которая для заданной матрицы x, содержащей M строк и N столбцов, вычисляет вектор-столбец из M элементов, содержащий суммы элементов соответствующих строк матрицы.

1) Выполним команду меню «Сервис — Макрос — Редактор Visual Basic». Откроется окно «Microsoft Visual Basic».

2) Выполним команду меню «InsertModule» и введём текст следующей программы-функции

Option Base 1

Function Sum_str(x)

Dim y()

Dim M As Integer, N As Integer

M = x.Rows.Count

N = x.Columns.Count

ReDim y(M)

For i = 1 To M:

y(i) = 0:  For j = 1 To N:  y(i) = y(i) + x(i, j):  Next j

Next i

Sum_str = Application.Transpose(y)

End Function

3) Выделим диапазон ячеек E1:E4, введем формулу =Sum_str(A1:C4) и удерживая нажатыми клавиши Ctrl и Shift нажмем Enter. В ячейках E1:E4 появятся числовые значения, совпадающие со значениями в D1:D4.

Таблица 6

A

B

C

D

E

1

1

-1

9

9

9

2

2

2

4

8

8

3

3

3

1

7

7

4

4

5

3

12

12

Задания для самостоятельной работы.

Написать макрос-функцию для вычисления

1. Столбца из произведений элементов строк матрицы.

2. Строки из произведений элементов столбцов  матрицы.

3. Столбца из максимальных элементов строк матрицы.

4. Столбца из минимальных элементов строк матрицы.

5. Строки из максимальных элементов столбцов матрицы.

6. Строки из минимальных элементов столбцов матрицы.

7. Суммы положительных элементов  матрицы.

8. Суммы отрицательных элементов  матрицы.

9. Числа положительных элементов  матрицы.

10. Числа отрицательных элементов  матрицы.

11. Суммы поддиагональных элементов  квадратной матрицы.

12. Числа элементов  квадратной матрицы, по модулю меньших единицы.

13. Числа элементов вектора, по модулю меньших единицы.

14. Числа элементов вектора, по модулю больших единицы.

15. Вектора из диагональных элементов квадратной матрицы.

16. Матрицы, элементы которой задаются формулой xij = i + j.

17. Вектора, элементы которого задаются формулой xi = i .

18. Матрицы, элементы которой задаются формулой xij = 2i + 5j 2.

19. Вектора, элементы которого задаются формулой xi = i 2.

20. Матрицы, элементы которой задаются формулой xij = i * j.

Рекомендуемая литература

1. Ричард Шеферд. Создание макросов для Microsoft Excel. — М.: НТ Пресс, 2007, — 352 с.

2. Б. В. Соболь, Б. Ч. Месхи, И. М. Пешхоев. Практикум по вычислительной математике. Ростов-на-Дону, Феникс, 2008.

Составители: д. т. н., проф. Б. В. Соболь, к. ф.-м. н., доц. И. М. Пешхоев

СОЗДАНИЕ МАКРОСОВ-ФУНКЦИЙ В EXCEL

Методические указания и задания для лабораторных работ
по дисциплине «Информационные технологии»

Редактор А. А. Литвинова

ЛР №             от                                    В набор

В печать                                               Офсет. Бумага тип.№3.

Формат 6084/16.     Объем 0,6 усл.п.л.,   0,5 уч.-изд.л.

Заказ №                 Тираж                Цена

Издательский центр ДГТУ.

Адрес университета и полиграфического предприятия:

344010, Ростов-на-Дону, пл. Гагарина, 1.


 

А также другие работы, которые могут Вас заинтересовать

24367. Основные функции науки в жизни общества (наука как мировоззрение, как производительная и социальная сила) 59 KB
  Культурная сущность науки влечет за собой ее этическую и ценностную наполненность. Результативная функция науки осуществляется из систему образования воспитания обучения и подключения членов общества к исследовательской деятельности и эпосу науки. Функций у науки много и с ее развитием их становится все больше и больше.
24368. Возникновение науки. Две стратегии порождения знаний: обобщение практического опыта и конструирование теоретических моделей 104 KB
  Такой например характер имели геометрические знания древних египтян. Однако по мере развития практики наряду с отмеченными способами познания формируется новый способ построения знания. При таком методе исходные идеальные объекты черпаются уже не из практики а заимствуются из ранее сложившихся систем знаний языка и применяются в качестве строительного материала при формировании нового знания. Таким образом в науке наряду с эмпирическими правилами и зависимостями которые знала и преднаука формируется особый тип знания теория...
24369. Античный этап развития науки: логика и математика 104 KB
  Первые европейские ученые и философы любители мудрости Фалес Анакасимен Анаксимандр Гераклит опираясь на факты и логику впервые мыслили вещи не фантастически а стремились к естественнонаучном безличному целостному описанию природы космоса мира. Осуществляя многочисленные наблюдения за поведением планет Солнца природных и общественных явлений используя также и мифологически воззрения от них полностью устраниться не удалось они пытались найти как общие законы изменения и устройства мира так и частные его характеристики....
24370. Наука средневековья. Роль христианской теологии в изменении созерцательной позиции ученого 114 KB
  Начало мира это сам Бог. В результате христианское учение постепенно стало приобретать форму рациональной теологии где определенное место отводилось вопросам познания устройства мира. Предельность конечность мира в пространстве включала геоцентризм Аристотеля и Птоломея и оттеняла космическую функцию Христа. Он как бы замещал исследование причинноследственных связей превращался в важнейший способ восприятия мира и выражения опыта развивал мышление позволяя превращать истины веры в зрительные образы.
24371. Формирование идеалов (математизированное и опытное, экспериментальное знание) науки Нового времени (Г. Галилей, Ф. Бэкон, Р. Декарт) 127 KB
  это время становления новой современной науки. Этому способствовали как внутренние изменения самой науки уже Коперник и Кеплер свою гелиоцентрическую картину мира обосновывают с помощью математического расчета. Давление воды на лопатку движение деталей насоса кузнечного молота шелкопрядильной машины включали в себя непрерывную цепь механических причин и следствий ставших основой механической картины мира классического идеала науки.
24372. Формирование и соотношение естественных, технических и социально-гуманитарных наук: сходство и различия 106 KB
  Лпркшпрожю Развитие технических наук стимулирует развитие естествознания их взаимосвязь не прервалась и после выделения технической науки в отдельную область знания. В то же время существует большой разрыв между действительным применением результатов технической науки на практике и занятием самой этой наукой. С методологической точки зрения исследование в технической науке не сильно отличается от естественнонаучного исследования. Таким образом в научнотехнических дисциплинах необходимо четко различать исследования включенные в инженерную...
24373. Многообразие типов научного знания. Сущность и структура эмпирического знания 55 KB
  Материализация и первичное обобщение данных отражения в форме знания на основе правил соответствия узнавание сравнение измерение описание образуют эмпирические факты эмпирические объекты эмпирическую информацию. Эмпирические факты условно можно разделить на два вида: а факты в основание которых лежат не зависящие от субъекта явления например природные процессы и б факты созданные человеком например экономика экономические отношения. Эмпирические факты обладают большей степенью общности чем единичные данные но меньшей чем...
24374. Сущность и структура теоретического знания 52.5 KB
  Теория это высшая самая развитая форма организации научного знания дающая целостное представление о закономерностях и существенных связях определенное области действительности объекта данной теории 77. С помощью этих знаковых образований языка теории возникает возможность более точно и глубоко судить о соответствующей изучаемой предметной области. Кроме того тот или иной вид теории определяется предметом и задачами исследования глубиной раскрытия сущности предметов и др. Также имеют место попытки поиска идеальной схемы...
24375. Основания науки: нормы и идеалы науки, роль философских идей и принципов в обосновании научного знания (законы и категории) 116.5 KB
  Среди идеалов и норм можно выделить два взаимосвязанных блока: а собственно познавательные установки которые регулируют процесс воспроизведения в различных формах научного знания; б социальные нормативы фиксируют роль науки и ее ценность для общественной жизни на определенном этапе исторического развития. Существует еще и такое мнение что в период нормального эволюционного периода развития науки возможно бессознательное использование многих научных идеалов и норм. Закон единства и борьбы противоположностей является ядром диалектики...