Skip to content

Productivity › 試算表 › 資料分析流程

Excel 資料分析流程 (Data Analysis Workflow) ​

資料準備好之後,Excel 的分析常走這條路:探索 → 分群與彙總 → 假設分析 (what-if) → 初步預測。Excel 擅長把商業問題快速轉成表格邏輯;分析需求長大時,重點是知道何時換工具。

流程 ​

text
1. 樞紐分析表找出值得追的維度與指標
2. IF / IFS / SWITCH 建立分群
3. SUMIFS 等條件聚合算出各群指標
4. Goal Seek / Scenario Manager / Data Table 測試假設
5. 移動平均、趨勢線、FORECAST.ETS 做初步外推
6. 流程變重複、資料變大 → Power Query、BI、SQL、Python

1. 探索:樞紐分析表 ​

樞紐分析表 (PivotTable) 不只是報表,而是第一個探索工具:哪些維度值得切、哪些群組差異最大、哪些指標要往下追。

功能用途
值欄位設定加總、計數、平均、% of total
計算欄位 (Calculated Field)補出原始資料沒有的指標(如毛利率)
群組 (Grouping)日期 → 月 / 季 / 年;數值 → 區間
交叉分析篩選器 (Slicer)、時間表 (Timeline)互動式篩選,適合 demo 與 review
樞紐分析圖 (PivotChart)隨樞紐表一起篩選的圖

原始表保持乾淨,分析邏輯先在樞紐層驗證;指標與切法穩定後,再決定要不要搬進 Power Pivot 或 BI 工具。

2. 分群與條件聚合 ​

很多問題不是「總銷售多少」,而是「哪些客戶值得關注、哪群成長最快」。先用邏輯函數定義群組,再用條件聚合算每群的指標。

函數適合例
IF二元:是否達標、是否續約=IF(C2>=Target, "met", "missed")
IFS多層門檻=IFS(C2>=1000, "A", C2>=500, "B", TRUE, "C")
SWITCH等值對應=SWITCH(D2, 1, "new", 2, "active", "churned")
SUMIFS / COUNTIFS / AVERAGEIFS各群彙總=SUMIFS(Sales[Amount], Sales[Segment], "A")

IFS 沒有內建 else,全部不成立回傳 #N/A,所以最後補 TRUE, fallback。語法細節見 Excel 函數。

3. 假設分析 (What-If Analysis) ​

分析問題輸入
情境分析 (scenario analysis)幾組離散假設下結果各是多少?保守 / 基準 / 樂觀
敏感度分析 (sensitivity analysis)某輸入在一段範圍內變動,輸出怎麼反應?哪個輸入最敏感?連續範圍

模型要分清自變數(外部給定的輸入,如收入、扣除額、稅率)與應變數(由輸入算出的輸出,如 應納稅額 = (收入 − 扣除額) × 稅率)。把輸入集中在一區、輸出公式只參照輸入格,what-if 工具才好用。

工具(資料 → 模擬分析)用途例
目標搜尋 (Goal Seek)已知想要的結果,反推一個輸入毛利率要 20%,售價該設多少?
分析藍本管理員 (Scenario Manager)儲存多組輸入並產生比較摘要最佳 / 基準 / 最差情境
運算列表 (Data Table)一或兩個輸入跑過一段範圍,產生敏感度表折扣 5%–30% × 銷量,對應的利潤
規劃求解 (Solver,增益集)多個輸入、有限制條件的最佳化預算限制下的最大化產量

4. 初步預測 ​

概念意義
預測不是事實依賴歷史資料與方法假設,環境改變就可能失效
季節性 (seasonality)與月份、季節、節日相關的週期規律;有季節性時單純平均會失真
預測偏誤 (bias)系統性偏高或偏低:只跟到舊趨勢、結構改變卻沒更新方法
預測區間表達不確定性,不要只看單點預測
方法做法限制
簡單移動平均=AVERAGE(最近 n 期)對突變反應慢、不處理季節性
加權移動平均=SUMPRODUCT(values, weights) / SUM(weights)權重主觀
趨勢線 (trendline)圖表 → 加入趨勢線,可顯示方程式與 R²只看方向,不是完整預測
FORECAST.LINEAR()線性迴歸外推假設線性、無季節性
FORECAST.ETS() / FORECAST.ETS.CONFINT()指數平滑 (AAA 版本),自動偵測季節性;後者給預測區間的半寬時間軸需等距;不適合模型比較與回測
預測工作表 (Data → Forecast Sheet)一鍵產生 ETS 預測表與圖同上

加權移動平均例:最近三期 2、3、4,權重 0.15、0.35、0.50 → (2×0.15 + 3×0.35 + 4×0.50) / 1.00 = 3.35。

需要比較模型、做特徵或回測時就離開 Excel:時間序列方法見 時間序列。

何時升級工具 ​

需求工具
重複的匯入與清理Power Query
多表關聯、可重用指標Power Pivot、Power BI
統計檢定、迴歸、模型Python / R(統計)
Dashboard 設計原則資料溝通