הגדר טווח על בסיס ערך התא

כדי להגדיר טווח המבוסס על ערך בתא אחר, תוכל להשתמש ב פונקציית INDEX . בדוגמה המוצגת, הנוסחה ב- J7 היא:

= SUM (firstcell: INDEX (data,rows,cols))

היכן 'הנתונים' הם טווח בשם B5: G9.

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

להצטיין במספר קטן מ- או שווה ל-

בתוך פונקציית הסכום, ההתייחסות הראשונה היא פשוט התא הראשון בטווח המכסה את כל התאים האפשריים:

 
= SUM (C5: INDEX (data,J5,J6))

כדי לקבל את התא האחרון, אנו משתמשים ב- INDEX. כאן, אנו נותנים ל- INDEX את טווח בשם 'data', שהוא טווח הערכים המרבי האפשרי, וגם הערכים מ- J5 (שורות) ו- J6 (עמודות). INDEX אינו מחזיר טווח, הוא מחזיר רק תא בודד במיקום זה, E9 בדוגמה:

ספור אם התא מכיל טקסט ספציפי

 
= SUM (C5:

הנוסחה המקורית מצטמצמת ל:

 
 INDEX (data,J5,J6) // returns E9

שמחזירה 300, סכום כל הערכים ב- C5: E9.

כיצד להתאים אוטומטית את כל העמודות ב- Excel

הנוסחה ב- J8 כמעט זהה, אך משתמשת מְמוּצָע במקום סְכוּם לחישוב ממוצע. כאשר משתמש משנה ערכים ב- J5 או J6 הטווח מתעדכן ותוצאות חדשות מוחזרות.

חלופי עם OFFSET

אתה יכול לבנות נוסחאות דומות עם פונקציית OFFSET , המוצג להלן:

 
= SUM (C5:E9)

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