Hàm AGGREGATE là một hàm khá phức tạp, tuy nhiên nó được coi là một trong những hàm Excel mạnh nhất vì nó có thể sử dụng thay thế cho nhiều hàm khác trong Excel. Trong bài viết này, Gitiho sẽ cùng bạn tìm hiểu về sức mạnh của hàm AGGREGATE trong Excel nhé!
Aggregate là kết hợp, như vậy đúng như tên gọi của nó hàm AGGREGATE là một hàm kết hợp được thêm vào Excel từ phiên bản 2010 trở lên. Cú pháp của hàm AGGREGATE gồm nhiều Options nên chúng ta có thể sử dụng hàm AGGREGATE để thay thế cho các hàm như: SUM, COUNT, MAX, MIN, AVERAGE, LARGE, SMALL,… Đây chính là sức mạnh vượt trội của hàm AGGREGATE trong Excel.
Xem thêm: Tải về bài tập Excel có lời giải (Hướng dẫn cách dùng hàm IF, ROUND, SUM, MIN, MAX, AVERAGE)
Cú pháp hàm AGGREGATE dạng tham chiếu như sau:
= AGGREGATE(function_num, options, ref1, [ref2], …)
Cú pháp hàm AGGREGATE dạng mảng:
= AGGREGATE(function_num,options,array,[k])
Trong đó:
function_num | Hàm | Miêu tả | Dạng |
1 | AVERAGE | Trung bình | Tham chiếu |
2 | COUNT | Đếm số ô chứa giá trị số | Tham chiếu |
3 | COUNTA | Đếm số ô chứa giá trị (khác rỗng) | Tham chiếu |
4 | MAX | Giá trị lớn nhất | Tham chiếu |
5 | MIN | Giá trị nhỏ nhất | Tham chiếu |
6 | PRODUCT | Nhân các giá trị với nhau | Tham chiếu |
7 | STDEV.S | Tính độ lệch chuẩn của mẫu | Tham chiếu |
8 | STDEV.P | Tính độ lệch chuẩn của tổng thể | Tham chiếu |
9 | SUM | Tính tổng các giá trị | Tham chiếu |
10 | VAR.S | Tính phương sai của mẫu | Tham chiếu |
11 | VAR.P | Tính phương sai của tổng thế | Tham chiếu |
12 | MEDIAN | Trung vị (giá trị ở giữa) của các giá trị | Tham chiếu |
13 | MODE.SNGL | Giá trị xuất hiện nhiều nhất | Tham chiếu |
14 | LARGE | Giá trị lớn thứ n | Mảng |
15 | SMALL | Giá trị nhỏ thứ n | Mảng |
16 | PERCENTILE.INC | Phân vị thứ k của các giá trị trong phạm vi | Mảng |
17 | QUARTILE.INC | Tứ phân vị của tập dữ liệu | Mảng |
18 | PERCENTILE.EXC | Phân vị thứ k của các giá trị trong phạm vi | Mảng |
19 | QUARTILE.EXC | Tứ phân vị của tập dữ liệu | Mảng |
options | Điều kiện |
0 hoặc để trống | Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác. |
1 | Bỏ qua các dòng bị ẩn, Bỏ qua những ô SUBTOTAL hoặc AGGREGATE khác. |
2 | Bỏ qua các giá trị lỗi, Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác. |
3 | Bỏ qua các dòng bị ẩn, các giá trị lỗi và Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác. |
4 | Bỏ qua các dòng trống |
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 |
Hàm LARGE: Là thứ tự n của giá trị lớn nhất cần tìm trong mảng
Hàm SMALL: Là thứ tự n của giá trị bé nhất cần tìm trong mảng
Lưu ý:
- Ngay sau khi bạn nhập tên hàm AGGREGATE trong Excel, danh sách function_num và options sẽ xuất hiện, bạn có thể căn cứ vào đó để lựa chọn con số tương ứng.
- Nếu để trống giá trị ref1, ref2 khi cần thiết thì kết quả hàm AGGREGATE trả về lỗi #VALUE. Các hàm bắt buộc phải có đối số ref2 là: hàm LARGE (array;k), hàm SMALL (array;k), hàm PERCENTILE.INC (array;k), hàm QUARTILE.INC (array;quart), hàm PERCENTILE.EXC (array;k), hàm QUARTILE.EXC (array;quart)
- Hàm AGGREGATE tính toán theo chiều dọc. Ví dụ: Bạn sử dụng funtion_num =1 để tính tổng phụ của một phạm vi ngang với công thức: =AGGREGATE(1;1;ref1) thì kết quả của bạn sẽ không bị ảnh hưởng nếu bạn ẩn một cột nhưng nếu bạn ẩn một hàng trong phạm vi dọc thì sẽ ảnh hướng đến kết quả.
- Hàm AGGREGATE không hoạt động trong tham chiếu 3D.
Xem thêm: 25 kỹ năng Excel mà bạn nên biết từ cơ bản, trung cấp đến nâng cao
Sử dụng hàm AGGREGATE để tính toán các giá trị sau:
- Tìm giá trị lớn nhất trong vùng B2:B10, bỏ qua giá trị lỗi: =AGGREGATE(4;6;B2:B10)
- Tìm giá trị lớn thứ 4 trong vùng B2:B10, bỏ qua giá trị lỗi: =AGGREGATE(14;6;B2:B10;4)
- Tìm tổng của vùng B2:B10, bỏ qua dòng bị ẩn: =AGGREGATE(9;7;B2:B10)
- Tìm trung vị của vùng B2:B10 và C2:C10 bỏ qua giá trị lỗi: =AGGREGATE(12;6;B2:B10;C2:C10)
Kết quả thu được như sau:
Trong bài viết trên, Gitiho đã cùng bạn tìm hiểu về hàm AGGREGATE, một trong những hàm mạnh nhất và nhiều công dụng nhất trong Excel. Hy vọng bạn đã hiểu rõ và áp dụng thành công cho công việc của mình. Đừng quên theo dõi chúng mình để xem thêm các bài viết bổ ích khác nhé!
Ngay bây giờ hãy cùng luyện tập thêm với bộ bài tập Excel có đáp án của Gitiho để nằm lòng tin học văn phòng nhé!
Khóa học liên quan