《Excel技巧 及 花名冊》由會員分享，可在線閱讀，更多相關(guān)《Excel技巧 及 花名冊（17頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、真誠為您提供優(yōu)質(zhì)參考資料，若有不當(dāng)之處，請指正。 設(shè)計標(biāo)準(zhǔn)化規(guī)范化的Excel基礎(chǔ)表格，是高效數(shù)據(jù)分析的第一步，因為數(shù)據(jù)分析的源頭就是基礎(chǔ)表格數(shù)據(jù)。 ? ? 設(shè)計基礎(chǔ)表格的總體原則是： ? ? 結(jié)構(gòu)的科學(xué)性 ? ? 數(shù)據(jù)的易讀性 ? ? 匯總的便宜性 ? ??分析的靈活性 ? ? 外觀的美觀性 ? ? 結(jié)構(gòu)的科學(xué)性，就是要按照工作的性質(zhì)，管理的內(nèi)容，數(shù)據(jù)的種類，分別設(shè)計基礎(chǔ)管理表格，分別保存不同數(shù)據(jù)。基礎(chǔ)表格要越簡單越好，那些把所有數(shù)據(jù)都裝在一個工作表中的做法是絕對不可取的。比如，要做入庫出庫管理，你會如何設(shè)計這樣的基礎(chǔ)表格呢？要用幾個表格來反映入庫出庫數(shù)據(jù)？

2、每個表格要怎么保存數(shù)據(jù)？ ? ? 數(shù)據(jù)的易讀性包含兩個方面：利用函數(shù)讀數(shù)（取數(shù)）方便，叫函數(shù)讀數(shù)；眼睛查看數(shù)據(jù)容易，叫人工讀數(shù)。一個雜而亂的表格，是很難實現(xiàn)這兩種高效讀數(shù)的。數(shù)據(jù)易讀性差的主要原因有：表格結(jié)構(gòu)設(shè)計不合理；數(shù)據(jù)保存不合理；殘缺不全的表格數(shù)據(jù)結(jié)構(gòu)。 ? ? 匯總的便宜性是指不論多大的數(shù)量，匯總要簡單方便容易。你可以問自己：我設(shè)計的工作表內(nèi)據(jù)匯總方便嗎？大量表格數(shù)據(jù)之間的匯總方便嗎？如果不方便，或者做起來非常吃力，Excel很好用的工具也用不上，那唯一的原因就是基礎(chǔ)表格設(shè)計有問題，不要發(fā)牢騷說Excel太難了，總是學(xué)不會，尤其是函數(shù)太難掌握了！ ? ? 分析的靈

3、活性，是指不論做何種分析，要講究數(shù)據(jù)分析的靈活多變。因為我們對數(shù)據(jù)進行分析的目的，是要針對企業(yè)的數(shù)據(jù)進行深度挖掘，從不同方面找問題、找原因、找對策，這就要求基礎(chǔ)數(shù)據(jù)必須能夠精準(zhǔn)反映企業(yè)的管理流程，制作的分析報告也必須具有靈活性，能夠在幾分鐘內(nèi)通過轉(zhuǎn)換分析角度而得到另外一份分析報告。 ? ? 外觀的美觀性。不論是基礎(chǔ)表還是報告，都盡量要求把表格進行美化?；A(chǔ)表的美化以容易管理數(shù)據(jù)標(biāo)準(zhǔn)，而報告的美化以分析結(jié)果清楚為標(biāo)準(zhǔn)。特別強調(diào)的是，不論是基礎(chǔ)表還是報告，很多人喜歡把數(shù)據(jù)區(qū)域加上邊框，并保持工作表默認的網(wǎng)格線。其實，我們可以取消網(wǎng)格線，而把數(shù)據(jù)區(qū)域設(shè)置為非常簡練的線條表格，并把單元格字體、顏

4、色、邊框等進行合理的設(shè)置。 下面我們以人力資源管理中一個簡單的員工信息表為例，來說明標(biāo)準(zhǔn)化表格的設(shè)計方法和技巧。這個表格的基本要求如下： ??·員工工號從“G0001”開始編號，中間不能斷號，新入職員工依次分配連續(xù)的工號 ??·員工姓名中不允許輸入空格 ??·所屬部門必須快速準(zhǔn)確輸入企業(yè)存在的部門，要名稱統(tǒng)一 ??·學(xué)歷必須快速規(guī)范輸入 ??·婚姻狀況要快速規(guī)范輸入 ??·身份證號碼必須是18位的文本，不允許重復(fù) ??·出生日期、年齡、性別從身份證號碼中自動提取 ??·入職時間必須是合法的日期 ??·本公司工齡自動計算得出 ??·為便于分析流動

5、性，工作表要有離職時間和離職原因兩列數(shù)據(jù)，離職原因是固定的幾種類型 ??·新員工輸入后，該員工的工號、生日、年齡、工齡等等計算公式自動往下復(fù)制 ??·表格自動美化 ???1、表格結(jié)構(gòu)設(shè)計 ???根據(jù)人力資源中對員工信息管理和分析的需要，創(chuàng)建一個工作表，命名為“員工信息”，數(shù)據(jù)列標(biāo)題如下圖。 ???2、工號的自動連續(xù)填充 ??在單元格A2輸入公式“="G"&TEXT(ROW(A1),"0000")”，就得到能夠往下連續(xù)填充工號的計算公式。這里，ROW函數(shù)是獲取指定單元格的行號（ROW(A1)的結(jié)果就是1，ROW(A2)的結(jié)果就是2，以此類推），TEXT函數(shù)是把

6、一個數(shù)字按照指定的格式轉(zhuǎn)換為文本。那么這個公式的結(jié)果就是G0001。如果把該公式復(fù)制到單元格A3，該公式就變成了“="G"&TEXT(ROW(A2),"0000")”，結(jié)果就是G0002。 ???3、規(guī)范姓名的輸入，不允許在姓名文字中輸入空格 ??選中單元格B2，設(shè)置有效性，其有效性的自定義公式為“=SUBSTITUTE(B2,"?","")=B2”，如下圖。這里，先使用SUBSTITUTE函數(shù)把輸入的姓名中的所有空格替換掉，然后再跟輸入的姓名進行比較，如果兩者相等，表明輸入的姓名中沒有空格，否則就是有空格，就不允許輸入到單元格。 ???4、規(guī)范快速輸入部門名稱 ?

7、?公司部門是確定的，在一定時期是不會變化的，因此可以使用數(shù)據(jù)有效性來快速規(guī)范輸入部門名稱。假如企業(yè)的部門有：總經(jīng)辦、財務(wù)部、人力資源部、貿(mào)易部、后勤部、技術(shù)部、生產(chǎn)部、銷售部、信息部、質(zhì)檢部、市場部，那么選擇單元格C2，設(shè)置數(shù)據(jù)有效性，即選擇“序列”，來源為“總經(jīng)辦,財務(wù)部,人力資源部,貿(mào)易部,后勤部,技術(shù)部,生產(chǎn)部,銷售部,信息部,質(zhì)檢部,市場部”，如下圖。這樣，就為單元格設(shè)置了一個下拉列表，從下拉列表里快速選擇輸入某個部門名稱。 ???5、快速輸入學(xué)歷名稱 ??員工的學(xué)歷也是固定的幾種。假若是以下幾個：博士、碩士、本科、大專、中專、高中，那么也可以使用數(shù)據(jù)有效性來快速規(guī)范輸

8、入學(xué)歷名稱。選擇單元格D2，設(shè)置數(shù)據(jù)有效性，即選擇“序列”，來源為“博士,碩士,本科,大專,中專,高中”。 ???6、快速輸入婚姻狀況 ??單元格E2輸入員工的婚姻狀況。婚姻狀況也就兩種數(shù)據(jù)：已婚和未婚，因此也可以使用數(shù)據(jù)有效性來控制輸入，也就是在數(shù)據(jù)有效性對話框的“來源”中輸入“已婚,未婚”。 ???7、輸入不重復(fù)的18位身份證號碼 ??每個員工的身份證號碼是不重復(fù)的，并且必須是18位，因此單元格F2輸入身份證號碼時也要使用數(shù)據(jù)有效性來控制。首先將F列的單元格格式設(shè)置成文本，然后選擇單元格F2，其有效性的自定義公式 ???? =AND(LEN(F2)=18,COU

9、NTIF($F$2:F2,F2)=1) ??這里，使用LEN函數(shù)判斷輸入的身份證號碼是不是18位，即LEN(F2)=18；使用COUNTIF統(tǒng)計在前面已經(jīng)輸入的身份證號碼中，即將輸入的身份證號碼是不是還沒有輸過，即COUNTIF($F$2:F2,F2)=1；然后用AND函數(shù)把這兩個條件組合起來。如果兩個條件都成立，表明輸入的身份證號碼有效。 ???8、自動輸入性別 ??員工性別從身份證號碼中自動提取，不需要人工輸入。選擇單元格G2，輸入公式 ???? =IF(ISEVEN(MID(F2,17,1)),"女","男") ??這里，先用MID函數(shù)提取身份證號碼的第1

10、7位數(shù)字，再用ISEVEN函數(shù)判斷是否為偶數(shù)，如果是偶數(shù)，該員工性別就是女，否則就是男，判斷處理則使用了IF函數(shù)。 ???9、自動輸入出生日期 ??員工的出生日期也是從身份證號碼中自動提取，不需要人工輸入。選擇單元格H2，輸入公式 ???? =1*TEXT(MID(F2,7,8),"0000-00-00") ??這里，先用MID函數(shù)提取身份證號碼的中間8位生日數(shù)字，再用TEXT函數(shù)把這8位數(shù)字按照日期的格式轉(zhuǎn)換成文本型日期格式，最后把TEXT函數(shù)的結(jié)果乘以數(shù)字1，將文本型日期轉(zhuǎn)換為真正的日期。 ???10、自動計算年齡 ??有了出生日期，我們就可以使用DAT

11、EDIF函數(shù)自動計算年齡。選擇單元格I2，輸入下面的公式，就自動得到員工的實際年齡： ???? =DATEDIF(H2,TODAY(),"Y") ???11、規(guī)范輸入入職時間 ??入職時間是一個非常重要的數(shù)據(jù)，因為要根據(jù)這列日期計算工齡，分析流動性。由于這列日期要手工輸入，就必須規(guī)范輸入的入職時間數(shù)據(jù)合法有效，也就是要輸入正確格式的日期。選擇J2單元格，設(shè)置數(shù)據(jù)有效性，如下圖所示 ???12、自動計算本公司工齡 ??有了入職時間，我們就可以使用DATEDIF函數(shù)自動計算本公司工齡。選擇單元格K2，輸入下面的公式，就自動得到員工的本公司工齡： ?????=DA

12、TEDIF(J2,TODAY(),"Y") ???13、保證員工基本信息的完整性 ??由于B列至K列是員工的最基本信息，是不能缺少這些數(shù)據(jù)的，因此需要保證每個員工基本信息完整不缺。選擇B2單元格，把數(shù)據(jù)有效性的條件修改為 ???? =AND(SUBSTITUTE(B2,"?","")=B2,COUNTA($B1:$K1)=10) ??也就是增加了一個條件COUNTA($B1:$K1)=10，它用來判斷上一行的B列至K列的數(shù)據(jù)是否都完整了（共有10列數(shù)據(jù)） ???14、規(guī)范輸入離職時間 ??離職時間是一個非常重要的數(shù)據(jù)，因為要根據(jù)這列日期來分析離職。由于這列日

13、期要手工輸入，就必須規(guī)范輸入的離職時間數(shù)據(jù)，也就是要輸入正確格式的日期。選擇L2單元格，設(shè)置日期數(shù)據(jù)的有效性。 ???15、規(guī)范輸入離職原因 ??離職原因用來分析員工的流動性和離職狀態(tài)，因此必須規(guī)范離職原因的表述文字。假如企業(yè)對離職原因的描述是下述的文字： ??合同到期但個人不愿續(xù)簽 ??合同到期但公司不愿續(xù)簽 ??因個人原因辭職 ??因公司原因辭職 ??違反公司規(guī)定辭退 ??生產(chǎn)任務(wù)變化辭退 ??考核不合要求辭退 ??退休 ??死亡 ??其他 ??那么就可以使用有效性來快速準(zhǔn)確輸入這些描述文字。 ???16、創(chuàng)建表格，自動復(fù)

14、制有效性和公式，并自動美化表格 ??前面我們做的數(shù)據(jù)有效性、設(shè)置公式等，都是僅僅設(shè)置了第2行的單元格，并沒有選擇整列來做，因為這樣的話會使文件變得很大，復(fù)制很多公式也是很不方便。Excel提供了表格功能，不僅可以自動復(fù)制有效性和公式，還可以自動美化表格。 ??先在第1行輸入第一個人的信息，然后單擊數(shù)據(jù)區(qū)域的任意單元格，在單擊“插入”選項卡里的“表”命令（Excel 2007）或“表格”命令（Excel 2010），如圖2-20所示，即可把普通的數(shù)據(jù)區(qū)域變成了一個表格，然后再在“設(shè)計”選項卡中的“表格樣式”選擇一個自己喜歡的樣式，就可以把表格自動美化。 ??這樣，我們就得到了一個標(biāo)準(zhǔn)化規(guī)范化的員工信息管理表格，在此表格基礎(chǔ)上，就可以建立各種自動化分析模版了，比如員工屬性分析模版，員工流動性分析模版，等等。 ???下圖是員工信息基礎(chǔ)表的樣子： ???下面的三個表分別是基于此基本信息表格數(shù)據(jù)制作的員工屬性分析報告、員工流動性分析報告、以及離職分析報告。這4張工作表，就構(gòu)成了員工信息統(tǒng)計分析模版。 17 / 17