EXCEL Python 實現繪制柱狀線型組合圖和樹狀圖（包含數據透視表）

1、組合圖、數據透視表

（1）數據預處理

知識點

日期函數 year() month()
數據透視表操作

同比計算公式

環比計算公式

（2）excel 數據透視表+插入組合圖

a.2015~2017數據集處理方式：

操作：
結果

b.2020~2022數據集處理方式

一次數據透視結果：

操作
結果

兩次數據透視結果

操作：
結果：

（3）python繪制組合圖

a.數據預處理結果

在這里插入圖片描述

b.代碼

知識點：

使用 make_subplots 創建子圖，設置 secondary_y=True 啟用雙 Y 軸

交互模式：hovermode=‘x unified’ 使鼠標懸停時同時顯示所有系列在同一日期的數據，便于對比。

import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots# 讀取數據
data = pd.read_excel('組合圖數據.xlsx',engine='openpyxl')# 將日期列轉換為datetime類型
data['日期'] = pd.to_datetime(data['日期'])# 創建組合圖
fig = make_subplots(specs=[[{"secondary_y": True}]])# 添加成交金額柱狀圖
fig.add_trace(go.Bar(x=data['日期'],y=data['求和項:成交金額'],name='求和項:成交金額',marker_color='#1f77b4'),secondary_y=False
)# 添加同比增幅折線圖
fig.add_trace(go.Line(x=data['日期'],y=data['同比'],name='同比增幅',line=dict(color='#d62728', width=2, dash='dash')),secondary_y=True
)# 添加環比增幅折線圖
fig.add_trace(go.Line(x=data['日期'],y=data['環比'],name='環比增幅',line=dict(color='#2ca02c', width=2, dash='dash')),secondary_y=True
)# 設置圖表布局
fig.update_layout(title='2020年1月1日-2022年12月1日的成交金額、環比增幅、同比增幅的組合圖',xaxis_title='日期',yaxis_title='成交金額',yaxis2=dict(title='增幅 (%)',overlaying='y',side='right'),hovermode='x unified'
)# 顯示圖表
fig.show()

c.結果

組合圖

優化：銷售數據儀表盤：

a.代碼

知識點

dash 構建交互界面，dash_bootstrap_components 提供美觀的 UI 組件。
dbc.Container：Bootstrap 的響應式容器，fluid=True表示寬度 100%
dbc.Row, dbc.Col：Bootstrap 的網格系統，一行一列。
html.H1：HTML 標題標簽，className添加樣式（居中、上下邊距）。
dcc.Dropdown：下拉菜單組件：
id：組件唯一標識，用于回調。
options：選項列表，格式為[{label:顯示文本, value:實際值}]。
value：默認選中的值。
multi=True：允許多選。
dbc.Button：Bootstrap 按鈕：
n_clicks：記錄點擊次數，觸發回調。
color=“primary”：藍色主按鈕。
dbc.Spinner：加載動畫，在數據處理時顯示。
dcc.Graph：Plotly 圖表組件，id='sales-graph’用于顯示組合圖。
@app.callback：裝飾器，定義回調函數。
Output：回調輸出，更新sales-graph組件的figure屬性。
Input：觸發回調的輸入，這里是按鈕的n_clicks。
State：獲取下拉菜單當前值（不觸發回調）。
hovertemplate：鼠標懸停時顯示的信息：
%{x|%Y年%m月}：格式化日期（如 2023 年 01 月）。
%{text}：顯示text中的金額。
< extra></ extra>：隱藏右側默認信息。
overlaying=‘y’：與左側 Y 軸共享 X 軸。
hovermode=‘x unified’：鼠標懸停時，所有數據在同一 X 軸對齊顯示。
tickformat=‘% Y 年 % m 月’：X 軸日期格式化為2023年01月。
tickformat=‘,’：Y 軸數字添加千位分隔符（如1,000,000）。
流式布局（fluid layout）fluid=True 響應式布局適配不同屏幕。

import pandas as pd
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import dash
from dash import dcc, html, Input, Output, State
import dash_bootstrap_components as dbc# 讀取數據
data = pd.read_excel('組合圖數據.xlsx',engine='openpyxl')# 確保日期列是正確的格式
data['日期'] = pd.to_datetime(data['日期'])
data['年份'] = data['日期'].dt.year
data['月份'] = data['日期'].dt.month# 創建應用
app = dash.Dash(__name__, external_stylesheets=[dbc.themes.BOOTSTRAP])
server = app.server  # 用于生產部署# 獲取年份和月份的唯一值
years = sorted(data['年份'].unique())
months = list(range(1, 13))
month_names = ['一月', '二月', '三月', '四月', '五月', '六月','七月', '八月', '九月', '十月', '十一月', '十二月']# 應用布局
app.layout = dbc.Container([dbc.Row([dbc.Col(html.H1("銷售數據分析儀表盤", className="text-center mt-4 mb-4"), width=12)]),dbc.Row([dbc.Col([html.Label("選擇年份:", className="mr-2"),dcc.Dropdown(id='year-dropdown',options=[{'label': str(year), 'value': year} for year in years],value=years,  # 默認選擇所有年份multi=True,className="w-100")], width=3),dbc.Col([html.Label("選擇月份:", className="mr-2"),dcc.Dropdown(id='month-dropdown',options=[{'label': month_names[i - 1], 'value': i} for i in months],value=months,  # 默認選擇所有月份multi=True,className="w-100")], width=3),dbc.Col([dbc.Button("應用篩選",id='apply-filter',n_clicks=0,className="mt-3",color="primary")], width=2)], className="mb-4"),dbc.Row([dbc.Col([dbc.Spinner(id="loading-spinner",children=[dcc.Graph(id='sales-graph')],color="primary",type="grow")], width=12)])], fluid=True)# 回調函數
@app.callback(Output('sales-graph', 'figure'),[Input('apply-filter', 'n_clicks')],[State('year-dropdown', 'value'),State('month-dropdown', 'value')]
)
def update_graph(n_clicks, selected_years, selected_months):# 確保參數有效if not selected_years:selected_years = yearsif not selected_months:selected_months = months# 篩選數據filtered_data = data[data['年份'].isin(selected_years) &data['月份'].isin(selected_months)]# 如果沒有數據，返回空圖表if filtered_data.empty:fig = go.Figure()fig.update_layout(title="沒有匹配的數據",xaxis_title="日期",yaxis_title="成交金額")return fig# 創建組合圖fig = make_subplots(specs=[[{"secondary_y": True}]])# 添加成交金額柱狀圖fig.add_trace(go.Bar(x=filtered_data['日期'],y=filtered_data['求和項:成交金額'],name='成交金額',text=[f"{x:,.0f}" for x in filtered_data['求和項:成交金額']],hovertemplate='日期: %{x|%Y年%m月}<br>成交金額: %{text}<extra></extra>',marker_color='#1f77b4'),secondary_y=False)# 添加同比增幅折線圖fig.add_trace(go.Scatter(x=filtered_data['日期'],y=filtered_data['同比'],name='同比增幅',text=[f"{x:.1f}%" for x in filtered_data['同比']],hovertemplate='日期: %{x|%Y年%m月}<br>同比增幅: %{text}<extra></extra>',line=dict(color='#d62728', width=2, dash='dash'),marker=dict(size=8)),secondary_y=True)# 添加環比增幅折線圖fig.add_trace(go.Scatter(x=filtered_data['日期'],y=filtered_data['環比'],name='環比增幅',text=[f"{x:.1f}%" for x in filtered_data['環比']],hovertemplate='日期: %{x|%Y年%m月}<br>環比增幅: %{text}<extra></extra>',line=dict(color='#2ca02c', width=2, dash='dash'),marker=dict(size=8)),secondary_y=True)# 設置圖表布局fig.update_layout(title=f"成交金額與增幅分析 ({', '.join(map(str, selected_years))}年)",title_font=dict(size=20),xaxis_title="日期",yaxis_title="成交金額",yaxis2=dict(title="增幅 (%)",overlaying='y',side='right'),legend=dict(x=0, y=1.05,orientation='h',bgcolor='rgba(255, 255, 255, 0.8)',bordercolor='rgba(0, 0, 0, 0.1)',borderwidth=1,font=dict(size=14)),hovermode='x unified',plot_bgcolor='rgba(240, 240, 240, 0.5)',margin=dict(l=60, r=60, t=60, b=60),font=dict(family="SimHei, WenQuanYi Micro Hei, Heiti TC", size=14))# 設置X軸格式fig.update_xaxes(tickformat='%Y年%m月',tickfont=dict(size=14))# 設置Y軸格式fig.update_yaxes(tickformat=',',title_font=dict(size=16))return figif __name__ == '__main__':app.run_server(debug=True)

b.結果

在這里插入圖片描述

銷售數據儀表盤

2、樹狀圖可視化

（1）數據預處理，數據透視表實現求和

在這里插入圖片描述

（2）EXCEL 插入樹狀圖

在這里插入圖片描述

（3）python matplotlib庫中的squarify.plot()函數繪制樹狀圖

import pandas as pd
import matplotlib.pyplot as plt
import squarify
import numpy as np# 讀取數據
df = pd.read_excel('樹狀圖.xlsx', engine='openpyxl')# 設置圖片清晰度
plt.rcParams['figure.dpi'] = 300
# 設置中文字體
plt.rcParams['font.sans-serif'] = ['SimHei', 'WenQuanYi Micro Hei', 'Heiti TC']# 數據預處理：計算占比，用于標簽顯示
total = df['求和項:成交金額'].sum()
df['占比'] = df['求和項:成交金額'].apply(lambda x: f"{x/total*100:.1f}%")# 創建自定義顏色映射
cmap = plt.cm.get_cmap('viridis', len(df))
colors = [cmap(i) for i in range(len(df))]# 繪制樹狀圖
plt.figure(figsize=(12, 8))  # 設置圖形大小squarify.plot(sizes=df['求和項:成交金額'],label=[f"{name}\n{amount/1e8:.1f}億\n{percent}"for name, amount, percent in zip(df['類別'], df['求和項:成交金額'], df['占比'])],color=colors,alpha=0.8,pad=True  # 添加間隔，使圖形更清晰
)# 設置標題和樣式
plt.title('不同類別成交金額樹狀圖', fontsize=16, pad=10)
plt.axis('off')  # 隱藏坐標軸# 添加圖例說明
plt.text(0.99, 0.01,f"總計: {total/1e8:.2f}億",ha='right',va='bottom',transform=plt.gca().transAxes,fontsize=10,bbox=dict(facecolor='white', alpha=0.7)
)# 調整布局
plt.tight_layout()# 顯示圖形
plt.show()