חיפוש התאמה הקרובה ביותר

כדי לחפש את ההתאמה הקרובה ביותר של ערך חיפוש בקבוצת נתונים מספרית ב- Excel, ניתן להשתמש בפונקציות INDEX, MATCH, ABS, ו-MIN יחד.

כיצד למצוא את ההתאמה הקרובה ביותר ב- Excel?

כדי לדעת איזה סוכן מכירות הגיע הכי קרוב ליעד של 20,000 דולר כפי שמוצג למעלה, נוסחה שמשלבת את הפונקציות INDEX, MATCH, ABS ו-MIN תסייע לכם בדרך זו: הפונקציה ABS תשנה את כל הערכים ההבדליים בין המכירות של כל סוכן והיעד למספרים חיוביים, ואז MIN תמצא את ההפרש הקטן ביותר, מה שאומר את ההתאמה הקרובה ביותר. כעת נוכל להשתמש בפונקציית MATCH כדי לאתר את מיקום ההתאמה הקרובה ביותר ולהשתמש ב-INDEX כדי לחלץ את הערך במיקום המתאים.

תחביר כללי

=INDEX(return_range,MATCH(MIN(ABS(lookup_array-lookup_value)),ABS(lookup_array-lookup_value),0))

√ הערה: זוהי נוסחת מערך הדורשת להקליד עם Ctrl + Shift + Enter.

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

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-20000)),ABS(C5:C10-20000),0))

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

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

הסבר על הנוסחה

=INDEX(B5:B10,MATCH(MIN(ABS(C5:C10-F4)),ABS(C5:C10-F4),0))

• ABS(C5:C10-F4): החלק C5:C10-F4 יקבל את כל ערכי ההפרש בין כל המכירות בטווח C5:C10 לבין המכירות המטרה $20,000 בתא F4 במערך כזה: {-4322;2451;6931;-1113;6591;-4782}. הפונקציה ABS תשנה את כל המספרים השליליים לחיוביים כך: {4322;2451;6931;1113;6591;4782}.
• MIN(MIN(ABS(C5:C10-F4))) = MIN(MIN({4322;2451;6931;1113;6591;4782})): הפונקציה MIN תמצא את המספר הקטן ביותר מהמערך {4322;2451;6931;1113;6591;4782}, כלומר את ההפרש הקטן ביותר, או כפי שאנו אומרים את ההתאמה הקרובה ביותר. לכן הפונקציה תחזיר 1113.
• MATCH(MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4)),,ABS(C5:C10-F4)ABS(C5:C10-F4),0),0) = MATCH(MATCH(1113,,{4322;2451;6931;1113;6591;4782},0),0): הסוג ההתאמה 0 מכריח את פונקציית MATCH למצוא את מיקומו של המספר המדויק 1113 במערך {4322;2451;6931;1113;6591;4782}. הפונקציה תחזיר 4 מכיוון שהמספר נמצא במקום ה4.
• INDEX(B5:B10B5:B10,,,MATCH(MIN(ABS(C5:C10-F4)ABS(C5:C10-F4))),,,ABS(C5:C10-F4)ABS(C5:C10-F4),0))) = INDEX(B5:B10B5:B10,,,4): הפונקציה INDEX מחזירה את הערך הרביעי בטווח השמות B5:B10, שהוא Bale.

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

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