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

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

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

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

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

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

جلسه دوم : کار با سلول ها در نرم افزار اکسل ۲۰۱۰

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

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

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

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

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

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

جلسه نهم : رسم نمودار در نرم افزار اکسل ۲۰۱۰

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

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

جلسه دوازدهم : فرمول نویسی در نرم افزار اکسل ۲۰۱۰

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

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

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

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

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

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

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

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

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

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

کد محصول نام محصول گروه محصول تعداد
۱۰۰۰۱ ماوس مدل ۱ ماوس ۱۴
۱۰۰۰۲ ماوس مدل ۲ ماوس ۲۴
۱۰۰۰۳ ماوس مدل ۳ ماوس ۲۴
۱۰۰۰۴ ماوس مدل ۴ ماوس ۲۳
۱۰۰۰۵ ماوس مدل ۵ ماوس ۲۴
۱۰۰۰۶ کیبرد ۱ کیبرد ۳۰
۱۰۰۰۷ کیبرد ۲ کیبرد ۲۸
۱۰۰۰۸ کیبرد ۳ کیبرد ۲۴
۱۰۰۰۹ کیبرد ۴ کیبرد ۱۴
۱۰۰۱۰ کیبرد ۵ کیبرد ۱۶

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

در سلول D1 فرمول (SUM(D5:D14 رو بنویسید. نتیجه عدد ۲۲۱ هست. حالا اگر مثل تصویر زیر گروه خاصی رو در جدول فیلتر کنیم باز هم عدد ۲۲۱ در سلول D1 نمایش داده میشه.

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

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

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

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

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

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

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

=SUBTOTAL(9;D5:D15)

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

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

نکته : اگر در محدوده تابع SUBTOTAL اکسل شما، سلولی وجود داشته باشه که شامل فرمول SUBTOTAL دیگه ای باشه. تابع SUBTOTAL اون سلول رو در محاسبات خودش لحاظ نمیکنه.

فیسبوک توییتر گوگل + لینکداین تلگرام واتس اپ کلوب


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

خیلی ممنونیم

سلام خدمت شما..خواهش میکنم..موفق باشید.

پاسخی بگذارید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *