期末成績分析，老師最想要：下拉選班級、選項目（總分/平均/及格率/優秀率），數據與圖表雙聯動實時刷新。WPS 表格結合數據有效性下拉菜單、條件統計函數、動態圖表引用，零 VBA 實現專業級分析儀。

數據結構

• 「原始數據」表：8 班 × 8 科，含班級、姓名、各科分數；
• 「班級項目查詢」表：A1「查詢班級」、A2「查詢項目」，B1/B2 為下拉菜單單元格；
• 輔助計算區：原始數據表 P3:X13 區域預置統計公式。

圖1 原始成績表

核心實現步驟

1. 構建下拉菜單（數據有效性）

• B1 來源：1,2,3,4,5,6,7,8（半角逗號，班級號）；
• B2 來源：總分,平均分,及格率,優秀率（項目名）。

2. 輔助區動態引用查詢條件

Q3：=班級項目查詢!B1（當前班級）
Q4：=班級項目查詢!B2（當前項目）

3. 條件統計公式（以語文 D 列為例，Q 列對應語文）

• Q5 總分：=SUMIF($B:$B,$Q$3,D:D)
• Q6 平均分：=Q5/COUNTIF($B:$B,$Q$3)
• Q7 及格率：=SUMPRODUCT(($B:$B=$Q$3)*(D:D>=60))/COUNTIF($B:$B,$Q$3)
• Q8 優秀率：=SUMPRODUCT(($B:$B=$Q$3)*(D:D>=85))/COUNTIF($B:$B,$Q$3)

選中 Q5:Q8 → 橫向填充至 X 列（8 科全覆蓋） → 設置數值格式保留兩位小數。

圖2 輔助計算區佈局

4. 查詢結果顯示區（第二表格）

Q11 輸入項目名（或引用 Q4），Q12 寫科目標籤，Q13：
=VLOOKUP($Q$11,$P$5:$X$8,COLUMN()-15,FALSE) → 橫向填充至 X13。
COLUMN()-15 將絕對列號轉為相對序號，配合 VLOOKUP 精準取值。

圖3 結果顯示區格式

5. 動態圖表綁定

1. 空白處輸入動態標題：="期末考試"&B1&"班"&B2&"圖表"；
2. 插入「簇狀柱形圖」→ 系列頁：
   名稱：=原始數據!$Q$11
   值：=原始數據!$Q$13:$X$13
   X 軸標籤：=原始數據!$Q$12:$X$12；


圖4 圖表數據源設置

3. 顯示數值標籤，完成拖至查詢表合適位置。

圖5 最終聯動效果

使用效果

B1 換班級、B2 換項目 → 輔助區公式自動重算 → 結果區 VLOOKUP 取新值 → 圖表數據源引用結果區 → 圖表同步更新。全過程無代碼、全公式、可擴展、易維護，教師成績分析神器！