英文:
Add source column in QUERY with more than one data sources
问题
=QUERY({'yr Import'!A2:BH; 'ar Import'!A2:BH, ARRAYFORMULA("yr Import"); ARRAYFORMULA("ar Import")},"Select * where Col1 is not null")
英文:
I use the following formula to combine the data of two google sheet tabs in one sheet:
=QUERY({'yr Import'!A2:BH;'ar Import'!A2:BH},"Select * where Col1 is not null ")
I want to change the formula, that I get an additional column (after the imported data) with the source name of the data.
| Data of A2:BH | Addtional Source column |
|---|---|
| ... | yr Import |
| ... | ar Import |
| ... | yr Import |
| ... | yr Import |
| ... | yr Import |
| ... | ar Import |
| ... | ar Import |
| ... | ar Import |
| ... | ar Import |
I tried for exampe to add an arrayformula:
=QUERY({'yr Import'!A2:BM, ARRAYFORMULA("yr Import") ;'ar Import'!A2:BM, ARRAYFORMULA("ar Import")},"Select * where Col1 is not null")
--> This leads to an "#REF!" error!
答案1
得分: 2
<!-- language-all: js -->
*这是一种您可以尝试的方法：*
=let(yr, "年 Import", ar, "ar Import",
query({{'年 Import'!A2:BH,wrapcols(yr,rows('年 Import'!A2:A),yr)};
{'ar Import'!A2:BH,wrapcols(ar,rows('ar Import'!A2:A),ar)},"where Col1 is not null"))
英文:
<!-- language-all: js -->
Here's one approach you may test out:
=let(yr,"yr Import", ar,"ar Import",
query({{'yr Import'!A2:BH,wrapcols(yr,rows('yr Import'!A2:A),yr)};
{'ar Import'!A2:BH,wrapcols(ar,rows('ar Import'!A2:A),ar)}},"where Col1 is not null"))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论