昨天有个同事让村长帮忙设置几个公式 ，需求是这样子的，有一个订单的明细表（真实数据涉及私密，以下数据纯属我后来虚拟的）
她需要根据这个订单明细做一个单据查询（打印）的模板，如下图（虚拟）
填写单据号后自动带出客户名称、发货日期、发货仓库及发货的明细，其中根据单据号查询客户名称、发货日期、发货仓库比较简单，就是普通的VLOOKUP查询就可以了，这里简单写一下公式
客户名称
=IFERROR(VLOOKUP(C2,订单明细!$A$1:$D$381,4,0),"")
发货日期
=IFERROR(VLOOKUP(C2,订单明细!$A$1:$B$381,2,0),"")
发货仓库
=IFERROR(VLOOKUP(C2,订单明细!$A$1:$C$381,3,0),"")
接下来主要讲解一下怎么根据订单号查询相应的订单明细，对实际业务了解的人都知道，一张订单里面一般只有一个客户、一个发货日期，一个发货仓库，但是一张订单却可能会有多个产品，这个时候我们就不能简单的用VLOOKUP函数进行查询了，因为VLOOKUP函数用得是遍历法查找，只会自上而下查找到第一个符合条件的结果然后结束查找，不能查询到所有的结果，那么我们要怎么做呢？
首先我们可以在订单明细里面设置一列辅助列，订单明细J列是金额，那么我们就把J列右边那一列K列作为辅助列，K2单元格输入公式并下拉
=COUNTIF($A$2:A2,A2)&A2
效果如下图所示
这么做的用途是将单据号变成唯一，原来一个单据号对应多个产品明细，现在我们根据产品明细的个数给单据号编号，将不唯一变成了唯一，接下来就是简单的条件查询了，这里我们用INDEX+MATCH这对黄金组合函数做一下
B6单元格输入公式
=IFERROR(INDEX(订单明细!E$1:E$381,MATCH(ROW(A1)&$C$2,订单明细!$K$1:$K$381,0)),"")
然后将公式右拉下拉，效果如下图所示
正常来说需求到这里就结束了，可是村长总觉得很不对劲，为什么呢？因为一般单据号比较长我们很难记住，也不容易输入，只能从其他地方把单据号复制过来，这样使用体验就很不好了，那么我们是否可以反过来想一下，如果可以通过名称带出单据信息是不是更好一点呢？可是一个客户有多张订单，输入客户之后Excel怎么知道我们想查询哪一张订单呢？
我们再来想一下，如果我们能输入客户后，带出这个客户的所有单据号，然后单据号字段那里可以通过数据有效性进行选择，这样是不是可以呢？说做就做
首先，单据查询表里面的I列作为辅助列，查询指定客户的所有订单列表，公式如下
=IFERROR(INDEX(订单明细!A:A,SMALL(IF((订单明细!$D$1:$D$381=$F$2)*(MATCH(订单明细!$A$1:$A$381,订单明细!$A$1:$A$381,0)=ROW($1:$381)),ROW($1:$381),9^9),ROW(A1))),"")
效果如下图所示
然后J1单元格输入公式统计客户的订单总数
=SUMPRODUCT(N(I2:I13<>""))
接着我们需要打开“公式”选项卡，选择“名称管理器”，新建一个定义名称，名称设为“单据号”（可自定义），然后引用位置输入如下公式
=OFFSET($I$1,1,,$J$1)
最后我们选中C2单元格（单据号），打开“数据”选项卡，选择“数据验证”（13版本以下叫数据有效性），允许处选择“序列”，来源输入“=单据号”，保存即可。
做完以上操作已经大功告成，接下来我们通过下面两张截图对比看一下成果：
好久没有写Excel相关的教程了，一个是工作确实有点忙，再就是也想腾点时间学习一下新知识（Python），今天实在忍不住了所以就写一下，希望能对大家有所帮助，由于篇幅有限，写得太多也不知道大家喜不喜欢，所以有些优化的地方这里没有接着写，例如：
1、可以用几句VBA代码加一个按钮设置一键打印单据；
2、还可以把查询客户所有单据号的辅助列放置在一个新的工作表，既不影响单据查询（打印）模板的美观，也不影响使用；
3、再有就是当我们重新输入一个客户名称的时候，单据号字段（C2）和客户是不匹配的，需要我们重新选择单据号，否则这个单据是错的，从使用上来说应该是选择了新的客户之后订单明细应该是查询数据为空白，因为这个客户和当前单据号不匹配，这一点的修改也比较简单，只要把产品明细的查询条件再加上客户名称就可以了。
4、单据查询最多只能显示8行，如果该订单明细的实际行数超过8号需要分页该怎么处理？
好了文章就写到这里吧，如果你喜欢村长写得文章，请帮忙点赞加关注再转发，村长会争取多写一点跟实际工作相关的Excel教程跟大家一起分享。
发表评论