基于Python+pandas实现Excel数据统计分析自动化完整指南_Python

在很多办公自动化场景里，excel 是数据流转的入口：销售明细、考勤记录、库存台账、项目工时、财务流水，通常都会先以 .xlsx 文件的形式出现。如果数据量不大，手工筛选和透视表可以解决问题；但当文件每天都要处理、规则经常重复、结果还要导出给同事时，用 python + pandas 自动化处理会更稳定，也更容易沉淀成可复用脚本。

本文围绕一个常见的“销售订单 excel 统计分析”案例，系统演示 pandas 的 dataframe 基本概念、读取 excel、数据筛选、数据排序、分组统计、缺失值处理，以及导出新的 excel 文件。

1. pandas 库简介

pandas 是 python 数据分析领域最常用的第三方库之一，适合处理结构化数据，例如 excel、csv、数据库查询结果、日志表格等。它的优势主要有三点：

表格数据处理能力强：可以像操作 excel 表一样操作行、列、筛选条件和统计字段。
api 简洁：读取 excel、分组统计、排序、缺失值处理通常只需要几行代码。
生态成熟：可以和 openpyxl、xlsxwriter、matplotlib、sqlalchemy 等库配合，完成报表生成、图表绘制、数据库读写等工作。

安装 pandas 和 excel 读取引擎：

pip install pandas openpyxl

其中：

pandas 负责数据处理。
openpyxl 负责读取和写入 .xlsx 文件。

2. dataframe 基本概念

pandas 中最核心的数据结构是 dataframe。可以把它理解为 python 里的“二维表格”：

每一行代表一条记录，例如一笔订单。
每一列代表一个字段，例如订单编号、地区、销售额。
行索引用来定位记录。
列名用来访问字段。

例如下面这张 excel 表：

订单编号	日期	地区	产品	销售员	销售额	成本	状态
so2026001	2026-04-01	华东	笔记本电脑	张三	8500	6500	已完成
so2026002	2026-04-02	华南	显示器	李四	1800	1200	已完成
so2026003	2026-04-03	华北	打印机	王五	2600	1900	退款

读入 pandas 后，就是一个 dataframe 对象。我们可以用代码完成筛选、计算利润、统计各地区销售额等操作。

3. 读取 excel 文件

读取 excel 最常用的是 pd.read_excel()：

import pandas as pd

df = pd.read_excel("sales_orders.xlsx", sheet_name="订单明细")

print(df.head())
print(df.info())

常用参数说明：

io：excel 文件路径。
sheet_name：工作表名称或索引，默认读取第一个工作表。
usecols：指定读取哪些列，例如 "a:h" 或 ["订单编号", "地区", "销售额"]。
dtype：指定字段类型，常用于订单编号、手机号等不能被当成数字处理的字段。
parse_dates：指定日期列自动转成日期类型。

示例：

df = pd.read_excel(
    "sales_orders.xlsx",
    sheet_name="订单明细",
    dtype={"订单编号": str},
    parse_dates=["日期"]
)

4. 数据筛选

数据筛选是办公自动化中最常见的操作。pandas 使用布尔条件筛选行。

4.1 筛选已完成订单

completed_df = df[df["状态"] == "已完成"]

4.2 筛选销售额大于 5000 的订单

large_orders = df[df["销售额"] > 5000]

4.3 多条件筛选

筛选“华东地区且已完成”的订单：

east_completed = df[(df["地区"] == "华东") & (df["状态"] == "已完成")]

筛选“销售额大于 5000 或产品为笔记本电脑”的订单：

important_orders = df[(df["销售额"] > 5000) | (df["产品"] == "笔记本电脑")]

注意：多个条件之间要使用 &、|，每个条件都要用括号包起来。

5. 数据排序

排序使用 sort_values()。

5.1 按销售额从高到低排序

df_sorted = df.sort_values(by="销售额", ascending=false)

5.2 按地区和销售额排序

先按地区升序，再按销售额降序：

df_sorted = df.sort_values(
    by=["地区", "销售额"],
    ascending=[true, false]
)

排序后如果希望重置行号：

df_sorted = df_sorted.reset_index(drop=true)

6. 分组统计

分组统计是 pandas 最适合替代 excel 透视表的能力之一，核心方法是 groupby()。

6.1 按地区统计销售额

region_summary = df.groupby("地区", as_index=false)["销售额"].sum()

结果类似：

地区	销售额
华东	35000
华南	28000
华北	19000

6.2 按地区统计销售额、成本和利润

先增加利润列：

df["利润"] = df["销售额"] - df["成本"]

再分组统计：

region_summary = df.groupby("地区", as_index=false).agg(
    订单数=("订单编号", "count"),
    销售额合计=("销售额", "sum"),
    成本合计=("成本", "sum"),
    利润合计=("利润", "sum")
)

6.3 按销售员统计业绩

seller_summary = df.groupby("销售员", as_index=false).agg(
    订单数=("订单编号", "count"),
    销售额合计=("销售额", "sum"),
    平均客单价=("销售额", "mean")
)

为了让结果更适合阅读，可以按销售额排序：

seller_summary = seller_summary.sort_values(
    by="销售额合计",
    ascending=false
).reset_index(drop=true)

7. 缺失值处理

真实业务数据很少完全干净。excel 中可能存在空单元格、空字符串、未填写状态、缺失成本等情况。

7.1 查看缺失值数量

print(df.isna().sum())

7.2 填充文本字段缺失值

例如销售员为空时填充为“未分配”：

df["销售员"] = df["销售员"].fillna("未分配")

7.3 填充数值字段缺失值

例如成本为空时填充为 0：

df["成本"] = df["成本"].fillna(0)

7.4 删除关键字段缺失的记录

如果订单编号或销售额缺失，这类数据通常不能参与统计，可以删除：

df = df.dropna(subset=["订单编号", "销售额"])

7.5 清理字符串空格

有些 excel 数据看起来一样，实际包含前后空格，会影响分组统计：

text_columns = ["地区", "产品", "销售员", "状态"]

for col in text_columns:
    df[col] = df[col].astype(str).str.strip()

8. 导出新的 excel 文件

导出单个工作表：

df.to_excel("cleaned_sales_orders.xlsx", index=false)

如果要把“清洗后的明细”“地区统计”“销售员统计”写入同一个 excel 文件，可以使用 excelwriter：

with pd.excelwriter("sales_analysis_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="清洗后明细", index=false)
    region_summary.to_excel(writer, sheet_name="地区统计", index=false)
    seller_summary.to_excel(writer, sheet_name="销售员统计", index=false)

这样生成的 excel 文件更适合作为日报、周报或月报附件。

9. 完整案例代码

下面给出一个完整脚本。它会：

读取销售订单 excel。
清理缺失值和文本空格。
过滤掉退款订单。
计算利润和利润率。
生成地区统计、销售员统计、产品统计。
导出一个新的多工作表 excel 分析报告。

from pathlib import path

import pandas as pd


input_file = path("sales_orders.xlsx")
output_file = path("sales_analysis_report.xlsx")


def load_sales_data(file_path: path) -> pd.dataframe:
    """读取销售订单 excel。"""
    df = pd.read_excel(
        file_path,
        sheet_name="订单明细",
        dtype={"订单编号": str},
        parse_dates=["日期"]
    )
    return df


def clean_sales_data(df: pd.dataframe) -> pd.dataframe:
    """清洗销售订单数据。"""
    df = df.copy()

    required_columns = ["订单编号", "日期", "地区", "产品", "销售员", "销售额", "成本", "状态"]
    missing_columns = [col for col in required_columns if col not in df.columns]
    if missing_columns:
        raise valueerror(f"excel 缺少必要字段: {missing_columns}")

    # 删除关键字段缺失的记录
    df = df.dropna(subset=["订单编号", "销售额"])

    # 文本字段去除前后空格，并填充业务默认值
    text_columns = ["地区", "产品", "销售员", "状态"]
    for col in text_columns:
        df[col] = df[col].fillna("未填写").astype(str).str.strip()

    # 数值字段转成数字，异常值转为 nan 后再填充
    df["销售额"] = pd.to_numeric(df["销售额"], errors="coerce")
    df["成本"] = pd.to_numeric(df["成本"], errors="coerce")
    df = df.dropna(subset=["销售额"])
    df["成本"] = df["成本"].fillna(0)

    # 日期字段统一转换
    df["日期"] = pd.to_datetime(df["日期"], errors="coerce")

    # 只统计已完成订单，排除退款、取消等状态
    df = df[df["状态"] == "已完成"].copy()

    # 增加业务分析字段
    df["利润"] = df["销售额"] - df["成本"]
    df["利润率"] = df["利润"] / df["销售额"]

    return df.reset_index(drop=true)


def build_summary(df: pd.dataframe) -> tuple[pd.dataframe, pd.dataframe, pd.dataframe]:
    """生成地区、销售员和产品三个维度的统计表。"""
    region_summary = df.groupby("地区", as_index=false).agg(
        订单数=("订单编号", "count"),
        销售额合计=("销售额", "sum"),
        成本合计=("成本", "sum"),
        利润合计=("利润", "sum"),
        平均订单金额=("销售额", "mean")
    )
    region_summary["利润率"] = region_summary["利润合计"] / region_summary["销售额合计"]
    region_summary = region_summary.sort_values("销售额合计", ascending=false)

    seller_summary = df.groupby("销售员", as_index=false).agg(
        订单数=("订单编号", "count"),
        销售额合计=("销售额", "sum"),
        利润合计=("利润", "sum"),
        平均客单价=("销售额", "mean")
    )
    seller_summary = seller_summary.sort_values("销售额合计", ascending=false)

    product_summary = df.groupby("产品", as_index=false).agg(
        订单数=("订单编号", "count"),
        销售额合计=("销售额", "sum"),
        利润合计=("利润", "sum")
    )
    product_summary = product_summary.sort_values("销售额合计", ascending=false)

    return (
        region_summary.reset_index(drop=true),
        seller_summary.reset_index(drop=true),
        product_summary.reset_index(drop=true)
    )


def export_report(
    detail_df: pd.dataframe,
    region_summary: pd.dataframe,
    seller_summary: pd.dataframe,
    product_summary: pd.dataframe,
    output_file: path
) -> none:
    """导出 excel 分析报告。"""
    with pd.excelwriter(output_file, engine="openpyxl") as writer:
        detail_df.to_excel(writer, sheet_name="清洗后明细", index=false)
        region_summary.to_excel(writer, sheet_name="地区统计", index=false)
        seller_summary.to_excel(writer, sheet_name="销售员统计", index=false)
        product_summary.to_excel(writer, sheet_name="产品统计", index=false)


def main() -> none:
    raw_df = load_sales_data(input_file)
    cleaned_df = clean_sales_data(raw_df)
    region_summary, seller_summary, product_summary = build_summary(cleaned_df)
    export_report(cleaned_df, region_summary, seller_summary, product_summary, output_file)

    print(f"分析完成，已生成文件: {output_file.resolve()}")


if __name__ == "__main__":
    main()

10. 工作中的实际应用场景

python + pandas 处理 excel 的价值，不只是“少点几下鼠标”，更重要的是把重复、易错、依赖人工经验的流程标准化。

10.1 销售日报和月报

销售部门每天导出订单明细后，可以自动生成：

各地区销售额排名。
各销售员业绩排名。
产品销量和利润统计。
异常订单清单，例如退款、负利润、高折扣订单。

脚本每天跑一次，就能把报表结果稳定输出到固定目录。

10.2 财务对账

财务经常需要比对订单系统、支付平台和发票系统的数据。pandas 可以用订单号、流水号等字段做合并和差异检查，自动找出：

系统有订单但支付平台无流水的数据。
支付成功但未开票的数据。
金额不一致的数据。
重复入账的数据。

10.3 人事考勤统计

考勤机导出的 excel 通常字段多、格式不统一。通过 pandas 可以清理日期、员工编号、部门名称，再统计：

迟到次数。
缺卡次数。
加班时长。
部门出勤率。

10.4 库存和采购分析

对于库存台账，可以用 pandas 统计：

各仓库库存数量。
低库存预警清单。
高周转和低周转商品。
采购金额和供应商占比。

10.5 批量数据质检

当 excel 是业务系统导入前的模板时，可以先用 pandas 做校验：

必填字段是否为空。
身份证号、手机号、邮箱格式是否正确。
金额字段是否为负数。
枚举字段是否超出允许范围。

发现问题后，把异常数据单独导出给业务人员修改，比导入系统后再报错更高效。

11. 实战建议

在真实项目中使用 pandas 处理 excel，可以遵循下面几条经验：

先明确输入字段和输出结果，不要一边写代码一边猜业务规则。
对关键字段做校验，例如订单编号、日期、金额、状态。
清洗数据时尽量保留原始文件，不要直接覆盖源 excel。
中间结果拆成多个函数，方便后续维护和复用。
导出报告时使用多个工作表，让明细和统计结果分开。

总结

pandas 非常适合 python 办公自动化方向的 excel 数据分析任务。它既能完成基础的读取、筛选、排序、缺失值处理，也能像 excel 透视表一样做分组统计，还可以把清洗后的明细和统计结果导出为新的 excel 报告。

当你的工作中出现“每天都要处理类似 excel”“人工筛选容易出错”“统计口径需要固定下来”这类需求时，就很适合用 pandas 写成自动化脚本。这样不仅能提高效率，也能让数据处理过程更透明、更可复用。

以上就是基于python+pandas实现excel数据统计分析自动化完整指南的详细内容，更多关于python excel数据统计分析自动化的资料请关注代码网其它相关文章！

基于Python+pandas实现Excel数据统计分析自动化完整指南

2026年05月09日 • Python •我要评论