Nhận COUNTIFS với Phạm vi tiêu chí động trong Excel
Chúng tôi biết rằng hàm COUNTIFS trong Excel có thể dựa trên nhiều tiêu chí.
Nó nhận các đối số là một vài phạm vi tiêu chí và tiêu chí. Chúng tôi có thể thay đổi tiêu chí động bằng cách cung cấp tham chiếu ô nhưng chúng tôi không thể thay đổi động cột tiêu chí. Chà, không trực tiếp nhưng chúng ta có thể.
Đó là những gì chúng ta học được trong các công thức excel trước. Làm những việc trong excel mà bình thường không thể làm được. Hãy xem làm thế nào.
Chúng ta hãy tìm hiểu bằng ví dụ.
Ngữ cảnh
Ở đây, tôi đã chuẩn bị một dữ liệu về doanh số bán hàng được thực hiện trong các tháng khác nhau trong năm bởi các chuyên gia tư vấn bán hàng của chúng tôi. Họ bán các mẫu sản phẩm khác nhau của chúng tôi, được đặt tên chung là model1, model2, v.v. Những gì chúng ta cần làm là đếm động doanh số của các mẫu xe khác nhau trong các tháng khác nhau.
Trong ô I2, chúng ta sẽ chọn tháng. Trong ô I2, chúng tôi sẽ chọn mô hình. Những giá trị này có thể sẽ thay đổi. Và số lượng cũng sẽ thay đổi.
Http: // COUNTIFS [Hàm COUNTIFS] sẽ tìm kiếm cột tháng sẽ là phạm vi tiêu chí. Sau đó, nó sẽ tìm kiếm mô hình trong cột tháng đó.
Vì vậy, ở đây cả hai tiêu chí và tiêu chí_phạm vi đều có thể thay đổi. Vậy làm cách nào để tạo biến cột trong COUNTIFS? Đây là cách làm?
Sử dụng phạm vi được đặt tên cho cột biến hoặc phạm vi tiêu chí
Công thức chung
=COUNTIFS(INDIRECT(named_range),criteria)
Đầu tiên, đặt tên từng cột theo tiêu đề của chúng. Để làm như vậy, hãy chọn bảng và nhấn CTRL + SHIFT + F3 và đặt tên cho các cột theo hàng trên cùng. Đọc về nó ở đây.
Vì vậy, Phạm vi B3: B11, C3: C11, D3: D11 và E3: E11 được đặt tên lần lượt là Tháng 1, Tháng 2, Tháng 3 và Tháng 4.
Viết công thức này trong I4.
=COUNTIFS(INDIRECT(I2),I3)
Bây giờ nếu bạn thay đổi tháng trong I4, số tháng tương ứng của Model4 sẽ được hiển thị trong I4.
Làm thế nào nó hoạt động?
Công thức rất đơn giản. Hãy bắt đầu từ bên trong.
Như chúng ta biết, hàm INDIRECT chuyển đổi văn bản tham chiếu thành tham chiếu thực tế. Chúng tôi đã cung cấp cho nó I2. I2 chứa tháng tư. Vì chúng tôi có phạm vi E3: E11 tên tháng tư, liên kết: / lookup-công thức-excel-gián tiếp-hàm [INDIRECT] (I2)
dịch sang E3: E11.
COUNTIFS đếm bất kỳ thứ gì trong I3 trong phạm vi E3: E11.
Khi bạn thay đổi tháng, cột sẽ tự động thay đổi. Đây được gọi là COUNTIFS với cột Biến. Trong gif, tôi đã sử dụng một số liên kết: / có điều kiện-định dạng-có điều kiện-định dạng-dựa trên-một-ô-giá trị khác [định dạng có điều kiện dựa trên một ô khác].
Công thức cũng có thể hoạt động với hàm countif. Nhưng nếu bạn muốn có nhiều điều kiện thì hãy sử dụng hàm COUNTIFS.