Adeiladu tablau gyda gwahanol benawdau o lyfrau lluosog

Ffurfio'r broblem

Mae gennym nifer o ffeiliau (yn ein hesiampl - 4 darn, yn yr achos cyffredinol - cymaint ag y dymunwch) mewn un ffolder Adroddiadau:

Y tu mewn, mae'r ffeiliau hyn yn edrych fel hyn:

Lle:

• Gelwir y daflen ddata sydd ei hangen arnom bob amser pics, ond gall fod yn unrhyw le yn y llyfr gwaith.
  
• Y tu hwnt i'r ddalen pics Gall fod dalennau eraill ar bob llyfr.
• Mae gan dablau gyda data nifer wahanol o resi a gallant ddechrau gyda rhes wahanol ar y daflen waith.
• Gall enwau'r un colofnau mewn gwahanol dablau fod yn wahanol (er enghraifft, Nifer = Nifer = Qty).
• Gellir trefnu colofnau mewn tablau mewn trefn wahanol.

Tasg: casglwch ddata gwerthiant o bob ffeil o'r ddalen pics mewn un tabl cyffredin er mwyn adeiladu crynodeb neu unrhyw ddadansoddeg arall arno wedyn.

Cam 1. Paratoi cyfeiriadur o enwau colofnau

Y peth cyntaf i'w wneud yw paratoi cyfeirlyfr gyda'r holl opsiynau posibl ar gyfer enwau colofnau a'u dehongliad cywir:

Rydyn ni'n trosi'r rhestr hon yn dabl “clyfar” deinamig gan ddefnyddio'r botwm Fformat fel tabl ar y tab Hafan (Cartref - Fformat fel Tabl) neu lwybr byr bysellfwrdd Ctrl+T a'i lwytho i mewn i Power Query gyda'r gorchymyn Data – O Dabl/Amrediad (Data — O Dabl/Ystod). Mewn fersiynau diweddar o Excel, mae wedi cael ei ailenwi i Gyda dail (O ddalen).

Yn ffenestr golygydd ymholiad Power Query, rydym yn draddodiadol yn dileu'r cam Math wedi'i Newid ac ychwanegu cam newydd yn ei le trwy glicio ar y botwm f_xyn y bar fformiwla (os nad yw'n weladwy, yna gallwch ei alluogi ar y tab adolygiad) a nodwch y fformiwla yno yn yr iaith Power Query M:

= Tabl.ToRows(Ffynhonnell)

Bydd y gorchymyn hwn yn trosi'r un a lwythwyd yn y cam blaenorol ffynhonnell tabl cyfeirio yn rhestr sy'n cynnwys rhestrau nythu (Rhestr), y mae pob un ohonynt, yn ei dro, yn bâr o werthoedd Yr oedd-daeth o un llinell:

Bydd angen y math hwn o ddata ychydig yn ddiweddarach, wrth ailenwi penawdau màs o'r holl dablau wedi'u llwytho.

Ar ôl cwblhau'r trosi, dewiswch y gorchmynion Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… a math o fewnforio Dim ond creu cysylltiad (Cartref - Cau a Llwyth - Cau a Llwythwch i… - Creu cysylltiad yn unig) a mynd yn ôl i Excel.

Cam 2. Rydym yn llwytho popeth o'r holl ffeiliau fel y mae

Nawr, gadewch i ni lwytho cynnwys ein holl ffeiliau o'r ffolder - am y tro, fel y mae. Dewis timau Data - Cael data - O ffeil - O ffolder (Data - Cael Data - O ffeil - O ffolder) ac yna'r ffolder lle mae ein llyfrau ffynhonnell.

Yn y ffenestr rhagolwg, cliciwch Trosi (Trawsnewid) or Newid (Golygu):

Ac yna ehangu cynnwys yr holl ffeiliau wedi'u llwytho i lawr (Deuaidd) botwm gyda saethau dwbl ym mhennawd y golofn Cynnwys:

Ymholiad pŵer ar enghraifft y ffeil gyntaf (Vostok.xlsx) yn gofyn i ni enw'r ddalen yr ydym am ei chymryd o bob llyfr gwaith – dewiswch pics a gwasgwch OK:

Ar ôl hynny (mewn gwirionedd), bydd nifer o ddigwyddiadau nad ydynt yn amlwg i'r defnyddiwr yn digwydd, y mae eu canlyniadau i'w gweld yn glir yn y panel chwith:

1. Bydd Power Query yn cymryd y ffeil gyntaf o'r ffolder (bydd gennym ni Vostok.xlsx — gweld Enghraifft o ffeil) fel enghraifft ac yn mewnforio ei gynnwys trwy greu ymholiad Trosi ffeil sampl. Bydd gan yr ymholiad hwn rai camau syml fel ffynhonnell (mynediad ffeil) Llywio (dewis dalen) ac o bosibl codi'r teitlau. Dim ond o un ffeil benodol y gall y cais hwn lwytho data Vostok.xlsx.
   
2. Yn seiliedig ar y cais hwn, bydd y swyddogaeth sy'n gysylltiedig ag ef yn cael ei greu Trosi ffeil (a ddangosir gan eicon nodweddiadol f_x), lle na fydd y ffeil ffynhonnell bellach yn gysonyn, ond yn werth newidiol - paramedr. Felly, gall y swyddogaeth hon dynnu data o unrhyw lyfr y byddwn yn llithro iddo fel dadl.
   
3. Bydd y swyddogaeth yn cael ei gymhwyso yn ei dro i bob ffeil (Deuaidd) o'r golofn Cynnwys - Cam sy'n gyfrifol am hyn Ffoniwch swyddogaeth arferiad yn ein hymholiad sy'n ychwanegu colofn at y rhestr o ffeiliau Trosi ffeil gyda chanlyniadau mewnforio o bob llyfr gwaith:

   

4. Mae colofnau ychwanegol yn cael eu tynnu.
5. Mae cynnwys byrddau nythu yn cael eu hehangu (cam Colofn tabl estynedig) – a gwelwn ganlyniadau terfynol casglu data o bob llyfr:

   
   

Cam 3. sandio

Mae'r sgrinlun blaenorol yn dangos yn glir bod y cynulliad uniongyrchol "fel y mae" wedi troi allan i fod o ansawdd gwael:

• Mae'r colofnau wedi'u gwrthdroi.
• Llawer o linellau ychwanegol (gwag ac nid yn unig).
• Nid yw penawdau tabl yn cael eu gweld fel penawdau ac maent yn gymysg â data.
  

Gallwch chi atgyweirio'r holl broblemau hyn yn hawdd iawn - dim ond tweakiwch yr ymholiad Trosi Ffeil Sampl. Bydd yr holl addasiadau a wnawn iddo yn disgyn yn awtomatig i'r swyddogaeth Trosi ffeil cysylltiedig, sy'n golygu y byddant yn cael eu defnyddio'n ddiweddarach wrth fewnforio data o bob ffeil.

Trwy agor cais Trosi ffeil sampl, ychwanegu camau i hidlo rhesi diangen (er enghraifft, fesul colofn Column2) a chodi'r penawdau gyda'r botwm Defnyddiwch y llinell gyntaf fel penawdau (Defnyddiwch y rhes gyntaf fel penawdau). Bydd y bwrdd yn edrych yn llawer gwell.

Er mwyn i golofnau o wahanol ffeiliau ffitio'n awtomatig o dan ei gilydd yn ddiweddarach, rhaid eu henwi yr un peth. Gallwch chi berfformio ailenwi màs o'r fath yn ôl cyfeiriadur a grëwyd yn flaenorol gydag un llinell o god M. Gadewch i ni bwyso'r botwm eto f_x yn y bar fformiwla ac ychwanegu swyddogaeth i newid:

= Tabl.RenameColumns(#"Penawdau Elevated", Penawdau, MissingField.Ignore)

Mae'r swyddogaeth hon yn cymryd y tabl o'r cam blaenorol Penawdau uchel ac yn ailenwi pob colofn ynddi yn ol y rhestr chwilio nythog Penawdau. Trydydd dadl MissingField.Ignore sydd ei angen fel nad yw gwall yn digwydd ar y penawdau hynny sydd yn y cyfeiriadur, ond nad ydynt yn y tabl.

A dweud y gwir, dyna i gyd.

Yn dychwelyd at y cais Adroddiadau byddwn yn gweld darlun hollol wahanol - llawer brafiach na'r un blaenorol:

• Beth yw Power Query, Power Pivot, Power BI a pham mae eu hangen ar ddefnyddiwr Excel
  
• Casglu data o bob ffeil mewn ffolder penodol
• Casglu data o bob dalen o'r llyfr mewn un tabl