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_num	Hàm	Miêu tả	Dạng hàm
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

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

options	Miêu tả
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

- 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ính	Miêu tả
LARGE	Thứ tự n cần tìm (Làm LARGE tìm ra giá trị lớn thứ n trong mảng)
SMALL	Thứ 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:

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

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.

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

Tham khảo bài viết gốc ở : Một trong các hàm Excel mạnh nhất – Hàm Aggregate

Thủ thuật Excel tại : Coffee Excel