VBA生鲜商品损耗自动核销宏，打破老会计手动折算生鲜亏损传统，录入折价比例代码，一键核销库存成本，动态算损耗，机器实时核算碾压隔日人工统算模式。
结合智能会计中的“实地盘存制”与“成本流转假设”，用 Python + VBA 混合双打（注：Python负责核心逻辑，VBA负责Excel交互，这是企业级落地的标准姿势），打造一套生鲜商品损耗自动核销宏。
一、 实际应用场景描述
场景：
某大型连锁超市的生鲜区，每天傍晚需要对当天剩余的蔬菜、水果进行折价处理（比如“买一送一”或“半价清仓”）。
到了晚上，会计拿到一张《生鲜变价申请表》和一张《盘点表》。
* 西红柿：进价3元，原价5元，现价2元，剩余50斤。
* 草莓：进价15元，原价25元，现价10元，剩余20盒。
老做法：
会计手动计算：
"(原价 - 现价) * 数量"，算出损耗金额，再去ERP里找到对应的库存科目，手工做凭证冲减成本。一旦商品种类超过50种，加班是必然的。
二、 引入痛点 (The Pain Points)
我们要推翻的“隔日人工统算”模式有以下硬伤：
1. 时效性极差：损耗是当天发生的，却要第二天甚至第三天才能入账，导致当日利润失真。
2. 人为计算误差：折价比例复杂（有的8折，有的5折，有的直接报损），Excel公式嵌套过多，极易出错。
3. 库存与财务脱节：手动改库存金额，容易导致库存数量为正数但金额为0的“幽灵库存”。
4. 审计风险：缺乏清晰的折价核销轨迹，审计问起“为什么这笔成本少了500块”，答不上来。
三、 核心逻辑讲解 (The Algorithm)
我们将采用“成本差异即时核销法”。
核心思想：
不以“售价”论英雄，而以“成本与可变现净值孰低”原则（会计谨慎性原则）进行存货计价。
计算公式：
1. 正常成本 (Original_Cost) = 进货单价 × 剩余数量
2. 折价后价值 (Markdown_Value) = 折价单价 × 剩余数量
3. 损耗金额 (Loss_Amount) =
"Original_Cost - Markdown_Value"
4. 核销分录：
* 借：主营业务成本—生鲜损耗
* 贷：库存商品—生鲜
逻辑流程：
1. 从Excel导入“当日生鲜盘点及变价表”。
2. 遍历每一行商品，提取
"Cost_Price",
"Markdown_Price",
"Quantity"。
3. 计算
"Loss_Amount"。
4. 生成核销凭证数据（或直接更新库存台账）。
四、 代码模块化实现 (Python + VBA Bridge)
由于生鲜会计重度依赖Excel，我们采用 Python 处理逻辑 + VBA 作为触发器 的方案。
1. 项目结构
Fresh_Inventory_Loss/
├── fresh_loss_core.py # Python核心逻辑
├── Run_Macro.xlsm # Excel文件 (含VBA按钮)
├── sample_data.csv # 待核销数据
└── README.md
2. Python核心代码
"fresh_loss_core.py"
"""
模块名称：生鲜损耗自动核销核心处理器
作者：全栈技术博主
功能：读取Excel数据，计算折价损耗，输出核销凭证
"""
import pandas as pd
import numpy as np
from datetime import date
class FreshInventoryLossCalculator:
"""
生鲜损耗计算器类
遵循存货成本与可变现净值孰低计量原则
"""
def __init__(self, config: dict = None):
"""
初始化配置
config: 包含会计科目映射等配置
"""
self.config = config or {
"loss_account": "6403.02", # 主营业务成本-生鲜损耗
"inventory_account": "1405" # 库存商品
}
print("✅ 生鲜损耗计算器已初始化")
def load_data(self, file_path: str) -> pd.DataFrame:
"""
加载生鲜盘点数据
参数:
file_path (str): CSV或Excel文件路径
返回:
pd.DataFrame: 包含商品信息的DataFrame
"""
try:
if file_path.endswith('.csv'):
df = pd.read_csv(file_path)
else:
df = pd.read_excel(file_path)
# 数据校验：确保关键列存在
required_cols = ['Item_Name', 'Cost_Price', 'Markdown_Price', 'Quantity']
for col in required_cols:
if col not in df.columns:
raise ValueError(f"缺少必要列: {col}")
print(f"✅ 成功加载 {len(df)} 条生鲜商品数据")
return df
except Exception as e:
raise IOError(f"数据加载失败: {e}")
def calculate_loss(self, df: pd.DataFrame) -> pd.DataFrame:
"""
核心计算逻辑：计算每一条商品的损耗金额
"""
# 防止负损耗（如果涨价了就不算损耗）
df['Unit_Loss'] = np.maximum(
0,
df['Cost_Price'] - df['Markdown_Price']
)
# 计算总损耗金额
df['Total_Loss_Amount'] = df['Unit_Loss'] * df['Quantity']
# 计算核销后的库存净值
df['Adjusted_Inventory_Value'] = df['Markdown_Price'] * df['Quantity']
print("🧮 损耗计算完成，已剔除负损耗项")
return df
def generate_voucher_entries(self, df: pd.DataFrame) -> pd.DataFrame:
"""
生成会计凭证分录
格式符合智能会计系统导入标准
"""
total_loss = df['Total_Loss_Amount'].sum()
voucher_data = [
{
"Date": date.today(),
"Account_Code": self.config["loss_account"],
"Debit": round(total_loss, 2),
"Credit": 0.00,
"Description": "生鲜商品折价损耗自动核销"
},
{
"Date": date.today(),
"Account_Code": self.config["inventory_account"],
"Debit": 0.00,
"Credit": round(total_loss, 2),
"Description": "生鲜商品折价损耗自动核销"
}
]
voucher_df = pd.DataFrame(voucher_data)
print(f"📝 凭证已生成，总核销金额: {total_loss:,.2f}")
return voucher_df
def run_full_process(self, file_path: str, output_path: str):
"""
运行完整流程
"""
raw_data = self.load_data(file_path)
calculated_data = self.calculate_loss(raw_data)
voucher = self.generate_voucher_entries(calculated_data)
# 保存结果到Excel的不同Sheet
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
raw_data.to_excel(writer, sheet_name='原始数据', index=False)
calculated_data.to_excel(writer, sheet_name='损耗计算明细', index=False)
voucher.to_excel(writer, sheet_name='会计凭证', index=False)
print(f"🎉 所有结果已导出至: {output_path}")
# 如果是直接运行此脚本
if __name__ == '__main__':
calculator = FreshInventoryLossCalculator()
calculator.run_full_process(
file_path='sample_data.csv',
output_path='Fresh_Loss_Report.xlsx'
)
3. VBA 宏代码 (放置在
"Run_Macro.xlsm" 中)
Sub Run_Fresh_Loss_Calculation()
'
' 宏功能：调用Python脚本执行生鲜损耗计算
' 前提：已安装Python，并已安装pandas, openpyxl库
'
Dim objShell As Object
Dim pythonExePath As String
Dim scriptPath As String
Dim dataFilePath As String
Dim outputFilePath As String
' --- 配置区域 ---
pythonExePath = """C:\Users\YourUser\Anaconda3\python.exe""" ' 修改为你的Python路径
scriptPath = """C:\Users\YourUser\Desktop\Fresh_Inventory_Loss\fresh_loss_core.py"""
dataFilePath = """C:\Users\YourUser\Desktop\Fresh_Inventory_Loss\sample_data.csv"""
outputFilePath = """C:\Users\YourUser\Desktop\Fresh_Inventory_Loss\Fresh_Loss_Report.xlsx"""
Set objShell = VBA.CreateObject("Wscript.Shell")
' 构建命令
Dim cmd As String
cmd = pythonExePath & " " & scriptPath & " --input " & dataFilePath & " --output " & outputFilePath
' 执行命令
MsgBox "正在启动Python进行损耗计算，请稍候...", vbInformation
objShell.Run cmd, 0, True
' 打开结果文件
Workbooks.Open Replace(outputFilePath, """", "")
MsgBox "✅ 生鲜损耗核销完成！请查看生成的Excel文件。", vbExclamation
End Sub
4. 示例数据
"sample_data.csv"
Item_Name,Cost_Price,Markdown_Price,Quantity
西红柿,3.00,2.00,50
草莓,15.00,10.00,20
进口香蕉,5.00,5.00,30
菠菜,2.50,1.00,40
五、 README 文件与使用说明
Fresh-Inventory-Loss-Automation
简介
本项目通过 Python 实现生鲜商品折价损耗的自动计算与核销凭证生成，并通过 VBA 宏实现 Excel 一键触发，解决零售业生鲜会计手工核算滞后的痛点。
环境准备
1. 安装 Python 3.8+
2. 安装依赖库：
"pip install pandas openpyxl"
3. 启用 Excel 宏功能（
".xlsm" 格式）
使用步骤
1. 在
"sample_data.csv" 中录入当日需核销的商品信息（成本价、变价、数量）。
2. 打开
"Run_Macro.xlsm" 文件。
3. 点击自定义功能区中的 “一键核销损耗” 按钮。
4. 等待程序运行结束，自动弹出生成的
"Fresh_Loss_Report.xlsx"。
六、 核心知识点卡片 (Knowledge Cards)
知识点 说明
成本与可变现净值孰低 (LCM) 会计谨慎性原则的核心体现，防止资产虚增。
VBA-Python 混合架构 兼顾 Excel 用户习惯与 Python 强大数据处理能力的最佳落地方案。
面向对象 (OOP) 使用
"Class" 封装计算器，便于后续增加“报损率预警”“多仓库合并”等功能。
会计分录自动化 将数据直接转化为标准凭证格式，实现“业财一体化”。
七、 总结
作为全栈工程师，我常说：“会计的尽头是代码，代码的尽头是逻辑。”
这套生鲜损耗自动核销系统的价值在于：
1. 实时性：从“隔日算”变成“当场算”，晚上8点变价，9点就能出凭证。
2. 准确性：机器不会累，不会因为看错小数点而把几千块的损耗算成几百块。
3. 合规性：每一笔损耗都有据可查，审计追踪链条完整。
利用AI解决实际问题，如果你觉得这个工具好用，欢迎关注长安牧笛！