Tsim cov rooj nrog cov headers sib txawv ntawm ntau phau ntawv

Formulation ntawm qhov teeb meem

Peb muaj ntau cov ntaub ntawv (hauv peb qhov piv txwv - 4 daim, hauv cov ntaub ntawv dav dav - ntau npaum li koj nyiam) hauv ib daim ntawv tais ceev tseg Reports:

Sab hauv, cov ntaub ntawv zoo li no:

Qhov twg:

• Cov ntaub ntawv peb xav tau yog ib txwm hu ua duab, tab sis tuaj yeem nyob qhov twg hauv phau ntawv ua haujlwm.
  
• Tshaj ntawm daim ntawv duab Txhua phau ntawv yuav muaj lwm nplooj ntawv.
• Cov ntxhuav nrog cov ntaub ntawv muaj ntau tus kab sib txawv thiab tuaj yeem pib nrog ib kab sib txawv ntawm daim ntawv ua haujlwm.
• Cov npe ntawm tib kab hauv cov rooj sib txawv yuav txawv (piv txwv li, Quantity = Qty = Qty).
• Cov kab hauv cov rooj tuaj yeem muab faib ua qhov sib txawv.

Kev Ua Haujlwm: sau cov ntaub ntawv muag khoom los ntawm txhua cov ntaub ntawv los ntawm daim ntawv duab nyob rau hauv ib lub rooj sib tham nyob rau hauv thiaj li yuav tom qab tsim cov ntsiab lus los yog lwm yam analytics ntawm nws.

Kauj Ruam 1. Npaj cov npe ntawm kab ntawv

Thawj qhov uas yuav tsum tau ua yog npaj ib phau ntawv siv nrog txhua txoj hauv kev rau cov npe kab thiab lawv cov lus txhais kom raug:

Peb hloov daim ntawv teev npe no mus rau hauv lub rooj "ntse" dynamic siv hom ntawv raws li lub rooj khawm ntawm lub tab Tsev (Lub Tsev — Format as Table) los yog keyboard shortcut Ctrl+T thiab thauj nws rau hauv Power Query nrog cov lus txib Cov ntaub ntawv - Los ntawm Rooj / Ntau (Cov ntaub ntawv - Los ntawm Table / Range). Nyob rau hauv tsis ntev los no versions ntawm Excel, nws tau renamed rau Nrog nplooj (Los ntawm daim ntawv).

Hauv Power Query query editor window, peb ib txwm tshem tawm cov kauj ruam Hloov hom thiab ntxiv ib kauj ruam tshiab es tsis txhob ntawm nws los ntawm txhaj rau lub pob f_xnyob rau hauv lub formula bar (yog tias nws tsis pom, ces koj tuaj yeem qhib nws ntawm lub tab Review) thiab nkag mus rau cov qauv muaj nyob rau hauv built-in Power Query lus M:

=Table.ToRows(Source)

Qhov kev hais kom ua no yuav hloov ib qho loaded hauv cov kauj ruam dhau los Tau qhov twg los cov lus siv rau hauv ib daim ntawv teev npe uas muaj cov npe nested (List), txhua tus uas, nyob rau hauv lem, yog ib khub ntawm qhov tseem ceeb Nws yog-ua los ntawm ib kab:

Peb yuav xav tau hom ntaub ntawv no me ntsis tom qab, thaum loj renaming headers los ntawm tag nrho cov loaded ntxhuav.

Tom qab ua tiav qhov hloov dua siab tshiab, xaiv cov lus txib Tsev - Kaw thiab Load - Kaw thiab Load hauv… thiab hom ntshuam Tsuas yog tsim kev sib txuas (Lub Tsev - Kaw & Load - Kaw & Load rau… - Tsuas yog tsim kev sib txuas) thiab rov qab mus rau Excel.

Kauj ruam 2. Peb thauj txhua yam los ntawm tag nrho cov ntaub ntawv raws li yog

Tam sim no cia peb thauj cov ntsiab lus ntawm tag nrho peb cov ntaub ntawv los ntawm lub nplaub tshev - rau tam sim no, zoo li yog. Xaiv pab pawg Cov ntaub ntawv - Tau cov ntaub ntawv - Los ntawm cov ntaub ntawv - Los ntawm folder (Cov ntaub ntawv - Tau cov ntaub ntawv - Los ntawm cov ntaub ntawv - Los ntawm cov ntawv tais ceev tseg) thiab tom qab ntawd cov ntawv tais ceev tseg uas peb cov phau ntawv yog.

Hauv qhov rais saib ua ntej, nyem Hloov siab (Kev hloov pauv) or hloov (Kho kom raug):

Thiab ces nthuav cov ntsiab lus ntawm tag nrho cov downloaded ntaub ntawv (Binary) khawm nrog ob tug xub nyob rau hauv kab lus Cov ntsiab lus:

Power Query ntawm qhov piv txwv ntawm thawj cov ntaub ntawv (Vostok.xlsx) yuav nug peb lub npe ntawm daim ntawv uas peb xav nqa los ntawm txhua phau ntawv ua haujlwm - xaiv duab thiab nias OK:

Tom qab ntawd (qhov tseeb), ntau qhov xwm txheej uas tsis pom tseeb rau tus neeg siv yuav tshwm sim, qhov tshwm sim uas pom meej meej hauv vaj huam sib luag sab laug:

1. Power Query yuav coj thawj cov ntaub ntawv los ntawm cov ntawv tais ceev tseg (peb yuav muaj nws Vostok.xlsx — saib Cov ntaub ntawv piv txwv) ua piv txwv thiab imports nws cov ntsiab lus los ntawm kev tsim cov lus nug Hloov ua lwm yam ntaub ntawv. Cov lus nug no yuav muaj qee cov kauj ruam yooj yim xws li Tau qhov twg los (cov ntaub ntawv nkag) navigation (cov ntawv xaiv) thiab tejzaum nws tsa lub npe. Qhov kev thov no tsuas tuaj yeem thauj cov ntaub ntawv los ntawm ib cov ntaub ntawv tshwj xeeb Vostok.xlsx.
   
2. Raws li qhov kev thov no, cov haujlwm cuam tshuam nrog nws yuav raug tsim Hloov cov ntaub ntawv (qhia los ntawm tus yam ntxwv icon f_x), qhov twg cov ntaub ntawv qhov chaw yuav tsis tas mus li, tab sis tus nqi sib txawv - ib qho parameter. Yog li, qhov haujlwm no tuaj yeem rho tawm cov ntaub ntawv los ntawm txhua phau ntawv uas peb xa mus rau nws raws li kev sib cav.
   
3. Cov haujlwm yuav raug siv rau hauv lem rau txhua cov ntaub ntawv (Binary) los ntawm kem Cov ntsiab lus - kauj ruam yog lub luag hauj lwm rau qhov no Hu rau kev cai muaj nuj nqi hauv peb cov lus nug uas ntxiv ib kem rau cov npe ntawm cov ntaub ntawv Hloov cov ntaub ntawv nrog cov txiaj ntsig tau los ntawm txhua phau ntawv ua haujlwm:

   

4. Cov kab ntxiv raug tshem tawm.
5. Cov ntsiab lus ntawm cov rooj nested tau nthuav dav (qib Extended rooj kab) - thiab peb pom cov txiaj ntsig kawg ntawm kev sau cov ntaub ntawv los ntawm txhua phau ntawv:

   
   

Kauj Ruam 3. Sanding

Cov duab thaij duab dhau los qhia meej meej tias kev sib dhos ncaj qha "raws li yog" tig los ua qhov tsis zoo:

• Cov kab yog thim rov qab.
• Ntau cov kab ntxiv (kho thiab tsis yog xwb).
• Cov lus headers tsis pom tias yog headers thiab sib xyaw nrog cov ntaub ntawv.
  

Koj tuaj yeem kho tag nrho cov teeb meem no yooj yim heev - tsuas yog tweak qhov Hloov Cov Ntaub Ntawv Cov Lus Nug. Txhua qhov kev hloov kho uas peb ua rau nws yuav cia li poob rau hauv cov ntaub ntawv hloov pauv uas cuam tshuam, uas txhais tau tias lawv yuav siv tom qab thaum importing cov ntaub ntawv los ntawm txhua cov ntaub ntawv.

Los ntawm qhib kev thov Hloov ua lwm yam ntaub ntawv, ntxiv cov kauj ruam los lim cov kab tsis tsim nyog (piv txwv li, los ntawm kab Column2) thiab tsa cov kab lus nrog khawm Siv thawj kab ua headers (Siv thawj kab ua headers). Lub rooj yuav zoo dua.

Txhawm rau cov kab los ntawm cov ntaub ntawv sib txawv kom haum rau ib leeg tom qab, lawv yuav tsum muaj npe tib yam. Koj tuaj yeem ua qhov kev hloov pauv loj li no raws li daim ntawv teev npe yav dhau los tsim nrog ib kab ntawm M-code. Wb nias lub khawm dua f_x nyob rau hauv lub formula bar thiab ntxiv ib tug muaj nuj nqi hloov:

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

Txoj haujlwm no siv lub rooj los ntawm cov kauj ruam dhau los Cov taub hau siab thiab renames tag nrho cov kab nyob rau hauv nws raws li nested lookup list Headlines. Peb sib cav MissingField.Tsis quav ntsej yog xav tau kom ntawm cov npe uas nyob hauv phau ntawv teev npe, tab sis tsis nyob hauv lub rooj, qhov yuam kev tsis tshwm sim.

Qhov tseeb, yog tag nrho.

Rov qab mus rau qhov kev thov Reports peb yuav pom ib daim duab sib txawv kiag li - ntau nicer dua li yav dhau los:

• Dab tsi yog Power Query, Power Pivot, Power BI thiab vim li cas tus neeg siv Excel xav tau lawv
  
• Sau cov ntaub ntawv los ntawm tag nrho cov ntaub ntawv nyob rau hauv ib tug muab folders
• Sau cov ntaub ntawv los ntawm txhua nplooj ntawv ntawm phau ntawv rau hauv ib lub rooj