Направите табеле са различитим заглављима из више књига

Формулисање проблема

Имамо неколико датотека (у нашем примеру - 4 комада, у општем случају - колико желите) у једној фасцикли Извештаји:

Унутра, ове датотеке изгледају овако:

У чему:

• Лист са подацима који нам је потребан увек се зове Фотографије, али може бити било где у радној свесци.
  
• Иза листа Фотографије Свака књига може имати друге листове.
• Табеле са подацима имају различит број редова и могу почети другим редом на радном листу.
• Имена истих колона у различитим табелама могу се разликовати (нпр. Количина = Количина = Количина).
• Колоне у табелама могу се поредати различитим редоследом.

Задатак: прикупити податке о продаји из свих фајлова са листа Фотографије у једну заједничку табелу да би се на њој накнадно изградио резиме или било која друга аналитика.

Корак 1. Припрема директоријума имена колона

Прво што треба да урадите је да припремите референтну књигу са свим могућим опцијама за називе колона и њихову исправну интерпретацију:

Конвертујемо ову листу у динамичку „паметну“ табелу користећи дугме Форматирај као табелу на картици Početna (Почетна — Формат као табела) или пречица на тастатури Цтрл+T и учитајте га у Повер Куери помоћу команде Подаци – из табеле/опсега (Подаци — из табеле/опсега). У новијим верзијама Екцел-а, преименован је у Са лишћем (Са листа).

У прозору Повер Куери уређивача упита традиционално бришемо корак Промењен тип и додајте нови корак уместо њега кликом на дугме f_xу траци формуле (ако није видљиво, можете га омогућити на картици преглед) и тамо унесите формулу у уграђеном Повер Куери језику М:

=Табле.ТоРовс(извор)

Ова команда ће конвертовати ону учитану у претходном кораку извор референтну табелу у листу која се састоји од угнежђених листа (Листа), од којих је свака, пак, пар вредности Било је-постало из једног реда:

Ова врста података ће нам требати мало касније, када ћемо масовно преименовати заглавља из свих учитаних табела.

Након што завршите конверзију, изаберите команде Почетна — Затвори и учитај — Затвори и учитај у… и врста увоза Само створите везу (Почетна — Затвори&Учитај — Затвори&Учитај у… — Само креирај везу) и вратите се у Екцел.

Корак 2. Учитавамо све из свих датотека онако како јесте

Сада учитајмо садржај свих наших датотека из фасцикле – за сада, какав јесте. Бирање тимова Подаци – Преузми податке – Из датотеке – Из фасцикле (Подаци — Узми податке — Из датотеке — Из фолдера) а затим фасциклу у којој се налазе наше изворне књиге.

У прозору за преглед кликните Претворити (Трансформисати) or Променити (Ремик):

Затим проширите садржај свих преузетих датотека (бинарни) дугме са двоструким стрелицама у наслову колоне садржина:

Повер Куери на примеру прве датотеке (Восток.клск) ће нас питати за назив листа који желимо да узмемо из сваке радне свеске – изаберите Фотографије и притисните ОК:

Након тога (у ствари) ће се десити неколико догађаја који нису очигледни за корисника, чије су последице јасно видљиве на левом панелу:

1. Повер Куери ће узети прву датотеку из фасцикле (имаћемо је Восток.клск — видети Пример датотеке) као пример и увози његов садржај креирањем упита Претворите узорак датотеке. Овај упит ће имати неколико једноставних корака као што су извор (приступ фајлу) navigacija (избор листа) и евентуално подизање наслова. Овај захтев може да учита податке само из једне одређене датотеке Восток.клск.
   
2. На основу овог захтева биће креирана функција повезана са њим Претвори датотеку (означено карактеристичном иконом f_x), при чему изворни фајл више неће бити константа, већ променљива вредност – параметар. Дакле, ова функција може да издвоји податке из било које књиге које убацимо у њу као аргумент.
   
3. Функција ће се примењивати редом на сваки фајл (бинарни) из колоне садржина – за ово је одговоран корак Позовите прилагођену функцију у нашем упиту који додаје колону на листу датотека Претвори датотеку са резултатима увоза из сваке радне свеске:

   

4. Додатне колоне се уклањају.
5. Садржај угнежђених табела се проширује (корак Колона проширене табеле) – и видимо коначне резултате прикупљања података из свих књига:

   
   

Корак 3. Брушење

Претходни снимак екрана јасно показује да се директна монтажа „као што јесте“ показала лошег квалитета:

• Колоне су обрнуте.
• Много додатних редова (празних и не само).
• Заглавља табеле се не доживљавају као заглавља и помешана су са подацима.
  

Можете врло лако да решите све ове проблеме – само подесите упит Цонверт Сампле Филе. Сва подешавања која извршимо аутоматски ће пасти у придружену функцију Претвори датотеку, што значи да ће се касније користити приликом увоза података из сваке датотеке.

Отварањем захтева Претворите узорак датотеке, додајте кораке за филтрирање непотребних редова (на пример, по колони ЦолумнКСНУМКС) и подизање наслова помоћу дугмета Користите први ред као заглавља (Користите први ред као заглавља). Табела ће изгледати много боље.

Да би се колоне из различитих датотека касније аутоматски уклопиле једна испод друге, морају се назвати истим именом. Такво масовно преименовање можете извршити према претходно креираном директоријуму са једном линијом М-кода. Хајде да поново притиснемо дугме f_x у траци формуле и додајте функцију за промену:

= Табле.РенамеЦолумнс(#”Повишена заглавља”, Хеадерс, МиссингФиелд.Игноре)

Ова функција преузима табелу из претходног корака Повишена заглавља и преименује све колоне у њему према угнежђеној листи за тражење Наслови. Трећи аргумент МиссингФиелд.Игноре је потребно да на оним насловима који се налазе у директоријуму, а нису у табели, не дође до грешке.

У ствари, то је све.

Враћајући се на захтев Извештаји видећемо потпуно другачију слику – много лепшу од претходне:

• Шта је Повер Куери, Повер Пивот, Повер БИ и зашто су потребни кориснику програма Екцел
  
• Прикупљање података из свих датотека у датој фасцикли
• Прикупљање података са свих листова књиге у једну табелу