excel 数组公式引用列求值

Question

大神们早上好。。

现有一个数组公式如下：
=INDEX(B:B,SMALL(IF((A$1:A$100={"ABC","BCD","CDE"})*(C$1:C$100<>0),ROW($1:$100),4^8),ROW(A1)))&""

然后得到的B列值如下：
2
3
4
2
3
1
2
1

但是我求B列的和时得到的是空值。。。公式为=sum(B1:B100)

请问该如何修改呢。。。多谢。。

Answer 1

我不看你的话了 我把公式详细的说一遍
首先是数组公式 输入 修改后要按CTRL+SHIFT+回车

第一层 IF(L3="","",....)

就是如果L3是空那公式就显示空 要不就返回后面的公式 就是 下面这个咯

INDIRECT(ADDRESS(SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)),COUNTIF($L$3:L3,L3)),RIGHT(SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)*1000+COLUMN($A$3:$I$32)),COUNTIF($L$3:L3,L3)),3)-1))

indirect 是引用的意思

ADDRESS是返回一个单元格的地址的意思

连起来就是引用ADDRES返回的单元格里面的数据的意思

address分 行 和列 2个参数

SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)),COUNTIF($L$3:L3,L3))是你的行

RIGHT(SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)*1000+COLUMN($A$3:$I$32)),COUNTIF($L$3:L3,L3)),3)-1)

是你的列

如果行等于1 列等于2

那么INDIRECT(DEERESS(1,2))

就是 1 行 2列 就是B2了

好了 现在INDIRECT addres 说好了 主要讲里面的2个参数返回的原理

SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)),COUNTIF($L$3:L3,L3))

意思是 取出IF($A$3:$I$32=L3,ROW($A$3:$I$32))这里面的第COUNTIF($L$3:L3,L3)小的数字

IF($A$3:$I$32=L3,ROW($A$3:$I$32))意思是如果$A$3:$I$32=L3 那么就返回$A$3:$I$32的行号

这里其实可能不止一个相等 所以返回的是一个数组

后面COUNTIF($L$3:L3,L3) 意思是计算$L$3:L3 里面有几个L3 这里是1个 但走你公式看 下拉后$L$3:L3 会变成4 5 6 那么肯定就不是一个了

结合前面的我理解是返回$A$3:$I$32=L3 里面行号最大的那个行号 COUNTIF($L$3:L3,L3)是相同的数量 SMALL符合条件里面第“ 相同数量” 最小的一个

假设4个相同 那返回第4小的 那么就是最大的咯

下面是列

RIGHT(SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)*1000+COLUMN($A$3:$I$32)),COUNTIF($L$3:L3,L3)),3)-1)

里面SMALL(IF($A$3:$I$32=L3,ROW($A$3:$I$32)*1000+COLUMN($A$3:$I$32)),COUNTIF($L$3:L3,L3))类似上面的解释你应该可以理解

RIGHT意思是取最右面3个

最后减 1 是最外层的 就是RIGHT后减1
不知理解否？

Answer 2

因为你的公式中，为了使不符合条件引用的空单元格不显示0，公式加了“&""”，使数字变为文本格式。
文本数字是不能用SUM求和的。
方法1、把原公式的“&""”去掉，拖公式时，拖到出现0为止。
（或者在工具——选项——视图中，将“零值”的选勾去掉。）
方法2、求和公式用=SUMPRODUCT(1*B1:B100)
（数据区域不能包含非数字文本。）

Answer 3

将公式改为：
=--(INDEX(B:B,SMALL(IF((A$1:A$100={"ABC","BCD","CDE"})*(C$1:C$100<>0),ROW($1:$100),4^8),ROW(A1)))&"")
然后再求和就好了。

Answer 4

公式改为=--INDEX(B:B,SMALL(IF((A$1:A$100={"ABC","BCD","CDE"})*(C$1:C$100<>0),ROW($1:$100),4^8),ROW(A1)))&""

Answer 5

B列是文本？