Không sử dụng SUBTOTAL trong Excel: Thay vào đó hãy sử dụng AGGREGATE

[Starlink]

Tạo tổng phụ linh hoạt hơn.

Trong nhiều năm, tôi đã sử dụng hàm SUBTOTAL trong Microsoft Excel để tạo các tổng phụ dễ nhìn thấy ở đầu bảng tính của mình. Tuy nhiên, khi tôi gặp AGGREGATE, đây đã trở thành hàm tôi sử dụng để đạt được kết quả tương tự nhưng linh hoạt hơn.

Trước khi giải thích thêm, chúng ta hãy cùng xem lại cách hàm SUBTOTAL hoạt động.

1. Hàm SUBTOTAL

Hàm SUBTOTAL của Microsoft Excel khá dễ sử dụng. Nó cho phép bạn thực hiện các phép tính tổng phụ cho các phạm vi ô và bạn có thể quyết định xem bạn có muốn bao gồm các hàng ẩn thủ công trong kết quả hay không.

Tuy nhiên, các hàng được lọc ra luôn bị loại trừ và hàm SUBTOTAL không hoạt động nếu có lỗi trong dữ liệu, trừ khi bạn lồng nó vào hàm IFERROR. Hơn nữa, SUBTOTAL chỉ hỗ trợ 11 hàm, hạn chế việc sử dụng nếu bạn muốn thực hiện các phép tính thống kê phức tạp hơn.

Sau đây là cú pháp:

Mã nguồn [Chọn] Expand

=SUBTOTAL(a,b,c)
Trong đó:

    a (bắt buộc) là một số biểu thị hàm bạn muốn sử dụng trong phép tính,
    b (bắt buộc) là phạm vi ô đầu tiên để tính tổng phụ và
    c (tùy chọn) biểu thị phạm vi đầu tiên trong tối đa 252 phạm vi để tính tổng phụ, mỗi phạm vi được phân tách bằng dấu phẩy.

Đối với đối số a, 1 hoặc 101 = TRUNG BÌNH, 2 hoặc 102 = ĐẾM, 3 hoặc 103 = ĐẾM, 4 hoặc 104 = TỐI ĐA, 5 hoặc 105 = TỐI THIỂU, 6 hoặc 106 = TÍNH, 7 hoặc 107 = STDEV, 8 hoặc 108 = STDEV.P, 9 hoặc 109 = TỔNG, 10 hoặc 110 = VAR và 11 hoặc 111 = VARP.

Các số từ 1 đến 11 đối với đối số a buộc kết quả phải bao gồm các hàng ẩn thủ công, trong khi các số từ 101 đến 111 loại trừ các hàng ẩn thủ công.

Trong ví dụ này, hàm SUBTOTAL đã được sử dụng để tính giá trị trung bình của các giá trị trong cột B, với hàng 6 (đội E) bị ẩn. Tổng phụ trong ô E1 bao gồm hàng ẩn (đối số a = 1), trong khi tổng phụ trong ô E2 thì không (đối số a = 101).

Tuy nhiên, khi bộ lọc được sử dụng để ẩn điểm của đội E, cả hai tổng phụ đều giống nhau, vì không có cách nào để TỔNG PHỤ bao gồm các giá trị đã lọc.

Trong ví dụ cuối cùng này, bạn có thể thấy hàm SUBTOTAL trả về lỗi nếu có lỗi trong phạm vi.

Để tránh những vấn đề này, bạn có thể sử dụng hàm AGGREGATE thay thế.

2. Hàm AGGREGATE

Hàm AGGREGATE tương tự như hàm SUBTOTAL, nhưng có nhiều tùy chọn hơn cho loại tổng hợp bạn muốn tạo. Ngoài ra, bạn có nhiều lựa chọn hơn về những thứ bạn có thể loại trừ khỏi kết quả và bạn có thể khiến Excel bỏ qua lỗi trong dữ liệu của mình.

Nói cách khác, so với hàm SUBTOTAL, hàm AGGREGATE mạnh mẽ và linh hoạt hơn.

Hàm AGGREGATE có hai cú pháp—một cho tham chiếu và một cho mảng—mặc dù bạn không cần phải lo lắng về việc mình đang sử dụng cú pháp nào, vì Excel sẽ chọn cú pháp phù hợp tùy thuộc vào các đối số bạn nhập vào.

Cú pháp cho dạng tham chiếu của hàm AGGREGATE là:

Mã nguồn [Chọn] Expand

=AGGREGATE(a,b,c,d)
Trong đó:

    a (bắt buộc) là một số biểu thị hàm bạn muốn sử dụng trong phép tính,
    b (bắt buộc) là một con số xác định những gì bạn muốn phép tính bỏ qua,
    c (bắt buộc) là phạm vi ô mà hàm sẽ được áp dụng và
    d (tùy chọn) là đối số đầu tiên trong tối đa 252 đối số bổ sung dùng để chỉ định các phạm vi xa hơn.

Nếu bạn đang làm việc với mảng, hàm AGGREGATE có cú pháp hơi khác một chút:

Mã nguồn [Chọn] Expand

=AGGREGATE(a,b,c,d)
Trong đó:

    a (bắt buộc) là một số biểu thị hàm bạn muốn sử dụng trong phép tính,
    b (bắt buộc) là một con số xác định những gì bạn muốn phép tính bỏ qua,
    c (bắt buộc) là mảng các giá trị mà hàm sẽ được áp dụng và
    d là đối số thứ hai được yêu cầu bởi các hàm mảng như LARGE, SMALL,   Đăng nhập để xem liên kết và các hàm khác.

Sự khác biệt đầu tiên cần lưu ý giữa SUBTOTAL và AGGREGATE là hàm AGGREGATE có nhiều tùy chọn hơn cho đối số a : 1 = AVERAGE, 2 = COUNT, 3 = COUNTA, 4 = MAX, 5 = MIN, 6 = PRODUCT, 7 = STDEV.S, 8 = STDEV.P, 9 = SUM, 10 = VAR.S, 11 = VAR.P, 12 = MEDIAN, 13 = MODE.SNGL, 14 = LARGE, 15 = SMALL, 16 =   Đăng nhập để xem liên kết, 17 =   Đăng nhập để xem liên kết, 18 = PERCENTILE.EXC và 19 = QUARTILE.EXC.

Tuy nhiên, điểm thực sự vượt trội hơn SUBTOTAL là khả năng loại trừ một số giá trị nhất định khỏi kết quả (đối số b ):

Vậy, chúng ta hãy cùng xem nó hoạt động như thế nào nhé.

Trong ví dụ này, hãy nhập:

Mã nguồn [Chọn] Expand

=AGGREGATE(1,7,Team_Scores[Score])
vào ô E2 trả về giá trị trung bình (đối số a = 1) của các giá trị hiển thị trong cột B, bỏ qua giá trị ẩn ở hàng 8 và lỗi ở hàng 7 (đối số b = 7).

Trong trường hợp này, bạn sẽ không thể có được kết quả tương tự khi chỉ sử dụng hàm SUBTOTAL vì hàm này không thể bỏ qua lỗi nếu không được lồng vào công thức IFERROR.

Mặc dù bỏ qua lỗi là một cách tốt để làm cho bảng tính của bạn trông gọn gàng hơn, nhưng đừng có thói quen bỏ qua chúng hoàn toàn! Chúng ở đó vì một lý do và có thể giúp khắc phục sự cố.

Mặt khác, hãy nhập:

Mã nguồn [Chọn] Expand

=AGGREGATE(1,6,Team_Scores[Score])
vào ô E3 trả về giá trị trung bình (đối số a = 1) của tất cả các giá trị trong cột B, bao gồm giá trị trên hàng 8 được lọc ra và loại trừ lỗi (đối số b = 6).

Một lần nữa, điều này sẽ không thể thực hiện được với hàm SUBTOTAL vì nó luôn bao gồm các hàng đã lọc trong kết quả và không có cách nào để bỏ qua lỗi trong dữ liệu.

Lần này, trong ô E3, hàm AGGREGATE được sử dụng để tạo trung vị (đối số a = 12), một trong nhiều phép tính mà hàm SUBTOTAL không cho phép:

Mã nguồn [Chọn] Expand

=AGGREGATE(12,6,Team_Scores[Score])

Trong ví dụ cuối cùng, đối số d đã được sử dụng để tạo ra giá trị lớn thứ hai:

Mã nguồn [Chọn] Expand

=AGGREGATE(14,6,Team_Scores[Score],2)

3. Tóm tắt: SUBTOTAL so với AGGREGATE trong Excel

Sau đây là tóm tắt về các hàm SUBTOTAL và AGGREGATE của Microsoft Excel:

Ngay cả khi bạn nghĩ rằng mình đã chọn được một hàm phù hợp với mình, hãy luôn kiểm tra lại xem có hàm nào khác thực hiện công việc tốt hơn không! Microsoft thường giới thiệu các hàm mới là bản nâng cấp từ các hàm cũ—ví dụ, hiện tôi sử dụng XLOOKUP thay vì VLOOKUP và XMATCH thân thiện với người dùng hơn nhiều so với MATCH.