第1章了解动态数组
WPS动态数组功能是一种新的数据处理方式，在使用公式处理或分析数据时，在动态数组的加持下会变得更加灵活、直观，并简化了对数据集进行计算的过程，动态数组的溢出功能可以更加简单、快速、优雅地实现相同需求。在操作上只需要输入一个公式，无须按Ctrl+Shift+Enter组合键确认，也无须下拉或双击填充操作，即可将计算后数组结果返回到指定的单元格中。
1.1 从了解动态数组开始
在讲解动态数组之前，需要先了解在没有动态数组功能前表格中的两种公式。
第一种是常规的公式。函数或公式通过计算返回一个值，在单元格中输入公式即可显示计算结果。在D3单元格输入公式，函数返回B3:B7单元格区域的合计值15，如图1-1所示。
=SUM(B3:B7)

图1-1 输入求和公式
第二种是数组公式。当需要公式计算或返回多个值时，需要使用数组公式来实现。数组公式又分为区域数组和内存数组，其中，区域数组公式需要先选中多个单元格，输入公式后同时按Ctrl+Shift+Enter组合键确认。选中D3:D7单元格区域后输入公式，然后同时按Ctrl+Shift+Enter组合键，公式依次对B3:B7单元格区域进行计算后返回5个计算结果，依次填充到D3:D7单元格区域，如图1-2所示。
=B3:B7*2

图1-2 输入区域数组公式
内存数组公式是在一个单元格输入公式，在使用公式计算时对多个值进行计算，计算后使用聚合函数或引用函数返回一个值，在输入公式后也需要同时按Ctrl+Shift+Enter组合键确认。如求“A组”最大数量，在E3单元格输入公式，然后同时按Ctrl+Shift+Enter组合键，如图1-3所示。IF函数依次对B3:B7单元格区域进行判断，如果等于“A组”则返回对应的C3:C7单元格区域，如果不等于则返回空文本，然后使用MAX函数求最大值，结果为3。
=MAX(IF(B3:B7="A组",C3:C7,""))

图1-3 输入内存数组公式
可以看到，在输入数组公式后，软件会自动给公式加上大括号，用于区分正常公式。两种数组公式也都有各自的缺点：区域数组公式不够灵活，很多时候在公式计算前，我们无法得知公式返回数组的大小，导致无法选择合适的单元格区域，又或者当我们需要选择一个比较大的单元格区域时，选择单元格区域后输入数组公式的操作也是很不方便的；内存数组虽然没有选取单元格区域的问题，但是由于有些函数自身支持数组，无须按Ctrl+Shift+Enter组合键也可以正常计算，有些函数不支持数组需要按Ctrl+Shift+Enter组合键才可计算，导致用户学习成本太高，大部分用户依旧无法熟练地使用，此外内存数组计算效率也很低，如果在工作表中大量使用内存数组公式，每一次计算都需要很长时间，从而使表格变得卡顿，使用体验很差。
在了解数组公式后，开始学习动态数组功能。动态数组功能是指软件可以根据公式返回的多个值，自动向下向右溢出，把公式返回的多个值对应填充到多个单元格中。
如在E3单元格输入公式，如图1-4所示。
=B3:C7
输入公式后，软件即可自动把B3:C7单元格区域5行2列10个值自动填充到从E3单元格开始向下5行向右2列区域。

图1-4 动态数组溢出
相同需求下动态数组溢出功能可以更简单、更快速、更优雅地实现，输入一个公式即可，无须按Ctrl+Shift+Enter组合键确认，无须用鼠标下拉单元格或双击填充公式，特别在一些新函数的加持下，可以简化之前很多复杂的公式，以及可以实现很多在没有动态数组功能之前，只能通过VBA、JSA编程才可以实现的功能，并且在溢出功能的特性下计算效率也有很大的提升。
1.2 神秘的@和#符号
1.隐式交集运算符：@运算符
该运算符用于支持动态数组功能，以确保旧版本表格公式能够正常显示。当在支持动态数组的软件中打开使用旧版本编写的公式时，如果公式存在隐式交集运算，系统会自动在引用单元格前添加@运算符，如图1-5所示。

图1-5 自动加隐式运算符
因为VLOOKUP函数中的第1个参数引用的E列为隐式交集运算，所以在支持动态数组功能版本软件中打开时，会自动添加@运算符。
1）隐式交集运算
在旧版本的软件中，因为没有动态数组功能，一个单元格中只能接收一个返回值，在公式引用单元格区域时，如果引用了多个单元格区域，会自动触发隐式交集运算，强制返回一个单值。隐式交集运算规则如下。
（1）如果引用一个单元格，则返回该单元格值。
在F3单元格输入公式，如图1-6所示。
=E3

图1-6 引用一个单元格
使用公式计算后返回E3单元格的值“飞鱼”。
（2）如果引用多个单元格区域，则返回与公式位于同一行或同一列中的单元格中的值。
在F3单元格输入公式引用E列，如图1-7所示。
旧版本输入：
=E:E
支持动态数组功能输入：
=@E:E

图1-7 引用多个单元格区域（E列）
公式中引用E列，因为是在F列第3行F3单元格输入的公式，所以隐式交集运算后返回E列第3行E3单元格的值“飞鱼”。
在F3单元格输入公式，引用第2行，如图1-8所示。
旧版本输入：
=2:2
支持动态数组功能输入：
=@2:2

图1-8 引用多个单元格区域（第2行）
公式中引用第2行，因为是在F列第3行F3单元格输入的公式，所以隐式交集运算后返回F列第2行F2单元格的值“分数”。
（3）在F3单元格输入公式，引用B4:C7单元格区域时，如图1-9所示。
旧版本输入：
=B4:C7
支持动态数组功能输入：
=@B4:C7

图1-9 引用多个单元格区域（B4:C7）
当公式引用的单元格区域和输入公式的单元格无交集时，返回错误值#VALUE!。
2）如果值为数组，则返回数组左上角值
在F3单元格输入公式，引用一个常量数组，如图1-10所示。
旧版本输入：
={1;2;3;4}
支持动态数组功能输入：
=@{1;2;3;4}

图1-10 引用常量数组
在F3单元格输入公式后同时按Ctrl+Shift+Enter组合键，引用B5:C7单元格区域，如图1-11所示。
=B5:C7

图1-11 使用内存数组公式
因为是内存数组公式，所以公式返回的是一个3行2列的数组中左上角B5单元格的值“飞鱼”。在支持动态数组功能新版本中，旧版本的数组公式也是可以使用的。
在F3单元格输入公式，引用B5:C7单元格区域，如图1-12所示。
=@+B5:C7

图1-12 +运算符可以将单元格区域转换为数组
可以看出，使用+运算符可以将单元格区域转换为数组，然后使用@运算符返回数组左上角的值“飞鱼”。
某些函数返回的结果为一个单元格区域，这些常用的函数包括OFFSET、INDIRECT以及INDEX。在使用这些函数时，如果需要使用@运算符，则需先使用+运算符进行转换。
提示：支持动态数组功能的软件可以使用+运算符进行转换，旧版本则无法使用此功能。
示例1-1：查询分数最高的人
在E3单元格输入公式，如图1-13所示。
=@SORTBY(B3:B7,C3:C7,-1)

图1-13 查询分数最高的人
本示例公式未考虑最高分重复的情况。当出现最高分重复时，此公式将返回数据源中最先出现的人。
SORTBY函数内容可转至2.6节学习。
2.引用动态数组结果：#运算符
在需要引用动态数组返回的数据二次计算时，可以通过引用动态数组左上角公式所在单元格，并在其后加上#运算符，可直接获取该单元格公式返回的数组结果，如图1-14所示。
在D3单元格输入公式，使用UNIQUE函数对B3:B7单元格区域去除重复项。
=UNIQUE(B3:B7)
在F3单元格输入公式，使用COUNTA函数对D3单元格返回的去重后的姓名进行计数。
=COUNTA(D3#)

图1-14 使用#引用区域
此外，在引用单元格区域时，如果通过鼠标选取的单元格区域是动态数组返回的数据，将自动转换为#引用模式。
如果引用的单元格没有公式，则在其后加上#运算符后，会返回错误值#REF!，如图1-15所示。
=B3#

图1-15 引用无效单元格错误
由于B3单元格并未输入公式，因此在引用该单元格时，如果在其单元格后面添加#运算符，会导致公式返回错误值#REF!，即引用了无效的单元格。
1.3 动态数组功能注意事项
1.支持动态数组功能的WPS
WPS Office 2023秋季更新（15933）版本后，下载WPS的地址为https://www.wps.cn/。
2.文件保存类型：XLSX、XLSM
（1）动态数组功能只支持XLSX格式的文件，不支持XLS格式，如果在XLS格式文件中使用动态数组功能，在保存XLS格式的文件时，会将动态数组功能溢出的公式转换为区域数组公式，关闭文件后再次打开，单元格中溢出的公式将转换为区域数组公式，如果需要使用动态数组功能，只能将区域数组公式清除后重新输入公式。
（2）如果在XLS格式文件中使用了LET函数或LAMBDA类函数，则在保存XLS格式文件时，无法保存此类函数，关闭文件后再次打开，包含LET函数或LAMBDA类函数公式的单元格将返回错误值#VALUE!。
3.错误值：#SPILL!(溢出错误)
（1）动态数组溢出功能只能向空白单元格区域溢出，当溢出单元格区域已有内容时，会返回错误值#SPILL!，并提示“溢出区域不是空白区域”。在D3单元格输入公式，如图1-16所示。
=UNIQUE(B3:B7)

图1-16 溢出区域不是空白区域
单击输入公式单元格右侧的“错误检查”按钮，单击“选择造成阻碍的单元格(S)”按钮，可以选中阻碍的单元格，如图1-17所示。

图1-17 选择造成阻碍的单元格(S)
选中阻碍单元格后，右击，在弹出的快捷菜单中单击“清除内容(N)”命令即可。
（2）输入公式的单元格及溢出单元格区域不能有合并单元格，否则公式会返回错误值#SPILL!，并提示“溢出区域包含合并单元格”，如图1-18所示。

图1-18 溢出区域包含合并单元格
单击输入公式单元格右侧的“错误检查”按钮，单击“选择造成阻碍的单元格(S)”按钮，选中阻碍的单元格，单击“开始”选项卡，展开“合并”菜单，单击“取消合并单元格(U)”按钮，取消合并单元格即可。
（3）动态数组溢出功能不能在“表格”中使用，否则公式会返回错误值#SPILL!，并提示“溢出区域位于表中”，如图1-19所示。

图1-19 溢出区域位于表中
（4）溢出到工作表边缘之外，公式会返回错误值#SPILL!，并提示“溢出区域太大”。在F3单元格输入公式，如图1-20所示。
=VLOOKUP(E:E,B:C,2,0)

图1-20 公式可能溢出到工作表边缘之外
在输入公式后，会弹出“是否要改用以下公式？”对话框，是因为在动态数组功能中使用隐式交集运算，需要在引用单元格地址前加@运算符，如果不加@运算符，同时不是在第1行输入公式引用整列，会弹出此对话框。如果单击“否(N)”按钮，公式会返回错误值#SPILL!，并提示“溢出区域太大”，如图1-21所示。

图1-21 溢出区域太大
可以通过以下3种方法解决。
第1种，使用@运算符，强制使用隐式交集运算，手动在E列前加@运算符，或在“是否要改用以下公式？”对话框中单击“是(Y)”按钮。在F3单元格输入公式，如
图1-22所示。
=VLOOKUP(@E:E,B:C,2,0)

图1-22 使用@运算符隐式交集运算
第2种，引用一个单元格后向下填充公式。在F3单元格输入公式，如图1-23所示。
=VLOOKUP(E3,B:C,2,0)

图1-23 引用一个单元格后向下填充公式
第3种，引用有内容的多个单元格区域。在F3单元格输入公式，如图1-24所示。
=VLOOKUP(E3:E4,B:C,2,0)

图1-24 引用有内容的多个单元格区域
除VLOOKUP函数以外，对于XLOOKUP、SUMIF、SUMIFS、COUNTIF、COUNTIFS
等函数，当函数的参数类型是一个值、非单元格区域或数组时，如果引用了整行或整列，需要使用@运算符，强制使用隐式交集运算，否则除在A列或第1行输入公式外，公式将返回错误值#SPILL!。
（5）手动计算无法溢出。如果将“公式”选项卡下“计算选项”按钮设置为“手动”，在D3单元格输入公式后，双击公式溢出的E3:F7单元格区域中的任意单元格，公式将返回错误值#SPILL!，并提示“无法溢出”，如图1-25所示。
=B3:C7

图1-25 手动计算无法溢出
在“公式”选项卡下单击“重算工作簿”按钮或单击“计算工作表”按钮，重新计算即可解决，将“公式”选项卡下“计算选项”按钮设置为“自动”也可以解决此问题。
（6）动态数组溢出功能不支持随机函数结果溢出。包括RAND、RANDBETWEEN等随机函数，在B3单元格输入公式后，按快捷键F9计算工作表，公式将返回错误值#SPILL!，并提示“溢出区域未知”，如图1-26所示。
=SEQUENCE(RANDBETWEEN(1,5))

图1-26 溢出区域未知
4.错误值：#CALC!(空数组)
由于一些新函数可以返回数组结果，当函数返回空的数组时，会返回错误值#CALC!。在F5单元格输入公式，如图1-27所示。
=FILTER(B2:D7,B2:B7=G2)

图1-27 公式返回了空的数组
FILTER函数可以通过设置第3个参数来指定空值，如果其他函数返回错误值#CALC!，可以使用IFERROR函数将错误值转换为指定值。