змест
Пастаноўка задачы
У якасці зыходных даных мы маем файл Excel, дзе адзін з лістоў змяшчае некалькі табліц з дадзенымі аб продажах наступнага выгляду:
Звярніце ўвагу, што:
- Табліцы розных памераў і з рознымі наборамі прадуктаў і рэгіёнаў у радках і слупках без сартавання.
- Паміж табліцамі можна ўстаўляць пустыя радкі.
- Колькасць сталоў можа быць любым.
Два важныя здагадкі. Мяркуецца, што:
- Над кожнай табліцай у першым слупку пазначана прозвішча кіраўніка, продажы якога ілюструе табліца (Іваноў, Пятроў, Сідараў і інш.)
- Назвы тавараў і рэгіёнаў ва ўсіх табліцах пішуцца аднолькава – з дакладнасцю да рэгістра.
Канчатковая мэта складаецца ў тым, каб сабраць дадзеныя з усіх табліц у адну плоскую нармалізаваную табліцу, зручную для наступнага аналізу і пабудовы зводкі, гэта значыць у гэтую:
Крок 1. Падключыцеся да файла
Давайце створым новы пусты файл Excel і абярэм яго на ўкладцы Дата Каманда Атрымаць даныя – з файла – з кнігі (Дадзеныя — З файла — З рабочай кнігі). Паказваем размяшчэнне зыходнага файла з дадзенымі аб продажах, а затым у акне навігатара выбіраем патрэбны нам ліст і націскаем на кнопку Пераўтварэнне дадзеных (Пераўтварэнне дадзеных):
У выніку ўсе дадзеныя з яго павінны быць загружаныя ў рэдактар Power Query:
Крок 2. Прыбярыце смецце
Выдаліць аўтаматычна створаныя крокі мадыфікаваны тып (Зменены тып) и Прыпаднятыя загалоўкі (Рассунутыя загалоўкі) і пазбавіцца ад пустых радкоў і радкоў з вынікамі з дапамогай фільтра нуля и РАЗАМ па першым слупку. У выніку атрымліваем наступную карціну:
Крок 3. Даданне кіраўнікоў
Каб потым разумець, дзе чые продажу, трэба дадаць у нашу табліцу слупок, дзе ў кожным радку будзе адпаведнае прозвішча. Для гэтага:
1. Дадамо дапаможны слупок з нумарамі радкоў з дапамогай каманды Дадаць слупок – Слупок індэксу – з 0 (Дадаць слупок — Слупок індэкс — Ад 0).
2. Дадайце слупок з формулай з дапамогай каманды Даданне слупка - Карыстальніцкі слупок (Дадаць слупок — карыстальніцкі слупок) і ўвядзіце там наступную канструкцыю:
Логіка гэтай формулы простая - калі значэннем наступнай ячэйкі ў першым слупку з'яўляецца «Прадукт», то гэта азначае, што мы натыкнуліся на пачатак новай табліцы, таму адлюстроўваем значэнне папярэдняй ячэйкі з дапамогай імя кіраўніка. У адваротным выпадку мы нічога не адлюстроўваем, гэта значыць нуль.
Каб атрымаць бацькоўскую ячэйку з прозвішчам, спачатку звяртаемся да табліцы з папярэдняга кроку #"Індэкс дададзены", а затым паказваем імя патрэбнага нам слупка [Калонка 1] у квадратных дужках і нумар ячэйкі ў гэтым слупку ў фігурных дужках. Нумар ячэйкі будзе на адзінку менш бягучага, які бярэм з слупка індэксАдпаведна.
3. Засталося запоўніць пустыя вочкі нуля імёны з вышэйшых клетак з камандай Transform – Fill – Down (Пераўтварэнне — Запаўненне — Уніз) і выдаліць больш не патрэбны слупок з індэксамі і радкі з прозвішчамі ў першым слупку. У выніку атрымліваем:
Крок 4. Групоўка ў асобныя табліцы па кіраўніках
Наступны крок - згрупаваць радкі для кожнага кіраўніка ў асобныя табліцы. Для гэтага на ўкладцы Transformation выкарыстоўвайце каманду Group by (Transform – Group By) і ў якое адкрылася акне абярыце слупок Manager і аперацыю All rows (Усе радкі), каб проста сабраць даныя без прымянення якой-небудзь функцыі агрэгавання. іх (сума, сярэдняя і інш.). П.):
У выніку атрымліваем асобныя табліцы для кожнага кіраўніка:
Крок 5: Пераўтварэнне ўкладзеных табліц
Зараз прывядзем табліцы, якія ляжаць у кожнай вочку атрыманага слупка Усе дадзеныя у годнай форме.
Спачатку выдаліце слупок, які больш не патрэбны ў кожнай табліцы Менеджэр. Выкарыстоўваем яшчэ раз Карыстальніцкі слупок таб Пераўтварэнне (Пераўтварэнне — карыстальніцкі слупок) і наступная формула:
Затым з дапамогай іншага вылічанага слупка мы падымаем першы радок кожнай табліцы да загалоўкаў:
І, нарэшце, выконваем галоўнае пераўтварэнне - разгортванне кожнай табліцы з дапамогай М-функцыі Table.UnpivotOtherColumns:
Назвы рэгіёнаў з шапцы пяройдуць у новы слупок і мы атрымаем больш вузкую, але ў той жа час даўжэйшую нармалізаваную табліцу. Пустыя вочкі с нуля ігнаруюцца.
Пазбавіўшыся ад непатрэбных прамежкавых слупкоў, мы маем:
Крок 6 Разгарніце ўкладзеныя табліцы
Засталося разгарнуць усе нармалізаваныя ўкладзеныя табліцы ў адзіны спіс з дапамогай кнопкі з падвойнымі стрэлкамі ў загалоўку слупка:
... і мы нарэшце атрымліваем тое, што хацелі:
Вы можаце экспартаваць атрыманую табліцу назад у Excel з дапамогай каманды Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у…).
- Стварыце табліцы з рознымі загалоўкамі з некалькіх кніг
- Збор дадзеных з усіх файлаў у дадзенай тэчцы
- Збор дадзеных з усіх аркушаў кнігі ў адну табліцу