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

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

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

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

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

אם יש לכם גישה ל-Excel 365 או Excel 2021 והגרסאות החדשות יותר, השימוש בנוסחת מערך דינמית היא השיטה הפשוטה ביותר.

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

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

לאחר מכן, גררו את ידית המילוי כלפי מטה כדי למלא את הנוסחה לתאים אחרים, ראו צילום מסך:

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

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

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

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

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

תוצאה:

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

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

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

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

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

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

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

Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
    'Updateby Extendoffice
    Dim result As String
    Dim i As Long
    result = ""
    For i = 1 To lookupRange.Rows.Count
        If lookupRange.Cells(i, 1).Value = lookupValue Then
            result = result & returnRange.Cells(i, 1).Value & delimiter
        End If
    Next i
    If Len(result) > 0 Then
        JoinLookupValues = Left(result, Len(result) - Len(delimiter))
    Else
        JoinLookupValues = ""
    End If
End Function

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

=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")

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

מאמרים קשורים: