בעת שימוש בנוסחאות חיפוש ב- Excel (כגון VLOOKUP, XLOOKUP או INDEX/MATCH), הכוונה היא למצוא את הערך התואם ולקבל את הערך (או ערך תואם באותה שורה/עמודה) כתוצאה מכך.
אך במקרים מסוימים, במקום לקבל את הערך, ייתכן שתרצה שהנוסחה תחזיר את כתובת התא של הערך.
זה יכול להיות שימושי במיוחד אם יש לך מערך נתונים גדול וברצונך לברר את המיקום המדויק של תוצאת נוסחת החיפוש.
יש כמה פונקציות ב- Excel שנועדו לעשות זאת בדיוק.
במדריך זה אראה לך כיצד תוכל למצוא ולהחזיר את כתובת התא במקום הערך ב- Excel באמצעות נוסחאות פשוטות.
חיפוש והחזרת כתובת סלולר באמצעות הפונקציה ADDRESS
הפונקציה ADDRESS ב- Excel נועדה בדיוק לכך.
הוא לוקח את השורה ואת מספר העמודה ונותן לך את כתובת התא של התא הספציפי הזה.
להלן התחביר של הפונקציה ADDRESS:
= ADDRESS (row_num, column_num, [abs_num], [a1], [sheet_text])
איפה:
- row_num: מספר השורה של התא שעבורו תרצה את כתובת התא
- column_num: מספר העמודה של התא שעבורו תרצה את הכתובת
- [abs_num]: ארגומנט אופציונלי שבו תוכל לציין אם תרצה שהתייחסות התא תהיה מוחלטת, יחסית או מעורבת.
- [a1]: ארגומנט אופציונלי שבו תוכל לציין אם אתה רוצה את ההפניה בסגנון R1C1 או בסגנון A1
- [sheet_text]: ארגומנט אופציונלי שבו תוכל לציין אם ברצונך להוסיף את שם הגיליון יחד עם כתובת התא או לא
כעת, ניקח דוגמא ונראה כיצד זה עובד.
נניח שיש מערך נתונים כפי שמוצג להלן, שבו יש לי את מזהה העובד, את שמו ואת המחלקה שלהם, ואני רוצה לדעת במהירות את כתובת התא המכילה את המחלקה למזהה העובד KR256.
להלן הנוסחה שתעשה זאת:
= כתובת (MATCH ("KR256", A1: A20,0), 3)
בנוסחה שלעיל, השתמשתי בפונקציה MATCH כדי לברר את מספר השורות המכיל את מזהה העובד הנתון.
ומכיוון שהמחלקה נמצאת בעמודה C, השתמשתי ב -3 כטענה השנייה.
נוסחה זו עובדת מצוין, אך יש לה חסרון אחד - היא לא תעבוד אם תוסיף את השורה שמעל מערך הנתונים או עמודה משמאל למערך הנתונים.
הסיבה לכך היא שכאשר אני מציין את הארגומנט השני (מספר העמודה) כ- 3, הוא מקודד קשה ולא ישתנה.
במקרה שאוסיף עמודה כלשהי משמאל למערך הנתונים, הנוסחה תספור 3 עמודות מתחילת גליון העבודה ולא מתחילת מערך הנתונים.
אז אם יש לך מערך נתונים קבוע ואתה צריך נוסחה פשוטה, זה יעבוד מצוין.
אבל אם אתה צריך שזה יהיה יותר הוכח מטומטם, השתמש באחד המפורט בסעיף הבא.
חיפוש והחזרה של כתובת התא באמצעות פונקציית CELL
בעוד שפונקציית ADDRESS נעשתה במיוחד כדי לתת לך את הפניה לתא של השורה ומספר העמודות שצוין, יש פונקציה נוספת שגם היא עושה זאת.
היא נקראת פונקציית CELL (והיא יכולה לתת לך הרבה יותר מידע על התא מאשר הפונקציה ADDRESS).
להלן התחביר של הפונקציה CELL:
= CELL (מידע_סוג, [הפניה])
איפה:
- מידע_סוג: המידע על התא שאתה רוצה. זו יכולה להיות הכתובת, מספר העמודה, שם הקובץ וכו '.
- [התייחסות]: ארגומנט אופציונלי שבו תוכל לציין את הפניה לתא שעבורה אתה צריך את פרטי התא.
כעת, בואו נראה דוגמה שבה תוכלו להשתמש בפונקציה זו כדי להרים את המבט ולקבל את הפניה לתא.
נניח שיש לך מערך נתונים כפי שמוצג להלן, ואתה רוצה לדעת במהירות את כתובת התא המכילה את המחלקה למזהה עובד KR256.
להלן הנוסחה שתעשה זאת:
= CELL ("כתובת", INDEX ($ A $ 1: $ D $ 20, MATCH ("KR256", $ A $ 1: $ A $ 20,0), 3))
הנוסחה לעיל היא פשוטה למדי.
השתמשתי בנוסחת INDEX כטענה השנייה כדי לקבל את המחלקה עבור מזהה העובד KR256.
ואז פשוט עטף אותו בתוך הפונקציה CELL וביקש ממנו להחזיר את כתובת התא של ערך זה שאני מקבל מהנוסחה INDEX.
עכשיו הנה ה סוד מדוע זה עובד - נוסחת INDEX מחזירה את ערך החיפוש כאשר אתה נותן לה את כל הארגומנטים הדרושים. אך יחד עם זאת, הוא גם יחזיר את הפניה לתא של אותו התא שנוצר.
בדוגמה שלנו, נוסחת INDEX מחזירה את "מכירות" כערך המתקבל, אך יחד עם זאת, תוכל גם להשתמש בה כדי לתת לך את הפניה לתא של ערך זה במקום את הערך עצמו.
בדרך כלל, כאשר אתה מזין את נוסחת INDEX בתא, הוא מחזיר את הערך מכיוון שזה מה שהוא צפוי לעשות. אך בתרחישים בהם נדרשת הפניה לתא, נוסחת INDEX תיתן לך את הפניה לתא.
בדוגמה זו, זה בדיוק מה שהוא עושה.
והחלק הטוב ביותר בשימוש בנוסחה זו הוא שהיא אינה קשורה לתא הראשון בגליון העבודה. המשמעות היא שאתה יכול לבחור כל מערך נתונים (שיכול להיות בכל מקום בגליון העבודה), להשתמש בנוסחת INDEX כדי לבצע בדיקה קבועה וזה עדיין ייתן לך את הכתובת הנכונה.
ואם אתה מוסיף שורה או עמודה נוספים, הנוסחה תתאים בהתאם ותתן לך את כתובת התא הנכונה.
אז אלו שתי נוסחאות פשוטות שניתן להשתמש בהן כדי להרים את המבט למעלה מצא והחזר את כתובת התא במקום הערך ב- Excel.
אני מקווה שמצאת הדרכה שימושית.
