有没有办法在 Excel 2010 中使用名称范围作为 sum(countif()) 公式的数组条件

Question

图片 A 列充满了评论，就像调查问题的反馈一样。现在图片 B 列有一个公式，用于计算每条评论中特定关键词的出现次数。我目前在 B 列中使用这个公式：SUM(COUNTIF(A27,{"劳伦斯伯克利国家实验室“，”劳伦斯·伯克利“，”低层基底","劳伦斯实验室*"}))

由于列表会在此过程中不断增长和缩小，并且因为我最终会得到多个类似的公式（针对不同的类别），所以我想在命名范围内控制列表并从那里引用它。

现在假设我的列表如下所示，并且名称范围为 search_items1

• 劳伦斯伯克利国家实验室
• 劳伦斯·伯克利
• 低层基底
• 劳伦斯实验室

我的公式将看起来像 SUM(COUNTIF(A27,search_items1))。

请注意，使用 * 作为通配符会带来另一个挑战，但即使没有 *，我也无法使上述公式起作用。有没有办法让它起作用？使用通配符 * 的解决方案将是理想的。

或者，我可以引用从名称范围连接在一起的一个单元格，如下所示：{“劳伦斯伯克利国家实验室“，”劳伦斯·伯克利“，”低层基底“，”劳伦斯实验室*“}。我尝试这样做，但公式将其解释为一个文本块。

我尝试了多种语法变化，并进行了无数次 Google 和超级用户搜索。请帮忙。

Answer 1

您的公式的作用是：

=SUM(COUNTIF(A27,{"LBNL","Lawrence Berkeley","LBL","Lawrence Lab*"}))

LBNL如果单元格 A27 是、或Lawrence Berkeley或LBL或Lawrence Lab*（*此处充当通配符），则计数为 1。

因此，如果单元格为LBNL，则结果为 1。为 则Dr. LBNL结果为 0。

如果要获取1单元格包含至少 1 的计数LBNL，则需要在文本的两边使用通配符，即：

=SUM(COUNTIF(A27,{"*LBNL*","*Lawrence Berkeley*","*LBL*","*Lawrence Lab*"}))

您可以创建一个命名范围并将其放在那里，但这将成为一个仅适用于Ctrl++Shift的数组公式Enter：

=SUM(COUNTIF(A27,search_items1))

现在，您可以使用SUMPRODUCT此公式来避免使用 CSE：

=SUMPRODUCT(COUNTIF(A27,search_items1))

但是如果您有一个包含的单元格LBNL LBNL，并希望结果为2，那就另当别论了，因为COUNTIF一旦在单元格中找到所寻找的内容并返回， 的工作就完成了1。

在这种情况下，我建议计算每个要检查的单元格（因为上述函数可用于查看整列）：

=SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,search_items1,"")))/LEN(search_items1))

[现在请注意，由于工作方式的原因，您需要删除星号SUBSTITUTE。]

Answer 2

它不一定总是公式。它也可以是一个范围。这可以让你保持电子表格的整洁。

您可以通过名称管理器定义命名数组。从公式功能区调用名称管理器，创建一个新名称。给它一个清晰的名称（例如“BinWithMarks”），然后将其粘贴到“引用：”字段中：

={100,89,84,79,74,69,64,59,54,49,39,0}

类似地，您可以按如下方式添加相关成绩，并使用成绩作为名称：

={"A+","A","A-","B+","B","B-","C+","C","C-","D","E"}

确认您的选择后，您可以使用该数组通过索引和匹配公式的组合来查找学生的相关成绩。

=INDEX(Grades,MATCH(N11,BinWithMarks,-1))