别再为WPS中的#SPILL!错误崩溃,一篇讲透!

E精精
E精精

WPS函数专家

WPS在12月左右更新了动态数组,这本是一个好事,但是很多过去不严谨的写法,导致更新后出现的各种报错问题,其中最典型的就是VLOOKUP整列导致的“#SPILL!”错误!

比如很多新手,使用VLOOKUP函数,在过去喜欢整列引用,在没更新动态数组的情况下,这个公式90%情况下是不报报错的,但是并不表示用法正确!

▼ 就是下面这玩意

▍隐式交叉

下面是更新前的案例演示。正常需求都是查询内容和结果在同一样,这样是没有问题,这个过去的“隐式交叉”!

但是如果我们查询内容和需要的结果不在同一行呢?结果就报错了!

如果我们引用整列或者多个单元格区域,返回区域也就是引用区域和当前行的交叉点的单元格内容

下面就是更新前的演示,G2单元格的查询内容,引用了整列和当前行交叉,正好是A2单元格,所以返回的是A2对应的查询结果,其实我们想要的是A1的结果,也就是这种写法,没办法实现错位显示、或者显示在下面等位置都是不行的,只能同一行!

正确的解法是这样,过去VLOOKUP第一参数也不支持单元格数组!我们应该单独写一个查询的单元格!

▼ 正确写法

那么更新后为什么会报错呢?更新动态数组后,VLOOKUP函数第一参数支持数组了,也就是可以依次返回多个结果!

▍#SPILL!错误详解

SPILL直接翻译就是溢出,这是一个新的函数错误类型,一句话来说就是公式的结果无法正常溢出,大部分情况都是由于有阻挡!

比如下面ROW(1:3)需要一个3行一列的区域来显示结果,但是下面有个2挡住了,区域不够,扩展不开,导致错误!

点击小绿帽,也能看到错误提示,虽然显示两个提示,但是本质就一个,就有阻挡,扩展不开

了解了,我们来看开头的问题,为什么会出现错误,就明了了吧!

第一参数引用整列,那么结果也应该是整列和A列对应显示,但是我们公式写在了E2,也就是第二行,比需要的行数少了1,导致扩展不开!

当你把公式放在第一行,公式不再报错了,对应的姓名成绩也能查询出来了,但是这样写非常不好的习惯,我们查询只有4行,写整理就是返回整列查询,Excel有1048576行,冗余太卡,这样的公式太多,分分钟卡死!

除了上面的错误,其实还有一种特殊情况,也会导致这样的错误,也就是使用随机数的情况,可能是内部处理问题,WPS目前不支持随机函数结果溢出。给出的说明是溢出区域未知

同样的函数公式,只有一个结果是不会报错,其他多值随机都会返回#SPILL!错误!

这样的公式,在OFFICE中一般不会出错,但是在一些随机数据源用其他函数处理时OFFICE中也会出错!

比如这样,又作为其他的数据源,有一定的概率出现溢出错误!

下面是正常不出错的结果,也就是说部分正常,部分情况出错,不稳定

问题应该说清楚了!下面我们来看看处理方案!

▍处理方案

针对VLOOKUP函数引用整理的问题,我们只要加一个"@"符号即可,这个算是代替过去的“隐藏交叉”,因为现在支持动态数组了!

上面虽然可以解决问题,但是这并不是我们推荐的写法,因为第一参数支持多单元格,直接引用多单元格区域,结果自动溢出,不用下拉,一次搞定!这也就是我们开头说到的动态数组更新是一件“好事”!更加方便,也更容易理解数组!

▼动画演示

▍文章小结

1、“#SPILL”错误主要有两个方面,一是多个值结果因为有阻挡扩展不开,其次是随机数扩展区域未知,主要关注第一种!

2、错误处理方法:选择合适的查询区域,其次,如果确实要返回多值,清理一下函数公式周围区域,留有足够的扩展区域。

3、“@”符号用于代替过去的“隐式交叉”,返回当前行的交叉点,用于数组,返回数组的首个内容(补充)

大家反响这么激烈,主要是新事物不了解和过去不严谨的写法导致,其实了解后,你会发现这波更新,真的是非常好的事情,数组公式多值结果更加直观可读,过去FILTER等函数也可以直接使用,而不再需要“三键”录入。

更多有关动态数组相关知识!欢迎一起交流探讨!

您觉得动态数组更新是好是坏
好事
19 (90%)
遗憾
2 (10%)
21人参与 投票已截止
江苏省
浏览 15946
5
33
分享
33 +1
32
5 +1
全部评论 32
 
Vincent༽
以前多么自由的公式。 现在到处溢出,难用的要死。
· 江苏省
1
回复
 
YH
说了一堆,啥也没看懂。意思就是表格做得不好。以前EXCEL一直这么用,到你这就不行了,到处报错,神经病。典型的没有生活工作经验,不会研发就不要乱七八糟的改。这是我时隔10年再次愤怒吐槽WPS,垃圾!!!
· 浙江省
1
回复
 
漩涡卡洛特
学习
· 北京
回复
 
A谢银波A
撇嘴打卡,求提高使用次数多上线。我的文章写到紧要关头,次数不够了,哇哦,可以单买吗?撇嘴撇嘴
· 江苏省
回复
 
Waiting
· 湖北省
回复
 
水墨染青花
· 四川省
回复
 
奶油泡泡子
· 重庆
回复
 
答案
· 浙江省
回复
 
wils
wils

创作者俱乐部成员

和fetch一样,盼了很多年了,有了动态数组和fetch,才算是超越excel
· 广东省
回复
不用学习考第一
旧公式添加@符号就可以显示以前支持现在不支持的公式。
· 福建省
回复
 
优越
· 山东省
回复
E精精
E精精

WPS函数专家

· 江苏省
回复
 
丁功令
丁功令

创作者俱乐部成员

· 四川省
回复
 
雨
学海无涯苦作舟,学到了
· 宁夏
回复
 
goodwatera
看到支持动态数组立马更新了,今天刚好遇到这个错误。 主要是他不支持表格里面的filter。就是插入一个表格的话就不能用了。 希望更新高级筛选自动刷新功能
· 福建省
回复
E精精
E精精

WPS函数专家

表格不支持 动态扩展
· 江苏省
回复
 
Mr Chen
Mr Chen

创作者俱乐部成员

学歘
· 甘肃省
回复
 
亂雲飛渡
很好
· 广东省
1
回复
 
wps新路
wps新路

WPS函数专家

很多用户觉得能用就不要动,动就不对,这.....哎
· 重庆
回复
E精精
E精精

WPS函数专家

不乱好坏,对新事物人们总是畏惧的!
· 江苏省
回复