شمارش و جمع مقادیر در اکسل

یکی از متداول ترین عملیات بر روی داده های صفه گسترده، شمارش و جمع کردن مقادیر عددی و دیگر عناصر سلول های کاربرگ  با یکدیگر است. به نظر می رسد که کاربران همیشه به دنبال فرمول هایی هستند که عملیات فروش یا جمع اقلام مختلف موجود دریک کاربرگ با یکدیگر را انجام دهند. در این فصل، مثال های متنوع زیادی ارائه شده اند که می توانند برای کارهای مختلف مورد نظر به کار گرفته شونند.

شمارش و جمع کردن سلول های کاربرگ با یکدیگر

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

جدول زیر توابع اکسل که در هنگام ساخت فرمول های شمارش یا جمع به کار گرفته می شوند را فهرست کرده است. البته تمام این توابع در این فصل بررسی نخواهد شد. همچنین اگر هیچ کدام از توابع جدول نتواند مشکل شما را حل کند، احتمالا باید از یک فرمول آرایه ای استفاده کنید.

دقت کنید که اگر داده های شما به شکل جدول باشد می توانید از خصوصیت AutoFilter برای انجام بسیاری از عملیات شمارش و جمع استفاده کنید. برای این کار کافی است ملاک های مربوط به AutoFilter را تنظیم کنید تا جدول تنها ردیف هایی که با آن ملاک تطبیق دارند را نمایش دهد. همچنین می توانید فرمول هایی بسازید که در ردیف Total جدول، شمارش یا جمع دلخواه شما را انجام دهد.

 

تابع	عملکرد
COUNT	تعداد سلول هایی که حاوی یک مقدار عددی عستند را بر می گرداند.
COUNTA	تعداد سلول هایی که غیر تهی هستند را بر می گرداند.
COUNTBLANK	تعداد سلول هایی که تهی هستند را برمی گرداند.
COUNTIF	تعداد سلول هایی که با یک ملاک مشخص و تطبیق دارند را بر می گرداند.
COUNTIFS	تعداد سلول هایی که با یک ملاک مشخص و تطبیق دارند را بر می گرداند.
DCOUNT	با استفاده از پایگاه داده ی کاربرگ، تعداد رکوردهای که با ملاک های مشخص شده تطبیق دارند را شمارش می کند.
DCOUNTA	با استفاده از پایگاه داده ی کاربرگ، تعداد رکوردهای غیر تهی که با ملاک های مشخص شده تطبیق دارند را می شمارد.
DSUM	با استفاده از پایگاه داده ی کاربرگ، مجموع مقادیر عددی یک ستون که با ملاک های مشخص شده تطبیق دارند را بر می گرداند.
FREQUENCY	تعداد دفعات تکرار یک مقدار عددی در یک دامنه را محاسبع می کند و یک آرایه‌ی عمودی از اعداد را بر می گرداند. این تابع تنها در فرمول های آرایه ای چند سلولی استفاده می شود.
SUBTOTAL	وقتی آرگومان های اول این تابع 2، 3،102 یا 103 باشد، تعداد سلول هایی که شامل مقدار زیر مجموع(Subtotal) هستند را مشخص می کند. وقتی ارگومان نخست آن 9یا 109 باشد، مجموع سلول هایی که شامل مقدار Subtotal هستند را بر می گرداند.
SUM	حاصل جمع آرگومان های خود را برمی گرداند
SUMIF	حاصل جمع سلول هایی که با ملاک مشخص شده تطبیق دارند را بر می گرداند.
SUMIFS	حاصل جمع سلول هایی که با چند ملاک مشخص شده تطبیق دارند را بر می گرداند.
SUMPRODUCT	سلول های موجود در دو یا چند دامنه را در هم ضرب می کند و حاصل جمع آنها را بر می گرداند.

 

انجام سریع عملیات شمارش جمع

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

فرمول های اصلی شمارش

کار با فرمول های اصلی شمارش که در این بخش معرفی خواهند شد، بسیار ساده است. این توابع معرف توانایی توابع شمارش در اکسل برای شمردن تعداد سلول  هایی در دامنه ی انتخاب شده که با ملاک هایی خاص تطبیق دارن هستند. تصویر زیر کاربرگی را نشان می دهد که در ستون E از فرمول هایی برای شرح محتویات دامنه‌ی A1:B10 استفاده می کند. این دامنه 20 سلولی،Data نامیده می شود و سامل داده های متنوعی مانند مقادیر عددی، رشته های متنی، مقادیر منطقی، پیغام های خطا و سلول های خالی است.

 

 شمارش تعداد کل سلول ها

برای بدست آوردن تعداد کل سلول ها در یک دامنه اعم از سلول های خالی و غیر خالی باید از فرمول زیر استفاده کنید. این فرمول، تعداد سلول های دامنه ی Data را بر می گرداند. شیوه ی محاسبه در این فرمول، ضرب تعداد ردیف ها در تعداد ستون ها است:

=ROW(Data)*COLOMNS(Data)

این فرمول در صورتی که در دامنه‌ی Data، سلول های ناپیوسته و غیر مجاور وجود داشته باشند پاسخ نمی دهند. به عبارت دیگر دامنه ی Data باید دامنه مستطیلی شکل و پیوسته باشد.

شماره سلول های تهی

فرمول زیر تعداد سلول های تهی یا خالی در دامنه ی Data را بر می گرداند:

=COUNTBLANK(Data)

تابعCOUNTBLANK، سلول هایی حاوی فرمول هستند و یک رشته تهی  را به عنوان پاسخ بر می گردانند هم شمارش می کند. برای مثال، فرمول زیر در صورتی که مقدار عددی سلول A1 بزرگتر از 5 باشد، یک رشته ی تهی را بر می گرداند و اگر سلولی با این شرط تطبیق داشته باشد، تابعCOUNTBLANK، ان را در عملیات شمارش به عنوان یک سلول خالی به حساب خواهد آورد:

=IF(A1>5͵””͵A1)

می توانید تابعCOUNTBLANK با آرگومانی که شامل کل ردیف یا ستون باشد نیز استفاده کنید. برای مثال، فرمول زیر تعداد سلول های خالی در ستون A را بر می گرداند:

=COUNTBLANK (A:A)

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

=COUNTBLANK (Sheet1!1:1048576)

شمارش سلول های غیرتهی

برای شمارش سلول های غیر تهی از تابع COUNTA استفاده کنید. فرمول زیر از این تابع برای مشخص کردن تعداد سلول های غیر تهی در دامنه ی Data استفاده می کنید:

= COUNTA (Data)

تابع  COUNTA سلول هایی که حاوی مقادیر عددی، عبارت های متنی یا مقادیر منطقی هستند را در عملیات شمارش به حساب می اورد. البته اگر سلولی حاوی فرمولی که پاسخ آن یک رشته ی تهی است، باشد نیز در شمارش تابع به حساب خواهد آمد، با وجود اینکه ظاهرا خالی به نظر می رسد.

شمارش سلول های عددی

اگر بخواهید تنها سلول های عددی را در دامنه ای بشمارید باید از فرمول زیر استفاده کنید( با اینک فرض که دامنه‌ی مورد نظر، Data  نام دارد):

= COUNT (Data)

سلول هایی که شامل مقادیر تاریخ و ساعت هستند نیز سلول عددی محسوب می شوند و سلول هایی که حاوی مقادیر منطقی هستند، سلول عددی به حساب نمی آید.

شمارش سلول های متنی

برای شمارش سلول های متنی در یک دامنه باید از یک فرمول آرایه ای استفاده کنید. فرمول آرایه ای زیر تعداد سلول های متنی در دامنه ی Data را بر می گرداند:

{=SUM(IF(ISTEXT(Data)͵1))}

 

شمارش سلول های غیر متنی

فرمول آرایه ای زیر از تابع ISNONTEXT استفاده می کند که اگر آرگومان آن به یک سلول غیر متنی ارجاع شود، مقدارTRUE  را بر می گرداند. این فرمول، تعداد سلول هایی که حاوی مقدار متنی نیستند یا خالی هستند را می شمارد:

{=SUM(IF(ISNONTEXT(Data)͵1))}

 شمارش مقادیر منطقی

فرمول آرایه ای زیر تعداد مقادیر منطقی را در دامنه ای به نام Date می شمارد:

{=SUM(IF(ISLOGICAL(Data)͵1))}

شمارش مقادیر خطا در یک دامنه

اکسل با سه تابع برای تعیین این که آیا سلولی حاوی یک مقدار خطاست یا خیر به کاربر کمک می کند:

1- ISERROE: اگر سلول حاوی یک مقدار خطا باشد مقدارTRUE را بر می گرداند.

2- ISERR: اگر سلول حاوی هر مقدار خطا به جز #N/A باشد، مقدار TRUE را بر می گرداند.

3- ISNA: اگر سلول تنها حاوی مقدار خطای #N/A باشد، مقدار TRUE را بر می گرداند.

می توانید از این توابع در هر فرمول آرایه ای استفاده کنید تا تعداد خطاهای موجود در یک دامنه را شمارش کند. برای مثال، فرمول آرایه ای زیر تعداد مجموع مقادیر خطا در دامنه ای به نام Date را بر می گردند:

{=SUM(IF(ISERROR(Data)͵1))}

بسته به نیاز کاربر می توانید به جای تابع iserror، از تابع ISERR  یا ISNA نیز استفاده کنید. اگر بخواهید انواع خاصی از مقادیر خطا را شمارش کنید، می توانید از تابع COUNTIF استفاده کنید. برای مثال فرمول زیر، تعداد مقادیر خطای #DIV/0! در دامنه ی Date را بر می گرداند:

=COUNTIF(Data͵”#DIV/0!”)

---

نویسنده : --