代码之家 › 专栏 › 技术社区 › franciscofcosta

具有唯一值的COUNTIFS Excel

countif sum excel

franciscofcosta · 技术社区 · 7 年前

我试图计算Excel表格中出现不同字符串的次数。目前在表1中的示例表如下:

我在另一个电子表格中有另一个表格,我想指出,对于表1左侧的每个字母,右侧出现了多少“za”、“zc”或“zd”条目。然而

最终结果,在表2的B行,必须是这样的:

SUM COUNTIFS 更具体地说,应用于该示例,我使用以下公式:

=SUM(COUNTIFS(Sheet1!A1:A18,Sheet2!$A1,Sheet1!B1:B18,{"za","zc","zd"}))

公式正在做一些预期的事情。然而,它并不是只计算一次每个条目。相反,它正在计算左侧每个字母的“za”、“zc”或“zd”的每个条目。公式返回的表格如下:

我如何改变公式,使其符合我的意愿?

2 回复 | 直到 7 年前

Erin Halbmaier 7 年前

我最初的想法是:

 =SUM(MIN (1,COUNTIFS(Sheet1!A1:A18,Sheet2!$A1,Sheet1!B1:B18,{"za","zc","zd"}))

编辑:MIN函数取COUNTIFS数组中至少1个或所有项,而不是COUNTIFS数组中至少1个和每个项,这是我担心的。使用

=MIN(COUNTIFS(Sheet1!A$1:A$18,Sheet2!$A1,Sheet1!B$1:B$18,"za"),1)+MIN(COUNTIFS(Sheet1!A$1:A$18,Sheet2!$A1,Sheet1!B$1:B$18,"zc"),1)+MIN(COUNTIFS(Sheet1!A$1:A$18,Sheet2!$A1,Sheet1!B$1:B$18,"zd"),1)

将获得预期结果。它有点笨重,但比数组公式简单。如果需要数组公式,可以使用:

=SUM(FREQUENCY(IFERROR(MATCH({"za","zc","zd"},(IF(Sheet1!$A$1:$A$18=$A5,Sheet1!$B$1:$B$18)),0),""),IFERROR(MATCH({"za","zc","zd"},(IF(Sheet1!$A$1:$A$18=$A5,Sheet1!$B$1:$B$18)),0),"")))

这使用频率函数获取一组值,并查看另一组值中有多少项落在每个数据范围内。由于您需要文本而不是数字,因此我们使用MATCH函数在值第一次出现在列表中时查找,如果没有,则使用IFERROR函数返回“”。(我们只需要第一次出现,因为您不想知道出现了多少次)。因为它是文本,所以我们对频率的两个参数使用相同的输入。

因此,如果需要更改正在搜索的值或搜索的范围,请确保同时更改这两个值!或者,你可以在某个地方列出这些值,比如在F1:F3中,并为其创建一个命名范围,另一个为A1:A18,另一个为B1:B18。然后,你的公式将如下所示:

=SUM(FREQUENCY(IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),""),IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),"")))

然后只需更改命名范围定义,公式就会更新。:-)

注意:由于这是一个数组公式,因此必须按CTRL+SHIFT+ENTER键关闭单元格,而不是只按ENTER键。当你看到公式栏时,你应该看到

{=SUM(FREQUENCY(IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),""),IFERROR(MATCH(SearchValues,(IF(colA=$A2,colB)),0),"")))}

你自己输入花括号是不起作用的

A.S.H 7 年前

B1 并填写:

B1:
=SUMPRODUCT(((Sheet1!$A$1:$A$18=A1)*(Sheet1!$B$1:$B$18= {"za","zc","zd"}))/
  COUNTIFS(Sheet1!$A$1:$A$18,Sheet1!$A$1:$A$18,Sheet1!$B$1:$B$18,Sheet1!$B$1:$B$18))