Question

excel中，为何我将==INDEX(AM$1:AM$111,SMALL(IF(TRIM($AM$1:$AM$111)<>"",ROW($1:$111),4^9),ROW()),)

改为=INDEX(C$1:C$111,SMALL(IF(TRIM($C$1:$C$111)<>"",ROW($1:$111),4^9),ROW()),)就显示错误呢？

Answer 1

公式改为这样就不会出错了：

=INDEX(C:C,SMALL(IF(TRIM(C$1:C$111)<>"",ROW($1:$111),4^8),ROW()))&""

如果C1:C111这个区域是不会存在空格（注：空格跟空白不一样）的情况，公式还可以精简点：

=INDEX(C:C,SMALL(IF(C$1:C$111<>"",ROW($1:$111),4^8),ROW()))&""

原公式一旦下拉的行数超出了C列不等于空的行数，公式就会返回#REF的错误值！

例如，C列有9行是有数据的，公式下拉至第10行即开始出错，并返回#REF错误值!

这主要是因为公式前面部份=INDEX(C$1:C$111   与后面的4^9（一般是4^8）这不对称引起的，这两部份相当行数。

公式在一开始就固定在C1:C111共111行这个区域中，但后面的4^9=262144行，前面是要求固定读取范围在111行，后面却要求读取第262144行的数据，后者不能大于前者，一般是两者是相等的，可现在相差巨大啊。

如果C列有正常结果，即文字，也有非正常结果    #N/A，公式修改为这样就行了：

=INDEX(C:C,SMALL(IF(ISERROR(TRIM(C$1:C$111)<>""),4^8,ROW($1:$111)),ROW()))&""

追问

我在g1输入，然后下拉在g1就出错了

c列有16000行。
都需要运算
4^9是什么意思请问

追答

我上面的回答中有写明的 4^9就是4的9次方，等于262144  就是第262144行，EXCEL2003最大支持65536行，所以这个数字为了兼容EXCEL 2003，一般只用4^8=65536。

那公式我这边测试是可以用的，那是数组公式，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束。

提问中是111行，现在追问变了16000行，公式改这样，不过数据增大了很多，公式用多了，表格运行速度会变慢的哦。

=INDEX(C:C,SMALL(IF(ISERROR(TRIM(C$1:C$16000)<>""),4^8,ROW($1:$16000)),ROW()))&""

追问

整个百度知道你是数一数的牛，这个问题问了很多年，今天终于圆满解决了！希望下次你还来照顾我的生意！晚安！

Answer 2

如果你改动之前是可以正常运行的话，那么请检查一下是否前面漏了一个等号，还有检查一下是否引用单元格有误，其中单元格描述如：C$1或者$C1都是属于相对引用，复制公式后所代表的单元格是会变化的，而$C$1则是绝对引用，复制公式后所代表的单元格是不会变化的！如果检查无误，请确认公式录入完成后你是否使用Ctrl+Alt+Enter结束的！

追问

原来绝对引用的我没有改，相对引用我肯定改了。三键操作

Answer 3

公式没问题啊
除非C$1:C$111全部是空白单元格

追问

C列有正常结果，即文字，也有非正常结果 #N/A

追答

截图或者放例表上来看看

不要让别人靠猜测来帮你解决问题