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

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

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

• באמצעות פונקציות TEXTJOIN ו-FILTER
• באמצעות תכונה מתקדמת
• באמצעות פונקציה מותאמת אישית (User Defined Function)

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

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

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

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(C2:C17, A2:A17=E2)))

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

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

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

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

תוצאה:

כעת תוכל לראות שכל הערכים התואמים נשלפו ללא פריטים כפולים, ראה צילום מסך:

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

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

1. החזק את המקשים "Alt" + "F11" כדי לפתוח את חלון "Microsoft Visual Basic for Applications".
2. לחץ על "הוסף" > "מודול", והדבק את הקוד הבא בחלון ה"מודול".

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

     Function VlookupUnique(lookupValue As String, lookupRange As Range, resultRange As Range, delim As String) As String
       Dim cell As Range
       Dim result As String
       Dim dict As Object
       Set dict = CreateObject("Scripting.Dictionary")
       For Each cell In lookupRange
           If cell.Value = lookupValue Then
               If Not dict.exists(resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value) Then
                   dict.Add resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value, True
                   result = result & delim & resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value
               End If
           End If
       Next cell 
       If Len(result) > 0 Then
           VlookupUnique = Mid(result, Len(delim) + 1)
       Else
           VlookupUnique = ""
       End If
   End Function

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

   =VlookupUnique(E2, A2:A17, C2:C17, ", ")

   
   

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