如何用Excel公式按特定检测代码（U/J/R等）统计交替列数据？

核心需求 #

针对每行化学样品数据，统计三类情况的数量及占比：

• 有数值且无检测代码（记为「已检测」）
• 有数值且带特定检测代码（如U、J+、R等）
• 适配大规模数据集的自动化计算

现有问题 #

已使用以下公式，但无法精准筛选特定检测代码：

• 统计无检测代码的数值数量：=SUMPRODUCT((ISNUMBER(C3:O3)*(ISBLANK(D3:P3))))
• 统计带文本检测代码的数值数量：=SUMPRODUCT((ISNUMBER(C5:O5)*(ISTEXT(D5:P5))))

解决方案 #

1. 特定检测代码的精准统计公式 #

针对单个检测代码（如U），用以下公式统计对应有数值的数量：

=SUMPRODUCT((ISNUMBER(C3:O3))*(D3:P3="U"))

如果要匹配多个代码（如U、J+），用多条件逻辑扩展：

=SUMPRODUCT((ISNUMBER(C3:O3))*((D3:P3="U")+(D3:P3="J+")>0))

注：公式中C3:O3是数值列范围，D3:P3是对应检测代码列范围，需根据实际表格调整。

2. 「已检测」数量统计（原公式优化） #

保留原逻辑，确保数值列有值且对应代码列空白：

=SUMPRODUCT((ISNUMBER(C3:O3))*(ISBLANK(D3:P3)))

3. 占比计算 #

以该行总有效数值数为分母，占比公式示例（以U类为例）：

=SUMPRODUCT((ISNUMBER(C3:O3))*(D3:P3="U"))/COUNT(C3:O3)

若需包含文本型数值，用COUNTA(C3:O3)替代COUNT(C3:O3)，统计所有非空单元格总数。

4. 大规模数据集自动化方案 #

• 定义名称：将数值列范围和代码列范围分别定义为名称（如SampleValues、TestCodes），公式中直接引用名称，避免重复调整范围。
• 批量填充：输入第一行公式后，双击单元格右下角填充柄，自动应用到所有行。
• 动态数组公式（Excel 365/2021）：用BYROW函数一次性生成所有行的统计结果，无需逐行填充：

  =BYROW(C3:O1000, LAMBDA(row, SUMPRODUCT((ISNUMBER(row))*(OFFSET(row,0,1)="U"))))
  

  OFFSET(row,0,1)表示对应行的下一列（即检测代码列），需根据列位置调整偏移量。

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