錦子老師

光華大學排課人員詢問錦子老師，錦子老師我們學校有15個班級，是否可以將每個班級條列式的課表，如下圖所示，編排成一個月(一學期)的課表要如何做。

這種編排方式的轉換，要使用到二個函數SUMPRODUCT、OFFSET函數。

步驟1：按CTRL+A鍵將所有條列式的課表項目合部框選起來。

步驟2：點取「公式 > 從選取範圍建立」指令，如下圖所示。

步驟3：在【以選取範圍建立名稱則】對話方塊，點取「頂端列」核取方塊，使其打勾，如下圖所示。

步驟4：點取「確定」鈕。

步驟5：新增一張工作表，建立整個月份(學期)的資料，如下圖所示。

A欄是七個節次，而第一列為日期，第二列為日期是屬於星期幾。

步驟6：點取B3儲存格輸入公式「=IFERROR(OFFSET(工作表1!$A$1,SUMPRODUCT((節次=工作表2!$A3)*(星期=工作表2!B$2)*ROW(班級))-1,1)&CHAR(10)&OFFSET(工作表1!$A$1,SUMPRODUCT((節次=工作表2!$A3)*(星期=工作表2!B$2)*ROW(科目))-1,3)&CHAR(10)&OFFSET(工作表1!$A$1,SUMPRODUCT((節次=工作表2!$A3)*(星期=工作表2!B$2)*ROW(講師))-1,4),"")」後，複製到整個月份(學期)的所有儲存格，結果如下圖所示。

目前只是以一個班級做說明，如果要做多個班級對應多張工作表，則可以在每張工作表A2儲存格輸入班級名稱，再將公式改變為「=IFERROR(OFFSET(工作表1!$A$1,SUMPRODUCT((班級=$A$2)*(節次=工作表2!$A6)*(星期=工作表2!G$2)*ROW(科目))-1,3)&CHAR(10)&OFFSET(工作表1!$A$1,SUMPRODUCT((班級=$A$2)*(節次=工作表2!$A6)*(星期=工作表2!G$2)*ROW(講師))-1,4),"")」，結果如下圖所示。

【公式說明】

SUMPRODUCT((節次=工作表2!$A3)*(星期=工作表2!B$2)*ROW(班級))-1 找尋節次欄中等於「一」且星期欄為「六」的班級欄記錄由於第一列是標題故須要減一。

CHAR(10) 強迫換行，必須設定儲存格自動換列才可以顯示效果。

OFFSET(工作表1!$A$1,SUMPRODUCT((節次=工作表2!$A3)*(星期=工作表2!B$2)*ROW(班級))-1,1) 抓取工作表1的A1儲存格算起差距列(SUMPRODUCT((節次=工作表2!$A3)*(星期=工作表2!B$2)*ROW(班級))-1)與差距欄(由於班級在B欄與A欄只差1欄，故輸入1)。