מצא מיקומים, צור רשימות של מספרים, תן שמות לחלקים של נוסחה, והשאר רק את השורות הראשונות של רשימה.
Excel מודרני ומהירות · 5 תרגילים קצרים בגיליון אלקטרוני חי: כל נוסחה מוקלדת ידנית ולענות על שאלות שדורשות לנחש תוצאות או לאתר באגים. כל תשובה נבדקת מיד.
ארבע הפונקציות האלה הן אבני בניין קטנות. XMATCH מוצאת מיקום, SEQUENCE מייצרת מספרים, LET נותנת שמות לחלקים של נוסחה, ו-TAKE חותכת שורות מרשימה. כל אחת שימושית לבד. יחד הן מחליפות הרבה טריקים ישנים ומסורבלים.
XMATCH: איפה זה נמצא?
אם A2:A6 מכיל Mon, Tue, Wed, Tue, Fri, אז =XMATCH("Tue",A2:A6) מחזיר 2. היא מתאימה בדיוק אלא אם מציינים אחרת. הארגומנט הרביעי קובע את כיוון החיפוש: =XMATCH("Tue",A2:A6,0,-1) מחפש מלמטה ומחזיר 4. הארגומנט השלישי יכול לאפשר את הערך הקטן הבא (-1) או הגדול הבא (1), או תווים כלליים (2).
היא משתלבת בטבעיות עם INDEX, שמחזירה את הערך במיקום נתון. אם B2:B6 מכיל 40, 25, 60, 10 ו-35, אז =INDEX(B2:B6,XMATCH("Tue",A2:A6)) מחזיר 25, הערך שלצד ה-Tue הראשון. זה עושה את עבודת החיפוש, וגם עובד כשעמודת התשובות נמצאת משמאל לעמודת החיפוש.
הראשונה ממלאת בלוק בגודל 3 על 2 ב-1, 2 / 3, 4 / 5, 6. השנייה נותנת 10, 15, 20, 25. השלישית נותנת ארבעה תאריכים, כל אחד שבוע אחרי הקודם, החל מ-5 בינואר 2026 (מומלץ לעצב את התאים כתאריכים).
הארגומנטים הם שורות, עמודות, התחלה וצעד. רק הראשון נדרש.
LET: נותנים שמות לדברים
נניח ש-B2:B6 מכיל 40, 25, 60, 10 ו-35. כדי להציג כל אחד כחלק מהסכום, אפשר לכתוב =B2/SUM(B2:B6). עם LET נותנים שם לסכום פעם אחת: =LET(total,SUM(B2:B6),B2/total). התוצאה היא בערך 0.235.
אפשר לתת שם לכמה דברים, ולהשתמש בשמות קודמים בשמות מאוחרים יותר: =LET(total,SUM(B2:B6),share,B2/total,ROUND(share*100,1)) מחזיר 23.5, האחוז ש-40 מהווה מתוך 170. השמות מופיעים בזוגות (שם, ערך) והארגומנט האחרון הוא החישוב. שם חייב להתחיל באות ולא להיראות כמו כתובת תא או הפניה שורה-עמודה, ולכן כדאי להימנע מאותיות בודדות כמו c ו-r. היתרון הגדול מופיע בנוסחאות ארוכות: כל חלק מחושב פעם אחת, והנוסחה נקראת כמו משפט.
TAKE: שמירה על השורות הראשונות או האחרונות
=TAKE(A2:A8,3) מחזיר את שלוש השורות הראשונות ו-=TAKE(A2:A8,-3) את שלוש האחרונות. ארגומנט שלישי אופציונלי מגביל עמודות. השימוש הטוב ביותר הוא רשימת Top-N: =TAKE(SORT(B2:B8,1,-1),3) מחזיר את שלושת הערכים הגדולים ביותר. DROP עושה את ההפך ומסירה שורות.
הערת גרסה:XMATCH, SEQUENCE ו-LET עובדות מ-Excel 2021. TAKE דורשת Excel 2024 או Microsoft 365. בגרסה ישנה מופיעה #NAME?.
שילוב הכול
ארבע הפונקציות משתלבות היטב. נניח שרוצים רשימה ממוספרת של שלושת הערכים הגדולים ביותר ב-B2:B8. SEQUENCE יכולה ליצור את המספרים 1 עד 3 לצד TAKE של הערכים הממוינים:
=SEQUENCE(3)
=TAKE(SORT(B2:B8,1,-1),3)
מניחים את הנוסחה הראשונה ב-D2 והשנייה ב-E2. שתיהן דולפות שלוש שורות, ולכן הדירוג 1 נמצא לצד הערך הגדול ביותר.
אם צריך את אותה רשימה ממוינת פעמיים, כדאי לתת לה שם בעזרת LET, כך ש-Excel ממיין פעם אחת והנוסחה אומרת מה שהיא עושה. כדאי לתת שמות שמתארים את הנתונים, כמו sales או topThree, ולא x ו-y.