ένας φίλος μουΕίμαι συχνά σε επαφή μαζί του για ορισμένα από τα πραγματικά προβλήματα που αντιμετωπίζει όταν εργάζεται με δεδομένα στο Excel.
Πολλές φορές μεταφράζω το ερώτημά του σε ένα σεμινάριο Excel σε αυτόν τον ιστότοπο, επειδή μπορεί να είναι χρήσιμο και στους άλλους αναγνώστες μου.
Αυτό είναι επίσης ένα τέτοιο σεμινάριο.
Ο φίλος μου μου τηλεφώνησε την περασμένη εβδομάδα με τις ακόλουθες ερωτήσεις:
Έχω δεδομένα διεύθυνσης σε μια στήλη στο Excel και θέλω να αναγνωρίσω/φιλτράρω κελιά που περιέχουν επαναλαμβανόμενες συμβολοσειρές κειμένου (λέξεις) στη διεύθυνση.
Ακολουθεί ένα παρόμοιο σύνολο δεδομένων όπου θέλει να φιλτράρει κελιά (κελιά με κόκκινα βέλη) που έχουν επαναλαμβανόμενες συμβολοσειρές κειμένου:
Αυτό που το κάνει δύσκολο τώρα είναι ότι τα δεδομένα δεν είναι συνεπή.Δεδομένου ότι πρόκειται για μια συλλογή συνόλων δεδομένων που δημιουργούνται με μη αυτόματο τρόπο από αντιπροσώπους πωλήσεων, ενδέχεται να υπάρχουν αποκλίσεις στα σύνολα δεδομένων.
Σκέψου το:
- Οποιαδήποτε συμβολοσειρά κειμένου μπορεί να επαναληφθεί σε αυτό το σύνολο δεδομένων.Για παράδειγμα, μπορεί να είναι ένα όνομα περιοχής ή ένα όνομα πόλης ή και τα δύο.
- Οι λέξεις χωρίζονται με διαστήματα και δεν υπάρχει συνέπεια στο αν το όνομα της πόλης είναι μετά από έξι ή οκτώ χαρακτήρες.
- Υπάρχουν χιλιάδες τέτοιες εγγραφές και πρέπει να φιλτράρετε αυτές που έχουν επαναλαμβανόμενες συμβολοσειρές κειμένου.
Αφού εξέτασα πολλές επιλογές, όπως κείμενο σε στήλες και τύπους, τελικά αποφάσισα να χρησιμοποιήσω το VBA για να κάνω τη δουλειά.
Έτσι, δημιούργησα μια προσαρμοσμένη συνάρτηση VBA ('IdDuplicate') για να αναλύσω αυτά τα κελιά και να μου δώσει TRUE εάν υπάρχουν διπλότυπες λέξεις στη συμβολοσειρά κειμένου και FALSE εάν δεν υπάρχουν διπλότυπα (όπως φαίνεται παρακάτω):
Αυτή η προσαρμοσμένη συνάρτηση αναλύει κάθε λέξη στη συμβολοσειρά κειμένου και ελέγχει πόσες φορές εμφανίζεται στο κείμενο.Επιστρέφει TRUE εάν το πλήθος είναι μεγαλύτερο από 1.Διαφορετικά επιστρέψτε FALSE.
Επίσης, δημιουργήθηκε για να μετράει μόνο λέξεις με περισσότερους από τρεις χαρακτήρες.
Μόλις έχω τα δεδομένα TRUE/FALSE, μπορώ εύκολα να φιλτράρω όλες τις εγγραφές που είναι TRUE.
Τώρα επιτρέψτε μου να σας δείξω πώς να το κάνετε αυτό στο Excel.
Κωδικός VBA για προσαρμοσμένη λειτουργία
Αυτό γίνεται με τη δημιουργία μιας προσαρμοσμένης συνάρτησης στο VBA.Αυτή η συνάρτηση μπορεί στη συνέχεια να χρησιμοποιηθεί ως οποιαδήποτε άλλη συνάρτηση φύλλου εργασίας στο Excel.
Εδώ είναι ο κωδικός VBA του:
Συνάρτηση IdDuplicates(rng ως εύρος) Ως συμβολοσειρά Dim StringtoAnalyze Ως παραλλαγή Dim i Ως ακέραιος Dim j Ως ακέραιος Const minWordLen ως ακέραιος = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") Για i = UBoundnazely(ToA)toA. Βήμα -0 If Len(StringtoAnalyze(i)) < minWordLen Τότε GoTo SkipA Για j = 1 To i - 0 If StringtoAnalyze(j) = StringtoAnalyze(i) Τότε IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: End Function
Πώς να χρησιμοποιήσετε αυτόν τον κώδικα VBA
Τώρα που έχετε τον κώδικα VBA, πρέπει να τον τοποθετήσετε στο backend του Excel, ώστε να μπορεί να λειτουργεί ως κανονική συνάρτηση φύλλου εργασίας.
Ακολουθούν τα βήματα για να τοποθετήσετε τον κώδικα VBA στο backend:
- Μεταβείτε στην καρτέλα προγραμματιστή.
- Κάντε κλικ στη Visual Basic (μπορείτε επίσης να χρησιμοποιήσετε τη συντόμευση πληκτρολογίου ALT+F11)
- Στο ανοιχτό backend του προγράμματος επεξεργασίας VB, κάντε δεξί κλικ σε οποιοδήποτε αντικείμενο βιβλίου εργασίας.
- Μεταβείτε στο "Εισαγωγή" και κάντε κλικ στο "Module".Αυτό θα εισαγάγει το αντικείμενο της ενότητας του βιβλίου εργασίας.
- Στο παράθυρο κώδικα μονάδας, αντιγράψτε και επικολλήστε τον κώδικα VBA που αναφέρεται παραπάνω.
Αφού έχετε τον κωδικό VBA στο backend, μπορείτε να χρησιμοποιήσετε τη συνάρτηση - 'IdDuplicates' όπως οποιαδήποτε άλλη κανονική συνάρτηση φύλλου εργασίας.
Αυτή η συνάρτηση παίρνει μία παράμετρο, την αναφορά κελιού του κελιού όπου έχετε το κείμενο.
Το αποτέλεσμα αυτής της συνάρτησης είναι TRUE (αν υπάρχουν διπλότυπες λέξεις) ή FALSE (αν δεν υπάρχουν διπλότυπες).Αφού έχετε αυτήν τη λίστα TRUE/FALSE, μπορείτε να φιλτράρετε αυτές με TRUE για να λάβετε όλα τα κελιά που έχουν επαναλαμβανόμενες συμβολοσειρές κειμένου σε αυτά.
Σημείωση: Ο κωδικός που δημιούργησα αφορά μόνο λέξεις που ξεπερνούν τους τρεις χαρακτήρες.Αυτό διασφαλίζει ότι εάν υπάρχουν λέξεις στη συμβολοσειρά κειμένου που έχουν μήκος 1, 2 ή 3 χαρακτήρες (π.χ. 12 A, KGM ή LDA), αυτές οι λέξεις αγνοούνται κατά την καταμέτρηση των διπλότυπων.Μπορείτε εύκολα να το αλλάξετε σε κώδικα εάν χρειάζεται.
Αυτή η δυνατότητα είναι διαθέσιμη μόνο στο βιβλίο εργασίας όπου αντιγράψατε τον κώδικα στη λειτουργική μονάδα.Εάν θέλετε αυτός ο κωδικός να είναι διαθέσιμος και σε άλλα βιβλία εργασίας, πρέπει να αντιγράψετε και να επικολλήσετε αυτόν τον κωδικό σε αυτά τα βιβλία εργασίας.Εναλλακτικά, μπορείτε να δημιουργήσετε ένα πρόσθετο (αν ενεργοποιήσετε αυτό, η δυνατότητα θα είναι διαθέσιμη σε όλα τα βιβλία εργασίας του συστήματός σας).
Επίσης, θυμηθείτε να αποθηκεύσετε αυτό το βιβλίο εργασίας με επέκταση .xlsm (επειδή περιέχει κώδικα μακροεντολής).
