Hàm AGGREGATE trong Excel giúp bạn tính toán dữ liệu một cách linh hoạt, kể cả khi có lỗi hoặc hàng ẩn trong bảng tính. Bài viết này sẽ hướng dẫn cách sử dụng hàm AGGREGATE theo từng dạng công thức, kèm ví dụ cụ thể để bạn dễ hiểu và áp dụng vào công việc hàng ngày.
Cách sử dụng hàm AGGREGATE trong Excel
1. Hàm AGGREGATE là gì?
Hàm AGGREGATE trong Excel là một hàm tổng hợp đa năng, giúp người dùng thực hiện nhiều phép tính thống kê và xử lý dữ liệu linh hoạt. Đặc biệt, hàm này tích hợp 19 hàm tính toán khác nhau như SUM, AVERAGE, COUNT, MAX, MIN và nhiều hàm nâng cao, cho phép bạn tính toán nhanh chóng và loại trừ lỗi trong bảng dữ liệu.
Hàm AGGREGATE hoạt động như một bộ lọc thông minh, cho phép bạn loại trừ các giá trị lỗi, hàng ẩn hoặc công thức lồng nhau khi tính toán. Nhờ đó, kết quả luôn chính xác và ổn định ngay cả với các bảng dữ liệu phức tạp.
Giới thiệu hàm AGGREGATE trong Excel và ứng dụng thực tế
2. Cách sử dụng hàm AGGREGATE trong Excel
2.1. Công thức hàm AGGREGATE
Hàm AGGREGATE trong Excel có hai cách dùng chính: dạng tham chiếu và dạng mảng, mỗi dạng phù hợp với mục đích tính toán khác nhau.
2.1.1. Dạng tham chiếu:
=AGGREGATE(function_num, options, ref1, [ref2], …)
Dùng khi dữ liệu nằm trong các vùng phạm vi cố định (range), phù hợp cho các phép tính cơ bản như SUM, AVERAGE, MAX, MIN.
2.1.2. Dạng mảng:
=AGGREGATE(function_num, options, array, [k])
Phù hợp để xử lý biểu thức mảng, dữ liệu tạo bởi công thức, hoặc khi cần dùng các hàm yêu cầu tham số k như LARGE, SMALL, PERCENTILE.
Công thức của hàm AGGREGATE trong Excel
Giải thích tham số:
- function_num (bắt buộc): Chọn giá trị từ 1–19 tương ứng với loại phép tính cần thực hiện.
- options (bắt buộc): Chọn giá trị từ 0–7 để quy định cách xử lý hàng ẩn, lỗi hoặc công thức lồng nhau.
- ref1 (bắt buộc): Phạm vi hoặc đối số đầu tiên được dùng để tính toán.
- ref2,… (tùy chọn): Các phạm vi hoặc đối số bổ sung, tối đa 253 vùng.
- array (bắt buộc): Mảng dữ liệu cần tính toán (áp dụng cho dạng mảng).
- k (tùy chọn): Giá trị thứ tự dùng cho một số hàm như LARGE hoặc SMALL.
2.2. Bảng function_num trong hàm AGGREGATE
| function_num | Hàm tính toán | Chức năng |
|---|---|---|
| 1 | AVERAGE | Tính trung bình cộng |
| 2 | COUNT | Đếm số ô chứa số |
| 3 | COUNTA | Đếm số ô không trống |
| 4 | MAX | Trả về giá trị lớn nhất |
| 5 | MIN | Trả về giá trị nhỏ nhất |
| 6 | PRODUCT | Nhân tất cả các giá trị trong phạm vi |
| 7 | STDEV.S | Tính độ lệch chuẩn mẫu |
| 8 | STDEV.P | Tính độ lệch chuẩn tổng thể |
| 9 | SUM | Tính tổng các giá trị |
| 10 | VAR.S | Tính phương sai mẫu |
| 11 | VAR.P | Tính phương sai tổng thể |
| 12 | MEDIAN | Trả về giá trị trung vị |
| 13 | MODE.SNGL | Trả về giá trị xuất hiện nhiều nhất |
| 14 | LARGE | Trả về giá trị lớn thứ n |
| 15 | SMALL | Trả về giá trị nhỏ thứ n |
| 16 | PERCENTILE.INC | Tính phân vị thứ k (bao gồm 0 và 1) |
| 17 | QUARTILE.INC | Tính tứ phân vị thứ k (bao gồm 0 và 1) |
| 18 | PERCENTILE.EXC | Tính phân vị thứ k (không bao gồm 0 và 1) |
| 19 | QUARTILE.EXC | Tính tứ phân vị thứ k (không bao gồm 0 và 1) |
2.3. Bảng options trong hàm AGGREGATE
| Options | Ý nghĩa |
|---|---|
| 0 | Bỏ qua các ô chứa hàm SUBTOTAL và AGGREGATE |
| 1 | Bỏ qua hàng ẩn và các ô chứa hàm SUBTOTAL, AGGREGATE |
| 2 | Bỏ qua giá trị lỗi và các ô chứa hàm SUBTOTAL, AGGREGATE |
| 3 | Bỏ qua cả hàng ẩn, giá trị lỗi và các ô chứa hàm SUBTOTAL, AGGREGATE |
| 4 | Tính toán toàn bộ dữ liệu, không bỏ qua bất kỳ giá trị nào |
| 5 | Bỏ qua hàng ẩn nhưng vẫn tính giá trị lỗi và hàm SUBTOTAL, AGGREGATE |
| 6 | Bỏ qua giá trị lỗi nhưng vẫn tính hàng ẩn và các hàm SUBTOTAL, AGGREGATE |
| 7 | Bỏ qua cả hàng ẩn và giá trị lỗi nhưng vẫn tính hàm SUBTOTAL, AGGREGATE |
3. Ví dụ về hàm AGGREGATE trong Excel
Ví dụ 1: Tính tổng và bỏ qua lỗi
Nếu muốn tính tổng nhưng không bị ảnh hưởng bởi các ô chứa lỗi, hãy sử dụng hàm AGGREGATE với tùy chọn 6 (bỏ qua lỗi trong vùng dữ liệu).
Công thức: =AGGREGATE(9,6,B2:B12)
Kết quả: 560 (chỉ cộng các giá trị hợp lệ trong phạm vi).
Giải thích:
- 9 → đại diện cho hàm SUM trong AGGREGATE.
- 6 → bỏ qua các ô chứa lỗi.
- B2:B12 → phạm vi dữ liệu cần tính tổng.
Dùng hàm AGGREGATE tính tổng bỏ qua lỗi trong Excel
Ví dụ 2: Tính tổng nhưng không bỏ qua lỗi
Nếu bạn không thiết lập tùy chọn bỏ qua lỗi (chọn 4), chỉ cần trong vùng dữ liệu có một ô bị lỗi thì toàn bộ kết quả trả về cũng sẽ là lỗi.
Công thức: =AGGREGATE(9,4,B2:B12)
Kết quả: #DIV/0! (hoặc lỗi tương tự), vì vùng dữ liệu có chứa các ô lỗi như #DIV/0!, #VALUE!, #NUM!, và tùy chọn 4 không bỏ qua lỗi trong phép tính.
Giải thích:
- 9 → thực hiện phép SUM.
- 4 → tính cả các ô lỗi, dẫn đến công thức bị lỗi.
- B2:B12 → vùng dữ liệu có chứa lỗi (#DIV/0!, #VALUE!, #NUM!).
Tính tổng nhưng không bỏ qua lỗi trong vùng dữ liệu
Ví dụ 3: Tìm giá trị lớn nhất và bỏ qua lỗi
Khi muốn xác định giá trị lớn nhất trong vùng dữ liệu nhưng một số ô chứa lỗi (#DIV/0!, #VALUE!, #NUM!), ta có thể dùng tùy chọn 6 để Excel tự động bỏ qua các lỗi đó và chỉ tính trên các giá trị hợp lệ.
Công thức: =AGGREGATE(4,6,B2:B12)
Kết quả: 92 (hàm tự bỏ qua các ô lỗi và trả về giá trị lớn nhất hợp lệ trong vùng dữ liệu).
Giải thích:
- 4 → đại diện cho hàm MAX trong AGGREGATE.
- 6 → tùy chọn bỏ qua lỗi.
- B2:B12 → phạm vi chứa dữ liệu cần tìm giá trị lớn nhất.
Tìm giá trị lớn nhất bằng hàm AGGREGATE và bỏ qua lỗi
Ví dụ 4: Tính giá trị trung bình và bỏ qua lỗi
Khi muốn tính giá trị trung bình của vùng dữ liệu nhưng có một số ô bị lỗi (#DIV/0!, #VALUE!, #NUM!), ta có thể dùng hàm AGGREGATE với tùy chọn 6 để loại bỏ các giá trị lỗi và chỉ lấy dữ liệu hợp lệ.
Công thức: =AGGREGATE(1,6,B2:B12)
Kết quả: 70 (hàm tính trung bình các giá trị hợp lệ: 85, 92, 60, 73, 40, 55, 65, 90).
Giải thích:
- 1 → đại diện cho hàm AVERAGE trong danh sách hàm của AGGREGATE.
- 6 → tùy chọn bỏ qua các giá trị lỗi.
- B2:B12 → phạm vi dữ liệu cần tính trung bình.
Tính giá trị trung bình bằng hàm AGGREGATE trong Excel
4. Lưu ý khi sử dụng hàm AGGREGATE
Để hàm AGGREGATE hoạt động chính xác và hiệu quả, cần ghi nhớ một số điểm sau:
- Khi gõ =AGGREGATE(, Excel sẽ hiển thị danh sách function_num (từ 1–19) để chọn. Sau khi chọn xong và nhập dấu phẩy, danh sách options sẽ xuất hiện giúp bạn xác định điều kiện loại trừ lỗi hoặc hàng ẩn.
- Nếu bỏ sót đối số bắt buộc như ref1 hoặc array, hàm sẽ báo lỗi #VALUE!. Hãy đảm bảo nhập đầy đủ tham số cần thiết.
- Hàm AGGREGATE chỉ hỗ trợ phạm vi trong một bảng tính duy nhất, không áp dụng cho tham chiếu 3D (ví dụ: Sheet1:Sheet3!A1:A10).
- Hàm hoạt động ổn định nhất khi áp dụng cho dữ liệu theo cột. Nếu dùng trên hàng ngang, kết quả có thể sai hoặc phát sinh lỗi ngoài ý muốn.
Hàm AGGREGATE giúp bạn tối ưu việc tính toán và xử lý dữ liệu phức tạp trong Excel, đặc biệt khi cần loại bỏ lỗi hoặc hàng ẩn. Đừng quên theo dõi chuyên mục Kinh nghiệm của Siêu Thị Điện Máy – Nội Thất Chợ Lớn để cập nhật thêm nhiều mẹo và hướng dẫn sử dụng Excel hiệu quả hơn mỗi ngày.
Bạn có thể thao tác nhanh và làm việc tiện lợi hơn với các mẫu máy tính bảng hiệu năng cao, màn hình lớn, hỗ trợ Excel mượt mà. Tham khảo ngay tại Siêu Thị Điện Máy – Nội Thất Chợ Lớn để chọn thiết bị phù hợp cho học tập và công việc văn phòng.
Để lại một bình luận