Vaka matafura ane misoro yakasiyana kubva kumabhuku akawanda

Kugadziriswa kwedambudziko

Tine mafaira akati wandei (mumuenzaniso wedu - 4 zvidimbu, mune yakajairika kesi - akawanda sezvaunoda) mune imwe folda Reports:

Mukati, mafaera aya anoita seizvi:

Izvo:

• Iyo data sheet yatinoda inogara ichinzi Photos, asi inogona kuva chero mubhuku rekushanda.
  
• Kupfuura pepa Photos Bhuku rimwe nerimwe rinogona kuva nemamwe mapepa.
• Matafura ane data ane nhamba yakasiyana yemitsara uye inogona kutanga nemutsara wakasiyana papepa rekushanda.
• Mazita emakoramu akafanana mumatafura akasiyana anogona kusiyana (semuenzaniso, Huwandu = Huwandu = Qty).
• Makolamu mumatafura anogona kurongwa nenzira yakasiyana.

Basa: unganidza data rekutengesa kubva kune ese mafaera kubva papepa Photos mune imwe tafura yakajairika kuitira kuti ugozovaka pfupiso kana chero imwe analytics pairi.

Danho 1. Kugadzira dhairekitori remazita emakoramu

Chinhu chekutanga kuita kugadzirira bhuku rereferenzi rine zvese zvingasarudzwa zvemazita emakoramu uye dudziro yavo chaiyo:

Isu tinoshandura iyi rondedzero kuita ine simba "smart" tafura tichishandisa iyo Format sebhatani retafura pane tab Home (Kumba - Fomati seTafura) kana nzira yekudimbudzira keyboard Ctrl+T uye zvitakure muPower Query nemurairo Data – Kubva Tafura/Range (Data - Kubva Tafura/Range). Mune shanduro dzichangoburwa dzeExcel, yakatumidzwa zita rekuti Nemashizha (Kubva pepa).

Mune iyo Power Query query edhita hwindo, isu tsika kudzima nhanho Changed Type uye wedzera nhanho itsva panzvimbo payo nekudzvanya pabhatani f_xmune formula bar (kana isingaonekwe, saka unogona kuigonesa pane iyo tab ongorora) uye isa fomura ipapo mune yakavakirwa-mukati Power Query mutauro M:

=Table.ToRows(Mabviro)

Uyu murairo uchashandura iyo yakatakurwa munhanho yapfuura mabviro tafura yereferensi mune runyorwa rune nested lists (Rondedzero), imwe neimwe yadzo, iyowo, mbiri yehunhu. Zvaka-va kubva mumutsara mumwe:

Tichada rudzi urwu rwe data zvishoma gare gare, apo ruzhinji rwekutumidza mazita kubva kumatafura ese akaremerwa.

Mushure mekupedza kutendeuka, sarudza mirairo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... uye rudzi rwekupinza Ingogadzira chinongedzo (Kumba — Vhara & Rodha — Vhara&Rodha ku… — Gadzira chinongedzo chete) uye dzokera kuExcel.

Nhanho 2. Tinotakura zvinhu zvose kubva pamafaira ose sezvazviri

Zvino ngatitorei zviri mukati memafaira edu ese kubva mufolda - ikozvino, sezvazviri. Kusarudza zvikwata Dhata - Tora data - Kubva faira - Kubva folda (Data - Tora Dhata - Kubva faira - Kubva folda) uyezve folda apo mabhuku edu anowanikwa.

Muwindo rekuona, tinya pindura (Shandura) or chinja (Hodha):

Uye wobva wawedzera zvirimo zveese mafaera akatorwa (Bhinary) bhatani rine miseve miviri mumusoro wembiru gutsikana:

Power Query pamuenzaniso wefaira rekutanga (Vostok.xlsx) achatibvunza zita rebepa ratinoda kutora kubva mubhuku rega rega rebasa - sarudza Photos uye tinya OK:

Mushure meizvozvo (chaizvoizvo), zviitiko zvinoverengeka zvisingaonekwe kumushandisi zvichaitika, mhedzisiro yacho inonyatso kuoneka mune yekuruboshwe:

1. Power Query inotora yekutanga faira kubva kune folda (tichave nayo Vostok.xlsx - ona Muenzaniso wefaira) semuenzaniso uye inopinza zvirimo nekugadzira mubvunzo Shandura faira remuenzaniso. Mubvunzo uyu uchave nematanho akareruka senge mabviro (faira kupinda) Navigation (kusarudzwa kwepepa) uye pamwe nekusimudza mazita. Ichi chikumbiro chinogona chete kurodha data kubva kune imwe chaiyo faira Vostok.xlsx.
   
2. Zvichienderana nechikumbiro ichi, basa rakabatana nacho richagadzirwa Shandura faira (inoratidzwa nechiratidzo chechimiro f_x), apo iyo faira yefaira haichazove yakagadzikana, asi kukosha kwekuchinja - parameter. Saka, basa iri rinogona kubvisa data kubva kune chero bhuku ratinopinda mariri senharo.
   
3. Basa racho rinozoshandiswa kune rimwe nerimwe faira (Binary) kubva pamutsara gutsikana – danho ndiro rinokonzera izvi Call tsika basa mumubvunzo wedu uyo ​​unowedzera koramu kune runyorwa rwemafaira Shandura faira nezvinobuda kunze kubva mubhuku rega rega rebasa:

   

4. Mamwe ma column anobviswa.
5. Zviri mukati mematafura anested zvinowedzerwa (nhanho Koramu yetafura yakawedzerwa) - uye tinoona mhedzisiro yekuunganidza data kubva kumabhuku ese:

   
   

Danho 3. Sanding

Iyo yakapfuura skrini inoratidza zvakajeka kuti gungano rakananga "sezvazviri" rakazove remhando yakashata:

• Makoramu akadzoserwa kumashure.
• Mitsetse yakawanda yekuwedzera (isina uye kwete chete).
• Tafura misoro haionekwe semusoro uye inosanganiswa nedata.
  

Unogona kugadzirisa ese aya matambudziko zviri nyore - ingo gadzirisa iyo Shandura Sample File query. Kugadziridza kwese kwatinoita kwairi kunongowira mune inosanganisirwa Shandura faira basa, zvinoreva kuti ichashandiswa gare gare pakuunza data kubva kune yega faira.

Nokuvhura chikumbiro Shandura faira remuenzaniso, wedzera matanho ekusefa zvisina basa mitsara (semuenzaniso, nekoramu Column2) nekusimudza misoro nebhatani Shandisa mutsara wekutanga semusoro (Shandisa mutsara wekutanga semusoro). Tafura ichaita sezviri nani.

Kuti makoramu kubva kune akasiyana mafaera akwanise kukwana pasi peimwe gare gare, anofanirwa kutumidzwa zvakafanana. Iwe unogona kuita hukuru hwekutumidza zita maererano neyakagadzirwa dhairekitori nemutsara mumwe weM-code. Ngatidzvanye bhatani zvakare f_x mune formula bar uye wedzera basa rekuchinja:

= Tafura. Rename Columns(#"Misoro Yakasimudzwa", Misoro, Nzvimbo Isipo.Ignore)

Basa iri rinotora tafura kubva padanho rekare Misoro yakasimudzwa uye inotumidza mazita ese makoramu mairi zvichienderana neiyo nested lookup list Musoro. Nharo yechitatu MissingField.Ignore inodiwa kuitira kuti pane iyo misoro iri mudhairekitori, asi isiri mutafura, kukanganisa hakuitike.

Chaizvoizvo, ndizvo chete.

Kudzokera kuchikumbiro Reports tichaona mufananidzo wakasiyana zvachose - wakanaka kupfuura wekutanga:

• Chii chinonzi Power Query, Power Pivot, Power BI uye nei mushandisi weExcel achizvida
  
• Kuunganidza data kubva kune ese mafaera mune yakapihwa folda
• Kuunganidza data kubva pamapepa ese ebhuku mune imwe tafura