تابع SUBTOTAL و آموزش کار در اکسل همراه با مثال
تابع SUBTOTAL و آموزش کار در اکسل همراه با مثال
تابع کاربردی و پر استفاده دیگری را برای کسانی می خواهند با توابع اکسل کار کنند برای شما آماده کرده ایم که آنهم تابع SUBTOTAL است.
در ادامه این مطلب می خواهیم شما را با تابع SUBTOTAL و نحوه استفاده از آن را در اکسل به شما آموزش دهیم. پس در ادامه با ما همراه باشید.
جلسه اول : آشنایی اولیه با نرم افزار اکسل ۲۰۱۰
جلسه دوم : کار با سلول ها در نرم افزار اکسل ۲۰۱۰
جلسه سوم : انتخاب فرمت های نمایشی در نرم افزار اکسل ۲۰۱۰
جلسه چهارم : ویرایش کاربرگ ها در نرم افزار اکسل ۲۰۱۰
جلسه پنجم : تعمیم سلول ها در نرم افزار اکسل ۲۰۱۰
جلسه ششم : انجام تغییرات بر روی سلول ها در نرم افزار اکسل ۲۰۱۰
جلسه هفتم : نمایش و تنظیمات آن در نرم افزار اکسل ۲۰۱۰
جلسه هشتم : کاربرگ ها و کار با آنها در نرم افزار اکسل ۲۰۱۰
جلسه نهم : رسم نمودار در نرم افزار اکسل ۲۰۱۰
جلسه دهم : ویرایش نمودار ها در نرم افزار اکسل ۲۰۱۰
جلسه یازدهم : چیدمان صحیح داده های نمودار در نرم افزار اکسل ۲۰۱۰
جلسه دوازدهم : فرمول نویسی در نرم افزار اکسل ۲۰۱۰
جلسه سیزدهم : انواع آدرس دهی در نرم افزار اکسل ۲۰۱۰
جلسه چهاردهم : کار با توابع در نرم افزار اکسل ۲۰۱۰
جلسه پانزدهم : توابع ریاضی در نرم افزار اکسل ۲۰۱۰
جلسه شانزدهم : توابع آماری در نرم افزار اکسل ۲۰۱۰
جلسه هفدهم: توابع منطقی در نرم افزار اکسل ۲۰۱۰
جلسه هجدهم : انواع خطاهای فرمول نویسی و منطقی در اکسل
جلسه نوزدهم : آشنایی با تابع 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 (محدوده مدنظر ; شماره تابع)
شماره تابع : این پارامتر عملیاتی رو که میخوایم روی محدوده مد نظر انجام بدیم مشخص میکنه. این عملیات ها شامل موارد زیر هست. دقت کنید که در شماره تابع های ۱ تا ۱۱، تنها سلول هایی که فیلتر شده باشند لحاظ خواهند شد.
شماره های ۱۰۱ تا ۱۱۱ دقیقا مشابه توابع ۱ تا ۱۱ هستند با این تفاوت که در این شماره توابع، سلول هایی که HIDE شده باشند نیز لحاظ نخواهند شد :
| شماره تابع | تابع | توضیحات |
| ۱ | AVERAGE | میانگین سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۲ | COUNT | تعداد سلول های محدوده مدنظر رو شمارش میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۳ | COUNTA | تعداد سلول های محدوده مدنظر رو شمارش میکنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۴ | MAX | ماکزیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۵ | MIN | مینیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۶ | PRODUCT | حاصلضرب سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۷ | STDEV.S | انحراف معیار نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۸ | STDEV.P | انحراف معیار سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۹ | SUM | جمع سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰ | VAR.S | واریانس نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۱ | VAR.P | واریانس سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۱ | AVERAGE | میانگین سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۲ | COUNT | تعداد سلول های محدوده مدنظر رو شمارش میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۳ | COUNTA | تعداد سلول های محدوده مدنظر رو شمارش میکنه. سلول هایی که محتوی متن باشند هم شمارش خواهند شد. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۴ | MAX | ماکزیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۵ | MIN | مینیمم سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۶ | PRODUCT | حاصلضرب سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۷ | STDEV.S | انحراف معیار نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۸ | STDEV.P | انحراف معیار سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۰۹ | SUM | جمع سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۱۰ | VAR.S | واریانس نمونه سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
| ۱۱۱ | VAR.P | واریانس سلول های محدوده مدنظر رو حساب میکنه. در صورتی که در محدوده مدنظر سلولی فیلتر یا HIDE شده باشه در محاسبات لحاظ نخواهد شد |
محدوده مدنظر : این محدوده سلولهایی که قراره عملیات روی اونها انجام بشه رو مشخص میکنه.
مثال : یک مثال از نحوه ی استفاده از تابع SUBTOTAL اکسل را در ادامه خواهیم دید :
جدول زیر رو در نظر بگیرید. فرض کنید این جدول رو در محدوده A4:D14 شیت خودمون داریم:
| کد محصول | نام محصول | گروه محصول | تعداد |
| ۱۰۰۰۱ | ماوس مدل ۱ | ماوس | ۱۴ |
| ۱۰۰۰۲ | ماوس مدل ۲ | ماوس | ۲۴ |
| ۱۰۰۰۳ | ماوس مدل ۳ | ماوس | ۲۴ |
| ۱۰۰۰۴ | ماوس مدل ۴ | ماوس | ۲۳ |
| ۱۰۰۰۵ | ماوس مدل ۵ | ماوس | ۲۴ |
| ۱۰۰۰۶ | کیبرد ۱ | کیبرد | ۳۰ |
| ۱۰۰۰۷ | کیبرد ۲ | کیبرد | ۲۸ |
| ۱۰۰۰۸ | کیبرد ۳ | کیبرد | ۲۴ |
| ۱۰۰۰۹ | کیبرد ۴ | کیبرد | ۱۴ |
| ۱۰۰۱۰ | کیبرد ۵ | کیبرد | ۱۶ |
فرض کنید میخوایم در بالای جدول، جمع تعدادی محصولات رو ببینیم اما میخوایم وقتی که گروه یا محصول خاصی رو فیلتر کردیم فقط جمع همون گروه یا محصولات نمایش داده بشه و نه جمع تعدادی کل محصولات. خب اول بیاید با تابع SUM اکسل شروع کنیم ببینیم میشه اینکار رو کرد یا نه.
در سلول D1 فرمول (SUM(D5:D14 رو بنویسید. نتیجه عدد ۲۲۱ هست. حالا اگر مثل تصویر زیر گروه خاصی رو در جدول فیلتر کنیم باز هم عدد ۲۲۱ در سلول D1 نمایش داده میشه.
برای اینکه بجای ۲۲۱، تنها جمع تعدادی گروه محصول فیلتر شده نمایش داه بشه از تابع SUBTOTAL به شکل زیر استفاده میکنیم:
تعیین شماره تابع SUBTOTAL اکسل :
ما میخوایم جمع تعدادی محصولات رو نمایش بدیم. همونطور که میدونیم تابع جمع در اکسل SUM هست. پس کافیه شماره تابع رو برابر ۹ قرار بدیم. (در صورتی که بخوایم سلول های HIDE شده هم در جمع لحاظ نشن میتونیم بجای ۹ از شماره ۱۰۹ استفاده کنیم).
تعیین محدوده مدنظر :
تعداد محصولات در سلول های D5 تا D14 قرار داره پس محدوده مدنظر ما برای جمع، محدوده D5:D14 هست.
در نهایت فرمول تابع SUBTOTAL اکسل ما به شکل زیر کامل میشه :
=SUBTOTAL(9;D5:D15)
حالا این فرمول رو در سلول D2 بنویسید و گروه محصول مدنظرتون رو فیلتر کنید. همونطور که میبینید، حالا بر خلاف تابع SUM، زمانی که شما محدوده مدنظرتون رو فیلتر میکنید تابع SUBTOTAL اکسل تنها مقادیری که فیلتر نشدند رو جمع میکنه. به عنوان مثال در تصویر زیر تنها جمع تعدادی گروه محصول ماوس نمایش داده میشه که برابر با عدد ۱۰۹ هست.
نکته : اگر در محدوده تابع SUBTOTAL اکسل شما، سلولی وجود داشته باشه که شامل فرمول SUBTOTAL دیگه ای باشه. تابع SUBTOTAL اون سلول رو در محاسبات خودش لحاظ نمیکنه.
