2012年10月19日 星期五

Excel-用公式記錄時間不會變動(使用循環參照)

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

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

要如何來設計呢?參考以下的做法:

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

(一) 製作資料輸入表單

輸入表單參考下圖:

image

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

image

接下來要做查表工作:

儲存格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 陣列,因此計算出的總和即為要求的數量。

 

【補充資料】

函數詳細資料,請參閱微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

沒有留言:

張貼留言

檢視其他文章

好康東東