計算機應用基礎(chǔ)課件第7章 Excel高級應用1-人事檔案管理分析

7.1項目分析7.2任務一：設(shè)計制作職工檔案模板任務要求與效果：1）美化并規(guī)范職工檔案模板。2）自定義單元格格式3）利用VLOOKUP函數(shù)動態(tài)抽取員工各項信息并根據(jù)身份證號碼提取歸屬地4）利用SUMIF函數(shù)的計算各學院所有新入職員工的安置費總和以及所得科研啟動資金的總額。知識點解析1.完善工作表格式2.VLOOKUP函數(shù)的應用VLOOKUP函數(shù)的功能：在數(shù)據(jù)區(qū)域的第一列中查找指定的數(shù)值，并返回數(shù)據(jù)區(qū)域當前行中指定列處的數(shù)值。VLOOKUP函數(shù)的語法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)3.SUMIF函數(shù)的應用SUMIF函數(shù)與COUNTIF函數(shù)用法相似，SUMIF主要是針對單個條件的統(tǒng)計求和。SUMIF函數(shù)的功能：用于按指定條件在查找區(qū)域進行查找，并返回對應于查找區(qū)域的另外一個數(shù)據(jù)區(qū)域中數(shù)值的和。SUMIF函數(shù)語法是：SUMIF(range，criteria，sum_range)SUMIF函數(shù)的參參數(shù)的函義：第一個參數(shù)：Range為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個參數(shù)：Criteria是求和條件，為確定哪些單元格將被相加求和的條件，其形式可以由數(shù)字、邏輯表達式等組成的判定條件。例如，條件可以表示為32、"32"、">32"或"木材"。第三個參數(shù)：Sum_range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。是真正進行求和計算的區(qū)域。

任務實現(xiàn)步驟1.制作員工檔案模板2.利用VLOOKUP函數(shù)動態(tài)抽取員工檔案各項信息3.利用VLOOKUP函數(shù)查詢出員工身份歸屬地4.利用SUMif函數(shù)統(tǒng)計各部門員工獲得的安置費用及科研啟動資金的總額總結(jié)與提高1.VLOOKUP近似查詢：2.SUMIFS函數(shù)：7.3任務二：分類匯總?cè)蝿找笈c效果1）利用分類匯總的操作。統(tǒng)計新入職男女員工的平均年齡及各職稱等的平均年齡，各部門的入職人數(shù)。結(jié)果如圖所示。2）利用分類匯總統(tǒng)計各二級學院新入職員工的安置費和科研啟動資金總額，并找出科研啟動資金最高的學院知識點解析分類匯總是以某一個字段做為分類的項，對工作表數(shù)據(jù)區(qū)域的其他字段進行各種統(tǒng)計計算，如求和、計數(shù)、求平均值、最大值等。

任務實現(xiàn)步驟1.

統(tǒng)計新入職男女員工的平均年齡及各職稱的平均年齡。2.利用分類匯總統(tǒng)計各二級學院新入職員工的安置費和科研啟動資金總額，并找出科研啟動資金最高的學院總結(jié)與提高多重分類匯總。實際工作中有時需要匯總多種結(jié)果，此時需要進行多重分類匯總，可以是多字段的也可以是多重計算的多重分類匯總。7.4任務三：自動篩選

任務要求與效果需要從新入職的員工記錄中篩選出具有高級職稱而且筆試成績在80-90（包含90）的女職工的記錄。效果如圖知識點解析Excel的自動篩選功能可以根據(jù)需要（即篩選的條件）大工作表中只顯示想要查看的內(nèi)容，而隱藏其余內(nèi)容。篩選不會以任何方式更改數(shù)據(jù)。一旦移除篩選，所有數(shù)據(jù)就會重新顯示。從而達到根據(jù)不同需求只查看到要關(guān)注的記錄。

任務實現(xiàn)步驟篩選特別關(guān)注的員工信息從新入職的員工記錄中篩選出具有高級職稱而且筆試成績在80-90（包含90）的女職工的記錄?？偨Y(jié)與提高當進行篩選條件比較復雜的多條件查詢或者需要將篩選結(jié)果復制到其它位置時需要利用高級篩選完成。7.5任務三：數(shù)據(jù)透視表任務要求與效果1）能夠分析出各學院各種職稱等級的學歷層次，2）改變數(shù)據(jù)透視表的布局、透視關(guān)系，分析各不同學歷層次不同職稱級別的筆試成績。3）設(shè)置字段顯示方式，分析各職稱在新入職員工的占比情況。知識點解析1.什么是數(shù)據(jù)透視表數(shù)據(jù)透視表是用來從excel數(shù)據(jù)列表、關(guān)系數(shù)據(jù)庫或OLAP多維數(shù)據(jù)集中的字段中總結(jié)信息的分析工具。是一種交互式報表，可以快速匯總、分析大量數(shù)據(jù)表格的交互式工具。按照數(shù)據(jù)表格的不同字段從多個角度進行透視，并建立交叉表格，用以查看數(shù)據(jù)表格不同層面。只需動動鼠標移動字段位置便可變換出各種類型的報表。2.數(shù)據(jù)透視表的用途數(shù)據(jù)透視表功能，可以被認為是Excel的精華所在。使用數(shù)據(jù)透視表可以深入分析數(shù)值數(shù)據(jù)，以幫助用戶發(fā)現(xiàn)關(guān)鍵數(shù)據(jù)，并做出有關(guān)企業(yè)中關(guān)鍵數(shù)據(jù)的決策。數(shù)據(jù)透視表有機綜合了數(shù)據(jù)排序、篩選、分類匯總等數(shù)據(jù)分析的優(yōu)點，具有所謂“透視”功能?？梢詫⒓姺钡臄?shù)據(jù)化為有價值的信息，提供研究和決策所用。3.數(shù)據(jù)透視表的結(jié)構(gòu)如圖7-46所示。對于“員工信息表2”中大量員工流水數(shù)據(jù)，如果需要按二級學院統(tǒng)計各級別職稱中員工的學歷層次情況，可以建如圖7-46所示的透視表。從結(jié)構(gòu)看，數(shù)據(jù)透視表分為四個部分。行區(qū)域、列區(qū)域、數(shù)據(jù)區(qū)域、報表篩選區(qū)域。4.幾個名詞的含義行標簽：作為橫向分類依據(jù)的字段列標簽：作為縱向分類依據(jù)的字段。數(shù)值：拖至此處的字段是計算的依據(jù)，Excel將用某種算法，對其進行計算，然后將計算結(jié)果（匯總數(shù)值）顯示出來報表篩選：作為分類顯示（篩選）依據(jù)的字段，可以將一個或多個字段拖至此處。此區(qū)域中的字段是分類篩選的首要條件。

任務實現(xiàn)步驟1.

創(chuàng)建數(shù)據(jù)透視表2.改變數(shù)據(jù)透視表的布局、透視關(guān)系3.改變數(shù)據(jù)透視表的值顯示方式4.添加切片器用于查看透視表中不同部門的職稱學歷分布總結(jié)與提高制作數(shù)據(jù)透視圖直觀顯示各職稱類別人數(shù)的占比情況。7.6任務四：制作動態(tài)圖表

任務要求與效果1）利用自動篩選制作動態(tài)圖表以更方便查看調(diào)查結(jié)果，效果如圖2）利用VLOOKUP函數(shù)制作動態(tài)圖表效果如圖知識點解析1.什么是動態(tài)圖表動態(tài)圖表，即交互圖表，可以讓用戶通過鼠標來選擇不同的預設(shè)項目從而在圖表中動態(tài)顯示對應的數(shù)據(jù)。這里學習兩種動態(tài)圖表的設(shè)計方法2.利用自動篩選法制作動態(tài)圖表利用自動篩選是實現(xiàn)動態(tài)圖表最簡單的方法，只要選擇全部數(shù)據(jù)制作圖表再利用自動篩選挑出感興趣的數(shù)據(jù)項即可實現(xiàn)動態(tài)查看。3.利用函數(shù)公式法制作動態(tài)圖表在數(shù)據(jù)區(qū)域以外通過添加輔助行，利用VLOOKUP(也可以利用其他查詢和引用函數(shù))函數(shù)取得項目的數(shù)據(jù)。再利用EXCEL的數(shù)據(jù)有效性功能設(shè)置下拉列表來選擇項目從而實現(xiàn)動態(tài)選擇數(shù)據(jù)制作動態(tài)圖表。

任務實現(xiàn)步驟1.自動篩選法制作動態(tài)圖表2.公式法制作動態(tài)圖表總結(jié)與提高利用定義名稱法制作動態(tài)圖表，圖表引用的數(shù)據(jù)區(qū)域由OFFSET函數(shù)生成，不必出現(xiàn)在工作表中，這時工作表頁面簡潔，數(shù)據(jù)也不會被查看到。使用OFFSET函數(shù)定義一個名稱，圖表的數(shù)據(jù)區(qū)域指向名稱所在位置，當OFFSET中參數(shù)值發(fā)生變化，那么名稱所指定的區(qū)域也發(fā)生變化，