بررسی ورود صحیح فرمول های آرایه در اکسل

Question

شرکت جفریس تعدادی گزارش دارد که از تعداد زیادی فرمول آرایه CSE ( Ctrl+Shift+Enter ) استفاده می کند. وقتی شخصی فراموش می کند که Ctrl و Shift را هنگام فشار دادن Enter نگه دارد ، فرمول های به دست آمده با پاسخ صحیح برابر نیستند. بررسی هر سلول، جستجوی براکت های { } هم خسته کننده و هم وقت گیر است. جفری به این فکر می کند که اگر Ctrl+Shift+Enter در زمانی که باید فشار داده نشود، راه سریعی برای یافتن «پرانتزهای گمشده» یا بالا بردن پرچم خطا وجود دارد ؟

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

Sub MakeCSE1()
    Dim rCell As Range

    For Each rCell In Selection
        rCell.FormulaArray = rCell.Formula
    Next rCell
End Sub

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

Sub MakeCSE2()
    Dim rng As Range
    Dim rCell As Range
    Dim rArea As Range

    Set rng = Range("CSERange")
    For Each rArea In rng.Areas
        For Each rCell In rArea.Cells
            If rCell.HasArray = False Then
                rCell.FormulaArray = rCell.Formula
            End If
        Next rCell
    Next rArea
End Sub

این ماکرو به دنبال محدوده ای به نام CSERange می گردد و سپس تمام سلول های محدوده را بررسی می کند. اگر فرمول آرایه ای نداشته باشد، فرمول به فرمول آرایه تبدیل می شود.

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

Function NoCellArray1(rng As Range) As Boolean
    NoCellArray1 = Not rng.HasArray
End Function

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

=NoCellArray1(A5)

از آنجایی که اگر سلول حاوی فرمول آرایه نباشد، NoCellArray True را برمی‌گرداند، فرمت شرطی شما می‌تواند رنگ سلول را قرمز تنظیم کند یا نشانه‌های قابل مشاهده دیگری را تنظیم کند که نشان می‌دهد سلول فرمول آرایه لازم را ندارد. همچنین می توانید از تابع زیر برای انجام همان کار استفاده کنید:

Function NoCellArray2(rng As Range) As Boolean
    NoCellArray2 = (Evaluate(rng.FormulaArray) <> rng.Value)
End Function

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

+N("{")

این به هیچ وجه بر محاسبات تأثیر نمی گذارد، اما می توان به راحتی بررسی کرد که آیا وجود دارد یا خیر. بررسی را می توان توسط یک کنترل کننده رویداد انجام داد، مانند موارد زیر:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Right(Selection.FormulaArray, 5) = "(""{"")" Then
        ActiveCell.Select
        Selection.FormulaArray = ActiveCell.Formula
    End If
End Sub

توجه داشته باشید که کنترل کننده بررسی می کند که آیا فرمول با ("{") ختم می شود یا خیر، و اگر پایان می یابد، فرمول را مجبور می کند که به عنوان یک فرمول آرایه در نظر گرفته شود. نکته مهم در مورد این رویکرد این است که هرگز مجبور نخواهید شد دوباره Ctrl+Shift+Enter را در برگه فشار دهید—کنترل رویداد از آن برای شما مراقبت می کند. اگر در نقطه ای، می خواهید فرمول را به نسخه معمولی (غیر آرایه ای) تبدیل کنید، به سادگی فرمول را تغییر دهید تا +N ("{") را شامل نشود.