學不完．教不停．用不盡

最近幫學校要設計一個可以用學生證記錄上學遲到學生學號，可以自動產生學生基本資料及入校時間的資料表(參考下圖)：

進而產生每週遲到學校數量的統計分析表(參考下圖)：

要如何來設計呢？參考以下的做法：

學生的基本資料如下圖，首先，選取資料表中所有的資料(例如儲存格B1:F1000)，將其定義名稱：Data。

(一) 製作資料輸入表單

輸入表單參考下圖：

由於學號部分是要由條碼機掃描而來，可能會帶入含有「0」開頭的學號，所以必須將「學號」欄位的儲存格設定為「文字」：

接下來要做查表工作：

儲存格B2：=IF(ISBLANK($A2),"",VLOOKUP($A2,Data,COLUMN(B:B),FALSE))

複製儲存格B2，貼至儲存格B2:E2，再往下各列貼上。

VLOOKUP($A2&"",Data,COLUMN(B:B),FALSE)：其中 COLUMN(B:B)=2，表示要取 Data 中第 2 欄的資料，當往右複製時，會變成 COLUMN(C:C)=3、COLUMN(D:D)=4、…。

藉由 ISBLANK($A2) 的結果來判斷 A 欄儲存格是否為空白，如果是，則不顯內容(顯示空白)，否則顯示查詢結果。

接著要自動產生日期和時間，因為如果使用函數(TODAY, NOW, …)記錄時間，則會產生一個變動的時間，如果不想讓時間變動，你要藉助反覆運算功能。

所以，你要選取[檔案/選項]功能，在［公式］的「計算選項」區中，勾選「啟用反覆運算」，最高次數設定為「1」即可。

儲存格F2：=IF(A2="","",IF(F2="",NOW(),1*F2))

公式中參照到自己的儲存格，因此發生「循環參照」。在此強迫接受循環參照的結果，就可把日期和時間記錄下來而不再變動。

公式中未何要「1*F2」，乃要將空白的儲存格轉換為「0」(因為之後的分析要用到該欄的運算)。

如此，只要輸入學號，即會查到該學生的基本資料，並且自動填入當時之日期和時間。

(二) 製作每週統計分析表

在資料輸入表單中，選取含有欄位的資料範圍(例如儲存格A1:A1000)，按一下 Ctrl+Shift+F3 鍵，定義名稱：學號、班級、班級名稱、座號、姓名、入校時間。

接著，在另一個工作表來建立分析表。

儲存格B4：=SUMPRODUCT((班級名稱=$A4)*(入校時間>=B$2)*(入校時間<=B$3))

複製儲存格B4，貼至其他資料表中的儲存格。

此公式中：

(班級名稱=$A4)：找出符合班級(儲存格A4)的 True/False 陣列

(入校時間>=B$2)：找出入校時比起日大的 True/False 陣列

(入校時間<=B$3)：找出入學時間比迄日小的 True/False 陣列

經由 SUMPRODUCT 函數找出三者都符合的 True 陣列，再藉由「*」的動作，將 Truel/False 陣列轉成 1/0 陣列，因此計算出的總和即為要求的數量。

【補充資料】

函數詳細資料，請參閱微軟網站：

SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

VLOOKUP：http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx