运营管理之PMC*Excel函数实战培训

      做为一个从基层一步步提升企业中高层管理的高级职业经理人，一个立志于企业经运营终生修炼人，今天为读者，特别是PMC职业从业者分享运营管理之PMC*Excel函数实战培训，希望有所启发和帮助，加油！

   制造业PMC Excel函数实战培训——从数据处理到智能决策

📋 课程目录

1. 课程目标与前置准备

2. 查找与引用函数（VLOOKUP / XLOOKUP / INDEX+MATCH / OFFSET）

3. 逻辑判断与容错函数（IF / IFS / IFERROR）

4. 文本处理函数（LEFT/RIGHT/MID / TEXT / TRIM）

5. 统计与汇总函数（SUMIFS/COUNTIFS / SUMPRODUCT / GROUPBY / PERCENTOF）

6. 日期与时间函数（DATEDIF / NETWORKDAYS / EOMONTH）

7. 动态数组与筛选函数（UNIQUE / FILTER / SORT）

8. 高级累计函数（SCAN / REDUCE）

9. 综合应用案例：物料齐套分析与库存周转计算

10. 常见错误与最佳实践

1. 课程目标与前置准备

🎯 学习目标

· 掌握PMC日常工作中高频使用的20+个Excel函数

· 能够独立搭建库存分析、生产排程、物料追踪等自动化模板

· 理解函数组合逻辑，提升数据处理效率与准确性

🛠️ 前置准备

· Excel 2016 或更高版本（部分函数需 Microsoft 365）

· 建议将数据源转为 「表格」（Ctrl+T），便于公式自动扩展

2. 查找与引用函数

🔹 VLOOKUP —— 垂直查找的“老黄牛”

语法：=VLOOKUP(找谁, 在哪找, 返回第几列, 0/1)

PMC案例：根据物料编码从物料主数据表返回物料描述与基本单位。

excel

=VLOOKUP(A2, 物料主数据!A:C, 2, 0)   // 返回物料描述

⚠️ 注意：查找值必须在首列，且无法向左查。

🔹 XLOOKUP —— 新一代全能查找

语法：=XLOOKUP(找谁, 查找列, 返回列, [未找到值], [匹配模式], [搜索模式])

PMC案例：根据物料编码反向查找物料描述，无需数第几列。

excel

=XLOOKUP(A2, 物料表!C:C, 物料表!A:A, "物料不存在")

✅ 优势：支持从左向右或从右向左查，自带容错，效率更高。

🔹 INDEX + MATCH —— 黄金组合，双向查找

语法：=INDEX(返回区域, MATCH(找谁, 条件列, 0), [列号])

PMC案例：根据物料编码和仓库位置，从二维库存表中提取数量。

excel

=INDEX(库存明细!$C$3:$G$100, MATCH(A2, 库存明细!$B$3:$B$100, 0), MATCH(B1, 库存明细!$C$2:$G$2, 0))

🔹 OFFSET —— 动态区域构建器

语法：=OFFSET(起点, 下移行数, 右移列数, [高度], [宽度])

PMC案例：制作最近7天产量的动态滚动图表数据源。

excel

=OFFSET(产量表!$A$1, COUNTA(产量表!$A:$A)-7, 1, 7, 1)

3. 逻辑判断与容错函数

🔹 IF —— 基础条件分支

PMC案例：判断库存是否低于安全库存。

excel

=IF(D2<安全库存!$B$2, "需补货", "充足")

🔹 IFS —— 多条件清晰判断

PMC案例：根据库龄区间划分物料状态。

excel

=IFS(E2<=30, "正常", E2<=60, "临期预警", E2>60, "呆滞")

🔹 IFERROR —— 屏蔽错误值

PMC案例：VLOOKUP查找不到时显示友好提示。

excel

=IFERROR(VLOOKUP(A2, 物料表!A:B, 2, 0), "查无此料")

4. 文本处理函数

🔹 LEFT / RIGHT / MID —— 编码字段提取

PMC案例：物料编码规则为“分类(2位)-流水号”，提取分类代码。

excel

=LEFT(A2, 2)   // 返回前两位分类

=MID(A2, 4, 5)  // 从第4位起提取5位流水号

🔹 TEXT —— 格式化日期与数字

PMC案例：将计划日期显示为“YYYY-MM-DD”，将数量补零显示。

excel

=TEXT(B2, "yyyy-mm-dd")          // 日期标准化

=TEXT(C2, "00000")               // 工单号补足5位🔹 TRIM —— 清洗多余空格

PMC案例：清除从ERP导出的物料描述中的首尾空格及中间多余空格。

excel

=TRIM(C2)

5. 统计与汇总函数

🔹 SUMIFS / COUNTIFS —— 多条件求和与计数

PMC案例：统计某仓库中特定物料的库存总量。

excel

=SUMIFS(库存表!E:E, 库存表!A:A, A2, 库存表!C:C, "原材料仓")

COUNTIFS案例：统计某物料在本月的到货次数。

excel

=COUNTIFS(收货表!A:A, A2, 收货表!B:B, ">="&DATE(2026,4,1), 收货表!B:B, "<="&DATE(2026,4,30))

🔹 SUMPRODUCT —— 乘积之和，计算总金额/总工时

PMC案例：计算多张工单的总工时（数量×单件工时）。

excel

=SUMPRODUCT(工单表!D2:D100, 工单表!E2:E100)   // D列为数量，E列为单件工时

🔹 GROUPBY / PIVOTBY —— 函数式透视（Excel 365）

PMC案例：按产品系列汇总计划数量与金额。

excel

=GROUPBY(计划表[产品系列], 计划表[计划数量], SUM)

📌 替代手动透视表，实现动态刷新。

🔹 PERCENTOF —— 快速计算占比（Excel 365）

PMC案例：计算某物料库存金额占总库存金额的百分比。

excel

=PERCENTOF(SUMIFS(库存金额列, 物料列, A2), 总库存金额)

6. 日期与时间函数

🔹 DATEDIF —— 计算工龄、库龄

语法：=DATEDIF(开始日期, 结束日期, "单位")

PMC案例：计算物料入库至今的天数。

excel

=DATEDIF(B2, TODAY(), "d")   // 库龄天数

🔹 NETWORKDAYS —— 计算实际工作日

PMC案例：根据工单下达日期与完工日期，计算生产占用工作日（扣除周末与假日）。

excel

=NETWORKDAYS(C2, D2, 节假日表!A:A)

🔹 EOMONTH —— 返回月末日期

PMC案例：生成月结报表的截止日期（如本月最后一天）。

excel

=EOMONTH(TODAY(), 0)   // 本月月末

7. 动态数组与筛选函数

🔹 UNIQUE —— 提取不重复料号

PMC案例：从采购明细中提取唯一物料清单，用于制作下拉菜单。

excel

=UNIQUE(采购表[物料编码])

🔹 FILTER —— 筛选符合条件的全部记录

PMC案例：筛选所有逾期未交付的采购订单。

excel

=FILTER(采购表, (采购表[要求交期]<TODAY())*(采购表[实收数量]=0), "无逾期订单")

🔹 SORT —— 动态排序

PMC案例：将物料按库存数量降序排列，快速识别高库存物料。

excel

=SORT(库存表, 库存表[库存数量], -1)

8. 高级累计函数（LAMBDA 辅助函数）

适用于 Microsoft 365，用于处理复杂的累计逻辑。

🔹 SCAN —— 扫描数组并返回每一步的中间值

PMC案例：计算每日累计入库量。

excel

=SCAN(0, 入库表[当日入库量], LAMBDA(acc, val, acc+val))

🔹 REDUCE —— 将数组归约为单一值

PMC案例：累加所有物料的总需求金额（单价×需求量）。

excel

=REDUCE(0, SEQUENCE(ROWS(需求表)), LAMBDA(total, i, total + INDEX(需求表[单价], i) * INDEX(需求表[需求量], i)))

💡 此写法可避免SUMPRODUCT行数限制，且便于嵌套复杂逻辑。

9. 综合应用案例：物料齐套分析与库存周转率

📦 场景描述

某电子装配车间需检查生产工单的物料齐套性，并计算关键物料的库存周转天数。

数据源：

· BOM表：成品编码、物料编码、单台用量

· 库存表：物料编码、可用库存

· 工单表：工单号、成品编码、计划数量、开工日期

① 计算每张工单的物料毛需求

excel

=工单计划数量 * XLOOKUP(物料编码, BOM表[物料编码], BOM表[单台用量])

② 判断单张工单是否齐套

使用 FILTER + SUMIFS + IF 组合：

excel

=IF(SUM((工单毛需求 - SUMIFS(库存[可用库存], 库存[物料编码], BOM[物料编码]))>0), "缺料", "齐套")

③ 计算物料周转天数

excel

=AVERAGE(库存[可用库存]) / (SUM(出库表[出库数量])/30)   // 月度周转天数

可结合 SUMIFS 与 DATEDIF 自动刷新。

10. 常见错误与最佳实践

常见错误/原因/解决方案

#N/A/查找值不存在/使用 IFERROR 或 XLOOKUP 第4参数

#VALUE!/数据类型不匹配/检查文本型数字，用 VALUE 函数转换

#REF!/引用的单元格被删除/使用表格结构化引用（如 表名[列名]）

公式不更新/未使用动态数组函数/改用 FILTER、SORT 等新函数

✅ 最佳实践建议；

1. 所有源数据使用「表格」，公式自动扩展且可读性强。

2. 将关键参数（如安全库存、工作日历）放在单独的「参数表」中引用。

3. 复杂公式分步写，利用辅助列降低调试难度。

4. 版本允许时优先使用 XLOOKUP、FILTER、GROUPBY 等新函数。

📎 附录：函数速查表（按用途分类）

用途/推荐函数

精确查找/XLOOKUP

二维交叉查询 /INDEX+MATCH

动态下拉菜单/UNIQUE + 数据验证

多条件汇总/SUMIFS / COUNTIFS

按条件提取清单/FILTER

累计计算（库存流水）/SCAN 或 SUM+OFFSET

库龄分析 DATEDIF + IFS

排程工作日计算 NETWORKDAYS