多表指定日期与时间的生产数据查询No.260

古哥计划

2024年03月优秀创作者

某工厂的生产运行数据报表分别在一张表格里面的不同页上，每个页面登记着具体的某一天的生产运行数据。具体的格式是固定的，包含日期、时间、设备1#和2#的生产运行数据，登记了24小时的数据。

具体如下图1所示：

图1

现在需要建立一个汇总的查询表，查询有登记的所有日期的具体某一天，某一段开始时间与结束时间的生产运行数据。例如输入查询条件：2023年6月4日，起始时间是24，结束时间是5，数据就可以自动查询出来。

具体如下图2所示：

图2

需求分析：

难点在于需要查询的数据分别在不同的工作页上，不过从数据上来看，虽然分了多个日期（页面），好在每个页面的格式都一样，这样的话，可以用到VSTACK函数，多页面合并。

查询条件这里分了三个录入查询点，分别是年、月、日，但是对应的生产运行数据的日期又是标准的日期格式，所以需要用到DATE函数转换。

起始时间与结束时间都是数字，需要根据这个数字的范围来确认筛选的结果（查询结果），判断具体的位置的函数是MATCH，分别判断开始和结束的位置和相互运算一下就可以得到一个数字范围，根据这个范围配合CHOOSEROWS和SEQUENCE就可以得到需求的结果。

具体如下图3所示：

图3

合并多表：

多表合并在函数VSTACK函数没有出来前是一件非常痛苦的事情，现在有了它，就变得非常简单了，只需要录入函数：

=VSTACK('1日:10日'!A6:D29)

录入技巧：

录入VSTACK函数后鼠标点到第一个页面的第一个单元格，按住Shift键后，鼠标移动到最后一个页面的最后一个单元格，这样第一个页面到最后一个页面的单元格范围就全部选中完毕了。

具体如下图4所示：

转换日期：

因为查询条件是分开的，分别为年、月、日三个单元格，多表查询中需要按标准日期来判断，所以需要把查询条件转换成标准日期格式，这个需求可以用DATE函数；

录入函数：

=DATE(G3,H3,I3)

函数释义：

通过DATE函数转换成标准的日期格式：“YYYY-MM-DD”

具体如下图5所示：

图5

筛选数据：

有了上面两个条件，现在可以先筛选第一步的数据，也就是没有起始时间和结束时间的数据。筛选数据用的函数是筛选函数FILTER.

录入函数：

=FILTER(M5#,CHOOSECOLS(M5#,1)=G6)

函数释义：

M5#为合并多表的一个汇总结题，这是一个大的范围，筛选的判断条件只有1列，所以用CHOOSECOL选择这个区域的第1列，得到日期列，日期列等于G6（标准日期），符号条件的结果就筛选出来了。

具体如下图6所示：

图6

判断位置：

查询条件起始时间和结束时间的位置，用MATCH函数来判断。

录入函数：

起始位置=MATCH(J3,CHOOSECOLS(B5#,2),0)；

结束位置=MATCH(K3,CHOOSECOLS(B5#,2),0)

位置范围=H9-H8+1

函数释义：

筛选的结果是一个获得范围，需要配合CHOOSECOLS来选择对应的时间判断列，所以这里的参数是第2列（时间在第2列），有了时间这一列,分别用MATCH判断录入的查询条件来判断起始和结束的位置。

具体如下图7所示：

图7

选择指定行

上面已经计算出位置的范围区间是6，也就是录入案例中的查询条件：2023年6月3日，24时到5时的返回行数应该是一个数组，也就是17行、18行……一直到22行。

录入函数：

=SEQUENCE(H10,,H8)

函数释义：

根据起始和结束的位置数，生成一组数字{17;18;19;20;21;22}

具体如下图8所示：

图8

把筛选后的指定日期的筛选结果移动到R列后录入函数：

录入函数：

=CHOOSEROWS(R5#,J8#)

函数释义：

R5#为筛选指定后日期的结果

J8#为生成的起始和结束的行数字

CHOOSEROWS就是选择筛选后的结果，并返回指定的行数的结果。也就是2023年6月3日起始时间为24时到结束时间为5时的数据。

具体如下图9所示：

图8

合并公式

辅助列的优点就是一步一步运算，逻辑清晰，缺点就是需要额外占用单元格。这里可以把所有辅助列去除，录入一个公式搞定，但因为WPS没有LET函数,所以公式看起来非常长，供大家学习参考。

录入函数：

=CHOOSEROWS(FILTER(VSTACK('1日:10日'!A6:D29),(CHOOSECOLS(VSTACK('1日:10日'!A6:D29),1)=DATE(G3,H3,I3))),SEQUENCE((MATCH(K3,CHOOSECOLS(FILTER(VSTACK('1日:10日'!A6:D29),(CHOOSECOLS(VSTACK('1日:10日'!A6:D29),1)=DATE(G3,H3,I3))),2),0))-(MATCH(J3,CHOOSECOLS(FILTER(VSTACK('1日:10日'!A6:D29),(CHOOSECOLS(VSTACK('1日:10日'!A6:D29),1)=DATE(G3,H3,I3))),2),0))+1,,MATCH(J3,CHOOSECOLS(FILTER(VSTACK('1日:10日'!A6:D29),(CHOOSECOLS(VSTACK('1日:10日'!A6:D29),1)=DATE(G3,H3,I3))),2),0)))

具体如下图10所示：