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

Дата добавления: 2014-11-10; просмотров: 870

Учебное пособие

Редактор Н.С. Менькина

Изготовление оригинал-макета: М.Н. Авдюхова

Лицензия А № 165724 от 11 апреля 2006 г.

Подписано в печать 28.04.10. Тир. 300.

Уч.-изд. л. 3. Формат 60 ´ 84 ¹/₁₆. Усл. п. л. 5,3.

Гарнитура таймс. Зак. .

ГОУ ВПО «Череповецкий государственный университет»

162600 г. Череповец, пр. Луначарского, 5.

ПРОГРАМУВАННЯ В СЕРЕДОВИЩІ EXCEL

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

ЗМІСТ

ВСТУП....................................................................................................................... 5

Лабораторна робота №1. Програмування МАТЕМАТИЧНИХ ФУНКЦІЙ ТА ЇХ ТАБУЛЮВАННЯ................................................................................................ 7

1.1. Програма роботи..................................................................................... 7

1.2. Вказівки до виконання роботи................................................................ 7

1.3. Зразок виконання завдання..................................................................... 8

1.4. Індивідуальні завдання.......................................................................... 10

1.5. Контрольні запитання............................................................................ 12

Лабораторна робота №2. Програмування ЛОГіЧНИХ ФУНКЦІЙ ТА ЇХ ТАБУЛЮВАННЯ........................................................................................................................ 13

2.1. Програма роботи................................................................................... 13

2.2. Вказівки до виконання роботи.............................................................. 13

2.3. Зразок виконання завдання................................................................... 14

2.4. Індивідуальні завдання.......................................................................... 17

2.5. Контрольні запитання............................................................................ 20

Лабораторна робота №3. ХАРАКТЕРНІ ПРИЙОМИ НАКОПИЧЕННЯ СУМИ І ДОБУТКУ........................................................................................................................ 21

3.1. Програма роботи................................................................................... 21

3.2. Вказівки до виконання роботи.............................................................. 21

3.3. Зразок виконання завдання................................................................... 22

3.4. Індивідуальні завдання.......................................................................... 25

3.5. Контрольні запитання............................................................................ 27

Лабораторна робота №4. ОБЧИСЛЕННЯ ЕЛЕМЕНТІВ МАСИВІВ........................... 28

4.1. Програма роботи................................................................................... 28

4.2. Вказівки до виконання роботи.............................................................. 28

4.3. Зразок виконання завдання................................................................... 28

4.4. Індивідуальні завдання.......................................................................... 36

4.5. Контрольні запитання............................................................................ 40

Лабораторна робота №5. ДІЇ НАД МАТРИЦЯМИ.................................................... 41

5.1. Програма роботи................................................................................... 41

5.2. Вказівки до виконання роботи.............................................................. 41

5.3. Зразок виконання завдання................................................................... 41

5.4. Індивідуальні завдання.......................................................................... 48

5.5. Контрольні запитання............................................................................ 51

Лабораторна робота №6. Характерні прийоми оброблення елементів матриць........................................................................................................................ 52

6.1. Програма роботи................................................................................... 52

6.2. Вказівки до виконання роботи.............................................................. 52

6.3. Зразок виконання завдання................................................................... 55

6.4. Індивідуальні завдання.......................................................................... 73

6.5. Контрольні запитання............................................................................ 77

Лабораторна робота №7. ВИКОРИСТАННЯ ФУНКЦІЙ КОРИСТУВАЧА.................. 78

7.1. Програма роботи................................................................................... 78

7.2. Вказівки до виконання роботи.............................................................. 78

7.3. Зразок виконання завдання................................................................... 78

7.4. Індивідуальні завдання.......................................................................... 87

7.5. Контрольні запитання............................................................................ 91

----------------------

ВСТУП

У даному лабораторному практикумі підібрано низку лабораторних робіт, призначених для набуття основних навиків програмування математичних та логічних виразів у середовищі Microsoft Excel та додатку до нього Visual Basic for Application.

Частина І присвячена вивченню середовища VISUAL BASIC. Перші три лабораторні роботи полегшують знайомство з програмою та дозволяють засвоїти основні прийоми створення та використання електронних таблиць. Виконавши ці роботи, користувач навчиться створювати нову робочу книгу та вводити в неї дані, зберігати та відкривати робочі книги, розмножувати дані, вставляти та знищувати окремі частини таблиць тощо. Наступні лабораторні роботи, що супроводжуються індивідуальними завданнями, призначені для розв'язування конкретних задач. Це табулювання функцій, побудова діаграм, розробка складних бланків, розрахунок заробітної платні, операції зі списками даних тощо.

Частина ІІ присвячена вивченню елементів програмування на Visual Basic for Application, як додаток до Excel. Перші лабораторні роботи дають можливість вивчити роботу з об'єктами та опрацьовувати подію click. Наступні роботи дають приклади реалізації лінійних, розгалужених та циклічних алгоритмів, а також розглядається робота з масивами. Visual Basic for Application розглядається в розрізі доповнення і розширення можливостей Excel.

У лабораторних роботах наводяться тексти програм для середовища Excel у режимі формул. Необхідно пам'ятати, що запис "Клітина B26" означає знаходження курсора листа у даній клітині. Запис "à =C22" означає, що у цю клітину передається значення з клітини С22, після чого необхідно натиснути клавішу < Enter>. Запис "Клітина B27:B36" означає виділення зазначеного діапазону клітин. Запис "à =B26+$G$22" означає, що після натискання "=" у клітині В27 необхідно набрати зазначену формулу, а для розповсюдження її по всьому діапазону необхідно натиснути комбінацію клавіш <Ctrl + Enter>.

У лабораторних роботах наводяться тексти функцій і програм для середовища Visual Basic for Application. Рекомендується під час програмування математичних виразів у функціях записувати їх не одним рядком, а розписувати по частинах. Це частково забезпечить вас від здійснення помилок, а також дасть змогу набагато швидше їх проаналізувати і виявити. Рекомендується також перед кожною групою дій записувати відповідні коментарі, що дасть змогу швидко розібратися у тексті програми.

Представлений матеріал у практикумі побудовано за принципом повторюваності з попереднім поясненням дій для виконання тієї чи іншої операції. При цьому пояснення наводиться, якщо операція виконується вперше. Наступні завдання передбачають, що користувач вже володіє даним матеріалом. Тому, якщо формулювання чергової команди здається незрозумілим, потрібно повернутися назад, до попередніх команд, що пояснювалися, або відповідних теоретичних підказок (зазначених значком ).

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

Звичайно, запропоновані завдання не можуть охопити всю повноту можливостей програми Excel та Visual basic for Application. Проте набутих навичок буде достатньо для вільного програмування у середовищі Excel та Visual Basic for Application в межах початківця, використання для вирішення найпростіших економічних задач та для подальшої самостійної роботи з програмою.

P.S. Для того щоби при наступному включенні всі функції і макроси працювали - відкрийте головне меню Сервис – Макрос - Безопасность -установіть рівень – Середній.

Лабораторна робота №1.
Програмування МАТЕМАТИЧНИХ ФУНКЦІЙ ТА ЇХ ТАБУЛЮВАННЯ

1.1. Програма роботи

1.1.1. Підготувати бланк завдання та отримати завдання.

1.1.2. Запрограмувати математичні функції згідно з вказівками до виконання роботи.

1.1.3. Виконати одновимірне табулювання функцій згідно з вказівками до виконання роботи.

1.1.4. Виконати двовимірне табулювання функцій згідно з вказівками до виконання роботи.

1.1.5. Підготувати власті коректні вхідні дані для кожної з виконаних задач, провести розрахунки і проаналізувати їх.

1.1.6. Оформити звіт про роботу та захистити її

1.2. Вказівки до виконання роботи

1.2.1. Студент, відповідно до індивідуального номера, вибирає своє завдання з розд. 1.4, яке складається з декількох задач, і записує його до бланку.

1.2.2. Математичні функції у середовищі Excel необхідно запрограмувати двома способами: з введенням і без введення допоміжних змінних. У середовищі Visual Basic математичні функції студент програмує на власний розсуд.

1.2.3. Необхідно пам'ятати, що одновимірне табулювання функцій здійснюється за однією незалежною змінною, конкретна назва якої задається у самій задачі. У ній також задаються початкове і кінцеве її значення, а також крок табулювання. Після виконання одновимірного табулювання функцій необхідно побудувати одновимірні їх графіки.

1.2.4. Необхідно пам'ятати, що двовимірне табулювання функцій здійснюється за двома незалежними змінними, конкретні назви яких задаються у самій задачі. У ній також задаються початкові та кінцеві їх значення, а також кроки табулювання. Після виконання двовимірного табулювання функцій необхідно побудувати двовимірні їх графіки, бажано поверхні.

1.2.5. Власних вхідних даних необхідно підготувати не менше двох ком­плек­­тів. Їхні значення мають бути коректними, знаходитися в розумних межах і не суперечити своїм математичним виразам (наприклад, щоб значення аргументу кореня квадратного чи натурального логарифма не були від'ємним, аргумент тангенса – не занадто великим і т.д.). Після проведення розрахунків отримані результати необхідно проаналізувати і занести до звіту.

1.2.6. Звіт має містити такі розділи:

· бланк завдання з записаною на ньому умовою задачі;

· текст програми у режимі формул – для середовища Excel; тексти функцій – для середовища Visual Basic;

· результати відповідних розрахунків, виконані у середовищі Excel, які передба­ча­ють­ся умовами задач;

· аналіз отриманих результатів, висновки і пропозиції.

1.3. Зразок виконання завдання

Задача 1.1. Задано x=2, y=4, z=-3. Відповідно до номера варіанту потрібно розробити програму для обчислення значень функцій a[x,y,z,b]іb[x,y,z] згідно з такими математичними виразами:

і .

Текст програми у режимі формул – для середовища Excel

Клітина C17 à =E9*((C9^2+TAN(E9+G9)^2)/(0,345*E9+SIN(C9^2)^2)+EXP(-(C9+E9)/G9))

Клітина F17 à =(C9+E9)^2*(C9+E9^2)*ABS(C17^2+G9)^0,3/(EXP(G9-2)+E9^2)

Тексти функцій – для середовища Visual Basic

Результати розрахунку, виконані у середовищі Excel

Задача 1.2. Задано x_п=-1,x_к=1,Dx=0.2, y=4.7, z=3.32. Відповідно до номера варіанту потрібно розробити програму для одновимірного табулювання функцій a=f[x,y,z,b] і b=f[x,y,z] за незалежною змінною х згідно з математичними виразами, наведеними у задачі 1.1. Побудувати одновимірні графіки цих функцій.

Текст програми у режимі формул – для середовища Excel

Одновимірне табулювання функцій b=f[x,y,z]іa=f[x,y,z,b]

Клітина B26 à =C22 – < Enter>

Клітини B27:B36 à =B26+$G$22 – <Ctrl + Enter>

Клітини C26:C36 à =Fn_b(B26;$C$23;$E$23) – <Ctrl + Enter>

Клітини D26:D36 à =Fn_a(B26;$C$23;$E$23;C26) – <Ctrl + Enter>

Клітини E26:E36 à =D26+C26 – <Ctrl + Enter>

Результати розрахунку, виконані у середовищі Excel

A	B	C	D	E	F	G
	xп=	-1	хк=		Dx=	0,2
	y=	4,7	z=	3,26
	X	b[x,y,z]	a[x,y,z,b]	a+b
	-1,0	75,418	149,575	224,99
	-0,8	87,597	183,516	271,11
	-0,6	98,234	219,293	317,53
	-0,4	103,390	251,039	354,43
	-0,2	104,463	279,194	383,66
	0,0	104,378	307,196	411,57
	0,2	104,326	336,818	441,14
	0,4	103,114	365,578	468,69
	0,6	97,820	385,927	483,75
	0,8	87,042	390,916	477,96
	1,0	74,720	386,484	461,20

Задача 1.3. Задано x_п=-1,x_к=1,Dx=0.2, y_п=-2,y_к=2,Dy=0.2, z=1.32. Відповідно до номера варіанту потрібно розробити програму для двовимірного табулювання функцій a=f[x,y,z,b] і b=f[x,y,z] за незалежними змінними х і y згідно з математичними виразами, наведеними у задачі 1.1. Побудувати двовимірні графіки цих функцій, бажано поверхні.

Текст програми у режимі формул – для середовища Excel

Одновимірне табулювання функції b=f[x,y,z]

Клітина B55 à =C50

Клітини B56:B63 à =B55+$G$50 – <Ctrl + Enter>

Клітина C54 à =C51

Клітини D54:M54 à =C54+$G$51 – <Ctrl + Enter>

Клітини C55:M63 à =Fn_b(C$54;$B55;$E$52) – <Ctrl + Enter>

Двовимірне табулювання функції a=f[x,y,z,b]

Клітина B67 à =C50

Клітини B68:B75 à =B67+$G$50 – <Ctrl + Enter>

Клітина C66 à =C51

Клітини D66:M66 à =C66+$G$51 – <Ctrl + Enter>

Клітини C67:M75 à =Fn_a(C$66;$B67;$E$52;C55) – <Ctrl + Enter>

Результати розрахунку, виконані у середовищі Excel

1.4. Індивідуальні завдання

Задача 1.1. Задано x=0.48×№, y=0.47×№, z=-1.32×№(де № – но­мер ва­рі­ан­ту). Відповідно до номера варіанту потрібно розробити програму для обчислення значень функцій a[x,y,z,b]іb[x,y,z] згідно з такими математичними виразами:

1) , ;

2) , ;

3) , ;

4) , ;

5) , ;

6) , ;

7) , ;

8) , ;

9) , ;

10) , ;

11) , ;

12) , ;

13) , ;

14) , ;

15) , .

16) , ;

17) , ;

18) , ;

19) , ;

20) , ;

21) , ;

22) , ;

23) , ;

24) , ;

25) , ;

26) , ;

27) , ;

28) , ;

29) , ;

30) , .

Задача 1.2. Задано x_п=-1,x_к=1,Dx=0.2, y=4.7, z=1.32. Відповідно до номера варіанту потрібно розробити програму для одновимірного табулювання функцій a=f[x,y,z,b] і b=f[x,y,z] за незалежною змінною х згідно з математичними виразами, наведеними в завданні 1.1. Побудувати одновимірні графіки цих функцій.

Задача 1.3. Задано x_п=-1,x_к=1,Dx=0.2, y_п=-2,y_к=2,Dy=0.2, z=1.32. Відповідно до номера варіанту потрібно розробити програму для двовимірного табулювання функцій a=f[x,y,z,b] і b=f[x,y,z] за незалежними змінними х і y згідно з математичними виразами, наведеними у задачі 1.1. Побудувати двовимірні графіки цих функцій.

1.5. Контрольні запитання

1. Що називається формулою?

2. Які елементи можуть входити у формули?

3. Що таке оператори, операції та операнди?

4. Назвати основні правила введення формул.

5. Як виконується перегляд формул листа та їх значень?

6. Що називається функцією?

7. Основні правила використання функцій.

8. Записати послідовність дій при виправленні формул з функціями.

9. Записати послідовність дій при вставці функції у формулу.

11. Записати послідовність дій при введенні вкладених функцій.

12. Навести приклади математичних функцій.

13. Навести приклади тригонометричних функцій

Лабораторна робота №2.
Програмування ЛОГіЧНИХ ФУНКЦІЙ ТА ЇХ ТАБУЛЮВАННЯ

2.1. Програма роботи

2.1.1. Підготувати бланк завдання та отримати завдання.

2.1.2. Запрограмувати логічні функції згідно з вказівками до виконання роботи.

2.1.3. Виконати одновимірне табулювання логічних функцій згідно з вказівками до виконання роботи.

2.1.4. Виконати двовимірне табулювання логічних функцій згідно з вказівками до виконання роботи.

2.1.5. Підготувати власті коректні вхідні дані для кожної з виконаних задач, провести розрахунки і проаналізувати їх.

2.1.6. Оформити звіт про роботу та захистити її

2.2. Вказівки до виконання роботи

2.2.1. Студент, відповідно до індивідуального номера, вибирає своє завдання з розд. 2.4, яке складається з декількох задач, і записує його до бланку.

2.2.2. Логічні функції у середовищі Excel необхідно запрограмувати двома способами: з введенням і без введення допоміжних змінних. У середовищі Visual Basic логічні функції студент програмує на власний розсуд.

2.2.3. Необхідно пам'ятати, що одновимірне табулювання логічних функцій здійснюється за однією незалежною змінною, конкретна назва якої задається у самій задачі. У ній також задаються початкове і кінцеве її значення, а також крок табулювання. Після виконання одновимірного табулювання функцій необхідно побудувати одновимірні їх графіки.

2.2.4. Необхідно пам'ятати, що двовимірне табулювання логічних функцій здійснюється за двома незалежними змінними, конкретні назви яких задаються у самій задачі. У ній також задаються початкові та кінцеві їх значення, а також кроки табулювання. Після виконання двовимірного табулювання логічних функцій необхідно побудувати двовимірні їх графіки, бажано поверхні.

2.2.4. Звіт має містити такі розділи:

· бланк завдання з записаною на ньому умовою задачі;

· текст програми у режимі формул – для середовища Excel; тексти функцій – для середовища Visual Basic;

· результати відповідних розрахунків, виконані у середовищі Excel, які передба­ча­ються умовами задач;

· аналіз отриманих результатів, висновки і пропозиції.

2.3. Зразок виконання завдання

Задача 2.1. Задано x=2, y=4, z=-3. Відповідно до номера варіанту потрібно розробити програму для обчислення значень функцій a[x,y,z,b]іb[x,y,z] згідно з такими математичними виразами:

і

Текст програми у режимі формул – для середовища Excel

Клітина C16 à =1+(E15-C15)/G15+(E15-C15)^2/SIN(E15+0,1)+(E15-C15)^3/COS(C15)

Клітина F15 à =ЕСЛИ(C15<-0,5;F17;ЕСЛИ(И(C15>=-0,3;C15<0,3);F18;F19))

Клітина F16 à =ЕСЛИ(C15<-0,5;ABS(C15*E15+2*C21)^(1/3);ЕСЛИ(И(C15>=-0,3; C15<0,3); C21+C15^1/ФАКТР(1)+E15^2/ФАКТР(2);КОРЕНЬ(ABS(C21^2-C15*0,2)^0,3)))

Клітина С17 à =Fn_B2(C15;E15;G15)

Клітина F17 à =Fn_C2(C15;E15;G15;C23)

Тексти функцій – для середовища Visual Basic

Результати розрахунку, виконані у середовищі Excel

Задача 2.2. Задано x_п=-1,x_к=1,Dx=0.2, y=4.7, z=1.32. Відповідно до номера варіанту потрібно розробити програму для одновимірного табулювання логічних функцій c=f[x,y,z,b] і b=f[x,y,z] за незалежною змінною х згідно з математичними виразами, наведеними у задачі 2.1. Побудувати одновимірні графіки цих функцій.

Текст програми у режимі формул – для середовища Excel

Одновимірне табулювання функцій b=f[x,y,z]іc=f[x,y,z,b]

Клітина B26 à =C22

Клітина B27:B36 à =B26+$G$22 – <Ctrl + Enter>

Клітина C26:C36 à =Fn_B2(B26;$C$23;$E$23) – <Ctrl + Enter>

Клітина D26:D36 à =Fn_C2(B26;$C$23;$E$23;C26) – <Ctrl + Enter>

Клітина E26:E36 à =D26+C26 – <Ctrl + Enter>

Результати розрахунку, виконані у середовищі Excel

	B	C	D	E	F	G
	xп=	-1	хк=		Dx=	0,2
	y=		z=	1,32
	X	b[x,y,z]	c[x,y,z,b]	с+b
	-1,0	63,671	5,005	68,676
	-0,8	43,712	4,411	48,123
	-0,6	32,097	3,979	36,075
	-0,4	24,500	1,617	26,116
	-0,2	19,138	20,938	40,076
	0,0	15,149	17,149	32,298
	0,2	12,068	14,268	26,335
	0,4	9,625	1,403	11,028
	0,6	7,656	1,354	9,010
	0,8	6,058	1,305	7,362
	1,0	4,767	1,256	6,023

Задача 2.3. Задано x_п=-1,x_к=1,Dx=0.2, y_п=-2,y_к=2,Dy=0.2, z=1.32. Відповідно до номера варіанту потрібно розробити у програму для двовимірного табулювання логічних функцій c=f[x,y,z,b] і b=f[x,y,z] за незалежними змінними хізгідно з математичними виразами, наведеними у задачі 2.1. Побудувати двовимірні графіки цих функцій, бажано гістограми.

Текст програми у режимі формул – для середовища Excel

Одновимірне табулювання функції b=f[x,y,z]

Клітина B55 à =C50 – <Enter>

Клітини B56:B63 à =B55+$G$50 – <Ctrl + Enter>

Клітина C54 à =C51 – <Enter>

Клітини D54:M54 à =C54+$G$51 – <Ctrl + Enter>

Клітини C55:M63 à =Fn_B(C$54;$B55;$E$52) – <Ctrl + Enter>

Двовимірне табулювання функції c=f[x,y,z,b]

Клітина B67 à =C50

Клітини B68:B75 à =B67+$G$50 – <Ctrl + Enter>

Клітина C66 à =C51

Клітини D66:M66 à =C66+$G$51 – <Ctrl + Enter>

Клітини C67:M75 à =Fn_C2(C$66;$B67;$E$52;C55) – <Ctrl + Enter>

Результати розрахунку, виконані у середовищі Excel

2.4. Індивідуальні завдання

Задача 2.1. Задано x=0.34×№, y=0.18×№, z=-1.72×№(де № – но­мер ва­рі­ан­ту). Відповідно до номера варіанту потрібно розробити програму для обчислення значень функцій c[x,y,z,b] i b[x,y,z] згідно з такими математичними виразами:

1) ;

2) ;

3) ;

4) ;

5) ;

6) ;

7) ;

8) ;

9) ;

10) ;

11) ;

12) ;

13) ;

14) ;

15) .

16) ;

17) ;

18) ;

19) ;

20) ;

21) ;

22) ;

23) ;

24) ;

25) ;

26) ;

27) ;

28) ;

29) ;

30) .

Задача 2.2. Задано x_п=-1,x_к=1,Dx=0.2, y=4.2, z=1.33. Відповідно до номера варіанту потрібно розробити програму для одновимірного табулювання логічних функцій с=f[x,y,z,b] і b=f[x,y,x) за незалежною змінною х згідно з математичними виразами, наведеними в завданні 2.1. Побудувати одновимірні графіки цих функцій.

Задача 2.3. Задано x_п=-1,x_к=1,Dx=0.1, y_п=-2,y_к=2,Dy=0.2, z=1.33. Відповідно до номера варіанту потрібно розробити програму для двовимірного табулювання логічних функцій с=f[x,y,z,b] і b=f[x,y,z] за незалежними змінними хіу згідно з математичними виразами, наведеними в завданні 2.1. Побудувати двовимірні графіки цих функцій, бажано гістограми.

2.5. Контрольні запитання

1. Навести приклади використання функції сторінки ИСТИНА

2. Навести приклади використання функції сторінки ЛОЖЬ

3. Навести приклади використання функції сторінки ЕСЛИ