Python实现档案继续教育费用自动核算实操

一、环境准备与依赖安装

在开始编写代码之前，需要确保你的系统中已经安装了Python环境。本指南基于Python 3.8版本开发，我们将使用pandas库进行高效的数据处理，使用openpyxl库处理Excel文件的读写操作。这两个库是Python数据处理领域的标准配置，稳定且功能强大。

打开终端或命令提示符，执行以下命令安装必要的依赖库。请确保网络连接通畅，以便从PyPI镜像源下载包。

pip install pandas openpyxl

安装完成后，可以通过以下命令验证是否安装成功：

python -c "import pandas, openpyxl; print('环境配置完成')"

二、项目目录结构设计

为了保持项目的整洁和可维护性，建议在本地创建一个专门的项目文件夹。本指南假设文件夹命名为archive_edu_calc。在项目运行过程中，程序会自动生成输入模板和输出结果文件，无需手动创建额外的子目录。

核心功能将封装在一个Python脚本中，我们将该脚本命名为calculate_fee.py。该脚本将包含数据生成、清洗、核算逻辑和结果导出的完整功能。

三、原始数据标准化处理

在实际业务中，档案继续教育费用的数据通常来源于Excel台账。为了确保本指南的实操性，我们首先编写一段代码，自动生成一份符合业务场景的模拟数据Excel文件。这样你无需准备外部数据即可直接运行代码查看效果。

以下代码将创建一个名为education_data.xlsx的文件，包含员工ID、姓名、培训日期、培训类型、发票金额等关键字段。

import pandas as pd
import random
from datetime import datetime, timedelta
def generate_mock_data(filename='education_data.xlsx'):
定义档案专业常见的培训类型
training_types = ['档案信息化建设', '电子档案管理规范', '档案保护技术', '档案法律法规', '非档案类培训']
data = []
模拟生成20条记录
for i in range(1, 21):
emp_id = f"EMP{1000 + i}"
name = f"员工{i}"
随机生成过去一年内的日期
date = datetime.now() - timedelta(days=random.randint(0, 365))
t_type = random.choice(training_types)
金额在200到1500之间随机
amount = random.randint(200, 1500)
data.append({
'员工ID': emp_id,
'姓名': name,
'培训日期': date.strftime('%Y-%m-%d'),
'培训类型': t_type,
'发票金额': amount
})
df = pd.DataFrame(data)
df.to_excel(filename, index=False)
print(f"模拟数据已生成：{filename}")
if __name__ == '__main__':
generate_mock_data()

将上述代码保存并运行，你将得到一份包含随机数据的Excel文件。请注意，其中包含了“非档案类培训”的数据，我们需要在核算过程中将其剔除。

四、核心核算逻辑代码实现

接下来是本指南的核心部分。我们将编写完整的核算逻辑。根据档案继续教育的相关规定，通常只有符合专业要求的培训费用才能报销，且每人每年有固定的费用上限（本例假设上限为3600元）。代码需要完成以下步骤：

1. 数据加载：读取Excel文件。

2. 数据清洗：转换日期格式，过滤掉非档案专业的培训记录。

3. 费用核算：按员工分组计算总费用，并判断是否超标。

4. 结果导出：生成包含原始数据、核算结果和审核状态的最终报表。

请创建calculate_fee.py文件并写入以下完整代码：

import pandas as pd
import os
def process_education_fees(input_file, output_file):
1. 数据加载
try:
df = pd.read_excel(input_file)
print("数据读取成功，开始处理...")
except FileNotFoundError:
print(f"错误：未找到文件 {input_file}")
return
2. 数据清洗与类型转换
将日期列转换为datetime类型，方便后续按年统计（如有需要）
df['培训日期'] = pd.to_datetime(df['培训日期'])
定义合规的档案培训类型白名单
valid_types = ['档案信息化建设', '电子档案管理规范', '档案保护技术', '档案法律法规']
过滤数据：只保留白名单内的培训类型
使用copy()避免SettingWithCopyWarning警告
df_clean = df[df['培训类型'].isin(valid_types)].copy()
添加一列标记是否合规
df['合规状态'] = df['培训类型'].apply(lambda x: '合规' if x in valid_types else '剔除：非档案专业')
3. 核心核算逻辑
按员工ID和姓名分组，对合规金额求和
注意：这里是对清洗后的df_clean进行聚合
summary_df = df_clean.groupby(['员工ID', '姓名']).agg(
总合规金额=('发票金额', 'sum'),
培训次数=('发票金额', 'count')
).reset_index()
定义年度报销上限
ANNUAL_LIMIT = 3600
计算超额情况
summary_df['报销上限'] = ANNUAL_LIMIT
summary_df['超额金额'] = summary_df['总合规金额'] - ANNUAL_LIMIT
summary_df['审核状态'] = summary_df['超额金额'].apply(lambda x: '通过' if x <= 0 else f'超支：{x}元')
4. 数据合并与导出
将核算结果合并回原始数据表，方便查看每笔明细对应的汇总情况
这里我们创建一个透视表或最终报告
final_report = summary_df[['员工ID', '姓名', '总合规金额', '报销上限', '审核状态']]
导出明细表（包含合规状态标记）
detail_output = "detail_" + output_file
with pd.ExcelWriter(output_file) as writer:
df.to_excel(writer, sheet_name='原始明细及合规标记', index=False)
final_report.to_excel(writer, sheet_name='费用核算汇总表', index=False)
print(f"处理完成！结果已保存至：{output_file}")
print(final_report)
执行函数
if __name__ == '__main__':
如果不存在模拟数据，先调用生成函数（此处假设数据已存在或手动生成）
if not os.path.exists('education_data.xlsx'):
为了代码完整性，这里简单嵌入生成逻辑，实际建议分开
from datetime import datetime, timedelta
import random
training_types = ['档案信息化建设', '电子档案管理规范', '档案保护技术', '档案法律法规', '非档案类培训']
data = []
for i in range(1, 21):
data.append({
'员工ID': f"EMP{1000 + i}", '姓名': f"员工{i}",
'培训日期': (datetime.now() - timedelta(days=random.randint(0, 365))).strftime('%Y-%m-%d'),
'培训类型': random.choice(training_types),
'发票金额': random.randint(200, 1500)
})
pd.DataFrame(data).to_excel('education_data.xlsx', index=False)
process_education_fees('education_data.xlsx', 'final_report.xlsx')

五、关键代码逻辑详解

上述代码中包含了几个关键的技术细节，直接决定了程序的健壮性，这里做进一步说明：

1. 白名单过滤机制：

代码使用了isin(valid_types)方法。这是Pandas中处理分类筛选最高效的方式之一。相比使用循环遍历每一行，向量化操作在处理成千上万条数据时速度优势明显。我们定义了valid_types列表，只有在这个列表中的“培训类型”才会被计入费用。

2. GroupBy聚合操作：

groupby(['员工ID', '姓名']).agg(...)是实现核算的核心。这里使用了聚合函数agg，可以在一次分组操作中同时计算“总和”和“计数”。这比分别计算两次汇总要节省资源，且代码逻辑更紧凑。

3. ExcelWriter多Sheet导出：

为了方便财务人员核对，我们不仅输出了汇总结果，还保留了带有“合规状态”标记的原始明细。使用pd.ExcelWriter可以将两个DataFrame写入同一个Excel文件的不同Sheet中，形成一个完整的分析报告包。

六、运行验证与结果分析

在终端中运行脚本：

python calculate_fee.py

运行成功后，目录下会生成final_report.xlsx文件。打开该文件，你会看到两个工作表：

1. 原始明细及合规标记：每一笔培训费后面都新增了一列“合规状态”。如果是“非档案类培训”，状态会显示为“剔除：非档案专业”，且该笔金额不会被计入下一张表的总额中。

2. 费用核算汇总表：这是按员工维度的汇总。你可以清晰地看到每位员工的“总合规金额”。如果金额超过3600元，“审核状态”列会明确显示“超支：XXX元”，否则显示“通过”。

通过这套流程，我们将原本需要人工逐条核对、计算Excel公式的繁琐工作，转化为自动化脚本执行。整个过程从数据准备到结果输出均在毫秒级完成，且消除了人工计算可能出现的公式错误或遗漏剔除项的风险。