如何用QUERY函数结合复选框与下拉菜单筛选并分组发票数据？

安全阿岳

2026-7-13

发票系统数据合并与查询需求

我正尝试创建公式，基于选中的复选框和下拉菜单的值执行查询，之后按要求格式化显示数据：需将单日单楼宇完成的所有单元合并到一行，但由于正在构建发票系统，不同Job Type计费方式不同，因此要确保每行仅包含一种Job Type。我不确定仅用公式能否实现（这比App Script更便捷）。

部分数据示例

日期	客户	楼宇	房间	Job Type	未完成
08/13	Monica	Building1	1C	Dpt	TRUE
08/13	Monica	Building1	D	Dpt	TRUE
08/13	Monica	Building1	2	Dpt	TRUE
08/13	Monica	Building1	1*	Dpt	TRUE
08/13	Monica	Building1	3	Dpt	TRUE
08/13	Monica	Building1	12	Dpt	FALSE
08/13	Monica	Building1	8	Dpt	FALSE
08/13	Monica	Building1	9	Dpt	FALSE
08/13	Monica	Building1	10	Dpt	FALSE
08/13	Monica	Building1	4	Dpt	FALSE
08/13	Monica	Building1	2	Dpt	FALSE
08/13	Monica	Building2	8	Dpt	FALSE

已尝试的公式参考

辅助工作表A2:E单元格公式

=IF('Units Report'!C4="Yes",filter('Unit Log'!A2:A,NOT(REGEXMATCH('Unit Log'!J2:J,"\!"))),ARRAYFORMULA('Unit Log'!A2:A))

辅助工作表AA2:AE单元格公式

=sort(let(a,unique({A2:INDEX(A:A,MATCH(3,1/(A:A<>""))),C2:INDEX(C:C,MATCH(3,1/(C:C<>""))),B2:INDEX(B:B,MATCH(3,1/(B:B<>"")))}),map(INDEX(a,,1),INDEX(a,,2),INDEX(a,,3),lambda(y,z,f,{to_date(y),f,z,join(", ",filter(D:D&IF(LEN(E:E)," ("&E:E&")",),A:A&C:C&B:B=y&z&f)),join(", ",filter(D:D,A:A&C:C=y&z))}))))

Units Report E2单元格公式

=QUERY('Helper sheet'!AA2:AG,
       "select "&IF(A4="Yes","AA","AA")&
       " where "&IF(A4="Yes","AA","AA")&" is not null"&
       IF(A2<>""," and AA >= date '"&TEXT(A2,"yyyy-mm-dd")&"'",""
       IF(C2<>""," and AA <= date '"&TEXT(C2,"yyyy-mm-dd")&"'",""
       IF(A6=True,," and AB matches '"&TEXTJOIN("|",TRUE,A7:A)&"'")&
       IF(C6=True,," and AC matches '"&TEXTJOIN("|",TRUE,C7:C)&"'")
       )