چگونه از تابع زیرمجموعه اکسل استفاده کنیم

2024 نویسنده: Abigail Brown | [email protected]. آخرین اصلاح شده: 2024-01-07 19:02

وقتی کاربرگ اکسل شما حاوی ردیف‌های پنهان، داده‌های فیلتر شده یا داده‌های گروه‌بندی شده است، از تابع Excel SUBTOTAL استفاده کنید. تابع SUBTOTAL می تواند مقادیر پنهان را در محاسبات شامل یا حذف کند. اکسل علاوه بر یافتن کل یک گروه داده، می تواند میانگین، حداکثر، حداقل، انحراف استاندارد و واریانس داده های شما را محاسبه کند. در اینجا نحوه درج جمع‌های فرعی در اکسل آمده است.

دستورالعمل‌های این مقاله برای Excel برای Microsoft 365، Excel 2019 و Excel 2016 اعمال می‌شود.

سینتکس تابع SUBTOTAL

از تابع SUBTOTAL در اکسل برای خلاصه کردن مقادیر در یک کاربرگ به روش های مختلف استفاده کنید. به ویژه زمانی مفید است که کاربرگ شما حاوی ردیف‌های مخفی باشد که می‌خواهید در محاسبه لحاظ کنید.

نحو تابع SUBTOTAL این است: SUBTOTAL(تابع_تعداد، ref1، ref2،…)

function_num مورد نیاز است و نوع عملیات ریاضی مورد استفاده برای جمع فرعی را مشخص می کند. تابع SUBTOTAL می تواند اعداد را اضافه کند، میانگین مقدار اعداد انتخاب شده را محاسبه کند، مقادیر حداکثر و حداقل را در یک محدوده بیابد، تعداد مقادیر را در یک محدوده انتخاب شده بشمارد و موارد دیگر.

عملکرد SUBTOTAL سلول هایی را که حاوی داده نیستند و سلول هایی با مقادیر غیر عددی نادیده می گیرد.

این آرگومان یک عدد است و بستگی به این دارد که آیا می‌خواهید ردیف‌های مخفی را در نتیجه اضافه کنید یا ردیف‌های پنهان را از نتیجه حذف کنید. این ردیف ها ممکن است به صورت دستی توسط یک فیلتر پنهان یا پنهان شوند.

آگومان های function_num عبارتند از:

کارکرد	function_num	function_num
	(شامل مقادیر پنهان)	(بدون مقادیر پنهان)
متوسط	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 با ردیف های پنهان

توابع اکسل را می توان به صورت دستی یا با کمک کادر محاوره ای Function Arguments وارد کرد. برای نشان دادن نحوه وارد کردن دستی تابع با استفاده از نوار فرمول، مثال زیر از آرگومان COUNT function_num برای شمارش تعداد مقادیر در ردیف‌های قابل مشاهده و در ردیف‌های قابل مشاهده و پنهان استفاده می‌کند.

برای استفاده از تابع SUBTOTAL برای شمارش تعداد ردیف‌های یک کاربرگ:

1. با یک کاربرگ که حاوی چندین ردیف داده است شروع کنید.
2. سلولی را انتخاب کنید که حاوی تعداد ردیف های قابل مشاهده باشد.

3. در نوار تابع، =SUBTOTAL را وارد کنید. همانطور که شما تایپ می کنید، اکسل یک تابع را پیشنهاد می کند. روی تابع SUBTOTAL دوبار کلیک کنید.

   برای استفاده از کادر محاوره‌ای Function Arguments برای وارد کردن تابع SUBTOTAL، به Formulas بروید و را انتخاب کنید و Math & Trig >را انتخاب کنید. SUBTOTAL.

   

4. در منوی کشویی که ظاهر می شود، روی آرگومان 102 – COUNT function_num دوبار کلیک کنید.

   

5. یک کاما (,). تایپ کنید

   

6. در کاربرگ، سلول‌هایی را انتخاب کنید که در فرمول گنجانده شوند.

   

7. Enter را فشار دهید تا نتیجه را در سلولی که در مرحله ۲ انتخاب کرده‌اید ببینید.

   

8. سلولی را انتخاب کنید که شامل تعداد ردیف های قابل مشاهده و پنهان باشد.

9. در نوار تابع، =SUBTOTAL را وارد کنید. همانطور که شما تایپ می کنید، اکسل یک تابع را پیشنهاد می کند. روی تابع SUBTOTAL دوبار کلیک کنید.

10. در منوی کشویی که ظاهر می شود، روی آرگومان 2 – COUNT function_num دوبار کلیک کنید، سپس یک کاما (,). را تایپ کنید.

11. در کاربرگ، سلول‌هایی را انتخاب کنید که در فرمول گنجانده شوند، سپس Enter را فشار دهید.

    

12. پنهان کردن چندین ردیف داده. در این مثال، ردیف‌هایی با فروش کمتر از 100000 دلار پنهان شدند.

    

استفاده از تابع SUBTOTAL با داده های فیلتر شده

استفاده از تابع SUBTOTAL در داده های فیلتر شده، داده های ردیف هایی را که توسط فیلتر حذف شده اند نادیده می گیرد. هر بار که معیار فیلتر تغییر می کند، تابع دوباره محاسبه می شود تا مجموع فرعی ردیف های قابل مشاهده را نشان دهد.

برای استفاده از تابع SUBTOTAL برای مشاهده تفاوت در نتایج محاسبات در هنگام فیلتر کردن داده ها:

1. فرمول SUBTOTAL ایجاد کنید. به عنوان مثال، فرمول هایی برای تعیین مقادیر فرعی و میانگین داده های فیلتر شده ایجاد کنید.

   مهم نیست که از آرگومان function_num برای ردیف های قابل مشاهده یا پنهان استفاده کنید. هر دو آرگومان یک نتیجه را در داده های فیلتر شده ارائه می دهند.

   

2. هر سلولی در مجموعه داده را انتخاب کنید.

3. به Home برو، سپس Sort & Filter > Filter را انتخاب کنید..

   

4. برای فیلتر کردن داده‌های کاربرگ از فلش‌های کشویی استفاده کنید.

   

5. توجه کنید که هر بار که معیارهای فیلتر مختلف را انتخاب می کنید، مقادیر چگونه تغییر می کنند.

   

استفاده از تابع SUBTOTAL با داده های گروه بندی شده

هنگامی که داده ها گروه بندی می شوند، راهی برای اعمال تابع SUBTOTAL برای هر گروه جداگانه و سپس محاسبه کل کل برای کل مجموعه داده وجود دارد.

1. هر سلولی در مجموعه داده را انتخاب کنید.

2. Data > Subtotal را انتخاب کنید تا کادر گفتگوی Subtotal باز شود..

   

3. در هر تغییر فلش کشوییرا انتخاب کنید و گروه بندی را انتخاب کنید که هر زیرجمع به آن محاسبه می شود.
4. پیکان کشویی Use function را انتخاب کنید و یک function_num را انتخاب کنید.
5. در افزودن مجموع فرعی به فهرست ، ستونی را که فرمول اعمال می شود انتخاب کنید.

6. OK را انتخاب کنید.

   

7. مجموع فرعی برای هر گروه داده درج می شود، و کل کل در پایین مجموعه داده درج می شود.

   

8. برای تغییر تابع_num، هر سلولی را در مجموعه داده هایلایت کرده و Data > Subtotal را انتخاب کنید. سپس، در کادر محاوره ای Subtotal انتخاب های خود را انجام دهید.