Trong nhiều năm, hàm SUBTOTAL là công cụ đắc lực để tạo ra các tổng phụ dễ dàng quan sát trên các bảng tính Microsoft Excel. Tuy nhiên, khi hàm AGGREGATE xuất hiện, nó nhanh chóng trở thành lựa chọn hàng đầu nhờ khả năng mang lại kết quả tương tự nhưng với tính linh hoạt vượt trội. Đối với độc giả của tincongnghemoi.com, những người luôn tìm kiếm các giải pháp công nghệ hiệu quả để tối ưu hóa công việc, việc hiểu rõ sự khác biệt giữa hai hàm này là điều cần thiết.
Trước khi đi sâu vào ưu điểm của AGGREGATE, hãy cùng nhìn lại cách hoạt động của hàm SUBTOTAL quen thuộc.
Tổng quan về hàm SUBTOTAL trong Excel
Hàm SUBTOTAL của Microsoft Excel tương đối đơn giản để 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 dải ô và bạn có thể quyết định có muốn bao gồm các hàng được ẩn thủ công trong kết quả hay không. Đây là một hàm hữu ích cho các tác vụ tổng hợp dữ liệu nhanh chóng, giúp người dùng dễ dàng phân tích các tập dữ liệu lớn.
Tuy nhiên, hàm SUBTOTAL cũng tồn tại những hạn chế đáng kể. Các hàng bị lọc luôn bị loại trừ khỏi kết quả, điều này có thể gây ra sai sót nếu bạn cần tính toán trên toàn bộ dữ liệu gốc bất kể bộ lọc. Hơn nữa, 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, làm tăng độ phức tạp của công thức. Cuối cùng, SUBTOTAL chỉ hỗ trợ 11 hàm cơ bản, giới hạn đáng kể 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.
Cú pháp của hàm SUBTOTAL như sau:
=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à dải ô đầu tiên để tính tổng phụ.
- c (tùy chọn) biểu thị dải ô bổ sung đầu tiên trong số tối đa 252 dải ô khác để tính tổng phụ, mỗi dải được phân tách bằng dấu phẩy.
Đối với đối số a:
- 1 hoặc 101 = AVERAGE (Trung bình)
- 2 hoặc 102 = COUNT (Đếm số)
- 3 hoặc 103 = COUNTA (Đếm giá trị không trống)
- 4 hoặc 104 = MAX (Giá trị lớn nhất)
- 5 hoặc 105 = MIN (Giá trị nhỏ nhất)
- 6 hoặc 106 = PRODUCT (Tích)
- 7 hoặc 107 = STDEV (Độ lệch chuẩn mẫu)
- 8 hoặc 108 = STDEV.P (Độ lệch chuẩn tổng thể)
- 9 hoặc 109 = SUM (Tổng)
- 10 hoặc 110 = VAR (Phương sai mẫu)
- 11 hoặc 111 = VARP (Phương sai tổng thể)
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 được ẩn thủ công, trong khi các số từ 101 đến 111 sẽ loại trừ các hàng được ẩn thủ công.
Trong ví dụ này, hàm SUBTOTAL được sử dụng để tính trung bình 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 không bao gồm (đối số a = 101).
Ví dụ hàm SUBTOTAL tính trung bình với các tùy chọn bao gồm hoặc loại trừ hàng ẩn thủ công trong Excel.
Tuy nhiên, khi bộ lọc được sử dụng để ẩn điểm của đội E, cả hai tổng phụ đều cho cùng một kết quả. Điều này cho thấy không có cách nào để hàm SUBTOTAL bao gồm các giá trị đã bị lọc ra. Đây là một hạn chế lớn khi bạn cần phân tích dữ liệu trên toàn bộ dải ô, không phụ thuộc vào trạng thái lọc.
Hàm SUBTOTAL cho kết quả giống nhau khi hàng được lọc, minh họa hạn chế không bỏ qua hàng đã lọc.
Trong ví dụ cuối cùng này, bạn có thể thấy rằng hàm SUBTOTAL trả về một lỗi nếu có lỗi trong dải ô dữ liệu. Điều này buộc người dùng phải xử lý lỗi trước hoặc lồng hàm IFERROR, làm phức tạp thêm công thức.
Hàm SUBTOTAL trả về lỗi khi gặp giá trị lỗi trong dải ô dữ liệu, yêu cầu xử lý bổ sung.
Để khắc phục những vấn đề này, bạn có thể sử dụng hàm AGGREGATE thay thế.
Khám phá sức mạnh của hàm AGGREGATE trong Excel
Hàm AGGREGATE tương tự như hàm SUBTOTAL nhưng cung cấp nhiều tùy chọn hơn cho loại phép 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 gì 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, AGGREGATE mạnh mẽ và linh hoạt hơn rất nhiều, là một giải pháp tối ưu cho việc xử lý dữ liệu phức tạp.
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 đang sử dụng loại nào, vì Excel sẽ tự động chọn loại phù hợp tùy thuộc vào các đối số bạn nhập.
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 số xác định những gì bạn muốn phép tính bỏ qua.
- c (bắt buộc) là dải ô mà hàm sẽ được áp dụng.
- d (tùy chọn) là đối số bổ sung đầu tiên trong số tối đa 252 đối số, chỉ định các dải ô bổ sung.
Nếu bạn đang làm việc với các mảng, hàm AGGREGATE tuân theo một cú pháp hơi khác:
=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 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.
- d là đối số thứ hai bắt buộc bởi các hàm mảng như LARGE, SMALL, PERCENTILE.INC và các hàm khác.
Điểm khác biệt đầu tiên giữa SUBTOTAL và AGGREGATE là 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 (Trung vị), 13 = MODE.SNGL (Giá trị xuất hiện nhiều nhất), 14 = LARGE (Giá trị lớn thứ k), 15 = SMALL (Giá trị nhỏ thứ k), 16 = PERCENTILE.INC, 17 = QUARTILE.INC, 18 = PERCENTILE.EXC và 19 = QUARTILE.EXC. Với 19 hàm hỗ trợ, AGGREGATE mở ra nhiều khả năng phân tích thống kê hơn so với SUBTOTAL.
Tuy nhiên, nơi mà AGGREGATE thực sự vượt trội hơn SUBTOTAL là ở khả năng loại trừ các giá trị nhất định khỏi kết quả (đối số b):
| Số | Những gì bị bỏ qua |
|---|---|
| 0 | Các hàm SUBTOTAL và AGGREGATE lồng nhau |
| 1 | Các hàng ẩn và các hàm SUBTOTAL, AGGREGATE lồng nhau |
| 2 | Các lỗi và các hàm SUBTOTAL, AGGREGATE lồng nhau |
| 3 | Các hàng ẩn, giá trị lỗi và các hàm SUBTOTAL, AGGREGATE lồng nhau |
| 4 | Không có gì |
| 5 | Chỉ các hàng ẩn |
| 6 | Chỉ các lỗi |
| 7 | Các hàng ẩn và các lỗi |
Hãy cùng xem AGGREGATE hoạt động như thế nào trong thực tế.
Trong ví dụ này, việc nhập công thức:
=AGGREGATE(1,7,Team_Scores[Score])vào ô E2 sẽ 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 trong hàng 8 và lỗi trong hàng 7 (đối số b = 7).
Minh họa hàm AGGREGATE tính trung bình, bỏ qua hàng bị ẩn và các giá trị lỗi trong dữ liệu.
Bạn sẽ không thể đạt được kết quả tương tự chỉ với hàm SUBTOTAL trong trường hợp này, vì nó không thể bỏ qua các lỗi mà không cần lồng trong công thức IFERROR.
Mặc dù việc bỏ qua lỗi là một cách hay để làm cho bảng tính của bạn trông gọn gàng hơn, nhưng đừng bao giờ có thói quen bỏ qua chúng hoàn toàn! Chúng tồn tại có lý do và có thể giúp khắc phục sự cố.
Mặt khác, việc nhập công thức:
=AGGREGATE(1,6,Team_Scores[Score])vào ô E3 sẽ 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ị trong hàng 8 đã bị lọc ra và loại trừ lỗi (đối số b = 6).
Hàm AGGREGATE với đối số b=6, tính trung bình bỏ qua lỗi nhưng vẫn tính hàng bị lọc, cho thấy sự linh hoạt.
Một lần nữa, điều này sẽ không thể thực hiện được với SUBTOTAL, vì nó luôn bao gồm các hàng đã bị 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 giá trị trung vị (đối số a = 12), một trong nhiều phép tính mà hàm SUBTOTAL không cho phép:
=AGGREGATE(12,6,Team_Scores[Score])
Ví dụ hàm AGGREGATE tính giá trị trung vị (MEDIAN), một tính năng không có trong SUBTOTAL.
Trong một ví dụ cuối cùng, đối số d đã được sử dụng để tạo giá trị lớn thứ hai:
=AGGREGATE(14,6,Team_Scores[Score],2)
Sử dụng hàm AGGREGATE để tìm giá trị lớn thứ hai (LARGE) trong một dải dữ liệu, kết hợp đối số d.
So sánh chi tiết: SUBTOTAL và AGGREGATE
Dưới đây là bảng tóm tắt các tính năng chính của hai hàm SUBTOTAL và AGGREGATE trong Microsoft Excel để bạn dễ dàng so sánh và đưa ra lựa chọn phù hợp nhất:
| Tính năng | SUBTOTAL | AGGREGATE |
|---|---|---|
| Phù hợp cho các phép tổng hợp đơn giản | Có | Có |
| Phù hợp cho các phép tổng hợp thống kê phức tạp | Không | Có |
| Số lượng hàm được hỗ trợ | 11 | 19 |
| Có thể bao gồm hoặc loại trừ hàng ẩn thủ công | Có | Có |
| Có thể bao gồm hoặc loại trừ hàng được lọc | Không | Có |
| Có thể bỏ qua lỗi | Không | Có |
| Có thể bỏ qua kết quả SUBTOTAL hoặc AGGREGATE lồng nhau | Có | Có |
| Cung cấp các tùy chọn bổ sung cho hàm mảng | Không | Có |
Kết luận
Qua phân tích chi tiết, có thể thấy rõ ràng rằng hàm AGGREGATE là một bước tiến đáng kể so với SUBTOTAL trong Microsoft Excel. Với tính linh hoạt cao hơn trong việc xử lý các tình huống phức tạp như bỏ qua hàng ẩn, hàng đã lọc và lỗi dữ liệu, cùng với việc hỗ trợ nhiều hàm thống kê hơn, AGGREGATE mang lại giải pháp toàn diện và mạnh mẽ hơn cho người dùng Excel chuyên nghiệp.
Ngay cả khi bạn nghĩ rằng mình đã tìm thấy một hàm hoạt động hiệu quả, hãy luôn kiểm tra kỹ xem có hàm nào khác làm tốt hơn công việc đó không! Microsoft thường xuyên 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ụ, tôi hiện đã chuyển sang 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.
Bạn đã thử sử dụng AGGREGATE để xử lý dữ liệu của mình chưa? Hãy chia sẻ trải nghiệm của mình hoặc khám phá thêm các thủ thuật Excel và công nghệ khác tại tincongnghemoi.com!
