Mách bạn cách dùng hàm đếm ô dữ liệu trong Excel chi tiết nhất

Trong công việc tin học văn phòng, không tránh khỏi những lúc chúng ta phải tổng hợp số lượng của một dữ liệu nào đó. Đây chính là lúc hàm đếm phát huy tác dụng. Cùng tìm hiểu hàm đếm trong bài viết sau đây nhé!

Việc thực hiện đếm những dữ liệu thỏa mãn theo một vài điều kiện nhất định cho trước trong Microsoft Excel là công việc chúng ta rất hay gặp phải khi làm việc. Nhưng việc “tập đếm” không phải lúc nào cũng có thể thực hiện dễ dàng, đặc biệt khi phải sử dụng những hàm đếm có điều kiện. Trong bài viết này, 123job.vn sẽ giới thiệu tới các bạn cách xử lý chi tiết với việc sử dụng hàm đếm, hàm đếm có điều kiện trong Excel...

I. Các hàm đếm ô dữ liệu trong Excel

1. Hàm Count - hàm đếm ô có dữ liệu là số

Ý nghĩa: Hàm Count là hàm đếm cơ bản có nhiệm vụ thực hiện đếm các ô có chứa dữ liệu là số.

Cú pháp:

=Count(value1, value2,…)

Trong đó: value1, value2 là các tham chiếu ô hoặc các mảng của hàm đếm, trong đó có các ô là dữ liệu số muốn đếm.

Chú ý:

Hàm Count là hàm đếm chỉ thực hiện đếm các ô có chứa giá trị số, ngày tháng hay có thể là các chữ thể hiện số. Nếu các ô tương đương trong đó không phải kiểu dữ liệu số hoặc không thể dịch sang dạng dữ liệu số sẽ không được đếm.

2. Hàm Counta - hàm đếm ô có dữ liệu 

Ý nghĩa: Hàm Counta trong Excel sẽ thực hiện đếm các ô đã có dữ liệu.

Cú pháp:

Counta(value1, value2,…)

Trong đó: value1, value2… là các tham chiếu ô hoặc các mảng trong hàm đếm muốn đếm có chứa dữ liệu hay không.
Chú ý:

Hàm Counta trong Excel sẽ thực hiện đếm tất cả các ô được tham chiếu tới có chứa dữ liệu hay không. Hàm đếm này không phân biệt dữ liệu số hay ký tự.

3. Hàm Countif - hàm Excel đếm dữ liệu theo điều kiện

Ý nghĩa: Hàm đếm Countif thực hiện đếm số ô dữ liệu với 1 điều kiện cho trước.

Cú pháp của hàm: =Countif(range, criteria)

Trong đó:

• range: Phạm vi hàm đếm theo mảng dữ liệu muốn đếm.
• criteria: Điều kiện mà chúng ta cần xét cho mảng dữ liệu range để đếm.

Chú ý:

• Hàm Countif là hàm đếm không phân biệt kiểu dữ liệu là số hay ký tự. Chỉ cần hàm này chứa dữ liệu phù hợp với điều kiện cần xét, ô dữ liệu tương ứng đó sẽ được đếm.
• Phạm vi của Hàm đếm này được cho rộng hơn nhiều so với hàm count.
• Trường hợp khi mà hàm này không có ô dữ liệu phù hợp với điều kiện => Hàm đếm trả về giá trị 0.

II. Phương pháp đếm nhiều điều kiện trong Excel sử dụng hàm COUNTIFS

Xét bảng dữ liệu sau:

Bảng dữ liệu cho trước

1. Phân tích yêu cầu

Yêu cầu là: “Sử dụng hàm đếm để đếm số người có giới tính là nam có tuổi từ 20 trở lên, hoặc có số thẻ trong phần dữ liệu lớn hơn hoặc bằng 50“. Đây là yêu cầu rất rõ ràng, nhưng để Excel hay cụ thể là hàm đếm hiểu được yêu cầu này để mặt tính toán được thì chúng ta cần phải phân tích ra từng phần cụ thể hơn:

• Đối tượng có liên quan cần xuất hiện trong hàm đếm: cột Giới tính, cột Tuổi, cột Số thẻ
• Điều kiện cụ thể của hàm đếm: Giới tính = Nam, Tuổi >=20, Số thẻ >=50
• Mối liên quan giữa các điều kiện trong hàm đếm là gì?
• Nếu không có mối liên quan trong hàm đếm thì sẽ đếm riêng lẻ từng điều kiện rồi cộng lại. Tuy nhiên ở đây thì các điều kiện cho trước lại có mối quan hệ với nhau

Giới tính là nam và đồng thời có tuổi từ 20 trở lên: Trong này bao hàm điều kiện của hàm “VÀ” (hàm AND)

Hoặc có số thẻ cho trước lớn hơn hoặc bằng 50: Trong này bao hàm ý nghĩa của điều kiện “HOẶC” (hàm OR)

Cụ thể cần chú ý các điều kiện của hàm đếm như sau:

• Yêu cầu 1: Giới tính là nam và có độ tuổi cho trước từ 20 trở lên
• Yêu cầu 2: Giới tính là nam và có số thẻ trong bảng lớn hơn hoặc bằng 50
• Yêu cầu 1 hoặc yêu cầu 2 trong hàm đếm đều đúng

2. Xây dựng công thức tính của hàm đếm

Xét yêu cầu: gồm 2 điều kiện rõ ràng

Các đối tượng trong hàm đếm - hàm countifs bao gồm:

• Criteria_range1: Vùng chứa điều kiện thứ 1 của hàm đếm, cụ thể là cột Giới tính B2:B13
• Criteria1: điều kiện thứ 1 (nằm trong vùng điều kiện thứ 1) của hàm đếm, cụ thể là phần dữ liệu giới tính Nam
• Criteria_range2: Vùng chứa điều kiện thứ 2 của hàm đếm, cụ thể là cột Tuổi C2:C13 (yêu cầu là các vùng criteria_range tương ứng trong đó phải có độ lớn bằng nhau)
• Criteria2: điều kiện thứ 2 (nằm trong vùng điều kiện thứ 2 của hàm đếm), cụ thể là tuổi >=20

Cách viết hàm Countifs - hàm đếm như sau:

=COUNTIFS(B2:B13,”Nam”,C2:C13,”>=”&20)

Tương tự như vậy với yêu cầu thứ 2 đã xác định thì chúng ta có công thức:

=COUNTIFS(B2:B13,”Nam”,D2:D13,”>=”&50)

Vì 2 điều kiện này thuộc dạng Hoặc (hàm OR): chỉ cần đúng 1 trong 2 trường hợp nếu trên thì đều được tính nên công thức hàm đếm tổng quát là:

=COUNTIFS(B2:B13,”Nam”,C2:C13,”>=”&20)+COUNTIFS(B2:B13,”Nam”,D2:D13,”>=”&50)

Kết quả được trả về như sau:

Kết quả được trả về

3. Đánh giá

Ưu điểm:

• Không cần sử dụng tới công thức mảng
• Phân rõ được tính chất, yêu cầu cụ thể của bài toán
• Dễ thực hiện hàm đếm

Nhược điểm

Cần tới 2 lần sử dụng hàm con trong hàm đếm khiên công thức bị dài

III. Hướng dẫn 2 cách sử dụng hàm đếm để đếm số giá trị không trùng trong 1 danh sách trên Excel

1. Cách thứ nhất: Thêm cột phụ và sử dụng hàm COUNTIF 

Đây là cách làm cơ bản của hàm đếm và cũng dễ thực hiện, dễ hiểu. Nhược điểm duy nhất ở đây của cách này là chúng ta phải làm qua nhiều bước và vô hình làm tăng số công thức phải tính lên nhiều.

Ví dụ với dữ liệu trong cột Ngày, chúng ta sẽ tạo 1 cột phụ là cột F và tiếp tục sử dụng hàm đếm COUNTIF tại đó.

Để thực hiện phân biệt giá trị tại dòng đó xuất hiện lần thứ mấy thì chúng ta sử dụng hàm đếm COUNTIF như sau:

Cách thứ nhất: Thêm cột phụ và sử dụng hàm COUNTIF

• Thứ 1: Thực hiện đếm số lần xuất hiện dữ liệu tại mỗi dòng trong vùng tính từ dòng đầu tiên tới dòng mà chúng ta đang đếm. Điều này rất quan trọng bởi trong quá trình tính theo cách này thì giá trị của hàm COUNTIF đếm được sẽ lần lượt là 1, 2 , 3… và sẽ tăng dần theo số lần xuất hiện của vùng điều kiện (criteria) đang xét. Khác với việc chọn cố định Range là cả vùng dữ liệu từ C2:C11, ở đây chúng ta chỉ xét từ C2 tăng dần theo các dòng. Giá trị điểm đầu C2 trong vùng Range trong trang tính được cố định.
• Thứ 2: Sau khi dùng hàm đếm có điều kiện COUNTIF, chúng ta sẽ ra được kết quả trả về tại cột F từ F2:F11. Mỗi 1 giá trị số 1 tương ứng thể hiện là ngày tại cột C xuất hiện lần thứ 1, ý nghĩa của nó là chưa bị trùng. Các giá trị lớn hơn 1 là đã bị trùng. Khi đó kết quả của hàm đếm giá trị không trùng chỉ cần dùng hàm đếm có điều kiện COUNTIF trong cột F với điều kiện cụ thể là số 1

Kết quả hàm đếm thu được là 9

Như vậy khi chúng ta sử dụng hàm đếm có điều kiện COUNTIF trong Excel trong cột phụ thì quá trình giải quyết yêu cầu trên đã trở nên khá dễ dàng.

Nhược điểm

Cách này tuy đơn giản nhưng lại có nhược điểm lớn nhất là tính thủ công. Bạn phải thao tác chọn 1 cột phụ. Dùng công thức tại vị trí của các cột phụ đó. Sau đó là dùng công thức tính kết quả. Khá nhiều bước phát sinh và hơn nữa với bảng dữ liệu lớn thì việc này sẽ trực tiếp khiến tăng thêm 1 lượng công thức rất lớn, với mỗi dòng dữ liệu trong Excel là 1 công thức.

2. Cách thứ hai: Công thức mảng SUMPRODUCT trong Excel kết hợp với FREQUENCY

Hẳn bạn thấy FREQUENCY trong Excel là một hàm hơi lạ phải không. Đây là một hàm có nhiệm vụ xử lý dữ liệu dạng mảng, dùng để đếm số lần xuất hiện của các giá trị trong 1 phạm vi cho trước nào đó. Trong yêu cầu của bài toán cụ thể này, nếu không sử dụng cột phụ thì chúng ta phải sử dụng thêm các công thức xử lý dữ liệu theo mảng để hỗ trợ cho việc tính của hàm đếm.

Cấu trúc hàm FREQUENCY như sau:

=FREQUENCY(data_array,bins_array)

Nguyên tắc của hàm Frequency là hàm đếm xem các giá trị trong tham số bins_array được xuất hiện lặp lại bao nhiêu lần trong data_array. Về bản chất thì đây giống như việc dùng hàm đếm có điều kiện COUNTIF để đếm từng phần tử bins_array (criteria) trong vùng dữ liệu data_array (range) vậy.

Ứng dụng nguyên tắc trên, chúng ta có thể xét 2 nội dung sau:

Số thứ tự dòng tương ứng của mỗi dòng dữ liệu trong cột cần đếm giá trị không trùng sẽ lần lượt là từ 1 đến 10 (ứng với vị trí các dòng từ C2:C11). Coi như vùng dữ liệu này là bins_array. Giả sử chúng ta xét A2:A11 chính là nội dung trong hàm đếm chúng ta cần.

Đếm xem mỗi giá trị cụ thể đã xuất hiện trong cột ngày là giá trị thứ mấy với hàm MATCH. Vùng chọn đặc biệt này được coi là data_array. Hàm MATCH được viết như sau:

Cách thứ hai

Khi thực hiện kiểm tra kết quả hàm MATCH trong Excel bằng cách bôi đen công thức trên thanh Formula Bar và tiếp tục nhấn phím F9 ta có:

Kiểm tra kết quả hàm MATCH

Các giá trị mà hàm đếm lần lượt trả về là 1 mảng gồm các phần tử 1, 2, 3, 4, 5, 6, 7, 8, 10 trong đó chú ý phần tử 8 xuất hiện 2 lần do ngày 27/6/2018 đã được xuất hiện 2 lần trong mảng. Điều này tương ứng với cách biểu diễn hàm MATCH trong Excel như sau:

Các giá trị mà hàm đếm lần lượt trả về

Khi kết hợp 2 thành phần trên vào hàm Frequency trong Excel ta sẽ được:

Khi kết hợp 2 thành phần trên vào hàm Frequency trong Excel

Việc còn lại là thực hiện đếm số giá trị lớn hơn 0 trong mảng tạo ra bởi hàm đếm Frequency trong Microsoft Excel là ra kết quả.

Tuy nhiên vì công thức cụ thể của chúng ta chỉ đặt trong 1 ô nên hàm đếm sẽ trả về giá trị lớn hơn 0, phải dùng đến hàm SUMPRODUCT. Cách làm cụ thể như sau:

• Xét các giá trị được tạo ra bởi mảng trong hàm Frequency đã cho và so sánh với 0, lấy các giá trị tương ứng >0
• Việc so sánh giá trị của hàm Frequency>0 sẽ tạo ra các mảng giá trị là kết quả True/False. Khi đó tiếp tục chuyển các giá trị True/False về dạng 1/0 bằng cách đặt thêm 2 dấu trừ ở trước biểu thức so sánh đó.

Kết quả hàm đếm

Kết quả của hàm này cũng ra là 9

Như vậy chúng ta chỉ cần đặt 1 biểu thức tại 1 ô C13, cũng ra được kết quả tương ứng giống như cách thứ 1.

Ưu điểm:

• Khắc phục được nhược điểm hàm đếm của cách 1
• Giúp chúng ta nâng cao hơn khả năng sử dụng linh hoạt Excel và các tư duy về việc sử dụng hàm, xử lý dữ liệu, loại dữ liệu trên Microsoft Excel.
• Ứng dụng khá mạnh trong việc trích xuất dữ liệu không trùng từ bảng dữ liệu bằng cách sử dụng hàm

Nhược điểm:

Công thức của hàm là khá phức tạp nên cần thực sự hiểu kỹ về bản chất dữ liệu và kết hợp với cách sử dụng công thức mảng

IV. Đọc thêm: Chức năng và cú pháp hàm  FREQUENCY trong Excel 

Hàm đếm FREQUENCY trong Microsoft Excel được dùng để trả về kết quả là một mảng số học, thực hiện đếm số lần xuất hiện của các giá trị trong một phạm vi giá trị cụ thể nào đó.

Cú pháp của hàm đếm này là: 

=FREQUENCY(DATA_ARRAY,BINS_ARRAY)

Trong đó:

• Data_array: Mảng dữ liệu đã được cho trước.           
• Bins_array: Mảng chứa các khoảng giá trị mà hàm đếm cần tính tần suất.

Do kết quả trả về của hàm FREQUENCY trong Microsoft Excel là một mảng số học, cho nên trước khi dùng hàm ta phải thao tác chọn mảng sẽ hiển thị kết quả và để kết quả trả về có thể hiển thị trên các ô đó thì sau khi nhập cú pháp chính xác xong ta phải nhấn tổ hợp CTRL+SHIFT+ENTER.

VÍ DỤ

Trong bảng số liệu dưới đây, sử dụng hàm đếm thực hiện tìm tần số xuất hiện của dữ liệu ở mảng Điều kiện được cho trong mảng ĐTB.

Bảng số liệu

Dùng công thức: =FREQUENCY(C4:C8;D4:D8), thu được kết quả của hàm đếm là:

Kết quả

V. Kết luận 

Như vậy, qua phần nội dung trên đây, 123job.vn đã hướng dẫn các bạn cách sử dụng họ hàm COUNT - các hàm đếm trong Excel để áp dụng vào việc tính toán và thống kê chính xác dữ liệu. Trong một số trường hợp, hàm COUNT và các hàm liên quan này cũng nên được kết hợp với các hàm khác trong excel có chức năng đặc biệt để quá trình sử dụng Microsoft Excel trở nên nhẹ nhàng và hiệu quả hơn.