Nemo kalmomi masu mahimmanci a cikin rubutu

Neman kalmomi masu mahimmanci a cikin rubutun tushe shine ɗayan ayyuka na yau da kullun yayin aiki tare da bayanai. Bari mu kalli maganinta ta hanyoyi da dama ta amfani da misali mai zuwa:

Bari mu ɗauka cewa ni da ku muna da jerin mahimman kalmomi - sunayen samfuran mota - da babban tebur na kowane nau'in kayan gyara, inda kwatancin wani lokaci na iya ƙunshi nau'ikan nau'ikan nau'ikan iri ɗaya ko da yawa a lokaci ɗaya, idan kayan aikin ya dace da fiye da ɗaya. alamar mota. Ayyukanmu shine nemo da nuna duk mahimman kalmomin da aka gano a cikin sel maƙwabta ta hanyar da aka ba da halayen rabuwa (misali, waƙafi).

Hanyar 1. Tambayar Wuta

Tabbas, da farko za mu juya teburin mu zuwa mai ƙarfi ("smart") ta amfani da gajeriyar hanyar keyboard Ctrl+T ko umarni Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur), ba su sunaye (misali Takamaimanи Sassan ɓangarori) sannan a loda daya bayan daya a cikin editan Query ta zabi kan shafin Bayanai - Daga Tebur / Range (Bayanai - Daga Tebura/Range). Idan kuna da tsoffin juzu'in Excel 2010-2013, inda aka shigar da Query Query azaman ƙari daban, to maɓallin da ake so zai kasance akan shafin. Tambayar .arfi. Idan kuna da sabon sigar Excel 365, sannan maɓallin Daga Tebura/Range kira can yanzu Tare da ganye (Daga Sheet).

Bayan loda kowane tebur a cikin Query Query, zamu koma Excel tare da umarnin Gida - Rufe kuma ɗauka - Rufe kuma ɗauka zuwa… - Ƙirƙiri haɗi kawai (Gida - Rufe & Load - Rufe & Loda zuwa… - Ƙirƙiri haɗi kawai).

Yanzu bari mu ƙirƙiri buƙatun kwafin Sassan ɓangarorita hanyar danna-dama akansa kuma zaɓi Kwafin buƙata (Tambayoyin Kwafi), sannan sake suna sakamakon buƙatar kwafin suna zuwa Sakamakon kuma za mu ci gaba da aiki da shi.

Hankalin ayyuka shine kamar haka:

1. A kan Babba shafin Ƙara ginshiƙi zabi tawagar Rukunin al'ada (Ƙara shafi - ginshiƙi na al'ada) kuma shigar da dabara = Alamomi. Bayan danna kan OK za mu sami sabon ginshiƙi, inda a cikin kowane tantanin halitta za a sami tebur na gida tare da jerin kalmomin mu - automaker brands:

   

2. Yi amfani da maɓallin tare da kibau biyu a cikin kan jigon da aka ƙara don faɗaɗa duk teburi masu gida. A lokaci guda, layukan da ke da kwatancen samfuran kayan aikin za su ninka ta hanyar adadin nau'ikan samfuran, kuma za mu sami duk yuwuwar nau'i-nau'i-haɗe-haɗe na "sassarar-samfurin":

   

3. A kan Babba shafin Ƙara ginshiƙi zabi tawagar Sharadi na sharadi (Sharadi na sharadi) kuma saita sharadi don bincika faruwar wata kalma (alama) a cikin rubutun tushe (bayanin sashi):

   

4. Don sanya yanayin binciken ya rasa hankali, ƙara hujja ta uku da hannu a cikin ma'aunin dabara Kwatanta.OrdinalIgnoreCase zuwa aikin duba abin da ya faru Rubutu. Ya ƙunshi (idan ba'a iya ganin ma'aunin dabarar, to ana iya kunna shi akan shafin review):

   

5. Muna tace teburin da aka samu, muna barin ɗaya kawai a cikin ginshiƙi na ƙarshe, watau matches da cire ginshiƙin da ba dole ba Abubuwan da ke faruwa.
6. Haɗa kwatance iri ɗaya tare da umarni Rukuni ta tab Sake Kama (Canza - Rukuni ta hanyar). A matsayin aikin tarawa, zaɓi Duk layi (Duk layuka). A wurin fitarwa, muna samun ginshiƙi tare da teburi, wanda ya ƙunshi duk cikakkun bayanai na kowane ɓangaren kayan gyara, gami da samfuran kera motoci da muke buƙata:

   

7. Don cire maki ga kowane bangare, ƙara wani shafi mai ƙididdigewa akan shafin Ƙara ginshiƙi - Shagon Al'ada (Ƙara shafi - ginshiƙi na al'ada) kuma yi amfani da dabarar da ta ƙunshi tebur (suna cikin rukuninmu details) da sunan ginshiƙin da aka ciro:

   

8. Muna danna maballin tare da kibiyoyi biyu a cikin taken shafi na sakamakon kuma zaɓi umarnin Cire ƙima (Cire dabi'u)don fitar da tambari tare da kowane hali mai iyaka da kuke so:

   

9. Cire ginshiƙin da ba dole ba details.
10. Don ƙara zuwa teburin da aka samu sassan da suka ɓace daga gare ta, inda ba a sami alamun alama a cikin kwatancen ba, muna aiwatar da hanyar haɗa tambayar. Sakamako tare da buƙatun asali Sassan ɓangarori button hada tab Gida (Gida - Tambayoyin Haɗa). Nau'in haɗin kai - Haɗa Dama (Haɗin waje na dama):

    

11. Abin da ya rage shi ne cire ƙarin ginshiƙai kuma a sake suna-matsar da sauran - kuma an warware aikinmu:

    
    

Hanyar 2. Formules

Idan kuna da sigar Excel 2016 ko kuma daga baya, to ana iya magance matsalarmu ta cikin ƙaƙƙarfan tsari da kyakkyawar hanya ta amfani da sabon aikin. HADE (TEXTJOIN):

Hankalin da ke bayan wannan dabara yana da sauƙi:

• aiki SEARCH (NEMO) ya nemo abin da ya faru na kowace alama bi da bi a cikin bayanin da ake ciki yanzu na sashin kuma ya mayar da ko dai serial number na alamar, farawa daga inda aka samo alamar, ko kuma kuskuren #VALUE! idan alamar ba a cikin bayanin ba.
• Sannan amfani da aikin IF (IF) и EOSHIBKA (ISERROR) muna maye gurbin kurakurai tare da fanko rubutu kirtani “”, da ordinal lambobi na haruffa tare da alamar sunayen kansu.
• Sakamakon tsararrun sel marasa komai da samfuran samfuran da aka samo an haɗa su cikin kirtani ɗaya ta hanyar da aka ba da halayen rabuwa ta amfani da aikin. HADE (TEXTJOIN).

Kwatanta Ayyuka da Tambayoyin Neman Ƙarfi don Ƙaddamarwa

Don gwajin aiki, bari mu ɗauki tebur na bayanin kayan gyara 100 azaman bayanan farko. A kan shi muna samun sakamako masu zuwa:

• Lokacin sake kirgawa ta dabara (Hanyar 2) - 9 sec. lokacin da kuka fara kwafi dabarar zuwa gabaɗayan ginshiƙi da 2 s. a maimaita (buffering yana shafar, mai yiwuwa).
  
• Lokacin sabuntawa na tambayar Tambayoyin Wuta (Hanyar 1) ya fi muni - 110 seconds.
  

Tabbas, da yawa ya dogara da kayan aikin PC na musamman da sigar da aka shigar na Office da sabuntawa, amma gabaɗaya hoto, ina tsammanin, a bayyane yake.

Don hanzarta tambayar Tambayoyin Wuta, bari mu ajiye teburin neman Takamaiman, saboda ba ya canzawa a cikin tsarin aiwatar da tambaya kuma ba lallai ba ne a sake ƙididdige shi akai-akai (kamar yadda Power Query de facto ke yi). Don wannan muna amfani da aikin Tebur.Buffer daga ginanniyar Harshen Tambayar Wuta M.

Don yin wannan, buɗe tambaya Sakamakon kuma a kan tab review danna maɓallin Babban Edita (Duba - Babban Edita). A cikin taga da ke buɗewa, ƙara layi tare da sabon canji Marki 2, wanda zai zama buffered version of your automakers directory, da kuma amfani da wannan sabon m daga baya a cikin wadannan umarnin tambaya:

Bayan irin wannan gyare-gyare, saurin sabuntawa na buƙatarmu yana ƙaruwa da kusan sau 7 - har zuwa 15 seconds. Wani abu daban 🙂

• Binciken rubutu mai ban mamaki a cikin Query Query
• Maye gurbin rubutu mai girma tare da dabaru
• Maye gurbin rubutu mai girma a cikin Tambayar Wuta tare da Lissafi.Tara aikin