Збірник_Excel

10.На робочому листі Лист3 впорядкувати список, помістивши спочатку товари фірми, яка поставила товарів на найбільшу суму, потім товари фірми наступної по величині загальної вартості поставок і т.д. Роздрукувати результат.

Вказівки до виконання:

yвиконати команду Сервис→Параментры…;

yактивізувати вкладку Списки;

yстворити новий список, який міститиме назви фірм-постачальників у порядку в залежності від сум поставлених товарів (див. п.9);

yвиконати команду Данные→Сортировка…;

yу полі Сортировать по діалогового вікна Сортировка диапазона

вибрати елемент Постачальник;

yнатиснути на кнопку Параметры для визначення спеціального порядку сортування;

yу полі Сортировка по первому ключу діалогового вікна Парметры сортировки вибрати елемент, який відповідає створеному раніше списку.

Рис. 13.3. Діалогове вікно "Парметры сортировки".

11. Змінити порядок розташування полів Поставлено і Дата поставки.

Вказівки до виконання:

yвиділити діапазон комірок, який відповідає полям Поставлено і Дата поставки;

yвиконати команду Данные→Сортировка…;

yу діалоговому вікні Параметры сортировки увімкнути перемикач

Сортировать столбцы диапазона.

12.На робочому листі Лист3 відобразити Олівці поставлені у лютому місяці. Роздрукувати отриманий список.

Вказівки до виконання:

yвиділити будь-яку комірку списку;

yвиконати команду Данные→Фильтр→Автофильтр;

yу заголовку поля Група натиснути кнопку зі стрілкою;

yзі списку, що відкрився вибрати елемент (Условие…);

yу діалоговому вікні Пользовательский автофильтр встановити параметри відбору: "содержит" та "олів";

41

Рис. 13.4. Діалогове вікно "Пользовательский автофильтр" для визначення умови відбору по полю Група.

yу заголовку поля Дата поставки натиснути кнопку зі стрілкою;

yзі списку, що відкрився вибрати елемент (Условие…);

yу діалоговому вікні Пользовательский автофильтр встановити необхідні параметри відбору (рис. 13.5.).

Рис. 13.5. Діалогове вікно "Пользовательский автофильтр" для визначення умови відбору по полю Дата поставки.

13.На робочому листі Лист4 отримати список всіх товарів фірм-виробників Самоцвет і CENTROPEN. Роздрукувати результат.

Вказівки до виконання:

yвикористати автофільтр.

14.На робочому листі Лист4 отримати список всіх товарів поставлених після 01.04.05 р. кількістю більшою ніж 300 одиниць. Роздрукувати результат.

Вказівки до виконання:

yвиділити рядок заголовку створеного списку;

yвикористовуючи буфер обміну скопіювати виділений діапазон і вставити його декількома рядками нижче створеного списку, строго під ним;

yу наступних рядках під скопійованим рядком заголовку вказати умови відбору записів;

Рис. 13.6. Створений діапазон умов.

42

yвиділити будь-яку комірку списку;

yвиконати команду Данные→Фильтр→Расширенный фильтр;

yу діалоговому вікні Расширенный фильтр:

9вказати адресу діапазону умов;

9увімкнути перемикач скопировать результат в другое место;

9визначити адресу діапазону з результатом фільтрації у полі

поместить результат в діапазон.

Рис. 13.6. Діалогове вікно "Расширенный фильтр".

15.За допомогою розширеного фільтру на робочому листі Лист4 отримати список всіх товарів виробництва ТОВ "Зошит України" поставлених фірмами, у назві яких зустрічається буква „к” або виробництва А-HIO поставлених до 01.03.05р. Роздрукувати отриманий результат.

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

1.Засоби для роботи з базами даних в середовищі MS Excel.

2.Присвоєння імен спискам і полям.

3.Способи введення даних у список.

4.Сортування в алфавітно-цифровому порядку.

5.Сортування по даті і часу.

Лабораторна робота №14.

Тема: Робота зі списками в MS Excel. Сортування та фільтрація даних.

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

Хід роботи:

1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).

2.Зберегти створений документ під іменем ЛР14_Прізвище.xls.

3.На робочому листі Лист1 створити таблицю "Перелік замовлених товарів", наведену на рис. 14.1. Змінити назву робочого листа Лист1 на

Замовлення.

43

	№	Категорія	Назва	Рік випуску	Кількість сторінок	Кількість замовленого
	п/п
	1	Бази даних	Разработка баз данных MS SQL Server 2000	2001	464	68
	2	Видавничі і графічні	Цифровая фотография для "чайников".	2004	224	58
		додатки
	3	Операційні системи	Системы хранения данных в Windows.	2005	432	79
	4	Інтегровані пакети	AutoCAD 2004 для "чайников".	2003	368	63
	5	Апаратне забезпечення	Сканеры и сканирование. Краткое	2004	288	73
			руководство.
	6	Видавничі і графічні	3ds max 4. Библия пользователя + CD-ROM.	2002	1104	70
		додатки
	7	Комп'ютерні науки	Искусство программирования. 2т.	2001	820	63
			ПОЛУЧИСЛЕННЫЕ АЛГОРИТМЫ.
	8	Бази даних	Использование Microsoft Office Access	2005	1312	55
			2003. + CD-ROM.
	9	Апаратне забезпечення	ПК для "чайников". 2-е издание.	2004	256	18
	10	Апаратне забезпечення	Персональный компьютер. 2-е изд.	2000	432	21
	11	Видавничі і графічні	Цифровое видео для "чайников" + CD-	2004	352	80
		додатки	ROM.
	12	Бази даних	SQL для "чайников", 5-е издание.	2005	352	10
	13	Апаратне забезпечення	Как собрать ПК для "чайников", 4-е	2004	320	70
			издание.
	14	Видавничі і графічні	Flash MX. Библия пользователя + CD-ROM.	2003	1088	30
		додатки
	15	Видавничі і графічні	Photoshop CS для "чайников".	2004	448	25
		додатки
	16	Операційні системи	Unix. Библия пользователя.	2001	640	14
	17	Бази даних	Освой самостоятельно DB2 Universal	2004	528	71
			Database за 21 день + CD-ROM.
	18	Інтегровані пакети	Office XP для "чайников".	2002	288	23
	19	Видавничі і графічні	Освой самостоятельно Adobe Photoshop CS	2004	512	78
		додатки	за 24 часа.
	20	Комп'ютерні науки	Введение в экспертные системы.	2001	624	24
	21	Інтегровані пакети	Изучи Microsoft PowerPoint 2002 за 10	2002	192	40
			минут.
	22	Комп'ютерні науки	Практическая криптография.	2005	432	19
	23	Комп'ютерні науки	Системы баз данных. Полный курс.	2003	1088	28
	24	Бази даних	Access 2000 для Windows для "чайников".	2000	336	38
	25	Операційні системи	Windows 98 для "чайников". Учебный курс.	2000	272	20
	26	Операційні системи	Система безопасности Windows 2000.	2001	592	92
	27	Апаратне забезпечення	Запись компакт-дисков и DVD для	2004	304	84
			"чайников", 2-е издание.

Рис. 14.1. Таблиця "Перелік замовлених товарів".

4.Змінити порядок розташування полів Рік випуску і Кількість сторінок за допомогою операції впорядкування стовпців списку за змістом рядків.

5.Скопіювати створений список на робочі листи Лист2, Лист3, Лист4.

6.На робочому листі Замовлення відобразити книги категорії Операційні системи. Обчислити загальну кількість замовлених книг цієї категорії.

44

Роздрукувати отриманий результат.

Вказівки до виконання:

yвикористати можливості автофільтру для відбору книг категорії

Операційні системи.

7.Змінити назву робочого листа Лист2 на CD.

8.На робочому листі CD відобразити список книг, які доповнені СD. Впорядкувати отриманий список за полем Категорія.

9.Побудувати діаграму на основі отриманих у п.8 даних, на якій відобразити кількість замовлених книг та їх назви. Тип діаграм вибрати самостійно. Помістити діаграму на робочий лист CD.

10.Змінити назву робочого листа Лист3 на 2001.

11.На робочому листі 2001 відобразити список книг випущених у 2001 році, кількість сторінок яких не перевищує 500 і випущених у 2003 р, кількість сторінок яких не менше 800.

Вказівки до виконання:

yвикористати можливості розширеного фільтру для відбору даних за певним критерієм.

12.Змінити назву робочого листа Лист4 на Забезпечення.

13.Використовуючи можливості розширеного фільтру на робочому листі

Забезпечення отримати список книг категорії Апаратне забезпечення

замовлених у межах від 50 до 70 шт.

Вказівки до виконання:

yвиділити поле Кількість замовленого і скопіювати виділений діапазон у кінець списку;

yстворити діапазон умов відбору, який відповідає поставленим умовам

(рис. 14.2.);

yвикористати можливості розширеного фільтру для відбору даних за певним критерієм.

Рис. 14.2. Діапазон умов. 14. Роздрукувати отримані результати.

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

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

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

3.Перевпорядкування списку (сортування стовпців діапазону).

45

4.Типи критеріїв, які підтримуються в середовищі MS Excel.

5.Використання форми даних для відбору інформації.

Лабораторна робота №15.

Тема: Робота зі списками в MS Excel. Обчислення проміжних підсумків, сортування та фільтрація даних.

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

Хід роботи:

1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).

2.Зберегти створений документ під іменем ЛР15_Прізвище.xls.

3.На робочому листі Лист1 створити таблицю "Перелік товарів на складі фірми "Приладбуд", наведену на рис. 15.1. Змінити назву робочого листа Лист1 на Прихід.

№	Тип обладнання	Вид обладнання	Країна	Місто	Ціна	Грошова одиниця
п/п			виробник	виробник
	Запчастини,	Ланцюг "Oregon" до бензопил
22	комплектуючі	з шиною 16".	Франція	Орлеан	7,50	$
	Запчастини,	Ключ запасний до патрону
27	комплектуючі	ПСР-10.	Італія	Сієна	10,35	руб.
	Запчастини,	Ключ запасний до патрону
34	комплектуючі	ПСР-16.	Італія	Сієна	17,95	руб.
	Запчастини,	Ключ запасний до патрону
16	комплектуючі	ПСР-26.	Італія	Сієна	23,85	руб.
	Запчастини,	Перехідник до компресорів №
7	комплектуючі	446/1.	Латвія	Рига	33,60	руб.
19	Запчастини,	Пилки для лобзика по	Латвія	Рига	40,00	руб.
	комплектуючі	алюмінію "Rebir".
	Запчастини,	Перехідник до компресорів №
9	комплектуючі	350.	Латвія	Рига	67,15	руб.
	Запчастини,	Патрон свердлильний ПСР-
3	комплектуючі	10, 1/2".	Латвія	Рига	72,00	руб.
	Запчастини,	Патрон свердлильний ПСР-
28	комплектуючі	10, М12, 1.25.	Латвія	Рига	72,00	руб.
13	Запчастини,	Патрон свердлильний ПСР-	Латвія	Рига	75,00	руб.
	комплектуючі	13, М12, 1.25.
	Генератори,	Електрокомпресор Ultra С-
18	компресори	140/6	Франція	Ліон	109,00	$
32	Електро і	Бензопила ланцюгова "Poulan"	США	Дала	165,60	$
	бензопили	2250 R
10	Запчастини,	Диск пильний "Атака", діам.	Італія	Анкона	175,00	руб.
	комплектуючі	200х24х32 мм.
29	Запчастини,	Диск пильний "Атака", діам.	Італія	Анкона	175,00	руб.

46

		комплектуючі	200х24х30 мм.
	14	Генератори,	Бензогенератор Ultra G-	Франція	Орлеан	192,00	$
		компресори	1000/12
	30	Електро і	Бензопила ланцюгова "Poulan"	США	Дала	201,25	$
		бензопили	2550 R, в коробці
	21	Електро і	Бензопила ланцюгова "Poulan"	США	Дала	239,20	$
		бензопили	РР 260 Pro, в кейсі
	24	Дриль	Акумуляторна викрутка ОА-	Росія	Хімки	680,00	руб.
			4,8
	8	Генератори,	Фарбопульт №162 В/1860/	Італія	Анкона	710,00	руб.
		компресори
		Генератори,	Електрокомпресор Ultra AW-
	23	компресори	6090	Франція	Ліон	720,00	$
	4	Дриль	Електродриль ДУ-580 ЕР	Росія	Хімки	810,00	руб.
	2	Дриль	Електродриль ИЭ-1519 Э, д.	Росія	Конаково	930,00	руб.
			13 мм
	25	Дриль	Електродриль ДУ-650 ЕР	Росія	Хімки	940,00	руб.
	31	Дриль	Електродриль ДУ-780 ЕР	Росія	Хімки	1030,0	руб.
						0
	11	Електро і	Електролобзик ПМ 3-600 Е,	Україна	Сімфероп	1140,0	руб.
		бензопили	"Фіолент".		оль	0
		Генератори,	Бензогазогенератор Ultra LРG-			1235,0
	1	компресори	6500 E	Франція	Ліон	0	$
		Генератори,	Дизельний генератор Ultra G-			1300,0
	6	компресори	5000 E	Франція	Орлеан	0	$
		Генератори,	Дизельний генератор Ultra G-			1395,0
	5	компресори	5000 E3	Франція	Орлеан	0	$
			Акумуляторна дриль АД-12			1450,0
	15	Дриль	ЕР	Росія	Хімки	0	руб.
		Електро і	Електролобзик ПМ 4-700 Е,		Сімфероп	1460,0
	20	бензопили	"Фіолент".	Україна	оль	0	руб.
			Акумуляторна дриль ДА-18			1940,0
	35	Дриль	ЕР	Росія	Хімки	0	руб.
		Електро і	Електропила ПЭЛ-1400 Excel			2500,0
	33	бензопили		Росія	Перм	0	руб.
		Електро і	Електропила ланцюгова ПЦ-			2700,0
	17	бензопили	16Т	Росія	Хімки	0	руб.
		Електро і	Електропила ланцюгова ПЦ-			3000,0
	12	бензопили	400	Росія	Хімки	0	руб.
		Генератори,	Монтажний пістолет ПЦ-84			4710,0
	26	компресори		Росія	Тула	0	руб.

Рис. 15.1. Таблиця "Перелік товарів на складі фірми "Приладбуд".

4.Додати до списку поля Ціна в гривнях, Кількість, Вартість.

5.Обчислити ціну товару в гривнях, якщо відомо що

1$ = 27 руб. і 1$ = 5,06 грн.

Вказівки до виконання:

yвикористати функцію ЕСЛИ().

6.Заповнити поле Кількість використовуючи такі умови:

—якщо ціна ≤ 100 грн. – кількість від 100 до 300;

—якщо 100 < ціна ≤ 300 – кількість від 50 до 100;

47

—якщо 300 < ціна ≤ 1000 – кількість від 30 до 50;

—якщо 1000 < ціна ≤ 3000 – кількість від 10 до 30;

—якщо ціна > 3000 – кількість від 1 до 9.

Вказівки до виконання:

y використати функції ЕСЛИ(), ОКРУГЛ(), СЛЧИС().

7.Замінити формули у комірках поля Кількість значеннями.

Вказівки до виконання:

y виділити діапазон комірок з формулами;

yскопіювати виділений діапазон у буфер обміну виконавши відповідну команду контекстного меню виділеного діапазону;

yвиконати команду Правка→Специальная вставка;

yвставити інформацію з буферу на теж саме місце, вказавши параметр вставки – Значения.

8.Обчислити вартість товару на складі фірми "Приладбуд".

9.Збільшити кількість робочих листів до 8.

10.Скопіювати створений список на всі робочі листи документу.

11.На робочому листі Лист2 впорядкувати список за полями Тип обладнання

іВартість. Роздрукувати отриманий результат.

12.На робочому листі Лист3 впорядкувати список за складним ключем: за полями Країна виробник, Місто виробник, Тип обладнання і Кількість.

Роздрукувати отриманий результат.

13.На робочому листі Лист4 обчислити середню вартість кожного типу обладнання. Роздрукувати отриманий результат.

Вказівки до виконання:

yвикористати можливість обчислення проміжних підсумків на робочому листі.

14.На робочому листі Лист5 обчислити кількість найменувань кожного типу обладнання. У межах одного типу обладнання обчислити кількість найменувань кожної країни виробника. Роздрукувати отриманий результат.

Вказівки до виконання:

yвпорядкувати список за полями Тип обладнання і Країна виробник;

yз допомогою команди Данные→Итоги… обчислити кількість найменувань кожного типу обладнання;

48

Рис. 15.2. Діалогове вікно "Промежуточные итоги".

yвиконати ще раз команду Данные→Итоги… і обчислити кількість найменувань кожної країни виробника;

yдля збереження попередніх обчислених підсумків зняти параметр

Заменить текущие итоги.

15.На робочому листі Лист6 відобразити товари, ціна в гривнях яких знаходиться в межах від 400 до 1000 грн. Роздрукувати отриманий результат.

Вказівки до виконання:

yвиконати команду Данные→Фильтр→Автофильтр;

yу заголовку поля Ціна в гривнях натиснути кнопку зі стрілкою;

yзі списку, що відкрився вибрати елемент (Условие…);

yу діалоговому вікні Пользовательский автофильтр встановити необхідні параметри відбору.

16.Використовуючи розширений фільтр на робочому листі Лист7 відобразити товари, ціна яких знаходиться в межах від 100$ до 250$. Роздрукувати отриманий результат.

Вказівки до виконання:

yміж полями Ціна і Грошова одиниця додати до списку поле, яке повністю повторює поле Ціна;

yскопіювати рядок заголовків списку декількома рядками нижче списку;

yстворити діапазон умов, вказавши необхідні умови для полів Грошова одиниця і Ціна;

yвиконати команду Данные→Фильтр→Расширенный фильтр;

yу діалоговому вікні Расширенный фильтр вказати адреси необхідних діапазонів.

17.На робочому листі Лист8 відобразити всі електротовари. Скопіювати результат відбору даних в інше місце зазначеного робочого листа.

Вказівки до виконання:

yвикористати можливості відбору даних з допомогою розширеного фільтру.

18.На робочому листі Лист8 відобразити всі Дрилі виробництва Росії

49

довільної ціни і Генератори виробництва Франції з ціною більше 6500 грн. Скопіювати результат відбору даних в інше місце зазначеного робочого листа.

Вказівки до виконання:

yвикористати можливості відбору даних з допомогою розширеного фільтру.

19.На робочому листі Лист8 відобразити всі товари виробництва Латвії вартістю від 1000 до 2000 грн. і виробництва Італії вартістю від 500 до 5000 грн. Скопіювати результат відбору даних в інше місце зазначеного робочого листа.

Вказівки до виконання:

yу кінець списку додати поле, яке повністю повторює поле Вартість;

yвикористати можливості відбору даних з допомогою розширеного фільтру.

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

1.Використання автофільтру для вибірки даних на базі простих критеріїв.

2.Створення діапазону критеріїв.

3.Використання розширеного фільтру для вибірки даних на базі складних критеріїв.

4.Оператори, які використовуються при створенні критеріїв.

5.Виведення, редагування, сортування і друк відфільтрованих даних.

6.Створення діаграм на основі відфільтрованих даних.

Лабораторна робота №16.

Тема: Консолідація даних.

Мета: Набути навичок консолідації даних в MS Excel.

Хід роботи:

1.Відкрити табличний процесор MS Excel і переконатися в тому, що при відкритті процесора створено новий документ (Книга1).

2.Зберегти створений документ під іменем ЛР16_Прізвище.xls.

3.На робочих листах Лист1-Лист4 створити таблиці з даними про

використані будівельні матеріали на різних об’єктах, наведені на рис. 16.1. Змінити назви робочих листів Лист1-Лист4 на Об’єкт1-

Об’єкт4.

50