تابع SUBTOTAL و آموزش کار در اکسل همراه با مثال

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

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

 

 سری آموزش های نرم افزار صفحه گسترده اکسل (Excel)

جلسه اول : آشنایی اولیه با نرم افزار اکسل 2010

جلسه دوم : کار با سلول ها در نرم افزار اکسل 2010

جلسه سوم : انتخاب فرمت های نمایشی در نرم افزار اکسل 2010

جلسه چهارم : ویرایش کاربرگ ها در نرم افزار اکسل 2010

جلسه پنجم : تعمیم سلول ها در نرم افزار اکسل 2010

جلسه ششم : انجام تغییرات بر روی سلول ها در نرم افزار اکسل 2010

جلسه هفتم : نمایش و تنظیمات آن در نرم افزار اکسل 2010

جلسه هشتم : کاربرگ ها و کار با آنها در نرم افزار اکسل 2010

جلسه نهم : رسم نمودار در نرم افزار اکسل 2010

جلسه دهم : ویرایش نمودار ها در نرم افزار اکسل 2010

جلسه یازدهم : چیدمان صحیح داده های نمودار در نرم افزار اکسل 2010

جلسه دوازدهم : فرمول نویسی در نرم افزار اکسل 2010

جلسه سیزدهم : انواع آدرس دهی در نرم افزار اکسل 2010

جلسه چهاردهم : کار با توابع در نرم افزار اکسل 2010

جلسه پانزدهم : توابع ریاضی در نرم افزار اکسل 2010

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

جلسه هفدهم: توابع منطقی در نرم افزار اکسل 2010

جلسه هجدهم : انواع خطاهای فرمول نویسی و منطقی در اکسل

جلسه نوزدهم : آشنایی با تابع SUMIF و نحوه استفاده از آن 

جلسه بیستم : آشنایی با تابع IFERROR اکسل به همراه مثال

جلسه بیست و یکم : آشنایی با تابع SUM اکسل همراه با مثال

جلسه بیست و دوم: آشنایی با تابع AVERAGE اکسل همراه با مثال 

جلسه بیست و سوم: آشنایی با تابع COUNT اکسل همراه با مثال

جلسه بیست و چهارم: آشنایی با تابع IF شرطی به همراه مثال

جلسه بیست و پنجم: آشنایی با تابع SUMIFS شرطی به همراه مثال 

جلسه بیست و ششم : آشنایی با تابع AVERAGEIF میانگین گیری شرطی 

جلسه بیست و هفتم : آشنایی با تابع AVERAGEIFS میانگین گیری چند شرطی 

جلسه بیست و هشتم : آموزش تابع AVERAGEA و تفاوت آن با تابع AVERAGE 

جلسه بیست و نهم: آشنایی با تابع COUNTA اکسل همراه با مثال 

جلسه سی ام: آشنایی با تابع COUNTBLANK اکسل همراه با مثال

جلسه سی و یکم: آشنایی با تابع COUNTIF تابع شمارشی شرطی 

جلسه سی و دوم: آشنایی با تابع COUNTIFS تابع شمارشی چند شرطی

جلسه سی و سوم: تابع INDEX و نحوه استفاده آن در اکسل بهمراه مثال

جلسه سی و چهارم: تابع HLOOKUP و آموزش استفاده در اکسل با مثال کاربردی

جلسه سی و پنجم: تابع 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 اون سلول رو در محاسبات خودش لحاظ نمیکنه.

منبع: یوکد