展开/展开具有工单打开和关闭状态的Google表格表格，以显示每月打开的所有工单
IMTheNachoMan
我的工作示例/ Google表格示例：https：//docs.google.com/spreadsheets/d/1AvAb_qnrrexYl5W5wAW6aA6P552T-prBxoONOis-XZM/edit？usp = sharing
我有一个包含票证信息的源表。为了这个问题，只有三列很重要：
- 票证编号
- 开票日期
- 门票关闭日期-如果关闭
根据这些信息，我需要生成显示特定月份的指标/报告：
- 当月开张的票
- 当月关闭的车票
- 已打开的票（之前已打开且未关闭）
而且，我需要原始数据，而不仅仅是每月摘要。这意味着，我应该能够选择一个月份并显示与以上三个条件匹配的所有票证ID。
使用此功能很容易获得前两个（在我的样本表中列EG）：
{
QUERY(A2:C, "SELECT B, 'Opened', A WHERE A IS NOT NULL LABEL 'Opened' '' FORMAT B 'YYYY-MM'");
QUERY(A2:C, "SELECT C, 'Closed', A WHERE C IS NOT NULL LABEL 'Closed' '' FORMAT C 'YYYY-MM'")
}
我被困在第三名。我试图弄清楚如何每月打开一张票。每月打开一张票，应该在其中一行：
- 第一列是月份
- 第二列是“打开”
- 第三列是工单ID
例如：
- 编号0010
- 2018-11年开业
- 于2019-01年关闭
- 打开：
- 2018-12
- 编号0019
- 于2018-08年开业
- 于2019-03年关闭
- 打开：
- 2018-09
- 2018-10
- 2018-11
- 2018-12
- 2019-01
- 2019-02
会成为：
| Month | Status | Ticket ID |
|---------|--------|-----------|
| 2018-11 | Opened | ID0010 |
| 2018-12 | Open | ID0010 |
| 2019-01 | Closed | ID0010 |
| 2018-08 | Opened | ID0019 |
| 2018-09 | Open | ID0019 |
| 2018-10 | Open | ID0019 |
| 2018-11 | Open | ID0019 |
| 2018-12 | Open | ID0019 |
| 2019-01 | Open | ID0019 |
| 2019-02 | Open | ID0019 |
| 2019-03 | Closed | ID0019 |
注意：打开和关闭故障单的月份也不会显示故障单的“打开”状态。换句话说，机票在月份中只应具有“开放”时间：
- 之后但不包括打开
- 之前且不包括关闭
而且，如果尚未关闭车票，则直到今天为止，每个月都要排一次票。
我以为可以在公认的Google表格公式解决方案中使用该概念来计算具有不同开始/结束日期，重叠和间隔的任务的实际总工时，但是由于数据量太大，我遇到了错误。该公式在我的样本表的I2中。
CONCATENATE的文本结果超出了50000个字符的限制。
所以我想知道是否还有另一种方式可以获取所需的数据。我知道如何使用自定义功能执行此操作，但我希望尽可能避免使用自定义功能。
更新解决方案
我能够接受player0答案的关键部分，并将其用于我的原始解决方案中。它的缺点是，而不是CONCATENATE用来合并列/行，而是使用QUERYwith999^99作为报头计数（第3个参数），因为QUERY这时将合并所有行（使用空格分隔符）。
最终公式为：
=ARRAYFORMULA(
QUERY(
SPLIT(
TRANSPOSE(
SPLIT(
QUERY(
TRANSPOSE(
TRIM(
QUERY(
IF(
A2:A <> "",
A2:A & "," & EDATE(
IFERROR(
SPLIT(
REPT(
EOMONTH(B2:B, -1) + 1 & ",",
DATEDIF(
EOMONTH(B2:B, 0) + 1,
EOMONTH(
IF(
C2:C <> "",
C2:C,
EDATE(TODAY(), 1)
),
0
),
"M"
)
),
","
),
0
),
TRANSPOSE(
ROW(
INDIRECT(
"A1:A" & MAX(
IF(
B2:B <> "",
IFERROR(
DATEDIF(
EOMONTH(B2:B, 0) + 1,
EOMONTH(
IF(
C2:C <> "",
C2:C,
EDATE(TODAY(), 1)
),
0
),
"M"
),
0
),
)
)
)
)
)
) & ";",
),
,
999^99
)
)
),
,
999^99
) & " ",
"; "
)
),
","
),
"SELECT Col2, 'Open', Col1 WHERE Col2 > 4000 LABEL 'Open' '' FORMAT Col2 'YYYY-MM'",
0
)
)
更新以使用SEQUENCE
=ARRAYFORMULA(
QUERY(
SPLIT(
TRANSPOSE(
SPLIT(
QUERY(
TRANSPOSE(
TRIM(
QUERY(
IF(
A2:A <> "",
A2:A & "," & EDATE(
IFERROR(
SPLIT(
REPT(
EOMONTH(B2:B, -1) + 1 & ",",
DATEDIF(
EOMONTH(B2:B, 0) + 1,
EOMONTH(
IF(
C2:C <> "",
C2:C,
EDATE(TODAY(), 1)
),
0
),
"M"
)
),
","
),
0
),
SEQUENCE(
1,
MAX(
IF(
B2:B <> "",
IFERROR(
DATEDIF(
EOMONTH(B2:B, 0) + 1,
EOMONTH(
IF(
C2:C <> "",
C2:C,
EDATE(TODAY(), 1)
),
0
),
"M"
),
0
),
)
)
)
) & ";",
),
,
999^99
)
)
),
,
999^99
) & " ",
"; "
)
),
","
),
"SELECT Col2, 'Open', Col1 WHERE Col2 > 4000 LABEL 'Open' '' FORMAT Col2 'YYYY-MM'",
0
)
)
玩家0
相同的逻辑：
=ARRAYFORMULA(UNIQUE(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
"♦"&FILTER(A2:A, B2:B<>"", C2:C<>"")&"♠"&SPLIT(REPT(FILTER(B2:B, B2:B<>"", C2:C<>"")+1&"♣",
NETWORKDAYS(FILTER(B2:B, B2:B<>"", C2:C<>"")+1, FILTER(C2:C, B2:B<>"", C2:C<>""))), "♣")+
TRANSPOSE(ROW(INDIRECT("A1:A"&MAX(
NETWORKDAYS(FILTER(B2:B, B2:B<>"", C2:C<>"")+1, FILTER(C2:C, B2:B<>"", C2:C<>"")))))-1)&"♠")
,,999^99)),,999^99), "♦")), "♠"),
"select Col2,'Open',Col1 where Col2>4000 label 'Open''' format Col2 'YYYY-MM'", 0)))
更新：
=ARRAYFORMULA(UNIQUE(QUERY(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE("♦"&
FILTER(A2:A, B2:B<>"", C2:C<>"", MONTH(B2:B)<>MONTH(C2:C), YEAR(B2:B)<>YEAR(C2:C))&"♠"&EOMONTH(SPLIT(REPT(
FILTER(B2:B, B2:B<>"", C2:C<>"", MONTH(B2:B)<>MONTH(C2:C), YEAR(B2:B)<>YEAR(C2:C))+1&"♣", DATEDIF(
FILTER(B2:B, B2:B<>"", C2:C<>"", MONTH(B2:B)<>MONTH(C2:C), YEAR(B2:B)<>YEAR(C2:C))+1,
FILTER(C2:C, B2:B<>"", C2:C<>"", MONTH(B2:B)<>MONTH(C2:C), YEAR(B2:B)<>YEAR(C2:C)), "M")-1), "♣"),
TRANSPOSE(ROW(INDIRECT("A1:A"&MAX(NETWORKDAYS(
FILTER(B2:B, B2:B<>"", C2:C<>"", MONTH(B2:B)<>MONTH(C2:C), YEAR(B2:B)<>YEAR(C2:C))+1,
FILTER(C2:C, B2:B<>"", C2:C<>"", MONTH(B2:B)<>MONTH(C2:C), YEAR(B2:B)<>YEAR(C2:C))))))))&"♠")
,,999^99)),,999^99), "♦")), "♠"),
"select Col2,'Open',Col1 where Col2>4000 label 'Open''' format Col2 'YYYY-MM'", 0)))
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
编辑于
相关文章
TOP 榜单
- 1
UITableView的项目向下滚动后更改颜色，然后快速备份
- 2
Linux的官方Adobe Flash存储库是否已过时？
- 3
用日期数据透视表和日期顺序查询
- 4
应用发明者仅从列表中选择一个随机项一次
- 5
Mac OS X更新后的GRUB 2问题
- 6
验证REST API参数
- 7
Java Eclipse中的错误13，如何解决？
- 8
带有错误“ where”条件的查询如何返回结果？
- 9
ggplot：对齐多个分面图-所有大小不同的分面
- 10
尝试反复更改屏幕上按钮的位置 - kotlin android studio
- 11
如何从视图一次更新多行（ASP.NET - Core）
- 12
计算数据帧中每行的NA
- 13
蓝屏死机没有修复解决方案
- 14
在 Python 2.7 中。如何从文件中读取特定文本并分配给变量
- 15
离子动态工具栏背景色
- 16
VB.net将2条特定行导出到DataGridView
- 17
通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException
- 18
在Windows 7中无法删除文件（2）
- 19
python中的boto3文件上传
- 20
当我尝试下载 StanfordNLP en 模型时，出现错误
- 21
Node.js中未捕获的异常错误，发生调用
我来说两句