تابع Subtotal در اکسل و نکات جالب آن

تابع Subtotal در اکسل و کاربردهای جالب آن

یکی از راه های رایج و پر استفاده برای نمایش داده ها در اکسل، دسته بندی کردن داده ها از طریق Hide/Unhide و یا Filter هست. برای دسته بندی کردن داده ها و انجام عملیات بر روی آنها، ابزاری رو معرفی کردیم به نام ابزار Subtotal. و گفتیم که این ابزار ترکیبی است از تابع Subtotal و گروه بندی دستی داده ها (که در واقع همون Hide/Unhide کردن راحت تر و شکیل تر هست). در این مقاله میخوایم به آموزش تابع Subtotal بپردازیم که ۳ ویژگی و کاربرد منحصر بفرد داره. یکی از کاربردهای این تابع انجام محاسبات روی داده های فیلتر شده هست که در ادامه توضیح داده میشه.

آرگومان های این تابع به شرح زیر است:

Function_Num: عددی است از ۱ تا ۱۱ و ۱۰۱ تا ۱۱۱ که هر کدوم یک عمل رو انجام میده.

Ref1,[Ref2],…: محدوده ای که میخوایم عملیات مورد نظر روی اون انجام بشه.

جدول زیر نمایش دهنده حالت های مختلف آرگومان اول هست:

این دو سری آرگومان در خصوص داده های:

• فیلتر شده یکسان عمل میکنن و در محاسبات خودشون داده های پنهان شده رو حساب نمیکنن.
• Hide شده متفاوت عمل میکنن. ۱-۱۱ داده های پنهان شده در نظر میگیره و ۱۰۱-۱۱۱ داده های پنهان شده رو در محاسبات در نظر نمیگیره.

مثال اول:

در مثال زیر (شکل ۱) میخوایم وقتی فیلتر میکنیم روی تیم ۱، جمع فروش اون تیم رو ببینیم. همونطور که در شکل ۱ میبینید تابع Sum اصلا به فیلتر حساس نیست و همه داده ها رو جمع میزنه. پس باید از تابع Subtotal استفاده کنیم. که با توجه به جدول بالا، عدد ۹ یا ۱۰۹ جمع رو برای ما محاسبه میکنه.

شکل ۱- تابع Subtotal – جمع داده های فیلتر شده با Subtotal

لینک نکات پیشرفته در فیلتر رو هم مشاهده کنید.

مثال دوم:

میخوایم جمع فروش در هر ماه به تفیکیک ببینیم. برای این کار یا بصورت دستی گروه بندی میکنیم یا از ابزار Subtotal استفاده میکنیم.

شکل ۲- تابع Subtotal – جمع فروش بر اساس ماه

اگر از ابزار Subtotal استفاده کنید، خودش فرمول نویسی رو هم انجام میده. اما اگه از گروه بندی دستی استفاده کنید باید فرمول نویسی رو خودتون انجام بدید. در هر صورت نهایتا خروجی مشابه شکل ۲ باید باشه.

تا اینجا دو ویژگی این تابع رو توضیح دادیم که در خصوص داده های Filter/Hide شده بود. حالا میخوایم ویژگی سوم این تابع رو توضیح بدی:

همونطور که در شکل ۲ می بینید در قسمت Grand Total که جمع کل فروش رو نمایش میده، Subtotal روی کل ستون فروش اعمال شده. بعبارتی انتظار داریم که در مجموع، دوبرابر نشون داده بشه چرا که یکی بار خود داده های فروش، یکبار هم جمع فروش در هر ماه. اما نکته مهم در خصوص تابع Subtotal این هست که این تابع سلول های حاوی تابع Subtotal رو در محاسبات در نظر نمیگیره. بعبارتی در مثال شکل ۲، سلول های C4, C8, C12, C16, C20 که جمع هر ماه رو نشون میدن، در محاسبه جمع کل در نظر گرفته نشده اند.

شکل ۳- ویژگی تابع Subtotal –در نظر نگرفتن سلول های حاوی این تابع

همونطور که در شکل ۳ می بینید، اگر بجای Subtotal از تابع Sum استفاده میشد، جمع کل دو برابر میشد. چرا؟

چون یک بار خود داده های مربوط به ماه ها رو جمع میکرد و یکبار سلولهایی که جمع ماه ها هستند. پس این ویژگی تابع Subtotal باعث میشه محاسبات براحتی و با درستی انجام بشه.

با تابع Subtotal و منطق و روش کار اون آشنا شدیم. تابع قدرتمندتر با ویژگی های بیشتری از اکسل ۲۰۱۰ به بعد اضافه شده به نام Aggregate که منطق و نوع کارش مثل Subtotal هست با این تفاوت که هم توابع بیشتری رو ارائه میده و هم حالت های مختلف اینکه این توابع روی چه داده هایی اعمال بشه.

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

دانلود فایل این آموزش