Trong nhiều năm, Tri thức software đã 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. Tuy nhiên, khi tôi gặp hàm AGGREGATE, đây trở thành hàm tôi thường dùng để đạt được kết quả tương tự nhưng linh hoạt hơn. Trong bài viết này, chúng ta cùng nhau phân tích chúng ta hãy cùng xem lại cách hoạt động của 2 hàm này.
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 có muốn bao gồm các hàng ẩn thủ công vào kết quả hay không.
Tuy nhiên, các hàng bị lọc ra luôn bị loại trừ, và hàm SUBTOTAL sẽ 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:
=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 số 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 = AVERAGE, 2 hoặc 102 = COUNT, 3 hoặc 103 = COUNTA, 4 hoặc 104 = MAX, 5 hoặc 105 = MIN, 6 hoặc 106 = PRODUCT, 7 hoặc 107 = STDEV, 8 hoặc 108 = STDEV.P, 9 hoặc 109 = SUM, 10 hoặc 110 = VAR và 11 hoặc 111 = VARP.
Các số từ 1 đến 11 cho đố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 bị ẩ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 để SUBTOTAL bao gồm các giá trị đã lọc ra.
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.
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ể yêu cầu Excel bỏ qua các 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.
Cú pháp cho dạng tham chiếu của hàm AGGREGATE là:
=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 số tối đa 252 đối số bổ sung chỉ định các phạm vi tiếp theo.
Nếu bạn đang làm việc với mảng, hàm AGGREGATE sẽ tuân theo cú pháp hơi khác một chút:
=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, PERCENTILE.INC và các hàm khác.
Sự khác biệt đầu tiên cần lưu ý giữa hàm SUBTOTAL và hàm 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 = PERCENTILE.INC, 17 = QUARTILE.INC, 18 = PERCENTILE.EXC và 19 = QUARTILE.EXC.
Tuy nhiên, điểm mà AGGREGATE thực sự vượt trội hơn SUBTOTAL nằm ở khả năng loại trừ một số giá trị nhất định khỏi kết quả (đối số b ).
Tổng kết
Vậy là tôi đã giới thiệu xong cách dùng Hàm SUBTOTAL vs AGGREGATE trên Microsoft Excel. Nếu gặp bất kỳ khó khăn nào trong quá trình thực hiện, bạn có thể liên hệ banquyenphanmem.com hoặc gọi điện thoại số 028.22443013 để được trợ giúp. Với các hướng dẫn trên, chúng tôi hy vọng bạn đã giải quyết được nhu cầu của mình một cách nhanh chóng và hiệu quả.
