2016年3月29日 星期二

Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT)

在 Excel 中如果有多個工作表,若要將其摘要在同一個工作表,該如何設計公式?
以二個工作表為例,參考下圖,有工作表:104年和105年。
Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT) Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT)
下圖中是在另一個工作表(整理)中以下拉式選單選取一個工作表,並將所對應的工作表摘要成下圖的清單。
Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT)

【公式設計與解析】
1. 製作下拉式清單
利用「資料驗證」功能,將資料驗證準則設定為:
儲存格內允許:清單;來源:「104年,105年」。
注意:其中清單內容必須和工作表名稱一致。
image

2. 設定名稱
因為104年、105年兩個工作表中有相同的欄位名稱,所以在定義名稱時,千萬不要使用Ctrl+Shift+F3 鍵來快速設定名稱,因為這個操作的預設領域為『活頁簿』。所以請你手動設定將每一個工作表中的資料,並以其欄位名稱來定義名稱,並將其名稱指定所屬的工作表名稱。
image
最後共設定了 6 個名稱:
image

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年'!金額

沒有留言:

張貼留言

檢視其他文章

好康東東