Cách tính phép tính bỏ qua các ô bị lỗi hoặc ô bị ẩn trong exel

Trong Exel, đôi khi bạn muốn tính một mảng ô, hay một cột, một hàng dữ liệu với các hàm SUM để tính tổng, hàm AVERAGE để tính giá trị trung bình, hàm COUNT để đếm, hàm MAX để tìm giá trị lớn nhất, hàm MIN để tìm giá trị nhỏ nhất. Tuy nhiên sẽ gặp một số vấn đề khi tính toán khi sử dụng các hàm trên thì nếu gặp các ô bị các lỗi #N/A, DIV… thì kết quả sẽ trả về lỗi.

Hoặc trường hợp cũng sử dụng các hàm trên nhưng có các dòng ẩn, và bạn không muốn tính các dòng bị ẩn vào kết quả thì sẽ khó khăn khi chọn từng dòng dữ liệu một, tuy nhiên với hàm AGGREGATE sẽ giải quyết các vấn đề trên.

Cách dùng hàm AGGREGATE để tạo phép tính có điều kiện bỏ qua các ô bị lỗi và các ô bị ẩn.

Hàm AGGREGATE là một hàm sử dụng để tạo một phép tính với các điều kiện lọc khác nhau nhằm tạo ra các phép tính theo ý muốn để bỏ qua các giá trị không cần thiết gây phát sinh lỗi hoặc kết quả không được như ý muốn người dùng.

Cấu trung sử dụng hàm như sau:

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

Với:

function_num : Là số từ 1 đến 19 lựa chọn công thức tính của hàm với 19 hàm có thể sử dụng. Ví dụ bạn chọn số 1 là hàm AVERAGE tính bình quân, số 9 là hàm SUM tính tổng. Về 19 hàm sử dụng được thống kê như sau:

Số	Hàm tương ứng
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
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

options: Bạn chọn điều kiện tính với số từ 0 đến 7 với các lựa chọn tương ứng như sau:

Tùy chọn	Cách tính
0 hoặc bỏ qua	Bỏ qua các hàm lồng SUBTOTAL và AGGREGATE
1	Bỏ qua các hàng ẩn, hàm lồng SUBTOTAL và AGGREGATE
2	Bỏ qua các giá trị lỗi, hàm lồng SUBTOTAL và AGGREGATE
3	Bỏ qua các hàng ẩn, giá trị lỗi, hàm lồng SUBTOTAL và AGGREGATE
4	Không bỏ qua bất kỳ thứ gì
5	Bỏ qua các hàng ẩn
6	Bỏ qua các giá trị lỗi
7	Bỏ qua các hàng ẩn và các giá trị lỗi

ref1 : Mảng cần thực hiện phép tính toán

Ví dụ sử dụng AGGREGATE để tạo phép tính tổng (SUM) bỏ qua các ô bị lỗi

Ở ví dụ sau khi bạn dùng hàm SUM để tính từ B3 cho đến B8 kết quả bị lỗi, vì vậy để có một phép tính tổng sử dụng hàm SUM bỏ qua các ô bị lỗi thì bạn sử dụng hàm AGGREGATE như sau:

=AGGREGATE(9,6,B3:B8)

Với:

chọn hàm

9

SUM

tùy chọn

6	Bỏ qua các giá trị lỗi

Và phạm vi tính từ B3:B8 ta được kết quả như sau:

Tương tự như vậy để tính phép tính SUM tính tổng bỏ qua các ô bị lỗi và bị ẩn:

bạn sẽ chọn tùy chọn 7 và được kết quả như sau: (Ở ví dụ này tôi ẩn hàng 4 đến 6)

 

Như vậy tương tự như các hàm khác, bạn sẽ chọn các số lựa chọn hàm tính toán từ 1 đến 19 và các số này được gợi ý khi gõ hàm AGGREGATE nên rất thuận tiện khi sử dụng. Với phương pháp này bạn có thể tính toán rất nhiều hàm khác nhau mà bỏ qua các ô bị lỗi, bị ẩn nhằm đạt được mục tiêu tính toán của bạn.