贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

我們常在一個 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)。藉助班級清單,便可找出任意「不連續」工作表的不及格人數總和。

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()


留言列表 (3)

發表留言
  • 訪客
  • 请问,2,1,1,是指定什么?
  • 訪客
  • 请问,2,1,1,是指定什么?
  • 提供您微軟的函數說明內容:
    ADDRESS 函數
    您可以使用 ADDRESS 函數,根據指定的欄列號碼,取得工作表中儲存格的位址。 例如,ADDRESS(2,3) 會傳回 $C$2。 另一個範例 ADDRESS(77,300) 會傳回 $KN$77。 您可以使用其他函數,例如 ROW 和 COLUMN 函數,為 ADDRESS 函數提供欄號和列號的引數。

    語法
    ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
    ADDRESS 函數語法具有下列引數:
    row_num 必要。 指定要用在儲存格參照中之列號的數值。
    column_num 必要。 指定要用在儲存格參照中之欄號的數值。
    abs_num 選擇性。 指定要傳回之參照類型的數值。

    vincent 於 2015/04/24 15:42 回覆

  • 0629
  • 您好
    因為找很多篇都找不到
    想要請教您關於excel的問題
    請問:若是我的excel活頁簿裡有許多工作表
    除了工作表名稱之外, 還設定不同顏色做區分
    是否有什麼方法, 可以讓我直接把某個顏色的工作表直接快速選取到另一個活頁簿裡???
    真的已經爬過很多文, 但都找不到類似的方法
    所以想拜託您幫幫忙, 是否可以教學?

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼