Khả năng lọc của Excel giúp dễ dàng hiển thị thông tin bạn muốn từ bảng dữ liệu. Cách bạn sử dụng bộ lọc đã được thảo luận trong các vấn đề khác của ExcelTips. Khi bạn đã áp dụng bộ lọc cho dữ liệu của mình, bạn có thể muốn sử dụng một hàm để hiển thị số lượng ô được hiển thị trong một phạm vi đã lọc.

Nếu bạn chỉ cần nhanh chóng biết số lượng, hãy đánh dấu phạm vi đã lọc, nhấp chuột phải vào bất kỳ đâu trên thanh trạng thái và chọn Đếm từ menu Ngữ cảnh kết quả. Trong thanh trạng thái, Excel sẽ hiển thị số ô được hiển thị trong phạm vi bạn đã chọn.

Nếu bạn cần một giải pháp mà bạn có thể sử dụng trong một công thức, bạn nên hướng tới hàm SUBTOTAL. Hàm này cung cấp một số kết quả “tổng phụ” khác nhau, nhưng nó chỉ hoạt động trên dữ liệu được hiển thị. Điều này có nghĩa là thông tin được lọc ra bởi Bộ lọc tự động sẽ không được tính vào những gì SUBTOTAL trả về.

Cú pháp chung của hàm SUBTOTAL như sau:

=SUBTOTAL(type,range)

Tất cả những gì bạn cần làm là chỉ định type và range. Phần phạm vi phải dễ dàng: nó chỉ là một phạm vi tiêu chuẩn. Type specifier có thể là một số từ 1 đến 11, như sau:

Type

Function Performed

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

6

PRODUCT

7

STDEV

8

STDEVP

9

SUM

10

VAR

11

VARP

Lưu ý rằng type specifier mà bạn sử dụng cho biết hàm Excel nào được áp dụng cho kết quả được hiển thị. Do đó, nếu bạn muốn xác định số lượng kết quả được lọc trong phạm vi F3: F27, bạn sẽ sử dụng type specifier của 2, như sau:

=SUBTOTAL(2,F3:F27)

Tất nhiên, việc sử dụng này sẽ đếm các giá trị số trong phạm vi được chỉ định.

Nếu bạn muốn đếm giá trị văn bản, thì bạn nên sử dụng type specifier là 3 để Excel dựa vào hàm COUNTA thay thế.

Bạn cũng nên biết rằng bạn có thể sử dụng type specifier trong phạm vi từ 101 đến 111; chúng giống với các chỉ số được liệt kê ở trên, nhưng chỉ có 100 được thêm vào chúng. Sự khác biệt là các giá trị từ 1 đến 11 hoạt động trên tất cả các ô, trong khi các giá trị từ 101 đến 111 chỉ hoạt động trên các ô không ẩn. Phạm vi thông số phụ này được hỗ trợ trong Excel 2003, nhưng cũng có thể được hỗ trợ trong một số phiên bản Excel trước. (Cách tốt nhất để tìm hiểu? Hãy thử chúng và xem chức năng có bị lỗi đối với bạn hay không.)

Nếu bạn là kiểu người thích giải pháp dựa trên macro, thì bạn có thể muốn tham khảo Cơ sở kiến ​​thức Microsoft, bài viết 213330 để thảo luận đầy đủ hơn:

http://support.microsoft.com/kb/213330

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

Mẹo này (1955) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: