Tóm lược
Để định cấu hình COUNTIFS (hoặc COUNTIF) với một phạm vi thay đổi, bạn có thể sử dụng hàm OFFSET. Trong ví dụ được hiển thị, công thức trong B11 là:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Công thức này đếm các ô không trống trong phạm vi bắt đầu từ B5 và kết thúc 2 hàng phía trên ô có công thức. Công thức tương tự được sao chép và dán 2 hàng bên dưới mục nhập cuối cùng trong dữ liệu như hình.
Giải trình
Trong ví dụ được hiển thị, công thức trong B11 là:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Làm việc từ trong ra ngoài, công việc thiết lập một phạm vi biến được thực hiện bởi hàm OFFSET ở đây:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET có năm đối số và được cấu hình như thế này:
- tham chiếu = B $ 5, bắt đầu tại ô B5, hàng bị khóa
- hàng = 0, bù không hàng từ ô bắt đầu
- cols = 0, bù đắp 0 cột bắt đầu ô
- height = ROW () - ROW (B $ 5) -1 = cao 5 hàng
- width = rộng 1 cột
Để tính toán chiều cao của phạm vi trong hàng, chúng tôi sử dụng hàm ROW như sau:
ROW()-ROW(B$5)-1 // work out height
Vì ROW () trả về số hàng của ô "hiện tại" (tức là ô chứa công thức), chúng ta có thể đơn giản hóa như sau:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Với cấu hình trên, OFFSET trả về phạm vi B5: B9 trực tiếp thành COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Lưu ý rằng tham chiếu đến B $ 5 trong công thức trên là một tham chiếu hỗn hợp, với cột tương đối và hàng bị khóa. Điều này cho phép sao chép công thức sang một cột khác và vẫn hoạt động. Ví dụ: sau khi được sao chép sang C12, công thức là:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Lưu ý: OFFSET là một hàm dễ bay hơi và có thể gây ra các vấn đề về hiệu suất trong các trang tính lớn hoặc phức tạp.
Với INDIRECT và ADDRESS
Một cách tiếp cận khác là sử dụng công thức dựa trên các hàm INDIRECT và ADDRESS. Trong trường hợp này, chúng tôi tập hợp một phạm vi dưới dạng văn bản, sau đó sử dụng INDIRECT để đánh giá văn bản dưới dạng tham chiếu. Công thức trong B11 sẽ là:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
Hàm ADDRESS được sử dụng để tạo một phạm vi như sau:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Trong trường hợp đầu tiên của ADDRESS, chúng tôi cung cấp số hàng là giá trị được mã hóa cứng 5 và cung cấp số cột bằng hàm COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Trong trường hợp thứ hai, chúng tôi cung cấp số hàng "hiện tại" trừ đi 2 và cột hiện tại với hàm COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Sau khi nối hai giá trị này với nhau, chúng ta có:
"$B$5:$B$9" // as text
Lưu ý đây là một chuỗi văn bản. Để chuyển đổi thành tham chiếu hợp lệ, chúng tôi cần sử dụng INDIRECT:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Cuối cùng, công thức trong B11 trở thành:
=COUNTIFS($B$5:$B$9,"") // returns 4
Lưu ý: INDIRECT là một hàm dễ bay hơi và có thể gây ra các vấn đề về hiệu suất trong các trang tính lớn hoặc phức tạp.
