Зведену таблицю можна використовувати, щоб підбивати підсумки та аналізувати й переглядати зведені дані, а також представляти їх іншим. Зведені діаграми доповнюють зведені таблиці, даючи змогу візуалізувати дані зведеної таблиці, щоб швидко виявляти відношення, шаблони й тенденції. Зведені таблиці та зведені діаграми допомагають ухвалювати обґрунтовані рішення про важливі дані в організації. Крім того, можна підключатися до зовнішніх джерел даних, таких як таблиці SQL Server, куби SQL Server Analysis Services, Ринок Azure, файли зв’язку даних Office (ODC), XML-файли, бази даних Access і текстові файли, щоб створювати зведені таблиці або створювати на основі наявних зведених таблиць інші таблиці.
Якщо у вас є відповідні вимоги до ліцензії, ви можете попросити Copilot допомогти вам створити зведену таблицю або діаграму.
Відомості про зведені таблиці
Зведена таблиця – це інтерактивний спосіб швидко підбити підсумки великих обсягів даних. Зведену таблицю можна використовувати, щоб докладно аналізувати числові дані й знаходити відповіді на найрізноманітніші запитання про них. Зведену таблицю спеціально призначено для:
- виконання запитів до великих обсягів даних зручними способами;
- отримання проміжних підсумків і агрегації числових даних, зведення даних за категоріями й підкатегоріями та створення настроюваних обчислень і формул;
- розгортання та згортання рівнів даних для виокремлення результатів і деталізації потрібних зведених даних;
- перетворення рядків на стовпці або стовпців на рядки ("зведення" їх), завдяки чому можна переглядати різні зведення вихідних даних;
- фільтрування, сортування, групування й умовного форматування найкорисніших і найцікавіших підмножин даних для виділення виключно потрібної інформації;
- представлення лаконічних і привабливих інтерактивних або друкованих звітів із зауваженнями.
Наприклад, ліворуч наведено простий список витрат родини, а праворуч – зведену таблицю на основі цього списку:
| Дані про збут | Відповідна зведена таблиця |
|---|---|
|
|
Докладніше див. в статті Створення зведеної таблиці для аналізу даних аркуша.
Способи роботи зі зведеною таблицею
Коли ви створите зведену таблицю, виберете її джерело даних, упорядкуєте поля в списку полів зведеної таблиці й виберете початковий макет, ви зможете скористатися нею для виконання наведених нижче дій.
Дослідження даних. Ви зможете:
- розгортати та згортати дані, а також відображати базові докладні відомості, пов’язані зі значеннями;
- сортувати, фільтрувати й групувати поля та елементи;
- змінювати функції зведення та додавати настроювані обчислення та формули.
Змініть макет форми та впорядкування полів , виконавши такі дії:
- змінювати форму зведеної таблиці (стисла форма, структура або таблиця);
- додавати, перевпорядковувати й видаляти поля;
- змінювати порядок полів або елементів.
Щоб змінити макет стовпців, рядків і проміжних підсумків, зробіть ось що:
- активувати або вимикати заголовки полів стовпців і рядків, відображати чи приховувати пусті рядки;
- відображати проміжні підсумки над відповідними рядками або під ними;
- налаштовувати ширини стовпців під час оновлення;
- переміщати поля стовпця до області рядків або поля рядка до області стовпців;
- об’єднувати або розʼєднувати клітинки для елементів зовнішніх рядків і стовпців.
Щоб змінити відображення пустих клітинок і помилок , зробіть ось що:
- змінювати спосіб, яким відображаються помилки й пусті клітинки;
- змінювати спосіб, яким відображаються елементи й надписи без даних;
- відображати або приховувати пусті рядки.
Змініть формат, виконавши такі дії:
- форматувати клітинки й діапазони вручну, а також застосовувати умовне форматування;
- змінювати загальний стиль форматування зведеної таблиці;
- змінювати формат чисел для полів;
- включати форматування сервера OLAP.
Докладні відомості див. в статті СКонструювання макета й формату звіту зведеної таблиці.
Відомості про зведені діаграми
Зведені діаграми дають змогу графічно представити дані в пов’язаних зведених таблицях. Зведені діаграми також є інтерактивними. Коли створюється зведена діаграма, з’являється панель фільтра зведеної діаграми. Цю панель можна використовувати, щоб сортувати й фільтрувати відповідні дані зведеної діаграми. Зміни, внесені до макета й даних пов’язаної зведеної таблиці, миттєво відображаються на макеті й у даних зведеної діаграми й навпаки.
У зведених діаграмах відображаються ряди даних, категорії, позначки даних і осі, так само, як і на звичайних діаграмах. Крім того, можна змінити тип діаграми та інші параметри, наприклад заголовки, розташування легенди, підписи даних, розташування діаграми тощо.
Ось зведена діаграма на основі наведеного вище прикладу зведеної таблиці.
Докладні відомості див. в статті Створення зведеної діаграми.
Відмінності між зведеними та звичайними діаграмами
Якщо ви вже працювали зі звичайними діаграмами, більшість операцій у зведених діаграмах буде знайомою вам. Проте є кілька відмінностей:
Орієнтація рядка або стовпця На відміну від звичайної діаграми, змінити орієнтацію рядка або стовпця зведеної діаграми за допомогою діалогового вікна Вибір джерела даних не можна. Натомість ви можете звести підписи рядків і стовпців пов’язаної зведеної таблиці, щоб отримати такий самий результат.
Типи діаграм Зведену діаграму можна змінити на будь-який тип діаграми, крім точкової, біржової або бульбашкової.
Вихідні дані Standard діаграми зв'язані безпосередньо з клітинками аркуша, а зведені діаграми базуються на пов'язаному з ними джерелі даних зведеної таблиці. На відміну від стандартної діаграми, діапазон даних діаграми не можна змінити в діалоговому вікні Вибір джерела даних зведеної діаграми.
Форматування Більшість параметрів форматування, зокрема додані елементи діаграми, макет і стиль, зберігаються під час оновлення зведеної діаграми. Проте лінії тренду, підписи даних, планки похибок та інші зміни в наборах даних не зберігаються. У звичайних діаграмах це форматування не втрачається після застосування.
Хоча розмір підписів даних у зведеній діаграмі не можна явно змінити, це можна зробити, збільшивши розмір шрифту тексту.
Створення зведеної таблиці або зведеної діаграми на основі даних аркуша
Як основу для зведеної таблиці або зведеної діаграми можна використовувати дані з аркуша Excel. Дані мають бути у форматі списку з підписами стовпців у першому рядку, які програма Excel використовуватиме для імен полів. Кожна клітинка в наступних рядках має містити дані, які відповідають заголовку стовпця, і не слід змішувати типи даних в одному стовпці. Наприклад, не слід змішувати значення грошових одиниць і дати в одному стовпці. Крім того, у діапазоні даних не повинно бути пустих рядків або стовпців.
Таблиці Excel Таблиці Excel уже мають формат списку та чудово підходять для вихідних даних зведеної таблиці. Якщо оновити зведену таблицю, нові й оновлені дані з таблиці Excel автоматично включаються в операцію оновлення.
Використання динамічного іменованого діапазону Щоб спростити оновлення зведеної таблиці, можна створити динамічний іменований діапазон і використати це ім'я як джерело даних зведеної таблиці. Якщо іменований діапазон розширюється за рахунок нових даних, їх буде включено в разі оновлення зведеної таблиці.
Включення підсумків Excel автоматично створює проміжні й загальні підсумки у зведеній таблиці. Якщо вихідні дані містять автоматичні проміжні й загальні підсумки, створені за допомогою команди Проміжні підсумки в групі Структура на вкладці Дані, перш ніж створювати зведену таблицю, видаліть підсумки за допомогою цієї команди.
Використання зовнішнього джерела даних для створення зведеної таблиці або зведеної діаграми
Дані можна отримати із зовнішнього джерела даних, наприклад бази даних, куба OLAP (онлайнової аналітичної обробки) або текстового файлу. Наприклад, може існувати база даних із записами про збут, які потрібно підсумувати й проаналізувати.
Файли зв'язків даних Office Якщо для отримання зовнішніх даних для зведеної таблиці використовується файл зв'язку даних Office (ODC), дані можна вводити безпосередньо у зведену таблицю. Радимо отримувати зовнішні дані для звітів за допомогою ODC-файлів.
Вихідні дані OLAP Під час отримання вихідних даних із бази даних OLAP або файлу куба дані повертаються до програми Excel лише як зведена таблиця або зведена таблиця, перетворена на функції аркуша. Докладні відомості див. в статті Перетворення клітинок зведеної таблиці на формули аркуша.
Вихідні дані, відмінні від OLAP Це базові дані для зведеної таблиці або зведеної діаграми, які походять із джерела, відмінного від бази даних OLAP. Наприклад, дані з реляційних баз даних або текстових файлів.
Докладніше див. в статті Створення зведеної таблиці із зовнішнього джерела даних.
Використання іншої зведеної таблиці як джерела даних
Кеш зведеної таблиці Щоразу, коли ви створюєте зведену таблицю або зведену діаграму, Excel зберігає копію даних звіту в пам'яті та зберігає цю область сховища як частину файлу книги – це називається кешем зведеної таблиці. Для кожної нової зведеної таблиці потрібна додаткова пам'ять і дисковий простір. Однак, коли наявна зведена таблиця використовується як джерело для нової в одній книзі, обидва мають спільний доступ до одного кеша. Оскільки ви повторно користуєтеся кешем, розмір книги зменшується, а в пам'яті зберігається менше даних.
Вимоги до розташування Щоб використовувати одну зведену таблицю як джерело для іншої, обидві зведені таблиці мають бути в одній книзі. Якщо вихідну зведену таблицю розташовано в іншій книзі, скопіюйте вихідну таблицю до книги, у яку потрібно додати нову таблицю. Зведені таблиці та зведені діаграми в різних книгах не пов’язані між собою: кожна з них має власну копію даних у пам’яті та книгах.
Зміни впливають на обидві зведені таблиці Під час оновлення даних у новій зведеній таблиці програма Excel також оновлює дані у вихідній зведеній таблиці та навпаки. Коли ви групуєте елементи, скасовуєте їхнє групування або створюєте обчислювані поля чи обчислювані елементи в одній зведеній таблиці, це впливає на обидві таблиці. Якщо потрібні незалежні зведені таблиці, створіть нову таблицю на основі вихідного джерела даних, а не копіюйте вихідну зведену таблицю. Просто пам’ятайте, що якщо робити це надто часто, це вплине на обсяг вільної пам’яті.
Зведені діаграми Нову зведену таблицю або зведену діаграму можна створити на основі іншої зведеної таблиці, але нову зведену діаграму не можна створити безпосередньо на іншій зведеній діаграмі. Зміни у зведеній діаграмі впливають на пов’язану зведену таблицю та навпаки.
Змінення вихідних даних наявної зведеної таблиці
Якщо змінити вихідні дані, для аналізу можуть стати доступними інші дані. Наприклад, можна легко перейти з тестової бази даних до робочої. Можна доповнити зведену таблицю або зведену діаграму новими даними, подібними до відомостей, доступних за вихідним зв’язком із даними, перевизначивши вихідні дані. Якщо дані суттєво відрізняються через велику кількість нових або додаткових полів, може бути простіше створити нову зведену таблицю або зведену діаграму.
Відображення нових даних, введених під час оновлення Оновлення зведеної таблиці також може змінити дані, доступні для відображення. Для зведених таблиць на основі даних аркушів програма Excel отримує нові поля в межах вказаного вихідного або іменованого діапазону. Для звітів на основі зовнішніх даних програма Excel отримує нові дані, які відповідають певним критеріям для базового запиту або даних, що стають доступними в кубі OLAP. Ви можете переглянути будь-які нові поля в списку полів і додати поля до звіту.
Змінення створених кубів OLAP Звіти на основі даних OLAP завжди мають доступ до всіх даних у кубі. Якщо створити автономний куб із підмножиною даних у кубі сервера, можна скористатися командою Автономний режим OLAP, щоб змінити файл куба, який міститиме інші дані із сервера.
Додаткові відомості
Створення зведеної таблиці для аналізу даних аркуша
Використання зведених таблиць та інших інструментів бізнес-аналітики для аналізу даних