Sesha amagama angukhiye embhalweni

Ukusesha amagama angukhiye kumbhalo womthombo kungomunye wemisebenzi evamile lapho usebenza nedatha. Ake sibheke isixazululo saso ngezindlela eziningi sisebenzisa isibonelo esilandelayo:

Ake sithi mina nawe sinohlu lwamagama angukhiye - amagama ezinhlobo zezimoto - kanye netafula elikhulu lazo zonke izinhlobo zezingxenye eziyisipele, lapho izincazelo ngezinye izikhathi zingaqukatha uhlobo olulodwa noma ezimbalwa ngesikhathi esisodwa, uma ingxenye eyisipele ilingana ngaphezu kweyodwa. uhlobo lwemoto. Umsebenzi wethu ukuthola nokubonisa wonke amagama angukhiye atholiwe kumaseli angomakhelwane ngohlamvu olunikeziwe lwesihlukanisi (isibonelo, ikhoma).

Indlela 1. Umbuzo Wamandla

Kunjalo, siqale siguqule amathebula ethu abe yi-dynamic (“smart”) sisebenzisa isinqamuleli sekhibhodi Ctrl+T noma imiyalo Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula), zinikeze amagama (ngokwesibonelo Izitembuи izingxenye ezisele) bese ulayisha ngayinye ngayinye kusihleli Sombuzo Wamandla ngokukhetha kuthebhu Idatha - Kusuka kuThebula/Ububanzi (Idatha - Kusuka Kuthebula/Ububanzi). Uma unezinguqulo ezindala ze-Excel 2010-2013, lapho i-Power Query ifakwe njengesengezo esihlukile, khona-ke inkinobho oyifunayo izoba kuthebhu. Umbuzo Wamandla. Uma unenguqulo entsha sha ye-Excel 365, cindezela inkinobho Kusukela kuThebula/Ububanzi kubizwe lapho manje Ngamaqabunga (Kusuka Eshidini).

Ngemva kokulayisha ithebula ngalinye Kumbuzo Wamandla, sibuyela emuva ku-Excel nomyalo Ikhaya — Vala bese ulayisha — Vala bese ulayisha ku… — Dala ukuxhumana kuphela (Ekhaya — Vala & Layisha — Vala & Layisha ku… — Dala ukuxhumana kuphela).

Manje masidale isicelo esiyimpinda izingxenye eziselengokuchofoza kwesokudla kuso bese ukhetha Isicelo esiyimpinda (Phinda umbuzo), bese uqamba kabusha isicelo sekhophi esiwumphumela sibe Imiphumela futhi sizoqhubeka nokusebenza naye.

I-logic yezenzo yilena elandelayo:

1. Kuthebhu ethuthukisiwe Yengeza ikholomu khetha iqembu Ikholomu yangokwezifiso (Engeza ikholomu — Ikholomu yangokwezifiso) bese ufaka ifomula = Imikhiqizo. Ngemva kokuchofoza OK sizothola ikholomu entsha, lapho esitokisini ngasinye kuzoba netafula elinesidleke elinohlu lwamagama ethu angukhiye - ama-automaker brand:

   

2. Sebenzisa inkinobho enemicibisholo ekabili enhlokweni yekholomu eyengeziwe ukuze unwebe wonke amathebula afakwe isidleke. Ngesikhathi esifanayo, imigqa enezincazelo zezingxenye eziyisipele izophindaphindeka ngokuphindaphinda inombolo yemikhiqizo, futhi sizothola wonke amapheya-inhlanganisela ye "spare part-brand":

   

3. Kuthebhu ethuthukisiwe Yengeza ikholomu khetha iqembu Ikholomu enemibandela (Ikholomu enemibandela) futhi usethe umbandela wokuhlola ukuvela kwegama elingukhiye (umkhiqizo) kumbhalo womthombo (incazelo yengxenye):

   

4. Ukuze wenze indaba yosesho ingazweli, engeza ngokwakho i-agumenti yesithathu kubha yefomula Qhathanisa.I-OrdinalIgnoreCase kumsebenzi wokuhlola ukwenzeka Umbhalo.Uqukethe (uma ibha yefomula ingabonakali, khona-ke inganikwa amandla kuthebhu Buyekeza):

   

5. Sihlunga ithebula eliwumphumela, sishiya elilodwa kuphela kukholomu yokugcina, okungukuthi okufanayo bese sikhipha ikholomu engadingekile Izehlakalo.
6. Ukuqoqa izincazelo ezifanayo ngomyalo Hlanganisa ngu ithebhu Uguquko (Guqula - Iqembu nge). Njengomsebenzi wokuhlanganisa, khetha Yonke imigqa (Yonke imigqa). Kokukhiphayo, sithola ikholomu enamathebula, equkethe yonke imininingwane yengxenye ngayinye eyisipele, okuhlanganisa nezinhlobo zama-automaker esiwadingayo:

   

7. Ukuze ukhiphe amamaki engxenye ngayinye, engeza enye ikholomu ebaliwe kuthebhu Ukwengeza Ikholomu – Ikholomu Ngokwezifiso (Engeza ikholomu — Ikholomu yangokwezifiso) futhi usebenzise ifomula ehlanganisa itafula (zitholakala kukholamu yethu Yekuchumana) kanye negama lekholomu ekhishiwe:

   

8. Sichofoza inkinobho enemicibisholo emibili enhlokweni yekholomu ewumphumela bese ukhetha umyalo Khipha amanani (Khipha amanani)ukukhipha izitembu nganoma yiluphi uhlamvu lwe-delimiter olufunayo:

   

9. Ukukhipha ikholomu engadingekile Yekuchumana.
10. Ukwengeza kuthebula eliwumphumela izingxenye ezinyamalale kulo, lapho kungekho mikhiqizo etholakele ezincazelweni, senza inqubo yokuhlanganisa umbuzo. Result ngesicelo sangempela izingxenye ezisele inkinobho Hlanganisa ithebhu Ikhaya (Ikhaya - Hlanganisa imibuzo). Uhlobo lokuxhuma - Ongaphandle Joyina Kwesokudla (Ukujoyina kwesokudla):

    

11. Okusele nje ukususa amakholomu engeziwe bese uqamba kabusha-ukuhambisa asele - futhi umsebenzi wethu usuxazululiwe:

    
    

Indlela 2. Amafomula

Uma unenguqulo ye-Excel 2016 noma eyakamuva, inkinga yethu ingaxazululwa ngendlela ehlangene nenhle kakhulu kusetshenziswa umsebenzi omusha. hlanganisa (TEXTJOIN):

I-logic yale fomula ilula:

• Umsebenzi SEARCH (THOLA) icinga ukwenzeka komkhiqizo ngamunye encazelweni yamanje yengxenye bese ibuyisela inombolo yomkhiqizo wophawu, okuqalwa kuyo igama elitholwe kuyo, noma iphutha elithi #VALUE! uma umkhiqizo ungekho encazelweni.
• Bese usebenzisa umsebenzi IF (UMA) и EOSHIBKA (ISERROR) sishintsha amaphutha ngochungechunge lombhalo olungenalutho "", kanye nezinombolo ze-ordinal zezinhlamvu ngamagama ebhrendi ngokwawo.
• Umphumela ofanayo wamaseli angenalutho namabhrendi atholiwe ahlanganiswa abe yiyunithi yezinhlamvu eyodwa ngohlamvu olunikeziwe lwesihlukanisi kusetshenziswa umsebenzi. hlanganisa (TEXTJOIN).

Ukuqhathanisa Ukusebenza kanye Nombuzo Wamandla Wokubhafa we-Speedup

Ukuze kuhlolwe ukusebenza, ake sithathe ithebula lezincazelo zezingxenye eziyi-100 njengedatha yokuqala. Kuyo sithola imiphumela elandelayo:

• Isikhathi sokubala kabusha ngamafomula (Indlela 2) - 9 sec. uma uqala ukukopisha ifomula kuyo yonke ikholomu kanye namasekhondi angu-2. ngokuphindaphindiwe (ukugcina kumthamo kuthinta, mhlawumbe).
  
• Isikhathi sokubuyekeza sombuzo Wombuzo Wamandla (Indlela 1) sibi kakhulu - imizuzwana engu-110.
  

Yiqiniso, okuningi kuncike ku-hardware ye-PC ethile kanye nenguqulo efakiwe ye-Office nezibuyekezo, kodwa isithombe sonke, ngicabanga, sicacile.

Ukuze usheshise umbuzo Wombuzo Wamandla, masibeke kubhafa ithebula lokubheka Izitembu, ngoba ayiguquki enqubweni yokwenziwa kombuzo futhi akudingekile ukuyibala kabusha njalo (njengoba i-Power Query de facto yenza). Kulokhu sisebenzisa umsebenzi Ithebula.Ibhafa kusukela kulimi olwakhelwe ngaphakathi Lombuzo Wamandla uM.

Ukuze wenze lokhu, vula umbuzo Imiphumela nakuthebhu Buyekeza cindezela inkinobho Umhleli Othuthukile (Buka — Isihleli Esithuthukile). Ewindini elivulayo, engeza umugqa onoguquko olusha UMark 2, okuzoba inguqulo ye-buffer yohla lwemibhalo yethu ye-automaker, futhi usebenzise lokhu kuguquguquka okusha kamuva emyalweni wombuzo olandelayo:

Ngemuva kokulungiswa okunjalo, isivinini sokuvuselela isicelo sethu sikhuphuka cishe izikhathi ezingu-7 - kufika kumasekhondi angu-15. Into ehluke kakhulu 🙂

• Ukusesha umbhalo okungaqondakali kokuthi Umbuzo Wamandla
• Ukushintshwa kombhalo ngobuningi ngamafomula
• Ukushintshwa kombhalo ngobuningi kumbuzo wamandla ngohlu.Buthelela umsebenzi