代码收藏家 【高效办公】Python高效率办公的10个Excel使用场景拿来即用含详细解释
基础数据处理
1.销售数据合并
使用场景：将多个Excel文件中的销售数据合并到一个文件中。
import pandas as pd
import os
# 合并文件夹内所有Excel文件
files = [f for f in os.listdir('sales_data') if f.endswith('.xlsx')]
dfs = [pd.read_excel(f'sales_data/{f}') for f in files]
combined = pd.concat(dfs)
combined.to_excel('combined_sales.xlsx', index=False)
解释：
• os.listdir('sales_data')：列出指定文件夹中的所有文件。
• f.endswith('.xlsx')：筛选出文件扩展名为.xlsx的文件。
• pd.read_excel(f'sales_data/{f}')：逐个读取Excel文件为DataFrame。
• pd.concat(dfs)：将多个DataFrame合并为一个。
• combined.to_excel('combined_sales.xlsx', index=False)：将合并后的数据保存为新的Excel文件，不保存索引。
注意事项：
• 确保所有Excel文件的列结构一致，否则合并时会报错。
• 如果文件夹路径或文件名有误，会导致代码无法运行。
2.库存预警系统
使用场景：根据库存数据生成补货预警。
df = pd.read_excel('inventory.xlsx')
df['需要补货'] = df['当前库存'] < df['安全库存']
df.to_excel('inventory_with_alert.xlsx', index=False)
解释：
• pd.read_excel('inventory.xlsx')：读取库存数据。
• df['需要补货'] = df['当前库存'] < df['安全库存']：创建新列，判断当前库存是否低于安全库存。
• df.to_excel('inventory_with_alert.xlsx', index=False)：将结果保存到新的Excel文件。
注意事项：
• 确保当前库存和安全库存列名正确。
• 如果数据量较大，建议对结果进行进一步筛选或汇总。
3.员工考勤统计
使用场景：统计员工迟到次数。
attendance = pd.read_excel('attendance.xlsx')
# 计算迟到次数
late_count = attendance.groupby('员工ID')['是否迟到'].sum().reset_index()
late_count.to_excel('late_summary.xlsx', index=False)
解释：
• pd.read_excel('attendance.xlsx')：读取考勤数据。
• attendance.groupby('员工ID')['是否迟到'].sum()：按员工ID分组，统计每个员工的迟到次数。
• reset_index()：将分组结果转换为DataFrame。
• late_count.to_excel('late_summary.xlsx', index=False)：将结果保存到新的Excel文件。
注意事项：
• 确保员工ID和是否迟到列名正确。
• 如果是否迟到列是布尔值，可以直接统计；如果是其他格式，需要先转换。
数据清洗类
4.电话号码格式标准化
使用场景：将电话号码格式化为统一的格式。
def format_phone(phone):
return f"{phone[:3]}-{phone[3:7]}-{phone[7:]}"
df = pd.read_excel('contacts.xlsx')
df['联系电话'] = df['联系电话'].astype(str).apply(format_phone)
解释：
• pd.read_excel('contacts.xlsx')：读取联系人数据。
• df['联系电话'].astype(str)：将电话号码列转换为字符串格式。
• apply(format_phone)：对每一行的电话号码应用格式化函数。
注意事项：
• 确保电话号码的位数正确，否则格式化会出错。
• 如果电话号码中包含非数字字符，需要先进行清洗。
5.去除重复订单
使用场景：删除重复的订单记录。
orders = pd.read_excel('orders.xlsx')
clean_orders = orders.drop_duplicates(subset=['订单号'], keep='last')
解释：
• pd.read_excel('orders.xlsx')：读取订单数据。
• drop_duplicates(subset=['订单号'], keep='last')：删除重复的订单号，保留最后一条记录。
注意事项：
• 如果需要保留第一条重复记录，可以将keep='last'改为keep='first'。
• 确保订单号列名正确。
6.缺失值处理
使用场景：处理数据中的缺失值。
sales = pd.read_excel('sales.xlsx')
sales['销售额'] = sales['销售额'].fillna(0)
sales['区域'] = sales['区域'].ffill() # 前向填充
解释：
• pd.read_excel('sales.xlsx')：读取销售数据。
• sales['销售额'].fillna(0)：将销售额列中的缺失值填充为0。
• sales['区域'].ffill()：将区域列中的缺失值用前一行的值填充。
注意事项：
• 根据实际业务需求选择合适的填充方式，如fillna()、bfill()、ffill()等。
• 确保列名正确。
高级分析类
7.销售趋势分析
使用场景：分析销售数据的趋势。
import matplotlib.pyplot as plt
df = pd.read_excel('monthly_sales.xlsx')
pivot = df.pivot_table(index='月份', values='销售额', aggfunc='sum')
pivot.plot(kind='line')
plt.savefig('sales_trend.png')
解释：
• pd.read_excel('monthly_sales.xlsx')：读取月度销售数据。
• pivot_table(index='月份', values='销售额', aggfunc='sum')：创建数据透视表，按月份汇总销售额。
• pivot.plot(kind='line')：绘制折线图。
• plt.savefig('sales_trend.png')：保存图表为图片。
注意事项：
• 确保月份和销售额列名正确。
• 如果数据量较大，可以使用resample()方法进行时间序列分析。
8.客户分群（RFM分析）
使用场景：对客户进行RFM分析。
from datetime import datetime
rfm = df.groupby('客户ID').agg({
'订单日期': lambda x: (datetime.now() - x.max()).days,
'订单号': 'count',
'销售额': 'sum'
})
rfm.columns = ['Recency', 'Frequency', 'Monetary']
解释：
• groupby('客户ID')：按客户ID分组。
• agg()：对每个分组应用聚合函数。
• Recency：计算最近一次购买的时间差（天数）。
• Frequency：计算购买次数。
• Monetary：计算总销售额。
• rfm.columns = ['Recency', 'Frequency', 'Monetary']：重命名列。
注意事项：
• 确保客户ID、订单日期、订单号和销售额列名正确。
• 如果订单日期是字符串格式，需要先转换为datetime类型。
9.自动生成数据透视表
使用场景：自动生成销售数据透视表。
pivot = pd.pivot_table(df,
index='区域',
columns='产品类别',
values='销售额',
aggfunc='sum')
pivot.to_excel('sales_pivot.xlsx')
解释：
• pd.pivot_table()：创建数据透视表。
• index='区域'：按区域分组。
• columns='产品类别'：按产品类别分列。
• values='销售额'：汇总销售额。
• aggfunc='sum'：使用求和函数。
• pivot.to_excel('sales_pivot.xlsx')：将数据透视表保存为Excel文件。
注意事项：
• 确保区域、产品类别和销售额列名正确。
• 如果需要其他聚合函数（如mean、max等），可以修改aggfunc参数。
报表自动化
10.自动邮件周报
使用场景：自动发送包含周报的邮件。
import smtplib
from email.mime.multipart import MIMEMultipart
# 生成报表后发送
msg = MIMEMultipart()
msg['Subject'] = '每周销售报告'
msg.attach(open('weekly_report.xlsx', 'rb').read())
server = smtplib.SMTP('smtp.company.com')
server.sendmail(from_addr, to_addrs, msg.as_string())
解释：
• MIMEMultipart()：创建多部分邮件对象。
• msg['Subject']：设置邮件主题。
• msg.attach()：附加Excel文件。
• smtplib.SMTP()：连接到SMTP
—
让转型不迷航——邹工转型手札
作者：邹工转型手札
