содржина
Формулирање на проблемот
Како влезни податоци, имаме датотека Excel, каде што еден од листовите содржи неколку табели со податоци за продажба од следната форма:
Забележи го тоа:
- Табели со различни големини и со различни групи производи и региони во редови и колони без никакво сортирање.
- Помеѓу табелите може да се вметнат празни линии.
- Бројот на табели може да биде кој било.
Две важни претпоставки. Се претпоставува дека:
- Над секоја табела, во првата колона стои името на менаџерот чија продажба ја илустрира табелата (Иванов, Петров, Сидоров итн.)
- Имињата на стоките и регионите во сите табели се напишани на ист начин - со точност на букви.
Крајната цел е да се соберат податоци од сите табели во една рамна нормализирана табела, погодна за последователна анализа и градење резиме, т.е. во оваа:
Чекор 1. Поврзете се со датотеката
Ајде да создадеме нова празна датотека Excel и да ја избереме на јазичето податоци Команда Добијте податоци – од датотека – од книга (Податоци - Од датотека - Од работна книга). Наведете ја локацијата на изворната датотека со податоци за продажба, а потоа во прозорецот на навигаторот изберете го листот што ни треба и кликнете на копчето Конвертирај податоци (Трансформирајте податоци):
Како резултат на тоа, сите податоци од него треба да се вчитаат во уредувачот на Power Query:
Чекор 2. Исчистете го ѓубрето
Избришете ги автоматски генерираните чекори модифициран тип (Променет тип) и Покачени заглавија (Промовирани заглавија) и ослободете се од празни линии и линии со збирки користејќи филтер нула и ВКУПНО од првата колона. Како резултат, ја добиваме следната слика:
Чекор 3. Додавање менаџери
За подоцна да разбереме каде е чија продажба, неопходно е да се додаде колона на нашата табела, каде што во секој ред ќе има соодветно презиме. За ова:
1. Ајде да додадеме помошна колона со броеви на линии користејќи ја командата Додадете колона – Индекс колона – од 0 (Додај колона — Индекс колона — од 0).
2. Додадете колона со формула со командата Додавање колона – Прилагодена колона (Додај колона - Прилагодена колона) и воведете ја следната конструкција таму:
Логиката на оваа формула е едноставна - ако вредноста на следната ќелија во првата колона е „Производ“, тогаш тоа значи дека наидовме на почеток на нова табела, па ја прикажуваме вредноста на претходната ќелија со име на управителот. Во спротивно, не прикажуваме ништо, односно нула.
За да ја добиеме матичната ќелија со презиме, прво се повикуваме на табелата од претходниот чекор #„Додаден индекс“, а потоа наведете го името на колоната што ни треба [Колона 1] во квадратни загради и бројот на ќелијата во таа колона во кадрави загради. Бројот на ќелијата ќе биде еден помал од сегашниот, што го земаме од колоната индекс, соодветно.
3. Останува да се пополнат празните ќелии со нула имиња од повисоки ќелии со командата Трансформирај – Пополни – Надолу (Трансформирајте - Пополнете - Надолу) и избришете ја веќе непотребната колона со индекси и редови со презимиња во првата колона. Како резултат, добиваме:
Чекор 4. Групирање во посебни табели по менаџери
Следниот чекор е да се групираат редовите за секој менаџер во посебни табели. За да го направите ова, на табулаторот Трансформација, користете ја командата Group by (Transform – Group By) и во прозорецот што се отвора, изберете ја колоната Менаџер и операцијата Сите редови (Сите редови) за едноставно собирање податоци без примена на некоја агрегирана функција на нив (збир, просек, итн.). П.):
Како резултат на тоа, добиваме посебни табели за секој менаџер:
Чекор 5: Трансформирајте ги вгнездените табели
Сега ги даваме табелите што лежат во секоја ќелија од добиената колона Сите податоци во пристојна форма.
Прво, избришете колона што повеќе не е потребна во секоја табела Менаџер. Повторно користиме Прилагодена колона табот Трансформација (Трансформирајте - приспособена колона) и следнава формула:
Потоа, со друга пресметана колона, го подигаме првиот ред во секоја табела до насловите:
И, конечно, ја извршуваме главната трансформација - расклопувајќи ја секоја табела користејќи ја функцијата M Табела.UnpivotOtherColumns:
Имињата на регионите од заглавието ќе влезат во нова колона и ќе добиеме потесна, но во исто време, подолга нормализирана табела. Празни ќелии со нула се игнорираат.
Ослободувајќи се од непотребните средни колони, имаме:
Чекор 6 Проширете ги вгнездените табели
Останува да се прошират сите нормализирани вгнездени табели во една листа користејќи го копчето со двојни стрелки во заглавието на колоната:
... и конечно го добиваме она што го сакавме:
Можете да ја извезете добиената табела назад во Excel користејќи ја командата Дома — Затвори и вчитај — Затвори и вчитај во… (Дома - Затвори&Вчитај - Затвори&Вчитај до…).
- Изградете табели со различни заглавија од повеќе книги
- Собирање податоци од сите датотеки во дадена папка
- Собирање податоци од сите листови од книгата во една табела