✅ Подготовка на изходни данни за обобщена таблица

Съдържание

Какъв е добрият дизайн за изходните данни за обобщена таблица?
Общи капани, които трябва да се избягват при подготовката на изходните данни
Примери за дизайн на лоши източници на данни
[ИЗСЛЕДВАНЕ НА СЛУЧАЙ] Преобразуване на лошо форматирани данни в готови източници за готови за обобщена таблица

Наличието на данните в правилния формат е решаваща стъпка при създаването на стабилна и без грешки обобщена таблица. Ако не бъде направено по правилния начин, в крайна сметка можете да имате много проблеми с вашата обобщена таблица.

Какъв е добрият дизайн за изходните данни за обобщена таблица?

Нека да разгледаме пример за добри изходни данни за обобщена таблица.

Ето какво го прави добър дизайн на изходни данни:

Първият ред съдържа заглавки, които описват данните в колоните.
Всяка колона представлява уникална категория данни. Например, колона C съдържа само данни за продуктите и само колона D и данни за месец.
Всеки ред е запис, който би представлявал един екземпляр от сделката или продажбата.
Заглавките на данните са уникални и не се повтарят никъде в набора от данни. Например, ако имате номера на продажбите за четири тримесечия за една година, НЕ трябва да назовавате всички тези като продажби. Вместо това дайте на тези заглавки на колони уникални имена като Sales Q1, Sales Q2 и т.н.
- Ако нямате уникални заглавия, можете да продължите и да създадете обобщена таблица и Excel автоматично ще ги направи уникални, като добавите суфикс (като Sales, Sales2, Sales3). Това обаче би бил ужасен начин за подготовка и използване на обобщена таблица.

Общи капани, които трябва да се избягват при подготовката на изходните данни

В изходните данни не трябва да има празни колони. Тази е лесна за забелязване. Ако имате празна колона в изходните данни, няма да можете да създадете обобщена таблица. Той ще покаже грешка, както е показано по -долу.
В изходните данни не трябва да има празни клетки/редове. Въпреки че можете успешно да създадете обобщена таблица, въпреки че имате празни клетки или редове, има много странични ефекти, които могат да ви ухапят по-късно през деня.
- Да приемем например, че имате празна клетка в колоната за продажби. Ако създадете обобщена таблица с помощта на тези данни и поставите полето за продажби в областта на колоните, тя ще ви покаже COUNT, а не SUM. Това е така, защото Excel интерпретира цялата колона като текстови данни (само поради една празна клетка).
Приложете подходящ формат към клетки в изходните данни. Например, ако имате дати (които се съхраняват като серийни номера в задната част на Excel), приложете един от приемливите формати за дата. Това ще ви помогне да създадете обобщена таблица и да използвате Дата като един от критериите за обобщаване, групиране и сортиране на данните.
- Ако имате няколко секунди, опитайте това. Форматирайте датите във вашата обобщена таблица като числа и след това създайте обобщена таблица, използвайки тези данни. Сега в обобщената таблица изберете полето за дата и вижте какво се случва. Той автоматично ще го постави в областта със стойности. Това е така, защото вашата обобщена таблица не знае, че това са дати. Той ги интерпретира като числа.
Не включвайте никакви суми на колони, суми на редове, средни стойности и т.н. като част от изходните данни. След като имате обобщена таблица, можете лесно да ги получите по -късно.
Винаги създавайте таблица на Excel и след това я използвайте като източник за обобщена таблица. Това е по -скоро добра практика, а не клопка. Вашата обобщена таблица би работила добре с изходни данни, които също не са таблица на Excel. Предимството на Excel Table е, че може да коригира разширяващите се данни. Ако добавите още редове към набора от данни, не е нужно да коригирате изходните данни отново и отново. Можете просто да опресните обобщената таблица и тя автоматично да отчита новите редове, добавени към изходните данни.

Примери за дизайн на лоши източници на данни

Нека да разгледаме някои лоши примери за дизайн на изходни данни.

Лош дизайн на изходни данни - пример 1

Това е често срещан начин за поддържане на данни, тъй като е лесен за проследяване и разбиране. Има два проблема с това подреждане на данни:

Не получавате пълна картина. Например, можете да видите продажбите за Средния Запад в 1 -ви тримесечие са 2924300. Но дали това е единична продажба или редица продажби. Ако имате всеки запис на разположение в отделен ред, можете да направите по -добър анализ.
Ако продължите и създадете обобщена таблица, като използвате това (което можете), ще получите различни полета за различни тримесечия. Нещо, както е показано по -долу:

Лош дизайн на изходни данни - пример 2

Това представяне на данни може да бъде прието добре от ръководството и публиката на презентации на PowerPoint, но не е подходящо за създаване на обобщена таблица.

Отново това е вид резюме, което лесно можете да създадете с помощта на обобщена таблица. Така че, дори ако в крайна сметка искате такъв вид на вашите данни, поддържайте изходните данни във формат, готов за създаване, и създайте този изглед с помощта на обобщена таблица.

Лош дизайн на изходни данни - пример 3

Това отново е изход, който лесно може да бъде получен с помощта на обобщена таблица. Но не може да се използва за създаване на обобщена таблица.

В набора от данни има празни клетки и четвъртинките са разпределени като заглавки на колони.

Също така, регионът е посочен в горната част, докато той трябва да бъде част от всеки запис.

[ИЗСЛЕДВАНЕ НА СЛУЧАЙ] Преобразуване на лошо форматирани данни в готови източници за готови за обобщена таблица

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

Ето пример за лош дизайн на данни:

Сега можете да използвате Excel функции или Pivot Query, за да конвертирате тези данни във формат, който може да се използва като изходни данни за обобщена таблица.

Нека видим как работят и двата метода.

Метод 1: Използване на формули на Excel

Нека да видим как да използваме функциите на Excel, за да преобразуваме тези данни във формат, готов за обобщена таблица.

Създайте уникален заглавие на колона за всички категории в оригиналния набор от данни. В този пример това ще бъде регион, тримесечие и продажби.
В клетката под заглавката Region използвайте следната формула: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Плъзнете формулата надолу и тя ще повтори всички региони.
В клетката под заглавката Quarter използвайте следната формула: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1)+0,1) , 0))
- Плъзнете формулата надолу и тя ще повтори всички тримесечия.
В заглавката под продажбите използвайте следната формула: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Плъзнете го надолу, за да получите всички стойности. Тази формула използва данните за регион и тримесечие като стойности за търсене и връща стойността на продажбите от първоначалния набор от данни.

Сега можете да използвате тези получени данни като изходни данни за обобщена таблица.

Щракнете тук, за да изтеглите примерния файл.

Метод 2: Използване на Power Query

Power Query има функция, която лесно може да преобразува този вид данни във формат за данни, готов за Pivot.

Ако използвате Excel 2016, функциите на Power Query ще бъдат налични в раздела Данни в групата Получаване и преобразуване. Ако използвате Excel 2013 или предишни версии, можете да го използвате като добавка.

Ето едно отлично ръководство за Инсталиране на Power Query от Jon от Excel Campus.

Отново, като се има предвид, че имате форматирани данни, както е показано по -долу:

Ето стъпките за преобразуване на изходните данни във формат, готов за обобщена таблица:

Преобразувайте данните в таблица на Excel. Изберете набора от данни и отидете на Вмъкване -> Таблици -> Таблица.
В диалоговия прозорец Вмъкване на таблица се уверете, че е избран правилният диапазон и щракнете върху OK. Това ще преобразува табличните данни в таблица на Excel.
В Excel 2016 отидете на Data -> Get & Transform -> From Table.
- Ако използвате добавката Power Query в предишна версия, отидете на Power Query -> Външни данни -> От таблица.
В редактора на заявки изберете колоните, които искате да разгънете. В случая това са тези за четирите тримесечия. За да изберете всички колони, задръжте клавиша Shift и след това изберете първата колона и последната колона.
В редактора на заявки отидете на Transform -> Any Column -> Unpivot Columns. Това ще преобразува данните на колоната в удобен формат за обобщена таблица.
Power Query дава общи имена на колоните. Променете тези имена на тези, които искате. В този случай променете атрибута на тримесечие и стойността на продажбите.
В редактора на заявки отидете на Файл -> Затваряне и зареждане. Това ще затвори диалоговия прозорец Power Query Editor и ще създаде отделен работен лист, който ще съдържа данните с непроменени колони.