Περιεχόμενα
Έχω ήδη γράψει για το πώς μπορείτε να κολλήσετε γρήγορα κείμενο από πολλά κελιά σε ένα και, αντίθετα, να αναλύσετε μια μεγάλη συμβολοσειρά κειμένου σε στοιχεία. Ας δούμε τώρα μια στενή, αλλά ελαφρώς πιο περίπλοκη εργασία – πώς να κολλήσετε κείμενο από πολλά κελιά όταν πληρούται μια συγκεκριμένη καθορισμένη συνθήκη.
Ας πούμε ότι έχουμε μια βάση δεδομένων πελατών, όπου ένα όνομα εταιρείας μπορεί να αντιστοιχεί σε πολλά διαφορετικά email των υπαλλήλων της. Καθήκον μας είναι να συλλέξουμε όλες τις διευθύνσεις ανά ονόματα εταιρειών και να τις συνδέσουμε (χωρισμένες με κόμμα ή ερωτηματικά) για να δημιουργήσουμε, για παράδειγμα, μια λίστα αλληλογραφίας για πελάτες, δηλαδή να λάβουμε έξοδο κάτι σαν:
Χρειαζόμαστε δηλαδή ένα εργαλείο που θα κολλάει (συνδέει) το κείμενο ανάλογα με την συνθήκη – ένα ανάλογο της συνάρτησης ΣΟΥΜΜΕΣΛΙ (SUMIF), αλλά για κείμενο.
Μέθοδος 0. Τύπος
Όχι πολύ κομψό, αλλά ο πιο εύκολος τρόπος. Μπορείτε να γράψετε έναν απλό τύπο που θα ελέγχει αν η εταιρεία στην επόμενη σειρά διαφέρει από την προηγούμενη. Εάν δεν διαφέρει, τότε κολλήστε την επόμενη διεύθυνση χωρισμένη με κόμμα. Εάν διαφέρει, τότε "επαναφέρουμε" τα συσσωρευμένα, ξεκινώντας ξανά:
Τα μειονεκτήματα αυτής της προσέγγισης είναι προφανή: από όλα τα κελιά της πρόσθετης στήλης που ελήφθη, χρειαζόμαστε μόνο τα τελευταία για κάθε εταιρεία (κίτρινο). Εάν η λίστα είναι μεγάλη, τότε για να τις επιλέξετε γρήγορα, θα πρέπει να προσθέσετε άλλη στήλη χρησιμοποιώντας τη συνάρτηση DLSTR (LEN), ελέγχοντας το μήκος των συσσωρευμένων χορδών:
Τώρα μπορείτε να φιλτράρετε αυτά και να αντιγράψετε την απαραίτητη κόλλα διεύθυνσης για περαιτέρω χρήση.
Μέθοδος 1. Μακρολειτουργία κόλλησης κατά μία συνθήκη
Εάν η αρχική λίστα δεν είναι ταξινομημένη ανά εταιρεία, τότε ο παραπάνω απλός τύπος δεν λειτουργεί, αλλά μπορείτε εύκολα να μετακινηθείτε με μια μικρή προσαρμοσμένη συνάρτηση στο VBA. Ανοίξτε το Visual Basic Editor πατώντας μια συντόμευση πληκτρολογίου Alt + F11 ή χρησιμοποιώντας το κουμπί Visual Basic αυτί προγραμματιστή (Προγραμματιστής). Στο παράθυρο που ανοίγει, εισαγάγετε μια νέα κενή μονάδα μέσω του μενού Εισαγωγή – Ενότητα και αντιγράψτε το κείμενο της συνάρτησής μας εκεί:
Συνάρτηση MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " οι κολλήσεις δεν είναι ίσες μεταξύ τους - βγαίνουμε με σφάλμα Αν SearchRange.Count <> TextRange.Count Στη συνέχεια MergeIf = CVERr(xlErrRef) Έξοδος Συνάρτηση Τέλος Εάν «περάστε από όλα τα κελιά, ελέγξτε τη συνθήκη και συλλέξτε το κείμενο στη μεταβλητή OutText For i = 1 To Search Range. Cells.Count If SearchRange.Cells(i) Like Condition then OutText = OutText & TextRange.Cells(i) & Delimeter Επόμενο "εμφανίζω αποτελέσματα χωρίς τελευταίο οριοθέτη MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) Τέλος λειτουργία
Εάν τώρα επιστρέψετε στο Microsoft Excel, τότε στη λίστα των συναρτήσεων (κουμπί fx στη γραμμή τύπων ή στην καρτέλα Τύποι – Συνάρτηση εισαγωγής) θα είναι δυνατή η εύρεση της λειτουργίας μας MergeIf στην κατηγορία Ορίζεται Χρήστη (Ορισμένο από τον χρήστη). Τα ορίσματα της συνάρτησης είναι τα εξής:
Μέθοδος 2. Συνδέστε κείμενο με ανακριβή συνθήκη
Αν αντικαταστήσουμε τον πρώτο χαρακτήρα στη 13η γραμμή της μακροεντολής μας = στον χειριστή κατά προσέγγιση αντιστοίχισης Αρέσει, τότε θα είναι δυνατό να πραγματοποιηθεί η κόλληση με ανακριβή αντιστοίχιση των αρχικών δεδομένων με το κριτήριο επιλογής. Για παράδειγμα, εάν το όνομα της εταιρείας μπορεί να γραφτεί σε διαφορετικές παραλλαγές, τότε μπορούμε να τις ελέγξουμε και να τις συλλέξουμε όλες με μία συνάρτηση:
Υποστηρίζονται τυπικοί χαρακτήρες μπαλαντέρ:
- αστερίσκος (*) – υποδηλώνει οποιονδήποτε αριθμό χαρακτήρων (συμπεριλαμβανομένης της απουσίας τους)
- ερωτηματικό (?) – σημαίνει οποιονδήποτε μεμονωμένο χαρακτήρα
- σύμβολο λίβρας (#) - σημαίνει οποιοδήποτε μονοψήφιο (0-9)
Από προεπιλογή, ο τελεστής Like κάνει διάκριση πεζών-κεφαλαίων, δηλαδή κατανοεί, για παράδειγμα, το "Orion" και το "orion" ως διαφορετικές εταιρείες. Για να αγνοήσετε πεζά και πεζά, μπορείτε να προσθέσετε τη γραμμή στην αρχή της ενότητας στο πρόγραμμα επεξεργασίας της Visual Basic Επιλογή Σύγκριση κειμένου, το οποίο θα κάνει εναλλαγή Like για διάκριση πεζών-κεφαλαίων.
Με αυτόν τον τρόπο, μπορείτε να συνθέσετε πολύ περίπλοκες μάσκες για τον έλεγχο των συνθηκών, για παράδειγμα:
- ?1##??777RUS – επιλογή όλων των πινακίδων κυκλοφορίας της περιοχής 777, ξεκινώντας από 1
- LLC* – όλες οι εταιρείες των οποίων το όνομα αρχίζει με LLC
- ##7## – όλα τα προϊόντα με πενταψήφιο ψηφιακό κωδικό, όπου το τρίτο ψηφίο είναι 7
- ????? – όλα τα ονόματα των πέντε γραμμάτων κ.λπ.
Μέθοδος 3. Λειτουργία μακροεντολής για κόλληση κειμένου υπό δύο συνθήκες
Στην εργασία μπορεί να υπάρχει πρόβλημα όταν πρέπει να συνδέσετε το κείμενο περισσότερες από μία προϋποθέσεις. Για παράδειγμα, ας φανταστούμε ότι στον προηγούμενο πίνακα μας, προστέθηκε μια ακόμη στήλη με την πόλη και η κόλληση θα πρέπει να γίνει όχι μόνο για μια δεδομένη εταιρεία, αλλά και για μια δεδομένη πόλη. Σε αυτήν την περίπτωση, η λειτουργία μας θα πρέπει να εκσυγχρονιστεί ελαφρώς προσθέτοντας έναν άλλο έλεγχο εύρους σε αυτήν:
Συνάρτηση MergeIfs(TextRange ως εύρος, Search Range1 ως εύρος, Condition1 As String, Search Range2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'χαρακτήρες οριοθέτησης (μπορούν να αντικατασταθούν με κενό ή ; κ.λπ.) ε.) 'εάν τα εύρη επικύρωσης και συγκόλλησης δεν είναι ίσα μεταξύ τους, βγείτε με ένα σφάλμα Εάν SearchRange1.Count <> TextRange.Count ή SearchRange2.Count <> TextRange.Count Τότε MergeIfs = CVERr(xlErrRef) Έξοδος Συνάρτηση Τέλος Εάν "περάστε από όλα τα κελιά, ελέγξτε όλες τις συνθήκες και συλλέξτε το κείμενο στη μεταβλητή OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Στη συνέχεια OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'εμφανίζω αποτελέσματα χωρίς τελευταίο οριοθέτη MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Θα εφαρμοστεί ακριβώς με τον ίδιο τρόπο – μόνο τα ορίσματα πρέπει τώρα να διευκρινιστούν περισσότερο:
Μέθοδος 4. Ομαδοποίηση και κόλληση στο Power Query
Μπορείτε να λύσετε το πρόβλημα χωρίς προγραμματισμό σε VBA, εάν χρησιμοποιήσετε το δωρεάν πρόσθετο Power Query. Για το Excel 2010-2013 μπορείτε να το κατεβάσετε εδώ και στο Excel 2016 είναι ήδη ενσωματωμένο από προεπιλογή. Η σειρά των ενεργειών θα είναι η εξής:
Το Power Query δεν ξέρει πώς να δουλεύει με κανονικούς πίνακες, επομένως το πρώτο βήμα είναι να μετατρέψουμε το τραπέζι μας σε «έξυπνο». Για να το κάνετε αυτό, επιλέξτε το και πατήστε το συνδυασμό Ctrl+T ή επιλέξτε από την καρτέλα Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα). Στην καρτέλα που εμφανίζεται στη συνέχεια Κατασκευαστής (Σχέδιο) μπορείτε να ορίσετε το όνομα του πίνακα (άφησα το πρότυπο Πίνακας 1):
Τώρα ας φορτώσουμε τον πίνακα μας στο πρόσθετο Power Query. Για να το κάνετε αυτό, στην καρτέλα ημερομηνία (εάν έχετε Excel 2016) ή στην καρτέλα Power Query (αν έχετε Excel 2010-2013) κάντε κλικ Από το τραπέζι (Δεδομένα — Από πίνακα):
Στο παράθυρο του προγράμματος επεξεργασίας ερωτημάτων που ανοίγει, επιλέξτε τη στήλη κάνοντας κλικ στην κεφαλίδα Εταιρεία και πατήστε το κουμπί παραπάνω Group (Ομαδοποίηση κατά). Εισαγάγετε το όνομα της νέας στήλης και τον τύπο λειτουργίας στην ομαδοποίηση – Όλες οι γραμμές (Όλες οι σειρές):
Κάντε κλικ στο OK και λαμβάνουμε έναν μίνι πίνακα ομαδοποιημένων τιμών για κάθε εταιρεία. Τα περιεχόμενα των πινάκων είναι ευδιάκριτα αν κάνετε αριστερό κλικ στο λευκό φόντο των κελιών (όχι στο κείμενο!) στη στήλη που προκύπτει:
Τώρα ας προσθέσουμε μια ακόμη στήλη, όπου, χρησιμοποιώντας τη συνάρτηση, κολλάμε τα περιεχόμενα των στηλών Διεύθυνση σε κάθε έναν από τους μίνι-πίνακες, χωρισμένα με κόμματα. Για να το κάνετε αυτό, στην καρτέλα Προσθήκη στήλης πατάμε Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη) και στο παράθυρο που εμφανίζεται, εισαγάγετε το όνομα της νέας στήλης και τον τύπο σύζευξης στη γλώσσα M που είναι ενσωματωμένη στο Power Query:
Σημειώστε ότι όλες οι συναρτήσεις M κάνουν διάκριση πεζών-κεφαλαίων (σε αντίθεση με το Excel). Αφού κάνετε κλικ στο OK παίρνουμε μια νέα στήλη με κολλημένες διευθύνσεις:
Απομένει να αφαιρέσετε την ήδη περιττή στήλη Διευθύνσεις πίνακα (δεξί κλικ στον τίτλο) Διαγραφή στήλης) και ανεβάστε τα αποτελέσματα στο φύλλο κάνοντας κλικ στην καρτέλα Αρχική σελίδα — Κλείσιμο και λήψη (Αρχική σελίδα — Κλείσιμο και φόρτωση):
Σημαντική απόχρωση: Σε αντίθεση με τις προηγούμενες μεθόδους (συναρτήσεις), οι πίνακες από το Power Query δεν ενημερώνονται αυτόματα. Εάν στο μέλλον υπάρξουν αλλαγές στα δεδομένα προέλευσης, τότε θα χρειαστεί να κάνετε δεξί κλικ οπουδήποτε στον πίνακα αποτελεσμάτων και να επιλέξετε την εντολή Ενημέρωση & Αποθήκευση (Φρεσκάρω).
- Πώς να χωρίσετε μια μεγάλη συμβολοσειρά κειμένου σε μέρη
- Διάφοροι τρόποι για να κολλήσετε κείμενο από διαφορετικά κελιά σε ένα
- Χρήση του τελεστή Like για δοκιμή κειμένου σε σχέση με μια μάσκα