Efnisyfirlit
Leit að leitarorðum í frumtexta er eitt algengasta verkefnið þegar unnið er með gögn. Við skulum skoða lausn þess á nokkra vegu með því að nota eftirfarandi dæmi:
Segjum sem svo að þú og ég séum með leitarorðalista – nöfn bílamerkja – og stóra töflu yfir alls kyns varahluti, þar sem lýsingar geta stundum innihaldið eitt eða fleiri slíkar tegundir í einu, ef varahluturinn passar fyrir fleiri en eina. vörumerki bíls. Verkefni okkar er að finna og birta öll greind leitarorð í nálægum frumum í gegnum tiltekið skiljustaf (til dæmis kommu).
Aðferð 1. Power Query
Auðvitað, fyrst breytum við borðum okkar í kraftmikið ("snjallt") með því að nota flýtilykla Ctrl+T eða skipanir Heim - Snið sem töflu (Heima - Snið sem töflu), gefðu þeim nöfn (td Stampsи Varahlutir) og hlaðið eitt af öðru inn í Power Query ritilinn með því að velja á flipanum Gögn – Frá töflu/sviði (Gögn — úr töflu/sviði). Ef þú ert með eldri útgáfur af Excel 2010-2013, þar sem Power Query er sett upp sem sér viðbót, þá verður hnappurinn sem þú vilt vera á flipanum Orkufyrirspurn. Ef þú ert með glænýja útgáfu af Excel 365, þá hnappinn Frá borði/sviði hringdi þangað núna Með laufblöðum (Úr blaði).
Eftir að hafa hlaðið hverri töflu í Power Query förum við aftur í Excel með skipuninni Heim — Loka og hlaða — Loka og hlaða í... — Aðeins búið til tengingu (Heima — Loka og hlaða — Loka og hlaða í... — Búa til tengingu aðeins).
Nú skulum við búa til tvítekna beiðni Varahlutirmeð því að hægrismella á það og velja Afrit beiðni (Tvítekið fyrirspurn), endurnefna síðan afritsbeiðnina sem myndast í Niðurstöðumar og við munum halda áfram að vinna með honum.
Rökfræði aðgerða er eftirfarandi:
- Á Advanced flipanum Að bæta við dálki velja lið Sérsniðinn dálkur (Bæta við dálki - sérsniðinn dálkur) og sláðu inn formúluna = Vörumerki. Eftir að hafa smellt á OK við fáum nýjan dálk, þar sem í hverjum reit verður hreiðrað tafla með lista yfir leitarorð okkar – vörumerki bílaframleiðenda:
- Notaðu hnappinn með tvöföldum örvum í haus dálksins sem bætt var við til að stækka allar hreiðrar töflur. Á sama tíma munu línurnar með lýsingum á varahlutum margfaldast með margfeldi af fjölda vörumerkja og við fáum allar mögulegar pör-samsetningar af „varahlutavörumerki“:
- Á Advanced flipanum Að bæta við dálki velja lið Skilyrt dálkur (Skilyrt dálkur) og settu skilyrði fyrir því að athuga hvort leitarorð (vörumerki) sé fyrir hendi í frumtextanum (lýsing á hluta):
- Til að gera leitina ónæman skaltu bæta við þriðju röksemdinni handvirkt á formúlustikuna Compare.OrdinalIgnoreCase í atviksskoðunaraðgerðina Texti.Inniheldur (ef formúlustikan er ekki sýnileg, þá er hægt að virkja hana á flipanum Review):
- Við síum töfluna sem myndast, skiljum aðeins eina eftir í síðasta dálknum, þ.e. samsvörun og fjarlægðum óþarfa dálkinn Tilvik.
- Sameina sömu lýsingar með skipuninni Group by flipi Umbreyting (Umbreyta — hópa eftir). Sem samansafn aðgerð, veldu Allar línur (Allar línur). Við úttakið fáum við dálk með töflum, sem inniheldur allar upplýsingar um hvern varahlut, þar á meðal vörumerki bílaframleiðenda sem við þurfum:
- Til að draga út einkunnir fyrir hvern hluta skaltu bæta við öðrum reiknuðum dálki á flipanum Að bæta við dálki - Sérsniðinn dálkur (Bæta við dálki - sérsniðinn dálkur) og notaðu formúlu sem samanstendur af töflu (þau eru staðsett í dálkinum okkar Nánar) og heiti dálksins sem dróst út:
- Við smellum á hnappinn með tvöföldum örvum í haus dálksins sem myndast og veljum skipunina Dragðu út gildi (Dregið út gildi)til að gefa út frímerki með hvaða afmörkunarstaf sem þú vilt:
- Fjarlægir óþarfa dálk Nánar.
- Til að bæta við töfluna sem myndast hlutum sem hurfu úr henni, þar sem engin vörumerki fundust í lýsingunum, framkvæmum við aðferðina til að sameina fyrirspurnina Niðurstaða með upphaflegri beiðni Varahlutir hnappinn Sameina flipi Heim (Heima — sameina fyrirspurnir). Tengitegund - Ytri Join Hægri (Hægri ytri tengi):
- Allt sem er eftir er að fjarlægja aukadálkana og endurnefna-færa þá sem eftir eru – og verkefni okkar er leyst:
Aðferð 2. Formúlur
Ef þú ert með útgáfu af Excel 2016 eða nýrri, þá er hægt að leysa vandamál okkar á mjög þéttan og glæsilegan hátt með því að nota nýju aðgerðina Sameina (TEXTJOIN):
Rökfræðin á bak við þessa formúlu er einföld:
- virka SEARCH (FINNA) leitar að tilviki hvers vörumerkis fyrir sig í núverandi lýsingu á hlutanum og skilar annað hvort raðnúmeri táknsins, sem byrjar á því sem vörumerkið fannst, eða villuna #VALUE! ef vörumerkið er ekki í lýsingunni.
- Notaðu síðan aðgerðina IF (EF) и EOSHIBKA (VILLA) við skiptum villunum út fyrir tóman textastreng "", og raðtölur stafanna fyrir vörumerkin sjálf.
- Fylgið af tómum frumum og fundnum vörumerkjum sem myndast er sett saman í einn streng í gegnum tiltekið skiljustaf með því að nota aðgerðina Sameina (TEXTJOIN).
Samanburður á afköstum og aflfyrirspurn í biðminni fyrir hraðaupphlaup
Fyrir frammistöðuprófun skulum við taka töflu með 100 varahlutalýsingum sem upphafsgögn. Á henni fáum við eftirfarandi niðurstöður:
- Endurútreikningstími með formúlum (Aðferð 2) – 9 sek. þegar þú afritar formúluna fyrst í allan dálkinn og 2 sek. við endurtekið (jafnvægi hefur áhrif, líklega).
- Uppfærslutími Power Query fyrirspurnarinnar (aðferð 1) er miklu verri - 110 sekúndur.
Auðvitað veltur mikið á vélbúnaði tiltekinnar tölvu og uppsettri útgáfu af Office og uppfærslum, en heildarmyndin held ég að sé skýr.
Til að flýta fyrir Power Query fyrirspurn skulum við leggja biðminni á uppflettingartöfluna Stamps, vegna þess að það breytist ekki í framkvæmd fyrirspurna og það er ekki nauðsynlegt að endurreikna það stöðugt (eins og Power Query gerir í raun). Til þess notum við aðgerðina Tafla.Buffer frá innbyggða Power Query tungumálinu M.
Til að gera þetta skaltu opna fyrirspurn Niðurstöðumar og á flipanum Review Ýttu á takkann Háþróaður ritstjóri (Skoða — Ítarleg ritstjóri). Í glugganum sem opnast skaltu bæta við línu með nýrri breytu Marký 2, sem verður biðminni útgáfa af bílaframleiðendaskránni okkar, og notaðu þessa nýju breytu síðar í eftirfarandi fyrirspurnarskipun:
Eftir slíka betrumbætur eykst uppfærsluhraði beiðninnar okkar um næstum 7 sinnum - allt að 15 sekúndur. Allt annað 🙂
- Óljós textaleit í Power Query
- Skipt um magn texta með formúlum
- Magn textaskipti í Power Query með List.Accumulate aðgerð