Стварыце табліцы з рознымі загалоўкамі з некалькіх кніг

Пастаноўка задачы

У нас некалькі файлаў (у нашым прыкладзе - 4 штукі, у агульным выпадку - колькі заўгодна) у адной тэчцы Справаздачы:

Унутры гэтыя файлы выглядаюць так:

Пры гэтым:

• Заўсёды выклікаецца патрэбны нам тэхпашпарт фатаграфіі, але можа знаходзіцца ў любым месцы працоўнай кніжкі.
  
• За лістом фатаграфіі Кожная кніга можа мець іншыя аркушы.
• Табліцы з дадзенымі маюць розную колькасць радкоў і могуць пачынацца з іншага радка на аркушы.
• Назвы адных і тых жа слупкоў у розных табліцах могуць адрознівацца (напрыклад, Колькасць = Колькасць = Колькасць).
• Слупкі ў табліцах могуць быць размешчаны ў розным парадку.

Задача: сабраць дадзеныя аб продажах з усіх файлаў ліста фатаграфіі у адну агульную табліцу, каб потым будаваць на ёй зводную або іншую аналітыку.

Крок 1. Падрыхтоўка каталога імёнаў слупкоў

Першае, што трэба зрабіць - падрыхтаваць даведнік з усімі магчымымі варыянтамі назваў слупкоў і іх правільнай расшыфроўкі:

Мы пераўтворым гэты спіс у дынамічную «разумную» табліцу з дапамогай кнопкі «Фарматаваць як табліцу» на ўкладцы Галоўная (Галоўная — фармат у табліцу) або спалучэнне клавіш Ctrl+T і загрузіце яго ў Power Query з дапамогай каманды Дадзеныя – з табліцы/дыяпазону (Дадзеныя — з табліцы/дыяпазону). У апошніх версіях Excel ён быў перайменаваны ў З лісточкамі (З аркуша).

У акне рэдактара запытаў Power Query мы традыцыйна выдаляем крок Зменены тып і дадаць новы крок замест яго, націснуўшы на кнопку f_xу радку формул (калі ён не бачны, вы можаце ўключыць яго на ўкладцы агляд) і ўвядзіце туды формулу на ўбудаванай мове Power Query M:

=Table.ToRows(Крыніца)

Гэтая каманда пераўтворыць загружаны на папярэднім этапе крыніца даведачную табліцу ў спіс, які складаецца з укладзеных спісаў (List), кожны з якіх, у сваю чаргу, з'яўляецца парай значэнняў Было-стала з аднаго радка:

Гэты тып дадзеных нам спатрэбіцца крыху пазней, пры масавым перайменаванні загалоўкаў з усіх загружаных табліц.

Пасля завяршэння пераўтварэння абярыце каманды Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… і тып імпарту Проста стварыце злучэнне (Дадому — Зачыніць&Загрузіць — Зачыніць&Загрузіць у… — Толькі стварыць злучэнне) і вярнуцца ў Excel.

Крок 2. Загружаем усё з усіх файлаў як ёсць

Зараз давайце загрузім змесціва ўсіх нашых файлаў з тэчкі - пакуль, як ёсць. Выбар каманд Дадзеныя – Атрымаць даныя – З файла – З папкі (Дадзеныя — Атрымаць даныя — З файла — З папкі) а затым тэчку, дзе знаходзяцца нашы зыходныя кнігі.

У акне папярэдняга прагляду націсніце Канвертаваць (Ператварэнне) or Мяняць (Рэдагаваць):

А затым разгарніце змесціва ўсіх загружаных файлаў (двайковы) кнопка з падвойнымі стрэлкамі ў загалоўку слупка змест:

Power Query на прыкладзе першага файла (Усход.xlsx) спытае ў нас назву аркуша, які мы хочам узяць з кожнай працоўнай кніжкі – выбірайце фатаграфіі і націсніце OK:

Пасля гэтага (фактычна) адбудзецца некалькі невідавочных для карыстальніка падзей, наступствы якіх добра бачныя на левай панэлі:

1. Power Query возьме першы файл з папкі (ён будзе ў нас Усход.xlsx — убачыць Прыклад файла) у якасці прыкладу і імпартуе яго змесціва шляхам стварэння запыту Пераўтварыць узор файла. Гэты запыт будзе мець некалькі простых крокаў, такіх як крыніца (доступ да файла) рух (выбар аркуша) і, магчыма, павышэнне загалоўкаў. Гэты запыт можа загрузіць даныя толькі з аднаго канкрэтнага файла Усход.xlsx.
   
2. На аснове гэтага запыту будзе створана звязаная з ім функцыя Канвертаваць файл (пазначаецца характэрным значком f_x), дзе зыходны файл будзе ўжо не канстантай, а зменным значэннем – параметрам. Такім чынам, гэтая функцыя можа здабываць дадзеныя з любой кнігі, якія мы ўстаўляем у яе ў якасці аргументу.
   
3. Функцыя будзе прымяняцца па чарзе да кожнага файла (двайковага) са слупка змест – за гэта адказвае крок Выклік карыстальніцкай функцыі у нашым запыце, які дадае слупок у спіс файлаў Канвертаваць файл з вынікамі імпарту з кожнай кнігі:

   

4. Лішнія слупкі выдаляюцца.
5. Змесціва ўкладзеных табліц пашырана (крок Пашыраны слупок табліцы) – і мы бачым канчатковыя вынікі збору дадзеных з усіх кніг:

   
   

Крок 3. Шліфоўка

На папярэднім скрыншоце добра відаць, што прамая зборка «як ёсць» атрымалася няякаснай:

• Слупкі пераварочваюцца.
• Шмат лішніх радкоў (пустых і не толькі).
• Загалоўкі табліц не ўспрымаюцца як загалоўкі і змешваюцца з дадзенымі.
  

Вы можаце выправіць усе гэтыя праблемы вельмі лёгка - проста наладзьце запыт Convert Sample File. Усе карэкціроўкі, якія мы робім у ім, аўтаматычна трапляюць у звязаную функцыю Convert file, што азначае, што яны будуць выкарыстоўвацца пазней пры імпарце даных з кожнага файла.

Адкрыўшы запыт Пераўтварыць узор файла, дадайце крокі для фільтрацыі непатрэбных радкоў (напрыклад, па слупку Column2) і падымаючы загалоўкі кнопкай Выкарыстоўвайце першы радок у якасці загалоўкаў (Выкарыстоўваць першы радок у якасці загалоўкаў). Стол будзе выглядаць значна лепш.

Каб потым слупкі з розных файлаў аўтаматычна падганяліся адзін пад адным, іх трэба называць аднолькава. Ажыццявіць такое масавае перайменаванне можна па загадзя створанаму каталогу адным радком М-кода. Націснем кнопку яшчэ раз f_x у радку формул і дадайце функцыю для змены:

= Table.RenameColumns(#”Павышаныя загалоўкі”, загалоўкі, MissingField.Ignore)

Гэтая функцыя бярэ табліцу з папярэдняга кроку Прыпаднятыя загалоўкі і пераназывае ўсе слупкі ў ім у адпаведнасці з укладзеным спісам пошуку апошнія навіны. Аргумент трэці MissingField.Ignore патрэбен для таго, каб у тых загалоўках, якія ёсць у каталогу, але іх няма ў табліцы, не ўзнікала памылка.

Уласна, вось і ўсё.

Вяртаючыся да запыту Справаздачы мы ўбачым зусім іншую карціну – значна прыемнейшую за папярэднюю:

• Што такое Power Query, Power Pivot, Power BI і навошта яны патрэбныя карыстачу Excel
  
• Збор дадзеных з усіх файлаў у дадзенай тэчцы
• Збор дадзеных з усіх аркушаў кнігі ў адну табліцу