Trong môi trường làm việc hiện đại, Excel không chỉ là một công cụ tính toán số liệu đơn thuần mà còn là một nền tảng mạnh mẽ để xử lý và phân tích dữ liệu văn bản. Một trong những nhu cầu phổ biến mà người dùng thường gặp là đếm số lần một từ cụ thể xuất hiện trong một cột, một hàng hoặc toàn bộ bảng tính. Dù bạn là nhà phân tích dữ liệu, nhân viên marketing, giáo viên hay sinh viên, việc biết cách đếm từ một cách nhanh chóng, chính xác sẽ giúp bạn tiết kiệm thời gian và đưa ra quyết định dựa trên dữ liệu thực tế.

Bài viết này sẽ cung cấp cho bạn hướng dẫn chi tiết, đầy đủ và thực tiễn về cách đếm từ cụ thể trong Excel, bao gồm:

Giới thiệu tổng quan về vấn đề và tại sao lại cần đếm từ trong Excel.
Các hàm cơ bản (COUNTIF, SUMPRODUCT, LEN, SUBSTITUTE…) và cách áp dụng chúng.
Công thức nâng cao cho trường hợp đếm từ trong chuỗi dài, đếm nhiều từ cùng lúc, và đếm từ có dấu/không dấu.
Sử dụng Power Query để thực hiện đếm từ trên dữ liệu lớn.
Kỹ thuật với VBA (Macro) để tự động hoá quá trình đếm từ.
Các lưu ý thực tiễn: xử lý lỗi, loại bỏ khoảng trắng thừa, phân biệt hoa thường, và tính năng “Find & Replace”.
Ví dụ thực tế trong các lĩnh vực: marketing, giáo dục, quản lý dự án, và nghiên cứu khoa học.
Bảng tổng hợp công thức và bảng so sánh các phương pháp để bạn dễ dàng lựa chọn.

Mục tiêu: Sau khi đọc xong bài viết, bạn sẽ có thể tự tin sử dụng Excel để đếm bất kỳ từ nào trong bất kỳ phạm vi dữ liệu nào, dù dữ liệu đó có phức tạp đến đâu.

Có thể bạn quan tâm: Hàm Random Không Lặp Lại Trong Excel: Cách Tạo Dữ Liệu Ngẫu Nhiên Độc Đáo, Ứng Dụng Thực Tiễn Và Mẹo Tối Ưu

Mục lục bài viết

1. Tại Sao Việc Đếm Từ Cụ Thể Trong Excel Lại Quan Trọng?

1.1. Ứng dụng thực tiễn trong công việc

Marketing: Đếm số lần xuất hiện từ “khuyến mãi”, “giảm giá” trong phản hồi khách hàng để đánh giá hiệu quả chiến dịch.
HR: Phân tích nội dung CV, đếm số lần xuất hiện các kỹ năng quan trọng như “Python”, “Excel”, “SQL”.
Giáo dục: Kiểm tra tần suất sử dụng từ khóa trong bài luận, giúp giáo viên đánh giá mức độ hiểu biết của học sinh.
Nghiên cứu khoa học: Đếm các thuật ngữ chuyên ngành trong tài liệu để phân tích nội dung.

1.2. Tiết kiệm thời gian và giảm sai sót

Thay vì phải duyệt qua hàng ngàn dòng dữ liệu bằng mắt, một công thức hoặc macro trong Excel sẽ thực hiện công việc trong vài giây, đồng thời giảm thiểu sai sót do con người gây ra.

Có thể bạn quan tâm: Cách Đếm Số Dòng Được Lọc Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Các Hàm Cơ Bản Để Đếm Từ Trong Excel

2.1. COUNTIF – Đếm ô thỏa mãn điều kiện đơn giản

Cú pháp:

=COUNTIF(range, criteria)

range: phạm vi cần kiểm tra (cột, hàng hoặc toàn bộ bảng).
criteria: điều kiện, có thể là một từ hoặc một biểu thức.

Ví dụ 1: Đếm số ô trong cột A chứa từ “Excel” (không phân biệt hoa thường).

=COUNTIF(A:A, "Excel")

Kí tự là wildcard, đại diện cho bất kỳ chuỗi ký tự nào trước hoặc sau từ “Excel”.

Lưu ý: COUNTIF không phân biệt chữ hoa/thường, nhưng không thể xử lý các trường hợp phức tạp như đếm nhiều từ trong một ô.

2.2. SUMPRODUCT Kết Hợp LEN và SUBSTITUTE – Đếm số lần xuất hiện của một từ trong một ô

Khi một ô có thể chứa nhiều lần từ cần đếm, ta cần tính số lần xuất hiện trong cùng một ô, không chỉ đếm ô có chứa từ.

Công thức:

=SUMPRODUCT((LEN(A1:A100)-LEN(SUBSTITUTE(LOWER(A1:A100),"excel","")))/LEN("excel"))

LOWER chuyển toàn bộ nội dung thành chữ thường để không phân biệt hoa thường.
SUBSTITUTE(text, old_text, "") loại bỏ từ cần đếm, sau đó so sánh độ dài trước và sau khi thay thế.
LEN("excel") là độ dài của từ cần đếm, dùng để tính số lần xuất hiện.

Ví dụ 2: Đếm số lần từ “excel” xuất hiện trong phạm vi A1:A100.
Kết quả trả về là tổng số lần xuất hiện, kể cả khi một ô chứa từ này nhiều lần.

2.3. COUNTIFS – Đếm với nhiều tiêu chí

Nếu muốn đếm ô chứa cùng lúc hai từ, ví dụ “excel” và “training”, ta dùng COUNTIFS:

=COUNTIFS(A:A, "excel", A:A, "training")

Công thức này sẽ chỉ đếm các ô mà cả hai từ đều xuất hiện.

2.4. FILTER (Excel 365) – Lọc và Đếm

Với phiên bản Excel 365, hàm FILTER giúp tạo mảng dữ liệu thỏa mãn điều kiện, sau đó dùng COUNTA để đếm:

=COUNTA(FILTER(A:A, ISNUMBER(SEARCH("excel", A:A))))

SEARCH trả về vị trí của từ “excel” nếu tồn tại, ngược lại trả về lỗi.
ISNUMBER chuyển kết quả thành TRUE/FALSE để FILTER hoạt động.

Có thể bạn quan tâm: Cách Đếm Giá Trị Độc Nhất (distinct) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

3. Công Thức Nâng Cao

3.1. Đếm nhiều từ khác nhau trong cùng một phạm vi

Giả sử bạn cần đếm từ “excel”, “powerpoint”, “word” trong cột B và tổng hợp kết quả vào một bảng tóm tắt.

Bước 1: Tạo danh sách từ cần đếm (D2:D4)

D2: excel
D3: powerpoint
D4: word

Bước 2: Dùng công thức mảng (Ctrl+Shift+Enter trong Excel trước 365) hoặc hàm LET (Excel 365) để tính:

=LET( words, D2:D4, rng, B:B, result, MAP(words, LAMBDA(w, SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(LOWER(rng),w,"")))/LEN(w)))), result
)

Kết quả là một mảng 3 giá trị, tương ứng với số lần xuất hiện của từng từ.

3.2. Đếm từ có dấu và không dấu (tiếng Việt)

Tiếng Việt có dấu, nên khi đếm từ “công việc” và “cong viec” (không dấu) chúng ta cần chuẩn hoá dữ liệu.

Cách 1 – Sử dụng hàm SUBSTITUTE lặp lại:

=SUMPRODUCT((LEN(A1:A100)-LEN(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(LOWER(A1:A100),"á","a"),"à","a"),"ả","a"),"ã","a"),"ạ","a"), "é","e"),"è","e"),"ẻ","e"),"ẽ","e"),"ẹ","e")))/LEN("cong viec"))

Cách 2 – Sử dụng Power Query: Power Query có chức năng Replace Values cho phép thay thế hàng loạt ký tự có dấu thành không dấu, sau đó áp dụng công thức đếm.

3.3. Đếm từ trong các ô hợp nhất (merged cells)

Excel không cho phép công thức trực tiếp trên các ô hợp nhất. Giải pháp:

Tách dữ liệu: Sử dụng =TEXTJOIN(" ",TRUE,range) để gộp nội dung các ô lại thành một chuỗi duy nhất, sau đó áp dụng công thức đếm.
Sử dụng VBA: Viết macro để duyệt qua từng ô hợp nhất, lấy .MergeArea.Value.

Function CountWordInMerged(rng As Range, word As String) As Long Dim cell As Range, txt As String, cnt As Long For Each cell In rng If cell.MergeCells Then txt = cell.MergeArea.Value Else txt = cell.Value End If cnt = cnt + (Len(LCase(txt)) - Len(Replace(LCase(txt), LCase(word), ""))) / Len(word) Next cell CountWordInMerged = cnt
End Function

Sau đó trong Excel dùng: =CountWordInMerged(A1:A20,"excel").

4. Sử Dụng Power Query Để Đếm Từ Trên Dữ Liệu Lớn

Có thể bạn quan tâm: Cách Đếm Số Ký Tự Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Power Query (Get & Transform) là công cụ mạnh mẽ cho ETL (Extract, Transform, Load), đặc biệt hữu ích khi dữ liệu có hàng nghìn, hàng triệu dòng.

4.1. Bước thực hiện cơ bản

Load dữ liệu: Data > Get Data > From Table/Range.
Thêm cột tùy chỉnh: Add Column > Custom Column.
Công thức M (ngôn ngữ Power Query) để đếm từ:

= Table.AddColumn(PreviousStep, "CountExcel", each List.Sum( List.Transform( Text.Split(YourColumn, " "), (x) => if Text.Lower(x) = "excel" then 1 else 0 ) ))

Text.Split tách nội dung cột thành danh sách các từ.
List.Transform kiểm tra từng từ, trả về 1 nếu khớp, ngược lại 0.
List.Sum cộng tổng các giá trị, cho ra số lần xuất hiện trong từng ô.
Tổng hợp: Để có tổng số trên toàn bảng, dùng Group By và Sum cột CountExcel.

4.2. Ưu điểm của Power Query

Xử lý nhanh trên dữ liệu lớn (hàng triệu dòng) mà công thức truyền thống có thể chậm.
Ghi nhớ lịch sử: Mọi bước biến đổi được lưu lại, dễ dàng chỉnh sửa.
Không thay đổi dữ liệu gốc: Dữ liệu gốc vẫn nguyên vẹn, chỉ tạo một bảng mới.

5. Tự Động Hoá Với VBA (Macro)

Khi bạn cần đếm từ thường xuyên hoặc muốn tích hợp vào một quy trình tự động, VBA là lựa chọn tối ưu.

5.1. Macro đơn giản để đếm một từ trong một cột

Sub CountWordInColumn() Dim ws As Worksheet Dim rng As Range, cell As Range Dim wordToFind As String Dim totalCount As Long Set ws = ThisWorkbook.Sheets("Sheet1") Set rng = ws.Range("A2:A1000") ' Thay đổi phạm vi tùy ý wordToFind = InputBox("Nhập từ cần đếm:", "Đếm từ") totalCount = 0 For Each cell In rng If Not IsError(cell.Value) Then totalCount = totalCount + (Len(LCase(cell.Value)) - Len(Replace(LCase(cell.Value), LCase(wordToFind), ""))) / Len(wordToFind) End If Next cell MsgBox "Tổng số lần xuất hiện của từ '" & wordToFind & "' là: " & totalCount, vbInformation, "Kết quả"
End Sub

5.2. Đếm nhiều từ cùng lúc và xuất ra bảng tóm tắt

Sub CountMultipleWords() Dim ws As Worksheet, outWs As Worksheet Dim rng As Range, cell As Range Dim words As Variant, i As Long, cnt As Long Dim dict As Object Set ws = ThisWorkbook.Sheets("Data") Set outWs = ThisWorkbook.Sheets("Summary") Set rng = ws.Range("B2:B5000") words = Array("excel", "powerpoint", "word") ' Danh sách từ cần đếm Set dict = CreateObject("Scripting.Dictionary") For i = LBound(words) To UBound(words) dict(words(i)) = 0 Next i For Each cell In rng If Not IsError(cell.Value) Then For i = LBound(words) To UBound(words) cnt = (Len(LCase(cell.Value)) - Len(Replace(LCase(cell.Value), words(i), ""))) / Len(words(i)) dict(words(i)) = dict(words(i)) + cnt Next i End If Next cell ' Ghi kết quả vào sheet Summary outWs.Range("A1").Value = "Từ" outWs.Range("B1").Value = "Số lần xuất hiện" i = 2 For Each key In dict.Keys outWs.Cells(i, 1).Value = key outWs.Cells(i, 2).Value = dict(key) i = i + 1 Next key MsgBox "Hoàn thành! Kiểm tra sheet 'Summary' để xem kết quả.", vbInformation
End Sub

5.3. Khi nào nên dùng VBA?

Tần suất cao: Bạn cần thực hiện đếm từ hằng ngày/tuần.
Tự động hoá báo cáo: Kết hợp với các macro khác để tạo báo cáo tổng hợp.
Xử lý dữ liệu tùy biến: Khi công thức Excel không đáp ứng được yêu cầu phức tạp (ví dụ: bỏ qua các từ trong ngoặc, hoặc chỉ đếm từ trong câu hoàn chỉnh).

6. Các Lưu Ý Thực Tiễn Khi Đếm Từ

Vấn đề	Nguyên nhân	Giải pháp
Ký tự khoảng trắng thừa	Dữ liệu nhập thủ công có thể có space đầu/cuối hoặc nhiều space liên tiếp.	Dùng `TRIM` để loại bỏ khoảng trắng đầu/cuối và `SUBSTITUTE(text," "," ")` lặp lại để giảm nhiều space thành một.
Phân biệt hoa thường	Một số hàm (COUNTIF) không phân biệt, một số khác (SEARCH) có thể phân biệt.	Quy chuẩn hoá dữ liệu bằng `LOWER` hoặc `UPPER` trước khi đếm.
Từ con trong từ khác	Đếm “cat” sẽ tính cả “concatenation”.	Sử dụng rìa từ (`" cat "`), hoặc kết hợp `=IF(ISNUMBER(SEARCH(" cat ", " " & A1 & " ")),1,0)`.
Dấu câu & ký tự đặc biệt	Dấu chấm, dấu phẩy, dấu gạch ngang… làm từ bị chia cắt.	Dùng `SUBSTITUTE` để thay thế dấu câu bằng space trước khi đếm.
Đếm từ trong công thức	Nếu ô chứa công thức trả về văn bản, các hàm vẫn hoạt động, nhưng cần `VALUE` hoặc `TEXT`.	Đảm bảo ô trả về kiểu text (`=TEXT(A1,"@")`).
Ngôn ngữ Unicode	Tiếng Việt có dấu, tiếng Trung, tiếng Nhật…	Sử dụng Power Query hoặc VBA với `StrConv(text, vbUnicode)` để chuẩn hoá.

7. Ví Dụ Thực Tế

7.1. Marketing: Phân Tích Phản Hồi Khách Hàng

Bạn có bảng phản hồi (cột C) với hàng ngàn bình luận. Muốn biết từ “giảm giá” xuất hiện bao nhiêu lần.

Công thức nhanh:

=SUMPRODUCT((LEN(C2:C5000)-LEN(SUBSTITUTE(LOWER(C2:C5000),"giảm giá","")))/LEN("giảm giá"))

Kết quả: Số lần “giảm giá” được đề cập trong toàn bộ phản hồi.

7.2. HR: Đánh Giá Kỹ Năng Ứng Viên

Bạn có danh sách CV trong cột D, muốn biết có bao nhiêu người có kỹ năng “Python”.

Công thức:

=COUNTIF(D:D,"python")

Nếu muốn đếm số lần từ “python” xuất hiện (có thể một người đề cập nhiều lần), dùng SUMPRODUCT như trong phần 2.2.

7.3. Giáo Dục: Kiểm Tra Tần Suất Từ Khóa Trong Bài Luận

Giả sử bài luận của học sinh nằm trong cột E. Bạn muốn biết mỗi học sinh đã dùng từ “bảo vệ môi trường” bao nhiêu lần.

Công thức (đặt trong cột F, cùng hàng với học sinh):

= (LEN(LOWER(E2))-LEN(SUBSTITUTE(LOWER(E2),"bảo vệ môi trường","")))/LEN("bảo vệ môi trường")

Sau khi kéo công thức xuống, cột F sẽ cho số lần xuất hiện cho mỗi học sinh.

7.4. Nghiên Cứu Khoa Học: Đếm Thuật Ngữ Trong Bảng Tóm Tắt

Bạn có một cột chứa tóm tắt nghiên cứu, cần đếm các thuật ngữ “nanoparticle”, “biosensor”, “electrochemical”.

Bảng tóm tắt công thức (Excel 365 – dùng LET và MAP):

=LET( terms, {"nanoparticle","biosensor","electrochemical"}, rng, A2:A200, MAP(terms, LAMBDA(t, SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(LOWER(rng),t,"")))/LEN(t))))
)

Kết quả trả về một mảng 3 giá trị, có thể đặt vào 3 ô liền kề.

8. Bảng Tổng Hợp Công Thức

Mục tiêu	Công thức	Ưu điểm	Nhược điểm
Đếm ô chứa từ (không phân biệt HOA/THƯỜNG)	`=COUNTIF(A:A,"excel")`	Đơn giản, nhanh	Không đếm số lần trong một ô
Đếm số lần xuất hiện trong mỗi ô	`=SUMPRODUCT((LEN(A1:A100)-LEN(SUBSTITUTE(LOWER(A1:A100),"excel","")))/LEN("excel"))`	Đếm chính xác số lần	Cần dùng mảng, chậm với dữ liệu lớn
Đếm nhiều từ cùng lúc (Excel 365)	`=LET(words, D2:D4, rng, B:B, MAP(words, LAMBDA(w, SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(LOWER(rng),w,"")))/LEN(w)))))`	Linh hoạt, không cần VBA	Yêu cầu Excel 365
Đếm trong dữ liệu lớn (Power Query)	`Table.AddColumn(..., each List.Sum(...))`	Xử lý nhanh, ghi lại lịch sử	Cần học Power Query
Tự động hoá định kỳ (VBA)	Macro (xem phần 5)	Tự động, tùy biến cao	Cần kiến thức lập trình VBA
Đếm từ có dấu/không dấu	Chuỗi `SUBSTITUTE` lặp lại hoặc Power Query chuyển ký tự	Đảm bảo tính chính xác trong tiếng Việt	Công thức dài, phức tạp

9. Kết Luận

Việc đếm từ cụ thể trong Excel không còn là một thách thức khi bạn đã nắm vững các công cụ và kỹ thuật ở trên. Từ các hàm cơ bản như COUNTIF, SUMPRODUCT đến các giải pháp mạnh mẽ hơn như Power Query và VBA, mỗi phương pháp đều có ưu và nhược điểm riêng, phù hợp với các tình huống và khối lượng dữ liệu khác nhau.

Những điểm cần nhớ:

Xác định mục tiêu: Đếm ô hay đếm số lần trong ô? Phân biệt hoa thường hay không?
Chuẩn hoá dữ liệu: Loại bỏ khoảng trắng thừa, chuẩn hoá dấu, chuyển về cùng một dạng chữ.
Chọn công cụ phù hợp: Công thức nhanh cho dữ liệu nhỏ, Power Query cho dữ liệu lớn, VBA cho tự động hoá.
Kiểm tra và xác nhận: Luôn so sánh kết quả với một mẫu kiểm tra để chắc chắn công thức hoạt động đúng.

Hy vọng bài viết này đã cung cấp cho bạn bộ công cụ toàn diện để giải quyết mọi nhu cầu đếm từ trong Excel, dù là trong công việc thường ngày hay trong các dự án phân tích dữ liệu phức tạp. Hãy thử áp dụng ngay vào bảng tính của mình và chia sẻ kết quả với đồng nghiệp nhé!

Tiến sĩ Lê Thống Nhất

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 hì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ẻ.

Cách Đếm Số Lần Xuất Hiện Của Từ Cụ Thể Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao