Cách Sử dụng Dải động trong Excel Với COUNTIF và INDIRECT

Mục lục:

2024 Tác giả: Abigail Brown | [email protected]. Sửa đổi lần cuối: 2023-12-17 07:05

Điều cần biết

Hàm INDIRECT thay đổi phạm vi tham chiếu ô trong công thức mà không cần chỉnh sửa công thức.
Sử dụng INDIRECT làm đối số cho COUNTIF để tạo một dải ô động đáp ứng các tiêu chí đã chỉ định.
Tiêu chí được thiết lập bởi hàm INDIRECT và chỉ những ô đáp ứng tiêu chí mới được tính.

Bài viết này giải thích cách sử dụng hàm INDIRECT trong công thức Excel để thay đổi phạm vi tham chiếu ô được sử dụng trong công thức mà không cần phải chỉnh sửa chính công thức. Điều này đảm bảo rằng các ô giống nhau được sử dụng, ngay cả khi bảng tính của bạn thay đổi. Thông tin áp dụng cho Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel cho Mac và Excel Online.

Sử dụng Dải động với Công thức COUNTIF - INDIRECT

Hàm INDIRECT có thể được sử dụng với một số hàm chấp nhận tham chiếu ô làm đối số, chẳng hạn như hàm SUM và COUNTIF.

Sử dụng INDIRECT làm đối số cho COUNTIF tạo ra một phạm vi động các tham chiếu ô có thể được tính bởi hàm nếu các giá trị ô đáp ứng tiêu chí. Nó thực hiện điều này bằng cách chuyển dữ liệu văn bản, đôi khi được gọi là chuỗi văn bản, thành tham chiếu ô.

Ví dụ này dựa trên dữ liệu được hiển thị trong hình trên. Công thức COUNTIF - INDIRECT được tạo trong hướng dẫn là:

=COUNTIF (INDIRECT (E1 &":" & E2), ">10")

Trong công thức này, đối số cho hàm INDIRECT chứa:

Ô tham chiếu E1 và E2, chứa dữ liệu văn bản D1 và D6.
Toán tử phạm vi, dấu hai chấm (:) được bao quanh bởi dấu ngoặc kép ("") biến dấu hai chấm thành văn bản chuỗi.
Hai dấu và (&) được sử dụng để nối hoặc nối với nhau, dấu hai chấm với tham chiếu ô E1 và E2.

Kết quả là INDIRECT chuyển đổi chuỗi văn bản D1: D6 thành tham chiếu ô và chuyển nó cùng với hàm COUNTIF để được tính nếu các ô được tham chiếu lớn hơn 10.

Hàm INDIRECT chấp nhận bất kỳ đầu vào văn bản nào. Đây có thể là các ô trong trang tính có chứa văn bản hoặc tham chiếu ô văn bản được nhập trực tiếp vào hàm.

Tự động Thay đổi Phạm vi Công thức

Hãy nhớ rằng, mục tiêu là tạo một công thức với một phạm vi động. Một dải động có thể được thay đổi mà không cần chỉnh sửa chính công thức.

Bằng cách thay đổi dữ liệu văn bản nằm trong các ô E1 và E2, từ D1 và D6 thành D3 và D7, phạm vi tổng cộng của hàm có thể dễ dàng thay đổi từ D1: D6 thành D3: D7. Điều này giúp loại bỏ sự cần thiết phải chỉnh sửa trực tiếp công thức trong ô G1.

Hàm COUNTIF trong ví dụ này chỉ đếm các ô chứa số nếu chúng lớn hơn 10. Mặc dù bốn trong năm ô trong phạm vi D1: D6 chứa dữ liệu, chỉ có ba ô chứa số. Các ô trống hoặc chứa dữ liệu văn bản sẽ bị hàm bỏ qua.

Đếm văn bản với COUNTIF

Hàm COUNTIF không giới hạn ở việc đếm dữ liệu số. Nó cũng đếm các ô có chứa văn bản bằng cách kiểm tra xem chúng có khớp với một văn bản nhất định hay không.

Để thực hiện việc này, công thức sau được nhập vào ô G2:

=COUNTIF (INDIRECT (E1 &":" & E2), "hai")

Trong công thức này, hàm INDIRECT tham chiếu các ô từ B1 đến B6. Hàm COUNTIF tính tổng số ô có giá trị văn bản là hai trong đó.

Trong trường hợp này, kết quả là 1.

COUNTA, COUNTBLANK và INDIRECT

Hai hàm đếm khác trong Excel là COUNTA, đếm các ô chứa bất kỳ loại dữ liệu nào trong khi chỉ bỏ qua các ô trống hoặc ô trống và COUNTBLANK, chỉ đếm các ô trống hoặc ô trống trong một phạm vi.

Vì cả hai hàm này đều có cú pháp tương tự với hàm COUNTIF, nên chúng có thể được thay thế vào ví dụ trên bằng hàm INDIRECT để tạo các công thức sau:

=COUNTA (INDIRECT (E1 &":" & E2))

=COUNTBLANK (INDIRECT (E1 &":" & E2)

Đối với phạm vi D1: D6, COUNTA trả về câu trả lời là 4, vì bốn trong năm ô chứa dữ liệu. COUNTBLANK trả về câu trả lời là 1 vì chỉ có một ô trống trong phạm vi.

Tại sao lại sử dụng Hàm INDIRECT?

Lợi ích của việc sử dụng hàm INDIRECT trong tất cả các công thức này là các ô mới có thể được chèn vào bất kỳ vị trí nào trong phạm vi.

Phạm vi thay đổi động bên trong các chức năng khác nhau và kết quả cập nhật tương ứng.

Nếu không có hàm INDIRECT, mọi hàm sẽ cần được chỉnh sửa để bao gồm tất cả 7 ô, kể cả ô mới.

Lợi ích của hàm INDIRECT là các giá trị văn bản có thể được chèn làm tham chiếu ô và nó tự động cập nhật các phạm vi bất cứ khi nào bảng tính của bạn thay đổi.

Điều này giúp việc bảo trì bảng tính tổng thể dễ dàng hơn nhiều, đặc biệt là đối với các bảng tính rất lớn.