تابع Subtotal در اکسل

برخلاف سایر توابع اکسل که فقط برای انجام تنها یک عملیات مشخص روی یک محدوده طراحی شده اند، تابع Subtotal در اکسل به طرز حیرت انگیزی همه کاره است!
این تابع توانایی استفاده از عملیات مختلفی از جمله جمع کردن (SUM) شمارش سلول ها (COUNT)، محاسبه میانگین (AVERAGE)، تعیین حداقل مقدار (MIN) یا حداکثر مقدار (MAX) و موارد دیگر را دارد. اما این عملیات با شرایط منحصر به فردی همراه هستند. در ادامه به بررسی این تابع و ویژگی های خاص آن می پردازیم.

قاعده کلی تابع Subtotal به شکل زیر است:

SUBTOTAL(function_num,ref1,[ref2],…

در واقع آرگومان های این تابع شامل دو بخش است:

1- Function_num (شماره تابع): رفتار تابع Subtotal توسط این آرگومان کنترل می شود، که به عنوان یک مقدار عددی ارائه می شود و مشخص می کند کدام تابع را به کار ببرید. در واقع این شماره تابع متعلق به یکی از مجموعه های زیر می باشد:

شماره های 1 تا 11 ← سلولهای فیلتر شده (Filter) را در محاسبات نادیده می گیرند، اما ردیف های پنهان شده (Hide) را شامل می شوند.
شماره های 101 تا 111 ← همه سلول های فیلتر شده و پنهان شده را در محاسبات نادیده می گیرند.

جدول زیر توابع موجود با شماره تابع مربوط به آن ها را نشان می دهد.

2- …,[ref1, [ref2: محدوده مورد نظر برای اعمال Subtotal است. توجه داشته باشید که فقط انتخاب اولین محدوده (Ref1) اجباری است.

نحوه استفاده از تابع Subtotal در اکسل

برای به کارگیری تابع Subtotal در اکسل، مراحل زیر را دنبال می کنیم:

1- همانطور که در قسمت فرمول نویسی، روش به کارگیری توابع را آموختیم، ابتدا در نوار فرمول یا در یک سلول علامت = و سپس چند حرف از تابع Subtotal را تایپ می کنیم. سپس از لیست نمایان شده تابع مورد نظر را نشان کرده و با فشردن کلید Tab فرمول را آماده دریافت آرگومان ها می کنیم.
همانطور که در تصویر مشاهده می کنید، لیستی از شماره تابع های موجود برای شما نمایش داده می شود. بر روی تابع مورد نظر دو بار کلیک می کنیم یا کلید Tab را فشار می دهیم و کاراکتر جداکننده را بعد از آن تایپ می کنیم.

2- اکنون باید محدوده مورد نظر را مشخص کنیم. پس می توانیم اولین سلول داده ستون مورد نظر را انتخاب کنیم و سپس با فشردن کلید های Ctrl + Shift + Arrow Down کل محدوده را انتخاب کنیم. (توجه داشته باشید که اگر از این روش برای انتخاب محدوده استفاده می کنید، نباید بین سلول های داده، سلول خالی وجود داشته باشد). سپس با فشردن کلید Enter می توانیم نتیجه را مشاهده کنیم.

به عنوان مثال، در جدول داده زیر، ما مقدار ماکسیمم (شماره تابع 4) را برای ستون C محاسبه کردیم.

اکنون که با تابع Subtotal آشنا شدید این سوال پیش می آید که چرا از توابع اصلی Sum، Count، Max و… استفاده نکنیم؟ جواب این سوال را می توانید در سه ویژگی منحصر به فرد این تابع جستجو کنید:

نادیده گرفتن مقادیر مخفی شده در محاسبات

یکی از کاربرد های این تابع انجام محاسبات روی داده های فیلتر شده (مخفی شده) هست. به عبارت دیگر محاسبه را روی سلول هایی انجام می دهد که نمایش داده می شوند.
توجه داشته باشید که سایر توابع اکسل مانند Sum این ویژگی را ندارند. یعنی در هر حال جمع همه سلول ها را محاسبه می‌کنند حتی اگر با فیلتر در حالت مخفی قرار گرفته باشند.
برای درک بهتر به مثال زیر توجه کنید.
داده های ستون C را با دو فرمول Sum و Subtotal جمع زدیم و نتیجه یکسانی دریافت کردیم.

تابع Subtotal می تواند برای عمل جمع، از هر دو شماره تابع 9 یا 109 استفاده کند. زیرا هر دو مجموعه شماره تابع (1-11 و 101-111) سلول های فیلتر شده را نادیده می گیرند.

اکنون با استفاده از ابزار فیلتر، ستون B را روی یکی از مرکز پخش ها فیلتر می کنیم. مشاهده می کنیم که حاصل جمع از فرمول Sum تغییری نکرده است اما تابع Subtotal به صورت خودکار محاسبه را دوباره انجام داده و تنها سلول های نمایش داده شده را جمع زده است.

حساس بودن به سطرهای مخفی شده (Hide)

تابع Subtotal را می توان طوری تنظیم کرد که سطرهایی که در حالت مخفی قرار دارند را محاسبه کند یا نکند.
همانطور که گفته شد، فرمول Subtotal دارای دو مجموعه شماره تابع است. اگر از توابع 1 تا 11 استفاده کنید، سطرهای مخفی شده در محاسبه لحاظ می شوند. اما اگر از توابع 101 تا 111 استفاده کنید، سطرهای مخفی شده در محاسبه لحاظ نمی شوند.
در شکل زیر اگر به شماره ردیف ها دقت کنید متوجه می شوید که برخی از سطر ها مخفی شده اند. با انتخاب شماره تابع مناسب، می توانید تعیین کنید که داده های موجود در سلول های مخفی شده در محاسبات لحاظ شوند یا نه.