根据其他2个表创建百分比表-Google查询 [英] Create Percentage Table from 2 other tables - Google Query

问题描述

有没有一种方法可以计算出第3个交叉表，其中包含从Google查询创建的2个交叉表中的百分比?最初，我使用花括号将2个查询堆叠在一起，但是发现如果行或列不匹配，则两个表查询都将导致#VALUE错误.因此，将2个表分隔为100个分组行.我想在这些表下面计算FTR百分比表.

这是一个

---

以及一个公式中的所有3个表都是完全动态的:

  = ARRAYFORMULA({TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFNA(HLOOKUP(INDEX(TRANSPOSE(QUERY(InputData！A3:F，"选择D，count(B)其中D不为空并且year(D)=& C1&".和E！='lost'按D分组枢轴C标签D'NO OF ISSUES'''))，1)，TRANSPOSE(QUERY({InputData！A3:F}，选择Col4，count(Col2)其中Col4不为null和year(Col4)=& C1&"和Col5！='丢失'和Col6 ='是'按Col4分组枢轴Col3标签Col4'NO OF ISSUES'''))，ROW(A1:A10000)，0))，"其中Col1<>'#REF！'''))，"format Col1'm/d/yyyy'"))；SPLIT(REPT(♠"，COLUMNS(TRANSPOSE(QUERY(InputData！A3:F，"选择D，count(B)其中D不为空并且year(D)=& C1&".和E！='lost'按D分组枢轴C标签D'TOTAL OPPORTUNITIES')))))，♠")；TRANSPOSE(QUERY(InputData！A3:F，"选择D，count(B)其中D不为空并且year(D)=& C1&".和E！='lost'按D分组枢轴C标签D'TOTAL OPPORTUNITIES'))；SPLIT(REPT(♠"，COLUMNS(TRANSPOSE(QUERY(InputData！A3:F，"选择D，count(B)其中D不为空并且year(D)=& C1&".和E！='lost'按D分组枢轴C标签D'TOTAL OPPORTUNITIES')))))，♠")；REGEXREPLACE(TO_TEXT(QUERY(QUERY({QUERY({InputData！D3:D&♦"& InputData！C3:C，InputData！B3:F，InputData！D3:D&IF(InputData！D3:D =，"，♥"和TEXT(InputData！D3:D，"m/d/yyyy"))}}，选择Col1，Col4，Col3，Col7，count(Col2)其中Col4不为null和year(Col4)=& C1&"和Col5！='丢失'按Col1，Col4，Col3，Col7"分组)，IFNA(VLOOKUP(INDEX(QUERY({InputData！D3:D&♦"& InputData！C3:C，InputData！B3:F}，选择Col1，Col4，Col3，count(Col2)其中Col4不为null和year(Col4)=& C1&"和Col5！='丢失'通过Col1，Col4，Col3),, 1)进行分组，QUERY({InputData！D3:D&``& InputData！C3:C，InputData！B3:F}，选择Col1，count(Col2)其中Col4不为null和year(Col4)=& C1&"和Col5！='丢失'和Col6 ='是'按Col1)，2，0))}分组，选择Col4，Col3，Col6/Col5")，选择Col2，max(Col3)按Col2分组枢轴Col1标签Col2'FTR％'格式为max(Col3)'#.00％'''))，(\ d +♥)"，)}) 

电子表格演示

Is there a way to calculate a 3rd crosstab Table containing percentages from 2 crosstab tables created from Google Queries? Initially i was stacking the 2 queries together using braces, but found that if there were row or column mismatches, then both the table queries would result in #VALUE error. So separated the 2 tables with 100 grouped rows between them. I want to calculate FTR percentage table below these tables.

Here is an FTRTable with Edit access.

You can refer to my earlier related post here.

解决方案

formula for FTR table:

=ARRAYFORMULA(REGEXREPLACE(TO_TEXT(QUERY(QUERY({QUERY(
 {InputData!D3:D&"♦"&InputData!C3:C, InputData!B3:F, InputData!D3:D&
 IF(InputData!D3:D="",,"♥"&TEXT(InputData!D3:D, "dd/mm/yyyy"))}, 
 "select Col1,Col4,Col3,Col7,count(Col2) 
  where Col4 is not null 
    and year(Col4)="&C1&" 
    and Col5 != 'lost'     
  group by Col1,Col4,Col3,Col7"),
IFNA(VLOOKUP(INDEX(QUERY({InputData!D3:D&"♦"&InputData!C3:C, InputData!B3:F}, 
 "select Col1,Col4,Col3,count(Col2) 
  where Col4 is not null 
    and year(Col4)="&C1&" 
    and Col5 != 'lost'     
  group by Col1,Col4,Col3"),,1), 
QUERY({InputData!D3:D&"♦"&InputData!C3:C, InputData!B3:F}, 
 "select Col1,count(Col2) 
  where Col4 is not null 
    and year(Col4)="&C1&" 
    and Col5 != 'lost' 
    and Col6  = 'yes' 
  group by Col1"), 2, 0))}, 
 "select Col4,Col3,Col6/Col5"), 
 "select Col2,max(Col3) 
  group by Col2
  pivot Col1 
  label Col2'FTR%' 
  format max(Col3)'#.00%'")), "(\d+♥)", ))

---

and all 3 tables in one formula fully dynamic:

=ARRAYFORMULA({TRANSPOSE(QUERY(TRANSPOSE(QUERY(IFNA(
 HLOOKUP(INDEX(TRANSPOSE(QUERY(InputData!A3:F, 
 "select D,count(B) 
  where D is not null 
    and year(D)="&C1&" 
    and E!='lost' 
  group by D 
  pivot C 
  label D 'NO OF ISSUES'")), 1), TRANSPOSE(QUERY({InputData!A3:F}, 
 "select Col4,count(Col2) 
  where Col4 is not null
    and year(Col4)="&C1&" 
    and Col5!='lost' 
    and Col6 = 'yes' 
  group by Col4 
  pivot Col3 
  label Col4 'NO OF ISSUES'")), ROW(A1:A10000), 0)), 
 "where Col1 <> '#REF!'")), 
 "format Col1'm/d/yyyy'")); 
 SPLIT(REPT(" ♠", COLUMNS(TRANSPOSE(QUERY(InputData!A3:F, 
 "select D,count(B) 
  where D is not null
    and year(D)="&C1&" 
    and E!='lost' 
  group by D 
  pivot C 
  label D 'TOTAL OPPORTUNITIES'")))), "♠"); 
 TRANSPOSE(QUERY(InputData!A3:F, 
 "select D,count(B) 
  where D is not null
    and year(D)="&C1&" 
    and E!='lost' 
  group by D 
  pivot C 
  label D 'TOTAL OPPORTUNITIES'")); 
 SPLIT(REPT(" ♠", COLUMNS(TRANSPOSE(QUERY(InputData!A3:F, 
 "select D,count(B) 
  where D is not null
    and year(D)="&C1&" 
    and E!='lost' 
  group by D 
  pivot C 
  label D 'TOTAL OPPORTUNITIES'")))), "♠");
 REGEXREPLACE(TO_TEXT(QUERY(QUERY({QUERY(
 {InputData!D3:D&"♦"&InputData!C3:C, InputData!B3:F, InputData!D3:D&
 IF(InputData!D3:D="",,"♥"&TEXT(InputData!D3:D, "m/d/yyyy"))}, 
 "select Col1,Col4,Col3,Col7,count(Col2) 
  where Col4 is not null 
    and year(Col4)="&C1&" 
    and Col5 != 'lost'     
  group by Col1,Col4,Col3,Col7"),
IFNA(VLOOKUP(INDEX(QUERY({InputData!D3:D&"♦"&InputData!C3:C, InputData!B3:F}, 
 "select Col1,Col4,Col3,count(Col2) 
  where Col4 is not null 
    and year(Col4)="&C1&" 
    and Col5 != 'lost'     
  group by Col1,Col4,Col3"),,1), 
QUERY({InputData!D3:D&"♦"&InputData!C3:C, InputData!B3:F}, 
 "select Col1,count(Col2) 
  where Col4 is not null 
    and year(Col4)="&C1&" 
    and Col5 != 'lost' 
    and Col6  = 'yes' 
  group by Col1"), 2, 0))}, 
 "select Col4,Col3,Col6/Col5"), 
 "select Col2,max(Col3) 
  group by Col2
  pivot Col1 
  label Col2'FTR%' 
  format max(Col3)'#.00%'")), "(\d+♥)", )})

spreadsheet demo

这篇关于根据其他2个表创建百分比表-Google查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！