📚 课程目录第4章成本数据采集与整理

4、成本数据采集与整理：从ERP、Excel、手工台账到可分析数据集

玉石成本看板的价值完全取决于底层数据的质量。如果采集口径不一致、存在重复记录或缺失关键字段，再精美的可视化图表也会传递错误信号。本章聚焦于将ERP系统、Excel报表、手工台账三类典型来源的数据，转化为统一、干净、可分析的结构化数据集，为后续的指标计算与看板搭建奠定坚实基础。

4.1 数据源盘点与采集策略

核心原理：玉石加工企业的成本数据天然分散在多个载体中。ERP系统通常记录采购付款、库存收发、财务凭证等结构化数据；Excel报表多用于加工损耗统计、销售订单毛利测算等灵活分析；手工台账则记录了原石编号、切磨师傅、抛光批次等过程性细节。采集策略的核心不是“全部导入”，而是“按看板指标反推所需字段”，避免无效数据占用处理时间。

数据源	典型内容	采集方式	更新频率	质量风险
ERP系统	采购订单、入库单、领料单、财务凭证	SQL查询 / 报表导出 / API接口	日/实时	编码规则不一致、跨模块时间差
Excel报表	加工损耗表、销售明细、费用报销汇总	文件导入 / Power Query合并	周/月	手工修改公式、合并单元格、多版本文件
手工台账	原石编号、切磨工时、抛光批次、师傅姓名	人工录入 / 扫码枪 / 移动端表单	日	字迹潦草、漏记、编号重复

操作流程：第一步，与财务、生产、仓库负责人确认各数据源的“唯一业务含义”，例如“原石成本”在ERP中可能拆分为材料费+运费+关税；第二步，制定《数据采集模板》，明确字段名、类型、必填项；第三步，设定采集时间窗口，避免月结期间数据不一致；第四步，建立原始数据备份目录，按“日期_来源_版本”命名。

经验提醒：对于手工台账，不要直接要求师傅“填Excel”，而是设计带下拉选项的录入表单，将“缅甸翡翠”“危地马拉料”等常用选项预设好，从源头减少脏数据。

4.2 数据清洗：格式统一与去重

核心原理：清洗的本质是让不同来源的数据“说同一种语言”。格式统一包括日期格式（2024/1/5 vs 2024-01-05）、编码规则（原石编号“M-001” vs “M001”）、计量单位（克 vs 公斤 vs 两）。去重则需要识别“完全重复”和“业务重复”——前者是同一行被导出了两次，后者是同一笔业务在不同系统中被分别记录。

判断方法：

完全重复：所有字段值相同，直接删除保留一行。
主键重复：以“原石编号+批次号”为唯一键，若重复则对比各字段差异，保留信息最完整的一行。
跨源重复：例如ERP中的采购单与Excel中的手工登记表可能指向同一批原石，需通过“供应商+日期+金额”模糊匹配，人工确认后合并。

# 示例：使用Python pandas进行去重与格式统一
import pandas as pd

df_erp = pd.read_excel("erp_采购明细.xlsx")
df_excel = pd.read_excel("手工_原石台账.xlsx")

# 统一日期格式
df_erp["采购日期"] = pd.to_datetime(df_erp["采购日期"]).dt.strftime("%Y-%m-%d")
df_excel["日期"] = pd.to_datetime(df_excel["日期"]).dt.strftime("%Y-%m-%d")

# 统一原石编号（去除横杠和空格）
df_erp["原石编号"] = df_erp["原石编号"].str.replace("-", "").str.strip()
df_excel["原石编号"] = df_excel["原石编号"].str.replace("-", "").str.strip()

# 合并并去重
df_merged = pd.concat([df_erp, df_excel], ignore_index=True)
df_merged = df_merged.drop_duplicates(subset=["原石编号", "采购日期"], keep="last")

案例拆解：某玉石加工厂在搭建成本看板时，发现“切磨损耗率”计算异常偏高。排查后发现，ERP中“投入原石重量”以“克”记录，而Excel损耗表中“废料重量”以“两”记录（1两=50克），导致损耗率虚增约40%。通过统一单位为“克”并重新计算，损耗率从18.7%修正为13.2%，更接近行业正常水平。此案例说明，格式统一不是简单的“看着整齐”，而是直接影响业务指标的准确性。

风险提示：去重时务必保留原始备份。曾经有企业误删了“看似重复”的记录，后来发现其中一条包含重要的“瑕疵等级”信息，导致成本分摊失真。建议在清洗脚本中增加“被删除行数”日志，并保存到单独工作表。

4.3 缺失值处理策略

核心原理：缺失值处理没有“一刀切”的方案，需根据字段的业务属性和缺失比例决定策略。对于关键成本字段（如原石采购价），缺失可能导致看板指标无法计算；对于辅助字段（如加工师傅姓名），缺失不影响成本汇总，可暂时保留。

缺失字段类型	缺失比例	推荐策略	具体操作
关键数值（采购价、重量）	< 5%	填充估算	按同批次/同品质原石均价填充，并标记“估算值”
关键数值	≥ 5%	回溯补录	联系采购员或财务补录，无法补录则剔除该记录
分类维度（料型、颜色）	任意比例	单独标记	填充为“未知”，看板中单独展示，避免影响其他维度
文本备注（供应商全称）	较高	保留空值	不参与计算，仅在明细表中展示

操作流程：先统计每列缺失率，绘制缺失值热力图；然后按上述策略逐字段处理；最后生成《缺失值处理说明》，记录每个字段的填充规则和影响范围。对于“原石编号”缺失的记录，建议直接剔除，因为无法关联后续加工成本。

  实战场景：一家翡翠加工厂的“抛光费用”字段缺失率达12%，原因是部分批次由外协加工，费用记录在纸质单据上未录入系统。处理方案：将外协加工费按“件数×单件市场价”估算填充，并在看板中增加“估算占比”指标，提醒管理层该部分数据存在不确定性。后续推动外协费用周报制度，从源头解决缺失问题。

4.4 数据质量校验与输出

核心原理：清洗完成不等于数据可用，必须通过质量校验才能进入看板数据集。校验分为四个维度：完整性（必填字段无空值）、唯一性（主键不重复）、合规性（日期格式、数值范围）、业务合理性（如单价不为负数、损耗率不超过100%）。

校验清单：

完整性：检查“原石编号”“采购日期”“采购金额”“重量”四个核心字段，缺失率必须为0。
唯一性：以“原石编号+批次号”为联合主键，重复记录数必须为0。
格式合规：所有日期为YYYY-MM-DD，所有金额保留两位小数，所有重量统一为克。
业务合理性：筛选出单价<0、重量>10000克（超出原石正常范围）、损耗率>50%等异常记录，逐条人工复核。

输出格式：最终数据集建议输出为CSV或Parquet格式，字段命名遵循“小写字母+下划线”规范，例如purchase_date、stone_id、material_cost。同时生成一份data_dictionary.md数据字典，说明每个字段的含义、来源、单位、取值范围。

经验总结：数据清洗不是一次性工作，而是需要建立“月度清洗-季度审计”的循环机制。每次看板数据刷新前，自动运行清洗脚本，并输出质量报告。如果某月质量指标下滑，需回溯是源头录入问题还是清洗规则失效，及时调整。

复盘与常见误区：误区一：认为“ERP导出的数据就是干净的”，实际上ERP中同样存在手工修改、跨模块时间差等问题。误区二：过度清洗导致数据失真，例如将缺失的采购价全部填充为平均值，会掩盖价格波动趋势。误区三：忽略数据血缘，清洗后的数据无法追溯到原始记录，一旦看板指标异常，难以定位原因。正确的做法是：保留原始数据层、清洗后数据层、业务计算层三层结构，每层之间通过“数据版本号”关联，确保任何指标都可追溯。