Excel技巧及花名冊

1、設計標準化規(guī)范化的Excel基礎表格，是高效數(shù)據(jù)分析的第一步，因為數(shù)據(jù)分析的源頭就是基礎表格數(shù)據(jù)。設計基礎表格的總體原則是：結構的科學性數(shù)據(jù)的易讀性匯總的便宜性分析的靈活性外觀的美觀性結構的科學性，就是要按照工作的性質，管理的內(nèi)容，數(shù)據(jù)的種類，分別設計基礎管理表格，分別保存不同數(shù)據(jù)。基礎表格要越簡單越好，那些把所有數(shù)據(jù)都裝在一個工作表中的做法是絕對不可取的。比如，要做入庫出庫管理，你會如何設計這樣的基礎表格呢？要用幾個表格來反映入庫出庫數(shù)據(jù)？每個表格要怎么保存數(shù)據(jù)？數(shù)據(jù)的易讀性包含兩個方面：利用函數(shù)讀數(shù)（取數(shù)）方便，叫函數(shù)讀數(shù)；眼睛查看數(shù)據(jù)容易，叫人工讀數(shù)。一個雜而亂的表格，是很難實現(xiàn)這兩種高

2、效讀數(shù)的。數(shù)據(jù)易讀性差的主要原因有：表格結構設計不合理；數(shù)據(jù)保存不合理；殘缺不全的表格數(shù)據(jù)結構。匯總的便宜性是指不論多大的數(shù)量，匯總要簡單方便容易。你可以問自己：我設計的工作表內(nèi)據(jù)匯總方便嗎？大量表格數(shù)據(jù)之間的匯總方便嗎？如果不方便，或者做起來非常吃力，Excel很好用的工具也用不上，那唯一的原因就是基礎表格設計有問題，不要發(fā)牢騷說Excel太難了，總是學不會，尤其是函數(shù)太難掌握了！分析的靈活性，是指不論做何種分析，要講究數(shù)據(jù)分析的靈活多變。因為我們對數(shù)據(jù)進行分析的目的，是要針對企業(yè)的數(shù)據(jù)進行深度挖掘，從不同方面找問題、找原因、找對策，這就要求基礎數(shù)據(jù)必須能夠精準反映企業(yè)的管理流程，制作的分析

3、報告也必須具有靈活性，能夠在幾分鐘內(nèi)通過轉換分析角度而得到另外一份分析報告。外觀的美觀性。不論是基礎表還是報告，都盡量要求把表格進行美化。基礎表的美化以容易管理數(shù)據(jù)標準，而報告的美化以分析結果清楚為標準。特別強調的是，不論是基礎表還是報告，很多人喜歡把數(shù)據(jù)區(qū)域加上邊框，并保持工作表默認的網(wǎng)格線。其實，我們可以取消網(wǎng)格線，而把數(shù)據(jù)區(qū)域設置為非常簡練的線條表格，并把單元格字體、顏色、邊框等進行合理的設置。下面我們以人力資源管理中一個簡單的員工信息表為例，來說明標準化表格的設計方法和技巧。這個表格的基本要求如下：員工工號從“G0001”開始編號，中間不能斷號，新入職員工依次分配連續(xù)的工號員工姓名中不

4、允許輸入空格所屬部門必須快速準確輸入企業(yè)存在的部門，要名稱統(tǒng)一學歷必須快速規(guī)范輸入婚姻狀況要快速規(guī)范輸入身份證號碼必須是18位的文本，不允許重復出生日期、年齡、性別從身份證號碼中自動提取入職時間必須是合法的日期本公司工齡自動計算得出為便于分析流動性，工作表要有離職時間和離職原因兩列數(shù)據(jù)，離職原因是固定的幾種類型新員工輸入后，該員工的工號、生日、年齡、工齡等等計算公式自動往下復制表格自動美化1、表格結構設計根據(jù)人力資源中對員工信息管理和分析的需要，創(chuàng)建一個工作表，命名為“員工信息”，數(shù)據(jù)列標題如下圖。1UC£|-I-fI飛HIKIyMI'w工W時巴HrMa'-n和樨&a

5、mp;甘%R片W號有主即出任舊年聆J網(wǎng)司*上司二#南口加空眠拿四2、工號的自動連續(xù)填充在單元格A2輸入公式“二G&TEXT(ROW(A1),)工就得到能夠往下連續(xù)填充工號的計算公式。這里，ROW函數(shù)是獲取指定單元格的行號(ROW(A1)的結果就是1,ROW(A2)的結果就是2,以此類推)，TEXT函數(shù)是把一個數(shù)字按照指定的格式轉換為文本。那么這個公式的結果就是G0001。如果把該公式復制到單元格A3,該公式就變成了“二G&TEXT(ROW(A2),結果就是G0002。3、規(guī)范姓名的輸入，不允許在姓名文字中輸入空格選中單元格B2,設置有效性，其有效性的自定義公式為甥?抽屋？吃II

6、嘔？自尬,)=B2”,如下圖。這里,先使用SUBSTITUTE函數(shù)把輸入的姓名中的所有空格替換掉，然后再跟輸入的姓名進行比較，如果兩者相等，表.明輸入的姓名中沒有空格，否則就是有空格，就不允許輸入到單元格。4、規(guī)范快速輸入部門名稱公司部門是確定的，在一定時期是不會變化的，因此可以使用數(shù)據(jù)有效性來快速規(guī)范輸入部門名稱。假如企業(yè)的部門有：總經(jīng)辦、財務部、人力資源部、貿(mào)易部、后勤部、技術部、生產(chǎn)部、銷售部、信息部、質檢部、市場部，那么選擇單元格C2,設置數(shù)據(jù)有效性，即選擇“序列”，來源為“總經(jīng)辦，財務部,人力資源部,貿(mào)易部，市場質檢部，信息部，銷售部，生產(chǎn)部，技術部，后勤部.,如下圖?！边@樣，就為單

7、元格設置了一個下拉列表，部從下拉列表里快速選擇輸入某個部門名稱。數(shù)據(jù)有效性？101I錨輸入信/I出8*I*人寓閾I行進H條件允許（由：市利W面忽略至值F提：司布俱下杓霜X于_£jI未滿田；總肋，一壽凱人力皆泡/期曷孤司aae,拉松畫II國對日同樣救鬢的所有茸也單元格應用過金更商第）II全都清除©I觸71取消5、快速輸入學歷名稱員工的學歷也是固定的幾種。假若是以下幾個：博士、碩士、本科、大專、中專、高中，那么也可以使用數(shù)據(jù)有效性來快速規(guī)范輸入學歷名稱。選擇單元格D2，設置數(shù)據(jù)有效性，即選擇“序列”，來源為“博士,碩士,本科,大專,中專,高中”。6 、快速輸入婚姻狀況單元格E2

8、輸入員工的婚姻狀況?；橐鰻顩r也就兩種數(shù)據(jù)：已婚和未婚，因此也可以使用數(shù)據(jù)有效性來控制輸入，也就是在數(shù)據(jù)有效性對話框的“來源”中輸入“已婚,未婚”。7 、輸入不重復的18位身份證號碼每個員工的身份證號碼是不重復的，并且必須是18位，因此單元格F2輸入身份證號碼時也要使用數(shù)據(jù)有效性來控制。首先將F列的單元格格式設置成文本，然后選擇單元格F2，其有效性的自定義公式=AND(LEN(F2)=18,COUNTIF($F$2:F2,F2)=1)這里，使用LEN函數(shù)判斷輸入的身份證號碼是不是18位，即LEN(F2)=18；使用COUNTIF統(tǒng)計在前面已經(jīng)輸入的身份證號碼中，即將輸入的身份證號碼是不是還沒有輸

9、過，即COUNTIF($F$2:F2,F2)=1；然后用AND函數(shù)把這兩個條件組合起來。如果兩個條件都成立，表明輸入的身份證號碼有效。8、自動輸入性別員工性別從身份證號碼中自動提取，不需要人工輸入。選擇單元格G2，輸入公式?女?男)這里，先用MID函數(shù)提取身份證號碼的第17位數(shù)字，再用ISEVEN函數(shù)判斷是否為偶數(shù)，如果是偶數(shù)，該員工性別就是女，否則就是男，判斷處理則使用了IF函數(shù)。9、自動輸入出生日期員工的出生日期也是從身份證號碼中自動提取，不需要人工輸入。選擇單元格H2，輸入公式=1*TEXT(MID(F2,7,8),這里，先用MID函數(shù)提取身份證號碼的中間8位生日數(shù)字，再用TEXT函數(shù)把

10、這8位數(shù)字按照日期的格式轉換成文本型日期格式，最后把TEXT函數(shù)的結果乘以數(shù)字1，將文本型日期轉換為真正的日期。10、自動計算年齡有了出生日期，我們就可以使用DATEDIF函數(shù)目動計算年齡。選擇單元格I2,輸入下面的公式，就自動得到員工的實際年齡：=DATEDIF(H2,TODAY(),Y)11、規(guī)范輸入入職時間入職時間是一個非常重要的數(shù)據(jù)，因為要根據(jù)這列日期計算工齡，分析流動性。由于這列日期要手工輸入，就必須規(guī)范輸入的入職時間數(shù)據(jù)合法有效，也就是要輸入正確格式的日期。選擇J2單元格，設置數(shù)據(jù)有效性，如下圖所示12、自動計算本公司工齡有了入職時間，我們就可以使用DATEDIF函數(shù)自動計算本公司

11、工齡。選擇單元格K2，輸入下面的公式，就自動得到員工的本公司工齡：=DATEDIF(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ù)這列日期來分析離職。由于這列日期要手工輸入，就必須規(guī)

12、范輸入的離職時間數(shù)據(jù)，也就是要輸入正確格式的日期。選擇L2單元格，設置日期數(shù)據(jù)的有效性。15 、規(guī)范輸入離職原因離職原因用來分析員工的流動性和離職狀態(tài)，因此必須規(guī)范離職原因的表述文字。假如企業(yè)對離職原因的描述是下述的文字：合同到期但個人不愿續(xù)簽合同到期但公司不愿續(xù)簽因個人原因辭職因公司原因辭職違反公司規(guī)定辭退生產(chǎn)任務變化辭退考核不合要求辭退退休死亡其他那么就可以使用有效性來快速準確輸入這些描述文字。16 、創(chuàng)建表格，自動復制有效性和公式，并自動美化表格前面我們做的數(shù)據(jù)有效性、設置公式等，都是僅僅設置了第2行的單元格，并沒有選擇整列來做，因為這樣的話會使文件變得很大，復制很多公式也是很不方便。E

13、xcel提供了表格功能，不僅可以自動復制有效性和公式，還可以自動美化表格。先在第1行輸入第一個人的信息，然后單擊數(shù)據(jù)區(qū)域的任意單元格，在單擊“插入”選項卡里的“表”命令(Excel2007)或“表格”命令(Excel2010)，如圖2-20所示，即可把普通的數(shù)據(jù)區(qū)域變成了一個表格，然后再在“設計”選項卡中的“表格樣式”選擇一個自己喜歡的樣式，就可以把表格自動美化。這樣，我們就得到了一個標準化規(guī)范化的員工信息管理表格，在此表格基礎上，就可以建立各種自動化分析模版了，比如員工屬性分析模版，員工流動性分析模版，等等。下圖是員工信息基礎表的樣子：SIEIM MEJffl MCHCMJLLB AJU1-

14、 NW xr-3 XOufi MQ 訓 餐日由MET陶*5里些巾土w一ilh W4B*r卡予工*rr5i| ITOLJ I -PIT 11/ 'W |*«，r.己上一皿23 _ -一一建2|M峨3匡"W 水 WET+ BFff帽 卡 4輛“髀 卜心前 l%7萬H»* »I/fl*因NbWac I-UNI2 *SFWM號:他司才*mnT1.*手才由ww«MCBJtm*s5E尊/ilLHr*I'*4”星生皋.'/J 廣產(chǎn)”工忙*,專A» n r*F_lrl1 , JI i: -i! '.d 1ffmkim

15、iiI 11rlim1 /加工加”行 Afi號巾"T?W&1中 iwMbwiMiiuwiir dFPiH 產(chǎn) 11kML1TI， i « ' "i"i a'llULf jr> iiT-'.1i 11H l4'! IB 13 用界耳tl rv* J in 'Will呻9tM mt A H4 Zme1下面的三個表分別是基于此基本信息表格數(shù)據(jù)制作的員工屬性分析報告、員工流動性分析報告、以及離職分析報告。這4張工作表，就構成了員工信息統(tǒng)計分析模版。A。c【BFGM1JKLM«0r流動性統(tǒng)計分析:U*J14J*9(171