2398 |
每月不良數統計-OFFSET、INDIRECT、CONCAT、SUMIFS |
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欄)。
以上就是今天主要學習的知識點,希望對大家有所幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~
部落格相關範例
2020.07.03 |
|
2017.11.24 |
|
2020.02.06 |
|
2017.11.11 |
|
2017.11.07 |
|
2017.11.06 |
|
2017.11.08 |
|
2017.11.09 |
|
2017.10.20 |
|
2021.01.28 |
|
2018.01.10 |
|
2017.05.30 |
|
2017.12.28 |
|
2020.02.11 |
|
2020.08.20 |
|
2019.12.19 |
|
2019.12.19 |
|
2019.12.19 |
留言列表