数组与数组函数

• 认识数组：每次价格都不一样，那怎么求平均？应该更加倾向于求加权平均价格！

按照之前的预算，我们必须要增加一个辅助列才能算出，那有没有什么新的方法不需要增加辅助列也能够算出呢？

这个时候我们就要引入一个“动态数组”，这是office365的新特性；比如下面的例子，我们只要选中两列行数相同的数据，直接进行相乘也能够得出值！并且它会直接扩展到新的列中，这就叫做数组、动态扩展！

我们来看一下相较于之前的方法，它每个单元格的公式是没有发生变化的，它只是一个公式的结果集！

（在老版本的office中数组公式的结尾都是三个键，ctrl，shift加回车）

office365中VLOOKUP函数的加强

• 先看一个经典例子，根据客户ID来查找对应信息。

• 之前的做法是写多个Vlookup函数分别去查找，但是我们现在引入了数组，可以直接把函数第三个参数，用一个数组来表示，我们就可以一次性查找多列的值了。

=VLOOKUP(A2,客户信息表!A:L,{2,5,3,10},0)

如果公式数组要填充的位置被占领了，他就会报一个溢出错误！

数组中的多条件判断

• 按照之前的做法，我们要对这个数据进行求和的话，那我们就要用到sumifs函数 ！

=SUMIFS(D:D,B:B,F8,C:C,G8)

• Office365中数组，它的判断是什么呢？

=SUM((B2:B106=I8)*(C2:C106=J8)*D2:D106))

其实也是多项他会 一 一去比对，这个是动态数组的形式！

重点是想说明条件如何去表达“且”的关系，我们只要将两组数据进行相乘即可！

FILTER函数详解

用法概述

• FILTER本意是筛选的意思，但需要将数据给罗列、展现出来，都可以使用！

• 例子：筛选下数学成绩大于等于85的数据；使用FILTER后，满足数学85的全部会筛选回来，然后和他相关的数据也会自动显示！他的第二个参数其实是一个扩展的数组！

=FILTER(A2:E8,B2:B8>=85

• 如果要同时筛选数学和英语成绩都大于等于85的，这个时候他的条件区域也就是第二个参数该怎么写呢？回归上面讲的动态数组案例！

公式：

=FILTER(A2:E8,(B2:B8>=85)*(D2:D8>=85))

因此，表示“且”就是两个条件相乘。如果表示或就是两个条件相加！

• 假设表中的数据会不断增加，如何筛出数学成绩小于60分的？

如果直接写 =FILTER(A:E,(B:B<60) 结果空值也会算入小于60的！

解决办法就是我们需要将空值去除，只要加上一个非空约束，也就是乘以一个非空的条件！

公式：

=FILTER(A:E,(B:B<60)*(B:B<>""))

• 制作一个动态凭证明细，切换“科目代码”，下面的数据也会随之变动！
  
• 解题：只要科目代码=制定位置“科目代码”，用filter就行
  

再对单元格做一个数据验证就行！

函数语法

• 参数 =FILTER(数据源, 筛选条件, [无结果提示])

参数说明：
数据源 要筛选的原始数据区域（如A2:D100）

筛选条件 返回TRUE/FALSE的逻辑表达式（必须与数据源行数一致）

[无结果提示] 可选参数，当无匹配结果时返回指定内容（默认显示#CALC!错误）

基础筛选

• 单列精确匹配
  场景：筛选「部门」为"市场部"的所有记录

excel
=FILTER(A2:D100, B2:B100=“市场部”)
单条件筛选示例

• 排除空值
  场景：筛选「客户名称」列非空的记录

excel
=FILTER(A2:D100, C2:C100<>“”)

• 日期范围筛选
  场景：筛选2023年的订单记录

=FILTER(A2:D100, (D2:D100>=DATE(2023,1,1))*(D2:D100<=DATE(2023,12,31)))

多条件筛选

• AND逻辑（同时满足）

=FILTER(数据源, (条件1)(条件2)…)
示例：筛选「华东区」且「销售额>10000」的记录

=FILTER(A2:D100, (B2:B100=“华东”)*(D2:D100>10000), “无匹配订单”)

• OR逻辑（满足其一）

=FILTER(数据源, (条件1)+(条件2)+…)
示例：筛选「手机」或「平板」类产品

=FILTER(A2:D100, (C2:C100=“手机”)+(C2:C100=“平板”))
3.3 混合逻辑
示例：筛选（「华北区」且「销售额>5000」）或（「华南区」所有记录）

=FILTER(A2:D100, ((B2:B100=“华北”)*(D2:D100>5000))+(B2:B100=“华南”))

进阶技巧

• 动态表头处理

=FILTER(A1:D100, (B1:B100=“研发部”)*(ROW(B1:B100)>1))
说明：通过ROW()>1排除标题行

• 多列联合筛选

场景：筛选「姓名」包含"张"且「邮箱」包含"@company.com"

=FILTER(A2:D100,
ISNUMBER(SEARCH(“张”, A2:A100))*
ISNUMBER(SEARCH(“@company.com”, D2:D100))
)

• 跨工作表筛选

=FILTER(Sheet2!A:D,
(Sheet2!B:B=“财务部”)*
(Sheet2!D:D>TODAY()-30)
)

结合动态数组

• 自动扩展结果

=FILTER(A2:D100, B2:B100=“销售部”)#
说明：使用#符号自动填充相邻单元格

• 嵌套排序

=SORT(FILTER(A2:D100, D2:D100>5000), 4, -1)
参数解释：

• 按第4列（销售额）排序
  -1：降序排列
  5.3 去重处理

=UNIQUE(FILTER(B2:B100, D2:D100>10000))

高级函数整合

• 使用LET优化可读性

=LET(
data, A2:D100,
target_dept, “技术部”,
min_date, DATE(2023,6,1),
FILTER(data,
(B2:B100=target_dept)*
(D2:D100>=min_date)
)
)

• 创建LAMBDA自定义函数
  步骤1：定义函数

excel
高级筛选 = LAMBDA(源数据, 关键词,
FILTER(源数据,
ISNUMBER(SEARCH(关键词, INDEX(源数据, 0, 3)))
)
)
步骤2：调用函数

公式：
=高级筛选(A2:D100, “紧急”)

• 结合XLOOKUP二次匹配

=FILTER(A2:D100,
ISNUMBER(XLOOKUP(B2:B100, G2:G10, H2:H10))
)
说明：筛选B列值存在于G2:G10列表中的记录

错误处理

错误类型 触发场景 解决方案
#CALC! 无匹配结果 添加第三参数：FILTER(…, “无数据”)
#VALUE! 条件区域与数据源行数不等 检查条件区域是否对齐数据行数
#SPILL! 结果区域被占用 清空目标区域周边单元格

注意事项

版本兼容性

仅支持Excel 365/2021+
LET/LAMBDA需最新版Excel 365
性能优化

避免使用A:A整列引用，改用A2:A1000
复杂条件建议先用辅助列计算
动态数组特性

结果区域会自动扩展，需预留空间
引用动态结果时使用FILTER(…)#
特殊值处理

日期需用DATE()函数规范格式
文本匹配区分大小写（可用LOWER()统一格式）

FILTER经典问题：一对多查询

• 将符合条件的结果都筛选出来：就是“一对多”，因为下面一个编号的可能对应三四个人！

公式：

=TRANSPOSE(FILTER(B:B,A:A=G2))

XLOOKUP函数

XLOOKUP基础用法

• 如下图示例：
  

公式：

=XLOOKUP(A2,客户信息表!A:A,客户信息表!E:E,"",0,)

没找到也可以设置返回空、具有排除功能。第一个参数是要查找的值；第二个参数是参数1所在的位置；第三个参数是要返回需要查找的内容；第4个参数是错误要返回什么？

• 从右向左查询问题

公式：依旧是直接写第三个参数就行，不在乎左右

=XLOOKUP(A2,客户信息表!A:A,客户信息表!E:E,"",0,)

• 取回多列信息：第三个参数可以写多列的范围

=XLOOKUP($A2,客户信息表!A:A,客户信息表!B:E,"",0,

XLOOKUP函数多条件匹配和双向查询

• 多条件查询：面对多条件差，Xlookup可以直接用连字符连接在一起，当做当做查询区域和返回的列！
  
  公式：

=XLOOKUP($G4&$H4,A:A&B:B,C:C,"",,)

• 双向（横纵）查询：

公式：先写一个XLOOKUP，在嵌套一个

=XLOOKUP( C2, 职级薪资表!$B$1:$H$1, XLOOKUP(B2,职级薪资表!A:A,职级薪资表!B:H) )

借助UNIQUE函数统计唯一值数量

• UNIQUE函数，查找唯一值

=COUNTA(UNIQUE(B2:B15))

=UNIQUE(B2:B15)

• 再查找一下每个人负责的所有区域

=TRANSPOSE( UNIQUE ( FILTER(C1:C15,I2=B1:B15) ) )