Ugeholl datt Dir eng Lëscht hutt, an där, mat ënnerschiddleche Grad vun "Gläichheet", initial Daten geschriwwe sinn - zum Beispill Adressen oder Firmennimm:
 |  |
Et ass kloer ze gesinn datt déi selwecht Stad oder Firma hei a motley Varianten präsent ass, wat selbstverständlech vill Probleemer erstallt wann Dir an Zukunft mat dësen Dëscher schafft. A wann Dir e bëssen denkt, kënnt Dir vill Beispiller vun ähnlechen Aufgaben aus anere Beräicher fannen.
Stellt Iech elo vir, datt esou kromme Donnéeën regelméisseg bei Iech kommen, also dat ass net eng eemoleg Geschicht "manuell fixen, vergiess et", mee e Problem regelméisseg an an enger grousser Zuel vun Zellen.
Wat kann een maachen? Ersetzen net de kromme Text manuell 100500 Mol duerch de richtegen duerch d'Këscht "Find an Ersetzen" oder andeems Dir klickt Ctrl+H?
Déi éischt Saach, déi an esou enger Situatioun am Kapp kënnt, ass e Massenersatz no engem pre-kompiléierten Referenzbuch mat passenden falschen a korrekten Optiounen ze maachen - wéi dëst:
Leider, mat der offensichtlecher Prevalenz vun esou enger Aufgab, huet Microsoft Excel keng einfach agebaute Methoden fir se ze léisen. Fir unzefänken, loosst eis erausfannen, wéi een dat mat Formelen mécht, ouni "schwéier Artillerie" a Form vu Makroen an VBA oder Power Query involvéieren.
Fall 1. Bulk voll Ersatz
Fänke mer mat engem relativ einfache Fall un – eng Situatioun wou een den alen kromme Text duerch en neien ersetzt. voll.
Loosst eis soen datt mir zwee Dëscher hunn:
An der éischter - déi ursprénglech variegéiert Nimm vu Firmen. An der zweeter - e Referenzbuch vun der Korrespondenz. Wa mir am Numm vun der Firma an der éischter Tabell all Wuert aus der Kolonn fannen Fannen, da musst Dir dëse kromme Numm komplett duerch de richtege ersetzen - aus der Kolonn Substitute zweete Lookup Dësch.
Fir d'Bequemlechkeet:
- Béid Dëscher ginn op dynamesch ("Smart") ëmgewandelt mat enger Tastatur Ofkiirzung Ctrl+T oder Team Insert - Dësch (Insert - Tabell).
- Op der Tab déi erschéngt Builder (Design) éischten Dësch genannt Daten, an déi zweet Referenztabell - Substitutiounen.
Fir d'Logik vun der Formel z'erklären, loosst eis e bësse vu wäitem goen.
Déi éischt Firma aus der Zell A2 als Beispill ze huelen an temporär de Rescht vun de Firmen ze vergiessen, loosst eis probéieren ze bestëmmen wéi eng Optioun aus der Kolonn Fannen begéint do. Fir dëst ze maachen, wielt all eidel Zell am fräien Deel vum Blat a gitt d'Funktioun do FANNEN (FINN):
Dës Funktioun bestëmmt ob de gegebene Substring abegraff ass (dat éischt Argument ass all Wäerter aus der Kolonn Fannen) an de Quelltext (déi éischt Firma aus der Datentabelle) a soll entweder d'Ordinale Zuel vum Charakter ausginn, aus deem den Text fonnt gouf, oder e Feeler wann d'Substring net fonnt gouf.
Den Trick hei ass datt well mir net een, awer e puer Wäerter als éischt Argument spezifizéiert hunn, wäert dës Funktioun och als Resultat net ee Wäert zréckginn, mee eng Array vun 3 Elementer. Wann Dir net déi lescht Versioun vum Office 365 hutt, déi dynamesch Arrays ënnerstëtzt, dann nodeems Dir dës Formel aginn hutt a klickt op gitt Dir gesitt dës Array direkt um Blat:
Wann Dir virdrun Versioune vun Excel hutt, dann nodeems Dir op klickt gitt mir wäerten nëmmen den éischte Wäert aus dem Resultat Array gesinn, dh Feeler #VALUE! (#VALUE!).
Dir sollt keng Angscht hunn 🙂 Tatsächlech funktionnéiert eis Formel an Dir kënnt nach ëmmer déi ganz Palette vu Resultater gesinn wann Dir déi agefouert Funktioun an der Formelbar auswielt an de Schlëssel dréckt F9(vergiesst just net ze drécken Escfir zréck op d'Formel ze goen):
Déi doraus resultéierend Gamme vu Resultater heescht, datt am Original kromme Firma Numm (GK Morozko OAO) vun alle Wäerter an enger Kolonn Fannen fonnt nëmmen déi zweet (Morozko), an ab dem 4. Charakter an enger Zeil.
Loosst eis elo eng Funktioun zu eiser Formel derbäi VIEW(EROPKUCKEN):
Dës Funktioun huet dräi Argumenter:
- Wënsch Wäert - Dir kënnt all genuch grouss Zuel benotzen (D'Haaptsach ass datt et d'Längt vun all Text an de Quelldaten iwwerschreift)
- Gesinn_vektor - d'Gamme oder d'Array wou mir no de gewënschten Wäert sichen. Hei ass déi virdru agefouert Funktioun FANNEN, déi eng Array {#VALUE!:4:#VALUE!}
- Vector_Resultater - de Beräich, aus deem mir de Wäert zréckginn wann de gewënschten Wäert an der entspriechender Zell fonnt gëtt. Hei sinn déi richteg Nimm aus der Kolonn Substitute eis Referenz Dësch.
D'Haaptrei an net offensichtlech Feature hei ass datt d'Funktioun VIEW wann et keng genee Match, sicht ëmmer no der noosten klengste (virdrun) Wäert. Dofir, andeems Dir all hefteg Zuel (zum Beispill 9999) als gewënschten Wäert uginn, wäerte mir zwéngen VIEW Fannt d'Zelle mat der nooste klengster Zuel (4) an der Array {#VALUE!:4:#VALUE!} a gitt de entspriechende Wäert vum Resultatvektor zréck, dh de korrekte Firmennumm aus der Kolonn Substitute.
Déi zweet Nuance ass datt, technesch, eis Formel eng Arrayformel ass, well d'Funktioun FANNEN gëtt als Resultater net een, mee eng Array vun dräi Wäerter zréck. Mä zënter der Funktioun VIEW ënnerstëtzt Arrays aus der Këscht, da musse mir dës Formel net als klassesch Arrayformel aginn - mat enger Tastatur Ofkiirzung Ctrl+Verréckelung+gitt. Een einfachen wäert duergoen gitt.
Dat ass alles. Hoffen Dir kritt d'Logik.
Et bleift fir déi fäerdeg Formel op déi éischt Zelle B2 vun der Kolonn ze transferéieren fix - an eis Aufgab ass geléist!
Natierlech, mat gewéinlechen (net schlau) Dëscher, funktionnéiert dës Formel och super (vergiess just net iwwer de Schlëssel F4 an déi relevant Linken fixéieren):
Fall 2. Bulk partiell Ersatz
Dëse Fall ass e bësse méi schwiereg. Mir hunn erëm zwee "Smart" Dëscher:
Déi éischt Tabell mat kromme geschriwwe Adressen déi muss korrigéiert ginn (ech hunn et genannt Daten2). Déi zweet Tabell ass e Referenzbuch, no deem Dir e partiellen Ersatz vun engem Substring an der Adress maache musst (ech hunn dës Tabell genannt Auswiesselungen 2).
De fundamentalen Ënnerscheed hei ass datt Dir nëmmen e Fragment vun den ursprénglechen Donnéeën ersetzen musst - zum Beispill déi éischt Adress huet eng falsch "St. Petersburg" Riets "St. Petersburg", loosst de Rescht vun der Adress (Postcode, Strooss, Haus) wéi ass.
Déi fäerdeg Formel wäert esou ausgesinn (fir d'Erliichterung vun der Perceptioun hunn ech se opgedeelt a wéivill Zeilen benotzt Alt+gitt):
D'Haaptaarbecht hei gëtt vun der Standard Excel Textfunktioun gemaach SUBSTITUT (SUBSTITUT), déi 3 Argumenter huet:
- Quelltext - déi éischt kromme Adress aus der Adress Kolonn
- Wat mir sichen - hei benotze mir den Trick mat der Funktioun VIEW (EROPKUCKEN)vum fréiere Wee fir de Wäert aus der Kolonn ze zéien Fannen, deen als Fragment an enger kromme Adress abegraff ass.
- Mat wat ze ersetzen - op déiselwecht Manéier fanne mir de richtege Wäert deen et aus der Kolonn entsprécht Substitute.
Gitt dës Formel mat Ctrl+Verréckelung+gitt ass hei och net néideg, obwuel et tatsächlech eng Arrayformel ass.
An et ass kloer ze gesinn (kuckt #N/A Feeler am virege Bild) datt sou eng Formel, fir all seng Eleganz, e puer Nodeeler huet:
- Funktioun SUBSTITUT ass casesensitiv, Also "Spb" an der virläschter Linn gouf net an der Ersatztabell fonnt. Fir dëse Problem ze léisen, kënnt Dir entweder d'Funktioun benotzen ZAMENIT (Ersetzen), oder virleefeg béid Dëscher an de selwechte Register bréngen.
- Wann den Text am Ufank richteg ass oder dran et gëtt kee Fragment ze ersetzen (läscht Zeil), da werft eis Formel e Feeler. Dëse Moment kann neutraliséiert ginn andeems d'Fehler mat der Funktioun ofgefaangen an ersetzen IFERROR (IWWERT):
- Wann den Originaltext enthält e puer Fragmenter aus dem Verzeichnis gläichzäiteg, dann ersetzt eis Formel nëmmen déi lescht (an der 8. Linn, Ligovsky «Avenue« geännert ze "pr-t", Awer "S-Pb" on "St. Petersburg" net méi, well "S-Pb" ass méi héich am Verzeechnes). Dëse Problem kann geléist ginn andeems Dir eis eege Formel nei leeft, awer scho laanscht d'Kolonn fix:
Net perfekt an ëmständlech op Plazen, awer vill besser wéi déi selwecht manuell Ersatz, richteg? 🙂
PS
Am nächsten Artikel wäerte mir erausfannen wéi een esou eng Bulk Ersatz mat Macroen a Power Query implementéiert.
- Wéi d'SUBSTITUT Funktioun funktionnéiert fir Text ze ersetzen
- Fannt exakt Textmatcher mat der EXAKT Funktioun
- Case sensibel Sich an Ersatz (case sensibel VLOOKUP)