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