Підготовка чистих таблиць для моделі даних

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

Тому що модель даних менш поблажлива, ніж стандартна зведена таблиця. Коли ви завантажуєте таблицю в Power Pivot і визначаєте зв'язки, кожна структурна проблема у вихідних даних стає проблемою моделі. Рядки заголовків заважають коректному завантаженню таблиці. Порожні рядки всередині даних ускладнюють зв'язки. Дати, збережені як текст, повністю порушують обчислення на основі часу. Числові стовпці, відформатовані як текст, дають суму, що дорівнює нулю.

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

Таблиця готова для використання в Моделі даних, якщо вона відповідає всім наступним умовам:

Не потрібно запам’ятовувати цей список. Використовуйте його як контрольний перелік перед завантаженням будь-якої таблиці в Модель даних.

Перетворення у іменовану таблицю Excel

Після очищення даних останнім кроком є перетворення діапазону у формальну таблицю Excel та надання їй імені. Це не є опціональним — Power Pivot працює лише з іменованими таблицями Excel, а не зі звичайними діапазонами.

• Клацніть будь-де всередині очищеного діапазону даних;
• Перейдіть до Вставлення → Таблиця;
• Переконайтеся, що прапорець Моя таблиця з заголовками встановлено. Натисніть OK;
• На вкладці Конструктор таблиць знайдіть поле Ім’я таблиці зліва;
• Замініть стандартне ім’я (Table1, Table2 тощо) на змістовне — наприклад: Sales, Customers або Products.

Правила іменування:

• За можливості використовуйте одне описове слово: Sales, а не SalesData2025;
• Без пробілів. Якщо потрібно два слова, використовуйте PascalCase: SalesOrders, а не Sales Orders.
• Уникайте загальних імен на кшталт Table1 або Sheet2 — вони неінформативні у списку полів Power Pivot.

Іменована таблиця автоматично розширюється при додаванні нових рядків, що означає — оновлені дані потрапляють у модель без необхідності змінювати діапазони.

Завдання

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

Крок 1 — Виправлення структури

Відкрийте аркуш Messy_Export і виправте такі структурні проблеми:

1. Видаліть рядки із заголовками та всі порожні рядки над справжнім рядком заголовків, щоб рядок 1 став рядком заголовків стовпців.
2. Знайдіть і видаліть усі порожні рядки всередині даних. Щоб швидко їх знайти, використовуйте Ctrl+G/Fn+F5 → Special → Blanks.
3. Видаліть рядок TOTAL внизу даних.

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

Крок 2 — Виправлення типів даних

1. Виділіть стовпець Order Date. Застосуйте єдиний короткий формат дати (DD/MM/YYYY). Будь-яке значення, яке залишилося вирівняним по лівому краю після форматування, зберігається як текст — виправте ці клітинки за допомогою функції DATEVALUE() або Text-to-Columns (Data → Text to Columns → Finish).
2. Перевірте стовпці Quantity, Unit Price та Total. Якщо якісь значення вирівняні по лівому краю, вони зберігаються як текст. Виправте їх за допомогою Paste Special → Multiply by 1 або функції VALUE().

Після виконання цих дій усі дати повинні бути вирівняні по правому краю та мати однаковий формат, а всі числові стовпці — вирівняні по правому краю.

Крок 3 — Перетворення на іменовану таблицю Excel

1. Клацніть будь-де всередині чистого діапазону даних.
2. Перейдіть до Insert → Table. Переконайтеся, що прапорець "My table has headers" встановлено. Натисніть OK.
3. На вкладці Table Design перейменуйте таблицю з типового імені (Table1 або подібне) на Sales.