Садржај
Формулисање проблема
Као улазне податке имамо Екцел фајл, где један од листова садржи неколико табела са подацима о продаји следећег облика:
Напоменути да:
- Табеле различитих величина и са различитим скуповима производа и региона у редовима и колонама без икаквог сортирања.
- Празне линије се могу уметнути између табела.
- Број столова може бити било који.
Две важне претпоставке. Претпоставља се да:
- Изнад сваке табеле, у првој колони, налази се име менаџера чију продају табела илуструје (Иванов, Петров, Сидоров итд.)
- Називи роба и региона у свим табелама су написани на исти начин – са прецизношћу.
Крајњи циљ је прикупљање података из свих табела у једну равну нормализовану табелу, погодну за накнадну анализу и прављење резимеа, односно у овој:
Корак 1. Повежите се са датотеком
Хајде да направимо нову празну Екцел датотеку и изаберите је на картици Датум команда Преузмите податке – из датотеке – из књиге (Подаци — Из датотеке — Из радне свеске). Одредите локацију изворне датотеке са подацима о продаји, а затим у прозору навигатора изаберите лист који нам је потребан и кликните на дугме Претвори податке (Трансформисање података):
Као резултат, сви подаци из њега треба да се учитају у Повер Куери едитор:
Корак 2. Очистите смеће
Избришите аутоматски генерисане кораке модификованог типа (промењен тип) и Повишена заглавља (Промовисана заглавља) и ослободите се празних линија и редова са укупним вредностима користећи филтер нула и УКУПНО по првој колони. Као резултат, добијамо следећу слику:
Корак 3. Додавање менаџера
Да бисмо касније разумели где је чија продаја, потребно је нашој табели додати колону где ће у сваком реду бити одговарајуће презиме. За ово:
1. Додајмо помоћну колону са бројевима редова помоћу команде Додај колону – Колона индекса – Од 0 (Додај колону — Колона индекса — Од 0).
2. Додајте колону са формулом помоћу команде Додавање колоне – Прилагођена колона (Додај колону — Прилагођена колона) и тамо увести следећу конструкцију:
Логика ове формуле је једноставна – ако је вредност следеће ћелије у првој колони „Производ“, онда то значи да смо наишли на почетак нове табеле, па приказујемо вредност претходне ћелије са име управника. У супротном, не приказујемо ништа, тј. нулл.
Да бисмо добили родитељску ћелију са презименом, прво се позивамо на табелу из претходног корака #”Индекс је додат”, а затим наведите име колоне која нам је потребна [колона1] у угластим заградама и број ћелије у тој колони у витичастим заградама. Број ћелије ће бити за један мањи од тренутног, који узимамо из колоне индекс, редом.
3. Остаје да попуните празне ћелије са нула имена из виших ћелија са командом Трансформација – Попуна – Доле (Трансформација — Попуна — Доле) и брисати више непотребну колону са индексима и редове са презименима у првој колони. Као резултат, добијамо:
Корак 4. Груписање у засебне табеле по менаџерима
Следећи корак је груписање редова за сваког менаџера у засебне табеле. Да бисте то урадили, на картици Трансформација користите команду Групирај по (Трансформ – Гроуп Би) и у прозору који се отвори изаберите колону Менаџер и операцију Сви редови (Сви редови) да бисте једноставно прикупили податке без примене било какве функције агрегирања на њих (збир, просек, итд.). П.):
Као резултат, добијамо засебне табеле за сваког менаџера:
Корак 5: Трансформишите угнежђене табеле
Сада дајемо табеле које леже у свакој ћелији резултујуће колоне Svi podaci у пристојном стању.
Прво, избришите колону која више није потребна у свакој табели менаџер. Поново користимо Прилагођена колона табулатор Трансформација (Трансформација — Прилагођена колона) и следећу формулу:
Затим, са другом израчунатом колоном, подижемо први ред у свакој табели на наслове:
И на крају, вршимо главну трансформацију – отварамо сваку табелу помоћу М-функције Табле.УнпивотОтхерЦолумнс:
Имена региона из заглавља ће ићи у нову колону и добићемо ужу, али у исто време, дужу нормализовану табелу. Празне ћелије са нула се игноришу.
Ослобађајући се непотребних средњих колона, имамо:
Корак 6 Проширите угнежђене табеле
Остаје да проширите све нормализоване угнежђене табеле у једну листу помоћу дугмета са двоструким стрелицама у заглављу колоне:
… и коначно добијамо оно што смо желели:
Можете да извезете резултујућу табелу назад у Екцел користећи команду Почетна — Затвори и учитај — Затвори и учитај у… (Почетна — Затвори&Учитај — Затвори&Учитај у…).
- Направите табеле са различитим заглављима из више књига
- Прикупљање података из свих датотека у датој фасцикли
- Прикупљање података са свих листова књиге у једну табелу