1 of 35

Консолідація даних. Зведені таблиці.�

За навчальною програмою 2018 року

10

2 of 35

Консолідація даних. Зведені таблиці

Розділ 2 § 10

Як швидко узагальнити результати фінансової діяльності відділів фірми, що розміщені на окремих аркушах?

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

10

3 of 35

Консолідація даних. Зведені таблиці

Розділ 2 § 10

Як швидко узагальнити результати різних етапів спортивних змагань, що розміщені на окремих аркушах?

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

10

4 of 35

Консолідація даних

Розділ 2 § 10

Розглянемо консолідацію результатів з етапів спортивних змагань, що розміщені на окремих аркушах.

10

5 of 35

Консолідація даних

Розділ 2 § 10

Алгоритм консолідації:

Створіть новий аркуш та перейменуйте його на Результати.

Впишіть у комірку А1 аркуша Результати заголовок підсумкової таблиці Результати змагань.

10

6 of 35

Консолідація даних

Розділ 2 § 10

(Продовження…) Алгоритм консолідації:

Виділіть на аркуші Результати комірку А2 і на вкладці Дані у групі Знаряддя даних натисніть кнопку Консолідація.

2

1

10

7 of 35

Консолідація даних

Розділ 2 § 10

(Продовження…) Алгоритм консолідації:

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

У вікні Консолідація зі списку Функція виберіть функцію Сума.

10

8 of 35

Консолідація даних

Розділ 2 § 10

(Продовження…) Алгоритм консолідації:

Сформуйте список діапазонів:

установіть курсор в полі Посилання, перейдіть на аркуш І етап і виділіть діапазон А2:В10; натисніть кнопку Додати;

повторіть ці дії для аркушів II етап і III етап.

10

9 of 35

Консолідація даних

Розділ 2 § 10

(Продовження…) Алгоритм консолідації:

Натисніть ОК. Вигляд підсумкової таблиці наведено на рисунку.

10

10 of 35

Зведені таблиці

Розділ 2 § 10

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

За допомогою зведених таблиць можна швидко опрацьовувати великі обсяги інформації:

відфільтровувати

порівнювати

групувати дані

Це значно полегшує працю багатьом фахівцям (менеджерам, соціологам та ін.).

10

11 of 35

Зведені таблиці

Розділ 2 § 10

Алгоритм побудови зведеної таблиці в Excel такий.

Виділіть будь-яку комірку всередині таблиці і виберіть команду Вставлення ⇒ Зведена таблиця.

1

2

10

12 of 35

Зведені таблиці

Розділ 2 § 10

(Продовження…) Побудова зведеної таблиці в Excel:

У вікні Зведена таблиця натисніть кнопку Готово. До книги буде додано новий аркуш, на якому буде розміщено макет створюваної зведеної таблиці, список полів та панель інструментів зведеної таблиці. Макет таблиці формується

шляхом перетягування назв полів до вікон панелі інструментів.

10

13 of 35

Зведені таблиці

Розділ 2 § 10

(Продовження…) Побудова зведеної таблиці в Excel:

Панель інструментів містить чотири вікна.

10

14 of 35

Зведені таблиці

Розділ 2 § 10

(Продовження…) побудова зведеної таблиці в Excel:

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

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

до вікна Стовпці потрібно перетягти назви полів, за якими проводитиметься групування і підведення підсумків. Значення полів будуть заголовками стовпців у зведеній таблиці.

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

10

15 of 35

Зведені таблиці

Розділ 2 § 10

Дано таблицю, що містить дані про площу й населення деяких країн:

10

16 of 35

Зведені таблиці

Розділ 2 § 10

Побудуємо зведену таблицю з фільтрацією даних за Західною та Східною півкулями.

Для цього до вікна Фільтр звіту перетягнемо назву Півкуля, оскільки потрібно групувати країни за півкулями.

10

17 of 35

Зведені таблиці

Розділ 2 § 10

До вікна Ряди перетягнемо назви полів Частина світу і Країна. Ці назви стали заголовками рядків у зведеній таблиці, причому зберігається ієрархія відносин між полями: рядки Країна вкладені в рядки вищого рівня Частина світу.

Підсумки (сумарні значення площі та населення) підводяться за частинами світу.

10

18 of 35

Зведені таблиці

Розділ 2 § 10

Вигляд звіту за групою країн, розташованих у Західній півкулі, наведено на рисунку.

10

19 of 35

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

Розділ 2 § 10

Під час створення зведеної таблиці програма Excel автоматично застосовує до неї один з автоформатів. Вибрати інший стиль таблиці можна на вкладці Конструктор група інструментів Стилі зведеної таблиці.

1

2

10

20 of 35

Оновлення даних

Розділ 2 § 10

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

10

21 of 35

Оновлення даних

Розділ 2 § 10

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

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

клацнути на кнопці Оновити, що на вкладці Аналізувати група інструментів Дані.

1

2

10

22 of 35

Переміщення і редагування�поля Значення

Розділ 2 § 10

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

10

23 of 35

Переміщення і редагування�поля Значення

Розділ 2 § 10

Щоб вставити нове поле в зведену таблицю, потрібно:

вивести на екран панель інструментів Зведені таблиці;

клацнути на кнопці Відобразити поля, якщо частина панелі, що містить кнопки з назвами полів даних, прихована;

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

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

10

24 of 35

Зміна функції обчислення�загальних підсумків

Розділ 2 § 10

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

для розрахунку в полях з числовими значеннями

полях з іншими даними

Функцію

суми

Функцію

підрахунку

кількості

значень

10

25 of 35

Зміна функції обчислення�загальних підсумків

Розділ 2 § 10

Надалі ці функції можна змінити, скориставшись кнопками розкриття списків панелі Обчислення (панелі інструментів Зведені таблиці.

1

2

10

26 of 35

Зміна функції обчислення�загальних підсумків

Розділ 2 § 10

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

10

27 of 35

Зміна функції обчислення�загальних підсумків

Розділ 2 § 10

За даними зведеної таблиці можна побудувати зведену діаграму.

Для побудови використовується весь макет зведеної таблиці. На діаграмі відображаються кнопки полів зведеної таблиці.

10

28 of 35

Зміна функції обчислення�загальних підсумків

Розділ 2 § 10

Ці кнопки мають сірий колір і мають кнопки розкриття списків.

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

10

29 of 35

Зміна функції обчислення�загальних підсумків

Розділ 2 § 10

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

Такі таблиці дозволяють швидко сформувати різні звіти за одними й тими самими даними в розрізі різних критеріїв. Крім того, ці звіти можна гнучко налаштовувати і оновлювати в зв'язку зі змінами таблиці — джерела.

10

30 of 35

Розгадайте ребус

Консолідація

Розділ 2 § 10

10

31 of 35

Питання для самоперевірки

Розділ 2 § 10

Поясніть алгоритм консолідації даних з декількох списків.

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

Коли слід застосовувати зведені таблиці?

10

32 of 35

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

Проаналізувати

§ 10, ст. 54-61

Розділ 2 § 10

10

33 of 35

Працюємо за комп’ютером

Практична робота 1

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

Розділ 2 § 10

10

34 of 35

Працюємо за комп’ютером

Розділ 2 § 10

Сторінка

60-61

10

35 of 35

Дякую за увагу!

За навчальною програмою 2018 року

Урок 10

10