ЭИ_ПОСОБИЕ_ЧАСТЬ_2

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

1.Дати коротку характеристику основних елементів екрана Excel.

2.Як уставити рядок у таблицю Excel?

3.Як виконується введення даних в осередок Excel?

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

5.Як перевірити - знаходиться в осередку цифра або формула?

6.Що буде з текстом, якщо він перевищує поточне значення ширини осередку?

7.Як перейменувати лист у Excel?

8.Як здійснюється перенос за словами в осередку?

9.Як виконати заливання осередків кольором?

10.Визначити пріоритет операцій при обчисленнях у Excel?

11.Як можна швидко виконати підрахунок підсумкових значень по стовпцях або рядкам таблиці?

12.Як виділити несуміжні стовпці в таблицях Excel?

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

14.Як здійснюється закріплення областей у таблицях Excel?

Чому ви навчилися?

Уводити текстові і числові значення в електронні таблиці.

Перейменовувати листки робочої книги.

Форматувати вміст осередків таблиці.

Закріплювати області в таблицях Excel.

Результати виконання роботи повинні бути представлені в наступному виді:

Таблиця 1. Штатний розклад підприємства "Вектор"

№ пп	Посада	Кількість	Оклад (грн.)
		одиниць
1	Директор	1	2000
2	Заступник директора	1	1500
3	Бухгалтер	1	1000
4	Водій	4	900
5	Механік	1	850

14

6	Електрик	1	850
7	Прибиральниця	1	500

Таблиця 2. Список співробітників

		№		П.І.Б.		Посада				Стаж
		пп								роботи
		1		Іванов С. М.		Директор				10
						Заступник
		2		Петров О. М.		директора				5
		3		Сидорова Є. І.		Бухгалтер				10
		4		Семенова О. І.		Водій				5
		5		Михайлов І. С.		Водій				15
		6		Смирнов О. С.		Водій				10
		7		Малахов І. С.		Водій				5
		8		Патапов А. О.		Механік				10
		9		Яременко М. Б.		Електрик				4
		10		Броникова Є. А.		Прибиральниця				20
Таблиця 3. Облік робочого часу на підприємстві
							З них
	№ пп			П.І.Б.		Відпрацьовано днів		Відпуска		Лікарняні
	1			Іванов С.М.		22		0		0
	2			Петров О.М.		15		0		7
	3			Сидорова Є.І.		22		0		0
	4			Семенова О.І.		16		6		0
	5			Михайлов І.С.		0		22		0
	6			Смирнов О.С.		22		0		0
	7			Малахов І.С.		4		10		8
	8			Патапов А.О.		22		0		0
	9			Яременко М.Б.		12		10		0
	10			Броникова Є.А.		5		12		5

15

Числові формати користувача в MS Excel

Для зручності роботи можна створювати спеціальні панелі інструментів і на них розміщати ті кнопки, функції яких відповідають цілі роботи. Для цього потрібно викликати команду Вид/Панели инструментов/Настройка, відкриється діалогове вікно Настройка, перейти на вкладку Панели инструментов, клацнути на кнопці Создать і в діалоговому вікні Создание панели инструментов увести назву нової панелі і клацнути на кнопці ОК. Потім перейти на вкладку Команды, вибрати потрібні категорії, а в них команди, і перетягнути їх мишею на нову панель. Можна перейменувати панель або видалити, використовуючи відповідні кнопки на вкладці Панели инструментов. Вкладка Параметры дозволяє задати параметри нової панелі інструментів. В MS Excel утримується безліч убудованих форматів даних. Для форматування чисел і тексту в осередку варто виконати команди Формат/Ячейки/ діалогове вікно Формат ячеек/вкладка Число. Ця вкладка пропонує 12 категорій убудованих форматів даних (Общий, Числовой,

Денежный, Финансовый, Дата, Время, Процентный, Дробный, Експоненциальный, Текстовый, Дополнительный). У поле Образец цього вікна можна побачити, як обраний формат впливає на вміст активного осередку.

Крім того, на панель інструментів Форматирование винесені у виді кнопок три найбільше часто використовуваних числових формати: Денежный формат,

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

Якщо жоден з убудованих форматів Excel не підходить, можна створити і використовувати власні і числові формати користувача.

Формати користувача зберігаються разом з робочою книгою, у якій вони створювалися. При конструюванні числових форматів користувача використовуються спеціальні символи, що вводяться в текстове поле Тип, яке стає доступним при виконанні команди Формат/Ячейки/ діалогове вікно Формат ячеек/вкладка Число/ група Числовые форматы/Все форматы.

У форматів користувача можуть використовуватися наступні символи форматування:

Символ "#" використовується для відображення значущих (ненульових) цифр, незначні нулі не відображаються. Якщо число десяткових знаків перевищує кількість символів "#" у правій частині формату, то Excel округляє число до зазначеної кількості десяткових знаків після коми. Якщо число має

16

більшу кількість десяткових знаків ліворуч від коми, чим символів "#" у лівій частині формату, то Excel виводить більшу кількість цифр.

Символ "0" використовується для відображення цифр і незначних нулів. Для нього справедливі ті ж правила, що і для "#", але крім того, якщо число десяткових знаків менше кількості символів "0" у коді формату, то Excel виводить відсутн нулі.

"?" діє в такий же спосіб як 0, але незначні нулі замінюються пробілами, крім того, числа вирівнюються по десятковій комі.

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

Символ "," розділяє число на цілу і дробову частину. Якщо формат містить ліворуч від коми тільки символи "#", то Excel відображає числа не перевищуючі одиниці, починаючи з десяткової коми. Щоб уникнути цього, використовують символ "0" у якості першого (ліворуч від коми) символу коду формату.

" %" - Excel множить число на 100 і додає знак %, при введенні числа в заздалегідь отформатований осередок множення не виробляється.

"Текст" - відображає текст, заданий у лапках.

@ - указує місце у форматі, де буде відображений уведений користувачем

текст.

Приклади використання розглянутих символів у форматах:

Число, що вводиться	Формат числа	Число у форматі
38978,1276	0,##	38978,13
38978,1276	0,0000	38978,1276
341,56	#0,####	341,56
341,56	#0,0000	341,5600
0,768	0,000	0,768
0,768	#,000	,768
12345478,34	# 000,00 пробіл	12 345,68
12345478,34	# 000,00 два пробіли	12,35
341,56	#0,?????	341,56
341,56	###,00« грн.»	341,56 грн.
0,768	# #0,00%	76,80%

17

ДСП	«Матеріал »@	Матеріал ДСП
Графітний	@«олівець»	Графітний олівець

Excel дозволяє виводити числа пофарбованими в різні кольори. Для цього на початку формату варто вказати назву кольору в квадратних дужках. Наприклад, [Синій] ###,00. Можна задати кольори: чорний, білий, червоний, синій, зелений, жовтий або вказати колір так - [колір #], де # позначає номер кольору від 1 до 56 на колірній палітрі. Нумерація йде ліворуч праворуч і далі вниз. Відкрити колірну палітру – Сервис/Параметры/Вкладка Цвет.

До складу кодів числових форматів користувача можна додавати умови, що містять оператори порівняння (<,>,=,>=,<= , або <>) і беруться в квадратні дужки як частина опису формату.

Наприклад, усі числа більш 100 вивести синім кольором по заданому формату, усі числа менше нуля - червоним, а всі інші за замовчуванням чорним: [Синій] [>100] ###,00; [Червоний] [<0] 0,00; ###,00

Формати користувачів можуть складатися з чотирьох секцій кодів, розділених крапкою з комою:

додатний формат; від’ємний формат; нульовий формат; формат тексту

Перша секція визначає формат для додатних чисел в осередку, друга - для від’ємних чисел, третя - для нульового значення числа, четверта для тексту. При відсутності необхідності заповнення всіх секцій, замість пропущеного формату ставиться крапка з комою. Якщо формат користувача містить тільки одну секцію, то EXCEL застосовує цей формат до додатного, від’ємного і нульового значень. Якщо формат користувача містить дві секції, то перша застосовується до додатного і нульового, а друга - тільки до від’ємного значення. Приклад запису формату:

«Нараховано: »###,00;« Утримано: »###,00;«Відсутня інформація»;«Помилка! Це не число: »@

Результати застосування формату до введених значень

Уведене значення	Відображуване значення
144,20	Нараховано: 144,20
- 12,89	Утримано:12,89
0	Відсутня інформація
52.45	Помилка! Це не число:52.45

18

Методичні вказівки до виконання завдання № 2

Тема: Числові формати користувача в MS Excel

Ціль: Одержати практичні навички створення числових форматів користувача. План

1.Створення власної панелі інструментів

2.Використання убудованих форматів даних. Створення форматів користувача.

Технологія виконання завдання

1.Створити власну панель інструментів для форматування осередків таблиці,

включивши в неї кнопки: Ячейки, Полужирный, Курсив, По центру, Процентный формат, Увеличить разрядность, Уменьшить разрядность,

Формат по образцу. Використовувати надалі нову панель для виконання наступних завдань.

2.Для форматування таблиці створити числові формати користувача(Формат/Ячейка/Число/Все форматы) у відповідності з наступними вимогами:

1-й стовбець. При введенні числа менше 10 перед ним повинний виводиться 0;

2-й стовбець. При введенні "Найменування матеріалу" (наприклад, ДСП) в осередку виводиться текст "ДСП Вільха "16"" тощо;

3-й и 4-й столбец стовбець. Заповнити осередки за допомогою операції

автозаполнения даних;

5-й стовбець. При введенні числа в осередок повинний відбуватися аналіз кількості його розрядів (якщо число більше 999999, то воно 7-и розрядне і більш; якщо число більше 999 - 4-х розрядне і більш) і, у залежності від результату аналізу, число повинне округлятися до мільйона, тисячі, або не округлятися. Наприклад, число 123456 більше 999 і тому округляється до тисяч (результат в осередку - 123,456 тис.); число 1000000 більше 999999 і округляється до мільйонів (результат в осередку - 1 млн.);

6-й стовбець. Якщо число, що вводиться в осередок даного стовпця, додатне, то йому повинне передувати повідомлення "Прихід", якщо воно від’ємне, - то "Витрата", слово "Витрата" і це число представити червоним кольором. При відображенні введеного в осередок числа повинний

19

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

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

1.Як створити власну панель інструментів?

2.Які числові формати використовуються в MS Excel?

3.Яку роль виконують символи # і 0 у числових форматах?

4.Як у форматах виділити сиволи кольором?

5.Яку роль виконує символ пробіл у числових форматах?

6.У яких числових форматах можуть використовуватися оператори типу [умова значення]?

7.Як організувати розходження виводу додатних і від’ємних чисел в осередках, використовуючи можливості форматування?

8.Опишіть склад і призначення основних секцій кодів числових форматів.

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

10.Охарактеризуйте створені формати користувача по всіх стовпцях таблиці.

Чому ви навчилися?

Створювати власну панель інструментів для форматування осередків таблиці.

Створювати числові формати користувача.

Включати в числовий формат користувача текст колірних розходжень.

Використовувати основні секції кодів числових форматів.

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

20

Результати виконання роботи повинні бути представлені в наступному виді:

Таблиця 4. Оборотна відомість

Код	Найменування	Розмір	Одиниця	Залишок на	Обороти за
листа	матеріалу		виміру	1.10	місяць, грн.
				сума, грн.
1	2	3	4	5	6
01	ДСП Ольха "16"	2100х1800	кв.м	1,22 млн.	Прихід: 2 000
02	ДСП Дуб "16"	2100х1800	кв.м	10,50 тис.	Витрата: 800
03	ДСП Бук "16"	2100х1800	кв.м	800	Прихід:: 750
04	ДСП Вишня "16"	2100х1800	кв.м	21,50 тис.	Витрата: 900
05	ДСП Красне	2100х1800	кв.м	620	Прихід: 12 000
	дерево "16"
06	ДСП Сосна "16"	2100х1800	кв.м	1,00 млн.	Витрата: 700

Робота з майстром функцій у MS Excel

Команда Правка>Специальная вставка дозволяє вставити інформацію, форматування, формули з одного осередку в інший, використовуючи буфер обміну, а також виконувати ряд операцій над вмістом осередків при їхньому переміщенні або копіюванні. При виклику команди відкривається діалогове вікно Специальная вставка. Вставка занесеної в буфер інформації поточної або іншої робочої книги виконується за допомогою наступних перемикачів: Формулы,

Значения, Форматы, Без рамки, Ширины столбцов, Примечания, Условия на значения, Операция, Пропускать пустые ячейки, Транспортировать для преобразования строки в столбец.

Ідентифікатором осередку в робочій книзі є адреси (А6, З9 і ін.). За замовчуванням MS Ехсеl працює з відносними адресами. Відносні адреси осередків автоматично обновляються при копіюванні формул на нове місце. Найпростіший спосіб копіювання рядка або стовпця, що містять формули, - скористатися засобом автозаполнение. Для копіювання формули в кілька осередків того ж стовпця (рядка) варто виділити осередок з формулою і «протягти» її по потрібних осередках, утримуючи мишею маркер заповнення. Перерахування значення формули виконується автоматично при зміні адрес

21

осередків. Однак, у деяких випадках необхідно скопіювати формулу таким чином, щоб посилання на осередок або діапазон не змінювалася в процесі копіювання. У цьому випадку використовуються абсолютные адреса. Абсолютна адреса осередку - це точна адреса осередку у формулі, що посилається на даний осередок не залежно від положення осередку з формулою. Для перетворення відносних адрес формули в абсолютні потрібно відредагувати формулу:

двічі клацнути мишею на осередку з формулою або натиснути клавішу F2, або виділити потрібну адресу в рядку формул;

установити курсор на координаті адреси осередку, яку потрібно

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

Після закінчення редагування натиснути клавішу Enter і скопіювати формулу в потрібний інтервал осередків.

У тих випадках, коли необхідно, щоб при копіюванні змінювалися тільки рядок або тільки стовпець, використовуються змішані адреси, що містять як абсолютні, так і відносні адреси.

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

Еxcel пропонує зручні засоби створення простих і складних формул, використовуючи посилання на осередки, оператори і функції. Уведення функцій

вЕxcel можна здійснити, викликавши Мастер функций:

за допомогою команди Вставка>Функция;

при натисканні кнопки fx (Вставка функции) на панелі інструментів

Стандартная або в рядку формул.

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

Логические, Математические, Определенные пользователем, Статистические, Текстовые, Финансовые, Работа с базой данностях, Ссылки и массивы.

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

Уведення параметрів убудованих функцій Еxcel виконується в спеціальних діалогових вікнах, що відкриваються при виборі функції. У діалоговому вікні присутні поля для введення параметрів (аргументів) функції, короткий коментар про призначення функції і кожного окремого параметра. Кнопка з зображенням

22

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

Один з недоліків електронних таблиць полягає в тому, що будь-який користувач може случаймо змінити дані. В Еxcel існує три рівні захисту. Всі елементи робочих листків, включаючи осередки, елементи керування, захищаються за замовчуванням. Однак цей захист не працює поки не включений наступний рівень захисту - захист листків. Третій рівень захисту - захист книг не дозволяє додавати в книгу нові листки, видаляти і переміщати листки книги.

Приховання рядків і стовпців не впливає на роботу електронної таблиці, просто деякі осередки будуть невидимі. Формули з посиланнями на такі осередки будуть працювати коректно. Для виконання цієї операції в меню Формат потрібно вибрати відповідні команди, що відносяться до рядка або стовпця.

Методичні вказівки до виконання завдання № 3 Тема: Робота з майстром функцій у MS Excel

Ціль: Одержати практичні навички виконання розрахунків у

MS Excel

План

1.Керування даними робочого листка за допомогою Специальной вставки.

2.Виконання обчислень із застосуванням абсолютних адрес.

3.Заповнення таблиць, використовуючи посилання на інші аркуші робочої книги.

4.Використання убудованих функцій.

5.Захист інформації MS Excel.

Технологія виконання завдання

1.Створити робочий лист с ім'ям «Довідники», на якому ввести таблиці «Робочий календар» і «Інформація для розрахунку». «Номер поточного місяця» з таблиці «Робочий календар» передати по посиланню в таблицю «Інформація для розрахунку», «Кількість робочих днів» - використовуючи

«автозаполнение».

2.Створити робочий лист з ім'ям «Оклади співробітників», у яку інформацію про посади передати по посиланню з таблиці 1. Оклади заносити в

23