Excel SUM ו טווח דינמי טווח בלתי מוגבל

ל- Microsoft Excel יש כמה טריקים מגניבים והשימוש בתבניות SUM ו- INDIRECT דינמיים של טווחים הן רק שתי דרכים לתמרן בקלות את הנתונים שיש לך.

SUM - סקירה כללית של פורמולה לא ישירה

שימוש בפונקציה INDIRECT בנוסחאות Excel מקל על שינוי טווח הפניות התאים המשמשות בנוסחה מבלי לערוך את הנוסחה עצמה.

ניתן להשתמש ב- INDIRECT במספר פונקציות המקבלות התייחסות לתא כארגומנט, כגון פונקציות OFFSET ו- SUM.

במקרה האחרון, שימוש ב- INDIRECT כארגומנט עבור הפונקציה SUM יכול ליצור טווח דינמי של הפניות לתא שהפונקציה SUM תוסיף.

ה- INDIRECT עושה זאת על-ידי התייחסות לנתונים בתאים באופן עקיף באמצעות מיקום ביניים.

דוגמא: SUM - נוסחה ישירה המשמשת לכלל טווח דינמי של ערכים

דוגמה זו מבוססת על הנתונים שמופיעים בתמונה לעיל.

הנוסחה SUM - INDIRECT שנוצרה באמצעות שלבי ההנחיות שלמטה היא:

= SUM (עקיף ("D" ו- ​​E1 & ": D" ו- ​​E2))

בנוסחה זו, הטענה של פונקציה INDIRECT מקוננת מכילה הפניות לתאים E1 ו- E2. המספרים בתאים אלה, 1 ו 4, בשילוב עם שאר הוויכוח של INDIRECT, יוצרים את התאים הפניות D1 ו- D4.

כתוצאה מכך, טווח המספרים הסתכם על ידי הפונקציה SUM הוא הנתונים הכלולים בטווח של תאים D1 ל D4 - שהוא 50.

על ידי שינוי המספרים הממוקמים בתאים E1 ו- E2; עם זאת, טווח להיות הסתכם יכול להיות שונה בקלות.

דוגמה זו תשתמש תחילה בנוסחה לעיל כדי לסכם את הנתונים בתאים D1: D4 ולאחר מכן לשנות את טווח סיכם ל D3: D6 מבלי לערוך את הנוסחה בתא F1.

01 מתוך 03

הזנת הנוסחה - אפשרויות

האפשרויות להזנת הנוסחה כוללות:

• הקלדת הנוסחה לעיל ישירות לתוך F1 התא הקשה על המקש Enter במקלדת
• הזנת הפונקציה INDIRECT כארגומנט באמצעות תיבת הדו-שיח של פונקציית SUM

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

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

השלבים הבאים משתמשים בתיבת הדו-שיח SUM כדי להזין את הנוסחה.

הזנת נתוני המדריך

נתוני תא D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

1. הזן את הנתונים הבאים בתאים D1 עד E2

החל את SUM - פורמולה עקיפה - פתיחת תיבת הדו שיח SUM פונקציה תיבת הדו שיח

1. לחץ על תא F1 - זה המקום שבו התוצאות של דוגמה זו יוצגו
2. לחץ על הכרטיסייה נוסחאות בתפריט הסרט
3. בחר מתמטיקה & Trig מתוך הסרט כדי לפתוח את הרשימה הנפתחת פונקציה
4. לחץ על SUM ברשימה כדי לפתוח את תיבת הדו-שיח של הפונקציה

02 מתוך 03

כניסה לתכונה 'עקיבה' - לחץ כדי להציג תמונה גדולה יותר

יש להזין את הנוסחה העקיבה כארגומנט עבור הפונקציה SUM.

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

לכן, יש להזין את הפונקציה INDIRECT באופן ידני בשורה Number1 בתיבת הדו-שיח 'פונקציית SUM'.

1. בתיבת הדו-שיח, לחץ על השורה Number1
2. הזן את הפונקציה INDIRECT הבאה: ("D" ו- ​​"E"
3. לחץ על אישור כדי להשלים את הפונקציה ולסגור את תיבת הדו-שיח
4. המספר 50 צריך להופיע בתא F1 שכן זהו סך של הנתונים הממוקמים בתאים D1 עד D4
5. כאשר אתה לוחץ על התא F1 הנוסחה המלאה = SUM (עקיף ("D" & E1 & ": D" & E2)) מופיע בסרגל הנוסחה מעל גליון העבודה

לשבור את העקיפה פונקציה

כדי ליצור טווח דינמי בעמודה D תוך שימוש ב- INDIRECT, עלינו לשלב את האות D בארגומנט של הפונקציה INDIRECT עם המספרים הכלולים בתאים E1 ו- E2.

זה נעשה על ידי הדברים הבאים:

• אמפרסנד ( & ) משמש לשרשור או להצמדת נתוני טקסט (במקרה זה האות D) עם הפניה לתא (E1 ו- E2)
• יתר על כן, נתוני טקסט זה להיות משורשר עם הפניות תאים חייב להיות מוקף מרכאות כפולות ( "" )
• לבסוף, נקודות הקצה של הטווח מופרדות על ידי המעי הגס (:)

לכן, נקודת ההתחלה של הטווח מוגדרת על ידי התווים: "D" ו- ​​E1 .

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

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

"D" & E1 & ": D" & E2

03 מתוך 03

שינוי דינמי בטווח הפעולה של SUM

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

על ידי הכללת הפונקציה INDIRECT בנוסחה, שינוי המספרים בתאים E1 ו- E2 ישנה את טווח התאים הנקראים על ידי פונקציית ה- SUM.

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

1. לחץ על תא E1
2. הקלד את המספר 3
3. הקש על המקש Enter במקלדת
4. לחץ על תא E2
5. הקלד את המספר 6
6. הקש על המקש Enter במקלדת
7. התשובה בתא F1 צריכה להשתנות ל 90 - המהווה את סך המספרים הכלולים בתאים D3 ל D6
8. בדוק את הנוסחה על ידי שינוי התוכן של תאים B1 ו- B2 לכל המספרים בין 1 ל -6

עקיבה ואת #REF! ערך שגיאה

#REF! ערך השגיאה יופיע בתא F1 אם הארגומנט של הפונקציה INDIRECT:

• אינו אזכור תא חוקי
• מכיל הפניה חיצונית לחוברת עבודה אחרת, וחוברת העבודה אינה פתוחה
• מתייחס לטווח תאים מחוץ לגבולות גליון העבודה (שורה 1,048,576 או עמודה XFD)