כאשר גליון העבודה שלך ב-Excel מכיל שורות נסתרות, נתונים מסוננים או נתונים מקובצים, השתמש בפונקציית SUBTOTAL של Excel. הפונקציה SUBTOTAL יכולה לכלול או לא לכלול ערכים מוסתרים בחישובים. בנוסף למציאת סך כל קבוצת הנתונים, Excel יכול לחשב את הממוצע, המקסימום, המינימום, סטיית התקן והשונות של הנתונים שלך. הנה איך להוסיף סכומי ביניים ב-Excel.
ההוראות במאמר זה חלות על Excel עבור Microsoft 365, Excel 2019 ו-Excel 2016.
תחביר של הפונקציה SUBTOTAL
השתמש בפונקציה SUBTOTAL ב-Excel כדי לסכם את הערכים בגליון עבודה בדרכים שונות. זה מועיל במיוחד כאשר גליון העבודה שלך מכיל שורות נסתרות שברצונך לכלול בחישוב.
התחביר של הפונקציה SUBTOTAL הוא: SUBTOTAL(function_num, ref1, ref2, …)
הארגומנט function_num נדרש ומציין את סוג הפעולה המתמטית לשימוש עבור סכום המשנה. הפונקציה SUBTOTAL יכולה להוסיף מספרים, לחשב את הערך הממוצע של המספרים שנבחרו, למצוא את הערכים המקסימליים והמינימליים בטווח, לספור את מספר הערכים בטווח שנבחר ועוד.
הפונקציה SUBTOTAL מתעלמת מתאים שאינם מכילים נתונים ותאים עם ערכים לא מספריים.
ארגומנט זה הוא מספר ותלוי אם ברצונך לכלול שורות נסתרות בתוצאה או לא לכלול שורות נסתרות מהתוצאה. שורות אלה עשויות להיות מוסתרות ידנית או מוסתרות על ידי מסנן.
הארגומנטים function_num כוללים:
| מטלת פונקציה | function_num | function_num |
|---|---|---|
| (כולל ערכים מוסתרים) | (לא כולל ערכים מוסתרים) | |
| AVERAGE | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCT | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
ארגומנטי ההתייחסות function_num 1 עד 11 כוללים רק ערכים בשורות נסתרות בעת שימוש בפקודה Hide כדי להסתיר שורות. בעת שימוש בפקודה Filter, חישובי SUBTOTAL אינם כוללים תוצאות סינון נסתרות.
ארגומנט ref1 נדרש. אלו הם התאים המשמשים לחישוב התוצאות של הארגומנט function_num שנבחר. ארגומנט זה יכול להיות ערך, תא בודד או טווח של תאים.
הארגומנטים ref2, … הם אופציונליים. אלו הם תאים נוספים שנכללים בחישוב.
השתמש בפונקציית SUBTOTAL עם שורות נסתרות
ניתן להזין פונקציות Excel באופן ידני או בעזרת תיבת הדו-שיח Function Arguments. כדי להמחיש כיצד להזין את הפונקציה באופן ידני באמצעות שורת הנוסחאות, הדוגמה הבאה משתמשת בארגומנט COUNT function_num כדי לספור את מספר הערכים בשורות גלויות ובשורות גלויות וגם בשורות מוסתרות.
כדי להשתמש בפונקציה SUBTOTAL כדי לספור את מספר השורות בגליון עבודה:
- התחל עם גליון עבודה שמכיל שורות מרובות של נתונים.
- בחר את התא שיכיל את ספירת השורות הגלויות.
-
בשורת הפונקציות, הזן =SUBTOTAL. בזמן ההקלדה, Excel מציע פונקציה. לחץ פעמיים על הפונקציה SUBTOTAL.
כדי להשתמש בתיבת הדו-שיח Function Arguments כדי להיכנס לפונקציה SUBTOTAL, עבור אל Formulas ובחר Math & Trig >SUBTOTAL.
Image -
בתפריט הנפתח שמופיע, לחץ פעמיים על הארגומנט 102 – COUNT function_num.
Image -
הקלד פסיק (,).
Image -
בגליון העבודה, בחר את התאים לכלול בנוסחה.
Image -
הקש Enter כדי לראות את התוצאה בתא שבחרת בשלב 2.
Image - בחר את התא שיכיל את ספירת השורות הגלויות והמוסתרות.
- בשורת הפונקציות, הזן =SUBTOTAL. בזמן ההקלדה, Excel מציע פונקציה. לחץ פעמיים על הפונקציה SUBTOTAL.
- בתפריט הנפתח שמופיע, לחץ פעמיים על הארגומנט 2 – COUNT function_num, ולאחר מכן הקלד פסיק (,).
-
בגליון העבודה, בחר את התאים לכלול בנוסחה, ולאחר מכן הקש Enter.
Image -
הסתר מספר שורות של נתונים. בדוגמה זו, הוסתרו שורות עם מכירות מתחת ל-$100,000 בלבד.
Image
השתמש בפונקציית SUBTOTAL עם נתונים מסוננים
שימוש בפונקציה SUBTOTAL על נתונים מסוננים מתעלם מנתונים בשורות שהוסרו על ידי המסנן. בכל פעם שקריטריוני הסינון משתנים, הפונקציה מחשבת מחדש כדי להציג את סכום המשנה עבור השורות הגלויות.
כדי להשתמש בפונקציית SUBTOTAL כדי לראות את ההבדלים בתוצאות החישוב בזמן סינון נתונים:
-
צור נוסחאות SUBTOTAL. לדוגמה, צור נוסחאות כדי לקבוע את ערכי הביניים והממוצעים של הנתונים המסוננים.
לא משנה אם אתה משתמש בארגומנט function_num עבור שורות גלויות או נסתרות. שני הארגומנטים מספקים את אותה תוצאה בנתונים מסוננים.
Image - בחר תא כלשהו במערך הנתונים.
-
עבור אל Home, ולאחר מכן בחר מיין & סינון > Filter.
Image -
השתמש בחצים הנפתחים כדי לסנן את נתוני גליון העבודה.
Image -
שים לב כיצד הערכים משתנים בכל פעם שאתה בוחר קריטריוני סינון שונים.
Image
השתמש בפונקציית SUBTOTAL עם נתונים מקובצים
כאשר הנתונים מקובצים, יש דרך להחיל את הפונקציה SUBTOTAL על כל קבוצה בודדת ולאחר מכן לחשב את הסכום הכולל של כל מערך הנתונים.
- בחר תא כלשהו במערך הנתונים.
-
בחר Data > Subtotal כדי לפתוח את תיבת הדו-שיח Subtotal.
Image - בחרו את החץ הנפתח בכל שינוי ב ובחרו את הקיבוץ שאליו יחושב כל סכום משנה.
- בחר בחץ הנפתח השתמש בפונקציה ובחר function_num.
- ברשימה Add subtotal to, בחר את העמודה שבה הנוסחה תיושם.
-
בחר OK.
Image -
סיכומי משנה מוכנסים עבור כל קבוצת נתונים, וסך כולל מוכנס בתחתית מערך הנתונים.
Image - כדי לשנות את function_num, סמן כל תא במערך הנתונים ובחר Data > Subtotal. לאחר מכן, בצע את הבחירות שלך בתיבת הדו-שיח Subtotal.
