項目8：員工信息表制作與統(tǒng)計

項目8：員工信息表制作與統(tǒng)計教師：電話：輔導時間：郵箱：第二部分Excel2016高級應用案例知識技能點批量填充序列號拒絕錄入重復值姓名按筆畫排序下拉列表選擇自動驗證身份證位數自動從身份證提取出生年月日自動計算年齡員工生日自動提醒單元格數據聯動設置表格不同視圖凍結窗格創(chuàng)建、刪除自定義表格樣式單條件與多條件計數8.1項目背景小王在收集到員工的登記信息之后，希望通過Excel表格來統(tǒng)計分析員工的各項指標數據，如部門、學歷、年齡等分布情況，并希望在員工生日快到的時候能夠自動提醒，為保證輸入的準確性，需要對表格中的部分數據進行驗證，當輸入錯誤的時候，能自動提醒，為減少錄入的數據量，當輸入部分數據時，與之相關聯的數據能夠自動產生。Word強大之處在于文檔的排版，對于涉及大量數據計算，我們建議讀者采用Excel來完成，借助功能強大的公式，既能大幅提高工作效率，又保證數據的準確性，可以將分析統(tǒng)計結果復制到Word文檔中，提交完整的分析報告。8.2項目簡介本項目利用Excel2016，實現有規(guī)律數據的批量填充、重復性檢驗、將姓名按筆畫排序、從下拉列表中選擇數據，減少輸入工作量、驗證數據的位數、自動從身份證中提取出生的年月日、在任何時間打開，都能根據當前時間自動計算員工的年齡、在員工生日前的10天之內，在表格中自動顯示提醒、在選擇了學歷之后，自動根據學歷生成學位、為針對不同的人設置不同的表格視圖，只展示與其相關的數據、凍結窗格、設置表格樣式、自定義表格樣式以及單條件、多條件計數等功能。8.3項目制作8.3.1.批量填充序列號表格中的序號，表示該行在表格中的所處行的位置，一般每往下一行就增加1，對于這類有規(guī)律的序列（如等差或等比性質的數據），可以采用序列填充的方式完成數據錄入。快速填充多種類型的數據序列，首先選擇要用作其他單元格填充基礎的單元格，然后拖動填充手柄，將填充柄橫向或縱向拖過填充的單元格。步驟如下：步驟1：選擇包含要填充到相鄰單元格的數據的單元格。步驟2：拖動填充柄，使其經過要填充的單元格，如圖8-1所示。步驟3：要更改選定區(qū)域的填充方式，請單擊“自動填充選項”，然后單擊所需的選項。【小貼士】通過拖動自動填充手柄，既可以實現有規(guī)律數據的自動填充，同時可以完成單元格公式、樣式的自動更新。圖8-1自動填充8.3.2.拒絕錄入重復值在每個單位中，工號都是唯一的，在錄入數據時，是不允許重復的，可以采用“數據有效性”驗證，來設置拒絕錄入重復的工號。具體步驟如下：步驟1：選中不允許重復的列，此處為B列步驟2：選擇【數據】菜單，點擊【數據驗證】按鈕旁邊的符號，點擊【數據驗證】命令，如圖8-2數據驗證命令所示。彈出【數據驗證】對話框。圖8-2數據驗證命令步驟3：用公式設置不允許重復。在【允許】下拉列表中選擇【自定義】選項，在【公式】文本框中輸入“=COUNTIF(B:B,B1)=1”（不包含括號，在英文輸入狀態(tài)下輸入），如圖8-3所示。【小貼士】輸入公式時，一定要在英文狀態(tài)輸入，包括括號、冒號和逗號，否則公式不能正確運行。圖8-3設置不允許重復步驟4：設置輸入提示信息。選擇【輸入信息】選項卡，在【輸入信息】文本框內輸入提示消息“不允許重復”。如圖8-4所示圖8-4設置輸入提示信息步驟5：設置出錯警告提示信息。選擇【出錯警告】選項卡，在【樣式】下拉列表中選擇【警告】提示類型，在【標題】和【錯誤消息】文本框中分別輸入提示信息，點擊確定。如圖8-5所示。圖8-5設置出錯警告信息按上述步驟設置完畢后，當在工號列（即本例的B列）輸入相同工號的時候，將會顯示如下的提示消息，提示輸入有誤，點擊【否】按鈕，關閉提示信息框，輸入正確的數據。如圖8-6所示。圖8-6輸入錯誤提示信息8.3.3.姓名按筆畫排序在Excel中，除了可以將數字數據按升序或降序排序之外，還可以對文本數據按照一定的規(guī)律排序，本例將完成對姓名按筆畫數排序。選擇需要排序的數據，點擊【數據】菜單，選擇【排序】按鈕，在彈出的對話框中選擇【擴展選定區(qū)域】選項，在【排序】對話框中，將【主要關鍵詞】設置為【姓名】，【次序】列表中選擇【升序】，點擊【選項】按鈕，在【排序選項】對話框的【方法】組中，選擇【筆畫順序】選項，最后確定后，即完成了對姓名按筆畫排序。如圖8-7所示圖8-7按筆畫排序【小貼士】從【排序選項】對話框中，我們看到，還可以按字母排序，可以區(qū)分大小寫，另外，還可以通過點擊【添加條件】增加更多的排序條件，多條件排序是在先按第一個條件排序后，再在排序后的結果中再按第二個條件來排序，依次類推，可以實現更為精確的排序方式，讀者可自行嘗試。8.3.4.下拉列表選擇性別在諸如性別、學歷等選項，有固定的范圍，并且選項不多，如性別只有“男”和“女”，學歷只有“研究生”、“本科”、“大?！焙汀案咧小钡?，為避免輸入格式不規(guī)范，減少輸入工作量，可以采用下拉列表的方式讓用戶選擇，提升用戶體驗。具體步驟如下：步驟1：選擇需要提供下拉列表選擇的單元格，此處選擇“性別”列小的單元格，點擊【數據】選項卡，選擇【數據驗證】按鈕，彈出【數據驗證】對話框。步驟2：在【允許】下拉列表中選擇【序列】，確保選中【提供下拉箭頭】復選框，在【來源】文本框中輸入“男,女”，注意一定要確保男和女中間是用英文的逗號分隔，點擊【確定】按鈕，即完成了下拉列表的數據項的設置。如圖8-8所示?？梢杂猛瑯拥姆绞?，完成“學歷”欄的填寫。如圖8-9所示圖8-8設置性別下拉列表圖8-9設置學歷下拉列表8.3.5.輸入并驗證身份證號位數身份證位數，只能為15位或者18位，為了確保身份證輸入位數正確，可以通過【數據驗證】來實現自動驗證身份證號碼的位數。具體方法如下。步驟1：定位到需要設置身份證長度的單元格，此處為“F3”單元格，點擊【數據】菜單，選擇【數據驗證】按鈕，彈出【數據驗證】對話框。步驟2：設置驗證條件。在【允許】下拉列表中選擇【自定義】，在【公式】文本框中輸入“=OR(LEN(F3)=15,LEN(F3)=18”（不包含引號），特別需要注意的是，所有的括號和逗號，都需要在英文輸入狀態(tài)下輸入，如圖8-10所示。步驟4：設置相應的輸入信息和出錯警告信息，最后點擊【確定】按鈕，完成對身份證輸入長度的驗證和錯誤提示?！拘≠N士】為了讓輸入的身份證號碼不顯示為數字，需要先輸入符號“'”，即英文狀態(tài)下的單引號，表示將輸入的內容以文本形式顯示。如輸入“。公式中的OR關鍵詞，表示“或者”的意思，只要符合表達式計算結果之一即可，除了OR之外，還有AND，NOT關鍵詞，讀者可自行查閱圖8-10設置身份證輸入長度8.3.6.從身份證號提取出生年月日從文本中提取部分字符，需要用Excel中的LEFT()，RIGHT(),MID()等函數。下面以提取身份證號碼中的年月日為例說明如何用字符截取函數，來提取年、月、日的方法。MID(text,start_num,num_chars)：text必需，包含要提取字符的文本字符串，start_num必需，文本中要提取的第一個字符的位置，文本中第一個字符的start_num為1，以此類推，num_chars必需，指定希望MID從文本中返回字符的個數。LEFT(text,[num_chars])：Text必需，包含要提取的字符的文本字符串，num_chars，可選，指定要由LEFT提取的字符的數量。Num_chars必須大于或等于零，如果num_chars大于文本長度，則LEFT返回全部文本。RIGHT(text,[num_chars])：text必需，包含要提取字符的文本字符串，num_chars可選，指定希望RIGHT提取的字符數，Num_chars必須大于或等于零，如果num_chars大于文本長度，則RIGHT返回所有文本。在身份證號碼中，從第7位開始的后4位為出生日期中的年份，后2位為月份，再后兩位為出生的日期，可以采用MID函數來提取相應數據。選中“出生年月”的下一個單元格，此處為G3，在單元格中輸入“=MID(F3,7,4)&"/"&MID(F3,11,2)”。MID(F3,7,4)表示從單元格F3的字符中的第7個字符開始，截取4個字符，得到出生的年，即“1979”，MID(F3,11,2)表示從F3單元格中的第11個字符開始，截取2個字符，得到出生的月份，即“05”，兩者用“&”號將年與“/”和月連接起來，即組成了“1979/05”效果。上述公式，僅能截取長度為18位的身份證號碼的年月數據，但是，由于身份證號碼有可能有15位數字，因此，為了實現根據身份證號碼不同長度，能自動自動實現年月的截取，需要將公式修改為：“=IF(LEN(F3)=18,MID(F3,7,4)&"/"&MID(F3,11,2),"19"&MID(F3,6,2)&"/"&MID(F3,8,2))”上述公式的含義是：如果單元格F3的長度為18，即LEN(F3)=18條件滿足，如字符，就從第7個字符開始，截取4個字符，得到“1979”作為年份，拼接符號“/”，再截取后兩個字符，得到字符“05”作為月份，最后的結果就為“1979/05”，如果LEN(F3)=18條件不滿足，如字符“510108890305405”，就由字符“19”拼接從F3單元格第6個字符開始的兩個字符，得到字符“89”，拼接后的字符為“1989”，拼接字符“/”，再截取后兩個字符，得到“03”作為月份，最后的結果就為“1989/03”，滿足了需要的格式。如圖8-11所示【小貼士】按上述方式提取出來的數據，都是文本格式，如果需要日期格式的數據，可以用Date()函數將文本格式數據轉化為日期格式，公式如下：=DATE(MID(F3,7,4),MID(F3,11,2),MID(F3,13,2))，此處僅以18位的身份證為例。圖8-11提取出生日期數據8.3.7.自動計算年齡當身份證號確定之后，出生日期等信息可以從其中獲得，就可以自動計算年齡了。年齡的計算方式為當前日期與身份證中的出生年份之差。計算時間差，需要用到Excel的DATEDIF()函數和TODAY()函數。TODAY()函數自動獲得當前的日期，當每一次打開文件時，TODAY()函數的值會自動改變。DATEDIF()函數的基本格式如下：DATEDIF(start_date,end_date,unit)，即DATEDIF(開始日期,結束日期,返回參數)，返回參數有Y、M、D、YM、YD、MD六種，返回的內容大致如下表所示："Y"一段時期內的整年數。"M"一段時期內的整月數。"D"一段時期內的天數。"MD"start_date與end_date之間天數之差。忽略日期中的月份和年份?！癥M”start_date與end_date之間月份之差。忽略日期中的天和年份"YD"start_date與end_date的日期部分之差。忽略日期中的年份。根據以上分析，通過如下公式來完成年齡的自動計算：“=DATEDIF(H3,TODAY(),”Y”)”，開始的日期為H3中的數據，結束的時間為TODAY()函數自動取得的當前日期，以“Y”代表獲取年份之差。如8-12所示。將I3單元格向下自動填充，即可實現年齡的自動計算。圖8-12自動計算年齡8.3.8.員工生日自動提醒假如公司在員工生日當天送禮物，一般需要提前購買禮品，因此可以在員工信息表里能設置提醒，提示還有多少天就是員工生日了。假設從還有10天就開始提醒，過了生日之后，就自動取消提醒。還是可以用DATEDIF函數拉實現，在生日中，必須包含月份和日期，否則就不能準確到提前幾天的提醒功能了。如果直接用公式，DATEDIF(H3,TODAY(),"yd"）比如今天10月21日，員工出生日期是1979年10月23日，用上面這個公式返回結果是364天，假如需要提前10天提醒，需要設置為DATEDIF(H3-10,TODAY(),”yd”)來計算兩個的日期之差。具體輸入如圖8-13所示：公式“=TEXT(10-DATEDIF(H3-10，TODAY(),”yd”),”還有0天生日;;今天生日”)”表示的意思是：DATEDIF函數的計算結果大于0的，顯示為“還有N天生日”；小于0的不顯示；等于0的顯示為“今天生日”。TEXT函數的作用，是將結果以文本的形式顯示出來。圖8-13生日提醒公式8.3.9.根據學歷自動產生學位在現行的教育體系中，學歷有博士研究生、碩士研究生、本科、大專和高中等，對應的學位分別為博士、碩士、學士，大專和高中沒有學位?，F要求根據選擇或輸入的學歷，自動在學位欄輸出相應的學位，如果學歷為大?；蚋咧?，則在學位欄輸出“無”。如圖8-14所示。圖8-14根據學歷自動輸出學位使用的公式如下：=IF(K3="博士研究生","博士",IF(K3="碩士研究生","碩士",IF(K3="本科","學士","無")))，所代表的意思為：如果K3的值為“博士研究生”，則當前單元格的內容輸出“博士”，否則繼續(xù)判斷K3單元格的內容是否為“碩士研究生”，如果為是，設置當前單元格內容為“碩士”，否則繼續(xù)判斷K3單元格的內容是否為“本科”，如果是，則當前單元格的內容填入“學士”，若果為K3單元格的內容不為“本科”，則代表K3單元格的內容既不為博士研究生、也不為碩士研究生和本科，那么當前單元格的內容輸出為“無”?！拘≠N士】IF函數是在Excel中最常用的函數之一，它允許對單元格數據進行邏輯判斷，簡單的形式理解為：如果（內容為True，則執(zhí)行某些操作，否則就執(zhí)行其他操作）因此IF語句有兩個結果。第一個結果是條件為True時的結果，如果條件比較為False，則執(zhí)行第二個操作。IF語句是可以嵌套的，如下面例子，可以將學生的考試成績轉化為相應的等級：相應的公式為：=IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))此復雜嵌套IF語句遵循一個簡單邏輯：1.如果TestScore（單元格D2）大于89，則學生獲得A2.如果TestScore大于79小于等于89，則學生獲得B3.如果TestScore大于69小于等于79，則學生獲得C4.如果TestScore大于59小于等于69，則學生獲得D5.否則，學生獲得F我們發(fā)現，當條件比較多的時候，用IF語句將形成多層的嵌套，語義比較復雜，這時可以使用IFS函數，簡化多條件判斷語句的書寫，并且更容易理解。在上述轉化等級的多條件判斷語句中，可以用如下形式來完成：=IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")。所代表的意思就是：如果（D2大于89，則返回“A”，如果D2大于79，則返回“B”并以此類推，對于所有小于59的值，返回“F”）。讀者可以自行嘗試用IFS多條件語句修改前例。8.3.10.設置入職日期為當前日期在Excel中，插入當前日期的方式用快捷鍵【Ctrl】+【;】組合鍵，或者使用函數Today（）可以快速插入當前的日期。8.3.11.設置不同顯示視圖如果需要在Excel中反復多次按照不同條件進行篩選、隱藏行列等操作，當表格數據較多、自動篩選條件比較復雜時，反復操作就變成很麻煩的一件事了，采用“自定義視圖”，可以對每次的篩選結果都保留下來，方便在后面任何時候可以方便的打開查看，并且可以將處理好了后的內容打印出來，極大的方便針對不同的人或不同篩選后的打印管理。為生成沒有身份證號碼的員工信息信息表，在“員工信息表模板.xlsx”文件中，在“身份證號碼”列點擊右鍵，選擇【隱藏】命令，表示將該列隱藏起來，再選擇【視圖】菜單，點擊【自定義視圖】按鈕，彈出【視圖管理器】對話框，如圖8-15所示。點擊【添加】按鈕，在彈出的對話框中輸入視圖的名稱，如“隱藏身份證號的員工信息”，點擊【確定】按鈕，如圖8-16所示。圖8-15視圖管理器圖8-16為視圖命名用同樣方法，建立其他的視圖（將身份證號碼列取消隱藏）。當需要查看、打印不同信息時，選擇【視圖】->【自定義視圖】在彈出的【視圖管理器】對話框中，選擇不同的視圖名稱，點擊【顯示】按鈕，即可顯示預先定義好的內容，并可快速打印出來。圖8-17顯示不同視圖8.3.12.凍結窗格若要使工作表的某一區(qū)域在滾動到工作表的另一區(qū)域時仍保持可見，可采用“凍結窗格”的方法來完成。點擊【視圖】選項卡，在此選項卡中，選擇【凍結窗格】，將特定的行和列鎖定到位，也可以“拆分”窗格，創(chuàng)建同一工作表的單獨窗口。如果電子表格中的第一行包含標題，可以凍結該行，確保在電子表格中向下滾動時列標題保持可見。如圖8-18所示，首行被凍結后，下面的內容滾動，首行仍然保持不動。圖8-18凍結窗格在選擇凍結工作表中的行或列之前，以下問題需要注意：

（1）只能凍結工作表中的頂行和左側的列，無法同時凍結工作表中間的行和列。

（2）當位于單元格編輯模式（即，你正在單元格中輸入公式或數據）或工作表受保護時，“凍結窗格”命令不可用。若要取消單元格編輯模式，請按Enter或Esc。

（3）可以選擇只凍結工作表的頂行，或只凍結工作表的左側列，或同時凍結多個行和列。例如，如果凍結了行1，然后又決定凍結列A，則行1將無法再凍結。如果要凍結行和列，需要同時凍結它們。若要僅鎖定一行，選擇“視圖”選項卡，然后單擊“凍結首行”。若要僅鎖定一列，選擇“視圖”選項卡，然后單擊“凍結首列”。若要鎖定多行或多列，或同時鎖定行和列，選擇“視圖”選項卡，然后單擊“凍結窗格”，然后將光標置于要凍結的行下以及任何要凍結的列的右側。若要鎖定多行（從第1行開始），選擇要凍結的最后一行下方的一行，選擇“視圖”選項卡，然后單擊“凍結窗格”。

若要鎖定多列，選擇要凍結的最后一列右側的一列，選擇“視圖”選項卡，然后單擊“凍結窗格”。8.3.13.設置跨列居中一般情況下，表格的第一行，總是表格的標題，標題一般需要設置跨列居中對齊，可以通過“合并居中”的來實現，首先選中需要合并的單元格，點擊【開始】選項卡，選擇【合并后居中】按鈕，即可完成對選定單元格的合并，并讓單元格內容居中對齊。如圖8-19所示。圖8-19多個單元格合并居中8.3.14.設置表格樣式Excel提供了可用于快速設置表格格式的許多預定義的表格樣式。如果預定義的表格樣式不能滿足需要，可以創(chuàng)建并應用自定義表格樣式?？梢赃M一步調整表格的格式選擇快速樣式表的元素，如標題和匯總行、第一個和最后一列、鑲邊行和列，以及自動篩選選項等。如圖8-20所示。圖8-20表格樣式選項當有未格式化為表格的數據區(qū)域時，Excel將自動將其轉換為表格時選擇表格樣式，可以選擇不同的格式來更改現有表的格式。1.選擇表中或要為表格設置格式的單元格。2.在開始選項卡上，單擊套用表格格式。如圖8-21所示3.單擊要使用的表格樣式，即可快速應用格式。圖8-21快速表格樣式8.3.15.創(chuàng)建自定義表格樣式選擇要用于創(chuàng)建自定義樣式表中的任意單元格，在【開始】選項卡上，單擊【套用表格格式】，點擊【新建表格樣式】，這將啟動新的表格樣式對話框。如8-22所示圖8-22新建表格樣式在【名稱】框中，鍵入新的表格樣式的名稱。在表元素框中，可以執(zhí)行如下的操作︰

（1）選擇要設置格式的元素，然后單擊【格式】按鈕，然后從字體、邊框或填充選項卡中選擇所需的格式設置選項；

（2）若要刪除現有元素的格式，單擊相應元素，然后單擊清除。

（3）在預覽中，可以看到所做的更改格式對表的影響。

（4）若要用作當前工作簿中的默認表格樣式的新表樣式，選擇設置為此文檔的默認表格樣式復選框。8.3.16.刪除自定義表格樣式選擇要從中刪除自定義表格樣式表中的任意單元格。（1）在開始選項卡上，單擊【套用表格格式】。（2）在【自定義】組中，右鍵單擊要刪除的表格樣式，然后單擊快捷菜單上的刪除。8.3.17.單條件計數使用CountIF函數，可以統(tǒng)計某個區(qū)域內滿足某個條件的單元格數量，如本例要統(tǒng)計每個部門的人數。步驟如下：步驟1：輸入部門數據，按列錄入；步驟2：在“研發(fā)部”后面的單元格，輸入公式“=COUNTIF($L$3:$L$12,P2)”，表示將在絕對定位為L3到L12的范圍內，尋找P2單元格中內容出現的次數。錄入完畢回車即輸出研發(fā)部出現的次數。如圖8-23所示。圖8-23使用COUNTIF函數計數步驟3：在Q2單元格，拖動鼠標，向下填充。進入售后支持部后的單元格內，檢查公式，發(fā)現“$L$3:$L$12”并沒有隨著拖動而自動變化，而P5由于拖動，已經自動發(fā)生了變化。如圖8-24所示圖8-24分析絕對引用與相對引用【小貼士】絕對引用、相對引用和混合引用之間的區(qū)別相對引用

公式中的相對單元格引用（如A1）是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復制或填充公式，引用會自動調整。默認情況下，新公式使用相對引用。例如，如果將單元格B2中的相對引用復制或填充到單元格B3，將自動從=A1調整到=A2。復制的公式具有相對引用絕