به وب سایت سیمیا تدبیر خوش آمدید
  •   
  • خیابان انقلاب، خیابان بهار، برج بهار طبقه 9 واحد 735
  • info@simiatadbir.com

اطلاعات تماس

  •   
  • خیابان انقلاب، خیابان بهار، برج بهار طبقه 9 واحد 735
  • info@simiatadbir.com

تابع SUBTOTAL در اکسل

تابع SUBTOTAL در اکسل

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

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

تابع SUBTOTAL در اکسل

برای اینکار به راحتی از از تابع (SUM(D2:D15=  و (SUM(C2:C15= استفاده می کنیم. نتیجه مشخص است!

تابع SUBTOTAL در اکسل 2

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

تابع SUBTOTAL در اکسل3

بله درست است. با اینکه ما تنها جمع پروژه های استان تهران را می خواستیم اما باز هم جمع کل پروژه ها را به ما داد.

راه حل کار ساده است!

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

  • در هنگام فیلتر کردن قسمتهای فیلتر شده را محاسبه نمی کند
  • در هنگام Hide کردن قسمتهای Hide شده را محاسبه نمی کند
  • اگر چند تابع SUBTOTAL در یک ستون استفاده کرده باشید، سایر SUBTOTALها را در محاسبه نهایی محاسبه نمی کند.

ساختار این تابع به شکل زیر است.

SUBTOTAL (function_num, ref1, ref2=

مهمترین مساله ای که باید دقت نمایید function_num است.  function_numکدی است که شما با استفاده از آن به تابع می گویید که چه عملی انجام دهد. این اعداد شامل جمع، میانگین و … طبق جدول زیر می باشد. 

تابع SUBTOTAL در اکسل 4

برای داشتن جمع کل داده هایی که فیلتر می شوند تابع  (SUM(D2:D15= و (SUM(C2:C15= را پاک کنید و بجای آن از (SUBTOTAL(109;D2:D15= و (SUBTOTAL(109;C2:C15= استفاده کنید.

تابع SUBTOTAL در اکسل 5

همان نتیجه ای که می خواستیم حاصل شد!   

بجای کد 109 -که برای جمع است- کدهای دیگر را نیز امتحان کنید. مثلا کد 101 که برای میانگین است.

شما در این مقاله با تابع SUBTOTAL در اکسل آشنا شدید.

برای مشاهده آموزش اکسل اینجا کلیک کنید

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

محسن جلالی منش

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

نشانی ایمیل شما منتشر نخواهد شد.