2014年9月15日 星期一

Excel-取用不連續工作表中相同位置儲存格的計算(COUNTIF,INDIRECT,ADDRESS)

我們常在一個 Excel 活頁簿中的多個工作表,分別放置了相同格式但不同內容的資料,並且希望取用這些工作表內容加以計算。

例如:老師們將不同班級同學的成績記錄在多個格式一樣的工作表中,而要取出各個工作表的資料來計算各班的不及格人數,或是計算多班的不及格人數總和。該如何處理這類的問題呢?(參考下圖)

過去網友也問到:COUNTIF 函數無法跨工作表使用的相關問題,在此也會遇到,所以要用不同的方式來計算。

 

(1) 計算各班不及格人數

儲存格E2:=COUNTIF(INDIRECT(D2&"!"&"B2:B21"),"<60")

複製儲存格E2,貼至儲存格E2:E11。

透過 INDIRECT 函數將「D2&"!"&"B2:B21"」字串轉換成「位址」(本例為:301!B2:B21)。再透過 COUNTIF 函數來計算小於 60 的個數。

 

(2) 計算全部班級不及格人數總和

儲存格H2:={=SUM((N(INDIRECT(ADDRESS(ROW(A$2:A$21),2,1,1,
TRANSPOSE(D2:D11))))<60)*1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。Excel 會自動在頭尾產生「{  }

在陣列公式中,ROW(A$2:A$21) 代表第2, 3, 4, ..., 21。使用 TRANSPOSE 函數將班級名稱轉置(列的排列轉為欄的排列),在 ADDRESS 函數中即可取得表列文字所代表的每一個工作表相同位置的內容。

利用 INDIRECT 函數將 ADDRESS 函數取得的字串轉換成「位址」,而 N 函數將儲存格內容轉換為數字。公式 N(公式)<60)*1,目的為找出小於 60 的 TRUE/FALSE 陣列,「*1」的作用為將TRUE/FALSE 陣列轉換為 1/0 的陣列。再送至 SUM 函數計算總和,即為所求。

 

(3) 計算部分班級不及格人數總和

儲存格E5:={=SUM((N(INDIRECT(ADDRESS(ROW(A$2:A$21),2,1,1,
TRANSPOSE(G5:G9))))<60)*1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。Excel 會自動在頭尾產生「{  }

原理同 (2)。藉助班級清單,便可找出任意「不連續」工作表的不及格人數總和。

沒有留言:

張貼留言

檢視其他文章

好康東東