Đếm số lần xuất hiện của văn bản trong excel

Trong ví dụ này, mục tiêu là đếm các ô trong phạm vi chứa giá trị văn bản. Đây có thể là văn bản được mã hóa cứng như "quả táo" hoặc "màu đỏ", các số được nhập dưới dạng văn bản hoặc công thức trả về giá trị văn bản. Các ô trống và các ô chứa giá trị số hoặc lỗi không được tính vào số lượng. Vấn đề này có thể được giải quyết bằng hàm COUNTIF hoặc hàm TÓM TẮT. Cả hai cách tiếp cận được giải thích dưới đây. Để thuận tiện, dữ liệu là dải ô được đặt tên B5. B15

hàm COUNTIF

Cách đơn giản nhất để giải quyết vấn đề này là sử dụng hàm COUNTIF và ký tự đại diện dấu hoa thị [*]. Dấu hoa thị [*] khớp với 0 hoặc nhiều ký tự thuộc bất kỳ loại nào. Ví dụ: để đếm các ô trong phạm vi bắt đầu bằng "a", bạn có thể sử dụng COUNTIF như thế này

=COUNTIF[range,"a*"] // begins with "a"

Tuy nhiên, trong ví dụ này, chúng tôi không muốn khớp với bất kỳ giá trị văn bản cụ thể nào. Chúng tôi muốn khớp tất cả các giá trị văn bản. Để làm điều này, chúng tôi cung cấp dấu hoa thị [*] cho các tiêu chí. Công thức trong H5 là

=COUNTIF[data,"*"] // any text value

Kết quả là 4, vì có bốn ô trong dữ liệu [B5. B15] có chứa các giá trị văn bản

Để đảo ngược hoạt động của công thức và đếm tất cả các ô không chứa văn bản, hãy thêm toán tử logic không bằng [] như thế này

=COUNTIF[data,"*"] // non-text values

Đây là công thức được sử dụng trong ô H6. Kết quả là 7, vì có 7 ô trong dữ liệu [B5. B15] không chứa giá trị văn bản

Hàm COUNTIFS

Để áp dụng tiêu chí cụ thể hơn, bạn có thể chuyển sang hàm COUNTIF hỗ trợ nhiều điều kiện. Ví dụ: để đếm các ô có văn bản nhưng loại trừ các ô chỉ chứa ký tự khoảng trắng, bạn có thể sử dụng công thức như sau

=COUNTIFS[range,"*",range," "]

Công thức này sẽ đếm các ô chứa bất kỳ giá trị văn bản nào ngoại trừ một khoảng trắng [" "]

Hàm TÓM TẮT

Một cách khác để giải quyết vấn đề này là sử dụng hàm TÓM TẮT với hàm ISTEXT. TÓM TẮT giúp bạn dễ dàng thực hiện kiểm tra logic trên một phạm vi, sau đó đếm kết quả. Bài kiểm tra được thực hiện với hàm ISTEXT. Đúng như tên gọi của nó, hàm ISTEXT chỉ trả về TRUE khi được cung cấp một giá trị văn bản

=ISTEXT["apple"]// returns TRUE
=ISTEXT[70] // returns FALSE

Để đếm các ô có giá trị văn bản trong ví dụ hiển thị, bạn có thể sử dụng công thức như sau

=SUMPRODUCT[--ISTEXT[data]]

Làm việc từ trong ra ngoài, bài kiểm tra logic dựa trên chức năng ISTEXT

ISTEXT[data]

Bởi vì dữ liệu [B5. B15] chứa 11 giá trị, ISTEXT trả về 11 kết quả trong một mảng như thế này

{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

Trong mảng này, các giá trị TRUE tương ứng với các ô chứa giá trị văn bản và các giá trị FALSE tương ứng với các ô không chứa văn bản. Để chuyển đổi các giá trị TRUE và FALSE thành 1 và 0, chúng tôi sử dụng dấu âm kép [--]

________số 8

Mảng kết quả bên trong hàm SUMPRODUCT trông như thế này

=SUMPRODUCT[{1;1;1;0;0;1;0;0;0;0;0}] // returns 4

Với một mảng duy nhất để xử lý, SUMPRODUCT tính tổng mảng và trả về kết quả là 4

Để đảo ngược công thức và đếm tất cả các ô không chứa văn bản, bạn có thể lồng hàm ISTEXT vào bên trong hàm NOT như thế này

=COUNTIF[data,"*"] // any text value

0

Hàm NOT đảo ngược kết quả từ ISTEXT. Dấu phủ định kép [--] chuyển đổi mảng thành số và mảng bên trong TÓM TẮT trông như thế này

=COUNTIF[data,"*"] // any text value

1

Kết quả là 7, vì có 7 ô trong dữ liệu [B5. B15] không chứa giá trị văn bản

Ghi chú. các công thức TÓM TẮT ở trên có vẻ phức tạp, nhưng việc sử dụng các phép toán Boolean trong công thức mảng rất hiệu quả và linh hoạt. Đây cũng là một kỹ năng quan trọng trong các chức năng hiện đại như LỌC và XLOOKUP, thường sử dụng kỹ thuật này để chọn đúng dữ liệu. Mặt khác, cú pháp được COUNTIF sử dụng là duy nhất cho một nhóm gồm tám hàm và do đó không hữu ích hoặc không thể mang theo được

Xem cách đếm văn bản cụ thể trong một ô của trang tính bằng công thức Excel hoặc bằng Google Trang tính. Đếm tất cả các trường hợp của một chuỗi văn bản hoặc chỉ đếm toàn bộ từ

Đếm chuỗi văn bản trong ô

Một chuỗi văn bản cụ thể xuất hiện bao nhiêu lần trong một ô của trang tính?

• Trong ví dụ này, chuỗi văn bản cụ thể được nhập vào ô B2. không khí
• Tại các ô B5. B8, có các ô chứa nhiều chuỗi văn bản, được phân tách bằng dấu phẩy
  • GHI CHÚ. Một số ô có thêm dấu cách trước/sau dấu phẩy
• Các công thức trong cột C đếm số lần "không khí" xuất hiện trong ô

Với công thức này, chuỗi văn bản được tính cho dù đó là một từ đầy đủ hay một phần của một từ

GHI CHÚ. Công thức này [hiển thị bên dưới] sẽ hoạt động trong cả Excel và Google Trang tính

Công thức đếm chuỗi văn bản cụ thể

Công thức này được nhập vào ô C5 và sao chép xuống ô C8

• =[LEN[B5] - LEN[SUBSTITUTE[B5, $B$2,""]]] / LEN[$B$2]

Làm thế nào nó hoạt động

Đây là cách công thức hoạt động

• LEN trả về độ dài của mục nhập văn bản của ô. LEN[B5]
• SUBSTITUTE thay thế tất cả các phiên bản của "không khí" bằng một chuỗi trống
• LEN đo văn bản rút gọn
• Độ dài mới [14] được trừ vào độ dài cũ [23]
• Sự khác biệt [9] được chia cho độ dài của chuỗi văn bản cụ thể [3]
• Kết quả của sự phân chia đó là số lượng trường hợp [3]

Đây là công thức, với mỗi phần được tính toán, trong thanh công thức Excel. [Chọn các phần của công thức, sau đó nhấn phím F9, để tính toán phần đó. ]

Đếm các mục văn bản cụ thể trong ô

Một chuỗi văn bản cụ thể xuất hiện bao nhiêu lần, dưới dạng một mục riêng biệt, trong một ô của trang tính?

• Trong ví dụ này, chuỗi văn bản cụ thể được nhập vào ô B2. không khí
• Tại các ô B5. B8, có các ô chứa nhiều chuỗi văn bản, được phân tách bằng dấu phẩy
  • GHI CHÚ. Một số ô có thêm dấu cách trước/sau dấu phẩy

Giải pháp cho Excel và Google Trang tính

Đối với sự cố này, có các giải pháp khác nhau trong Google Trang tính và trong Excel

Với các công thức này, chuỗi văn bản chỉ được tính nếu nó nằm riêng lẻ giữa các dấu phẩy [có hoặc không có ký tự khoảng trắng]

--

--

--

--

Công thức Google Trang tính

Công thức Google Sheets ngắn hơn nhiều so với công thức Excel nhờ hàm SPLIT

Công thức này được nhập vào ô C5 và sao chép xuống ô C8

• =COUNTIF[SPLIT[SUBSTITUTE[B5," ",""],","],$B$2]

Làm thế nào nó hoạt động

Đây là cách thức hoạt động của công thức Google Trang tính

• SUBSTITUTE thay thế tất cả các ký tự khoảng trắng bằng một chuỗi rỗng
• SPLIT tạo một mảng các mục, được phân chia tại dấu phẩy
• COUNTIF đếm tất cả các mục trong mảng đó, nếu chúng khớp với văn bản trong ô B2

Đây là chức năng của hàm SPLIT nếu nó là hàm duy nhất trong công thức

• Các kết quả điền vào các cột bên phải
• Hàm COUNTIF có thể đếm các mục phù hợp trong phạm vi ô đó

Bằng cách kết hợp SPLIT và COUNTIF, tất cả kết quả đều nằm trong một ô

Công thức Excel

Tiếc là Excel không có hàm SPLIT nên cần công thức dài hơn

-- Nếu bạn có Excel 365, hãy sử dụng

-- Nếu bạn có Excel 2013 trở lên, hãy sử dụng

-- Trong các phiên bản trước của Excel

---- Sử dụng

---- HOẶC, sử dụng , nếu bạn thích

Công thức Excel - SEQUENCE

Nếu bạn có Excel 365, hãy sử dụng công thức này bao gồm hàm SEQUENCE. Cảm ơn UniMord đã gửi công thức này

Công thức cũng sử dụng các hàm SUM và SUBSTITUTE, và đây là công thức trong ô C5. Tôi đã thêm một vài ký tự khoảng trắng để dễ đọc

• =SUM[--[SUBSTITUTE[","& SUBSTITUTE[B5," ",""] & ",", "," & $B$2&",","", SEQUENCE[LEN[B5]]]

Làm thế nào nó hoạt động

Tôi đã tô màu các phần của công thức và chia nhỏ thành nhiều dòng. Có ghi chú ở mỗi phần bên dưới

Tổng quan về công thức

Để giải thích công thức này, tôi sẽ sử dụng chuỗi văn bản từ ô B5

Dưới đây là tổng quan về chức năng của công thức

1. Hàm SUBSTITUTE màu xanh lam sáng tạo Chuỗi văn bản 1
2. Điều đó được so sánh với Chuỗi văn bản 2, được tạo bởi phần màu tím của công thức
   • Nếu hai chuỗi văn bản không bằng nhau, kết quả là TRUE
   • Nếu chúng bằng nhau, kết quả là FALSE
3. 2 dấu trừ bên trong dấu ngoặc đầu tiên chuyển đổi các kết quả đó thành số
4. Hàm SUM cộng các số đó để đếm số lần một từ cụ thể được tìm thấy trong ô, dưới dạng một mục riêng biệt

Màu xanh tươi sáng THAY THẾ

SUBSTITUTE màu xanh sáng sử dụng 4 đối số để tạo Chuỗi văn bản 1

• Văn bản, Văn bản cũ, Văn bản mới, Phiên bản Num

Đối số văn bản

Phần màu đỏ của công thức nối 3 đoạn văn bản

1. dấu phẩy
2. văn bản từ ô B5, với khoảng trắng được thay thế bằng các chuỗi trống
3. dấu phẩy khác

Kết quả là chuỗi văn bản này. ,không khí,đào,không khí,không khí,

Đối số văn bản cũ

Phần công thức màu xanh đậm nối 3 đoạn văn bản

1. dấu phẩy
2. tìm kiếm từ ô B2
3. dấu phẩy khác

Kết quả là chuỗi văn bản này. ,không khí,

Đối số văn bản mới

Phần màu đen của công thức là một chuỗi rỗng. ""

Ví dụ Num đối số

Phần màu xanh của công thức tạo danh sách các số

• bắt đầu từ 1
• kết thúc bằng số ký tự trong ô B5 -- LEN[B5]

Vì SEQUENCE là hàm tràn nên nó trả về nhiều số, thay vì chỉ 1 số

Có 17 ký tự trong ô B5, vì vậy đây là kết quả cho đối số Instance Num

1,2,3,4,5,6,7,8,9. 10,11,12,13,14,15,16,17

Màu xanh tươi sáng THAY THẾ

Vì SEQUENCE là một hàm tràn, nên hàm SUBSTITUTE màu lam sáng trả về 17 chuỗi văn bản. Có một kết quả cho mỗi số thứ tự

Đây là 6 trường hợp đầu tiên, nếu chúng được liệt kê trên trang tính

1. Ví dụ 1, ,air, đầu tiên bị loại bỏ và kết quả là dig,air,air,
2. Ví dụ 2, thứ hai, không khí, bị loại bỏ và kết quả là, không khí, digair,
3. Ví dụ 3, ,air, thứ ba bị loại bỏ và kết quả là ,air,dig,air
4. Ví dụ 4, không có phiên bản thứ tư, vì vậy kết quả là,air,dig,air,air,

Chuỗi văn bản so sánh 2

Phần màu tím của công thức tạo chuỗi văn bản so sánh - Text String 2

Nó giống y hệt phần bôi đỏ công thức, và nối 3 đoạn văn bản

1. dấu phẩy
2. văn bản từ ô B5, với khoảng trắng được thay thế bằng các chuỗi trống
3. dấu phẩy khác

Kết quả là chuỗi văn bản này. ,không khí,đào,không khí,không khí,

So sánh chuỗi văn bản

Tiếp theo, đối với mỗi số phiên bản, Chuỗi văn bản 1 được so sánh với Chuỗi văn bản 2, sử dụng toán tử NOT EQUAL -

• Nếu hai chuỗi văn bản không bằng nhau, kết quả là TRUE
• Nếu chúng bằng nhau, kết quả là FALSE

TỔNG KẾT QUẢ

Tiếp theo, 2 dấu trừ bên trong dấu ngoặc đầu tiên chuyển đổi các kết quả TRUE/FALSE đó thành số

Và cuối cùng, hàm SUM cộng các số đó để đếm số lần một từ cụ thể được tìm thấy trong ô, dưới dạng một mục riêng biệt

Công thức FILTERXML

Nếu bạn có Excel 2013, hãy sử dụng công thức FILTERXML này. Nó không có sẵn trong Excel trực tuyến hoặc Excel cho Mac

FILTERXML trả về dữ liệu cụ thể từ nội dung XML, dựa trên XPath đã chỉ định. Công thức của chúng tôi sẽ trả về các mục cụ thể từ văn bản được phân tách bằng dấu phẩy, dựa trên từ tìm kiếm của chúng tôi

Bạn có thể tìm hiểu thêm về FILTERXML trên trang web của Microsoft

GHI CHÚ. Đó là một hành trình thú vị, cố gắng tìm ra giải pháp FILTERXML và tôi đã viết về điều đó trên blog Ngữ cảnh của mình. Cuộn xuống phần Thử nghiệm với FILTERXML XPath, nếu bạn quan tâm đến các chi tiết đẫm máu

Tạo XML

Để tạo mã XML, công thức của chúng tôi sẽ thay thế dấu phẩy trong văn bản được phân tách bằng dấu phẩy. Ví dụ: chuỗi văn bản này nằm trong một ô

=COUNTIF[data,"*"] // any text value

2

Công thức sẽ thay đổi nó thành cấu trúc XML, như thế này

• d đại diện cho dữ liệu ô
• dòng i là các mục được phân tách bằng dấu phẩy
• n là một thuộc tính của 1 cho mỗi mục, vì vậy chúng có thể được tính tổng sau

Kỹ thuật XML này được điều chỉnh từ một nhận xét trên diễn đàn của Chandoo và bạn có thể tìm hiểu thêm về XML [Ngôn ngữ đánh dấu mở rộng] trong XML này cho bài viết dành cho Người chưa biết trên trang web của Microsoft

Chuỗi XML

Để tạo từng chuỗi XML dễ dàng hơn, có 4 ô có chuỗi ở đầu trang tính

Công thức FILTERXML của chúng tôi kết hợp các giá trị đó với văn bản được phân tách bằng dấu phẩy trong một ô

1. Văn bản trong E1 đi trước văn bản ô
2. Văn bản trong F1 thay thế dấu phẩy
3. Văn bản trong G1 đi sau văn bản ô

Bạn có thể tìm hiểu thêm về XML [Ngôn ngữ đánh dấu mở rộng] trong XML này cho bài viết dành cho Người chưa biết trên trang web của Microsoft

Tạo XPath

Đối với đối số XPath, ô H1 có công thức kết hợp văn bản với từ tìm kiếm của chúng tôi

• ="//i[normalize-space[]='" & B2 & "']/@n"

Nếu dig là từ tìm kiếm, đây sẽ là XPath

• //i[normalize-space[]='dig']/@n

Nó có nghĩa là gì

Trong ví dụ này, đây là những gì XPath sẽ làm

• trả lại thuộc tính n [1] cho tất cả các mục bằng đào
• Hàm normalize-space loại bỏ các khoảng trắng ở đầu và cuối và các khoảng trắng thừa giữa các từ, như hàm TRIM của Excel

Công thức FILTERXML

Trên trang tính Items_XML, công thức này nằm trong ô C5, để đếm các mục văn bản khớp với mục tìm kiếm của chúng tôi

• =IFERROR[
     SUM[
     FILTERXML[$E$1& SUBSTITUTE[B5,",",$F$1] &$G$1
     ,0]

Làm thế nào nó hoạt động

Đây là cách tính toán trong công thức hoạt động

• Để tạo mã XML,
  • SUBSTITUTE thay thế tất cả các dấu phẩy bằng chuỗi văn bản trong ô F1
  • Chuỗi văn bản từ E1 được nối ở đầu
  • Chuỗi văn bản từ G1 được nối ở cuối
    • $E$1&SUBSTITUTE[B5,",",$F$1]&$G$1
• FILTERTEXT trả về thuộc tính số [1] từ mã XML đó, dựa trên XPath trong ô H1
• SUM trả về tổng của tất cả các số 1
• IFERROR trả về số không, nếu không tìm thấy mục phù hợp

Cột trợ giúp công thức Excel

Nếu bạn không có Excel 2013 trở lên, công thức này sẽ hoạt động trong các phiên bản cũ hơn. Ví dụ này sử dụng cột trợ giúp và có một cột trong phần tiếp theo

GHI CHÚ. Ví dụ này nằm trên trang tính Items_LEN trong sổ làm việc mẫu

dải phân cách

Công thức Excel đề cập đến 2 ô được đặt tên - SepSel và SepSel2. Trong tệp mẫu, các ô được đặt tên đó nằm trên trang tính Quản trị

Chuỗi tìm kiếm

Để xác định từng mục riêng biệt trong các ô văn bản, các dấu phân cách đó được sử dụng để tạo chuỗi tìm kiếm. Trong tệp mẫu, công thức này nằm trong ô D2, trên trang tính CountItems

Công thức kết hợp giá trị văn bản trong ô B2, với ký tự ống [SelSep2] ở đầu và dấu phẩy [SelSep] ở cuối

Công thức cột trợ giúp

Công thức Excel có thể được nhập tất cả trong một ô, nhưng cột trợ giúp sẽ giúp dễ hiểu hơn

Trong cột trợ giúp, công thức sẽ tạo một chuỗi văn bản đánh dấu từng mục

• thay thế tất cả các ký tự khoảng trắng bằng các chuỗi trống, sử dụng SUBSTITUTE
• đặt ký tự ống [SelSep2] ở đầu
• thay thế tất cả dấu phẩy [SelSep] bằng ký tự dấu phẩy và dấu gạch ngang, sử dụng SUBSTITUTE
• đặt dấu phẩy ở cuối

Đây là công thức từ cột trợ giúp

• =SelSep2 &
  SUBSTITUTE[
      SUBSTITUTE[B5," ",""],
  SelSep,
  & SelSep

Và đây là kết quả trong cột trợ giúp. Sẽ dễ dàng hơn để tìm và đếm các mục khớp với chuỗi tìm kiếm trong cuộc gọi màu xám --. không khí,

Đếm công thức mục văn bản

Công thức cuối cùng sẽ sử dụng cột trợ giúp đó để đếm các mục văn bản. Công thức này giống như công thức đầu tiên trên trang này, tính tất cả các lần xuất hiện của một chuỗi văn bản. Nhưng trong công thức này, chúng ta sẽ đề cập đến

• cột trợ giúp và công thức chuỗi tìm kiếm

thay vì

• ô văn bản và mục cần đếm

Đây là công thức trong cột Count

• =[LEN[D5] -LEN[SUBSTITUTE[D5,$D$2,""]]] / LEN[$D$2]

Làm thế nào nó hoạt động

Đây là cách công thức hoạt động

• LEN trả về độ dài của văn bản cột trợ giúp. LEN[D5]
• SUBSTITUTE thay thế tất cả các phiên bản của ". không khí," với một chuỗi rỗng
• LEN đo văn bản rút gọn
• Chiều dài mới [5] được trừ vào chiều dài cũ [20]
• Sự khác biệt [15] được chia cho độ dài của chuỗi văn bản cụ thể [5]
• Kết quả của sự phân chia đó là số lượng trường hợp [3]

Công thức tất cả trong một của Excel

Nếu không muốn tạo cột trợ giúp trên trang tính của mình, bạn có thể nhận được kết quả tương tự với công thức tất cả trong một

Bạn có đếm được bao nhiêu lần một cái tên xuất hiện trong Excel không?

Có cách nào để đếm các từ cụ thể trong Excel không?

Bạn có thể đếm văn bản màu trong Excel không?