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