שליפת ערך שנמצא בהצטלבות של שורה ועמודה

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

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

שלב ראשון – הגדרת שמות

נסמן את הטווח הרצוי, ובכרטיסיית 'נוסחאות' נבחר בקבוצת 'שמות מוגדרים' > 'צור מתוך קטע נבחר':

האפשרות הזאת מאפשרת לנו לתת שמות לטווחים, בלי שנצרך להקליד אותם ידנית, על פי הכותרות הצמודות למידע,
כלומר – אקסל מבין שהשמות שאנחנו רוצים לתת לשורות הוא שמות הסניפים, משום שעמודת הסניף מהווה כותרות לשורות,
ואילו לעמודות אנחנו רוצים לתת את שמות המוצרים, משום שהם מהווים כותרות לעמודות,
במקרה שלנו – אנחנו רוצים להחיל את השם 'ירושלים' על המחירים שבשורה 2, משום שהם שייכים לסניף ירושלים,
ואת השם 'מקלדת' על הערכים שמופיעים בעמודה C, משום שהם מחירי המקלדות.
יפתח החלון הבא:

אנחנו רואים שאקסל זיהה באופן אוטומטי שהכותרות נמצאות בשורה העליונה (במקרה שלנו – פריטים),
ובעמודה הימנית (במקרה שלנו – שמות הסניפים). וכמובן שתמיד אפשר לתקן, אם אקסל טעה בזיהוי…
מכיוון שאקסל זיהה נכון – נאשר.
אם נבדוק בתיבת השם, נוכל לראות את השמות שאקסל נתן:

שימו לב שעבור כותרות עם רווח, אקסל הוסיף באופן אוטומטי קו תחתי, לאחד את המילים, כדי לקבל שם תא תקני.

אז מה עכשיו?

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

נוסיף רווח בין שם הסניף למוצר:

ונלחץ על אנטר.
נקבל את הערך שנמצא בהצטלבות שתי הכותרות:

ובואו נעשה את זה יותר מעניין:

במקום להקליד, נבחר מתוך תיבת רשימה.
בעמודות מרוחקות ניצור את שתי הרשימות: באקסל 365, זה פשוט – נלחץ על הסימן שווה, ונסמן את הטווח הראשון של הסניפים:

כדי ליצור את רשימת המוצרים האופקית, נקיף את הנוסחה ב TRANSPOSE, לקבלת רשימה אנכית:

ורגע לפני שאנחנו יוצרים את אימות הנתונים – שימו לב לשמות

בעוד שבטבלה שמות הערים שמורכבים משני שמות מופיעים עם רווח, בתיבת השם הם מופיעים עם קו תחתי, כלומר – 'תל אביב' לעומת 'תל_אביב'.
את זה נתקן עם הפונקציה SUBSTITUTE שמיועדת להחליף בתוך מחרוזת תווים בתווים אחרים,
ולמרות שברשימת המוצרים אין לנו מוצרים שמורכבים משתי מילים, אקיף גם אותה ב SUBSTITUTE, למוצרים עתידיים:

אז עכשיו יש לנו שתי רשימות, שאותן ניתן להכניס לתוך 'אימות נתונים' > 'רשימה'
(לא יודעים איך יוצרים תיבת נפתחת באימות נתונים? קראו במאמר הזה)

ועוד טריק קטן כדי שיהיה אפשר לשלוף את המידע

אם ננסה להחיל את הנוסחה

=F2 G2

נקבל ערך ריק, משום שהנוסחה מפנה לערכים בתוך תאים, ולא לטווחים שמזוהים על ידי השמות האלה.
אז איך נהפוך את הערך הכתוב בתא לשם שמפנה לטווח? באמצעות הפונקציה INDIRECT, שמאפשרת ליצור הפניה עקיפה,
כלומר – אני מפנה לערך שנמצא בתא, והוא בתורו מפנה לטווח בעל אותו השם, וזו הנוסחה:

=INDIRECT(F2) INDIRECT(G2)

ועכשיו, כל שינוי ערך ברשימה הנפתחת, יתן לנו את הערך המתאים: