Google表格枢纽分析表:显示没有数据的项目 [英] Google sheets pivot table: show items with no data

问题描述

我正在尝试找到正确的Google查询语法，以显示源数据中存在的每个行元素的枢轴列，即使在应用了"where"子句之后也没有数据.这样，生成的数据透视表将始终具有相同的列数，从而可以对基于列的数据进行进一步的计算.

I am trying to find the correct google query syntax to show a pivot column for every row element that exists in the source data, even if there is no data for it after the 'where' clause has been applied. This way, the resultant pivot table will always be the same number of columns wide, making it possible to perform further computations on the data based on column.

这等效于在Microsoft Excel中创建数据透视表并选择显示没有数据的项目"选项.

This is the equivalent of creating a pivot table in Microsoft Excel and selecting the 'show items with no data' option.

这是当前代码:

=query(Memberships!A:P,"SELECT E,COUNT(H) where J >=1 group by E PIVOT B")

代码应该是什么才能产生我想要的结果?

What should the code be to produce the result that I want?

我可以使用查询语句，也可以使用Google Sheets数据透视功能(如果可以这样做的话).

I could use either a query statement, or the Google Sheets pivot functionality if it has a way to do this.

推荐答案

如果要包括WHERE子句排除的行，则必须使用WHERE以外的其他内容.一种典型的解决方法是添加一个具有 arrayformula(N(J:J> = 1))的列，该列在满足条件的行中将包含1，对于其他行将包含0.选择该列的 sum()代替 count().结果是零计数返回为0；否则，返回0.因此相应的行会保留并用于数据透视.

If you want to include the rows that are excluded by WHERE clause, then it's necessary to use something other than WHERE. A typical workaround is to add a column with arrayformula(N(J:J>=1)) which will have 1 in the rows that satisfy the condition, and 0 for others. The select sum() of that column in place of count(). The effect is that zero counts are returned as 0; so the corresponding rows are preserved and are used in pivot.

例如，可以随时添加额外的列

The extra column can be added on the fly, for example

=query({A2:J, N(J2:J>=1)}, "select Col5, sum(Col11) group by Col5 pivot Col2")

现在通过列在数组中的位置引用它们的列 {A2:J，N(J2:J> = 1)} :Col5为E，Col2为B，Col11为最后的多余一栏.

where the columns are now referred to by their positions in the array {A2:J, N(J2:J>=1)}: Col5 is E, Col2 is B, and Col11 is the extra column at the end.

一个人可能会发现不希望有零.可以使用If条件将其过滤掉，尽管这是公式长度的两倍以上:

One may find the presence of zeros undesirable. They can be filtered out with the If condition, though this more than doubles the length of the formula:

=arrayformula(if(query({A2:J,N(J2:J>=1)}, "select Col5, sum(Col11) group by Col5 pivot Col2") = 0, , query({A2:J,N(J2:J>=1)}, "select Col5, sum(Col11) group by Col5 pivot Col2"))) 

故意将 if 的第二个参数留空.

The second argument of if is intentionally left blank.

这篇关于Google表格枢纽分析表:显示没有数据的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！