錦子老師

JASON：「錦子老師您好：因工作需求要做一個每月各產品的不良統計表，希望總表放一個下拉選單可以依月份自行選擇，並帶出該月各產品的不良數加總的方法。」

錦子老師：「這個問題比較麻煩，原因在此自動選取工作表內容，會使用到四個函數(SUMIFS、INDIRECT、CONCATE(CONCATENATE)、OFFSET)的整合。」

首先第一步我們先要設定B1儲存格的清單，可以是儲存格的範圍內容或是直接人工輸入以逗號分隔，我個人偏向使用儲存格建立。

所以在工作表AA1:AA12建立一個12月份資料

【注意】要與各月份工作表名稱一模一樣。

第二步則是點取B1儲存格，再點取「資料 > 資料驗證」圖示。

在【資料驗證】對話方塊，點取「儲存格內允許▼」鈕，選擇「清單」項目。

點取「來源」欄位，輸入「=$AA$1:$AA$12」。

點取「確定」鈕，即可看到點取B2儲存格▼鈕，會出現各個月份項目。

接下來就是在C6儲存格輸入運算公式，首先是要如何抓取到B1儲存格的月份所在工作表。

在EXCEL中指定工作表的儲存格表示式是：工作表名稱!儲存格欄名列號。

故使用CONCAT函數先將表示字串整合起來，其公式如下：

CONCAT($B$1,"!","R"&ROW()-3&"C3") 將B1儲存格內容與!、R及列編號減3的值及C3字串結合，也可以寫成=B1&"!"&"R"&ROW()-3&"C3"。

CONCAT(字串1,字串2,…字串254)

ROW() 傳回目前儲存格的列編號。

當工作表及儲存格位置抓取沒問題後，接著我們要抓取儲存格的值，故須將CONCAT函數傳回的文字變成正式的位置，所以使用INDIRECT函數，將公式變為：

=INDIRECT(CONCAT($B$1,"!","R"&ROW()-3&"C3"),FALSE)

INDIRECT(儲存格位置,參照方式)

參照方式沒有輸入或輸入TRUE，表示是以A1格式，若為FALSE，表示是以R1C1格式。

我們己經抓取1月工作表的C3儲存格內容，但因為要統計整列資料中高低階各個產品的不良數量，故這是須要使用到二個函數OFFSET及SUMIFS函數，首先由OFFSET來框選要統計的範圍。

=OFFSET(INDIRECT(CONCAT($B$1,"!","R"&ROW()-3&"C3"),FALSE),0,0,1,31)

還是顯示原本資料是因為OFFSET傳回來顯示的是第一個格的資料，其餘資料隱藏。

OFFSET(起始位置，移動列數，移動欄數，框選列數，框選欄數)

接下來使用SUMIFS函數來統計不良數量合計，公式更改為：

=SUMIFS(OFFSET(INDIRECT(CONCAT($B$1,"!","R"&ROW()-3&"C3"),FALSE),0,0,1, 31),OFFSET(INDIRECT(CONCAT($B$1,"!","R2C3"),FALSE),0,0,1,31),C$5,OFFSET(INDIRECT(CONCAT($B$1,"!","R1C3"),FALSE),0,0,1,31),C$4)

再將公式複製到其他儲存格(C3:N21)。

SUMIFS(計算範圍,條件範圍1,條件1,條件範圍2,條件2…條件範圍127,條件127)

R2C3 是因為每個月份工作表產品名稱在第2列，故R(列)2，第一筆資料是在C欄，故C(欄)3(A在第1欄，故C是在第3欄)。

R1C3 是因為每個月份工作表階級在第1列，故R(列)1，第一筆資料是在C欄，故C(欄)3(C是在第3欄)。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例