sql - How to count occurrence of each sentence in Excel in this specific case?

Question

The challenge here is the sentence is not split by cell. They are in the same column, but they might appear in the same cell. One sentence per line.

I need to count the occurrence of each sentence, for example, occurrence of "The cat is pink" is 2 and occurrence of "The dog is green" is 1.

I can also do Access 2016 if needed.

标签： sqlexcelms-accessexcel-2016ms-access-2016

Answer 1

（假设您可以将多句单元格拆分为多个单元格）

1）用多个句子拆分单元格，您应该能够调整此代码来执行此操作。

2) 制作该列的副本（在同一张纸或另一张纸上的其他地方——在同一张纸上使用 B 列），

3）删除复制列的重复值

4）在列旁边使用以下数组公式：

{=SUM(LEN(A$1:A$5)-LEN(SUBSTITUTE(A$1:A$5,B1,"")))/LEN(B1)}

<CTRL><SHIFT><ENTER>（输入数组公式时按下）