Огляд зведених таблиць і зведених діаграм

Зведену таблицю можна використовувати, щоб підбивати підсумки та аналізувати й переглядати зведені дані, а також представляти їх іншим. Зведені діаграми доповнюють зведені таблиці, даючи змогу візуалізувати дані зведеної таблиці, щоб швидко виявляти відношення, шаблони й тенденції. Зведені таблиці та зведені діаграми допомагають ухвалювати обґрунтовані рішення про важливі дані в організації. Крім того, можна підключатися до зовнішніх джерел даних, таких як таблиці SQL Server, куби SQL Server Analysis Services, Ринок Azure, файли зв’язку даних Office (ODC), XML-файли, бази даних Access і текстові файли, щоб створювати зведені таблиці або створювати на основі наявних зведених таблиць інші таблиці.

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

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

  • виконання запитів до великих обсягів даних зручними способами;

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

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

  • перетворення рядків на стовпці або стовпців на рядки ("зведення" їх), завдяки чому можна переглядати різні зведення вихідних даних;

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

  • представлення лаконічних і привабливих інтерактивних або друкованих звітів із зауваженнями.

Наприклад, ліворуч наведено простий список витрат родини, а праворуч – зведену таблицю на основі цього списку:

Дані про витрати родини

Відповідна зведена таблиця

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



Зразок зведеної таблиці: "Категорії" в розділі "Рядки" та "Місяці" в розділі "Стовпці"

Докладніше див. в статті Створення зведеної таблиці для аналізу даних аркуша.

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

Дослідження даних   . Ви зможете:

  • розгортати та згортати дані, а також відображати базові докладні відомості, пов’язані зі значеннями;

  • сортувати, фільтрувати й групувати поля та елементи;

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

Змінення форми макета й упорядкування полів   . Ви зможете:

  • змінювати форму зведеної таблиці (стисла форма, структура або таблиця);

  • додавати, перевпорядковувати й видаляти поля;

  • змінювати порядок полів або елементів.

Змінення макета стовпців, рядків і проміжних підсумків   . Ви зможете:

  • активувати або вимикати заголовки полів стовпців і рядків, відображати чи приховувати пусті рядки;

  • відображати проміжні підсумки над відповідними рядками або під ними;

  • налаштовувати ширини стовпців під час оновлення;

  • переміщати поля стовпця до області рядків або поля рядка до області стовпців;

  • об’єднувати або розʼєднувати клітинки для елементів зовнішніх рядків і стовпців.

Налаштування відображення пустих клітинок і помилок   . Ви зможете:

  • змінювати спосіб, яким відображаються помилки й пусті клітинки;

  • змінювати спосіб, яким відображаються елементи й надписи без даних;

  • відображати або приховувати пусті рядки.

Змінення формату   . Ви зможете:

  • форматувати клітинки й діапазони вручну, а також застосовувати умовне форматування;

  • змінювати загальний стиль форматування зведеної таблиці;

  • змінювати формат чисел для полів;

  • включати форматування сервера OLAP.

Докладні відомості див. в статті СКонструювання макета й формату звіту зведеної таблиці.

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

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

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

Приклад зведеної діаграми Excel

Докладні відомості див. в статті Створення зведеної діаграми.

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

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

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

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

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

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

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

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

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

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

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

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

Використання зовнішнього джерела даних для створення зведеної таблиці або зведеної діаграми

Дані можна отримати із зовнішнього джерела даних, наприклад бази даних, куба OLAP (онлайнової аналітичної обробки) або текстового файлу. Наприклад, може існувати база даних із записами про збут, які потрібно підсумувати й проаналізувати.

Файли зв’язку з даними Office.    Якщо для отримання зовнішніх даних для зведеної таблиці використовується файл зв’язку з даними Office (ODC), можна вводити дані безпосередньо у зведену таблицю. Радимо отримувати зовнішні дані для звітів за допомогою ODC-файлів.

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

Вихідні дані, відмінні від OLAP.    Це дані для зведеної таблиці або зведеної діаграми, які надходять із джерела, відмінного від бази даних OLAP (наприклад, дані з реляційних баз даних або текстових файлів).

Докладніше див. в статті Створення зведеної таблиці із зовнішнього джерела даних.

Використання іншої зведеної таблиці як джерела даних

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

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

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

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

Змінення вихідних даних наявної зведеної таблиці

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

Відображення нових даних, отриманих після оновлення.    Коли зведена таблиця оновлюється, дані, доступні для відображення, також можуть змінитися. Для зведених таблиць на основі даних аркушів програма Excel отримує нові поля в межах вказаного вихідного або іменованого діапазону. Для звітів на основі зовнішніх даних програма Excel отримує нові дані, які відповідають певним критеріям для базового запиту або даних, що стають доступними в кубі OLAP. Переглянути нові поля та додати поля до звіту можна в списку полів.

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

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel UserVoice.

Див. також

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

Створення зведеної діаграми

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

Використання зведених таблиць та інших інструментів бізнес-аналітики для аналізу даних

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×