Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій, Оде-

са, 2004, 80 с.

2.Левин Александр. Самоучитель работы на компьютере. М: Нолидж, 1997. – 600 с.

3.Эрик Мэлони, Джошуа Носитер. Word 7 for Windows 95 (Серия «Без проблем!»): пер. с англ. – М.: БИНОМ;1996. – 400 с.

Теоретична частина

Приклад виконання

Намалюйте блок-схему рішення задачі, представлену на рисунку 4.1

				Початок
				Ввід даних: А, В, С
		Так						Ні
				А = 0
								D=B*B–4*A*C
					Ні
								D < 0
				X1 = (– B – sqrt (D) ) / (2*A)
										Так
				X2 = (– B + sqrt (D) ) / (2*A)
				Вивід: X1, X2
								Вивід: дійсних
	Вивід: рівняння
								кореней не
	не є квадратним							існує

Кінець Рис. 4.1 Блок-схема рішення задачі

50

1.Викличте на екран інструментальну панель Малювання, клацнувши на кнопці з відповідною назвою, розташованій на панелі інструментів Стандартна. Панель Малювання з'явиться в нижній частині вікна.

2.Встановіть курсор у вікні документа в позицію, де повинен починатися малюнок. Клацніть на панелі Малювання по кнопці Овал і при натиснутій лівій клавіші миші намалюйте початковий елемент блок-схеми. Відпустіть ліву клавішу миші, при цьому контур елементу залишиться виділеним. Клацніть правою клавішею миші усередині контуру, виділивши елемент. Виберіть в контекстному меню пункт Додати текст і введіть всередину елементу слово «Початок». Відформатуйте розмір шрифту і місцеположення слова усередині елементу, використовуючи ті ж прийоми, що і при роботі із звичним текстом. Відрегулюйте розмір овалу, виділивши його і перемістивши кутові або серединні мітки контуру.

3.Розкрийте список Автофігури і виберіть в ньому рядок Блок-схема. Виберіть елемент Дані і намалюйте паралелограм внизу під овалом. Викликавши контекстне меню, введіть в нього текст: «Введення даних: А,

В, С».

4.Клацніть на кнопці Лінія панелі Малювання і намалюйте, затиснувши клавішу <Shift>, вертикальну лінію, що сполучає раніше намальовані блоки. Відрегулюйте довжину і місцеположення лінії, виділивши її і перемістивши в потрібному напрямі саму лінію або її мітки.

5.У списку Автофігури виберіть рядок Блок-схема: рішення і під паралелограмом намалюйте ромб, що використовується для перевірки умови. За допомогою контекстного меню введіть умову, що перевіряється. З'єднайте намальовані блоки.

6.Намалюйте решту графічних елементів блок-схеми і введіть їх назви. Використовуйте прийом копіювання елементів блок-схеми, що повторюються, перетягуючи їх контур при натиснутій клавіші <Ctrl>. При малюванні блоків, що повторюються, не забувайте проводити в них заміну тексту.

7.З'єднайте всі намальовані блоки вертикальними і горизонтальними лініями, встановивши де треба Вид стрілки. Лінії, що виходять з умовних блоків, повинні мати написи: «Так» чи «ні». Для їх нанесення слід вибрати інструмент Напис на панелі Малювання. Для того, щоб навкруги введених слів не було видно рамку, треба по контуру елементу клацну-

51

ти правою клавішею мишки, в контекстному меню вибрати команду

Формат напису. і на вкладці Кольори і лінії вибрати Колір лінії: Немає ліній.

8.Об'єднайте всі елементи схеми в одну групу для спрощення надалі операцій по зміні розміру схеми і її місцеположення в тексті. Для цього послідовно виділяйте кожний елемент схеми при натиснутій клавіші <Shift>. Клацніть на кнопці Дії панелі Малювання. Виберіть із списку пункт Групувати. Згруповані фігури матимуть загальний контур і їх можна буде переміщувати і копіювати за одну операцію.

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

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

1.За допомогою якої панелі інструментів створюється блок-схема?

2.Як вивести на екран панель Малювання, якщо вона відсутня?

3.Де можна вибрати елементи блок-схеми?

4.Як можна скопіювати елементи блок-схеми без буфера обміну?

5.Як вставити текст в елемент блок-схеми?

6.Як згрупувати елементи блок-схеми в один малюнок?

7.Як вирівняти положення блок-схеми на сторінці?

8.Як намалювати лінію вертикально вверх, горизонтально, під “правиль-

ними кутом (30, 45, 60)?

9.Як намалювати квадрат, коло, сегмент кола?

10.Як змінити крок Сітки?

11.Як задати порядок розташування декількох фігур?

Варіанти індивідуальних завдань

Варіант 1. По наведеному прикладу виконання побудувати блок схему

52

Початок Ввід a,b,m I=1,m

Ввід x[i]

Ввід шапки таблиці

I=1,m

Y =(a*sqrt(x[i])-b*x[i])/(sqr(ln(x[i]))*ln(x[i]))

Вивід х,у

Кінець

Варіант 2. По наведеному прикладу виконання побудувати блок схему

Початок

Ввід a,b,xn,xk,dx

x=xn

Вивід шапки

X<=xk+dx/2

Y =(a*sqrt(x)-b*x)/(sqr(ln(x))*ln(x))

Вивід х,у

X=x+d

Кінець

Варіант 3. По наведеному прикладу виконання побудувати блок схему

53

Початок

Ввод x,y,z

x>=z

A=ln(x+z)/ln(10)		B=sqr(sin(y/2)/cos(y/2)
		)
Вивід х,у,z,а		Вивід х,у,z,b

Кінець

Варіант 4. По наведеному прикладу виконання побудувати блок схему

Початок

Ввід n

i=1

n

Ввід a[i]

k =0

нет

I=1..n

a[I] < 0

да

k =k+1

нет

да

k=0

Вивід k

Від’ємних

єлементів нема

кінець

54

Варіант 5. По наведеному прикладу виконання побудувати блок схему

Початок

Ввід a,b,m

h=(b-a)/m x=a

Вивід шапки

I=1..m+1

F1=sin(x)

F2=sqr(x)

Вивід F1, F2

X=x+h

Кінець

Варіант 6. По наведеному прикладу виконання побудувати блок схему

Початок

k=0

S=0

да

S=0

Вивід S

нет

К=к+1

S=S+k Кінець

55

Варіант 7. По наведеному прикладу виконання побудувати блок схему

Початок

Ввід n

i=1

n

Ввід a[i]

k =0

нет

I=1..n

a[I] = 0

да

k =k+1

нет

да

k=0

Вивід k

Нульових єле-

ментів нема

кінець

Варіант 8. По наведеному прикладу виконання побудувати блок cхему

Початок

Ввід a b m

h=(b-a)/m x=a

X<=b+h/2

F1=cos(x)

F2=sqrt(x)

Вивід F1,F2

X=x+h

Кінець

56

Варіант 9. По наведеному прикладу виконання побудувати блок схему

Початок Ввід с,b,m I=1,m

Ввід x[i]

Ввід шапки таблиці

I=1,m

Y =(с*sqr(x[i])-b*x[i])/(sqr(ln(x[i]))*ln(x[i]))

Вивід х,у

Кінець

Варіант 10. По наведеному прикладу виконання побудувати блок схему

Початок

k=0

S=0

S=0 да

нет

K=k+1

S=S+k

Вивід S

Кінець

57

Варіант 11. По наведеному прикладу виконання побудувати блок схему

	Початок
	Ввід n
	i=1	n
	Ввід a[i]
	k =0
	I=1..n
нет	a[I] > 0
		да
	Inc(k)
нет		да
	k=0

Вивід k	Додатних нема

кінець

Варіант 12. По наведеному прикладу виконання побудувати блок схему

			Початок
			Ввод x,y,z
			у>=z
A=ln(x+z)/ln(2)					B=sqr(sin(y/2)/cos(y/2)
					)
Вивід х,у,z,а					Вивід х,у,z,b

Кінець

58

Варіант 13. По наведеному прикладу виконання побудувати блок схему

Начало

Ввод a,b,m

I=1 m

Ввод x[i]

Ввод шапки

таблицы

I=1,m

Y =arctan(b)*x[i]/(1+exp(1/3*ln(a*x[i])))

Вывод х,у

Конец

Варіант 14. По наведеному прикладу виконання побудувати блок схему

Блок-схема функції

H(a,b,c)

Начало

+

-

C<>0

F:=(2*a-b-sin(с))/(5+abs(c)) F:=0

H:=f

Конец

59

Блок-схема основної програми

Начало

Ввод s,t

f:=
		+		+		H(2.2,t,s-t)
		h(t,-2*s,1.17)

Вывод f:7:1

Конец Варіант 15. По наведеному прикладу виконання побудувати блок схему

			Начало
							+
	-		x>0 and y>0
				F:=x*x+y*y
-			+
			X<=0 and y<=0
					F:=x+y*y

-

X<=0 and y>0

+

F:=x+y

H:=f

Конец

60

ТАБЛИЧНИЙ ПРОЦЕСОР EXCEL

Лабораторна робота 1. Настройка робочої книги

Мета завдання. Навчитися налаштовувати робочу книгу та панелі інструментів.

Постановка задачі. Налаштувати робочу книгу для подальшої роботи з нею.

Методичні вказівки по виконанню завдання Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій, Одеса, 2004, 80 с.

2.Рейзнер Т. Освой самостоятельно MS Excel 2000 за 24 часа. - М.: Изд.Дом "Вильямс", 2000 - 480 с.

3.Фултон Д. MS Excel 2000. - М.: Изд.Дом "Вильямс", 1999 - 223 с.

Теоретична частина

Microsoft Excel — програма для роботи з електронними таблицями, яка надає можливості розрахунків, графічні інструменти, та мову макропрограмування Visual Basic для додатків. На сьогодні Excel є одним з найпопулярніших табличних процесорів.

Робоча книга Microsoft Excel (див. рис. 1.1) є файл, якій вміщує один або декілька листів, які можна використовувати для розташування різноманітних відомостей та встановлення між ними необхідних зв’язків. Можна вводити та змінювати дані одночасно на декількох листах, а також виконувати обчислювання на основі даних із декількох листів. При побудові діаграми її можна помістити на лист з відповідними даними або на окремий лист діаграми.

Можна переміститися з одного листа на другий, обираючи внизу вікна книги відповідну вкладку. Щоб полегшити пошук потрібних вкладок, їх можна помітити різними кольорами. Вкладка активного листа підкреслюється вибраним кольором, вкладки неактивних листів закрашуються повністю.

Комірка є найменшою частиною електронної таблиці, та призначена для уводу та зберігання інформації. Кожна комірка може містити текст, число або формулу.

61

Адреса - це місце розташування комірки, яка складається з двох складових: буква (номер) стовпця та номер рядка, на пересіченні яких знаходиться дана комірка. Наприклад, на Рис. 1.1 адреса комірки, яка виділена, С4 на листі робочої книги Лист2.

Рис. 1.1 Інтерфейс стандартної робочої книги з листами.

Посилання вказує на комірку або діапазон комірок листа та передає в Microsoft Excel відомості про розташування значень або даних, які буде потрібно використати у формулі. Наприклад, посилання у формулі на виділену комірку на Рис. 1.1 буде називатися С4. За допомогою посилань можливо використовувати у одній формулі дані, які знаходяться у різних частинах листа, а також використовувати в декількох формулах значення одної комірки. Крім того, можна задавати посилання на комірки інших листів тієї ж книги (наприклад, Лист2!C4) та на інші книги. Посилання на комірки інших книг називаються зв’язками.

За умовчанням Microsoft Excel використовує стиль посилань A1, який визначає стовпці буквами (від A до IV, усього не більш 256 стовпців), а рядки номерами (от 1 до 65536). Ці букви та номера називаються заголовками рядків та стовпців. Для посилань на комірку введіть букву

62

стовпця, а услід номер строки. Наприклад, посилання С4 вказує на комірку, яка розташована на пересіченні стовпця С та рядка 4.

Настройки MS Excel.

Настройки - це зміна уже існуючих параметрів в MS Excel стандартними засобами, тобто за допомогою інтерфейсу MS Excel. Параметри, які встановлюються при інсталяції MS Excel, називаються "за умовчанням" і діляться, як мінімум на два типа: глобальні (Загальні) - це параметри, зміна яких приведе до зміни параметрів, у всіх робочих книгах, у тому числі і новостворюваних; локальні - це параметри, зміна яких приведе до зміни параметрів лише в тій робочій книзі, де вони були змінені.

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

1.Як запустити Excel?

2.Де можна знайти інформацію, що цікавить вас, по Microsoft Excel?

3.Як створити новий документ Microsoft Excel?

4.Як перейменувати робочий лист іншим ім'ям?

5.Як додати в документі ще один лист?

6.Як видалити непотрібний лист в документі?

Приклад виконання завдання

Завдання. Налаштувати панель інструментів. Побудувати електронну таблицю рівня знань учнів.

Ход виконання завдання.

1.Запустити Excel, при цьому з’явиться нова книга.

2.Налаштувати Стандартну панель.

−Правою кнопкою миші клацнути під головним меню, на пустому місці панелей, з’явиться контекстне меню панелей. Лівою кнопкою миші клацнути на Стандартна. Після цього Стандартна панель зникне. Повторити завдання, знов клацнувши на Стандартна, щоб панель з’явилася.

−Виконати команду Сервіс/Настройка, вкладка Команди. У категорії

вставка вибрати команду Вставка функції, затиснути на ній ліву

63

кнопку миші та протягнути до Стандартної панелі, відпустити кнопку між будь-якими кнопками Стандартної панелі. Натиснути кнопку ОК.

3.Налаштувати середу Excel для робочої книги.

−Виконати команду Сервіс/Параметри.

−У діалоговому вікні вибрати вкладку Загальні, та установити параметри:

Стиль посилань: R1C1 без прапорця. Листів у книзі - 5. Пам’ятати список з 5 файлів. Стандартній шрифт Times New Roman, розмір - 12. Указати робочій каталог, та ім’я користувача.

Утому ж діалоговому вікні вибрати вкладку Від, та установити прапорці параметрів: Відображати строку формул, вікна на панелі задач, Об’єкти - відображати. Параметри вікна - зняти прапорці з авторозбиття на сторінки, та формули, решта прапорців повинна бути установлена.

Утому ж діалоговому вікні вибрати вкладку Правка - зняти прапорець з Фіксований десятковий формат при введенні.

−Натиснути кнопку ОК.

4.Перейменувати робочий лист. Для цього клацнути правою кнопкою миші на Ліст1, у контекстному меню вибрати Перейменувати, ввести нове ім’я: Таблицярівня знань.

5.Зберегти робочу книгу: вибрати команду Файл/Зберегти як... У діалоговому вікні вибрати папку, вписати ім’я файлу, для типу файла вибрати у випадному списку Книга Excel.

Варіанти завдань

1.Налаштувати панель інструментів. Побудувати електронну таблицю продажу валюти.

2.Налаштувати панель інструментів. Побудувати електронну таблицю продажу авіаквитків.

3.Налаштувати панель інструментів. Побудувати електронну таблицю оплати комунальних послуг.

4.Налаштувати панель інструментів. Побудувати електронну таблицю зарплати викладачів.

5.Налаштувати панель інструментів. Побудувати електронну таблицю стипендії студентів.

При виконанні завдань користуватися вище розглянутим прикладом.

64

Лабораторна робота 2.1 Робота зі структурою таблиці та даними.

Мета завдання. Навчитися будувати та оформляти таблиці, вводити данні у таблицю.

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

Методичні вказівки по виконанню завдання Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій, Одеса, 2004, 80 с.

2.Рейзнер Т. Освой самостоятельно MS Excel 2000 за 24 часа. - М.: Изд.Дом "Вильямс", 2000 - 480 с.

3.Фултон Д. MS Excel 2000. - М.: Изд.Дом "Вильямс", 1999 - 223 с.

Теоретична частина Стандартні операції з блоками комірок

Виділення блоку суміжних комірок. Встановити курсор у комірку,

починаючи з якої виконується виділення. Натискувати ліву кнопку миші. Протягнути курсор, закрашуючи область виділення.

Виділення блоку несуміжних комірок. Виділити блок суміжних комірок. Виділити наступний блок суміжних комірок при натиснутій клавіші <Ctrl>.

Форматувати блок комірок. Виділити блок комірок. Виконати команду Формат/Комірки. Або використовувати команди контекстного меню.

Вилучити блок комірок (зміна структури таблиці). Виділити блок комірок. Команда Правка/Вилучити. Вказати об'єкт вилучення (рядки, стовпці або блок комірок із зрушенням комірок вліво або вгору). Або використовувати команди контекстного меню.

Вставити блок комірок (рядків, стовпців). Виділити блок комірок.

Виконати команду Вставка/Комірки (вказати зсув - управо або вниз, додавання рядків або стовпців). Або використовувати команди контекстного меню.

65

Копіювати блок комірок. Виділити блок комірок. Команда Правка/Копіювати. Встановити курсор в комірку, куди копіюється. Команда Правка/Вставити. Або використовувати команди контекстного меню.

Вставити блок комірок (заздалегідь виконана команда копіювання або вирізування). Встановити курсор в місце вставки. Команда Правка/Вставити. Або використовувати команди контекстного меню.

Вставити блок комірок з розмноженням (заздалегідь виконана команда копіювання або вирізування). Встановити курсор в місце вставки. Виділити блок, кратний вихідному блоку. Команда Правка/Вставити. Або використовувати команди контекстного меню.

Очистити блок. Виділити блок комірок. Виконати команду Правка/Очистити. Вказати об'єкт обробки: все, формати, вміст, примітка. Або використовувати команди контекстного меню.

Перенести виділений блок за допомогою миші. Виділити блок комірок. Встановити курсор миші на будь-яку лінію контуру. Натискувати ліву кнопку миші і, не відпускаючи її, перемістити блок на інше місце.

Скопіювати виділений блок за допомогою миші. Виділити блок комірок. Встановити курсор миші на будь-яку лінію контуру. Натискувати клавішу <Ctrl>. Натискувати ліву кнопку миші і, не відпускаючи її, перемістити блок в місце вставки. Відпустити клавішу <Ctrl>.

Заповнення блоку значеннями. Ввести значення в початок або в кінець блоку для тиражування. Виділити блок комірок (вниз, вгору, вправо або вліво від початковий комірки) для заповнення. Виконати команду Правка/Заповнити. Вибрати варіант заповнення: вниз, вгору, вправо, вліво, прогресія залежно від конфігурації блоку.

Заповнення блоку комірок при копіюванні за допомогою миші.

Ввести значення або формули в суміжні комірки. Виділити блок заповнених комірок. Встановити курсор в нижній правий кут блоку, добувшись появи чорного хрестика. Натискувати праву кнопку миші і протягнути курсор на необхідний розмір. Виконати команду контекстного меню Копіювати комірки.

Заповнення блоку комірок значеннями згідно прогресії. Ввести початкове значення прогресії в комірку. Виділити блок комірок для заповнення. Виконати команду Правка/Заповнити/Прогресія. Вказати типа і параметри прогресії.

66

Приховати блок рядків або стовпців. Встановити курсор на рядку з іменами стовпців або на стовпці з номерами рядків. Виділити блок рядків (стовпців). Виконати команду контекстного меню Приховати.

Показати блок прихованих рядків (стовпців). Якщо перший стовпець (рядок) є прихованим, виконаєте команду Правка/Перейти. У діалоговому вікні в полі Посилання введіть А1 і натискуйте кнопку ОК.

Виконаєте команду Формат/Стовпець або Рядок/Відображувати. Або виділити блок з двох стовпців (рядків), між якими знаходяться приховані стовпці (рядки), та використовувати команди контекстного меню.

Привласнення імені блоку комірок. Виділити блок комірок.

Команда Вставка/Ім'я/Привласнити. Вказати ім'я блоку комірок, що починається з букви.

Привласнення блоку комірок імені, що знаходиться в стовпці або рядку. Виділити блок комірок. Команда Вставка/Ім'я/Привласнити.

Вказати джерело імені: верхній або нижній рядок, лівий або правий стовпець.

Формат комірок таблиці

Комірки робочого листа мають заданий формат, який встановлюється командою Формат/Комірки або командою контекстного меню Формат комірок. По цій команді відкривається діалогове вікно з декількома вкладками:

•вкладка Число – вибирає категорії значень у комірці і завдання код формату, у тому числі створення призначених для користувача форматів;

•вкладка Вирівнювання – визначає спосіб вирівнювання значень комірки по горизонталі і вертикалі, орієнтацію тексту – горизонтально, вертикально, зверху вниз, від низу до верху, перенос по складах довгих рядків тексту усередині комірці;

•вкладка Шрифт – змінює шрифт, накреслення, розмір, колір, підкреслення та ефекти тексту у виділених комірках;

•вкладка Границя – створює рамки довкола комірки;

•вкладка Вид – дозволяє задати зафарбовування комірки (колір та

узор);

•вкладка Захист – управляє хованням формул та блокуванням комірок (заборона редагування даних комірок). Заздалегідь слід виконати захист на рівні листа або всієї книги за допомогою команди Сервіс/Захист.

67

Оформлення таблиць

Оформлення комірок таблиць можна здійснювати за допомогою команди Форматування/Комірки, вкладки Границя, Вид, а за допомогою вкладок Вирівнювання, Шрифт можна форматувати текст комірок таблиць. Також є можливість застосувати автоформатування таблиць використовуючи команду Форматування/Автоформат, та обравши будьякий формат таблиці з існуючих. За допомогою команди Формат/Стиль можна створити свій стиль форматування, для цього потрібно виконати команду Формат/Стиль, вибрати з списку Формат/Стиль та натиснути ОК.

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

1.Як за допомогою призначеного для користувача меню можна змінити висоту рядків і ширину стовпців?

2.Як виділити одну або декілька комірок за допомогою: комбінації клавіш; мишки?

3.Як відмінити останню дію?

4.Як скопіювати вміст груп комірок за допомогою: комбінації клавіш; мишки?

5.Як здійснити вирівнювання вмісту комірок?

6.Як змінити шрифт і розмір в певній групі комірок?

7.Як можна редагувати вміст комірки?

8.Як об'єднати комірки?

9.Як обмалювати таблицю та обробити її різними шрифтами і кольорами?

10.Як виділити стовпець або рядок, декілька стовпців або рядків?

Приклад виконання завдання.

Завдання. Побудувати таблицю рівня знань учнів за першу чверть та заповнити її прізвищами учнів. Понизу таблиці указати кількість 4 та 5 по предметам: фізика, математика, хімія, біологія, інформатика, також указати загальну кількість 4 та 5, максимальний та мінімальний загальний бал.

68

Нормований рівень знань учнів за першу чверть

	№			Прізвище, ініціали			Клас			Загальний бал			Пропуски
	з/п
1				Прізвище І.Б.
2				Прізвище І.Б.
3
4
5
6
7
8
9
10				Прізвище І.Б.

Кількість 4 та 5

Фізика

Математика

Хімія

Біологія

Інформатика

Загальна кількість 4 та 5 Максимальний загальний бал

Мінімальний загальний бал

Ход виконання завдання.

1.Запустити Excel. Виконати команду Файл/Відкрити. В діалоговому вікні встановити параметри:

Папка - ім’я вашого каталогу; Ім’я файлу: ім’я файлу з лабораторної роботи № 1;

2.Ввести текст з табл. 2.1 у вказані комірки наступним чином:

−Встановити покажчик миші в комірку, у яку буде уводитися текст, та увести текст з табл. 2.1, після уведення тексту у комірку натиснути клавішу Enter;

−Клацнути лівою кнопкою миші на наступну комірку з табл. 2.1 та знову увести текст і натиснути на Enter наприкінці і т.д.

69

Табл. 2.1. Вміст комірок, у яких розташовані назва таблиці та її заголовки.

Адреса	Текст у комірці
комірки
A1	Нормований рівень знань учнів за першу чверть
A3	№ з/п
B3	Прізвище, ініціали
C3	Клас
D3	Загальний бал
E3	Пропуски
A15	Кількість 4 та 5
A16	Фізика
A17	Математика
A18	Хімія
A19	Біологія
A20	Інформатика
A22	Загальна кількість 4 та 5
A23	Максимальний загальний бал
A24	Мінімальний загальний бал

3.Відформатувати комірки A1:E1, для цього:

−Виділити комірки з A1 по E1, клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−У контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Вирівнювання установити параметри Вирівнювання - по горизонталі: по центру; по вертикалі: по центру;

Відображення - напроти об’єднання комірок встановити прапорець

−У вкладці Шрифт установити параметри

Накреслення - напівжирне. Розмір - 14

Підкреслення - одинарне, по значенню

−Натиснути кнопку ОК.

4.Відформатувати заголовки таблиці, для цього

−виділити комірки з A3 по E3, клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−У контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Вирівнювання установити параметри

70

Вирівнювання - по горизонталі: по значенню; по вертикалі: по центру;

−У вкладці Шрифт установити параметри

Накреслення - напівжирне. Розмір - 12

Колір - темно-синій

−У вкладці Вид установити параметр Заливка комірок - Колір: голубий

−Натиснути кнопку ОК.

5.Установити обрамлення для таблиці, для цього

−виділити область з комірки A3 по комірку E13, клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−у контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Границя установити параметри Лінія - тип лінії: пунктирний. Клацнути у пункті Все на кнопці -

внутрішні. Знов у пункті Лінія установити тип лінії: подвійна лінія. Клацнути у пункті Все на кнопці - зовнішні.

−Натиснути кнопку ОК.

−Для установлення обрамлення заголовку таблиці виділити область з комірки A3 по комірку E3 клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−у контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Границя установити параметри Клацнути у пункті Окремі на нижню лінію у віконці, у пункті Лінія установити тип лінії: подвійна лінія.

−Натиснути кнопку ОК.

6.Об’єднати комірки з текстом Кількість 4 та 5 та змінити шрифт на курсив, для цього

−Виділити комірки з A15 по B15, клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−У контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Вирівнювання установити параметри Відображення - напроти об’єднання комірок встановити прапорець

−У вкладці Шрифт установити параметри

71

Накреслення - курсив.

−Натиснути кнопку ОК.

7.Також як у п.6 об’єднати комірки A та B для назв предметів Фізика, Математика, Хімія, Біологія та Інформатика. Після об’єднання установити підкреслення комірок A16-A20 з назвами наступним образом (на прикладі комірки A16)

−Клацнути на об'єднаній комірці A16 правою кнопкою миші для появи контекстного меню та вибрати пункт меню Формат комірок...

−У вкладці Шрифт установити параметр Підкреслювання - одинарне, по комірці.

−Натиснути кнопку ОК.

−Повторити дії для об’єднаних комірок A17-A20 для установи підкреслення комірок.

8.Відформатувати комірки з текстами Загальна кількість 4 та 5,

Максимальний загальний бал та Мінімальний загальний бал, для цього,

наприклад,

−Виділити комірки A22 та B22, визвати контекстне меню та вибрати пункт меню Формат комірок...

−У вкладці Вирівнювання установити параметри Відображення - напроти об’єднання комірок встановити прапорець

−У вкладці Шрифт установити параметри Накреслення - напівжирний курсив, .

−Натиснути кнопку ОК.

−Повторити дії для комірок A23, B23 та A24, B24.

9.У комірках B4 - B13 набрати прізвища та ініціали учнів.

10.Присвойте кожному учню номер за порядком почавши з першого. Для цього

−У комірці A4 набрати 1, у комірці A5 набрати 2.

−Виділити комірки А4 та А5

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

−Натиснути лівою кнопкою миші на чорному хрестику та протягнути його на 10 номерів і досягнувши 13-го рядка.

11.Установити необхідну ширину стовпців, для цього

72

−Підвести курсор між назвами стовпців B та C так, щоб у курсорі з’явився знак ↔ та натиснути ліву кнопку миші і розтягнути стовпець до потрібного розміру.

−Повторити дії для стовпців A, C, D, E, F.

12.Встановити орієнтацію сторінки, для цього.

− Вибрати команду Файл/Параметри сторінки

− У вкладці Сторінка встановити параметр Орієнтація - альбомна − Натиснути кнопку ОК.

13.Зберегти робочу книгу: вибрати команду Файл/Зберегти як... У діалоговому вікні вибрати папку, вписати ім’я файлу, для типу файла вибрати у випадному списку Книга Excel.

Варіанти завдань

1.Побудувати таблицю рівня продажу валюти банками та заповнити

їїназвами банків. Понизу таблиці указати кількість банків по кожної з валют (USD, Euro, GBP, CAD, JPY), у яких було продано більш ніж 1000 одиниць валюти, також указати загальну кількість банків, у яких було продано більш ніж 1000 одиниць валюти, максимальну та мінімальну продаж у гривнях.

Продаж валюти

№ з/п

назва банку

адреса

продаж у

веб-адреса

гривнах

Кількість банків, в яких продано > 1000 од. валюти

USD______________________________

Euro______________________________

73

GBP______________________________

CAD______________________________

JPY_______________________________

Загальна кількість банків, де продано валюти > 1000 од.

Максимальна продаж у грн. Мінімальна продаж у грн.

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

Продаж авіаквитків

№ з/п		Назва авіаліній	Адреса	Продаж у	Телефон
				грівнях	каси
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10

Кількість проданих квитків

Відень

Будапешт

Прага

Берлин

Варшава

Загальна кількість проданих квитків Максимальна кількість проданих квитків Мінімальна кількість проданих квитків

74

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

Оплата житла

№ з/п	Прізвище, ініціали	Адреса	Загальна сума	Пільги

1

2

3

4

5

6

7

8

9

10

Кількість комунальних послуг

Електрика Холодна вода Гаряча вода Газ Опалювання

Загальна кількість витраченої холодної та гарячої води

Максимальна оплата житла Мінімальна оплата житла

4. Побудувати таблицю зарплати викладачів та заповнити її прізвищами. Нижче таблиці указати кількість професорів, доцентів, асистентів, докторів наук, кандидатів наук, максимальну та мінімальну зарплати.

75

Зарплата викладачів

№

Прізвище, ініціали

Кафедра

Загальна

робочий

з/п

сума

телефон

1Прізвище1 І.Б.

2Прізвище2 І.Б.

3Прізвище3 І.Б.

4Прізвище4 І.Б.

5Прізвище5 І.Б.

6Прізвище6 І.Б.

7Прізвище7 І.Б.

8Прізвище8 І.Б.

9Прізвище9 І.Б.

10Прізвище10 І.Б.

Кількість

Професор

Доцент

Асистент Доктор наук Кандидат наук

Загальна кількість докторів та кандидатів наук

Максимальна зарплата Мінімальна зарплата

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

Стипендія студентів

№	Прізвище, ініціали	Група	Стипендія	Пропуски
з/п

1Прізвище1 І.Б.

2Прізвище2 І.Б.

3Прізвище3 І.Б.

76

4Прізвище4 І.Б.

5Прізвище5 І.Б.

6Прізвище6 І.Б.

7Прізвище7 І.Б.

8Прізвище8 І.Б.

9Прізвище9 І.Б.

10Прізвище10 І.Б.

Кількість 4 та 5

Фізика

Математика

Хімія

Фізкультура

Інформатика

Загальна кількість стипендіантів Максимальна кількість 4, 5 з предметів Мінімальна кількість 4, 5 з предметів

При виконанні завдань користуватися вище розглянутим прикладом.

Лабораторна робота 2.2. Робота з формулами та функціями в

Excel

Мета завдання. Навчитися використовувати формули та функції в

Excel.

Постановка задачі. Провести необхідні розрахунки, використовуючи формули і стандартні функції MS Excel.

Методичні вказівки по виконанню завдання Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій,

Одеса, 2004, 80 с.

2.Рейзнер Т. Освой самостоятельно MS Excel 2000 за 24 часа. - М.: Изд.Дом "Вильямс", 2000 - 480 с.

3.Фултон Д. MS Excel 2000. - М.: Изд.Дом "Вильямс", 1999 - 223 с.

77

Теоретична частина

Посилання в MS Excel

Відносні посилання. Відносне посилання у формулі, наприклад A1,

заснована на відносній позиції комірки, яка вміщує формулу, та комірку, на яку указує посилання. При зміні позиції комірки, яка вміщує формулу, змінюється і посилання. При копіюванні формули вздовж рядків та вздовж стовпців посилання автоматично коректується. За умовчуванням в нових формулах використовуються відносні посилання. Наприклад, при копіюванні відносного посилання з комірки B2 у комірку B3, воно автоматично змінюється з =A1 на =A2.

Абсолютні посилання. Абсолютне посилання комірки у формулі,

наприклад $A$1, завжди посилається на комірку, яка розташована у визначеному місці. При зміні позиції комірки, яка вміщує формулу, абсолютне посилання не змінюється. При копіюванні формули вздовж рядків та вздовж стовпців абсолютне посилання не коректується. За умовчуванням в нових формулах використовуються відносні посилання, і для використання абсолютних посилань треба вибирати відповідний параметр. Наприклад, при копіюванні абсолютного посилання з комірки B2 в комірку B3, вона остається колишньою =$A$1.

Змішані посилання. Змішане посилання вміщує або абсолютний стовпець та відносний рядок, або абсолютний рядок та відносний стовпець. Абсолютне посилання стовпців набуває вигляду $A1, $B1 і т. д. Абсолютне посилання рядка набуває вигляду A$1, B$1 і т. д. При зміні позиції комірки, яка вміщує формулу, відносне посилання змінюється, а абсолютне посилання не змінюється. При копіюванні формули уподовж рядків та уподовж стовпців відносне посилання автоматично коректується, а абсолютне посилання не коректується. Наприклад, при копіюванні змішаного посилання з комірки A2 у комірку B3, воно змінюється з =A$1

на =B$1.

Оператори

Операторами позначаються операції, які слід виконати над операндами формули. У Microsoft Excel включено чотири види операторів: арифметичні, текстові, оператори порівняння та оператори посилань.

Арифметичні оператори. Служать для виконання арифметичних операцій, таких як складання, віднімання, множення. Операції

78

виконуються над числами. Використовуються наступні арифметичні оператори: + (складання), – (віднімання), заперечування (–1), * (множення), / (ділення), % (відсоток), ^ (піднесення до ступеня).

Оператори порівняння. Використовуються для порівняння двох значень. Результатом порівняння є логічне значення: або ІСТИНА, або НЕПРАВДА. Оператори: порівняння =, > більше, < менше, >= більше або рівно, <= (знак менше або рівно), <> (не рівно).

Текстовий оператор конкатенації. Амперсанд (&)

використовується для об'єднання декількох текстових рядків в один рядок. Оператор посилання. Для опису посилань на діапазони комірок

використовуються наступні оператори:

: (двокрапка) ставиться між посиланнями на першу та останню комірку діапазону. Таке поєднання є посиланням на діапазон (B5:B15).

; (крапка з комою) - оператор об'єднання. Об'єднує декілька посилань в одне посилання (СУМ(B5:B15;D5:D15)).

(пробіл) - оператор пересічення множин, служить для посилання на спільні комірки двох діапазонів (B7:D7 C6:C8).

Формули

Формулами є вирази, по яких виконуються обчислення на сторінці. Формули обчислюють значення в певному порядку. Формула в MS Excel завжди починається із знаку рівності (=). Знак рівності свідчить про те, що подальші знаки складають формулу. Елементи, наступні за знаком рівності, є операндами, які відокремлюються операторами обчислень. Формула обчислюється зліва направо, відповідно до певного порядку для кожного оператора у формулі. Як операнди використовуються: числа; тексти (вводяться в подвійних лапках, наприклад, "Нез'явлення"); логічні значення (наприклад, ІСТИНА або умова типа А2=В4, і так далі); значення помилки; посилання - адреси комірок; вбудовані функції MS Excel. Нижче наведений приклад формули, що умножає 2 на 3 і що додає до результату

5:

=5+2*3

Формула також може містити наступні елементи: функції, посилання,

оператори, та константи.

Функції — це заздалегідь визначені формули, які виконують обчислення по заданих величинах, які звуться аргументами, та у

79

вказаному порядку. Ці функції дозволяють виконувати як прості, так і складні обчислення. Аргумент - значення, використовувані функцією для виконання операцій або обчислень. Тип аргументу, використовуваного функцією, залежить від конкретної функції. Зазвичай аргументи, використовувані функціями, є числами, текстом, засланнями на комірці та іменами. Іншими словами функція - стандартна формула, яка повертає результат виконання певних дій над значеннями, які виступають у якості аргументів. Функції дозволяють спростити формули у комірках листа, особливо якщо вони довгі або складні.

Оператор - це знак або символ, який задає тип обчислення у формулі. Існують математичні, логічні оператори, оператори порівняння і посилань.

Константа - це постійне (не обчислюємо) значення. Наприклад, число 210 та текст «Квартальна премія» є константами. Формула та результат обчислення формули константами не є.

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

Введення формул. Діалогове вікно Майстер функцій полегшує введення функцій при створенні формул, що містять функції. При введенні функції у формулу діалогове вікно Майстер функцій відображує ім'я функції, всі її аргументи, опис функції та кожного аргументу, поточний результат функції та всієї формули.

Щоб вставити формулу у комірку клацніть на ній курсором миші. Для того, щоб почати формулу з функції, натисніть кнопку Вставка функції на панелі формул. Потім виберіть функцію, яку потрібно використовувати. Можна ввести запит з описом операції, яку потрібно виконати, в полі Пошук функції (наприклад, по словах «складання чисел» буде знайдена функція СУММ), або вибрати категорію в полі або категорію. Введіть аргументи. Після закінчення введення формули натискуйте клавішу ENTER.

Вкладені функції. В деяких випадках може бути потрібно використання функції як одного з аргументів іншої функції. Наприклад, в наступній формулі функція СРЗНАЧ вкладена у функцію ЕСЛИ для порівняння середнього значення декількох значень з числом 50.

80

При переміщенні комірки з формулою посилання, що містяться у формулі, не змінюються. При копіюванні формули посилання на комірки можуть змінюватися залежно від їх типу.

Значення помилки в Excel. #ПУСТО!, #ДЕЛ/0!, #ЗНАЧ!, #ССЫЛ!,

#ИМЯ?, #ЧИСЛО!, #Н/Д, #Н/Д

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

1.Як здійснити звернення до значення комірки, розташованого на іншому листі?

2.Як змінити формат представлення чисел?

3.Який знак повинен передувати введенню формул?

4.Як підсумувати вміст групи комірок?

5.Як обчислити середнє значення вмісту комірок?

6.Як здійснити звернення до значення комірки, яка розташована на іншому листі?

7.У яких випадках необхідно використовувати логічну функцію “ЕСЛИ”?

8.Що таке абсолютна, відносна і змішана адреса комірки?

Приклад виконання завдання.

Завдання. Використовуючи робочу книгу з таблицею нормованого рівня знань учнів за першу чверть з лабораторної роботи 2 розрахувати нормований середній бал, кількість 4 та 5 по предметам: фізика, математика, хімія, біологія, інформатика, також загальну кількість 4 та 5, максимальний та мінімальний загальний бал. У стовпчику Примітки указати, хто відмінник (має усі 5-ки) і хто повинен пройти повторне навчання, якщо має 2-ку з будь-якого предмету. Для цього

1.Доповнити таблицю відповідними стовпчиками з предметами

2.Виставити оцінки по предметам

3.Над назвами предметів виставити коефіцієнти перерахунку.

4.У стовпчику з нормованим середнім балом ввести формулу, яка розраховує середній бал учня з урахуванням "ваги" предмета, тобто бал по предмету перемножується з його коефіцієнтом.

5.У нижній частині таблиці ввести формули для розрахунку загальної кількості 4 та 5, максимального та мінімального загального балу.

6.Порівняти результати з табл. 2.2

81

Нормований рівень знань учнів за першу чверть

						1,2	1,2	0,9	0,9	0,8
№		Прізвище, ініціали	Клас	Загальний	Пропуски
з/п				бал		Фізика	Математика	Хімія	Біологія	Інформатика	Примітки
1		Прізвище І.Б.		4,76		5	4	5	5	5
2		Прізвище І.Б.		4,00		4	4	4	4	4
3				3,40		3	4	3	3	4
4				4,30		5	5	3	4	4
5				4,52		4	4	5	5	5
6				4,82		5	5	4	5	5
7				4,84		5	5	5	5	4
8				5,00		5	5	5	5	5	відмінник
9				3,94		4	3	4	5	4	повторне
10		Прізвище І.Б.		2,82		3	3	2	3	3	навчання
Кількість 4 та 5
Фізика			8
Математика			8
Хімія			7
Біологія			8
Інформатика			9
Загальна кількість 4 та 5			40
Максимальний загальний
бал			5,00
Мінімальний загальний бал			2,82

82

Ход виконання завдання.

1.Запустити Excel. Виконати команду Файл/Відкрити. В діалоговому вікні встановити параметри:

Папка - ім’я вашого каталогу; Ім’я файлу: ім’я файлу з лабораторної роботи № 2;

2.Ввести в комірки з F3 по J3 назви предметів, у комірку K3 ввести

Примітки.

3.Ввести у комірки з F2 по J2 коефіцієнти предметів 1,2; 1,2; 0,9; 0,9; 0,8

4.Ввести для кожного учня відмітки 2, 3, 4, або 5 по предметам у комірки з

F4 по J13.

5.Розрахувати нормований середній бал для кожного учня. Для цього

−Перейти на комірку D4. Викликати майстер функцій: меню Вставка,

Функція...

−У діалоговому вікні вибрати параметри Категорія - Математичні

Обрати функцію - СУММ

−Натиснути кнопку ОК

−У діалоговому вікні для параметра Число 1 набрати F4*$F$2 для параметра Число 2 набрати G4*$G$2 і т.д., для параметра Число 5 набрати J4*$J$2 та натиснути кнопку OK

−Двічі клацнути на комірці D4 та поставити курсор в кінці виразу. Натиснути клавішу "/". У вікні з комірками виділених комірок відобразиться вікно з функціями, у якому нажавши на випадний список вибрати пункт Інші функції... У категорії Статистичні вибрати функцію СЧЕТ. Ввести для Значення1 діапазон комірок $F$2:$J$2

−Натиснути кнопку ОК

−Підвести курсор до правого нижнього кутка комірці D4 так, щоб курсор змінив вид на чорний хрестик, після чого натиснути на ліву кнопку миші та протягнути її до комірці D13.

−Якщо клацнути на комірку D4, формула повинна мати вид

=СУММ(F4*$F$2;G4*$G$2;H4*$H$2;I4*$I$2;J4*$J$2)/СЧЁТ($F$2:$J$2)

6. У стовпчику Примітки указати, хто відмінник (має усі 5-ки) і хто повинен пройти повторне навчання, якщо має 2-ку з будь-якого предмету. − У комірці К4 набрати формулу

=ЕСЛИ(И(F4=5;G4=5;H4=5;I4=5;J4=5);"відмінник";ЕСЛИ(ИЛИ(F4=2;G4=2;H4= 2;I4=2;J4=2);"повторне навчання";""))

83

−Підвести курсор до правого нижнього кутка комірці К4 так, щоб курсор змінив вид на чорний хрестик, після чого натиснути на ліву кнопку миші та протягнути її до комірці К13.

7.Дати імена блоків комірок по стовпцям з предметами. Наприклад, для стовпця F

−Виділити комірки з оцінками, тобто комірки з F4 по F13

−Виконати команду Вставка/Ім’я/Привласнити

−У діалоговому вікні у параметрі Ім’я ввести слово Фізика

−Клацнути на кнопку Додати

−Повторити дії для стовпців з рештою предметів, додавши імена Математика, Хімія, Біологія, Інформатика

8.Сховати стовпці з F по K

−Установити курсор на ім’я стовпця F та виділити стовпці по К включно.

−Виконати команду Формат/Стовпець/Скрити

9.Ввести формули підрахунку кількості 4-ок та 5-ок по предметам: фізика, математика, хімія, біологія, інформатика

−Перейти у комірку С16

−Виконати команду Вставка/Функція

−У діалоговому вікні вибрати категорію - Статистичні, функція - СЧЕТЕСЛИ. Клацнути на ОК

−У наступному діалоговому вікні встановити курсор у строку Діапазон,

та виконати команду Вставка/Ім’я/Вставити

−У діалоговому вікні, що з’явилося, виділити ім’я блока комірок Фізика та клацнути на ОК

−Повторити дії для підрахунку кількості решті предметів в комірках з С17 по С20

10.Підрахувати загальну кількість 4-ок та 5-ок

−Перейти на комірку С22 (напроти Загальна кількість 4 та 5)

−Клацнути по кнопці ∑

−Виділити комірки з С16 по С22, де розраховувалася кількість 4 та 5 по предметам

−Натиснути Enter

11.Знайти максимальний та мінімальній нормований середній бал

−Установити курсор у комірці С23, напроти Максимальний загальний бал

−Виконати команду Вставка/Функція...

84

−У діалоговому вікні вибрати категорію - Статистичні, функція - МАКС. Клацнути на ОК

−У наступному діалоговому вікні встановити курсор у строку Число та набрати D4:D13, блок комірок, який вміщує нормований середній бал для учнів

−Натиснути на клавішу ОК

−Повторити дії для знаходження мінімального загального балу у комірці С24, обрав функцію МИН

12. Виконати команду Файл/Зберегти.

Варіанти завдань

1. Використовуючи робочу книгу з таблицею продажу валюти з лабораторної роботи 2.1 розрахувати продаж у гривнях, кількість банків по кожної з валют, якої було продано більш ніж 1000 одиниць валюти, також загальну кількість банків, у яких було продано більш ніж 1000 одиниць валюти, максимальну та мінімальну продаж у гривнях. У стовпчику Примітки указати, яких банк є лідером з продаж усіх валют і який банк є аутсайдером по продажам усіх валют. Над назвами валют указати курс нацбанку. Назви стовпців: № з/п, назва банку, адреса, продаж у гривнах, веб-адреса, USD, Euro, GBP, CAD, JPY, Примітки. Продаж валюти у гривнах підраховується за формулою: ∑ai·bi, де ai – курс нацбанку, bi – кількість одиниць валюти.

2. Використовуючи робочу книгу з таблицею рівня продажу авіаквитків з лабораторної роботи 2.1 розрахувати продаж кожною касою у гривнях, кількість проданих квитків по кожному з напрямів (Відень, Будапешт, Прага, Берлин, Варшава), загальну кількість проданих квитків, максимальну та мінімальну кількість проданих квитків. У стовпчику Примітки указати, яка каса є лідером з продаж авіаквитків по усім напрямам і яка є аутсайдером по продажам усіх квитків. Над назвами напрямів указати ціну квитка. Назви стовпців: № з/п, назва авіаліній, адреса, продаж у гривнах, телефон каси, Відень, Будапешт, Прага, Берлин, Варшава, Примітки. Продаж квитків підраховується за формулою: ∑ai·bi, де ai – ціна квитка, bi – кількість квитків.

3. Використовуючи робочу книгу з таблицею оплати комунальних послуг з лабораторної роботи 2.1 розрахувати оплату комунальних послуг

85

кожним мешканцем, кількість витрат по комунальним послугам по кожної з них(електрика, холодна вода, гаряча вода, газ, опалювання), загальну кількість витраченої холодної та гарячої води, максимальну та мінімальну оплату житла. У стовпчику Примітки указати, який з мешканців здійснює найбільші витрати по усім послугам і який у кого найменші витрати по усім послугам. Над назвами послуг указати ціну послуги (1кВ=0,2436 грн., 1м3 хол.води=3,6 грн., 1м3 гор.води=18,06 грн., газ на 1 мешканця квартири=5,16 грн., 1 м3 отопления=6 грн.). Назви стовпців: № з/п, Прізвище, Адреса, Загальна сума, Пільги, електрика, холодна вода, гаряча вода, газ, опалювання, Примітки. Оплата комунальних послуг підраховується за формулою: ∑ai·bi, де ai – ціна послуги, bi – витрати.

4. Використовуючи робочу книгу з таблицею зарплати з лабораторної роботи 2.1 розрахувати зарплату викладача, кількість професорів, доцентів, асистентів, докторів наук та кандидатів наук, загальну кількість докторів та кандидатів наук, максимальну та мінімальну зарплату. У стовпчику Примітки указати, яки викладачі мають вчене звання та вчену ступень, та які не мають ані вченого звання ані ступеня. (Вважати, що викладач на посаді професора або доцента має вчене звання). Над назвами "професор" - "кандидат наук" указати коефіцієнти: 2; 1,5; 1; 1,2; 1,1. Назви стовпців: № з/п, Прізвище, Кафедра, Загальна сума, робочий телефон, професор, доцент, асистент, доктор наук, кандидат наук, Примітки. Зарплата викладачів підраховується за формулою: 1000∑ai·bi, де ai – коефіцієнт, bi – 1 або 0 в залежності від наявності.

5. Використовуючи робочу книгу з таблицею стипендії з лабораторної роботи 2.1 розрахувати стипендію (її отримають студенти без 3 та 2), кількість 4 та 5 по предметам: фізика, математика, хімія, інформатика, фізкультура, також загальну кількість стипендіатів, максимальну та мінімальну кількість 4 та 5 з предметів. У стовпчику Примітки указати, хто отримує підвищену стипендію (має усі 5-ки) і хто залишається без стипендії, якщо має 3-ку або 2-ку з будь-якого предмету. (Над назвами фізика, математика, хімія, інформатика, фізкультура указати коефіцієнти: 1,4; 1,4; 1,2; 1,1; 1). Назви стовпців: № з/п, Прізвище, Група, Стипендія, Пропуски, фізика, математика, хімія, інформатика, фізкультура, Примітки. Стипендія студентів підраховується за формулою: 20∑ai·bi, де ai – коефіцієнт, bi – оцінка.

При виконанні завдань користуватися вище розглянутим прикладом.

86

Лабораторна робота 3.1 Робота з діаграмами в Excel

Мета завдання. Навчитися будувати в Excel діаграми. Постановка задачі. Побудувати діаграму, упроваджену на лист з

вихідними даними, і діаграму на окремому листі.

Методичні вказівки по виконанню завдання Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій,

Одеса, 2004, 80 с.

2.Рейзнер Т. Освой самостоятельно MS Excel 2000 за 24 часа. - М.: Изд.Дом "Вильямс", 2000 - 480 с.

3.Фултон Д. MS Excel 2000. - М.: Изд.Дом "Вильямс", 1999 - 223 с.

Теоретична частина

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

Діаграму можна створити на окремому листі або помістити як упроваджений об'єкт на лист з даними. Крім того, діаграму можна опублікувати на веб-сторінці. Щоб створити діаграму, необхідно спочатку ввести для неї дані на листі. Після цього, виділивши ці дані, слід скористатися майстром діаграм (кнопка ) для покрокового створення діаграми, при якому вибираються її тип і різні параметри. Або використовуйте для створення основної діаграми панель інструментів Діаграма, яку згодом можна буде змінити.

Діаграма зв'язана з даними, на основі яких вона створена, таі оновлюється автоматично при зміні даних. На Рис. 3.1 зображено приклад діаграми, де цифри позначають наступне:

1. Маркер даних

2. Основна лінія

3. Імена категорій

4. Імена рядів діаграми даних

Маркер даних. Кожен маркер

відповідає одному значенню даних листа. Маркери даних одного кольору Рис. 3.1 Приклад діаграми.

87

представляють один ряд даних. У наведеному прикладі найправіший маркер даних відповідає дійсному значенню за другий квартал, рівному 99.

Основні лінії. Microsoft Excel створює значення на осі з даних робочого листа. Звернете увагу на те, що в наведеному прикладі значення на осі змінюються від 0 до 120, що відповідає значенням комірок діапазону на листі. Основні лінії позначають основні інтервали на осі. На діаграмі можна відображувати і проміжні лінії, що позначають інтервали усередині основних інтервалів.

Імена категорій. Excel використовує заголовки стовпців або рядків даних як імена рядів даних. У наведеному прикладі як імена рядів першого і другого кварталу листа виступають імена осі категорій.

Імена рядів даних діаграми. Excel використовує заголовки стовпців або рядків даних як імена рядів даних. Імена рядів відображуються в легенді діаграми. У наведеному прикладі як імена рядів виступають заголовки стовпців планованих і дійсних значень.

Упроваджені діаграми і листи діаграм

Діаграму можна створити на окремому листі або помістити як упроваджений об'єкт на лист з даними. Незалежно від способу розміщення діаграма є пов'язаною з вихідними даними на листі, тобто при оновленні даних

оновлюється і створена на їх основі діаграма.

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

діаграм разом з даними листа.

Листи діаграм. Лист діаграми — це окремий лист в книзі, що має власне ім'я. Листи діаграм слід використовувати у випадках, коли потрібно проглянути або змінити великі або складні діаграми окремо від даних, або коли потрібно зберегти простір екрану для роботи з листом.

88

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

1.Назвіть типів стандартних діаграм.

2.Як створити діаграму?

3.Як вибрати тип діаграми?

4.Як додати назву діаграми?

5.Як змінити написи на осях?

6.Як виділити елементи діаграми?

Приклад виконання завдання.

Завдання. Для таблиці з лабораторної роботи 2.2 побудувати діаграми оцінок учнів по предметам (гістограму на окремому листі та лінійчату діаграму, яка упроваджена в лист з таблицею). Для цього необхідно виконати наступні дії

1.Скопіювати таблицю на другий лист. Перейменувати цій лист.

2.Побудувати діаграму на окремому листі так, як показано на

рис. 3.2

3.Побудувати упроваджену діаграму.

			Нормований рівень знань учнів
6
5
4
									Фізика
Бал									Математика
									Хімія
3
									Біологія
									Інформатика
2
1
0
Прізвище1	Прізвище2	Прізвище3	Прізвище4	Прізвище5	Прізвище6	Прізвище7	Прізвище8	Прізвище9	Прізвище10
І.Б.	І.Б.	І.Б.	І.Б.	І.Б.	І.Б.	І.Б.	І.Б.	І.Б.	І.Б.
				Прізвище
Рис. 3.2 Діаграма рівня знань учнів.

89

Ход виконання завдання.

1.Відкрити файл лабораторної роботи 2.2

−Виконати команду Файл/Відкрити

−Вибрати у діалоговому вікні потрібну папку та документ.

2.Побудувати копію листа з таблицею, для цього

−Клацнути правою кнопкою миші на імені листа. У контекстному меню клацнути лівою кнопкою миші на пункт Перемістити/Скопіювати...

−У діалоговому меню

Упункті Перед листом клацнути на Лист 2

Поставити прапорець у параметри Побудувати копію. Натиснути клавішу ОК

3.Перейменувати скопійований робочий лист. Для цього клацнути правою кнопкою миші на <ім’я вашого скопійованого листа > (2), у контекстному меню вибрати Перейменувати, ввести нове ім’я - Таблиця для діаграм.

4.Побудувати діаграму на окремому листі, для цього

−Виконати Вставка/Діаграма...

−На вкладці Стандартні вибрати тип діаграми - Гістограма, від - Звичайна гістограма (перша)

−Клацнути на кнопку Далі

−На вкладці Діапазон даних встановити перемикач на ряди в стовпцях

−Клацнути на строку Діапазон

−Виділити на таблиці діапазон з B3 по В13, натиснути клавішу Ctrl і не відпускаючи її виділити діапазон з F3 по J13

−Відкрити вкладку Ряд. В вікні Ряд повинні з’явитися назви предметів.

−Клацнути на кнопку Далі

−У вкладці Заголовки ввести назву діаграми - Нормований рівень знань учнів Вісь Х - Прізвище

Вісь Y - Бал Клацнути Далі

−Вибрати у меню розташування діаграми на окремому листі. Клацнути на строку з текстом назви листа - Діаграма 1, та змінити її на

Гістограма рівня знань.

−Клацнути на кнопку Готово

5.Змінити фон діаграми

90

−Клацнути правою кнопкою миші в області побудови діаграми (на фоні діаграми, між стовпчиками)

−В меню клацнути на Формат області побудови...

−У пункті Заливка встановити перемикач на Звичайна

−Натиснути кнопку ОК

6.Порівняти результат з Рис.3.2.

7.Побудувати лінійчату діаграму, яка упроваджена в існуючий лист. Для цього повторити дії пунктів 4 - 5. При проходженні пункту 4 вибрати тип діаграми - лінійчата, вид - перший, та вибрати у меню розташування діаграми - на існуючому листі.

8.Зберегти завдання.

Варіанти завдань

1.Для таблиці з лабораторної роботи 2.2 побудувати діаграми продажу валюти банками по найменуванням валюти (гістограму на окремому листі та лінійчату діаграму, яка упроваджена в лист з таблицею).

2.Для таблиці з лабораторної роботи 2.2 побудувати діаграми продажу авіаквитків касами по напрямах польотів (гістограму на окремому листі та лінійчату діаграму, яка упроваджена в лист з таблицею).

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

4.Для таблиці з лабораторної роботи 2.2 побудувати діаграми зарплати викладачів по посадах та вчених звань (гістограму на окремому листі та лінійчату діаграму, яка упроваджена в лист з таблицею).

5.Для таблиці з лабораторної роботи 2.2 побудувати діаграми стипендії студентів по успішності з предметах (гістограму на окремому листі та лінійчату діаграму, яка упроваджена в лист з таблицею).

При виконанні завдань користуватися вище розглянутим прикладом.

Лабораторна робота 3.2 Редагування діаграм в Excel

Мета завдання. Навчитися редагувати діаграми в Excel. Постановка задачі. Відредагувати побудовану в попередній

лабораторній роботі діаграму.

91

Методичні вказівки по виконанню завдання

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

1.Як вилучити елемент діаграми?

2.Як змінити розмір елементів діаграми?

3.Як вставити в діаграму нові дані?

4.Які змінити назву осей та підписів діаграми?

Приклад виконання завдання.

Завдання. Відредагувати діаграму, яка побудована на окремому листі, з попереднього завдання, представив її у об’ємному виді, додав стовпець з загальним балом, та змінив параметри діаграми (назви осей, та орієнтацію підписів осей). Для цього

1.Скопіювати лист з діаграмою.

2.Змінить формат на об’ємний

3.Додати у діаграму стовпець з загальним балом

4.Змінити параметри діаграми (назви осей, та орієнтацію підписів осей)

5.Порівняти з Рис. 3.3

Нормований рівень знань учнів

5,00

4,50

4,00

3,50

3,00

Бал

2,50

2,00

1,50

1,00

0,50

0,00

.

Б .

І.

Б .

1

І.

Б .

е

2

І.

.Б .

щ

е

3

и

щ

І

Б

.

в

е

4

І.

із

и

щ

е

5

.Б .

в

и

щ

І

.Б .

із

в

е

6

Пр

и

щ

І

.Б .

Пр

із

в

е

7

р

із

и

щ

І

.Б .

П

р

з

и

щ

8

І

.Б

П

і

в

е

9

з

и

щ

І

Пр

і

в

е

0

р

з

и

щ

П

і

в

1

з

и

е

Пр

і

в

щ

з

Пр

і

и

Пр

в

Прізвище

із

Пр

ка

ти

ма

р

о

ф

н

гія

І

о

л

іо

Б

ія

ка

ім

ти

Х

а

Предмет

атем

М

л

ка

ба

и

із

й

Ф

и

н

ь

л

га

а

З

Рис. 3.3 Об’ємна діаграма рівня знань учнів.

Загальний бал

Фізика

Математика

Хімія

Біологія Інформатика

92

Ход виконання завдання.

1.Відкрити попереднє завдання.

2.Скопіювати лист з окремою діаграмою

−Клацнути правою кнопкою миші на імені листа (Гістограма рівня знань). У контекстному меню клацнути лівою кнопкою миші на пункт

Перемістити/Скопіювати...

−У діалоговому меню поставити прапорець у параметри Побудувати копію.

−Натиснути клавішу ОК

3.Перейменувати скопійований лист з діаграмою. Для цього клацнути правою кнопкою миші на <ім’я вашого скопійованого листа > (2), у контекстному меню вибрати Перейменувати, ввести нове ім’я - Об’ємна гістограма.

4.Змінить формат на об’ємний, для цього

−Клацнути правою кнопкою миші в області побудови діаграми.

−Клацнути правою клавішею миші та в контекстному меню обрати Тип

діаграми...

−У вкладці Стандартні, у пункті Вид обрати об’ємну гістограму

−Натиснути кнопку ОК

5.Додати у діаграму стовпець з загальним балом, для цього

−Клацнути правою кнопкою миші в області побудови діаграми.

−Клацнути правою клавішею миші та в контекстному меню обрати

Початкові дані...

Увкладці діапазон даних змінити діапазон, додавши стовпець з загальним балом: натиснути клавішу Ctrl та не відпускаючи її виділити діапазон з D3 по D13.

Натиснути на клавішу ОК.

6.Змінити параметри діаграми

−Клацнути правою кнопкою миші в області побудови діаграми та в контекстному меню обрати Параметри діаграми...

Увкладці Заголовки додати

Ось Y - Предмет Натиснути клавішу ОК

93

−Клацнути правою кнопкою миші на осі ряду даних (Предмет) та викликати меню Формат осі...

Увкладці Вирівнювання у параметра Орієнтація установити 20 градусів.

Натиснути клавішу ОК.

−Клацнути правою кнопкою миші на осі категорій (Прізвище) та викликати меню Формат осі...

Увкладці вирівнювання у параметра Орієнтація установити 30 градусів.

Натиснути клавішу ОК.

7.Порівняти результат з Рис. 3.3.

Варіанти завдань

1.Відредагувати діаграму, яка побудована на окремому листі, з попереднього завдання, представив її у об’ємному виді, додав стовпець з продажу валюти у гривнах (по банкам), та змінив параметри діаграми (назви осей, та орієнтацію підписів осей).

2.Відредагувати діаграму, яка побудована на окремому листі, з попереднього завдання, представив її у об’ємному виді, додав стовпець з продажу квитків у гривнах (по авіалініям), та змінив параметри діаграми (назви осей, та орієнтацію підписів осей).

3.Відредагувати діаграму, яка побудована на окремому листі, з попереднього завдання, представив її у об’ємному виді, додав стовпець з загальної суми (по фаміліям), та змінив параметри діаграми (назви осей, та орієнтацію підписів осей).

4.Відредагувати діаграму, яка побудована на окремому листі, з попереднього завдання, представив її у об’ємному виді, додав стовпець з загальної суми (по фаміліям), та змінив параметри діаграми (назви осей, та орієнтацію підписів осей).

5.Відредагувати діаграму, яка побудована на окремому листі, з попереднього завдання, представив її у об’ємному виді, додав стовпець з стипендії (по фаміліям), та змінив параметри діаграми (назви осей, та орієнтацію підписів осей).

При виконанні завдань користуватися вище розглянутим прикладом.

94

Лабораторна робота 3.3 Форматування діаграм в Excel

Мета завдання. Навчитися форматувати діаграми в Excel. Постановка задачі. Відформатувати діаграму з попередній

лабораторній роботі.

Методичні вказівки по виконанню завдання

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

1.Як додати легенду та провести її форматування?

2.Як змінити колір діаграми?

3.Як прибрати/задати сітку в області побудови діаграми?

Приклад виконання завдання.

Завдання. Відформувати об’ємну діаграму з попереднього завдання. Для цього

1.Скопіювати лист з діаграмою.

2.Змінить настройку об’ємного виду діаграми.

3.Змінити діапазон даних: ряди повинні бути в строках.

4.Провести формат області побудови.

5.Провести формат області діаграми

6.Провести форматування Осей

7.Провести форматування заголовків діаграми та осей

8.Провести форматування рядів даних

9.Провести форматування сітки, стін та основи

10.Провести форматування легенди

11.Порівняти з Рис. 3.4

95

Рис. 3.4 Відформатована об’ємна діаграма рівня знань учнів.

Ход виконання завдання.

1.Відкрити попереднє завдання.

2.Скопіювати лист з окремою діаграмою

−Клацнути правою кнопкою миші на імені листа (Гістограма рівня знань). У контекстному меню клацнути лівою кнопкою миші на пункт

Перемістити/Скопіювати...

−У діалоговому меню поставити прапорець у параметри Побудувати копію.

−Натиснути клавішу ОК

3.Перейменувати скопійований лист з діаграмою. Для цього клацнути правою кнопкою миші на <ім’я вашого скопійованого листа > (2), у контекстному меню вибрати Перейменувати, ввести нове ім’я - Змінена об’ємна гістограма.

4.Змінити діапазон даних.

−Клацнути правою кнопкою миші в області побудови діаграми та в контекстному меню обрати Початкові дані

96

−На вкладці Діапазон даних установити перемикач Ряди в стовпцях.

5.Змінити настройку об’ємного виду діаграми

−Клацнути правою кнопкою миші в області побудови діаграми та в контекстному меню обрати Об’ємний вид...

−У діалоговому вікні установити параметри

Підіймання - 30 Поворот - 40

Натиснути клавішу ОК

6.Провести формат області побудови.

−Клацнути правою кнопкою миші в області побудови діаграми та в контекстному меню обрати Формат області побудови...

−У вкладці Від установити параметри Рамка - невидима

Заливка - рожева Клацнути на кнопку Способи заливки... у вкладці Градієнта

установити перемикач типа штрихування - діагональна 1, варіант - 1.

Натиснути на клавішу ОК Натиснути на клавішу ОК

7.Провести формат області діаграми.

−Клацнути правою кнопкою миші в області діаграми (поле зовні області побудови, яка залита градієнтною заливкою) та в контекстному меню обрати Формат області діаграми...

−У вкладці Вид установити параметри Рамка - звичайна Установити прапорець - з тінню Заливка - світло-помаранчева

−У вкладці Шрифт установити параметри

Шрифт - Courier

Розмір - 10 Колір - синій

Натиснути клавішу ОК

8.Провести форматування Осей

−Підвести курсор на вісь категорій (з прізвищами) та клацнути на неї правою кнопкою миші і вибрати Формат осі...

Установити параметри на вкладці Шкала Число категорій між підписами ділень - 1

97

Число категорій між діленнями - 1

Установити параметри на вкладці Шрифт Розмір - 12

Натиснути на клавішу ОК

−Підвести курсор на вісь ряду даних (з назвами предметів) та клацнути на неї правою кнопкою миші і вибрати Формат осі...

Установити параметри на вкладці Шкала Число категорій між підписами ділень - 1 Число категорій між діленнями - 1

Установити параметри на вкладці Шрифт Розмір - 12

Установити параметри на вкладці Вирівнювання Орієнтація - 5

Натиснути на клавішу ОК

−Підвести курсор на вісь значень (бал) та клацнути на неї правою кнопкою миші і вибрати Формат осі...

Установити параметри на вкладці Шкала Ціна основних ділень - 1

Установити параметри на вкладці Шрифт Розмір - 12

Натиснути на клавішу ОК

9.Провести форматування заголовків діаграми та осів

−Підвести курсор до заголовку діаграми та клацнути правою кнопкою миші і вибрати Формат заголовка діаграми...

Увкладці Шрифт установити параметри

Накреслення - Напівжирний Розмір - 14

Колір - Красний Натиснути на клавішу ОК

−Підводити курсор до кожного з заголовків осів та клацати правою кнопкою миші і вибирати Формат назви осі...

Увкладці Шрифт установити параметри

Накреслення - Напівжирний Розмір - 12

− Натиснути на клавішу ОК 10. Провести форматування рядів даних

98

−Установити покажчик миші на ряді 1 (Загальний бал) та клацнути правою кнопкою миші.

−В меню Формат рядів даних установити параметри На вкладці Вид установити Заливка - красного кольору На вкладці Фігура установити фігуру номер 5.

На вкладці Підписи даних установити прапорець - Включити в підписи

значення На вкладці Порядок рядів Загальний бал перевести вниз, після

останнього ряду.

На вкладці Параметри установити глибину зазору - 200, ширину зазору - 200, глибину діаграми - 200

Натиснути на клавішу ОК 10. Провести форматування сітки, стін та основи

−Клацнути правою кнопкою миші в області діаграми та в контекстному меню обрати Параметри діаграми...

−У вкладці Лінії сітки установити параметри Прапорці напроти основних ліній осів X, Y, Z

−Натиснути клавішу ОК

−Установити покажчик у області Стінки та клацнути правою кнопкою миші. У контекстному меню обрати Формат стінок... У вкладці Вид установити заливку світло-бузкового кольору. Натиснути клавішу ОК.

−Установити покажчик у області Основи та клацнути правою кнопкою миші. У контекстному меню обрати Формат основи... У вкладці Вид установити заливку світло-бузкового кольору. Натиснути клавішу ОК.

11. Провести форматування легенди

−Клацнути правою кнопкою миші на вікно легенди та обрати Формат

легенди...

−Установити параметри

Увкладці Вид - Заливка рожева

Увкладці Розміщення - Розміщення зліва посередині

−Натиснути клавішу ОК.

12. Порівняти результат з Рис. 3.4.

99

Варіанти завдань

Для всіх варіантів з попередньої лабораторної роботи відформатувати об'ємну діаграму попереднього завдання.

При виконанні завдань користуватися вище розглянутим прикладом.

Лабораторна робота 4. Робота з даними в Excel: сортування та форматування

Мета завдання. Навчитися сортуванню та умовному форматуванню даних в таблиці в Excel.

Постановка задачі. Відсортувати таблицю та виконати умовне форматування.

Методичні вказівки по виконанню завдання Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій,

Одеса, 2004, 80 с.

2.Рейзнер Т. Освой самостоятельно MS Excel 2000 за 24 часа. - М.: Изд.Дом "Вильямс", 2000 - 480 с.

3.Фултон Д. MS Excel 2000. - М.: Изд.Дом "Вильямс", 1999 - 223 с.

Теоретична частина Сортування списків

Сортування рядків по стовпцях. В більшості випадків потрібно відсортувати рядки. Дана процедура описує сортування стовпців:

−Вкажіть комірку в списку, який будите сортувати.

−У меню Дані виберіть команду Сортування.

−Натискуйте кнопку Параметри.

−Встановите перемикач Сортувати в положення Стовпці діапазону і натискуйте кнопку OK.

−Виберіть рядки, по яких потрібно відсортувати стовпці, в полях

Сортувати по і Потім по.

Сортування рядків за збільшенням (від A до Я або від 0 до 9) або убуванню (від Я до A або від 9 до 0):

− Вкажіть комірку в сортованому списку.

100

−Натискуйте кнопку Сортувати за зростанням або Сортувати по убуванню .

Сортування рядків по двох або трьом стовпцям. Краще всього,

якщо сортований список матиме заголовки стовпців:

−Виділите комірку в списку, який потрібно відсортувати.

−У меню Дані виберіть команду Сортування.

−Вкажіть стовпці сортування в полях Сортувати по і Потім по.

−Виберіть решту параметрів сортування і натискуйте кнопку OK.

Умовне форматування

Умовний формат. Формат (наприклад, узор комірки або колір шрифту), який Microsoft Excel автоматично застосовує до комірки, якщо виконується вказана умова.).

Додавання, зміна і вилучення умовних форматів:

−Виберіть комірку, для якої потрібно додати, змінити або вилучити умовне форматування.

−В меню Формат виберіть команду Умовне форматування.

−Виконайте одну з дій, перерахованих нижче: додавання умовного формату, копіювання форматів в інші комірки або зміна або вилучення умовного формату.

Додавання умовного формату.

1. Виконайте одну з наступних дій.

Щоб як умову форматування використовувати значення виділених комірок, виберіть параметр значення, виберіть операцію порівняння, а потім введіть задане значення або формулу. Перед формулою потрібно поставити знак порівняння (=).

Для використання формули як критерій форматування (для оцінки даних або умов, відмінних від значень виділених комірок), виберіть параметр Формула, а потім введіть формулу, що набуває логічного значення ІСТИНА або НЕПРАВДА.

2. Натисніть кнопку Формат.

101

3.Виберіть тип форматування, який потрібно застосувати, коли значення комірки відповідає умові або формула повертає значення ІСТИНА.

4.Для додавання умови натисніть кнопку А також, а потім повторите кроки 1-3.

Може бути задане до трьох умов. Якщо жодна із заданих умов не

набуває дійсного значення, формат комірок залишається тим самим (попереднім).

Копіювання форматів в інші комірки. Виділите комірки, що містять умовний формат, який треба копіювати. Натискуйте кнопку Формат за зразком на панелі інструментів Стандартна , а потім виділите комірки, які повинні мати той же умовний формат.

Зміна або вилучення умовного формату. Виконаєте одне або декілька з вказаних нижче дій:

−Для зміни формату натисніть кнопку Формат для кожної умови, яку необхідно змінити.

−Для повторного вибору форматів на поточній вкладці діалогового вікна Формат комірок натисніть кнопку Очистити.

−Для вилучення одного або декількох умов натисніть кнопку Вилучити, а потім встановите прапорці для тих умов, які необхідно вилучити.

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

1.Як відсортувати дані таблиці по рядках/стовпцях?

2.Як виконати умовне форматування?

3.Як можна ідентифікувати поля сортованого діапазону?

4.Який порядок сортування за умовчанням?

Приклад виконання завдання.

Завдання. Відсортувати таблицю по трьом критеріям (по загальному балу та класу за убуванням і по прізвищу за зростанням) та задати умовне форматування (відмітки з загальним балом менш 3 виділити синім курсивом, відмітки з загальним балом менш 4 виділити зеленим кольором,

102

відмітки з загальним балом більш або рівними 4 виділити красним жирним; кількість пропусків більш або рівних 5 виділити синім та підкреслити).

1.Побудувати таблицю.

2.Відформувати таблицю.

3.Скопіювати таблицю на другий лист

4.Відсортувати її по трьом рівням.

5.Виконати умовне форматування

6.Порівняти з початковою таблицею 4.1.

Табл. 4.1 Початкова таблиця рівня знань учнів за першу чверть.

Хід виконання завдання.

1.Запустити Excel, при цьому з’явиться нова книга.

2.Перейменувати Лист 1 на Таблиця для сортування

3.Побудувати таблицю 2.3

4.Відформувати назву таблиці наступним чином

−Виділить діапазон комірок з A1 по F1 та клацнути на виділених комірках правою кнопкою миші. У контекстному меню клацнути лівою кнопкою миші на пункті Формат комірок...

−У вкладці Вирівнювання установити параметри Вирівнювання - по горизонталі: по центру;

103

Відображення - напроти об’єднання комірок встановити прапорець

−У вкладці Шрифт установити параметри

Накреслення - напівжирне. Розмір - 14

−Натиснути кнопку ОК.

5.Відформатувати заголовки таблиці, для цього

−виділити комірки з A3 по F3, клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−У контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Вирівнювання установити параметри

Вирівнювання - по горизонталі: по центру; по вертикалі: по центру; Відображення - напроти переносити по словам встановити прапорець

−У вкладці Шрифт установити параметри Розмір - 12

−У вкладці Границя клацнути у пункти Усі на кнопці зовнішні, та на кнопці внутрішні.

−Натиснути кнопку ОК.

6.Установити обрамлення для таблиці, для цього

−виділити область з комірки A4 по комірку A13, клацнути на виділений області правою кнопкою миші для появи контекстного меню;

−у контекстному меню клацнути лівою кнопкою миші на пункт меню

Формат комірок...

−У вкладці Границя установити параметри Клацнути у пункті Усі на кнопці - зовнішні.

−Натиснути кнопку ОК.

−Повторити дії для усіх стовпців зі значеннями - B, C ,D, E, F.

7.Скопіювати таблицю на другий лист. Виділить таблицю та її заголовок (комірки з A1 по F13) та скопіюйте на другий лист.

8.Відсортувати таблицю по трьом рівням.

−Установити покажчик на першу комірку з заголовком таблиці, тобто на комірку А3 (№ з/п), та виконати команду Дані/Сортування...

−У діалоговому вікні Сортування діапазону установити

Сортувати по - Загальний бал, по убуванню Потім по - Клас, по убуванню

104

В останню чергу по Прізвище, ініціали, по зростанню

Установити перемикач Ідентифікувати поля по підписам.

Натиснути клавішу ОК

9.Виконати умовне форматування даних у стовпчику Загальний бал по трьом умовам, для цього

−Виділити комірки зі значеннями у стовпчику Загальний бал (комірки з

D4 по D13)

−Виконати команду Формат/Умовне форматування... У діалоговому вікні установити Умова 1: значення менше 3.

Натиснути на кнопку Формат... У вкладці Шрифт встановити

Накреслення курсив, Колір - синій. Натиснути кнопку ОК

Умова 2: значення менше 4.

Натиснути на кнопку Формат... У вкладці Шрифт встановити Колір - зелений. Натиснути кнопку ОК Умова 3: значення більш або рівно 4.

Натиснути на кнопку Формат... У вкладці Шрифт встановити Накреслення напівжирний, Колір - красний. Натиснути кнопку ОК Натиснути кнопку ОК

10.Виконати умовне форматування даних у стовпчику Пропуски

−Виділити комірки зі значеннями у стовпчику Пропуски (комірки з Е4 по Е13)

−Виконати команду Формат/Умовне форматування... У діалоговому вікні установити Умова 1: значення більш або рівно 5.

Натиснути на кнопку Формат... У вкладці Шрифт встановити Підкреслення - одинарне, по значенню,

Колір - синій.

Натиснути кнопку ОК

−Натиснути кнопку ОК

11.Порівняти з початковою таблицею (Табл. 4.1) на листі Excel Таблиця для сортування

105

Варіанти завдань

1. Відсортувати таблицю по трьом критеріям (по загальному балу та прізвищу за убуванням і по групі за зростанням) та задати умовне форматування (загальний бал менш 60 виділити синім курсивом, загальний бал менш 75 виділити зеленим кольором, загальний бал більш або рівними 90 виділити красним жирним; кількість пропусків більш або рівних 4 виділити синім та підкреслити).

Успішність студентів

№	Прізвище,	Група	Загальний	Пропуски	День
з/п	ініціали		бал		народження

2. Відсортувати таблицю по трьом критеріям (по продажу у гривнах та назві банку за убуванням і по адресі за зростанням) та задати умовне форматування (продаж менш 5000 виділити синім курсивом, продаж менш 10 000 виділити зеленим кольором, продаж більш або рівними 20 000 виділити красним жирним).

Продаж валюти

№	Назва банку	Адреса	Продаж у гривнах	веб-
з/п				адреса

3. Відсортувати таблицю по трьом критеріям (по продажу у гривнах та назві авіаліній за убуванням і по адресі за зростанням) та задати умовне форматування (продаж менш 1000 виділити синім курсивом, продаж менш 5000 виділити зеленим кольором, продаж більш або рівними 10 000 виділити красним жирним).

106

Продаж авіаквитків

№	Назва авіаліній	Адреса	Продаж у гривнях	Телефон
з/п				каси

4. Відсортувати таблицю по трьом критеріям (по загальній суми та прізвищу за убуванням і по адресі за зростанням) та задати умовне форматування (загальну суму менш 300 виділити синім курсивом, загальну суму менш 500 виділити зеленим кольором, загальну суму більш або рівними 700 виділити красним жирним; пільги більш або рівних 50 грн. виділити синім та підкреслити).

Оплата житла

№	Прізвище, ініціали Адреса Загальна сума	Пільги
з/п

5. Відсортувати таблицю по трьом критеріям (по загальній суми та прізвищу за убуванням і по кафедрі за зростанням) та задати умовне форматування (загальну суму менш 1500 виділити синім курсивом, загальну суму менш 2000 виділити зеленим кольором, загальну суму більш або рівними 3000 виділити красним жирним).

Зарплата викладачів

№	Прізвище, ініціали	Кафедра	Загальна сума	робочий
з/п				телефон

107

6. Відсортувати таблицю по трьом критеріям (по стипендії та прізвищу за убуванням і по групі за зростанням) та задати умовне форматування (загальну суму менш 200 виділити синім курсивом, загальну суму менш 300 виділити зеленим кольором, загальну суму більш або рівними 600 виділити красним жирним; кількість пропусків більш або рівних 4 виділити синім та підкреслити).

Стипендія студентів

№	Прізвище, ініціали	Група	Стипендія	Пропуски
з/п

7. Відсортувати таблицю по трьом критеріям (по загальній вазі та прізвищу за убуванням і по числу спроб за зростанням) та задати умовне форматування (загальну вагу менш 250 виділити синім курсивом, загальну вагу менш 300 виділити зеленим кольором, загальну вагу більш або рівними 400 виділити красним жирним; кількість спроб більш або рівних 2 виділити синім та підкреслити).

Спортивні змагання з важкої атлетики

№	Прізвище,	Країна	Загальна	Число	Вік
з/п	ініціали		вага, кг	спроб

8. Відсортувати таблицю по трьом критеріям (по площі та назві за убуванням і по глибині за зростанням) та задати умовне форматування (площу менш 5000 виділити синім курсивом, площу менш 10 000 виділити зеленим кольором, площу більш або рівну 20 000 виділити красним жирним; глибину більш або рівну 500 м виділити синім та підкреслити).

108

Найбільші озера

№	Назва озера	Площа,	Глибина, м	Країна, де
з/п		км2		розташовано

9. Відсортувати таблицю по трьом критеріям (по середньої температурі та назві за убуванням і по осадкам за зростанням) та задати умовне форматування (вологість менш 60% виділити синім курсивом, вологість менш 75% виділити зеленим кольором, вологість більш або рівну 85% виділити красним жирним; осадки більш або рівну 60 мм виділити синім та підкреслити).

Клімат міст України у ліпні

№	Назва міста	Середня	°	С	Вологість повітря, %	Осадки, мм
з/п		температура,

10. Відсортувати таблицю по трьом критеріям (по дальності та прізвищу за убуванням і по числу спроб за зростанням) та задати умовне форматування (дальність менш 6 виділити синім курсивом, дальність менш 7 виділити зеленим кольором, дальність більш або рівними 7,5 виділити красним жирним; кількість спроб більш або рівних 1 виділити синім та підкреслити).

Спортивні змагання з стрибків у довжину

№	Прізвище,	Країна	Дальність,	Число	Вік
з/п	ініціали		м	спроб

При виконанні завдань користуватися вище розглянутим прикладом.

109

Лабораторна робота 5. Робота з даними в Excel: побудова графіків функцій

Мета завдання. Навчитися будувати графіки функцій в Excel. Постановка задачі. Розрахувати функції та побудувати їх графіки.

Методичні вказівки по виконанню завдання Список літератури

1.Коваленко Л.Б. Інформатика та системологія - Конспект лекцій,

Одеса, 2004, 80 с.

2.Рейзнер Т. Освой самостоятельно MS Excel 2000 за 24 часа. - М.: Изд.Дом "Вильямс", 2000 - 480 с.

3.Фултон Д. MS Excel 2000. - М.: Изд.Дом "Вильямс", 1999 - 223 с.

Теоретична частина

Точкова діаграма. Точкова діаграма відображує взаємозв'язок між числовими значеннями в декількох рядах і представляє дві групи чисел у вигляді одного ряду крапок в координатах XY. Ця діаграма часто використовується для представлення даних наукового характеру. При підготовці даних слід розташувати в одному рядку або стовпці всі значення змінної х, а відповідні значення в – в суміжних рядках або стовпцях. Даний тип діаграми схожий на графік, але останній відрізняється підписами під віссю OX. Точкові діаграми рекомендується використовувати, якщо: потрібно змінювати масштаб горизонтальної осі; потрібно використовувати для горизонтальної осі логарифмічну шкалу; значення розташовані на горизонтальній осі нерівномірно; на горизонтальній осі є безліч точок даних; потрібно ефективно відображувати дані електронної таблиці, які містять пари згрупованих полів із значеннями, і вводити незалежні шкали точкової діаграми для показу додаткових відомостей про згруповані значення; потрібно демонструвати не відмінності між точками даних, а аналогії у великих наборах даних; потрібно порівняти безліч точок даних без врахування часу, чим більше дані буде використано для побудови точкової діаграми, тим точніше буде порівняння.

110

Види точкової діаграми: 1) звичайний вигляд точкової діаграми; 2) згладжений вигляд точкової діаграми – значення на діаграмі з'єднані згладжувальними лініями; 3) згладжений вигляд точкової діаграми з маркерами; 4) точкова діаграма у відрізках – значення на діаграмі з'єднані відрізками; 5) точкова діаграма у відрізках з маркерами.

1) 2) 3) 4) 5)

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

1.Які дані зручно відображувати у вигляді гістограм?

2.Які дані зручно відображувати у вигляді діаграм-графіків?

3.Які дані зручно відображувати у вигляді точкової діаграми?

4.Які види точкових діаграм Вам відомі?

Приклад виконання завдання.

Завдання. Розрахувати функції y=3sin(Nx)+3Nsin(x) та z=3cos(Nx)+3Ncos(x), де x змінюється в діапазоні від 0 до 15 з шагом 0.5, та N=2.5. Побудувати графіки залежності 1) y=y(x), 2) z=z(x), 3) залежність z(y), 4) знов y=y(x), z=z(x), але уже на одній діаграмі.

1.Заповнити комірки значеннями x за допомогою автозаповнення.

2.Розрахувати функції y(x), z(x).

3.Побудувати графіки y=y(x), z=z(x), залежність z(y), та y=y(x) і z=z(x) на одній діаграмі.

Ход виконання завдання.

1.Запустити Excel, при цьому з’явиться нова книга.

2.Ввести текст з табл. 4.2 в указані комірки наступним чином:

−Встановити покажчик миші в комірку, у яку буде вводитися текст, та ввести текст з табл. 4.2, після введення тексту у комірку натиснути клавішу Enter;

−Клацнути лівою кнопкою миші на наступну комірку з табл. 4.2 та знов ввести текст і натиснути на Enter наприкінці і т.д.

111

Табл. 4.2. Вміст комірок, у яких розташовані заголовки таблиці.

Адреса	Текст у комірці
комірки
A1	x
B1	y
C1	z
D1	N

3.Заповнити комірки значень x-ів. Для цього

−У комірці A2 набрати 0, у комірці A3 набрати 0,5.

−Виділить комірки А2 та А3

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

−Натиснути лівою кнопкою миші на чорному хрестику та протягнути його до значення x рівного 15, досягнувши 32-го рядка.

4.У комірці D2 указати значення N, тобто набрати 2,5

5.Розрахувати за формулою значення функції y(x). Для цього у комірці B2

набрати наступну формулу: =3*SIN(D$2*A2)+3*D$2*SIN(A2)

6.Заповнити комірки значень y-ів. Для цього

−Виділить комірку B2

−Підвести курсор до правого ніжного кутка комірки B2 так, щоб у курсорі з’явився чорний хрестик замість звичайного, білого.

−Натиснути лівою кнопкою миші на чорному хрестику та протягнути його до значення x рівного 15, досягнувши 32-го рядка.

7.Розрахувати за формулою значення функції z(x). Для цього у комірці C2

набрати наступну формулу: =3*COS(D$2*A2)+3*D$2*COS(A2)

8.Заповнити комірки значень z-ів. Для цього

−Виділить комірку C2

−Підвести курсор до правого ніжного кутка комірки C2 так, щоб у курсорі з’явився чорний хрестик замість звичайного, білого.

−Натиснути лівою кнопкою миші на чорному хрестику та протягнути його до значення x рівного 15, досягнувши 32-го рядка.

9.Побудувати упроваджену діаграму функції y(x) на листі з даними

−Виконати Вставка/Діаграма...

−На вкладці Стандартні вибрати тип діаграми - Точкова, від -

Згладжена без маркерів.

112

−Клацнути на кнопку Далі

−На вкладці Діапазон даних встановити перемикач на ряди в стовпцях

−Клацнути на строку Діапазон

−Виділити на таблиці діапазон з A1 по В32.

−Відкрити вкладку Ряд. В вікні Ряд повинна з’явитися надпис y.

−Клацнути на кнопку Далі

−У вкладці Заголовки ввести

назву діаграми - Графік функції y(x) Ось Х - X

Ось Y - Y

Клацнути Далі

−Вибрати у меню розташування діаграми на існуючому листі.

−Клацнути на кнопку Готова

10. Побудувати упроваджену діаграму функції z(x) на листі з даними

−Виконати Вставка/Діаграма...

−На вкладці Стандартні вибрати тип діаграми - Точкова, від -

Згладжена без маркерів.

−Клацнути на кнопку Далі

−На вкладці Діапазон даних встановити перемикач на ряди в стовпцях

−Клацнути на строку Діапазон

−Виділити на таблиці діапазон з A1 по A32. Натиснути клавішу Ctrl і не відпускаючи її виділити діапазон з C1 по C32

−Відкрити вкладку Ряд. В вікні Ряд повинна з’явитися надпис z.

−Клацнути на кнопку Далі

−У вкладці Заголовки ввести

назву діаграми - Графік функції z(x) Ось Х - X

Ось Y - Z

Клацнути Далі

−Вибрати у меню розташування діаграми на існуючому листі.

−Клацнути на кнопку Готова

11. Побудувати упроваджену діаграму функції z(y) на листі з даними

−Виконати Вставка/Діаграма...

−На вкладці Стандартні вибрати тип діаграми - Точкова, від -

Згладжена без маркерів.

113

−Клацнути на кнопку Далі

−На вкладці Діапазон даних встановити перемикач на ряди в стовпцях

−Клацнути на строку Діапазон

−Виділити на таблиці діапазон з B2 по C32.

−Клацнути на кнопку Далі

−У вкладці Заголовки ввести

назву діаграми - Графік функції Z(Y) Ось Х - Y

Ось Y - Z

Клацнути Далі

−Вибрати у меню розташування діаграми на існуючому листі.

−Клацнути на кнопку Готова

12.Побудувати упроваджену діаграму функцій y(x) та z(x) на листі з даними

−Виконати Вставка/Діаграма...

−На вкладці Стандартні вибрати тип діаграми - Точкова, від -

Згладжена без маркерів.

−Клацнути на кнопку Далі

−На вкладці Діапазон даних встановити перемикач на ряди в стовпцях

−Клацнути на строку Діапазон

−Виділити на таблиці діапазон з A1 по C32.

−Відкрити вкладку Ряд. В вікні Ряд повинні з’явитися надписи y та z.

−Клацнути на кнопку Далі

−У вкладці Заголовки ввести

назву діаграми - Графіки функцій Y(X) та Z(X)

Ось Х - X

Ось Y - F(X)

Клацнути Далі

−Вибрати у меню розташування діаграми на існуючому листі.

−Клацнути на кнопку Готова

13.Зберегти робочу книгу: вибрати команду Файл/Зберегти як... У діалоговому вікні вибрати папку, вписати ім’я файлу, для типу файла вибрати у випадному списку Книга Excel.

114

Варіанти завдань

1.y=3sin(Nx)+3Nsin(x) z=3cos(Nx)+3Ncos(x), 0<=x<=15, N=2,5

2.y=5sin(Nx)+5Ncos(x) z=3cos(Nx)+3Nsin(x), 0<=x<=15, N=-1,5

3.y=4cos(Nx)-4Nsin(x) z=4sin(Nx)+3Ncos(x), 0<=x<=15, N=3,3

4.y=5xsin(Nx) z=5xcos(Nx), 0<=x<=15, N=2,5

5.y=5x(3sin(Nx)+cos(2Nx)), z=5x(3cos(Nx)+sin(2Nx)), 0<=x<=15, N=-6

6.y=2x(3sin(Nx)+sin(2Nx)), z=2x(3cos(Nx)+cos(2Nx)), 0<=x<=15, N=-2.5

7.y=3sin(Nx)+3Nsin(x2), z=3cos(Nx)+3Ncos(x2), 0<=x<=15, N=2,5

8.y=3sin(Nx)+3Ncos(x), z=3cos(Nx)+3Nsin(x), 0<=x<=15, N=-1,5

9.y=2cos(Nx)-3Ncos(x), z=2sin(Nx)+3Ncos(x), 0<=x<=15, N=-3,5

10.y=3xsin(N x ), z=3xcos(N x ), 0<=x<=15, N=3,5

115