Question

EXCEL中，我想输入表达式＝SUMPRODUCT({A1>B1;A1=B1;A1<B1}*{1;2;3}) 错在哪里？

怎样更正函数，或怎样在数组里输入逻辑表达式
目的就是要实现：如果
A1＞B1，C1＝1
A1＝B1，C2＝2
A1＜B1，C3＝3
但现在我输入的这个公式有错误，怎样更正，用IF函数我会，就用SUMPRODUCT，怎样用？

Answer 1

错误的原图是常量数组中不能使用单元格地址，建议改用其它公式。如
=(A1>B1)*1+(A1=B1)*2+(A1<B1)*3
或
=--TEXT(A1-B1,"1;3;2")
如果用SUMPRODUCT，可将下面三个表达式放在单元格中，再用公式引用单元格区域，如
C1输入：=A1>B1
C2输入：=A1=B1
C3输入：=A1<B1
统计公式：=SUMPRODUCT(C1:C3*{1;2;3})

追问

谢谢你：
这些我知道的，是否有别的变通的办法来用SUMPRODUCT来实现，而不用辅助单元格。

追答

非要脱掉裤子放屁吗？可用下面的公式：
=SUMPRODUCT(CHOOSE({1,2,3},A1>B1,A1=B1,A1<B1)*{1,2,3})

追问

虽然态度不是很好，但还是要感谢一下，其实我主要是想学习怎样把函数表达式放到数组里，CHOOSE({1,2,3},A1>B1,A1=B1,A1<B1)对我有帮助，谢谢！
另，=--TEXT(A1-B1,"1;3;2")也对我有启发，可以解释一下为什么吗？或提供关于TEXT函数的格式参数详解说明，麻烦你了。我会选你做最佳答案。

追答

在EXCEL格式设置中，依次是：正数;负数;零;文本。这个规则同样适用于TEXT函数的参数设置。当A1>B1时，A1－B1大于0，为正数，显示为1；小于0时，显示为2；等于0时显示为3。由于TEXT返回的数据是文本格式，所以前面加上“--”（减负——负负得正），同时把文本数字转为数值。

补充：要知道TEXT格式参数，可打开单元格的格式设置列表，选择其中一种格式，然后选自定义，这时自定义格式里的参数，可作为TEXT函数的参数。

Answer 2

{A1>B1;A1=B1;A1<B1}
这样的写法是错误的，大括号形式的数组只能为具体的数据，不能为引用或公式
简单点方法
=2-SIGN(A1-B1)
如果一定要用sumproduct
=sumproduct(choose({1,2,3},A1>B1,A1=B1,A1<B1)*{1,2,3})
这样有意义吗？

追问

=2-SIGN(A1-B1)
有点问题啊，SIGN返回的值只有两个啊，是不是缺少什么了？

追答

SIGN(0)=0

追问

谢谢，你和楼上的回答都很好，我只能按时间排序来选择最佳答案，真心感谢你。

追答

呵呵，没关系

Answer 3

建议你用F9查看这个公式运行的基理。

那这个公式来说：
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
先说COUNTIF函数部分：

我为了描述方便，把范围缩小下。
=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))

COUNTIF(A1:A5,A1:A5）
这部分统计的是对A1：A5范围内，A1单元格内数据重复了多少个，A2…重复…，A3……，……。COUNTIF统计的结果是1列5行的内存数组。
想想看，要是A1重复了2次，那么统计的结果就是2，要是A1没重复，那么就是1。现在假设A1到A5这组数据为
1
2
3
2
1
那么A1到A5分别重复了：2 2 1 2 2
COUNTAIF的结果用F9你势必会看到：

再看整体函数
=SUMPRODUCT(1/COUNTIF(A1:A5,A1:A5))
1/=
SUMPRODUCT的结果是3！

好象解释的很糟糕。
公式是巧妙的借用了数据重复次数为N，那么N*1/N=1。

追问

我这样使用sumproduct函数，提示数组内输入格式错误，应该怎样输入。

Answer 4

你是想实现什么结果啊，我实在没研究明白。再陈情下！

追问

假设A1和A2单元格里有值，在C1单元格里输入＝SUMPRODUCT({A1>B1;A1=B1;A1<B1}*{1;2;3})
目的就是要实现：如果
A1＞B1，C1＝1
A1＝B1，C2＝2
A1＜B1，C3＝3
但现在我输入的这个公式有错误，怎样更正，用IF函数我会，就用SUMPRODUCT，怎样用？

追答

第一次见人想这么用SUMPRODUCT函数，高！