Γενική φόρμουλα
=SUBSTITUTE(SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1)),INDEX(find,2),INDEX(replace,2))
Περίληψη
Για να βρείτε και να αντικαταστήσετε πολλές τιμές με έναν τύπο, μπορείτε να τοποθετήσετε πολλές συναρτήσεις SUBSTITUTE μαζί και να τροφοδοτήσετε εύρεση / αντικατάσταση ζευγών από έναν άλλο πίνακα χρησιμοποιώντας τη συνάρτηση INDEX. Στο παράδειγμα που εμφανίζεται, εκτελούμε 4 ξεχωριστές λειτουργίες εύρεσης και αντικατάστασης. Ο τύπος στο G5 είναι:
=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))
όπου "εύρεση" είναι η ονομαστική περιοχή E5: E8 και "Αντικατάσταση" είναι η ονομαζόμενη περιοχή F5: F8. Δείτε παρακάτω για πληροφορίες σχετικά με τον τρόπο ευκολότερης ανάγνωσης αυτού του τύπου.
Πρόλογος
Δεν υπάρχει ενσωματωμένος τύπος για την εκτέλεση μιας σειράς λειτουργιών εύρεσης και αντικατάστασης στο Excel, οπότε αυτός είναι ένας τύπος "concept" για να δείξει μια προσέγγιση. Το κείμενο για αναζήτηση και αντικατάσταση αποθηκεύεται απευθείας στο φύλλο εργασίας σε έναν πίνακα και ανακτάται με τη λειτουργία INDEX. Αυτό κάνει τη λύση "δυναμική" - οποιαδήποτε από αυτές τις τιμές αλλάζει, τα αποτελέσματα ενημερώνονται αμέσως. Φυσικά, δεν υπάρχει απαίτηση για χρήση του INDEX. Μπορείτε να βάλετε τιμές στον τύπο, εάν προτιμάτε.
Εξήγηση
Στον πυρήνα, ο τύπος χρησιμοποιεί τη συνάρτηση SUBSTITUTE για την εκτέλεση κάθε αντικατάστασης, με αυτό το βασικό μοτίβο:
=SUBSTITUTE(text,find,replace)
"Κείμενο" είναι η εισερχόμενη τιμή, "εύρεση" είναι το κείμενο που πρέπει να αναζητηθεί και "αντικατάσταση" είναι το κείμενο που θα αντικατασταθεί με. Το κείμενο για αναζήτηση και αντικατάσταση αποθηκεύεται στον πίνακα προς τα δεξιά, στην περιοχή E5: F8, ένα ζεύγος ανά σειρά. Οι τιμές στα αριστερά βρίσκονται στην ονομαστική περιοχή "εύρεση" και οι τιμές στα δεξιά βρίσκονται στην ονομαζόμενη περιοχή "αντικατάσταση". Η συνάρτηση INDEX χρησιμοποιείται για την ανάκτηση τόσο του κειμένου "εύρεση" όσο και του κειμένου "αντικατάσταση" ως εξής:
INDEX(find,1) // first "find" value INDEX(replace,1) // first "replace" value
Έτσι, για να εκτελέσετε την πρώτη αντικατάσταση (αναζητήστε "κόκκινο", αντικαταστήστε με "ροζ") χρησιμοποιούμε:
=SUBSTITUTE(B5,INDEX(find,1),INDEX(replace,1))
Συνολικά, εκτελούμε τέσσερις ξεχωριστές αντικαταστάσεις, και κάθε επόμενη SUBSTITUTE ξεκινά με το αποτέλεσμα από την προηγούμενη SUBSTITUTE:
=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))
Διακοπές γραμμής για αναγνωσιμότητα
Θα παρατηρήσετε ότι αυτό το είδος ένθετου τύπου είναι αρκετά δύσκολο να διαβαστεί. Με την προσθήκη αλλαγών γραμμής, μπορούμε να κάνουμε τον τύπο πολύ πιο εύκολο στην ανάγνωση και τη συντήρηση:
= 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))
Η γραμμή τύπων στο Excel αγνοεί επιπλέον κενό διάστημα και αλλαγές γραμμής, οπότε ο παραπάνω τύπος μπορεί να επικολληθεί απευθείας:
Παρεμπιπτόντως, υπάρχει μια συντόμευση πληκτρολογίου για επέκταση και κατάρρευση της γραμμής τύπων.
Περισσότερες αντικαταστάσεις
Μπορούν να προστεθούν περισσότερες σειρές στον πίνακα για να χειριστούν περισσότερα ζεύγη εύρεσης / αντικατάστασης. Κάθε φορά που προστίθεται ένα ζεύγος, ο τύπος πρέπει να ενημερώνεται για να συμπεριλάβει το νέο ζεύγος. Είναι επίσης σημαντικό να βεβαιωθείτε ότι τα ονομαστικά εύρη (εάν τα χρησιμοποιείτε) ενημερώνονται ώστε να περιλαμβάνουν νέες τιμές, όπως απαιτείται. Εναλλακτικά, θα μπορούσατε να χρησιμοποιήσετε έναν κατάλληλο πίνακα Excel για δυναμικές περιοχές, αντί για ονομαστικές περιοχές.
Άλλες χρήσεις
Η ίδια προσέγγιση μπορεί να χρησιμοποιηθεί για την εκκαθάριση κειμένου με "απογύμνωση" στίξης και άλλα σύμβολα από κείμενο με μια σειρά αντικαταστάσεων Για παράδειγμα, ο τύπος σε αυτήν τη σελίδα δείχνει τον τρόπο καθαρισμού και μορφοποίησης αριθμών τηλεφώνου.
