شمارش داده ها در اکسل

هر آنچه درباره شمارش داده ها در اکسل باید بدانید را در این آموزش اکسل مقدماتی می توانید پیدا کنید. توابع بسیار کاربردی که برای شمارش شرطی و غیر شرطی داده ها نیاز داریم.

7 روش برای شمارش داده ها در اکسل وجود دارد:

1. شمارش سلول های عددی،سلول های حاوی داده و سلول های خالی

· شمارش سلول های عددی با COUNT

در ابتدا داده های نمونه را در صفحه کار خود را وارد کنید.

مثل تصویر در سلول A7، فرمول COUNT را وارد کنید.

(COUNT(A1:A5=

و با زدن enter ، مشاهده می کنید که نتیجه آن عدد 3 میشود که به معنای تعداد سلول هایی است که حاوی اعداد هستند و سلول A1 شمارش نمی شود، زیرا حاوی متن است.

· شمارش سلول های حاوی داده با COUNTA

درابتدا داده های نمونه را در صفحه کار خود را وارد کنید.مثل تصویر در سلول A7 فرمول

(COUNTA(A1:A5=

را وارد کنید و با زدن ENTER، مشاهده می کنید که نتیجه آن 4 میشود که به معنای تعداد سلول هایی است که حاوی داده هستند.

· شمارش سلول های خالی با COUNTBLANK

درابتدا داده های نمونه را در صفحه کار خود را وارد کنید.مثل تصویر در سلول A7 فرمول

(COUNTBLANK(A1:A5=

را وارد کنید و با زدن ENTER، مشاهده می کنید که نتیجه آن 1 میشود که به معنای تعداد سلول های خالی موجود در داده هایمان هست.

نکته: سلول هایی که شبیه سلول های خالی هستند:

فرمول COUNTBLANK سلول های خالی را شمارش می کنند، اما اگر در یک سلول به ظاهر خالی یک space هم خورده باشد فرمول COUNTBLANK آن سلول را بعنوان متن شناخته و شمارش نمی کند.

2.شمارش سلول هایی که با یک شرط مطابقت دارند

· یک شرط را دقیقا مطابقت دهید

درابتدا داده های نمونه را در صفحه کار خود را وارد کنید. سپس مثل تصویر در سلول A12 فرمول:

(“COUNTIF(A1:A10,”Pen=

را وارد کنید (Pen کلمه ایی است که می خواهیم بدانیم خود کلمه Pen در داده های ما چند بار تکرار شده است ) و با زدن ENTER، مشاهده می کنید که نتیجه آن 4 میشود که به معنای تعداد سلول هایی است که درون آن کلمه مورد نظر ما (Pen) می باشد.

·شرط را در یک رشته متنی مطابقت دهید

درابتدا داده های نمونه را در صفحه کار خود را وارد کنید. سپس مثل تصویر در سلول A12 فرمول

(“*COUNTIF(A1:A10,”*Pen=

را وارد کنید (Pen کلمه ایی است که می خواهیم بدانیم ،خود کلمه Pen یا کلماتی که در آن Pen وجود دارد چند بار تکرار شده است) و با زدن ENTER، مشاهده می کنید که نتیجه آن 6 می شود که به معنای تعداد سلول هایی است که حاوی کلمه مورد نظر (Pen) می باشد.

3. شمارش اعداد در اعداد

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

در تصویر بالا، 4 مورد حاوی عدد “1 ” است ولی جواب فرمول COUNTIF که در سلول A9 نوشته شده عدد 3 می باشد زیرا در سلول A2 عدد 1 را به این دلیل که سلول شامل یک عدد واقعی است، نه یک رشته متنی نمی شمارد. برای حل این مشکل ما بایستی از تابع، FIND یا SEARCH برای شمارش اعداد خاص در اعداد استفاده کنیم.

در تصویر زیر فرمول های FIND ، SEARCH، COUNTIFدر ستون های C، D و E برای جستجوی “1” استفاده می شود.

- (“*C2: =COUNTIF(A2,”*1
- ((D2: =–ISNUMBER(FIND(1,A2
- ((E2: =–ISNUMBER(SEARCH(1,A2

- تابع ISNUMBER در ابتدای فرمول FINDو SEARCHبه ما بصورت مجزا نشان میدهد که در هر ردیف در صورت موجود بودن عدد مورد نظر عدد (1) و ناموجود بودن، عدد(0) را به ما نشان دهد . راه حل بعدی این است که ما می توانیم به جای چک کردن هر ردیف به صورت جداگانه، از تابع SUMPRODUCT در ابتدای فرمول های بالا تعداد کل را بدست آوریم

- (((A9: =SUMPRODUCT(–ISNUMBER(FIND(1,$A$2
- (((A10: =SUMPRODUCT(–ISNUMBER(SEARCH(1,$A$2:$A$7

4.شمارش سلول های “کمتر از” یا “بزرگتر از”

1) تعداد سلولهای بزرگتر یا مساوی

در ابتدا سلول مورد نظر خود را انتخاب کنید (سلول A12 در این مثال) و سپس فرمول را در آن نوشته

(“COUNTIF(B1:B10,”>=10=

(در این مثال ما بدنبال عدد بزرگتر یا مساوی 10 هستیم و چون یک معیار عددی است، باید در علامت نقل قول دوگانه قرارگیرد) اما به جای تایپ شرط بصورت مستقیم در یک فرمول، اگر سلول B12 حاوی شماره 10 باشد، می توانیم به یک سلول ارجاع دهیم. به عنوان مثال

=COUNTIF(B1:B10,”>=” & B12)

2)شمارش سلول بین دو عدد

شما می توانید فرمول COUNTIF را برای شمارش ردیف هایی که در محدوده ای از مقادیر هستند، ترکیب کنید. در این مثال، ردیف هایی را که در آن مقدار بین 5 تا 10 هستند را می شماریم.

در ابتدا سلول مورد نظر خود را انتخاب کنید (در این مثال سلول A12 ) و فرمول را تایپ کنید

=COUNTIF(B1:B10,”>=5″)-COUNTIF(B1:B10,”>10″)

5. شمارش سلول هایی که دارای معیارهای مختلف هستند

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

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

در این مثال، یک تاریخ در A2 وجود دارد و لیست سفارش در سلول A5: A26 دارای تاریخ است. با نوشتن تابع SUMPRODUCT در سلول D2 می توانیم تمام تاریخ هایی را که در همان ماه و سال به عنوان تاریخ در سلول A2 قرار دارند را بشماریم.

=SUMPRODUCT((MONTH(A5:A26)=MONTH(A2))*(YEAR(A5:A26)=YEAR(A2)))

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

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

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

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

=SUBTOTAL(3,D2:D10)

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

پس از فیلتر کردن ردیف ها در یک لیست، می توانید از تابع AGGREGATE برای شمارش ردیف های قابل مشاهده استفاده کنید. این تابع در اکسل 2010 معرفی شده و شبیه به SUBTOTAL است، اما دارای 19 عملکرد در مقایسه با 11 توابع SUBTOTAL است. مزیت دیگر این است که میتواند اشتباهات و ردیفهای پنهان را نادیده بگیرد.

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

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

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

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

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

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

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

4)برای شمارش آیتم هایی که در یک لیست فیلتر شده، از فرمول 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))