پر كردن فضاي خالي بين سلول هاي پر در اكسل

یکشنبه ۲۴ اردیبهشت ۹۶ | ۱۰:۴۷ ۱,۸۱۵ بازديد

با توابع شمارش سلول‌هاي پر و خالي در اكسل آشنا شويد.۱- تابع COUNTA: شمارش تعداد كاراكترها و همچنين تعداد سلول‌هاي پراين تابع در دو حالت استفاده مي‌شود.

حالت ۱) شمارش تعداد كاراكتر‌هاي داخل پرانتز كه با ويرگول از هم جدا شده‌اند اعم از حروف و اعداد فارسي و انگليسي و حتي فاصله. كه در اين حالت دستور اين تابع به صورت زير است:

=COUNTA(value1, [value2], ...)

مثلا جواب فرمول زير برابر است با ۳

=COUNTA(a,100,ش)

حالت ۲) شمارش تعداد سلول‌هاي پر شامل هر نوع كاراكتري حتي فاصله (منظور سلول خالي نيست، بلكه منظور كاراكتر فاصله است). در اين حالت دستور اين تابع به صورت زير است:

=COUNTA(range)

مثل:

=COUNTA(B2:B10)

تركيب دو حالت ۱ و ۲ را نيز مي‌توان براي تابع COUNTA استفاده كرد مثل:

۲- تابع COUNT: شمارش تعداد اعداد و تعداد سلول‌هاي شامل عدد از جمله تاريخ‌ها

از اين تابع هم مشابه تابع COUNTA در دو حالت مي‌توان استفاده كرد.

حالت ۱) شمارش تعداد اعداد داخل پرانتز كه با ويرگول از هم جدا شده‌اند. دستور اين تابع در اين حالت به صورت زير است:

=COUNT(value1, [value2], ...)

مثلا جواب فرمول زير برابر است با ۲

=COUNT(1, 5, ش)

حالت ۲) شمارش تعداد سلول‌هاي پر شامل عدد از جمله تاريخ. دستور اين تابع در اين حالت به صورت زير است:

=COUNT(range)

مثل:

=COUNT(B2:B10)

تركيب دو حالت ۱ و ۲ را نيز مي‌توان براي تابع COUNT استفاده كرد مثل:

۳- توابع COUNTIF و COUNTIFS: شمارش شرطي

براي شمارش تعداد سلول‌هايي كه داراي معيار مشخصي باشند از توابع COUNTIF و COUNTIFS استفاده مي‌شود. در واقع اين دو تابع، توابع شمارش شرطي هستند.

تابع COUNTIF:

از اين تابع زماني استفاده مي‌شود كه داراي يك شرط باشيم. ساختار اين تابع به صورت زير مي‌باشد:

=COUNTIF(range, criteria)

اين تابع دو آرگومان يا ورودي دارد. اولين آرگومان آدرس فهرستي است كه شمارش بايد براي آن انجام شود. دومين آرگومان. معيار يا شرط شمارش است. مثلا، خروجي فرمول زير، تعداد سلول‌هايي است كه در ناحيه B2:B7 داراي عددي برابر با ۲۰۰ مي‌باشند. يا:

=COUNTIF(B2:B7,200)

يا خروجي فرمول زير، تعداد سلول‌هايي است كه در ناحيه B2:B7 داراي كاراكتري برابر با كاراكتر موجود در سلول E2 مي‌باشند.

=COUNTIF(B2:B7,E2)

بجاي حالت برابري در فرمول‌هاي فوق مي‌توان از حالت‌هاي كوچك‌تر و كوچك‌ترمساوي و همچنين بزرگ‌تر و بزرگ‌ترمساوي نيز استفاده كرد. مثال:

=COUNTIF(B2:B7,">200")

يا

=COUNTIF(E2:E7,"<=300")

اما اگر بخواهيم بجاي مقايسه با يك عدد با عدد موجود در يك سلول خاص اين مقايسه انجام شود، بايد فرمول فوق را كمي تغيير داد. بطور مثال مي‌خواهيم بدانيم مطابق تصوير زير در محدوده E2:E7 چند سلول بيشتر و چند سلول كمتر از سلول B7 هستند. براي اينكار از فرمول‌هاي زير استفاده مي‌كنيم:

=COUNTIF(E2:E7,">"&B7)

=COUNTIF(E2:E7,"<"&B7)

خروجي فرمول‌هاي فوق به ترتيب ۴ و ۱ مي‌باشند.

اين تابع همچنين مي‌تواند براي داده‌هاي غير عددي نيز استفاده شود. مثلاً تابع

=COUNTIF(B2:B25,"Excel")

سلول‌هايي را شمارش مي‌كند كه در آن‌ها كلمه “Excel” قرار داشته باشد. (اين تابع به كوچكي و بزرگي حروف حساس نيست).

تابع COUNTIFS:

اين تابع زماني استفاده مي‌شود كه داراي بيش از يك شرط باشيم. ساختار اين تابع به صورت زير مي‌باشد:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

مثال:

=COUNTIFS(B2:B7,200,E2:E7,300)

كه در اين صورت اگر هر دو شرط همزمان برقرار باشند آنگاه اين سلول‌ها شمارش خواهند شد.

تمام حالت‌هاي گفته شده براي تابع Countif، براي تابع Countifs نيز قابل اجرا است.

مثال ۱) در تصوير فوق، در ناحيه E2:E7، تعداد سلول‌هاي حاوي عدد ۱۰۰ يا ۳۰۰ را محاسبه كنيد.

به نظر مي‌رسد كه مشابه مثال قبل با دو شرط روبرو هستيم و بايد از تابع Countifs استفاده كنيم:

=COUNTIFS(E2:E7,100,E2:E7,300)

اما استفاده از فرمول فوق اشتباه است، اين فرمول بيانگر اين است كه بايد هر دو شرط با هم برقرار باشد يعني تابع Countifs پس از بررسي شرط اول يعني برابري با ۱۰۰، شرط دوم يعني برابري با ۳۰۰ را بررسي مي‌كند كه ما چنين چيزي نمي‌خواهيم. فرمول بايد طوري باشد كه در صورتي كه يكي از دو حالت نيز برقرار بود شمارش انجام شود. البته اين نكته را نيز بايد دانست كه درون تابع Countifs نمي‌توان از OR استفاده كرد. براي حل اين مشكل دو راهكار وجود دارد:

در واقع ما با يك شرط روبرو هستيم. بايد دو عدد را درون كوشه بگذاريد و همراه با تابع Countif از تابع Sum هم استفاده كنيد: