Sich no Schlësselwieder am Text

Sich no Schlësselwieder am Quelltext ass eng vun den heefegsten Aufgaben wann Dir mat Daten schafft. Loosst eis seng Léisung op verschidde Manéiere kucken mat dem folgenden Beispill:

Loosst eis unhuelen, datt Dir an ech eng Lëscht vu Schlësselwierder hunn - d'Nimm vun Autosmarken - an eng grouss Tabelle vun all Zorte vun Ersatzdeeler, wou d'Beschreiwunge heiansdo eng oder méi esou Marken gläichzäiteg enthalen kënnen, wann den Ersatzdeel méi wéi eng passt. Mark vum Auto. Eis Aufgab ass all entdeckt Schlësselwieder an Nopeschzellen duerch e bestëmmten Separator Charakter ze fannen an ze weisen (zum Beispill e Komma).

Method 1. Power Query

Natierlech maache mir als éischt eis Dëscher an dynamesch ("Smart") mat enger Tastatur Ofkiirzung Ctrl+T oder Kommandoen Home - Format als Dësch (Home - Format als Table), gitt hinnen Nimm (zum Beispill Stampsи Ersatzdeeler) a lued een nom aneren an de Power Query Editor andeems Dir op der Tab wielt Donnéeën - Vun Table / Range (Daten - Vun Table / Range). Wann Dir méi al Versioune vun Excel 2010-2013 hutt, wou Power Query als separat Add-in installéiert ass, da wäert de gewënschte Knäppchen op der Tab sinn Muecht Ufro. Wann Dir eng fuschneie Versioun vun Excel 365 hutt, dann de Knäppchen Vun Dësch / Gamme do geruff elo Mat Blieder (vum Blat).

Nodeems Dir all Tabell an der Power Query gelueden hutt, gi mir zréck op Excel mam Kommando Home - Zoumaachen a lueden - Zoumaachen a lueden op ... - Nëmme Verbindung erstellen (Home - Zoumaachen & Lueden - Zoumaachen & Lueden op ... - Nëmme Verbindung erstellen).

Loosst eis elo eng duplizéiert Ufro erstellen Ersatzdeelerandeems Dir op et klickt a wielt Duplizéiert Ufro (Duplizéiert Ufro), dann ëmbenennen déi resultéierend Kopie Ufro op D'Resultater a mir wäerte weider mat him schaffen.

D'Logik vun Aktiounen ass déi folgend:

1. Op der Advanced Tab Dobäizemaachen eng Kolonn wielt eng Equipe Benotzerdefinéiert Kolonn (Füügt Kolonn - Benotzerdefinéiert Kolonn) a gitt d'Formel = Marken. Nodeems Dir op klickt OK mir kréien eng nei Kolonn, wou an all Zell et e nestet Dësch gëtt mat enger Lëscht vun eise Schlësselwieder - automaker Marken:

   

2. Benotzt de Knäppchen mat duebele Pfeile am Header vun der addéierter Kolonn fir all nestet Dëscher auszebauen. Zur selwechter Zäit wäerten d'Linnen mat Beschreiwunge vun Ersatzdeeler mat enger Multiple vun der Unzuel vun de Marken multiplizéieren, a mir kréien all méiglech Pairen-Kombinatioune vun "Ersatzdeel-Mark":

   

3. Op der Advanced Tab Dobäizemaachen eng Kolonn wielt eng Equipe Conditionell Kolonn (Conditionnelle Kolonn) a setze eng Konditioun fir d'Optriede vun engem Schlësselwuert (Mark) am Quelltext ze kontrolléieren (Deelbeschreiwung):

   

4. Fir de Sichfall onsensibel ze maachen, füügt manuell dat drëtt Argument an der Formelbar un Vergläichen.OrdinalIgnoreCase op d'Optriede kontrolléieren Funktioun Text.Enthält (wann d'Formelbar net sichtbar ass, da kann se op der Tab aktivéiert ginn iwwerpréiwen):

   

5. Mir filteren déi resultéierend Tabell, loossen nëmmen déi an der leschter Kolonn, dh Matcher an ewechzehuelen déi onnéideg Kolonn Optriede.
6. Gruppéiere identesch Beschreiwunge mam Kommando Grupp duerch Reiter Transformatioun (Transforméieren - Group by). Als Aggregatioun Operatioun, wielt All Linnen (All Reihen). Op der Ausgab kréie mir eng Kolonn mat Dëscher, déi all Detailer fir all Ersatzdeel enthält, och d'Marken vun den Automobilisten déi mir brauchen:

   

7. Fir Graden fir all Deel ze extrahieren, füügt eng aner berechent Kolonn op der Tab Eng Kolonn derbäisetzen - Benotzerdefinéiert Kolonn (Füügt Kolonn - Benotzerdefinéiert Kolonn) a benotzt eng Formel déi aus enger Tabell besteet (se sinn an eiser Kolonn Detailer) an den Numm vun der extrahéierter Kolonn:

   

8. Mir klickt op de Knäppchen mat duebele Pfeile am Header vun der resultéierender Kolonn a wielt de Kommando Extrait Wäerter (Wäerter extrahéieren)fir Timberen mat all Delimiter Charakter auszeginn deen Dir wëllt:

   

9. Ewechzehuelen eng onnéideg Kolonn Detailer.
10. Fir op déi resultéierend Tabell d'Deeler ze addéieren, déi dovu verschwonnen sinn, wou keng Marken an de Beschreiwunge fonnt goufen, maache mir d'Prozedur fir d'Ufro ze kombinéieren Resultat mat original Demande Ersatzdeeler Knäppchen Kombinéieren Reiter Home (Home - Ufroen fusionéieren). Verbindungstyp - Outer Join Recht (Riets baussenzege Verbindung):

    

11. Alles wat bleift ass déi extra Sailen ze läschen an déi verbleiwen ëmbenennen - an eis Aufgab ass geléist:

    
    

Method 2. Formulen

Wann Dir eng Versioun vun Excel 2016 oder méi spéit hutt, da kann eise Problem op eng ganz kompakt an elegant Manéier mat der neier Funktioun geléist ginn KOMBINÉIEREN (TEXTJOIN):

D'Logik hannert dëser Formel ass einfach:

• Funktioun SICH (FINN) sicht no der Optriede vun all Mark am Tour an der aktueller Beschreiwung vum Deel a gëtt entweder d'Seriennummer vum Symbol zréck, vun deem d'Mark fonnt gouf, oder de Feeler #VALUE! wann d'Mark net an der Beschreiwung ass.
• Dann benotzt d'Funktioun IF (WANN) и EOSHIBKA (FEELER) mir ersetzen d'Feeler duerch eng eidel Textstring "", an d'Uerdnungsnummere vun de Charaktere mat de Markennimm selwer.
• Déi doraus resultéierend Array vun eidelen Zellen a fonnt Marken ass an engem eenzege String duerch e bestëmmte Separator Charakter zesummegesat mat der Funktioun KOMBINÉIEREN (TEXTJOIN).

Performance Verglach a Power Query Query Buffer fir Speedup

Fir Leeschtungstestung, loosst eis en Dësch mat 100 Ersatzdeelbeschreiwungen als initial Donnéeën huelen. Op et kréien mir déi folgend Resultater:

• Neiberechnungszäit duerch Formelen (Methode 2) - 9 Sek. wann Dir éischt d'Formel un déi ganz Kolonn kopéiert an 2 sec. bei widderholl (Puffering beaflosst, wahrscheinlech).
  
• D'Aktualiséierungszäit vun der Power Query Ufro (Methode 1) ass vill méi schlëmm - 110 Sekonnen.
  

Natierlech hänkt vill vun der Iron vun engem bestëmmte PC an der installéierter Versioun vum Office an Updates of, awer d'Gesamtbild, mengen ech, ass kloer.

Fir eng Power Query Ufro ze beschleunegen, loosst eis de Lookup Tabelle pufferen Stamps, well et ännert sech net am Prozess vun der Ausféierung vun der Ufro an et ass net néideg fir se dauernd nei ze berechnen (wéi Power Query de facto mécht). Fir dëst benotze mir d'Funktioun Dësch.Buffer vun der agebauter Power Query Sprooch M.

Fir dëst ze maachen, öffnen eng Ufro D'Resultater an op der Tab iwwerpréiwen dréckt op de Knäppchen Fortgeschratt Redakter (View - Advanced Editor). An der Fënster déi opmaacht, füügt eng Zeil mat enger neier Variabel Marky 2, déi eng gebufferter Versioun vun eisem Automaker Verzeechnes wäert sinn, a benotzt dës nei Variabel méi spéit am folgenden Ufro Kommando:

No esou Verfeinerung erhéicht d'Aktualiséierungsgeschwindegkeet vun eiser Ufro ëm bal 7 Mol - bis zu 15 Sekonnen. Ganz aner Saach 🙂

• Fuzzy Text Sich an Power Query
• Bulk Text Ersatz mat Formelen
• Bulk Text Ersatz an Power Query mat List.Accumulate Funktioun