یکی از متداول ترین عملیات بر روی داده های صفه گسترده، شمارش و جمع کردن مقادیر عددی و دیگر عناصر سلول های کاربرگ با یکدیگر است. به نظر می رسد که کاربران همیشه به دنبال فرمول هایی هستند که عملیات فروش یا جمع اقلام مختلف موجود دریک کاربرگ با یکدیگر را انجام دهند. در این فصل، مثال های متنوع زیادی ارائه شده اند که می توانند برای کارهای مختلف مورد نظر به کار گرفته شونند.
اغلب فرمول شمارش می تواند تعداد سلول هایی در یک دامنه ی مشخص که با ملاک های خاصی تطبیق دارند را برگرداند و فرمول جمع، حاصل جمع مقادیر سلول هایی در یک دامنه مشخص که با ملاک های خاصی تطبیق دارند را بر می گرداند.
جدول زیر توابع اکسل که در هنگام ساخت فرمول های شمارش یا جمع به کار گرفته می شوند را فهرست کرده است. البته تمام این توابع در این فصل بررسی نخواهد شد. همچنین اگر هیچ کدام از توابع جدول نتواند مشکل شما را حل کند، احتمالا باید از یک فرمول آرایه ای استفاده کنید.
دقت کنید که اگر داده های شما به شکل جدول باشد می توانید از خصوصیت 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!”)
نویسنده : --