Tsvaga mazwi makuru muzvinyorwa

Kutsvaga mazwi akakosha mune chinyorwa chinyorwa nderimwe remabasa akajairika kana uchishanda nedata. Ngatitarisei mhinduro yayo munzira dzinoverengeka tichishandisa muenzaniso unotevera:

Ngatimboti iwe neni tine runyorwa rwemazwi akakosha - mazita emhando dzemotokari - uye tafura hombe yemarudzi ese ezvimedu, apo tsananguro dzimwe nguva dzinogona kuve nemhando imwe chete kana akati wandei panguva imwe chete, kana chikamu chinokodzera kupfuura chimwe. mhando yemota. Basa redu nderekutsvaga nekuratidza mazwi ese akaonekwa mumaseru akavakidzana kuburikidza neakapihwa patsanuro hunhu (semuenzaniso, koma).

Nzira 1. Power Query

Ehe, chekutanga tinoshandura matafura edu kuita ane simba ("smart") tichishandisa nzira yekudimbudzira keyboard Ctrl+T kana mirairo Kumba – Fomati setafura (Kumba - Fomati seTafura), vape mazita (somuenzaniso Stampsи Spare parts) uye rodha imwe neimwe muSimba Query edhita nekusarudza pane iyo tab Data – Kubva Tafura/Range (Data - Kubva Tafura/Range). Kana iwe uine ekare mavhezheni eExcel 2010-2013, uko Power Query inoisirwa seyakasiyana yekuwedzera-mukati, ipapo bhatani rinodiwa richange riri patebhu. Simba Query. Kana iwe uine mhando nyowani yeExcel 365, ipapo bhatani Kubva Tafura/Range kudaidzwa ikoko zvino Nemashizha (Kubva Pepa).

Mushure mekutakura tafura yega yega muPower Query, tinodzokera kuExcel nemurairo Musha — Vhara uye rongedza — Vhara uye rongedza ku… — Gadzira chinongedzo chete (Kumba - Vhara & Rodha - Vhara & Rodha ku… - Ingo gadzira chinongedzo).

Zvino ngatigadzirei chikumbiro chakafanana Spare partsnekudzvanya-kurudyi pairi uye kusarudza Duplicate chikumbiro (Duplicate mubvunzo), wozopa zita rekuti chikumbiro chekopi chabuda kuti The zvabuda uye ticharamba tichishanda naye.

Iyo logic yezviito ndeyekutevera:

1. Pa Advanced tabo Kuwedzera chikamu sarudza timu Custom column (Wedzera koramu - Tsika mbiru) uye isa fomula = Zvigadzirwa. Mushure mekudzvanya pa OK isu tichawana mbiru itsva, umo mune imwe neimwe sero pachava netafura inested ine runyorwa rwemashoko edu makuru - automaker brands:

   

2. Shandisa bhatani rine miseve yakapetwa kaviri mumusoro wekoramu yakawedzerwa kuwedzera matafura ese anested. Panguva imwecheteyo, mitsara ine tsananguro yezvisara inowanda nekuwanda kwehuwandu hwemhando, uye isu tichawana ese anogoneka maviri-musanganiswa we "spare part-brand":

   

3. Pa Advanced tabo Kuwedzera chikamu sarudza timu Conditional column (Conditional column) uye isa mamiriro ekutarisa kuitika kweizwi rakakosha (brand) mune chinyorwa chinyorwa (chikamu tsananguro):

   

4. Kuita kuti nyaya yekutsvaga isanzwe, wedzera nemaoko nharo yechitatu mubhawa reformula Compare.OrdinalIgnoreCase kune chiitiko chekutarisa basa Zvinyorwa.Zvine (kana iyo formula bar isingaonekwe, saka inogona kugoneswa pane iyo tab ongorora):

   

5. Isu tinosefa tafura inoguma, tichisiya imwe chete mumutsara wekupedzisira, kureva machisi uye kubvisa iyo isingakoshi mbiru Zviitiko.
6. Kubatanidza tsananguro dzakafanana nemurairo Gadzirira ne Tab Kuchinja (Shandura - Boka ne). Sekuita kwekuunganidza, sarudza Mitsetse yose (Mitsara yese). Pakubuda, tinowana koramu ine matafura, ayo ane ese maficha ega ega chikamu, kusanganisira mhando dzeautomaker dzatinoda:

   

7. Kuti utore mamakisi echikamu chimwe nechimwe, wedzera imwe yakaverengerwa koramu patebhu Kuwedzera Column - Custom Column (Wedzera koramu - Tsika mbiru) uye shandisa fomula ine tafura (iyo iri mukoramu yedu Details) uye zita rekoramu yakabviswa:

   

8. Isu tinodzvanya bhatani nemiseve yakapetwa kaviri mumusoro wekoramu inoguma uye sarudza iwo rairo Bvisa tsika (Bvisa kukosha)kuburitsa zvitambi nechero delimiter character yaunoda:

   

9. Kubvisa koramu isingakoshi Details.
10. Kuwedzera kune tafura inokonzeresa zvikamu zvakanyangarika kubva pairi, pasina mabhureki akawanikwa mune tsananguro, tinoita maitiro ekubatanidza mubvunzo. mugumisiro nechikumbiro chepakutanga Spare parts bhatani Gadzirai Tab Home (Kumba - Batanidza mibvunzo). Mhando yekubatanidza - Outer Join Right (Kudyidzana kwekunze):

    

11. Chasara ndechekubvisa mamwe makoramu uye kutumidza zita-kufambisa asara - uye basa redu rinogadziriswa:

    
    

Nzira 2. Zvirongwa

Kana iwe uine vhezheni yeExcel 2016 kana gare gare, saka dambudziko redu rinogona kugadziriswa nenzira yakanyatso compact uye inoyevedza uchishandisa basa idzva. COMBINE (TEXTJOIN):

The logic kuseri kweformula iri nyore:

• basa SEARCH (TSVAGA) inotsvaga kuitika kwemhando yega yega mune tsananguro yazvino yechikamu uye inodzosera ingave nhamba yesiriri yechiratidzo, kutangira pakawanikwa brand, kana kukanganisa #VALUE! kana mhando isiri murondedzero.
• Zvadaro kushandisa basa IF (KANA) и EOSHIBKA (ISERROR) tinotsiva zvikanganiso nemutsara usina chinhu "", uye nhamba dze ordinal dzevatambi vane mazita emhando ivo pachavo.
• Iwo anokonzeresa mutsara wemaseru asina chinhu uye akawanikwa mabhureki anounganidzwa kuita tambo imwe kuburikidza neakapihwa separator hunhu uchishandisa basa. COMBINE (TEXTJOIN).

Kuita Kuenzanisa uye Simba Mubvunzo Mubvunzo Buffering yeSpeedup

Kuyedzwa kwekuita, ngatitorei tafura ye100 spare parts tsananguro sedata rekutanga. Pairi tinowana zvinotevera zvinotevera:

• Nguva yekudzokorora nemafomati (Nzira 2) - 9 sec. paunotanga kukopa fomula kuchikamu chose uye 2 sec. pakudzokororwa (buffering inokanganisa, pamwe).
  
• Iyo nguva yekuvandudza yemubvunzo weSimba Query (Nzira 1) yakanyanya kushata - 110 masekonzi.
  

Ehe, zvakawanda zvinoenderana nehardware yeimwe PC uye iyo yakaiswa vhezheni yeHofisi uye inogadziridza, asi iyo yakazara mufananidzo, ndinofunga, yakajeka.

Kuti tikurumidze kubvunza Power Query, ngatisengedzei tafura yekutarisa Stamps, nekuti haishanduke mukuita kwemubvunzo uye hazvifanirwe kugara uchiiverengera (sezvinoita Power Query de facto). Nokuda kweizvi tinoshandisa basa Table.Buffer kubva kune yakavakirwa-mukati Power Query mutauro M.

Kuti uite izvi, vhura mubvunzo The zvabuda uye pa tab ongorora dzvanya bhatani Advanced Editor (Ona - Yepamberi Mupepeti). Muhwindo rinovhurwa, wedzera mutsara une shanduko itsva Mako 2, inova vhezheni yakabhurwa yedhairekitori yedu yeautomaker, uye shandisa iyi shanduko nyowani gare gare mune inotevera bvunzo yekuraira:

Mushure mekunatswa kwakadaro, kukurumidza kwekuvandudza kwechikumbiro chedu kunowedzera kanenge ka7 - kusvika kumasekonzi gumi nemashanu. Chinhu chakasiyana 🙂

• Kutsvaga mavara asina kujeka muKubvunza Kwesimba
• Kutsiviwa kwemavara akawanda nemafomula
• Kutsiviwa kwemavara akawanda muKubvunza Kwesimba neRondedzero.Unganisa basa