כיצד לסנן תאים המכילים מחרוזות טקסט חוזרות (מילים)

חבר שלילעתים קרובות אני בקשר איתו לגבי כמה מהבעיות בעולם האמיתי שהוא מתמודד איתו בעבודה עם נתונים ב-Excel.

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

זו גם מדריך כזה.

חבר שלי התקשר אליי בשבוע שעבר עם השאלות הבאות:

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

הנה מערך נתונים דומה שבו הוא רוצה לסנן תאים (תאים עם חיצים אדומים) שיש בהם מחרוזות טקסט חוזרות:

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

תחשוב על זה:

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

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

אז יצרתי פונקציית VBA מותאמת אישית ('IdDuplicate') כדי לנתח את התאים האלה ולתת לי TRUE אם יש מילים כפולות במחרוזת הטקסט ו-FALSE אם אין כפילויות (כפי שמוצג להלן):

פונקציה מותאמת אישית זו מנתחת כל מילה במחרוזת הטקסט ובודקת כמה פעמים היא מופיעה בטקסט.מחזירה TRUE אם הספירה גדולה מ-1;אחרת החזר FALSE.

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

ברגע שיש לי את הנתונים TRUE/FALSE, אני יכול לסנן בקלות את כל הרשומות שהן TRUE.

כעת הרשו לי להראות לכם כיצד לעשות זאת באקסל.

קוד VBA לפונקציה מותאמת אישית

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

הנה קוד ה-VBA שלו:

פונקציה IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") עבור i = UBound(StringtoAnalyze) To 0 שלב -1 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: End Function

כיצד להשתמש בקוד VBA זה

כעת, כשיש לך את קוד ה-VBA, עליך לשים אותו ב-backend של Excel כדי שהוא יוכל לעבוד כפונקציה רגילה של גליון עבודה.

להלן השלבים להכנסת קוד VBA בקצה האחורי:

1. עבור ללשונית מפתחים.
2. לחץ על Visual Basic (תוכל גם להשתמש בקיצור המקלדת ALT+F11)
3. בקצה האחורי הפתוח של עורך VB, לחץ לחיצה ימנית על כל אובייקט של חוברת עבודה.
4. עבור אל "הוספה" ולחץ על "מודול".פעולה זו תכניס את אובייקט המודול של חוברת העבודה.
5. בחלון קוד המודול, העתק והדבק את קוד ה-VBA שהוזכר לעיל.

ברגע שיש לך את קוד ה-VBA ב-backend, אתה יכול להשתמש בפונקציה - 'IdDuplicates' כמו כל פונקציית גליון עבודה רגילה אחרת.

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

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

הערה: הקוד שיצרתי מיועד רק למילים שאורכן יותר משלושה תווים.זה מבטיח שאם יש מילים במחרוזת הטקסט באורך של 1, 2 או 3 תווים (למשל 12 A, KGM או LDA), התעלמות מהמילים הללו בעת ספירת כפילויות.ניתן לשנות זאת בקלות בקוד במידת הצורך.

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

כמו כן, זכור לשמור חוברת עבודה זו עם סיומת .xlsm (מכיוון שהיא מכילה קוד מאקרו).