Google表格查询语言，返回0 [英] Google Sheets Query Language, return 0

问题描述

给出以下数据...

>

每张表格都是不同的玩家，我需要统计每个玩家的游戏数量和这些游戏的结果。

有些玩家可能与彼此处于相同的游戏中，因此游戏ID将作为唯一标识符提供，因此这些游戏只计算一次，这是我工作的部分。 / p>

现在我想要做的是记录A队在每个房间中获胜的次数。

您可以在上面看到我的问题截图。

制作它：

1. 将数据粘贴到单独的工作表中，如上图所示。
   
2. 选择范围 A1：B6


3. 转至数据→命名范围→将其称为数据
   
4. 输入公式，它现在应该可以正常工作。

示例文件带有工作公式

I've been having troubling getting the query function to return 0 when the queried data isn't found.

Given the following data...

With each sheet being a different player, I need to count the number of games each played and the outcome of those games.

Some players may be in the same game as each other so the gameIDs are given as a unique identifier so those games are only counted once, that part I have working.

What I'm trying to do now is record the number of wins team A has in each room.

You can see my problem in the above screenshot.

Here is the sample sheet that has the above data.

This is the formula used for the right most example

=QUERY({QUERY(UNIQUE(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 <> '' and Col3 contains 'A'")),"select Col1, 1");QUERY(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 = '' and Col1 <> '' and Col3 contains 'A'"),"select Col1, 1")},"select Col1, sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) 'TeamA Wins', Col1 'Room'")

Anyone able to help?

解决方案

You may add dummy data into the formula:

=QUERY(data;{QUERY(UNIQUE(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 <> '' and Col3 contains 'A'")),"select Col1, 1");QUERY(QUERY({Sheet1!A2:C;Sheet2!A2:C;Sheet3!A2:C},"select Col1, Col2 where Col2 = '' and Col1 <> '' and Col3 contains 'A'"),"select Col1, 1")},"select Col1, sum(Col2) where Col1 <> '' group by Col1 label sum(Col2) 'TeamA Wins', Col1 'Room'")
       ^^^^

data is a named range, it looks like this:

To make it:

1. paste the data to separate sheet, as shown in picture above.
2. select range A1:B6
3. go to Data → Named range → call it "data"
4. enter the formula and it should work fine now.

Sample file with working formula

这篇关于Google表格查询语言，返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！