分类 学习笔记 下的文章

在掌握了XLOOKUP的基础用法后,这里有一些高级技巧,涵盖多条件查找、二维交叉查询、通配符匹配、错误处理、动态数组溢出等进阶场景。

一、多条件查找(多条件联合查询)

当需要根据多个条件(如同时满足“部门”和“职位”)来查找一个值时,XLOOKUP可以通过连接符将多个条件合并为一个查找值,实现精准匹配 。
核心原理:将查找值与查找列都通过 & 连接成一个组合键,再进行比较。

示例:根据“产品类别”与“销售额度”查找对应的折扣率 。

=XLOOKUP(B2&C2, DiscountTable!A:A&DiscountTable!B:B, DiscountTable!C:C, 0)

说明:
B2&C2:将“产品类别”和“销售额度”合并为一个查找键。
DiscountTable!A:A&DiscountTable!B:B:将查找范围中的类别列和销售额度列也合并为组合键。
这种写法支持任意数量的条件,只需将更多列用 & 连接即可 。

二、二维交叉查询(双方向查找)

二维交叉查询用于查找行与列交汇处的值,例如根据“产品名称”和“月份”找到对应的销售额。XLOOKUP通过嵌套方式实现这一功能 。

示例:查找某产品在特定月份的销售数据。

=XLOOKUP(H2, B4:B20, XLOOKUP(H3, C3:N3, C4:N20))

工作原理:
内层 XLOOKUP:在月份标题行 C3:N3 中查找 H3(目标月份),返回对应的整列数据(C4:N20 中该月份所在列)。
外层 XLOOKUP:在产品列 B4:B20 中查找 H2(目标产品),然后从内层返回的月份列中提取对应值。
优势:相比传统的 INDEX+MATCH 组合,XLOOKUP嵌套的写法更直观,易于阅读和维护 。

三、通配符匹配(模糊查找)

当需要查找包含特定字符或以特定字符开头/结尾的文本时,XLOOKUP支持通配符匹配,需将 match_mode 参数设置为 2 。
通配符规则:

*:匹配任意数量的字符。
?:匹配单个字符。
~:将通配符转义为普通字符(如查找文本中的 * 本身)。
示例:查找所有以“Lap”开头的产品价格。

=XLOOKUP("Lap*", A2:A50, B2:B50, "未找到", 2)

注意:match_mode 默认值为 0(精确匹配),此时 * 和 ? 会被当作普通字符处理。务必设置 match_mode = 2,否则通配符不会生效,这是最常见的错误之一 。

四、近似匹配与区间查找

XLOOKUP的 match_mode 参数支持近似匹配,可用于处理阶梯定价、税率区间、佣金比例等场景 。

-|-|-
match_mode值 | 匹配行为 | 适用场景
-1|精确匹配,若无匹配则返回下一个较小的值|阶梯折扣、税率区间
1|精确匹配,若无匹配则返回下一个较大的值|成绩评级、达标判定
示例:根据销售数量查找对应的折扣率。

=XLOOKUP(45, A9:A12, B9:B12, "无折扣", -1)

说明:当销售数量为45时,查找表中有0、10、50、100四个档位。-1 模式会返回小于等于45的最大档位(即10)对应的折扣率(5%)。

五、逆向查找与最后一次匹配

  1. 逆向查找(向左查找)
    XLOOKUP支持任意方向的查找,不受VLOOKUP只能向右查找的限制 。

示例:根据“客户ID”(在D列)查找“客户名称”(在A列)。

=XLOOKUP(F2, D2:D300, A2:A300)
  1. 查找最后一次匹配
    search_mode 参数设置为 -1,可以从底部向上搜索,返回最后一个匹配项 。

示例:查找某产品的最新价格更新记录。

=XLOOKUP("P001", B15:B18, C15:C18, , , -1)

适用场景:订单记录、价格变更历史、日志等需要获取最新数据的场景 。

六、错误处理与容错机制

  1. 内置 if_not_found 参数
    XLOOKUP 的第四个参数 if_not_found 可以直接定义查找失败时的返回值,无需再嵌套 IFERROR 或 IFNA 。

    =XLOOKUP(A2, B2:B100, C2:C100, "未找到")
  2. 返回空值而非错误
    若希望查找失败时返回空单元格而非错误提示,可将 if_not_found 设为 "" 。

    =XLOOKUP(A2, products[SKU], products[Price], "")

    优势:内置的 if_not_found 参数比外层包裹 IFERROR 更简洁、运算效率更高,也更符合公式逻辑 。

七、返回多列数据

XLOOKUP 的 return_array 参数可以指定多列范围,从而一次性返回多个字段 。

示例:根据员工ID一次性返回其部门、经理和入职日期。

=XLOOKUP(A2, employees[ID], employees[Department:Start Date])

说明:employees[Department:Start Date] 表示从“部门”列到“入职日期”列的多列范围。当查找匹配时,XLOOKUP会返回对应行的所有列数据,并自动溢出到相邻单元格 。

八、结合其他函数的高级应用

XLOOKUP 可以与其他函数嵌套,构建更强大的数据分析公式 。

  1. 与 SUM 结合:条件求和
    =SUM(XLOOKUP("产品类别", CategoriesRange, SalesRange))
  2. 与 ADDRESS 结合:返回单元格地址
    =ADDRESS(ROW(XLOOKUP(A2, B2:B100, B2:B100)), COLUMN(XLOOKUP(A2, B2:B100, B2:B100)))
  3. 与 CHOOSE 结合:返回非连续列
    =XLOOKUP(A2, B2:B100, CHOOSE({1,2,3}, C2:C100, E2:E100, G2:G100))
    说明:CHOOSE 函数将 C、E、G 三列(非连续)组合成一个虚拟数组,供 XLOOKUP 返回 。

九、常见错误及排查方法

-|-|-
错误类型|错误值|常见原因及解决方法
未找到匹配|#N/A|查找值不存在,或未设置 if_not_found 参数;检查拼写、空格、数据类型一致性
范围不匹配|#VALUE!|lookup_array 与 return_array 的行数或列数不一致,导致无法对齐
版本不支持|#NAME?|当前Excel版本低于2019/Office 365,不支持XLOOKUP函数
通配符无效|#N/A|未设置 match_mode = 2,* 被当作普通字符处理
精确匹配误判|错误结果|默认 match_mode = 0 为精确匹配;若需近似匹配,须显式设置 match_mode 为 -1 或 1

十、总结:XLOOKUP进阶技巧速查表

-|-|-
技巧|核心参数|一句话记忆
多条件查找|= 连接符 + &|用 & 合并条件,实现多条件联合查询
二维交叉查询|嵌套 XLOOKUP|内层查列,外层查行,返回交叉点值
通配符匹配|match_mode = 2|匹配模式设为2,开启 * 和 ? 模糊查找
近似匹配|match_mode = -1 或 1|处理阶梯区间,返回小于或大于的匹配值
逆向查找|直接指定返回列|向左向右皆可,摆脱VLOOKUP的方向限制
最后匹配|search_mode = -1|从底部向上搜索,返回最新记录
内置容错|if_not_found|第四参数自定义错误提示,无需 IFERROR
多列返回|扩展返回数组|返回多列,自动溢出到相邻单元格

一、为什么你需要XLOOKUP?

在Excel中,查找数据是日常工作中最频繁的操作之一。过去,我们依赖VLOOKUP或HLOOKUP,但它们有诸多限制:

  • VLOOKUP:查找列必须在数据表的第一列,且只能向右查找。
  • HLOOKUP:查找行必须在数据表的第一行,且只能向下查找。
  • 两者都不支持反向查找,且处理错误值(如#N/A)时不够灵活。

XLOOKUP(在Excel 2021及Office 365中引入)彻底解决了这些问题,被誉为“查找函数的终极形态”。

二、XLOOKUP的基本语法

=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时的返回值], [匹配模式], [搜索模式])

-|-|-
参数|说明|是否必填
查找值|你要查找的内容|是
查找数组|在哪一列/行中查找|是
返回数组|返回哪一列/行的结果|是
未找到时的返回值|可选,匹配失败时返回自定义文本|否
匹配模式|0=精确匹配(默认),-1=小于,1=大于,2=通配符|否
搜索模式|1=从第一个开始搜索(默认),-1=从最后一个搜索,2=二分查找|否

三、XLOOKUP vs VLOOKUP:核心优势对比

-|-|-
功能|VLOOKUP|XLOOKUP
查找方向|只能向右|任意方向(左、右、上、下)
查找列位置|必须位于第一列|任意位置
返回列指定|整数(第几列)|直接指定返回数组
错误处理|需嵌套IFERROR|内置“未找到”参数
多条件查找|需辅助列|可直接用数组公式
通配符支持|支持|支持(匹配模式=2)

四、XLOOKUP的常见应用场景

场景1:经典单条件查找(替代VLOOKUP)
问题:根据员工ID查找姓名。

VLOOKUP写法:

=VLOOKUP(A2, A:D, 2, 0)

XLOOKUP写法:

=XLOOKUP(A2, A:A, B:B)

优势:无需计算列序号,直接指定返回列。

场景2:反向查找(VLOOKUP做不到)
问题:根据员工姓名查找员工ID(姓名在ID右侧)。

VLOOKUP:需要借助INDEX+MATCH组合。

XLOOKUP写法:

=XLOOKUP(B2, B:B, A:A)

优势:直接向左查找,公式更简洁。

场景3:多条件查找
问题:根据“部门”和“职位”两个条件查找“薪资”。

=XLOOKUP(1, (A:A=E2)*(B:B=F2), C:C)

说明:(A:A=E2)*(B:B=F2) 会生成一个由0和1组成的数组,只有两个条件同时满足时为1,XLOOKUP精确匹配1,返回对应的薪资。

场景4:处理查找失败的情况
问题:查找某个不存在的ID时,返回自定义提示而非错误值。

=XLOOKUP(A2, A:A, B:B, "未找到此人")

优势:无需嵌套IFERROR,公式更清爽。

场景5:从右向左查找最后一个匹配项
问题:查找某员工最后一次加班记录。

=XLOOKUP(A2, A:A, B:B, "无记录", 0, -1)

说明:第六个参数 -1 表示从数组的最后一个值开始搜索,返回最后一个匹配项,常用于“最后一次”场景。

五、XLOOKUP的局限与注意事项

-|-
局限|说明
两个数组|公式中两个数组需要保持相同大小,例如:A:A对应B:B即A列对B列;A1:A10,B11:B20,不需要都是1开始,但必需保持数组中元素个数相同。
wps office|ET 11.2及以上版本
ms office|Excel 2021及以上版本、Office 365支持
性能问题|在大数据量(>10万行)中,速度可能慢于INDEX+MATCH
精确匹配默认|不指定匹配模式时,默认为0(精确匹配),注意区分
数组公式依赖|多条件查找依赖动态数组,旧版本可能不支持

六、总结:什么时候用XLOOKUP?

-|-
场景|推荐函数
需要反向查找(向左查找)|XLOOKUP
需要多条件查找|XLOOKUP 或 INDEX+MATCH
需要处理#N/A错误|XLOOKUP
需要从右到左查找最后一项|XLOOKUP
数据量极大(>10万行)|INDEX+MATCH(性能更优)
老版本Excel(2019及以前)|INDEX+MATCH

七、一句话记忆

XLOOKUP = VLOOKUP的升级版 + HLOOKUP + INDEX+MATCH的简化版,且支持反向查找、错误处理和灵活搜索方向。

近期看大宝的作业其中有讲如何快速判断一个大数能否被整除,这里整理一下备查。

定理

a*b除以c的余数等于a除以c的余数*b除以c的余数
即 a*b mod c = (a mod c) * (b mod c) mod 表示取余。

原理

设:

a = k1*c + x
b = k2*c + y

则:

a*b 
= (k1*c+x)*(k2*c+y)
= k1*k2*c*c + (k1*y+k2*x)*c + x*y
= [k1*k2*c + (k1*y+k2*x)]*c + x*y

明显 [k1*k2*c + (k1*y+k2*x)]*c包含因数c,取余时可直接舍去。
故:

a*b mod c = x*y mod c

2

这个简单直接看末位能否被2整除。

3

所有数位上的数字相加能被3整除。
原理:abc = a*(3*33+1) + b*(3*3+1) +c =3*33*a + 3*3*b + a + b + c
明显3*33*a + 3*3*b能被3整除,故只用考虑 a + b + c能被3整除。

4

最后两位能被4整除。还有简便计算:ab(一个两位数),a*2 + b能被4整除。
原理:abc = a*(4*25) + b*(4*2+2) +c =4*25*a + 4*2*b + 2*b + c
明显4*25*a + 4*2*b能被4整除,故只用考虑 2*b + c能被3整除。

5

末位为0或5。

6

同时被2和3整除。

7

以三位分节,然后奇减偶,结果能被7整除。还有简便计算abc(一个三位数),a*2 + b*3 + c能被7整除。

原理

1、 10 、 100、1000、10000、100000……除7的余数分别为1、3、2、6、4、5、1、3、2、6、4、5……
即1 3 2 6 4 5的循环
同时6 4 5又可以写为-1 -3 -2因此可以改写为1 3 2 -1 -3 -2 1 3 2 -1 -3 -2……
即从个位起以三位分节。余数按1 3 2循环,奇数节为正,偶数节为负。
例:123456789123456
先分节:
大数|123 | 456 | 789 | 123 | 456
:-:|:-:|:-:|:-:|:-:|:-:
分节|5 | 4 | 3 | 2 | 1
所有奇数节为 1 3 5 对应 456 789 123
所有偶数节为 2 4 对应 123 456
奇减偶:(456 + 789 + 123) - (123 + 456) = 789
再运用简便计算:7*2 + 8*3 + 9 = 47 => 47/7=6 ··· 5 即123456789123456除以7余5。
注:如各节加和为负则对应余数加7转换为正数。

8

末三位能被8整除。简便计算abc(一个三位数),a*4 + b*2 + c 能被8整除。

原理

dabc = d*8*125 + a*(8*12+4) + b*(8+2) +c = d*8*125 + a*8*12 + b*8 + a*4 + b*2 +c
即余数等同于 a*4+b*2+c

进阶推广

任意大数除以2的n次方时,余数只需要考虑末n位
记最后n位为X(1)X(2)……X(n)
则结果为X(1)*2^(n-1) + X(2)*2^(n-2) + …… + X(n)*2^(n-n)

9

所有数位上的数字相加能被9整除。

原理

abc = a*(9*11+1) + b*(9*1+1)+c = a*9*11 + b*9*1 + a + b + c
即余数等同于 a + b + c

人类主要拥有至少七种表情:

  • 高兴:人们高兴时的面部动作包括:嘴角翘起,面颊上抬起皱,眼睑收缩,眼睛尾部会形成“鱼尾纹”。
  • 伤心:面部特征包括眯眼,眉毛收紧,嘴角下拉,下巴抬起或收紧。
  • 害怕:害怕时,嘴巴和眼睛张开,眉毛上扬,鼻孔张大。
  • 愤怒:这时眉毛下垂,前额紧皱,眼睑和嘴唇紧张。
  • 厌恶:厌恶的表情包括嗤鼻,上嘴唇上抬,眉毛下垂,眯眼。
  • 惊讶:惊讶时,下颚下垂,嘴唇和嘴巴放松,眼睛张大,眼睑和眉毛微抬。
  • 轻蔑:轻蔑的著名特征就是嘴角一侧抬起,作讥笑或得意笑状。

常见微表情解析:

  1. 单肩抖动——不自信时候更容易单肩抖动(并不是所有的单肩抖动就是不自信)。
  2. 注视对方眼睛——人在准备好谎言时,更容易注视对方眼睛,已使对方相信或观察对方是否相信。(并不是注视对方眼睛就是撒谎)
  3. 回答时生硬的重复问题——回答时生硬的重复问题很有可能是典型谎言。
  4. 把手放在眉骨之间——当人感到羞愧的时候,很可能出现的动作是把手放在眉骨或者是额骨附近,用来建立一个视觉阻碍。
  5. 揉鼻子——掩饰真相(男人的鼻子里的海绵体在撒谎时容易痒)。
  6. 眼睛向左看是在回忆,向右看是在思考谎话。
  7. 惊讶表情超过一秒就是假惊讶。
  8. 男人右肩微耸一下就是在说假话。
  9. 用手抚额头——表示羞愧。
  10. 瞳孔放大——恐惧,愤怒,性欲。
  11. 话语重复声音上扬——撒谎。
  12. 肢体阻抗向后退一步,表示刚说的话不可信。
  13. 抿嘴经典的模棱两可的动作。
  14. 摸脖子人撒谎的时候会摸脖子,典型的强迫行为 属于机械反应。
  15. 撒谎的时候没有与之相对应的表情,往往没有任何表情。
  16. 手一直摩挲,一种自我安慰的姿态,当你不相信自己的在说什么的时候,使自己安心。
  17. 当真正的凶手看到被害者照片的时候,会表现出恶心、轻蔑甚至是害怕。但绝对不会是吃惊。
  18. 要是有人要将要实施血腥的罪行,就会出现这样的表情:眉毛朝下皱紧,上眼睑扬起,眼袋绷紧。
  19. 亮出中指(fuck的手势),这是象征性的还有特定意识的下意识手势,充满敌意。
  20. 鼻孔外翻,嘴唇紧闭,生气。
  21. 下巴扬起,嘴角下垂,自责。
  22. 眉毛向上,拉紧,恐惧。
  23. 如果先突然大声说话,然后再用手猛拍桌子,这是对可怕事情反应的手势时间差。如果是真的发火,这两个动作会同时进行。
  24. 提高右边的眉毛,表示你很疑问。
  25. 受右脑控制的左半边脸更容易暴露内心的真实情感(左撇子相反)。

◆撒谎的微表情心理学分析

  • 向对方询问某事时,对方一侧肩抖动表示对方对自己的话不自信。身体和语言不一致,表示他在撒谎。惊奇、害怕的表情在脸上超过一秒,表示是假装的对方对你的质问表示不屑,通常你的质问会是真的。
  • 对方对你撒谎时,会有更多的眼神交流,来判断你是否相信他的谎言。对你的问题生硬的重复是典型的撒谎方式。
  • 说话或思考时把手放在鼻子上是想要掩饰什么的举动。说话时一直把手放在裤兜里或抵着大腿,是紧张的表现。描述一连串发生的事情,如果事情是编造的,他可以按虚构好的顺序说,但是却无法倒叙重复。
  • 人撒谎时会摸脖子,这是经典的机械反应。

◆紧张、愤怒、兴奋的微表情心理学分析

  • 紧张,愤怒,兴奋三种情绪会使人的瞳孔放大手臂紧贴腿部,手指竖起,是心里不安的表现。
  • 微笑的时候眨眼睛说明真的想到令人幸福的事。
  • 假笑眼角是没有皱纹的。
  • 人在害怕时会出现生理逃跑反应:血液从四肢回流回腿部,做好逃跑准备,手部会先变冰凉。微表情是内心流露与掩饰,通常发生在五分之一秒的时间之内。你说错了,对方会有嘴角轻微上扬而又恢复。撒谎时,摇头否认之前可能会有一瞬间的点头动作。

◆忧伤的微表情心理学分析

  • 眉毛挂下来。羞愧的表情:低着头,手抚摸额头,擦汗状。不屑的表情:上嘴唇上扬说话很急,使用手臂或者物体将自己与对方之间形成障碍,是焦虑的表现。当然,每个人遇到被人疑为罪犯时都会有这样的反应。

◆其它的微表情心理学分析

  • 瘪嘴角是经典的犯错表情。对自己的话没信心。
  • 声音的震颤可能是反感或厌恶。
  • 没有表情与出现表情同样重要当一个人面部两侧表情不对称时,他很有可能在伪装感情。
  • 抚摸自己的手是一种自我安慰的手势,在自己不完全相信自己所说的话时,用来尽量打消自己的疑虑。
  • 如果是真的生气,大吼大叫和手用力拍桌子应该是同时发生的,如果一先一后,就有可能是在伪装。
  • 研究表明,越受欢迎的人,越会撒谎,他们善于隐藏自己的情感因此容易受欢迎。
  • 真正的凶手会对被害人表现出轻视、厌恶,甚至害怕,而不是惊讶。

如果需要在某一文本字符串中替换指定的文本,请使用函数 SUBSTITUTE;
如果需要在某一文本字符串中替换特定位置处的任意文本,请使用函数 REPLACE。

SUBSTITUTE

SUBSTITUTE(text, old_text, new_text, [instance_num])
描述:在文本字符串中用 new_text 替换 old_text。

SUBSTITUTE 函数语法具有下列参数:

  • text 必需。需要替换其中字符的文本,或对含有文本(需要替换其中字符)的单元格的引用。
  • old_text 必需。需要替换的文本。
  • new_text 必需。用于替换 old_text 的文本。
  • Instance_num 可选。指定要将第几个 old_text 替换为 new_text。 如果指定了 instance_num,则只有满足要求的 old_text 被替换。 否则,文本中出现的所有 old_text 都会更改为 new_text。

例子1:
SUBSTITUTE("我们班一共有*个同学,男同学*人,女同学*人", "*", A2)
用单元格A2的内容替换字符串中的*号,通常可以用来补充固定模板中的数据。
我们班一共有30个同学,男同学30人,女同学30人
(因为没有指定替换哪一个*号,所以全部*号都被替换)

例子2
SUBSTITUTE("我们班一共有*个同学,男同学*人,女同学*人", "*", A3,2)
替换字符串中的第2个*号
我们班一共有*个同学,男同学16人,女同学*人

REPLACE和REPLACEB

根据指定位置的字符数,将部分字符串替换为不同的字符串。
REPLACE(old_text, start_num, num_chars, new_text)
REPLACEB(old_text, start_num, num_bytes, new_text)

  • 注意:这两个函数基本一致,只是在处理非英文字符的时候计算字符串的数量上不一致,国内可以一直使用replace。出错的情况下才需要考虑使用replaceb。

REPLACE 和 REPLACEB 函数语法具有下列参数:

  • old_text 必需。 要替换其部分字符的文本。
  • start_num 必需。 old_text 中要替换为 new_text 的字符位置。
  • num_chars 必需。 old_text 中希望 REPLACE 使用 new_text 来进行替换的字符数。
  • Num_bytes 必需。 old_text 中希望 REPLACEB 使用 new_text 来进行替换的字节数。
  • new_text 必需。 将替换 old_text 中字符的文本。

例子:
REPLACE("abcdefg",2,3, "+") ,将从第2个字符开始,连续3个字符bcd整体串替换为+。 a+efg