Sisu
Märksõnade otsimine lähtetekstist on andmetega töötamisel üks levinumaid ülesandeid. Vaatame selle lahendust mitmel viisil, kasutades järgmist näidet:
Oletame, et meil ja sinul on märksõnade nimekiri – automarkide nimetused – ja suur igasuguste varuosade tabel, kus kirjeldused võivad mõnikord sisaldada ühte või mitut sellist marki korraga, kui varuosa sobib rohkem kui ühele. auto mark. Meie ülesanne on etteantud eraldaja (näiteks koma) kaudu leida ja kuvada kõik naaberlahtrites tuvastatud märksõnad.
1. meetod. Power Query
Loomulikult muudame esmalt oma tabelid dünaamilisteks ("nutikateks"), kasutades kiirklahvi Ctrl+T või käske Avaleht – vorminda tabelina (Avaleht — vorminda tabelina), andke neile nimed (näiteks Margidи Varuosad) ja laadige ükshaaval Power Query redaktorisse, valides vahekaardil Andmed – tabelist/vahemikust (Andmed – tabelist/vahemikust). Kui teil on Exceli vanemad versioonid 2010–2013, kuhu Power Query on installitud eraldi lisandmoodulina, on soovitud nupp vahekaardil Toite päring. Kui teil on täiesti uus Excel 365 versioon, siis nupp Tabelist/vahemikust helistas nüüd sinna Lehtedega (Lehelt).
Pärast iga tabeli laadimist Power Querysse naaseme käsuga Excelisse Avaleht — Sule ja laadi — Sule ja laadi… — Loo ainult ühendus (Kodu – Sulge ja laadi – Sule ja laadi… – Loo ainult ühendus).
Nüüd loome duplikaattaotluse Varuosadparemklõpsates sellel ja valides Duplikaattaotlus (Duplikaatpäring), seejärel nimetage saadud koopiataotlus ümber nimeks Tulemused ja me jätkame temaga koostööd.
Toimingute loogika on järgmine:
- Vahekaardil Täpsemalt Veeru lisamine vali meeskond Kohandatud veerg (Lisa veerg – kohandatud veerg) ja sisestage valem = Kaubamärgid. Pärast klõpsamist OK saame uue veeru, kus igas lahtris on pesastatud tabel meie märksõnade loendiga – autotootjate kaubamärgid:
- Kõigi pesastatud tabelite laiendamiseks kasutage lisatud veeru päises olevat topeltnooltega nuppu. Samal ajal korrutuvad varuosade kirjeldustega read kaubamärkide arvu kordsega ja saame kõik võimalikud varuosade kaubamärgi paarid-kombinatsioonid:
- Vahekaardil Täpsemalt Veeru lisamine vali meeskond Tingimuslik veerg (Tingimuslik veerg) ja seada tingimus märksõna (brändi) esinemise kontrollimiseks lähtetekstis (osa kirjelduses):
- Otsingu tõstutundlikuks muutmiseks lisage käsitsi valemiribale kolmas argument Compare.OrdinalIgnoreCase esinemise kontrollimise funktsioonile Tekst.Sisaldab (kui valemiriba pole nähtav, saab selle lubada vahekaardil Ülevaade):
- Filtreerime saadud tabeli, jättes viimasesse veergu ainult ühed, st vasted ja eemaldame mittevajaliku veeru Esinemised.
- Identsete kirjelduste rühmitamine käsuga Grupi poolt tab Transformation (Teisendus – rühmitamine). Koondamistoiminguna valige Kõik read (Kõik read). Väljundis saame tabelitega veeru, mis sisaldab kõiki üksikasju iga varuosa kohta, sealhulgas meile vajalike autotootjate kaubamärke:
- Iga osa hinde eraldamiseks lisage vahekaardile veel üks arvutatud veerg Veeru lisamine – kohandatud veerg (Lisa veerg – kohandatud veerg) ja kasutage tabelist koosnevat valemit (need asuvad meie veerus Detailid) ja ekstraheeritud veeru nimi:
- Klõpsame saadud veeru päises topeltnooltega nuppu ja valime käsu Ekstrakti väärtused (Väärtuste eraldamine)templite väljastamiseks mis tahes soovitud eraldajaga:
- Ebavajaliku veeru eemaldamine Detailid.
- Saadud tabelisse sealt kadunud osade lisamiseks, mille kirjeldustest kaubamärke ei leitud, teostame päringu kombineerimise protseduuri Tulemus algse sooviga Varuosad nupp Ühendama tab Avaleht (Avaleht – Ühenda päringud). Ühenduse tüüp - Välimine ühendus parempoolne (Parem väline liitmik):
- Jääb vaid eemaldada lisaveerud ja ülejäänud veerud ümber nimetada-teisaldada – ja meie ülesanne on lahendatud:
Meetod 2. Valemid
Kui teil on Excel 2016 või uuem versioon, saab meie probleemi uue funktsiooni abil väga kompaktselt ja elegantselt lahendada COMBINE (TEXTJOIN):
Selle valemi loogika on lihtne:
- funktsioon OTSI (LEIA) otsib osa jooksvast kirjeldusest kordamööda iga kaubamärgi esinemist ja tagastab kas sümboli seerianumbri, millest alates kaubamärk leiti, või vea #VÄÄRTUS! kui kaubamärki kirjelduses pole.
- Seejärel kasutage funktsiooni IF (KUI) и EOSHIBKA (ISERROR) asendame vead tühja tekstistringiga “” ja märkide järjekorranumbrid brändinimedega.
- Saadud tühjade lahtrite ja leitud kaubamärkide massiiv koondatakse funktsiooni abil etteantud eraldusmärgi kaudu üheks stringiks COMBINE (TEXTJOIN).
Toimivuse võrdlus ja Power Query päringu puhverdamine kiirendamiseks
Toimivuse testimiseks võtame lähteandmeteks 100 varuosade kirjelduse tabeli. Selle põhjal saame järgmised tulemused:
- Valemite järgi ümberarvutamise aeg (2. meetod) – 9 sek. kui kopeerite valemi esmakordselt kogu veergu ja 2 sek. kordumisel (tõenäoliselt mõjutab puhverdus).
- Power Query päringu (1. meetod) uuendusaeg on palju hullem – 110 sekundit.
Muidugi sõltub palju konkreetse arvuti riistvarast ja Office'i installitud versioonist ja värskendustest, kuid üldpilt on minu arvates selge.
Power Query päringu kiirendamiseks puhverdame otsingutabeli Margid, sest see ei muutu päringu täitmise käigus ja seda pole vaja pidevalt ümber arvutada (nagu Power Query de facto teeb). Selleks kasutame funktsiooni Tabel. Puhver sisseehitatud Power Query keelest M.
Selleks avage päring Tulemused ja vahekaardil Ülevaade vajuta nuppu Täiustatud redaktor (Vaade – täiustatud redaktor). Avanevas aknas lisage rida uue muutujaga Marky 2, mis on meie autotootjate kataloogi puhverdatud versioon ja kasutage seda uut muutujat hiljem järgmises päringukäskluses:
Pärast sellist täpsustamist suureneb meie päringu värskenduskiirus peaaegu 7 korda – kuni 15 sekundini. Hoopis teine asi 🙂
- Hägune tekstiotsing Power Querys
- Teksti hulgi asendamine valemitega
- Teksti hulgiasendus Power Querys funktsiooniga List.Accumulate