אחד החברים שלי עובד בחברת ניתוח שירותי בריאות. לעתים קרובות הוא מתחבר אלי בכמה מהבעיות האמיתיות העומדות בפניו בזמן שהוא עובד עם נתונים ב- Excel.
הרבה פעמים, אני ממיר את השאילתות שלו למדריכי אקסל באתר זה, מכיוון שזה יכול להועיל גם לקוראים האחרים שלי.
זוהי גם הדרכה אחת כזו.
חבר שלי התקשר אלי בשבוע שעבר עם הנושא הבא:
יש נתוני כתובת בעמודה ב- Excel, ואני רוצה לזהות/לסנן תאים שבהם הכתובת מכילה מחרוזות טקסט כפולות (מילים).
להלן מערך הנתונים הדומה בו רצה לסנן תאים המכילים מחרוזת טקסט כפולה (אלה עם חצים אדומים):
כעת מה שמקשה על זה הוא שאין עקביות בנתונים אלה. מכיוון שמדובר באוסף מערך נתונים שנוצר באופן ידני על ידי נציגי מכירות, יכולות להיות וריאציות במערך הנתונים.
שקול זאת:
- כל מחרוזת טקסט יכולה לחזור על מאגר הנתונים הזה. לדוגמה, זה יכול להיות שם האזור או שם העיר או שניהם.
- המילים מופרדות על ידי תו רווח, ואין עקביות אם שם העיר יהיה שם אחרי שש מילים או שמונה מילים.
- יש אלפי רשומות כאלה, והצורך הוא לסנן את הרשומות שבהן יש מחרוזות טקסט כפולות.
לאחר ששקלתי אפשרויות רבות (כגון טקסט לעמודות ונוסחאות), לבסוף החלטתי להשתמש ב- VBA כדי לבצע זאת.
אז יצרתי פונקציית VBA מותאמת אישית ('IdDuplicate') כדי לנתח את התאים האלה ולתת לי TRUE אם יש מילה כפולה במחרוזת הטקסט, ו- FALSE למקרה שאין חזרות (כפי שמוצג להלן):
פונקציה מותאמת אישית זו מנתחת כל מילה במחרוזת הטקסט ובודקת כמה פעמים היא מופיעה בטקסט. אם הספירה היא יותר מ -1, היא מחזירה TRUE; אחרת הוא מחזיר FALSE.
כמו כן, הוא נוצר לספירת מילים בלבד יותר משלוש תווים.
ברגע שיש לי את הנתונים TRUE/FALSE, אוכל לסנן בקלות את כל הרשומות שהן TRUE.
עכשיו תן לי להראות לך כיצד לעשות זאת ב- Excel.
קוד VBA עבור הפונקציה המותאמת אישית
זה נעשה על ידי יצירת פונקציה מותאמת אישית ב- VBA. לאחר מכן ניתן להשתמש בפונקציה זו כמו כל פונקציה אחרת של גליון עבודה ב- Excel.
להלן קוד ה- VBA עבורו:
Function IdDuplicates (rng As Range) As String Dim StringtoAnaly As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split (UCase (rng.Value), "") For i = UBound (StringtoAnalyze) To 0 שלב -1 אם Len (StringtoAnalyze (i)) <minWordLen ואז GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze (j) = StringtoAnalyze (i) Then IdDuplicates = "TRUE" GoTo SkipB סוף אם הבא j SkipA: הבא i IdDuplicates = "FALSE" SkipB: פונקציית סיום
תודה וולטר שהציע גישה טובה יותר לקוד הזה!
כיצד להשתמש בקוד VBA זה
עכשיו כשיש לך את קוד VBA, עליך למקם אותו בקצה האחורי של Excel, כך שהוא יכול לפעול כפונקציה רגילה של דף עבודה.
להלן השלבים להנחת קוד ה- VBA על הקצה האחורי:
- עבור לכרטיסייה מפתחים.
- לחץ על Visual Basic (תוכל גם להשתמש בקיצור המקשים ALT + F11)
- בקצה האחורי של עורך VB שנפתח, לחץ באמצעות לחצן העכבר הימני על כל אחד מאובייקטים של חוברת העבודה.
- עבור אל 'הכנס' ולחץ על 'מודול'. זה יכניס את אובייקט המודול לחוברת העבודה.
- בחלון קוד המודול, העתק והדבק את קוד ה- VBA שהוזכר לעיל.
ברגע שיש לך את קוד VBA בקצה האחורי, תוכל להשתמש בפונקציה - 'IdDuplicates' כמו כל פונקציה רגילה אחרת של דף עבודה.
פונקציה זו לוקחת ארגומנט אחד, שהוא הפניה לתא של התא שבו יש לך את הטקסט.
התוצאה של הפונקציה היא TRUE (אם יש בה מילים כפולות) או FALSE (אם אין כפילויות). ברגע שיש לך את הרשימה הזו של TRUE/FALSE, תוכל לסנן את אלה עם TRUE כדי לקבל את כל התאים שיש בהם מחרוזות טקסט כפולות.
הערה: יצרתי את הקוד רק כדי להתייחס למילים שאורכן יותר משלוש תווים. זה מבטיח שאם ישנן 1, 2 או 3 מילים באורך תו (כגון 12 A, K G M או L D A) במחרוזת הטקסט, אלה יתעלמו ממנה בעת ספירת הכפילויות. אם תרצה, תוכל לשנות זאת בקלות בקוד.
פונקציה זו תהיה זמינה רק בחוברת העבודה שבה העתקת את הקוד במודול. אם אתה רוצה שזה יהיה זמין גם בחוברות עבודה אחרות, עליך להעתיק ולהדביק את הקוד הזה בחוברות העבודה האלה. לחלופין, תוכל גם ליצור תוסף (המאפשר הפונקציה הזו זמינה בכל חוברות העבודה במערכת שלך).
כמו כן, זכור לשמור את חוברת העבודה הזו בתוסף .xlsm (מכיוון שיש בה קוד מאקרו).
