问题概述 #

现有两个表格需要配合完成延迟原因批量填充：

• 报表表格：包含File（文件编号）和Delay（待填充延迟原因）列
• 数据源表格（跨电子表格）：包含Entry Date、File、Status列

需求规则：根据每个File在数据源中出现频次最高的Status类别，填充Delay列（例如某文件Agent类状态出现3次、Supplier类1次，则填充"Agent"）。

原尝试的公式仅返回单个值，公式如下：

=ARRAYFORMULA(IF((COUNTIF(FILTER(ProdLog!H2:H, ProdLog!C2:C=$B$3:$B), "Awaiting Supplier Response") + COUNTIF(FILTER(ProdLog!H2:H, ProdLog!C2:C=$B$3:$B), "Confirmations") < COUNTIF(FILTER(ProdLog!H2:H, ProdLog!C2:C=$B$3:$B), "Awaiting Agent Response"))=TRUE, "DS", "Supplier"))

原公式问题分析 #

• FILTER与ARRAYFORMULA配合时，无法对每个File单独执行筛选统计，只会返回第一个匹配项的结果
• 直接嵌套COUNTIF和FILTER的写法，不支持逐行的数组运算逻辑

解决方案 #

以下提供两种可行的批量填充方案，适配不同的状态分类场景：

方案1：针对固定两类状态的统计匹配 #

如果状态可明确归为Agent和Supplier两类，先按File分组统计两类状态的数量，再通过数量比较确定延迟原因：

=ARRAYFORMULA(IF(B3:B="",,VLOOKUP(B3:B,QUERY(IMPORTRANGE("数据源表格URL","ProdLog!C2:H"),"select Col1, count(Col6) where Col6 matches 'Awaiting Agent Response' group by Col1 label count(Col6)'AgentCount'"),2,0)>VLOOKUP(B3:B,QUERY(IMPORTRANGE("数据源表格URL","ProdLog!C2:H"),"select Col1, count(Col6) where Col6 matches 'Awaiting Supplier Response|Confirmations' group by Col1 label count(Col6)'SupplierCount'"),2,0),"DS","Supplier"))

操作说明：

1. 替换数据源表格URL为实际的跨表格链接，首次使用需授权访问
2. 两个QUERY分别统计每个File的Agent类、Supplier类状态出现次数
3. VLOOKUP将报表中的File与统计结果匹配，通过数量对比输出对应延迟原因
4. IF(B3:B="",,)避免空行自动填充无效内容

方案2：通用任意状态的频次最高值提取 #

如果状态类别不固定，需要自动抓取每个File下出现频次最高的状态作为延迟原因：

=ARRAYFORMULA(IF(B3:B="",,BYROW(B3:B,LAMBDA(file,MODE(FILTER(IMPORTRANGE("数据源表格URL","ProdLog!H2:H"),IMPORTRANGE("数据源表格URL","ProdLog!C2:C")=file))))))

操作说明：

1. BYROW遍历报表中每一行的File值
2. FILTER筛选出当前File对应的所有状态记录
3. MODE返回该组状态中出现频次最高的值
4. 同样通过空值判断避免无效填充

注意事项 #

• 使用IMPORTRANGE时，需要先完成跨表格访问授权
• 若存在多个状态频次相同的情况，MODE会返回最早出现的那个状态，可根据实际需求调整逻辑

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