Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất

Lê Nguyễn Nhật Phương
Lê Nguyễn Nhật Phương
Dec 15 2022

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é!
 

Biến Excel thành công cụ phân tích dữ liệu chuyên sâu.

Hàm AGGREGATE trong Excel

Công dụng của hàm AGGREGATE

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

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: (1-19) biểu thị hàm cần dùng tương ứng như sau:
function_numHàmMiêu tảDạng
1AVERAGE
 
Trung bình
 
Tham chiếu
 
2COUNT
 
Đếm số ô chứa giá trị số
 
Tham chiếu
 
3COUNTA
 
Đếm số ô chứa giá trị (khác rỗng)
 
Tham chiếu
 
4MAX
 
Giá trị lớn nhất
 
Tham chiếu
 
5MIN
 
Giá trị nhỏ nhất
 
Tham chiếu
 
6PRODUCT
 
Nhân các giá trị với nhau
 
Tham chiếu
 
7STDEV.S
 
Tính độ lệch chuẩn của mẫu
 
Tham chiếu
 
8STDEV.P
 
Tính độ lệch chuẩn của tổng thể
 
Tham chiếu
 
9SUM
 
Tính tổng các giá trị
 
Tham chiếu
 
10VAR.S
 
Tính phương sai của mẫu
 
Tham chiếu
 
11VAR.P
 
Tính phương sai của tổng thế
 
Tham chiếu
 
12MEDIAN
 
Trung vị (giá trị ở giữa) của các giá trị
 
Tham chiếu
 
13MODE.SNGL
 
Giá trị xuất hiện nhiều nhất
 
Tham chiếu
 
14LARGE
 
Giá trị lớn thứ n
 
Mảng
 
15SMALL
 
Giá trị nhỏ thứ n
 
Mảng
 
16PERCENTILE.INCPhân vị thứ k của các giá trị trong phạm vi
 
Mảng
 
17QUARTILE.INCTứ phân vị của tập dữ liệu
 
Mảng
 
18PERCENTILE.EXCPhân vị thứ k của các giá trị trong phạm vi
 
Mảng
 
19QUARTILE.EXCTứ phân vị của tập dữ liệu
 
Mảng
 
  • options: (0-7) thể hiện điều kiện của hàm như sau:
optionsĐiều kiện
0 hoặc để trống
 
Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác.
 
1Bỏ qua các dòng bị ẩn, Bỏ qua những ô SUBTOTAL hoặc AGGREGATE khác.
 
2Bỏ qua các giá trị lỗi, Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác.
 
3Bỏ qua các dòng bị ẩn, các giá trị lỗi và Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác.
 
4Bỏ qua các dòng trống
 
5Bỏ qua các dòng bị ẩn
 
6Bỏ qua giá trị lỗi
 
7Bỏ qua các dòng bị ẩn và các giá trị lỗi
 
  • ref1: Giá trị đầu tiên khi có nhiều tham số cần tính toán
  • ref2, ref3,...ref253: Các giá trị cần tính toán tiếp theo
  • array: Mảng cần xét
  • k: Đối tượng tương ứng với mảng cần xét. Ví dụ:

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.

 

Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất

 

Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất
 

 

- 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

Ứng dụng hàm AGGREGATE

Sử dụng hàm AGGREGATE để tính toán các giá trị sau:

Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất
 

- 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)

Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất
 

Kết quả thu được như sau:

Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất
 

Xem thêm các ứng dụng của hàm AGGREGATEHai cách tính tổng bỏ qua các giá trị lỗi trong Excel

Cách sử dụng hàm AGGREGATE - một trong những hàm Excel mạnh nhất

Kết luận

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é! 

5/5 - (1 bình chọn)

5/5 - (1 bình chọn)

Bài viết liên quan

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội