在 Excel 中如果有多個工作表，若要將其摘要在同一個工作表，該如何設計公式？
以二個工作表為例，參考下圖，有工作表：104年和105年。
下圖中是在另一個工作表(整理)中以下拉式選單選取一個工作表，並將所對應的工作表摘要成下圖的清單。
【公式設計與解析】
1. 製作下拉式清單
利用「資料驗證」功能，將資料驗證準則設定為：
儲存格內允許：清單；來源：「104年,105年」。
注意：其中清單內容必須和工作表名稱一致。
2. 設定名稱
因為104年、105年兩個工作表中有相同的欄位名稱，所以在定義名稱時，千萬不要使用 Ctrl+Shift+F3 鍵來快速設定名稱，因為這個操作的預設領域為『活頁簿』。所以請你手動設定將每一個工作表中的資料，並以其欄位名稱來定義名稱，並將其名稱指定所屬的工作表名稱。
最後共設定了 6 個名稱：
3. 輸入公式
(1) 使用定數
假設以『105年』工作表為例，公式設計：
儲存格B2：=SUMPRODUCT(('105年'!姓名=整理!$A2)*('105年'!區別=整理!B$1)*
('105年'!金額))
複製儲存格B2，貼至儲存格B2:E42。
在 SUMPRODUCT 函數中判斷 ('105年'!姓名=整理!$A2)*('105年'!區別=整理!B$1) 二個條件是否成立，其中「*」運算相當於執行邏輯 AND 運算，傳回 TRUE／FALSE 陣列。而 TRUE／FALSE 陣列和('105年'!金額)執行「*」(乘法)運算時，會轉換為 1／0 陣列。最後傳回相乘積的總和，即為所求。
這個公式若要套用在工作表：104年，則必須修改公式。
(2) 使用變數
根據使用定數的公式概念，如果想要使用下拉式清單來選取年度時，工作表名稱變成一個變數。公式修改為：
儲存格B2：=SUMPRODUCT((INDIRECT($G$2&"!姓名")=整理!$A2)*(INDIRECT
($G$2&"!區別")=整理!B$1)*(INDIRECT($G$2&"!金額")))
複製儲存格B2，貼至儲存格B2:E42。
因為儲存格G2的內容會變，所以使用：
INDIRECT($G$2&"!姓名") 取代 '105年'!姓名
INDIRECT($G$2&"!區別") 取代 '105年'!區別
INDIRECT($G$2&"!金額") 取代 '105年'!金額
留言列表