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

جمع سلول‌های فیلتر شده با تابع Subtotal

یکی از فرمولهای مهم و جدید اضافه شده در EXCEL 2010 تابع AGGREGATE می باشد که می توان گفت که این تابع، نسخه ارتقا یافته تابع SUBTOTAL در اکسل است.تابع subtotal و aggregate در اکسل برای فیلتر کردن

بنابراین ابتدا تابع SUBTOTAL را در EXCEL بررسی می نماییم:

 

 

 تابع SUBTOTAL در فرمول نویسی:

 

کار تابع SUBTOTAL انجام عملیاتهایی مانند جمع، ضرب، میانگین، شمارش و ... است اما با ویژگیهای زیر:

  • این تابع به فیلتر حساس است: یعنی هنگامیکه سطرها فیلتر می شوند ، محاسبه را روی سطرهایی انجام می‌دهد که نمایش داده می‌شوند و سایر سطرهای مخفی را در محاسبه در نظر نمی‌گیرد. توجه داشته باشید که سایر توابع اکسل مانند SUM این ویژگی را ندارند. یعنی در هر حال جمع همه سلول‌ها را محاسبه می‌کنند حتی اگر با فیلتر در حالت مخفی قرار گرفته باشند،
  • این تابع به سطرهای HIDE شده حساس است: می توان تابع SUBTOTAL را طوری تنظیم کرد که سطر و ستون ها که در حالت مخفی قرار دارند را محاسبه کند و یا نکند.
  • این تابع به خودش حساس است: یعنی اگر به این تابع آدرس سلولهایی را بدهیم که برخی از آنها دارای فرمول SUBTOTAL هستند، آن سلولها را محاسبه نمی کند.

بعنوان مثال:

=SUBTOTAL(109,A1:A10)

یعنی سلولهای A1:A10 را جمع بزن (کد 109 یعنی SUM)   / اگر فیتلر بودند آنهایی که نمایش داده نمی شوند را محاسبه نکن / اگر در A1:A10 به تابع SUBTOTAL  برخورد کردی آن را نیز محاسبه نکن.

شما می تواند سایر کدها را اینجا ببینید.

 

تابع AGGREGATE در فرمول نویسی:

این فرمول در Excel تمامی ویژگیهای بالا را دارد علاوه بر اینکه:

  •  کارهای بیشتری را نسبت به تابع SUBTOTAL انجام دهد مثلا : محاسبه میانه و یا کوچکترین SMALL و بزرگترین LARGE
  • می تواند به خطاهای EXCEL حساس باشد:

همانطور که می دانید اگر یک سلول حاوی خطا یا ERROR باشد هر آنچیز که به این سلول وابسته است نیز ERROR می شود اما تابع AGGREGATE این خطاها را در نظر نمی گیرد.

بعنوان مثال:

=AGGREGATE(9,3,A1:A10)

یعنی سلولهای A1:A10 را جمع بزن (کد 9 ) و مخفی ها را در نظر نگیر + خطاها را در نظر نگیر + توابع SUBTOTAL , AGGREGAT را در نظر نگیر (کد 3)

 

کد رفتار
0 or omitted Ignore nested SUBTOTAL and AGGREGATE functions
1 Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2 Ignore error values, nested SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error v

 

 

 

 

به این مطلب پاسخ دهید