شمارش داده ها در اکسل(پارت اخر)

۷.شمارش ردیف هایی که در یک لیست فیلتر شده اند

 پس از فیلتر کردن ردیف ها در یک لیست اکسل، شما می توانید با استفاده از توابع زیرتنها ردیف های قابل مشاهده را شمارش کنید.

 ۱)برای شمارش ساده تعداد قابل مشاهده یا تمام داده های قابل مشاهده، تابعSUBTOTAL

 در ابتدا یک فیلتر AutoFilter را به جدول اعمال کنید و حداقل یکی از ستون های جدول را فیلتر کنید. در این مثال، ستون اول برای Binders فیلتر شده است.    سپس سلول را بلافاصله در زیر ستونی که می خواهید جمع کنید انتخاب کنید. و فرمول را تایپ کنید

  =SUBTOTAL(3,D2:D10)

 ۲)برای شمارش اطلاعات قابل مشاهده و نادیده گرفتن اشتباهات، از تابع AGGREGATE

پس از فیلتر کردن ردیف ها در یک لیست، می توانید از تابع AGGREGATE برای شمارش ردیف های قابل مشاهده استفاده کنید. این تابع در اکسل ۲۰۱۰ معرفی شده و شبیه به SUBTOTAL است، اما دارای ۱۹ عملکرد در مقایسه با ۱۱ توابع SUBTOTAL است. مزیت دیگر این است که میتواند اشتباهات و ردیفهای پنهان را نادیده بگیرد.

در ابتدا حداقل یکی از ستونهای جدول را فیلتر کنید. در این مثال، ستون اول برای Binders فیلتر شده است. سلول را انتخاب کنید (در این مثال سلول B1)و فرمول زیر را بنویسید

=AGGREGATE(3,3,Table1[Total])

 ۳)برای شمارش موارد خاص در یک لیست فیلتر شده، از فرمول SUMPRODUCT

 با استفاده از این تکنیک، SUMPRODUCT می تواند  برای شمارش آیتم های قابل مشاهده در یک جدول فیلتر شده مورد استفاده قرار گیرد. در مثال بالا ستون D برای مقادیر بیش از ۱۰۰ فیلتر شده است. فرمول زیر تعدادی از ردیف های قابل مشاهده که حاوی “Pen” در ستون A را شمارش می کند.

 از لیست کشویی در سلول D1 فیلتر برای ردیف های بیش از ۱۰۰را انتخاب کنید سپس

 در سلول A12، نوع:Pen را نوشته ودر سلول B12، فرمول زیر را وارد کنید:

 =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)), –(A1:A10=A12))

۴)برای شمارش آیتم هایی که در یک لیست فیلتر شده، از فرمول UNIQUE

 در این مثال، لیست برای منطقه Central فیلتر شده است، و موارد منحصر به فرد در ستون D شمارش می شوند.

   =SUM(N(IF(ISNA(MATCH(“”””,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”””,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))