חיפוש המספר התואם החלקי הראשון

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

קבל את המיקום של המספר התואם החלקי הראשון
אחזר את המספר התואם החלקי הראשון

קבל את המיקום של המספר התואם החלקי הראשון

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

תחביר כללי

=MATCH("*"&מספר&"*",TEXT(lookup_array,"0"),0)

√ הערה: זוהי נוסחה מערך שדורשת ממך להכנס באמצעות Ctrl + Shift + Enter.

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

=MATCH("*"&345&"*",TEXT(B5:B16,"0"),0)

או השתמש בהתייחסות לתא כדי להפוך את הנוסחה לדינמית:

=MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0)

√ הערה: כדי לחבר התייחסות לתא/מספר וטקסט ביחד, עליך להוסיף את הסימן & ביניהם. והטקסט צריך להיות מוקף במרכאות כפולות.

הסבר לנוסחה

=INDEX("*"&345&"*",TEXT(B5:B16,"0"),0)

• TEXT(B5:B16,"0"): הפונקציה TEXT ממירה את כל הערכים המספרייים ב B5:B16 לטקסט עם קוד הפורמט “0”. אז נקבל מערך טקסט כזה: {"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"}.
  לחץ כאן כדי ללמוד עוד על הפונקציה TEXT.
• INDEX("*"&345&"*",TEXT(B5:B16,"0"),0) = INDEX("*"&345&"*",{"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"},0): הערך החיפוש "*"&345&"*" יכול להתאים לכל מחרוזות הטקסט המכילות את המחרוזת “345”, בלי קשר למיקום שבו נמצא 345 במחרוזות הטקסט. סוג ההתאמה 0 מבקש מהפונקציה MATCH למצוא את מיקום ההתאמה המדויקת הראשונה במערך. לכן, MATCH יחזיר 5.

אחזר את המספר התואם החלקי הראשון

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

תחביר כללי

=INDEX(MATCH(return_range,"*"&מספר&"*",TEXT(lookup_array,"0"),0))

√ הערה: זוהי נוסחה מערך שדורשת ממך להכנס באמצעות Ctrl + Shift + Enter.

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

=INDEX(MATCH(B5:B16,"*"&345&"*",TEXT(B5:B16,"0"),0))

או השתמש בהתייחסות לתא כדי להפוך את הנוסחה לדינמית:

=INDEX(MATCH(B5:B16,"*"&E5&"*",TEXT(B5:B16,"0"),0))

הסבר לנוסחה

=INDEX(B5:B16,MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0))

• MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0) = 5: לחץ כדי לראות את התהליך המפורט.
• INDEX(B5:B16B5:B16,MATCH("*"&E5&"*",TEXT(B5:B16,"0"),0)) = INDEX(B5:B16B5:B16,5): הפונקציה INDEX מחזירה את הערך החמשי בטווח החזרה B5:B16, שהוא 34545.

פונקציות קשורות

נוסחאות קשורות