excel单元格包含某特定文字，则输出是某种内容，如何做公式？

Question

比如excel里A列中出现爸爸、妈妈或者爷爷，则B列显示“人物”；A列出现小猫或者小狗，则B列显示“动物”，若同时出现人物、动物，则显示“都有”请教大神，谢谢！！！

Answer 1

在B2中输入或复制粘贴下列公式

=IF(AND(OR(ISNUMBER(FIND(D$2:D$4,A2))),OR(ISNUMBER(FIND(D$5:D$6,A2)))),"都有",IF(OR(ISNUMBER(FIND(D$2:D$4,A2))),"人物",IF(OR(ISNUMBER(FIND(D$5:D$6,A2))),"动物","")))

或

=IFS(AND(OR(ISNUMBER(FIND(D$2:D$4,A2))),OR(ISNUMBER(FIND(D$5:D$6,A2)))),"都有",OR(ISNUMBER(FIND(D$2:D$4,A2))),"人物",OR(ISNUMBER(FIND(D$5:D$6,A2))),"动物",1,"")

下拉填充

Answer 2

=IF(AND(SUM(COUNTIFS(A2,"*"&{"爸爸","妈妈","爷爷"}&"*")),SUM(COUNTIFS(A2,"*"&{"小猫","小狗"}&"*"))),"都有",IF(SUM(COUNTIFS(A2,"*"&{"爸爸","妈妈","爷爷"}&"*")),"人物",IF(SUM(COUNTIFS(A2,"*"&{"小猫","小狗"}&"*")),"动物","无")))

公式有点长不过都是重复的，想要添加条件直接在大括号里面加上去就可以了，用英文逗号隔开。and函数里面的条件要保证跟外面的if函数条件一样就可以，改好一个直接复制过去