function - Google表格简化中的查询功能

Question

我正在寻找一种简化此公式的方法，因为它必须在 17424 个唯一单元格中运行：

=IFERROR(SUMPRODUCT(QUERY('MASTER Accelo Data 1'!A:G;"Select G where (B>=date '2020-01-01' and B<=date '2020-01-31' and A matches '"&J2&"' and D matches '"&JOIN("|";$T$2:$T)&"')";0));0)+IFERROR(SUMPRODUCT(QUERY('MASTER Accelo Data 2'!A:G;"Select G where (B>=date '2020-01-01' and B<=date '2020-01-31' and A matches '"&J2&"' and D matches '"&JOIN("|";$T$2:$T)&"')";0));0)

在哪里...

MASTER Accelo Data 1!G:G + MASTER Accelo Data 2!G:G = 要求和的单元格

MASTER Accelo Data 1!B:B + MASTER Accelo Data 2!B:B= 要检查的日期

J2 = 客户 ID

MASTER Accelo Data 1!A:A + MASTER Accelo Data 2!A:A = 要检查的客户 ID

T2:T = 所有者数组

MASTER Accelo Data 1!D:D + MASTER Accelo Data 2!D:D = 要检查的所有者

链接到虚拟工作簿：https ://docs.google.com/spreadsheets/d/1hevSA0vl3YO81vaxfJ46TAyO-VkUhXKk8WRKY8268qY/edit?usp=sharing

希望各位大神帮忙！

标签： functiongoogle-sheetsgoogle-sheets-formula

Answer 1

使用数组公式求解：

=ARRAYFORMULA(
  QUERY(
   {{'MASTER Accelo Data 1'!A2:C;
     'MASTER Accelo Data 2'!A2:C}\
    HVIS.FEJL(
     LOPSLAG(
      {'MASTER Accelo Data 1'!D2:D;
       'MASTER Accelo Data 2'!D2:D};
      {"OQ"\"Apprentice";"OP"\"Apprentice";
       "DH"\"Associate";"KI"\"Associate";
       "UY"\"Intern";"YU"\"Intern";"ER"\"Intern";
       "WE"\"Intern";"KY"\"Intern";"VB"\"Intern";
       "KP"\"Management";"MD"\"Management";"KL"\"Management";
       "AS"\"Management";"SD"\"Management";"IGR"\"Pss";
       "GTI"\"Pss";"SS"\"Pss";"SQ"\"Pss";"VD"\"Pss";
       "AE"\"Senior";"A"\"Senior";"VE"\"Senior";
       "KJ"\"Senior";"KO"\"Senior";"LQ"\"Senior";
       "IU"\"Senior"};
      2;FALSK))\
    {'MASTER Accelo Data 1'!G2:G;
     'MASTER Accelo Data 2'!G2:G}};
   "select Col1, Sum(Col5) 
    where 
     Col3 is not null and
     Col2 >= date '2020-01-01' and
     Col2 <= date '2020-01-31'
    group by Col1
    pivot Col4
    label Col1 'ID'"))