شمارش سلول های حاوی فرمول

Question

راب به این فکر کرد که آیا راهی برای شمارش تعداد سلول های حاوی فرمول در یک ردیف یا ستون وجود دارد؟ پاسخ بسیار ساده است، با استفاده از ویژگی Go To اکسل. این مراحل را دنبال کنید:

شکل 1. کادر گفتگوی Go To Special.

1. صفحه کاری را که برای آن شمارش می خواهید نمایش دهید.
2. سطر یا ستونی را که می خواهید فرمول ها را در آن شمارش کنید انتخاب کنید.
3. F5 یا Ctrl+G را فشار دهید . اکسل کادر گفتگوی Go To را نمایش می دهد.
4. روی دکمه Special کلیک کنید. اکسل کادر محاوره ای Go To Special را نمایش می دهد. (شکل 1 را ببینید.)
5. مطمئن شوید که دکمه رادیویی Formulas انتخاب شده است.
6. روی OK کلیک کنید.

خودشه. اکسل تمام سلول های سطر یا ستون حاوی فرمول را انتخاب می کند. (اگر مرحله 2 را رد کنید، اکسل تمام فرمول های کل کاربرگ را انتخاب می کند.) در پایین صفحه، در نوار وضعیت، می توانید تعداد سلول های انتخاب شده را مشاهده کنید. (شکل 2 را ببینید.)

شکل 2. نوار وضعیت تعداد سلول های انتخاب شده را نشان می دهد.

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

اگر از اکسل 2013 یا نسخه جدیدتر استفاده می کنید، می توانید از یک فرمول برای تعیین تعداد فرمول در یک محدوده سلولی استفاده کنید، همانطور که در اینجا نشان داده شده است:

=SUMPRODUCT(--ISFORMULA(A:A))

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

همچنین در صورت تمایل می توانید از یک ماکرو برای تعیین تعداد استفاده کنید. مثال زیر از همان روشی که در مراحل قبلی به صورت دستی توضیح داده شد برای تعیین تعداد استفاده می کند:

Sub CountFormulas1()
    Dim Source As Range
    Dim iCount As Integer

    Set Source = ActiveSheet.Range("A:A")
    iCount = Source.SpecialCells(xlCellTypeFormulas, 23).Count
    ActiveSheet.Range("D1") = iCount
End Sub

این زیر روال، تعداد زیادی از سلول های حاوی فرمول در ستون A را به سرعت برمی گرداند و آن مقدار را در سلول D1 قرار می دهد.

بسیار مفید خواهد بود اگر این رویکرد بتواند به یک تابع تعریف شده توسط کاربر تبدیل شود، مانند این:

Function CountFormulas2(Source As Range)
    CountFormulas2 = Source.SpecialCells(xlCellTypeFormulas, 23).Count
End Function

با این حال، این کار نخواهد کرد. تابع همیشه تعداد سلول های موجود در محدوده Source را برمی گرداند، نه تعداد سلول های حاوی فرمول را. این یک اشکال باطنی در Excels VBA است که SpecialCells در توابع کار نمی کند. فقط در زیر برنامه ها کار می کند. مایکروسافت حتی این مورد را مستند نکرده است (که بتوانم آن را پیدا کنم)، بنابراین به آن به عنوان یک "اشکال" به جای "محدودیت" اشاره می کنم.

با این حال، یک محدودیت واقعی برای کاری که روش SpecialCells می تواند انجام دهد وجود دارد: این روش فقط می تواند تا 8192 سلول را شامل شود. شما می توانید محدوده ای بسیار بزرگتر را تجزیه و تحلیل کنید (مثل زمانی که آن را به کل ستون نگاه می کنید)، اما زیرمجموعه حاصل - محدوده حاصل - فقط می تواند تا 8192 سلول داشته باشد. اگر حاوی تعداد بیشتری باشد، آنگاه SpecialCells "بریک" خواهد شد و محدوده ای (و در نتیجه تعداد) برابر با تعداد سلول های محدوده اصلی را برمی گرداند.

اگر می خواهید یک تابع تعریف شده توسط کاربر برای تعیین تعداد ایجاد کنید، باید به چیزی غیر از روش SpecialCells تکیه کنید. در اینجا روشی وجود دارد که از ویژگی HasFormula استفاده می کند:

Function CountFormulas3(Source As Range)
    Dim c As Range
    Dim iCount As Integer

    iCount = 0
    For Each c In Source
        If c.HasFormula Then iCount = iCount + 1
    Next
    CountFormulas3 = iCount
End Function

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