Một trong các hàm Excel mạnh nhất – Hàm Aggregate

Home / Hàm / Một trong các hàm Excel mạnh nhất – Hàm Aggregate

AGGREGATE được thêm vào Excel từ phiên bản Excel 2010, từ khi được thêm vào, AGGREGATE có thể được xem là một trong các hàm Excel mạnh nhất. Trên thực tế là mình cũng hiếm khi xài tới AGGREGATE vì hàm này khá…phức tạp nhưng thôi hãy cùng Coffee Excel tìm hiểu sức mạnh của hàm AGGREGATE nhé. Cũng phải có lý do thì hàm AGGREGATE mới được xem làm một trong các hàm Excel mạnh nhất.

Ngoài ra, hãy cùng Coffee Excel học thủ thuật Excel tài chính tại Coffee Excel nhé.

1. Công dụng

Từ Aggregate có nghĩa là “kết hợp”, hàm Aggregate là một hàm kết hợp có thể thay thế cho các hàm khác nhau.

Hàm AGGREGATE có thể là SUM, COUNT, MAX, MIN, AVERAGE, LARGE, SMALL,… Điểm làm nên sức mạnh của hàm AGGRAGATE là hàm này sẽ cho chúng ta thêm nhiều Option khi thực hiện các hàm thông dụng mà bình thường sẽ không có

2. Cú pháp

Hàm AGGRAGATE có cú pháp như sau:

=AGGREGATE(function_num,options,ref1,[ref2],…) (dạng tham chiếu)

 

Hoặc

=AGGREGATE(function_num,options,array,[k]) (dạng mảng)

 

Trong đó:

– Các giá trị của tham số function_num như sau:

function_numHàmMiêu tảDạng hàm
1AVERAGETrung bìnhTham 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
4MAXGiá trị lớn nhấtTham chiếu
5MINGiá trị nhỏ nhấtTham chiếu
6PRODUCTNhân các giá trị với nhauTham chiếu
7STDEV.STính độ lệch chuẩn của mẫuTham chiếu
8STDEV.PTính độ lệch chuẩn của tổng thểTham chiếu
9SUMTính tổng các giá trịTham chiếu
10VAR.STính phương sai của mẫuTham chiếu
11VAR.PTính phương sai của tổng thếTham chiếu
12MEDIANTrung vị (giá trị ở giữa) của các giá trịTham chiếu
13MODE.SNGLGiá trị xuất hiện nhiều nhấtTham chiếu
14LARGEGiá trị lớn thứ nMảng
15SMALLGiá trị nhỏ thứ nMảng

– Các giá trị của tham số options như sau:

optionsMiêu tả
0 hoặc để trốngBỏ 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/ Ref 2: các giá trị khi hàm được sử dụng dưới dạng tham chiếu

array: Mảng nếu hàm được sử dụng dưới dạng mảng

– [k]: Tham số nếu sử dụng hàm AGGREGATE với công dụng tương ứng như sau:

Phép tínhMiêu tả
LARGEThứ tự n cần tìm (Làm LARGE tìm ra giá trị lớn thứ n trong mảng)
SMALLThứ tự n cần tìm (Làm SMALL tìm ra giá trị nhỏ thứ n trong mảng)

Như vậy, chúng ta thấy, hàm AGGREGATE cho chúng ta một loạt các điều kiện cực kỳ đa dạng và chi tiết để chúng ta có thể sử dụng theo nhu cầu. Điều này làm cho hàm AGGREGATE có thể xem là một trong các hàm mạnh nhất trong Excel. Nhưng cũng chính điều này làm cho hàm AGGREGATE trở nên khá phức tạp, ít người sử dụng

Nếu chúng ta nghĩ rằng những điều kiện hay lựa chọn ở trên quá nhiều để nhớ thì thật may mắn là Excel có một công cụ để chúng ta viết hàm cực kỳ hiệu quả là Excel sẽ tự gợi ý cho chúng ta từng thông số của hàm khi chúng ta viết hàm:

hàm Excel mạnh nhất

hàm Excel mạnh nhất

3. Ứng dụng

Để tìm hiểu một số ứng dụng của hàm AGGREGATE, chúng ta hãy thử xem 2 trường hợp sau nhé:

3.1. Trường hợp 1: Lấy giá trị lớn thứ n trong mảng có ô chứa giá trị lỗi

Trường hợp chúng ta muốn lấy ra giá trị lớn thứ 2 của mảng nhưng trong mảng lại có 1 ô có giá trị lỗi. Lúc này, chúng ta không thể sử dụng hàm LARGE và hàm LARGE sẽ luôn trả về kết quả là lỗi tương ứng.

Lúc này, hàm AGGREGATE lại phát huy tác dụng với tùy chọn bỏ qua các ô bị lỗi

hàm Excel mạnh nhất

3.2 Trường hợp 2: Tính giá trị trung bình bỏ qua các dòng bị ẩn

Ở Trường hợp này, chúng ta có thể tính giá trị trung bình và bỏ qua các dòng bị ẩn. Nếu sử dụng hàm AVERAGE, chúng ta cần phải chọn thủ công từng khu vực nhưng khi các ô ẩn thay đổi thì chúng ta sẽ phải…làm lại từ đầu.

Hàm AGGREGATE với điều kiện bỏ qua các dòng bị ẩn sẽ giúp chúng ta giải quy được vấn đề này.

hàm Excel mạnh nhất

Như vậy, chúng ta đã hiểu được lý do người ta xem hàm AGGREGATE là một trong các hàm Excel mạnh nhất rồi nhé.

Cả nhà tiếp tục cùng học thủ thuật Excel kiểm toán tại Coffee Excel nhé.

Nguồn: Excel Off the Grid

Dịch và Hiệu đính: Coffee Excel

Đếm số ký tự trong Excel với hàm LEN

Bài viết liên quan

Professionals in Finance, Accounting, Auditing. A financial, accounting, auditing Excel lovers.