Excel自动算DPPM和良率，告别手动统计

每月5号，质量部月报例会。你面前摊着12条产线的测试数据，一条条抄进Excel，算完良率算DPPM，算完DPPM还要按产品型号拆分汇总。计算器按了上百次，中间错一个数字，整个表都得推倒重来。一上午就这么没了，最后交付的报告还有可能被质疑数据对不上。

干了10年半导体品质，我可以告诉你：这套活Excel一个模板就能自动算，DPPM和良率自动更新，错误率归零，时间从一上午压缩到10分钟。

解决方案概览

核心思路：原始数据一表到底，公式一次写对，透视表自动汇总。建立标准的检验数据录入表，用固定公式自动计算良率和DPPM，按月/周/产品类型自动汇总。

分步实操

第一步：建标准数据表

新建Excel，Sheet1命名为"数据录入"，表头按下面结构列好（列A-F）：

关键设置：

• F列（不良类别）：用数据验证做下拉框。选F列 → 数据 → 数据验证 → 序列，输入你常用的不良类别（虚焊、偏位、漏焊、少锡、其他），避免手工输入五花八门的描述
• B/C列：同样做下拉框，保证产线和型号写法统一

这一步做好了，后面所有公式都是死公式，不需要改。

第二步：良率公式

G列命名为"良率"，G2输入：

=IF(D2=0,0,(D2-E2)/D2)

选中G列，单元格格式设为"百分比"，小数位2位。

公式说明：(投入数-不良数)/投入数，就是一次直通良率。外面套IF(D2=0,0,...)是防除零——没投产的批次不算，直接显示0，避免#DIV/0!错误满屏飘红。

下拉填充到所有数据行。

第三步：DPPM公式

H列命名为"DPPM"，H2输入：

=IF(D2=0,0,E2/D2*1000000)

公式说明：DPPM（Defective Parts Per Million）就是每百万件中不良的件数。不良数/投入数×1,000,000，结果不需要百分比格式，保持普通数字。

第四步：自动汇总——按产品型号

新建Sheet，命名"汇总"。先做按产品型号的汇总，A2开始列产品型号，B2输入：

=SUMIFS(数据录入!E:E,数据录入!C:C,A2)/SUMIFS(数据录入!D:D,数据录入!C:C,A2)*1000000

这是加权DPPM——用各型号所有批次的良品数加总再算，不是简单平均。C2放良率：

=1-SUMIFS(数据录入!E:E,数据录入!C:C,A2)/SUMIFS(数据录入!D:D,数据录入!C:C,A2)

注意：平均良率不能用AVERAGE直接对G列求平均，必须用总数加权，否则批次数量少的产线会被平均稀释。

第五步：按周/按月自动汇总

想在表里加一列"周次"，在"数据录入"表加I列：

=ISOWEEKNUM(A2)

然后在"汇总"表按周次汇总：

=SUMIFS(数据录入!E:E,数据录入!I:I,B2)/SUMIFS(数据录入!D:D,数据录入!I:I,B2)*1000000

月度趋势图直接从这张汇总表插入折线图，选数据区域 → 插入 → 折线图，2分钟出图。

第六步：数据透视表做多维度分析

如果客户要求按"产品×产线×不良类别"交叉分析，用透视表最快：

插入 → 数据透视表 → 行放"产品型号"、列放"产线"、值放"不良数"，再叠加一个值字段放"投入数"，用计算字段就能得到任意维度的DPPM。

关键参数说明

常见问题和避坑提醒

1. 直接对单批次良率取平均，数值会失真

这是最容易踩的坑。一批投5000、良率85%，另一批投50、良率50%，用AVERAGE算出来67.5%，看起来很差；用总数加权算出来（5000×85%+50×50%）/5050≈84.6%，才反映真实水平。正确做法：所有汇总一律用SUMIFS加总投入数和不良数再算，禁用AVERAGE。

2. DPPM和DPMO是两回事，别混用

DPPM按"出货件数"算，DPMO按"缺陷机会数"算。一颗芯片有100个焊点，客户投诉虚焊，如果按焊点数当机会算，DPMO会远低于按芯片颗数算的DPPM。正确做法：写报告前先和客户确认口径，一般客诉DPPM按颗数算，过程监控才用DPMO。

3. 投产数为0或空白的行，公式直接报错

如果表里混着空行或没填完的行，E/D会产生#DIV/0!，整个汇总列全变错误值。正确做法：所有除法公式都用IF(D2=0,0,...)包一层，同时用条件格式把异常值标红，录入时就能发现。

4. 数据透视表忘了刷新

透视表不会自动更新——新增了几行数据，透视表还是旧数，月报交出去才发现数据对不上，尴尬到脚趾扣地。正确做法：右键透视表 → 刷新；或右键 → 数据透视表选项 → 数据 → 打开文件时刷新数据。

5. 手工抄数本身就是错误源

用Excel模板自动算，前提是数据准确录入。如果检验员还在用纸笔记录再转抄，抄错概率不低。正确做法：建议用扫码枪或检验系统直接导出数据，粘贴到"数据录入"表，连录入环节都省掉，这才是真正的自动化。

总结

这套模板的价值不在公式本身，而在一次搭建、长期复用。建好之后，检验数据往里一贴，良率、DPPM、周趋势、产品对比全部自动更新，月报10分钟出稿，还能顺手做个趋势图发给客户。

建议先复制一份模板存档，每次月报用新副本，避免公式被误改。建一次表，后面每个月的自己都是受益者。

本文为质量数据分析系列第1篇，关注公众号获取后续良率损失分析、SPC控制图自动生成等系列内容。