הצמדת טקסט לפי תנאי

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

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

במילים אחרות, אנחנו צריכים כלי שידביק (יקשר) את הטקסט בהתאם לתנאי - אנלוגי לפונקציה SUMMESLI (SUMIF), אלא לטקסט.

שיטה 0. נוסחה

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

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

עכשיו אתה יכול לסנן את אלה ולהעתיק את הדבקת הכתובת הדרושה לשימוש נוסף.

שיטה 1. מאקרופונקציה של הדבקה לפי תנאי אחד

אם הרשימה המקורית לא ממוינת לפי חברה, אז הנוסחה הפשוטה לעיל לא עובדת, אבל אתה יכול בקלות להתמצא עם פונקציה מותאמת אישית קטנה ב-VBA. פתח את Visual Basic Editor על ידי לחיצה על קיצור מקשים Alt + F11 או באמצעות הכפתור Visual Basic כרטיסייה מפתחים (מפתח). בחלון שנפתח, הכנס מודול ריק חדש דרך התפריט הכנס - מודול והעתק את הטקסט של הפונקציה שלנו לשם:

פונקציה MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " הדבקות אינן שוות זו לזו - אנו יוצאים עם שגיאה אם ​​SearchRange.Count <> TextRange.Count ואז MergeIf = CVErr(xlErrRef) Exit Function End אם 'עבור דרך כל התאים, בדוק את התנאי ואסוף את הטקסט במשתנה OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter הבא i 'הצגת תוצאות ללא מפריד אחרון MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End פוּנקצִיָה  

אם אתה חוזר כעת ל-Microsoft Excel, אז ברשימת הפונקציות (כפתור fx בשורת הנוסחאות או בכרטיסייה נוסחאות - פונקציית הוספה) ניתן יהיה למצוא את הפונקציה שלנו MergeIf בקטגוריה משתמש הוגדר (משתמש הוגדר). הארגומנטים לפונקציה הם כדלקמן:

שיטה 2. שרשרת טקסט לפי מצב לא מדויק

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

תווים כלליים לחיפוש נתמכים:

• כוכבית (*) - מציינת כל מספר של תווים כלשהם (כולל היעדרם)
• סימן שאלה (?) - מייצג כל תו בודד
• סימן פאונד (#) - מייצג כל ספרה אחת (0-9)

כברירת מחדל, מפעיל הלייק הוא רגיש לאותיות גדולות, כלומר מבין, למשל, את "אוריון" ו"אוריון" כחברות שונות. כדי להתעלם מהמקרים, אתה יכול להוסיף את השורה ממש בתחילת המודול בעורך Visual Basic אפשרות השווה טקסט, אשר יחליף את ה-Like ללא רגישות רישיות.

בדרך זו, אתה יכול להרכיב מסכות מורכבות מאוד לבדיקת תנאים, למשל:

• ?1##??777RUS – מבחר של כל לוחיות הרישוי של אזור 777, החל מ-1
• LLC* - כל החברות ששמם מתחיל ב- LLC
• ##7## – כל המוצרים עם קוד דיגיטלי בן חמש ספרות, כאשר הספרה השלישית היא 7
• ????? – כל השמות של חמש אותיות וכו'.
  

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

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

פונקציה MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'תווי מפריד (ניתן להחליף ברווח או ; וכו') ה.) 'אם טווחי האימות וההדבקה אינם שווים זה לזה, צא עם שגיאה If SearchRange1.Count <> TextRange.Count או SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'עבור על כל התאים, בדוק את כל התנאים ואסוף את הטקסט למשתנה OutText For i = 1 To SearchRange1.Cells.Count אם SearchRange1.Cells(i) = Condition1 ו-SearchRange2.Cells(i) = Condition2 ואז OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'הצגת תוצאות ללא מפריד אחרון MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

זה יוחל בדיוק באותו אופן - רק ארגומנטים כעת צריכים לפרט יותר:

שיטה 4. קיבוץ והדבקה ב-Power Query

אתה יכול לפתור את הבעיה ללא תכנות ב-VBA, אם אתה משתמש בתוסף Power Query החינמי. עבור Excel 2010-2013 ניתן להוריד אותו כאן, וב-Excel 2016 הוא כבר מובנה כברירת מחדל. רצף הפעולות יהיה כדלקמן:

Power Query לא יודע לעבוד עם טבלאות רגילות, אז הצעד הראשון הוא להפוך את הטבלה שלנו ל"חכם". כדי לעשות זאת, בחר אותו ולחץ על השילוב Ctrl+T או בחר מהכרטיסייה בית - עיצוב כטבלה (בית - עיצוב כטבלה). בכרטיסייה שתופיע לאחר מכן בנאי (לְעַצֵב) אתה יכול להגדיר את שם הטבלה (עזבתי את התקן לוח 1):

עכשיו בואו נטען את הטבלה שלנו לתוסף Power Query. כדי לעשות זאת, בכרטיסייה נתונים (אם יש לך Excel 2016) או בכרטיסייה Power Query (אם יש לך Excel 2010-2013) לחץ מהשולחן (נתונים - מתוך טבלה):

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

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

כעת נוסיף עמודה אחת שבה, באמצעות הפונקציה, אנו מדביקים את התוכן של עמודות הכתובות בכל אחת מהמיני-טבלאות, מופרדות בפסיקים. כדי לעשות זאת, בכרטיסייה הוסף עמודה אנחנו לוחצים עמודה מותאמת אישית (הוסף עמודה - עמודה מותאמת אישית) ובחלון שמופיע, הזן את שם העמודה החדשה ואת נוסחת הצימוד בשפת M המובנית ב- Power Query:

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

נותר להסיר את העמודה שכבר מיותרת כתובות טבלה (קליק ימני על הכותרת) מחק עמודה) והעלה את התוצאות לגיליון על ידי לחיצה על הכרטיסייה דף הבית — סגור והורד (בית - סגור וטען):

ניואנס חשוב: בניגוד לשיטות הקודמות (פונקציות), טבלאות מ- Power Query אינן מתעדכנות באופן אוטומטי. אם בעתיד יהיו שינויים כלשהם בנתוני המקור, אז תצטרך ללחוץ לחיצה ימנית בכל מקום בטבלת התוצאות ולבחור בפקודה עדכן ושמור (לְרַעֲנֵן).

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