جمع کردن فقط سلول های حاوی فرمول

Question

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

راه های زیادی برای دستیابی به نتیجه دلخواه وجود دارد، اما من فقط بر روی چند مورد از آنها تمرکز می کنم.

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

1. سلول هایی را که می خواهید جمع کنید انتخاب کنید.
2. F5 را فشار دهید . اکسل کادر گفتگوی Go To را نمایش می دهد.
3. روی دکمه Special کلیک کنید. اکسل کادر محاوره ای Go To Special را نمایش می دهد.
4. دکمه رادیویی Formulas را انتخاب کنید.
5. روی OK کلیک کنید. اکسل سلول های انتخاب شده را طوری تغییر می دهد که فقط آنهایی که حاوی فرمول هستند انتخاب شوند.

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

اگر رویکرد مبتنی بر فرمول را ترجیح می دهید، یکی از عوامل کلیدی در اینجا نسخه اکسل است که استفاده می کنید. مایکروسافت تابع ISFORMULA را با انتشار اکسل 2013 معرفی کرد، بنابراین اگر از آن نسخه (یا جدیدتر) استفاده می کنید، تعیین مبلغ مورد نظر بسیار آسان است. فقط از این فرمول استفاده کنید:

=SUMPRODUCT(A1:A5,--ISFORMULA(A1:A5))

این فرمول فرض می کند که محدوده داده ای که می خواهید جمع کنید A1:A5 است. علامت "منهای مضاعف" قبل از تابع ISFORMULA برای تبدیل مقادیر TRUE و FALSE (که توسط ISFORMULA برگردانده شده است) به 1 یا 0 استفاده می شود.

اگر ترجیح می دهید از فرمول آرایه استفاده کنید، می توانید از فرمول زیر استفاده کنید:

=SUM(IF(ISFORMULA(A1:A6),A1:A6))

فقط به یاد داشته باشید که با استفاده از Ctrl+Shift+Enter وارد شوید و نتیجه مناسب را خواهید گرفت.

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

Function SumFormulas(ByVal r As Range)
    Dim c As Range
    Dim s As Double

    s = 0
    For Each c In r.Cells
    
        If c.HasFormula And IsNumeric(c) Then
            s = s + c.Value
        End If
    Next c
    SumFormulas = s
End Function

توجه داشته باشید که کد بررسی می کند تا مطمئن شود سلول حاوی فرمولی است (با استفاده از خاصیت HasFormula) و بررسی می کند تا از عددی بودن آن (با استفاده از تابع IsNumeric) مطمئن شود. هر دو لازم هستند زیرا امکان داشتن فرمول مبتنی بر متن در یک سلول وجود دارد و شما نمی خواهید سعی کنید نتایج چنین فرمولی را در جمع خود بگنجانید.

برای استفاده از تابع، به سادگی می توانید از موارد زیر در سلول کاربرگ استفاده کنید، با این فرض که می خواهید محدوده A1:C7 را جمع کنید:

=SumFormulas(A1:C7)

اگر به دلایلی نمی خواهید به تابع ISFORMULA تکیه کنید، رویکرد مبتنی بر کلان در نسخه های اکسل فراتر از اکسل 2010 نیز کار خواهد کرد. (به عنوان مثال، اگر باید از سازگاری با نسخه های قدیمی اکسل اطمینان حاصل کنید.)