Mangani matebulo okhala ndi mitu yosiyanasiyana kuchokera m'mabuku angapo

Kupanga vuto

Tili ndi mafayilo angapo (muchitsanzo chathu - zidutswa 4, nthawi zambiri - monga momwe mukufunira) mufoda imodzi malipoti:

Mkati, mafayilo awa amawoneka motere:

Momwe:

• Tsamba la deta lomwe timafunikira limatchedwa nthawi zonse Photos, koma ikhoza kukhala paliponse mu bukhu la ntchito.
  
• Pamwamba pa pepala Photos Buku lililonse likhoza kukhala ndi mapepala ena.
• Matebulo okhala ndi ma data amakhala ndi mizere yosiyana ndipo akhoza kuyamba ndi mizere yosiyana patsamba logwirira ntchito.
• Mayina amizati yomweyi m'matebulo osiyanasiyana akhoza kusiyana (mwachitsanzo, Kuchuluka = ​​Kuchuluka = ​​Qty).
• Mizati m'matebulo ikhoza kukonzedwa mosiyanasiyana.

Ntchito: sonkhanitsani deta yogulitsa kuchokera pamafayilo onse kuchokera papepala Photos mu tebulo limodzi lodziwika kuti muthe kupanga chidule kapena kusanthula kwina kulikonse.

Gawo 1. Kukonzekera chikwatu cha mayina a magawo

Choyambirira kuchita ndikukonzekera bukhu lolozera lomwe lili ndi zosankha zonse za mayina amzambiri ndi matanthauzidwe ake olondola:

Timatembenuza mndandandawu kukhala tebulo lamphamvu la "smart" pogwiritsa ntchito batani la Format ngati tebulo pa tabu Kunyumba (Kunyumba - Pangani Monga Table) kapena njira yachidule ya kiyibodi Ctrl+T ndikuyiyika mu Power Query ndi lamulo Deta - Kuchokera pa Table/Range (Deta - Kuchokera pa Table/Range). M'mitundu yaposachedwa ya Excel, idasinthidwa kukhala Ndi masamba (Kuchokera pa pepala).

Pawindo la Power Query query editor, mwachizolowezi timachotsa sitepeyo Mtundu Wosinthidwa ndikuwonjezera sitepe yatsopano m'malo mwake podina batani f_xmu bar ya formula (ngati sichikuwoneka, mutha kuyiyambitsa pa tabu Review) ndikulowetsani fomula pamenepo muchilankhulo cha Power Query M:

=Table.ToRows(Magwero)

Lamuloli lidzatembenuza lomwe ladzaza mu sitepe yapitayi gwero mndandanda wa mndandanda wokhala ndi zisa (Mndandanda), womwe uliwonse, nawonso, uli ndi mindandanda Izo zinakhala kuchokera pamzere umodzi:

Tidzafunika deta yamtunduwu pakapita nthawi, pamene misa tikusinthiranso mitu kuchokera pamagome onse odzaza.

Mukamaliza kutembenuka, sankhani malamulo Kunyumba - Tsekani ndikunyamula - Tsekani ndikulowetsani… ndi mtundu wa import Ingopangani kulumikizana (Kunyumba — Tsekani & Katundu — Tsekani & Kwezani ku… — Pangani kulumikizana kokha) ndi kubwerera ku Excel.

Khwerero 2. Timayika chirichonse kuchokera ku mafayilo onse monga momwe zilili

Tsopano tiyeni tikweze zomwe zili m'mafayilo athu onse kuchokera pafoda - pakadali pano, monga momwe zilili. Kusankha magulu Deta - Pezani zambiri - Kuchokera pafayilo - Kuchokera mufoda (Deta - Pezani Zambiri - Kuchokera pafayilo - Kuchokera mufoda) ndiyeno chikwatu chomwe mabuku athu ali.

Pawindo lowonetseratu, dinani Sintha (Sinthani) or Change (Sinthani):

Kenako onjezerani zomwe zili m'mafayilo onse otsitsidwa (Binary) batani lokhala ndi mivi iwiri pamutu wamutu Timasangalala:

Power Query pachitsanzo cha fayilo yoyamba (Vostok.xlsx) adzatifunsa dzina la pepala lomwe tikufuna kutenga m'buku lililonse - sankhani Photos ndikudina OK:

Pambuyo pake (kwenikweni), zochitika zingapo zomwe sizidziwikiratu kwa wogwiritsa ntchito zidzachitika, zomwe zotsatira zake zikuwonekera bwino pagawo lakumanzere:

1. Power Query itenga fayilo yoyamba kuchokera pafoda (tidzakhala nayo Vostok.xlsx - onani Fayilo chitsanzo) monga chitsanzo ndikulowetsa zomwe zilimo popanga funso Sinthani fayilo yachitsanzo. Funso ili likhala ndi njira zosavuta monga gwero (kufikira mafayilo) Navigation (kusankha mapepala) ndipo mwina kukweza mitu. Pempholi litha kungotsegula data kuchokera mufayilo imodzi yokha Vostok.xlsx.
   
2. Kutengera pempholi, ntchito yolumikizidwa nayo idzapangidwa Sinthani fayilo (zosonyezedwa ndi chizindikiro cha chikhalidwe f_x), pomwe fayilo yoyambira sikhalanso yokhazikika, koma mtengo wosinthika - parameter. Choncho, ntchitoyi ikhoza kuchotsa deta kuchokera m'buku lililonse lomwe timalowetsamo ngati mkangano.
   
3. Ntchitoyi idzagwiritsidwa ntchito motsatira fayilo iliyonse (Binary) kuchokera pamndandanda Timasangalala - sitepe ndi udindo pa izi Imbani makonda ntchito mufunso lathu lomwe limawonjezera gawo pamndandanda wamafayilo Sinthani fayilo ndi zotsatira zochokera ku buku lililonse la ntchito:

   

4. Mizati yowonjezera imachotsedwa.
5. Zomwe zili m'matebulo osungidwa zimawonjezedwa (sitepe Mzere wowonjezera wa tebulo) - ndipo tikuwona zotsatira zomaliza za kusonkhanitsa deta kuchokera m'mabuku onse:

   
   

Gawo 3. Sanding

Chithunzi cham'mbuyomu chikuwonetsa bwino kuti msonkhano wachindunji "monga momwe uliri" udakhala wopanda khalidwe:

• Mizati yatembenuzidwa.
• Mizere yambiri yowonjezera (yopanda kanthu osati yokha).
• Mitu ya patebulo sikuwoneka ngati mitu ndipo imasakanizidwa ndi deta.
  

Mutha kukonza zovuta zonsezi mosavuta - ingosinthani funso la Convert Chitsanzo Fayilo. Zosintha zonse zomwe timapanga zidzangogwera mu Fayilo ya Convert yomwe ikugwirizana nayo, zomwe zikutanthauza kuti zidzagwiritsidwa ntchito pambuyo pake potumiza deta kuchokera pafayilo iliyonse.

Potsegula pempho Sinthani fayilo yachitsanzo, onjezani masitepe kuti musefe mizere yosafunika (mwachitsanzo, ndi column Column2) ndikukweza mitu ndi batani Gwiritsani ntchito mzere woyamba ngati mitu (Gwiritsani ntchito mizere yoyamba ngati mitu). Gome lidzawoneka bwino kwambiri.

Kuti mizati yochokera kumafayilo osiyanasiyana ikwane pansi pa mzake pambuyo pake, iyenera kutchulidwa chimodzimodzi. Mutha kupanganso mayina ambiri motengera chikwatu chomwe chidapangidwa kale ndi mzere umodzi wa M-code. Tiyeni tisindikizenso batani f_x mu bar ya formula ndikuwonjezera ntchito kuti musinthe:

= Table.RenameColumns(#"Mitu Yokwezeka", Mitu, MissingField.Ignore)

Ntchitoyi imatenga tebulo kuchokera pa sitepe yapitayi Mitu yokwezeka ndikusinthanso zipilala zonse momwemo molingana ndi mndandanda wakusaka Mitu. Mtsutso wachitatu MissingField.Ignore ikufunika kuti pamitu yomwe ili m'ndandanda, koma palibe patebulo, cholakwika sichichitika.

Kwenikweni, ndizo zonse.

Kubwerera ku pempho malipoti tiwona chithunzi chosiyana kwambiri - chabwino kwambiri kuposa choyambirira:

• Kodi Power Query, Power Pivot, Power BI ndi chiyani komanso chifukwa chiyani wogwiritsa ntchito Excel amafunikira
  
• Kusonkhanitsa deta kuchokera ku mafayilo onse mufoda yoperekedwa
• Kusonkhanitsa deta kuchokera pamasamba onse a bukhulo mu tebulo limodzi