工业工程师的 Excel 实战手册：从工时分析、过程能力到线平衡的 30 个技法

论工具，IE 有仿真软件、有 Python、有 MES；但真正每天陪你进车间、能让班组长看懂、能让老板当场拍板的，还是 Excel。这篇不讲花哨技巧，只讲工业工程场景下真正用得上的东西：连续测时数据的差分处理、异常值剔除、标准时间模板、Cpk 与 ppm 换算、安全库存、ABC 分类、山积图、控制图与帕累托图，全部配可复现的算例和对应公式。

一、为什么 IE 绕不开 Excel

1.1 一个不太体面但很真实的判断

工业工程专业的学生容易有两种极端：一种觉得 Excel 太土，一心扑在 Python 和仿真软件上；另一种觉得 Excel 万能，所有东西都往表格里塞。两种都不对。

Excel 在 IE 工具箱里的真实定位，是"数据的第一现场"和"结果的第一载体"。

判断标准很简单：如果这件事的参与者里有"不会写代码的人"，Excel 就是最优解。 而 IE 的工作恰恰 90% 都要和不会写代码的人协作。

1.2 三个绕不开的现实

1. 数据从 Excel 来：MES 导出的、质检录入的、供应商发来的，第一手几乎都是 Excel/CSV。
2. 交付物要 Excel：改善报告、工时标准、成本核算、月度质量分析，评审时要能当场改、当场算。
3. 一线只会 Excel：你做的标准工时模板，最终是班组长在填。可维护性比先进性重要。

1.3 本文的统一示例数据集

后面所有算例共用一套数据，方便对照：

说明：本文公式按 Microsoft 365 / Excel 2021+ 写法给出。部分动态数组函数（FILTER、SORT、UNIQUE、LET、XLOOKUP）在 2019 及更早版本不可用，文中会标注替代方案。中文版 Excel 的函数名同样是英文，分隔符同样是逗号，不需要输入中文函数名。

二、数据录入与清洗：80% 的时间花在这里

2.1 一维表 vs 二维表：最常见的结构性错误

二维交叉表是给人看的，一维明细表是给机器算的。 一线录入时为了省事常做成这样：

这个表做透视分析会很痛苦（无法按"班次"作为筛选字段）。正确的一维表是：

规则：一列一字段，一行一记录。 录入表可以做成二维方便填写，但分析前必须转一维。

2.2 二维转一维：Power Query 逆透视（3 分钟搞定）

数据 → 获取和转换数据 → 从表格/区域
     → 选中日期列 → 转换 → 逆透视列 → 逆透视其他列
     → 重命名「属性」为「班次」、「值」为「不合格数」
     → 开始 → 关闭并上载

这一步的真正价值是一次配置、长期复用：下个月把新数据粘进源表，右键刷新即可。手工转置每月一次，逆透视配一次永久生效。

2.3 五个必会的清洗操作

识别文本型数字最快的办法：=ISTEXT(A2) 返回 TRUE 就是文本。批量检查用 =SUMPRODUCT(--ISTEXT(A2:A100))。

2.4 时间格式的三个坑

坑一：秒表读数被当成时间。 输入 34.1 表示 34.1 秒没问题；但如果输入 0:34.1，Excel 会把它当成"34.1 秒"存储为天的小数（0.000394…），直接求和会得到天，不是秒。

正确处理：测时数据一律用小数秒录入（34.1 而不是 0:34.1）。如果已经是时间格式，转换公式：

=A2*86400        ' 时间格式 → 秒（1天 = 86400秒）
=TEXT(A2/86400,"[s].0")   ' 秒 → 时间文本显示

坑二：跨午夜的工时计算。 用 =B2-A2 算夜班时长，跨午夜会得到负数。正确写法：

=MOD(B2-A2,1)    ' 自动处理跨午夜

坑三：显示位数骗人。 单元格显示 34.1，实际可能是 34.0999。做判断和比较时要用 ROUND，否则 =IF(A2=34.1,...) 可能不成立。

=ROUND(A2,1)

2.5 数据验证：从源头掐死录错

在模板里预设数据验证，比事后清洗有效十倍。

关键技巧：序列来源要引用一个"参数表"区域（或定义名称），而不是硬编码在验证里。 这样增加班次时改参数表即可，不用逐个改验证。

三、工时数据处理：从秒表读数到标准时间

3.1 连续测时法的差分处理

连续测时法（Continuous Timing）的做法是：秒表从第一次观测开始不停，记录每个作业单元的终点时刻。这样不会漏掉任何时间。

D1 数据集（单位：秒，累计读数）：

第一步，差分还原每个单元的时间。

在 Excel 中（假设 A 列单元读数在 B2:B13，D 列在 E2:E13），A 单元第 n 循环时长：

第一个循环（F2）：=B2
后续循环（F3）：  =B3-E2      ' 本次A读数 - 上次D读数

B、C、D 单元则是同一循环内的相邻列相减：

B单元（G2）：=C2-B2
C单元（H2）：=D2-C2
D单元（I2）：=E2-D2

第二步，算循环时间：

（J2）=E2
（J3）=E3-E2

还原结果（单元时长，秒）：

第 6 循环 A 单元 8.9 s 明显偏长，现场记录写明："拾取时螺丝掉落，弯腰捡拾"。

3.2 异常值剔除：为什么 3σ 在这里不好用

方法一：三倍标准差法

均值    =AVERAGE(J2:J13)
标准差  =STDEV.S(J2:J13)
上限    =均值 + 3*标准差
下限    =均值 - 3*标准差
判定    =IF(OR(J2>上限, J2<下限),"异常","")

本例 12 个循环时间：均值 34.30 s，样本标准差 0.836 s，控制限 [31.79, 36.81]。

结果：36.0 没有被判为异常。 这是小样本下 3σ 法的固有缺陷——异常值本身把标准差拉大了，导致控制限变宽。样本量越小，这个"自我掩护"效应越强。

方法二：四分位距法（IQR），小样本更可靠

Q1     =QUARTILE.INC(J2:J13,1)
Q3     =QUARTILE.INC(J2:J13,3)
IQR    =Q3-Q1
上限   =Q3 + 1.5*IQR
下限   =Q1 - 1.5*IQR

本例：

36.0 > 35.25 → 判定为异常，剔除。

推荐做法：两种方法都做，IQR 为主、3σ 为辅；同时必须以现场记录为准。 本例中即使不做统计检验，现场记录已经写明第 6 循环有异常事件——观测时的异常备注，比任何统计方法都重要。所以观测表上必须留"异常说明"列。

3.3 标准时间计算模板

剔除第 6 循环后，11 个有效数据：

观测平均时间 =AVERAGEIF(K2:K13,"",J2:J13)        ' K列为异常标记
           或 =AVERAGE(IF(K2:K13="",J2:J13))    ' 数组公式

$$\bar{x} = 34.145 \text{ s}, \quad s = 0.372 \text{ s}, \quad n = 11$$

变异系数检验（判断数据是否稳定）：

=STDEV.S(数据)/AVERAGE(数据)

$$CV = \frac{0.372}{34.145} = 1.09%$$

判读：CV < 5% 通常认为作业稳定；5%~10% 需复核；> 10% 说明作业未标准化或观测方法有问题。

需要的观测次数验证（要求均值误差 ≤ ±1%，置信水平 95%）：

$$n = \left(\frac{Z_{\alpha/2} \cdot s}{E \cdot \bar{x}}\right)^2 = \left(\frac{1.96 \times 0.372}{0.01 \times 34.145}\right)^2 = \left(\frac{0.729}{0.341}\right)^2 = (2.14)^2 = 4.57 \approx 5$$

Excel 实现：

=(NORM.S.INV(1-(1-0.95)/2)*STDEV.S(数据)/(0.01*AVERAGE(数据)))^2

结论：11 次有效观测远超所需的 5 次，样本充足。

标准时间计算（评比系数 110%，宽放率 15%）：

$$\text{正常时间} = \bar{x} \times \text{评比系数} = 34.145 \times 1.10 = 37.56 \text{ s}$$

$$\text{标准时间} = \text{正常时间} \times (1 + \text{宽放率}) = 37.56 \times 1.15 = 43.19 \text{ s}$$

模板结构建议（这才是能长期用下去的东西）：

金科玉律：参数与公式分离。 我见过太多模板把 ×1.15 硬写在十几个单元格里，宽放率一改就漏改三处。

3.4 MODAPTS 汇总表的 Excel 实现

模特排时法的动作代码可以用查找表自动换算时间：

（1 MOD = 0.129 s 为常用取值，具体以所用标准的规定为准）

计算式：

=XLOOKUP(代码单元格, 代码表!$A$2:$A$6, 代码表!$B$2:$B$6, 0)

若一次作业记录为 M3、M2、M4、M1（4 个动作），则：

=SUM(XLOOKUP(A2:A5, 代码表!$A$2:$A$6, 代码表!$B$2:$B$6, 0))
= 0.387 + 0.258 + 0.516 + 0.129 = 1.29 s = 10 MOD

注意：MODAPTS 只给出"正常时间"，不含宽放。 加宽放后才可与秒表法得到的标准时间对比。

四、统计与过程能力：从 Cpk 到 ppm

4.1 标准差用 S 还是 P：一个高频错误

Cpk 计算必须用 STDEV.S（因为是抽样估计）。用错会让 Cpk 略微偏乐观。

同理：VAR.S / VAR.P、STDEVA（含文本与逻辑值）。

4.2 过程能力算例（D2 数据集）

已知：轴径规格 $25.00 \pm 0.05$ mm，即 LSL = 24.95，USL = 25.05。抽取 30 件，测得 $\bar{x} = 25.012$ mm，$s = 0.0148$ mm。

Cpk 计算：

$$C_{pu} = \frac{USL - \bar{x}}{3s} = \frac{25.05 - 25.012}{3 \times 0.0148} = \frac{0.038}{0.0444} = 0.856$$

$$C_{pl} = \frac{\bar{x} - LSL}{3s} = \frac{25.012 - 24.95}{0.0444} = \frac{0.062}{0.0444} = 1.396$$

$$C_{pk} = \min(C_{pu}, C_{pl}) = 0.856$$

$$C_p = \frac{USL - LSL}{6s} = \frac{0.10}{6 \times 0.0148} = \frac{0.10}{0.0888} = 1.126$$

Excel 实现（均值在 B1，标准差在 B2，USL/LSL 在 B3/B4）：

Cpu  =(B3-B1)/(3*B2)
Cpl  =(B1-B4)/(3*B2)
Cpk  =MIN((B3-B1)/(3*B2),(B1-B4)/(3*B2))
Cp   =(B3-B4)/(6*B2)
偏移度 Ca =(B1-(B3+B4)/2)/((B3-B4)/2)

解读：$C_p = 1.126$（潜在能力尚可），但 $C_{pk} = 0.856$（实际能力不足）。差距来自分布中心偏移——均值 25.012 高于规格中心 25.000，偏向上限侧。

$$C_a = \frac{25.012 - 25.000}{0.05} = 0.24 \quad (24%)$$

改善方向优先级：先把中心调回来（机床偏置），而不是急着降低变差。 中心调正后，$C_{pk}$ 将趋近 $C_p = 1.126$，改善幅度 31.5%——这几乎是不花钱的改善。

不良率（ppm）换算：

$$P(X > USL) = 1 - \Phi\left(\frac{25.05 - 25.012}{0.0148}\right) = 1 - \Phi(2.569) = 1 - 0.99491 = 0.00509$$

$$P(X < LSL) = \Phi\left(\frac{24.95 - 25.012}{0.0148}\right) = \Phi(-4.189) \approx 0.0000141$$

$$\text{总不良率} = 0.00509 + 0.0000141 = 0.005104 \approx \mathbf{5104\ ppm}$$

Excel 实现：

超上限比例 =1-NORM.DIST(B3, B1, B2, TRUE)
超下限比例 =NORM.DIST(B4, B1, B2, TRUE)
总不良率   =1-NORM.DIST(B3,B1,B2,TRUE)+NORM.DIST(B4,B1,B2,TRUE)
ppm        =上式*1000000
Z_bench    =-NORM.S.INV(总不良率)
西格玛水平 =Z_bench+1.5

本例：$Z_{bench} = -\Phi^{-1}(0.005104) = 2.570$，西格玛水平 $= 2.570 + 1.5 = 4.07\sigma$。

交叉验证：$C_{pk} \times 3 = 0.856 \times 3 = 2.568 \approx Z_{bench} = 2.570$（四舍五入误差）。这是检验 Cpk 计算是否正确的快速方法。

重要提醒：以上换算基于正态分布假设。实际过程常常不服从正态（偏态、双峰、拖尾），此时 Cpk 和 ppm 的换算会有明显偏差。先用直方图或正态性检验（如 Anderson-Darling）确认分布形态，再引用 ppm 数值。 同时 1.5σ 漂移是业界通行约定而非物理规律，用于横向比较和自我追踪可以，不要拿去做对外承诺。

4.3 置信区间与样本量

均值的 95% 置信区间：

=CONFIDENCE.NORM(0.05, 标准差, 样本量)
下限 =AVERAGE(数据) - 上式
上限 =AVERAGE(数据) + 上式

本例（30 件，s = 0.0148）：

$$\text{半宽} = 1.96 \times \frac{0.0148}{\sqrt{30}} = 1.96 \times \frac{0.0148}{5.477} = 1.96 \times 0.002702 = 0.00530$$

$$\text{CI} = 25.012 \pm 0.0053 = [25.0067, 25.0173]$$

这个区间的实用价值：它在告诉你"真实的过程中心大概在哪"。本例区间完全落在规格中心 25.000 的右侧，说明偏移是真实的，不是抽样误差造成的——这为中心调整提供了统计依据。

4.4 直方图与分箱统计

不用分析工具库也能做分箱：

方法1（动态数组，365/2021+）：
=FREQUENCY(数据区域, 分箱上限区域)

方法2（兼容性好）：
=COUNTIFS(数据区域,">="&下限, 数据区域,"<"&上限)

方法3（动态数组，最简单）：
=LET(bins, SEQUENCE(10,1,24.95,0.01),
     HSTACK(bins, FREQUENCY(数据, bins)))

分箱数经验法则：

$$k = \lceil \sqrt{n} \rceil$$

$n = 30$ 时 $k = \lceil 5.48 \rceil = 6$ 组；$n = 100$ 时 $k = 10$ 组。也可用 Sturges 公式 $k = 1 + \log_2 n$。

五、线平衡与产能：山积图与工位分配

5.1 D5 数据集与山积图

8 个作业单元，节拍 85 s，先后约束如下：

理论最少工位数：

$$N_{\min} = \left\lceil \frac{\sum t_i}{T_{takt}} \right\rceil = \left\lceil \frac{288}{85} \right\rceil = \lceil 3.39 \rceil = 4$$

Excel：=ROUNDUP(288/85,0)

山积图的做法：

1. 准备"工位 × 作业单元"的时间矩阵（只填有分配的格子）
2. 插入 → 图表 → 堆积柱形图
3. 添加节拍线：新增一列常数列 85，改为折线图系列（右键 → 更改系列图表类型 → 折线图）
4. 每个作业单元用不同颜色 → 可直观看到同一单元分布在哪几个工位

5.2 改善前后的对比算例

改善前（按经验分配，5 个工位）：

$$\text{平衡率} = \frac{288}{5 \times 74} \times 100% = \frac{288}{370} \times 100% = 77.8%$$

$$\text{总空闲} = 5 \times 74 - 288 = 370 - 288 = 82 \text{ s}$$

改善后（按约束重排，4 个工位）：

$$\text{平衡率} = \frac{288}{4 \times 80} \times 100% = \frac{288}{320} \times 100% = 90.0%$$

$$\text{总空闲} = 4 \times 80 - 288 = 320 - 288 = 32 \text{ s}$$

对比：

注意"瓶颈工位时间反而增加"这个反直觉现象：从 74 s 涨到 80 s，但整体变好了。因为决定产出的是"工位数 × 节拍"，而不是瓶颈时间——只要所有工位都在节拍内，产出就由节拍决定。改善的本质是用更少的工位完成同样的产出。

5.3 用规划求解（Solver）自动分配工位

作业单元多时（比如 30 个），手排很痛苦。可以用 Solver：

建模方式：

步骤：

文件 → 选项 → 加载项 → Excel 加载项 → 勾选「规划求解加载项」
数据 → 规划求解
   设置目标：总空闲时间单元格 → 最小值
   通过更改可变单元格：X 矩阵区域
   遵守约束：① 每行和 = 1  ② 每列时间和 ≤ 85  ③ 变量为 bin（二元）
   求解方法：选择「演化」（因为有二元变量和非线性约束）

实务提醒：

• 单元数超过 20 个时，演化法求解可能很慢或只能得到可行解而非最优解。先手工排一个可行方案作为初值，Solver 会快很多。
• 先后约束的建模是难点。简化做法：先按"位置权重法（Positional Weight）"手工排序，再用 Solver 做局部优化。
• 求解结果必须人工复核先后约束是否真的满足（Solver 有时会在约束建模不严谨时给出违反约束的"解"）。

六、库存与计划：EOQ、安全库存与 ABC 分类

6.1 EOQ 与再订货点（D3 数据集）

已知：年需求 $D = 12{,}000$ 件，单次订货成本 $S = 180$ 元/次，单价 45 元，年持有成本率 25%（即 $H = 45 \times 0.25 = 11.25$ 元/（件·年））。

$$EOQ = \sqrt{\frac{2DS}{H}} = \sqrt{\frac{2 \times 12000 \times 180}{11.25}} = \sqrt{\frac{4{,}320{,}000}{11.25}} = \sqrt{384{,}000} \approx 620 \text{ 件}$$

=SQRT(2*D*S/H)
年订货次数 =D/EOQ                    ' 12000/620 = 19.35 次
订货间隔（天）=365/(D/EOQ)           ' 365/19.35 = 18.9 天
年总成本 =(D/Q)*S + (Q/2)*H          ' 3483 + 3487.5 = 6970.5 元

EOQ 的三个实务提醒：

1. 总成本曲线在 EOQ 附近非常平坦。批量取 500 或 750，总成本增加通常不到 5%。所以 EOQ 应该被当作参考量级，而不是必须精确执行的数——有运输整车、包装规格等约束时，取接近 EOQ 的"整箱/整托"数量更合理。
2. H 的取值最影响结果，而持有成本率（资金成本 + 仓储 + 损耗 + 保险）往往估不准。做敏感性分析：把 H 分别取 15%、25%、35%，看 EOQ 变化范围。
3. EOQ 假设需求恒定、瞬时到货。有折扣、允许缺货、渐进到货时要换模型。

6.2 安全库存与再订货点

已知：日需求均值 $\bar{d} = 33$ 件，日需求标准差 $\sigma_d = 12$ 件，补货提前期 $L = 5$ 天（固定），目标服务水平 95%（$z = 1.645$）。

$$\sigma_L = \sigma_d \times \sqrt{L} = 12 \times \sqrt{5} = 12 \times 2.236 = 26.83$$

$$SS = z \times \sigma_L = 1.645 \times 26.83 = 44.1 \approx 45 \text{ 件}$$

$$ROP = \bar{d} \times L + SS = 33 \times 5 + 45 = 165 + 45 = 210 \text{ 件}$$

Excel 实现：

z        =NORM.S.INV(0.95)                        ' 1.6449
σ_L      =σ_d*SQRT(L)
SS       =z*σ_L
ROP      =d̄*L + SS

关键细节一：为什么用 $\sqrt{L}$ 而不是 $L$？ 因为各天需求波动相互独立，方差可加而标准差不可加：$Var_L = L \times \sigma_d^2$，故 $\sigma_L = \sqrt{L} \times \sigma_d$。用 $L \times \sigma_d$ 会严重高估安全库存（本例会算成 98.7 件，是正确值的 2.24 倍）。

关键细节二：提前期本身也波动时，公式变为：

$$\sigma_L = \sqrt{L \cdot \sigma_d^2 + \bar{d}^2 \cdot \sigma_L^2}$$

（其中第二项的 $\sigma_L$ 为提前期的标准差，符号重名是惯例。）

设 $\sigma_L = 1.5$ 天：

$$\sigma_L^{总} = \sqrt{5 \times 12^2 + 33^2 \times 1.5^2} = \sqrt{720 + 2450.25} = \sqrt{3170.25} = 56.3$$

$$SS = 1.645 \times 56.3 = 92.6 \approx 93 \text{ 件}$$

提前期波动把安全库存从 45 件推到 93 件，翻了一倍多。 这解释了为什么"催供应商稳定交期"往往比"多备库存"更有效——降低提前期波动，是降低安全库存最被低估的杠杆。

6.3 ABC 分类（D3 数据集）

Excel 实现（年金额在 D 列，数据区 D2:D11）：

年金额    =B2*C2
降序排名  =RANK.EQ(D2,$D$2:$D$11,0)
累计金额  =SUMIF($D$2:$D$11,">="&D2)        ' 技巧：算出所有 >= 本行的金额之和
累计占比  =累计金额/SUM($D$2:$D$11)
分类      =IFS(累计占比<=0.8,"A", 累计占比<=0.95,"B", TRUE,"C")

兼容性：早期版本没有 IFS，用嵌套 IF：=IF(占比<=0.8,"A",IF(占比<=0.95,"B","C"))

结果：

汇总：

两个实务判断：

1. A 类占 40% 的 SKU 数偏多（典型分布是 A 类 10%~20%）。这说明该品类金额结构比较扁平。阈值不必死守 80/95，可以按 70/90 重新划分，或者干脆按"金额 + 关键性"双维度（C01 单价 130 元虽然金额不大，但如果它是唯一供应源或是安全件，应按 A 类管）。
2. ABC 只看了金额一个维度。更完整的做法是 ABC × XYZ 交叉分类（XYZ 看需求波动性：X 稳定、Y 中等、Z 极不稳定）。AZ 类（高金额 + 极不稳定）是最该投入精力的，因为它们既贵又难预测。

七、查找引用与多条件汇总

7.1 三个查找函数的选型

标准写法：

XLOOKUP（推荐）：
=XLOOKUP(查找值, 查找数组, 返回数组, "未找到", 0)

INDEX+MATCH（兼容）：
=INDEX(返回列, MATCH(查找值, 查找列, 0))

VLOOKUP 必须用精确匹配（第4参数写 0 或 FALSE）：
=VLOOKUP(查找值, 表格区域, 列序号, 0)     ' 漏写第4参数会默认近似匹配，是经典事故源

XLOOKUP 的一个 IE 实用技巧——一次返回多列：

=XLOOKUP(A2, 物料表!$A$2:$A$500, 物料表!$B$2:$E$500)

一条公式返回 B:E 四列（名称、规格、单位、单价），结果自动溢出到右侧单元格。

7.2 多条件汇总三剑客

=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)
=COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)
=AVERAGEIFS(平均区域, 条件区域1, 条件1, ...)

典型 IE 场景：

条件写法要点：条件是文本或表达式时必须用引号，引用单元格或公式时用 & 连接。日期比较务必用 DATE() 或引用单元格，不要写 ">2026/5/1" 这种字符串（在部分区域设置下会失效）。

7.3 SUMPRODUCT：被低估的万能函数

=SUMPRODUCT((条件1)*(条件2)*求和区域)

场景一：加权求和（ABC 评分、供应商评分）

=SUMPRODUCT(B2:B5, C2:C5)     ' 得分 × 权重

场景二：多条件求和（SUMIFS 做不到时）

=SUMPRODUCT((班次="甲")*(月份=5)*(不合格数))

场景三：或条件（SUMIFS 只能做"与"）

=SUMPRODUCT(((班次="甲")+(班次="乙"))*不合格数)   ' 加号 = 或

场景四：跨表加权平均

=SUMPRODUCT(数量, 单价)/SUM(数量)     ' 加权平均单价

注意：SUMPRODUCT 对大范围（> 10 万行）性能较差，此时改用 SUMIFS 或透视表。

7.4 动态数组：让公式彻底不同（365/2021+）

实例：自动输出 Top 5 缺陷

=LET(
  缺陷, UNIQUE(缺陷列),
  次数, COUNTIF(缺陷列, 缺陷),
  排序, SORTBY(HSTACK(缺陷, 次数), 次数, -1),
  TAKE(排序, 5)
)

实例：把 Cpk 封装成命名函数

' 名称管理器 → 新建，名称填 CPK，引用位置填：
=LAMBDA(数据, 规格上限, 规格下限,
   LET(m, AVERAGE(数据),
       s, STDEV.S(数据),
       MIN((规格上限-m)/(3*s), (m-规格下限)/(3*s))))
' 之后即可直接调用：
=CPK(A2:A31, 25.05, 24.95)

这一步的价值：把容易写错的统计公式固化一次，全公司复用，避免每人各写一遍各错一遍。

八、透视表与图表：让数据自己说话

8.1 透视表五次点击出结果

1. 选中数据区域（必须是一维表，首行是字段名，无空行空列）
2. 插入 → 数据透视表
3. 行：放维度（如"缺陷类型"）
4. 值：放度量（如"计数项：缺陷"）
5. 右键值字段 → 值显示方式 → 按某一字段汇总的百分比（做帕累托用）

三个必会设置：

最常见的新手错误：数值字段放进了"行"区域，导致透视表出现几百行。数值只能进"值"区域，维度才进"行/列"。

8.2 帕累托图（D4 数据集）

计算累计占比（先按件数降序，已在表中排好）：

累计件数（C2）  =SUM($B$2:B2)
累计占比（D2）  =C2/$B$9

作图步骤：

1. 选中"件数"与"累计占比"两列 → 插入 → 组合图
2. 件数设为簇状柱形图，累计占比设为带数据标记的折线图，勾选次坐标轴
3. 次坐标轴最大值设为 1（100%），主坐标轴最大值设为 合计值（573）
4. 这样两条线在同一个"高度基准"上，80% 线的位置才准确

读图结论：划伤 + 异响两项占 64.0%，加上功能失效达 79.1%。这三项是本轮改善的 A 类目标。

8.3 单值-移动极差（X-MR）控制图

D4 数据集延伸：20 天的日不良率（%），数据如下：

1.9, 2.2, 2.0, 1.8, 2.4, 2.1, 1.9, 2.3, 2.0, 1.7,
2.2, 2.5, 2.1, 1.8, 2.0, 2.3, 1.9, 2.6, 2.4, 3.8

第一步，算移动极差（MR）：

MR(n) = ABS(X(n) - X(n-1))     ' 从第2个点开始
（C3）=ABS(B3-B2)

19 个 MR 值之和 = 7.5，故：

$$\overline{MR} = \frac{7.5}{19} = 0.3947$$

第二步，估计标准差（单值图用 MR 法，$d_2 = 1.128$）：

$$\hat{\sigma} = \frac{\overline{MR}}{d_2} = \frac{0.3947}{1.128} = 0.350$$

第三步，算控制限：

$$\bar{X} = \frac{43.9}{20} = 2.195$$

$$UCL_X = \bar{X} + 3\hat{\sigma} = 2.195 + 3 \times 0.350 = 2.195 + 1.050 = 3.245$$

$$LCL_X = 2.195 - 1.050 = 1.145$$

$$UCL_{MR} = D_4 \times \overline{MR} = 3.267 \times 0.3947 = 1.290, \quad LCL_{MR} = 0$$

第四步，判异：

Excel 实现：

X̄        =AVERAGE(B2:B21)
MR̄       =AVERAGE(C3:C21)
σ̂        =MR̄/1.128
UCL_X    =X̄+3*σ̂
LCL_X    =X̄-3*σ̂
UCL_MR   =3.267*MR̄
作图      插入→折线图，把 X̄、UCL、LCL 三个常数列加为系列

八条判异准则（西方电气规则）中最常用的四条（Excel 里用公式实现）：

实务建议：先只上规则 1 + 规则 2。 一次上八条会淹没在报警里，反而没人看。

8.4 条件格式做异常预警

色阶热力图是 IE 最该多用的一招：把"班次 × 星期"或"工序 × 月份"做成二维表，套上色阶，异常组合会自己跳出来。这比任何统计检验都直观。

九、模板化与自动化

9.1 一个能长期用下去的 IE 模板应该长什么样

五条设计原则：

1. 参数与公式严格分离（前面强调过，最重要）
2. 录入区用颜色标注（约定：蓝底 = 需人工输入，白底 = 自动计算，灰底 = 勿动）
3. 下拉来源全部指向 99_代码表
4. 每个模板写明版本号与最后修改日期，避免"最终版 v3 最终版"地狱
5. 关键公式加批注说明来源（如"宽放率 15% 依据《XX 标准》，2025-03 修订"）

9.1 补充：模板的协作与防呆约定

模板做出来是要交给别人填的。以下五条约定能省掉大量返工：

"越界检查"这一条最容易被忽略，也最救命。 典型写法：

=IF(COUNTA(录入区)<>COUNT(录入区), "⚠ 存在空单元格，请检查", "")
=IF(COUNTA(录入区)>500, "⚠ 超过模板上限 500 行，请拆分", "")

再配合条件格式（包含"⚠"时整行标红），数据出错时一眼可见。

9.2 录制宏：10 分钟做出第一个自动化

适合录制宏的场景：每天/每周重复的固定操作序列——导入数据 → 转一维 → 刷新透视表 → 生成图表 → 导出 PDF。

开发工具 → 录制宏 → 指定名称和快捷键 → 执行一遍操作 → 停止录制

录完必须做两件事：

1. 改用相对引用（录制前点"使用相对引用"），否则宏只会操作录制时的固定单元格
2. 打开 VBA 编辑器看一眼代码，删掉录进去的误操作（选错单元格、点错菜单都会被录制）

9.3 三个 IE 常用的 VBA 片段

片段一：把"分:秒"或纯秒文本统一转成秒数

Function ToSeconds(v As Variant) As Double
    ' 支持 "1:23"（1分23秒）、"83"、"83.5"
    Dim s As String
    s = Trim(CStr(v))
    If InStr(s, ":") > 0 Then
        Dim a
        a = Split(s, ":")
        ToSeconds = Val(a(0)) * 60 + Val(a(1))
    Else
        ToSeconds = Val(s)
    End If
End Function

用法：=ToSeconds(A2)

片段二：一键刷新所有透视表与查询

Sub RefreshAllData()
    Application.ScreenUpdating = False
    ThisWorkbook.RefreshAll
    Dim ws As Worksheet, pt As PivotTable
    For Each ws In ThisWorkbook.Worksheets
        For Each pt In ws.PivotTables
            pt.RefreshTable
        Next pt
    Next ws
    Application.ScreenUpdating = True
    MsgBox "刷新完成", vbInformation
End Sub

片段三：批量把当前工作簿所有工作表导出为 PDF

Sub ExportSheetsToPDF()
    Dim ws As Worksheet, path As String
    path = ThisWorkbook.Path & "\输出_" & Format(Date, "yyyymmdd") & "\"
    If Dir(path, vbDirectory) = "" Then MkDir path
    For Each ws In ThisWorkbook.Worksheets
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=path & ws.Name & ".pdf"
    Next ws
End Sub

安全提醒：启用宏的文件要存为 .xlsm；从外部收到的宏文件先在受保护视图打开检查代码，不要直接启用。

9.4 Power Query：把"每月重复做一遍"变成"刷新一下"

数据 → 获取数据 → 自文件 → 从工作簿/从文件夹
     → 在 Power Query 编辑器中做所有清洗步骤（筛选、逆透视、合并查询、分组）
     → 关闭并上载
     → 下月只需：数据 → 全部刷新

"从文件夹"是最强大的入口：把 12 个月的文件放进同一个文件夹，Power Query 会自动合并全部文件。新增第 13 个月的文件，刷新即可自动纳入。

典型的 IE 月度报表自动化：

十、30 个技法速查表

十一、20 个高频坑

数据层

1. 用二维表直接做透视 → 先逆透视转一维
2. 数字存成文本导致 SUM = 0 → VALUE 或分列
3. VLOOKUP 漏写第 4 参数 → 默认近似匹配，返回错误结果
4. 秒表读数录入成时间格式 → 一律用小数秒
5. 合并单元格 → 透视和排序的死敌，录入表永远不要合并单元格
6. 用空格/颜色表示分组信息 → 机器读不懂，必须单独一列
7. 删除重复值时选错列组合 → 误删有效记录

公式层

8. STDEV.P 与 STDEV.S 混用 → Cpk 偏乐观
9. 引用没有加绝对引用 $ → 下拉公式时引用漂移
10. 日期写死成字符串 ">2026/5/1" → 区域设置改变时失效
11. 浮点误差导致 IF(A1=34.1,...) 不成立 → 用 ROUND
12. 数组公式在旧版本忘记 Ctrl+Shift+Enter
13. IFERROR 把真实错误也吞掉了 → 掩盖问题，慎用

分析层

14. 未剔除异常值就算标准差 → Cpk 严重失真
15. 数据不按班次/批次分层 → 辛普森悖论
16. 只看均值不看分布 → 均值 30 s 标准差 25 s 被当成稳定
17. Cpk 直接换算 ppm 但未验证正态性 → 数量级错误
18. 相关当因果（冰淇淋与溺水）→ 需受控实验验证
19. 安全库存用 $L \times \sigma_d$ 而非 $\sqrt{L} \times \sigma_d$ → 本例会高估 2.24 倍
20. 帕累托图主次坐标轴未对齐（主轴未设为合计值）→ 80% 线画错位置

小结

• Excel 是 IE 的"数据第一现场"和"结果第一载体"。判断标准：如果协作者里有不会写代码的人，Excel 就是最优解。
• 一维表铁律：一列一字段、一行一记录。二维表用 Power Query 逆透视转一维，一次配置永久复用。
• 测时用小数秒录入，0:34.1 会被存成天。跨午夜工时用 =MOD(B2-A2,1)。
• 小样本异常值用 IQR，不要用 3σ。本例 3σ 限 [31.79, 36.81] 漏掉了 36.0 这个异常点，而 IQR 上限 35.25 正确捕获。但最可靠的永远是观测时的现场异常备注。
• 参数与公式严格分离——评比系数、宽放率、规格限全部集中在参数表，绝不硬编码在公式里。
• Cpk 用 STDEV.S。本例 $C_p = 1.126$ 但 $C_{pk} = 0.856$，差距来自中心偏移（$C_a = 24%$）。**先调中心再压变差——前者几乎不花钱。**交叉验证：$C_{pk} \times 3 \approx Z_{bench}$。
• 安全库存用 $\sigma_L = \sigma_d \times \sqrt{L}$。本例用对得 45 件，用错（$L \times \sigma_d$）得 98.7 件，高估 2.24 倍。提前期波动比需求波动更致命：本例把 SS 从 45 推到 93 件。
• 线平衡的改善目标是"用更少工位完成同样产出"，不是"提高瓶颈速度"。本例 5 工位 → 4 工位，瓶颈反而从 74 s 涨到 80 s，但平衡率 77.8% → 90.0%，总空闲 82 s → 32 s。
• 帕累托图的次坐标轴必须把主轴最大值设为合计值，否则 80% 线位置是错的。
• 控制图先上"超出 3σ"和"连续 9 点同侧"两条规则，一次上八条会淹没在报警里。
• 色阶热力图（班次 × 星期）是 IE 最该多用的一招，异常组合会自己跳出来。

配套阅读：《工业工程软件技能地图：从 Excel 到仿真，工具该如何选型》《Python 在工业工程中的落地：从数据清洗到排产优化》《标准工时制定：从时间观测到宽放的完整方法》《线平衡：平衡率、瓶颈工位与 ECRS 改善》《统计过程控制 SPC：控制图、过程能力与判异准则》《质量管控七大手法：检查表、柏拉图、鱼骨图与直方图》《供应链与库存管理：EOQ、安全库存、ABC 分类的实操算法》《MODAPTS 模特排时法：预设时间标准的入门与实操》。

相关阅读

• 工业工程必备软件地图：从 Excel 到 FlexSim，每个阶段该学什么：按学习阶段给出 IE 的软件全景图：Excel、统计分析、仿真建模、CAD、企业系统，并给出…
• 工业工程数据与指标看板：从指标定义、采集口径到可视化落地的完整手册：从指标定义卡 12 要素讲到看板落地：OEE 三种分母口径对照（负荷 75.45% / 计划…
• Minitab 工业工程实战指南：从数据到结论的完整链路：工业工程领域出镜率最高的统计软件。本文按拿到数据后的真实使用顺序组织：数据导入清洗、图形化汇…
• SQL 工业工程数据分析实战：从 MES 取数到指标看板：IE 日常有 60% 的时间花在等数据上。本文从真实取数场景出发，讲透 SQL 核心语法（S…
• Power BI 工业工程看板实战：从数据到管理驾驶舱：每天早会 25 分钟花在对数字上，根源是没有口径统一、自动刷新的数据源。本文讲透 Power…