Paghimo mga lamesa nga adunay lainlaing mga ulohan gikan sa daghang mga libro

Pagporma sa problema

Kami adunay daghang mga file (sa among pananglitan - 4 ka piraso, sa kinatibuk-ang kaso - kutob sa imong gusto) sa usa ka folder mga taho:

Sa sulod, kini nga mga file ingon niini:

Diin:

• Ang data sheet nga atong gikinahanglan kanunay nga gitawag Litrato, apan mahimong bisan asa sa workbook.
  
• Labaw sa sheet Litrato Ang matag libro mahimong adunay lain nga mga panid.
• Ang mga lamesa nga adunay datos adunay lahi nga gidaghanon sa mga laray ug mahimong magsugod sa usa ka lahi nga laray sa worksheet.
• Ang mga ngalan sa parehas nga mga kolum sa lainlaing mga lamesa mahimong magkalainlain (pananglitan, Gidaghanon = Gidaghanon = Gidaghanon).
• Ang mga kolum sa mga lamesa mahimong gihan-ay sa lahi nga pagkahan-ay.

Buluhaton: pagkolekta sa datos sa pagbaligya gikan sa tanang mga file gikan sa sheet Litrato ngadto sa usa ka komon nga lamesa aron sa sunod nga paghimo og usa ka summary o bisan unsang uban nga analytics niini.

Lakang 1. Pag-andam og direktoryo sa mga ngalan sa kolum

Ang unang butang nga buhaton mao ang pag-andam og usa ka reperensiya nga libro uban sa tanang posibleng mga opsyon alang sa mga ngalan sa kolum ug sa hustong interpretasyon niini:

Among gi-convert kini nga listahan ngadto sa usa ka dinamikong "smart" nga lamesa gamit ang Format as table button sa tab Home (Balay — Format isip Talaan) o keyboard shortcut Ctrl+T ug i-load kini sa Power Query gamit ang command Data – Gikan sa Talaan/Sakop (Data — Gikan sa Table/Range). Sa bag-o nga mga bersyon sa Excel, kini giilisan sa ngalan sa Uban sa mga dahon (Gikan sa sheet).

Sa window sa editor sa pangutana sa Power Query, tradisyonal namong gitangtang ang lakang Gibag-o nga Type ug pagdugang og bag-ong lakang imbes niini pinaagi sa pag-klik sa buton f_xsa pormula bar (kung dili kini makita, mahimo nimo kini mahimo sa tab Review) ug isulod ang pormula didto sa built-in nga Power Query nga pinulongan M:

=Table.ToRows(Source)

Kini nga sugo mag-convert sa usa nga gikarga sa miaging lakang tinubdan reference table ngadto sa usa ka lista nga gilangkuban sa mga nested list (Listahan), nga ang matag usa, sa baylo, usa ka parisan sa mga kantidad Kini nahimo-nahimo gikan sa usa ka linya:

Kinahanglan namon kini nga matang sa datos sa ulahi, kung ang mga pag-ilis sa ngalan sa mga header gikan sa tanan nga na-load nga mga lamesa.

Human makompleto ang pagkakabig, pilia ang mga sugo Panimalay — Pagsira ug Pag-load — Pagsira ug Pag-load sa… ug matang sa import Paghimo lang og koneksyon (Home — Close&Load — Close&Load to… — Paghimo lang ug koneksyon) ug balik sa Excel.

Lakang 2. Gikarga namo ang tanan gikan sa tanan nga mga file sama sa

Karon atong i-load ang mga sulod sa tanan natong mga file gikan sa folder - sa pagkakaron, kung unsa man. Pagpili sa mga team Data - Pagkuha data - Gikan sa file - Gikan sa folder (Data - Pagkuha Data - Gikan sa file - Gikan sa folder) ug dayon ang folder diin naa ang among gigikanan nga mga libro.

Sa preview nga bintana, i-klik Pagbalhin (Pagbag-o) or Kausaban (I-edit):

Ug unya palapdan ang mga sulud sa tanan nga na-download nga mga file (Binary) buton nga adunay doble nga mga pana sa ulohan sa kolum Content:

Power Query sa pananglitan sa unang file (Vostok.xlsx) mangutana kanamo sa ngalan sa sheet nga gusto namong kuhaon gikan sa matag workbook - pilia Litrato ug pindota ang OK:

Pagkahuman niana (sa tinuud), daghang mga panghitabo nga dili klaro sa tiggamit ang mahitabo, ang mga sangputanan nga klaro nga makita sa wala nga panel:

1. Ang Power Query mokuha sa unang file gikan sa folder (atong makuha kini Vostok.xlsx — tan-awa ang Pananglitan sa file) isip usa ka pananglitan ug nag-import sa sulod niini pinaagi sa paghimo og pangutana I-convert ang sample file. Kini nga pangutana adunay pipila ka yano nga mga lakang sama sa tinubdan (pag-access sa file) tabok-tabok (pagpili sa panid) ug posible nga ipataas ang mga titulo. Kini nga hangyo mahimo ra magkarga sa datos gikan sa usa ka piho nga file Vostok.xlsx.
   
2. Pinasukad niini nga hangyo, ang function nga nalangkit niini pagabuhaton Pag-convert sa file (gipakita sa usa ka kinaiya nga icon f_x), diin ang tinubdan nga file dili na usa ka makanunayon, apan usa ka variable nga bili - usa ka parameter. Sa ingon, kini nga function mahimo’g makuha ang datos gikan sa bisan unsang libro nga among gisulod niini ingon usa ka argumento.
   
3. Ang function ipadapat sa turn sa matag file (Binary) gikan sa kolum Content – lakang ang responsable niini Tawga ang custom function sa among pangutana nga nagdugang usa ka kolum sa lista sa mga file Pag-convert sa file uban ang mga resulta sa pag-import gikan sa matag workbook:

   

4. Gikuha ang mga dugang nga kolum.
5. Ang mga sulod sa mga nested table gipalapdan (step Gipadako nga kolum sa lamesa) – ug atong makita ang kataposang resulta sa pagkolekta sa datos gikan sa tanang libro:

   
   

Lakang 3. Sanding

Ang miaging screenshot tin-aw nga nagpakita nga ang direktang asembliya "as is" nahimo nga dili maayo nga kalidad:

• Ang mga kolum gibaliktad.
• Daghang dugang nga mga linya (walay sulod ug dili lamang).
• Ang mga ulohan sa lamesa wala gitan-aw nga mga ulohan ug gisagol sa datos.
  

Mahimo nimong ayuhon ang tanan niini nga mga problema nga dali ra - i-tweak lang ang pangutana sa Convert Sample File. Ang tanan nga mga pag-adjust nga among gihimo niini awtomatik nga mahulog sa kauban nga Convert file function, nga nagpasabut nga kini gamiton sa ulahi kung mag-import og data gikan sa matag file.

Pinaagi sa pag-abli sa usa ka hangyo I-convert ang sample file, idugang ang mga lakang sa pagsala sa wala kinahanglana nga mga laray (pananglitan, pinaagi sa kolum Column2) ug ipataas ang mga ulohan gamit ang buton Gamita ang unang linya isip mga ulohan (Gamita ang unang laray isip mga ulohan). Ang lamesa mas nindot tan-awon.

Aron ang mga kolum gikan sa lainlaing mga file awtomatik nga mohaum sa usag usa sa ulahi, sila kinahanglan nga parehas nga ngalan. Mahimo nimong buhaton ang ingon nga pagbag-o sa ngalan sumala sa usa ka direktoryo nga nahimo kaniadto nga adunay usa ka linya sa M-code. Atong ipadayon ang buton pag-usab f_x sa formula bar ug pagdugang usa ka function aron mabag-o:

= Table.RenameColumns(#”Elevated Headers”, Header, MissingField.Ignore)

Kini nga function nagkuha sa lamesa gikan sa miaging lakang Gipataas nga mga ulohan ug giusab ang ngalan sa tanan nga mga kolum niini sumala sa nested lookup list Mga ulohan. Ikatulo nga argumento MissingField.Balewala gikinahanglan aron sa mga ulohan nga naa sa direktoryo, apan wala sa lamesa, dili mahitabo ang sayup.

Actually, mao ra.

Pagbalik sa hangyo mga taho makita nato ang usa ka hingpit nga lahi nga hulagway - mas nindot kay sa nauna:

• Unsa ang Power Query, Power Pivot, Power BI ug nganong gikinahanglan kini sa usa ka user sa Excel
  
• Pagkolekta sa datos gikan sa tanan nga mga file sa usa ka gihatag nga folder
• Pagkolekta sa datos gikan sa tanang panid sa libro ngadto sa usa ka lamesa