Sisällys
Avainsanojen etsiminen lähdetekstistä on yksi yleisimmistä tehtävistä tietojen kanssa työskennellessä. Katsotaanpa sen ratkaisua useilla tavoilla seuraavan esimerkin avulla:
Oletetaan, että sinulla ja minulla on avainsanalista – automerkkien nimet – ja iso taulukko kaikenlaisista varaosista, jossa kuvaukset voivat joskus sisältää yhden tai useamman tällaisen merkin kerralla, jos varaosa sopii useampaan kuin yhteen auton merkki. Tehtävämme on etsiä ja näyttää kaikki naapurisoluissa havaitut avainsanat tietyllä erotinmerkillä (esimerkiksi pilkulla).
Tapa 1. Power Query
Tietenkin ensin teemme taulukoistamme dynaamisia ("älykkäitä") pikanäppäimen avulla Ctrl+T tai käskyt Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona), anna heille nimet (esim Postimerkitи Varaosat) ja lataa yksitellen Power Query -editoriin valitsemalla välilehdestä Tiedot – taulukosta/alueesta (Tiedot – taulukosta/alueesta). Jos sinulla on Excel 2010-2013 vanhempia versioita, joissa Power Query on asennettu erillisenä apuohjelmana, haluttu painike on välilehdellä Tehokysely. Jos sinulla on aivan uusi Excel 365 -versio, painike Taulukosta/alueesta soitti sinne nyt Lehtien kanssa (Luettelosta).
Kun jokainen taulukko on ladattu Power Queryyn, palaamme takaisin Exceliin komennolla Etusivu — Sulje ja lataa — Sulje ja lataa… — Luo vain yhteys (Koti — Sulje & Lataa — Sulje & Lataa kohteeseen… — Luo vain yhteys).
Luodaan nyt päällekkäinen pyyntö Varaosatnapsauttamalla sitä hiiren kakkospainikkeella ja valitsemalla Päällekkäinen pyyntö (Kaksoinen kysely)ja nimeä sitten tuloksena oleva kopiopyyntö uudelleen muotoon Tulokset ja jatkamme työskentelyä hänen kanssaan.
Toiminnan logiikka on seuraava:
- Lisäasetukset -välilehdellä Sarakkeen lisääminen valitse joukkue Muokattu sarake (Lisää sarake — Muokattu sarake) ja syötä kaava = Tuotemerkit. Napsautuksen jälkeen OK saamme uuden sarakkeen, jossa jokaisessa solussa on sisäkkäinen taulukko, jossa on luettelo avainsanoistamme - autovalmistajien merkit:
- Laajenna kaikki sisäkkäiset taulukot käyttämällä lisätyn sarakkeen otsikossa olevaa kaksoisnuolien painiketta. Samanaikaisesti varaosien kuvaukset kertovat merkkien lukumäärän moninkertaisesti, ja saamme kaikki mahdolliset "varaosamerkin" parit-yhdistelmät:
- Lisäasetukset -välilehdellä Sarakkeen lisääminen valitse joukkue Ehdollinen sarake (Ehdollinen sarake) ja aseta ehto avainsanan (brändin) esiintymisen tarkistamiseksi lähdetekstissä (osan kuvaus):
- Jos haluat tehdä haun kirjainkoosta epäherkkä, lisää kolmas argumentti manuaalisesti kaavapalkkiin Compare.OrdinalIgnoreCase tapahtuman tarkistustoimintoon Text.Contains (jos kaavapalkki ei ole näkyvissä, se voidaan ottaa käyttöön välilehdellä Arvostelu):
- Suodatamme tuloksena olevan taulukon jättäen vain ykköset viimeiseen sarakkeeseen eli vastaavuuksia ja poistamme tarpeettoman sarakkeen esiintymät.
- Identtisten kuvausten ryhmittely komennon kanssa Ryhmän mukaan kieleke Muutos (Muunna — Ryhmittele). Valitse koontioperaatioksi Kaikki linjat (Kaikki rivit). Tuloksena saamme sarakkeen taulukoineen, jotka sisältävät kaikki tiedot jokaisesta varaosasta, mukaan lukien tarvitsemamme autonvalmistajien merkit:
- Poimi arvosanat jokaisesta osasta lisäämällä välilehteen toinen laskettu sarake Sarakkeen lisääminen – Mukautettu sarake (Lisää sarake — Muokattu sarake) ja käytä kaavaa, joka koostuu taulukosta (ne sijaitsevat sarakkeessamme Lisätiedot) ja poimitun sarakkeen nimi:
- Napsauta painiketta kaksoisnuolilla tuloksena olevan sarakkeen otsikossa ja valitse komento Poimi arvot (Poimi arvot)tulostaaksesi leimoja millä tahansa haluamallasi erottimella:
- Tarpeettoman sarakkeen poistaminen Lisätiedot.
- Lisätäksemme tuloksena olevaan taulukkoon siitä kadonneet osat, joissa kuvauksista ei löytynyt brändejä, suoritamme kyselyn yhdistämisen Tulos alkuperäisellä pyynnöstä Varaosat nappia Yhdistää kieleke Koti (Etusivu – Yhdistä kyselyt). Liitäntätyyppi - Ulompi liitos oikea (Oikea ulompi liitos):
- Jäljelle jää vain ylimääräisten sarakkeiden poistaminen ja jäljellä olevien sarakkeiden nimeäminen uudelleen – ja tehtävämme on ratkaistu:
Menetelmä 2. Kaavat
Jos sinulla on Excel 2016 tai uudempi versio, ongelmamme voidaan ratkaista erittäin kompaktilla ja tyylikkäällä tavalla uudella toiminnolla YHDISTÄÄ (TEKSTIJOIN):
Tämän kaavan takana oleva logiikka on yksinkertainen:
- Toiminto ETSI (LÖYTÖ) etsii vuorotellen kunkin merkin esiintymistä osan nykyisestä kuvauksesta ja palauttaa joko sen symbolin sarjanumeron, josta alkaen merkki löydettiin, tai virheen #ARVO! jos merkki ei ole kuvauksessa.
- Käytä sitten toimintoa IF (JOS) и Eoshibka (ISERROR) Korvaamme virheet tyhjällä tekstimerkkijonolla "" ja merkkien järjestysnumerot itse tuotenimillä.
- Tuloksena oleva tyhjien solujen ja löydettyjen merkkien joukko kootaan yhdeksi merkkijonoksi tietyn erotinmerkin kautta käyttämällä funktiota YHDISTÄÄ (TEKSTIJOIN).
Suorituskyvyn vertailu ja Power Query -kyselypuskurointi nopeuttamiseksi
Suorituskykytestausta varten otetaan lähtötietona 100 varaosakuvauksen taulukko. Siitä saamme seuraavat tulokset:
- Uudelleenlaskentaaika kaavoilla (Menetelmä 2) – 9 sek. kun kopioit kaavan ensimmäisen kerran koko sarakkeeseen ja 2 sek. toistuvasti (puskurointi vaikuttaa, luultavasti).
- Power Query -kyselyn (tapa 1) päivitysaika on paljon huonompi – 110 sekuntia.
Tietysti paljon riippuu tietyn tietokoneen laitteistosta ja asennetusta Office-versiosta ja päivityksistä, mutta kokonaiskuva on mielestäni selkeä.
Power Query -kyselyn nopeuttamiseksi puskuroidaan hakutaulukko Postimerkit, koska se ei muutu kyselyn suoritusprosessissa, eikä sitä tarvitse jatkuvasti laskea uudelleen (kuten Power Query käytännössä tekee). Tätä varten käytämme funktiota Taulukko. Puskuri sisäänrakennetusta Power Query -kielestä M.
Voit tehdä tämän avaamalla kyselyn Tulokset ja välilehdellä Arvostelu painaa nappia Edistynyt editori (Näytä — Advanced Editor). Lisää avautuvaan ikkunaan rivi, jossa on uusi muuttuja Marky 2, joka on puskuroitu versio autovalmistajien hakemistosta, ja käytä tätä uutta muuttujaa myöhemmin seuraavassa kyselykomennossa:
Tällaisen tarkentamisen jälkeen pyyntömme päivitysnopeus kasvaa lähes 7 kertaa – jopa 15 sekuntiin. Aivan eri asia 🙂
- Sumea tekstihaku Power Queryssä
- Joukkotekstin korvaaminen kaavoilla
- Joukkotekstin korvaaminen Power Queryssä List.Accumulate-toiminnolla