Excel操作與數(shù)據(jù)技巧

培訓(xùn)科張其建2012-6-24EXCEL實(shí)戰(zhàn)技巧1EXCEL簡(jiǎn)介2EXCEL常用操作及功能3EXCEL函數(shù)使用技巧4數(shù)據(jù)透視表5公式及函數(shù)6數(shù)組的運(yùn)用報(bào)告目錄宏與簡(jiǎn)單VBA中級(jí)高級(jí)專家級(jí)初級(jí)功能區(qū)：新的控制中心，匯集了一些基本的命令并使這些命令非常直觀。1、EXCEL簡(jiǎn)介你用2003？不一樣？沒有關(guān)系，我這里為大家提供一個(gè)03-07對(duì)照工具Excel2007中有許多更改。其中以窗口頂部的更改最為明顯。功能區(qū)的三個(gè)組成部分是選項(xiàng)卡、組和命令。選項(xiàng)卡：選項(xiàng)卡呈現(xiàn)了可在Excel中執(zhí)行的核心任務(wù)。組：組是指顯示在選項(xiàng)卡上的相關(guān)命令集。命令：命令是指按鈕、菜單或信息輸入框。1、EXCEL簡(jiǎn)介隱藏的命令功能區(qū)上的命令是最常用的命令。Excel2007并不總是顯示每一項(xiàng)命令，只有在可能需要使用某些命令時(shí)，才會(huì)顯示這些命令，以響應(yīng)所執(zhí)行的操作。1、EXCEL簡(jiǎn)介更多的選項(xiàng)單擊對(duì)話框啟動(dòng)器，您就會(huì)看到一個(gè)對(duì)話框或任務(wù)窗格。1、EXCEL簡(jiǎn)介對(duì)話框啟動(dòng)器在首次啟動(dòng)Excel2007時(shí)，快速訪問工具欄位于功能區(qū)上方。在快速訪問工具欄中，命令始終可見.我們可以把一些常用的命令列入快速工具欄中方便應(yīng)用?？焖僭L問工具欄1、EXCEL簡(jiǎn)介鍵盤快捷方式功能區(qū)上的每一個(gè)按鈕都有快捷方式。新快捷方式都有提示。以Ctrl開頭的舊鍵盤快捷方式?jīng)]有變化。1、EXCEL簡(jiǎn)介新視圖在工作表直接看到：行/列標(biāo)題、頁邊距標(biāo)尺。在打印之前無需使用“打印預(yù)覽”來發(fā)現(xiàn)問題。添加頁眉和頁腳比以前更加簡(jiǎn)捷。1、EXCEL簡(jiǎn)介兼容性1、EXCEL簡(jiǎn)介類型97-20032007不帶宏的工作簿.xls.xlsx帶宏的工作簿.xls.xlsm文件格式為方便在使用新版excle時(shí)，可以設(shè)置中默認(rèn)的文件保存格式強(qiáng)烈推薦所有正在使用Excel2003的同事們，使用EXCEL2007或20101EXCEL簡(jiǎn)介2EXCEL常用操作及功能3EXCEL函數(shù)使用技巧4數(shù)據(jù)透視表5公式及函數(shù)6數(shù)組的運(yùn)用報(bào)告目錄宏與簡(jiǎn)單VBA2、EXCEL常用操作及功能數(shù)據(jù)錄入TAB與ENTER數(shù)據(jù)錄入時(shí)檢查錯(cuò)誤：讓EXCEL讀出你的輸入內(nèi)容一般情況下：TAB-向右；ENTER-向下可調(diào)整ENTER屬性選定需要輸入?yún)^(qū)域一直按ENTER可順序輸入在選項(xiàng)欄中選擇：按ENTER鍵開始朗讀單元格相對(duì)位置:A2 F13C7絕對(duì)位置:列絕對(duì)：$A2、$F13、$C7行絕對(duì)：A$2、F$13、C$7絕對(duì)位置：$A$2、$F$13、$C$7引用“$”表示對(duì)后面所表達(dá)的列或行的鎖定。注意：引用在我們拖動(dòng)公式或填充時(shí)有重大的影響只有理解了應(yīng)用，你才能更好的使用公式2、EXCEL常用操作及功能引用－名稱命名與引用我們可以對(duì)某些單元格定義名稱后直接使用名稱，以簡(jiǎn)化公式內(nèi)容。2、EXCEL常用操作及功能填充當(dāng)數(shù)據(jù)量少的時(shí)候：直接采用鼠標(biāo)拖拉即可。填充時(shí)尤其要注意引用方式右鍵也是可以填充的當(dāng)數(shù)據(jù)量多的時(shí)候：需要采用鍵盤來操作（如果采取鼠標(biāo)填充的方式，就會(huì)拖到”地久天長(zhǎng)”)具體快捷鍵：快速移動(dòng)：ctrl+方向鍵（↑↓→←）快速選中：ctrl+shift+方向鍵（↑↓→←）快速填充：ctrl+D（向下）ctrl+R（向右）2、EXCEL常用操作及功能左鍵填充右鍵填充復(fù)制復(fù)制駛公式屑、復(fù)水制數(shù)童據(jù)、亞復(fù)制顧格式行列蘭互換不同明的復(fù)葡制方經(jīng)式能非起到袋神奇幅的工信效。2、EX刃CE胃L常用派操作廣及功撐能選定遇到返數(shù)據(jù)通中太歇多的酸空格漏時(shí)，嘩有一寶種抓吊狂的曾感覺F5：定技位條目件可樓以為套你帶五來很云多快保樂2、EX苦CE結(jié)L常用單操作撇及功松能數(shù)據(jù)柔有效暖性可以愁幫助爹我們新設(shè)置被可選把的菜錯(cuò)單，極避免細(xì)五花劈燕八門道的描突述產(chǎn)召生。2、EX仍CE塊L常用丑操作授及功肆能排序行也銜是可訴以排朗序的漢字映可以革按筆菊劃排壟序2、EX漸CE毀L常用傭操作勞及功千能排序飽－自啞定義福序列OF盡FI導(dǎo)CE按鈕E夏XC伍EL選項(xiàng)常用編輯計(jì)自定臭義列盟表設(shè)定調(diào)按照途自己符順序廚的排駁列2、EX側(cè)CE刺L常用城操作窯及功草能篩選—自動(dòng)危篩選羨和高擔(dān)級(jí)篩切選2、EX忽CE跪L常用竿操作皺及功滿能分列快速石的將慢一些湖有規(guī)質(zhì)律的浩數(shù)據(jù)心分成蓋不同與類別聯(lián)。2、EX遣CE錦L常用包操作腦及功酷能分類電匯總2、EX著CE順L常用版操作龍及功棵能單元田格格缺式快捷腹鍵：Ct劇rl桶+12、EX絕CE感L常用打操作溝及功吼能條件持格式開始樣式條件鵲格式條件摧格式痛是20醬07里面鋪新增維加的曉一項(xiàng)驗(yàn)功能嬸，按貨要求御對(duì)數(shù)挪據(jù)進(jìn)資行分瀉類顯掘示2、EX霞CE蜓L常用悄操作堂及功際能凍結(jié)哄和拆口分這是最查看燥大量曠數(shù)據(jù)撕顯示支表頭切的利塑器2、EX歐CE易L常用綁操作面及功振能2、EX粗CE澡L常用俯操作襲及功拿能其他氣簡(jiǎn)便型功能A、刪皆除重壤復(fù)數(shù)亞據(jù)照相防機(jī)-把數(shù)巴據(jù)變暗為圖央片，愚防止淹泄露撿其他借數(shù)據(jù)對(duì)象第選擇恨工具-選擇EX抵CE建L中的鹽圖形嚴(yán)對(duì)象B、隱尤藏著吸的快趣捷工勇具按拼蘆音排議序1EX擔(dān)CE絹L簡(jiǎn)介2EX氏CE弦L常用貌操作最及功耕能3EX碼CE川L函數(shù)飄使用朱技巧4數(shù)據(jù)腸透視攜表5公式塑及函睜數(shù)6數(shù)組幫的運(yùn)嫁用報(bào)告享目錄宏與張簡(jiǎn)單VB筆A源數(shù)梅據(jù)-一定蚊是要靜規(guī)則其清晰尼，無器空格恩斷行宣的數(shù)劣據(jù)。源數(shù)跟據(jù)的漢每列掛都將腸成為亭一個(gè)掠字段丸并且徑可在敬報(bào)表嘆中使塌用。報(bào)表蔽字段留的的求名稱冊(cè)來自儉源數(shù)匯據(jù)中鑒的列槽標(biāo)題你，標(biāo)召題下遷的其唉余各靈行應(yīng)統(tǒng)該包導(dǎo)含同耐一列振中的脅類似鼠項(xiàng)。刪除洗用于鋤數(shù)據(jù)嗽塊相預(yù)互隔脅開的另空白撤行。數(shù)據(jù)堤透視休表3、數(shù)朝據(jù)透脈視表一種織可以纏動(dòng)態(tài)樸地改丟變版姿面布殊置，遲以便熄按照六不同迅方式煎分析池?cái)?shù)據(jù)肯的動(dòng)卡態(tài)表醉格。創(chuàng)建針數(shù)據(jù)井透視憂表1、插培入選脊項(xiàng)卡2、選蛛擇數(shù)晃據(jù)透宇視表3、選餓項(xiàng)框膜中選卻擇確叨定3、數(shù)寨據(jù)透炒視表數(shù)據(jù)輸透視的表的半使用把你眼想透獵視的旨內(nèi)容販拖到終對(duì)應(yīng)醫(yī)區(qū)域3、數(shù)凈據(jù)透?jìng)}視表3、數(shù)螞據(jù)透廁視表數(shù)據(jù)霜透視懷表的活使用現(xiàn)在燃對(duì)應(yīng)您的內(nèi)盯容就塞是：遺誰在賤哪臺(tái)啄機(jī)器湊上的北產(chǎn)量牌是多擋少。行和嫂列標(biāo)宜簽的行自由定拖動(dòng)將可以婆創(chuàng)造救很多姓不同隨的數(shù)療據(jù)表盈達(dá)方柳式。3、數(shù)如據(jù)透頭視表數(shù)據(jù)津透視遼表的赴使用在圖傻表區(qū)辣域中殿點(diǎn)擊傷右鍵滋，還夏可以摔選擇根其他急的內(nèi)索容：例如際：匯敵總類熔型（很求和油，計(jì)狀數(shù)等查），濟(jì)顯示遣方式(百分梢比等等數(shù)據(jù)走透視提圖的辱使用偉方法蔑和透桶視表未一致樹，只逗是多艦了一蛛個(gè)可暈以篩獎(jiǎng)選的征圖表1EX券CE申L簡(jiǎn)介2EX顯CE盒L常用惠操作贈(zèng)及功嘴能3EX慈CE盤L函數(shù)討使用川技巧4數(shù)據(jù)投透視減表5公式軌及函慚數(shù)6數(shù)組滴的運(yùn)會(huì)用報(bào)告筍目錄宏與系簡(jiǎn)單VB極A1、公簽式簡(jiǎn)晴介EX稠CE蒼L中公涉式分待為了綿很多講類四、鉛公式果及函靈數(shù)我們?yōu)鯇W(xué)習(xí)申函數(shù)劣最大售的幫湯助工睜具是EX鵲CE腰l自帶濾的幫相助編寫襪公式牙時(shí)你蠶應(yīng)該淘具備玻基礎(chǔ)貴的程警序邏鹽輯思膽考技臂巧。四、域公式忽及函崇數(shù)2、函洋數(shù)與扁公式財(cái)?shù)幕侗靖啪帜頔X襯CE租L公式潤(rùn)以“=死“為引耕導(dǎo)，團(tuán)后面穗接上佳函數(shù)銳，以領(lǐng)及函撈數(shù)所酬使用恰的參什數(shù)=I魔F(謹(jǐn)lo繭gi斗ca荷l_榮te窄st況,印[v弦al岸ue窩_i殖f_安tr河ue墻],剪[絮va虹lu閘e_網(wǎng)if辣_f掀al因se墻])參數(shù)數(shù)據(jù)燭類型在EX斜CE損L中可全運(yùn)算梳的數(shù)卷據(jù)類厚型分光為：螺文本送，數(shù)料值、帥日期剖、邏雖輯值亭、錯(cuò)革誤值運(yùn)算銀符號(hào)符號(hào)類型符號(hào)案例算術(shù)運(yùn)算+；-；*；/；^=6^2=3+2*5比較運(yùn)算>;<;=;<>；>=；<=；=5<2=false文本運(yùn)算&="天龍"&"八部"區(qū)域運(yùn)算:=Sum(A1:A3)交叉運(yùn)算_(空格)=sum(A1:B5A4:D9)=sum(A4:B5)聯(lián)合運(yùn)算，（逗號(hào)）=rank(A1,(A1:A10，C1:C10))函數(shù)四、排公式糾及函膛數(shù)3、參忙數(shù)的圍類型不同澇的引止用在桌我們踏拖動(dòng)落公式森時(shí)具吸有不甚同的舍結(jié)果胡。常量首：無論澡如何鐘運(yùn)算銷，自雄身都寇不會(huì)長(zhǎng)改變享的值掠；一竹般為勾直接伙輸入夠的數(shù)斗據(jù)“=即1+績(jī)2*益3”雖然潔這個(gè)輕公式念結(jié)果勁不會(huì)遵變，膀但是宗它不象屬于釀常量.系統(tǒng)拜變量清：公式鋤的某唇些變島量來易源于掏計(jì)算信機(jī)系蜂統(tǒng)的贈(zèng)自身雀參數(shù)怎，如皺機(jī)器以時(shí)間廊。地址昂引用眼：對(duì)其孕他工黑作溥丘、工早作表臉、區(qū)暮域或伍單元廁格的凍內(nèi)容交的引光用這六種引設(shè)用在僵是根刮據(jù)被芒引用看對(duì)象融的位昆置進(jìn)瞞行的引用家的轉(zhuǎn)維換快踩捷鍵徹：F4四、狼公式片及函素?cái)?shù)4、常槍用函貧數(shù)類型函數(shù)用途類型函數(shù)用途數(shù)值Sum求和函數(shù)文本len計(jì)算文本長(zhǎng)度Average平均值函數(shù)left文本截?。ㄗ螅㎝ax/Min最大/最小函數(shù)right文本截?。ㄓ遥﹕umproduct數(shù)組求和函數(shù)mid文本截取（指定）SUMIF條件求和CONCATENATE文本合并Sumifs多條件求和text文本化統(tǒng)計(jì)Count單元格統(tǒng)計(jì)函數(shù)value數(shù)值化CountA時(shí)間DAY日期countblank查找與引用COLUMN列號(hào)比較RANK排名ROW行號(hào)LARGE第K大的值LOKKUP查找SMALL第K小的值VLOKKUP列查找邏輯IF判斷函數(shù)HLOOKUP行查找AND且函數(shù)MATCH匹配OR或函數(shù)INDEX引用IFERROR錯(cuò)誤判斷offset偏移以上專函數(shù)厭基本榴能滿戚足我襪們的雁一般零工作愉需求奪。四、移公式形及函粉數(shù)案例羽演示四、抹公式罰及函偏數(shù)4、函矮數(shù)的茄嵌套IF叨(A暑1>局1,刪S燦UM鉛(B印1:串G1蜂),招””博)在這有個(gè)公瓣式中柏，su萬m函數(shù)比是2級(jí)函厚數(shù)，宮它是if函數(shù)厚的參般數(shù)Ex吐ce訓(xùn)l2姑00煮7允許64層的渴嵌套抵，20堡03只能7層。=I草F(MO踏D(營(yíng)RO鄉(xiāng)豐W(獨(dú)),軍3)灰=1,源數(shù)扒據(jù)!A蓬$1,IF拐(MO蹈D(肚RO假W(麻),雕3)走=2即,IN更DE群X(源數(shù)畜據(jù)!$答A$危1:聰$G雨$3奪49,(R霧OW誰()碼+4企)/鐵3,CO耍LU里MN燈()),"酒"))第1個(gè)IF的條浴件參島數(shù)當(dāng)條悼件為刃真時(shí)淚返回院該值當(dāng)條還件為席假時(shí)命進(jìn)入勇下一鄭個(gè)IF第2個(gè)IF的條毯件參憲數(shù)當(dāng)條這件為漢真時(shí)蠅進(jìn)入In害de舌x函數(shù)當(dāng)條饞件為超假時(shí)汗返回鎖空值5、如獵何解赤讀函曠數(shù)=I攝F(編MO梢D(復(fù)RO肆W(拌),皆3)后=1穴,源數(shù)牧據(jù)!A鏟$1輝,I革F(倆MO腿D(自RO花W(碑),斬3)杯=2身,I烏ND畢EX識(shí)(源數(shù)梅據(jù)!$爺A$仁1:測(cè)$G焰$3斯49歌,(弦RO娘W(辜)+素4)賠/3混,C摔OL探UM里N(夏))酬,"腔"侍))復(fù)雜市案例A、設(shè)賭計(jì)目差的：按規(guī)些律切帖割工容資條B、設(shè)侵計(jì)思剪路：結(jié)果寺的第酸一行妻：永妥遠(yuǎn)是蘋原數(shù)燈據(jù)中重的第繁一行厘。結(jié)果克的第浮二行狹：結(jié)你果第2行對(duì)測(cè)應(yīng)原斧數(shù)據(jù)例第2行，役結(jié)果林第5行對(duì)蕉應(yīng)原濃數(shù)據(jù)掛第3行，站第8對(duì)應(yīng)孟第4，第11對(duì)應(yīng)戴第5。結(jié)果掘第三姻行：悟永遠(yuǎn)撈為空斷。這里怒面第即二個(gè)愚最難滿算，字我們芝看看伶他們紡之間舌有什梁么規(guī)較律。案例:工資旗打印立切割埋公式=I斤F(延MO番D(本RO岔W(者),柜3)湖=1踏,源數(shù)康據(jù)!A粗$1舍,I太F(刺MO遺D(潤(rùn)RO道W(刻),旨3)價(jià)=2蚊,I谷ND秘EX舍(源數(shù)填據(jù)!$片A$揮1:偉$G舍$3黨49饞,(節(jié)RO珠W(糕)+吐4)喘/3接,C警OL前UM伴N(啊))腰,"呼"東))四、儲(chǔ)公式夏及函父數(shù)6、函匪數(shù)是賊怎么雷編出健來的案例4.艦25、公測(cè)式的棕查錯(cuò)錯(cuò)誤值含義#######列寬不夠顯示，或使用了負(fù)的日期，和時(shí)間。#VALUE!參數(shù)或數(shù)值類型錯(cuò)誤#DIV/0!被零除錯(cuò)誤#NAME？無法識(shí)別的公式#N/A數(shù)值對(duì)函數(shù)或公式不可用#REF!單元格引用無效#NUM!無效的數(shù)字值#NULL!計(jì)算為空值時(shí)，無法得出計(jì)算結(jié)果；例如：公式差錯(cuò)的工具：1、鍵盤上的F92、公式選項(xiàng)卡中的公式查錯(cuò)時(shí)的要訣：1、讀懂公式2、看看公式在拖動(dòng)過程中的引用方式是否正確3、當(dāng)嵌套太多太復(fù)雜時(shí)，把公式分層粘到單元格中四、戲公式荷及函爪數(shù)1EX待CE蕩L簡(jiǎn)介2EX椅CE歌L常用子操作炸及功擠能3EX忍CE前L函數(shù)膀使用折技巧4數(shù)據(jù)淘透視憑表5公式前及函脖數(shù)6數(shù)組害的運(yùn)值用報(bào)告欣目錄宏與額簡(jiǎn)單VB續(xù)A數(shù)組工的概寨念五、賣數(shù)組曉的運(yùn)萍用數(shù)組后是一純個(gè)或隆多個(gè)燦元素樂按照答行業(yè)抹排列業(yè)方式員組成闖的組將合。在EX哈CE票l中使尊用{}表達(dá)惠數(shù)組={披1;狹2;疫3;甲4;鉤5}1、在一個(gè)單元格中輸入數(shù)組2、選中5個(gè)格子，按鍵盤上的F2。3、CTRL+SH