Python任务管理与财务报表程序：优先级与财务数据关联可视化需求

一、数据预处理（从openpyxl提取并整理关联数据） #

你需要从Excel文件中提取任务优先级、收支金额（建议收入记正、支出记负）、收益（可通过收入-支出计算）三类核心数据，再按优先级分组聚合统计。

• 用openpyxl读取数据的示例：

from openpyxl import load_workbook

wb = load_workbook("your_task_finance.xlsx")
ws = wb.active

# 假设表头为A:任务优先级, B:收支金额, C:收益
data = []
for row in ws.iter_rows(min_row=2, values_only=True):
    priority, amount, profit = row
    # 跳过空数据行
    if priority is not None and amount is not None:
        data.append({"priority": priority, "amount": amount, "profit": profit})

• 按优先级分组统计（纯Python实现）：

# 初始化分组统计字典，确保优先级分类统一
priority_stats = {
    "高": {"total_amount": 0, "total_profit": 0},
    "中": {"total_amount": 0, "total_profit": 0},
    "低": {"total_amount": 0, "total_profit": 0}
}

for item in data:
    p = item["priority"]
    # 只统计已定义的优先级类型
    if p in priority_stats:
        priority_stats[p]["total_amount"] += item["amount"]
        priority_stats[p]["total_profit"] += item["profit"]

# 提取可视化所需列表
priorities = list(priority_stats.keys())
total_amounts = [stats["total_amount"] for stats in priority_stats.values()]
total_profits = [stats["total_profit"] for stats in priority_stats.values()]

如果数据量较大，用pandas配合openpyxl会更高效：

import pandas as pd
df = pd.read_excel("your_task_finance.xlsx")
grouped = df.groupby("priority")[["amount", "profit"]].sum()
priorities = grouped.index.tolist()
total_amounts = grouped["amount"].tolist()
total_profits = grouped["profit"].tolist()

二、可视化实现（结合matplotlib） #

根据需求，推荐以下几种直观的图表类型：

1. 分组柱状图（对比各优先级的收支与收益） #

适合同时展示不同优先级下的总收支、总收益差异：

import matplotlib.pyplot as plt

x = range(len(priorities))
width = 0.35

fig, ax = plt.subplots(figsize=(8, 5))
# 绘制收支柱状图
rects_amount = ax.bar([i - width/2 for i in x], total_amounts, width, label='总收支')
# 绘制收益柱状图
rects_profit = ax.bar([i + width/2 for i in x], total_profits, width, label='总收益')

# 配置图表标签与样式
ax.set_xticks(x)
ax.set_xticklabels(priorities)
ax.set_ylabel('金额')
ax.set_title('任务优先级与财务数据关联')
ax.legend()

# 给柱子添加数值标签
def add_label(rects):
    for rect in rects:
        height = rect.get_height()
        ax.annotate(f'{height:.2f}',
                    xy=(rect.get_x() + rect.get_width()/2, height),
                    xytext=(0, 3),
                    textcoords="offset points",
                    ha='center', va='bottom')

add_label(rects_amount)
add_label(rects_profit)

plt.tight_layout()
plt.show()

2. 堆叠柱状图（展示单优先级的收支结构） #

如果需要拆分每个优先级下的收入、支出明细，用堆叠柱状图更清晰：

# 先重新统计各优先级的收入、支出
priority_in_out = {
    "高": {"income": 0, "expense": 0},
    "中": {"income": 0, "expense": 0},
    "低": {"income": 0, "expense": 0}
}

for item in data:
    p = item["priority"]
    if p not in priority_in_out:
        continue
    if item["amount"] > 0:
        priority_in_out[p]["income"] += item["amount"]
    else:
        priority_in_out[p]["expense"] += abs(item["amount"])

incomes = [stats["income"] for stats in priority_in_out.values()]
expenses = [stats["expense"] for stats in priority_in_out.values()]

fig, ax = plt.subplots(figsize=(8, 5))
ax.bar(priorities, incomes, label='收入')
ax.bar(priorities, expenses, bottom=incomes, label='支出')

ax.set_ylabel('金额')
ax.set_title('各优先级任务的收支结构')
ax.legend()
plt.show()

3. 柱状+折线组合图（突出收益趋势） #

若想重点展示收益变化，同时对比收支情况，可采用双轴组合图：

fig, ax1 = plt.subplots(figsize=(8, 5))

# 左侧轴：收支柱状图
color = 'tab:blue'
ax1.set_xlabel('任务优先级')
ax1.set_ylabel('收支金额', color=color)
ax1.bar(priorities, total_amounts, color=color)
ax1.tick_params(axis='y', labelcolor=color)

# 右侧轴：收益折线图
ax2 = ax1.twinx()
color = 'tab:red'
ax2.set_ylabel('收益', color=color)
ax2.plot(priorities, total_profits, color=color, marker='o', linewidth=2)
ax2.tick_params(axis='y', labelcolor=color)

fig.tight_layout()
plt.title('任务优先级与收支、收益关联')
plt.show()

三、注意事项 #

• 确保优先级分类统一（避免同时出现"高"和"高级"这类歧义值），否则分组会出错；
• 收支为负值时，柱状图会自动向下显示，可通过color参数给正负收支设置不同颜色，提升可读性；
• 提前清洗Excel中的空值、异常值，避免统计或可视化报错。

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