Зведені таблиці традиційно побудовано за допомогою кубів OLAP та інших складних джерел даних, які вже мають функціональні зв'язки між таблицями. Однак у Excel можна імпортувати кілька таблиць і створювати власні зв'язки між таблицями. Хоча ця гнучкість потужна, вона також дає змогу легко об'єднувати не пов'язані дані, що призводять до дивних результатів.
Чи створили ви таку зведену таблицю? Ви мали створити ієрархічну групу покупок за регіонами, а потім розірвати поле "Сума покупки" в область "Значення" та перепустити поле регіону збуту в область "Підписи стовпців". Але результати неправильні.
Як виправити це?
Проблема полягає в тому, що поля, додані до зведеної таблиці, можуть бути в одній книзі, але таблиці, які містять кожен стовпець, не пов'язані між собою. Наприклад, у вас може бути таблиця з переліком кожного регіону збуту, а в іншій – покупки для всіх регіонів. Щоб створити зведену таблицю та отримати правильні результати, потрібно створити зв'язок між двома таблицями.
Після створення зв'язку зведена таблиця правильно поєднує дані з таблиці покупок зі списком регіонів, а результати виглядають так:
Excel 2013 р. містить технології, розроблені Microsoft Research (MSR) для автоматичного виявлення й виправлення подібних проблем зв'язків.
Використання автоматичного виявлення
Автоматичне виявлення перевіряє нові поля, додані до книги, яка містить зведену таблицю. Якщо нове поле не пов'язано із заголовками стовпців і рядків зведеної таблиці, в області сповіщень у верхній частині зведеної таблиці з'явиться повідомлення про те, що може знадобитися зв'язок. Excel також проаналізує нові дані, щоб знайти потенційні зв'язки.
Ви можете й надалі ігнорувати повідомлення та працювати зі зведеною таблицею. однак якщо натиснути кнопку Створити,алгоритм працюватиме й аналізуватиме дані. Залежно від значень у нових даних, розміру та складності зведеної таблиці та вже створених зв'язків цей процес може тривати до кількох хвилин.
Цей процес складається з двох етапів:
-
Виявлення зв'язків. Після завершення аналізу можна переглянути список запропонованих зв'язків. Якщо цього не зробити, Excel автоматично перейде до наступного кроку зі створення зв'язків.
-
Створення зв'язків. Коли зв'язки буде застосовано, з'явиться діалогове вікно підтвердження, і ви зможете вибрати посилання Відомості, щоб переглянути список створених зв'язків.
Процес виявлення можна скасувати, але скасувати його не можна.
Алгоритм MSR шукає найкращий набір зв'язків, щоб з'єднати таблиці в моделі. Алгоритм виявляє всі можливі зв'язки для нових даних, враховуючи імена стовпців, типи даних стовпців, значення в стовпцях і стовпці у зведених таблицях.
Excel потім вибирає рівень із найвищим показником якості, який визначається внутрішньою евристичністю. Докладні відомості див. в огляді зв'язків і Усунення несправностей.
Якщо автоматичне виявлення не надає правильні результати, можна редагувати зв'язки, видаляти їх або створювати нові вручну. Докладні відомості див. в статті Створення зв'язків між двома таблицями або Створення зв'язків у поданні схеми.
Пусті рядки у зведених таблицях (невідомий елемент)
Оскільки зведена таблиця об'єднує пов'язані таблиці даних, якщо будь-яка таблиця містить дані, які не можуть бути пов'язані з ключем або відповідним значенням, їх потрібно якимось чином обробити. У багатовимірних базах даних спосіб обробки невідповідних даних – це призначати всі рядки без відповідних значень невідомому елементу. У зведеній таблиці невідомий елемент відображається як пустий заголовок.
Наприклад, якщо ви створюєте зведену таблицю, яку потрібно згрупувати за сховищем, але деякі записи в таблиці збуту не мають в списку імен магазинів, усі записи без припустимого імені сховища згруповано.
Якщо у вас є пусті рядки, у вас є два варіанти. Ви можете визначити працюючи зі зв'язком між таблицями (наприклад, створити ланцюжок зв'язків між кількома таблицями) або видалити поля зі зведеної таблиці, через які можуть виникнути пусті рядки.