如何高效计算同分类相邻日期数值总和占比并生成统计表格？

原始数据 #

需求目标 #

计算相邻日期中**相同分类（cat）**的数值（val）总和的比值，最终输出如下格式表格：

方案1：Excel 自动化实现 #

步骤（适配Excel 365动态数组） #

1. 提取唯一日期列表：
   在空白单元格输入公式：=UNIQUE(A2:A13)（假设原始数据日期列在A2:A13），生成排序后的唯一日期序列。

2. 计算每日比值：
   在日期列表右侧单元格输入以下动态数组公式，下拉填充：

   =LET(
       curr_date, INDEX(UNIQUE(A2:A13), ROW()-ROW(UNIQUE(A2:A13))+1),
       prev_date, INDEX(UNIQUE(A2:A13), ROW()-ROW(UNIQUE(A2:A13))),
       curr_cats, FILTER(B2:B13, A2:A13=curr_date),
       curr_vals, FILTER(C2:C13, A2:A13=curr_date),
       prev_cats, FILTER(B2:B13, A2:A13=prev_date),
       prev_vals, FILTER(C2:C13, A2:A13=prev_date),
       common_cats, INTERSECT(curr_cats, prev_cats),
       sum_curr, SUM(FILTER(curr_vals, ISNUMBER(MATCH(curr_cats, common_cats, 0)))),
       sum_prev, SUM(FILTER(prev_vals, ISNUMBER(MATCH(prev_cats, common_cats, 0)))),
       IF(ROW()=ROW(UNIQUE(A2:A13)), "NA", sum_curr/sum_prev)
   )
   

3. 格式设置：将比值列设置为百分比格式（保留两位小数）。

方案2：Python Pandas 批量处理 #

适合数据量大、需要重复执行的场景，代码可复用：

import pandas as pd

# 构造/读取原始数据
data = [
    ["01/02/2024", "cat A", 7],
    ["01/02/2024", "cat B", 4],
    ["01/02/2024", "cat C", 5],
    ["02/02/2024", "cat A", 9],
    ["02/02/2024", "cat B", 2],
    ["02/02/2024", "cat C", 3],
    ["02/02/2024", "cat D", 6],
    ["02/02/2024", "cat E", 8],
    ["03/02/2024", "cat B", 4],
    ["03/02/2024", "cat C", 7],
    ["03/02/2024", "cat D", 3],
    ["03/02/2024", "cat E", 1],
]
df = pd.DataFrame(data, columns=["date", "cat", "val"])

# 日期格式转换与排序
df["date"] = pd.to_datetime(df["date"], format="%d/%m/%Y")
sorted_dates = sorted(df["date"].unique())

# 计算比值
result = []
for idx, curr_date in enumerate(sorted_dates):
    if idx == 0:
        result.append({"date": curr_date.strftime("%d/%m/%Y"), "%": "NA"})
        continue
    prev_date = sorted_dates[idx-1]
    # 获取当日与前一日的分类-值映射
    curr_map = df[df["date"] == curr_date].set_index("cat")["val"].to_dict()
    prev_map = df[df["date"] == prev_date].set_index("cat")["val"].to_dict()
    # 提取共同分类并计算总和
    common_cats = set(curr_map.keys()) & set(prev_map.keys())
    sum_curr = sum(curr_map[cat] for cat in common_cats)
    sum_prev = sum(prev_map[cat] for cat in common_cats)
    # 格式化比值
    ratio = f"{(sum_curr / sum_prev)*100:.2f}%"
    result.append({"date": curr_date.strftime("%d/%m/%Y"), "%": ratio})

# 输出结果表格
result_df = pd.DataFrame(result)
print(result_df)

运行输出 #

date       %
0  01/02/2024      NA
1  02/02/2024  87.50%
2  03/02/2024  78.95%

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