COUNTIF
Hàm COUNTIF trả về số lượng ô trong tập hợp thỏa mãn điều kiện cho trước.
COUNTIF(dãy kiểm tra; điều kiện)
dãy kiểm tra: Tập hợp chứa các giá trị sẽ được kiểm tra. dãy kiểm tra có thể chứa bất kỳ giá trị nào.
điều kiện: Một biểu thức so sánh hoặc kiểm tra các giá trị và kết quả trong giá trị lô-gic TRUE hoặc FALSE. điều kiện có thể bao gồm các toán tử so sánh, hằng số, toán tử ghép nối và, tham chiếu và ký tự đại diện. Bạn có thể sử dụng ký tự đại diện để khớp với bất kỳ ký tự duy nhất nào hoặc nhiều ký tự trong biểu thức. Bạn có thể sử dụng dấu ? (dấu hỏi chấm) để biểu thị một ký tự, dấu * (dấu hoa thị) để biểu thị nhiều ký tự, và dấu ~ (dấu ngã) để biểu thị rằng ký tự tiếp sau đó cần phải khớp, chứ không phải được dùng làm ký tự đại diện. điều kiện cũng có thể chứa một hàm REGEX thay vì ký tự đại diện.
Ghi chú
Mỗi giá trị dãy kiểm tra được so sánh với điều kiện. Nếu giá trị đáp ứng kiểm tra điều kiện, nó được bao gồm trong tổng.
Ví dụ |
|---|
Thông tin trong bảng sau không có ý nghĩa, nhưng hữu ích cho việc minh họa loại đối số COUNTIF bao gồm trong kết quả. Cho bảng sau: |
A | B | C | D | |
|---|---|---|---|---|
1 | 100 | 200 | 300 | 400 |
2 | lorem | ipsum | dolor | sit |
3 | 100 | 200 | 300 | sit |
4 | TRUE | TRUE | FALSE | FALSE |
5 | 200 | 400 |
=COUNTIF(A1:D1; “>0”) trả về 4, vì tất cả các ô trong tập hợp có giá trị lớn hơn 0. =COUNTIF(A3:D3; “>=100”) trả về 3, vì tất cả ba số đều lớn hơn hoặc bằng 100 và giá trị văn bản được bỏ qua khi so sánh. =COUNTIF(A1:D5; “=ipsum”) trả về 1, vì chuỗi kiểm tra “ipsum” xuất hiện một lần trong tập hợp được vùng này tham chiếu. =COUNTIF(A1:D5; “=*t”) trả về 2, vì chuỗi kết thúc bằng chữ cái “t” xuất hiện hai lần trong tập hợp được vùng này tham chiếu. |
Ví dụ – Kết quả khảo sát |
|---|
Ví dụ này tập hợp tất cả các hình minh họa được sử dụng trong toàn bộ các hàm thống kê cùng nhau. Việc này dựa trên khảo sát giả định. Đây là một khảo sát ngắn (chỉ có năm câu hỏi) và có số lượng người trả lời hạn chế (10). Mỗi câu hỏi có thể được trả lời theo thang điểm từ 1 đến 5 (có thể phạm vi là từ “không bao giờ” đến “luôn luôn”), hoặc không trả lời. Mỗi khảo sát được gán một số (ID) trước khi gửi thư. Bảng sau hiển thị kết quả. Các câu hỏi được trả lời ngoài phạm vi (sai) hoặc không được trả lời được biểu thị bằng ô trống trong bảng. |
A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
1 | ID | Q1 | Q2 | Q3 | Q4 | Q5 |
2 | 101 | 5 | 4 | 4 | 3 | 4 |
3 | 105 | 3 | 2 | 2 | 3 | 3 |
4 | 102 | 4 | 4 | 4 | 4 | |
5 | 104 | 3 | 4 | 2 | 4 | 3 |
6 | 107 | 4 | 3 | 3 | ||
7 | 106 | 4 | 3 | 3 | 4 | |
8 | 109 | 3 | 4 | 1 | 3 | 4 |
9 | 111 | 5 | 2 | 2 | 5 | 3 |
10 | 121 | 4 | 2 | 3 | 3 | 4 |
11 | 115 | 3 | 3 | 3 | 3 |
Để minh họa một số hàm, giả sử rằng số hiệu khảo sát bao gồm tiền tố dạng chữ cái và thang điểm là A–E, thay vì 1–5. Khi đó, bảng sẽ trông như sau: |
A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
1 | ID | Q1 | Q2 | Q3 | Q4 | Q5 |
2 | 101 | E | D | D | C | D |
3 | 105 | C | B | B | C | C |
4 | 102 | D | D | D | D | |
5 | 104 | C | D | B | D | C |
6 | 107 | D | C | C | ||
7 | 106 | D | C | C | D | |
8 | 109 | C | D | A | C | D |
9 | 111 | E | B | B | E | C |
10 | 121 | D | B | C | D | |
11 | 115 | C | C | C | C | C |
Sử dụng bảng dữ liệu này và một số hàm thống kê có sẵn, bạn có thể thu thập thông tin về kết quả khảo sát. Lưu ý rằng mẫu này có kích cỡ nhỏ có chủ định nên kết quả có vẻ như hiển nhiên. Tuy nhiên, nếu bạn có 50, 100 người trả lời trở lên và có thể là nhiều câu hỏi hơn, kết quả sẽ không hiển nhiên. |
Hàm và đối số | Mô tả kết quả |
|---|---|
=CORREL(B2:B11; C2:C11) | Xác định mối tương quan giữa câu hỏi 1 và câu hỏi 2 bằng phân tích hồi quy tuyến tính. Tương quan là thước đo mức độ thay đổi cùng nhau giữa hai biến (trong trường hợp này là câu trả lời cho câu hỏi khảo sát). Cụ thể, việc này sẽ xem xét câu hỏi: Nếu người trả lời đã trả lời câu hỏi 1 với giá trị cao hơn (hoặc thấp hơn) giá trị trung bình cho câu hỏi 1, người trả lời có trả lời câu hỏi 2 với giá trị cao hơn (hoặc thấp hơn) giá trị trung bình cho câu hỏi 2 không? Trong trường hợp này, các câu trả lời không có sự tương quan cụ thể (-0.1732) |
=COUNT(A2:A11) hoặc =COUNTA(A2:A11) | Xác định tổng số khảo sát được trả lại (10). Lưu ý rằng nếu số hiệu khảo sát không phải là số, thì bạn sẽ cần sử dụng COUNTA thay vì COUNT. |
=COUNT(B2:B11) hoặc =COUNTA(B2:B11) | Xác định tổng số câu trả lời cho câu hỏi đầu tiên (9). Bằng cách mở rộng công thức này qua hàng, bạn có thể xác định tổng số câu trả lời cho từng câu hỏi. Vì tất cả các dữ liệu đều là số, COUNTA trả về cùng kết quả. Tuy nhiên, nếu khảo sát sử dụng A đến E thay vì 1 đến 5, bạn sẽ cần sử dụng COUNTA để đếm các kết quả. |
=COUNTBLANK(B2:B11) | Xác định số lượng ô trống, biểu thị cho câu trả lời không hợp lệ hoặc không có câu trả lời. Nếu bạn mở rộng công thức này qua hàng, bạn sẽ thấy rằng câu hỏi 3 (cột D) có 3 câu trả lời không hợp lệ hoặc không được trả lời. Việc này có thể khiến bạn xem xét câu hỏi này trên khảo sát để biết liệu câu hỏi có mang tính tranh cãi hoặc có từ ngữ không phù hợp không, vì không có câu trả lời nào có nhiều hơn 1 câu trả lời sai hoặc không được trả lời. |
=COUNTIF(B2:B11; “=5”) | Xác định số lượng người trả lời cho điểm 5 đối với một câu hỏi cụ thể (trong trường hợp này là câu hỏi 1). Nếu bạn mở rộng công thức này qua hàng, bạn sẽ biết rằng chỉ các câu hỏi 1 và 4 có bất kỳ người trả lời nào cho điểm 5 đối với câu hỏi. Nếu khảo sát sử dụng A đến E cho phạm vi, bạn phải sử dụng =COUNTIF(B2:B11; “=E”). |
=COVAR(B2:B11; C2:C11) | Xác định hiệp biến của câu hỏi 1 và câu hỏi 2. Hiệp biến là thước đo mức độ thay đổi cùng nhau giữa hai biến (trong trường hợp này là câu trả lời cho câu hỏi khảo sát). Cụ thể, việc này sẽ xem xét câu hỏi: Nếu người trả lời đã trả lời câu hỏi 1 với giá trị cao hơn (hoặc thấp hơn) giá trị trung bình cho câu hỏi 1, người trả lời có trả lời câu hỏi 2 với giá trị cao hơn (hoặc thấp hơn) giá trị trung bình cho câu hỏi 2 không? Lưu ý: COVAR sẽ không hoạt động với bảng sử dụng thước đo A–E, vì hàm này yêu cầu các đối số dạng số. |
=STDEV(B2:B11) hoặc =STDEVP(B2:B11) | Xác định độ lệch chuẩn – một thước đo độ phân tán – của các câu trả lời cho câu hỏi 1. Nếu bạn mở rộng công thức này qua hàng, bạn sẽ thấy rằng các câu trả lời cho câu hỏi 3 có độ lệch chuẩn cao nhất. Nếu các kết quả biểu thị câu trả lời từ toàn bộ tập hợp được nghiên cứu, thay vì một mẫu, STDEVP sẽ được sử dụng thay vì STDEV. Lưu ý rằng STDEV là căn bậc hai của VAR. |
=VAR(B2:B11) hoặc =VARP(B2:B11) | Xác định phương sai – một thước đo độ phân tán – của các câu trả lời cho câu hỏi 1. Nếu bạn mở rộng công thức này qua hàng, bạn sẽ thấy rằng các câu trả lời cho câu hỏi 5 có phương sai thấp nhất. Nếu các kết quả biểu thị câu trả lời từ toàn bộ tập hợp được nghiên cứu, thay vì một mẫu, VARP sẽ được sử dụng thay vì VAR. Lưu ý rằng VAR là bình phương của STDEV. |
Ví dụ sử dụng REGEX |
|---|
Cho bảng sau: |
A | B | |
|---|---|---|
1 | 45 | john@appleseed.com |
2 | 41 | Aaron |
3 | 29 | janedoe@appleseed.com |
4 | 64 | jake@appleseed.com |
5 | 12 | Sarah |
=COUNTIF(B1:B5; REGEX(“([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})”)) trả về số lượng ô trong B1:B5 chứa địa chỉ email. |