פונקציות Excel יכולות להיות עוצמתיות ביותר אם תמצא שילוב של נוסחאות שונות. דברים שאולי נראו בלתי אפשריים פתאום יראו כמו משחק ילדים.
דוגמה אחת כזו היא למצוא את ההתאמה הקרובה ביותר של ערך חיפוש במערך נתונים ב- Excel.
ישנן מספר פונקציות חיפוש שימושיות ב- Excel (כגון VLOOKUP & INDEX MATCH), שיכולות למצוא את ההתאמה הקרובה ביותר בכמה מקרים פשוטים (כפי שאראה עם דוגמאות להלן).
אבל החלק הטוב ביותר הוא שאתה יכול לשלב את פונקציות החיפוש האלה עם פונקציות Excel אחרות כדי לעשות הרבה יותר (כולל מציאת ההתאמה הקרובה ביותר של ערך חיפוש ברשימה לא ממוינת).
במדריך זה אראה לך כיצד אתה מוצא את ההתאמה הקרובה ביותר לערך חיפוש ב- 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 תיתן את שיעור העמלה עבור ערך זה.
הערה: כדי שזה יעבוד, עליך למיין את הנתונים בסדר עולה.לחץ כאן להורדת קובץ הדוגמה
מצא את המועמד הטוב ביותר (בהתבסס על הניסיון הקרוב ביותר)
בדוגמה שלמעלה, היה צריך למיין את הנתונים בסדר עולה. אך יכולים להיות מקרים בהם הנתונים אינם ממוינים.
אז בואו נסקור דוגמה ונראה כיצד נוכל למצוא את ההתאמה הקרובה ביותר ב- Excel באמצעות שילוב נוסחאות.
להלן מערך נתונים לדוגמה שבו אני צריך למצוא את שם העובד בעל ניסיון העבודה הקרוב לערך הרצוי. הערך הרצוי במקרה זה בעוד 2.5 שנים.
שים לב שהנתונים אינם ממוינים. כמו כן, החוויה הקרובה ביותר יכולה להיות פחות או יותר מחוויית ה- give. לדוגמה, שנתיים ו -3 שנים שניהם קרובים באותה מידה (הפרש של 0.5 שנים).
להלן הנוסחה שתיתן לנו את התוצאה:
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
הטריק בנוסחה זו הוא לשנות את מערך החיפוש ואת ערך החיפוש כדי למצוא את ההבדל המינימלי בניסיון בערכים הנדרשים והאמיתיים.
הבה נבין תחילה כיצד היית עושה זאת באופן ידני (ולאחר מכן אסביר כיצד נוסחה זו עובדת).
כשעושים זאת באופן ידני, תעבור על כל תא בעמודה ב 'ותמצא את ההבדל בחוויה בין מה שנדרש לזה שיש לאדם. ברגע שיש לך את כל ההבדלים, תמצא את המינימום ותביא את שמו של אותו אדם.
זה בדיוק מה שאנחנו עושים עם הנוסחה הזו.
הרשה לי להסביר.
ערך החיפוש בנוסחת 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 באמצעות נוסחאות חיפוש.
אולי תאהב גם את הטיפים/הדרכות הבאות של Excel
- קבל את המספר האחרון מרשימה באמצעות פונקציית VLOOKUP.
- קבל ערכי חיפוש מרובים ללא חזרה בתא בודד.
- VLOOKUP Vs. אינדקס/התאמה
- התאמת אינדקס של Excel
- מצא את ההופעה האחרונה של ערך חיפוש רשימה ברשימה ב- Excel
- מצא והסר כפילויות ב- Excel
- עיצוב מותנה.
