如何用Excel数据透视表与Python实现价格波动自动化分析？

一、Excel数据透视表半自动化实现 #

1. 识别年度/整体持续涨跌的产品品类 #

• 整理原始数据，确保包含交易日期（标准日期格式）、产品品类、交易价格核心字段
• 插入数据透视表：行字段选产品品类+交易日期（按年度分析时，右键日期字段选「分组」→「年」），值字段选交易价格，聚合方式设为平均值（消除同一品类同日多交易的价格差异）
• 将透视表结果复制为普通区域，按产品品类和交易日期（或年度）升序排序
• 新增涨跌标识列，用公式判断相邻日期价格变化：=IF(C2>C1,"涨",IF(C2<C1,"跌","平"))（假设价格列是C列）
• 按产品品类+年度（或整体）分组，用COUNTIFS统计每组内"涨"/"跌"的占比，筛选出100%为"涨"或"跌"的品类

2. 计算相邻交易的价格涨跌速度（百分比） #

• 基于排序后的透视表数据，新增涨跌速度(%)列，公式：=(C2-C1)/C1*100（保留2位小数）
• 若需年度汇总，再插入透视表：行字段设产品品类+年度，值字段选涨跌速度(%)，聚合方式按需选平均值或明细

二、Python全自动化实现（基于Pandas） #

1. 数据预处理 #

import pandas as pd
import numpy as np

# 读取交易数据（支持csv/excel，此处以csv为例）
df = pd.read_csv("交易记录.csv")
# 转换日期格式为datetime类型
df["交易日期"] = pd.to_datetime(df["交易日期"])
# 按品类和日期升序排序，确保时间序列正确
df = df.sort_values(by=["产品品类", "交易日期"], ascending=True)
# 计算每个品类每日均价（处理同一日期多条交易的情况）
df_daily = df.groupby(["产品品类", "交易日期"])["交易价格"].mean().reset_index()

2. 识别持续涨跌的产品品类 #

整体维度（全时间段）

# 标记相邻日期的价格涨跌方向
df_daily["涨跌方向"] = np.where(
    df_daily["交易价格"] > df_daily.groupby("产品品类")["交易价格"].shift(1),
    "涨",
    np.where(df_daily["交易价格"] < df_daily.groupby("产品品类")["交易价格"].shift(1), "跌", "平")
)

# 筛选全时间段内持续涨/跌的品类（排除"平"的情况）
overall_continuous = df_daily.groupby("产品品类").filter(
    lambda x: x["涨跌方向"].nunique() == 1 and x["涨跌方向"].iloc[0] != "平"
)["产品品类"].unique().tolist()
print("全时间段持续涨跌的品类：", overall_continuous)

年度维度

# 提取交易年度
df_daily["年度"] = df_daily["交易日期"].dt.year

# 筛选各年度内持续涨/跌的品类
annual_continuous = df_daily.groupby(["产品品类", "年度"]).filter(
    lambda x: x["涨跌方向"].nunique() == 1 and x["涨跌方向"].iloc[0] != "平"
)[["产品品类", "年度"]].drop_duplicates()
print("年度内持续涨跌的品类：\n", annual_continuous)

3. 计算相邻交易的涨跌速度（百分比） #

# 计算整体维度的相邻涨跌速度
df_daily["涨跌速度(%)"] = df_daily.groupby("产品品类")["交易价格"].pct_change() * 100
# 保留2位小数
df_daily["涨跌速度(%)"] = df_daily["涨跌速度(%)"].round(2)

# 计算年度维度的平均涨跌速度（按需选择）
annual_avg_speed = df_daily.groupby(["产品品类", "年度"])["涨跌速度(%)"].mean().round(2).reset_index()
print("年度平均涨跌速度：\n", annual_avg_speed)

# 保存结果到Excel，方便后续查看
df_daily.to_excel("全量价格波动分析结果.xlsx", index=False)
annual_avg_speed.to_excel("年度涨跌速度汇总.xlsx", index=False)

内容的提问来源于stack exchange，提问作者Chirag Sutariya