Ένας από τους φίλους μου εργάζεται σε εταιρεία ανάλυσης υγειονομικής περίθαλψης. Συχνά συνδέεται μαζί μου για μερικά από τα πραγματικά ζητήματα που αντιμετωπίζει ενώ εργάζεται με δεδομένα στο Excel.
Πολλές φορές, μετατρέπω τα ερωτήματά του σε σεμινάρια Excel σε αυτόν τον ιστότοπο, καθώς θα μπορούσε να είναι χρήσιμο και για τους άλλους αναγνώστες μου.
Αυτό είναι επίσης ένα τέτοιο σεμινάριο.
Ο φίλος μου με πήρε τηλέφωνο την προηγούμενη εβδομάδα με το ακόλουθο ζήτημα:
Υπάρχουν δεδομένα διευθύνσεων σε μια στήλη στο Excel και θέλω να προσδιορίσω/φιλτράρω κελιά όπου η διεύθυνση έχει διπλές συμβολοσειρές κειμένου (λέξεις).
Εδώ είναι το παρόμοιο σύνολο δεδομένων στο οποίο ήθελε να φιλτράρει κελιά που έχουν διπλή συμβολοσειρά κειμένου (αυτά με κόκκινα βέλη):
Αυτό που δυσκολεύει τώρα είναι ότι δεν υπάρχει συνέπεια σε αυτά τα δεδομένα. Δεδομένου ότι πρόκειται για μια συλλογή δεδομένων που έχει δημιουργηθεί με μη αυτόματο τρόπο από τους εκπροσώπους πωλήσεων, μπορεί να υπάρχουν παραλλαγές στο σύνολο δεδομένων.
Σκεφτείτε αυτό:
- Οποιαδήποτε συμβολοσειρά κειμένου θα μπορούσε να επαναληφθεί σε αυτό το σύνολο δεδομένων. Για παράδειγμα, μπορεί να είναι το όνομα της περιοχής ή το όνομα της πόλης ή και τα δύο.
- Οι λέξεις χωρίζονται από έναν κενό χαρακτήρα και δεν υπάρχει συνέπεια στο αν το όνομα της πόλης θα ήταν εκεί μετά από έξι λέξεις ή οκτώ λέξεις.
- Υπάρχουν χιλιάδες εγγραφές όπως αυτή, και η ανάγκη είναι να φιλτράρετε αυτές τις εγγραφές όπου υπάρχουν διπλές συμβολοσειρές κειμένου.
Αφού εξέτασα πολλές επιλογές (όπως κείμενο σε στήλες και τύπους), αποφάσισα τελικά να χρησιμοποιήσω το VBA για να το κάνω αυτό.
Έτσι δημιούργησα μια προσαρμοσμένη συνάρτηση VBA («IdDuplicate») για να αναλύσω αυτά τα κελιά και να μου δώσω TRUE αν υπάρχει διπλή λέξη στη συμβολοσειρά κειμένου και FALSE σε περίπτωση που δεν υπάρχουν επαναλήψεις (όπως φαίνεται παρακάτω):
Αυτή η προσαρμοσμένη συνάρτηση αναλύει κάθε λέξη στη συμβολοσειρά κειμένου και ελέγχει πόσες φορές εμφανίζεται στο κείμενο. Εάν η καταμέτρηση είναι μεγαλύτερη από 1, επιστρέφει TRUE. αλλιώς επιστρέφει ΛΑΘΟΣ.
Επίσης, έχει δημιουργηθεί για να μετρά μόνο λέξεις περισσότερους από τρεις χαρακτήρες.
Μόλις έχω τα αληθινά/ψευδή δεδομένα, μπορώ εύκολα να φιλτράρω όλες τις εγγραφές που είναι ΑΛΗΘΙΝΕΣ.
Τώρα επιτρέψτε μου να σας δείξω πώς να το κάνετε αυτό στο Excel.
Κωδικός VBA για την προσαρμοσμένη λειτουργία
Αυτό γίνεται δημιουργώντας μια προσαρμοσμένη συνάρτηση στο VBA. Αυτή η συνάρτηση μπορεί στη συνέχεια να χρησιμοποιηθεί ως οποιαδήποτε άλλη συνάρτηση φύλλου εργασίας στο Excel.
Εδώ είναι ο κωδικός VBA για αυτό:
Function IdDuplicates (rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 Βήμα -1 Αν Len (StringtoAnalyze (i)) <minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: End Function
Ευχαριστώ Walter που πρότεινες μια καλύτερη προσέγγιση σε αυτόν τον κώδικα!
Πώς να χρησιμοποιήσετε αυτόν τον κώδικα VBA
Τώρα που διαθέτετε τον κωδικό VBA, πρέπει να τον τοποθετήσετε στο backend του Excel, έτσι ώστε να μπορεί να λειτουργήσει ως κανονική συνάρτηση φύλλου εργασίας.
Παρακάτω είναι τα βήματα για να τοποθετήσετε τον κωδικό VBA στο backend:
- Μεταβείτε στην καρτέλα Προγραμματιστής.
- Κάντε κλικ στο Visual Basic (μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου ALT + F11)
- Στο πίσω άκρο του VB Editor που ανοίγει, κάντε δεξί κλικ σε οποιοδήποτε από τα αντικείμενα του βιβλίου εργασίας.
- Μεταβείτε στο «Εισαγωγή» και κάντε κλικ στο «Ενότητα». Αυτό θα εισαγάγει το αντικείμενο μονάδας για το βιβλίο εργασίας.
- Στο παράθυρο κωδικού μονάδας, αντιγράψτε και επικολλήστε τον κωδικό VBA που αναφέρθηκε παραπάνω.
Μόλις έχετε τον κωδικό VBA στο πίσω άκρο, μπορείτε να χρησιμοποιήσετε τη συνάρτηση - "IdDuplicates" όπως οποιαδήποτε άλλη κανονική συνάρτηση φύλλου εργασίας.
Αυτή η συνάρτηση λαμβάνει ένα μόνο όρισμα, το οποίο είναι η αναφορά κελιού του κελιού όπου έχετε το κείμενο.
Το αποτέλεσμα της συνάρτησης είναι TRUE (εάν υπάρχουν διπλές λέξεις σε αυτό) ή FALSE (εάν δεν υπάρχουν διπλότυπα). Μόλις έχετε αυτήν τη λίστα TRUE/FALSE, μπορείτε να φιλτράρετε αυτά με TRUE για να λάβετε όλα τα κελιά που έχουν διπλές συμβολοσειρές κειμένου.
Σημείωση: Έχω δημιουργήσει τον κώδικα μόνο για να λάβω υπόψη τις λέξεις που έχουν μήκος μεγαλύτερο από τρεις χαρακτήρες. Αυτό διασφαλίζει ότι εάν υπάρχουν 1, 2 ή 3 λέξεις με χαρακτήρες (όπως 12 A, K G M ή L D A) στη συμβολοσειρά κειμένου, αυτές θα αγνοηθούν κατά την καταμέτρηση των διπλότυπων. Εάν θέλετε, μπορείτε εύκολα να το αλλάξετε στον κώδικα.
Αυτή η λειτουργία θα είναι διαθέσιμη μόνο στο βιβλίο εργασίας όπου έχετε αντιγράψει τον κώδικα στη μονάδα. Σε περίπτωση που θέλετε να είναι διαθέσιμο και σε άλλα βιβλία εργασίας, πρέπει να αντιγράψετε και να επικολλήσετε αυτόν τον κώδικα σε αυτά τα βιβλία εργασίας. Εναλλακτικά, μπορείτε επίσης να δημιουργήσετε ένα πρόσθετο (ενεργοποιώντας το οποίο θα κάνει αυτή τη λειτουργία διαθέσιμη σε όλα τα βιβλία εργασίας του συστήματός σας).
Επίσης, θυμηθείτε να αποθηκεύσετε αυτό το βιβλίο εργασίας σε επέκταση .xlsm (αφού έχει κώδικα μακροεντολής).
