1、第五章 員工檔案資料管理重要知識(shí)點(diǎn)：1、YEAR()、MONTH()、NOW函數(shù)的運(yùn)用2、COUNTIF()、IF()函數(shù)運(yùn)用3、文本函數(shù)LEN、LEFT、RIGHT、MID4、添加和編輯控件5、插入超鏈接.員工檔案資料管理本章主要引見如何運(yùn)用EXCEL制造員工檔案資料管理，加強(qiáng)企業(yè)對員工的管理。本章首先引見了員工檔案資料表、員工工齡管理表及員工信息查詢表相關(guān)表格的內(nèi)容的編輯與制造，然后引見如何制造一個(gè)操作系統(tǒng)窗口界面，用戶可以經(jīng)過窗口中的控件功能來完成與已有表格之間的切換鏈接，從而方便用戶對不同表格數(shù)據(jù)信息的查看，使制造的表格更加的規(guī)范化，操作起來更加的方便快捷。.實(shí)例完成圖.5.1 創(chuàng)建員

2、工檔案資料表.5.1 創(chuàng)建員工檔案資料表設(shè)置數(shù)據(jù)有效性單擊“數(shù)據(jù)選項(xiàng)卡下“數(shù)據(jù)工具組里的“數(shù)據(jù)有效性選項(xiàng).5.1 創(chuàng)建員工檔案資料表設(shè)置身份證輸入格式“設(shè)置單元格格式下單擊“文本選項(xiàng).5.1 創(chuàng)建員工檔案資料表YEAR(serial_number)前往某日期對應(yīng)的年份。前往值為 1900 到 9999 之間的整數(shù)。Serial_number為一個(gè)日期值，其中包含要查找年份的日期。應(yīng)運(yùn)用 DATE 函數(shù)輸入日期，或者將函數(shù)作為其他公式或函數(shù)的結(jié)果輸入。例如，運(yùn)用函數(shù) DATE(2021,5,23) 輸入 2021 年 5 月 23 日。假設(shè)日期以文本方式輸入，那么會(huì)出現(xiàn)問題。.5.1 創(chuàng)建員工檔

3、案資料表NOW( )前往當(dāng)前日期和時(shí)間所對應(yīng)的序列號(hào)。假設(shè)在輸入函數(shù)前，單元格的格式為“常規(guī)，那么結(jié)果將設(shè)為日期格式。.5.1 創(chuàng)建員工檔案資料表DATE(year,month,day)前往代表特定日期的序列號(hào)。Year參數(shù) year 可以為一到四位數(shù)字Month代表一年中從 1 月到 12 月一月到十二月各月的正整數(shù)或負(fù)整數(shù)。 假設(shè) month 大于 12，那么 month 從指定年份的第一個(gè)月起累加月份數(shù)。例如，DATE(2021,14,2) 前往代表 2021 年 2 月 2 日的序列號(hào)。 假設(shè) month 小于 1，那么用 month 減去指定年份的月份數(shù)，再從該年份的第一個(gè)月起往向上

4、累加，例如，DATE(2021,-3,2) 前往代表 2007 年 9 月 2 日的序列號(hào)。Day代表一月中從 1 日到 31 日各天的正整數(shù)或負(fù)整數(shù)。 假設(shè) day 大于指定月份的天數(shù)，那么 day 將從該月份的第一天開場累加天數(shù)。例如，DATE(2021,1,35) 前往代表 2021 年 2 月 4 日的序列號(hào)。 假設(shè) day 小于 1，那么用 day 減去該月份的天數(shù)，再從該月份的第一天開場往上累加。例如，DATE(2021,1,-15) 前往代表 2007 年 12 月 16 日的序列號(hào)。.5.1 創(chuàng)建員工檔案資料表文本函數(shù)LEN(text)LEN 前往文本字符串中的字符數(shù)。Text

5、 是要查找其長度的文本。空格將作為字符進(jìn)展計(jì)數(shù)例如：A2為“Phoenix=LEN(A2) 第一個(gè)字符串的長度 (11).5.1 創(chuàng)建員工檔案資料表MID(text,start_num,num_chars)前往文本字符串中從指定位置開場的特定數(shù)目的字符，該數(shù)目由用戶指定Text 是包含要提取字符的文本字符串。Start_num 是文本中要提取的第一個(gè)字符的位置。文本中第一個(gè)字符的 start_num 為 1，以此類推。Num_chars 指定希望 MID 從文本中前往字符的個(gè)數(shù)。例如：A2為“Fluid Flow=MID(A2,1,5)上面字符串中的 5 個(gè)字符，從第一個(gè)字符開場 (Fluid

6、)=MID(A2,7,20)上面字符串中的 20 個(gè)字符，從第七個(gè)字符開場 (Flow).5.1 創(chuàng)建員工檔案資料表LEFT (text,num_chars)根據(jù)所指定的字符數(shù)，LEFT 前往文本字符串中第一個(gè)字符或前幾個(gè)字符。Text 是包含要提取的字符的文本字符串。Num_chars 指定要由 LEFT 提取的字符的數(shù)量。Num_chars 必需大于或等于零。假設(shè) num_chars 大于文本長度，那么 LEFT 前往全部文本。假設(shè)省略 num_chars，那么假設(shè)其值為 1。例如：A2=“Sale Price=LEFT(A2,4)第一個(gè)字符串中的前四個(gè)字符 (Sale).5.1 創(chuàng)建員工

7、檔案資料表RIGHT(text,num_chars)RIGHT 根據(jù)所指定的字符數(shù)前往文本字符串中最后一個(gè)或多個(gè)字符。Text 是包含要提取字符的文本字符串。Num_chars 指定要由 RIGHT 提取的字符的數(shù)量。Num_bytes 按字節(jié)指定要由 RIGHTB 提取的字符的數(shù)量。注解Num_chars 必需大于或等于零。 假設(shè) num_chars 大于文本長度，那么 RIGHT 前往一切文本。 假設(shè)省略 num_chars，那么假設(shè)其值為 1。 .5.1 創(chuàng)建員工檔案資料表TEXT(value,format_text)將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本Value 為數(shù)值、計(jì)算結(jié)果為數(shù)字

8、值的公式，或?qū)Π瑪?shù)字值的單元格的援用。Format_text 是作為用引號(hào)括起的文本字符串的數(shù)字格式。經(jīng)過單擊“設(shè)置單元格格式對話框中的“數(shù)字選項(xiàng)卡的“類別框中的“數(shù)字、“日期、“時(shí)間、“貨幣或“自定義并查看顯示的格式，可以查看不同的數(shù)字格式。注解Format_text 不能包含星號(hào) (*)。 例如：=Text(“19800310,0000-00-00) 1980-03-10.5.1 創(chuàng)建員工檔案資料表MOD(number,divisor)前往兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)一樣Number為被除數(shù)。Divisor為除數(shù)。注解假設(shè) divisor 為零，函數(shù) MOD 前往錯(cuò)誤值 #DIV/

9、0!。例如：=MOD(3, 2) 3/2 的余數(shù) (1).5.1 創(chuàng)建員工檔案資料表利用公式計(jì)算性別=IF(MOD(MID(E3,17,1),2)=0,女,男).5.1 創(chuàng)建員工檔案資料表利用公式計(jì)算出出生年齡=TEXT(MID(E3,7,8),0000-00-00).5.1 創(chuàng)建員工檔案資料表計(jì)算員工年齡=YEAR(NOW()-YEAR(F3).5.1 創(chuàng)建員工檔案資料表統(tǒng)計(jì)員工人數(shù)【設(shè)置格式】在自定義選項(xiàng)中，在“類型文本框中輸入“#人.5.1 創(chuàng)建員工檔案資料表輸入統(tǒng)計(jì)函數(shù)公式=COUNTIF(D3:D17,財(cái)務(wù)部)=COUNTIF(D3:D17,銷售部)=COUNTIF(D3:D17,消

10、費(fèi)部)=COUNTIF(D3:D17,行政部)=COUNTIF(C3:C17,男)=COUNTIF(C3:C17,“女)=SUM(K3:K6)=M3/M4.5.2制造員工工齡管理表隨著的實(shí)施，公司將給予任職一年以上的員工以天數(shù)不等，并且不扣除薪資的特別休假。.5.2制造員工工齡管理表新建單元格款式“開場選項(xiàng)卡下，單擊“款式選項(xiàng)組中的“單元格款式按鈕，在展開的列表中單擊“新建單元格款式選項(xiàng)。在“款式名文本框中輸入創(chuàng)建的新款式稱號(hào)，單擊“格式按鈕.5.2制造員工工齡管理表計(jì)算員工工齡=YEAR(NOW()-YEAR(F3)-IF(F3=DATE(YEAR(F3),MONTH(NOW(),DAY(N

11、OW(),1,0)輸入的公式中嵌套的IF函數(shù)內(nèi)容是表示判別入職日能否比當(dāng)前日期大，假設(shè)大那么工齡減1，假設(shè)小那么不減.5.2制造員工工齡管理表計(jì)算年假天數(shù)年假天數(shù)=工齡*2=IF(G3=1,G3*2,0).5.2制造員工工齡管理表設(shè)置高級(jí)挑選要求：工齡大于5且學(xué)歷是本科選擇“數(shù)據(jù)選項(xiàng)卡下的，單擊“排序和挑選選項(xiàng)組中的“高級(jí)按鈕.5.3 制造員工信息查詢表定義稱號(hào)選定A3：A17 在“公式選項(xiàng)卡單擊“定義的稱號(hào).5.3 制造員工信息查詢表設(shè)置數(shù)據(jù)有效性選中B2單元格，在“數(shù)據(jù)選項(xiàng)卡下單擊“數(shù)據(jù)工具選項(xiàng)組中的“數(shù)據(jù)有效性，“允許下拉下選擇序列，“來源文本框中輸入“=員工編號(hào).5.3 制造員工信息查

12、詢表單擊B2單元格右側(cè)的下拉按鈕，在展開的列表中可選擇需求輸入的員工編號(hào).5.3 制造員工信息查詢表援用員工姓名、年齡等=VLOOKUP(B2,員工工齡管理!A2:H17,2,0)=VLOOKUP(B2,人事檔案資料表!A2:H17,7,0).5.3 制造員工信息查詢表顯示數(shù)據(jù)信息.5.4 制造員工檔案管理系統(tǒng)界面.5.4 制造員工檔案管理系統(tǒng)界面1、新建任務(wù)表，并在“視圖選項(xiàng)卡下取消勾選“顯示/隱藏選項(xiàng)組中“網(wǎng)絡(luò)線復(fù)選框.5.4 制造員工檔案管理系統(tǒng)界面2、插入背景圖片.5.4 制造員工檔案管理系統(tǒng)界面3、調(diào)整圖片大小至適宜位置.5.4 制造員工檔案管理系統(tǒng)界面4、設(shè)置圖片款式及外形選中圖片，在“格式選項(xiàng)卡下單擊“圖片款式選項(xiàng)組中的“金屬框架款式選中圖片，單擊“圖片款式選項(xiàng)組中“圖片外形按鈕，選擇“圓角矩形選項(xiàng).5.4 制造員工檔案管理系統(tǒng)界面5、插入文本框單擊“插入下“文本選項(xiàng)組中“文本框的下拉按鈕，在展開的列表中單擊“橫排文本框選項(xiàng)在文本框中輸入文字，并設(shè)置字體的大小顏色等根本屬性.5.4 制造員工檔案管理系統(tǒng)界面6、再添加2個(gè)文本框，顯示效果如下.5.4 制造員工檔案管理系統(tǒng)界