优化计算区域内双值重复次数的Google Sheets函数性能

问题背景 #

需要统计指定区域（如$E$2:$K$12）内双值组合（如A2&B2）的重复次数，现有公式为：
=IF(AND(ISBLANK(A2)=FALSE;ISBLANK(B2)=FALSE); (LEN(CONCATENATE($E$2:$K$12))-LEN(SUBSTITUTE(CONCATENATE(TRANSPOSE($E$2:$K$12));A2&B2;"")))/LEN(A2&B2);)
当前仅部署280个该函数就导致表格操作延迟卡顿，需部署720个，因此急需更高效的替代方案。

现有公式的性能瓶颈 #

• 每次调用都会重复拼接整个目标区域的内容，大量重复计算导致CPU和内存占用过高
• CONCATENATE+TRANSPOSE的组合在二维区域下会生成超长字符串，处理效率极低，且函数数量增加时，重复运算量呈指数级增长

优化方案一：数组公式直接计算（无需辅助列） #

适用于统计纵向相邻双值（如E2&E3、E3&E4...）的重复次数，公式如下：
=IF(AND(A2<>"",B2<>""), SUMPRODUCT(--($E$2:$K$11=A2)*--($E$3:$K$12=B2)), "")
若需统计横向相邻双值（如E2&F2、F2&G2...），调整区域为：
=IF(AND(A2<>"",B2<>""), SUMPRODUCT(--($E$2:$J$12=A2)*--($F$2:$K$12=B2)), "")

优势 #

• 直接进行单元格级别的数值比较，无需拼接大字符串，内存占用大幅降低
• SUMPRODUCT执行一次数组运算即可完成统计，避免了重复调用区域拼接逻辑

优化方案二：辅助列+COUNTIF（低复杂度批量查询） #

步骤1：生成所有双值组合 #

在空白列（如M列）输入以下公式，一次性提取目标区域内的所有相邻双值：
=FLATTEN(ARRAYFORMULA($E$2:$K$11&$E$3:$K$12))
（横向相邻则改为=FLATTEN(ARRAYFORMULA($E$2:$J$12&$F$2:$K$12))）

步骤2：批量查询次数 #

在需要显示结果的单元格（如C2）输入公式，下拉填充至720行：
=IF(AND(A2<>"",B2<>""), COUNTIF($M:$M, A2&B2), "")

优势 #

• 仅需一次生成所有组合，后续720个查询仅需检索辅助列，运算量骤降
• 逻辑简单易懂，维护成本低

优化方案三：Google Apps Script（彻底解决卡顿） #

通过脚本预计算所有双值组合的次数，一次性输出结果，完全避免函数重复运算：

function calculateDoubleValueCounts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const targetRange = sheet.getRange("E2:K12");
  const targetValues = targetRange.getValues();
  const countMap = {};

  // 统计纵向相邻双值（如需横向，替换为列遍历逻辑）
  for (let col = 0; col < targetValues[0].length; col++) {
    for (let row = 0; row < targetValues.length - 1; row++) {
      const pair = `${targetValues[row][col]}${targetValues[row+1][col]}`;
      countMap[pair] = (countMap[pair] || 0) + 1;
    }
  }

  // 读取需要查询的A、B列数据（假设范围是A2到B721）
  const queryRange = sheet.getRange("A2:B721");
  const queryData = queryRange.getValues();
  const results = queryData.map(row => {
    const pair = `${row[0]}${row[1]}`;
    return row[0] && row[1] ? [countMap[pair] || 0] : [""];
  });

  // 将结果写入C列（对应C2到C721）
  sheet.getRange("C2:C721").setValues(results);
}

使用方法 #

1. 打开表格的脚本编辑器（工具→脚本编辑器）
2. 粘贴上述代码，保存并运行（首次运行需授权）
3. 可设置定时触发（编辑→当前项目的触发器），自动更新统计结果

优势 #

• 仅需一次运算即可完成所有统计，彻底消除720个函数带来的性能损耗
• 运算逻辑完全在后台执行，不影响表格前端操作

内容的提问来源于stack exchange，提问作者Dark