Αντικατάσταση μαζικού κειμένου με τύπους

Ας υποθέσουμε ότι έχετε μια λίστα στην οποία, με διάφορους βαθμούς «αυθεντικότητας», γράφονται τα αρχικά δεδομένα - για παράδειγμα, διευθύνσεις ή ονόματα εταιρειών:

Φαίνεται ξεκάθαρα ότι η ίδια πόλη ή εταιρεία είναι παρούσα εδώ σε ετερόκλητες παραλλαγές, οι οποίες, προφανώς, θα δημιουργήσουν πολλά προβλήματα κατά την εργασία με αυτά τα τραπέζια στο μέλλον. Και αν σκεφτείτε λίγο, μπορείτε να βρείτε πολλά παραδείγματα παρόμοιων εργασιών από άλλους τομείς.

Τώρα φανταστείτε ότι τέτοια λανθασμένα δεδομένα σας έρχονται τακτικά, δηλαδή δεν πρόκειται για μια εφάπαξ ιστορία «διορθώστε το χειροκίνητα, ξεχάστε το», αλλά ένα πρόβλημα σε τακτική βάση και σε μεγάλο αριθμό κελιών.

Τι να κάνω? Μην αντικαθιστάτε χειροκίνητα το στραβό κείμενο 100500 φορές με το σωστό μέσω του πλαισίου «Εύρεση και αντικατάσταση» ή κάνοντας κλικ Ctrl+H?

Το πρώτο πράγμα που σας έρχεται στο μυαλό σε μια τέτοια κατάσταση είναι να κάνετε μια μαζική αντικατάσταση σύμφωνα με ένα προκαταρτισμένο βιβλίο αναφοράς αντιστοίχισης εσφαλμένων και σωστών επιλογών - όπως αυτό:

Δυστυχώς, με την προφανή επικράτηση μιας τέτοιας εργασίας, το Microsoft Excel δεν διαθέτει απλές ενσωματωμένες μεθόδους για την επίλυσή της. Αρχικά, ας μάθουμε πώς να το κάνουμε αυτό με τύπους, χωρίς να περιλαμβάνει «βαρύ πυροβολικό» με τη μορφή μακροεντολών σε VBA ή Power Query.

Περίπτωση 1. Μαζική πλήρης αντικατάσταση

Ας ξεκινήσουμε με μια σχετικά απλή περίπτωση – μια κατάσταση όπου πρέπει να αντικαταστήσετε το παλιό στραβά κείμενο με ένα νέο. πλήρως.

Ας πούμε ότι έχουμε δύο πίνακες:

Στην πρώτη – τα αρχικά διαφοροποιημένα ονόματα των εταιρειών. Στο δεύτερο – βιβλίο αναφοράς αλληλογραφίας. Αν βρούμε στο όνομα της εταιρείας στον πρώτο πίνακα οποιαδήποτε λέξη από τη στήλη Να βρω, τότε πρέπει να αντικαταστήσετε εντελώς αυτό το στραβά όνομα με το σωστό – από τη στήλη Υποκατάστατο δεύτερος πίνακας αναζήτησης.

Για ευκολία:

• Και οι δύο πίνακες μετατρέπονται σε δυναμικούς ("έξυπνους") χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+T ή ομάδα Ένθετο – Πίνακας (Εισαγωγή — Πίνακας).
  
• Στην καρτέλα που εμφανίζεται Κατασκευαστής (Σχέδιο) ο πρώτος πίνακας με όνομα ημερομηνίακαι ο δεύτερος πίνακας αναφοράς – Αλλαγές.

Για να εξηγήσουμε τη λογική του τύπου, ας πάμε λίγο από μακριά.

Λαμβάνοντας ως παράδειγμα την πρώτη εταιρεία από το κελί A2 και ξεχνάμε προσωρινά τις υπόλοιπες εταιρείες, ας προσπαθήσουμε να προσδιορίσουμε ποια επιλογή από τη στήλη Να βρω συναντιέται εκεί. Για να το κάνετε αυτό, επιλέξτε οποιοδήποτε κενό κελί στο ελεύθερο μέρος του φύλλου και εισαγάγετε τη συνάρτηση εκεί ΝΑ ΒΡΩ (ΕΥΡΗΜΑ):

Αυτή η συνάρτηση καθορίζει εάν περιλαμβάνεται η δεδομένη υποσυμβολοσειρά (το πρώτο όρισμα είναι όλες οι τιμές από τη στήλη Να βρω) στο κείμενο προέλευσης (η πρώτη εταιρεία από τον πίνακα δεδομένων) και θα πρέπει να εξάγει είτε τον τακτικό αριθμό του χαρακτήρα από τον οποίο βρέθηκε το κείμενο είτε ένα σφάλμα εάν δεν βρέθηκε η δευτερεύουσα συμβολοσειρά.

Το κόλπο εδώ είναι ότι εφόσον καθορίσαμε όχι μία, αλλά πολλές τιμές ως πρώτο όρισμα, αυτή η συνάρτηση θα επιστρέψει επίσης ως αποτέλεσμα όχι μία τιμή, αλλά έναν πίνακα 3 στοιχείων. Εάν δεν διαθέτετε την πιο πρόσφατη έκδοση του Office 365 που υποστηρίζει δυναμικούς πίνακες, τότε αφού εισαγάγετε αυτόν τον τύπο και κάνετε κλικ στο εισάγετε θα δείτε αυτόν τον πίνακα ακριβώς στο φύλλο:

Εάν έχετε προηγούμενες εκδόσεις του Excel, κάντε κλικ στο εισάγετε θα δούμε μόνο την πρώτη τιμή από τον πίνακα αποτελεσμάτων, δηλαδή το σφάλμα #VALUE! (#ΑΞΙΑ!).

Δεν πρέπει να φοβάστε 🙂 Στην πραγματικότητα, ο τύπος μας λειτουργεί και μπορείτε ακόμα να δείτε ολόκληρη τη σειρά των αποτελεσμάτων εάν επιλέξετε τη συνάρτηση που έχετε εισαγάγει στη γραμμή τύπων και πατήσετε το πλήκτρο F9(απλά μην ξεχάσετε να πατήσετε Escγια να επιστρέψετε στον τύπο):

Η προκύπτουσα σειρά αποτελεσμάτων σημαίνει ότι στο αρχικό λανθασμένο όνομα εταιρείας (GK Morozko OAO) όλων των τιμών σε μια στήλη Να βρω βρήκε μόνο το δεύτερο (Μορόζκο), και ξεκινώντας από τον 4ο στη σειρά χαρακτήρα.

Τώρα ας προσθέσουμε μια συνάρτηση στον τύπο μας ΔΕΙΤΕ(ΨΑΧΝΩ):

Αυτή η συνάρτηση έχει τρία ορίσματα:

1. Επιθυμητή αξία – μπορείτε να χρησιμοποιήσετε οποιονδήποτε αρκετά μεγάλο αριθμό (το κύριο πράγμα είναι ότι υπερβαίνει το μήκος οποιουδήποτε κειμένου στα δεδομένα προέλευσης)
   
2. Viewed_vector – το εύρος ή ο πίνακας όπου αναζητούμε την επιθυμητή τιμή. Εδώ είναι η συνάρτηση που εισήχθη προηγουμένως ΝΑ ΒΡΩ, που επιστρέφει έναν πίνακα {#VALUE!:4:#VALUE!}
   
3. Διάνυσμα_αποτελέσματα – το εύρος από το οποίο θέλουμε να επιστρέψουμε την τιμή εάν βρεθεί η επιθυμητή τιμή στο αντίστοιχο κελί. Εδώ είναι τα σωστά ονόματα από τη στήλη Υποκατάστατο πίνακα αναφοράς μας.
   

Το κύριο και μη προφανές χαρακτηριστικό εδώ είναι ότι η συνάρτηση ΔΕΙΤΕ αν δεν υπάρχει ακριβής αντιστοίχιση, αναζητά πάντα την πλησιέστερη μικρότερη (προηγούμενη) τιμή. Επομένως, καθορίζοντας έναν μεγάλο αριθμό (για παράδειγμα, 9999) ως την επιθυμητή τιμή, θα αναγκάσουμε ΔΕΙΤΕ βρείτε το κελί με τον πλησιέστερο μικρότερο αριθμό (4) στον πίνακα {#VALUE!:4:#VALUE!} και επιστρέψτε την αντίστοιχη τιμή από το διάνυσμα αποτελέσματος, δηλαδή σωστό όνομα εταιρείας από τη στήλη Υποκατάστατο.

Η δεύτερη απόχρωση είναι ότι, τεχνικά, ο τύπος μας είναι τύπος πίνακα, επειδή η συνάρτηση ΝΑ ΒΡΩ επιστρέφει ως αποτελέσματα όχι μία, αλλά έναν πίνακα τριών τιμών. Αλλά από τη λειτουργία ΔΕΙΤΕ υποστηρίζει πίνακες out of the box, τότε δεν χρειάζεται να εισάγουμε αυτόν τον τύπο ως κλασικό τύπο πίνακα - χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+αλλαγή+εισάγετε. Ένα απλό θα αρκεί εισάγετε.

Αυτό είναι όλο. Ελπίζω να καταλαβαίνεις τη λογική.

Απομένει να μεταφέρετε τον τελικό τύπο στο πρώτο κελί Β2 της στήλης Σταθερό – και το έργο μας λύθηκε!

Φυσικά, με τα συνηθισμένα (όχι έξυπνα) τραπέζια, αυτή η φόρμουλα λειτουργεί επίσης εξαιρετικά (απλά μην ξεχνάτε το κλειδί F4 και επιδιόρθωση των σχετικών συνδέσμων):

Περίπτωση 2. Μερική αντικατάσταση χύδην

Αυτή η υπόθεση είναι λίγο πιο δύσκολη. Και πάλι έχουμε δύο «έξυπνους» πίνακες:

Ο πρώτος πίνακας με στραβά γραμμένες διευθύνσεις που πρέπει να διορθωθεί (τον φώναξα Δεδομένα2). Ο δεύτερος πίνακας είναι ένα βιβλίο αναφοράς, σύμφωνα με το οποίο πρέπει να κάνετε μια μερική αντικατάσταση μιας υποσυμβολοσειράς μέσα στη διεύθυνση (ονόμασα αυτόν τον πίνακα Αντικαταστάσεις 2).

Η θεμελιώδης διαφορά εδώ είναι ότι πρέπει να αντικαταστήσετε μόνο ένα τμήμα των αρχικών δεδομένων - για παράδειγμα, η πρώτη διεύθυνση έχει λανθασμένη «Αγ. Πετρούπολη» σχετικά με το δικαίωμα «Αγ. Πετρούπολη», αφήνοντας την υπόλοιπη διεύθυνση (ταχυδρομικός κώδικας, δρόμος, σπίτι) ως έχει.

Ο τελικός τύπος θα μοιάζει με αυτό (για ευκολία αντίληψης, τον χώρισα σε πόσες γραμμές χρησιμοποιώντας άλλος+εισάγετε):

Η κύρια εργασία εδώ γίνεται από την τυπική συνάρτηση κειμένου Excel ΥΠΟΚΑΤΑΣΤΑΤΟ (ΥΠΟΚΑΤΑΣΤΑΤΟ), το οποίο έχει 3 ορίσματα:

1. Πηγαίο κείμενο – η πρώτη λανθασμένη διεύθυνση από τη στήλη Διεύθυνση
2. Αυτό που ψάχνουμε – εδώ χρησιμοποιούμε το κόλπο με τη συνάρτηση ΔΕΙΤΕ (ΨΑΧΝΩ)από τον προηγούμενο τρόπο για να τραβήξετε την τιμή από τη στήλη Να βρω, το οποίο περιλαμβάνεται ως απόσπασμα σε μια καμπύλη διεύθυνση.
3. Με τι να αντικαταστήσουμε – με τον ίδιο τρόπο βρίσκουμε τη σωστή τιμή που αντιστοιχεί σε αυτήν από τη στήλη Υποκατάστατο.

Εισαγάγετε αυτόν τον τύπο με Ctrl+αλλαγή+εισάγετε δεν χρειάζεται ούτε εδώ, αν και είναι, στην πραγματικότητα, ένας τύπος πίνακα.

Και φαίνεται ξεκάθαρα (δείτε #N/A σφάλματα στην προηγούμενη εικόνα) ότι μια τέτοια φόρμουλα, παρ' όλη την κομψότητά της, έχει μερικά μειονεκτήματα:

• Λειτουργία Το SUBSTITUTE κάνει διάκριση πεζών-κεφαλαίων, οπότε το "Spb" στην προτελευταία γραμμή δεν βρέθηκε στον πίνακα αντικατάστασης. Για να λύσετε αυτό το πρόβλημα, μπορείτε είτε να χρησιμοποιήσετε τη συνάρτηση ΖΑΜΕΝΙΤ (ΑΝΤΙΚΑΘΙΣΤΩ), ή προκαταρκτικά φέρτε και τους δύο πίνακες στο ίδιο μητρώο.
• Αν το κείμενο είναι αρχικά σωστό ή μέσα σε αυτό δεν υπάρχει θραύσμα προς αντικατάσταση (τελευταία γραμμή), τότε ο τύπος μας βγάζει ένα σφάλμα. Αυτή η στιγμή μπορεί να εξουδετερωθεί αναχαιτίζοντας και αντικαθιστώντας τα σφάλματα χρησιμοποιώντας τη λειτουργία ΑΦΑΝΙΣΤΗΡΙΟ (IFERROR):

  

• Εάν το αρχικό κείμενο περιέχει πολλά κομμάτια από τον κατάλογο ταυτόχρονα, τότε ο τύπος μας αντικαθιστά μόνο τον τελευταίο (στην 8η γραμμή, Ligovsky «Λεωφόρος« άλλαξε σε "pr-t", Αλλά "S-Pb" on «Αγ. Πετρούπολη» όχι πλέον, γιατί «S-Pb” βρίσκεται ψηλότερα στον κατάλογο). Αυτό το πρόβλημα μπορεί να λυθεί εκτελώντας ξανά τον δικό μας τύπο, αλλά ήδη κατά μήκος της στήλης Σταθερό:

  
  

Όχι τέλειο και δυσκίνητο κατά τόπους, αλλά πολύ καλύτερο από την ίδια χειροκίνητη αντικατάσταση, σωστά; 🙂

PS

Στο επόμενο άρθρο, θα καταλάβουμε πώς να εφαρμόσουμε μια τέτοια μαζική αντικατάσταση χρησιμοποιώντας μακροεντολές και Power Query.

• Πώς λειτουργεί η λειτουργία SUBSTITUTE για την αντικατάσταση κειμένου
  
• Εύρεση ακριβών αντιστοιχίσεων κειμένου με χρήση της συνάρτησης EXACT
• Αναζήτηση και αντικατάσταση με διάκριση πεζών-κεφαλαίων (VLOOKUP με διάκριση πεζών-κεφαλαίων)