Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tính "tổng phụ nếu", chỉ đếm các hàng hiển thị có tiêu chí.
Hàm SUBTOTAL
Hàm SUBTOTAL có thể thực hiện các phép tính khác nhau trên một phạm vi dữ liệu (đếm, tổng, trung bình, v.v.). Quan trọng nhất, nó có thể được sử dụng để tính toán trên các hàng chỉ hiển thị (đã lọc). Trong ví dụ này, chúng ta sẽ sử dụng hàm để đếm (COUNTA) hàng hiển thị bằng cách đặt đối số SUBTOTAL function_num thành 3 (Bạn có thể tìm thấy danh sách đầy đủ các hàm khả thi tại đây.)
= SUBTOTAL (3, $ D $ 2: $ D $ 14)
Lưu ý kết quả thay đổi như thế nào khi chúng tôi lọc các hàng theo cách thủ công.
SUBTOTAL NẾU
Để tạo “Tổng phụ nếu”, chúng tôi sẽ sử dụng kết hợp SUMPRODUCT, SUBTOTAL, OFFSET, ROW và MIN trong một công thức mảng. Sử dụng sự kết hợp này, về cơ bản chúng ta có thể tạo một hàm “SUBTOTAL IF” chung. Hãy xem qua một ví dụ.
Chúng tôi có danh sách các thành viên và tình trạng tham dự của họ cho mỗi sự kiện:
Giả sử chúng tôi được yêu cầu đếm số lượng thành viên đã tham dự một sự kiện động khi chúng tôi lọc danh sách theo cách thủ công như vậy:
Để thực hiện điều này, chúng ta có thể sử dụng công thức sau:
= SUMPRODUCT ((=) * (SUBTOTAL (3, OFFSET (, ROW () - MIN (ROW ()), 0))))
= SUMPRODUCT ((D2: D14 = "Đã theo học") * (SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))))
Khi sử dụng Excel 2022 trở về trước, bạn phải nhập công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER để cho Excel biết rằng bạn đang nhập công thức mảng. Bạn sẽ biết công thức đã được nhập đúng cách dưới dạng công thức mảng khi dấu ngoặc nhọn xuất hiện xung quanh công thức (xem hình ảnh ở trên).
Công thức hoạt động như thế nào?
Công thức hoạt động bằng cách nhân hai mảng bên trong SUMPRODUCT, trong đó mảng đầu tiên xử lý các tiêu chí của chúng tôi và mảng thứ hai chỉ lọc các hàng hiển thị:
= SUMPRODUCT (*)
Mảng tiêu chí
Mảng tiêu chí đánh giá từng hàng trong phạm vi giá trị của chúng tôi (Trạng thái “Đã tham dự” trong ví dụ này) và tạo một mảng như sau:
=(=)
= (D2: D14 = "Đã tham dự")
Đầu ra:
{THẬT; SAI; SAI; THẬT; SAI; TURE; TURE; TURE; SAI; SAI; THẬT; SAI; THẬT}
Lưu ý rằng kết quả đầu ra trong mảng đầu tiên trong công thức của chúng tôi bỏ qua việc hàng có hiển thị hay không, đó là nơi mảng thứ hai của chúng tôi xuất hiện để trợ giúp.
Mảng hiển thị
Sử dụng SUBTOTAL để loại trừ các hàng không hiển thị trong phạm vi của chúng tôi, chúng tôi có thể tạo mảng khả năng hiển thị của mình. Tuy nhiên, chỉ riêng SUBTOTAL sẽ trả về một giá trị duy nhất, trong khi SUMPRODUCT đang mong đợi một mảng giá trị. Để giải quyết vấn đề này, chúng tôi sử dụng OFFSET để chuyển từng hàng một. Kỹ thuật này yêu cầu cung cấp OFFSET một mảng có chứa một số tại một thời điểm. Mảng thứ hai trông như thế này:
= SUBTOTAL (3, OFFSET (, ROW () - MIN (ROW ()), 0))
= SUBTOTAL (3, OFFSET (D2, ROW (D2: D14) -MIN (ROW (D2: D14)), 0))
Đầu ra:
{1;1;0;0;1;1}
Khâu cả hai lại với nhau:
= SUMPRODUCT ({TRUE; TRUE; FALSE; FALSE; TRUE; TRUE} * {1; 1; 0; 0; 1; 1})
= 4
SUBTOTAL NẾU có nhiều tiêu chí
Để thêm nhiều tiêu chí, chỉ cần kết hợp nhiều tiêu chí với nhau trong SUMPRODUCT như sau:
= SUMPRODUCT ((=) * (=) * (SUBTOTAL (3, OFFSET (, ROW () - MIN (ROW ()), 0))))
= SUMPRODUCT ((E2: E14 = "Đã theo học") * (B2: B14 = 2019) * (SUBTOTAL (3, OFFSET (E2, ROW (E2: E14) -MIN (ROW (E2: E14)), 0)) ))
NẾU PHỤ THUỘC trong Google Trang tính
Hàm SUBTOTAL IF hoạt động giống hệt trong Google Trang tính cũng như trong Excel:
