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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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