close

2398

每月不良數統計-OFFSETINDIRECTCONCATSUMIFS

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

image

image

image

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

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

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

image

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

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

image

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

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

image

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

image

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

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

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

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

image

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

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

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

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

image

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

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

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

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

image

還是顯示原本資料是因為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)

image

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

1064EXCEL快速填入應用

2017.11.24

579詳解 Excel 神奇的快速填入功能

2020.02.06

907知道嗎?您與Excel大神只差一步-快速填入

2017.11.11

563Excel 2013中將字串拆分到不同儲存格的方法

2017.11.07

560四個典型應用實例教我們學會Excel 2013快速填入功能()

2017.11.06

560四個典型應用實例教我們學會Excel 2013快速填入功能()

2017.11.08

560四個典型應用實例教我們學會Excel 2013快速填入功能()

2017.11.09

560四個典型應用實例教我們學會Excel 2013快速填入功能()

2017.10.20

541圖文實例詳解Excel 2013快速填充獨特的功能

2021.01.28

2129作弊神器-45招帶我們玩轉職場Excel不看白不看19Excel2013新技能快速填入

2018.01.10

618批量生成超連結

2017.05.30

456Excel中使用公式來實現資料快速錄入的3種方法

2017.12.28

606Excel中根據中國身份證字號自動生成生日和性別的方法

2020.02.11

909這樣的Excel儲存格合併,聽說會的人只有1%-基礎篇

2020.08.20

2008大批量地址處理

2019.12.19

Y2017西元2017年相關範例

2019.12.19

Y2021西元2021年相關範例

2019.12.19

Y2020西元2020年相關範例

 


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()