Excel公式练习：求三列数值之和等于指定值的组合数

引言：本文的练习整理自chandoo.org。多练习，这是我们从小就在使用的学习方法。在练习的过程中，认真思考，不断尝试，以此来磨练自己的公式与函数应用技能，也让研究Excel的大脑时刻保持着良好的状态。同时，想想自己怎么解决这个问题，看看别人又是怎样解决的，从而快速提高Excel公式应用水平。

Q：有3列数据，每列有10个数值，从每列中取1个数值，这3个数值相加的和为20，有多少种组合。示例数据如下图1所示。

图1

要求编写一个公式，返回其组合数。

规则：列A中的数值只能使用1次，其他两列数值可任意使用。

写下你的公式。

A：确实很具有挑战性。

公式1：数组公式。

=COUNT(1/MMULT(N(SMALL(B2:B11+TRANSPOSE(C2:C11),COLUMN(A:CV))+A2:A11=20),ROW(1:100)))

公式中：

B2:B11+TRANSPOSE(C2:C11)

会得到由列B中的数值与列C中的数值相加的所有结果。

COLUMN(A:CV)

生成一个由1至100的连续数字组成的数组。

上述两个数组作为SMALL函数的参数，由小到大依次取得列B与列C数值相加所有结果组成的数组，共100个值，然后，将列A中的值与之相加，得到一个100×10行的数组，这是3列所有数值组合相加的结果。将这个结果与指定的数值20比较，得到一个由TRUE/FALSE值组成的数组，其中的TRUE值就是3列中数值相加和为20的值所在位置。使用N函数将这个数组转换成由1/0组成的数组，每行中1的位置就是值为20所在的位置。ROW函数生成由1至100的连续数值组成的垂直数组。MMULT函数将上述两个数组相乘，得到一个100行1列组成的数组，由0和N函数生成的数组中每行数值相加的和的值组成的数组，这个数组被1除，生成一个由#DIV/0!和小数组成的数组，再由COUNT函数统计这个数组中数值的个数，即为组合数。

注意，公式通过将N函数生成的1/0数组与ROW函数生成的垂直数组相乘，巧妙地将每行中有多个20的结果转换成了1个，从而满足列A中的数值只能出现1次的要求。

公式2：数组公式。

=COUNT(IF(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100)),0),A2:A11,""))

或

=COUNT(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100))))

=COUNT(IF(MATCH(20-A2:A11,SMALL(B2:B11+TRANSPOSE(C2:C11),ROW(A1:A100)),0),A2:A11,""))

大家可以使用F9键或者公式求值功能自行解析，以理解公式的运作原理，从而更好地理解公式和所使用的函数。