如何提取表格指定时间区间内的数据并创建透视表

Excel 方案 #

数据提取方法 #

• 筛选法：选中date列表头，点击「数据」选项卡→「筛选」，点击date列的筛选箭头，选择「日期筛选」→「年份等于2020」即可筛选出全年数据，筛选后复制到新工作表即可完成提取。
• 函数法：Excel 365/2021及以上版本可直接用FILTER函数，假设原数据date列在A列、value列在B列，表头在第一行，新工作表A2输入公式：=FILTER(Sheet1!A2:B, YEAR(Sheet1!A2:A)=2020, "无匹配数据")，会自动溢出2020年的所有关联数据。

透视表创建 #

选中提取好的所有数据，点击「插入」选项卡→「数据透视表」，选择存放位置后，可按需求把date字段拖到行/列区域做时间维度拆分（支持按月份、季度自定义分组），value字段拖到值区域做求和、平均值、最大值等统计。

小提示：如果需要灵活调整提取的时间周期，把年份判断换成日期区间判断即可，比如要提取2020年3月到5月的数据，判断条件改为date >= '2020-03-01' AND date <= '2020-05-31'即可适配任意周期。

Python Pandas 方案 #

数据提取代码 #

假设你已经把原始数据读入为DataFrame对象df：

import pandas as pd
# 先确保date字段为日期类型，非日期格式需要先转换
df['date'] = pd.to_datetime(df['date'])
# 提取2020年全年数据
df_2020 = df[df['date'].dt.year == 2020].copy()

透视表创建代码 #

用pandas内置的pivot_table方法即可实现，示例为按月份统计value的总和：

pivot_df = pd.pivot_table(
    df_2020,
    index=df_2020['date'].dt.month, # 行维度设置为月份，也可修改为dt.quarter按季度统计
    values='value',
    aggfunc='sum' # 统计方式可替换为'mean'/'max'/'count'等
)

SQL 方案 #

如果数据存储在关系型数据库中，用内置日期函数筛选即可，不同数据库语法略有差异：

• MySQL/PostgreSQL：SELECT date, value FROM 表名 WHERE YEAR(date) = 2020;
• SQL Server：SELECT date, value FROM 表名 WHERE DATEPART(year, date) = 2020;
• Oracle：SELECT date, value FROM 表名 WHERE EXTRACT(YEAR FROM date) = 2020;

筛选后的数据可导出后做透视表，也可以直接用SQL聚合+GROUP BY语法直接生成透视结果，比如按月份统计value总和：

SELECT MONTH(date) as 月份, SUM(value) as 总mm值 
FROM 表名 
WHERE YEAR(date)=2020 
GROUP BY MONTH(date);

内容的提问来源于stack exchange，提问作者Rachele Franceschini