Chwilio am eiriau allweddol yn y testun

Chwilio am eiriau allweddol mewn testun ffynhonnell yw un o'r tasgau mwyaf cyffredin wrth weithio gyda data. Edrychwn ar ei ddatrysiad mewn sawl ffordd gan ddefnyddio'r enghraifft ganlynol:

Gadewch i ni dybio bod gennych chi a minnau restr o eiriau allweddol - enwau brandiau ceir - a thabl mawr o bob math o rannau sbâr, lle gall disgrifiadau weithiau gynnwys un neu sawl brand o'r fath ar unwaith, os yw'r rhan sbâr yn ffitio mwy nag un. brand y car. Ein tasg ni yw darganfod ac arddangos yr holl eiriau allweddol a ganfuwyd mewn celloedd cyfagos trwy nod gwahanydd penodol (er enghraifft, coma).

Dull 1. Pŵer Ymholiad

Wrth gwrs, yn gyntaf rydym yn troi ein tablau yn ddeinamig (“smart”) gan ddefnyddio llwybr byr bysellfwrdd Ctrl+T neu orchmynion Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl), rhowch enwau iddynt (er enghraifft Stampiauи Rhannau sbâr) a llwythwch fesul un i'r golygydd Power Query trwy ddewis ar y tab Data – O Dabl/Amrediad (Data — O Dabl/Ystod). Os oes gennych fersiynau hŷn o Excel 2010-2013, lle mae Power Query wedi'i osod fel ychwanegiad ar wahân, yna bydd y botwm dymunol ar y tab Ymholiad Pwer. Os oes gennych chi fersiwn newydd sbon o Excel 365, yna'r botwm O'r Tabl / Ystod galw yno yn awr Gyda dail (O'r Daflen).

Ar ôl llwytho pob bwrdd yn Power Query, rydym yn dychwelyd yn ôl i Excel gyda'r gorchymyn Hafan — Cau a llwytho — Cau a llwytho i… — Creu cysylltiad yn unig (Cartref - Cau a Llwytho - Cau a Llwytho i ... - Creu cysylltiad yn unig).

Nawr, gadewch i ni greu cais dyblyg Rhannau sbârtrwy dde-glicio arno a dewis Cais dyblyg (ymholiad dyblyg), yna ailenwi'r cais copi canlyniadol i Mae'r canlyniadau a byddwn yn parhau i weithio gydag ef.

Mae rhesymeg y gweithredoedd fel a ganlyn:

1. Ar y tab Advanced Ychwanegu colofn dewis tîm Colofn personol (Ychwanegu colofn - colofn personol) a rhowch y fformiwla = Brandiau. Ar ôl clicio ar OK byddwn yn cael colofn newydd, lle ym mhob cell bydd bwrdd nythu gyda rhestr o'n geiriau allweddol - brandiau automaker:

   

2. Defnyddiwch y botwm gyda saethau dwbl ym mhennyn y golofn a ychwanegwyd i ehangu'r holl dablau nythu. Ar yr un pryd, bydd y llinellau â disgrifiadau o rannau sbâr yn lluosi â lluosrif o nifer y brandiau, a byddwn yn cael yr holl gyfuniadau parau posibl o “frand rhan sbâr”:

   

3. Ar y tab Advanced Ychwanegu colofn dewis tîm Colofn amodol (Colofn amodol) a gosod amod ar gyfer gwirio bod allweddair (brand) yn digwydd yn y testun ffynhonnell (disgrifiad rhannol):

   

4. I wneud yr achos chwilio yn ansensitif, ychwanegwch y drydedd ddadl â llaw yn y bar fformiwla Compare.OrdinalIgnoreCase i'r swyddogaeth gwirio digwyddiad Testun.Cynnwys (os nad yw'r bar fformiwla yn weladwy, yna gellir ei alluogi ar y tab adolygiad):

   

5. Rydym yn hidlo'r tabl canlyniadol, gan adael dim ond rhai yn y golofn olaf, hy yn cyfateb ac yn dileu'r golofn ddiangen Digwyddiadau.
6. Grwpio disgrifiadau unfath gyda'r gorchymyn Grŵp erbyn tab Trawsnewid (Trawsnewid - Grwpio fesul). Fel gweithrediad agregu, dewiswch Pob llinell (Pob rhes). Yn yr allbwn, rydym yn cael colofn gyda thablau, sy'n cynnwys yr holl fanylion ar gyfer pob rhan sbâr, gan gynnwys y brandiau o wneuthurwyr ceir sydd eu hangen arnom:

   

7. I echdynnu graddau ar gyfer pob rhan, ychwanegwch golofn arall wedi'i chyfrifo ar y tab Ychwanegu Colofn - Colofn Custom (Ychwanegu colofn - colofn personol) a defnyddiwch fformiwla sy'n cynnwys tabl (maen nhw wedi'u lleoli yn ein colofn ni manylion) ac enw'r golofn a dynnwyd:

   

8. Rydym yn clicio ar y botwm gyda saethau dwbl ym mhennyn y golofn sy'n deillio o hyn a dewis y gorchymyn Gwerthoedd echdynnu (Gwerthoedd echdynnu)i allbynnu stampiau gydag unrhyw nod terfynydd rydych chi ei eisiau:

   

9. Cael gwared ar golofn ddiangen manylion.
10. I ychwanegu at y tabl canlyniadol y rhannau a ddiflannodd ohono, lle na chanfuwyd unrhyw frandiau yn y disgrifiadau, rydym yn perfformio'r weithdrefn ar gyfer cyfuno'r ymholiad Canlyniad gyda chais gwreiddiol Rhannau sbâr botwm Cyfunwch tab Hafan (Cartref - Cyfuno ymholiadau). Math o gysylltiad - Ymuno Allanol Ar y Dde (Ymuniad allanol dde):

    

11. Y cyfan sydd ar ôl yw tynnu'r colofnau ychwanegol ac ailenwi'r rhai sy'n weddill - ac mae ein tasg wedi'i datrys:

    
    

Dull 2. Fformiwlâu

Os oes gennych fersiwn o Excel 2016 neu ddiweddarach, yna gellir datrys ein problem mewn ffordd gryno a chain iawn gan ddefnyddio'r swyddogaeth newydd COMBINE (TEXTJOIN):

Mae'r rhesymeg y tu ôl i'r fformiwla hon yn syml:

• swyddogaeth CHWILIO (DARGANFOD) yn chwilio am ddigwyddiad pob brand yn ei dro yn y disgrifiad cyfredol o'r rhan ac yn dychwelyd naill ai rhif cyfresol y symbol, gan ddechrau y canfuwyd y brand ohono, neu'r gwall #VALUE! os nad yw'r brand yn y disgrifiad.
• Yna defnyddio'r swyddogaeth IF (OS) и EOSHIBKA (ISERROR) rydym yn disodli'r gwallau gyda llinyn testun gwag “”, a rhifau trefnol y nodau gyda'r enwau brand eu hunain.
• Mae'r casgliad canlyniadol o gelloedd gwag a brandiau a ddarganfuwyd yn cael eu cydosod yn llinyn sengl trwy nod gwahanydd penodol gan ddefnyddio'r ffwythiant COMBINE (TEXTJOIN).

Cymhariaeth Perfformiad ac Ymholiad Pŵer Clustogi ar gyfer Cyflymder

Ar gyfer profi perfformiad, gadewch i ni gymryd tabl o 100 o ddisgrifiadau rhannau sbâr fel data cychwynnol. Ar hyn rydym yn cael y canlyniadau canlynol:

• Amser ail-gyfrifo yn ôl fformiwlâu (Dull 2) – 9 eiliad. pan fyddwch chi'n copïo'r fformiwla gyntaf i'r golofn gyfan a 2 eiliad. ar dro ar ôl tro (byffro yn effeithio, yn ôl pob tebyg).
  
• Mae amser diweddaru'r ymholiad Power Query (Dull 1) yn waeth o lawer - 110 eiliad.
  

Wrth gwrs, mae llawer yn dibynnu ar galedwedd cyfrifiadur personol penodol a'r fersiwn gosodedig o Office a diweddariadau, ond mae'r darlun cyffredinol, rwy'n meddwl, yn glir.

Er mwyn cyflymu ymholiad Power Query, gadewch i ni glustogi'r tabl chwilio Stampiau, oherwydd nid yw'n newid yn y broses o gyflawni ymholiad ac nid oes angen ei ailgyfrifo'n gyson (fel y mae Power Query de facto yn ei wneud). Ar gyfer hyn rydym yn defnyddio'r swyddogaeth Tabl.Buffer o'r iaith Power Query M.

I wneud hyn, agorwch ymholiad Mae'r canlyniadau ac ar y tab adolygiad pwyswch y botwm Uwch Olygydd (Gweld - Golygydd Uwch). Yn y ffenestr sy'n agor, ychwanegwch linell gyda newidyn newydd Marky 2, a fydd yn fersiwn byffer o'n cyfeiriadur automaker, a defnyddiwch y newidyn newydd hwn yn ddiweddarach yn y gorchymyn ymholiad canlynol:

Ar ôl mireinio o'r fath, mae cyflymder diweddaru ein cais yn cynyddu bron i 7 gwaith - hyd at 15 eiliad. Peth hollol wahanol 🙂

• Chwiliad testun niwlog yn Power Query
• Amnewid testun swmp gyda fformiwlâu
• Amnewid testun swmp yn Power Query gyda swyddogaeth List.Accumulate