google-sheets - 在 Google Sheet 中查询条件中的数组

Question

我有 2 个标签：

• 每天应该完成的任务列表和列的概述。该任务可以多次打开或完成。
• 耻辱墙 - 它应该包括截止日期已过的开放任务。截止日期应从概览选项卡的第 1 行填写。如果任务多次逾期，则应将其指定为具有新截止日期的新行。

我需要有关“耻辱墙”选项卡公式的帮助。请看我的例子 - https://docs.google.com/spreadsheets/d/1gaH-XCf9JtM0C2pJU9EidOk1hsLhmZ5J_b-5_0JUgyc/edit#gid=113767391

标签： google-sheetsspreadsheet

Answer 1

在单元格中尝试此A1操作，删除“耻辱墙”表中的所有其他内容：

=arrayformula(
query({"Department"\"Activity"\"Owner"\"Deadline"\"Date open"\"Overdue, days";
query({
flatten(split(rept("|"&Overview!A2:A;columns(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0)));"|"))\
flatten(split(rept("|"&Overview!B2:B;columns(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0)));"|"))\
flatten(split(rept("|"&Overview!C2:C;columns(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0)));"|"))\
flatten(split(rept("|"&Overview!E2:E;columns(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0)));"|"))\
flatten(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0))\
if(flatten(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0))<>"";today()-flatten(query(iferror(split(flatten(trim(query(transpose(if(Overview!F2:AA="Open";if(Overview!F1:AA1<=today();Overview!F$1:AA$1;);));"";9^9)));" "););"";0));)
};"where Col6 is not null ";0)};
"select Col1,Col2,Col3,Col4,Col5,Col6";1))

我已将整个内容放在查询中，以便您可以决定要选择哪些列，即您可以适应select Col1,Col2,Col3,Col4,Col6