Poiščite ključne besede v besedilu

Iskanje ključnih besed v izvornem besedilu je eno najpogostejših opravil pri delu s podatki. Oglejmo si njegovo rešitev na več načinov z naslednjim primerom:

Poiščite ključne besede v besedilu

Predpostavimo, da imamo vi in ​​jaz seznam ključnih besed – imen avtomobilskih znamk – in veliko tabelo vseh vrst rezervnih delov, kjer lahko opisi včasih vsebujejo eno ali več takih znamk hkrati, če rezervni del ustreza več kot eni. znamka avtomobila. Naša naloga je najti in prikazati vse zaznane ključne besede v sosednjih celicah preko danega ločilnega znaka (na primer vejica).

1. način. Power Query

Seveda najprej naše tabele spremenimo v dinamične (»pametne«) z uporabo bližnjice na tipkovnici Ctrl+T ali ukazi Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo), jim dajte imena (npr Poštne znamkeи Rezervni deli) in enega za drugim naložite v urejevalnik Power Query tako, da izberete na zavihku Podatki – iz tabele/razpona (Podatki — iz tabele/razpona). Če imate starejše različice Excela 2010-2013, kjer je Power Query nameščen kot ločen dodatek, bo želeni gumb na zavihku poizvedba o moči. Če imate popolnoma novo različico Excela 365, potem gumb Iz tabele/razpona klical tja zdaj Z listi (Iz lista).

Ko vsako tabelo naložimo v Power Query, se z ukazom vrnemo nazaj v Excel Domov — Zapri in naloži — Zapri in naloži na… — Ustvari samo povezavo (Domov — Zapri & Naloži — Zapri & Naloži v… — Ustvari samo povezavo).

Zdaj pa ustvarimo podvojeno zahtevo Rezervni deliz desnim klikom nanjo in izbiro Podvojena zahteva (Podvojena poizvedba), nato preimenujte nastalo zahtevo za kopiranje v Rezultati in z njim bomo še naprej sodelovali.

Logika dejanj je naslednja:

  1. Na zavihku Napredno Dodajanje stolpca izberite ekipo Stolpec po meri (Dodaj stolpec — stolpec po meri) in vnesite formulo = Blagovne znamke. Po kliku na OK dobili bomo nov stolpec, kjer bo v vsaki celici ugnezdena tabela s seznamom naših ključnih besed – znamk avtomobilov:

    Poiščite ključne besede v besedilu

  2. Za razširitev vseh ugnezdenih tabel uporabite gumb z dvojnimi puščicami v glavi dodanega stolpca. Hkrati se bodo vrstice z opisi nadomestnih delov pomnožile z večkratnikom števila znamk in dobili bomo vse možne pare-kombinacije "nadomestni del-znamka":

    Poiščite ključne besede v besedilu

  3. Na zavihku Napredno Dodajanje stolpca izberite ekipo Pogojni stolpec (pogojni stolpec) in nastavite pogoj za preverjanje pojavljanja ključne besede (blagovne znamke) v izvornem besedilu (opis dela):

    Poiščite ključne besede v besedilu

  4. Če želite, da bo iskanje neobčutljivo za velike in male črke, ročno dodajte tretji argument v vrstici formule Primerjaj.OrdinalIgnoreCase na funkcijo preverjanja pojava Besedilo. Vsebuje (če vrstica s formulami ni vidna, jo lahko omogočite na zavihku pregled):

    Poiščite ključne besede v besedilu

  5. Dobljeno tabelo filtriramo tako, da v zadnjem stolpcu pustimo samo ena, torej ujemanja, in odstranimo nepotreben stolpec Pojavitve.
  6. Združevanje enakih opisov z ukazom Skupina z tab Preoblikovanje (Pretvorba — Združi po). Kot operacijo združevanja izberite Vse vrstice (Vse vrstice). Na izhodu dobimo stolpec s tabelami, ki vsebuje vse podrobnosti za vsak rezervni del, vključno z znamkami proizvajalcev avtomobilov, ki jih potrebujemo:

    Poiščite ključne besede v besedilu

  7. Če želite izvleči ocene za vsak del, na zavihku dodajte še en izračunani stolpec Dodajanje stolpca – stolpec po meri (Dodaj stolpec — stolpec po meri) in uporabite formulo, sestavljeno iz tabele (nahajajo se v našem stolpcu podrobnosti) in ime ekstrahiranega stolpca:

    Poiščite ključne besede v besedilu

  8. Kliknemo na gumb z dvojnimi puščicami v glavi nastalega stolpca in izberemo ukaz Izvleček vrednosti (Izvleček vrednosti)za izpis žigov s poljubnim ločilnim znakom:

    Poiščite ključne besede v besedilu

  9. Odstranjevanje nepotrebnega stolpca podrobnosti.
  10. Če želite v nastalo tabelo dodati dele, ki so izginili iz nje, kjer v opisih ni bilo najdenih blagovnih znamk, izvedemo postopek za združevanje poizvedbe Rezultat z originalno zahtevo Rezervni deli Gumb Združite tab Domov (Domov — Združi poizvedbe). Vrsta povezave – Zunanji desni spoj (desni zunanji spoj):

    Poiščite ključne besede v besedilu

  11. Vse kar ostane je, da odstranimo dodatne stolpce in preimenujemo-premaknemo preostale – in naša naloga je rešena:

    Poiščite ključne besede v besedilu

Metoda 2. Formule

Če imate različico Excela 2016 ali novejšo, lahko našo težavo rešite na zelo kompakten in eleganten način z uporabo nove funkcije KOMBIN (ZDRUŽI BESEDILO):

Poiščite ključne besede v besedilu

Logika te formule je preprosta:

  • funkcija ISKANJE (NAJTI) išče pojavljanje vsake znamke po vrsti v trenutnem opisu dela in vrne bodisi serijsko številko simbola, od katere je bila znamka najdena, bodisi napako #VREDNOST! če znamke ni v opisu.
  • Nato uporabite funkcijo IF (ČE) и EOŠIBKA (ISNAPAKA) napake nadomestimo s praznim besedilnim nizom “”, zaporedne številke znakov pa s samimi imeni blagovnih znamk.
  • Nastala matrika praznih celic in najdenih blagovnih znamk je sestavljena v en niz prek danega ločilnega znaka z uporabo funkcije KOMBIN (ZDRUŽI BESEDILO).

Primerjava zmogljivosti in medpomnilnik poizvedbe Power Query za pospešitev

Za testiranje delovanja vzemimo kot začetne podatke tabelo s 100 opisi rezervnih delov. Na njem dobimo naslednje rezultate:

  • Čas preračunavanja po formulah (metoda 2) – 9 sek. ko prvič kopirate formulo v celoten stolpec in 2 sek. pri ponavljajočem se (verjetno vpliva medpomnjenje).
  • Čas posodobitve poizvedbe Power Query (1. metoda) je veliko slabši – 110 sekund.

Seveda je veliko odvisno od strojne opreme določenega osebnega računalnika in nameščene različice Officea ter posodobitev, vendar je celotna slika, mislim, jasna.

Če želite pospešiti poizvedbo Power Query, shranite iskalno tabelo v medpomnilnik Poštne znamke, ker se v procesu izvajanja poizvedbe ne spreminja in ga ni treba nenehno preračunavati (kot de facto počne Power Query). Za to uporabljamo funkcijo Table.Buffer iz vgrajenega jezika Power Query M.

Če želite to narediti, odprite poizvedbo Rezultati in na zavihku pregled pritisni gumb Napredni urejevalnik (Pogled — napredni urejevalnik). V oknu, ki se odpre, dodajte vrstico z novo spremenljivko Marky 2, ki bo varovana različica našega imenika proizvajalca avtomobilov, in uporabite to novo spremenljivko pozneje v naslednjem ukazu poizvedbe:

Poiščite ključne besede v besedilu

Po takšni izboljšavi se hitrost posodabljanja naše zahteve poveča za skoraj 7-krat – do 15 sekund. Čisto nekaj drugega 🙂

  • Iskanje mehkega besedila v Power Query
  • Zamenjava množičnega besedila s formulami
  • Zamenjava množičnega besedila v Power Query s funkcijo List.Accumulate

Pustite Odgovori