Під час консультації педагоги навчаться:

1. створити зведену таблицю і додати дані
2. налаштувати і форматувати
3. виконувати обчислення
4. сортувати і фільтрувати дані

Рахувати, змінювати, пам’ятати, аналізувати... А хочеться мати підсумкову інформацію, але при цьому не вписувати формул в Excel і не копіювати сто разів таблиці. Ще бачити б підсумки в динаміці, у розрізі...

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

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

Зведені звіти можна змінити в будь-який момент: додати, видалити, відфільтрувати дані.

Як створити

Відкрийте Excel.

Підготуйте вихідні дані

Відкрийте файл з готовою Excel-таблицею з вихідними даними або створіть її. Вона буде джерелом даних зведеної таблиці. Кожен стовпець обов’язково повинен мати короткий заголовок. У таблиці не повинно бути:

• об’єднаних комірок і рядків з підсумками;
• порожніх рядків або стовпців.

Відформатуйте таблицю з вихідними даними: виділити → «Головна» → «Форматувати як таблицю». Тоді дані зведеної таблиці автоматично змінюватимуться, якщо до вихідної таблиці вноситимете зміни.

Створіть зведену таблицю

Натисніть на будь-яку комірку таблиці → «Вставка» → «Зведена таблиця». Відкриється діалогове вікно з автоматично виділеним діапазоном даних (рис. 1). Виберіть, куди помістити зведену таблицю: на цьому самому аркуші чи на новому. Потім натисніть ОК.

Рис. 1. Створюємо зведену таблицю

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

Новий аркуш зі зведеною таблицею з’явиться ліворуч від аркуша з джерелом даних. Праворуч на ньому відображено область завдань «Поля зведеної таблиці». Угорі на стрічці — вкладки:

• «Параметри» — для роботи зі зведеною таблицею;
• «Конструктор» — для налаштування макета таблиці і вибору стилю оформлення.

Додайте дані у зведену таблицю

Натисніть на будь-якому місці зведеної таблиці, щоб відкрити список полів. Установіть позначки біля назв потрібних полів — дані з’являться на аркуші (рис. 2).

Рис. 2. Налаштовуємо поля та області зведеної таблиці

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

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

Область «Назви стовпців» виводить поля у вигляді стовпців зведеної таблиці. Важливо, щоб заголовки стовпців відображали інформацію в них.

Область «Фільтри звіту» призначена для фільтрації таблиці на основі обраних елементів фільтра (рис. 2). Додати дані в неї можна тільки вручну: перетягніть поле, щоб перемістити його з однієї області до іншої.

Як налаштувати

Якщо список полів зник з правої частини монітора, натисніть:

• на будь-яку комірку зведеної таблиці;
• на зведеній таблиці правою кнопкою миші → «Показати список полів».

Це інтерактивна гармошка.

Клацайте на її клавіші, аби прочитати текст

Як форматувати дані

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

Числове форматування

До зведеної таблиці за замовчуванням застосовано формат «Загальний». Щоб змінити формат комірок стовпчика з числами, натисніть правою кнопкою миші по комірці і виберіть «Числовий формат». Ця команда відкриває стандартне вікно «Формат комірок». Виберіть потрібний формат (числовий, грошовий, дата, час) і натисніть ОК.

Умовне форматування

Щоб в таблиці автоматично виділити кольором максимальні або мінімальні значення, виділіть числовий діапазон → «Головна» → «Умовне форматування» → «Правила відбору перших і останніх значень» → «Перші 10 елементів» або «Останні 10 елементів». Виберіть кількість комірок, які треба форматувати, колір заливки і тексту і натисніть ОК.

У меню інструмента «Умовне форматування» також є «Гістограми», які дадуть змогу графічно відобразити дані. Виділіть потрібні комірки, виберіть стиль заливки і колір (рис. 3).

Рис. 3. Форматуємо дані за допомогою гістограм

Візуалізуйте зведену таблицю за допомогою діаграми: «Параметри» → «Сервіс» → «Зведена діаграма».

Стильове форматування

Установіть курсор миші в будь-яку комірку зведеної таблиці. Натисніть «Конструктор» → «Стилі зведеної таблиці». Виберіть з галереї стиль. Якщо елементів бракує, додайте їх — позначте потрібні у групі «Параметри стилів зведеної таблиці».

Як виконувати обчислення

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

Щоб відобразити числові значення у відсотках або частках, натисніть праву кнопку миші і в пункті «Додаткові обчислення» виберіть потрібний варіант.

Як сортувати і фільтрувати дані

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

Сортування

Використовуйте сортування, щоб упорядкувати текстові дані в алфавітному або зворотному порядку, а числові — за зростанням або спаданням. За замовчуванням Excel сортує дані текстового стовпця в порядку від А до Я. Щоб змінити цей порядок або впорядкувати інші стовпці, натисніть правою кнопкою по стовпцю, виберіть потрібний вид сортування із пункту «Сортування».

Звичайний фільтр

Фільтр дасть змогу вибрати потрібні дані з-поміж усього діапазону.

Перший спосіб. Натисніть на кнопку автофільтра. У зведеній таблиці її відображено в комірках заголовків (знак стрілки вниз). Автофільтр дасть змогу побачити список унікальних значень стовпця. Є дві умови фільтрування:

• «Фільтр за підписом»;
• «Фільтр за значенням».

Серед них: більше, менше, дорівнює — для числових даних; містить, починається, закінчується — для текстових.

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

Другий спосіб. Перетягніть в область «Фільтр звіту» потрібне поле зі списку полів таблиці. У першому рядку аркуша в комірці B1 з’явиться список унікальних значень. використовуйте його для фільтрування даних (рис. 2).

Зріз

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

Натисніть «Параметри» → «Вставити зріз». Виберіть потрібні стовпці і натисніть ОК. На аркуші з’являться графічні області зі списками унікальних значень обраних стовпців (рис. 4). Ви можете перемістити зрізи або налаштувати їх оформлення на вкладці «Конструктор».

Рис. 4. Фільтруємо дані за допомогою зрізів

Вам потрібно дізнатися скільки працівників має 20 років страхового стажу. Сформуйте зріз за стовпцем «Стаж страховий», натисніть на цифру 20. У зведеній таблиці відобразиться, що троє працівників мають 20 років страхового стажу, один з яких працює в закладі освіти за сумісництвом.

Змінюйте критерії зрізу та отримуйте іншу комбінацію даних. Щоб вибрати кілька значень для фільтрування, використовуйте клавіші Shift для сусідніх варіантів і Ctrl — для несусідніх.

Як оновити або видалити зведену таблицю

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

• правою кнопкою миші і виберіть «Оновити»;
• вкладку «Параметри» і виберіть «Оновити».

Ці способи не підійдуть, якщо у вихідній таблиці змінилася кількість стовпців або рядків. У цьому випадку використовуйте інструмент «Джерело даних» на вкладці «Параметри». Відкриється вікно, у якому потрібно заново виділити діапазон з даними.

+

Щоб видалити таблицю, виділіть її та натисніть клавішу Delete. Якщо з’явиться вікно «Не вдається змінити цю частину звіту зведеної таблиці», то ви вибрали не всю таблицю. Для виділення використовуйте поєднання клавіш Ctrl + A або інструмент «Виділити» на вкладці «Параметри». Якщо таблиця на окремому аркуші — видаліть його.

У статті використано версію Microsoft Excel 2010.

Якщо давненько користувались програмою, то освіжити знання про роботу в ній дасть змогу стаття «Як оптимізувати роботу в Excel: підказки користувача» у № 8/2018 журналу. Немає цього номера? Зазирніть на ezavdnz.mcfr.ua. Там є архів номерів, починаючи з 2015 року.

А потім...