英文:
How to pull data matching from all tabs in the google sheet?
问题
I have an spreadsheets with 50+ tabs. I would like to pull data match by keyword as sheet name & row number from all tabs.
有一个包含50多个标签的电子表格。我想要根据关键词从所有标签的表名和行号中提取数据。
It's possible by query with specific tab name, but when there is lot of tabs, how to recall from all tabs instated to re-write all tab names in query?
可以通过使用特定标签名称的查询来实现，但当有很多标签时，如何从所有标签中检索，而不是在查询中重写所有标签名称？
Worksheet demo:【工作表演示：】(https://docs.google.com/spreadsheets/d/1eQLiCTXbMfjWO-6_lRGNnQHTg2mtj_dDwpjaAGgrT7w/edit?usp=sharing)
英文:
I have an spreadsheets with 50+ tabs. I would like to pull data match by keyword as sheet name & row number from all tabs.
It's possible by query with specific tab name, but when there is lot of tabs, how to recall from all tabs instated to re-write all tab names in query?
答案1
得分: 1
更新后的解决方案:
=let(Δ,arrayformula(split(tocol(reduce(wraprows(,columns(A:Z),),to_text(tocol(B3:B,1)),lambda(a,c,{a;let(Σ,indirect("'"&c&"'!A:Z"),if(len(Σ),Σ&"|"&substitute(c,"-","🐠")&"|"&row(Σ),))})),1),"|")),
Ξ,let(Γ,wraprows(,3,),reduce(Γ,sequence(index(match(2,1/(A3:A<>"")))),lambda(a,c,{a;ifna(filter(Δ,index(Δ,,1)=index(A3:A,c)),Γ)}))),
unique(filter(substitute(Ξ,"🐠","-"),index(Ξ,,1)<>"")))
英文:
Updated Solution:
=let(Δ,arrayformula(split(tocol(reduce(wraprows(,columns(A:Z),),to_text(tocol(B3:B,1)),lambda(a,c,{a;let(Σ,indirect("'"&c&"'!A:Z"),if(len(Σ),Σ&"|"&substitute(c,"-","🐠")&"|"&row(Σ),))})),1),"|")),
Ξ,let(Γ,wraprows(,3,),reduce(Γ,sequence(index(match(2,1/(A3:A<>"")))),lambda(a,c,{a;ifna(filter(Δ,index(Δ,,1)=index(A3:A,c)),Γ)}))),
unique(filter(substitute(Ξ,"🐠","-"),index(Ξ,,1)<>"")))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论