google-sheets - 如何从 Google 表格中的数据集创建新表？

Question

我有一个记录器，它记录一个日期/时间和一个值，即

05/06/21 11:29:43    0
05/06/21 11:29:48    0
05/06/21 11:29:53    0
05/06/21 11:29:58    1
05/06/21 11:30:03    1
05/06/21 11:30:08    1
05/06/21 11:30:13    0

在 30 天的时间段内记录样品。

我想创建一个新的数据集来过滤此日志记录数据以记录当前序列中每个值的开始和结束时间，即

05/06/21 11:29:43    05/06/21 11:29:53    0
05/06/21 11:29:58    05/06/21 11:30:08    1
05/06/21 11:30:13    05/06/21 11:30:13    0

我也（另外）想要一个新的数据集，它是按日期计算的每个值的 COUNT，即

05/06/21    0   4
05/06/21    1   3

使用 Google 表格如何最好地实现其中任何一个？

标签： google-sheets

Answer 1

第 1 部分- 进入I3样品表的单元格：

=arrayformula({iferror(vlookup(query(if(if(A2:A<>"",B3:B,)=if(A2:A<>"",B1:B,),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A)),if(B2:B=if(A2:A<>"",B3:B,),row(A2:A),)),"where Col1 is not null",0),{row($A:$A),$A:$A},2,false),),iferror(vlookup(query(if(if(A2:A<>"",B3:B,)="",if(A2:A<>"",row(A2:A),),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A))),"where Col1 is not null",0),{row($A:$A),$A:$B},{2,3},false),),query(if(if(A2:A<>"",B3:B,)="",if(A2:A<>"",row(A2:A),),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A))),"where Col1 is not null",0)-query(if(if(A2:A<>"",B3:B,)=if(A2:A<>"",B1:B,),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A)),if(B2:B=if(A2:A<>"",B3:B,),row(A2:A),)),"where Col1 is not null",0)+1})

第 1 部分的工作：

查看B 列中安培的连续值，我们在序列开始时从 A 列捕获开始日期/时间，然后在序列结束时捕获停止日期/时间。如果序列中只有一个值（单元格B21），则该行是开始值和停止值。结束值（单元格B119）也是一个停止值。

以下结合了 3 个公式 in 和 array {1,2,3}：

#1 - 获取开始日期/时间（vlookup获取 col A）：

=arrayformula(iferror(vlookup(query(if(if(A2:A<>"",B3:B,)=if(A2:A<>"",B1:B,),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A)),if(B2:B=if(A2:A<>"",B3:B,),row(A2:A),)),"where Col1 is not null",0),{row($A:$A),$A:$A},2,false),))

它查看 B 列以查看当前单元格上方的值匹配的位置，但下方的值不同。它还匹配上面和下面的值不同的地方（单个条目）。

#2 - 获取停止日期/时间和安培（vlookup获取 col A 和 B）：

=arrayformula(iferror(vlookup(query(if(if(A2:A<>"",B3:B,)="",if(A2:A<>"",row(A2:A),),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A))),"where Col1 is not null",0),{row($A:$A),$A:$B},{2,3},false),))

它查看 B 列以查看当前单元格下方的值匹配的位置，但上面的值不同。它还匹配上面和下面的值不同的地方（单个条目），以及下面的值是空白的地方（停止值）。

#3 - 获取计数（停止行号减去开始行号，+1）：

=arrayformula(query(if(if(A2:A<>"",B3:B,)="",if(A2:A<>"",row(A2:A),),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A))),"where Col1 is not null",0)-query(if(if(A2:A<>"",B3:B,)=if(A2:A<>"",B1:B,),if(B2:B=if(A2:A<>"",B3:B,),,row(A2:A)),if(B2:B=if(A2:A<>"",B3:B,),row(A2:A),)),"where Col1 is not null",0)+1)

第 2 部分- 进入E2样品表的单元格：

=arrayformula(query({A:B,text(A:A,"dd/mm/yy")},"select Col3,Col2,count(Col2) where Col1 is not null group by Col3,Col2 label Col2 'Value', Col3 'Day', count(Col2) 'Count' ",0))

QUERY用于选择安培的功能，计算它们的出现次数，然后按天分组。