Excel

Find og erstat flere værdier

Find Replace Multiple Values

Excel-formel: Find og erstat flere værdierGenerisk formel
= SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2))
Resumé

For at finde og erstatte flere værdier med en formel kan du rede flere SUBSTITUTE-funktioner sammen og føde i find / udskift par fra en anden tabel ved hjælp af INDEX-funktionen. I det viste eksempel udfører vi 4 separate find- og erstatningsoperationer. Formlen i G5 er:

 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

hvor 'find' er navngivet rækkevidde E5: E8, og 'udskift' er det navngivne område F5: F8. Se nedenfor for information om, hvordan du gør denne formel lettere at læse.



Forord

Der er ingen indbygget formel til at køre en række find- og erstatningsoperationer i Excel, så dette er en 'koncept' -formel, der viser en tilgang. Teksten, du skal kigge efter og erstatte med, gemmes direkte på regnearket i en tabel og hentes med INDEX-funktionen. Dette gør løsningen 'dynamisk' - enhver af disse værdier ændres, resultater opdateres straks. Selvfølgelig er der ikke noget krav om at bruge INDEX, du kan hardkode værdier i formlen, hvis du foretrækker det.



Forklaring

I kernen bruger formlen SUBSTITUTE-funktionen til at udføre hver substitution med dette grundlæggende mønster:

 
= SUBSTITUTE (text,find,replace)

'Tekst' er den indgående værdi, 'find' er den tekst, der skal søges efter, og 'erstat' er den tekst, der skal erstattes med. Teksten, du skal kigge efter og erstatte med, er gemt i tabellen til højre i området E5: F8, et par pr. Række. Værdierne til venstre er i navngivet rækkevidde 'find'og værdierne til højre er i det navngivne område' erstatt '. INDEX-funktionen bruges til at hente både 'find' teksten og 'erstatte' teksten på denne måde:



hvad er absolut adressering i excel
 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

Så for at køre den første erstatning (se efter 'rød', erstatt med 'lyserød') bruger vi:

 
= SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1))

I alt kører vi fire separate udskiftninger, og hver efterfølgende SUBSTITUT begynder med resultatet fra den forrige SUBSTITUT:

hvordan man randomiserer ord i excel
 
= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE (B5, INDEX (find,1), INDEX (replace,1)), INDEX (find,2), INDEX (replace,2)), INDEX (find,3), INDEX (replace,3)), INDEX (find,4), INDEX (replace,4))

Linjeskift for læsbarhed

Du vil bemærke, at denne form for indlejret formel er ret vanskelig at læse. Ved at tilføje linjeskift kan vi gøre formlen meget lettere at læse og vedligeholde:



 
=  SUBSTITUTE (  SUBSTITUTE (  SUBSTITUTE (  SUBSTITUTE ( B5,  INDEX (find,1), INDEX (replace,1)),  INDEX (find,2), INDEX (replace,2)),  INDEX (find,3), INDEX (replace,3)),  INDEX (find,4), INDEX (replace,4))

Formelbjælken i Excel ignorerer ekstra hvidt mellemrum og linjeskift, så ovenstående formel kan indsættes direkte:

Linjeskift tilføjet i formellinjen for læsbarhed og vedligeholdelse

Forresten er der en tastaturgenvej til at udvide og skjule formellinjen.

Flere udskiftninger

Flere rækker kan tilføjes til tabellen for at håndtere flere find / udskift par. Hver gang et par tilføjes, skal formlen opdateres for at inkludere det nye par. Det er også vigtigt at sikre, at de navngivne områder (hvis du bruger dem) opdateres til at inkludere nye værdier efter behov. Alternativt kan du bruge en korrekt Excel-tabel for dynamiske områder i stedet for navngivne områder.

Andre anvendelser

Den samme fremgangsmåde kan bruges til at rydde op ved at 'strippe' tegnsætning og andre symboler fra tekst med en række udskiftninger. For eksempel viser formlen på denne side, hvordan man gør det rene og omformatere telefonnumre .

Forfatter Dave Bruns


^