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

دانلود فایل آموزش:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

تمرین:

مخصوص کاربران ویژه سایت
کاربر گرامی دسترسی کامل به مطالب مربوط به این بخش با اشتراک در بخش کاربران ویژه امکانپذیر میباشد. لطفا برای اشتراک در بخش کاربران ویژه از بخش عضویت ویژه استفاده نمایید

امیدوارم از این آموزش هم استفاده کافی رو برده باشید.

موفق باشید

55 Comments on “نحوه استفاده از تابع SUBTOTAL اکسل”

  1. اسکن

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

  2. davoud yourdkhani

    با سلام
    ببخشید سوالی داشتم نمیدونستم باید تو کدوم مبحث مطرحش کنم
    من میخوام از یک شیت که لیست کالاها و تعداد و دیگر ویژگی هاشون در اون هست بعضی کالاهای خاص مثلا کالاهایی که توی اسمشون کلمه پی وی سی هست رو ببرم به یک جدول دیگه تو یک شیت دیگه بدون اینکه مجبور باشم دونه دونه تایپشون کنم و تعداد و ویژگی هاشون دوباره بنویسم از چه فرمولی استفاده کنم ؟
    ممنون میشم راهنمایی کنید

    1. آموزش ماکرو نویسی در اینجا

      سلام ، جناب یوردخانی – اگه شما پرسش و پاسخهای سایت را مطالعه کنید ، مطالب زیاد خوبی از آنها یاد میگیرید . یکی از دوستان ، دقیقاً سوال شما را مطرح کرده بود و این موضوع در آنجا بحث شد
      با این تفاوت که کلمه کلیدی پرسش ایشان ، عبارت “سمپلر” بود!!
      شما میتوانید از دستور VLookUp ویا Match برای یافتن ردیف مورد نظر خود کمک بگیرید . عبارت مورد جستجو را بایستی داخل گیومه گذاشته و از WildCard ها نیز میتوانید استفاده کنید . موفق باشید – خانیکی
      0919-302-6452

  3. مصطفی

    سلام لطفا راهنمایی کنید چطور میتونیم دوتا فایل اکسل داشته باشیم و از جمع مثلا دو تا خانه A3 در شیت ۱ و D7 در شیت ۲ بصورت اتوماتیک در خانه A11 در شیت ۳ ثبت بشه. متشکرم

  4. محمدجواد حسینی

    سلام جناب آقای اسماعیل پور
    ممنون بابت مطلب فوق ؛ یک سوال داشتم اگر در محدود انتخابی اعداد صفر هم قرار گرفته باشد آیا راهی هست که تابع SUBTOTAL اعداد صفر را نادیده بگیرد. بعنوان مثال می خوام در تابع subtotal از تابع average استفاده کنم ولی اعداد صفر زیادی هست که می خوام در محاسبات لحاظ نشود.
    ممنون و سپاسگذار

    1. یاسین اسماعیل پور

      سلام جناب حسینی
      اگر فرض کنیم مقادیر شما در محدوده A1 تا A10 قرار دارند میتونیم با استفاده از فرمول آرایه ای زیر به چیزی که شما میخواید برسیم:
      =AVERAGE(IF(SUBTOTAL(2;OFFSET(A1;ROW(A1:A10)-ROW(A1);0));IF(A1:A10<>0;A1:A10)))
      حتما بعد از نوشتن فرمول با کلید ترکیبی CTRL+SHIFT+ENTER فرمولتون رو تایید کنید

    2. محسن

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

  5. حسینی

    سلام جناب اسماعیل پور
    یک دنیا ممنون و سپاسگذار ، مشکل حل شد.
    ان شاء الله که همیشه موفق و موید باشد.

  6. king28

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

  7. marzieeiniafzal

    میشه لطفا توضیح بدین که شماره توابع رو چطور باید تشخیص بدیم یا اینکه باید شماره هارو از حفظ باشیم؟

    1. یاسین اسماعیل پور

      باید حفظ کنی مرضیه جان. البته زمانیکه تابع رو دارید مینویسید خود اکسل نمایش میده که هر شماره مربوط به کدوم تابع هست

  8. pedram

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

  9. m135911725

    جناب مهمندس اسماعیل پور با سلام، من اکسل را خیلی دوست دارم زیاد آشنایی ندارم ولی دروه پیشرفته icdl را رفتم حالا یک سوال دارم:
    من میخواهم یک فایل اکسل در خصوص ساعت کاری خودم درست کنم به این طریق که ساعت کاری موظفیم از 7 صبح تا 15/40 بعداظهر است ودرماه 60 بیشتر مجاز نمی باشم اضافه کاری کنم و تا ساعت 19 هم بیشتر نمیتوانم بمانم و روزهای تعطیل هم 8 ساعت مجازم اضافه کاری کنم و 10دقیقه اول وقت (یعنی ده دقیقه به هفت صبح) اضافه کار محسوب می شود حالا اگه لطف کنید فرمولشو بهم بگین تا با وارد کردن ساعت ورود و خروجم ساعت اضافه کاریم بدست آید تا هرموقع 60 ساعت تکمیل شود مطلع شود
    با سپاس فراوان

  10. shayan

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

  11. سعید

    سلام
    میخواستم اعداد مثلا از 1 تا 1000
    بصورت ستون های 100 تایی در ده ستون پشت سر هم به صورت خودکار نوشته بشه
    بصورت فرمول نه کار با موس

    1. یاسین اسماعیل پور

      سلام
      شما فرمول زیر رو توی a1 بنویس و بعد کپی کن تا j100
      =row()*coulmn()

  12. امیر

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

  13. mahsa73.11.10

    سلام ممنون بابت سایتتون
    من میخوام یه فرمول بنویسم که ساعت های ورود و خروج پرسنل رو ثبت کنه میشه راهنماییم کنید؟

    1. یاسین اسماعیل پور

      سلام مهسا جان. فرمول برای محاسبات استفاده میشه نه ثبت. ثبت رو که باید کاربر انجام بده. میشه بیشتر توضیح بدین؟

  14. masi

    سلام
    من برای اینکه بتونم سلول های پرشده ستونی رو حساب کنم از تابع Count استفاده کردم و نتیجه گرفتم
    مشکل اینجاست که اگه روی ستون فیلتر کنم تعداد سلول های پر نمایش داده شده، به درستی شمرده نمیشه و عدد همون شمارش بدون فیلتر نمایش داده می شود.
    میخواستم راه حلی بفرمایین که بتونم با هر بار فیلتر تعداد درست نمایش داده شود
    ممنون

  15. مجتبي

    سلام . ممنون از اطلاعات مفيدتون…
    يه سوال دارم از خدمتتون…
    فرمولي هست ك بشه در تاريخ اتمام يه قرارداد آلارمي به اسم پايان قرارداد جلوش بياد…
    بدون وارد كردن تاريخ پيش فرض به طور خودكار وقتي تاريخ قراردادي ميگذره روز بعد آلارم پايان قرارداد بده اكسل؟؟

  16. zahra azimi

    اینکار رو که انجام میدم موقع انتقال به اکسل دیگه ای از هم بازشون میکنه چیکار کنم؟

  17. ali

    سلام وعرض تشکر
    لطفا اگرامکان داره راهنمایی کنید که چطور میشه از SUBTOTAL به گونه ای استفاده کردکه بعدازاینکه ستونها از فیلتر دربیاد جمع خانه هایی که فیلتر شده بود تغییر نکنه وخانه هایی که HIDE شده بودبعداز خاموش شدن فیلتر نیان رویه جمع قبلی ؟

  18. ع *نقدی

    درود
    بابت مطالبتون که توضیحات آن شفاف واز همه مهمتر باذکر مثال عنوان مینمایید ،مطالبتون قابل فهم وشیواتر شده است که جا داره ازتون تشکر کنم
    ممنون

  19. ch

    سلام
    یک فرم تو اکسل درست کردم و میخوام مقادیر MIN-MAX-SUM و… را نمایش بده
    اینکارا دستی انجام دادم ولی می خوام همزمان با ثبت داده مقادیر نیز بروز رسانی بشه
    چه دستوراتی لازمه؟

  20. alireza

    با سلام
    من فایلی دارم با 31 شیت (ایام یک ماه)و در هر روز(شیت)اطلاعات روزانه 58 نوع کالا به تفکیک زمان و مکان ثبت شده.

    سوال:
    چطور میتونم در شیت 32 مجموع اطلاعات روزانه 58 نوع کالا رو به تفکیک زمان و مکان داشته باشم.آدرس دهی خانه به خانه واسه این همه شیت خیلی سخته.اگر میشه راهنماییم کنید.
    ممنون

  21. دهقانی

    سلام وقت بخیر
    در ابتدا ممنون از اینکه هستید
    چطور میتونم تمام درسهای که ارائه میدید رو داشته باشم. همشو.
    البته با توضیحات خودتون و درخواست های که دوستان از شما داشتن.
    متشکرم . دهقانی b.dehghani@rooeein.co.ir

  22. لیلی

    سلام. ممنون از آموزش های کاربردی شما
    یه سوال داشتم من می خواهم از عدد یک تا ۲۷۸ را در با۱۶ هزار ردیف سلول رها تکرار کنم یعنی از عدد ۱ تا ۲۷۸ نوشته شود مجددا از عدد۱ تا ۲۷۸ نوشته شود و الی آخر … چطور می توانم با یک دستور در این ۱۶ هزار ردیف این اعداد را بنویسم
    سوال بعدی من این است که چگونه می توانم داده های یک تا ده فرضا یک ستون که ۱۶ هزار ردیف دارد را انتخاب کنم . بعد ۲۷۸ تا سلول را رد کنم دوباره ده تا سلول متوالی را انتخاب کنم دوباره ۲۷۸ تا ردیف را رد کنم و مجددا ده تا سلول متوالی را انتخاب کنم
    خیلی ممنون میشم سوال منو باسخ بدید خییییییییییییلی بهش احتیییییییاج دارم.خیلی

  23. قالیشویی

    کار کردن با توابع و فورمولهای اکسل بسیار لذت بخش
    لطفا چند مورد از این فورمول نویسیهایی که در روزمره کاربرد داره توضیح بدبد
    ممنون از سایتتون

  24. نگین

    سلام ممنون از سایت بسیار خوب و راهنمایی هاتون
    من از subtotal استفاده میکنم ولی زمانیکه می خوام فیلتر کنم بعد از فیلتر جمع کل هم فیلتر میشه و باید تو فیلتر blanck رو انتخاب کنم چیکارکنم که ردیف جمع کل ثابت بمونه همیشه؟

  25. f.khajehi

    سلام
    از راهنماییتون بی نهایت سپاسگزارم
    امیدوارم درتمام مراحل زندگی موفق باشید

  26. حمید

    سلام
    با اینکه خیلی سرم شلوغه ولی ناسپاسی بود که از مطالب به درد به خور شما تشکر نکنم
    تشششششکر وسپاس فراوان

پاسخ دهید

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