时间戳

首页 > 解决方案 > 如何在 Google Sheets 中合并两个数组查询的代码

regex - 如何在 Google Sheets 中合并两个数组查询的代码

问题描述

我需要合并两个都在 arrayformulas 中的查询，这样我就只有一个查询：

我试过使用联合

第一个代码：

 = ARRAYFORMULA(QUERY({MID(Sheet1!B1:B, 8, 5), Sheet1!A1:AS}, 
 "select count(Col13) 
 where Col13>=0 
 group by Col1 
 label count(Col13)'Winners #'"))

第二个代码：

= ARRAYFORMULA(QUERY({MID(Sheet1!B1:B, 8, 5), Sheet1!A1:AS}, 
"select count(Col13) 
where Col13<=0 
group by Col1 
label count(Col13)'Losers #'"))

标签： regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

解决方案

=ARRAYFORMULA(QUERY(REGEXREPLACE(TO_TEXT(QUERY({
 QUERY({MONTH(MID('grouping project'!A2:A, 8, 3)&1)&"♦"&
              MID('grouping project'!A2:A, 8, 5), 'grouping project'!A2:AO}, 
 "select Col1,count(Col3),'Winners #'
  where Col1 is not null 
    and Col3 >= 0 
  group by Col1 
  label count(Col3)'','Winners #'''", 0);
 QUERY({MONTH(MID('grouping project'!A2:A, 8, 3)&1)&"♦"&
              MID('grouping project'!A2:A, 8, 5), 'grouping project'!A2:AO}, 
 "select Col1,count(Col3),'Loosers #'
  where Col3 <= 0 
    and Col1 is not null
  group by Col1 
  label count(Col3)'','Loosers #'''", 0)}, 
 "select Col1,sum(Col2)
  group by Col1
  pivot Col3
  label Col1'Week ending'", 0)), "^.+♦", ), 
 "where Col1 is not null", 0))

0

推荐阅读