פונקציות Excel יכולות להיות חזקות מאוד אם אתה מבין בשילוב נוסחאות שונות.מה שנראה פעם בלתי אפשרי יכול להפוך פתאום למשחק ילדים.
דוגמה אחת כזו היא מציאת הערך הקרוב ביותר במערך נתונים של Excel.
ישנן מספר פונקציות חיפוש שימושיות באקסל (כגון VLOOKUP ו- INDEX MATCH) שיכולות למצוא את הקרוב ביותר值(כפי שמוצג בדוגמה למטה).
אבל החלק הטוב ביותר הוא שאתה יכול לשלב את פונקציות חיפוש אלה עם פונקציות אחרות של Excel כדי לעשות הרבה יותר (כולל מציאת ההתאמה הקרובה ביותר לערך חיפוש ברשימה לא ממוינת).
במדריך זה, אני אראה לך כיצד להשתמש בנוסחת חיפוש כדי למצוא את ההתאמה הקרובה ביותר לערך חיפוש ב-Excel.
ישנם תרחישים רבים ושונים שבהם עליך למצוא את ההתאמה הקרובה ביותר (או ערך ההתאמה הקרובה ביותר).
הנה דוגמה שאכסה במאמר זה:
- מצא שיעורי עמלות על סמך מכירות
- מצא את המועמד הטוב ביותר (בהתבסס על הניסיון הקרוב ביותר)
- מצא את תאריך האירוע הבא
בואו נתחיל!
תוכן
מצא שיעורי עמלות (חפש את ערך המכירה הקרוב ביותר)
נניח שיש לך מערך נתונים כמו זה למטה שבו אתה רוצה למצוא את שיעורי העמלות עבור כל אנשי המכירות.
העמלות מחולקות לפי שווי המכירה.זה מחושב באמצעות הטבלה בצד ימין.
לדוגמה, אם סך המכירות של איש המכירות הוא 5000, העמלה היא 0%, ואם סך המכירות שלו הוא 15000, העמלה היא 5%.
כדי לקבל שיעור עמלה, עליך למצוא את טווח המכירות הקרוב ביותר שנמצא ממש מתחת לערך המכירה.לדוגמה, עבור 15000 מכירות, העמלה היא 10,000 (או 5%), ועבור 25000 מכירות, שיעור העמלה הוא 20,000 (או 7%).
כדי למצוא את ערך המכירה הקרוב ביותר ולקבל שיעור עמלה, אתה יכול להשתמש בהתאמה משוערת ב-VLOOKUP.
הנוסחה הבאה יכולה לעשות זאת:
=VLOOKUP(B2,$E$2:$F$6,2,1)
שימו לב שבנוסחה זו, הפרמטר האחרון הוא 1, מה שאומר לנוסחה להשתמש בחיפוש משוער.המשמעות היא שהנוסחה תעבור על ערך המכירה בעמודה E ותמצא את הערך ממש מתחת לערך הבדיקה.
הנוסחה VLOOKUP אז תיתן את שיעור העמלה עבור ערך זה.
注意: לשם כך, עליך למיין את הנתונים בסדר עולה.
מצא את המועמד הטוב ביותר (בהתבסס על הניסיון הקרוב ביותר)
בדוגמה שלמעלה, יש למיין את הנתונים בסדר עולה.אבל ייתכנו מקרים שבהם הנתונים לא ממוינים.
אז בואו ניקח דוגמה ונראה כיצד נוכל להשתמש בשילובי נוסחאות כדי למצוא את ההתאמה הקרובה ביותר באקסל.
להלן מערך נתונים לדוגמה שבו אני צריך למצוא את שמות העובדים שניסיון העבודה שלהם הוא הכי קרוב לערך הרצוי.הערך הצפוי במקרה זה הוא 2.5 שנים.
שימו לב שהנתונים אינם ממוינים.בנוסף, החוויות הקרובות ביותר עשויות להיות פחות או יותר מחוויה נתונה.לדוגמה, שנתיים ו-2 שנים קרובות באותה מידה (הפרש של 3 שנים).
הנה הנוסחה שנותנת לנו את התוצאה:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
החוכמה בנוסחה זו היא לשנות את מערך הבדיקה ואת ערכי הבדיקה כדי למצוא את ההבדל האמפירי הקטן ביותר בין הערכים הרצויים למציאותיים.
בואו נבין קודם איך הייתם עושים את זה ידנית (ואז אני אסביר איך הנוסחה הזו עובדת).
כאשר אתה עושה זאת באופן ידני, אתה עובר על כל תא בעמודה B ומוצא את ההבדל בין הניסיון שאתה רוצה לחוויה שיש לו.ברגע שיש לך את כל ההבדלים, אתה מוצא את הקטן ביותר ומקבל את שמו של אותו אדם.
זה בדיוק מה שאנחנו עושים עם הנוסחה הזו.
הרשה לי להסביר.
ערך הבדיקה בנוסחת MATCH הוא MIN(ABS(D2-B2:B15)).
סעיף זה נותן לך את ההבדל המינימלי בין הניסיון הנתון (כלומר 2.5 שנים) לבין כל החוויות האחרות.במקרה זה הוא מחזיר 0.3
שימו לב שאני משתמש ב-ABS כדי לוודא שאני מחפש את הקרוב ביותר (כנראה פחות או יותר מהחוויה הנתונה).
כעת, הערך המינימלי הזה הופך לערך החיפוש שלנו.
מערך בדיקת המידע בפונקציית MATCH הוא ABS(D2-$B$2:$B$15).
זה נותן לנו מערך של מספרים שמהם מופחת 2.5 (נדרש ניסיון).
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
הפונקציה MATCH מוצאת את המיקום של 0.3 במערך זה, שהוא המיקום של השם של האדם עם הניסיון הקרוב ביותר.
לאחר מכן, הפונקציה INDEX משתמשת במספר המשרה כדי להחזיר את שמו של האדם.
הערה: אם יש מספר מועמדים עם אותו ניסיון מינימלי, הנוסחה לעיל תיתן את שם העובד התואם הראשון.
מצא את תאריך האירוע הבא
הנה דוגמה נוספת שבה אתה יכול להשתמש בנוסחת חיפוש כדי למצוא את התאריך הבא של אירוע בהתבסס על התאריך הנוכחי.
להלן מערך הנתונים שבו יש לי שמות אירועים ותאריכי אירועים.
מה שאני רוצה זה שם האירוע הבא ותאריך האירוע של האירוע הקרוב.
הנה הנוסחה שנותנת את שם האירוע הקרוב:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
הנוסחה הבאה תציג את תאריכי האירועים הקרובים:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
תן לי להסביר איך הנוסחה הזו עובדת.
כדי לקבל את תאריך האירוע, הפונקציה MATCH מחפשת בעמודה B את התאריך הנוכחי.במקרה זה, אנחנו לא מחפשים התאמה מדויקת, אלא התאמה משוערת.לכן, הפרמטר האחרון של הפונקציה MATCH הוא 1 (היא מוצאת את הערך הגדול ביותר קטן או שווה לערך הבדיקה).
אז הפונקציה MATCH תחזיר את המיקום של התא שהתאריך שלו במקרה קטן או שווה לתאריך הנוכחי.אז במקרה זה האירוע הבא יהיה בתא הבא (שכן הרשימה ממוינת בסדר עולה).
לכן, כדי לקבל את תאריך האירוע הקרוב, פשוט הוסף אחד למיקום התא שהוחזר על ידי פונקציית MATCH וזה ייתן לך את מיקום התא של תאריך האירוע הבא.
ערך זה ניתן לאחר מכן על ידי הפונקציה INDEX.
כדי לקבל את שם האירוע, השתמש באותה נוסחה ושנה את הטווח בפונקציית INDEX מעמודה B לעמודה A.
דוגמה זו עלתה במוחי כאשר חבר הגיש בקשה.הוא מפרט את ימי ההולדת של כל חבריו/קרוביו בטור ורוצה לדעת שמתקרב יום ההולדת הבא (ואת שמו של האדם).
שלוש הדוגמאות לעיל מראות כיצד להשתמש בנוסחת בדיקת המידע כדי למצוא את הערך הקרוב ביותר ב- Excel.
