195、excel公式获取符合条件数据并整理

按给定条件获取相应的数据是实际应用中最常用的需求，excel也提供了很多的方法实现这一目的。在此，介绍如何使用公式得到符合条件的数据。

如下图所示（图片所示仅为部分物料）：表格中记录了各物料25年及26年的采购金额，E列中给出了同比采购金额差，现在要根据要求获取相关的物料中文名称信息。

一、获取同比采购差额大于5000的物料唯一中文名称，并存放在一个单元格内。

思路：这里的要求有三个关键点：1、获取的信息为同比差额大于5000的；2、获取的是物料的中文名称并去重；3、要存放在同一单元格内。

根据思路要点，我们可以确定的是需要对表格的“品名”和“同比差额”两列进行逐行处理，“品名”列用于提取中文名称，这可i以用正则表达式函数提取，“同比差额”列用于判断是否符合条件（大于5000）。要对两列进行逐行处理，可以想到使用迭代函数map，获取符合条件的中文名称后再去重，最后进行连接放在一个单元格即可。

比如在B2单元格输入公式“=ARRAYTOTEXT(DROP(UNIQUE(MAP(E5:E435,B5:B435,LAMBDA(x,y,IF(x>5000,REGEXEXTRACT(y,"[一-龟]+"),"")))),1))”。公式中MAP(E5:E435,B5:B435,LAMBDA(x,y,IF(x>5000,REGEXEXTRACT(y,"[一-龟]+"),""))))部分的含义为：如果E5:E435即同比差额大于5000，那么就提取B5:B435即“品名”列的中文名称，否则返回空值。提取的中文名称使用UNIQUE获取单一值，然后使用DROP函数去掉第一个空值，最后使用ARRAYTOTEXT将获取的中文名称转换为文本值。结果如下图所示：

二、进一步思考：如果想要灵活的获取满足指定同比差额值的物料中文名称及对应的差额之和呢，又该如何处理呢？

思路：按要求有以下3个关键点：1、原表中筛选符合条件的所有物料信息（大于指定的同比差额）；2、提取符合条件物料的中文名称；3、根据中文名称和差额将数据进行分组。

实现：要实现第1个关键点，可以使用filter函数进行筛选；要实现第2个关键点可以使用正则表达函数；要实现第3个关键点可以使用分组函数groupby。

基于上述思考即可整理相应的公式，比如在H4单元格输入公式：

=LET(     筛选,FILTER(B4:E435,E4:E435>5000),     中文名,BYROW(                 INDEX(筛选,,1),                 LAMBDA(x,REGEXEXTRACT(x,"[一-龟]+"))                 ),     GROUPBY(中文名,INDEX(筛选,,4),SUM,3)     )

公式使用let函数，这样可以自定义变量名称。第一个变量为“筛选”，表达式为FILTER(B4:E435,E4:E435>5000)，也就是满足同比差额大于5000的数据；第二个变量为“中文名”，通过表达式BYROW(INDEX(筛选,,1),LAMBDA(x,REGEXEXTRACT(x,"[一-龟]+")))指定，也就是获取“品名”列的中文名称，最后的返回值通过函数GROUPBY(中文名,INDEX(筛选,,4),SUM,3)指定，对提取的中文名进行分组，数据列为“同比差额”列，功能为求和sum。返回的结果如下图所示：

这样建立了模型后，更改指定的差额条件值就可以及时获取所需要的数据了，如下视频所示：

已关注

关注

重播分享赞

视频详情

这一示例对于我们想要快速查看一些列中无法直接体现的数据信息时是比较有用的，涉及的函数也比较简单，有兴趣的朋友也可以改改条件自行练习。