МІНІСТЕРСТВО ОСВІТИ І НАУКИ , МОЛОДІ ТА СПОРТУ УКРАЇНИ
Одеський національний політехнічний університет новокаховський політехнічний коледж
Звіт з лабораторної роботи № 13-14
Використання розширеного фільтру. Консолідація даних. Майстер зведених таблиць при обробці даних в Excel
з дисципліни “Офісне програмне забезпечення”
ЗЛР.5.05010301.5102.12.013-014
Виконав студент _____________ Чиньоний А.О.
II курсу групи 5102:
Перевірив викладач: ______________ Л.В. Макрушина
Нова Каховка
2012
Тема роботи:
Використання розширеного фільтру. Консолідація даинх. Майстер зведених таблиць при обробці даних в Excel
Мета роботи:
2.1 Навчитись використовувати розширений фільтр, консолідацію даних, майстер зведених таблиць.
Обладнання:
3.1 ПЕОМ IBМ PC.
4 Порядок виконання роботи:
4.1 Називаю перший аркуш робочої книги іменем “Фірма 98”.
4.2 В області А6:F16 аркуша “Фірма 98” будую таблицю.
Прізвище |
Ім'я |
Посада |
Вік |
Прибутки |
Податки |
Антонов |
Ілля |
Директор |
35 |
60000 |
20000 |
Архипов |
Дмитро |
Маркетолог |
32 |
38000 |
11200 |
Бабич |
Микола |
Економіст |
42 |
42000 |
12800 |
Буров |
Віктор |
Бухгалтер |
48 |
45000 |
14000 |
Бобир |
Семен |
Менеджер |
26 |
32000 |
8800 |
Доценко |
Олег |
Менеджер |
25 |
35000 |
10000 |
Дамін |
Остап |
Програміст |
22 |
38000 |
11200 |
Климчук |
Орест |
Менеджер |
24 |
32000 |
8800 |
Петренко |
Федір |
Секретар |
28 |
26000 |
6400 |
Семенов |
Петро |
Водій |
30 |
24000 |
5600 |
4.3 Вивожу рядки з даними про співробітників, чиї прізвища починаються з “А”, “Б” або “Н”.
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
А
Б
Н
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
Ілля
Директор
35
60000
20000
Архипов
Дмитро
Маркетолог
32
38000
11200
Бабич
Микола
Економіст
42
42000
12800
Буров
Віктор
Бухгалтер
48
45000
14000
Бобир
Семен
Менеджер
26
32000
8800
4.4 Вивожу рядки з даними про співробітників віком не старше 25 років, які працюють на посаді менеджера.
4.12 Копіюю відфільтровані рядки в іншу частину робочого аркуша.
4.13 Перейменовую другий, третій, четвертий і п’ятий аркуші робочої книги відповідно на “Фірма 99”, “Фірма 00”, “Фірма 01” і “Фірма загалом”.
4.14 Створюю на робочих аркушах “Фірма 99”, “Фірма 00” і “Фірма 01” таблиці, що містять інформацію про співробітників фірми відповідно за 1999, 2000 і 2001 р.
4.15 Копіюю з аркуша “Фірма 98” на підсумковий аркуш “Фірма загалом” стовпці “Прізвище”, “Ім’я”, “Посада”, “Вік” та заголовки стовпців “Прибутки” і “Податки”.
4.17 Консолідую аркуші за місцем розташування, усереднюючи дані з робочих аркушів “Фірма 99”, “Фірма 00”, “Фірма 01” за стовпцями “Прибутки”, “Податки”.
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
60000
20000
Архипов
36333,33
11100
Бабич
42000
13100
Буров
45200
14000
Бобир
32467
9100
Доценко
35333,33
10666,67
Дамін
37636,33
10499,67
Климчук
33333,33
9200
Петренко
25700
6333,33
Семенов
24333,33
5433,333
4.18 Консолідую аркуші за місцем розташування, визначивши максимальні обсяги прибутків та податків за три роки.
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
70000
21000
Архипов
39000
12000
Бабич
43000
13800
Буров
46600
15000
Бобир
33000
9900
Доценко
37000
11000
Дамін
39909
11200
Климчук
37000
9900
Петренко
26100
6400
Семенов
26000
5700
4.19 Консолідую аркуші за місцем розташування, визначивши мінімальні обсяги прибутків та податків за три роки.
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
50000
19000
Архипов
32000
10100
Бабич
41000
12700
Буров
44000
13000
Бобир
32000
8600
Доценко
34000
10000
Дамін
35000
10099
Климчук
31000
8800
Петренко
25000
6300
Семенов
23000
5000
4.20 Для завдань 16-19 консолідую аркуші із зв’язком.
4.21 На робочому аркуші “Фірма 98” видаляю рядок з даними про співробітника Антонова, на аркуші “Фірма 99” – про Доценка, на аркуші “Фірма 00” додаю рядок з інформацією про співробітника Іванова.
4.22 Консолідую таблиці за категоріями, використовуючи функції СУММА, СРЕДНЕЕ, МАКСИМУМ та МИНИМУМ.
СУММА
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
180000
60000
Архипов
147000
44500
Бабич
168000
52100
Буров
180600
56000
Бобир
129401
36100
Доценко
106000
32000
Дамін
150909
42699
Климчук
132000
36400
Петренко
103100
25400
Семенов
97000
21900
Іванов
25000
6300
СРЕДНЕЕ
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
60000
20000
Архипов
36750
11125
Бабич
42000
13025
Буров
45150
14000
Бобир
32350,25
9025
Доценко
35333,33
10666,67
Дамін
37727,25
10674,75
Климчук
33000
9100
Петренко
25775
6350
Семенов
24250
5475
Іванов
25000
6300
МАКСИМУМ
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
70000
21000
Архипов
39000
12000
Бабич
43000
13800
Буров
46600
15000
Бобир
33000
9900
Доценко
37000
11000
Дамін
39909
11200
Климчук
37000
9900
Петренко
26100
6400
Семенов
26000
5700
Іванов
25000
6300
МИНИМУМ
-
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
Антонов
50000
19000
Архипов
32000
10100
Бабич
41000
12700
Буров
44000
13000
Бобир
32000
8600
Доценко
34000
10000
Дамін
35000
10099
Климчук
31000
8800
Петренко
25000
6300
Семенов
23000
5000
Іванов
25000
6300
4.23 На шостому робочому аркуші будую таблицю з рядків таблиць, розміщених на робочих аркушах “Фірма 98”, “Фірма 99”, “Фірма 00” і “Фірма 01”. Крім того, ліворуч стовпця “Прізвище” додаю стовпець “Рік” і зазначаю рік, за який наведено дані про співробітників фірми (тобто 1998, 1999, 2000 і 2001 р.). Стовпець “Ім’я” видаляю.
-
Рік
Прізвище
Ім'я
Посада
Вік
Прибутки
Податки
1999
Антонов
Ілля
Директор
35
60000
20000
1998
Архипов
Дмитро
Маркетолог
32
32000
10100
2000
Бабич
Микола
Економіст
42
43000
12700
2001
Буров
Віктор
Бухгалтер
48
44000
15000
1998
Бобир
Семен
Менеджер
26
32401
9900
1998
Доценко
Олег
Менеджер
25
37000
11000
2000
Дамін
Остап
Програміст
22
39909
10099
2001
Климчук
Орест
Менеджер
24
37000
9900
1999
Петренко
Федір
Секретар
28
26000
6400
2001
Семенов
Петро
Водій
30
23000
5700
4.24 Для розглядуваної таблиці будую зведену таблицю, розмістивши поля “Прізвище”, “Посада”, “Вік” у зоні рядків, поле “Рік” – у зоні стовпців, поля “Прибутки” і “Податки” – в області даних. Розміщую зведену таблицю на новому аркуші “Зведена таблиця”.
4.25 Застосовую до таблиці формат ОБЬЕМНЫЙ 2.
4.26 Приховую елементи внутрішніх полів “Посада” і “Вік”, двічі клацнувши на відповідному елементі зовнішнього поля “Прізвище”. Потім знову виводжу елементи внутрішніх полів.
4.27 Детально переглядаю інформацію про підсумкові значення прибутків та подітків для конкретного співробітника.
4.28 Переміщую поля “Прізвище” і “Рік” у зону рядків, поля “Посада” і “Вік” – у зону стовпців так, щоб для кожного співробітника елементи полів “Прибутки” і “Податки” групувалися за роками і відбувалися
проміжні підсумки.
4.29 Переміщую поле “Рік” у зону стовпців, відфільтрувавши дані за прибутками і податками для кожного року.
4.30 Переглядаю значення прибутків і податків для конкретного року, вибравши його зі списку.
4.31 Переглядаю значення прибутків і податків для співробіника, який обіймає конкретну посаду і має заданий рік.
4.32 Перейменовую заголовки полів, а потім повертаю їм старі назви.
4.33 Використовую функції СРЕДНЕЕ, МАКСИМУМ, МИНИМУМ для підбиття підсумків для кожного співробітника фірми.
4.34 На основі зведеної таблиці будую гістограму, що відображає прибутки і податки за роками для конкретного співробітника фірми.