我有2组数据。一是坦克名称

Tank Name  
  A1
  A2
  B1
  B2

接下来是开/关数据

ON/OFF
  0
  1
  1
  1
  1
  1
  0
  0
  1
  1
  1
  1
  1
  1
  1
  0
  0
  1
  1
  1
  0
  1
  1 

现在我要查看的结果是，当ON/OFF为1时，将提及第一个水箱：当它为0时，则不提及任何水箱。一旦提到了所有坦克，那么它应该再次从第一个坦克开始，即A1。但是如果0介于两者之间，那么它应该再次从A1开始..像这样

Result expected
 0  
 1  A1
 1  A2
 1  B1
 1  B2
 1  A1
 0  
 0  
 1  A1
 1  A2
 1  B1
 1  B2
 1  A1
 1  A2
 1  B1
 0  
 0  
 1  A1
 1  A2
 1  B1
 0  
 1  A1
 1  A2

您可以在此处查看 google 表格：场景 2https://docs.google.com/spreadsheets/d/1SP2SfA-bzzhHgfrvpyUIkeQfUykata0oHxyD-x69yxE/edit?usp=sharing https://docs.google.com/spreadsheets/d/1SP2SfA-bzzhHgfrvpyUIkeQfUykata0oHxyD-x69yxE/edit?usp=sharing

希望得到一些帮助来解决这个问题。 谢谢

Edit

单一配方解决方案：

=FILTER(IF(B1:B=0,"",VLOOKUP(IFERROR(VLOOKUP(SUMIF(ROW(B1:B),"<="&ROW(B1:B),B1:B),{ROW(INDIRECT("a1:a"&COUNTIF(B:B,1))) , TRANSPOSE(SPLIT(TEXTJOIN("",1,TRANSPOSE(TEXT(ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))*(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0"))>=ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))),"0"","";;"))),",")) },2,),0),{row(INDIRECT("a1:a"&COUNTIF(B1:B,1))) , VLOOKUP(MOD(ROW( INDIRECT("a1:a"&COUNTIF(B1:B,1)) )-1,COUNTA(A1:A))+1,{ROW(A1:A)-row(A1)+1,A1:A},2,)},2,)),B1:B<>"")

两个公式就可以了。

C1 中排名第一。对于带有循环的累积和：

=Filter(IFERROR(VLOOKUP(SUMIF(ROW(B1:B),"<="&ROW(B1:B),B1:B),{ROW(INDIRECT("a1:a"&COUNTIF(B:B,1))) , TRANSPOSE(SPLIT(TEXTJOIN("",1,TRANSPOSE(TEXT(ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))*(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0"))>=ROW(INDIRECT("a1:a"&MAX(LEN(SPLIT(TEXTJOIN("",1,B1:B),"0")))))), "0"","";;"))),",")) },2,),0),B1:B<>"")

D1 中排名第二。对于结果：

=FILTER(IF(B1:B=0,"",VLOOKUP(C1:C,{row(INDIRECT("a1:a"&COUNTIF(B1:B,1))) , VLOOKUP(MOD(ROW( INDIRECT("a1:a"&COUNTIF(B1:B,1)) )-1,COUNTA(A1:A))+1,{ROW(A1:A)-row(A1)+1,A1:A},2,)},2,)),B1:B<>"")

参考：

1. 计数器实验室 https://docs.google.com/spreadsheets/d/1MgqPVZm5Khackfa1UE9s3aaYskqH8yREdIgVojLhHf4/copy
2. 数组公式实验室 https://docs.google.com/spreadsheets/d/1VY157ykKsCVDqEKDBp3oAVaG0LTXAz8wUCggCrFXMDM/copy