google-sheets - 使用表中的计算值填充二维网格的公式

Question

这里有一个需要使用 Google 表格公式解决的问题。我有一个大表(表“数据”，标题位于第一行)，其中包含这些列:

• A、产品引用
• B:客户
• C 到 F，KPI1 到 KPI4

在另一张纸上，按客户 (B1:1) 划分的产品引用 (A2:A) 网格。现在，我需要用 KPI 串联填充网格中的每个单元格 (data!C24&"|"&data!D24&"|"&data!E24&"|"&data!F24)您能制定一个单一公式来填充所有单元格吗？

以下是包含数据和网格工作表的示例电子表格: https://docs.google.com/spreadsheets/d/1iA_kw4kKw99Qk69X4tST9U-QN2SeG2EN3KEeyG6AtHs/edit?usp=sharing

我已经制定了一个可以完成这项工作的公式，尽管在大型数据集上的性能非常差:

=ARRAYFORMULA(
  IFNA(
    VLOOKUP(
      $B3:$B&"|"&C$2:$2,
      ARRAYFORMULA(
        {data!A2:A&"|"&data!B2:B,data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F}
      ),2,0
    ),""
  )
)

解决方案

在带有数据透视的查询中使用 ArrayFormula:

=ARRAYFORMULA(
  QUERY(
    {data!A2:A, data!B2:B, data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F},
    "select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2",0
  )
)

Answer 1

previous 的较短版本答案(不需要预先排序，因为 pivot 会自行排序):

=ARRAYFORMULA(QUERY(
 {data!A2:A, data!B2:B, data!C2:C&"|"&data!D2:D&"|"&data!E2:E&"|"&data!F2:F},
 "select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2", 0))