Excel技巧 及 花名冊(cè)

真誠(chéng)為您提供優(yōu)質(zhì)參考資料，若有不當(dāng)之處，請(qǐng)指正。設(shè)計(jì)標(biāo)準(zhǔn)化規(guī)范化的Excel基礎(chǔ)表格，是高效數(shù)據(jù)分析的第一步，因?yàn)閿?shù)據(jù)分析的源頭就是基礎(chǔ)表格數(shù)據(jù)。     設(shè)計(jì)基礎(chǔ)表格的總體原則是：     結(jié)構(gòu)的科學(xué)性     數(shù)據(jù)的易讀性     匯總的便宜性     分析的靈活性     外觀的美觀性     結(jié)構(gòu)的科學(xué)性，就是要按照工作的性質(zhì)，管理的內(nèi)容，數(shù)據(jù)的種類(lèi)，分別設(shè)計(jì)基礎(chǔ)管理表格，分別保存不同數(shù)據(jù)。基礎(chǔ)表格要越簡(jiǎn)單越好，那些把所有數(shù)據(jù)都裝在一個(gè)工作表中的做法是絕對(duì)不可取的。比如，要做入庫(kù)出庫(kù)管理，你會(huì)如何設(shè)計(jì)這樣的基礎(chǔ)表格呢？要用幾個(gè)表格來(lái)反映入庫(kù)出庫(kù)數(shù)據(jù)？每個(gè)表格要怎么保存數(shù)據(jù)？     數(shù)據(jù)的易讀性包含兩個(gè)方面：利用函數(shù)讀數(shù)（取數(shù)）方便，叫函數(shù)讀數(shù)；眼睛查看數(shù)據(jù)容易，叫人工讀數(shù)。一個(gè)雜而亂的表格，是很難實(shí)現(xiàn)這兩種高效讀數(shù)的。數(shù)據(jù)易讀性差的主要原因有：表格結(jié)構(gòu)設(shè)計(jì)不合理；數(shù)據(jù)保存不合理；殘缺不全的表格數(shù)據(jù)結(jié)構(gòu)。     匯總的便宜性是指不論多大的數(shù)量，匯總要簡(jiǎn)單方便容易。你可以問(wèn)自己：我設(shè)計(jì)的工作表內(nèi)據(jù)匯總方便嗎？大量表格數(shù)據(jù)之間的匯總方便嗎？如果不方便，或者做起來(lái)非常吃力，Excel很好用的工具也用不上，那唯一的原因就是基礎(chǔ)表格設(shè)計(jì)有問(wèn)題，不要發(fā)牢騷說(shuō)Excel太難了，總是學(xué)不會(huì)，尤其是函數(shù)太難掌握了！    分析的靈活性，是指不論做何種分析，要講究數(shù)據(jù)分析的靈活多變。因?yàn)槲覀儗?duì)數(shù)據(jù)進(jìn)行分析的目的，是要針對(duì)企業(yè)的數(shù)據(jù)進(jìn)行深度挖掘，從不同方面找問(wèn)題、找原因、找對(duì)策，這就要求基礎(chǔ)數(shù)據(jù)必須能夠精準(zhǔn)反映企業(yè)的管理流程，制作的分析報(bào)告也必須具有靈活性，能夠在幾分鐘內(nèi)通過(guò)轉(zhuǎn)換分析角度而得到另外一份分析報(bào)告。    外觀的美觀性。不論是基礎(chǔ)表還是報(bào)告，都盡量要求把表格進(jìn)行美化?；A(chǔ)表的美化以容易管理數(shù)據(jù)標(biāo)準(zhǔn)，而報(bào)告的美化以分析結(jié)果清楚為標(biāo)準(zhǔn)。特別強(qiáng)調(diào)的是，不論是基礎(chǔ)表還是報(bào)告，很多人喜歡把數(shù)據(jù)區(qū)域加上邊框，并保持工作表默認(rèn)的網(wǎng)格線。其實(shí)，我們可以取消網(wǎng)格線，而把數(shù)據(jù)區(qū)域設(shè)置為非常簡(jiǎn)練的線條表格，并把單元格字體、顏色、邊框等進(jìn)行合理的設(shè)置。下面我們以人力資源管理中一個(gè)簡(jiǎn)單的員工信息表為例，來(lái)說(shuō)明標(biāo)準(zhǔn)化表格的設(shè)計(jì)方法和技巧。這個(gè)表格的基本要求如下：   ·員工工號(hào)從“G0001”開(kāi)始編號(hào)，中間不能斷號(hào)，新入職員工依次分配連續(xù)的工號(hào)   ·員工姓名中不允許輸入空格   ·所屬部門(mén)必須快速準(zhǔn)確輸入企業(yè)存在的部門(mén)，要名稱(chēng)統(tǒng)一   ·學(xué)歷必須快速規(guī)范輸入   ·婚姻狀況要快速規(guī)范輸入   ·身份證號(hào)碼必須是18位的文本，不允許重復(fù)   ·出生日期、年齡、性別從身份證號(hào)碼中自動(dòng)提取   ·入職時(shí)間必須是合法的日期   ·本公司工齡自動(dòng)計(jì)算得出   ·為便于分析流動(dòng)性，工作表要有離職時(shí)間和離職原因兩列數(shù)據(jù)，離職原因是固定的幾種類(lèi)型   ·新員工輸入后，該員工的工號(hào)、生日、年齡、工齡等等計(jì)算公式自動(dòng)往下復(fù)制   ·表格自動(dòng)美化    1、表格結(jié)構(gòu)設(shè)計(jì)    根據(jù)人力資源中對(duì)員工信息管理和分析的需要，創(chuàng)建一個(gè)工作表，命名為“員工信息”，數(shù)據(jù)列標(biāo)題如下圖。    2、工號(hào)的自動(dòng)連續(xù)填充   在單元格A2輸入公式“="G"&TEXT(ROW(A1),"0000")”，就得到能夠往下連續(xù)填充工號(hào)的計(jì)算公式。這里，ROW函數(shù)是獲取指定單元格的行號(hào)（ROW(A1)的結(jié)果就是1，ROW(A2)的結(jié)果就是2，以此類(lèi)推），TEXT函數(shù)是把一個(gè)數(shù)字按照指定的格式轉(zhuǎn)換為文本。那么這個(gè)公式的結(jié)果就是G0001。如果把該公式復(fù)制到單元格A3，該公式就變成了“="G"&TEXT(ROW(A2),"0000")”，結(jié)果就是G0002。    3、規(guī)范姓名的輸入，不允許在姓名文字中輸入空格   選中單元格B2，設(shè)置有效性，其有效性的自定義公式為“=SUBSTITUTE(B2," ","")=B2”，如下圖。這里，先使用SUBSTITUTE函數(shù)把輸入的姓名中的所有空格替換掉，然后再跟輸入的姓名進(jìn)行比較，如果兩者相等，表明輸入的姓名中沒(méi)有空格，否則就是有空格，就不允許輸入到單元格。    4、規(guī)范快速輸入部門(mén)名稱(chēng)   公司部門(mén)是確定的，在一定時(shí)期是不會(huì)變化的，因此可以使用數(shù)據(jù)有效性來(lái)快速規(guī)范輸入部門(mén)名稱(chēng)。假如企業(yè)的部門(mén)有：總經(jīng)辦、財(cái)務(wù)部、人力資源部、貿(mào)易部、后勤部、技術(shù)部、生產(chǎn)部、銷(xiāo)售部、信息部、質(zhì)檢部、市場(chǎng)部，那么選擇單元格C2，設(shè)置數(shù)據(jù)有效性，即選擇“序列”，來(lái)源為“總經(jīng)辦,財(cái)務(wù)部,人力資源部,貿(mào)易部,后勤部,技術(shù)部,生產(chǎn)部,銷(xiāo)售部,信息部,質(zhì)檢部,市場(chǎng)部”，如下圖。這樣，就為單元格設(shè)置了一個(gè)下拉列表，從下拉列表里快速選擇輸入某個(gè)部門(mén)名稱(chēng)。    5、快速輸入學(xué)歷名稱(chēng)   員工的學(xué)歷也是固定的幾種。假若是以下幾個(gè)：博士、碩士、本科、大專(zhuān)、中專(zhuān)、高中，那么也可以使用數(shù)據(jù)有效性來(lái)快速規(guī)范輸入學(xué)歷名稱(chēng)。選擇單元格D2，設(shè)置數(shù)據(jù)有效性，即選擇“序列”，來(lái)源為“博士,碩士,本科,大專(zhuān),中專(zhuān),高中”。    6、快速輸入婚姻狀況   單元格E2輸入員工的婚姻狀況?；橐鰻顩r也就兩種數(shù)據(jù)：已婚和未婚，因此也可以使用數(shù)據(jù)有效性來(lái)控制輸入，也就是在數(shù)據(jù)有效性對(duì)話框的“來(lái)源”中輸入“已婚,未婚”。    7、輸入不重復(fù)的18位身份證號(hào)碼   每個(gè)員工的身份證號(hào)碼是不重復(fù)的，并且必須是18位，因此單元格F2輸入身份證號(hào)碼時(shí)也要使用數(shù)據(jù)有效性來(lái)控制。首先將F列的單元格格式設(shè)置成文本，然后選擇單元格F2，其有效性的自定義公式      =AND(LEN(F2)=18,COUNTIF($F$2:F2,F2)=1)   這里，使用LEN函數(shù)判斷輸入的身份證號(hào)碼是不是18位，即LEN(F2)=18；使用COUNTIF統(tǒng)計(jì)在前面已經(jīng)輸入的身份證號(hào)碼中，即將輸入的身份證號(hào)碼是不是還沒(méi)有輸過(guò)，即COUNTIF($F$2:F2,F2)=1；然后用AND函數(shù)把這兩個(gè)條件組合起來(lái)。如果兩個(gè)條件都成立，表明輸入的身份證號(hào)碼有效。    8、自動(dòng)輸入性別   員工性別從身份證號(hào)碼中自動(dòng)提取，不需要人工輸入。選擇單元格G2，輸入公式      =IF(ISEVEN(MID(F2,17,1),"女","男")   這里，先用MID函數(shù)提取身份證號(hào)碼的第17位數(shù)字，再用ISEVEN函數(shù)判斷是否為偶數(shù)，如果是偶數(shù)，該員工性別就是女，否則就是男，判斷處理則使用了IF函數(shù)。    9、自動(dòng)輸入出生日期   員工的出生日期也是從身份證號(hào)碼中自動(dòng)提取，不需要人工輸入。選擇單元格H2，輸入公式      =1*TEXT(MID(F2,7,8),"0000-00-00")   這里，先用MID函數(shù)提取身份證號(hào)碼的中間8位生日數(shù)字，再用TEXT函數(shù)把這8位數(shù)字按照日期的格式轉(zhuǎn)換成文本型日期格式，最后把TEXT函數(shù)的結(jié)果乘以數(shù)字1，將文本型日期轉(zhuǎn)換為真正的日期。    10、自動(dòng)計(jì)算年齡   有了出生日期，我們就可以使用DATEDIF函數(shù)自動(dòng)計(jì)算年齡。選擇單元格I2，輸入下面的公式，就自動(dòng)得到員工的實(shí)際年齡：      =DATEDIF(H2,TODAY(),"Y")    11、規(guī)范輸入入職時(shí)間   入職時(shí)間是一個(gè)非常重要的數(shù)據(jù)，因?yàn)橐鶕?jù)這列日期計(jì)算工齡，分析流動(dòng)性。由于這列日期要手工輸入，就必須規(guī)范輸入的入職時(shí)間數(shù)據(jù)合法有效，也就是要輸入正確格式的日期。選擇J2單元格，設(shè)置數(shù)據(jù)有效性，如下圖所示    12、自動(dòng)計(jì)算本公司工齡   有了入職時(shí)間，我們就可以使用DATEDIF函數(shù)自動(dòng)計(jì)算本公司工齡。選擇單元格K2，輸入下面的公式，就自動(dòng)得到員工的本公司工齡：      =DATEDIF(J2,TODAY(),"Y")    13、保證員工基本信息的完整性   由于B列至K列是員工的最基本信息，是不能缺少這些數(shù)據(jù)的，因此需要保證每個(gè)員工基本信息完整不缺。選擇B2單元格，把數(shù)據(jù)有效性的條件修改為      =AND(SUBSTITUTE(B2," ","")=B2,COUNTA($B1:$K1)=10)   也就是增加了一個(gè)條件COUNTA($B1:$K1)=10，它用來(lái)判斷上一行的B列至K列的數(shù)據(jù)是否都完整了（共有10列數(shù)據(jù)）    14、規(guī)范輸入離職時(shí)間   離職時(shí)間是一個(gè)非常重要的數(shù)據(jù)，因?yàn)橐鶕?jù)這列日期來(lái)分析離職。由于這列日期要手工輸入，就必須規(guī)范輸入的離職時(shí)間數(shù)據(jù)，也就是要輸入正確格式的日期。選擇L2單元格，設(shè)置日期數(shù)據(jù)的有效性。    15、規(guī)范輸入離職原因   離職原因用來(lái)分析員工的流動(dòng)性和離職狀態(tài)，因此必須規(guī)范離職原因的表述文字。假如企業(yè)對(duì)離職原因的描述是下述的文字：   合同到期但個(gè)人不愿續(xù)簽   合同到期但公司不愿續(xù)簽   因個(gè)人原因辭職   因公司原因辭職   違反公司規(guī)定辭退   生產(chǎn)任務(wù)變化辭退   考核不合要求辭退   退休   死亡   其他   那么就可以使用有效性來(lái)快速準(zhǔn)確輸入這些描述文字。    16、創(chuàng)建表格，自動(dòng)復(fù)制有效性和公式，并自動(dòng)美化表格   前面我們做的數(shù)據(jù)有效性、設(shè)置公式等，都是僅僅設(shè)置了第2行的單元格，并沒(méi)有選擇整列來(lái)做，因?yàn)檫@樣的話會(huì)使文件變得很大，復(fù)制很多公式也是很不方便。Excel提供了表格功能，不僅可以自動(dòng)復(fù)制有效性和公式，還可以自動(dòng)美化表格。   先在第1行輸入第一個(gè)人的信息，然后單擊數(shù)據(jù)區(qū)域的任意單元格，在單擊“插入”選項(xiàng)卡里的“表”命令（Excel 2007）或“表格”命令（Excel 2010），如圖2-20所示，即可把普通的數(shù)據(jù)區(qū)域變成了一個(gè)表格，然后再在“設(shè)計(jì)”選項(xiàng)卡中的“表格樣式”選擇一個(gè)自己喜歡的樣式，就可以把表格自動(dòng)美化。   這樣，我們就得到了一個(gè)標(biāo)準(zhǔn)化規(guī)范化的員工信息管理表格，在此表格基礎(chǔ)上，就可以建立各種自動(dòng)化分析模版了，比如員工屬性分析模版，員工流動(dòng)性分析模版，等等。    下圖是員工信息基礎(chǔ)表的樣子：    下面的三個(gè)表分別是基于此基本信息表格數(shù)據(jù)制作的員工屬性分析報(bào)告、員工流動(dòng)性分析報(bào)告、以及離職分析報(bào)告。這4張工作表，就構(gòu)成了員工信息統(tǒng)計(jì)分析模版。 17 / 17