ARTICLE · 1075334
Excel经典案例:将多家门店同一商品的销售信息按条件提取到同一张表中
Excel经典案例:将多家门店同一商品的销售信息按条件提取到同一张表中本文用一个案例，从需求拆解、函数解析、逆向逻辑思路整理等多个维度解锁嵌套函数组合的底层逻辑，通过本文可以掌握嵌套函数的底层逻辑，便于以后轻松组合各种嵌套函数。成品如下： 
需求：10张分表中存储着10家门店的销售明细，商品销售分线上和线下两种销售方式。现在想要查看某一单品在10家门店的总销售情况，需要将10张表中某单品的销售信息全部提取到总表中查看。至于提取到总表后是汇总还是统计，那就相对简单了，本文不再赘述。 提取要求
商品和销售方式分别从两个下拉框中选取。 只要两个下拉框选一个，就能提取到相应的数据，两个下拉框都没选时不能有数据； 提取出的数据按销售日期的先后顺序排序； 分表样式 
总表样式 
最终效果 
经典公式 
公式： A6=IF(AND(B3="",E3=""),"",SORT(FILTER(VSTACK(观音桥门店:两江新区门店!A3:G200),((B3="")+(VSTACK(观音桥门店:两江新区门店!B3:B200)=B3))*((E3="")+(VSTACK(观音桥门店:两江新区门店!G3:G200)=E3))*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")),1)) 公式解析： 1、IF函数：当B3和E3（两个下拉框）都为空值时，返回空值，否则执行SORT函数； 2、SORT函数： =SORT(FILTER(VSTACK(观音桥门店:两江新区门店!A3:G200),((B3="")+(VSTACK(观音桥门店:两江新区门店!B3:B200)=B3))*((E3="")+(VSTACK(观音桥门店:两江新区门店!G3:G200)=E3))*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")),1) 对红色部分FILTER函数筛选的数据按第1列升序排序； 3、FILTER函数： =FILTER(VSTACK(观音桥门店:两江新区门店!A3:G200),((B3="")+(VSTACK(观音桥门店:两江新区门店!B3:B200)=B3))*((E3="")+(VSTACK(观音桥门店:两江新区门店!G3:G200)=E3))*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")) 在红色部分的数据区域内，按蓝色部分为条件进行筛选。 (1) 数据区域： =VSTACK(观音桥门店:两江新区门店!A3:G200)。 将第1～10张分表中的A3:G200区域按列垂直合并； (2) 筛选条件： =((B3="")+(VSTACK(观音桥门店:两江新区门店!B3:B200)=B3))*((E3="")+(VSTACK(观音桥门店:两江新区门店!G3:G200)=E3))*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"") 红色、绿色和蓝色分别为3个筛选条件，用*连接，表示需要三个都满足； 红色条件中又嵌套了两个条件，B3=""和VSTACK(观音桥门店:两江新区门店!B3:B200)=B3，用+连接，表示只要满足一个就可以。B3为空值或十张分表的B3:B200区域中的内容=B3； 绿色条件中又嵌套了两个条件，E3=""和VSTACK(观音桥门店:两江新区门店!G3:G200)=E3，用+连接，表示只要满足一个就可以。E3为空值或十张分表的G3:G200区域中的内容=E3； 蓝色条件：VSTACK(观音桥门店:两江新区门店!A3:A200)<>""。十张分表的A3:A200区域中的内容不为空值。 这段条件的意思是：两个下拉框只要有一个不为空值，按下拉框中的内容筛选有销售日期的数据。 函数逻辑 看到这里，有些新手可能一头雾水，下面从逆向整理一下函数，讲解一下嵌套函数的底层逻辑。 如果人工将十张分表中所有满足条件的数据提取到总表中，操作顺序应该是： 第1步：将十张分表的所有数据合并到总表中； 第2步：按条件筛选数据； 第3步：对筛选出的数据排序； 其实用函数实现这个功能也是遵循上述步骤 第1步：用VSTACK函数对十张分表中的相关区域进行合并； 第2步：用FILTER函数对合并后的数据按条件筛选； 第3步：用SORT函数对筛选的数据进行排序； 第4步：用IF函数对不满足要求的数据进行处理； 函数书写步骤 如果对嵌套函数不是很熟悉的话，应将各个子函数写在其它不受影响的单元格中，并验证公式的正确性，最后将公式复制到一起。 第1步：先写VSTACK合并函数，本例中有4个数据区域需要合并，分别是所有数据区域、销售日期列、名称列、销售方式列 (1) 所有数据区域合并： =(VSTACK(观音桥门店:两江新区门店!A3:G200)） (2) 销售日期列合并： =(VSTACK(观音桥门店:两江新区门店!A3:A200) (3) 名称列合并： =(VSTACK(观音桥门店:两江新区门店!B3:B200)) (4) 销售方式列合并： =(VSTACK(观音桥门店:两江新区门店!G3:G200)) 小技巧： 分步书写公式时，最好将整个公式用括号括起来，避免遗漏括号公式报错； 在选取十张分表的数据区域时，点击第一张分表的标签，按住shift键后点击最后一张分表的标签，这样10张分表都被选中了，框选数据区域时会将10张分表的区域都被框选。 合并区域的行数应该要相同，本例中都是3行到200行，为了数据的可扩展性，行数尽量多选一些，多选后会出现空值，这就是为什么筛选条件中有个销售日期不为空的条件，就是为了屏蔽多选的这些行。 第2步：整理3个筛选条件 (1) 第1个条件：商品名称下拉框内为空值或在分表名称区域匹对 =(B3=""和VSTACK(观音桥门店:两江新区门店!B3:B200)=B3) (2) 第2个条件：销售方式下拉框内为空值或在分表销售方式区域匹对 =(E3=""和VSTACK(观音桥门店:两江新区门店!G3:G200)=E3) (3) 第3个条件：分表中销售日期不为空值（为了屏蔽数据区域的空值） =(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"") (4) 将3个条件用*连接 =((B3=""和VSTACK(观音桥门店:两江新区门店!B3:B200)=B3)*(E3=""和VSTACK(观音桥门店:两江新区门店!G3:G200)=E3)*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")) 第3步：写筛选函数：FILTER(数据区域，筛选条件)，第3参数不写，最后要用IF函数处理无匹配项。其中数据区域为第1步中合并的所有数据区域，筛选条件是第2步整理的3个筛选条件。筛选公式为： =(FILTER((VSTACK(观音桥门店:两江新区门店!A3:G200)),((B3=""和VSTACK(观音桥门店:两江新区门店!B3:B200)=B3)*(E3=""和VSTACK(观音桥门店:两江新区门店!G3:G200)=E3)*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")))) 第4步：写排序函数：SORT（数据区域，排序方式），其中数据区域就FILTER函数筛选出的数据，按销售日期排序，销售日期在数据区域的第1列，所以排序方式为1,其余两个参数不写，默认。排序公式为： =SORT((FILTER((VSTACK(观音桥门店:两江新区门店!A3:G200)),((B3=""和VSTACK(观音桥门店:两江新区门店!B3:B200)=B3)*(E3=""和VSTACK(观音桥门店:两江新区门店!G3:G200)=E3)*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")))),1) 第5步：用IF函数处理无匹配项时出现的错误值或无效值，当商品名称下拉框和销售方式下拉框中均为空值时，函数返回空值，否则按FILTER函数筛选并按SORT函数排序。最后的公式为： =IF(AND(B3="",E3=""),"",SORT(FILTER(VSTACK(观音桥门店:两江新区门店!A3:G200),((B3="")+(VSTACK(观音桥门店:两江新区门店!B3:B200)=B3))*((E3="")+(VSTACK(观音桥门店:两江新区门店!G3:G200)=E3))*(VSTACK(观音桥门店:两江新区门店!A3:A200)<>"")),1)) 小技巧 如果用分步法写公式时，写最顶层函数时，先不写等号，或在等号前加个符号，便于从其他单元格复制公式； 没完成一层公式后，补齐等号验证公式的正确性，有错误时及时订正。