循环浏览一组Google工作表值 [英] Looping through a set of google sheet values
问题描述
我有2组数据.一种是坦克名称
I have 2 sets of data. One is tank names
Tank Name
A1
A2
B1
B2
下一个是ON/OFF数据
and the next is ON/OFF Data
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和0之间,那么它应该再次从A1开始..
Now the result I am looking is, when the ON/OFF is 1 then the first tank is to be mentioned: when it's 0, no tank to be mentioned. Once all the tanks are mentioned,then it should again start from the first tank ie A1.But if 0 comes in between then it should start again from A1 .. like this
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工作表:方案2 https://docs.google.com/spreadsheets/d/1SP2SfA-bzzhHgfrvpyUIkeQfUykata0oHxyD-x69yxE/edit?usp=sharing
You can check the google sheet here : Scenario 2 https://docs.google.com/spreadsheets/d/1SP2SfA-bzzhHgfrvpyUIkeQfUykata0oHxyD-x69yxE/edit?usp=sharing
希望获得一些帮助以解决此问题. 谢谢
Hope to get some help to get this solved. Thanks
推荐答案
编辑
单一公式解决方案:
Edit
Single formula solution:
=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<>"")
两个公式都可以做到.
Two formulas will do it.
#1.对于具有循环的累加总和:
#1 in C1. For cumulative sum with a loop:
=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中的#2.对于结果:
#2 in D1. For the result:
=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<>"")
参考:
- Counters Lab
- Array Formulas Lab
这篇关于循环浏览一组Google工作表值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!