数数还是和(?)与Excel中列表中的条件匹配的单个单元格中的项

Question

我有一个单元格，它是调查的输出，包含从20个可能项目的列表中选择的项目。

即。最初可能的选择：

猿，蓝，猫，红，狗，黄，猪，紫，斑马

用户被要求从可能的选择列表中“选择所有的动物”。输出将他们标识的所有项目放在一个单元格中，用逗号分隔。为每个调查条目创建一个新行。

即。

用户1："Ape，Cat，Pig，Purple“

“猫，红，狗，猪，斑马”

用户3 "Ape，Cat，Dog，Pig，Zebra“等。

我有一张桌子上有所有的动物和颜色，有明确的范围。

即。动物= A1:A5，颜色= B1:B4

我需要在一个新的单元格中为每个用户“评分”该单元格。如果得分值是正确识别的项目数的计数，则每一项计算为1分。

即。

用户1："Ape，Cat，Pig，Purple“

“猫，红，狗，猪，斑马”

用户3 "Ape，Cat，Dog，Pig，Zebra“

对于每行的分数单元格，公式需要什么？

我找到了一个前面的线程，它似乎指向正确的方向，Excel: Searching for multiple terms in a cell，但这只会从列表中检查单元格中是否存在任何项，并返回一个true或false。

谢谢大家的帮助！

Answer 1

与SUMPRODUCT的合作：

=SUMPRODUCT(COUNTIF(D2,"*" & $A$1:$A$5 & "*"))

它还有一个限制，即amimals不是子字符串，比如Ant和Ant-Eater。

​

​

如果子字符串有问题，请使用以下命令：

=SUMPRODUCT(--(ISNUMBER(SEARCH(", " & $A$1:$A$5 & ", ",", " & D2 & ", "))))

这将使逗号之间完全匹配。

​

​

Answer 2

如果您不喜欢使用上面的公式，这是非常有效和最理想的，一个更简单但更长的方法如下：

选择动物->Data->文本到列，并将它们分割成列，分隔符为逗号。

一旦完成，在每一列上做一个countif，它将为您汇总它。你需要做20个工作，所以离理想的IE还很远。

=countifs(column which it could be in],[no.1 animal])+
countifs(column which it could be in],[no.2 animal])+...
countifs(column which it could be in],[no.20 animal])

这很容易看出它是如何工作的，如果你收到更多的答案，你将不得不再次将它们分开。