Công thức chung
=COUNTIF(INDIRECT("'"&sheetname&"'!"&"range"),criteria)
Tóm lược
Để tìm kiếm nhiều trang tính trong sổ làm việc cho một giá trị và trả về một số lượng, bạn có thể sử dụng công thức dựa trên các hàm COUNTIF và INDIRECT. Với một số thiết lập sơ bộ, bạn có thể sử dụng phương pháp này để tìm kiếm toàn bộ sổ làm việc cho một giá trị cụ thể. Trong ví dụ được hiển thị, công thức trong C5 là:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),$C$4)
Bối cảnh - dữ liệu mẫu
Sổ làm việc chứa tổng cộng 4 trang tính. Mỗi Sheet1 , Sheet2 và Sheet3 chứa 1000 tên đầu tiên ngẫu nhiên trông giống như sau:
Giải trình
Phạm vi B7: B9 chứa các tên trang tính mà chúng tôi muốn đưa vào tìm kiếm. Đây chỉ là các chuỗi văn bản và chúng tôi cần thực hiện một số công việc để chúng được công nhận là tham chiếu trang tính hợp lệ.
Làm việc từ trong ra ngoài, biểu thức này được sử dụng để xây dựng một tham chiếu trang tính đầy đủ:
"'"&B7&"'!"&"1:1048576"
Các dấu ngoặc kép được thêm vào để cho phép tên trang tính có khoảng trắng và dấu chấm than là cú pháp tiêu chuẩn cho các phạm vi bao gồm tên trang tính. Văn bản "1: 1048576" là một phạm vi bao gồm mọi hàng trong trang tính.
Sau khi B7 được đánh giá và các giá trị được nối, biểu thức trên trả về:
"'Sheet1'!1:1048576"
đi vào hàm INDIRECT dưới dạng đối số 'ref_text'. INDIRECT đánh giá văn bản này và trả về một tham chiếu chuẩn cho mọi ô trong Sheet1 . Điều này đi vào hàm COUNTIF dưới dạng phạm vi. Tiêu chí được cung cấp dưới dạng tham chiếu tuyệt đối đến C4 (bị khóa để công thức có thể được sao chép xuống cột C).
Sau đó, COUNTIF trả về tổng số tất cả các ô có giá trị bằng "mary", trong trường hợp này là 25.
Lưu ý: COUNTIF không phân biệt chữ hoa chữ thường.
Chứa so với bằng
Nếu bạn muốn đếm tất cả các ô có chứa giá trị trong C4, thay vì tất cả các ô bằng C4, bạn có thể thêm các ký tự đại diện vào các tiêu chí như sau:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1048576"),"*"&C4&"*")
Bây giờ COUNTIF sẽ đếm các ô có chuỗi con "John" ở bất kỳ vị trí nào trong ô.
Hiệu suất
Nói chung, không phải là một phương pháp hay để chỉ định một phạm vi bao gồm tất cả các ô trang tính. Làm như vậy có thể gây ra các vấn đề về hiệu suất, vì phạm vi bao gồm hàng triệu và hàng triệu ô. Trong ví dụ này, vấn đề là phức tạp, vì công thức sử dụng hàm INDIRECT, một hàm dễ thay đổi. Các hàm đa năng sẽ tính toán lại mỗi lần thay đổi trang tính, vì vậy tác động đến hiệu suất có thể rất lớn.
Khi có thể, hãy hạn chế phạm vi ở một kích thước hợp lý. Ví dụ: nếu bạn biết dữ liệu sẽ không xuất hiện trong hàng 1000, bạn có thể chỉ tìm kiếm trong 1000 hàng đầu tiên như sau:
=COUNTIF(INDIRECT("'"&B7&"'!"&"1:1000"),$C$4)
