Đếm xem bảng tính có bao nhiêu dòng dữ liệu nghe thì đơn giản, COUNT và COUNTA là hai anh em ‘guột’ cùng đi đếm nhưng tiêu chuẩn lại khác nhau, chỉ cần chọn nhầm giữa COUNT và COUNTA là con số báo cáo lệch ngay. COUNT chỉ đếm ô chứa số, còn COUNTA (A là All) đếm mọi ô có nội dung. Bài này đi qua 11 tình huống thực tế về cách dùng hàm COUNT và COUNTA trong Excel, dùng dữ liệu đơn hàng đèn led của Đèn Led Giá Sỉ, để bạn nắm chắc khi nào dùng hàm nào, bẫy nằm ở đâu, và cách phối hợp chúng để kiểm tra chất lượng dữ liệu.
Bảng dữ liệu mẫu áp dụng các công thức trong ví dụ từng trường hợp bên dưới
Mục lục.
COUNT cơ bản: đếm ô chứa số
Anh kế toán khó tính chỉ cho phép con số đi qua cổng – chữ nghĩa xin mời ở lại bên ngoài
CÚ PHÁP
=COUNT(value1, [value2], …)
CÔNG DỤNG
COUNT đếm xem trong vùng bạn chọn có bao nhiêu ô chứa GIÁ TRỊ SỐ (số nguyên, số thập phân, phần trăm, tiền tệ, ngày tháng, giờ giấc). Ô trống, ô chứa chữ, ô báo lỗi đều bị cho ra rìa. Đây là hàm đầu tiên nên dùng khi bạn muốn biết một cột dữ liệu có bao nhiêu dòng đã được nhập số liệu thật sự, chẳng hạn bao nhiêu đơn hàng đã có số lượng.
VÍ DỤ
=COUNT(C2:C11)
KẾT QUẢ
Cột C ghi số lượng đèn LED của 10 đơn hàng, trong đó có 1 ô bỏ trống và 1 ô gõ số dạng chữ (“30” lệch trái). COUNT chỉ trả về 8, tức 8 ô chứa số thật. Con số này cho biết có 8 đơn đã chốt được số lượng chính xác, còn 2 đơn cần gọi lại cho khách để xác nhận hoặc nhập bổ sung vào bảng.
Hàm Count cơ bản
COUNT nhiều vùng cùng lúc
Một lần gọi, đếm cả mấy khu vực – khỏi phải cộng tay từng bảng
CÚ PHÁP
=COUNT(vung_1, vung_2, vung_3, …)
CÔNG DỤNG
COUNT nhận tối đa 255 đối số, mỗi đối số có thể là một ô, một vùng hoặc một con số gõ thẳng vào công thức. Nhờ vậy bạn đếm được số liệu rải rác ở nhiều cột, nhiều khu vực không liền kề mà vẫn chỉ cần một công thức duy nhất. Nếu vùng nằm trên nhiều sheet, mỗi vùng vẫn là một đối số riêng.
VÍ DỤ
=COUNT(C2:C11, E2:E11, 100)
KẾT QUẢ
Công thức gom số liệu ở cột C (8 ô số), cột E toàn chữ (0 ô số) và thêm hằng số 100 gõ trực tiếp (1 giá trị số). Kết quả 8 + 0 + 1 = 9. Lưu ý: số gõ thẳng vào đối số luôn được tính, còn ô tham chiếu thì phải thật sự chứa số.
Hàm count nhiều vùng
COUNT với ngày tháng: ngày cũng là số
Bí mật của Excel: ngày tháng thực chất chỉ là những con số mặc áo đẹp
CÚ PHÁP
=COUNT(vung_ngay_thang)
CÔNG DỤNG
Trong Excel, mỗi ngày được lưu dưới dạng một số thứ tự (số ngày tính từ 01/01/1900), nên COUNT đếm được ô ngày tháng hợp lệ. Mẹo hay: nếu một ô trông giống ngày nhưng bị COUNT bỏ qua, gần như chắc chắn ô đó đang là chữ, và bạn cần chuyển lại đúng định dạng ngày.
VÍ DỤ
=COUNT(D2:D11)
KẾT QUẢ
Cột D là ngày giao hàng dự kiến của 10 đơn, có 2 đơn chưa hẹn ngày. COUNT trả về 8, tức 8 đơn đã có lịch giao. Muốn biết đơn nào chưa có lịch, lấy tổng số dòng trừ đi kết quả này, hoặc bật bộ lọc cột ngày để chọn nhóm ô trống rồi nhắn cho bộ phận giao vận xếp lịch.
Hàm Count đếm ngày tháng
COUNT sẽ lờ đi những gì?
Danh sách đen của COUNT: chữ, ô trống, ô lỗi và TRUE/FALSE trong vùng đều bị gạt đi
CÚ PHÁP
=COUNT(vung_du_lieu) [bo qua: chu, o trong, loi, TRUE/FALSE trong vung]
CÔNG DỤNG
Nhớ kỹ quy tắc này để khỏi bất ngờ: COUNT bỏ qua văn bản (kể cả số bị lưu thành chữ), ô trống, giá trị lỗi như #N/A hay #DIV/0! và giá trị logic TRUE/FALSE nằm TRONG vùng tham chiếu. Ngoại lệ duy nhất là khi bạn gõ trực tiếp TRUE, FALSE hoặc số dạng chữ như “5” vào ngay trong danh sách đối số, lúc đó Excel vẫn tính chúng.
VÍ DỤ
=COUNT(F2:F11) so voi =COUNT(TRUE, “5”, 7)
KẾT QUẢ
Cột F là trạng thái thanh toán toàn TRUE/FALSE nên COUNT(F2:F11) trả về 0. Trong khi đó COUNT(TRUE, “5”, 7) trả về 3 vì cả ba đều gõ thẳng vào đối số. Hai kết quả trái ngược này là chỗ khiến nhiều người rối nhất.
Loại trừ của hàm Count
COUNTA cơ bản: đếm mọi ô có nội dung
Ông bảo vệ dễ tính: miễn là có gì đó trong ô, cứ vào hết, không hỏi giấy tờ
CÚ PHÁP
=COUNTA(value1, [value2], …)
CÔNG DỤNG
COUNTA (chữ A là viết tắt của All, nghĩa là đếm tất cả) đếm số ô KHÔNG TRỐNG, bất kể bên trong là số, chữ, ngày, TRUE/FALSE hay thậm chí mã lỗi. Đây là hàm lý tưởng để đếm số dòng đã nhập dữ liệu của một cột văn bản như tên khách hàng, mã đơn hàng, ghi chú.
VÍ DỤ
=COUNTA(B2:B11)
KẾT QUẢ
Cột B là tên khách hàng, cả 10 ô đều có chữ nên COUNTA trả về 10. Nếu bạn dùng COUNT cho cột này sẽ nhận 0, vì không có ô nào chứa số. Đó là lý do cột văn bản luôn là sân chơi của COUNTA.
Hàm CountA cơ bản
COUNTA và cái bẫy chuỗi rỗng
Ô trông trống trơn nhưng COUNTA vẫn bắt được – vì bên trong có một chuỗi rỗng vô hình
CÚ PHÁP
=COUNTA(vung_du_lieu) [dem ca o chua cong thuc tra ve “”]
CÔNG DỤNG
COUNTA coi ô chứa công thức trả về chuỗi rỗng (“”) hoặc ô chỉ có một dấu cách là ô CÓ nội dung, dù mắt thường thấy trống. Đây là nguyên nhân kinh điển khiến số đếm cao hơn mong đợi, nhất là khi dữ liệu được tạo bằng các công thức IF trả về “”. Muốn đếm đúng ô thật sự trống, hãy dùng COUNTBLANK, hoặc SUMPRODUCT kết hợp LEN lớn hơn 0 để chỉ đếm ô có chữ thật sự.
VÍ DỤ
=COUNTA(E2:E11) so voi =SUMPRODUCT(–(LEN(E2:E11)>0))
KẾT QUẢ
Cột E (ghi chú) có 5 ô chứa chữ, 4 ô trống thật và 1 ô E8 chứa công thức trả về “”. COUNTA báo 6 vì đếm luôn ô chuỗi rỗng. Công thức thứ hai chỉ đếm ô có độ dài lớn hơn 0 nên ra đúng 5 ghi chú thật sự.
bẫy chuỗi rỗng hàm CountA so với hàm Sumproduct
COUNT chia COUNTA: tính tỷ lệ hoàn thành
Cặp đôi hoàn hảo: một người đếm phần đã xong, một người đếm tổng số việc
CÚ PHÁP
=COUNT(vung_so) / COUNTA(vung_ma)
CÔNG DỤNG
Kết hợp COUNT và COUNTA cho bạn tỷ lệ nhập liệu hoặc tỷ lệ hoàn thành rất nhanh. Dùng COUNTA trên một cột luôn có dữ liệu (mã đơn) để lấy tổng số dòng, dùng COUNT trên cột số liệu cần theo dõi để lấy số dòng đã điền. Chia hai con số rồi định dạng phần trăm là xong thanh tiến độ cho cả bảng.
VÍ DỤ
=COUNT(C2:C11) / COUNTA(A2:A11)
KẾT QUẢ
Tử số là 8 đơn đã có số lượng, mẫu số là 10 mã đơn hàng, kết quả 0,8 và hiển thị 80% khi định dạng phần trăm. Cả đội nhìn vào là biết còn 20% đơn hàng chưa được cập nhật số lượng.
Tính tỷ lệ hoàn thành bằng Count và CountA
COUNTA đếm số dòng dữ liệu tự động
Bảng dài ra thì công thức tự dài theo – không cần ngồi sửa vùng chọn mỗi sáng
CÚ PHÁP
=COUNTA(A:A) – 1
CÔNG DỤNG
Khi bảng liên tục có thêm dòng mới, bạn có thể đếm số bản ghi bằng COUNTA trên toàn cột (A:A) rồi trừ đi 1 dòng tiêu đề. Cách này không cần sửa công thức khi thêm dữ liệu. Điều kiện là cột đó phải liền mạch, không để ô trống giữa chừng và không có ghi chú lạc ở cuối cột, vì COUNTA sẽ đếm cả những thứ đó.
VÍ DỤ
=COUNTA(A:A) – 1
KẾT QUẢ
Cột A chứa tiêu đề cộng 10 mã đơn hàng nên COUNTA(A:A) trả về 11, trừ 1 còn 10 đơn hàng. Hôm sau nhập thêm 5 đơn, ô kết quả tự nhảy thành 15 mà bạn không phải đụng vào công thức.
Đếm dòng tự động bằng CountA
COUNTA, COUNTBLANK và ROWS: bộ ba kiểm tra dữ liệu thiếu
COUNTBLANK đếm ô trống (cả ô chứa chuỗi rỗng), ROWS cho biết vùng có bao nhiêu dòng. Phối hợp với COUNTA, bạn dựng nhanh một bảng kiểm tra chất lượng dữ liệu: bao nhiêu ô đã có, bao nhiêu ô còn thiếu, và có trùng khớp với nhau hay không. Nếu ROWS trừ COUNTA khác COUNTBLANK, chắc chắn đang có chuỗi rỗng hoặc ô chứa khoảng trắng ẩn.
VÍ DỤ
=ROWS(E2:E11) – COUNTA(E2:E11) va =COUNTBLANK(E2:E11)
KẾT QUẢ
Công thức đầu cho 10 – 6 = 4 (ô trống thật), công thức sau cho 5 vì COUNTBLANK tính thêm ô chuỗi rỗng. Hai kết quả lệch nhau 1 đơn vị chính là dấu hiệu có ô chứa công thức trả về “” trong vùng.
CountA,CountBlank, Rows
COUNT và COUNTA trong Google Sheets có khác trong Excel?
Sang nhà hàng xóm vẫn gặp người quen – chỉ khác cách xưng hô
CÚ PHÁP
=COUNTA(value1; [value2]; …)
CÔNG DỤNG
Trong Google Sheets, COUNT và COUNTA hoạt động gần như y hệt Excel: COUNT chỉ đếm số và ngày tháng, COUNTA đếm mọi ô có nội dung. Điểm khác biệt đáng nhớ nhất nằm ở dấu ngăn cách đối số, Google Sheets dùng dấu chấm phẩy khi ngôn ngữ vùng của bảng tính là Việt Nam. Ngoài ra, Sheets còn tính ô chứa chuỗi rỗng là ô có nội dung, giống như Excel, nên cái bẫy ở mục trên vẫn áp dụng nguyên vẹn.
VÍ DỤ
=COUNTA(A2:A11; B2:B11)
KẾT QUẢ
Công thức gom hai cột mã đơn hàng và tên khách, mỗi cột có 10 ô đều có chữ nên kết quả là 20. Nếu bạn gõ dấu phẩy mà Sheets báo lỗi cú pháp, hãy thử đổi sang dấu chấm phẩy rồi bấm Enter lại.
Lỗi thường gặp khi dùng COUNT và COUNTA
Bốn cú vấp khiến người mới tưởng Excel đếm sai – thật ra Excel đếm đúng, chỉ là mình nhập sai
CÚ PHÁP
So “gia tri” dung kieu du lieu: COUNT = so, COUNTA = bat ky noi dung
CÔNG DỤNG
Thứ nhất: dùng COUNT cho cột chữ nên ra 0, hãy đổi sang COUNTA. Thứ hai: số lưu dạng chữ (có dấu nháy đơn đầu ô hoặc lệch trái) bị COUNT bỏ qua, hãy chuyển về số bằng cách nhân với 1 hoặc dùng Text to Columns. Thứ ba: COUNTA bị phồng lên vì ô chứa dấu cách hay chuỗi rỗng. Thứ tư: quên rằng TRUE/FALSE trong vùng không được COUNT tính. Khi cần đếm theo điều kiện, hãy dùng COUNTIF hoặc COUNTIFS thay vì ép COUNT làm việc ngoài khả năng.
VÍ DỤ
=SUMPRODUCT(–ISNUMBER(C2:C11)) so voi =COUNT(C2:C11)
KẾT QUẢ
Với dữ liệu mẫu, cả hai đều trả về 8. Dùng ISNUMBER bên trong SUMPRODUCT là cách kiểm chứng chéo để xác nhận COUNT đang đếm đúng, đồng thời giúp bạn nhận ra ô nào là số thật, ô nào chỉ là số mặc áo chữ.
Lời kết cho cách dùng hàm COUNT và COUNTA trong Excel
Tóm lại: gặp cột số thì nghĩ tới COUNT, gặp cột chữ hoặc cần biết ô nào đã được nhập thì nghĩ tới COUNTA, và luôn cảnh giác với ô chứa chuỗi rỗng hay số lưu dạng chữ. Khi cần đếm có điều kiện, hãy xem thêm cách dùng hàm COUNTIF và COUNTIFS. Đừng quên tải file Excel mẫu đi kèm để tự tay thử từng công thức trên bộ dữ liệu có sẵn các ô trống, ô chữ và ô chuỗi rỗng như trong bài.