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

[Saman Cheraghi 13]

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

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

Function_Num: عددی است از 1 تا 11 و 101 تا 111 که هر کدوم یک عمل رو انجام میده.

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

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

مشاهده جدول در منبع آموزش

حتما بخوانید: چرا عملگر ها در اکسل یکی از اساس فرمول نویسی هستند؟

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

 

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

مثال اول:

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

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

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

مثال دوم:

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

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

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

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

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

حتما بخوانید: چرا تابع Vlookup درست کار نمیکنه؟

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

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

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

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

منبع آموزش:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام