Excel公式练习：真真假假，假假真真——有多少是真？

标签：Excel公式练习

看清楚这个世界，并不能让这个世界变得更好。但可能让你在看清楚这个世界是个怎样的世界后，把自己变得更好。--朱德庸

有时候，简单的问题往往能够有多种实现方法，也能让人更好地了解不同方法的用处和实质。

今天的问题很简单，但当你尝试使用不同函数与公式来解决的时候，你会更容易进一步了解这些函数和方法。

如下图1所示，判断单元格区域A1:C1中是否只有两个TRUE值，并返回TRUE值的数量。

图1

最简单直接的方式就是：

=A1+B1+C1

因为Excel将TRUE值转换为1，FALSE值转换为0，所以上述式子结果如果为2，则表明该区域仅包含两个TRUE值。

还可以使用求和的数组公式：

=SUM(A1:C1*{1,1,1})

或者：

=SUM(A1:C1*{TRUE,TRUE,TRUE})=2

或者直接使用数组函数SUMPRODUCT：

=SUMPRODUCT(A1:C1*{1,1,1})

这两个公式原理没什么区别，只是一个使用数组公式，一个无需按Ctrl+Shift+Enter。

或者更简单：

=SUMPRODUCT(--(A1:C1))

由于是求数量，自然会想到使用计数函数：

=COUNTIF(A1:C1,"TRUE")

或者：

=COUNTIFS(A1:C1,TRUE)

或者：

=COUNT(1/A1:C1)=2

或者：

=MAX(COUNTIF(A1:C1,A1:C1))=2

还可以使用下面的公式：

=IFERROR(2/((A1+B1+C1)=2),"")

有时候除法在公式中很有用，特别是在有意制造错误时。

也可以使用逻辑判断：

=AND((A1*B1+C1)=1,(A1+B1*C1)=1)

或者：

=AND(OR(A1:C1),NOT(XOR(A1:C1)))

或者：

=(A1=B1)+(B1=C1)+(C1=A1)=1

可以使用二进制函数：

=--DEC2BIN(A1+B1+C1)=10

可以使用字符串替换来计算：

=((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"TRUE",""))))/4)=2

或者：

=((LEN(A1&B1&C1)-(LEN(SUBSTITUTE(A1&B1&C1,"T","")))))=2

下面将问题进行拓展，示例数据如下图2所示。在单元格区域A1:C100中包含着TRUE/FALSE值，求各行中仅包含两个TRUE值的行数。

图2

仍然很简单，可先判断再求和，公式如下：

=SUMPRODUCT(--((A1:A100+B1:B100+C1:C100)=2))

或者想到矩阵，将100行3列中每行求和，然后与2比较，求等于2的数即为所要求的行数：

=SUM(--(MMULT(--(A1:C100),{1;1;1})=2))

或者：

=SUM(N(MMULT(--A1:C100,{1;1;1})=2))

使用计数函数：

=COUNT(MATCH(--A1:A100&--B1:B100&--C1:C100,{"011","101","110"},))

这是一个数组公式，输入完后按Ctrl+Shift+Enter组合键。

或者：

=SUM(--(A1:A100*100+B1:B100*10+C1:C100={11,101,110}))

这是一个数组公式，输入完后按Ctrl+Shift+Enter组合键。

也可以使用下面的公式：

=SUMPRODUCT(--(--DEC2BIN(A1:A100+B1:B100+C1:C100)=10))

FREQUENCY函数也可以派上用场：

=SUM(N(FREQUENCY(A1:C100*ROW(A1:A100),(ROW(A1:A100)-1))=2))

你还有什么巧妙的解法？欢迎留言。

注：可以到知识星球完美Excel社群下载本文配套示例工作簿。