Skip to main content

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

Author: Sun Last Modified: 2025-05-30

arrow blue right bubble חיפוש ערך והחזרת מספר ערכים מתאימים עם נוסחת מערך

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

1. הזן את הערך שברצונך לחפש בתא ריק. ראה צילום מסך:

Enter the value you want to look up

2. בתא הסמוך, הקלד את הנוסחה הזו =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2))
לתוך התא, והקש Shift + Ctrl + Enter ביחד, ולאחר מכן גרור את מילוי אוטומטי למילוי תאים כלפי מטה עד שיופיע התא הריק הראשון. ראה צילום מסך:

return multiple corresponding values with formula

הערה: בנוסחה לעיל, $A$1:$B$7 מציין את טווח הנתונים, $A$1:$A$7 מייצג את טווח העמודה שבו תחפש את הערך המסוים, $D$4 מציין את התא שבו הקלדת את ערך החיפוש בשלב 1, 2 מציין מציאת הערכים המתאימים בעמודה השנייה.


arrow blue right bubble חיפוש ערך והחזרת מספר ערכים מתאימים עם פילטר

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

1. בחר את טווח העמודה שבו תרצה לחפש ערך, ולחץ על נתונים > פילטר. ראה צילום מסך:

go to filter feature

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

check the value you want to look up

3. לחץ אישור, כעת תראה שהערך שחיפשת וערכי התאימו לו בלבד נותחו.

filter all corresponding values


arrow blue right bubble חיפוש ערך והחזרת מספר ערכים מתאימים עם פונקציה מוגדרת

אם אתה מעוניין בפונקציה מוגדרת, תוכל גם לפתור את הבעיה עם פונקציה מוגדרת.

1. הקש Alt + F11 כדי לפתוח את חלון Microsoft Visual Basic For Applications.

2. לחץ על מודול > הוסף כדי להכניס חלון מודול, והעתק את הקוד VBA הבא לחלון.

VBA: חיפוש ערך והחזרת מספר ערכים מתאימים.

Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
    If pWorkRng = pRng.Cells(i, 1) Then
        arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
        ReDim Preserve arr(UBound(arr) + 1)
    End If
Next
If pType = "h" Then
    xCol = Range(Application.Caller.Address).Columns.Count
    For i = UBound(arr) To xCol
        arr(UBound(arr)) = ""
        ReDim Preserve arr(UBound(arr) + 1)
    Next
    ReDim Preserve arr(UBound(arr) - 1)
    MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
    arr(UBound(arr)) = ""
    ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function

3. סגור את החלון, והקלד את הנוסחה הזו לתוך תא =MyVlookup(A10,$A$2:$B$7,2) (A10 מציין את ערך החיפוש, $A$2:$B$7 מציין את טווח הנתונים, 2 מציין את מספר האינדקס של העמודה). והקש Shift + Ctrl + Enter. לאחר מכן גרור את ידית המילוי למטה לתאים, שים את הסמן בשורת הנוסחאות, והקש שוב Shift + Ctrl + Enter.

טיפ: אם ברצונך להחזיר את הערכים בתאים אופקיים, תוכל להקליד את הנוסחה הזו =MyVlookup(A10, $A$2:$B$7, 2, "h").

כלי הפרודוקטיביות הטובים ביותר ל-Office

🤖 Kutools AI Aide: מהפכה בניתוח נתונים באמצעות: ביצוע חכם |צור קוד |צור נוסחאות מותאם אישית |נתח נתונים וייצר תרשימים |הפעל פונקציות משופרות
פיצ'רים פופולריים: מצא, הדגש או סמן כפילויות | מחק שורות ריקות | שלב עמודות או תאים מבלי לאבד נתונים |   עיגול ...
חיפוש מתקדם: VLookup עם מספר קריטריונים | VLookup לערכים מרובים | חיפוש במספר גליונות | התאמה עמומה ....
רשימת נפתחת מתקדמת: צור במהירות רשימה נפתחת | רשימה נפתחת תלויה | רשימה נפתחת עם בחירה מרובה ....
מנהל עמודות: הוסף מספר עמודות מסוים | העבר עמודות | החלף מצב תצוגה של עמודות מוסתרות | השווה טווחים ועמודות ...
פיצ'רים נבחרים: מיקוד רשת | תצוגת עיצוב | שורת נוסחאות משופרת | נהל חוברת עבודה וגליון עבודה | ספריית טקסט אוטומטי (Auto Text) | בורר תאריך | מיזוג נתונים | הצפן/פענח תאים | שלח דואר אלקטרוני לפי טבלה חד-ממדית | סינון מתקדם | סינון מיוחד (סנן תאים עם גופן מודגש/נטוי/קו חוצה...) ...
15 ערכות כלים מובילות:12 כלי טקסט (הוסף טקסט, מחק תווים מסוימים, ...) | מעל50 סוגי תרשימים (תרשים גאנט, ...) | מעל40 נוסחאות חכמות (חישוב גיל על פי תאריך לידה, ...) |19 כלי הוספה (הכנס קוד QR, הכנס תמונה מנתיב, ...) |12 כלי המרה (המרה למילים, המרת מטבע, ...) |7 כלי מיזוג ופיצול (מיזוג מתקדם של שורות, פיצול תאים, ...) | ...ועוד

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


Office Tab מביא ממשק טאב ל-Office והופך את העבודה שלך להרבה יותר קלה

  • אפשר עריכה וקריאה בטאבים ב-Word, Excel, PowerPoint
  • פתח וצור מסמכים מרובים בטאבים חדשים באותו חלון, במקום בחלונות חדשים.
  • הגדל את הפרודוקטיביות שלך ב-50% וצמצם מאות קליקים של עכבר בכל יום!