電子表格含內容兩篇

培訓：1夏Excel在人力資源管理中的應用數(shù)據(jù)錄入01認識Excel函數(shù)02認識數(shù)據(jù)透視表03CONTENTS目錄數(shù)據(jù)錄入單元格的引用\數(shù)據(jù)有效性\條件格式1-1單元格的引用相對引用相對引用指公式中的單元格位置將隨著公式單元格的位置而改變絕對引用絕對引用是指公式和函數(shù)中的位置是固定不變的.絕對引用是在列字母和行數(shù)字之前都加上美元符號”$”,如$A$4,$C$6混合引用GO混合引用是指在一個單元格引用中,既有絕對引用,也有相對引用.例:行變列不變:$A4列變行不變:A$41-2數(shù)據(jù)的有效性

控制單元格輸入文本長度GO選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---允許---文本長度可防止身份證/銀行卡號/手機號碼輸入多一位或者少一位數(shù)序列選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---序列---在“來源”輸入（A,B,C,D)注意逗號要半角格式禁止輸入重復數(shù)據(jù)

=countif(區(qū)域,起始單元格)=1選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---允許（自定義)---公式填寫=COUNTIF(A:A,A1)=11-2數(shù)據(jù)的有效性如何取消對于“數(shù)據(jù)有效性”的設置？選中區(qū)域---數(shù)據(jù)---數(shù)據(jù)有效性---允許---任何值GO1-3條件格式利用突出的格式，經常能起到提醒的功能如：突出不滿勤的人GO突出年齡大于30的人等如何取消條件格式？認識Excel函數(shù)Excel函數(shù)的結構/常見的幾類函數(shù)/應用實例2-1.EXCEL函數(shù)的結構Excel函數(shù)即是預先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務的特殊公式。Excel函數(shù)結構:函數(shù)名語法函數(shù)結構函數(shù)作用例子運行結果1、Left1、LEFT(text,num_chars)其中：Text

是包含要提取字符的文本字符串。Num_chars

指定要由LEFT所提取的字符數(shù)。=LEFT(A1,2)從A1最左邊向右取兩位2、Right2、RIGHT(text,num_chars)其中：Text

是包含要提取字符的文本字符串。Num_chars

指定希望RIGHT提取的字符數(shù)。=RIGHT(A1,2)從A1最右邊向左取兩位3、Mid3、MID(text,start_num,num_chars)其中：Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一個字符的位置。Num_chars

指定希望MID從文本中返回字符的個數(shù)。=MID(A1,2,3)從A1左邊第2位開始取3位4、&（連接符號）XX&XX=A2&B2將A2和B2內容合并在一起常用函數(shù)之截取、合并類函數(shù)應用實例從身份證號碼提取出生日期在C2單元格中輸入=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)公式解析：MID(B2,7,4)表示提取B2單元格中從第7位開始連續(xù)4位的字符，即出生年；MID(B2,11,2)表示提取B2單元格中從第11位開始連續(xù)2位的字符，即出生月；MID(B2,11,2)表示提取B2單元格中從第13位開始連續(xù)2位的字符，即出生日；在公式中加入&可以用來連接兩個文本字符串。函數(shù)名語法函數(shù)結構函數(shù)作用FIND=FIND("龍",C6)在c6中查找龍，結果顯示龍在c6中的位數(shù)，查不到顯示#VALUE!常用函數(shù)之查找類函數(shù)參數(shù)簡單說明輸入數(shù)據(jù)類型lookup_value要查找的值數(shù)值、引用或文本字符串table_array要查找的區(qū)域數(shù)據(jù)表區(qū)域col_index_num返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)正整數(shù)range_lookup精確匹配/模糊匹配0或1函數(shù)VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)常用函數(shù)之查找類函數(shù)常用函數(shù)之查找類函數(shù)

函數(shù)VOOKUP選擇區(qū)域里最好不要有合并的單元格（先“取消單元格合并”）如果單元格里有空格，有可能導不出來

（提示：函數(shù)TRIM（A1)可以去除字符串首部和尾部的空格）注意重復的名字查找區(qū)域首列必須含有查找的內容（第一個參數(shù)）介紹vookup函數(shù)與IFERROR函數(shù)組合使用(以從8月工資表導入考勤到8月考核表為例）GO=IFERROR(VLOOKUP(A4,'8月工資'!B:F,5,0),0)

如果VOOKUP函數(shù)運行后找不到對應的數(shù)據(jù)，就會顯示為#N/A，IFERROR的作用就是把這些#N/A都變?yōu)榈?個參數(shù)。函數(shù)名語法/例子函數(shù)結構函數(shù)作用Value=value(b1)將b1數(shù)值化Numberstring=NumberString(7890,1）運行結果：七千八百九十=NumberString(7890,2)運行結果：柒仟捌佰玖拾=NumberString(7890,3)運行結果：七八九○=numberstring(b1,2)將b1數(shù)字轉為大寫漢字Row=ROW(reference)其中：Reference

為需要得到其行號的單元格或單元格區(qū)域。=row(b1)求b1是第幾行（值為1)Column=COLUMN(reference)=column(b1)求b1是第幾列(值為2)常用函數(shù)之文本類函數(shù)函數(shù)名語法函數(shù)結構函數(shù)作用len=LEN(text)其中：Text

是要查找其長度的文本?？崭駥⒆鳛樽址M行計數(shù)。=len(C2)計算C2的字符長度rankRANK(number,ref,order)Number

為需要找到排位的數(shù)字。Ref

為數(shù)字列表數(shù)組或對數(shù)字列表的引用。Ref中的非數(shù)值型參數(shù)將被忽略。Order

為一數(shù)字，指明排位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)計算b1在b1:b10區(qū)域中從大到小排第幾名；計算b1在b1:b10區(qū)域中從小到大排第幾名。常用函數(shù)之文本類函數(shù)如何根據(jù)A1、B1得出C1？應用實例在C1輸入=A1&"+"&B1如何根據(jù)A1得出B1和C1？在B1輸入=LEFT(A1,FIND("+",A1)-1)分析：思路是從左邊開始，截取“+”號前的位數(shù)。在C1輸入=RIGHT(A1,LEN(A1)-FIND("+",A1))分析：思路是截取“+”號后的位數(shù)。函數(shù)名語法函數(shù)結構函數(shù)作用IFIF(logical_test,value_if_true,value_if_false)=if(a2>0,1,0)當a2>0時，顯示1，否則顯示0IFREEOR第一個參數(shù)一般為一個函數(shù)，當這個函數(shù)運行是個錯誤值時（#N/A

#DIV/0!

#N/A

#VALUE!#NAME

#NULL!

#NUM!

#REF!），運行結果等于第二個參數(shù)=iferror(vookup(A1,A:B,2,0),0)如果VLOOKUP函數(shù)運行值是個錯誤值的話（找不到數(shù)據(jù)），就等于0，否則，就等于VLOOKUP函數(shù)運行的值。ANDAND(logical1,logical2,...)Logical1,logical2,...

表示待檢測的1到30個條件值，各條件值可為TRUE或FALSE。=and(a3>0,b3<0)當A3>0,并且b3<0時，顯示true，否則顯示falseOROR(logical1,logical2,...)Logical1,logical2,...

表示待檢測的1到30個條件值，各條件值可為TRUE或FALSE。=or(a4>0,b4<0)當a4>0,或者b4<0時，都顯示true，否則顯示false常用函數(shù)之判斷類函數(shù)分數(shù)段等級分數(shù)≥90分A80分≤分數(shù)＜90分B70分≤分數(shù)＜80分C分數(shù)＜70分D區(qū)域段自動算出對應等級=IF(K4>=90,"A",IF(K4>=80,"B",IF(K4>=70,"C","D")))嵌套了兩個IF函數(shù)IF函數(shù)的應用實例函數(shù)名語法函數(shù)結構函數(shù)作用SUMIFSUMIF(range,criteria,sum_range)其中：Range

為用于條件判斷的單元格區(qū)域。Criteria

為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為32、"32"、">32"或"apples"。Sum_range

是需要求和的實際單元格。=sumif(B:B,H1,E:E)當B列滿足了條件（HI），對應的E列的單元格的數(shù)值之和GOROW求余數(shù)的函數(shù)，當?shù)诙€參數(shù)為2時，常用于判斷奇偶數(shù)，第二個參數(shù)為2時，運行結果為0是偶數(shù)，1是奇數(shù)=ROW(5，2)求5除以2的余數(shù)是多少，本例的運行結果是1常用函數(shù)之求和類函數(shù)應用實例從身份證號碼中提取性別（身份證倒數(shù)第2位若為偶數(shù)為女性，奇數(shù)為男性）在C2單元格中輸入=IF(MOD(MID(B2,17,1),2)=1,"男","女")公式分析：MID(B2,17,1)是從身份證第17位起取1位字符；在本例即為數(shù)字5，MOD函數(shù)就是取余數(shù)的函數(shù)，在本例中就是MOD(5，2),5除以2的余數(shù)，最外面是一個IF函數(shù)，當這個余數(shù)為1時，即為奇數(shù)，奇數(shù)就是男性，否則為女性。函數(shù)名語法函數(shù)結構函數(shù)作用1、NOW=now()顯示當前的日期+時間2、TODAY=today()顯示今天的日期3、WEEKDAY=weekday(b1,2)判斷b1為星期幾4、DAY=day(b1)顯示b1的號數(shù)5、MONTH=month(b1)顯示b1的月數(shù)6、YEAR=year(b1)顯示b1的年數(shù)常用函數(shù)之日期類函數(shù)函數(shù)名以單元格b1為2013-4-5為例的運行結果函數(shù)結構函數(shù)作用WEEKDAY6=weekday(b1,2)判斷b1為星期幾DAY5=day(b1)顯示b1的號數(shù)MONTH4=month(b1)顯示b1的月數(shù)YEAR2013=year(b1)顯示b1的年數(shù)常用函數(shù)之日期類函數(shù)常用函數(shù)之日期類函數(shù)DATEDIF函數(shù)常用于計算兩日期的時間差=DATEDIF(較早的日期，較晚的日期，第三個參數(shù)）第三個參數(shù)：“Y”---兩日期間共多少年。“M”----兩日期間共多少個月?！癉”----兩日期間共多少天。“MD”是兩日期中天數(shù)的差。忽略日期中的月和年?！癥M”是兩日期中月數(shù)的差。忽略日期中的年。“YD”是兩日期中天數(shù)的差。忽略日期中的年。常用函數(shù)之日期類函數(shù)舉例:算兩個日期間差幾年、幾月、幾日開始時間是2012-2-21結束時間是2014-2-19C2=DATEDIF(A2,B2,"Y")D2=DATEDIF(A2,B2,"YM")E2=DATEDIF(A2,B2,"MD")F2=DATEDIF(A2,B2,"D")G2=DATEDIF(A2,B2,"M")H2=DATEDIF(A2,B2,“YD")I2=C2&"年"&D2&"個月"I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“個月"常用函數(shù)之日期類函數(shù)應用實例3從出生日得出年齡在D2單元格中輸入=DATEDIF(C2,NOW(),“y”)因為從身份證提取出生日的C2=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)所以若直接從身份證得出年齡D2=DATEDIF(MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2),NOW(),"y")培訓協(xié)議服務期限完成提醒思路：過期了可以顯示“協(xié)議期限完成”沒過期的，顯示“沒過期”=IFERROR(IF(DATEDIF(B2,TODAY(),"d")>=0,"協(xié)議期限完成",),"沒過期")分析：DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就會產生錯誤值，如果“今天”在B2之后，這個函數(shù)就會算出B2距離“今天”有多少天。當這個天數(shù)≥0時，就會執(zhí)行IF函數(shù)，顯示“協(xié)議期限完成”，當DATEDIF運行是一個錯誤值時，這時候就執(zhí)行IFERROR函數(shù)，顯示“沒過期”。如果我想到期前一個月就提醒呢？應用實例思路：=IFERROR(IF(DATEDIF(TODAY(),B2,"D")<=31,"少于31天后就會過期","多于31天"),"已過期")多于今天超過31天的顯示“多于31天”多于今天不超過31天的顯示“少于31天后就會過期”比今天早的顯示“已過期”DATEDIF函數(shù)能運行，說明B2晚于今天能執(zhí)行IF函數(shù)當B2晚于今天≤31天顯示“少于31天后就會過期”當B2晚于今天＞31天顯示“多于31天”不能運行，B2早于今天（培訓期限過期了）DATEDIF函數(shù)會產生錯誤值IF函數(shù)也會產生錯誤值執(zhí)行IFERROR函數(shù)顯示“已過期”函數(shù)名語法函數(shù)結構函數(shù)作用1、COUNT=Count(a1:a10)計a1:a10中數(shù)字的個數(shù)2、COUNTA=Counta(a1:a10)計a1:a10中非空單元格的個數(shù)，（0也算）3、COUNTBLANK=Countblank(a1:a10)計a1:a10中空白單元格的個數(shù)常用函數(shù)之計數(shù)類函數(shù)常用函數(shù)之計數(shù)類函數(shù)COUNTIF函數(shù)COUNTIF(range,criteria)區(qū)域條件標準是一個數(shù)數(shù)的函數(shù)，可以用于數(shù)出參數(shù)1區(qū)域內，符合參數(shù)2的條件的單元格個數(shù)。如：=countif(F3:F10,“女”)統(tǒng)計F3:F10中女的個數(shù)。COUNTIF函數(shù)的應用實例應用實例在9月流動表中找出有相同名字的人步驟1：打開9月流動表，找出9月在職的工作表，在姓名右邊新建一列，插入函數(shù)，找到COUNTIF函數(shù)，選擇“確定”。COUNTIF函數(shù)的應用實例這個的意思就是在9月在職的人中只有一個叫嚴邦平的點擊Range的框格，選中E列，點擊Criteria的框格，點擊E2單元格，點擊“確定”。點擊F2單元格，把鼠標移到F2右下角，鼠標箭頭變?yōu)楹谏值臅r候雙擊。COUNTIF函數(shù)的應用實例步驟4：選中F列---排序與篩序---篩選---點擊F1左上角小箭頭---去掉1前的小勾---確定GOCOUNTIF函數(shù)的應用實例統(tǒng)計一定區(qū)間的個數(shù)如：統(tǒng)計9月流動表“9月在職”工作表中，≥50歲以上的人數(shù)：=COUNTIF(F:F,">=50")20歲≤年齡＜40歲的人數(shù)：=COUNTIF(F:F,">=20")-COUNTIF(F:F,">=40")（注意：如果要統(tǒng)計的數(shù)據(jù)是有一個綠色的左角的，

要先選擇要統(tǒng)計的數(shù)據(jù)，點擊符號，選擇

“轉換為數(shù)字”，不然是統(tǒng)計不了個數(shù)的）GO課程大綱數(shù)據(jù)錄入單元格的引用數(shù)據(jù)有效性條件格式認識Excel函數(shù)Excel函數(shù)的結構常見的幾類函數(shù)應用實例認識數(shù)據(jù)透視表數(shù)據(jù)透視表是什么數(shù)據(jù)透視表的幾個簡單應用實例認識數(shù)據(jù)透視表數(shù)據(jù)透視表是什么/數(shù)據(jù)透視表的幾個簡單應用實例認識數(shù)據(jù)透視表一種快速分析數(shù)據(jù)的工具數(shù)據(jù)透視表是一種交互，交叉制作的報表，一組具有多個字段的數(shù)據(jù)進行多立體的分析匯總，用于對多種來源的數(shù)據(jù)進行匯總和分析，從而可以快速合并和比較大量數(shù)據(jù)。當數(shù)據(jù)規(guī)模比較大時，這種分析的意義就顯得尤為突出。說人話前提這些數(shù)據(jù)使用數(shù)段表進行管理認識數(shù)據(jù)透視表統(tǒng)計9月入職的招聘來源分布統(tǒng)計離職原因分布個數(shù)統(tǒng)計各部門平均工資統(tǒng)計各部門學歷分布情況數(shù)據(jù)透視表可以快速解決這些事情統(tǒng)計9月入職的招聘來源分布步驟1：打開工作表---選擇數(shù)據(jù)來源區(qū)域（A1：i79）---插入---數(shù)據(jù)透視表---確定統(tǒng)計9月入職的招聘來源分布步驟2：把招聘來源拖到下面的“行標簽”框，把序號拖到下面的“數(shù)值”框，就能快速生成下圖。統(tǒng)計離職原因分布個數(shù)通過雙擊B3，出來“值字段對話框”，選擇“計數(shù)”同理，可根據(jù)前面介紹的步驟在辭工名單的表格中快速做出離職原因分布的表格GO拖“離職原因”到“行標簽”框，拖“序號”到“數(shù)值”框注意：要留意“數(shù)值”框，是求和，還是計數(shù)，還是其他，左圖的默認是“求和”，而我們是想“計數(shù)”。這是原數(shù)據(jù)這是生成的數(shù)據(jù)透視表統(tǒng)計出不同部門工資總和、平均工資行標簽---部門數(shù)值---求和項：工資，平均值項(值字段設置可找到）：工資想想：如果在列標簽先拖入性別，再拖入最高學歷，數(shù)據(jù)透視表會怎樣統(tǒng)計各部門學歷分布情況行標簽---部門，列標簽---最高學歷數(shù)值---計數(shù)項：最高學歷Excel在人力資源管理中的應用EXCEl的使用技巧.新.46EXCEL學習介紹EXCEL基本操作EXCEL函數(shù)EXCEL圖表內容提綱.新.47EXCEL基本操作掌握EXCEL菜單命令用法掌握常用快捷鍵.新.48EXCEL基本操作單元格了解

主要了解單元格周圍的小圖標鼠標放在單元格邊框上可以拖動單元格，放在右下角可以向上/下或者左/右復制單元格，放在右下角按住SHIFT可以插入單元格。.新.49EXCEL基本操作數(shù)據(jù)錄入

1.身份證號碼輸入（設置單元格屬性為“文本”或者前面加’）

2.分數(shù)輸入（直接輸1/3EXCEL默認為1月3日，應該寫成0空格1/3）

3.平方、立方的輸入(右鍵選擇單元格格式，選上標、下標)4.單元格里回車（alt+enter）.新.50EXCEL基本操作查找

1.模糊查找按CTRL+F進入查找，在查找里直接輸查找內容為模糊查找。

2.精確查找在查找里點開選項，將單元格匹配的勾打上為精確查找

3.值查找在查找里點開選項，查找范圍選擇值為值查找。（如果單元格里是公式那必須用值查找）.新.51EXCEL基本操作常用功能

1.凍結窗口光標放在我們需要固定范圍旁邊的單元格上，選擇菜單“窗口”-“凍結窗口”。

2.排序選擇“數(shù)據(jù)”-“排序”

3.篩選選擇“數(shù)據(jù)”-”篩選“4.插入對象選擇“插入”-“對象”

5.下拉菜單制作點到你要制作下拉菜單的單元格，選擇“數(shù)據(jù)”-“有效性”，在允許框中選“序列”，在來源框中選擇數(shù)據(jù)范圍或者直接輸入值，主要在直接輸入值時要用“，”隔開。.新.52EXCEL函數(shù)什么是函數(shù)

Excel函數(shù)即是預先定義，執(zhí)行計算、分析等處理數(shù)據(jù)任務的特殊公式。以常用的求和函數(shù)SUM為例，它的語法是“SUM(number1,number2,......)”。其中“SUM”稱為函數(shù)名稱，一個函數(shù)只有唯一的一個名稱，它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號，接著是用逗號分隔的稱為參數(shù)的內容，最后用一個右括號表示函數(shù)結束。什么是公式

函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預先定義好的特殊公式，后者就是由用戶自行設計對工作表進行計算和處理的公式。以公式“=SUM(E1:H1)*A1+26”為例，它要以等號“=”開始，其內部可以包括函數(shù)、引用、運算符和常量。上式中的“SUM(E1:H1)”是函數(shù)，“A1”則是對單元格A1的引用(使用其中存儲的數(shù)據(jù))，“26”則是常量，“*”和“+”則是算術運算符。.新.53EXCEL函數(shù)基礎概念—引用

1.認識工作表區(qū)域

EXCEL共有256列，65536行。

2.單元格引用方式

相對應用A1、B13.單元格引用分類一維引用A1:A10,A1:F1

二維引用A1:C10

三位引用sheet1：sheet3！A1.新.54EXCEL函數(shù)常用函數(shù)

SUM，SUMIF，MAX，COUNT/COUNTA/COUNTIF，ROUND/ROUNDUP/ROUNDDOWN，HEX2DEC/DEC2HEX，LEFT/RIGHT，AVERAGE，VLOOKUP，LEN，MID，SUBSTITUTE，IF，AND/OR.新.55EXCEL函數(shù)SUM用途：返回某一單元格區(qū)域中所有數(shù)字之和。語法：SUM(number1，number2，...)。參數(shù)：Number1，number2，...為1到N個需要求和的數(shù)值。用法舉例：.新.56EXCEL函數(shù)SUMIF用途：根據(jù)指定條件對若干單元格、區(qū)域或引用求和。語法：SUMIF(range，criteria，sum_range)參數(shù)：Range為用于條件判斷的單元格區(qū)域，Criteria是由數(shù)字、邏輯表達式等組成的判定條件，Sum_range為需要求和的單元格、區(qū)域或引用。用法舉例：.新.57EXCEL函數(shù)MAX用途：返回數(shù)據(jù)集中的最大數(shù)值。語法：MAX(number1，number2，...)參數(shù)：Number1，number2，...是需要找出最大數(shù)值的1至N個數(shù)值。用法舉例：如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、