查询中的IF语句 [英] IF statement in query

问题描述

我有一个包含两列A和B的表，第一列是标签，第二列是金额.我试图编写一个包含两列的查询，一列汇总负值，另一列汇总正值.

I have a table with two columns A and B the first is a tag and the second is an amount. I am trying to write a query with two columns, one summing up negative values while the other summing up positive ones.

来自SQL，我尝试了以下

Coming from SQL, I tried the following

=QUERY(A1:B100, 
      "SELECT A, SUM( B * IF(B>0, 0, 1) ), 
                 SUM( B * IF(B<0, 0, 1) ) GROUP BY A ")

但是查询中似乎不支持IF函数.我知道我可以在工作表中创建两个中间列(一个为正值，一个为负值)，但我想知道是否可以通过查询或以某种方式实现我想要的内容而无需中间列.

But it seems that the IF function is not supported in a query. I know I can create two intermediate columns in my sheet (one for positive value and one for negative ones), but I was wondering if it's possible to achieve what I want with a query or somehow without intermediate columns.

推荐答案

如果必须使用查询功能，则假设您的标记数据在A列中，而值在B列中:

If you must use the query function, assuming your Tag Data is in Column A, and your Values in Column B:

=arrayformula(query({A1:A100,if(B1:B100>0,B1:B100,),if(B1:B100<0,B1:B100,)},"Select Col1, sum(Col2), sum(Col3) where Col1 <>'' group by Col1 label Col1 'Tag', sum(Col2) 'Positive', sum(Col3) 'Negative'"))

以下是示例输出: https://docs.google.com/spreadsheets/d/1DW5CyPCC71CopW48uKy6basn-WP4hMfh7kuuJXT-C4o/edit#gid=1606239479

这篇关于查询中的IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！