Công ty của Jeffrey có một số báo cáo sử dụng nhiều công thức mảng CSE (Ctrl + Shift + Enter). Khi ai đó quên giữ Ctrl và Shift khi nhấn Enter, các công thức kết quả không giống với câu trả lời đúng. Việc kiểm tra từng ô, tìm kiếm các dấu ngoặc vuông vừa tẻ nhạt vừa tốn thời gian. Jeffrey tự hỏi liệu có cách nào nhanh chóng để tìm “dấu ngoặc nhọn” hoặc treo cờ báo lỗi nếu Ctrl + Shift + Enter không được nhấn khi cần thiết không?

Không có phương pháp nội tại hoặc công thức nào để thực hiện việc này trong Excel. Điều này có nghĩa là bạn cần chuyển sang giải pháp dựa trên macro.

May mắn thay, VBA cung cấp một số cách khác nhau để bạn có thể tiếp cận vấn đề này. Một cách tiếp cận là chỉ cần sử dụng một công thức để đảm bảo rằng mỗi công thức trong một vùng chọn thực sự là một công thức mảng.

Sub MakeCSE1()

Dim rCell As Range

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

Macro này giả định rằng bạn sẽ chọn các ô được “chuyển đổi” trước khi thực sự chạy macro. Nếu muốn, bạn có thể xác định một dải ô (đặt tên cho dải ô) và sau đó chạy một macro tương tự luôn hoạt động trên dải ô đó.

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

Macro này tìm kiếm một phạm vi có tên CSERange và sau đó kiểm tra mọi ô trong phạm vi. Nếu nó không chứa công thức mảng, thì công thức được chuyển đổi thành công thức mảng.

Lưu ý việc sử dụng thuộc tính HasArray để kiểm tra xem một ô có chứa công thức mảng hay không. Thuộc tính này thực sự có thể hữu ích theo những cách khác. Ví dụ: bạn có thể tạo một hàm đơn giản do người dùng định nghĩa, chẳng hạn như sau:

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

Hàm này trả về True nếu ô được trỏ đến không chứa công thức mảng. Nếu nó chứa một, thì trả về False. Sau đó, bạn có thể sử dụng hàm này làm cơ sở cho định dạng có điều kiện. Tất cả những gì bạn cần làm là tạo một định dạng sử dụng nó theo cách này:

=NoCellArray1(A5)

Vì NoCellArray trả về True nếu ô không chứa công thức mảng, nên định dạng có điều kiện của bạn có thể đặt màu của ô thành màu đỏ hoặc đặt một số dấu hiệu hiển thị khác cho thấy ô không có công thức mảng cần thiết. Bạn cũng có thể sử dụng hàm sau để thực hiện tác vụ tương tự:

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

End Function

Một cách tiếp cận hoàn toàn khác là thêm một cái gì đó vào công thức của bạn để cho phép chúng dễ dàng được nhận dạng là công thức mảng. Ví dụ: bạn có thể thêm phần sau vào cuối bất kỳ công thức mảng nào của mình:

+N("{")

Điều này không ảnh hưởng đến tính toán theo bất kỳ cách nào nhưng có thể dễ dàng kiểm tra xem nó có ở đó hay không. Việc kiểm tra có thể được thực hiện bởi trình xử lý sự kiện, chẳng hạn như sau:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Selection.FormulaArray, 5) = "(""{"")" Then         ActiveCell.Select         Selection.FormulaArray = ActiveCell.Formula     End If End Sub

Lưu ý rằng trình xử lý sẽ kiểm tra xem công thức có kết thúc bằng (“\ {“) hay không và nếu có, nó buộc công thức được coi là công thức mảng. Điều tuyệt vời của cách tiếp cận này là bạn sẽ không bao giờ phải nhấn Ctrl + Shift + Enter trên trang tính nữa — trình xử lý sự kiện sẽ giải quyết việc đó cho bạn. Nếu, tại một số điểm, bạn muốn chuyển đổi công thức trở lại phiên bản thông thường (không phải mảng), chỉ cần sửa đổi công thức để công thức không bao gồm + N (“\ {“).

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (478) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: