כיצד לבצע חיפוש והדבקה של מספר ערכים מתאימים ב-Excel?

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

חיפוש והדבקה של מספר ערכים מתאימים ב-Excel

• שימוש בפונקציות TEXTJOIN ו-FILTER (Excel 365/2021)
• שימוש ב-Kutools עבור Excel
• שימוש בפונקציה מוגדרת על ידי המשתמש

חיפוש והדבקה של מספר ערכים מתאימים עם פונקציות TEXTJOIN ו-FILTER

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

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

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))

חיפוש והדבקה של מספר ערכים מתאימים עם Kutools עבור Excel

השתעממת מהגבלות VLOOKUP של Excel כאשר מתמודדים עם מספר ערכים מתאימים? אבל עם תכונת החיפוש אחד לרבים של Kutools עבור Excel, תוכל בקלות לחפש ולהדביק מספר ערכים מתאימים בכמה קליקים בלבד!

לאחר התקנת Kutools עבור Excel, אנא עשה כך:

לחץ על "Kutools" > "חיפוש מתקדם" > "חיפוש אחד לרבים (מחזיר מספר תוצאות)" כדי לפתוח את תיבת הדו-שיח. בתיבת הדו-שיח, אנא ציין את הפעולות כדלקמן:

1. בחר את "טווח פלט" ואת "ערכים לחיפוש" בשדות הטקסט בנפרד;
2. בחר את טווח הטבלה שברצונך להשתמש בו;
3. ציין את עמודת המפתח ועמודת ההחזרה מהתפריטים הנפתחים של "עמודת מפתח" ו"עמודת החזרה" בנפרד;
4. לבסוף, לחץ על כפתור אישור.
   

תוצאה: כעת, כל הערכים התואמים מחוברים לתא אחד. ראה צילום מסך:

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

אם אתה מרגיש בנוח עם VBA (Visual Basic for Applications), אתה יכול ליצור פונקציה מוגדרת על ידי המשתמש (UDF) כדי להדביק מספר ערכים מתאימים. שיטה זו עובדת בכל גרסאות Excel.

1. החזק את המקשים "ALT + F11" כדי לפתוח את חלון "Microsoft Visual Basic for Applications".

2. לחץ על "הוסף" > "מודול", והדבק את הקוד הבא בחלון המודול.

קוד VBA: חיפוש והדבקה של מספר ערכים תואמים בתא

Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
    Dim Cell As Range
    Dim Result As String
    Result = ""
    For Each Cell In LookupRange
        If Cell.Value = LookupValue Then
            Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
        End If
    Next Cell
    If Result <> "" Then
        Result = Left(Result, Len(Result) - Len(Delimiter))
    End If
    ConcatenateMatches = Result
End Function

3. לאחר מכן שמור וסגור את הקוד הזה, חזור לגיליון העבודה, והזן את הנוסחה הזו: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) לתוך תא ריק שבו ברצונך להכניס את התוצאה, לאחר מכן גרור את ידית המילוי כלפי מטה כדי למלא את הנוסחה לתאים אחרים. כל הערכים המתאימים בהתבסס על נתון מסוים הוחזרו לתא אחד עם מפרידים של פסיק ורווח, ראה צילום מסך:

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