3天了，公式的结果自动变成#SPILL!，终于有解了

图片

先来了解概念，当然也可以直接忽略，直接看最后的解决方法。A:A这种写法，代表A列，低版本的Excel或WPS没有动态数组，会自动从公式的当前单元格，与你多引用的区域做“隐式交集运算”。

比如在非动态数组版的Excel中：

一、B2单元格，直接写公式：=A1:A10（结果是10个单元格才能容纳的数组集合）而B2单元格容纳不下，结果就会产生隐式交集，返回与公式所位于行相关联的值（A2)；如果是在B11单元格写=A1:A10，由于11行与1:10无法产生交集，会返回#Value错误。

二、再比如在C9单元格中写下 =VLOOKUP(A:A,F:G,2,0)，公式也会先将C9与A:A区域进行隐式交叉（把第一参数换成A9) 。

目前的新版WPS，与Office365都升级了动态数组，在B2单元格，写公式 =A1:A10，结果就会自动向下(或右)溢出成10个单元格的数组区域。

查找值标准写法是A2，而不是A:A，切记！=VLOOKUP(A2,D:I,4,0)

图片

除了VLOOKUP，还有很多函数的条件，都要写一个单元格，别用A:A，比如SUMIF。直接引用A列，会弹出警告对话框。

图片

只有标准的写法，才能正常。=SUMIF(D:D,A2,G:G)

图片

VIP学员看到这里，就觉得新版本不方便。其实大错特错，新版本是在教你正确写公式，而且也提供了很多超好用的函数，能够大大的提高工作效率。以前需要很复杂的公式才能解决，现在轻而易举就能解决。

1.数组公式，不用再按三键结束了

对于很多新手，经常会忘记按三键导致公式结果出错，现在再也不会出现这种问题了。

比如，现在要统计每个月的金额。

普通版本，需要按Ctrl+Shift+Enter三键结束，按完公式会自动出现{}。

图片

新版本，直接回车就搞定了。

=SUM((MONTH($C$2:$C$18)=F2)*$D$2:$D$18)

图片

2.用UNIQUE函数就可以提取不重复

普通版本，用公式是相当麻烦的。

=OFFSET($A$1,MATCH(0,COUNTIF(H$1:H1,$A$2:$A$19),0),0)&""

图片

新版本，为了表示神奇，我用动画演示。只需在一个单元格输入公式，回车以后会自动扩展区域，并提取不重复。

=UNIQUE(A1:A18)

图片

除了可以针对一列，同时也可以针对多列，比如针对公司名称和软件提取不重复。

=UNIQUE(A1:B18)

图片

3.不重复计数

UNIQUE可以提取不重复值，怎么进行不重复计数？

图片

那太简单了，再嵌套个COUNTA统计个数就行。

=COUNTA(UNIQUE(B2:B18))

图片

那如果是按公司名称、软件2个条件不重复计数呢？

同样简单，改下区域，再除以2就可以。

=COUNTA(UNIQUE(A2:B18))/2

图片

4.用SORT函数对内容自动排序

对月份降序。

=SORT(F2:G4,1,-1)

图片

语法说明：

=SORT(区域,对第几列排序,-1为降序1为升序)

比如现在要对金额升序。

=SORT(F2:G4,2,1)

图片

5.凭证自动生成的最简单公式

图片

以前卢子分享过凭证自动生成的方法，不过实在太繁琐了，详见：凭证自动生成，太难了？

不需要锁定区域，也不需要下拉和右拉公式，也不需要三键，啥都不需要。只需在一个单元格输入公式，就自动扩展，简单到没朋友。

=FILTER(C2:G11,B2:B11=D14)

图片

语法说明：

=FILTER(返回区域,条件区域=条件)

6.找不到对应值，不用再嵌套IFERROR

正常情况下，用VLOOKUP或者LOOKUP查找的时候，找不到对应值会显示#N/A，一般情况下需要嵌套IFERROR。

而XLOOKUP即便是找不到对应值，也不需要嵌套其他函数。

=XLOOKUP(E2,A:A,B:B,"")

图片

语法说明：

=XLOOKUP(查找值,查找区域,返回区域,错误值显示值)

7.将查找到的所有对应值去除重复，再合并在一个单元格

这个前阵子帮学员写了一个公式，套了又套，挺复杂的。现在有了新函数，那一切就不一样了。

=TEXTJOIN(",",1,UNIQUE(FILTER($A$2:$A$18,$B$2:$B$18=F2)))

图片

这个就相当于将前面学的函数综合起来，FILTER就是将符合条件的筛选出来，再用UNIQUE去除重复值，最后用TEXTJOIN将内容合并起来。

8.多表查找

要根据E1的姓名，在4个分表查找相应的数据。

图片

分表的格式都一样。

图片

完美的公式，区域嵌套VSTACK函数，就可以一次引用所有表，超级简单。

=VLOOKUP($E$1,VSTACK(生产:行政!$A$1:$G$99),COLUMN(A1),0)

图片

对于VSTACK函数，很多人都比较陌生，卢子再通过一些案例详细说明，语法跟SUM函数类似。

将2个区域的内容合并

=VSTACK(A1:D5,F2:I4)

图片

同理，如果是3个区域，就再将区域写上即可。区域怎么摆放都行，不影响合并。

=VSTACK(A1:D5,F2:I4,F7:I9)

图片

语法：

=VSTACK(区域1,区域2,区域3)

如果区域的内容有重复，想去掉重复也可以，再嵌套UNIQUE函数。

=UNIQUE(VSTACK(A1:D5,F2:I4,F7:I9))

图片

将2个工作表的内容合并

=VSTACK(生产:品质!A1:D5)

图片

语法：

=VSTACK(开始表格:结束表格!区域)

现在要查找某个人，直接用VLOOKUP函数查找合并后的区域就可以。

=VLOOKUP($F2,$A$1:$D$9,COLUMN(B1),0)

图片

看到这里，应该明白VSTACK函数的作用，就是将所有表格合并在一起构造成一个新的表格，从而可以正常查找。

9.字符分离

从科目里面将末级科目、一级科目提取出来。

图片

先来看早期的解决公式，都挺复杂的。末级科目：=TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",50)),50))一级科目：=LEFT(A2,FIND("-",A2)-1)有了新函数，问题变得更简单了。末级科目：=TEXTAFTER(A2,"-",-1)

图片

一级科目：=TEXTBEFORE(A2,"-",1)

图片

而TEXTSPLIT是按分隔符号将所有内容分开，类似于分列。=TEXTSPLIT(A2,"-")

图片

这个函数还有一个作用，计算人数。姓名用分隔符号隔开，早期的公式挺麻烦的。=LEN(A2)-LEN(SUBSTITUTE(A2,"、",""))+1

图片

现在简单多了。=COUNTA(TEXTSPLIT(A2,"、"))

图片

时代在进步，现在写复杂公式的机会越来越少了，很多问题结合新函数都能轻易解决，完全不伤脑筋。

你还知道新版本有啥好用的函数或者功能？

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。