第十八篇 数据清洗：Python智能筛选与统计：从海量Excel数据中秒级挖掘，辅助决策！你的数据分析利器！

Excel筛选复杂，统计耗时，无法快速挖掘数据价值

Excel筛选复杂，统计耗时，无法快速挖掘数据价值！

在职场中， 我们经常被海量Excel数据“淹没”？

想统计每个部门的平均工资、每个产品的总销量，却要在Excel中手动插入复杂公式，或者拖拽半天才能看到结果。

更恼人的是，辛辛苦苦整理出的数据，往往只能得到表面的数字，却无法快速从中看出“哪个产品最畅销？”、“哪个区域业绩下滑最严重？”这些真正有价值的数据洞察，从而难以辅助决策！

这种Excel筛选复杂、统计耗时的痛点，是许多职场数据分析的噩梦。

今天，我将带你进入Python数据分析的世界！我们将手把手教你如何利用数据处理的“瑞士军刀”——Pandas库，轻松实现：

Python筛选Excel： 精准定位你想要的数据。

数据分组统计： 轻松实现数据汇总，从宏观角度看清数据。

自动化生成透视表与交叉表： 高级统计报表，一键呈现复杂分析结果。

最终，你将拥有一个强大的Excel数据分析利器”，全面提升办公自动化能力。

1.数据筛选核心：df.loc与df.iloc，精准定位你想要的数据

在Python筛选Excel中，能够精确地从海量数据中提取出你需要的部分，是Python数据分析的第一步。

Pandas的DataFrame提供了多种强大的筛选方法，其中df.loc和df.iloc是最常用的手段。

功能：
df.loc：主要通过**标签（列名、行索引名）**进行筛选，也支持布尔条件。

df.iloc：主要通过**整数位置（行号、列号）**进行筛选。

布尔条件筛选： 最常用，根据数据的真/假值来选择行。

1.1基于条件筛选：过滤数据中的不恰当因素

场景： 你有一份销售订单表，只想看“销售额大于20000”的记录，或者“区域”是“东北”的订单。

方案： Pandas的布尔条件筛选能让你像在Excel中设置筛选器一样，通过简单的逻辑表达式，精准定位你想要的数据，过滤掉无关数据

代码：

import pandas as pd
import os

def filter_data_by_condition(file_path, output_path, condition_type="sales_amount"):
    """
    根据条件筛选Excel数据。
    这是Python筛选Excel和数据自动化处理的基础。
    :param file_path: 源Excel文件路径
    :param output_path: 筛选后Excel文件的输出路径
    :param condition_type: 筛选条件类型 ("sales_amount", "region", "multi_condition")
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在根据条件筛选文件 '{os.path.basename(file_path)}'...")
        print("   --- 原始数据头部 ---")
        print(df.head())

        df_filtered = None
        if condition_type == "sales_amount":
            # 筛选销售额大于5000的记录
            df_filtered = df[df['销售额'] > 20000]
            print("   ℹ️ 筛选条件：销售额 > 20000")
        elif condition_type == "region":
            # 筛选区域为“华东”的记录
            df_filtered = df[df['区域'] == '东北']
            print("   ℹ️ 筛选条件：区域 == '东北'")
        elif condition_type == "multi_condition":
            # 组合条件筛选：区域是“华南” 并且 销售额大于5000
            df_filtered = df[(df['区域'] == '华南') & (df['销售额'] >5000)]
            print("   ℹ️ 筛选条件：区域 == '华南' AND 销售额 > 8000")
        else:
            print("⚠️ 未知筛选条件类型。")
            return

        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        df_filtered.to_excel(output_path, index=False)
        
        print(f"✅ 数据筛选成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 筛选后数据头部 ---")
        print(df_filtered.head())
    except Exception as e:
        print(f"❌ 数据筛选失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data.xlsx")
    pd.DataFrame({'区域': ['华东', '华南', '华北', '华东', '华南'], 
                  '销售额': [6000, 9000, 4000, 12000, 7500], 
                  '产品': ['A', 'B', 'C', 'D', 'E']}).to_excel(test_excel_path, index=False)

    print("--- 示例1：筛选销售额大于5000 ---")
    filter_data_by_condition(test_excel_path, os.path.expanduser("~/Desktop/sales_gt_5000.xlsx"), "sales_amount")

    print("\n--- 示例2：筛选华东区域 ---")
    filter_data_by_condition(test_excel_path, os.path.expanduser("~/Desktop/sales_huadong.xlsx"), "region")

步骤：

准备Excel文件： 在桌面创建sales_data.xlsx，输入一些销售数据（包含区域、销售额等）。

修改代码路径和筛选条件： 修改 test_excel_path 等，并选择 condition_type。

运行： 运行 python filter_data.py。

展示：

1.2 多条件组合筛选：精确锁定目标数据

当你的筛选条件不止一个时，Pandas可以轻松通过逻辑运算符（&代表“且”，|代表“或”，~代表“非”）进行多条件组合筛选。

场景： 你需要找到华东南域所有销售额大于5000的产品，或者找出所有“耳机”或“背包”的销售记录。

代码：

import pandas as pd
import os

def multi_condition_filter_excel(file_path, output_path):
    """
    根据多个条件组合筛选Excel数据。
    这是Python筛选Excel的高级技巧，实现精准数据洞察。
    :param file_path: 源Excel文件路径
    :param output_path: 筛选后Excel文件的输出路径
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在根据多条件组合筛选文件 '{os.path.basename(file_path)}'...")
        
        # 示例：筛选“区域”是华南 AND “销售额”大5000 的记录
        condition_and = (df['区域'] == '华南') & (df['销售额'] > 5000)
        df_and_filtered = df[condition_and]
        print("\n   --- 筛选结果 (华南 AND 销售额 > 5000) ---")
        print(df_and_filtered)

        # 示例：筛选“区域”是华北 OR “产品”是C 的记录
        condition_or = (df['区域'] == '华北') | (df['产品'] == 'C')
        df_or_filtered = df[condition_or]
        print("\n   --- 筛选结果 (华北 OR 产品 == C) ---")
        print(df_or_filtered)

        # 最终保存第一个结果作为演示
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        df_and_filtered.to_excel(output_path, index=False)
        
        print(f"✅ 多条件筛选成功！结果保存到：'{os.path.basename(output_path)}'")
    except Exception as e:
        print(f"❌ 多条件筛选失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data.xlsx") # 使用1.1节创建的文件
    output_filtered_path = os.path.expanduser("~/Desktop/sales_multi_filtered.xlsx")
    
    multi_condition_filter_excel(test_excel_path, output_filtered_path)

展示：

1.3字符串匹配筛选：查找包含特定关键词的数据

场景： 你想找出所有产品名称中包含“智能”或“Pro”的订单，或者所有地址中包含“街道”的客户。

方案： Pandas的str.contains()方法可以进行模糊匹配，查找包含特定字符串的行。

代码：

import pandas as pd
import os

def string_match_filter_excel(file_path, output_path, column_name="产品", keyword="键"):
    """
    根据字符串内容（模糊匹配）筛选Excel数据。
    这是Python筛选Excel中处理文本数据的实用功能。
    :param file_path: 源Excel文件路径
    :param output_path: 筛选后Excel文件的输出路径
    :param column_name: 要进行字符串匹配的列名
    :param keyword: 要匹配的关键词
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在根据关键词 '{keyword}' 筛选列 '{column_name}' 中的数据...")

        # **核心操作：str.contains()进行字符串模糊匹配**
        # na=False 处理缺失值，case=False 忽略大小写
        df_filtered = df[df[column_name].str.contains(keyword, na=False, case=False)]
        
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        df_filtered.to_excel(output_path, index=False)
        
        print(f"✅ 字符串匹配筛选成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 筛选后数据头部 ---")
        print(df_filtered.head())
    except Exception as e:
        print(f"❌ 字符串匹配筛选失败：{e}")

if __name__ == "__main__":
    # 使用1.1节的 sales_data.xlsx
    test_excel_path = os.path.expanduser("~/Desktop/sales_data.xlsx") 
    output_filtered_path = os.path.expanduser("~/Desktop/sales_keyword_filtered.xlsx")

    # 示例调用：筛选产品名称中包含“键”的记录
    string_match_filter_excel(test_excel_path, output_filtered_path, column_name="产品", keyword="键")

展示：

2. 数据聚合与分组统计：数据汇总的关键groupby

场景： 你有海量订单数据，想知道每个产品类别的总销售额、每个区域的平均利润、或者每个员工的订单数量。手动筛选、复制、粘贴、求和，耗时耗力还容易错！

方案： Pandas的groupby()方法是数据分组统计的主要手段，它能让你像操作Excel数据透视表一样，但更灵活高效地进行数据汇总和聚合分析，实现Excel数据分析自动化！

实现：
groupby()操作分为三步：

Splitting (分组)： 根据一个或多个键将DataFrame拆分成多个组。

Applying (应用)： 对每个组独立应用一个函数（如求和、求平均）。

Combining (组合)： 将结果组合成一个DataFrame。

2.1 按单列分组统计：快速掌握分类数据概况

场景： 你想知道每个区域的总销售额是多少？哪个区域销售额最高？

方案： groupby()结合聚合函数（如sum()，mean()，count()）能快速帮你按单列进行数据汇总。

代码：

import pandas as pd
import os

def group_by_single_column(file_path, output_path, group_col="区域", agg_col="销售额", agg_func="sum"):
    """
    按单列进行数据分组统计。
    这是Python数据分析入门和数据分组统计的核心功能。
    :param file_path: 源Excel文件路径
    :param output_path: 统计结果Excel文件的输出路径
    :param group_col: 用于分组的列名
    :param agg_col: 要聚合的列名
    :param agg_func: 聚合函数 ("sum", "mean", "count", "min", "max")
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在按列 '{group_col}' 分组，并对列 '{agg_col}' 进行 '{agg_func}' 统计...")
        
        # **核心操作：groupby() + 聚合函数**
        if agg_func == "sum":
            grouped_data = df.groupby(group_col)[agg_col].sum()
        elif agg_func == "mean":
            grouped_data = df.groupby(group_col)[agg_col].mean()
        elif agg_func == "count":
            grouped_data = df.groupby(group_col)[agg_col].count()
        elif agg_func == "min":
            grouped_data = df.groupby(group_col)[agg_col].min()
        elif agg_func == "max":
            grouped_data = df.groupby(group_col)[agg_col].max()
        else:
            print("⚠️ 未知聚合函数。")
            return

        # 将Series转换为DataFrame以便保存到Excel
        grouped_df = grouped_data.reset_index() 
        
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        grouped_df.to_excel(output_path, index=False)
        
        print(f"✅ 分组统计成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 分组统计结果 ---")
        print(grouped_df)
    except Exception as e:
        print(f"❌ 分组统计失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data_full.xlsx")
    # 创建包含区域、产品、销售额、订单日期等列的复杂数据，以便进行分组统计
    pd.DataFrame({'区域': ['华东', '华南', '华北', '华东', '华南', '华东', '华北'], 
                  '销售额': [6000, 9000, 4000, 12000, 7500, 8000, 5000], 
                  '产品': ['A', 'B', 'C', 'D', 'E', 'A', 'F'],
                  '订单日期': pd.to_datetime(['2023-01-01', '2023-01-10', '2023-02-01', '2023-02-15', '2023-03-01', '2023-03-10', '2023-04-01'])}
                  ).to_excel(test_excel_path, index=False)

    print("--- 示例1：按区域统计总销售额 ---")
    group_by_single_column(test_excel_path, os.path.expanduser("~/Desktop/sales_by_region_sum.xlsx"), group_col="区域", agg_col="销售额", agg_func="sum")

    print("\n--- 示例2：按产品统计平均销售额 ---")
    group_by_single_column(test_excel_path, os.path.expanduser("~/Desktop/sales_by_product_mean.xlsx"), group_col="产品", agg_col="销售额", agg_func="mean")

步骤： 准备包含区域、销售额、产品等列的Excel文件，修改路径，运行脚本。

展示：

2.2按多列分组统计：多维度交叉分析数据

场景： 你不仅想知道每个区域的总销售额，还想细化到每个区域的每个产品的销售额。
方案： groupby()方法支持传入一个列名列表，实现多维度交叉分析，让你从更细致的角度挖掘洞察。
代码：

import pandas as pd
import os

def group_by_multiple_columns(file_path, output_path, group_cols, agg_col="销售额", agg_func="sum"):
    """
    按多列进行数据分组统计。
    这是Python数据分析中多维度交叉分析的关键。
    :param file_path: 源Excel文件路径
    :param output_path: 统计结果Excel文件的输出路径
    :param group_cols: 用于分组的列名列表 (e.g., ['区域', '产品'])
    :param agg_col: 要聚合的列名
    :param agg_func: 聚合函数 ("sum", "mean", "count")
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在按列 {group_cols} 分组，并对列 '{agg_col}' 进行 '{agg_func}' 统计...")
        
        # **核心操作：groupby() 传入列表 + 聚合函数**
        if agg_func == "sum":
            grouped_data = df.groupby(group_cols)[agg_col].sum()
        elif agg_func == "mean":
            grouped_data = df.groupby(group_cols)[agg_col].mean()
        # ... 其他聚合函数
        
        grouped_df = grouped_data.reset_index() 
        
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        grouped_df.to_excel(output_path, index=False)
        
        print(f"✅ 多列分组统计成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 多列分组统计结果 ---")
        print(grouped_df)
    except Exception as e:
        print(f"❌ 多列分组统计失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data_full.xlsx") # 使用2.1节创建的文件
    output_path = os.path.expanduser("~/Desktop/sales_by_region_product.xlsx")

    # 示例调用：按区域和产品统计总销售额
    group_by_multiple_columns(test_excel_path, output_path, group_cols=['区域', '产品'], agg_col="销售额", agg_func="sum")

展示：

2.3应用多种聚合函数：从不同角度解读数据

场景： 你不仅想知道每个区域的总销售额，还想同时知道销售额的平均值、最大值、最小值以及订单数量。

方案： groupby().agg()方法能让你一次性对多个列应用多种聚合函数，从不同角度全面解读数据，为职场决策提供更丰富的数据支持。

代码：

import pandas as pd
import os

def group_by_with_multiple_agg_funcs(file_path, output_path, group_col="区域"):
    """
    按单列分组，并应用多种聚合函数进行统计。
    这是Python数据分析中获取多维度洞察的强大功能。
    :param file_path: 源Excel文件路径
    :param output_path: 统计结果Excel文件的输出路径
    :param group_col: 用于分组的列名
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在按列 '{group_col}' 分组，并应用多种聚合函数...")
        
        # **核心操作：groupby() + agg() 方法**
        # 对 '销售额' 列进行求和、平均值、计数、最大值、最小值统计
        grouped_data = df.groupby(group_col).agg(
            总销售额=('销售额', 'sum'),
            平均销售额=('销售额', 'mean'),
            订单数量=('销售额', 'count'),
            最高销售额=('销售额', 'max'),
            最低销售额=('销售额', 'min')
        )
        
        grouped_df = grouped_data.reset_index() 
        
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        grouped_df.to_excel(output_path, index=False)
        
        print(f"✅ 多聚合函数分组统计成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 多聚合函数统计结果 ---")
        print(grouped_df)
    except Exception as e:
        print(f"❌ 多聚合函数分组统计失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data_full.xlsx") # 使用2.1节创建的文件
    output_path = os.path.expanduser("~/Desktop/sales_multi_agg.xlsx")

    # 示例调用：按区域统计多种销售指标
    group_by_with_multiple_agg_funcs(test_excel_path, output_path, group_col="区域")

展示:

3.高级统计：透视表与交叉表自动化生成

数据透视表和交叉表是Excel中非常强大的数据分析工具。它们能让你以多维度、动态的方式汇总数据，发现数据间的深层联系。而Python，可以帮你自动化生成这些复杂的自动化报表！

实现：

pivot_table()： 用于创建数据透视表，可以指定行、列、值和聚合函数。
crosstab()： 用于创建交叉表，统计两个或多个因素之间的频次分布。

3.1pivot_table：轻松生成复杂数据透视表

场景： 你需要生成一个报表，行是“区域”，列是“产品”，单元格是“销售额的总和”。这在Excel中需要拖拽字段，而在Python中，一行代码搞定！

方案： pd.pivot_table()方法能让你用代码精准构建复杂的数据透视表，实现Excel数据分析中的高级报表自动化。

代码：

import pandas as pd
import os

def generate_pivot_table(file_path, output_path, index_col, columns_col, values_col, agg_func="sum"):
    """
    生成Excel数据透视表。
    这是Python数据分析和自动化报表的强大功能。
    :param file_path: 源Excel文件路径
    :param output_path: 透视表Excel文件的输出路径
    :param index_col: 用作行索引的列名
    :param columns_col: 用作列索引的列名
    :param values_col: 用作聚合值的列名
    :param agg_func: 聚合函数 ("sum", "mean", "count")
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在生成透视表，行索引：'{index_col}'，列索引：'{columns_col}'，值：'{values_col}'，聚合函数：'{agg_func}'...")
        
        # **核心操作：pd.pivot_table()**
        pivot_df = pd.pivot_table(df, 
                                 index=index_col,      # 行索引
                                 columns=columns_col,  # 列索引
                                 values=values_col,    # 聚合值
                                 aggfunc=agg_func)     # 聚合函数
        
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        pivot_df.to_excel(output_path) # 保存透视表，index=True因为索引就是行标签
        
        print(f"✅ 数据透视表生成成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 透视表头部 ---")
        print(pivot_df.head())
    except Exception as e:
        print(f"❌ 数据透视表生成失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data_full.xlsx") # 使用2.1节创建的文件
    output_path = os.path.expanduser("~/Desktop/pivot_table_report.xlsx")

    # 示例调用：按区域和产品统计销售额
    generate_pivot_table(test_excel_path, output_path, index_col="区域", columns_col="产品", values_col="销售额", agg_func="sum")

展示：

3.2crosstab：自动化构建交叉分析表

场景： 你想快速统计不同区域的客户，他们购买了哪些产品，以及不同产品在不同区域的销售频次。

方案： pd.crosstab()方法能方便地构建交叉表（也叫列联表），统计两个或多个因素之间的频次分布，是快速进行数据洞察的工具。

代码：

import pandas as pd
import os

def generate_crosstab_report(file_path, output_path, index_col, columns_col, values_col=None, agg_func=None):
    """
    生成Excel交叉分析表。
    这是Python数据分析和自动化报表的另一种强大功能。
    :param file_path: 源Excel文件路径
    :param output_path: 交叉表Excel文件的输出路径
    :param index_col: 用作行索引的列名
    :param columns_col: 用作列索引的列名
    :param values_col: 可选，用于聚合的列名（如果需要计算非频次的值）
    :param agg_func: 可选，聚合函数
    """
    if not os.path.exists(file_path): return print(f"❌ Excel文件不存在：{file_path}")
    try:
        df = pd.read_excel(file_path)
        print(f"🚀 正在生成交叉表，行：'{index_col}'，列：'{columns_col}'...")
        
        # **核心操作：pd.crosstab()**
        # values和aggfunc可选，用于计算非频次的值
        if values_col and agg_func:
            crosstab_df = pd.crosstab(index=df[index_col], 
                                     columns=df[columns_col], 
                                     values=df[values_col], 
                                     aggfunc=agg_func)
        else:
            crosstab_df = pd.crosstab(index=df[index_col], columns=df[columns_col])
        
        os.makedirs(os.path.dirname(output_path), exist_ok=True)
        crosstab_df.to_excel(output_path)
        
        print(f"✅ 交叉表生成成功！结果保存到：'{os.path.basename(output_path)}'")
        print("\n   --- 交叉表头部 ---")
        print(crosstab_df.head())
    except Exception as e:
        print(f"❌ 交叉表生成失败：{e}")

if __name__ == "__main__":
    test_excel_path = os.path.expanduser("~/Desktop/sales_data_full.xlsx") # 使用2.1节创建的文件
    output_path = os.path.expanduser("~/Desktop/crosstab_report.xlsx")

    # 示例1：统计不同区域的产品销售频次
    generate_crosstab_report(test_excel_path, output_path, index_col="区域", columns_col="产品")

    # 示例2：统计不同区域的产品总销售额
    # generate_crosstab_report(test_excel_path, output_path, index_col="区域", columns_col="产品", values_col="销售额", agg_func="sum")

显示：

4.你的“Excel数据分析利器”！

我们深入学习了Pandas库在Excel数据分析中的应用，掌握了如何：

Python筛选Excel： 运用df.loc和df.iloc，实现基于条件、多条件组合、字符串匹配的精准数据筛选。

数据分组统计： 运用groupby()玩转数据汇总，按单列、多列分组，并应用多种聚合函数，从不同维度获取数据洞察。

自动化报表： 运用pivot_table()和crosstab()，自动化生成复杂透视表与交叉表，让数据呈现更专业、决策更精准。

5.尾声：数据分析自动化，让决策更精准！

通过本篇文章，你已经掌握了Python智能筛选与统计的强大能力，为你的办公自动化之旅又增添了一个重量级技能！你学会了如何利用Pandas这个Python实用工具，高效地进行Excel中的数据分析。

除了今天学到的筛选、分组、透视表功能，你还希望Python能帮你实现哪些Excel自动化操作？比如自动发送报表邮件？数据预警通知？在评论区分享你的需求和想法，你的建议可能会成为我们下一篇文章的灵感来源！

敬请期待！ 在下一篇文章中，我们将继续深入Excel的自动化世界，探索如何利用Python实现Excel自动化报表的最终章，包括图表生成和报表自动化发送等，让你的数据呈现和分发工作效率翻倍！同时，本系列所有代码都将持续更新并汇总在我的GitHub仓库中，敬请关注！未来，这个**“Python职场效率专家实战包”还将包含更多开箱即用、功能强大**的自动化工具