Інструкція 19

Тема: Фільтрування та групування даних.

Мета: Обробка даних з використанням фільтру.

Матеріально-технічне оснащення робочого місця:

1. Персональний комп’ютер

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

3. Текстовий процесор Microsoft Excel

Зміст і послідовність виконання завдань

На робочому аркуші 1 створіть таблицю 1.

1. За допомогою автофільтру зробіть вибір із таблиці 1. Задайте фільтр таблиці командою Данные  Фильтр Автофильтр.

   • Знайти всі продажі для Сокирко. Дані зберегти окремою таблицею на листі 2.

   • Знайти всі продажі за березень місяць. Дані зберегти окремою таблицею на листі 2.

   • Знайти продажі для Славута і ВАЗ. Дані зберегти окремою таблицею на листі 2.

   • Знайти самий дорогий автомобіль і самий дешевий. Дані зберегти окремою таблицею на листі 2.

   • Знайти три найбільші суми продажів. Дані зберегти окремою таблицею на листі 2.

2. Підвести підсумки по кожному продавцю враховуючи суми продажів. Для цього потрібно сористатись командою Данные Итоги…

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

4. Перейменувати лист 3 на Підсумки, а лист 2 – на Фільтр.

Збережіть свою роботу із назвою ДЕСЯТА РОБОТА у власній папці.

Інструкція 20

Тема: Майстер "зведених таблиць".

Мета: Обробка даних з використанням "зведених таблиць"

Матеріально-технічне оснащення робочого місця:

1. Персональний комп’ютер

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

3. Текстовий процесор Microsoft Excel

Зміст і послідовність виконання завдань

Теоретичні відомості

Зведена таблиця - це таблиця, в якій початкові дані, взяті із звичайної таблиці, перетворюються таким чином, що з'являється можливість виводити на екран дані, оброблені і відібрані по попередньо визначених критеріях.

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

Для роботи зі зведеною таблицею є панель інструментів "Зведені таблиці".

Завдання 1:

Три автотранспортні підприємства: АТП-1, АТП-2 та АТП-3 мають різну кількість автобусів: Ikarus-260, Ikarus-280, ЛАЗ-52523, ЗИЛ-32500 та Газель.

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

1. Які марки автобусів знаходяться в кожному з АТП;

2. Середньодобовий пробіг всього парку АТП.

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

Таблиця 1

№№ АТП	Автобус	Кількість	Сер.доб. пробіг	Всього
АТП-1	Ikarus-260	42	180	7560
АТП-2	Ikarus-260	40	200	8000
АТП-1	Ikarus-280	32	230	7360
АТП-2	ЛАЗ-52523	30	210	6300
АТП-3	ЛАЗ-52523	34	250	8500
АТП-1	ЗИЛ-32500	18	240	4320
АТП-2	ЗИЛ-32500	17	280	4760
АТП-3	ЗИЛ-32500	27	300	8100
АТП-1	Газель	53	290	15370
АТП-2	Газель	47	290	13630

Алгоритм створення Зведеної таблиці:

1. 1 Зробити активною чарунку А1. Командою "ДАННЫЕ" → "СВОДНАЯ ТАБЛИЦА" викликати підпрограму "МАЙСТЕР ЗВЕДЕНИХ ТАБЛИЦЬ" і далі слідувати вказівкам Майстра.

2. На першому кроці вказати джерело даних: включити опцію "В СПИСКЕ ИЛИ БАЗЕ ДАННЫХ MICROSOFT EXCEL"

3. На другому кроці вказати діапазон чарунок, в якому містяться початкові дані: перейти на робочий лист "Початкові дані" виділити діапазон чарунок A2:E13. Натиснути Далее.

4. На третьому кроці створити макет зведеної таблиці. Для цього натиснути кнопку МАКЕТ, у вікні що відкрилося, за допомогою маніпулятора миша перемістити:

• кнопку "№№АТП"  в область “Страница ",

• кнопку "Автобус"  в область "Строка",

• кнопку "Середньодобовий пробіг, км"  в область " Столбец ",

• кнопку "Всього "  в область " Данные ".  ОК

5. Виключити опцію "ПОМЕСТИТЬ ТАБЛИЦУ В СУЩЕСТВУЮЩИЙ ЛИСТ".

6. Завершити створення зведеної таблиці натисненням кнопки "Готово".

7. Виділити рядки 1-10. Включити Формат  Столбец  Автободбор ширины.

Зведена таблиця при необхідності може бути перерахована. Скопіюйте Зведену таблицю з листа ЗВЕДЕНА ТАБЛИЦЯ на лист ЗАВДАННЯ 2.

8. Перейдіть на лист "Початкові дані", змініть середньодобовий пробіг для Ikarus-260 з АТП-1 з 180км на 220км. Для оновлення зведеної таблиці досить натиснення правої кнопки миші по напису "Сер.доб. пробіг" на листі ЗАВДАННЯ 2. В контекстному меню, яке з'явилося, виберіть опцію "Оновити дані" - зведена таблиця автоматично перерахується.

9. В "зведеній таблиці" натисніть чорний трикутник поряд з написом (Все) і виберіть АТП-1, Подивіться на зміни в таблиці.

10. Зробіть зведені таблиці для АТП-2 та АТП-3.

11. Викличте зведену таблицю для всіх АТП і побудуйте для неї діаграму за допомогою кнопки "Майстер діаграм" на панелі інструментів "Зведені таблиці". За допомогою кнопки "поля №№АТП" викличте діаграми для АТП-1, АТП-2 та АТП-3.

Завдання 2:

1. Створіть новий лист і назвіть його ТАБЛИЦЯ 2.

2. За початковим даними з таблиці 2 на листі ТАБЛИЦЯ 2, на листах "Таблиця 2-1" "Таблиця 2-4" побудувати чотири зведені таблиці з різними макетами.

Таблиця 2

№№ АТП	Автобус	Дата	Кількість машин на лінії	Контрольна витрата палива	Пробіг, км	Добова витрата палива, л
АТП-1	Ikarus-260	04.09.2002	22	22	170	822,8
АТП-1	Ikarus-260	05.09.2002	24	22	190	1003,2
АТП-1	Ikarus-260	06.09.2002	20	22	180	792
АТП-2	Ikarus-260	04.09.2002	35	22	200	1540
АТП-2	Ikarus-260	05.09.2002	33	22	190	1379,4
АТП-2	Ikarus-260	06.09.2002	38	22	180	1504,8
АТП-1	Ikarus-280	04.09.2002	40	24,1	250	2410
АТП-1	Ikarus-280	05.09.2002	38	24,1	230	2106,34
АТП-2	ЗИЛ-32500	06.09.2002	23	12	280	772,8
АТП-3	ЗИЛ-32500	04.09.2002	30	12	300	1080
АТП-3	ЗИЛ-32500	05.09.2002	32	12	290	1113,6
АТП-3	ЗИЛ-32500	06.09.2002	29	12	280	974,4

Завдання 2-1: побудувати зведену таблицю для отримання відомостей про дату і кількість виходу на лінію автобусів по всім АТП і по кожному АТП окремо.

Завдання 2-2: побудувати зведену таблицю для розв'язання задачі: визначити витрату палива по кожному АТП, по кожній марці автобуса, за кожний конкретний день і за всі дні разом.

Завдання 2-3: побудувати зведену таблицю для розв’язання задачі: визначити мінімальну кількість автобусів які вийшли на лінію по кожній даті, по кожній марці автобуса і по кожному АТП окремо.

Завдання 2-4: побудувати зведену таблицю для розв’язання задачі:

Визначити максимальну витрату палива по кожній даті, по кожній марці автобуса і по кожному АТП окремо.

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

Збережіть свою роботу із назвою ДЕВЯТА РОБОТА у власній папці.