Hàm Aggregate Trong Excel – Cách Ứng Dụng Trong Công Việc
Hàm Aggregate là hàm tổng hợp của các hàm khác trong Excel, vô cùng tiện dụng mà chắc hẳn nhiều người đi làm chưa biết tới! Hàm Aggregate cho phép bạn ẩn hoặc bỏ qua các hàng, các giá trị bị lỗi. Cùng Tin học Đại Dương khám phá các tính năng thông minh của hàm Aggregate và cách ứng dụng hàm trong công việc văn phòng nhé!
1. Cú pháp hàm Aggregate trong Excel
Cú pháp hàm Aggregate trong Excel
Đối với biểu mẫu tham chiếu:
=AGGREGATE(function_num, options, ref1, ref2, …)
Trong đó:
- function_num: Bao gồm số 1 – 19, quy định chức năng riêng (số đại diện cho hàm). Cụ thể xem ở bảng Function_num phía dưới.
- options: Bao gồm số 0 – 7, quy định tùy chọn khi sử dụng hàm (các lựa chọn bỏ qua theo yêu cầu). Xem chi tiết ở bảng Options phía dưới.
- ref1: Vùng dữ liệu/ô tham chiếu (danh sách tham chiếu, tối đa 254 ref).
- ref2: Không bắt buộc. Đối số số thứ hai – k (dùng cho hàm có tham số k).
Đối với mảng, biểu mẫu bảng:
=AGGREGATE(function_num, options, array, k)
Trong đó:
- function_num và options: Tương tự như trên
- Array: Mảng cần xét.
- K: Đối số tương ứng với mảng.
Bảng Function_num trong hàm Aggregate
| Function_num | Hàm tương ứng | Chức năng | Ref2 |
| 1 | AVERAGE | Tính trung bình | |
| 2 | COUNT | Đếm số ô chứa giá trị số | |
| 3 | COUNTA | Đếm số ô có chứa số và ký tự (các ô chứa bất kỳ kiểu giá trị nào, gồm cả giá trị lỗi và văn bản trống) | |
| 4 | MAX | Giá trị lớn nhất | |
| 5 | MIN | Giá trị nhỏ nhất | |
| 6 | PRODUCT | Tính kết quả cho các ô (Nhân tất cả các đối số đã chọn, trả về tích của chúng) | |
| 7 | STDEV.S | Ước tính độ lệch chuẩn mẫu dựa trên mẫu | |
| 8 | STDEV.P | Tính toán độ lệch chuẩn dựa trên toàn bộ số | |
| 9 | SUM | Tính tổng | |
| 10 | VAR.S | Tính toán phương sai dựa trên mẫu | |
| 11 | VAR.P | Tính toán phương sai trên toàn bộ tập hợp | |
| 12 | MEDIAN | Tính số trung bình vị | |
| 13 | MODE.SNGL | Đo lường xu hướng trung tâm | |
| 14 | LARGE | Tìm giá trị lớn thứ k | k |
| 15 | SMALL | Tìm giá trị nhỏ thứ k | k |
| 16 | PERCENTILE.INC | Trả về phân vị thứ k | k |
| 17 | QUARTILE.INC | Trả về tứ phân vị của tập dữ liệu | quart |
| 18 | PERCENTILE.EXC | Trả về giá trị tại một phân vị đã cho của một tập dữ liệu, giữa 0 và 1 | k |
| 19 | QUARTILE.EXC | Trả về tứ phân vị của tập dữ liệu thứ k của các giá trị trong mảng dữ liệu/phạm vi dữ liệu | quart |
Bảng Options trong hàm Aggregate
| Options | Chức năng |
| O/trống | Bỏ qua những ô SUBTOTAL/ AGGREGATE khác. |
| 1 | Bỏ qua các dòng bị ẩn, những ô SUBTOTAL hoặc AGGREGATE khác. |
| 2 | Bỏ qua các giá trị lỗi, những ô SUBTOTAL hoặc AGGREGATE khác. |
| 3 | Bỏ qua các dòng bị ẩn, các giá trị lỗi, những ô SUBTOTAL hoặc AGGREGATE khác. |
| 4 | Không bỏ qua bất kỳ thứ gì |
| 5 | Bỏ qua các dòng bị ẩn |
| 6 | Bỏ qua giá trị lỗi |
| 7 | Bỏ qua các dòng bị ẩn và các giá trị lỗi |
2. Ứng dụng hàm Aggregate trong công việc
Quan sát bảng dữ liệu dưới, bạn có thể thấy có nhiều ô trong Excel bị lỗi #DIV/O!, lỗi #NAME?. Nếu sử dụng hàm SUM như cách thông thường, bạn sẽ không thể tính tổng cho cột Số lượng và cột Thành tiền được.
Vì vậy, hàm Aggregate là hàm cực kỳ hữu ích trong trường hợp này. Hàm cho phép bạn tính tổng, tìm giá trị lớn nhất, giá trị nhỏ nhất cho phép mà có thể bỏ qua các ô bị ẩn hoặc bỏ qua các hàng, các giá trị bị lỗi.
Tính tổng cho cột Số lượng, Thành tiền
Nhập công thức = AGGREGATE(9;3;D5:D40)
Giải thích:
- Số 9: Tương ứng với hàm SUM để tính tổng.
- Số 3: Bỏ qua các dòng bị ẩn, các giá trị lỗi, những ô SUBTOTAL hoặc AGGREGATE khác.
- D5:D40: Vùng dữ liệu cần tính tổng
Bạn đặt con chuột tại góc phải bên dưới ô D3, xuất hiện mũi dấu cộng. Bạn kéo ngang qua để tính tổng cho cột Thành tiền.
Tìm số lớn nhất bằng hàm Aggregate:
Nhập công thức: = AGGREGATE (5;3;D5:D40)
Giải thích công thức hàm Aggregate:
- Số 5: Tương ứng với hàm MIN để tìm số nhỏ nhất.
- Số 3: Bỏ qua các dòng bị ẩn, các giá trị lỗi, những ô SUBTOTAL hoặc AGGREGATE khác.
- D5:D40: Vùng dữ liệu cần tính tổng.
Tương tự, sao chép công thức từ ô D5 sang ô E2 để tính tìm giá trị nhỏ nhất trong cột Thành Tiền.
Tìm số nhỏ nhất:
Nhập công thức: = AGGREGATE (4;3;D5:D40)
Giải thích:
- Số 4: Tương ứng với hàm MAX để tìm số lớn nhất.
- Số 3: Bỏ qua các dòng bị ẩn, các giá trị lỗi, những ô SUBTOTAL hoặc AGGREGATE khác.
- D5:D40: Vùng dữ liệu cần tính tổng
Tương tự, sao chép công thức từ ô D1 sang ô E1 để tính tìm giá trị lớn nhất trong cột Thành Tiền.
Kết quả cuối cùng:
Bạn cần lọc sản phẩm và tự động cập nhật bảng Số lớn nhất, Số nhỏ nhất, Tổng. Ứng dụng hàm Aggregate trong trường hợp này giúp bạn cập nhật số liệu một cách nhanh chóng và chính xác nhất, tiết kiệm thời gian và tăng hiệu suất làm việc.
Ví dụ trong trường hợp này, mình lọc sản phẩm “Mực ống Filler” và “Nghêu lụa”.
Nhìn vào trong hình, bạn có thể thấy:
- Cột Số lượng: Số lớn nhất là 876, số nhỏ nhất là 486, tổng cột số lượng là 1362.
- Cột Thành tiền: Số lớn nhất là 569.400.000 vnd, số nhỏ nhất là 58.320.000 vnd, tổng cột số lượng là 627.720.000 vnd.
Hy vọng bài viết này sẽ giúp các bạn hiểu rõ về hàm Aggregate trong Excel và biết cách ứng dụng hàm tính toán này trong công việc. Chúc các bạn thành công và tối ưu hiệu suất làm việc của mình! Nếu các bạn còn bất kỳ câu hỏi nào về tin học văn phòng, đừng ngần ngại inbox cho Tin học Đại Dương để được giải đáp kiến thức nhé!
—————————————————
Tin Học Đại Dương
Học thật – Thi thật – Giá trị thật
Fanpage:Tin Học Đại Dương
Website: tinhocdaiduong.vn
Hotline: 028 37222786
Cơ sở 1: Số 25, Đường 23, Phường Linh Chiểu, Tp Thủ Đức, HCM (0932413041)
Cơ sở 2: Tầng 4, Nhà Văn Hóa Sinh Viên, Khu Đại Học Quốc Gia, Tp Thủ Đức, HCM (0979076051)
