用一个跨表多条件搜索的经典案例解锁Excel函数嵌套的底层逻辑

公式3：T3=(VSTACK(观音桥门店:两江新区门店!B3:B10))

合并销售方式区域：

公式4：U3=（VSTACK(观音桥门店:两江新区门店!G3:G100)）

小技巧及注意事项：

(1) 因为嵌套函数稍微复杂一些，新手可以将每个公式先写在某个不受影响的单元格中，如K3、S3、T3、U3中，在函数嵌套时复制过去；

(2) 写公式需要跨表选择数据区域时，选中第一张分表，按住shift键后选中最后一张分表，这样10张分表都会被选中，框选数据区域时就会将所有选中分表的区域均选中，如：“观音桥门店:两江新区门店!G3:G100)”

(3)写嵌套函数时，最好提前将公式用括号括起来，避免在嵌套函数时遗漏括号导致出错；

(4) 所有公式中选取数据的行数必须一致，如上面四个公式中的数据区域均是3行到100行。

第2步

纯手工制作的第2步就是筛选，Excel也提供了实现筛选功能的函数FILTER。其用法是=FILTER(数据区域，筛选条件，无符合项时该怎么办)，因为FILTER函数是数组函数，在进行多条件筛选时，用*代替AND函数，用+代替OR函数，不能直接用AND和OR函数。

那么前面理顺的筛选条件应该改为：

=((条件1="")+(条件1=商品名称))*((条件2="")+(条件2=销售方式))

结合案例，条件1为B3单元格中的内容，条件2为E3单元格中的内容，商品名称为上述公式3，销售区域为上述公式4，用相应单元格和公式替换后，筛选条件的表达式为：

公式5：=(((B3="")+(B3=公式3))*((E3="")+(E3=公式4)))

用FILTER函数筛选的公式为：=FILTER(公式1，公式5)

注：FILTER函数的第三参数“无匹配项时该怎么办”先空着，后面再来处理

当写完公式验证时发现，有效数据后面有很多无效的0数据，这是因为分表中有效的数据区域可能只有20几行，为了数据的可扩展性，在选取数据区域时选了第3行到100行的区域，有空行。那么还要剔除掉空行。应该在筛选条件中需增加一个条件，仅筛选有销售日期的数据，即筛选销售日期不为空值的数据，公式表达式为公式2<>""，那么完整的筛选条件为：

公式6：

=(((B3="")+(B3=公式3))*((E3="")+(E3=公式4))*(公式2<>""))

完整的筛选公式为：

公式7：=(FILTER(公式1，公式6))

第3步：

纯手工制作的第3步就是排序，Exce同样提供了排序函数SORT，用法是SORT(需要排序的数据，按第几列排序，升序还是降序，按行排序还是按列排序)，我们这里需要排序的数据区域是按条件筛选出来的所有数据区域即公式7，按销售日期（第1列）排序，排序后的公式为：

公式8：=SORT(函数7,1)，

注：SORT函数的另外两个函数可不填，即为默认值。

第4步：

第4步就是最后一步，当两个查询条件都为空值时，数据区域返空，只要有一个不为空值时，按要求提取数据，公式表达式为

函数9：

=IF(AND(B3="",E3=""),"",公式8)

完整函数为：

=IF(AND(B3="",E3=""),"",SORT(FILTER((VSTACK(观音桥门店:两江新区门店!A3:G100)),((B3="")+(B3=VSTACK(观音桥门店:两江新区门店!B3:B100)))*((E3="")+(E3=VSTACK(观音桥门店:两江新区门店!G3:G100)))*(VSTACK(观音桥门店:两江新区门店!A3:A100)<>"")),1))

嵌套函数编写的小技巧：

(1) 先将内层公式写在其它不受影响的单元格中，并验证公式的正确性；

(2) 在写外层函数时，先不写等号，将内层函数复制进去后，最后输入等号；

案例源文件下载链接：8月份销售明细.xlsx