Excel

Find og erstat flere værdier

Find Replace Multiple Values

Excel -formel: Find og erstat flere værdierGenerisk formel | _+_ | Resumé

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





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

hvor 'find' er navngivet område E5: E8, og 'erstat' er det navngivne område F5: F8. Se nedenfor for oplysninger 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 erstat operationer i Excel, så dette er et 'koncept' formel for at vise en tilgang. Den tekst, der skal søges efter og erstattes 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, resultaterne opdateres med det samme. Selvfølgelig er der ikke noget krav om at bruge INDEX, du kan hard-kode værdier i formlen, hvis du foretrækker det.





Forklaring

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

 
= 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))

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



hvad er absolut adressering i excel
 
= SUBSTITUTE (text,find,replace)

Så for at køre den første substitution (se efter 'rød', erstat med 'pink') bruger vi:

 
 INDEX (find,1) // first 'find' value  INDEX (replace,1) // first 'replace' value

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

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

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))

Formellinjen 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

I øvrigt er der en tastaturgenvej for 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 intervaller (hvis du bruger dem) opdateres til at omfatte 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 'fjerne' tegnsætning og andre symboler fra tekst med en række substitutioner. For eksempel viser formlen på denne side, hvordan rene og omformat telefonnumre .

Forfatter Dave Bruns


^