shield_lock
JinDaGe
← Back to skills
extension
Category: Data & AnalyticsNo API key required

盘点表清洗助手

把杂乱的盘点表 Excel 清洗成标准模板，按 差异 = 实盘 − 账面 自动算出差异并标出盘盈、盘亏、相符。 盘点表清洗助手用于处理仓管、盘点小组交上来的格式不统一的盘点表。它会识别写在表格上方的盘点批次、库房、盘点人、参与盘点人、盘点日期，回填到每一条明细行并标明来源行号；自动映射不同叫法的表头到 14 个标准字段；逐行计算差异并输出统计。 盘点场景特有的处理：实盘数量为 0 是合法数据（现场一件都没盘到），正常进模板并单列，只有空白和非数字判异常；缺账面数量时降级为待比对模板，不推测账面数。已填差异列时校验而非静默覆盖，不符时写出两个操作数。 输出包含 7 个工作表的标准模板 Excel + 盘点报告 + 异常行 CSV。报告中盘盈/盘亏统计会写出每一行的物资名称与差异数量，避免二次复述时方向读反。 本 Skill 只处理用户上传的 Excel 文件，不连接任何生产系统，不调整库存，不执行平账，差异类型是计算结果非盘点结论。

personAuthor: u_d1525ce8hubenterprise
downloadDownload package

盘点表清洗助手 — Stocktake Excel Cleaner

Clean messy physical inventory count sheets into a standard, reviewable stocktake template.

Core Boundary

This skill is offline and file-based.

  • Do not connect to any live warehouse system.
  • Do not read or write any production database.
  • Do not adjust stock, post write-offs, or settle differences (不执行平账).
  • Do not judge why a difference exists — only that it exists.
  • Only transform user-provided Excel files into reviewable outputs.

The Difference Formula

The workflow this serves: staff receive a sheet that already carries the book quantity per material, write down what they actually counted, and the difference classifies the line.

差异 = 实盘数量 − 账面数量
差异 > 0 → 盘盈    差异 < 0 → 盘亏    差异 = 0 → 相符

Always present the classification as a computed result, never as a conclusion. Whether a shortfall is theft, breakage, a mis-issue or a unit-conversion error is outside this tool's scope and must not be guessed.

Two Behaviours Specific to Counting

A counted quantity of 0 is valid data. It means the shelf was empty. Do not treat it as a missing value or filter it out — it is usually the single most important line on the sheet. The report lists these rows in their own section. Only an absent or non-numeric cell fails validation.

Book quantity missing → degrade, do not fail. If no book-quantity column is found, the script switches to "待比对模板" mode: counted quantities are cleaned and placed, 账面数量 / 差异数量 / 差异类型 are left blank, and the report says so at the top. Never invent a book quantity.

Header Block Backfill

Batch-level facts (盘点批次, 库房, 盘点人, 参与盘点人, 盘点日期) usually sit above the detail table. The script extracts them, backfills onto every detail row, and records the source row of each value. Show the user those sources — a wrong backfill contaminates every row. A detail column of the same name wins over the header block.

Wrong-Skill Hint

This skill does not detect what kind of list the file is. The user already chose this skill; that choice is the intent. The only exception: if every detail row fails validation, the report adds a "可能选错了 Skill" notice naming the sibling scenario the sheet resembles. That is a hint — never a trigger to switch templates or re-process.

Totals Rows

Rows whose first filled cell is 合计 / 总计 / 小计 / 共计 / 累计 / 总额 / total / sum are skipped and counted separately, not reported as invalid.

What the Warehouse Keeper Actually Needs

Cleaning is not the deliverable — acting on the differences is. Three things follow from that, and all three are produced automatically:

  • 差异明细 worksheet, differences only, sorted by absolute magnitude. On a 300-row sheet the biggest problem must be row 2, not buried in the middle.
  • Both totals. 净差异 nets surpluses against shortfalls; 差异绝对值合计 does not. A +100 and a −100 net to zero and look like a clean count while two lines are wrong. Always report both, and say which one measures the size of the problem.
  • 盘点覆盖率. Difference figures computed over 72% of the sheet describe 72% of the warehouse. State the ratio next to the figures, not buried in the error count.

The tool does not compute the money value of a difference — stocktake sheets rarely carry unit prices. Say so rather than leaving the user wondering.

Standard Output Columns

Use these columns in this order:

盘点批次, 库房, 盘点人, 参与盘点人, 盘点日期, 物资编码, 物资名称, 规格型号, 单位, 账面数量, 实盘数量, 差异数量, 差异类型, 备注

Required per detail row: 物资名称, 实盘数量.

差异类型 takes 盘盈 / 盘亏 / 相符, or 未比对 when the book quantity is missing — a blank there would read as 相符 and hide the fact that nothing was compared.

User-Supplied Difference Column

If the sheet already has a difference column, verify it against the formula rather than silently overwriting. On mismatch, write the recomputed value, and warn with both operands spelled out — "表中差异 -30 与 实盘 280 − 账面 300 = -20 不符". A message stating only the result invites the reading agent to invent operands.

Script

python3 scripts/clean_stocktake_excel.py input.xlsx --out-dir outputs

Expected outputs:

  • cleaned_stocktake_template.xlsx
  • stocktake_report.md
  • errors.csv

References

Read references/field-mapping.md for the full alias table, header-block detection rules, validation rules and reminder triggers.