Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ПРАКТИЧНЕ ЗАНЯТТЯ № 2
ВІДБІР ІНФОРМАЦІЇ ТА СТВОРЕННЯ ЗВЕДЕНИХ ТАБЛИЦЬ В MICROSOFT EXCEL 2007
Мета роботи: навчитись відсіювати зайву табличну інформацію та формувати звіти у вигляді зведених таблиць.
Програмне забезпечення: Microsoft Excel 2007.
Завдання: За допомогою операції фільтрування та майстра зведених таблиць відібрати з журналу реєстрації відряджень всіх операторів ЕОМ, які були у відрядженні тривалістю 5 днів і визначити кількість та середню три валість відряджень кожної мети у кожне місце скерування.
Теоретичні відомості: Упорядкування табличних даних може суттєво полегшити пошук необхідної інформації або її поверхневий візуальний аналіз. Воно реалізується за допомогою функції сортування, яка викликається з меню кнопки Сортування і фільтр групи Редагування на вкладці Основне і упорядковує виділений список за першим стовпцем. Якщо потрібно упорядкувати таблицю за відмінним від першого стовпцем або за кількома стовпцями, потрібно вибрати команду Настроюване сортування… і у діалоговому вікні задати параметри сортування.
Фільтрування, на відміну від сортування, коли змінюється лише порядок записів, тимчасово приховує записи, які не відповідають умовам відбору. Фільтр буває звичайним (автофільтр) і розширеним.
Застосування звичайного фільтру здійснюється командою Фільтр з меню кнопки Сортування і фільтр. При цьому повинна бути виділеною будь-яка з клітинок таблиці. В результаті у кожному стовпці з’являться кнопки зі стрілочками (рис. 2.1), призначені для задання параметрів фільтру. Стовпці, за якими відбулося фільтрування, позначаються лійкою, підвівши під яку курсор, можна отримати інформацію про фільтр.
Рис. 2.1
Для завдання складніших умов відбору з меню кнопок із стрілками вибираються команди Текстові фільтри (починається з…, закінчується…, містить…, не містить…), Фільтри чисел (більше…, більше або дорівнює…, менше…, менше або дорівнює…, між…, перші 10…, більше середнього…, менше середнього…) або Користувацький фільтр… (дає змогу застосовувати для умов відбору логічні оператори “і” та “або”).
Щоб відмінити автофільтр (відобразити приховані стовпці), достатньо повторно виконати команду Фільтр. Розширений фільтр зручно застосовувати у випадках, коли результат треба розмістити окремо від основної таблиці. Для цього у вільне місце копіюються заголовки стовпців і на вкладці Дані у групі Сортування і фільтр почергово натискаються кнопки Фільтр і Додатково. Після цього потрібно задати параметри розширеного фільтру (див. рис. 2.2): – перемикач встановити у положення Скопіювати результат до іншого розташування (положення Фільтрувати список на місці дасть результат, ідентичний автофільтруванню);
Рис. 2.2
Зведена таблиця – це таблиця, яка узагальнює і аналі-зує дані однієї чи кількох інших таблиць, які можуть міститися як на одному аркуші, так і в іншій книзі. Змінюючи структуру зведеної таблиці, можна динамічно отримувати різноманітні відомості.
Для побудови зведеної таблиці в Excel 2007 потрібно на вкладці Вставлення у групі Таблиці натиснути на кнопку Зведена таблиця і вибрати однойменну команду. Після вибору вихідних даних та місця для результуючої зведеної таблиці відкриється знаряддя для зведених таб-лиць (вкладки Параметри і Конструктор) і область “Список полів зведеної таблиці”, у якій формується її структура:
1. В області Виберіть поля, які слід додати до звіту: галочками відзначаються ті стовпці вихідної таблиці, які будуть аналізуватися.
2. В область Позначки стовпців перетягуються стовпці вихідної таблиці, дані з яких будуть заголовками стовпців зведеної таблиці.
3. В область Позначки рядків перетягуються стовпці вихідної таблиці, дані з яких будуть заголовками рядків зведеної таблиці.
4. В область Значення перетягуються стовпці вихідної таблиці, за якими здійснюватиметься кількісний аналіз і за допомогою команди Параметри значення поля… (доступна після натискання на стрілку в кінці поля) вибираються дії для аналізу (кількість, сума, середнє, максимум тощо).
5. В область Фільтр звіту перетягується решта стовпців, які не брали участі в трьох попередніх пунктах.
Отримана зведена таблиця має властивість інтерактивності – використовуючи кнопки зі стрілками (такі ж, як і для операції фільтрування даних), користувач може отримувати інформацію для довільних комбінацій критеріїв.
Хід роботи:
Етап 1. Відбір інформації
1. Створіть у середовищі Microsoft Excel 2007 файл Журнал_обліку_Відряджень.xlsх, який містить два аркуші: Журнал реєстрації і Бланк посвідки.
2. На аркуші Журнал реєстрації створіть таблицю з такими стовпцями: “№”, “Прізвище, ім’я та по батькові”, “Посада”, “Куди”, “Дата вибуття”, “Тривалість, днів”, “Дата прибуття”, “Мета”, “Наказ”.
3. Зробіть так, щоб стовпці № та Дата прибуття заповнювалися автоматично в процесі заповнення журналу реєстрації. Для цього у комірку А2 введіть формулу =ЕСЛИ(ЕПУСТО(В2);””;МАКС($A$1:A1)+1). Це означає, що якщо у комірці В2 пусто, то нічого не відбудеться, я якщо не пусто (тобто введено прізвище), то у комірку А1 буде записане число, на одиницю більше від максимального у стовпці А.
4. Аналогічно для обчислення дати прибуття у комірку G2 введіть формулу =ЕСЛИ(F2>0;E2+F2-1;””).
5. Виділивши комірку А2, підведіть вказівник миші до правого нижнього кута комірки (його вигляд повинен змінитись не хрестик) і, натиснувши ліву кнопку миші, розтягніть область вниз до комірки А51. У цьому випадку 50 записів у журналі нумеруватимуться автоматично.
6. Повторіть дії пункту 5 для комірки G2, забезпе-ивши тим самим автоматине введення дати прибуття перших 50 записів у журналі реєстрації.
7. Виокремте стовпці Е і G (тримаючи натиснутою клавішу Ctrl, клацніть лівою кнопкою миші на їх назвах) та задайте тип даних Дата на панелі інструментів Число закладки Основне.
8. Для унеможливлення доступу до комірок з формулами виділіть таблицю, крім стовпців А і G, виконайте команду Формат клітинок з меню кнопки Формат на панелі інструментів Клітинки, перейдіть у діалоговому вікні “Списки” на вкладку Захист і відмініть активацію опції Захистити клітинки.
9. Перейдіть на вкладку Рецензування і на панелі інструментів Зміни натисніть на кнопку Захистити аркуш та введіть пароль. Переконайтеся, що програма не дає можливості вносити чи змінювати інформацію в стовпцях № і Дата прибуття.
10. Заповніть таблицю даними (принаймі 20 записів) такого типу:
11. Щоб відібрати операторів ЕОМ, які їздили у відрядження тривалістю 5 днів, натисніть кнопку Фільтр на панелі інструментів Сортування і фільтр закладки Дані, виберіть зі списку посад оператора ЕОМ, а зі списку тривалостей – 5. Покажіть результат викладачу.
Етап 2. Створення зведеної таблиці
12. Ще раз натискаємо кнопку Фільтр, щоб повернутися до повного списку відряджень.
13. На закладці Вставлення на панелі інструментів Таблиці натискаємо на кнопку Зведена таблиця.
14. У вікні “Створення зведеної таблиці” перемикач Виберіть дані для аналізу встановлюємо в положення Виберіть таблицю або діапазон (оскільки вихідні дані містяться у поточній книзі), вказуємо діапазон і місце, де розмістити результуючу зведену таблицю – на новому аркуші. Натискаємо на кнопку ОК.
15. В області Виберіть поля, які слід додати до звіту: галочками відзначаємо стовпці “Посада”, “Тривалість”, “Мета” і “Куди”.
16. В область Позначки стовпців перетягуємо стовпець “Мета”, в область Позначки рядків - “Куди”, в область Фільтр звіту - стовпець “Посада”. Двічі перетягніть в область Значення поле “Тривалість” і за допомогою команди Параметри значення поля… (доступна після натискання на стрілку в кінці поля) вибираємо параметри Кількість і Середнє.
17. Покажіть зведену таблицю викладачу: