نحوه استفاده از تابع SUBTOTAL اکسل | بلاگ

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

تعرفه تبلیغات در سایت

آخرین مطالب

امکانات وب

سلام به همه دوستان عزیز. بعد از مدتی که به دلیل مشغله کاری کمتر میتونستم در خدمتتون باشم امروز با یک آموزش دیگه در خدمت شما دوستان هستم. در این مطلب آموزش اکسل میخوایم با نحوه استفاده از تابع SUBTOTAL اکسل آشنا بشیم و کاربردهای تابع SUBTOTAL اکسل رو ببینیم. در ادامه این مطلب آموزش اکسل با من همراه باشید.

ما از تابع SUBTOTAL اکسل برای انجام عملیاتی مانند جمع کردن، میانگین گرفتن و … روی یک محدوده خاص استفاده میکنیم. بر خلاف توابعی مانند SUM، AVERAGE و … که تنها یک عملیات مشخص رو روی یک محدوده یا RANGE انجام میدن، تابع SUBTOTAL اکسل این قابلیت رو داره که با استفاده از پارامتری که ما به اون میدیم عملیات های مختلفی رو روی محدوده مدنظر انجام بده.

قاعده کلی تابع SUBTOTAL اکسل:

=SUBTOTAL(محدوده مدنظر; شماره تابع)

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

شماره تابع تابع توضیحات
1 AVERAGE میانگین سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
2 COUNT تعداد سلول های محدوده مدنظر رو شمارش میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
3 COUNTA تعداد سلول های محدوده مدنظر رو شمارش میکنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
4 MAX ماکزیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
5 MIN مینیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
6 PRODUCT حاصلضرب سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
7 STDEV.S انحراف معیار نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
8 STDEV.P انحراف معیار سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
9 SUM جمع سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
10 VAR.S واریانس نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
11 VAR.P واریانس سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد
101 AVERAGE میانگین سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
102 COUNT تعداد سلول های محدوده مدنظر رو شمارش میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
103 COUNTA تعداد سلول های محدوده مدنظر رو شمارش میکنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
104 MAX ماکزیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
105 MIN مینیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
106 PRODUCT حاصلضرب سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
107 STDEV.S انحراف معیار نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
108 STDEV.P انحراف معیار سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
109 SUM جمع سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
110 VAR.S واریانس نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد
111 VAR.P واریانس سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد

محدوده مدنظر: این محدوده سلولهایی که قراره عملیات روی اونها انجام بشه رو مشخص میکنه.

 یک مثال از استفاده از تابع SUBTOTAL اکسل:

جدول زیر رو در نظر بگیرید. فرض کنید این جدول رو در محدوده A4:D14 شیت خودمون داریم:

کد محصول نام محصول گروه محصول تعداد
10001 ماوس مدل 1 ماوس 14
10002 ماوس مدل 2 ماوس 24
10003 ماوس مدل 3 ماوس 24
10004 ماوس مدل 4 ماوس 23
10005 ماوس مدل 5 ماوس 24
10006 کیبرد 1 کیبرد 30
10007 کیبرد 2 کیبرد 28
10008 کیبرد 3 کیبرد 24
10009 کیبرد 4 کیبرد 14
10010 کیبرد 5 کیبرد 16

فرض کنید میخوایم در بالای جدول، جمع تعدادی محصولات رو ببینیم اما میخوایم وقتی که گروه یا محصول خاصی رو فیلتر کردیم فقط جمع همون گروه یا محصولات نمایش داده بشه و نه جمع تعدادی کل محصولات. خب اول بیاید با تابع SUM اکسل شروع کنیم ببینیم میشه اینکار رو کرد یا نه. در سلول D1 فرمول SUM(D5:D14) رو بنویسید. نتیجه عدد 221 هست. حالا اگر مثل تصویر زیر گروه خاصی رو در جدول فیلتر کنیم باز هم عدد 221 در سلول D1 نمایش داده میشه.

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

برای اینکه بجای 221، تنها جمع تعدادی گروه محصول فیلتر شده نمایش داه بشه از تابع SUBTOTAL به شکل زیر استفاده میکنیم:

تعیین شماره تابع SUBTOTAL اکسل:

ما میخوایم جمع تعدادی محصولات رو نمایش بدیم. همونطور که میدونیم تابع جمع در اکسل SUM هست. پس کافیه شماره تابع رو برابر 9 قرار بدیم. (در صورتی که بخوایم سلول های HIDE شده هم در جمع لحاظ نشن میتونیم بجای 9 از شماره 109 استفاده کنیم).

تعیین محدوده مدنظر:

تعداد محصولات در سلول های D5 تا D14 قرار داره پس محدوده مدنظر ما برای جمع، محدوده D5:D14 هست.

در نهایت فرمول تابع SUBTOTAL اکسل ما به شکل زیر کامل میشه:

=SUBTOTAL(9;D5:D15)

حالا این فرمول رو در سلول D2 بنویسید و گروه محصول مدنظرتون رو فیلتر کنید. همونطور که میبینید، حالا بر خلاف تابع SUM، زمانی که شما محدوده مدنظرتون رو فیلتر میکنید تابع SUBTOTAL اکسل تنها مقادیری که فیلتر نشدند رو جمع میکنه. به عنوان مثال در تصویر زیر تنها جمع تعدادی گروه محصول ماوس نمایش داده میشه که برابر با عدد 109 هست.

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

نکته:
اگر در محدوده تابع SUBTOTAL اکسل شما، سلولی وجود داشته باشه که شامل فرمول SUBTOTAL دیگه ای باشه. تابع SUBTOTAL اون سلول رو در محاسبات خودش لحاظ نمیکنه.
...
نویسنده : بازدید : 3 تاريخ : چهارشنبه 5 ارديبهشت 1397 ساعت: 15:32