EXCEL基礎(chǔ)知識(shí)及在勞資工作中的常用技巧

基礎(chǔ)知識(shí)及在勞資工作中的常用技巧2012年9月26日一、EXCEL2007基本操作界面介紹二、常用快捷鍵及常用函數(shù)1、常用快捷鍵三、EXCEL2007常用功能點(diǎn)2、常用函數(shù)四、透視表介紹及基本操作

（引用及函數(shù)概念介紹）標(biāo)題欄菜單欄工具欄名稱(chēng)框擴(kuò)展箭頭列標(biāo)活動(dòng)單元格工作表標(biāo)簽G22G:列表22：行號(hào)工作表插入按鈕行號(hào)滾動(dòng)條縮放控件頁(yè)面視圖控件工作表標(biāo)簽滾動(dòng)按鈕快速訪問(wèn)工具欄Office按鈕一、2007基本操作界面介紹幫助按鈕插入函數(shù)按鈕函數(shù)編輯欄一、2007基本操作界面介紹2003與2007交互界面：？一、2007基本操作界面介紹工作?。何臋n就是工作簿。如“案例講解”工作表：顯示在工作簿窗口中的表格。如“2”、“3”單元格：組成工作表的最小單位。如“G22”單元格區(qū)域：連續(xù)的單元格。如“A916”行號(hào)：如“G22”的22就是行號(hào)列表：如“G22”的G就是列標(biāo)公式：工作表中進(jìn)行數(shù)值計(jì)算的等式如“=3*6-2”函數(shù)：預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。如求和（）、平均值（）、最大值（）等基本概念介紹一、2007基本操作界面介紹基本概念介紹算術(shù)運(yùn)算符含義（示例）+（加號(hào)）加法運(yùn)算(3+3)–（減號(hào)）減法運(yùn)算(3–1)、負(fù)(–1)*（星號(hào)）乘法運(yùn)算(3*3)/（正斜線）除法運(yùn)算(3/3)%（百分號(hào)）百分比(20%)^（插入符號(hào)）乘冪運(yùn)算(3^2)比較運(yùn)算符含義（示例）=（等號(hào)）等于(A11)>（大于號(hào)）大于(A1>B1)<（小于號(hào)）小于(A1<B1)>=（大于等于號(hào)）大于或等于(A1>1)<=（小于等于號(hào)）小于或等于(A1<1)<>（不等號(hào)）不相等(A1<>B1)一、2007基本操作界面介紹基本概念介紹文本運(yùn)算符含義（示例）&（和號(hào)）將兩個(gè)文本值連接或串起來(lái)產(chǎn)生一個(gè)連續(xù)的文本值(“浙江”&“煙草")引用運(yùn)算符含義（示例）:（冒號(hào)）區(qū)域運(yùn)算符，產(chǎn)生對(duì)包括在兩個(gè)引用之間的所有單元格的引用(B515),（逗號(hào)）聯(lián)合運(yùn)算符，將多個(gè)引用合并為一個(gè)引用((B515515))

（空格）交叉運(yùn)算符產(chǎn)生對(duì)兩個(gè)引用共有的單元格的引用。(B77C68)二、常用快捷鍵及常用函數(shù)1、常用快捷鍵快捷鍵含義復(fù)制粘貼剪切全選打印查找替代強(qiáng)行換行滾輪放大縮小保存撤消上一步定位F4重復(fù)上一步操作二、常用快捷鍵及常用函數(shù)引用的概念類(lèi)型解釋絕對(duì)引用：?jiǎn)卧裰械慕^對(duì)單元格引用（例如$G$22）總是在指定位置引用單元格G22。如果公式所在單元格的位置改變，絕對(duì)引用的單元格始終保持不變。相對(duì)引用：公式中的相對(duì)單元格引用（例如A1）是基于包含公式和單元格引用的單元格的相對(duì)位置。如果公式所在單元格的位置改變，引用也隨之改變?；旌弦茫壕哂薪^對(duì)列和相對(duì)行，或是絕對(duì)行和相對(duì)列。絕對(duì)引用列采用$A1、$B1等形式。案例講解：《單元格引用》實(shí)際操作：《全省系統(tǒng)工資發(fā)放情況統(tǒng)計(jì)表》F4鍵在修改引用類(lèi)型中的妙用二、常用快捷鍵及常用函數(shù)函數(shù)的概念函數(shù)概念:是一些預(yù)定義的公式，它們使用一些稱(chēng)為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算。函數(shù)結(jié)構(gòu)：=函數(shù)(參數(shù),參數(shù),……)例如：(1,2,3)，(A1>B1,”大于”,”不大于”)注意:“=(,”必須是英文的格式結(jié)構(gòu)符函數(shù)名稱(chēng)參數(shù)分隔符函數(shù)輸入兩種方式：A選擇函數(shù)B手工輸入二、常用快捷鍵及常用函數(shù)2.1

Vlookup函數(shù)函數(shù)含義：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組中該數(shù)值所在行中指定列處的數(shù)值公式：()對(duì)應(yīng)為：（要查找的值，要查找的區(qū)域，返回?cái)?shù)據(jù)在區(qū)域的第幾列數(shù)，精確匹配）說(shuō)明：“要查找的值”一般情況下須位于“要查找的區(qū)域”的第一列“精確匹配”包括“1”和“0”，“0”即“一模一樣”才進(jìn)行匹配。常用范圍：人員信息查找、工資數(shù)據(jù)查找例題：案例操作二、常用快捷鍵及常用函數(shù)2.1

Vlookup函數(shù)①②③④⑤⑥二、常用快捷鍵及常用函數(shù)2.2

Datedif函數(shù)函數(shù)含義：返回兩個(gè)日期之間的年\月\日間隔數(shù)語(yǔ)法公式：（）對(duì)應(yīng)為：（起始日期,結(jié)束日期,返回類(lèi)型）說(shuō)明：返回類(lèi)型包括“Y”、“M”、“D”、“”、“”、"""Y"時(shí)間段中的整年數(shù)，"M"時(shí)間段中的整月數(shù)，"D"時(shí)間段中的天數(shù)。""日期中月數(shù)的差。忽略日期中的日和年。""日期中天數(shù)的差。忽略日期中的年。""日期中天數(shù)的差。忽略日期中的月和年。常用范圍：工齡計(jì)算、年齡計(jì)算、年休計(jì)算。例題：計(jì)算出生日期為1983-4-1人的年齡公式：("1973-4-1"(),"Y")計(jì)算日期為1983-4-1和當(dāng)前日期的間隔月份數(shù)公式：("1973-4-1"(),"M")計(jì)算日期為1983-4-1和當(dāng)前日期的間隔天數(shù)公式：("1973-4-1"(),"D")二、常用快捷鍵及常用函數(shù)2.2

Datedif函數(shù)起始日期結(jié)束日期返回類(lèi)型函數(shù)名稱(chēng)結(jié)構(gòu)符二、常用快捷鍵及常用函數(shù)2.3

Sum等函數(shù)函數(shù)含義：（求和）、（平均值）、（最大值）、（最小值）公式：(12,...)對(duì)應(yīng)為：(數(shù)字1,數(shù)字2,……)說(shuō)明：常用范圍：日常工資統(tǒng)計(jì)、比賽分?jǐn)?shù)統(tǒng)計(jì)例題：完成《第三屆卷煙商品營(yíng)銷(xiāo)職業(yè)技能競(jìng)賽評(píng)分表》成績(jī)統(tǒng)計(jì)二、常用快捷鍵及常用函數(shù)2.4

Rank等函數(shù)函數(shù)含義：返回一個(gè)數(shù)字在數(shù)字列表中的排位公式：()對(duì)應(yīng)為：(需要排位的數(shù)字,為數(shù)字列表數(shù)組,排位的方式)說(shuō)明：排位的方式包括“1”和“0”，“1”為升序排列，“0”為降序排序

常用范圍：民主測(cè)評(píng)排序、比賽成績(jī)排序、績(jī)效考核排序例題：完成《第三屆卷煙商品營(yíng)銷(xiāo)職業(yè)技能競(jìng)賽評(píng)分表統(tǒng)計(jì)》名次排序二、常用快捷鍵及常用函數(shù)2.5

Left等函數(shù)函數(shù)含義：左截取函數(shù)、右截取函數(shù)、中間截取函數(shù)公式：(,)、(,)、（）對(duì)應(yīng)為：(字符串,截取個(gè)數(shù))、(字符串,截取個(gè)數(shù))、（字符串,第幾個(gè)開(kāi)始,截取個(gè)數(shù)）說(shuō)明：(“浙江煙草”,2)顯示為“浙江”（“浙江煙草”,2,2）顯示為“江煙”常用范圍：身份證出生日期的截取、姓氏截取等例題：截取十八位身份證的出生年份、出生月份、出生日二、常用快捷鍵及常用函數(shù)2.6

Concatenate函數(shù)函數(shù)含義：合并文本字符串。等同于”＆”公式：(1,[2],...)等同于1＆2對(duì)應(yīng)為：(文本1,文本2,...)說(shuō)明：("浙江","煙草"）顯示為“浙江煙草”等同于：=”浙江”＆”煙草”(”釣魚(yú)島”,”是中國(guó)的”,”固有領(lǐng)土”）顯示為“釣魚(yú)島是中國(guó)的固有領(lǐng)土”等同于：=”釣魚(yú)島”&”是中國(guó)的”&”固有領(lǐng)土”常用范圍：出生日期的組合等例題：將上題截取的出生年份、出生月份、出生日組合成如下格式“1981-05-03”二、常用快捷鍵及常用函數(shù)2.7

IF函數(shù)函數(shù)含義：執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果公式：()對(duì)應(yīng)為：(邏輯判斷,判斷成立返回值,判斷不成立返回值)說(shuō)明：(A1>=60,”及格”,”不及格”）即如果A1單元格的值大于或等于60，返回及格，否則返回不及格

常用范圍：非常廣泛！例題：二、常用快捷鍵及常用函數(shù)2.7

IF函數(shù)①②③④⑤⑥二、常用快捷鍵及常用函數(shù)2.8

常用公式組合功能公式個(gè)稅計(jì)算公式((A1-3500)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2755,5505,13505},0)出生日期計(jì)算年齡(A1(),”Y”)身份證提取性別((A1)=15(((A1,15,1),2)=1,"男","女")(((A1,17,1),2)=1,"男","女"))身份證提取出生日期((A1)=15("19"(A1,7,2),"-"(A1,9,2),"-"(A1,11,2))((A1,7,4),"-"(A1,11,2),"-"(A1,13,2)))身份證計(jì)算年齡(((A1)=15("19"(A1,7,2),"-"(A1,9,2),"-"(A1,11,2))((A1,7,4),"-"(A1,11,2),"-"(A1,13,2)))(),"y")三、2007常用功能點(diǎn)3.1排序功能排序功能1、對(duì)《工資表》按照職級(jí)、系數(shù)分別進(jìn)行排序；2、對(duì)《工資表》按照姓名的拼音順序排序；3、對(duì)《工資表》按照職級(jí)升序、系統(tǒng)降序排序；4、對(duì)《工資表》按照籍貫順序（杭州、寧波、溫州、嘉興、湖州、紹興、金華、衢州、麗水、臺(tái)州、舟山）、職級(jí)升序、系數(shù)升序排序。三、2007常用功能點(diǎn)3.2篩選功能篩選功能1、對(duì)《工資表》篩選2012年2月工資發(fā)放清單（日期篩選）；2、對(duì)《工資表》篩選籍貫是杭州、寧波、溫州人員發(fā)放清單（文本篩選）；3、對(duì)《工資表》篩選個(gè)人實(shí)發(fā)3000元以上和高于平均收入清單（數(shù)字篩選）；4、對(duì)《工資表》篩選2012年1月籍貫非杭州且個(gè)人實(shí)發(fā)少于5000元的女性人員名單；5、對(duì)《工資表》篩選姓王且姓名只有兩個(gè)字的人員名單；（“*”代表不限長(zhǎng)度的任意內(nèi)容，“？”代表僅為一個(gè)字符的任意內(nèi)容）三、2007常用功能點(diǎn)3.3分列①②三、2007常用功能點(diǎn)3.4刪除重復(fù)項(xiàng)①②③三、2007常用功能點(diǎn)3.5數(shù)據(jù)有效性①②③三、2007常用功能點(diǎn)3.6合并計(jì)算①②④③⑤⑥三、2007常用功能點(diǎn)3.7條件格式顯示實(shí)發(fā)高于3000元的顯示績(jī)效工資在2000至4000元的顯示籍貫是嘉興地區(qū)的顯示姓名重復(fù)的人員名單三、2007常用功能點(diǎn)3.7條件格式顯示實(shí)發(fā)工資最高的十個(gè)顯示實(shí)發(fā)工資高于平均值的思考：如僅需顯示實(shí)發(fā)工資前6的如何操作？三、2007常用功能點(diǎn)3.7條件格式用數(shù)據(jù)條顯示個(gè)人實(shí)發(fā)清除上述個(gè)人實(shí)發(fā)的數(shù)據(jù)條格式選擇性粘貼選項(xiàng)選項(xiàng)含義全部粘貼所復(fù)制的數(shù)據(jù)的所有單元格內(nèi)容和格式數(shù)值僅粘貼在單元格顯示的數(shù)據(jù)值格式僅粘貼所復(fù)制數(shù)據(jù)的單元格格式轉(zhuǎn)置將所復(fù)制數(shù)據(jù)的行變成列、列變成行三、2007常用功能點(diǎn)3.8選擇性粘貼①②三、2007常用功能點(diǎn)3.9常用排版技巧表格剛好有點(diǎn)大，想打印在一個(gè)頁(yè)面里?。勘砀癖容^長(zhǎng)，想每一頁(yè)都打印標(biāo)題行??？表格特別大，想固定住某一行或列進(jìn)行查看?。咳绾谓o表格加入頁(yè)碼？三、2007常用功能點(diǎn)3.9常用排版技巧如何將《工資表》按照部門(mén)分頁(yè)打??？如何快速地復(fù)制字體？18位身份證號(hào)碼輸入后顯示不正常？如何將文字豎直排版？右鍵右鍵如何為數(shù)據(jù)注明信息？右鍵（或者在數(shù)字前加“’”）四、透視表介紹及基本操作透視表的幾個(gè)特點(diǎn)：是集排序、篩選、組合、匯總等多個(gè)功能于一體的交互式報(bào)表有強(qiáng)大、靈活的數(shù)據(jù)處理功能熟練掌握該項(xiàng)功能能給你的工作效率顯著的提高是的一個(gè)重點(diǎn)、難點(diǎn)是衡量掌握熟練程度的一個(gè)指標(biāo)初識(shí)透視表數(shù)據(jù)透視表的概念：是一種對(duì)大量數(shù)據(jù)快速匯總和建立交叉列表的交互式動(dòng)態(tài)表格，能幫助用戶(hù)分析、組織數(shù)據(jù)。四、透視表介紹及基本操作制作簡(jiǎn)單透視表第一步：選擇數(shù)據(jù)區(qū)域第二步：插入—數(shù)據(jù)透視表—數(shù)據(jù)透視表第三步：確定四、透視表介紹及基本操作字段列表字段控制窗口編輯顯示窗口四、透視表介紹及基本操作第四步：選擇字符段第五步：調(diào)整順序手工篩選按鈕四、透視