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

המדריך הזה מדבר על חיפוש ערך והחזרת מספר ערכים מתאימים ב-Excel כפי שמוצג בתמונות המסך הבאות:
חיפוש ערך והחזרת מספר ערכים מתאימים עם נוסחת מערך
חיפוש ערך והחזרת מספר ערכים מתאימים עם נוסחת מערך
כאן ישנה נוסחת מערך ארוכה שיכולה לעזור בחיפוש ערך והחזרת מספר ערכים מתאימים.
1. הזן את הערך שברצונך לחפש בתא ריק. ראה צילום מסך:
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 ביחד, ולאחר מכן גרור את מילוי אוטומטי למילוי תאים כלפי מטה עד שיופיע התא הריק הראשון. ראה צילום מסך:
הערה: בנוסחה לעיל, $A$1:$B$7 מציין את טווח הנתונים, $A$1:$A$7 מייצג את טווח העמודה שבו תחפש את הערך המסוים, $D$4 מציין את התא שבו הקלדת את ערך החיפוש בשלב 1, 2 מציין מציאת הערכים המתאימים בעמודה השנייה.
חיפוש ערך והחזרת מספר ערכים מתאימים עם פילטר
ב-Excel, אתה יכול גם להשתמש בתכונת הפילטר כדי לפתור את הבעיה.
1. בחר את טווח העמודה שבו תרצה לחפש ערך, ולחץ על נתונים > פילטר. ראה צילום מסך:
2. לאחר מכן, לחץ על כפתור החץ בתא הראשון של הטווח שבחרת, וסמן את הערך שברצונך לחפש רק ברשימה הנפתחת. ראה צילום מסך:
3. לחץ אישור, כעת תראה שהערך שחיפשת וערכי התאימו לו בלבד נותחו.
חיפוש ערך והחזרת מספר ערכים מתאימים עם פונקציה מוגדרת
אם אתה מעוניין בפונקציה מוגדרת, תוכל גם לפתור את הבעיה עם פונקציה מוגדרת.
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
שדרג את כישורי ה-Excel שלך עם Kutools for Excel ותחווה יעילות שלא הכרת. Kutools for Excel מציע מעל300 פיצ'רים מתקדמים לשיפור הפרודוקטיביות ולחסוך זמן. לחץ כאן כדי לקבל את הפיצ'ר שהכי נחוץ לך...
Office Tab מביא ממשק טאב ל-Office והופך את העבודה שלך להרבה יותר קלה
- אפשר עריכה וקריאה בטאבים ב-Word, Excel, PowerPoint
- פתח וצור מסמכים מרובים בטאבים חדשים באותו חלון, במקום בחלונות חדשים.
- הגדל את הפרודוקטיביות שלך ב-50% וצמצם מאות קליקים של עכבר בכל יום!