ЛПР Зведені таблиці
Теоретичний мінімум

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

  • є таблиця з великою кількістю рядків (записів) ;
  • необхідно провести аналіз даних, який вимагає вибірки (фільтрування) даних, їх групування (сумування, підрахунки), та представлення даних в різних розрізах (підготовка звітів);
  • аналіз складно проводити на основі вихідних таблиць з використанням інших засобів: фільтри, формули, розширені фільтри.
Підготовка зведеної таблиці (вимоги до вихідної таблиці):
  • кожен стовпчик (поле) повинен мати заголовок;
  • в кожен стовпчик повинні вводитись дані тільки в одному форматі (наприклад, стовпчик Постачальник – назва компанії тільки в текстовому форматі, а Код постачальника – в числовому форматі);
  • в таблиці не повинно бути незаповнених рядків чи стовпців;
  • в таблицю вводяться значення, які не можна рознести в різні стовпці (наприклад, в одну комірку не можна вводити «Місто, назва вулиці, будинок №_». Потрібно створити ТРИ однойменні стовпчики);
  • уникати таблиць з неправильною структурою
Замість того, щоб створювати стовпці, що повторюються (регіон1, регіон2, …), в яких буде велика кількість порожніх комірок, потрібно створювати раціональнішу таблицю.

Виконання лабораторно-практичної роботи
  1. Запустити програму MS Excel (найбільш зручним для себе способом), або ж скористатися версією online (потрібен обліковий запис Microsoft)
  2. Створити нову книгу.
    На аркуші книги створити таблицю, що містить дані про продажі автомобілів трьома гіпотетичними фірмами (Альфа, Бета і Гама).
    -

    Провести необхідне форматування (зовнішню лінію таблиці зробити товстішою, «шапку» таблиці залити сірим кольором (сірий - 15%)), та окреслити товстішою лінією).
    Назвати лист Продажі і зберегти книгу, надавши ім’я – ЛПР Зведені таблиці (на РОБОЧИЙ стіл).

  3. Виділити комірку таблиці (наприклад, В3), та перейти на вкладку Вставка, і в групі Таблиці натиснути Зведені таблиці
    -

  4. У діалоговому вікні Створення зведеної таблиці вибрати діапазон даних для аналізу і місце розташування зведеної таблиці (на новому аркуші).
    -

  5. Для створення таблиці, заповнити макет зведеної таблиці в панелі Список полів зведеної таблиці, відмітити або ж перенести поля таблиці до відповідних області:
    • поле Марка - в Назви рядків
    • поле Фірма - в Назви стовпців
    • поле Ціна - в область Значення.
    Отримати:
    -

  6. Сформулювати запитання, на яке відповідає створена зведена таблиця, та записати його (зошит, доступний текстовий редактор, ...).

  7. Створити копію аркуша зведена1, та перейменувати його на зведена2. Отримати:
    -

  8. Ознайомитися із групою вкладок Знаряддя для зведених таблиць. Змінити функцію підсумовування по полю Ціна на Середнє значення,
    Група вкладок Робота зі зведеними таблицями ➔ вкладка Аналізівати ➔ група Активне поле ➔ кнопка Параметри
    -

    В діалоговому вікні Параметри значення поля вказати що Зводити значення полів за - Середнє, та натиснути ОК
    -

    Отримати:
    -

    9. Примітка.
      Цю ж дію можна виконати натиснувши ПКМ на комірці з числовим значенням (B5), та обрати з контекстного меню команду Звести значення за
      -

  9. Створити копію аркуша зведена2, та перейменувати його на зведена3
  10. Змінити орієнтацію рядків і стовпців таблиці, користуючись списками що розкриваються в областях панелі Список полів зведеної таблиці
    - ➔ -

  11. Створити копію аркуша зведена3, та перейменувати його на зведена4
  12. Змінити форматування числових значень зведеної таблиці, застосувавши грошовий формат (Робота зі зведеними таблицями ➔ вкладка Аналізівати ➔ група Активне поле ➔ кнопка Параметри).
    - ➔ -

    Застосувати на власний вибір стилі до зведеної таблиці (Робота зі зведеними таблицями ➔ вкладка Конструктор ➔ група Стилі зведеної таблиці)
    Отримати (можна як на зразку):
    -

  13. Створити копію аркуша зведена4, та перейменувати його на зведена5
  14. Приховати заголовки полів (Робота зі зведеними таблицями ➔ вкладка Аналізувати ➔ група Показати)
    -

  15. Побудувати діаграму, що відображає дані зведеної таблиці (Робота зі зведеними таблицями ➔ вкладка Аналізувати ➔ група Знаряддя ➔ Зведена діаграма).
    -

  16. Створити копію аркуша зведена3, та перейменувати його на зведена6
    Сформувати зведену таблицю, що дозволяє оцінити кількість продажів і суму продажів трьох фірм різних марок авто.
    Отримати:
    -

  17. Створити копію аркуша зведена3, та перейменувати його на зведена7
    Сформувати зведену таблицю, що дозволяє оцінити суму продажів і дати продажів трьох фірм різних марок авто.
    Отримати:
    -
    Проаналізувати дані таблиці

  18. Створити копію аркуша зведена7, та перейменувати його на зведена8
    Згрупувати дані по місяцях, потім по кварталах, потім по місяцях і кварталах (Робота зі зведеними таблицями ➔ вкладка Аналізувати ➔ група Група ➔ Групувати поле)
    -

    Отримати:
    -

    -

    -
    Проаналізувати дані таблиці

  19. Створити нову зведену таблицю, яка буде відображати сумарну ціну і кількість проданих кожної фірмою автомобілів в залежності від року випуску автомобіля і дати продажу
    Отримати:
    -

  20. Згрупувати дані про роки випуску з кроком в три роки
    Отримати:
    -

  21. Створити зведену таблицю в якій показати поле Сума, грн.
    Для його створення:
    1. Відкрити вікно Обчислювальне поле (Робота зі зведеними таблицями ➔ вкладка Аналізувати ➔ група Обчислення ➔ Обчислювальне поле)
    2. Додати ім'я полю Сума в грн., а в поле Формула ввести формулу =37,7 * Ціна, $
      -
    3. Натиснути ОК.

    Провести форматування, і отримати:
    -

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

  • * Додаткове завдання для роботи із ЗВЕДЕНИМИ ТАБЛИЦЯМИ
    Для завантаження умови додаткового завдання натиснути ТУТ.