前言
一、python对excel的基础使用操作
1.1 读取excel文件
1.2 写入学生数据信息
二、自动生成录取通知书
2.1生成十万条假数据
2.2制作录取通知书
三、员工工资信息管理操作
3.1 制作员工的工资信息
3.2 员工信息管理gui
四、自动化财务数据分析预测
4.1制作数据
参考文献
前言
python自动化办公将主要研究如何通过python代码读取excel文件的形式实现在实际生活中应用，本文属于分享博文，涉及深度比较浅。
首先安装包
pip install pandas faker openpyxl -i https://mirrors.tuna.tsinghua.edu.cn/pypi/web/simple一、python对excel的基础使用操作
1.1 读取excel文件
import pandas as pd
# 读取 Excel 文件中的第一个工作表
df = pd.read_excel('一万条学生成绩数据.xlsx')
# 显示前几行数据
print(df.head())1.2 写入学生数据信息
import pandas as pd
# Step 1: 读取 Excel 文件
# 假设有一个文件 'example.xlsx'，我们读取它
df = pd.read_excel('一万条学生成绩数据.xlsx')
# 打印原始数据以供参考
print("Original Data:")
print(df)
# Step 2: 添加一行新数据
# 创建一个新行，列名需要与现有的列名保持一致
new_row = {'Column1': 'New Data 1', 'Column2': 'New Data 2', 'Column3': 'New Data 3'}
# 将新行转换为 DataFrame 格式
new_row_df = pd.DataFrame([new_row])
# 使用 pd.concat 方法将新行添加到原始数据框中
df = pd.concat([df, new_row_df], ignore_index=True)
# 打印添加新行后的数据
print("\nData After Adding New Row:")
print(df)# Step 3: 将包含新行的数据写入到新的 Excel 文件中
# 将数据框写入新的 Excel 文件
df.to_excel('example_with_new_row.xlsx', index=False)print("\nNew Excel file 'example_with_new_row.xlsx' saved with the new row.")
二、自动生成录取通知书
2.1生成十万条假数据
import random
import pandas as pd
from faker import Faker
# 初始化 Faker
fake = Faker('zh_CN') # 使用中文
# 定义一些大学和专业
universities = ['清华大学', '北京大学', '浙江大学', '复旦大学', '上海交通大学','中国科学技术大学', '南京大学', '武汉大学', '华中科技大学', '中山大学'
]
majors = ['计算机科学与技术', '电子信息工程', '机械工程', '金融学', '临床医学','法学', '建筑学', '化学工程与工艺', '生物工程', '国际经济与贸易'
]
# 生成成绩的函数
def generate_score(min_score=50, max_score=100):return random.randint(min_score, max_score)# 生成祝愿语的函数
def generate_wish_message(university, principal_name):return f"祝贺您被 {university} 录取，愿您在校长 {principal_name} 的带领下，在大学的学习和生活中一帆风顺，前程似锦！"
# 生成假数据
def generate_data(num_records):data = []for i in range(num_records):student_name = fake.name() # 生成中文姓名exam_id = f"EX{i+1:06d}" # 考号，格式化为 6 位数，如 EX000001chinese_score = generate_score()math_score = generate_score()english_score = generate_score()science_score = generate_score()# 总分 = 语文 + 数学 + 英语 + 科学total_score = chinese_score + math_score + english_score + science_score# 随机选择一个大学和专业university = random.choice(universities)major = random.choice(majors)# 生成校长姓名principal_name = fake.name()# 生成祝愿语wish_message = generate_wish_message(university, principal_name)data.append({'姓名': student_name,'考号': exam_id,'语文成绩': chinese_score,'数学成绩': math_score,'英语成绩': english_score,'科学成绩': science_score,'总分': total_score,'录取大学': university,'录取专业': major,'校长姓名': principal_name,'祝愿语': wish_message})return data
# 生成 10 万条数据
num_records = 100000
data = generate_data(num_records)
# 转换为 DataFrame
df = pd.DataFrame(data)
# 写入 Excel 文件
df.to_excel('十万条学生成绩数据_带录取信息.xlsx', index=False)
print(f"已成功生成 {num_records} 条学生成绩数据（包含录取信息和祝愿语），并写入到 Excel 文件中。")
2.2制作录取通知书
安装包
pip install pandas python-docx -i https://mirrors.tuna.tsinghua.edu.cn/pypi/web/simpleimport pandas as pd
from docx import Document
from docx.shared import Pt
from docx.enum.text import WD_ALIGN_PARAGRAPH
# 读取Excel文件
df = pd.read_excel('十万条学生成绩数据_带录取信息.xlsx')
# 创建一个Word文档对象
doc = Document()
# 设置录取通知书模板
def create_admission_letter(doc, name, exam_id, university, major, principal, wish_message):# 添加标题title = doc.add_heading(f'{university} 录取通知书', level=1)title.alignment = WD_ALIGN_PARAGRAPH.CENTER# 添加学生姓名和考号doc.add_paragraph(f"尊敬的 {name} 同学：")doc.add_paragraph(f"考号：{exam_id}")# 添加录取信息doc.add_paragraph(f"恭喜您被 {university} {major} 专业录取！")# 添加校长祝福语doc.add_paragraph(f"校长：{principal}")doc.add_paragraph(wish_message)# 添加分隔符，每个录取通知书一页doc.add_page_break()# 遍历每位学生的记录，生成录取通知书
for idx, row in df.iterrows():create_admission_letter(doc,row['姓名'],row['考号'],row['录取大学'],row['录取专业'],row['校长姓名'],row['祝愿语'])
# 保存Word文档
doc.save('录取通知书合集.docx')
print("所有学生的录取通知书已生成！")
三、员工工资信息管理操作
3.1 制作员工的工资信息
import random
import pandas as pd
from faker import Faker
# 初始化 Faker
fake = Faker('zh_CN') # 使用中文
# 定义假数据生成函数
def generate_salary_data(num_employees, year):data = []months = [f"{year}-{str(month).zfill(2)}" for month in range(1, 13)] # 生成每个月份，如 2024-01, 2024-02employee_ids = [f"EMP{str(i).zfill(4)}" for i in range(1, num_employees + 1)] # 工号，如 EMP0001, EMP0002for employee_id in employee_ids:employee_name = fake.name() # 生成中文姓名for month in months:salary = random.randint(5000, 15000) # 假设工资在 5000 到 15000 之间bonus = random.randint(1000, 5000) # 假设绩效在 1000 到 5000 之间total_payment = salary + bonus # 实际发放为工资加绩效data.append({'姓名': employee_name,'工号': employee_id,'月份': month,'工资': salary,'绩效': bonus,'实际发放': total_payment})return data
# 生成 100 名员工的 2024 年工资信息
num_employees = 100
year = 2024
salary_data = generate_salary_data(num_employees, year)
# 转换为 DataFrame
df = pd.DataFrame(salary_data)
# 写入 Excel 文件
df.to_excel('员工2024年工资信息.xlsx', index=False)
print(f"已成功生成 {num_employees * 12} 条员工工资信息，并写入到 Excel 文件中。")
3.2 员工信息管理gui
import pandas as pd
import tkinter as tk
from tkinter import ttk
from tkinter import messagebox
import matplotlib.pyplot as plt
import matplotlib
matplotlib.use('TkAgg') # 设置后端为交互式的 TkAgg
plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False # 解决负号 '-' 显示为方块的问题# 读取 Excel 数据
file_path = '员工2024年工资信息.xlsx'
df = pd.read_excel(file_path)# 创建主窗口
root = tk.Tk()
root.title("员工工资信息系统")# 创建标签和输入框
label1 = tk.Label(root, text="查看所有员工信息:")
label1.pack()# 创建表格以显示员工信息
tree = ttk.Treeview(root, columns=("姓名", "工号", "月份", "工资", "绩效", "实际发放"), show='headings')
tree.heading("姓名", text="姓名")
tree.heading("工号", text="工号")
tree.heading("月份", text="月份")
tree.heading("工资", text="工资")
tree.heading("绩效", text="绩效")
tree.heading("实际发放", text="实际发放")
tree.pack(fill=tk.BOTH, expand=True)# 插入所有员工信息
for index, row in df.iterrows():tree.insert("", tk.END, values=list(row))# 月份查询
def query_by_month():month = month_entry.get()if month:filtered_df = df[df['月份'] == month]if not filtered_df.empty:for item in tree.get_children():tree.delete(item)for index, row in filtered_df.iterrows():tree.insert("", tk.END, values=list(row))else:messagebox.showinfo("信息", "没有找到该月份的记录。")else:messagebox.showwarning("警告", "请输入月份。")# 按员工姓名查询
def query_by_name():name = name_entry.get()if name:filtered_df = df[df['姓名'] == name]if not filtered_df.empty:for item in tree.get_children():tree.delete(item)for index, row in filtered_df.iterrows():tree.insert("", tk.END, values=list(row))else:messagebox.showinfo("信息", "没有找到该员工的记录。")else:messagebox.showwarning("警告", "请输入员工姓名。")# 绘制图表
def draw_chart():name = name_entry.get()chart_type = chart_type_var.get() # 获取选择的图表类型if name:filtered_df = df[df['姓名'] == name]if not filtered_df.empty:if chart_type == "折线图":plt.figure(figsize=(10, 5))plt.plot(filtered_df['月份'], filtered_df['实际发放'], marker='o', label='实际发放')plt.title(f"{name} 的工资信息")plt.xlabel("月份")plt.ylabel("实际发放")plt.xticks(rotation=45)plt.grid()plt.legend()plt.show()elif chart_type == "柱状图":plt.figure(figsize=(10, 5))plt.bar(filtered_df['月份'], filtered_df['实际发放'], color='skyblue', label='实际发放')plt.title(f"{name} 的工资信息")plt.xlabel("月份")plt.ylabel("实际发放")plt.xticks(rotation=45)plt.grid()plt.legend()plt.show()elif chart_type == "饼状图":plt.figure(figsize=(8, 8))plt.pie(filtered_df['实际发放'], labels=filtered_df['月份'], autopct='%1.1f%%')plt.title(f"{name} 的工资信息")plt.show()else:messagebox.showinfo("信息", "没有找到该员工的记录。")else:messagebox.showwarning("警告", "请输入员工姓名。")# 月份查询输入框
month_label = tk.Label(root, text="输入月份（格式: 2024-01）：")
month_label.pack()
month_entry = tk.Entry(root)
month_entry.pack()month_button = tk.Button(root, text="查询该月份工资信息", command=query_by_month)
month_button.pack()# 姓名查询输入框
name_label = tk.Label(root, text="输入员工姓名：")
name_label.pack()
name_entry = tk.Entry(root)
name_entry.pack()name_button = tk.Button(root, text="查询该员工工资信息", command=query_by_name)
name_button.pack()# 选择图表类型
chart_type_var = tk.StringVar()
chart_type_label = tk.Label(root, text="选择图表类型：")
chart_type_label.pack()
chart_type_dropdown = ttk.Combobox(root, textvariable=chart_type_var)
chart_type_dropdown['values'] = ("折线图", "柱状图", "饼状图")
chart_type_dropdown.current(0) # 设置默认值
chart_type_dropdown.pack()chart_button = tk.Button(root, text="绘制图表", command=draw_chart)
chart_button.pack()# 启动主循环
root.mainloop()
四、自动化财务数据分析预测
自动化数据分析可以帮助公司了解业务趋势、优化成本和做出数据驱动的决策： BI工具：如 Power BI、Tableau 可与会计系统集成，生成可视化的财务分析报告。 Python数据分析：使用 pandas 结合 matplotlib、seaborn 对财务数据进行分析，发现趋势和异常。 机器学习预测：通过历史数据，使用机器学习模型进行财务预测和成本估算。 案例： 某企业使用 Power BI 将财务数据可视化，每月自动生成分析报告，管理层可以实时了解成本和利润情况，帮助制定预算。这个我们可以进一步探讨一下。
4.1制作数据
假数据应包含的字段
-
员工信息
-
姓名：员工的名字
-
工号：唯一标识员工的工号
-
部门：员工所在的部门（例如：销售部、技术部等）
-
职位：员工的职位（例如：经理、助理等）
-
工资调整记录：员工工资调整的历史记录，包括调薪日期和调整幅度
-
-
工资信息
-
年份：2024年
-
月份：1到12
-
基本工资：员工的基本工资
-
绩效工资：基于员工表现的绩效工资
-
实际发放工资：基本工资和绩效工资的总和
-
-
财务指标
-
公司收入：当月公司总收入
-
公司支出：当月公司总支出
-
利润：公司收入减去支出
-
成本：公司运营的成本
-
代码如下:
import pandas as pd
import random
from datetime import datetime, timedelta# 设置随机种子以便于重现
random.seed(42)# 员工数量和基本工资范围
num_employees = 100
base_salary_range = (3000, 10000) # 基本工资范围# 创建员工信息
names = [f'员工{i}' for i in range(1, num_employees + 1)]
employee_ids = [f'E{str(i).zfill(3)}' for i in range(1, num_employees + 1)]
departments = ['销售部', '技术部', '人事部', '财务部', '市场部']
positions = ['经理', '助理', '专员', '主管', '总监']# 生成工资信息
data = []
for employee_id, name in zip(employee_ids, names):for month in range(1, 13):# 随机生成基本工资和绩效工资basic_salary = random.randint(*base_salary_range)performance_bonus = random.randint(0, 2000) # 绩效工资范围actual_salary = basic_salary + performance_bonus# 财务指标company_income = random.randint(50000, 200000) # 公司收入范围company_expense = random.randint(30000, 150000) # 公司支出范围profit = company_income - company_expensecost = random.randint(10000, 50000) # 成本范围# 随机生成部门和职位department = random.choice(departments)position = random.choice(positions)# 添加调薪记录if month == 6: # 假设在6月进行调薪adjustment_date = datetime(2024, 6, 15).date()adjustment_amount = random.randint(500, 2000) # 调薪幅度adjustment_record = f"调薪日期: {adjustment_date}, 调整幅度: {adjustment_amount}"else:adjustment_record = None# 收集数据data.append({'姓名': name,'工号': employee_id,'年份': 2024,'月份': month,'基本工资': basic_salary,'绩效工资': performance_bonus,'实际发放工资': actual_salary,'公司收入': company_income,'公司支出': company_expense,'利润': profit,'成本': cost,'部门': department,'职位': position,'工资调整记录': adjustment_record})# 创建 DataFrame
df = pd.DataFrame(data)# 将数据写入 Excel 文件
df.to_excel('2024年员工工资和财务指标数据.xlsx', index=False)print("假数据生成完成，已保存到 '2024年员工工资和财务指标数据.xlsx'。")
为了从会计的角度进行财务数据分析，包括生成每月的财务报表和风险预测，我们可以遵循以下步骤：
1. 数据准备与清洗
确保数据质量是财务分析的第一步。通过以下步骤准备和清洗数据：
-
检查缺失值和异常值：识别并处理缺失值或极端值。
-
格式化日期：确保日期字段的格式一致，方便进行时间序列分析。
2. 生成每月财务报表
每月财务报表通常包括收入、支出、利润等关键指标。以下是生成财务报表的步骤：
-
按月份聚合数据：使用
pandas对数据进行聚合，计算每月的总收入、总支出和总利润。 -
创建财务报表：将聚合结果存储到新的 DataFrame 或 Excel 文件中。
以下是示例代码：
import pandas as pd# 读取生成的数据
df = pd.read_excel('2024年员工工资和财务指标数据.xlsx')# 按月份聚合数据
monthly_report = df.groupby(['年份', '月份']).agg(总收入=('公司收入', 'sum'),总支出=('公司支出', 'sum'),总利润=('利润', 'sum'),总成本=('成本', 'sum')
).reset_index()# 将财务报表保存到 Excel
monthly_report.to_excel('2024年每月财务报表.xlsx', index=False)print("每月财务报表生成完成，已保存到 '2024年每月财务报表.xlsx'。")
3. 财务指标分析
根据生成的每月财务报表，可以进行以下分析：
-
收入与支出趋势分析：绘制时间序列图，观察收入和支出的变化趋势。
-
利润分析：计算每月的利润率（利润/收入），并绘制利润率变化趋势图。
以下是示例代码，展示如何进行趋势分析：
import matplotlib.pyplot as plt# 绘制收入、支出和利润的趋势图
plt.figure(figsize=(12, 6))
plt.plot(monthly_report['月份'], monthly_report['总收入'], label='总收入', marker='o')
plt.plot(monthly_report['月份'], monthly_report['总支出'], label='总支出', marker='o')
plt.plot(monthly_report['月份'], monthly_report['总利润'], label='总利润', marker='o')
plt.title('2024年每月财务指标趋势')
plt.xlabel('月份')
plt.ylabel('金额')
plt.xticks(monthly_report['月份'])
plt.legend()
plt.grid()
plt.savefig('2024年每月财务指标趋势.png') # 保存图像
plt.show()
4. 风险预测分析
风险预测可以通过多种方式进行，以下是一些常用方法：
-
时间序列分析：使用时间序列模型（如 ARIMA）预测未来的收入和支出。
-
机器学习模型：使用历史数据构建模型，预测未来的财务指标，识别潜在的风险。
以下是使用 statsmodels 库进行简单时间序列预测的示例代码：
from statsmodels.tsa.arima.model import ARIMA# 设置月份为索引
monthly_report.set_index(['年份', '月份'], inplace=True)# 使用 ARIMA 模型预测下一月的收入
model = ARIMA(monthly_report['总收入'], order=(1, 1, 1))
model_fit = model.fit()
forecast = model_fit.forecast(steps=1) # 预测下一个月的收入print(f"预计下个月的总收入为：{forecast[0]:.2f}")
总体代码展示
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib
from statsmodels.tsa.arima.model import ARIMAmatplotlib.use('TkAgg') # 设置后端为交互式的 TkAgg
plt.rcParams['font.sans-serif'] = ['SimHei'] # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False # 解决负号 '-' 显示为方块的问题# 读取生成的数据
df = pd.read_excel('2024年员工工资和财务指标数据.xlsx')# 按月份聚合数据
monthly_report = df.groupby(['年份', '月份']).agg(总收入=('公司收入', 'sum'),总支出=('公司支出', 'sum'),总利润=('利润', 'sum'),总成本=('成本', 'sum')
).reset_index()# 将财务报表保存到 Excel
monthly_report.to_excel('2024年每月财务报表.xlsx', index=False)print("每月财务报表生成完成，已保存到 '2024年每月财务报表.xlsx'。")# 绘制收入、支出和利润的趋势图
plt.figure(figsize=(12, 6))
plt.plot(monthly_report['月份'], monthly_report['总收入'], label='总收入', marker='o')
plt.plot(monthly_report['月份'], monthly_report['总支出'], label='总支出', marker='o')
plt.plot(monthly_report['月份'], monthly_report['总利润'], label='总利润', marker='o')
plt.title('2024年每月财务指标趋势')
plt.xlabel('月份')
plt.ylabel('金额')
plt.xticks(monthly_report['月份'])
plt.legend()
plt.grid()
plt.savefig('2024年每月财务指标趋势.png') # 保存图像
plt.show()# 设置月份为索引
monthly_report.set_index(['年份', '月份'], inplace=True)# 使用 ARIMA 模型预测下一月的收入
model = ARIMA(monthly_report['总收入'], order=(1, 1, 1))
model_fit = model.fit()# 预测下一个月的收入
forecast = model_fit.forecast(steps=1)# 输出预测结果
if isinstance(forecast, pd.Series):print(f"预计下个月的总收入为：{forecast.values[0]:.2f}") # 访问预测值
else:print(f"预计下个月的总收入为：{forecast[0]:.2f}") # 对于其他类型