Chuỗi đếm excel được phân tách bằng dấu phẩy

Bạn muốn thành thạo Microsoft Excel và nâng triển vọng công việc tại nhà của mình lên một tầm cao mới?

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. hàng không
• 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. hàng không
• 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 Sheets

Đố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 Excel 365

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

-- Công thức Excel - Tất cả trong một

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 công thức với hàm SEQUENCE

-- Nếu bạn có Excel 2013 trở lên, hãy sử dụng công thức với hàm FILTERXML

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

---- Sử dụng công thức với cột trợ giúp

---- HOẶC, sử dụng công thức tất cả trong một, 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

Để 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

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. ,hàng không,

Đố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

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,

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í,

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

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 ô

house, dig, air , hair , air, dig

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"

• //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ông thức tất cả trong mộ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 --. hàng không,

Đế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]

Đâ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