Trong công việc hàng ngày, việc xử lý dữ liệu trong Excel là một nhu cầu phổ biến đối với nhiều người dùng, từ nhân viên kế toán, nhà quản trị dự án, cho tới các nhà phân tích dữ liệu. Một trong những thao tác cơ bản nhưng không kém phần quan trọng là đếm số ô chứa văn bản (text) trong một khoảng dữ liệu. Việc này giúp bạn nhanh chóng nắm bắt được lượng thông tin chữ, kiểm tra tính đầy đủ của dữ liệu, hoặc thực hiện các phân tích thống kê nhanh chóng. Bài viết dưới đây sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ các công thức cơ bản đến các kỹ thuật nâng cao, giúp bạn trở thành “bậc thầy” trong việc đếm ô chứa văn bản trong Excel.
Có thể bạn quan tâm: Excel Concat String With Separator: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Mục Lục
- Giới thiệu nhanh về nhu cầu đếm ô chứa văn bản
- Công thức cơ bản:
COUNTIFvàCOUNTIFS - Sử dụng hàm
LENđể lọc ô trống và ô chỉ chứa khoảng trắng - Kỹ thuật với hàm
ISTEXTvàSUMPRODUCT - Đếm ô chứa một từ khóa cụ thể hoặc một phần chuỗi
- Kết hợp hàm mảng (Array Formulas) trong Excel 365/2026
- Sử dụng Power Query để đếm ô chứa văn bản trong dữ liệu lớn
- Thực hành: 10 ví dụ thực tế trong các tình huống công việc
- Lưu ý thường gặp và cách khắc phục lỗi
- Kết luận và tài nguyên tham khảo
Có thể bạn quan tâm: Excel Cho Người Đi Làm: Bí Quyết Thành Thạo, Tăng Năng Suất Và Thăng Tiến Nhanh Chóng
1. Giới thiệu nhanh về nhu cầu đếm ô chứa văn bản
1.1 Tại sao cần đếm ô chứa văn bản?
- Kiểm tra tính đầy đủ của dữ liệu: Khi thu thập dữ liệu từ các nguồn khác nhau (form nhập liệu, hệ thống ERP…), việc có bao nhiêu ô đã được điền chữ giúp bạn phát hiện các thiếu sót.
- Thống kê nhanh: Trong báo cáo, bạn có thể muốn biết số lượng sản phẩm có mô tả, số khách hàng đã cung cấp địa chỉ email, v.v.
- Phân loại dữ liệu: Đôi khi bạn cần tách ra các ô có chữ và các ô có số để thực hiện các phép tính khác nhau.
- Tự động hoá quy trình: Kết hợp với các hàm điều kiện (
IF,FILTER,SORT) để tạo ra các bảng tổng hợp tự động.
1.2 Các trường hợp thực tế
| Trường hợp | Mô tả | Lý do cần đếm |
|---|---|---|
| Danh sách khách hàng | Cột “Ghi chú” có thông tin bổ sung | Xác định bao nhiêu khách hàng đã để lại ghi chú |
| Bảng sản phẩm | Cột “Mô tả sản phẩm” | Kiểm tra độ đầy đủ của mô tả trước khi xuất kho |
| Khảo sát | Cột “Ý kiến” | Đếm số câu trả lời có nội dung để phân tích qualititative |
| Báo cáo tài chính | Cột “Ghi chú tài chính” | Xác định bao nhiêu mục cần giải thích thêm |
Có thể bạn quan tâm: Học Excel Cho Dân Văn Phòng: Từ Cơ Bản Đến Thành Thạo Trong 30 Ngày
2. Công thức cơ bản: COUNTIF và COUNTIFS
2.1 COUNTIF – Đếm dựa trên một tiêu chí
Cú pháp:
COUNTIF(range, criteria)
- range: Vùng ô cần kiểm tra.
- criteria: Tiêu chí (điều kiện) để đếm. Đối với văn bản, thường sử dụng ký tự đại diện
.
Ví dụ 1: Đếm số ô có bất kỳ nội dung chữ nào trong cột A (A2:A100).
=COUNTIF(A2:A100,"")
Giải thích:
– Dấu đại diện cho “bất kỳ chuỗi ký tự nào”, vì vậy mọi ô không rỗng và có ít nhất một ký tự sẽ được tính.
Lưu ý: Công thức trên sẽ đếm cả ô chứa số nếu chúng được nhập dưới dạng văn bản (ví dụ: '123). Để chắc chắn chỉ đếm ô thực sự chứa văn bản, cần kết hợp các hàm khác (xem phần 4).
2.2 COUNTIFS – Đếm với nhiều tiêu chí
Khi bạn muốn đếm các ô thỏa mãn đồng thời nhiều điều kiện, COUNTIFS rất hữu ích.
Cú pháp:
COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)
Ví dụ 2: Đếm ô trong cột B có văn bản và đồng thời trong cột C có giá trị lớn hơn 100.
=COUNTIFS(B2:B200,"",C2:C200,">100")
2.3 Khi nào COUNTIF không đủ?
- Ô chứa chỉ khoảng trắng (
" "):COUNTIFsẽ tính như có nội dung, nhưng thực tế không có ký tự có nghĩa. - Ô chứa công thức trả về chuỗi rỗng (
""):COUNTIFsẽ không tính, nhưng có thể gây nhầm lẫn nếu bạn muốn loại bỏ hoàn toàn. - Ô chứa dữ liệu dạng số dạng văn bản: Như đã đề cập,
COUNTIFkhông phân biệt.
Do vậy, trong các trường hợp phức tạp, chúng ta sẽ chuyển sang các hàm mạnh hơn như ISTEXT, SUMPRODUCT, hoặc sử dụng hàm mảng.
3. Sử dụng hàm LEN để lọc ô trống và ô chỉ chứa khoảng trắng
3.1 Hàm LEN – Độ dài chuỗi
Cú pháp:
LEN(text)
- Trả về số ký tự trong chuỗi, bao gồm cả khoảng trắng.
Ví dụ 3: Kiểm tra độ dài của ô A2.
=LEN(A2)
Nếu A2 chứa " " (ba khoảng trắng), hàm trả về 3.
3.2 Kết hợp LEN và COUNTIF để loại bỏ ô chỉ có khoảng trắng
=COUNTIF(A2:A100,"")-COUNTIF(A2:A100," ")
Tuy nhiên, cách này không thực tế vì không biết trước số lượng khoảng trắng. Thay vào đó, dùng công thức mảng:
=SUMPRODUCT(--(LEN(TRIM(A2:A100))>0))
Giải thích:
– TRIM loại bỏ khoảng trắng đầu và cuối.
– LEN(TRIM(...))>0 trả về TRUE cho ô có ít nhất một ký tự sau khi trim.
– -- chuyển TRUE/FALSE thành 1/0.
– SUMPRODUCT cộng lại tổng số ô thỏa mãn.
3.3 Công thức mảng truyền thống (Ctrl+Shift+Enter)
Nếu bạn dùng phiên bản Excel trước 365, bạn có thể viết:
=SUM(IF(LEN(TRIM(A2:A100))>0,1,0))
Sau khi nhập, nhấn Ctrl+Shift+Enter để công thức trở thành mảng (được bao quanh bởi {}).
4. Kỹ thuật với hàm ISTEXT và SUMPRODUCT
4.1 Hàm ISTEXT
ISTEXT(value)
Trả về TRUE nếu value là kiểu dữ liệu văn bản.
Ví dụ 4: Kiểm tra A2.
=ISTEXT(A2)
4.2 Đếm ô là văn bản thực sự bằng SUMPRODUCT
Công thức:
=SUMPRODUCT(--ISTEXT(A2:A200))
ISTEXT(A2:A200)trả về mảng các giá trị TRUE/FALSE.--chuyển sang 1/0.SUMPRODUCTcộng lại.
4.3 Loại bỏ ô rỗng hoặc chỉ chứa khoảng trắng
Kết hợp ISTEXT và LEN(TRIM()):
=SUMPRODUCT(--(ISTEXT(A2:A200)),--(LEN(TRIM(A2:A200))>0))
Giải thích:
– ISTEXT đảm bảo ô là văn bản.
– LEN(TRIM(...))>0 bảo ô không phải là chuỗi rỗng hoặc chỉ khoảng trắng.
– Kết quả là số ô chứa văn bản có ý nghĩa.
4.4 Đếm ô chứa công thức trả về văn bản
Nếu ô chứa công thức, ví dụ: =IF(B2>0,"Yes","No"), hàm ISTEXT vẫn trả về TRUE vì kết quả là chuỗi. Vì vậy, công thức trên vẫn hoạt động tốt.
5. Đếm ô chứa một từ khóa cụ thể hoặc một phần chuỗi
5.1 Sử dụng COUNTIF với ký tự đại diện
- Chứa từ “excel” (không phân biệt hoa thường):
=COUNTIF(A2:A500,"excel")
- Bắt đầu bằng “Report”:
=COUNTIF(A2:A500,"Report")
- Kết thúc bằng “2026”:
=COUNTIF(A2:A500,"2026")
5.2 Đếm không phân biệt hoa thường bằng SEARCH trong mảng
SEARCH trả về vị trí xuất hiện của chuỗi con, không phân biệt hoa thường.
=SUMPRODUCT(--(ISNUMBER(SEARCH("excel",A2:A500))))
SEARCH("excel",A2:A500)trả về mảng các vị trí (số) hoặc lỗi #VALUE! nếu không tìm thấy.ISNUMBERchuyển thành TRUE/FALSE.--vàSUMPRODUCTcho tổng.
5.3 Đếm nhiều từ khóa cùng lúc
Có thể bạn quan tâm: Excel Conditional Formatting Formula If: Hướng Dẫn Chi Tiết Từ A Đến Z
Giả sử bạn muốn đếm ô chứa “excel” hoặc “google”:
=SUMPRODUCT(--( (ISNUMBER(SEARCH("excel",A2:A500))) + (ISNUMBER(SEARCH("google",A2:A500))) >0 ))
Giải thích:
– Hai biểu thức ISNUMBER(SEARCH(...)) tạo ra 2 mảng TRUE/FALSE.
– Cộng lại, nếu ít nhất một trong chúng là TRUE (giá trị >0), ô sẽ được tính.
5.4 Đếm ô chứa một trong nhiều từ khóa bằng COUNTIFS + OR
Excel không hỗ trợ OR trực tiếp trong COUNTIFS, nhưng bạn có thể cộng các COUNTIF:
=COUNTIF(A2:A500,"excel") + COUNTIF(A2:A500,"google") - COUNTIF(A2:A500,"excelgoogle")
Phần trừ là để tránh tính trùng lặp khi một ô chứa cả hai từ khóa.
6. Kết hợp hàm mảng (Array Formulas) trong Excel 365/2026
6.1 Hàm FILTER để tạo danh sách các ô thỏa mãn
=FILTER(A2:A500, (ISTEXT(A2:A500)) (LEN(TRIM(A2:A500))>0))
Kết quả là một mảng các ô chứa văn bản hợp lệ. Để đếm, chỉ cần:
=ROWS(FILTER(A2:A500, (ISTEXT(A2:A500)) (LEN(TRIM(A2:A500))>0)))
6.2 Hàm LET để tối ưu công thức dài
=LET( rng, A2:A500, txt, ISTEXT(rng), notBlank, LEN(TRIM(rng))>0, SUMPRODUCT(--(txt notBlank))
)
LET giúp đặt tên biến, làm cho công thức dễ đọc và nhanh hơn trong tính toán.
6.3 Hàm LAMBDA tự tạo hàm đếm
Bạn có thể tạo một hàm tùy chỉnh:
= LAMBDA(range, LET( txt, ISTEXT(range), notBlank, LEN(TRIM(range))>0, SUMPRODUCT(--(txt notBlank)) )
)
Sau khi lưu dưới tên CountTextCells, sử dụng:
=CountTextCells(A2:A500)
7. Sử dụng Power Query để đếm ô chứa văn bản trong dữ liệu lớn
Khi dữ liệu lên hàng chục, hàng trăm nghìn dòng, việc dùng công thức trên có thể làm chậm bảng tính. Power Query (Get & Transform) cung cấp một cách tiếp cận mạnh mẽ:
7.1 Các bước cơ bản
- Chọn dải dữ liệu →
Data→From Table/Range. - Trong Power Query Editor, chọn cột cần đếm.
- Thêm cột tùy chỉnh (Add Column → Custom Column) với công thức:
m = if Text.Trim(ColumnName) <> "" and Value.Type(ColumnName) = type text then 1 else 0
- Sum cột mới: Click chuột phải →
Transform→Sum. - Close & Load để trả về Excel.
7.2 Lợi ích
- Xử lý nhanh: Power Query thực hiện tính toán trên bộ nhớ, không tải toàn bộ công thức vào sheet.
- Tự động cập nhật: Khi dữ liệu nguồn thay đổi, chỉ cần refresh.
- Ghi chú lịch sử: Bạn có thể lưu lại các bước xử lý để tái sử dụng.
8. Thực hành: 10 ví dụ thực tế trong các tình huống công việc
Ví dụ 1: Đếm số khách hàng đã nhập địa chỉ email
=SUMPRODUCT(--(ISTEXT(B2:B1000)),--(LEN(TRIM(B2:B1000))>0))
Giải thích: Cột B chứa email. Công thức chỉ tính những ô có văn bản thực sự.
Ví dụ 2: Đếm ô trong cột “Ghi chú” chứa từ “khẩn cấp”
=SUMPRODUCT(--(ISNUMBER(SEARCH("khẩn cấp",C2:C500))))
Ví dụ 3: Đếm sản phẩm có mô tả dài hơn 50 ký tự
=SUMPRODUCT(--(LEN(D2:D2000)>50))
Ví dụ 4: Đếm ô trong cột “Trạng thái” không rỗng và không phải số
=SUMPRODUCT(--(ISTEXT(E2:E300)),--(LEN(TRIM(E2:E300))>0))
Ví dụ 5: Đếm bao nhiêu dòng có ít nhất một ô chứa văn bản trong các cột A‑C
=SUMPRODUCT(--( (ISTEXT(A2:A1000)+ISTEXT(B2:B1000)+ISTEXT(C2:C1000))>0 ))
Ví dụ 6: Đếm ô chứa “đã hoàn thành” hoặc “đang tiến hành”
=SUMPRODUCT(--( (ISNUMBER(SEARCH("đã hoàn thành",F2:F800))) + (ISNUMBER(SEARCH("đang tiến hành",F2:F800))) >0 ))
Ví dụ 7: Đếm ô trong cột “Mô tả” chỉ chứa ký tự chữ (không có số)
=SUMPRODUCT(--(ISTEXT(G2:G600)),--(ISERROR(SEARCH("0-9",G2:G600))))
Lưu ý: Dùng hàm SEARCH với biểu thức chính quy (Excel 365 hỗ trợ TEXTJOIN+FILTERXML để làm regex, nhưng ở đây dùng cách đơn giản).
Ví dụ 8: Đếm số ô có hai từ khóa: “đánh giá” và “tích cực”
=SUMPRODUCT(--(ISNUMBER(SEARCH("đánh giá",H2:H400))),--(ISNUMBER(SEARCH("tích cực",H2:H400))))
Ví dụ 9: Đếm ô trong cột “Ghi chú” chứa bất kỳ ký tự không phải là khoảng trắng (loại bỏ các ô chỉ có dấu cách)
=SUMPRODUCT(--(LEN(TRIM(I2:I500))>0))
Ví dụ 10: Đếm ô chứa văn bản trong bảng động (Excel Table) có tên tblData
=SUMPRODUCT(--(ISTEXT(tblDataColumnName)),--(LEN(TRIM(tblDataColumnName))>0))
9. Lưu ý thường gặp và cách khắc phục lỗi
| Vấn đề | Nguyên nhân | Giải pháp |
|---|---|---|
| Kết quả quá cao | COUNTIF("") tính cả ô chứa số dạng văn bản | Dùng ISTEXT + LEN(TRIM()) |
| Kết quả 0 dù có dữ liệu | Dữ liệu có ký tự đặc biệt, hoặc công thức trả về "" | Kiểm tra bằng =LEN(A2) hoặc dùng TRIM |
| Công thức mảng không hoạt động | Dùng Excel phiên bản cũ, chưa nhấn Ctrl+Shift+Enter | Đảm bảo nhập đúng cách hoặc chuyển sang SUMPRODUCT |
Lỗi #VALUE! khi dùng SEARCH | Ô chứa lỗi hoặc giá trị không phải chuỗi | Bọc SEARCH trong IFERROR(...,0) |
| Quá chậm khi xử lý hàng trăm nghìn dòng | Công thức mảng tính trên mỗi ô | Sử dụng Power Query hoặc PivotTable để tóm tắt |
9.1 Cách debug nhanh
- Kiểm tra từng phần: Ví dụ, tạo cột phụ
=ISTEXT(A2)để xem kết quả TRUE/FALSE. - Sử dụng
Evaluate Formula: TabFormulas→Evaluate Formulađể xem từng bước. - Kiểm tra định dạng ô: Đôi khi ô hiển thị là số nhưng định dạng là Text, gây nhầm lẫn.
10. Kết luận và tài nguyên tham khảo
Việc đếm ô chứa văn bản trong Excel không chỉ là một thao tác đơn giản mà còn là nền tảng cho nhiều quy trình phân tích dữ liệu chuyên sâu. Từ các công thức cơ bản như COUNTIF đến những kỹ thuật nâng cao như SUMPRODUCT kết hợp ISTEXT và LEN(TRIM()), hay thậm chí sử dụng Power Query cho dữ liệu lớn, bạn có đủ công cụ để giải quyết hầu hết các nhu cầu thực tế.
10.1 Những điểm quan trọng cần nhớ
- Luôn loại bỏ ô trống và khoảng trắng bằng
TRIM+LEN. - Sử dụng
ISTEXTđể chắc chắn ô là kiểu văn bản. - Kết hợp hàm mảng (đặc biệt trong Excel 365) để viết công thức ngắn gọn, hiệu quả.
- Power Query là giải pháp tối ưu cho dữ liệu quy mô lớn.
10.2 Tài nguyên học tập bổ sung
| Tài liệu | Link |
|---|---|
| Microsoft Docs – COUNTIF, COUNTIFS | https://support.microsoft.com/en-us/office/countif-function |
| ExcelJet – SUMPRODUCT examples | https://exceljet.net/excel-functions/sumproduct-function |
| Power Query for Excel – Official Guide | https://learn.microsoft.com/en-us/powerquery-m/ |
| Excel Campus – LAMBDA & LET tutorials | https://www.excelcampus.com/functions/let-lambda/ |
| YouTube – “Count Cells with Text in Excel” (kênh Leila Gharani) | https://www.youtube.com/watch?v=… |
Bạn đã nắm vững các phương pháp và công cụ cần thiết để đếm ô chứa văn bản trong mọi tình huống. Hãy áp dụng ngay vào công việc của mình, tự tin xử lý dữ liệu nhanh chóng và chính xác hơn bao giờ hết!
Chúc bạn thành công và tiếp tục khám phá sức mạnh vô hạn của Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.