贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

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

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

學生的基本資料如下圖,首先,選取資料表中所有的資料(例如儲存格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應該要尋找完全符合還是大約符合的值。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(6) 人氣()


留言列表 (6)

發表留言
  • 阿宏
  • 我按照您所說的:
    在[公式]的「計算選項」區中,勾選「啟用反覆運算」,最高次數設定為「1」
    但我每次打開檔案或是在其他儲存格輸入內容時,所有NOW()還是全部都變成當下的時間了…怎麼會這樣呢?
  • 晴
  • 以下這段公式也必需輸入才行

    儲存格F2:=IF(A2="","",IF(F2="",NOW(),1*F2))
  • 紋
  • 想請問如果只想固定某個儲存格
    有方法可以解決嗎 該怎麼做呢?
    謝謝!!
  • WEI
  • 您好,請教一下。
    這套函數,如果要再GOOGLE的試算表也能試用嗎?
    我測試了好幾個方式都無法做到我想要的。
    我想在B1輸入任何數值後,A1出現當天月、日。
    但隔天開啟的時候她的TODAY()都會跑為當天開啟日期。
    假設3/14輸入,3/15開啟的時候A1就變成3/15了
    =IF(B1="","",IF(H1="",TODAY(),H1))
    這是我目前的函數,我也試過了改循環參數,但還是不行呢...
  • 澤為 宋
  • 您好,不好意思帽妹的請教一下。
    這套函數,如果要再GOOGLE的試算表也能試用嗎?

    我想在B1輸入任何數值後,A1出現日期。
    但隔天開啟的時候她的TODAY()都會跑為當天開啟日期。
    假設3/14輸入,3/15開啟的時候A1就變成3/15了。

    =IF(B1="","",IF(H1="",TODAY(),H1))
    這是我目前的函數,我也試過了改循環參數,但還是不行呢...
    麻煩您了。

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼