英文:
Sum Query Import Range function in Sheets
问题
需要您的帮助。我有1份工作文档和1份数据库文档。
我在工作文档中放入了这个公式：
=QUERY(IMPORTRANGE("URL数据库文档","范围"), "select sum(Col19) where Col1 = 'ABC' and Col2='01/01/23' label sum(Col19)''", 0)
它运行得很好。
然而，我需要用以下信息替换'ABC'和'01/01/2023'：
'ABC' 替换为单元格 B100，'01/01/23' 替换为工作文档中的 J2
例如：
=QUERY(IMPORTRANGE("URL数据库文档","范围"), "select sum(Col19) where Col1 = '"&B100&"' and Col2='"&J2&"' label sum(Col19)''", 0)
但它不起作用。
我需要在向下填充公式时进行更改。
B100 ==> B101 ==> B102 等...
J2 ==> J4 ==> J5 ...
是否有解决方案？
非常感谢您的帮助。
Eve
英文:
Need your help. I have 1 work document and 1 database document.
I put this formula in the work document
=QUERY(IMPORTRANGE("URL database document","Range"),"select sum(Col19) where Col1 = 'ABC' and Col2='01/01/23' label sum(Col19)''",0)
It works perfectly.
However, I need to remplace 'ABC' and '01/01/2023' by the following information:
'ABC' by the cell B100 and '01/01/23' by J2 from the working document
For example:
=QUERY(IMPORTRANGE("URL database document","Range"),"select sum(Col19) where Col1 = '"B100"' and Col2='"J2"' label sum(Col19)''",0)
But it doesn't work.
I need it to change when I downfill the formula.
B100 ==> B101 ==> B102 etc...
J2 ==> J4 ==> J5 ...
Is there any solution?
Thank you in advance for your help.
Eve
答案1
得分: 0
如果Col2和J2是合适的日期格式，那么可以尝试以下操作：
=QUERY(IMPORTRANGE("URL数据库文档","范围"),"select sum(Col19) where Col1 = '"&B100&"' and Col2=date'"&text(G1,"yyyy-mm-dd")&"' label sum(Col19)''",0)
如果Col2和J2只是文本日期（对于ISDATE函数返回FALSE），则可以尝试以下操作：
=QUERY(IMPORTRANGE("URL数据库文档","范围"),"select sum(Col19) where Col1 = '"&B100&"' and Col2='"&J2&"' label sum(Col19)''",0)
英文:
You can try either of these and see how it goes:
If Col2 and J2 are proper date formats then try:
=QUERY(IMPORTRANGE("URL database document","Range"),"select sum(Col19) where Col1 = '"&B100&"' and Col2=date'"&text(G1,"yyyy-mm-dd")&"' label sum(Col19)''",0)
If Col2 and J2 are just text-dates (tests FALSE for ISDATE Fx)
=QUERY(IMPORTRANGE("URL database document","Range"),"select sum(Col19) where Col1 = '"&B100&"' and Col2='"&J2&"' label sum(Col19)''",0)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论