Ταξινόμηση δεδομένων στο Excel VBA
Το Excel διαθέτει ένα εξαιρετικό μέσο για την ταξινόμηση μιας σειράς από πίνακες δεδομένων χρησιμοποιώντας την κορδέλα στο μπροστινό άκρο του Excel και κάποια στιγμή, πιθανότατα θα θέλετε να χρησιμοποιήσετε αυτήν τη λειτουργικότητα στον κώδικα VBA. Ευτυχώς, αυτό είναι πολύ εύκολο να γίνει.
Το παράθυρο διαλόγου front-end βρίσκεται κάνοντας κλικ στο εικονίδιο "Ταξινόμηση" στην ομάδα "Ταξινόμηση & Φίλτρο" της καρτέλας "Δεδομένα" στην κορδέλα του Excel. Πρέπει πρώτα να επιλέξετε μια σειρά από πίνακες δεδομένων.
Μπορείτε επίσης να χρησιμοποιήσετε το Alt-A-S-S για να εμφανίσετε το παράθυρο διαλόγου για μια προσαρμοσμένη ταξινόμηση.
Η μέθοδος ταξινόμησης έχει βελτιωθεί σημαντικά σε μεταγενέστερες εκδόσεις του Excel. Η ταξινόμηση περιοριζόταν σε τρία επίπεδα, αλλά τώρα μπορείτε να εισάγετε όσα επίπεδα χρειάζεστε, και αυτό ισχύει και στο VBA.
Μπορείτε να ενσωματώσετε όλες τις λειτουργίες ταξινόμησης που προσφέρονται στο πλαίσιο διαλόγου Ταξινόμηση Excel στον κώδικα VBA. Η λειτουργία ταξινόμησης στο Excel είναι γρήγορη και ταχύτερη από οτιδήποτε θα μπορούσατε να γράψετε μόνοι σας σε VBA, οπότε επωφεληθείτε από τη λειτουργικότητα.
Σημειώστε ότι όταν κάνετε μια ταξινόμηση σε VBA, οι παράμετροι ταξινόμησης παραμένουν οι ίδιες στο παράθυρο διαλόγου ταξινόμησης στο μπροστινό μέρος. Επίσης αποθηκεύονται όταν αποθηκεύεται το βιβλίο εργασίας.
Εάν ένας χρήστης επιλέξει το ίδιο εύρος δεδομένων πίνακα και κάνει κλικ στο εικονίδιο Ταξινόμηση, θα δει όλες τις παραμέτρους που έχουν εισαχθεί από τον κωδικό VBA. Αν θέλουν να κάνουν ένα είδος δικού τους σχεδιασμού, τότε θα πρέπει να διαγράψουν όλα τα επίπεδα ταξινόμησής σας, κάτι που θα τους ενοχλήσει πολύ.
Επίσης, εάν δεν αλλάξετε τις παραμέτρους στον κώδικά σας και βασιστείτε στις προεπιλεγμένες τιμές, ενδέχεται να διαπιστώσετε ότι ο χρήστης έχει πραγματοποιήσει αλλαγές που θα αντικατοπτρίζονται στο είδος VBA και μπορεί να δώσει απροσδόκητα αποτελέσματα, τα οποία μπορεί να είναι πολύ δύσκολο να εντοπιστούν σφάλματα Το
Ευτυχώς, υπάρχει μια μέθοδος Εκκαθάριση στο VBA για να επαναρυθμίσετε όλες τις παραμέτρους ταξινόμησης, έτσι ώστε ο χρήστης να βλέπει ένα καθαρό παράθυρο διαλόγου ταξινόμησης
| 1 | Φύλλα εργασίας ("Φύλλο1"). Ταξινόμηση.SortFields.Clear |
Είναι καλή πρακτική να καθαρίζετε τις παραμέτρους ταξινόμησης στο VBA πριν και μετά την ολοκλήρωση της ταξινόμησης.
Πρακτική χρήση της μεθόδου ταξινόμησης στο VBA
Όταν τα δεδομένα πίνακα εισάγονται στο Excel, είναι συχνά σε πολύ τυχαία σειρά. Θα μπορούσε να εισαχθεί από ένα αρχείο CSV (τιμές διαχωρισμένες με κόμμα) ή θα μπορούσε να προέλθει από έναν σύνδεσμο σε μια βάση δεδομένων ή μια ιστοσελίδα. Δεν μπορείτε να βασιστείτε σε μια ορισμένη σειρά από τη μία εισαγωγή στην άλλη.
Εάν παρουσιάζετε αυτά τα δεδομένα σε έναν χρήστη στο φύλλο εργασίας σας, ο χρήστης ενδέχεται να δυσκολεύεται να εξετάσει και να κατανοήσει έναν τεράστιο όγκο δεδομένων, ο οποίος από πλευράς σειράς βρίσκεται παντού. Μπορεί να θέλουν να ομαδοποιήσουν τα δεδομένα ή να κόψουν και να επικολλήσουν ορισμένα τμήματα τους σε άλλη εφαρμογή.
Μπορεί επίσης να θέλουν να δουν, για παράδειγμα, τον πιο ακριβοπληρωμένο υπάλληλο ή τον υπάλληλο με τη μεγαλύτερη υπηρεσία.
Χρησιμοποιώντας τη μέθοδο Ταξινόμηση σε VBA, μπορείτε να προσφέρετε επιλογές που επιτρέπουν την εύκολη ταξινόμηση για τον χρήστη.
Δείγματα δεδομένων για επίδειξη ταξινόμησης Excel με VBA
Χρειαζόμαστε πρώτα μερικά δείγματα δεδομένων για να καταχωρηθούν σε ένα φύλλο εργασίας, έτσι ώστε ο κώδικας να μπορεί να επιδείξει όλες τις διαθέσιμες εγκαταστάσεις στο VBA.
Αντιγράψτε αυτά τα δεδομένα σε ένα φύλλο εργασίας (που ονομάζεται «Φύλλο1») ακριβώς όπως φαίνεται.
Λάβετε υπόψη ότι έχουν χρησιμοποιηθεί διαφορετικά χρώματα φόντου κελιών και χρώματα γραμματοσειράς, καθώς αυτά μπορούν επίσης να χρησιμοποιηθούν ως παράμετροι ταξινόμησης. Η ταξινόμηση χρησιμοποιώντας χρώματα κελιών και γραμματοσειρών θα αποδειχθεί αργότερα στο άρθρο. Σημειώστε επίσης ότι στο κελί Ε3, το όνομα του τμήματος είναι πεζά.
Δεν χρειάζεστε το εσωτερικό του κελιού και τα χρώματα γραμματοσειράς εάν δεν επιθυμείτε να χρησιμοποιήσετε τα παραδείγματα ταξινόμησης ανά κελί και χρώμα γραμματοσειράς.
Εγγραφή μακροεντολής για ταξινόμηση VBA
Ο κώδικας VBA για ταξινόμηση μπορεί να γίνει αρκετά περίπλοκος και μερικές φορές μπορεί να είναι καλή ιδέα να κάνετε την ταξινόμηση στο μπροστινό μέρος του Excel και να καταγράψετε μια μακροεντολή για να σας δείξει πώς λειτουργεί ο κώδικας.
Δυστυχώς, η λειτουργία εγγραφής μπορεί να δημιουργήσει τεράστιο όγκο κώδικα επειδή ορίζει σχεδόν κάθε διαθέσιμη παράμετρο, παρόλο που οι προεπιλεγμένες τιμές για πολλές παραμέτρους είναι αποδεκτές για τη λειτουργία ταξινόμησης.
Ωστόσο, σας δίνει μια πολύ καλή ιδέα για το τι περιλαμβάνει η συγγραφή κώδικα ταξινόμησης VBA και ένα πλεονέκτημα είναι ότι ο εγγεγραμμένος κώδικας θα λειτουργεί πάντα για εσάς. Ο δικός σας κώδικας μπορεί να χρειάζεται δοκιμή και εντοπισμό σφαλμάτων για να λειτουργήσει σωστά.
Θυμηθείτε ότι για μια λειτουργία που γίνεται στο VBA, δεν υπάρχει δυνατότητα αναίρεσης, οπότε είναι καλή ιδέα να δημιουργήσετε ένα αντίγραφο των δεδομένων των πινάκων σε άλλο φύλλο εργασίας πριν ξεκινήσετε να γράφετε τον κωδικό ταξινόμησης.
Για παράδειγμα, αν κάνατε μια απλή ταξινόμηση στα παραπάνω δείγματα δεδομένων, ταξινόμηση κατά Υπάλληλο, η εγγραφή θα δημιουργούσε τον ακόλουθο κώδικα:
| 123456789101112131415161718 | Υπο Macro1 ()Εύρος ("A1: E6"). ΕπιλέξτεActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _Ταξινόμηση: = xlSortOnValues, Σειρά: = xlΑύξουσα, DataOption: = xlSortNormalΜε ActiveWorkbook.Worksheets ("Sheet1"). Ταξινόμηση.SetRange Range ("A1: E6").Header = xlΝαι.MatchCase = Λάθος. Προσανατολισμός = xlTopToBottom.SortMethod = xlPinYin.ΙσχύουνΤέλος μεΤέλος υπο |
Αυτό είναι ένα μεγάλο κομμάτι κώδικα και πολλά από αυτά είναι περιττά λόγω των προεπιλεγμένων παραμέτρων που χρησιμοποιούνται. Ωστόσο, εάν είστε υπό πίεση χρόνου για να ολοκληρώσετε ένα έργο και χρειάζεστε γρήγορα κάποιον κώδικα που να λειτουργεί, μπορείτε εύκολα να τον επικολλήσετε στον δικό σας κώδικα VBA.
Ωστόσο, εάν θέλετε να κάνετε τον κωδικό σας κατανοητό και πιο κομψό, τότε υπάρχουν και άλλες διαθέσιμες επιλογές.
Κωδικός VBA για ταξινόμηση ενός επιπέδου
Εάν θέλετε να ταξινομήσετε το δείγμα κώδικα βάσει του Employee μόνο όπως πριν κατά την εγγραφή μιας μακροεντολής, ο κώδικας είναι πολύ απλός:
| 1234567 | Sub SingleLevelSort ()Φύλλα εργασίας ("Φύλλο1"). Ταξινόμηση.SortFields.ClearΕύρος ("A1: E6"). Ταξινόμηση κλειδιού1: = Εύρος ("A1"), Κεφαλίδα: = xlΝαιΤέλος υπο |
Αυτό είναι πολύ πιο κατανοητό από τον εγγεγραμμένο κώδικα επειδή δέχεται τις προεπιλογές, π.χ., ταξινόμηση αύξουσα, οπότε δεν χρειάζεται να ορίσετε τις παραμέτρους στις προεπιλεγμένες τιμές. Αυτό προϋποθέτει ότι έχετε χρησιμοποιήσει μια δήλωση «Εκκαθάριση» εκ των προτέρων.
Η μέθοδος «Εκκαθάριση» χρησιμοποιείται αρχικά για να διασφαλιστεί ότι κάθε παράμετρος ταξινόμησης για αυτό το φύλλο εργασίας επαναφέρεται στις προεπιλεγμένες τιμές. Ένας χρήστης μπορεί να έχει ρυθμίσει προηγουμένως τις παραμέτρους σε διαφορετικές τιμές ή ένα προηγούμενο είδος στο VBA να τις έχει αλλάξει. Είναι σημαντικό να ξεκινήσετε από μια προεπιλεγμένη θέση κατά την ταξινόμηση, διαφορετικά θα μπορούσατε εύκολα να καταλήξετε σε λανθασμένα αποτελέσματα.
Η μέθοδος Εκκαθάριση δεν επαναφέρει την παράμετρο της κεφαλίδας και είναι σκόπιμο να το συμπεριλάβετε στον κώδικά σας, διαφορετικά το Excel μπορεί να δοκιμάσει και να μαντέψει εάν υπάρχει ή όχι μια γραμμή κεφαλίδας.
Εκτελέστε αυτόν τον κώδικα με τα δείγματα δεδομένων και το φύλλο εργασίας σας θα μοιάζει με αυτό:
Κωδικός VBA για ταξινόμηση πολλαπλών επιπέδων
Μπορείτε να προσθέσετε όσα επίπεδα ταξινόμησης απαιτούνται στον κώδικά σας. Ας υποθέσουμε ότι θέλατε να ταξινομήσετε πρώτα ανά τμήμα και μετά κατά ημερομηνία έναρξης αλλά με αύξουσα σειρά για το τμήμα και φθίνουσα σειρά για ημερομηνία έναρξης:
| 12345678 | Sub MultiLevelSort ()Φύλλα εργασίας ("Φύλλο1"). Ταξινόμηση.SortFields.ClearΕύρος ("A1: E6"). Ταξινόμηση κλειδιού1: = Εύρος ("E1"), Κλειδί2: = Εύρος ("C1"), Κεφαλίδα: = xlΝαι, _Ταξινόμηση 1: = xlΑύξουσα, τάξη2: = xlΚατευθυνόμενηΤέλος υπο |
Σημειώστε ότι υπάρχουν τώρα δύο κλειδιά στη δήλωση ταξινόμησης (Key1 και Key2). Το Key1 (στήλη E τμήματος) ταξινομείται πρώτα απ 'όλα και στη συνέχεια το Key2 (Ημερομηνία έναρξης στήλη C) ταξινομείται με βάση την πρώτη ταξινόμηση.
Υπάρχουν επίσης δύο παράμετροι παραγγελίας. Το Order1 συσχετίζεται με το Key1 (Τμήμα) και το Order2 συσχετίζει με το Key2 (Ημερομηνία έναρξης). Είναι σημαντικό να διασφαλίσετε ότι τα κλειδιά και οι παραγγελίες τηρούνται μεταξύ τους.
Εκτελέστε αυτόν τον κώδικα με τα δείγματα δεδομένων και το φύλλο εργασίας σας θα μοιάζει με αυτό:
Η στήλη Τμήμα (Ε) είναι σε αύξουσα σειρά και η στήλη Ημερομηνία έναρξης (Γ) σε φθίνουσα σειρά.
Η επίδραση αυτού του είδους είναι πιο αισθητή όταν κοιτάζουμε τις Jane Halfacre (σειρά 3) και John Sutherland (σειρά 4). Είναι και οι δύο στα Οικονομικά αλλά η Τζέιν Χάλφακρ ξεκίνησε πριν από τον Τζον Σάδερλαντ και οι ημερομηνίες εμφανίζονται σε φθίνουσα σειρά.
Εάν το εύρος των δεδομένων πίνακα μπορεί να έχει οποιοδήποτε μήκος, μπορείτε να χρησιμοποιήσετε το αντικείμενο UsedRange για να ορίσετε το εύρος ταξινόμησης. Αυτό θα λειτουργήσει μόνο εάν υπάρχουν μόνο τα δεδομένα πίνακα στο φύλλο εργασίας, καθώς τυχόν τιμές εκτός των δεδομένων θα δώσουν εσφαλμένα αποτελέσματα για τον αριθμό των γραμμών και των στηλών.
| 1234567 | Sub MultiLevelSort ()Φύλλα εργασίας ("Φύλλο1"). Ταξινόμηση.SortFields.ClearΦύλλα εργασίας ("Φύλλο1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlΝαι, _Ταξινόμηση 1: = xlΑύξουσα, τάξη2: = xlΚατευθυνόμενηΤέλος υπο |
Αυτό αποτρέπει το πρόβλημα εάν χρησιμοποιείτε τη μέθοδο "Τέλος (xlDown)" για να καθορίσετε το εύρος ταξινόμησης. Εάν υπάρχει ένα κενό κελί στη μέση των δεδομένων, τότε οτιδήποτε μετά το κενό κελί δεν θα συμπεριληφθεί, ενώ το UsedRange κατεβαίνει στο τελευταίο ενεργό κελί στο φύλλο εργασίας.
Ταξινόμηση κατά χρώμα κυψέλης
Από το Excel 2007, είναι πλέον δυνατή η ταξινόμηση με βάση το χρώμα φόντου ενός κελιού, η οποία παρέχει τεράστια ευελιξία κατά το σχεδιασμό του κώδικα ταξινόμησης σε VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Φύλλα εργασίας ("Φύλλο1"). Ταξινόμηση.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _Ταξινόμηση: = xlSortOnCellColor, σειρά: = xlΑύξουσα, DataOption: = xlSortNormalΜε ActiveWorkbook.Worksheets ("Sheet1"). Ταξινόμηση.SetRange Range ("A1: E6").ΙσχύουνΤέλος μεΤέλος υπο |
Αυτός ο κωδικός θα ταξινομήσει το δείγμα εύρους δεδομένων (A2: A6) με βάση το χρώμα φόντου του κελιού. Σημειώστε ότι υπάρχει τώρα μια πρόσθετη παράμετρος που ονομάζεται "SortOn" η οποία έχει την τιμή "xlSortOnCellColor".
Σημειώστε ότι η παράμετρος "Ταξινόμηση" μπορεί να χρησιμοποιηθεί μόνο από ένα αντικείμενο φύλλου εργασίας και όχι από ένα αντικείμενο εύρους.
Εξαιτίας αυτού, ο κώδικας είναι πιο περίπλοκος από ό, τι για ένα είδος που χρησιμοποιεί τιμές κελιού.
Αυτός ο κώδικας χρησιμοποιεί μια τιμή κλειδιού για την ταξινόμηση που καλύπτει όλο το εύρος δεδομένων, αλλά μπορείτε να καθορίσετε μεμονωμένες στήλες ως κλειδί για την ταξινόμηση χρώματος φόντου και να χρησιμοποιήσετε πολλαπλά επίπεδα όπως φαίνεται νωρίτερα.
Μετά την εκτέλεση αυτού του κώδικα, το φύλλο εργασίας σας θα μοιάζει τώρα με αυτό:
Ταξινόμηση κατά χρώμα γραμματοσειράς
Η λειτουργία ταξινόμησης στο Excel VBA προσφέρει ακόμη μεγαλύτερη ευελιξία στο ότι μπορείτε να ταξινομήσετε κατά χρώματα γραμματοσειράς:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Φύλλα εργασίας ("Φύλλο1"). Ταξινόμηση.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Με ActiveWorkbook.Worksheets ("Sheet1"). Ταξινόμηση.SetRange Range ("A1: E6").Header = xlΝαι. Προσανατολισμός = xlTopToBottom.ΙσχύουνΤέλος μεΤέλος υπο |
Ο κώδικας για την ταξινόμηση κατά χρώμα γραμματοσειράς είναι πολύ πιο περίπλοκος από ό, τι για το χρώμα φόντου του κελιού. Η παράμετρος 'SortOn' διατηρεί τώρα την τιμή του 'xlSortOnFontColor'.
Σημειώστε ότι πρέπει να καθορίσετε τον προσανατολισμό ως «xlTopToBottom» και πρέπει να καθορίσετε ένα χρώμα για ταξινόμηση. Αυτό καθορίζεται με όρους RGB (Κόκκινο, Πράσινο, Μαύρο) με τιμές που κυμαίνονται από 0 έως 255.
Αφού εκτελέσετε αυτόν τον κώδικα με τα δείγματα δεδομένων, το φύλλο εργασίας σας θα μοιάζει τώρα με αυτό:
Η ταξινόμηση με χρήση χρωμάτων στο VBA είναι πολύ πιο περίπλοκη από μια ταξινόμηση πολλαπλών επιπέδων, αλλά εάν ο κωδικός ταξινόμησής σας δεν θα λειτουργήσει (κάτι που μπορεί να συμβεί εάν λείπει μια παράμετρος ή δεν έχετε εισαγάγει σωστά τον κώδικα), τότε μπορείτε πάντα να επιστρέψετε στην εγγραφή μια μακροεντολή και ενσωμάτωση του εγγεγραμμένου κώδικα στο VBA σας.
Χρήση άλλων παραμέτρων στη ταξινόμηση VBA
Υπάρχει μια σειρά προαιρετικών παραμέτρων που μπορείτε να χρησιμοποιήσετε στον κώδικα VBA για να προσαρμόσετε την ταξινόμησή σας.
Ταξινόμηση
Το SortOn επιλέγει εάν η ταξινόμηση θα χρησιμοποιεί τιμές κελιών, χρώματα φόντου κελιών ή χρώματα γραμματοσειράς κελιού. Η προεπιλεγμένη ρύθμιση είναι Τιμές κελιού.
| 1 | Ταξινόμηση = xlSortOnValues |
Σειρά
Η σειρά επιλέγει αν η ταξινόμηση θα γίνει με αύξουσα ή φθίνουσα σειρά. Η προεπιλογή είναι Αύξουσα.
| 1 | Τάξη = xlΑύξουσα |
DataOption
Το DataOption επιλέγει τον τρόπο ταξινόμησης κειμένου και αριθμών. Η παράμετρος xlSortNormal ταξινομεί τα αριθμητικά και τα δεδομένα κειμένου ξεχωριστά. Η παράμετρος xlSortTextAsNumbers αντιμετωπίζει το κείμενο ως αριθμητικά δεδομένα για την ταξινόμηση. Η προεπιλογή είναι xlSortNormal.
| 1 | DataOption = xlSortNormal |
Επί κεφαλής
Η κεφαλίδα επιλέγει εάν το εύρος δεδομένων του πίνακα έχει μια γραμμή κεφαλίδας ή όχι. Εάν υπάρχει μια γραμμή κεφαλίδας, δεν θέλετε αυτή να συμπεριληφθεί στην ταξινόμηση.
Οι τιμές παραμέτρων είναι xlYes, xlNo και xlYesNoGuess. Το xlYesNoGuess αφήνει στο Excel να καθορίσει εάν υπάρχει μια γραμμή κεφαλίδας, η οποία θα μπορούσε εύκολα να οδηγήσει σε ασυνεπή αποτελέσματα. Δεν συνιστάται η χρήση αυτής της τιμής.
Η προεπιλεγμένη τιμή είναι XNo (καμία γραμμή κεφαλίδας μέσα στα δεδομένα). Με τα εισαγόμενα δεδομένα, υπάρχει συνήθως μια γραμμή κεφαλίδας, οπότε βεβαιωθείτε ότι έχετε ορίσει αυτήν την παράμετρο σε xl Ναι.
| 1 | Κεφαλίδα = xlΝαι |
MatchCase
Αυτή η παράμετρος καθορίζει εάν η ταξινόμηση είναι διάκριση πεζών-κεφαλαίων ή όχι. Οι τιμές επιλογής είναι σωστές ή λανθασμένες. Εάν η τιμή είναι False, τότε οι πεζές τιμές θεωρούνται ίδιες με τις τιμές κεφαλαίων. Εάν η τιμή είναι True, τότε η ταξινόμηση θα δείξει τη διαφορά μεταξύ κεφαλαίων και κεφαλαίων τιμών εντός της ταξινόμησης. Η προεπιλεγμένη τιμή είναι ψευδής.
| 1 | MatchCase = Λάθος |
Προσανατολισμός
Αυτή η παράμετρος καθορίζει εάν η ταξινόμηση θα πραγματοποιηθεί προς τα κάτω στις γραμμές ή σε όλες τις στήλες. Η προεπιλεγμένη τιμή είναι xlTopToBottom (ταξινόμηση μεταξύ γραμμών). Μπορείτε να χρησιμοποιήσετε το xlLeftToRight εάν θέλετε να ταξινομήσετε οριζόντια. Τιμές όπως xlRows και xlColumns δεν λειτουργούν για αυτήν την παράμετρο.
| 1 | Προσανατολισμός = xlTopToBottom |
ΤαξινόμησηΜέθοδος
Αυτή η παράμετρος χρησιμοποιείται μόνο για την ταξινόμηση των κινεζικών γλωσσών. Έχει δύο τιμές, xlPinYin και xlStroke. Το xlPinYin είναι η προεπιλεγμένη τιμή.
Το xlPinYin ταξινομεί χρησιμοποιώντας τη φωνητική σειρά κινεζικής ταξινόμησης για χαρακτήρες. Το xlStroke ταξινομεί κατά την ποσότητα των εγκεφαλικών επεισοδίων σε κάθε χαρακτήρα.
Εάν καταγράψετε μια μακροεντολή ταξινόμησης, αυτή η παράμετρος θα περιλαμβάνεται πάντα στον κώδικα και ίσως αναρωτηθήκατε τι σήμαινε. Ωστόσο, εκτός εάν ασχολείστε με δεδομένα στα κινέζικα, είναι ελάχιστης χρήσης.
| 1 | SortMethod = xlPinYin |
Χρήση συμβάντος διπλού κλικ για ταξινόμηση δεδομένων πίνακα
Σε όλη τη λειτουργικότητα που συμπεριέλαβε η Microsoft στις μεθόδους ταξινόμησης για VBA, δεν περιλάμβανε ένα απλό μέσο για να κάνετε διπλό κλικ σε μια κεφαλίδα στήλης και να ταξινομήσετε το σύνολο των δεδομένων πίνακα με βάση τη συγκεκριμένη στήλη.
Αυτό είναι ένα πραγματικά χρήσιμο χαρακτηριστικό και είναι εύκολο να γράψετε τον κώδικα για να το κάνετε.
| 12345678910111213141516171819202122232425262728293031323334 | Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)«Υποτίθεται ότι τα δεδομένα ξεκινούν από το κελί Α1«Δημιουργήστε τρεις μεταβλητές για να συλλάβετε την επιλεγμένη στήλη στόχου και τη μέγιστη στήλη και σειρά του _«τα πίνακα δεδομέναDim Col As Integer, RCol As Long, RRow As Long«Βεβαιωθείτε ότι ο χρήστης έχει κάνει διπλό κλικ στη γραμμή κεφαλίδας - η σειρά 1 εξέρχεται διαφορετικά από την υποΕάν Target.Row 1 Στη συνέχεια, κάντε έξοδο από το Sub"Καταγράψτε τις μέγιστες σειρές στο εύρος δεδομένων πίνακα χρησιμοποιώντας το αντικείμενο" UsedRange "RCol = ActiveSheet.UsedRange.Columns.Count"Καταγράψτε τις μέγιστες στήλες στο εύρος δεδομένων πίνακα χρησιμοποιώντας το αντικείμενο" UsedRange "RRow = ActiveSheet.UsedRange.Rows.Count«Βεβαιωθείτε ότι ο χρήστης δεν έχει κάνει διπλό κλικ σε μια στήλη έξω από το εύρος δεδομένων του πίνακαIf Target.Column> RCol Τότε Έξοδος από Sub'Αποτυπώστε τη στήλη στην οποία έχει κάνει διπλό κλικ ο χρήστηςCol = Target.Column«Διαγράψτε τις προηγούμενες παραμέτρους ταξινόμησηςActiveSheet.Sort.SortFields.Clear"Ταξινόμηση της περιοχής πίνακα όπως ορίζεται από τις μέγιστες γραμμές και στήλες από το αντικείμενο" UsedRange "«Ταξινομήστε τα δεδομένα του πίνακα χρησιμοποιώντας τη στήλη με διπλό κλικ από τον χρήστη ως κλειδί ταξινόμησηςActiveSheet.Range (Κελιά (1, 1), Κελιά (RCol, RRow)). Κλειδί ταξινόμησης 1: = Κελιά (1, Col), Κεφαλίδα: = xlΝαι«Επιλέξτε κελί Α1 - αυτό για να διασφαλίσετε ότι ο χρήστης δεν θα μείνει σε κατάσταση επεξεργασίας μετά την ταξινόμηση _'ολοκληρώθηκεActiveSheet.Range ("A1"). ΕπιλέξτεΤέλος υπο |
Αυτός ο κωδικός πρέπει να τοποθετηθεί στο συμβάν διπλού κλικ στο φύλλο που περιέχει τα δεδομένα πίνακα. Μπορείτε να το κάνετε αυτό κάνοντας κλικ στο όνομα του φύλλου εργασίας στο παράθυρο Project Explorer (επάνω αριστερή γωνία της οθόνης VBE) και, στη συνέχεια, επιλέγοντας «Φύλλο εργασίας» στο πρώτο αναπτυσσόμενο μενού στο παράθυρο κωδικού. Επιλέξτε "BeforeDoubleClick" στο δεύτερο αναπτυσσόμενο μενού και, στη συνέχεια, μπορείτε να εισαγάγετε τον κωδικό σας.
Σημειώστε ότι κανένα όνομα, εύρος ή αναφορές κελιών δεν είναι κωδικοποιημένα σε αυτόν τον κώδικα εκτός από τη μετακίνηση του δρομέα στο κελί Α1 στο τέλος του κώδικα. Ο κώδικας έχει σχεδιαστεί για να λαμβάνει όλες τις πληροφορίες που απαιτούνται από τις συντεταγμένες κελιού στις οποίες έχει κάνει διπλό κλικ ο χρήστης και το μέγεθος του εύρους δεδομένων πίνακα.
Δεν έχει σημασία πόσο μεγάλο είναι το εύρος δεδομένων πίνακα. Ο κώδικας θα συνεχίσει να συγκεντρώνει όλες τις απαιτούμενες πληροφορίες και μπορεί να χρησιμοποιηθεί σε δεδομένα που βρίσκονται οπουδήποτε μέσα στο βιβλίο εργασίας σας χωρίς να χρειάζεται να πραγματοποιήσετε σκληρούς κωδικούς σε τιμές.
Η μόνη υπόθεση που γίνεται είναι ότι υπάρχει μια γραμμή κεφαλίδας στα δεδομένα του πίνακα και ότι το εύρος δεδομένων ξεκινά από το κελί Α1, αλλά η αρχική θέση για το εύρος δεδομένων μπορεί εύκολα να αλλάξει μέσα στον κώδικα.
Οποιοσδήποτε χρήστης θα εντυπωσιαστεί κατάλληλα με αυτή τη νέα λειτουργία ταξινόμησης!
Επέκταση της λειτουργίας ταξινόμησης με χρήση VBA
Η Microsoft επέτρεψε τεράστια ευελιξία στην ταξινόμηση χρησιμοποιώντας ένα ευρύ φάσμα παραμέτρων. Ωστόσο, στο VBA, μπορείτε να το προχωρήσετε περαιτέρω.
Ας υποθέσουμε ότι θέλετε να ταξινομήσετε τυχόν τιμές με έντονη γραμματοσειρά στο πάνω μέρος των δεδομένων σας. Δεν υπάρχει τρόπος να το κάνετε αυτό στο Excel, αλλά μπορείτε να γράψετε τον κωδικό VBA για να το κάνετε:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()«Δημιουργήστε μεταβλητές για να κρατήσετε τον αριθμό των γραμμών και στηλών για τα δεδομένα των πινάκωνDim RRow As Long, RCol As Long, N As Long«Απενεργοποιήστε την ενημέρωση οθόνης, έτσι ώστε ο χρήστης να μην μπορεί να δει τι συμβαίνει - μπορεί να δει _«οι αξίες αλλάζουν και αναρωτιέμαι γιατίApplication.ScreenUpdating = Λάθος«Καταγράψτε τον αριθμό των στηλών στο εύρος δεδομένων πίνακαRCol = ActiveSheet.UsedRange.Columns.Count«Αποτυπώστε τον αριθμό των γραμμών εντός του εύρους δεδομένων των πινάκωνRRow = ActiveSheet.UsedRange.Rows.Count«Επαναλάβετε όλες τις γραμμές στο εύρος δεδομένων πίνακα αγνοώντας τη σειρά κεφαλίδαςΓια N = 2 To RRow"Εάν ένα κελί έχει μια έντονη γραμματοσειρά, τότε τοποθετήστε μια κορυφαία τιμή 0 έναντι της τιμής του κελιούΑν ActiveSheet.Cells (N, 1) .Font.Bold = True ΤότεActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueΤέλος εανΕπόμενο Ν«Διαγράψτε τυχόν προηγούμενες παραμέτρους ταξινόμησηςActiveSheet.Sort.SortFields.Clear«Ταξινόμηση του εύρους δεδομένων πίνακα. Όλες οι τιμές με κορυφαία τιμή 0 θα μετακινηθούν στην κορυφήActiveSheet.Range (Κελιά (1, 1), Κελιά (RCol, RRow)). Κλειδί ταξινόμησης 1: = Κελιά (1, 1), Κεφαλίδα: = xlΝαι«Επαναλάβετε όλες τις γραμμές στο εύρος δεδομένων πίνακα αγνοώντας τη σειρά κεφαλίδαςΓια N = 2 To RRow"Εάν ένα κελί έχει έντονη γραμματοσειρά, αφαιρέστε την τιμή 0 από την τιμή κελιού στο _"επαναφορά των αρχικών τιμώνΑν ActiveSheet.Cells (N, 1) .Font.Bold = True ΤότεActiveSheet.Cells (N, 1). Value = Mid (ActiveSheet.Cells (N, 1). Value, 2)Τέλος εανΕπόμενο Ν«Ενεργοποιήστε ξανά την ενημέρωση οθόνηςApplication.ScreenUpdating = TrueΤέλος υπο |
Ο κώδικας επεξεργάζεται το μέγεθος του εύρους δεδομένων του πίνακα χρησιμοποιώντας το αντικείμενο "UsedRange" και στη συνέχεια επαναλαμβάνεται σε όλες τις γραμμές μέσα σε αυτό. Όταν βρεθεί μια έντονη γραμματοσειρά, ένα μηδενικό σημείο τοποθετείται μπροστά από την τιμή του κελιού.
Στη συνέχεια λαμβάνει χώρα μια ταξινόμηση. Καθώς η ταξινόμηση είναι σε αύξουσα σειρά, οτιδήποτε έχει ένα μηδέν μπροστά θα πάει στην κορυφή της λίστας.
Στη συνέχεια, ο κώδικας επαναλαμβάνεται σε όλες τις γραμμές και αφαιρεί τα μηδενικά που οδηγούν, επαναφέροντας τα δεδομένα στις αρχικές του τιμές.
Αυτός ο κώδικας ταξινομεί τη χρήση έντονων γραμματοσειρών ως κριτηρίου, αλλά μπορείτε εύκολα να χρησιμοποιήσετε άλλα χαρακτηριστικά κελιού με τον ίδιο τρόπο, π.χ. πλάγια γραμματοσειρά, μέγεθος σημείου κειμένου, γραμματοσειρά υπογράμμισης, όνομα γραμματοσειράς κ.λπ.
