تابع Subtotal در اکسل و کاربردهای جالب آن
یکی از راه های رایج و پر استفاده برای نمایش داده ها در اکسل، دسته بندی کردن داده ها از طریق Hide/Unhide و یا Filter هست. برای دسته بندی کردن داده ها و انجام عملیات بر روی آنها، ابزاری رو معرفی کردیم به نام ابزار Subtotal. و گفتیم که این ابزار ترکیبی است از تابع Subtotal و گروه بندی دستی داده ها (که در واقع همون Hide/Unhide کردن راحت تر و شکیل تر هست). در این مقاله میخوایم به آموزش تابع Subtotal بپردازیم که ۳ ویژگی و کاربرد منحصر بفرد داره. یکی از کاربردهای این تابع انجام محاسبات روی داده های فیلتر شده هست که در ادامه توضیح داده میشه.
آرگومان های این تابع به شرح زیر است:
Function_Num: عددی است از ۱ تا ۱۱ و ۱۰۱ تا ۱۱۱ که هر کدوم یک عمل رو انجام میده.
Ref1,[Ref2],…: محدوده ای که میخوایم عملیات مورد نظر روی اون انجام بشه.
جدول زیر نمایش دهنده حالت های مختلف آرگومان اول هست:
عملکرد | تابع | function_num (داده های Hide شده رو در محاسبات در نظر نمیگیره) |
function_num (داده های Hide شده رو در محاسبات در نظر میگیره) |
میانگین | AVERAGE | ۱ | ۱۰۱ |
شمارش اعداد | COUNT | ۲ | ۱۰۲ |
شمارش سلول های پر | COUNTA | ۳ | ۱۰۳ |
ماکزیمم | MAX | ۴ | ۱۰۴ |
مینیمم | MIN | ۵ | ۱۰۵ |
ضرب | PRODUCT | ۶ | ۱۰۶ |
انحراف معیار نمونه | STDEV | ۷ | ۱۰۷ |
انحراف معیار جامعه | STDEVP | ۸ | ۱۰۸ |
جمع | SUM | ۹ | ۱۰۹ |
واریانس | VAR | ۱۰ | ۱۱۰ |
واریانس جامعه | VARP | ۱۱ | ۱۱۱ |
این دو سری آرگومان در خصوص داده های:
- فیلتر شده یکسان عمل میکنن و در محاسبات خودشون داده های پنهان شده رو حساب نمیکنن.
- 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 هست با این تفاوت که هم توابع بیشتری رو ارائه میده و هم حالت های مختلف اینکه این توابع روی چه داده هایی اعمال بشه.
یکی از کاربردهای جالب دیگه این تابع شماره گذاری برای محدود های فیلتر شده هست که حتما بهتون پیشنهاد میکنم حل مسئله شماره گذاری خودکار در اکسل رو ببینید.
سلام من یک تیبل دارم می خواستم با انجام فیلتر تعداد سلول های غیر تکراری یک ستون شمارش بشه ( حساس باشه به فیلتری که کردم )
این کار شدنی هست
درود، میتونید اول با استفاده از Conditional Formatting سلول های Unique رو رنگی کنید (تنظیمات Duplicate Values رو روی Unique بذارید)، بعد با استفاده از Filter by Color رنگی که در قسمت قبل مشخص کردید رو فیلتر و نهایتا با انتخاب همه سلول ها تعداد آنها رو در Status Bar ببینید.
سلام
من میخواستم تو اکسل وقتی در یک سلول حرف O یا حرف L رو تایپ کنم در سلول مجاورش مثلا شکل دایره یا L رو با اشکال shape ترسیم کنه.
چطوری میتونم اینکار رو انجام بدم؟
درود بر شما
اگ رمیخواید با فرمول اینکار و بکنید و راحت تر باشید
بهتره از symbol استفاده کنید بجای shape
خیلی کاربردی و خوب بود. ممنون. و صد بار دیگه ممنون بابت اینکه فرمول درست تایپ شده و مثل بعضی سایت های دیگه آدم رو سردرگم نمیکنید