כיצד לבצע VLOOKUP כדי להחזיר מספר ערכים בתא אחד ב-Excel?

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

החזר מספר ערכים לתא אחד עם פונקציית TEXTJOIN (Excel 2019 ו-Office 365)

• החזר את כל הערכים התואמים לתא אחד
• החזר את כל הערכים התואמים ללא כפילויות לתא אחד

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

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

• החזר את כל הערכים התואמים לתא אחד
• החזר את כל הערכים התואמים ללא כפילויות לתא אחד

החזר מספר ערכים לתא אחד עם פונקציית TEXTJOIN (Excel 2019 ו-Office 365)

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

  החזר את כל הערכים התואמים לתא אחד

אנא החלו את הנוסחה הבאה בתא ריק שבו ברצונכם להכניס את התוצאה, ולאחר מכן לחצו יחדיו על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאה הראשונה, ואז גררו את ידית המילוי כלפי מטה לתאים שבהם תרצו להשתמש בנוסחה זו, ותקבלו את כל הערכים התואמים כפי שמוצג בסcreenshot למטה:

  החזר את כל הערכים התואמים ללא כפילויות לתא אחד

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

אנא העתיקו והדבקו את הנוסחה הבאה לתא ריק, ולאחר מכן לחצו יחדיו על מקשי Ctrl + Shift + Enter כדי לקבל את התוצאה הראשונה, ואז העתקו את הנוסחה למילוי תאים אחרים, ותקבלו את כל הערכים התואמים ללא הכפילויות כפי שמוצג בסcreenshot למטה:

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

עם תכונת 'מיזוג מתקדם של שורות' של Kutools עבור Excel, תוכלו בקלות לאחזר מספר ערכים תואמים לתוך תא אחד—אין צורך בנוסחאות מורכבות! אומרים שלום לפתרונות עבודה ידניים ופותחים את הדרך היעילה יותר להתמודד עם משימות חיפוש ב-Excel. בואו נחקור כיצד Kutools עבור Excel הופכת את כל זה אפשרי!

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

1. בחרו את טווח הנתונים שברצונכם לשלב את הנתונים של עמודה אחת בהתבסס על עמודה אחרת.

2. לחצו על 'Kutools' > 'מיזוג ופיצול' > 'מיזוג מתקדם של שורות', ראו צילום מסך:

3. בחלון הדו-שיח 'מיזוג מתקדם של שורות' שנפתח:

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

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

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

הורידו ונסו בחינם את Kutools עבור Excel עכשיו!

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

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

  החזר את כל הערכים התואמים לתא אחד

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

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

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

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

3. לאחר מכן שמורו וסגרו את הקוד הזה, חזרו לגיליון העבודה, והכניסו את הנוסחה הבאה: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") לתא ריק מסוים שבו ברצונכם להציב את התוצאה, ולאחר מכן גררו את ידית המילוי כלפי מטה כדי לקבל את כל הערכים התואמים בתא אחד שאתם רוצים, ראו צילום מסך:

  החזר את כל הערכים התואמים ללא כפילויות לתא אחד

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

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

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

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

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. לאחר הכנסת הקוד, לחצו על 'כלים' > 'הפניות' בחלון 'Microsoft Visual Basic for Applications' שנפתח, ולאחר מכן, בחלון הדו-שיח 'הפניות – VBAProject' שנפתח, סמנו את האפשרות 'Microsoft Scripting Runtime' ברשימת 'הפניות הזמינות', ראו צילומי מסך:

4. לאחר מכן לחצו על אישור כדי לסגור את חלון הדו-שיח, לשמור ולסגור את חלון הקוד, חזרו לגיליון העבודה, והכניסו את הנוסחה הבאה: =MultipleLookupNoRept(E2,$A$2:$C$11,3) לתא ריק שבו ברצונכם להציג את התוצאה, ולאחר מכן גררו את ידית המילוי כלפי מטה כדי לקבל את כל הערכים התואמים, ראו צילום מסך:

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

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