当条件在谷歌工作表中匹配时，查询函数具有唯一的Filter 2列

Question

我有一个车队维护的维护计划表，因为每当服务状态为"；Live"和设备，过滤器的服务类型是独一无二的。我需要帮助找出这个公式。

我试过的样本配方是对是错：

电子表格链接

=ARRAYFORMULA(query('Master Schedule'!A1:O,"Select B,E,F,M,N,O where O='Live'",1),
Query('Master Schedule'!A1:O,"Select * "&UNIQUE('Master Schedule'!B2:E)&"",1))
=Query(Query('Master Schedule'!A1:O,"Select B,E,F,M,N,O where O='Live'"),
"Select '"&UNIQUE({'Master Schedule'!B:B, 'Master Schedule'!B:B&'Master Schedule'!E:E, 'Master Schedule'!E:E})&"'",1)
=Arrayformula(VLookup(Filter(Unique('Master Schedule'!B:B &'Master Schedule'!E:E);
Unique('Master Schedule'!B2:E)<>"");Query('Master Schedule'!A:O; "Select B, E,F,M,N,O ";1); {123456};0))

主进度

要求的结果

Answer 1

试试这个

=query(arrayformula(IFERROR(VLOOKUP(unique((Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M)),
query({arrayformula(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M),Data!B:B,Data!E:E,Data!F:F,Data!M:O},"select * where Col7='Live'"),
{2,3,4,5,6,7},0))),"select * where Col1 is not null")

或使用分号表示

=query(arrayformula(IFERROR(VLOOKUP(unique((Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M));
query({arrayformula(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M)Data!B:BData!E:EData!F:FData!M:O};"select * where Col7='Live'");
{234567};0)));"select * where Col1 is not null")

解释

建立一个作为unique(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M)的唯一引用

使用此引用构建一个新矩阵，并且仅将"活动"行作为query({arrayformula(Data!B:B&"~"&Data!E:E&"~"&Data!F:F&"~"&Data!M:M),Data!B:B,Data!E:E,Data!F:F,Data!M:O},"select * where Col7='Live'")

然后应用vlookup