通过Google表格中的查询对多个文本值进行分组

多伦·阿西斯（Doron Assis）

因此，我有一个通过Google表格中的查询功能创建的数据透视表，我希望根据决策规则将其按行进行分组。

数据透视表基本上看起来像这样（一个类和年级的表，以及一个带有学生姓名的标题）：

            | John Dough | John Though | John Doe |... |  John A Hill
   History  |     79     |             |          |... |              |
   Chem 101 |            |             |    87    |... |              |
   Phys 101 |            |             |          |... |     77       |
   Phys 202 |            |             |          |... |              |
   Geo  101 |            |      75     |          |... |              |
       ...  |            |             |    ...   |... |              |
   Sport AT |            |             |     85   |... |              |

现在，假设期末考试所需的分数是75，我想做的就是获取此表：

            |       Failed                       Passed
   History  |     John Dough            | John A Hill , John Deere                          
   Chem 101 |     John E , John Tra     | John Son , John Snow                         
   Phys 101 |     John B Good , John Na | #N/A                          
   Phys 202 |     John Bon Jovi         | John Diy ,  John L , John R                   
   Geo  101 |     #N/A                  | John Lennon                
       ...  |         ...               |      ...   
   Sport AT |     John Bone             | John the revelator

不足之处是我想用公式包装现有的数据透视表，所以看起来像这样：

=MagicFormula[Query("Data !A1:X99","select yada yada, sum(yada), Pivot(whatever)]

我的问题是，可以通过包装来完成吗？

玩家0

=ARRAYFORMULA({"", "PASSED", "FAILED"; A2:A, REGEXREPLACE(TRIM({
 TRANSPOSE(QUERY(TRANSPOSE(IF((B2:E>=79)*(B2:E<>""), B1:E1&",", )),,999^99)),
 TRANSPOSE(QUERY(TRANSPOSE(IF((B2:E< 79)*(B2:E<>""), B1:E1&",", )),,999^99))}), 
 ",$", )})

更新：

=ARRAYFORMULA({{QUERY(QUERY({List!B5:D},
 "select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0), 
 "select Col1", 0)}, {"PASSED", "FAILED"; 
 REGEXREPLACE(TRIM({TRANSPOSE(QUERY(TRANSPOSE(IF((QUERY(QUERY({List!B5:D}, 
 "select sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0), 
 "offset 1", 0)>=79)*(QUERY(QUERY({List!B5:D}, 
 "select sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0), 
 "offset 1", 0)<>""), QUERY(QUERY({List!B5:D},
 "select sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0),
 "limit 0", 1)&",", )),,999^99)), TRANSPOSE(QUERY(TRANSPOSE(IF((QUERY(QUERY({List!B5:D}, 
 "select sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0), 
 "offset 1", 0)< 79)*(QUERY(QUERY({List!B5:D}, 
 "select sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0),
 "offset 1", 0)<>""), QUERY(QUERY({List!B5:D},
 "select sum(Col3) where Col1 is not null group by Col1 pivot Col2", 0), 
 "limit 0", 1)&",", )),,999^99))}), ",$", )}})