Rob tự hỏi liệu có cách nào để đếm số ô chứa công thức trong một hàng hoặc cột hay không. Câu trả lời khá đơn giản, đó là sử dụng tính năng Go To của Excel. Làm theo các bước sau:

  1. Hiển thị trang tính mà bạn muốn đếm.

  2. Chọn hàng hoặc cột mà bạn muốn đếm công thức.

  3. Nhấn F5 hoặc Ctrl + G. Excel sẽ hiển thị hộp thoại Đi tới.

  4. Nhấp vào nút Đặc biệt. Excel sẽ hiển thị hộp thoại Đi đến Đặc biệt.

(Xem Hình 1.)

  1. Đảm bảo rằng nút radio Công thức được chọn.

  2. Bấm OK.

Đó là nó. Excel chọn tất cả các ô trong hàng hoặc cột có chứa công thức. (Nếu bạn bỏ qua bước 2, Excel sẽ chọn tất cả các công thức trong toàn bộ trang tính.) Ở cuối màn hình, trong thanh trạng thái, bạn có thể thấy số lượng ô được chọn. (Xem Hình 2.)

image

Hình 2. Thanh trạng thái hiển thị số lượng ô đã chọn.

Nếu vì lý do nào đó, bạn không thấy số lượng trên thanh trạng thái, bạn nên kiểm tra để đảm bảo rằng bạn đã định cấu hình thanh trạng thái của mình để hiển thị số lượng.

Chỉ cần nhấp chuột phải vào bất kỳ chỗ trống nào trên thanh trạng thái và chọn Đếm biểu mẫu các tùy chọn kết quả.

Nếu bạn đang sử dụng Excel 2013 hoặc phiên bản mới hơn, bạn cũng có thể sử dụng công thức để tìm ra có bao nhiêu công thức trong một phạm vi ô, như được hiển thị ở đây:

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

Ví dụ này trả về tổng số của tất cả các công thức trong cột A; bạn có thể dễ dàng thay thế một loạt các ô khác nhau trong công thức. Bất kể phạm vi nào bạn chỉ định, nó không được bao gồm ô nơi bạn đặt công thức cụ thể này — điều này sẽ dẫn đến tham chiếu vòng tròn và có thể xảy ra lỗi.

Nếu muốn, bạn cũng có thể sử dụng macro để xác định số lượng. Ví dụ sau sử dụng cách tiếp cận tương tự để xác định số lượng như đã được mô tả thủ công trong các bước trước đó:

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

Chương trình con này trả về, rất nhanh chóng, tổng số tất cả các ô chứa công thức trong cột A và nhét giá trị đó vào ô D1.

Sẽ rất hữu ích nếu phương pháp này có thể được biến thành một hàm do người dùng định nghĩa, chẳng hạn như sau:

Function CountFormulas2(Source As Range)

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

Tuy nhiên, điều này sẽ không hoạt động. Hàm luôn trả về số lượng ô trong phạm vi Nguồn, không trả về số ô chứa công thức. Đó là một lỗi bí truyền trong VBA của Excel mà SpecialCells không hoạt động trong các hàm; nó chỉ hoạt động trong các chương trình con. Microsoft thậm chí đã không ghi lại điều này (mà tôi có thể tìm thấy), do đó, tôi tham khảo nó như một “lỗi” thay vì là một “hạn chế”.

Tuy nhiên, có một giới hạn thực tế đối với những gì phương pháp SpecialCells có thể làm: Nó chỉ có thể chứa một phạm vi tối đa 8.192 ô. Bạn có thể phân tích một phạm vi lớn hơn nhiều (như trường hợp bạn xem xét toàn bộ cột), nhưng tập hợp con kết quả — phạm vi kết quả — chỉ có thể chứa tối đa 8.192 ô. Nếu nó chứa nhiều hơn, thì SpecialCells sẽ “không thành công” và trả về một phạm vi (và do đó là số lượng) bằng với số ô trong phạm vi ban đầu.

Nếu bạn muốn tạo một hàm do người dùng xác định để xác định số lượng, bạn sẽ cần phải dựa vào một cái gì đó khác ngoài phương thức SpecialCells.

Đây là một cách tiếp cận sử dụng thuộc tính 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

Nếu bạn chọn để macro này đánh giá toàn bộ một cột hoặc toàn bộ một hàng, thì hãy chuẩn bị đợi một chút — có thể mất một lúc để macro này chuyển qua từng ô trong một cột hoặc hàng. Phương pháp SpecialCells thu được kết quả nhanh hơn nhiều so với bước qua từng ô.

_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 (13330) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365.