公司員工信息表管理

1、課題：公司員工信息表管理教學(xué)要求：1掌握數(shù)據(jù)的有效性、快速輸入、函數(shù)的使用方法。2.掌握數(shù)據(jù)的分類匯總、數(shù)據(jù)透視表方法。課時(shí)安排： 2 學(xué)時(shí)教學(xué)重點(diǎn)、難點(diǎn)：1掌握數(shù)據(jù)的有效性、快速輸入、函數(shù)的使用方法。2. 掌握數(shù)據(jù)的分類匯總、數(shù)據(jù)透視表方法。教學(xué)環(huán)境及教具準(zhǔn)備：電腦 1 臺(tái)/ 人，裝有 MicrosoftOffice2003 辦公軟件。教學(xué)過程：項(xiàng)目：公司員工信息表管理1、項(xiàng)目提出本項(xiàng)目以人資源信息表的管理為例介紹快速輸入數(shù)據(jù)、 分析數(shù)據(jù)和查詢數(shù)據(jù)的方法和經(jīng) 驗(yàn)。通過該項(xiàng)目，學(xué)習(xí)和掌握如何對(duì)數(shù)據(jù)有效性、前綴格式填充、mid、 today、vlookup 函數(shù)、數(shù)據(jù)分類匯總及數(shù)據(jù)透視表的運(yùn)用。

2、2、項(xiàng)目要求與分析要求：（ 1）啟動(dòng) Excel2003 軟件，打開素材文件。（2）在 “北京分公司員工信息表” 工作表中完成：1）用填充柄方法在 A5單元格開始的列中輸入數(shù)據(jù)：“0001 ”、“0002”、“0003”、 “0012”。2）運(yùn)用前綴格式（前綴都是統(tǒng)一為：BJX ）的方法在 B列輸入如效果圖所示工作證號(hào)。3）運(yùn)用數(shù)據(jù)有效性，設(shè)置 C 列中“姓名”的數(shù)據(jù)有效性為 “員工登記表”中的姓 名，并通過下拉框選擇如效果圖所示的姓名，完成“姓名”一列數(shù)據(jù)的輸入。4）運(yùn)用數(shù)據(jù)有效性，設(shè)置 D 列部門的有效性數(shù)據(jù)為序列“研發(fā)部，市場部，銷售 部”，并通過下拉框選擇完成“部門”一列數(shù)據(jù)的輸入。5

3、）在 單 元 格 F15 、 F16 分 別 輸 入 下 列 身 份 證 號(hào) 碼 ：6）運(yùn)用函數(shù)：利用身份證號(hào)碼計(jì)算出生年月、年齡及性別。&0001e1)0027boos驢弓丁作訐號(hào)O?co4E阪5：0)00611 twi3_t)ooa13 r(mA ocioIF V1011iTbciatjiiii王罐鋒EJX238錢樂EJX333齊曉勵(lì)EJX334王鼎晶BJX234侗祖棗EJ：142ft 上戸1鄒柯押妍EJX333陶著光EJX141弓牘取EJX237劉柄尤FJX239 M 紅E；JX2丸安為軍丈丈11010819

4、7611028521Fn0106L975U258CK0108L38JlL248&L11012070367217j01U9LJ711124b4 1101OSL37DO01OE52 ，201220Ld55ara3S350109L97212168G32 022i2nrsrKn24Fiz 1020219BD1218CEL 31D1061 972110289241976/1L/023413T5/11/2535iyyu/n.1301972/07/031976/1L/L219TC/08AD40l9f.5；Q7/O3151972/12/U127S/0E/D21980/12/131972/11/02lOlUBg

5、OaOSSeE? 196S/03/0B3043衣亠EL-亠 t- 用至 UI - -分析：該項(xiàng)目涉及到數(shù)據(jù)有效性、前綴格式填充、mid、today、vlookup函數(shù)、分類匯總、數(shù)（3）在“信息查詢表”工作表中完成:1）運(yùn)用數(shù)據(jù)有效性，定義 C3單元格的姓名為“北京分公司員工信息表”工作表的 姓名，并通過下拉框選擇。2）在C4、C5、C6單元格運(yùn)用 VLOOKUP函數(shù)，根據(jù)姓名，在“北京分公司員工 信息表”中查詢數(shù)據(jù)，并返回該員工的年齡、文化程度和職務(wù)。（4）在“員工登記表”工作表中，對(duì)數(shù)據(jù)清單利用高級(jí)篩選，篩選出性別為女年齡在30-40之間文化程度為“大學(xué)本科”或者民族是回族的所有記錄，條件

6、區(qū)域：S4： W6，篩選結(jié)果放至以Q9單元格為左上角的區(qū)域。（5） 在“sheetl”工作表中，利用分類匯總求出各種文化程度的平均工資情況。（先對(duì) 文化程度遞增排序，再使用分類匯總）（6）在“sheet2”工作表中，利用數(shù)據(jù)透視表功能求出各部門中各職務(wù)的文化程度分布情況。將數(shù)據(jù)透視表放在B16單元格起始的位置（7 ）按原文件名保存文件。效果圖：FCH:據(jù)透視表等操作。3、項(xiàng)目實(shí)現(xiàn)（1）啟動(dòng)Excel2003應(yīng)用程序，打開素材文件。2)單擊“北京分公司員工信息表”工作表：1) 雙擊 A5 單元格，輸入：0001(0001 前面要加英文狀態(tài)下的單引號(hào) )，按回車， 利用填充柄將數(shù)據(jù)復(fù)制到 A16

7、單元格。2) 選擇 B5 ：B16 單元格區(qū)域，單擊右鍵，選擇“單元格格式”命令，在“數(shù)字” 選項(xiàng)卡中選擇“自定義”，將“ G/通用格式”修改為：”BJX”# ,(其中雙引號(hào) 不能省略，雙引號(hào)括起來的表示統(tǒng)一的前綴，該題的前綴為BJX，后面的#表示后面輸入的是數(shù)字，如果輸入的是文本，就將#號(hào)改為)，然后回到 B5 單元格，依次輸入 BJX 后面的數(shù)字就可以了，如 B5 單元格輸入 141， B6 單元 格輸入238,，前綴BJX會(huì)自動(dòng)顯示出來。3)單擊“員工登記表”工作表，選擇C5: C36，選擇菜單欄“插入一名稱一定義”命令，在當(dāng)前工作簿中的名稱欄中輸入“name”(其實(shí)輸入什么不重要，主要

8、是起個(gè)名字) ，接著回到“北京分公司員工信息表”工作表，選擇C5：C16 單元格區(qū)域， 選擇菜單欄 “數(shù)據(jù)有效性” ，設(shè)置有效條件允許為 “序列”， 來源欄中輸入“ =name”，確定，這時(shí)在 C5: C16中每個(gè)單元格旁邊都會(huì)有一 個(gè)下拉按鈕，單擊該按鈕在下拉框中依次選擇輸入姓名。4)單擊 “北京分公司員工信息表”工作表，選擇 D5： D16 單元格區(qū)域，選擇菜 單欄“數(shù)據(jù)有效性” ，設(shè)置有效條件允許為 “序列”，來源欄中輸入： 研發(fā)部， 市場部，銷售部(注意：逗號(hào)必須是英文狀態(tài)下的) ，確定，這時(shí)在 D5： D16 中每個(gè)單元格旁邊都會(huì)有一個(gè)下拉按鈕， 單擊該按鈕在下拉框中依次選擇輸入 姓

9、名。5)在“北京分公司員工信息表” 工作表中， 單擊 F15 單元格， 輸入(身份證號(hào)前要輸單引號(hào)) ，用同樣的方法在 F16 單 元格輸入另一身份證號(hào)碼。6)1 8位身份證號(hào)碼： 16位為地區(qū)代碼， 710位為出生年份( 4位)， 1112位 為出生月份， 1314 位為出生日期，第 1517位為順序號(hào)，并能夠判斷性別， 奇數(shù)為男，偶數(shù)為女，第 18位為校驗(yàn)碼。出生日期： = MID(F5,7,4)&/&MID(F5,11,2)&/&MID(F5,13,2)年齡：=(TODAY( )-G5)/365)性別：= IF(MOD(V ALUE(MID(F5,

10、15,3),2)=0, 女, 男 )幾個(gè)函數(shù)說明：IF 函數(shù)：條件測(cè)試函數(shù)格式： IF( logical_test, value_if_true, value_if_false )功能：當(dāng)邏輯測(cè)試條件 logical_test 為 TRUE 時(shí)，返回 value_if_true 的結(jié)果，否則 返回 value_if_false 的結(jié)果。任意位置取字符串函數(shù) MID格式： MID ( text, start_num, num_chars) 功能：返回文本字符串 text 中從 start_num 位置開始往后連續(xù)取 num_chars 個(gè)字符。& (字符連接符) ， 將兩個(gè)文字連接起來生成一個(gè)新

11、文字。如： “多媒體” & “計(jì)算機(jī)”的運(yùn)算結(jié)果是 “多媒體計(jì)算機(jī)” 。取余數(shù)函數(shù) MOD字符轉(zhuǎn)數(shù)值函數(shù) VALUE系統(tǒng)日期函數(shù) TODAY （）格式：TODAY （）功能：返回計(jì)算機(jī)系統(tǒng)內(nèi)部時(shí)鐘當(dāng)前日期的序列數(shù)。（3）在“信息查詢表”工作表中完成：1）步驟與（2）的第三小題相似，參照。2）在C4、C5、C6單元格運(yùn)用 VLOOKUP函數(shù)，輸入如下公式: 年齡：=VLOOKUP（C3,北京分公司員工信息表 ！C5:H16,6,0） 文化程度：=VLOOKUP（C3,員工登記表!C5:K36,9,0）=VLOOKUP（C3,北京分公司員工信息表 ！ C5:I16,7,O）（4）在“員工登記表”工

12、作表中，條件區(qū)域和篩選結(jié)果如下圖所示。RSTVVXYZ性別性別民族民族年齡年齡年齡文化提度文化提度回女=3040大學(xué)本科No_姓名姓名性別性別民族民族出生年月出生年月年靜年靜工作日期工作日期工齡工齡文化程度1錢躍之北京市1375-11-25301995-9111大學(xué)本科3陳13男回曉西城1977-11-1281393-12412研究生S于衛(wèi)平回寧夏朮寧1S52-3-6541965-12-13S尢學(xué)本科7任水濱立河北青縣1570-11-13519-12-111大學(xué)本科2E岳晉生男回四川謹(jǐn)寧1952-11-1S31969-7-137研究生31顏紅女回1972/11/02331997-S-10g研究

13、生（5）在“ sheetT工作表中，將光標(biāo)放在數(shù)據(jù)區(qū)域中任一個(gè)單元格，先對(duì)文化程度關(guān)鍵字 按升序排序，然后選擇“數(shù)據(jù)一分類匯總”菜單命令，分類字段：文化程度；匯總方式：平 均值；匯總項(xiàng)：平均工資。確定。如圖所示。分類匯總效果圖人力酬源信需表Mil職務(wù)性刖勺齡工將本科3000帀場邸王嚴(yán)55與朮452WW市場部煎目經(jīng)良 矣為軍里39本科2300芥料平均悄29W工旨師王新力里2?1MW0工捏師女33博士博士平均值3400餘娟女38犬專32WJH程巧陳坍為2iW莓小琳女25大專2*00市場郵遼朋男30大專2000大專平均值2330淫理異述一31 (W會(huì)計(jì)白俺女40碩士3? 00確士平均值2EOO2&50（6）在“ sheet2”工作表中，將光標(biāo)放在數(shù)據(jù)區(qū)域中任一個(gè)單元格，選擇“數(shù)據(jù)一數(shù)據(jù)透視表和數(shù)據(jù)透視圖”菜單命令，單擊兩次下一步，在步驟之3單擊“布局”按鈕，將“部門”拖放在行字段，“文化程度”拖放在列字段，數(shù)據(jù)項(xiàng)為