تابع SUBTOTAL از توابع ریاضی (Math & Trig) بوده و محاسبات (جمع، میانگین، ماکزیمم و…) محدوده فیلتر شده و یا دارای سلول‌های مخفی را انجام میدهد. همچنین از جمع‌های فرعی داخل محدوده صرف‌نظر کرده تا از حاصل مضاعف جلوگیری کند.
هدف تابع
محاسبات محدوده فیلتر شده و یا دارای سلول‌های مخفی
خروجی
حاصل تابع مشخص شده
ساختار
=SUBTOTAL(function_num,ref1,[ref2],...)
آرگومان‌ها
function_num: کد تابع و شرایط مدنظر
ref1: مرجع یا محدوده نام‌گذاری شده
ref2 [اختیاری]: مرجع یا محدوده نام‌گذاری شده
کاربرد
علیرغم نام SUBTOTAL، این تابع می‌تواند محاسبات توابع دیگری را بصورت ویژه انجام میدهد. SUBTOTAL زمانیکه محدوده فیلتر شده، حاوی داده مخفی و یا دارای جمع‌های فرعی باشد به کمکمان میاید. رفتار این تابع توسط آرگومان اول تعیین می‌شود که کدهای آن در جدول زیر لیست شده است:
| تابع | محاسبه داده‌های فیلتر شده | محاسبه داده‌های فیلتر/مخفی شده |
| 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 |
برای مثال، فرض کنید میخواهیم مجموع ارزش انبار فیلتر شده را بدست آوریم. تابع SUM در این شرایط کارایی نداشته و حتی موارد مخفی شده توسط فیلتر را هم جمع میکند. در این شرایط، تابع SUBTOTAL فقط موارد فیلتر شده را مدنظر قرار میدهد.
=SUBTOTAL(9,D5:D10)
ترجمه: مقادیر قابل رویت در محدوده D5 تا D10 را جمع کن.
تابع SUBTOTAL در مثال فوق، سلول‌های D5 تا D7 را جمع کرده و سلول‌های D8 الی D10 را نادیده میگیرد. همچنین در مواقعی که سلول مخفی در محدوده یا جدول داریم، SUBTOTAL از طریق آرگومان اول خود به ما حق انتخاب میدهد. فرض کنید در محدوده B3 تا B12 سلول‌های مخفی وجود دارد و میخواهیم دو حالت را بررسی کنیم. فرض اول اینکه خودمان از سلول مخفی شده اطلاع داریم و نیازی به شمارش آن نیست و فرض دوم اینکه بصورت ناخواسته مخفی شده‌اند و در محاسبات به آن نیاز داریم:
همانطور که پیداست، ۳ سلول در این محدوده مخفی شده‌اند. علاوه بر این‌ها، SUBTOTAL از جمع‌های فرعی موجود در محدوده نیز صرف‌نظر میکند. برای مثال در تصویر زیر به دنبال جمع کل ارزش انبار هستیم:
همانطور که در تصویر ملاحظه می‌کنید، SUBTOTALهای فرعی در حاصل تابع تاثیری ندارند که این امر باعث جلوگیری جمع مضاعف می‌شود.
*در نسخه‌های جدید اکسل، این محدودیت برای تابع SUM رفع شده و با کلیدهای ترکیبی “alt و =” فقط مقادیر سلول‌های حاوی تابع SUM جمع می‌شوند.
ورژن مورد نیاز
اکسل ۲۰۰۳ و بالاتر
پی‌نوشت
*تابع SUBTOTAL در جداول افقی، سلول‌های مخفی را در محاسبات لحاظ میکند (حتی با کدهای ۱۰۱-۱۱۱).
*در محدوده‌های فیلتر شده، SUBTOTAL سلول‌های مخفی را نادیده میگیرد (حتی با کدهای ۱-۱۱).
*علاوه بر تابع SUBTOTAL، در اکسل ابزار SUBTOTAL نیز وجود دارد که کار را در جداول بزرگ راحت‌تر میکند.