googlesheets查询以取消表

发布于 2025-01-19 10:45:50 字数 848 浏览 2 评论 0原文

示例 - https://docs.google.com/spreadsheets/d/1dNV8BP6AJfc8-u-8BFkkf1i3_QBLe9U1-YfVfUvRdok/edit#gid=1046241405

表 - 1 - 数据每天更新
表 - 2 - 数据备份从 1 张
表 - Diff - 是我如何得到差异 atm
表 - WIP - 是我目前正在尝试做什么，

我试图

=query('1'!A:N; "SELECT C WHERE (A CONTAINS '"&A2&"') ";0)-query('2'!A:N; "SELECT C WHERE (A CONTAINS '"&A2&"') ";0)

在每个单元格上从 2 中减去值表 1，它可以工作，但在 WIP A1 上效率不高

，我试图用 1 个查询来完成它，

=query({'1'!A:N;'2'!A:N}; "SELECT Col1, sum(Col3) WHERE Col1 is not null GROUP BY Col1";1)

显然它不是总和，但我设置为示例，因为我想要相反，没有用于减法的聚合器，所以有没有办法在这个例子中从每个工作表中调用 Col3 并制作像 '1'Col3-'2'Col3 ？或任何其他方式来完成它？

原文

Example - https://docs.google.com/spreadsheets/d/1dNV8BP6AJfc8-u-8BFkkf1i3_QBLe9U1-YfVfUvRdok/edit#gid=1046241405

Sheet - 1 - data is updated daily
Sheet - 2 - data is backed up from 1
Sheet - Diff - is how im getting the difference atm
Sheet - WIP - is what im trying to do

currently im trying to substract values sheet 1 from 2 with

=query('1'!A:N; "SELECT C WHERE (A CONTAINS '"&A2&"') ";0)-query('2'!A:N; "SELECT C WHERE (A CONTAINS '"&A2&"') ";0)

on each cell, it works but its not efficient

on WIP A1, im trying to do it with 1 query

=query({'1'!A:N;'2'!A:N}; "SELECT Col1, sum(Col3) WHERE Col1 is not null GROUP BY Col1";1)

obviously its not sum, but i set as exemple cos i want the oposite, there is no agregator for substraction, so is there a way to call in this example Col3 from each Sheet and make like '1'Col3-'2'Col3 ? or any other way to acomplish it?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

忆依然 2025-01-26 10:45:50

通常：

3 + 2 = 5

3 + (2 * (-1)) = 1

删除范围B2：g中的所有内容，并在B2中使用此内容：

=INDEX(IFNA(VLOOKUP(A2:A; QUERY({'1'!A:H; '2'!A:B\ '2'!C:H*-1}; 
 "select Col1,sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where Col1 is not null 
  group by Col1"); {2\3\4\5\6\7}; )))

要删除可以使用的零：

=INDEX(IFERROR(1/(1/VLOOKUP(A2:A; QUERY({'1'!A:H; '2'!A:B\ '2'!C:H*-1}; 
 "select Col1,sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where Col1 is not null 
  group by Col1"); {2\3\4\5\6\7}; ))))

in general:

3 + 2 = 5

3 + (2 * (-1)) = 1

delete everything in range B2:G and use this in B2:

=INDEX(IFNA(VLOOKUP(A2:A; QUERY({'1'!A:H; '2'!A:B\ '2'!C:H*-1}; 
 "select Col1,sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where Col1 is not null 
  group by Col1"); {2\3\4\5\6\7}; )))

to remove zeros you can use:

=INDEX(IFERROR(1/(1/VLOOKUP(A2:A; QUERY({'1'!A:H; '2'!A:B\ '2'!C:H*-1}; 
 "select Col1,sum(Col3),sum(Col4),sum(Col5),sum(Col6),sum(Col7),sum(Col8) 
  where Col1 is not null 
  group by Col1"); {2\3\4\5\6\7}; ))))