Зведені таблиці в Excel – це ефективний інструмент для обчислення, відомості та аналізу даних, який полегшує пошук порівнянь, закономірностей та тенденцій. З їх допомогою можна за лічені секунди перетворити мільйон рядків даних на короткий звіт. Крім швидкого підбиття підсумків, зведені таблиці дозволяють буквально «на льоту» змінювати спосіб аналізу шляхом перетягування полів з однієї області звіту в іншу.
У цій статті розберемося, навіщо потрібні зведені таблиці. На конкретному прикладі покажемо, як їх створити, налаштувати та використовувати.
Створення зведеної таблиці в Excel
Щоб зведена таблиця спрацювала коректно, важливо дотриматися кількох вимог до вихідних даних:
- у кожного стовпця вихідної таблиці має бути заголовок;
- у кожному стовпці застосовується лише один формат – текст, число, дата;
- відсутність порожніх клітинок та рядків.
- уникайте повторення груп у вигляді стовпців. Наприклад, усі дати мають бути в одному полі, а не розбиті по місяцях в окремих стовпцях.
Якщо умови дотримані, тоді виділяємо будь-яку клітинку таблиці та переходимо на вкладку «Вставлення». Зліва на стрічці знаходяться дві кнопки: «Зведена таблиця» і «Рекомендовані зведені таблиці».
Якщо Ви не знаєте, яким чином організувати наявні дані, можна скористатися командою «Рекомендовані зведені таблиці». Excel на основі ваших даних покаже мініатюри можливих макетів. Обираєте відповідний варіант і зведена таблиця готова. Залишається її лише довести до потрібного вигляду.
Оскільки стандартна заготівля зведеної таблиці навряд чи збігається з вашими бажаннями, то в такому разі краще побудувати зведену таблицю самостійно з нуля. Для цього натискаєте кнопку «Зведена таблиця». З’явиться вікно, де потрібно вказати 2 значення:
- вихідний діапазон (якщо активувати будь-яку клітинку таблиці, тоді Excel визначиться сам);
- місце розташування майбутньої зведеної таблиці (за замовчуванням буде обрано новий аркуш).
Зазвичай нічого міняти тут не потрібно. Після натискання кнопки OK буде створено новий аркуш Excel із порожнім макетом зведеної таблиці. Для зручності можна одразу перейменувати його.
Зліва на аркуші розташована область, де з’явиться зведена таблиця після налаштувань. Праворуч – панель «Поля зведеної таблиці», до яких ми будемо вносити ці налаштування. Розберемося, як скористатися цією панеллю.
Налаштування зведеної таблиці
У верхній частині панелі налаштувань знаходиться блок із переліком можливих полів зведеної таблиці. Поля взято із заголовків стовпців вихідної таблиці. Якщо до макета потрібно додати якесь поле, то для цього достатньо поставити позначку навпроти назви і програма сама визначить, де має бути розміщене це поле. Однак Excel не завжди вгадує, тому поле краще перетягнути мишею в потрібне місце макета.
Нижня частина панелі налаштувань складається з чотирьох областей – «Значення», «Рядки», «Стовпці» та «Фільтри». Кожна область має свою функцію:
- Значення – проводить обчислення на основі вибраних даних з вихідної таблиці і відносить результати до зведеної таблиці. За замовчуванням Excel підсумовує вибрані дані, але можна вибрати інші дії. Наприклад, розрахувати середнє значення, показати мінімум чи максимум, перемножити.
Якщо дані вибраного поля у числовому форматі, програма підсумує їх значення (наприклад, розрахує загальну вартість проданих товарів). Якщо текстовий формат даних – програма покаже кількість клітинок (наприклад, визначить кількість проданих товарів). - Рядки та Стовпці – відповідають за візуальне розташування полів у зведеній таблиці. Якщо вибрати рядки, поля розмістяться рядками. Якщо вибрати стовпці, поля розмістяться по стовпцях.
- Фільтри – відповідають за фільтрацію підсумкових даних у зведеній таблиці. Після побудови зведеної таблиці панель фільтрів з’являється окремо від неї. У ній можна вибрати, які дані потрібно показати у зведеній таблиці, а які приховати. Наприклад, можна показувати продаж лише одного з менеджерів або тільки за обраний період.
За допомогою додавання та видалення полів у зазначені області, ви за лічені секунди зможете налаштувати будь-який зріз ваших даних, який забажаєте.
Подивимося, як це працює у дії. Створимо зведену таблицю, у якій треба відобразити звіт про продажі всіх товарів по областях. Для цього мишкою перетягнемо в область Значення поле «Виторг», в область Рядки перетягнемо поле «Область», в Стовпці – «Товар». В результаті ми отримуємо справжню зведену таблицю, яка згрупувала всі області за типами товарів, які були продані.
Робота зі зведеними таблицями в Excel
Змінити існуючу зведену таблицю також легко. Подивимося, як побажання директора легко втілюються у реальність. Замінимо виторг на прибуток та подивимося результат.
Налаштовуємо фільтри зведеної таблиці
Щоб можна було фільтрувати інформацію зведеної таблиці, потрібно перенести поля в область «Фільтри». У нашому прикладі перетягнемо туди поля «Менеджер» та «Місто». Може бути цікаво: використання автофільтру в Excel.
У блоці фільтрів натисніть на стрілку праворуч від поля «Менеджер». У вікні встановіть позначку навпроти бажаного менеджера. Якщо потрібно виділити кілька значень, встановіть позначку «Вибрати кілька елементів». Натисніть кнопку ОК.
Тепер зведена таблиця показує лише продаж конкретного менеджера. Щоб знову показати таблицю у повному обсязі, потрібно в тому ж блоці очистити встановлений фільтр. Фільтри можна вибирати та видаляти як вам зручно – залежно від того, яку інформацію ви хочете побачити у зведеній таблиці.
Додаткові обчислення у зведеній таблиці
Зараз у нашій зведеній таблиці всі продажі менеджерів відображаються у гривнях. Припустимо, нам потрібно зрозуміти, який відсоток продажів кожного продавця у загальному обсязі. Можна розрахувати це вручну, а можна скористатися додатками зведених таблиць.
Спершу перенастроюємо належним чином поля таблиці. Далі клацніть правою кнопкою на будь-яке значення ціни в таблиці. Виберіть параметр «Відображати значення як», а потім «Відсоток від загальної суми».
Тепер замість цін у гривнях відображаються відсотки: який відсоток кожен проданий товар склав від загальної суми продажів всієї фірми для конкретного менеджера. Відсотки навпроти прізвищ менеджерів – їхній загальний відсоток продажу.
Можна згорнути подробиці з переліками товарів, натиснувши на знак «–» ліворуч від прізвища менеджера. Тоді таблиця стане коротшою і надасть дані, хто з менеджерів попрацював краще.
Щоб значення знову виражалися у гривнях – через праву кнопку миші повертаємось до «Відображати значення як» та вибираємо «Без обчислення».
Оновлення даних у зведеній таблиці Excel
Якщо змінити джерело (наприклад, додати нові рядки або змінити дані), зведена таблиця не зміниться, тому потрібно буде оновити їх вручну.
Щоб дані зведеної таблиці теж оновилися, переходимо на вкладку «Аналізувати», натискаємо кнопку «Оновити». Також можна скористатися командою «Дані» – «Оновити все» або знайти цю команду шляхом натискання правої кнопки миші по зведеній таблиці.
Так зроблено спеціально через те, що зведена таблиця займає багато місця в оперативній пам’яті. Щоб витрачати ресурси комп’ютера більш економно, робота йде безпосередньо з джерелом, і з кешем, де знаходиться моментальний знімок вихідних даних.
В кінці пару слів по Google таблиці. Щоб використовувати зведені таблиці в Google Таблицях, потрібно перейти на вкладку «Вставити» та обрати параметр «Зведена таблиця». Подальший хід дій такий самий, як і в Excel: вибрати діапазон таблиці та аркуш, на якому її потрібно побудувати. Перейшовши на створений аркуш, у вікні «Редактор зведеної таблиці» вказати всі необхідні настройки.
Тепер, знаючи, як зробити зведені таблиці Excel навіть на такому базовому рівні, ви зможете в рази збільшити швидкість і якість обробки великих масивів даних.
