Изградете табели со различни заглавија од повеќе книги

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

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

Внатре, овие датотеки изгледаат вака:

При што:

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

Задача: собирајте податоци за продажба од сите датотеки од листот слики во една заедничка табела со цел последователно да се изгради резиме или која било друга аналитика на неа.

Чекор 1. Подготовка на директориум со имиња на колони

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

Оваа листа ја претвораме во динамична „паметна“ табела користејќи го копчето Формат како табела на јазичето Почетна (Дома - Формат како табела) или кратенка на тастатура Ctrl+T и вчитајте го во Power Query со командата Податоци – од табела/опсег (Податоци - од табела/опсег). Во последните верзии на Excel, тој беше преименуван во Со лисја (Од лист).

Во прозорецот на уредувачот на барањето Power Query, традиционално го бришеме чекорот Променет тип и наместо него додадете нов чекор со кликнување на копчето f_xво лентата со формула (ако не е видлива, тогаш можете да ја овозможите на јазичето преглед) и внесете ја формулата таму во вградениот јазик на Power Query M:

=Table.ToRows(Извор)

Оваа команда ќе ја конвертира онаа вчитана во претходниот чекор извор референтна табела во листа која се состои од вгнездени списоци (Список), од кои секоја, пак, е пар вредности Беше-стана од една линија:

Овој тип на податоци ќе ни треба малку подоцна, при масовно преименување на заглавијата од сите вчитани табели.

Откако ќе ја завршите конверзијата, изберете ги командите Дома — Затвори и вчитај — Затвори и вчитај во… и видот на увозот Само создадете врска (Дома - Затвори&Вчитај - Затвори&Вчитај во... - Создај само врска) и вратете се на Excel.

Чекор 2. Вчитуваме сè од сите датотеки како што е

Сега ајде да ја вчитаме содржината на сите наши датотеки од папката - засега, како што е. Избор на тимови Податоци – Добијте податоци – Од датотека – Од папка (Податоци - Добијте податоци - Од датотека - Од папка) а потоа папката каде што се нашите изворни книги.

Во прозорецот за преглед, кликнете Конвертирај (Трансформирајте) or Промени (Уреди):

И потоа проширете ја содржината на сите преземени датотеки (Бинарно) копче со двојни стрелки во насловот на колоната содржина:

Power Query на примерот на првата датотека (Восток.xlsx) ќе не праша како се вика листот што сакаме да го земеме од секоја работна книга – изберете слики и притиснете ОК:

После тоа (всушност), ќе се случат неколку настани кои не се очигледни за корисникот, чии последици се јасно видливи во левиот панел:

1. Power Query ќе ја земе првата датотека од папката (ќе ја имаме Vostok.xlsx - види Пример за датотека) како пример и ја увезува неговата содржина со креирање на барање Конвертирај примерок датотека. Ова барање ќе има неколку едноставни чекори како извор (пристап до датотека) навигација (избор на листови) и евентуално подигање на насловите. Ова барање може да вчита податоци само од една специфична датотека Восток.xlsx.
   
2. Врз основа на ова барање, ќе се креира функцијата поврзана со него Конвертирај датотека (означено со карактеристична икона f_x), каде што изворната датотека повеќе нема да биде константа, туку променлива вредност - параметар. Така, оваа функција може да извлече податоци од која било книга што ја внесуваме во неа како аргумент.
   
3. Функцијата ќе се примени за возврат на секоја датотека (Бинарна) од колоната содржина – чекор е одговорен за ова Повикајте приспособена функција во нашето барање што додава колона на списокот со датотеки Конвертирај датотека со резултати од увоз од секоја работна книга:

   

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

   
   

Чекор 3. Брусење

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

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

Можете да ги решите сите овие проблеми многу лесно - само прилагодете го барањето Конвертирај примерок датотека. Сите прилагодувања што ги правиме во него автоматски ќе паднат во поврзаната функција Конвертирај датотека, што значи дека ќе се користат подоцна при увоз на податоци од секоја датотека.

Со отворање на барање Конвертирај примерок датотека, додадете чекори за филтрирање на непотребните редови (на пример, по колона Column2) и подигање на насловите со копчето Користете ја првата линија како заглавија (Користете го првиот ред како заглавија). Табелата ќе изгледа многу подобро.

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

= Table.RenameColumns(#“Подигнати заглавија“, Заглавија, MissingField.Ignore)

Оваа функција ја зема табелата од претходниот чекор Покачени заглавија и ги преименува сите колони во него според вгнездената листа за пребарување Наслови. Трет аргумент MissingField.Игнорирај е потребно за на оние наслови кои се во директориумот, а ги нема во табелата, да не се појави грешка.

Всушност, тоа е сè.

Враќање на барањето Извештаи ќе видиме сосема поинаква слика – многу поубава од претходната:

• Што е Power Query, Power Pivot, Power BI и зошто му се потребни на корисникот на Excel
  
• Собирање податоци од сите датотеки во дадена папка
• Собирање податоци од сите листови од книгата во една табела