Cynnwys
Ffurfio'r broblem
Fel data mewnbwn, mae gennym ffeil Excel, lle mae un o'r dalennau'n cynnwys sawl tabl gyda data gwerthiant o'r ffurf ganlynol:
Noder:
- Tablau o wahanol feintiau a gyda setiau gwahanol o gynhyrchion a rhanbarthau mewn rhesi a cholofnau heb unrhyw ddidoli.
- Gellir gosod llinellau gwag rhwng byrddau.
- Gall nifer y tablau fod yn unrhyw un.
Dwy dybiaeth bwysig. Tybir bod:
- Uwchben pob tabl, yn y golofn gyntaf, mae enw'r rheolwr y mae'r tabl yn dangos ei werthiant (Ivanov, Petrov, Sidorov, ac ati).
- Ysgrifennir enwau nwyddau a rhanbarthau ym mhob tabl yn yr un modd – gyda chywirdeb achos.
Y nod yn y pen draw yw casglu data o'r holl dablau yn un tabl normaleiddio gwastad, sy'n gyfleus ar gyfer dadansoddiad dilynol ac adeiladu crynodeb, hy yn yr un hwn:
Cam 1. Cysylltu â'r ffeil
Gadewch i ni greu ffeil Excel wag newydd a'i dewis ar y tab Dyddiad Gorchymyn Cael Data - O Ffeil - O'r Llyfr (Data — O ffeil — O'r llyfr gwaith). Nodwch leoliad y ffeil ffynhonnell gyda data gwerthiant ac yna yn y ffenestr llywio dewiswch y ddalen sydd ei hangen arnom a chliciwch ar y botwm Trosi Data (Trawsnewid Data):
O ganlyniad, dylid llwytho'r holl ddata ohono i'r golygydd Power Query:
Cam 2. Glanhewch y sbwriel
Dileu camau a gynhyrchir yn awtomatig math wedi'i addasu (Math wedi'i Newid) и Penawdau uchel (Penawdau a Hyrwyddir) a chael gwared ar linellau gwag a llinellau gyda chyfansymiau gan ddefnyddio hidlydd null и CYFANSWM gan y golofn gyntaf. O ganlyniad, rydym yn cael y llun canlynol:
Cam 3. Ychwanegu rheolwyr
Er mwyn deall yn ddiweddarach ble mae eu gwerthiant, mae angen ychwanegu colofn at ein tabl, lle bydd cyfenw cyfatebol ym mhob rhes. Ar gyfer hyn:
1. Gadewch i ni ychwanegu colofn ategol gyda rhifau llinell gan ddefnyddio'r gorchymyn Ychwanegu Colofn - Colofn Fynegai - O 0 (Ychwanegu colofn - colofn fynegai - O 0).
2. Ychwanegu colofn gyda fformiwla gyda'r gorchymyn Ychwanegu Colofn - Colofn Custom (Ychwanegu colofn - colofn personol) a chyflwyno'r adeiladwaith canlynol yno:
Mae rhesymeg y fformiwla hon yn syml – os mai “Cynnyrch” yw gwerth y gell nesaf yn y golofn gyntaf, yna mae hyn yn golygu ein bod wedi baglu ar ddechrau tabl newydd, felly rydym yn dangos gwerth y gell flaenorol gyda'r enw'r rheolwr. Fel arall, nid ydym yn arddangos unrhyw beth, hy null.
I gael y rhiant gell gyda'r enw olaf, rydym yn gyntaf yn cyfeirio at y tabl o'r cam blaenorol #"mynegai wedi'i ychwanegu", ac yna nodwch enw'r golofn sydd ei hangen arnom [Colofn 1] mewn cromfachau sgwâr a rhif y gell yn y golofn honno mewn cromfachau cyrliog. Bydd rhif y gell un yn llai na'r un gyfredol, yr ydym yn ei gymryd o'r golofn mynegai, Yn y drefn honno.
3. Mae'n parhau i fod i lenwi'r celloedd gwag gyda null enwau o gelloedd uwch gyda'r gorchymyn Trawsnewid – Llenwch – Lawr (Trawsnewid - Llenwch - i lawr) a dileu'r golofn nad oes ei hangen mwyach gyda mynegeion a rhesi gydag enwau olaf yn y golofn gyntaf. O ganlyniad, rydym yn cael:
Cam 4. Rheolwyr yn eu grwpio'n dablau ar wahân
Y cam nesaf yw grwpio'r rhesi ar gyfer pob rheolwr yn dablau ar wahân. I wneud hyn, ar y tab Trawsnewid, defnyddiwch y Grŵp trwy orchymyn (Trawsnewid - Grŵp Erbyn) ac yn y ffenestr sy'n agor, dewiswch y golofn Rheolwr a'r gweithrediad Pob rhes (Pob rhes) i gasglu data heb gymhwyso unrhyw swyddogaeth agregu i nhw (swm, cyfartaledd, ac ati). P.):
O ganlyniad, rydym yn cael tablau ar wahân ar gyfer pob rheolwr:
Cam 5: Trawsnewid Tablau Nythu
Nawr rydyn ni'n rhoi'r tablau sy'n gorwedd ym mhob cell o'r golofn canlyniadol Yr holl ddata mewn siâp gweddus.
Yn gyntaf, dilëwch golofn nad oes ei hangen mwyach ym mhob tabl Rheolwr. Rydym yn defnyddio eto Colofn personol tab Trawsnewid (Trawsnewid - colofn personol) a'r fformiwla ganlynol:
Yna, gyda cholofn gyfrifedig arall, rydym yn codi'r rhes gyntaf ym mhob tabl i'r penawdau:
Ac yn olaf, rydyn ni'n perfformio'r prif drawsnewidiad - gan ddatblygu pob tabl gan ddefnyddio'r swyddogaeth M Tabl.UnpivotOtherColumns:
Bydd enwau'r rhanbarthau o'r pennawd yn mynd i golofn newydd a byddwn yn cael tabl mwy cul, ond ar yr un pryd, wedi'i normaleiddio'n hirach. Celloedd gwag gyda null yn cael eu hanwybyddu.
Cael gwared ar golofnau canolradd diangen, mae gennym ni:
Cam 6 Ehangu Tablau Nythu
Mae'n dal i fod i ehangu'r holl dablau nythu wedi'u normaleiddio yn un rhestr gan ddefnyddio'r botwm gyda saethau dwbl ym mhennyn y golofn:
... ac o'r diwedd rydyn ni'n cael yr hyn roedden ni ei eisiau:
Gallwch allforio'r tabl canlyniadol yn ôl i Excel gan ddefnyddio'r gorchymyn Cartref - Cau a Llwythwch - Cau a Llwythwch i mewn… (Cartref - Cau a Llwyth - Cau a Llwythwch i…).
- Adeiladu tablau gyda gwahanol benawdau o lyfrau lluosog
- Casglu data o bob ffeil mewn ffolder penodol
- Casglu data o bob dalen o'r llyfr mewn un tabl