教育用-Excel實(shí)戰(zhàn)培訓(xùn)課件

Excel實(shí)戰(zhàn)技巧培訓(xùn)目錄前言操作與技巧——原來(lái)可以如此簡(jiǎn)單數(shù)據(jù)透視表——百變星君函數(shù)與公式——Excel的精髓之處12430001020304050607圖表——專業(yè)的我也可以做到56總結(jié)與作業(yè)2前言——Excel有些什么VBA編程VBA編程透視表透視表函數(shù)、圖表函數(shù)、圖表基本操作基本操作3前言——成為“高手”的捷徑心態(tài)方法堅(jiān)持No.1積極的心態(tài)“興趣是最好的老師、壓力是前進(jìn)的動(dòng)力”No.2正確的方法循序漸進(jìn)善用資源、學(xué)以致用閱讀與實(shí)踐的結(jié)合No.3持之以恒的努力，每天進(jìn)步一點(diǎn)點(diǎn)5前言——關(guān)于本次培訓(xùn)解決問(wèn)題的思路學(xué)習(xí)Excel的方法注重知識(shí)點(diǎn)20%實(shí)例講解80%構(gòu)成采用Excel2010進(jìn)行講解，盡量向低版本兼容說(shuō)明6前言——起步之前讓我們回顧幾個(gè)場(chǎng)景場(chǎng)景1Excel+計(jì)算器場(chǎng)景2Excel取代計(jì)算器場(chǎng)景3單元格表間運(yùn)算場(chǎng)景4Vlookup+sumif場(chǎng)景6Excel+數(shù)據(jù)庫(kù)場(chǎng)景5高級(jí)公式+透視表…...7操作與技巧基礎(chǔ)——排序

排序的基本操作案例1:如何按漢字筆畫(huà)排序案例2:如何多重排序案例3:如何按行排序案例4:輔助列排序案例5:如何按單元格顏色排序（2007以上版本)9操作與技巧基礎(chǔ)——篩選（自動(dòng)篩選)

篩選的基本操作案例1:自定義篩選案例2:篩選中數(shù)據(jù)區(qū)域的注意事項(xiàng)案例3:如何進(jìn)行反向選擇10操作與技巧基礎(chǔ)——查找與替換

查找與替換基本操作案例1:查找內(nèi)容案例2:查找格式案例3:如何在整個(gè)工作簿查找案例4:如何刪除工作表中的通配符和特定字符11操作與技巧基礎(chǔ)——選擇性粘貼

選擇性粘貼的基本操作和意義案例1:如何將公式轉(zhuǎn)為數(shù)值案例2:如何直接對(duì)某個(gè)區(qū)域進(jìn)行加減乘除運(yùn)算案例3:如何只復(fù)制單元格中的有效性設(shè)置案例4:關(guān)于轉(zhuǎn)置功能的運(yùn)用案例5:關(guān)于跳過(guò)空單元格13操作與技巧基礎(chǔ)——效率加倍的快捷鍵

何為有用的快捷鍵？需要自己去摸索總結(jié)，本次培訓(xùn)僅僅列舉了一小部分供大家參考。令效率加倍的快捷鍵舉例14目錄前言操作與技巧——原來(lái)可以如此簡(jiǎn)單數(shù)據(jù)透視表——百變星君函數(shù)與公式——Excel的精髓之處12430001020304050607圖表——秀的是專業(yè)、拼的是創(chuàng)意56總結(jié)與作業(yè)基礎(chǔ)中的基礎(chǔ)排序篩選查找與替換定位選擇性粘貼效率加倍的快捷鍵基礎(chǔ)中的進(jìn)階單元格格式保護(hù)工作表/工作簿名稱的運(yùn)用數(shù)據(jù)有效性條件格式高級(jí)篩選15操作與技巧進(jìn)階——工作表/工作簿的保護(hù)

保護(hù)工作表/保護(hù)工作簿案例1:保護(hù)工作表基礎(chǔ)操作案例2:如何限定用戶只能選擇可編輯的區(qū)域案例3:如何在已保護(hù)的工作表中運(yùn)用篩選17操作與技巧進(jìn)階——數(shù)據(jù)有效性

數(shù)據(jù)有效性的概述案例1:如何利用數(shù)據(jù)有效性控制單元格的輸入案例2:如何限制重復(fù)值的錄入案例3:用有效性制作二級(jí)下拉菜單18操作與技巧進(jìn)階——條件格式

條件格式概述案例1:利用條件格式標(biāo)注達(dá)成率案例2:用條件格式判斷列是否有重復(fù)案例3:用條件格式自動(dòng)實(shí)現(xiàn)間隔底紋簡(jiǎn)單介紹2010的條件格式、迷你圖19操作與技巧進(jìn)階——名稱的運(yùn)用

為什么要使用名稱名稱的優(yōu)點(diǎn)：使公式、有效性、條件格式等等易于理解，比如用“稅率”代替單元格引用.簡(jiǎn)化公式利于安全，易于維護(hù)可以在條件格式、有效性中引用其他工作表單元格或常量數(shù)組調(diào)用Excel4.0宏表函數(shù)必須使用名稱可以在控件中調(diào)用單元格內(nèi)容Excel的幾乎任何地方那個(gè)都可以使用名稱21操作與技巧進(jìn)階——名稱的運(yùn)用

關(guān)于名稱的約定名稱不得與單元格地址相同。包括R1C1樣式。名稱不得使用空格。名稱不得以數(shù)字開(kāi)頭或單獨(dú)使用數(shù)字。Excel識(shí)別名稱時(shí)，不區(qū)分大小寫(xiě)。名稱是一種特殊形式的“公式”，因此其長(zhǎng)度、內(nèi)部嵌套等同樣受到限制，比如內(nèi)部長(zhǎng)度最大16,384個(gè)字符。名稱所用字符不能超過(guò)255個(gè)字符。當(dāng)名稱引用單元格區(qū)域時(shí)，要考慮相對(duì)引用和絕對(duì)引用。22操作與技巧進(jìn)階——名稱的運(yùn)用

定義名稱的三種方法方法1:插入名稱案例2:使用名稱框案例3:根據(jù)所選內(nèi)容創(chuàng)建（批量）23操作技巧進(jìn)階——綜合案例分析一份市場(chǎng)活動(dòng)贈(zèng)品申請(qǐng)表的編制案例單純的表格VS名稱+數(shù)據(jù)有效性+條件格式+工作表保護(hù)的表格25操作與技巧進(jìn)階——練習(xí)

練習(xí)題目1請(qǐng)看案例26函數(shù)與公式——熱身運(yùn)動(dòng)公式？函數(shù)？

公式：是以等號(hào)“=”開(kāi)頭，通過(guò)使用運(yùn)算符將數(shù)據(jù)、函數(shù)等元素按一定順序連接在一起，從而實(shí)現(xiàn)對(duì)工作表中的數(shù)據(jù)執(zhí)行計(jì)算的等式。例如C1=A1+A2+A3+A4+B1+B2+B3+B4函數(shù)：簡(jiǎn)而言之，函數(shù)就是預(yù)先定義好了的公式。比如上例中的公式就可以用函數(shù)C1=SUM(A1:B4)代替。

函數(shù)不僅可以簡(jiǎn)化公式，而且具有僅用運(yùn)算符連接的一般公式所不能替代的功能。例如查找引用，邏輯判斷等等。29函數(shù)與公式——函數(shù)的結(jié)鉤分析30函數(shù)與公式——熱身運(yùn)動(dòng)快速選擇函數(shù)

快速選擇、輸入函數(shù)的方法通過(guò)“公式庫(kù)”功能區(qū)快速選擇輸入通過(guò)開(kāi)頭字母的提示快速輸入通過(guò)插入函數(shù)輸入直接輸入31目錄前言操作與技巧——原來(lái)可以如此簡(jiǎn)單數(shù)據(jù)透視表——百變星君函數(shù)與公式——Excel的精髓之處12430001020304050607圖表——秀的是專業(yè)、拼的是創(chuàng)意56總結(jié)與作業(yè)熱身運(yùn)動(dòng)，現(xiàn)在開(kāi)始Excel運(yùn)算符及其含義什么是公式、什么是函數(shù)如何快速、準(zhǔn)確找到適合的函數(shù)記住三個(gè)F吧，他們很棒F1,不離不棄的好兄弟F4,相對(duì)？還是絕對(duì)？由你說(shuō)了算F9,一把“庖丁解?！钡膶毜?2函數(shù)起步——F1,不離不棄的好兄弟自學(xué)函數(shù)的捷徑全在這里了記?。篹xcel本身就是最好的參考書(shū)F1，F(xiàn)1哪里不會(huì)點(diǎn)哪里33函數(shù)起步——F4,相對(duì)？絕對(duì)？由你說(shuō)了算

何為引用，有何影響？案例1:三種引用的同時(shí)理解案例2:引用位置偏移會(huì)出現(xiàn)錯(cuò)誤數(shù)據(jù)案例3:綜合案例34函數(shù)起步——F9,一把“庖丁解牛”的寶刀F9——應(yīng)用舉例解析統(tǒng)計(jì)函數(shù):IF+COUNTIF+SUMIF的綜合應(yīng)用解析文本函數(shù):FIND+MID的綜合應(yīng)用解析復(fù)雜數(shù)組公式:其實(shí)就是這么簡(jiǎn)單35目錄前言操作與技巧——原來(lái)可以如此簡(jiǎn)單數(shù)據(jù)透視表——百變星君函數(shù)與公式——Excel的精髓之處12430001020304050607圖表——秀的是專業(yè)、拼的是創(chuàng)意56總結(jié)與作業(yè)熱身運(yùn)動(dòng)，現(xiàn)在開(kāi)始什么是函數(shù)如何快速、準(zhǔn)確找到適合的函數(shù)兵馬未動(dòng)，邏輯先行記住三個(gè)F吧，他們很棒F1,不離不棄的好兄弟F4,相對(duì)？還是絕對(duì)？由你說(shuō)了算F9,一把“庖丁解?！钡膶毜逗瘮?shù)如此簡(jiǎn)單TRUEvsFALSE邏輯判斷是函數(shù)的靈魂數(shù)據(jù)匯總統(tǒng)計(jì)函數(shù)查找引用函數(shù)文本處理函數(shù)璀璨明珠—Sumproduct36函數(shù)如此簡(jiǎn)單——TRUEvsFALSEExcel的邏輯值只有2個(gè)：TRUEFALSETRUEFALSE是函數(shù)的關(guān)鍵，可以這么說(shuō)：任何函數(shù)都是在解決TRUE還是FALSE的問(wèn)題TRUEFALSE與運(yùn)算符進(jìn)行各種計(jì)算時(shí)，請(qǐng)牢記：TRUE=1FALSE=0，請(qǐng)務(wù)必牢記

通過(guò)示例文件，我們來(lái)詳細(xì)解釋一下TRUE和FALSE37函數(shù)如此簡(jiǎn)單——邏輯判斷是函數(shù)的靈魂串聯(lián)開(kāi)關(guān)等同于邏輯函數(shù)AND,即多個(gè)條件同時(shí)滿足：比如——TRUE*TRUE并聯(lián)開(kāi)關(guān)等同于邏輯函數(shù)OR,即只要滿足一個(gè)條件：比如——TRUE+FALSE38函數(shù)如此簡(jiǎn)單——邏輯判斷是函數(shù)的靈魂

通過(guò)案例，輕松掌握邏輯運(yùn)算案例1:是否能申請(qǐng)經(jīng)濟(jì)適用房案例2:如何根據(jù)性別和職務(wù)計(jì)算補(bǔ)貼案例3:多層判斷——徹底理清邏輯關(guān)系39函數(shù)如此簡(jiǎn)單——數(shù)據(jù)匯總統(tǒng)計(jì)類函數(shù)

注：數(shù)據(jù)匯總統(tǒng)計(jì)類將全部采用案例形式進(jìn)行講解

匯總求和類函數(shù)，關(guān)鍵詞:sum、sumif、sumifs、subtotal案例1:匯總多個(gè)表格中相同單元格的數(shù)據(jù)（sum)案例2:如何匯總某品名的銷售數(shù)量（sumif)案例3:深入理解sumif函數(shù)的“相對(duì)位置”案例4:如何匯總月薪在2000-4000元的工資總額（sumifs)案例5:如何忽略隱藏的行匯總數(shù)據(jù)(subtotal)案例6:如何始終生成連續(xù)的商品序號(hào)(subtotal)40函數(shù)如此簡(jiǎn)單——數(shù)據(jù)匯總統(tǒng)計(jì)類函數(shù)

數(shù)據(jù)計(jì)數(shù)類函數(shù)，關(guān)鍵詞:count,countif,countifs案例1:如何統(tǒng)計(jì)成績(jī)表中的及格人數(shù)(countif,countifs)案例2:如何統(tǒng)計(jì)產(chǎn)品規(guī)格中帶“*”號(hào)的產(chǎn)品個(gè)數(shù)(countif)案例3:如何統(tǒng)計(jì)35歲以上有房有車(chē)的人員名單案例4:如何統(tǒng)計(jì)不重復(fù)的數(shù)據(jù)個(gè)數(shù)41函數(shù)如此簡(jiǎn)單——數(shù)據(jù)匯總統(tǒng)計(jì)類函數(shù)

極值均值類函數(shù)，關(guān)鍵詞:min,max,median,large,small,average案例1:如何設(shè)置提成獎(jiǎng)金的上下限（max,min,mdeian)案例2:如何不計(jì)重復(fù)返回一組數(shù)據(jù)中次大，次小值(large,small)42函數(shù)如此簡(jiǎn)單——查找引用類函數(shù)

注：查找引用類將采用函數(shù)解析+案例的方式VLOOKUP函數(shù)動(dòng)態(tài)解析(*辦公室函數(shù)使用率之最*）MATCH函數(shù)動(dòng)態(tài)解析案例1:根據(jù)不同的商品不同的訂貨量計(jì)算單價(jià)及折扣案例2:輸入工號(hào)后可自動(dòng)顯示相關(guān)的人事資料（測(cè)試題)案例3:如何處理VLOOKUP函數(shù)找不到對(duì)應(yīng)數(shù)據(jù)產(chǎn)生的錯(cuò)誤43函數(shù)如此簡(jiǎn)單——查找引用類函數(shù)INDXE函數(shù)動(dòng)態(tài)解析案例4:如何從右往左查詢對(duì)應(yīng)的數(shù)據(jù)（測(cè)試題）案例5:如何根據(jù)品牌和規(guī)格雙條件查找單價(jià)OFFSET函數(shù)動(dòng)態(tài)解析案例6:如何保證新增加的銷量自動(dòng)求和(offset+counta動(dòng)態(tài)引用)44函數(shù)如此簡(jiǎn)單——文本函數(shù)

我們一起來(lái)學(xué)習(xí)幾個(gè)最常用的文本處理函數(shù)：LEFT,MID,RIGHT“三個(gè)兄弟，本事各不同”FIND——在文本中找文本SUBSTITUTE——替換函數(shù)案例1:判斷產(chǎn)品缺陷中是否含某個(gè)字符案例2:不通過(guò)分列如何將數(shù)據(jù)分成2列案例2:如何查找某字符在字符串中第N次出現(xiàn)的位置45SUMPRODUCT——函數(shù)中一顆璀璨的明珠*千萬(wàn)不會(huì)要去迷戀Excel幫助文件中對(duì)此函數(shù)的描述。也許是因?yàn)镾UMPRODUCT函數(shù)能力太過(guò)超群，Excel幫助文件都嫉妒了它的才能。^_^SUMPRODUCT=SUMPRODUCT((數(shù)據(jù)1)*(數(shù)據(jù)2)*(數(shù)據(jù)3)*(數(shù)據(jù)4)……*(數(shù)據(jù)N))=SUMPRODUCT((數(shù)據(jù)1)+(數(shù)據(jù)2)+(數(shù)據(jù)3)+(數(shù)據(jù)4)……+(數(shù)據(jù)N))將匯總數(shù)相乘？？是這樣嗎？將乘積的結(jié)果匯總!Good??！=SUMPRODUCT((數(shù)據(jù)1)*(數(shù)據(jù)2))46SUMPRODUCT——函數(shù)中一顆璀璨的明珠

在了解的基本原理后，我們還是通過(guò)案例來(lái)講解SUMPRODUCTDE的常規(guī)用法：案例1:起步,讓一般統(tǒng)計(jì)類函數(shù)汗顏~~^_^案例2:奔跑,讓多條件統(tǒng)計(jì)類函數(shù)折服~~^_^Hi，不小心，我們已經(jīng)打開(kāi)了統(tǒng)計(jì)類“數(shù)組公式”的潘多拉盒子。函數(shù)“美麗”（魅力）綻放，你發(fā)現(xiàn)了嗎？47我和函數(shù)的約定——日積月累、學(xué)以致用函數(shù)是Excel中最核心的部分，是技巧、圖表、透視表，VBA的基本功。同時(shí)函數(shù)本身內(nèi)容也相當(dāng)豐富，值得大家不斷的去學(xué)習(xí)、思考如何讓工作更輕松。同時(shí)：由于函數(shù)內(nèi)容太多，不需要大家把每個(gè)函數(shù)都去掌握由于函數(shù)很有深度，很多函數(shù)都值得反復(fù)的去學(xué)習(xí)、去發(fā)現(xiàn)由于許多函數(shù)在理解上比較困難，不可能短期內(nèi)達(dá)到某個(gè)高度因此，建議大家“日積月累、學(xué)以致用”Lookup查找之王Text文本之王48認(rèn)識(shí)數(shù)據(jù)透視表創(chuàng)建第一個(gè)數(shù)據(jù)透視表無(wú)拘無(wú)束的數(shù)據(jù)透視表如虎添翼的計(jì)算字段、計(jì)算項(xiàng)動(dòng)態(tài)數(shù)據(jù)透視表介紹多重合并和外部引用創(chuàng)建透視表＊介紹數(shù)據(jù)透視圖＊目錄前言操作與技巧——原來(lái)可以如此簡(jiǎn)單數(shù)據(jù)透視表——百變星君函數(shù)與公式——Excel的精髓之處12430001020304050607圖表——秀的是專業(yè)、拼的是創(chuàng)意56總結(jié)與作業(yè)49數(shù)據(jù)透視圖數(shù)據(jù)透視圖數(shù)據(jù)透視表數(shù)據(jù)透視表——認(rèn)識(shí)數(shù)據(jù)透視表數(shù)據(jù)庫(kù)文本文件Excel數(shù)據(jù)透視表引擎數(shù)據(jù)透視表數(shù)據(jù)透視圖數(shù)據(jù)透視表50數(shù)據(jù)透視表——了解數(shù)據(jù)透視表的結(jié)構(gòu)51數(shù)據(jù)透視表——認(rèn)識(shí)數(shù)據(jù)透視表結(jié)構(gòu)報(bào)表篩選區(qū)域行區(qū)域列區(qū)域數(shù)值區(qū)域52數(shù)據(jù)透視表——?jiǎng)?chuàng)建一個(gè)透視表選擇區(qū)域中任意單元格插入透視表選擇列、行、值53數(shù)據(jù)透視表——無(wú)拘無(wú)束的數(shù)據(jù)透視表(1)篩選字段、列、行字段任意拖拽的無(wú)拘無(wú)束字段匯總方式的無(wú)拘無(wú)束字段顯示方式的無(wú)拘無(wú)束修改數(shù)據(jù)透視表的字段名稱靈活處理錯(cuò)誤值和空白單元格54數(shù)據(jù)透視表——無(wú)拘無(wú)束的數(shù)據(jù)透視表(2)誰(shuí)先誰(shuí)后由決定，展示字段先后順序任意指定數(shù)據(jù)透視表的排序數(shù)據(jù)透視表的篩選數(shù)據(jù)透視表分組，組合方式由我定更改數(shù)據(jù)源和移動(dòng)數(shù)據(jù)透視表55數(shù)據(jù)透視表——如虎添翼的計(jì)算字段和計(jì)算項(xiàng)首先理解計(jì)算字段、計(jì)算項(xiàng)案例１：用計(jì)算項(xiàng)計(jì)算銷售提成案例２：用計(jì)算項(xiàng)計(jì)算年銷售差異56數(shù)據(jù)透視表——?jiǎng)討B(tài)創(chuàng)建數(shù)據(jù)透視表方法１：定義名稱法創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表方法２：使用表功能創(chuàng)建動(dòng)態(tài)的數(shù)據(jù)透視表方法３：導(dǎo)入外部數(shù)據(jù)創(chuàng)建動(dòng)態(tài)數(shù)據(jù)透視表57數(shù)據(jù)透視表——多重合并、外部引用、透視圖多重合并的介紹數(shù)據(jù)源５引用外部數(shù)據(jù)源