تابع Subtotal اکسل – جمع جزء

تابع Subtotal اکسل

کلمه Sub به معنی زیر یا جز می باشد. پس کلمه Subtotal به معنی جمع جز می باشد. تابع Subtotal اکسل برای هر عملیات ریاضی و آماری به صورت جزء به جزء کاربرد دارد.

اما توجه کنید که کار این تابع تنها جمع کردن نمی باشد. بلکه ماهیت کاملا متفاوتی نسبت به سایر توابع اکسل دارد و کار کردن با آن متفاوت می باشد. ولی بسیار بسیار یادگیری و استفاده از آن ساده و کاربردی می باشد.

پیش نیاز این مطلب، آموزش ابزار Filter در اکسل می باشد و این تابع را با ابزار Subtotal اشتباه نگیرید.

جدول زیر را در نظر بگیرید.

از شما می خواهند جمع کل فروش خالص را حساب کنید.

این کار بسیار ساده می باشد و با تابع Sum این کار را انجام می دهیم.

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

حالا از شما می خواهند که جمع کل را فقط در مناطق شمال بیان کنید.

ابتدا به کمک ابزار Filter این کار را انجام می دهیم.ولی همان طور که می بینید جمع و میانگین همان جمع و میانگین کل می باشد.در حالی که ما جمع و میانگین فقط در مناطق شمال را می خواهیم.

برای اینکه این مشکل را برطرف کنیم نیاز داریم از تابع Subtotal استفاده کنیم.پس با ما همراه باشید تا آموزش این ابزار را بیاموزید.

 ساختار تابع subtotal اکسل:

SUBTOTAL(function_num,ref1,[ref2],…)

SUBTOTAL(مرجع 2],مرجع1,شماره تابع],…)

اگر به ساختار این تابع توجه کنید کاملا با ساختار سایر توابع فرق دارد.

شماره تابع:

در این تابع برای هر کدام از عملیات های جمع،میانگین،و….یک شماره در نظر گرفته است. مثلا اگر بخواهید یک ستون را جمع کنید از عدد 9 یا 109 استفاده خواهید کرد.این کد به تابع می فهماند که باید عملیات جمع را انجام دهد.یا اگر بخواهیم میانگین بگیریم از کد 1 یا 101 استفاده خواهیم کرد.این کد به تابع می فهماند که باید عملیات میانگین را انجام دهد.

مرجع:

در قسمت قبلی گفتیم نوع عملیات را مشخص می کنیم ولی باید به تابع بفهمانیم کدام ستون یا داده ها را جمع کند.در این قسمت به تابع می فهمانیم باید چه قسمت هایی را با هم جمع کند.در مثال از سلول D2:D29 می باشد.

یعنی تابع ما برای عملیات جمع کردن در حالت فیلتر به این شکل خواهد شد:

=SUBTOTAL(109,D2:D29)

و برای عملیات میانگین در حالت فیلتر به این شکل خواهد شد:

=SUBTOTAL(101,D2:D29)

نتیجه مانند تصویر زیر خواهد بود:

حالا اگر از شما بخواهند جمع فروش خالص در مناطق شمال و شهرستان تهران چقدر بوده است،کافیست فقط عملیات فیلتر را انجام دهید!!! تابع Subtotal خودش این کار را انجام می دهد.

همان طور که مشاهده می کنید تابع Subtotal یک سری عملیات را در حالت فیلتر انجام میدهد.

اما چند سوال مهم :

• تابع Subtotal چه شماره هایی را قبول می کند؟

جواب:طبق جدول زیر شماره های 101 تا 111 و شماره های 1تا11 را قبول می کند و مشاهده خواهید کرد که هر کد چه عملی را انجام میدهد.

• با توجه به جواب سوال1، چرا در تابع subtotal هم عدد1و 101 یک عمل را انجام می دهند؟ تفاوت این دو در چیست؟

فرض کنید بعضی از سطرها را شما Hide کرده باشید و بخواهید(یا نخواهید) آن ها را هنگام فیلتر کردن در محاسبات دخیل کند.برای این منظور دو کد قرار داده شده است.

1. کد های 1 تا11 برای داده هایی می باشد که اگر سطرهای آن توسط شما Hide شده باشد باز هم در محاسبات subtotal در نظر گرفته خواهد شد.
2. کدهای101 تا 111 برای داده هایی می باشد که اگر سطرهای آن توسط شما Hide شده باشد در محاسبات subtotal در نظر گرفته نمی شود.

•  اگر در میان داده ها دارای خطا باشیم و بخواهیم این عملیات ها را انجام دهیم، چه می کنیم؟ تابع aggregate اکسل