Το Excel έχει ήδη δύο τρόπους για γρήγορη ταξινόμηση δεδομένων.
Μπορείτε εύκολα να ταξινομήσετε ένα σύνολο δεδομένων χρησιμοποιώντας τα εικονίδια ταξινόμησης στην κορδέλα ή το παράθυρο διαλόγου ταξινόμησης.
Τότε γιατί πρέπει να ξέρετε πώς να το κάνετε αυτό χρησιμοποιώντας το VBA;
Η γνώση του τρόπου ταξινόμησης δεδομένων χρησιμοποιώντας το VBA μπορεί να είναι χρήσιμη όταν περιλαμβάνεται ως μέρος του κώδικα σας. Για παράδειγμα, ας υποθέσουμε ότι λαμβάνετε ένα σύνολο δεδομένων καθημερινά/εβδομαδιαίως που πρέπει να μορφοποιήσετε και να ταξινομήσετε με μια συγκεκριμένη σειρά.
Μπορείτε να δημιουργήσετε μια μακροεντολή για να τα κάνετε όλα αυτά για εσάς με ένα μόνο κλικ. Αυτό θα σας εξοικονομήσει πολύ χρόνο και προσπάθεια κάθε φορά που το κάνετε.
Επίσης, εάν δημιουργείτε πίνακες εργαλείων του Excel, μπορείτε να ανεβάσετε τη δυνατότητα ταξινόμησης του Excel σε νέο επίπεδο επιτρέποντας στον χρήστη να ταξινομήσει τα δεδομένα απλά κάνοντας διπλό κλικ στην κεφαλίδα (όπως φαίνεται παρακάτω).
Θα καλύψω πώς να το δημιουργήσω αργότερα σε αυτό το σεμινάριο. Ας πάρουμε πρώτα γρήγορα τα βασικά.
Κατανόηση του εύρους. Μέθοδος ταξινόμησης στο Excel VBA
Κατά την ταξινόμηση χρησιμοποιώντας VBA, πρέπει να χρησιμοποιήσετε τη μέθοδο Range.Sort στον κώδικά σας.
Το "εύρος" θα είναι τα δεδομένα που προσπαθείτε να ταξινομήσετε. Για παράδειγμα, εάν ταξινομείτε τα δεδομένα στο A1: A10, τότε το "Range" θα είναι Range ("A1: A10").
Μπορείτε επίσης να δημιουργήσετε μια ονομαστική περιοχή και να τη χρησιμοποιήσετε αντί για τις αναφορές κελιών. Για παράδειγμα, εάν δημιουργήσω μια ονομαστική περιοχή "DataRange" για τα κελιά A1: A10, τότε μπορώ επίσης να χρησιμοποιήσω το Range ("DataRange")
Με τη μέθοδο ταξινόμησης, πρέπει να παρέχετε κάποιες πρόσθετες πληροφορίες μέσω παραμέτρων. Παρακάτω είναι οι βασικές παράμετροι που πρέπει να γνωρίζετε:
- Κλειδί - εδώ πρέπει να καθορίσετε τη στήλη που θέλετε να ταξινομήσετε. Για παράδειγμα, εάν θέλετε να ταξινομήσετε τη στήλη Α, πρέπει να χρησιμοποιήσετε το κλειδί: = Εύρος ("A1")
- Σειρά - εδώ καθορίζετε αν θέλετε η ταξινόμηση με αύξουσα ή φθίνουσα σειρά. Για παράδειγμα, εάν θέλετε η ταξινόμηση κατά αύξουσα σειρά, θα χρησιμοποιήσετε την τάξη: = xlΑύξουσα
- Επί κεφαλής - εδώ καθορίζετε αν το σύνολο δεδομένων σας έχει κεφαλίδες ή όχι. Εάν έχει κεφαλίδες, η ταξινόμηση ξεκινά από τη δεύτερη σειρά του συνόλου δεδομένων, διαφορετικά ξεκινά από την πρώτη σειρά. Για να καθορίσετε ότι τα δεδομένα σας έχουν κεφαλίδες, θα χρησιμοποιήσετε την κεφαλίδα: = xlΝαι
Ενώ αυτά τα τρία αρκούν στις περισσότερες περιπτώσεις, μπορείτε να διαβάσετε περισσότερα για τις παραμέτρους σε αυτό το άρθρο.
Τώρα ας δούμε πώς να χρησιμοποιήσετε τη μέθοδο Range.Sort σε VBA για να ταξινομήσετε δεδομένα στο Excel.
Ταξινόμηση μίας στήλης χωρίς κεφαλίδα
Ας υποθέσουμε ότι έχετε μία μόνο στήλη χωρίς κεφαλίδα (όπως φαίνεται παρακάτω).
Μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα για να τον ταξινομήσετε με αύξουσα σειρά.
Sub SortDataWithoutHeader () Range ("A1: A12"). Sort Key1: = Range ("A1"), Order1: = xlΑύξουσα, κεφαλίδα: = xlΌχι Τέλος υπο
Σημειώστε ότι έχω καθορίσει το εύρος δεδομένων χειροκίνητα ως Εύρος ("A1: A12").
Σε περίπτωση που ενδέχεται να υπάρξουν αλλαγές στα δεδομένα και να προστεθούν/διαγραφούν τιμές, μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα που προσαρμόζεται αυτόματα με βάση τα συμπληρωμένα κελιά στο σύνολο δεδομένων.
Sub SortDataWithoutHeader () Εύρος ("A1", Range ("A1"). Τέλος (xlDown)). Κλειδί ταξινόμησης 1: = Εύρος ("A1"), σειρά 1: = xlΑύξουσα, κεφαλίδα: = xlΌχι Τέλος υπο
Σημειώστε ότι αντί για Εύρος ("A1: A12"), έχω χρησιμοποιήσει το Range ("A1", Range ("A1"). Τέλος (xlDown)).
Αυτό θα ελέγξει το τελευταίο διαδοχικά συμπληρωμένο κελί στη στήλη και θα το συμπεριλάβει στη διαλογή. Σε περίπτωση που υπάρχουν κενά, θα λάβει υπόψη μόνο τα δεδομένα μέχρι το πρώτο κενό κελί.
Μπορείτε επίσης να δημιουργήσετε μια ονομαστική περιοχή και να χρησιμοποιήσετε αυτήν την ονομαστική περιοχή αντί για τις αναφορές κελιών. Για παράδειγμα, εάν το εύρος που ονομάζεται είναι DataSet, ο κωδικός σας θα είναι τώρα όπως φαίνεται παρακάτω.
Sub SortDataWithoutHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlΑύξουσα, Κεφαλίδα: = xlNo End Sub
Τώρα επιτρέψτε μου να εξηγήσω γρήγορα τις παραμέτρους που χρησιμοποιούνται στα παραπάνω παραδείγματα:
- Key1: = Range ("A1") - Καθορισμένο A1 έτσι ώστε ο κώδικας να γνωρίζει ποια στήλη θα ταξινομήσει.
- Σειρά 1: = xlAscending - Καθορίστηκε η σειρά ως xlAscending. Εάν θέλετε να είναι σε φθίνουσα σειρά, χρησιμοποιήστε xlDescending.
- Κεφαλίδα: = xlNo - Καθορίζεται ότι δεν υπάρχουν κεφαλίδες. Αυτή είναι επίσης η προεπιλεγμένη τιμή. Έτσι, ακόμη και αν το παραλείψετε, τα δεδομένα σας θα ταξινομηθούν λαμβάνοντας υπόψη ότι δεν έχουν κεφαλίδες.
Αναρωτιέστε πού να βάλετε αυτόν τον κώδικα VBA και πώς να εκτελέσετε τη μακροεντολή; Διαβάστε αυτό το σεμινάριο!
Ταξινόμηση μίας στήλης με κεφαλίδα
Στο προηγούμενο παράδειγμα, το σύνολο δεδομένων δεν είχε κεφαλίδα.
Όταν τα δεδομένα σας έχουν κεφαλίδες, πρέπει να το προσδιορίσετε στον κώδικα, έτσι ώστε η ταξινόμηση να ξεκινήσει από τη δεύτερη σειρά του συνόλου δεδομένων.
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:
Παρακάτω είναι ο κώδικας που θα ταξινομήσει τα δεδομένα σε φθίνουσα σειρά με βάση τις πωλήσεις των καταστημάτων.
Sub SortDataWithHeader () Range ("DataRange"). Sort Key1: = Range ("C1"), Order1: = xlDescending End Sub
Σημειώστε ότι έχω δημιουργήσει μια ονομαστική περιοχή - "DataRange" και χρησιμοποίησα αυτό το όνομα περιοχής στον κώδικα.
Ταξινόμηση πολλαπλών στηλών με κεφαλίδες
Μέχρι τώρα σε αυτό το σεμινάριο, έχουμε δει πώς να ταξινομείτε μια μόνο στήλη (με και χωρίς κεφαλίδες).
Τώρα, τι γίνεται αν θέλετε να ταξινομήσετε με βάση πολλές στήλες.
Για παράδειγμα, στο παρακάτω σύνολο δεδομένων, τι γίνεται αν θέλω πρώτα να ταξινομήσω κατά τον κωδικό κατάστασης και στη συνέχεια κατά το κατάστημα.
Εδώ είναι ο κώδικας που θα ταξινομήσει πολλές στήλες με τη μία.
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlΝαι. Εφαρμογή λήξης με τέλος υπο
Παρακάτω είναι το αποτέλεσμα που θα έχετε.
Στο παραπάνω παράδειγμα, τα δεδομένα ταξινομούνται πρώτα με τον κωδικό κατάστασης (στήλη Α). Στη συνέχεια, στα δεδομένα του κωδικού κατάστασης, ταξινομείται ξανά κατά Store (Στήλη Β). Αυτή η σειρά καθορίζεται από τον κωδικό στον οποίο την αναφέρετε.
Ταξινόμηση δεδομένων χρησιμοποιώντας διπλό κλικ στην κεφαλίδα
Εάν δημιουργείτε έναν πίνακα εργαλείων ή θέλετε μεγαλύτερη ευκολία στη χρήση των αναφορών σας, μπορείτε να γράψετε έναν κωδικό VBA που θα ταξινομεί τα δεδομένα όταν κάνετε διπλό κλικ στις κεφαλίδες.
Κάτι όπως φαίνεται παρακάτω:
Παρακάτω είναι ο κώδικας που θα σας επιτρέψει να το κάνετε αυτό:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Collumn <= ColumnCount Στη συνέχεια Cancel = True Set KeyRange = Range (Target.Address) Range ("DataRange"). Ταξινόμηση κλειδιού1: = KeyRange, Header: = xlΝαι τέλος αν τελειώσει
Σημειώστε ότι έχω δημιουργήσει μια ονομαστική περιοχή ("DataRange") και την έχω χρησιμοποιήσει στον κώδικα αντί να χρησιμοποιήσω τις αναφορές κελιών.
Μόλις κάνετε διπλό κλικ σε οποιαδήποτε από τις κεφαλίδες, ο κώδικας απενεργοποιεί τη συνήθη λειτουργία διπλού κλικ (που είναι να μπείτε στη λειτουργία επεξεργασίας) και χρησιμοποιεί αυτό το κελί ως κλειδί κατά την ταξινόμηση των δεδομένων.
Σημειώστε επίσης ότι από τώρα, αυτός ο κώδικας θα ταξινομήσει όλες τις στήλες μόνο σε αύξουσα σειρά.
Σημειώστε ότι το διπλό κλικ είναι ένας κανόνας ενεργοποίησης που επιτρέπει στο Excel να εκτελέσει τον καθορισμένο κώδικα. Αυτές οι ενεργοποιήσεις όπως διπλό κλικ, άνοιγμα βιβλίου εργασίας, προσθήκη νέου φύλλου εργασίας, αλλαγή κελιού κ.λπ. ονομάζονται συμβάντα και μπορούν να χρησιμοποιηθούν για την εκτέλεση μακροεντολών στο Excel. Μπορείτε να διαβάσετε περισσότερα για τις εκδηλώσεις Excel VBA εδώ.Πού να βάλω αυτόν τον κωδικό;
Πρέπει να επικολλήσετε αυτόν τον κώδικα στο παράθυρο κώδικα του φύλλου στο οποίο θέλετε αυτή τη λειτουργία ταξινόμησης διπλού κλικ.
Για να το κάνω αυτό:
- Κάντε δεξί κλικ στην καρτέλα φύλλου.
- Κάντε κλικ στην επιλογή Προβολή κώδικα.
- Επικολλήστε τον κωδικό στο παράθυρο κωδικού του φύλλου στο οποίο βρίσκονται τα δεδομένα σας.
Τώρα τι γίνεται αν θέλετε να ταξινομήσετε τις δύο πρώτες στήλες («Κατάσταση» και «Αποθήκευση») σε αύξουσα σειρά, αλλά στη στήλη «Πωλήσεις» σε φθίνουσα σειρά.
Εδώ είναι ο κώδικας που θα το κάνει:
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Collumn <= ColumnCount Στη συνέχεια Cancel = True Set KeyRange = Range (Target.Address) If Target.Value = "Sales" Στη συνέχεια SortOrder = xlDescending Else SortOrder = xlAscending End If Range ("DataRange"). Ταξινόμηση κλειδιού1: = KeyRange, Header: = xlΝαι, Order1: = SortOrder End If End Sub
Στον παραπάνω κώδικα, ελέγχει αν το κελί στο οποίο γίνεται διπλό κλικ είναι η κεφαλίδα πωλήσεων ή όχι. Εάν ναι, τότε εκχωρεί την τιμή xlDescending στη μεταβλητή SortOrder, αλλιώς την κάνει xlAscending.
Τώρα ας πάμε αυτό ένα επίπεδο και να δείξουμε έναν οπτικό δείκτη (βέλος και έγχρωμο κελί) στην κεφαλίδα όταν είναι ταξινομημένο.
Κάτι όπως φαίνεται παρακάτω:
Για να το αποκτήσω, πρόσθεσα ένα νέο φύλλο εργασίας και έκανα τις ακόλουθες αλλαγές σε αυτό (μπορείτε να κατεβάσετε το παράδειγμα του αρχείου και να το ακολουθήσετε):
- Άλλαξε το όνομα του νέου φύλλου σε "BackEnd".
- Στο κελί Β2, εισαγάγετε ένα σύμβολο βέλους (για να το κάνετε αυτό, μεταβείτε στην επιλογή Εισαγωγή και κάντε κλικ στην επιλογή "Σύμβολο").
- Αντιγράψτε και επικολλήστε τις κεφαλίδες από το σύνολο δεδομένων στο κελί A3: C3 στο φύλλο "Backend".
- Χρησιμοποιήστε την ακόλουθη συνάρτηση στο κελί A4: AC4:
= IF (A3 = $ C $ 1, A3 & "" & $ B $ 1, A3)
- Τα υπόλοιπα κελιά θα γεμίσουν αυτόματα από τον κωδικό VBA όταν κάνετε διπλό κλικ στις κεφαλίδες για να ταξινομήσετε τη στήλη.
Το φύλλο backend θα μοιάζει με αυτό που φαίνεται παρακάτω:
Τώρα μπορείτε να χρησιμοποιήσετε τον παρακάτω κώδικα για να ταξινομήσετε τα δεδομένα κάνοντας διπλό κλικ στις κεφαλίδες. Όταν κάνετε διπλό κλικ σε μια κεφαλίδα, θα εμφανιστεί αυτόματα το βέλος στο κείμενο της κεφαλίδας. Σημειώστε ότι έχω χρησιμοποιήσει επίσης μορφοποίηση υπό όρους για να επισημάνω επίσης το κελί.
Private Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean) Dim KeyRange As Range Dim ColumnCount As Integer ColumnCount = Range ("DataRange"). Columns.Count Cancel = False If Target.Row = 1 And Target.Collumn <= ColumnCount Στη συνέχεια Cancel = True Worksheets ("Backend"). Range ("C1") = Target.Value Set KeyRange = Range (Target.Address) Range ("DataRange"). Sort Key1: = KeyRange, Header: = xl Ναι Worksheets ("BackEnd ") .Range (" A1 ") = Target.Column For i = 1 To ColumnCount Range (" DataRange "). Cells (1, i). Value = Works works (" Backend "). Range (" A4 "). Offset (0, i - 1). Value Next i End If End Sub
Σημειώστε ότι αυτός ο κώδικας λειτουργεί καλά για τον τρόπο κατασκευής των δεδομένων και του βιβλίου εργασίας μου. Εάν αλλάξετε τη δομή των δεδομένων, θα πρέπει να τροποποιήσετε τον κώδικα ανάλογα.
Κατεβάστε το Παράδειγμα αρχείου
