Yakha amatafula anezihloko ezahlukene ezivela ezincwadini eziningi

Ukwakhiwa kwenkinga

Sinamafayela amaningana (esibonelweni sethu - izingcezu ezingu-4, esimweni esijwayelekile - amaningi ngendlela oyithandayo) kufolda eyodwa Imibiko:

Ngaphakathi, lawa mafayela abukeka kanje:

Lapho:

• I-data sheet esiyidingayo ihlale ibizwa Izithombe, kodwa ingaba noma yikuphi encwadini yokusebenzela.
  
• Ngaphandle kweshidi Izithombe Incwadi ngayinye ingaba namanye amashidi.
• Amathebula anedatha anenombolo ehlukile yemigqa futhi angase aqale ngomugqa ohlukile ekhasini lokusebenza.
• Amagama amakholomu afanayo kumathebula ahlukene angase ahluke (isibonelo, Ubuningi = Ubuningi = Ubuningi).
• Amakholomu emathebulani angahlelwa ngendlela ehlukile.

Umsebenzi: qoqa idatha yokuthengisa kuwo wonke amafayela eshidini Izithombe etafuleni elilodwa elivamile ukuze kamuva kwakheke isifinyezo nanoma yiziphi ezinye izibalo kulo.

Isinyathelo 1. Ukulungiselela uhla lwemibhalo lwamagama ekholomu

Into yokuqala okufanele yenziwe ukulungisa ibhuku lereferensi elinazo zonke izinketho zamagama amakholomu kanye nencazelo yawo efanele:

Siguqula lolu hlu lube ithebula “elihlakaniphile” eliguquguqukayo sisebenzisa inkinobho ethi Fometha njengethebula kuthebhu Ikhaya (Ekhaya — Fometha Njengethebula) noma isinqamuleli sekhibhodi Ctrl+T bese uyilayisha kokuthi Umbuzo Wamandla ngomyalo Idatha - Kusuka kuThebula/Ububanzi (Idatha - Kusuka Kuthebula/Ububanzi). Ezinguqulweni zakamuva ze-Excel, iqanjwe kabusha ngokuthi Ngamaqabunga (Kusuka eshidini).

Ewindini lomhleli wombuzo wamandla, ngokwesiko sisusa isinyathelo Uhlobo Olushintshiwe bese wengeza isinyathelo esisha esikhundleni saso ngokuchofoza inkinobho f_xkubha yefomula (uma ingabonakali, ungayinika amandla kuthebhu Buyekeza) bese ufaka ifomula lapho ngolimi olwakhelwe ngaphakathi Lombuzo Wamandla M:

=Table.ToRows(Umthombo)

Lo myalo uzoguqula olayishwe esinyathelweni sangaphambilini Umthombo Ithebula lereferensi ohlwini oluhlanganisa izinhlu ezifakwe isidleke (Uhlu), ngalinye, elinye, eliyipheya yamanani Kwaba-kwaba kusuka kulayini owodwa:

Sizodinga lolu hlobo lwedatha kamuva, lapho siqamba kabusha izihloko eziningi kuwo wonke amathebula alayishiwe.

Ngemva kokuqeda ukuguqulwa, khetha imiyalo Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... kanye nohlobo lokungenisa Vele udale uxhumano (Ekhaya — Vala&Layisha — Vala&Layisha ku… — Dala ukuxhumana kuphela) bese ubuyela ku-Excel.

Isinyathelo sesi-2. Silayisha yonke into kuwo wonke amafayela njengoba injalo

Manje ake silayishe okuqukethwe kwawo wonke amafayela ethu kufolda - okwamanje, njengoba kunjalo. Ukukhetha amaqembu Idatha - Thola idatha - Kusuka kufayela - Kusuka kufolda (Idatha - Thola Idatha - Kusuka kufayela - Kusuka kufolda) kanye nefolda lapho izincwadi zethu eziwumthombo zikhona.

Ewindini lokuhlola kuqala, chofoza Convert (Guqula) or Guqula (Hlela):

Bese wandisa okuqukethwe kwawo wonke amafayela alandiwe (Kanambambili) inkinobho enemicibisholo emibili esihlokweni sekholomu Okuqukethwe:

Umbuzo Wamandla esibonelweni sefayela lokuqala (I-Vostok.xlsx) uzosibuza igama lephepha esifuna ukulithatha encwadini ngayinye yokusebenzela - khetha Izithombe bese ucindezela u-OK:

Ngemva kwalokho (eqinisweni), izenzakalo ezimbalwa ezingabonakali kumsebenzisi zizokwenzeka, imiphumela yazo ibonakala ngokucacile kuphaneli yesokunxele:

1. Umbuzo Wamandla uzothatha ifayela lokuqala kufolda (sizoba nalo I-Vostok.xlsx — bheka Isibonelo sefayela) njengesibonelo futhi ingenisa okuqukethwe kwayo ngokudala umbuzo Guqula ifayela lesampula. Lo mbuzo uzoba nezinyathelo ezilula ezifana Umthombo (ukufinyelela ifayela) Navigation (ukukhethwa kweshidi) kanye nokuphakamisa izihloko. Lesi sicelo singalayisha kuphela idatha evela kufayela elithile elithile I-Vostok.xlsx.
   
2. Ngokusekelwe kulesi sicelo, umsebenzi ohlotshaniswa naso uzodalwa Guqula ifayela (kuboniswa uphawu lwesici f_x), lapho ifayela lomthombo lingeke lisaba njalo, kodwa inani eliguquguqukayo - ipharamitha. Ngakho, lo msebenzi ungakhipha idatha kunoma iyiphi incwadi esingena kuyo njengengxabano.
   
3. Umsebenzi uzosetshenziswa ngokulandelana kwefayela ngalinye (Kanambambili) elisuka kukholomu Okuqukethwe – isinyathelo sinesibopho salokhu Shayela umsebenzi wangokwezifiso embuzweni wethu owengeza ikholomu ohlwini lwamafayela Guqula ifayela ngemiphumela yokungenisa evela encwadini yokusebenzela ngayinye:

   

4. Amakholomu engeziwe asusiwe.
5. Okuqukethwe kwamathebula afakwe esidlekeni kuyanwetshwa (isinyathelo Ikholomu yethebula enwetshiwe) - futhi sibona imiphumela yokugcina yokuqoqwa kwedatha kuzo zonke izincwadi:

   
   

Isinyathelo sesi-3. I-Sanding

Isithombe-skrini sangaphambilini sibonisa ngokusobala ukuthi umhlangano oqondile “njengoba unjalo” uphenduke owekhwalithi embi:

• Amakholomu ahlehlisiwe.
• Imigqa eminingi eyengeziwe (ayinalutho futhi hhayi kuphela).
• Izihloko zethebula azibonwa njengezihloko futhi zixutshwa nedatha.
  

Ungalungisa zonke lezi zinkinga kalula kakhulu - vele ulungise umbuzo we-Convert Sample File. Zonke izinguquko esizenzayo kulo zizowela ngokuzenzakalelayo kumsebenzi ohlobene nokuguqula ifayela, okusho ukuthi zizosetshenziswa kamuva lapho singenisa idatha kufayela ngalinye.

Ngokuvula isicelo Guqula ifayela lesampula, engeza izinyathelo zokuhlunga imigqa engadingekile (ngokwesibonelo, ngekholomu Column2) bese bephakamisa izihloko ngenkinobho Sebenzisa umugqa wokuqala njengezihloko (Sebenzisa umugqa wokuqala njengezihloko). Ithebula lizobukeka lingcono kakhulu.

Ukuze amakholomu asuka kumafayela ahlukene alingane ngokuzenzakalelayo ngaphansi kwelinye kamuva, kufanele aqanjwe ngendlela efanayo. Ungenza ukuqamba kabusha okuningi okunjalo ngokohlu lwemibhalo olwakhiwe ngaphambilini ngomugqa owodwa we-M-code. Asicindezele inkinobho futhi f_x kubha yefomula bese wengeza umsebenzi ozowushintsha:

= Ithebula.Qamba Kabusha Amakholomu(#”Izihloko Eziphakanyisiwe”, Izihloko, IndawoElahlekile.Ziba)

Lo msebenzi uthatha ithebula esinyathelweni sangaphambilini Izihloko eziphakanyisiwe futhi iqambe kabusha wonke amakholomu kuyo ngokohlu lokubheka olusesidlekeni Izihloko. Impikiswano yesithathu MissingField.Ziba kuyadingeka ukuze kulezo zihloko ezisohlwini lwemibhalo, kodwa ezingekho kuthebula, kungenzeki iphutha.

Empeleni, yilokho kuphela.

Ibuyela esicelweni Imibiko sizobona isithombe esihluke ngokuphelele - esihle kakhulu kunesedlule:

• Uyini Umbuzo Wamandla, I-Power Pivot, Power BI nokuthi kungani umsebenzisi we-Excel ezidinga
  
• Iqoqa idatha kuwo wonke amafayela kufolda enikeziwe
• Iqoqa idatha kusuka kuwo wonke amashidi encwadi etafuleni elilodwa