英文:
Trying to nest a transpose formula inside an array formula
问题
Here's the sheet: https://docs.google.com/spreadsheets/d/1TbPKehggzCGWy6LyEuzHZc5RouBHhyju35bqIGpInhM/edit#gid=14120435
I know how to transpose the data in colA (=Transpose(A2:A)). I know how to write an array formula to output the data i'd like (F1), but I'd like to nest the Transpose formula into the formula in F1 so that as items are added to colA, the table on the right populates with the header rows relying on the values in A2:A and the data as it is in ColF
英文:
Here's the sheet: https://docs.google.com/spreadsheets/d/1TbPKehggzCGWy6LyEuzHZc5RouBHhyju35bqIGpInhM/edit#gid=14120435
I know how to transpose the data in colA (=Transpose(A2:A)). I know how to write an array formula to output the data i'd like (F1), but I'd like to nest the Transpose formula into the formula in F1 so that as items are added to colA, the table on the right populates with the header rows relying on the values in A2:A and the data as it is in ColF
答案1
得分: 0
你可以尝试以下代码：
=ARRAYFORMULA(LET(header,转换为行(A2:A,1),{header;IF(D2:D="","",E2:E*VLOOKUP(header,A:B,2,))})
或者：
=ARRAYFORMULA(LET(header,筛选(A2:A,A2:A<>""),{header;IF(D2:D="","",E2:E*VLOOKUP(header,A:B,2,))})
英文:
You could try:
=ARRAYFORMULA(LET(header,TOROW(A2:A,1),{header;IF(D2:D="",,E2:E*VLOOKUP(header,A:B,2,))}))
Or:
=ARRAYFORMULA(LET(header,TRANSPOSE(FILTER(A2:A,A2:A<>"")),{header;IF(D2:D="",,E2:E*VLOOKUP(header,A:B,2,))}))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论