主页 / coding / 问题 / 76922707
Accepted
Jhon Baptist
Asked: 2023-08-17 22:53:26 +0800 CST

如何使用查询根据多个复选框和下拉菜单进行过滤？

  • 772

我正在尝试创建一个公式来根据选定的复选框和下拉菜单中的值执行查询。然后，格式化要显示的数据，如下所示：

我需要将每栋建筑一天内完成的所有单元分组为一排。但由于我正在创建发票系统，并且每种作业类型的计费方式都不同，因此我需要确保每一行上只有 1 种作业类型。

但不知道是否可以用公式实现，这比app Script方便得多。

这是下面共享的电子表格的一部分：

日期 客户 单元 房间 工作类型 没完成
08/13 莫妮卡 1号楼 1C dpt 真的
08/13 莫妮卡 1号楼 D dpt 真的
08/13 莫妮卡 1号楼 2 dpt 真的
08/13 莫妮卡 1号楼 1* dpt 真的
08/13 莫妮卡 1号楼 3 dpt 真的
08/13 莫妮卡 1号楼 12 dpt 错误的
08/13 莫妮卡 1号楼 8 dpt 错误的
08/13 莫妮卡 1号楼 9 dpt 错误的
08/13 莫妮卡 1号楼 10 dpt 错误的
08/13 莫妮卡 1号楼 4 dpt 错误的
08/13 莫妮卡 1号楼 2 dpt 错误的
08/13 莫妮卡 2号楼 8 dpt 错误的

这是电子表格，其中解释了所有选项卡

我尝试使用辅助表来实现这一点，但它很混乱，只完成了一半的工作，我将其粘贴，以便您有一个想法，但不要关注它，因为会与共享发生冲突表，我认为不值得您花时间研究它：

辅助表 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))}))))

单位报告 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)&"'")
       )

我可以分享一个更完整的电子表格，但为了节省您的时间，我只分享了我的一个具体疑问，所以我可以尝试自己完成其余的工作。

google-sheets

1 个回答

  1. Best Answer

    这是您可以测试的一种可能的解决方案：

    使用上面您自己的查询作为 step_1 基础数据集

    =QUERY('Unit Log'!A2:F; "select *"&" where "&IF(A4="Yes";"E";"A")&" is not null"& IF(A2<>"";" and A >= date '"&TEXT(A2;"yyyy-mm-dd")&"'";"")& IF(C2<>"";" and A <= date '"&TEXT(C2;"yyyy-mm-dd")&"'";"")& IF(A6=True;;" and B matches '"&TEXTJOIN("|";TRUE;A7:A)&"'")& IF(C6=True;;" and C matches '"&TEXTJOIN("|";TRUE;C7:C)&"'"))

    公式：

    =let(Λ;{E3:E\G3:G\I3:I};Σ;unique(filter(Λ;index(Λ;;1)<>""));
     map(index(Σ;;1);index(Σ;;2);index(Σ;;3);lambda(date;unit;job;{date\unit\textjoin(", ";1;filter(H:H&" ("&I:I&")";E:E=date;G:G=unit;I:I=job))})))
    • Column_A有人将选项卡中的日期重新格式化Unit Log为有效日期格式（感谢他们！！）；在重新使用原始工作表中的公式时，您可能需要仔细检查这一点
    • Column_DUnit Log选项卡Room的格式设置为TEXT数据类型，因为查询在单列中呈现混合数据类型时出现问题

    在此输入图像描述

    • 0

相关问题