פונקציית פיצול Excel VBA - מוסברת עם דוגמאות

בעת עבודה עם VBA ב- Excel, ייתכן שיהיה עליך לפצל מחרוזת לחלקים שונים המבוססים על מפריד.

לדוגמה, אם יש לך כתובת, תוכל להשתמש בפונקציית פיצול VBA כדי לקבל חלקים שונים של הכתובת המופרדים באמצעות פסיק (אשר יהיה המפריד במקרה זה).

SPLIT היא פונקציית מחרוזת מובנית ב- Excel VBA בה תוכל לפצל מחרוזת טקסט המבוססת על המפריד.

פונקציית Excel VBA SPLIT - תחביר

פיצול (ביטוי, [תוחם], [גבול], [השווה])

• ביטוי: זו המחרוזת שברצונך לפצל בהתבסס על המפריד. לדוגמה, במקרה של דוגמת הכתובת, הכתובת כולה תהיה 'הביטוי'. במקרה שמדובר בפונקציית SPLIT מחרוזת באורך אפס ("") תחזיר מערך ריק.
• מפריד: זהו טיעון אופציונלי. זהו תוחם המשמש לפיצול הטיעון 'ביטוי'. במקרה של דוגמת הכתובת שלנו, פסיק הוא תוחם המשמש לפיצול הכתובת לחלקים שונים. אם אינך מציין ארגומנט זה, תו רווח נחשב כמפריד ברירת המחדל. במקרה שאתה נותן מחרוזת באורך אפס (""), כל מחרוזת 'הביטוי' מוחזרת על ידי הפונקציה.
• לְהַגבִּיל: זהו טיעון אופציונלי. כאן אתה מציין את המספר הכולל של המצעים שברצונך להחזיר. לדוגמה, אם אתה רק רוצה להחזיר את שלושת המשנים הראשונים מתוך הארגומנט 'ביטוי', זה יהיה 3. אם לא תציין ארגומנט זה, ברירת המחדל היא -1, המחזירה את כל המשנים.
• לְהַשְׁווֹת: זהו טיעון אופציונלי. כאן אתה מציין את סוג ההשוואה שברצונך לבצע את הפונקציה SPLIT בעת הערכת המצעים. האפשרויות הבאות זמינות:
  • כאשר השוואה היא 0: זוהי השוואה בינארית. המשמעות היא שאם המפריד שלך הוא מחרוזת טקסט (נניח ABC), זה יהיה תלוי באותיות רישיות. 'ABC' לא יהיה שווה ל- 'abc'.
  • כאשר השוואה היא 1: זוהי השוואת טקסט. המשמעות היא שאם המפריד שלך הוא מחרוזת טקסט (נניח ABC), גם אם יש לך 'abc' במחרוזת 'ביטוי', הוא ייחשב כמפריד.

כעת, לאחר שסיקרנו את היסודות של פונקציית SPLIT, בואו נראה כמה דוגמאות מעשיות.

דוגמה 1 - פיצול המילים במשפט

נניח שיש לי את הטקסט - "השועל החום המהיר קופץ על הכלב העצל".

אני יכול להשתמש בפונקציה SPLIT כדי להכניס כל מילה במשפט הזה כפריט נפרד במערך.

הקוד שלהלן יעשה זאת:

Sub SplitWords () Dim TextStrng כתוצאה מחרוזת Dim () As String TextStrng = "השועל החום המהיר קופץ על הכלב העצל" תוצאה () = פיצול (TextStrng) End Sub

למרות שהקוד לא עושה דבר מועיל, הוא יעזור לך להבין מה עושה הפונקציה Split ב- VBA.

פונקציית הפיצול מפצלת את מחרוזת הטקסט ומקצה כל מילה למערך התוצאה.

אז במקרה הזה:

• התוצאה (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 = "השועל החום המהיר קופץ מעל הכלב העצל" תוצאה = פיצול (TextStrng) WordCount = UBound (Result ()) + 1 MsgBox "The ספירת מילים היא "& תת סיום WordCount

במקרה זה, הפונקציה UBound מספרת לנו את הגבול העליון של המערך (כלומר, מספר המרכיבים המרבי שיש למערך). מכיוון שבסיס המערך הוא 0, נוסף 1 כדי לקבל את ספירת המילים הכוללת.

תוכל להשתמש בקוד דומה ליצירת פונקציה מותאמת אישית ב- VBA שתיקח את הטקסט כקלט ותחזיר את ספירת המילים.

הקוד שלהלן ייצור פונקציה זו:

פונקציה WordCount (CellRef כטווח) Dim TextStrng כתוצאה מחרוזת עמומה () כתוצאה מחרוזת = פיצול (גליון WorksFunction.Trim (CellRef.Text), "") WordCount = UBound (תוצאה ()) + פונקציית סיום 1

לאחר היצירה, אתה יכול להשתמש בפונקציה WordCount בדיוק כמו כל פונקציה רגילה אחרת.

פונקציה זו מטפלת גם ברווחים מובילים, נגררים וכפולים בין מילים. הדבר התאפשר על ידי שימוש בפונקציית TRIM בקוד VBA.

אם אתה רוצה ללמוד עוד על אופן הפעולה של נוסחה זו לספירת מספר המילים במשפט או שאתה רוצה ללמוד על דרך נוסחה שאינה VBA להשיג את ספירת המילים, עיין במדריך זה.

דוגמא 3 - שימוש בתו מפריד מלבד תו שטח

בשתי הדוגמאות הקודמות, השתמשנו רק בארגומנט אחד בפונקציית SPLIT, והשאר היו ארגומנטים המוגדרים כברירת מחדל.

כאשר אתה משתמש במפריד אחר, עליך לציין זאת בנוסחת SPLIT.

בקוד שלהלן, הפונקציה SPLIT מחזירה מערך המבוסס על פסיק כמפריד, ולאחר מכן מציג הודעה עם כל מילה בשורה נפרדת.

Sub CommaSeparator () Dim TextStrng כתוצאה מחרוזת עמומה () As String Dim DisplayText As String TextStrng = "The, Quick, Brown, Fox, Jump, Over, The, Lazy, Dog" Result = Split (TextStrng, ",") For i = LBound (Result ()) ל- UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine הבא i MsgBox DisplayText End Sub

בקוד לעיל, השתמשתי בלולאה For Next כדי לעבור על כל רכיב במערך 'התוצאה' להקצות אותו למשתנה 'DisplayText'.

דוגמה 4 - חלקו כתובת לשלושה חלקים

בעזרת הפונקציה SPLIT, תוכל לציין כמה מספר פיצולים אתה רוצה לקבל. לדוגמה, אם אינני מציין כלום, כל מופע של המפריד ישמש לפיצול המחרוזת.

אבל אם אציין 3 כמגבלה, הרי שהמחרוזת תתפצל לשלושה חלקים בלבד.

לדוגמה, אם יש לי את הכתובת הבאה:

2703 וויניפרד וואי, אינדיאנפוליס, אינדיאנה, 46204

אני יכול להשתמש בפונקציית הפיצול ב- VBA כדי לחלק את הכתובת הזו לשלושה חלקים.

הוא מפצל את השניים הראשונים על בסיס מפריד הפסיק והחלק שנותר הופך להיות האלמנט השלישי של המערך.

הקוד שלהלן יציג את הכתובת בשלוש שורות שונות בתיבת הודעות:

Sub CommaSeparator () Dim TextStrng כתוצאה מחרוזת עמומה () As String Dim DisplayText As String TextStrng = "2703 Winifred Way, Indianapolis, Indiana, 46204" Result = Split (TextStrng, ",", 3) For i = LBound (Result ( )) ל- UBound (Result ()) DisplayText = DisplayText & Result (i) & vbNewLine Next i MsgBox DisplayText End Sub

אחד השימושים המעשיים של זה יכול להיות כאשר ברצונך לחלק כתובת שורה אחת לפורמט המוצג בתיבת ההודעות. לאחר מכן תוכל ליצור פונקציה מותאמת אישית המחזירה את הכתובת המחולקת לשלושה חלקים (כשכל חלק בשורה חדשה).

הקוד הבא יעשה זאת:

פונקציה ThreePartAddress (cellRef כטווח) Dim TextStrng As String Dim Result () As String Dim DisplayText As String Result = Split (cellRef, ",", 3) For i = LBound (Result ()) To UBound (Result ()) DisplayText = DisplayText & Trim (Result (i)) & vbNewLine Next i ThreePartAddress = Mid (DisplayText, 1, Len (DisplayText) - 1) פונקציית סיום

לאחר שיהיה לך קוד זה במודול, תוכל להשתמש בפונקציה (ThreePartAddress) בחוברת העבודה בדיוק כמו כל פונקציית Excel אחרת.

פונקציה זו לוקחת ארגומנט אחד - הפניה לתא שיש לה את הכתובת.

שים לב שכדי שהכתובת המתקבלת תופיע בשלוש שורות שונות, עליך להחיל את תבנית טקסט העטיפה על התאים (היא נמצאת בכרטיסייה דף הבית בקבוצת היישור). אם פורמט 'גלישת טקסט' אינו מופעל, תראה את כל הכתובת כשורה אחת.

דוגמה 5 - קבל את שם העיר מהכתובת

עם פונקציית פיצול ב- VBA, תוכל לציין באיזה חלק של המערך שנוצר אתה רוצה להשתמש.

לדוגמה, נניח שאני מפצל את הכתובת הבאה על סמך הפסיק כמפריד:

2703 וויניפרד וואי, אינדיאנפוליס, אינדיאנה, 46204

המערך שהתקבל ייראה משהו כפי שמוצג להלן:

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

מכיוון שמדובר במערך, אני יכול לבחור להציג או להחזיר חלק מסוים של מערך זה.

להלן קוד לפונקציה מותאמת אישית, שבה תוכל לציין מספר והוא יחזיר את האלמנט הזה מהמערך. לדוגמה, אם אני רוצה את שם המדינה, אני יכול לציין 3 (מכיוון שהוא האלמנט השלישי במערך).

פונקציה ReturnNthElement (CellRef כטווח, ElementNumber כמספר שלם) תוצאה עמומה () כתוצאה מחרוזת = פיצול (CellRef, ",") ReturnNthElement = Result (ElementNumber - 1) סיום פונקציה

הפונקציה לעיל לוקחת שני ארגומנטים, הפניה לתא שיש לה את הכתובת ומספר האלמנט שברצונך להחזיר. הפונקציה פיצול מפצלת את רכיבי הכתובת ומקצה אותה למשתנה התוצאה.

לאחר מכן הוא מחזיר את מספר האלמנט שציינת כארגומנט השני. שים לב שמכיוון שהבסיס הוא 0, ElementNumber-1 משמש להחזרת החלק הנכון בכתובת.

נוסחה מותאמת אישית זו מתאימה ביותר כאשר יש לך פורמט עקבי בכל הכתובת - כלומר, העיר מוזכרת תמיד לאחר שתי הפסיקים. אם הנתונים אינם עקביים, לא תקבל את התוצאה הרצויה.

במקרה שאתה רוצה את שם העיר, תוכל להשתמש ב -2 כטענה השנייה. במקרה שאתה משתמש במספר הגבוה מהמספר הכולל של האלמנטים, הוא יחזיר את ערך ה- #! שְׁגִיאָה.

תוכל לפשט עוד יותר את הקוד כפי שמוצג להלן:

Function ReturnNthElement (CellRef כטווח, ElementNumber כשלם) ReturnNthElement = פיצול (CellRef, ",") (ElementNumber - 1) פונקציית סיום

בקוד לעיל, במקום להשתמש במשתנה Result, הוא מחזיר רק את מספר האלמנט שצוין.

אז אם יש לך ספליט ("בוקר טוב") (0), הוא יחזיר רק את האלמנט הראשון, שהוא "טוב".

באופן דומה, בקוד לעיל, הוא מחזיר רק את מספר האלמנט שצוין.