錦子老師

沙悟淨：「師兄，最近主管希望我做一份整月份的注塑10個機台異常報表，主管是要求說：

1.他要知道某一天，一共有哪些機台出問題(日期對機台)。

2.他要知道某個機台，在哪幾天發生過什麼問題(機台對日期)。

3.統計每個機台各種問題次數(機台對問題)。

4.統計各個問題次數(BY月、BY季、BY年)。

發生的問題可能有色差、毛邊、不飽模’缺料」。

孫悟空：「悟淨，要解決這些問題需要師兄一步一步慢慢的告訴你。」

問題一：某一天，一共有哪些機台出問題(日期對機台)。

點取A1儲存格，再框選A1:K366儲存格範圍(按CTRL+A鍵)。

點取「公式 > 從選取範圍建立」圖示。

在【以選取範圍建立名稱】對話方塊，點取「頂端列」核取方塊，使其打勾。-

點取「確定」鈕，設定日期、注塑機1~10等11個範圍名稱及代表範圍。

點取N1儲存格，輸入欲查詢的日期，本例為：2018/1/31。

點取M3儲存格，輸入公式「=IFERROR(OFFSET($A$1,,SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN(B:K),""),ROW()-2)-1),"")」後，按Ctrl + Shift + Enter鍵完成輸入，並向下複製到M4:M12儲存格(這是由於只有10台，故最多10列)。

【公式說明】

1  MATCH($N$1,日期,0) 傳回N1儲存格內容位於日期範圍中的第幾列。

2  OFFSET($A$1, MATCH($N$1,日期,0),1,1,10) 從A1儲存格移動1運算結果列數，移動1欄後，框選1列10欄範圍。

3  IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN(B:K),"") 如果2框選範圍儲存格有不等於空白的儲存格，傳回其位於B欄~K欄中的那一欄。

4  SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN(B:K),""),ROW()-2) 傳回3運算結果中目前儲存格列號減2的最小欄位記錄，若第3列則傳回最小欄位值為1的欄位，依此類推。

5  OFFSET($A$1,,SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN (B:K),""),ROW()-2)-1) 傳回從A1儲存格開始移動4計算結果的欄數之儲存格內容。

6  IFERROR(OFFSET($A$1,,SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"", COLUMN(B:K),""),ROW()-2)-1),"") 如果5運算出現錯誤則不填入資料。

點取N3儲存格，輸入公式「=IFERROR(OFFSET($A$1,MATCH($N$1,日期,0),SMALL(IF(OFFSET($A$1,MATCH($N$1,日期,0),1,1,10)<>"",COLUMN(B:K),""), ROW()-2)-1),"")」後，按Ctrl + Shift + Enter鍵完成輸入，並向下複製到N4:N12儲存格(這是由於只有10台，故最多10列)。

【公式說明】

1  MATCH($N$1,日期,0) 傳回N1儲存格內容位於日期範圍中的第幾列。

2  OFFSET($A$1, MATCH($N$1,日期,0),1,1,10) 從A1儲存格移動1運算結果列數，移動1欄後，框選1列10欄範圍。

3  IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN(B:K),"") 如果2框選範圍儲存格有不等於空白的儲存格，傳回其位於B欄~K欄中的那一欄。

4  SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN(B:K),""),ROW()-2) 傳回3運算結果中目前儲存格列號減2的最小欄位記錄，若第3列則傳回最小欄位值為1的欄位，依此類推。

5  OFFSET($A$1,MATCH($N$1,日期,0),SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"",COLUMN (B:K),""),ROW()-2)-1) 傳回從A1儲存格開始移動1計算結果的列數與4計算結果的欄數之儲存格內容。

6  IFERROR(OFFSET($A$1,MATCH($N$1,日期,0),SMALL(IF(OFFSET($A$1, MATCH($N$1,日期,0),1,1,10)<>"", COLUMN(B:K),""),ROW()-2)-1),"") 如果5運算出現錯誤則不填入資料。