Зведені таблиці Excel (3.9)

Матеріал з Фізмат Вікіпедії
Перейти до: навігація, пошук

Мета: вивчення нової теми «Зведені таблиці»; закріплення та поглиблення знань з теми «Табличний процесор»; стимулювання піднавального інтересу до даної теми та предмету «Інформатика»; активізація взаємодії між учнями, розиток навичок групової роботи; виховання в учнів самостійності, колективізму, відповідальності; розвиток розумової діяльності, пам’яті.

Тип уроку: комбінований.

Мета для учнів: впевнити себе, що Я МОЖУ, Я ВМІЮ, Я ЗНАЮ. Я знаю, що таке зведена таблиця, я знаю, де вони застосовуються, я вмію працювати з ними, користуватися діловою графікою, працювати у колективі, я можу застосовувати свої зання для вирішення практичних завдань.

ХІД УРОКУ

Організаційний момент.

Привітання, налагодження на робочий настрій, вияснення відсутніх.

Постановка мети та завдань уроку.

Я3.jpg

Контроль домашнього завдання.

Актуалізація знань.

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

Вивчення нового матеріалу.

Скажіть будь ласка, яке основне призначення електронних таблиць? Ведення розрахунків. Важко назвати розрахункову операцію, з якою не впоралися б електронні таблиці. Коли мова заходить про доступність, простоту і функціональність – Excel просто неперевершений. Але в електронних таблицях, так би мовити, «святая святих» – зведені таблиці – зручний та ефективний інструмент для аналізу багатовимірних даних.

S3.jpg

Освойте зведені таблиці – і для вас стануть доступними багато облікових завдань. А завдяки використанню потужних програмних засобів первинні дані перетворяться на цінні управлінські відомості.

R2.jpg

Наприклад, ви отримали аркуш Excel із даними збуту вашої організації, яка містить сотні або навіть тисячі рядків. Звичайно, виникають різні питання стосовно значення цих даних. Як отримати відповіді на них?

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


Ви самі вирішуєте, які дані слід проаналізувати, і як їх упорядкувати. Замість однієї довільної форми, яка навряд чи відповідатиме вашим потребам, кожен звіт зведеної таблиці дає вам змогу поглянути на дані по-іншому, відразу відповісти на потрібні запитання.

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

  • за регіонами,
  • за продавцями,
  • за квартал або за місяць?

Чи підуть ваші справи краще, якщо ваші найбільш здібні працівники продаватимуть лише найпопулярніші продукти? Чи це залишить без прибутку цілі лінії продуктів?

Коли ви визначите запитання, Excel допоможе вам знайти відповіді на них. Для того, щоб побудувати зведену таблицю даних запам’ятайте послідовність ваших дій. Алгоритм побудови зведеної таблиці можна побудувати у вигляді такого малюнка:

Ц1.jpg

1. Поставте запитання.

2. Використайте майстер.

3. Створіть звіт зведеної таблиці.

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

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

R1.jpg

Слідуємо за алгоритмом побудови зведеної таблиці. Перш за все слід визначити, відповіді на які запитання ви отримати, скориставшись цими даними:

•На яку суму продав товарів кожен продавець?

•Які обсяги збуту в кожній країні?

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

Переходимо до другого кроку алгоритму побудови зведеної таблиці – викликаємо майстра. Клацніть на довільному місці в таблиці даних і запустіть майстер, вибравши в меню Дані команду Зведена таблиця.

Q1.jpg

Третій крок алгоритму побудови зведеної таблиці: Коли майстер відкриється, просто натисніть кнопку Готово, й нічого більше.

Q2.jpg

Можна витратити більше часу на роботу з майстром, але зараз це не обов'язково. Натиснення кнопки Готово повідомляє майстру, що йому слід діяти стандартним чином, а саме:

- використати дані зі списку Excel або з бази даних;

- підготувати область макета для створення звіту зведеної таблиці;

- використати усі дані списку;

- помістити область макета звіту на новий аркуш.

Запитання до класу: - Чому, на вашу думку, на даному етапі достатньо натискання на кнопці Готово, а не на кнопці Далі. - Порівняйте з майстром побудови діаграм.

Менш ніж за секунду майстер підготує новий аркуш з усім, що потрібно для створення зведеної таблиці з даних: списком полів зведеної таблиці, з якого перетягуються елементи, областю макета, до якої вони перетягуються, і панеллю інструментів «Зведена таблиця».

Q3.jpg

З цього кроку починається найскладніше – побудова макета зведеної таблиці. Для того, щоб це найскладніше перетворилося теж на просте, вам необхідно розібратися, що є чим у самому макеті і що з ним робити. Подивимося у праву частину макету, де написано списки полів зведеної таблиці. Запитання до класу: Чи є у таблиці Excel, яку ми розглядаємо, назви, які ви бачите у Списку полів зведеної таблиці? (Так, це назви стовпчиків. Кожному стовпцю вихідних даних відповідає поле з таким самим ім'ям). У Списку полів зведеної таблиці перелічено імена стовпців вихідних даних: «Країна», «Продавець», «Обсяг збуту», «Дата замовлення» та «Код замовлення». А тепер основне - перетягнення полів зі списку до області макета - ось як створюється подання зведеної таблиці.

Q4.jpg

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

Згадаємо, яке питання ми поставили перед собою перед створенням зведеної таблиці, які результати хочемо отримати? Мета складання нашого звіту - дізнатися, на яку суму продав товарів кожен продавець. Щоб отримати відповідь, ми використаємо два поля у списку полів: Продавець - для відображення прізвищ продавців, і Обсяг збуту - для відображення суми продажу для кожного продавця.

Після визначення використовуваних полів слід визначити, куди має бути перетягнуто ці поля. Щоб прізвище кожного продавця відображалося в окремому рядку, поле «Продавець» має опинитися в області «Перетягніть сюди поля рядків». Коли ви відпустите кнопку миші, у макеті з'являться ці прізвища.

Для відображення сум продажу для кожного продавця слід перетягти поле «Обсяг збуту» до області з написом «Перетягніть сюди елементи даних». До цієї області звичайно перетягуються числові дані, оскільки Excel автоматично підсумовує вміст цієї області. Підсумкові цифри відображаються у звіті. Коли поле «Обсяг збуту» буде перетягнуто до області для елементів даних, замість кольорових рамок області макета з'явиться готовий звіт зведеної таблиці.

Q5.jpg

Звіт готовий! Він містить 799 рядків відомостей, з яких можна відразу дізнатися, на яку суму продав товарів кожен продавець. Скористайтеся автоформатом меню Формат. Отримані відомості можна використати як завгодно: передати іншим користувачам, скопіювати до документа Word або опублікувати на веб-сторінці. Ще одна корисна функція зведеної таблиці – миттєва побудова зведеної діаграми. Достатньо знайти і натиснути на кнопці «Майстер діаграм зведеної таблиці»

Q6.jpg

Закріплення вивченого на уроці.

Ділова гра. Нехай існує певна міжнародна мережева компанія, яка займається збутом парфумерії. (Як приклад, ізраїльська компанія D-r Nona). Ви всі працівники аналітичного відділу компанії. (Пояснення терміну «аналітичного»). До вас надходить інформація від усіх менеджерів компанії, усіх відділень світу. Директор компанії (можливо, є учень, який цю тему вже вивчав раніше, то його можна назначити директором) вимагає надати йому звіт приблизно такого виду: щоб було видно усі продажі, по всім співробітникам, по всім регіонам. Обов’язковим є побудова діаграми. Максимальний бал - 9. Для оцінки 12 балів – додаткове завдання: визначити увесь обсяг збуту2 співробітників з України за 2003 рік. Файл Zavdannia з даними знаходиться у папці «Зведені таблиці». На інтерактивній дошці – пам’ятка і текст завдання. Для тих, хто не зрозумів процес створення зведеної таблиці, пропонуємо переглянути флеш-фільм по створенню зведеної таблиці, який знаходиться у папці з завданням (назва файлу creat_pivot_tables).

  • Учні виконують завдання.
  • Пояснення тими учнями, які першими виконали завдання.

Етап самоперевірки у вигляді тесту.

Тест: у папці «Зведені таблиці» є папка «Тест». Питання тесту:

1.Що потрібно зробити в першу чергу, щоб створити звіт зведеної таблиці?

  • відкрити майстер зведених таблиць

перетягти дані з аркуша до подання зведеної таблиці

  • вирішити, про що вам потрібно дізнатися

2.Розташування даних у зведеній таблиці ніколи не буває помилковим:

  • так
  • ні

3.Які з цих дій можна виконувати за допомогою подання зведеної таблиці?

  • Усі вищенаведені
  • Аналіз і підсумовування даних
  • Упорядкування та порівняння даних

4.Подання зведеної таблиці створюється шляхом перетягнення полів зі списку полів зведеної таблиці:

  • так
  • ні

5.Що таке поля зведеної таблиці?

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

6.Вказати на малюнку, за допомогою якої кнопки можна створити діаграму у зведеній таблиці.

Висновки уроку.

Зведені таблиці зручні при аналізі даних з кількох причин:

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

Етап оцінювання учнів.

Домашнє завдання.

З’ясувати, чим подібні і чим відрізняються зведені таблиці від сортування та фільтрації даних?

Пам’ятка для роботи зі зведеними таблицями

І. Підготовка даних Переконайтеся, що дані Excel добре структуровані. Акуратність має велике значення! Наприклад, перший рядок повинен містити назви стовпців, оскільки ці дані буде використано для назв полів у звіті. Переконайтеся, що кожен стовпець містить дані одного типу - наприклад, лише текст або лише числові значення. Видаліть усі автоматичні проміжні підсумки. Не турбуйтеся - зведена таблиця сама обчислює проміжні та кінцеві підсумки.

2. Виклик майстра. Виділіть на аркуші будь-яку клітинку з даними або всі дані та стовпці, які має бути включено до звіту. У меню Дані виберіть команду Зведена таблиця.

  • На кроці 1 майстра установіть перемикач база даних або аркуш Microsoft Office Excel.
  • У групі Тип звіту, який необхідно створити установіть перемикач зведена таблиця.
  • Натисніть кнопку Готово.

3. Майстер створить на аркуші область для звіту та відобразить Список полів зведеної таблиці з усіма доступними полями. Примітка. Якщо списку полів не видно, натисніть кнопку Показати список полів на панелі інструментів Зведена таблиця. Щоб знайти відповідь на запитання, виберіть поля у списку полів і визначте на макеті звіту місце для їх перетягнення. До області елементів даних звичайно перетягуються числові поля, тому що вони можуть автоматично підсумовуватися.

4. Увага! Зведені таблиці не є динамічними, тобто автоматично не обновлюються при зміні значень. Щоб оновити таблицю, заходимо в меню Дані, а далі – Обновити дані.

5. Не забудьте використати меню Автоформатування: меню Формат – Автоформат.