Съдържание
Формулиране на проблема
Като входни данни имаме Excel файл, където един от листовете съдържа няколко таблици с данни за продажбите в следната форма:
Отбележи, че:
- Таблици с различни размери и различни набори от продукти и региони в редове и колони без никакво сортиране.
- Между таблиците могат да се вмъкват празни редове.
- Броят на масите може да бъде произволен.
Две важни предположения. Предполага се, че:
- Над всяка таблица, в първата колона, има името на мениджъра, чиито продажби илюстрира таблицата (Иванов, Петров, Сидоров и др.)
- Наименованията на стоките и регионите във всички таблици се изписват по един и същ начин – с голяма точност.
Крайната цел е данните от всички таблици да се съберат в една плоска нормализирана таблица, удобна за последващ анализ и изграждане на обобщение, т.е. в тази:
Стъпка 1. Свържете се с файла
Нека създадем нов празен Excel файл и го изберете в раздела Дата Команда Вземете данни – От файл – От книга (Данни — От файл — От работна книга). Посочете местоположението на изходния файл с данни за продажбите и след това в прозореца на навигатора изберете листа, от който се нуждаем, и щракнете върху бутона Конвертиране на данни (Трансформиране на данни):
В резултат на това всички данни от него трябва да бъдат заредени в редактора на Power Query:
Стъпка 2. Почистете боклука
Изтрийте автоматично генерираните стъпки модифициран тип (Променен тип) и Повишени заглавки (Популяризирани заглавки) и се отървете от празни редове и редове с общи суми с помощта на филтър нула и ОБЩО от първата колона. В резултат на това получаваме следната картина:
Стъпка 3. Добавяне на мениджъри
За да разберем по-късно къде чии са продажбите, е необходимо да добавим колона към нашата таблица, където във всеки ред ще има съответното фамилно име. За това:
1. Нека добавим помощна колона с номера на редове с помощта на командата Добавяне на колона – Индексна колона – От 0 (Добавяне на колона — Индексна колона — От 0).
2. Добавете колона с формула с командата Добавяне на колона – Персонализирана колона (Добавяне на колона — Персонализирана колона) и въведете следната конструкция там:
Логиката на тази формула е проста – ако стойността на следващата клетка в първата колона е „Продукт“, това означава, че сме попаднали на началото на нова таблица, така че показваме стойността на предишната клетка с име на управителя. В противен случай не показваме нищо, т.е. нула.
За да получим родителската клетка с фамилното име, първо препращаме към таблицата от предишната стъпка #"Добавен индекс", и след това посочете името на колоната, от която се нуждаем [Колона 1] в квадратни скоби и номера на клетката в тази колона във къдрави скоби. Номерът на клетката ще бъде с една по-малък от текущия, който вземаме от колоната индекс, Съответно.
3. Остава да попълните празните клетки с нула имена от по-високи клетки с командата Трансформиране – Запълване – Надолу (Трансформиране — Запълване — Надолу) и изтрийте вече ненужната колона с индекси и редове с фамилни имена в първата колона. В резултат на това получаваме:
Стъпка 4. Групиране в отделни таблици по ръководители
Следващата стъпка е да групирате редовете за всеки мениджър в отделни таблици. За да направите това, в раздела Трансформация използвайте командата Групиране по (Трансформиране – Групиране по) и в прозореца, който се отваря, изберете колоната Мениджър и операцията Всички редове (Всички редове), за да събирате просто данни, без да прилагате никаква агрегираща функция към тях (сума, средна стойност и т.н.). П.):
В резултат на това получаваме отделни таблици за всеки мениджър:
Стъпка 5: Трансформирайте вложени таблици
Сега даваме таблиците, които се намират във всяка клетка на получената колона Всички данни в приличен вид.
Първо изтрийте колона, която вече не е необходима във всяка таблица Мениджър. Използваме отново Персонализирана колона етикет Трансформация (Трансформиране — Персонализирана колона) и следната формула:
След това с друга изчислена колона повдигаме първия ред във всяка таблица до заглавията:
И накрая, извършваме основната трансформация – разгъване на всяка таблица с помощта на M-функцията Table.UnpivotOtherColumns:
Имената на регионите от заглавката ще преминат в нова колона и ще получим по-тясна, но в същото време по-дълга нормализирана таблица. Празни клетки с нула се игнорират.
Отървавайки се от ненужните междинни колони, имаме:
Стъпка 6 Разгънете вложени таблици
Остава да разширите всички нормализирани вложени таблици в един списък, като използвате бутона с двойни стрелки в заглавката на колоната:
... и най-накрая получаваме това, което искахме:
Можете да експортирате получената таблица обратно в Excel с помощта на командата Начало — Затвори и зареди — Затвори и зареди в… (Начало — Затвори&Зареди — Затвори&Зареди в...).
- Създавайте таблици с различни заглавки от множество книги
- Събиране на данни от всички файлове в дадена папка
- Събиране на данни от всички листове на книгата в една таблица