SUBTOTAL באקסל: סכומים שמדלגים על סכומי ביניים ומסננים

=SUBTOTAL(9,C2:C8) מחברת את המספרים ב-C2:C8, כמו SUM, עם שני הבדלים: היא מדלגת על כל נוסחת SUBTOTAL אחרת בתוך הטווח, והיא מדלגת על שורות שמוסתרות על ידי מסנן. הארגומנט הראשון, 9, אומר איזה חישוב לבצע.

נוסחאותאיפוס

C8fx

=SUBTOTAL(9,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Check	Result
2	North	Apple	120		SUM of C2:C7	890
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South total		245
8	Grand total		445

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

הסכום הכולל ב-C8 עובר על כל העמודה, כולל שורות סכומי הביניים, ועדיין מציג 445: SUBTOTAL משאירה בחוץ את C4 ואת C7 כי יש בהם נוסחאות SUBTOTAL. F2 עושה את אותו הדבר עם SUM ומציג 890, כל מכירה נספרת פעמיים. כש-SUBTOTAL נמצאת בכל שורת סכום, אפשר להוסיף או להזיז קבוצות בלי לכתוב מחדש את הסכום הכולל.

מספרי הפונקציה של SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], ...)

כשמקלידים =SUBTOTAL(, אקסל מציג את הרשימה הזו, כך שאין צורך לזכור אותה בעל פה. 9 ו-109 (SUM), 1 (AVERAGE) ו-103 (ספירת שורות גלויות) הם הנפוצים ביותר.

נוסחאותאיפוס

F2fx

=SUBTOTAL(1,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Calculation	Result
2	North	Apple	120		AVERAGE (1)	88.33
3	North	Pear	80		COUNTA (3)	6
4	South	Apple	200		MAX (4)	200
5	South	Pear	45		MIN (5)	30
6	East	Apple	55		Visible rows (103)	6
7	East	Plum	30		SUM (109)	530

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

שום דבר לא מוסתר כאן, ולכן כל שורה שווה לפונקציה הרגילה: ממוצע של 88.33, 6 שורות, MAX של 200, MIN של 30 ו-SUM של 530. ההבדל נראה רק כשיש שורות מוסתרות, ועל זה הסעיף הבא.

SUBTOTAL 9 מול 109, ושורות מסוננות

הפעילו מסנן עם נתונים > סינון (Ctrl+Shift+L, או Cmd+Shift+F ב-Mac), ואז בחרו North ברשימה הנפתחת של Region. השורות של האזורים האחרים מוסתרות:

• =SUM(C2:C7) עדיין מחברת את כל שש השורות.
• =SUBTOTAL(9,C2:C7) ו-=SUBTOTAL(109,C2:C7) מחברות רק את השורות הגלויות של North.
• =SUBTOTAL(103,A2:A7) סופרת את השורות שנשארו על המסך: 2, אותו מספר כמו "2 of 6 records found" (נמצאו 2 מתוך 6 רשומות) בשורת המצב.

שתי המשפחות נבדלות רק בשורות שאתם מסתירים ידנית (בוחרים שורות, לחיצה ימנית > הסתר). 9 עדיין מחברת אותן; 109 לא. אם הסכום צריך תמיד להתאים למה שעל המסך, השתמשו ב-109. אם אתם מסתירים שורות רק כדי לסדר את התצוגה ועדיין רוצים אותן בסכום, השתמשו ב-9.

SUBTOTAL עובדת רק על שורות. עמודות מוסתרות תמיד נכללות, ולכן =SUBTOTAL(109,B2:G2) לאורך שורה מחברת גם עמודות מוסתרות.

הדרך המהירה ביותר לקבל SUBTOTAL היא הכפתור סכום אוטומטי כשמסנן פעיל: אקסל כותב =SUBTOTAL(9,...) במקום SUM. נתונים > סכום ביניים הולך רחוק יותר: ברשימה שממוינת לפי עמודה, הוא מוסיף שורת סכום מתחת לכל קבוצה וסכום כולל, כולם עם SUBTOTAL, ובנוסף כפתורי חלוקה לרמות לכיווץ הקבוצות.

AGGREGATE: SUBTOTAL שיכולה לדלג על שגיאות

אם תא אחד בטווח מכיל שגיאה, SUM ו-SUBTOTAL מחזירות את השגיאה הזו. AGGREGATE (Excel 2010 ואילך) היא SUBTOTAL עם ארגומנט נוסף של אפשרויות; אפשרות 6 מתעלמת מערכי שגיאה.

נוסחאותאיפוס

F3fx

=AGGREGATE(9,6,C2:C7)

	A	B	C	D	E	F
1	Region	Item	Sales		Formula	Result
2	North	Apple	120		SUBTOTAL	#N/A
3	North	Pear	#N/A		AGGREGATE, ignore errors	450
4	South	Apple	200		AGGREGATE, MAX	200
5	South	Pear	45
6	East	Apple	55
7	East	Plum	30

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

ב-C3 יש #N/A, ולכן גם F2 מציג #N/A. F3 מתעלם ממנו ומחבר את חמשת האחרים: 450. הארגומנט הראשון שלה משתמש באותם מספרים כמו SUBTOTAL (9 הוא SUM, 4 הוא MAX). אפשרויות נוספות: 5 מתעלמת משורות מוסתרות, 7 משורות מוסתרות ומשגיאות, ו-3 משורות מוסתרות, משגיאות ומנוסחאות SUBTOTAL ו-AGGREGATE מקוננות. החליפו את C3 במספר ו-F2 יציג את אותו סכום כמו F3.

תרגול: סכום כולל מעל סכומי ביניים

נוסחאותאיפוס

C9fx

	A	B	C
1	Region	Item	Sales
2	North	Apple	120
3	North	Pear	80
4	North total		200
5	South	Apple	200
6	South	Pear	45
7	South	Plum	60
8	South total		305
9	Grand total

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

תורכם: ברשימה יש סכום ביניים מתחת לכל אזור. כתבו ב-C9 סכום כולל שמכסה את C2:C8 בלי לספור את שורות סכומי הביניים פעמיים.

בדיקההצגת התשובה

למה סכום עם SUBTOTAL עדיין שגוי

• סכומי הקבוצות משתמשים ב-SUM. SUBTOTAL מדלגת על נוסחאות SUBTOTAL אחרות בתוך הטווח שלה, לא על נוסחאות SUM. סכום קבוצה שנכתב כ-=SUM(C2:C3) נספר שוב. שנו כל שורת סכום ל-SUBTOTAL.
• השורות הוסתרו ידנית ומספר הפונקציה הוא 9. השתמשו ב-109.
• הנתונים בעמודות ולא בשורות. על עמודות מוסתרות אף פעם לא מדלגים.
• אתם צריכים תנאי, לא מסנן. SUBTOTAL הולכת אחרי מה שהמסנן מסתיר. כדי לסכם את North בלי לסנן, השתמשו ב-SUMIF. לסיכום של כל הקבוצות בבת אחת, טבלת ציר עושה את זה בלי שורות סכום בתוך הנתונים.