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

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

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

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

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

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

تعریف تابع 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 به شکل زیر استفاده میکنیم:

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

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

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

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

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

=SUBTOTAL(9;D5:D15)

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

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

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

درباره نویسنده

حسن بهرمندپور حسن بهرمندپور هستم، فارغ التحصیل مهندسی نرم افزار..علاقه شخصی ام یادگیری زبان های کاربردی مثل سی شارپ،PHP و اندروید هست.. دوست دارم در این زمینه هر تجربه و اطلاعاتی دارم با شما دوستان به اشتراک بگذارم..♥
منتظر نظرات خوب شما هستیم!

2 دیدگاه

    • سلام , مهمان