Công thức Excel: Chỉ đếm các hàng hiển thị với tiêu chí
Công thức Excel: Chỉ đếm các hàng hiển thị với tiêu chí

Công thức chung
=SUMPRODUCT((range=criteria)*(SUBTOTAL(103,OFFSET(range,rows,0,1))))
Tóm lược

Để chỉ đếm các hàng hiển thị với tiêu chí, bạn có thể sử dụng một công thức khá phức tạp dựa trên GIỚI THIỆU, TIÊU ĐỀ, và BÙ LẠI. Trong ví dụ được hiển thị, công thức trong C12 là:

=SUMPRODUCT((C5:C8=C10)*(SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))))

Lời nói đầu

Hàm SUBTOTAL có thể dễ dàng tạo tổng và đếm cho các hàng ẩn và không ẩn. Tuy nhiên, nó không thể xử lý các tiêu chí như COUNTIF hoặc là SUMIF mà không cần một số trợ giúp. Một giải pháp là sử dụng SUMPRODUCT để áp dụng cả hàm SUBTOTAL (thông qua OFFSET) và tiêu chí. Chi tiết của cách tiếp cận này được mô tả dưới đây.

Giải trình

Về cơ bản, công thức này hoạt động bằng cách thiết lập hai mảng bên trong SUMPRODUCT. Mảng đầu tiên áp dụng tiêu chívà mảng thứ hai xử lý khả năng hiển thị:

=SUMPRODUCT(criteria*visibility)

Tiêu chí được áp dụng với một phần của công thức:

=(C5:C8=C10)

Tạo một mảng như thế này:

{FALSE;TRUE;FALSE;TRUE}

Trường hợp TRUE có nghĩa là “đáp ứng tiêu chí”. Lưu ý vì chúng tôi đang sử dụng phép nhân

{0;1;0;1}

trên mảng này, các giá trị TRUE FALSE sẽ tự động được chuyển đổi thành 1 và 0 bằng phép toán, vì vậy chúng ta kết thúc bằng: Bộ lọc hiển thị được áp dụng bằng SUBTOTAL, vớihàm số 103

. SUBTOTAL có thể loại trừ các hàng ẩn khi chạy tính toán, vì vậy chúng tôi có thể sử dụng nó trong trường hợp này để tạo “bộ lọc” nhằm loại trừ các hàng ẩn bên trong SUMPRODUCT. Tuy nhiên, vấn đề là SUBTOTAL trả về một số duy nhất, trong khi chúng ta cần một mảng kết quả

để sử dụng nó thành công bên trong SUMPRODUCT. Mẹo là sử dụng OFFSET để cung cấp SUBTOTAL một tham chiếu cho mỗi hàng, để OFFSET sẽ trả về một kết quả cho mỗi hàng.

=ROW(C5:C8)-MIN(ROW(C5:C8)

Tất nhiên, điều đó đòi hỏi một thủ thuật khác, đó là cung cấp cho OFFSET một mảng chứa một số trên mỗi hàng, bắt đầu bằng số không. Chúng tôi làm điều đó với một biểu thức được xây dựng trên hàm ROW:

{0;1;2;3}

sẽ tạo ra một mảng như thế này:

=SUBTOTAL(103,OFFSET(C5,ROW(C5:C8)-MIN(ROW(C5:C8)),0))
=SUBTOTAL(103,OFFSET(C5,{0;1;2;3},0))
=SUBTOTAL(103,{"East";"West";"Midwest";"West"})
={1;0;1;1}

Tóm lại, mảng thứ hai (xử lý khả năng hiển thị bằng SUBTOTAL), được tạo như sau:

=SUMPRODUCT({0,1,0,1}*{1;0;1;1})

Và, cuối cùng, chúng tôi có:

Trả về 1.

Nhiều tiêu chí

=SUMPRODUCT((rng1=criteria1)*(rng2=criteria2)*(SUBTOTAL(103,OFFSET(rng,rows,0,1))))

Bạn có thể mở rộng công thức để xử lý nhiều tiêu chí như sau:

Tổng hợp kết quả

=SUMPRODUCT(criteria*visibility*sumrange)

Để trả về một tổng các giá trị thay vì một số đếm, bạn có thể điều chỉnh công thức để bao gồm một phạm vi tổng: Các mảng tiêu chí và khả năng hiển thị hoạt động giống như đã giải thích ở trên, loại trừ các ô không hiển thị. Nếu bạn cần đối sánh từng phần, bạn có thể tạo biểu thức bằng cách sử dụng ISNUMBER + SEARCH,như được giải thích ở đây

Mike Girvin’s Magic Trick 1010

https://exceljet.net/formula/count-visible-rows-only-with-criteria