Gestel jy het 'n lys waarin, met verskillende grade van "reguitheid", aanvanklike data geskryf word - byvoorbeeld adresse of maatskappyname:
 |  |
Dit word duidelik gesien dat dieselfde stad of maatskappy hier in bont variante aanwesig is, wat natuurlik baie probleme sal skep wanneer daar in die toekoms met hierdie tafels gewerk word. En as jy 'n bietjie dink, kan jy baie voorbeelde van soortgelyke take uit ander gebiede vind.
Stel jou nou voor dat sulke skewe data gereeld na jou toe kom, maw dit is nie 'n eenmalige "manually fix it, forget it"-storie nie, maar 'n probleem op 'n gereelde basis en in 'n groot aantal selle.
Wat om te doen? Moenie die krom teks 100500 keer met die hand vervang deur die "Vind en vervang"-blokkie of deur te klik Ctrl+H?
Die eerste ding wat in so 'n situasie in gedagte kom, is om 'n massavervanging te maak volgens 'n vooraf saamgestelde naslaanboek van ooreenstemmende verkeerde en korrekte opsies – soos volg:
Ongelukkig, met die ooglopende voorkoms van so 'n taak, het Microsoft Excel nie eenvoudige ingeboude metodes om dit op te los nie. Om mee te begin, laat ons uitvind hoe om dit met formules te doen, sonder om "swaar artillerie" in die vorm van makro's in VBA of Power Query te betrek.
Geval 1. Grootmaat volle vervanging
Kom ons begin met 'n relatief eenvoudige saak – 'n situasie waar jy die ou skewe teks met 'n nuwe een moet vervang. ten volle.
Kom ons sê ons het twee tabelle:
In die eerste – die oorspronklike bont name van maatskappye. In die tweede – 'n naslaanboek van korrespondensie. As ons in die naam van die maatskappy in die eerste tabel enige woord uit die kolom vind Om te vind, dan moet jy hierdie krom naam heeltemal vervang met die korrekte een – uit die kolom plaasvervanger tweede opsoektabel.
Vir gerief:
- Beide tabelle word omgeskakel na dinamies ("slim") met behulp van 'n sleutelbordkortpad Ctrl+T of span Voeg in – Tabel (Voeg in — tabel).
- Op die blad wat verskyn konstruktor (Ontwerp) eerste tafel genoem data, en die tweede verwysingstabel – Substitusies.
Om die logika van die formule te verduidelik, kom ons gaan 'n bietjie van ver af.
Neem die eerste maatskappy uit sel A2 as 'n voorbeeld en vergeet tydelik van die res van die maatskappye, kom ons probeer om te bepaal watter opsie uit die kolom Om te vind ontmoet daar. Om dit te doen, kies enige leë sel in die vrye deel van die blad en voer die funksie daar in TE VIND (VIND):
Hierdie funksie bepaal of die gegewe substring ingesluit is (die eerste argument is alle waardes uit die kolom Om te vind) in die bronteks (die eerste maatskappy uit die datatabel) en moet óf die rangnommer van die karakter waaruit die teks gevind is, óf 'n fout indien die substring nie gevind is nie.
Die truuk hier is dat aangesien ons nie een nie, maar verskeie waardes as die eerste argument gespesifiseer het, hierdie funksie ook as gevolg daarvan nie een waarde sal terugkeer nie, maar 'n verskeidenheid van 3 elemente. As jy nie die nuutste weergawe van Office 365 het wat dinamiese skikkings ondersteun nie, nadat jy hierdie formule ingevoer het en op Tik jy sal hierdie skikking reg op die blad sien:
As jy vorige weergawes van Excel het, dan nadat jy op Tik ons sal slegs die eerste waarde van die resultaatskikking sien, dws fout #VALUE! (#WAARDE!).
Jy moet nie bang wees nie 🙂 Trouens, ons formule werk en jy kan steeds die hele reeks resultate sien as jy die ingevoerde funksie in die formulebalk kies en die sleutel druk F9(moet net nie vergeet om te druk nie Escom terug te gaan na die formule):
Die gevolglike reeks resultate beteken dit in die oorspronklike skewe maatskappynaam (GK Morozko OAO) van alle waardes in 'n kolom Om te vind net die tweede gevind (Morozko), en begin vanaf die 4de karakter in 'n ry.
Kom ons voeg nou 'n funksie by ons formule VIEW(SOEK OP):
Hierdie funksie het drie argumente:
- Gewenste waarde - jy kan enige voldoende groot getal gebruik (die belangrikste ding is dat dit die lengte van enige teks in die brondata oorskry)
- Bekyk_vektor – die reeks of skikking waar ons die verlangde waarde soek. Hier is die voorheen ingevoerde funksie TE VIND, wat 'n skikking {#VALUE!:4:#VALUE!}
- Vector_resultate – die reeks waaruit ons die waarde wil terugstuur as die verlangde waarde in die ooreenstemmende sel gevind word. Hier is die korrekte name uit die kolom plaasvervanger ons verwysingstabel.
Die belangrikste en nie-vanselfsprekende kenmerk hier is dat die funksie VIEW as daar geen presiese passing is nie, soek altyd na die naaste kleinste (vorige) waarde. Daarom, deur enige stewige getal (byvoorbeeld 9999) as die verlangde waarde te spesifiseer, sal ons dwing VIEW vind die sel met die naaste kleinste getal (4) in die skikking {#VALUE!:4:#VALUE!} en gee die ooreenstemmende waarde van die resultaatvektor terug, dws korrekte maatskappynaam uit die kolom plaasvervanger.
Die tweede nuanse is dat ons formule tegnies 'n skikkingsformule is, want funksie TE VIND gee as resultate nie een nie, maar 'n skikking van drie waardes. Maar sedert die funksie VIEW ondersteun skikkings uit die boks, dan hoef ons nie hierdie formule as 'n klassieke skikkingsformule in te voer nie - met 'n sleutelbordkortpad Ctrl+Skuif+Tik. 'n Eenvoudige een sal voldoende wees Tik.
Dis al. Hoop jy verstaan die logika.
Dit bly om die voltooide formule oor te dra na die eerste sel B2 van die kolom vaste – en ons taak is opgelos!
Natuurlik, met gewone (nie slim) tabelle werk hierdie formule ook uitstekend (moet net nie van die sleutel vergeet nie F4 en die regstelling van die relevante skakels):
Geval 2. Grootmaat gedeeltelike vervanging
Hierdie saak is 'n bietjie moeiliker. Weereens het ons twee "slim" tabelle:
Die eerste tabel met skeefgeskrewe adresse wat reggemaak moet word (ek het dit genoem Data2). Die tweede tabel is 'n naslaanboek, waarvolgens jy 'n gedeeltelike vervanging van 'n substring binne die adres moet maak (ek het hierdie tabel genoem Vervangings 2).
Die fundamentele verskil hier is dat jy slegs 'n fragment van die oorspronklike data hoef te vervang - byvoorbeeld, die eerste adres het 'n verkeerde “St. Petersburg” op die regte “St. Petersburg”, en laat die res van die adres (poskode, straat, huis) soos dit is.
Die voltooide formule sal so lyk (vir gemak van persepsie, het ek dit verdeel in hoeveel reëls gebruik alt+Tik):
Die hoofwerk hier word gedoen deur die standaard Excel-teksfunksie PLAASVERVANGER (PLAASVERVANG), wat 3 argumente het:
- Bronteks – die eerste skewe adres uit die adreskolom
- Waarna ons soek – hier gebruik ons die truuk met die funksie VIEW (SOEK OP)van die vorige manier om die waarde uit die kolom te trek Om te vind, wat as 'n fragment in 'n geboë adres ingesluit is.
- Waarmee om te vervang – op dieselfde manier vind ons die korrekte waarde wat daarmee ooreenstem in die kolom plaasvervanger.
Voer hierdie formule in met Ctrl+Skuif+Tik is ook nie hier nodig nie, hoewel dit in werklikheid 'n skikkingsformule is.
En dit word duidelik gesien (sien #N/A foute in die vorige prent) dat so 'n formule, met al sy elegansie, 'n paar nadele het:
- funksie PLAASVERVANG is hooflettersensitief, dus is "Spb" in die voorlaaste reël nie in die vervangingstabel gevind nie. Om hierdie probleem op te los, kan jy óf die funksie gebruik ZAMENIT (VERVANG), of bring beide tabelle voorlopig na dieselfde register.
- As die teks aanvanklik korrek is of daarin daar is geen fragment om te vervang nie (laaste reël), dan gooi ons formule 'n fout. Hierdie oomblik kan geneutraliseer word deur foute met behulp van die funksie te onderskep en te vervang IFERROR (IFERROR):
- As die oorspronklike teks bevat verskeie fragmente uit die gids gelyktydig, dan vervang ons formule slegs die laaste een (in die 8ste reël, Ligovsky «Laan« verander na "pr-t", Maar "S-Pb" on “St. Petersburg” nie meer nie, want “S-Pb” is hoër in die gids). Hierdie probleem kan opgelos word deur ons eie formule weer te laat loop, maar reeds langs die kolom vaste:
Nie perfek en omslagtig op plekke nie, maar baie beter as dieselfde handvervanging, reg? 🙂
PS
In die volgende artikel sal ons uitvind hoe om so 'n grootmaatvervanging te implementeer deur makro's en Power Query te gebruik.
- Hoe die PLAASVERVANGER-funksie werk om teks te vervang
- Soek presiese tekspassings deur die PRESIESE funksie te gebruik
- Hooflettergevoelige soektog en vervanging (hooflettersensitiewe VLOOKUP)