9kiem.vn

Tổng hợp Cách sử dụng, có ví dụ minh họa dễ hiểu

Chia sẻ Cách sử dụng, có ví dụ minh họa dễ hiểu là chủ đề trong bài viết hôm nay của Cửu Kiếm. Đọc bài viết để biết chi tiết nhé.

Hàm AGGREGATE là một hàm khá lạ lẫm với nhiều người sử dụng Excel, tuy nhiên nếu biết sử dụng sẽ làm tăng tốc độ tính toán lên rất nhanh. Cùng 9kiem.vn tìm hiểu cách sử dụng hàm AGGREGATE trong Excel tại bài viết sau nhé!

Ví dụ trong bài được thực hiện trên phiên bản Excel 2016. Bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017 và Microsoft Excel 365.

1Công thức hàm AGGREGATE

Công thức hàm AGGREGATE

Từ Aggregate trong tiếng Anh có nghĩa là “Tổng hợp”. Hàm AGGREGATE là hàm tổng hợp của các hàm khác và cho bạn thêm các tùy chọn bỏ qua hoặc ẩn các hàng hoặc giá trị lỗi.

Cú pháp

=AGGREGATE(function_num, options, ref1, ref2, …)

Trong đó:

  • function_num: Từ 1 đến 19, quy định chức năng bạn muốn sử dụng, được thể hiện ở bảng Function_num bên dưới.
  • options: Từ 0 đến 7, quy định tùy chọn khi sử dụng hàm, được thể hiện ở bảng Options bên dưới.
  • ref1: Ô/vùng tham chiếu.
  • ref2: Không bắt buộc. Đối số số thứ hai – k.

Đối với mảng, công thức hàm AGGREGATE như sau:

=AGGREGATE(function_num, options, array, k)

Trong đó:

  • array: Mảng cần xét.
  • k: Đối số tương ứng với mảng.

Hàm AGGREGATE

Bảng Function_num trong hàm AGGREGATE:

Function_num Chức năng Ref2
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  
13 MODE.SNGL  
14 LARGE k
15 SMALL k
16 PERCENTILE.INC k
17 QUARTILE.INC quart
18 PERCENTILE.EXC k
19 QUARTILE.EXC quart

Bảng Options trong hàm AGGREGATE:

Options Miêu tả
0/trống Bỏ qua những ô SUBTOTAL hoặc 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

2Ví dụ cơ bản của hàm AGGREGATE

Bạn có thể truy cập vào đây để tải về file ví dụ bên dưới.

Với một hàm khá lạ lẫm với đa số mọi người, hãy đi qua một vài ví dụ để hiểu nhanh nhất cách hàm này hoạt động nhé!

Trong ví dụ bên dưới, ta có vùng dữ liệu từ A1 đến A10, trong đó ô A6 ở dòng thứ 6 đang bị ẩn (có giá trị là 6) và ô A2 đang bị lỗi #DIV/0!. 

Ví dụ hàm AGGREGATE

Bạn sử dụng hàm AGGREGATE để tính tổng của vùng A1:A10, bỏ qua các hàng ẩn và giá trị lỗi như sau: =AGGREGATE(9,7,A1:A10)

Sử dụng hàm AGGREGATE để tính tổng bỏ qua dòng bị ẩn và giá trị lỗi

Bạn sử dụng hàm AGGREGATE để tính trung bình của vùng A1:A10, bỏ qua các giá trị lỗi như sau: =AGGREGATE(1,6,A1:A10)

Sử dụng hàm AGGREGATE để tính trung bình bỏ qua các giá trị lỗi

Bạn sử dụng hàm AGGREGATE để đếm số ô chứa dữ liệu của vùng A1:A10, bỏ qua các hàng ẩn như sau: =AGGREGATE(2,5,A1:A10)

Sử dụng hàm AGGREGATE để đếm số ô chứa dữ liệu bỏ qua các hàng ẩn

Như vậy bạn có thể thấy, hàm AGGREGATE cho bạn kết quả nhanh chóng khi tính toán mà không cần phải xử lý giá trị lỗi hay ẩn/hiện dòng ẩn. 

3Những lưu ý khi dùng hàm AGGREGATE

Danh sách function_num sẽ được hiển thị đầy đủ ngay sau khi bạn gõ AGGREGATE.

Danh sách function_num

Danh sách options sẽ được hiển thị đầy đủ ngay sau khi bạn chọn function_num.

Danh sách options

Nếu không cung cấp đủ đối số ref1, ref2 khi cần thiết, hàm AGGREGATE trả về giá trị lỗi #VALUE!.

Lỗi #VALUE! khi sử dụng hàm AGGREGATE

Hàm AGGREGATE được thiết kế để dùng cho các cột dữ liệu hoặc phạm vi dọc. Nếu bạn sử dụng hàm này cho một phạm vi ngang thì việc ẩn một cột sẽ không ảnh hưởng đến kết quả.

Hàm AGGREGATE không bỏ qua cột bị ẩn

Trên đây là bài viết chia sẻ đến bạn cách dùng hàm AGGREGATE. Hy vọng những thông tin này đã giúp bạn ứng dụng hàm này thành công cho công việc của mình.