如何在Google Sheets中编写公式判断US/CA/NL/HK是否全部存在于指定列表

你可以用以下几种简洁的公式实现需求，比FIND更适合处理这类多元素存在性判断：

方法1：AND + ISNUMBER + MATCH组合 #

逐个检查每个目标国家是否在列表中，通过MATCH查找位置，ISNUMBER确认找到有效结果，最后用AND确保所有目标都存在：

=IF(AND(
  ISNUMBER(MATCH("US", A1:A31, 0)),
  ISNUMBER(MATCH("CA", A1:A31, 0)),
  ISNUMBER(MATCH("NL", A1:A31, 0)),
  ISNUMBER(MATCH("HK", A1:A31, 0))
), "Yes", "No")

如果你的列表不是放在单元格区域，而是直接用数组，把A1:A31替换成你的国家数组即可：

=IF(AND(
  ISNUMBER(MATCH("US", {"CA","US","MX","AU","IN","JP","NZ","SG","TW","AT","BE","CZ","DK","EE","FR","DE","HU","IE","IT","LV","LT","NL","NO","PL","PT","RO","SK","ES","SE","CH","GB"}, 0)),
  ISNUMBER(MATCH("CA", {"CA","US","MX","AU","IN","JP","NZ","SG","TW","AT","BE","CZ","DK","EE","FR","DE","HU","IE","IT","LV","LT","NL","NO","PL","PT","RO","SK","ES","SE","CH","GB"}, 0)),
  ISNUMBER(MATCH("NL", {"CA","US","MX","AU","IN","JP","NZ","SG","TW","AT","BE","CZ","DK","EE","FR","DE","HU","IE","IT","LV","LT","NL","NO","PL","PT","RO","SK","ES","SE","CH","GB"}, 0)),
  ISNUMBER(MATCH("HK", {"CA","US","MX","AU","IN","JP","NZ","SG","TW","AT","BE","CZ","DK","EE","FR","DE","HU","IE","IT","LV","LT","NL","NO","PL","PT","RO","SK","ES","SE","CH","GB"}, 0))
), "Yes", "No")

方法2：SUM + COUNTIF组合 #

通过COUNTIF统计每个目标国家在列表中的出现次数，求和后判断是否等于4（4个目标都存在的话，每个至少出现1次，总和为4）：

=IF(SUM(COUNTIF(A1:A31, {"US","CA","NL","HK"}))=4, "Yes", "No")

替换成数组版本：

=IF(SUM(COUNTIF({"CA","US","MX","AU","IN","JP","NZ","SG","TW","AT","BE","CZ","DK","EE","FR","DE","HU","IE","IT","LV","LT","NL","NO","PL","PT","RO","SK","ES","SE","CH","GB"}, {"US","CA","NL","HK"}))=4, "Yes", "No")

为什么FIND没成功？ #

FIND函数是用来在单个文本字符串中查找子串的，没法直接处理「多个元素组成的列表」的存在性判断，所以用专门处理区域/数组查找的MATCH或COUNTIF更合适。

内容的提问来源于stack exchange，提问作者Rama Krishna Padhy