سیمیا تدبیرآرین

تابع SUBTOTAL در اکسل

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

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

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

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

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

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

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

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

در هنگام Hide کردن قسمتهای Hide شده را محاسبه نمی کند

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

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

SUBTOTAL (function_num, ref1, ref2=

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

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