Λειτουργία διαχωρισμού Excel VBA - Εξηγείται με παραδείγματα

Όταν εργάζεστε με VBA στο Excel, μπορεί να χρειαστεί να χωρίσετε μια συμβολοσειρά σε διαφορετικά μέρη με βάση έναν οριοθέτη.

Για παράδειγμα, εάν έχετε μια διεύθυνση, μπορείτε να χρησιμοποιήσετε τη συνάρτηση VBA Split για να λάβετε διαφορετικά μέρη της διεύθυνσης που διαχωρίζονται με κόμμα (που θα ήταν ο οριοθέτης σε αυτήν την περίπτωση).

Το SPLIT είναι μια ενσωματωμένη συνάρτηση συμβολοσειράς στο Excel VBA που μπορείτε να χρησιμοποιήσετε για να χωρίσετε μια συμβολοσειρά κειμένου βάσει του οριοθέτη.

Λειτουργία Excel VBA SPLIT - Σύνταξη

Διαχωρισμός (Έκφραση, [Οριοθέτης], [Όριο], [Σύγκριση])

• Εκφραση: Αυτή είναι η συμβολοσειρά που θέλετε να χωρίσετε με βάση τον οριοθέτη. Για παράδειγμα, στην περίπτωση του παραδείγματος διεύθυνσης, ολόκληρη η διεύθυνση θα ήταν η «έκφραση». Σε περίπτωση που πρόκειται για συνάρτηση συμβολοσειράς μηδενικού μήκους ("") θα επιστρέψει έναν κενό πίνακα.
• Οριοθέτης: Αυτό είναι ένα προαιρετικό επιχείρημα. Αυτός είναι ο οριοθέτης που χρησιμοποιείται για να χωρίσει το όρισμα «Έκφραση». Στην περίπτωση του παραδείγματος διεύθυνσής μας, ένα κόμμα είναι ένας οριοθέτης που χρησιμοποιείται για να χωρίσει τη διεύθυνση σε διαφορετικά μέρη. Εάν δεν καθορίσετε αυτό το όρισμα, ένας χαρακτήρας διαστήματος θεωρείται ο προεπιλεγμένος οριοθέτης. Σε περίπτωση που δώσετε μια συμβολοσειρά μηδενικού μήκους (""), ολόκληρη η συμβολοσειρά "Έκφραση" επιστρέφεται από τη συνάρτηση.
• Οριο: Αυτό είναι ένα προαιρετικό επιχείρημα. Εδώ καθορίζετε τον συνολικό αριθμό υπο -συμβολοσειρών που θέλετε να επιστρέψετε. Για παράδειγμα, εάν θέλετε να επιστρέψετε μόνο τα τρία πρώτα υποσύμβολα από το όρισμα «Έκφραση», αυτό θα είναι 3. Αν δεν καθορίσετε αυτό το όρισμα, η προεπιλογή είναι -1, η οποία επιστρέφει όλα τα υποσύμβολα.
• Συγκρίνω: Αυτό είναι ένα προαιρετικό επιχείρημα. Εδώ καθορίζετε τον τύπο σύγκρισης που θέλετε να εκτελεί η συνάρτηση SPLIT κατά την αξιολόγηση των υποστοιχιών. Οι ακόλουθες επιλογές είναι διαθέσιμες:
  • Όταν η σύγκριση είναι 0: Αυτή είναι μια δυαδική σύγκριση. Αυτό σημαίνει ότι εάν ο οριοθέτησής σας είναι μια συμβολοσειρά κειμένου (ας πούμε ABC), τότε αυτό θα έχει διάκριση πεζών-κεφαλαίων. Το «ABC» δεν θα είναι ίσο με το «abc».
  • Όταν η σύγκριση είναι 1: Αυτή είναι μια σύγκριση κειμένου. Αυτό σημαίνει ότι εάν ο οριοθέτησής σας είναι μια συμβολοσειρά κειμένου (ας πούμε ABC), τότε ακόμη και αν έχετε «abc» στη συμβολοσειρά «Έκφραση», θα θεωρείται ως οριοθέτης.

Τώρα που καλύψαμε τα βασικά της συνάρτησης SPLIT, ας δούμε μερικά πρακτικά παραδείγματα.

Παράδειγμα 1 - Διαχωρίστε τις λέξεις σε μια πρόταση

Ας υποθέσουμε ότι έχω το κείμενο - "The Quick Brown Fox Jumps Over The Lazy Dog".

Μπορώ να χρησιμοποιήσω τη συνάρτηση SPLIT για να μετατρέψω κάθε λέξη αυτής της πρότασης ως ξεχωριστό στοιχείο σε έναν πίνακα.

Ο παρακάτω κωδικός θα το έκανε αυτό:

Sub SplitWords () Dim TextStrng As String Dim Result () As String TextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog" Αποτέλεσμα () = Split (TextStrng) End Sub

Ενώ ο κώδικας δεν κάνει τίποτα χρήσιμο, θα σας βοηθήσει να καταλάβετε τι κάνει η λειτουργία Split στο VBA.

Η λειτουργία Split χωρίζει τη συμβολοσειρά κειμένου και εκχωρεί κάθε λέξη στον πίνακα Result.

Σε αυτήν την περίπτωση λοιπόν:

• Το αποτέλεσμα (0) αποθηκεύει την τιμή "The"
• Το αποτέλεσμα (1) αποθηκεύει την τιμή "Γρήγορη"
• Το αποτέλεσμα (2) αποθηκεύει την τιμή "Καφέ" και ούτω καθεξής.

Σε αυτό το παράδειγμα, έχουμε ορίσει μόνο το πρώτο όρισμα - το οποίο είναι το κείμενο που πρόκειται να χωριστεί. Δεδομένου ότι δεν έχει οριστεί οριοθέτης, λαμβάνει χαρακτήρα χώρου ως προεπιλεγμένο οριοθέτη.

Σημαντική σημείωση:

1. Η συνάρτηση VBA SPLIT επιστρέφει έναν πίνακα που ξεκινά από τη βάση 0.
2. Όταν το αποτέλεσμα της συνάρτησης SPLIT εκχωρηθεί σε έναν πίνακα, αυτός ο πίνακας πρέπει να δηλωθεί ως τύπος δεδομένων συμβολοσειράς. Εάν το δηλώσετε ως τύπος δεδομένων παραλλαγής, θα εμφανιστεί σφάλμα αναντιστοιχίας τύπου). Στο παραπάνω παράδειγμα, σημειώστε ότι έχω δηλώσει το αποτέλεσμα () ως τύπο δεδομένων συμβολοσειράς.

Παράδειγμα 2 - Μετρήστε τον αριθμό των λέξεων σε μια πρόταση

Μπορείτε να χρησιμοποιήσετε τη συνάρτηση SPLIT για να λάβετε τον συνολικό αριθμό λέξεων σε μια πρόταση. Το κόλπο εδώ είναι να μετρήσετε τον αριθμό των στοιχείων στον πίνακα που λαμβάνετε όταν χωρίζετε το κείμενο.

Ο παρακάτω κώδικας θα εμφανίσει ένα πλαίσιο μηνυμάτων με τον αριθμό λέξεων:

Sub WordCount () Dim TextStrng As String Dim WordCount As Integer Dim Result () As String TextStrng = "The Quick Brown Fox Jumps Over The Lazy Dog" Result = Split (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox "The Word Count είναι "& WordCount End Sub

Σε αυτή την περίπτωση, η συνάρτηση UBound μας λέει το ανώτερο όριο του πίνακα (δηλαδή, τον μέγιστο αριθμό στοιχείων που έχει ο πίνακας). Δεδομένου ότι η βάση του πίνακα είναι 0, προστίθεται 1 για να λάβουμε τον συνολικό αριθμό λέξεων.

Μπορείτε να χρησιμοποιήσετε έναν παρόμοιο κώδικα για να δημιουργήσετε μια προσαρμοσμένη συνάρτηση στο VBA που θα λάβει το κείμενο ως είσοδο και θα επιστρέψει τον αριθμό λέξεων.

Ο παρακάτω κώδικας θα δημιουργήσει αυτήν τη συνάρτηση:

Λειτουργία WordCount (CellRef As Range) Dim TextStrng As String Dim Result () As String Result = Split (WorksheetFunction.Trim (CellRef.Text), "") WordCount = UBound (Result ()) + 1 End End

Μόλις δημιουργηθεί, μπορείτε να χρησιμοποιήσετε τη συνάρτηση WordCount όπως κάθε άλλη κανονική συνάρτηση.

Αυτή η λειτουργία χειρίζεται επίσης κύρια, πίσω και διπλά κενά μεταξύ των λέξεων. Αυτό έγινε δυνατό με τη χρήση της λειτουργίας TRIM στον κώδικα VBA.

Σε περίπτωση που θέλετε να μάθετε περισσότερα για το πώς λειτουργεί αυτός ο τύπος για να μετρήσετε τον αριθμό των λέξεων σε μια πρόταση ή θέλετε να μάθετε για έναν τύπο που δεν είναι VBA για να λάβετε τον αριθμό λέξεων, ελέγξτε αυτό το σεμινάριο.

Παράδειγμα 3 - Χρήση οριοθέτη εκτός από χαρακτήρα διαστήματος

Στα δύο προηγούμενα παραδείγματα, χρησιμοποιήσαμε μόνο ένα όρισμα στη συνάρτηση SPLIT και τα υπόλοιπα ήταν τα προεπιλεγμένα ορίσματα.

Όταν χρησιμοποιείτε κάποιο άλλο όριο, πρέπει να το καθορίσετε στον τύπο SPLIT.

Στον παρακάτω κώδικα, η συνάρτηση SPLIT επιστρέφει έναν πίνακα βασισμένο σε κόμμα ως οριοθέτη και στη συνέχεια εμφανίζει ένα μήνυμα με κάθε λέξη σε ξεχωριστή γραμμή.

Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub

Στον παραπάνω κώδικα, χρησιμοποίησα τον βρόχο For Next για να περάσω από κάθε στοιχείο του πίνακα 'Result' για να το αντιστοιχίσω στη μεταβλητή 'DisplayText'.

Παράδειγμα 4 - Χωρίστε μια διεύθυνση σε τρία μέρη

Με τη συνάρτηση SPLIT, μπορείτε να καθορίσετε πόσους αριθμούς διασπάσεων θέλετε να λάβετε. Για παράδειγμα, εάν δεν καθορίσω τίποτα, κάθε παρουσία του οριοθέτη θα χρησιμοποιηθεί για να χωρίσει τη συμβολοσειρά.

Αλλά αν ορίσω το 3 ως όριο, τότε η συμβολοσειρά θα χωριστεί σε τρία μέρη μόνο.

Για παράδειγμα, εάν έχω την ακόλουθη διεύθυνση:

2703 Winifred Way, Indianapolis, Indiana, 46204

Μπορώ να χρησιμοποιήσω τη λειτουργία Split στο VBA για να χωρίσω αυτήν τη διεύθυνση σε τρία μέρη.

Διαχωρίζει τα δύο πρώτα με βάση τον οριοθέτη κόμμα και το υπόλοιπο μέρος γίνεται το τρίτο στοιχείο του πίνακα.

Ο παρακάτω κώδικας θα εμφανίζει τη διεύθυνση σε τρεις διαφορετικές γραμμές σε ένα πλαίσιο μηνυμάτων:

Sub CommaSeparator () Dim TextStrng As String Dim Result () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split (TextStrng, ",", 3) Για i = LBound (Result ( )) Προς UBound (Αποτέλεσμα ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub

Μια από τις πρακτικές χρήσεις αυτού θα μπορούσε να είναι όταν θέλετε να διαιρέσετε μια διεύθυνση γραμμής στη μορφή που εμφανίζεται στο πλαίσιο μηνυμάτων. Στη συνέχεια, μπορείτε να δημιουργήσετε μια προσαρμοσμένη συνάρτηση που επιστρέφει τη διεύθυνση χωρισμένη σε τρία μέρη (με κάθε τμήμα σε μια νέα γραμμή).

Ο ακόλουθος κώδικας θα το έκανε αυτό:

Λειτουργία ThreePartAddress (cellRef As Range) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) Για i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Trim (Result (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) Λειτουργία τερματισμού

Μόλις έχετε αυτόν τον κώδικα στη μονάδα, μπορείτε να χρησιμοποιήσετε τη συνάρτηση (ThreePartAddress) στο βιβλίο εργασίας, όπως και κάθε άλλη συνάρτηση Excel.

Αυτή η συνάρτηση παίρνει ένα όρισμα - την αναφορά κελιού που έχει τη διεύθυνση.

Λάβετε υπόψη ότι για να εμφανιστεί η διεύθυνση που προκύπτει σε τρεις διαφορετικές γραμμές, πρέπει να εφαρμόσετε τη μορφή περιτυλίγματος κειμένου στα κελιά (βρίσκεται στην καρτέλα Αρχική σελίδα στην ομάδα Ευθυγράμμιση). Εάν η μορφή "Αναδίπλωση κειμένου" δεν είναι ενεργοποιημένη, θα δείτε ολόκληρη τη διεύθυνση ως μία μόνο γραμμή.

Παράδειγμα 5 - Λάβετε το όνομα της πόλης από τη διεύθυνση

Με τη λειτουργία Split στο VBA, μπορείτε να καθορίσετε ποιο μέρος του πίνακα που προκύπτει θέλετε να χρησιμοποιήσετε.

Για παράδειγμα, ας υποθέσουμε ότι χωρίζω την ακόλουθη διεύθυνση με βάση το κόμμα ως οριοθέτη:

2703 Winifred Way, Indianapolis, Indiana, 46204

Ο πίνακας που προκύπτει θα μοιάζει με κάτι όπως φαίνεται παρακάτω:

{"2703 Winifred Way", "Indianapolis", "Indiana", "46204"}

Δεδομένου ότι πρόκειται για έναν πίνακα, μπορώ να επιλέξω να εμφανίσω ή να επιστρέψω ένα συγκεκριμένο μέρος αυτού του πίνακα.

Παρακάτω είναι ένας κωδικός για μια προσαρμοσμένη συνάρτηση, όπου μπορείτε να καθορίσετε έναν αριθμό και θα επιστρέψει αυτό το στοιχείο από τον πίνακα. Για παράδειγμα, εάν θέλω το όνομα κατάστασης, μπορώ να καθορίσω 3 (καθώς είναι το τρίτο στοιχείο του πίνακα).

Λειτουργία ReturnNthElement (CellRef As Range, ElementNumber As Integer) Dim Result () As String Result = Split (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) End Function

Η παραπάνω συνάρτηση παίρνει δύο ορίσματα, την αναφορά κελιού που έχει τη διεύθυνση και τον αριθμό στοιχείου που θέλετε να επιστρέψετε. Η συνάρτηση Split χωρίζει τα στοιχεία διεύθυνσης και τα εκχωρεί στη μεταβλητή Result.

Στη συνέχεια επιστρέφει τον αριθμό στοιχείου που καθορίσατε ως δεύτερο όρισμα. Σημειώστε ότι δεδομένου ότι η βάση είναι 0, το ElementNumber-1 χρησιμοποιείται για να επιστρέψει το σωστό μέρος της διεύθυνσης.

Αυτός ο προσαρμοσμένος τύπος ταιριάζει καλύτερα όταν έχετε μια σταθερή μορφή σε όλες τις διευθύνσεις - δηλαδή, η πόλη αναφέρεται πάντα μετά από τα δύο κόμματα. Εάν τα δεδομένα δεν είναι συνεπή, δεν θα έχετε το επιθυμητό αποτέλεσμα.

Σε περίπτωση που θέλετε το όνομα της πόλης, μπορείτε να χρησιμοποιήσετε το 2 ως δεύτερο όρισμα. Σε περίπτωση που χρησιμοποιείτε έναν αριθμό που είναι υψηλότερος από το συνολικό αριθμό στοιχείων, θα επιστρέψει την #ΑΞΙΑ! λάθος.

Μπορείτε να απλοποιήσετε περαιτέρω τον κώδικα όπως φαίνεται παρακάτω:

Συνάρτηση ReturnNthElement (CellRef As Range, ElementNumber As Integer) ReturnNthElement = Split (CellRef, ",") (ElementNumber - 1) End Function

Στον παραπάνω κώδικα, αντί να χρησιμοποιεί τη μεταβλητή Αποτέλεσμα, επιστρέφει μόνο τον καθορισμένο αριθμό στοιχείου.

Έτσι, αν έχετε Split ("Good Morning") (0), θα επιστρέψει μόνο το πρώτο στοιχείο, το οποίο είναι "Good".

Ομοίως, στον παραπάνω κώδικα, επιστρέφει μόνο τον καθορισμένο αριθμό στοιχείου.