分享

根据客户、起止日期自动查询对账单,很难也很好用!

 Excel不加班 2019-12-26

与 30万 读者一起学Excel

学员的问题,以起止日期为条件,查找出客户在销售明细表中的销售情况。

对账单

销售明细表

两个表的要引用的列,标题名称一样,顺序有可能不同。

对于这种问题,几乎可以不用考虑,就知道用筛选公式。筛选公式很难,不过有昨天的那篇文章作为铺垫,会相当容易理解一点。

1.根据起止日期引用数据,标题和顺序完全一致

在D6输入公式,按Ctrl+Shift+Enter三键结束。

=IFERROR(INDEX(销售明细表!I:I,SMALL(IF((对账单!$E$2<=销售明细表!$A$2:$A$500)*(对账单!$H$2>=销售明细表!$A$2:$A$500),ROW($2:$500)),ROW(D1))),"")

IF((条件1)*(条件2),本身行号),条件可以1个也可以多个,满足条件就返回本身行号。

SMALL(IF((条件1)*(条件2),本身行号),ROW(D1)),从小到大依次将行号提取出来。

INDEX(返回区域,SMALL(IF((条件1)*(条件2),本身行号),ROW(D1))),将行号变成对应值。

最后,IFERROR函数,就是让错误值显示空白。

2.根据起止日期和客户名称引用数据,标题和顺序完全一致

也就是增加1个条件,这种很简单,直接增加新的条件就行。

*(销售明细表!$E$2:$E$500=对账单!$B$2)

3.根据起止日期和客户名称引用数据,标题一样但顺序不同

这种最难,需要再嵌套MATCH函数判断返回区域第几列。

MATCH(对账单!A$5,销售明细表!$1:$1,0)

同时引用区域也要改变成:销售明细表!$A:$V。

引用日期列的时候,需要将单元格设置为日期格式。

最终公式:

=IFERROR(INDEX(销售明细表!$A:$V,SMALL(IF((对账单!$E$2<=销售明细表!$A$2:$A$500)*(对账单!$H$2>=销售明细表!$A$2:$A$500)*(销售明细表!$E$2:$E$500=对账单!$B$2),ROW($2:$500)),ROW(A1)),MATCH(对账单!A$5,销售明细表!$1:$1,0)),"")

这条公式,只要1,2个月的时间能够看懂就不错了,慢慢来,先把公式收藏起来。

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多