Contents [show]
Soek na sleutelwoorde in bronteks is een van die mees algemene take wanneer jy met data werk. Kom ons kyk na die oplossing daarvan op verskeie maniere deur die volgende voorbeeld te gebruik:
Kom ons veronderstel dat ek en jy 'n lys sleutelwoorde het – die name van motorhandelsmerke – en 'n groot tabel van allerhande soorte onderdele, waar beskrywings soms een of meer sulke handelsmerke gelyktydig kan bevat, as die onderdeel meer as een pas merk motor. Ons taak is om alle bespeurde sleutelwoorde in naburige selle te vind en te vertoon deur 'n gegewe skeidingskarakter (byvoorbeeld 'n komma).
Metode 1. Kragnavraag
Natuurlik verander ons eers ons tabelle in dinamiese ("slim") met behulp van 'n sleutelbordkortpad Ctrl+T of opdragte Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel), gee hulle name (byvoorbeeld Seëlsи Onderdele) en laai een vir een in die Power Query-redigeerder deur op die oortjie te kies Data – Van tabel/reeks (Data – van tabel/reeks). As jy ouer weergawes van Excel 2010-2013 het, waar Power Query as 'n aparte byvoeging geïnstalleer is, dan sal die verlangde knoppie op die blad wees Kragnavraag. As jy 'n splinternuwe weergawe van Excel 365 het, dan is die knoppie Van tabel/reeks het nou daarheen gebel Met blare (Vanuit Blad).
Nadat ons elke tabel in Power Query gelaai het, keer ons terug na Excel met die opdrag Tuis — Maak toe en laai — Maak toe en laai na … — Skep slegs verbinding (Tuis — Maak toe en laai — Maak toe en laai na … — Skep slegs verbinding).
Kom ons skep nou 'n duplikaatversoek Onderdeledeur met die rechtermuisknop daarop te klik en te kies Duplikaatversoek (Duplikaatnavraag), hernoem dan die gevolglike kopieversoek na Die resultate en ons sal voortgaan om saam met hom te werk.
Die logika van aksies is die volgende:
- Op die oortjie Gevorderd Voeg 'n kolom by kies 'n span Gepasmaakte kolom (Voeg kolom by – Pasgemaakte kolom) en voer die formule in = Handelsmerke. Nadat u op OK ons sal 'n nuwe kolom kry, waar daar in elke sel 'n geneste tabel sal wees met 'n lys van ons sleutelwoorde - motorvervaardigershandelsmerke:
- Gebruik die knoppie met dubbele pyltjies in die kopskrif van die bygevoegde kolom om alle geneste tabelle uit te brei. Terselfdertyd sal die lyne met beskrywings van onderdele vermenigvuldig met 'n veelvoud van die aantal handelsmerke, en ons sal alle moontlike pare-kombinasies van "onderdeel-handelsmerk" kry:
- Op die oortjie Gevorderd Voeg 'n kolom by kies 'n span Voorwaardelike kolom (Voorwaardelike kolom) en stel 'n voorwaarde vir die kontrolering van die voorkoms van 'n sleutelwoord (handelsmerk) in die bronteks (deelbeskrywing):
- Om die soekkas ongevoelig te maak, voeg die derde argument handmatig in die formulebalk by Vergelyk.OrdinalIgnoreCase na die voorkomskontrole-funksie Teks.Bevat (as die formulebalk nie sigbaar is nie, kan dit op die oortjie geaktiveer word Resensie):
- Ons filtreer die resulterende tabel, laat slegs een in die laaste kolom, dws pas by en verwyder die onnodige kolom Voorvalle.
- Groepering van identiese beskrywings met die opdrag Groepeer volgens tab Transformasie (Transformeer — Groepeer volgens). As 'n samevoegingsbewerking, kies Alle lyne (Alle rye). By die afvoer kry ons 'n kolom met tabelle, wat al die besonderhede vir elke onderdeel bevat, insluitend die handelsmerke van motorvervaardigers wat ons benodig:
- Om punte vir elke deel te onttrek, voeg nog 'n berekende kolom op die oortjie by Voeg 'n kolom by - Pasgemaakte kolom (Voeg kolom by – Pasgemaakte kolom) en gebruik 'n formule wat uit 'n tabel bestaan (hulle is in ons kolom geleë besonderhede) en die naam van die onttrekte kolom:
- Ons klik op die knoppie met dubbele pyltjies in die kop van die gevolglike kolom en kies die opdrag Onttrek waardes (Onttrek waardes)om seëls uit te voer met enige skeidingskarakter wat jy wil hê:
- Verwyder 'n onnodige kolom besonderhede.
- Om die dele wat daaruit verdwyn het, waar geen handelsmerke in die beskrywings gevind is, by die resulterende tabel te voeg nie, voer ons die prosedure uit om die navraag te kombineer Gevolg met oorspronklike versoek Onderdele knoppie Kombineer tab What is This (Tuis – Voeg navrae saam). Verbinding tipe - Buitenste Verbind Regs (Regs buitenste aansluiting):
- Al wat oorbly is om die ekstra kolomme te verwyder en die oorblywendes te hernoem-skuif – en ons taak is opgelos:
Metode 2. Formules
As jy 'n weergawe van Excel 2016 of later het, kan ons probleem op 'n baie kompakte en elegante manier opgelos word deur die nuwe funksie te gebruik kombineer (TEKSSLUIT):
Die logika agter hierdie formule is eenvoudig:
- funksie SOEK (VIND) soek om die beurt na die voorkoms van elke handelsmerk in die huidige beskrywing van die onderdeel en gee óf die reeksnommer van die simbool terug, vanaf waar die handelsmerk gevind is, óf die fout #VALUE! as die handelsmerk nie in die beskrywing is nie.
- Gebruik dan die funksie IF (AS) и EOSHIBKA (FOUT) ons vervang die foute met 'n leë teksstring "", en die rangnommers van die karakters met die handelsname self.
- Die resulterende reeks leë selle en gevind handelsmerke word saamgestel in 'n enkele string deur 'n gegewe skei karakter met behulp van die funksie kombineer (TEKSSLUIT).
Prestasievergelyking en Kragnavraag-navraagbuffering vir Speedup
Vir prestasietoetsing, kom ons neem 'n tabel van 100 onderdelebeskrywings as aanvanklike data. Daarop kry ons die volgende resultate:
- Herberekeningstyd deur formules (Metode 2) – 9 sek. wanneer jy eers die formule na die hele kolom kopieer en 2 sek. by herhaalde (buffering beïnvloed, waarskynlik).
- Die opdateringstyd van die Power Query-navraag (metode 1) is baie erger – 110 sekondes.
Natuurlik hang baie af van die hardeware van 'n spesifieke rekenaar en die geïnstalleerde weergawe van Office en opdaterings, maar die algehele prentjie, dink ek, is duidelik.
Om 'n Power Query-navraag te bespoedig, kom ons buffer die opsoektabel Seëls, want dit verander nie in die proses van navraaguitvoering nie en dit is nie nodig om dit voortdurend te herbereken nie (soos Power Query de facto doen). Hiervoor gebruik ons die funksie Table.Buffer van die ingeboude Power Query-taal M.
Om dit te doen, maak 'n navraag oop Die resultate en op die blad Resensie druk die knoppie Gevorderde redakteur (Bekyk - Gevorderde Redigeerder). Voeg 'n reël by met 'n nuwe veranderlike in die venster wat oopmaak Markie 2, wat 'n gebufferde weergawe van ons motorvervaardigergids sal wees, en gebruik hierdie nuwe veranderlike later in die volgende navraagopdrag:
Na sulke verfyning verhoog die opdateringspoed van ons versoek met byna 7 keer – tot 15 sekondes. Nogal 'n ander ding 🙂
- Fuzzy tekssoektog in Power Query
- Grootmaat teksvervanging met formules
- Grootmaat teksvervanging in Power Query met List.Accumulate-funksie