建立员工资料库

第四章建立員工資料庫主題介紹員工資料表員工基本資料檔員工特休年資計算表員工請假特休統計表4-1員工資料表4-1-1建立範圍名稱4-1-2插入註解4-1-3編輯註解4-1-4複製註解4-1-5自訂頁首資訊4-1-6隱藏工作表4-1-7取消隱藏工作表4-1員工資料表4-1-1建立範圍名稱建立範圍名稱可將特定範圍事先給一個名字，等到要使用參照位置時，再直接予以套用1選擇A1：A3儲存格2執行「插入／名稱／建立」指令4-1員工資料表4-1-1建立範圍名稱2按「確定」鈕1勾選「頂端列」4-1員工資料表4-1-1建立範圍名稱3勾選「頂端列」後按「確定」鈕2執行「插入／名稱／建立」指令1按住「Ctrl」鍵，選取不連續範圍資料如圖示4-1員工資料表4-1-1建立範圍名稱1選取「M2」儲存格2執行「資料／驗證」指令結果4-1員工資料表4-1-1建立範圍名稱1切換到「設定」標籤2選取「清單」3直接輸入「＝性別」4按「確定」鈕4-1員工資料表4-1-1建立範圍名稱性別出現清單選取M3儲存格執行此指令4-1員工資料表4-1-1建立範圍名稱1切換到「設定」標籤2選取「清單」3按此鈕4-1員工資料表4-1-1建立範圍名稱執行「插入／名稱／貼上」指令4-1員工資料表4-1-1建立範圍名稱2按「確定」鈕1選取「血型」名稱4-1員工資料表4-1-1建立範圍名稱按此鈕回到「資料驗證」對話視窗4-1員工資料表4-1-1建立範圍名稱按「確定」鈕同樣出現「＝血型」結果4-1員工資料表4-1-1建立範圍名稱搭配儲存格自訂格式，可顯示正確的格式數值TIPS4-1員工資料表4-1-2插入註解1選取N2儲存格2執行「插入／註解」指令4-1員工資料表4-1-2插入註解出現註解方塊，在插入點之後輸入文字4-1員工資料表4-1-2插入註解輸入文字完成後，點選任意儲存格結束編輯註解結果4-1員工資料表4-1-2插入註解當游標移到N2儲存格或者選取，則會出現註解4-1員工資料表4-1-3編輯註解2執行「插入／編輯註解」指令1選取N2儲存格4-1員工資料表4-1-3編輯註解1點選文字方塊外框2選擇字型大小「12」4-1員工資料表4-1-3編輯註解文字變大了選擇「黑色」填滿色彩4-1員工資料表4-1-3編輯註解文字框底色變成黑色選擇「黃色」文字色彩4-1員工資料表4-1-3編輯註解將游標移到方塊控制點4-1員工資料表4-1-3編輯註解按住滑鼠向上拖曳，縮小方塊範圍結果4-1員工資料表4-1-3編輯註解放開滑鼠則完成修改註解格式4-1員工資料表4-1-4複製註解1選取N2儲存格2執行「編輯／複製」指令4-1員工資料表4-1-4複製註解2執行「編輯／選擇性貼上」指令1選取I24儲存格4-1員工資料表4-1-4複製註解2按「確定」鈕1選擇「註解」按「是」結果4-1員工資料表4-1-4複製註解完成複製註解的工作4-1員工資料表4-1-5自訂頁首資訊執行「檢視／頁首及頁尾」指令4-1員工資料表4-1-5自訂頁首資訊自動開啟「版面設定」對話視窗並切換到「頁首／頁尾」按「自訂頁首」鈕4-1員工資料表4-1-5自訂頁首資訊1將插入點移到「中」空白處，並輸入公司名稱後反白選取2按「字型」圖示鈕4-1員工資料表4-1-5自訂頁首資訊2按「確定」鈕1選擇字型格式如圖示4-1員工資料表4-1-5自訂頁首資訊1按「Enter」輸入表首名稱後反白選取2按「字型」圖示鈕4-1員工資料表4-1-5自訂頁首資訊1選擇字型格式如圖示2按「確定」鈕4-1員工資料表4-1-5自訂頁首資訊按「確定」鈕結果按「預覽列印」鈕檢查設定的結果完成頁首資訊設定結果4-1員工資料表4-1-5自訂頁首資訊表格上方果真出現表首資訊按「下頁」鈕結果表格第二頁也出現相同表首資訊完成圖4-1員工資料表4-1-6隱藏工作表2執行「工作表／隱藏」指令1切換到「準則」工作表結果4-1員工資料表4-1-6隱藏工作表「準則」工作表不見了完成圖4-1員工資料表4-1-7取消隱藏工作表執行「工作表／取消隱藏」指令2按「確定」鈕1選擇「準則」工作表結果4-1員工資料表4-1-7取消隱藏工作表「準則」工作表又重現江湖完成圖4-2員工基本資料檔4-2-1自動判斷性別4-2-2使用表單輸入資料4-2-3自動搜尋資料4-2-4自訂條件篩選4-2員工基本資料檔4-2-1自動判斷性別身份證字號英文字母後方第一位數字代表著持有人的性別如果是「1」表示「男性」如果是「2」表示「女性」我們可以利用這個特性，加上函數的協助，只要輸入身份證字號後，就能自動判定性別4-2員工基本資料檔4-2-1自動判斷性別文字函數LEFT()函數RIGHT()函數4-2員工基本資料檔4-2-1自動判斷性別範例公式=IF(RIGHT(LEFT(身份證字號欄位,2),1)=1,男,女)假設身份證字號為A121212121，依上述公式推論：•LEFT(A21212121,2)＝A1•RIGHT(A,1)＝1•IF(1=1,男,女)＝男4-2員工基本資料檔4-2-1自動判斷性別2按「插入函數」鈕1選取D2儲存格4-2員工基本資料檔4-2-1自動判斷性別2按「確定」鈕1選取IF（）函數4-2員工基本資料檔4-2-1自動判斷性別2按「確定」鈕1分別在以下欄位輸入引數結果4-2員工基本資料檔4-2-1自動判斷性別複製公式到下方儲存格自動判定為男性4-2員工基本資料檔4-2-2使用表單輸入資料可是EXCEL只要執行一個指令，就可讓您擁有表單式的輸入環境4-2員工基本資料檔4-2-2使用表單輸入資料1選取A1儲存格2執行「資料╱表單」指令4-2員工基本資料檔4-2-2使用表單輸入資料以表單的方式顯示工作表第一筆資料按「新增」鈕4-2員工基本資料檔4-2-2使用表單輸入資料開啟「新增記錄」空白表單1在空白表單欄位中輸入新員工資料2按「關閉」鈕結果4-2員工基本資料檔4-2-2使用表單輸入資料工作表中新增一筆員工資料4-2員工基本資料檔4-2-3自動搜尋資料使用自動篩選的功能，輕鬆尋找當月壽星，而且自動篩選功能不只能執行單一條件篩選，還可以設定多重條件的篩選工作4-2員工基本資料檔4-2-3自動搜尋資料2執行「資料／篩選／自動篩選」指令1選取整列14-2員工基本資料檔4-2-3自動搜尋資料標題列各欄位出現下拉選項鈕2選擇「2」月份1按「月」欄位的選項鈕結果4-2員工基本資料檔4-2-3自動搜尋資料工作表僅顯示2月份出生的人4-2員工基本資料檔4-2-3自動搜尋資料2選擇「女」1按「性別」欄位的選項鈕結果4-2員工基本資料檔4-2-3自動搜尋資料工作表顯示2月份出生的女性4-2員工基本資料檔4-2-3自動搜尋資料2選擇「全部」月份1按「月」欄位的選項鈕結果4-2員工基本資料檔4-2-3自動搜尋資料執行「資料／篩選／自動篩選」指令工作表顯示所有女性的資料結果4-2員工基本資料檔4-2-3自動搜尋資料工作表回復顯示全部資料4-2員工基本資料檔4-2-4自訂條件篩選EXCEL的自動篩選功能還提供自訂條件的篩選1執行「資料／篩選／自動篩選」指令，按「年」欄位的選項鈕2選擇「自訂」4-2員工基本資料檔4-2-4自訂條件篩選按下拉清單鈕選擇「大於」選項1按下拉清單鈕選擇「70」年次2按「確定」鈕結果4-2員工基本資料檔4-2-4自訂條件篩選工作表顯示70年次以後出生的員工1按「戶籍地址」欄位的選項鈕2選擇「自訂」4-2員工基本資料檔4-2-4自訂條件篩選按下拉清單鈕選擇「開頭以」選項1直接輸入條件文字敘述「高雄縣」2按「確定」鈕結果4-2員工基本資料檔4-2-4自訂條件篩選工作表顯示70年次以後出生家住高雄縣的員工4-3員工特休年資計算表4-3-1日期函數的應用4-3-2定義範圍名稱4-3-3範圍名稱的應用4-3-4ISERROR()函數的應用4-3員工特休年資計算表4-3-1日期函數的應用NOW()函數TODAY()函數YEAR()函數MONTH()函數DAY()函數DATE()函數4-3員工特休年資計算表4-3-1日期函數的應用範例公式＝YEAR(NOW())-YEAR(到職日期)-IF(到職日期=DATE(YEAR(到職日期),MONTH(NOW()),DAY(NOW())),1,0)4-3員工特休年資計算表4-3-1日期函數的應用假設到職日期為2000/9/1，電腦系統當天日期的序列值為38208•YEAR(NOW())＝YEAR(38208)＝2004•YEAR(2000/9/1)2000原則上年資2004－2000＝4使用IF()函數判斷式，來判斷工作是否「滿」4年4-3員工特休年資計算表4-3-1日期函數的應用當月份數值12時，自動在年數加上1年；而天數當月的最大日時，也會自動在月份加上1個月•DATE(YEAR(2000/9/1),MONTH(NOW()),DAY(NOW()))＝DATE(2000,8,9)＝2000/8/9•IF(2000/9/1=2000/8/9,1,0)＝1因此實際年資應該4－1＝3已滿三年但未滿四年，以三年計算4-3員工特休年資計算表4-3-1日期函數的應用1在F2儲存格中輸入「＝」等號2按最近使用函數清單鈕3選擇「其他函數」4-3員工特休年資計算表4-3-1日期函數的應用3按「確定」鈕1選擇「日期及時間」類別2選取YEAR()函數4-3員工特休年資計算表4-3-1日期函數的應用2按「確定」鈕1在YEAR()函數引數中，直接輸入「NOW()」函數4-3員工特休年資計算表4-3-1日期函數的應用1在YEAR()函數後方輸入「－」減號2按此處插入YEAR()函數4-3員工特休年資計算表4-3-1日期函數的應用按此鈕選擇引數儲存格位置4-3員工特休年資計算表4-3-1日期函數的應用公式中也會同步顯示完整函數1選取E2儲存格作為引數2按此鈕回到函數視窗此處會顯示選取的引數4-3員工特休年資計算表4-3-1日期函數的應用按「確定」鈕這裡會顯示函數傳回的值4-3員工特休年資計算表4-3-1日期函數的應用1在YEAR()函數後方再輸入「－」減號2選擇IF()函數4-3員工特休年資計算表4-3-1日期函數的應用1分別在以下欄位輸入引數Logical_test：E2=DATE(YEAR(E2),MONTH(NOW()),DAY(NOW())Value_if_true：1Value_if_false：02按「確定」鈕結果4-3員工特休年資計算表4-3-1日期函數的應用按滑鼠右鍵執行此指令顯示公式計算的結果好像有錯誤4-3員工特休年資計算表4-3-1日期函數的應用3按「確定」鈕1切換到「數值」格式2選擇「通用格式」結果4-3員工特休年資計算表4-3-1日期函數的應用拖曳填滿下方儲存格顯示正確的數值4-3員工特休年資計算表4-3-2定義範圍名稱資料範圍是兩欄以上或是一個表格，就必須使用「定義」範圍名稱的方法2執行「插入／名稱／定義」指令1選取B2：C8儲存格4-3員工特休年資計算表4-3-2定義範圍名稱按鍵盤「DEL」鍵將預設名稱刪除自動顯示參照位置4-3員工特休年資計算表4-3-2定義範圍名稱1輸入自訂名稱2按「新增」鈕結果4-3員工特休年資計算表4-3-2定義範圍名稱按「關閉」鈕回到工作視窗完成新增範圍名稱4-3員工特休年資計算表4-3-3範圍名稱的應用需要參照資料範圍時，若直接選取範圍儲存格，必須注意參照位置，使用範圍名稱能輕易解決範例公式=VLOOKUP(年資欄位,特休天數,2,1)4-3員工特休年資計算表4-3-3範圍名稱的應用1選取G2儲存格並輸入「＝」號2選擇最近使用過的VLOOKUP()函數4-3員工特休年資計算表4-3-3範圍名稱的應用1將插入點移到搜尋值2選取年資欄位4-3員工特休年資計算表4-3-3範圍