6-精品excel中級(jí)培訓(xùn)教程(從入門到精通).ppt

1、公式與函數(shù)基礎(chǔ)（10Min）P2-P8 認(rèn)識(shí)公式 公式/函數(shù)的輸入與編輯 單元格和范圍的引用 文本的處理（5Min）P9-P13 字符串的編輯 字符串的截取/返回相應(yīng)值 使用函數(shù)（45Min）P14-P28 日期及時(shí)間函數(shù)（5Min） 查詢函數(shù)（10Min） 邏輯判斷函數(shù)（10Min） 數(shù)學(xué)函數(shù)（5Min） 統(tǒng)計(jì)函數(shù)（5Min） 函數(shù)使用范例（10Min）,數(shù)據(jù)篩選（10Min） P29-P31 自動(dòng)篩選 高級(jí)篩選 數(shù)據(jù)有效性設(shè)置及應(yīng)用 數(shù)據(jù)匯總（5Min）P32-P36 分類匯總的計(jì)算方法 數(shù)據(jù)的組合與取消 合并計(jì)算 制作與使用圖表（5Min） P37 使用數(shù)據(jù)透視表（15Min） 創(chuàng)建數(shù)據(jù)

2、透視表 編輯數(shù)據(jù)透視表 宏的錄制與運(yùn)用（10Min） 宏的功能 錄制與運(yùn)行宏 Excel小技巧分享與討論（10Min）,課程大綱,前言,Excel用戶層次分類原則,剛剛開始接觸,基本掌握常用功能,熟練使用常用功能+數(shù)據(jù)透視表+公式,+數(shù)組公式+VBA簡(jiǎn)單編程,Excel技能+專業(yè)知識(shí)+行業(yè)經(jīng)驗(yàn),新手,初級(jí)用戶,中級(jí)用戶,高級(jí)用戶,專家,一、公式與函數(shù)基礎(chǔ)（10Min）,1.2、公式中的運(yùn)算符： 1）算術(shù)運(yùn)算符,1、認(rèn)識(shí)公式,1.1、常量：指直接輸入公式中的數(shù)字或者文本值，即不用計(jì)算的值；,1.2、公式中的運(yùn)算符,一、公式與函數(shù)基礎(chǔ)（10Min）,1、認(rèn)識(shí)公式,3）、比較運(yùn)算符,2）. 文本運(yùn)算

3、符： “”，然后按空格鍵 最后按“Ctrl+Shift+;顯示當(dāng)前日期和時(shí)間,區(qū)別： Now和Today函數(shù)是動(dòng)態(tài)的； 但快捷鍵得到的是靜態(tài)的；,2、查詢函數(shù),無作業(yè)工時(shí)的統(tǒng)計(jì)界面如下，如果想知道某個(gè)部門在某一個(gè)月的無作業(yè)工時(shí)，如何解決？Hlookup（水平查找）,三、使用函數(shù)（40Min）,需要在數(shù)據(jù)區(qū)域首行搜索的值,原始的數(shù)據(jù) 區(qū)域,滿足條件2的單元格在數(shù)據(jù)區(qū)域的行數(shù),0：找不到時(shí)：返回#N/A； 1：找不到時(shí)：返回0,2、查詢函數(shù),如果無作業(yè)工時(shí)的統(tǒng)計(jì)界面如下，想知道某個(gè)部門在某一個(gè)月的無作業(yè)工時(shí)，又如何解決？Vlookup（豎直查找）,三、使用函數(shù)（40Min）,需要在數(shù)據(jù)區(qū)域首列搜索

4、的值- PMC部,原始的數(shù)據(jù) 區(qū)域,滿足條件2的單元格在數(shù)據(jù)區(qū)域的列數(shù),2、查詢函數(shù),Hlookup/Vlookup使用技巧 1）如何獲取查詢滿足條件的列序列號(hào),三、使用函數(shù)（40Min）,2、查詢函數(shù),Hlookup/Vlookup使用技巧 2）當(dāng)行/列序列號(hào)不同時(shí)，重新輸入公式比較慢，如何解決？,三、使用函數(shù)（40Min）,首先確保數(shù)據(jù)區(qū)域已經(jīng)被全部選中； 鼠標(biāo)點(diǎn)擊單元格右下角，填充公式后，手動(dòng)更改行數(shù)/列數(shù)即可,2、查詢函數(shù),手中有一個(gè)部門的電話號(hào)碼清單，現(xiàn)在有其中一個(gè)人的電話號(hào)碼，需要知道這個(gè)人是誰？查找指定數(shù)值lookup函數(shù),三、使用函數(shù)（40Min）,注意：數(shù)據(jù)區(qū)域的值需要以升序

5、排序，否則可能無法提供正確的值,3、邏輯判斷函數(shù),三、使用函數(shù)（40Min）,1）：邏輯判斷入門,3、邏輯判斷函數(shù),三、使用函數(shù)（40Min）,1）：取交集進(jìn)行邏輯判斷 同時(shí)滿足所有條件才 Ture,2）：取并進(jìn)行邏輯判斷 只要其中一個(gè)滿足就 Ture,3）：根據(jù)不同條件返回 不同值,4、數(shù)學(xué)函數(shù),三、使用函數(shù)（40Min）,1）舉例：產(chǎn)品標(biāo)準(zhǔn)重量85KG，計(jì)算多件產(chǎn)品與標(biāo)準(zhǔn)重量的偏差；ABS（取絕對(duì)值）,4、數(shù)學(xué)函數(shù),三、使用函數(shù)（40Min）,3）兩數(shù)相除的整數(shù)部分：QUOTIENT 函數(shù),2）舉例：計(jì)算兩數(shù)相除的余數(shù)；MOD 成品的包裝運(yùn)輸有數(shù)量限制，假如一個(gè)卡通箱只能包60PCS，客戶

6、的訂單情況如下；想知道整包裝出貨后仍有多少數(shù)量剩余？,4、數(shù)學(xué)函數(shù),三、使用函數(shù)（40Min）,4）條件求和；SUMIf 各部門Q1 無作業(yè)工時(shí)匯總?cè)缦?，如何快速取得一個(gè)或多個(gè)部門的Q1無作業(yè)工時(shí)之和？,4、數(shù)學(xué)函數(shù),三、使用函數(shù)（40Min）,5）舍入和取整函數(shù)Ceiling/Floor（按條件向上/下舍入） Even/ODD（按條件向上舍入為偶數(shù)/奇數(shù)），INT：向下取整；,5、統(tǒng)計(jì)函數(shù),三、使用函數(shù)（40Min）,1）獲取數(shù)組的最大/最小值MAX,MAXA/MIN,MINA MAX與MIN功能是獲取所有數(shù)據(jù)的最小值； MAXA與MINA功能是獲取所有非空單元格的最小值,2）單元格的統(tǒng)計(jì)函

7、數(shù) COUNT與COUNTA 統(tǒng)計(jì)單元格個(gè)數(shù)； COUNTBLANK 統(tǒng)計(jì)空白單元格個(gè)數(shù)；COUNTIF：按條件統(tǒng)計(jì)； COUNTIFS：按多重條件統(tǒng)計(jì),3）計(jì)算條件平均值A(chǔ)VERAGEIF,1、自動(dòng)篩選：快捷鍵：Alt+D+F+F,四、數(shù)據(jù)篩選（10Min）,2、高級(jí)篩選 數(shù)據(jù)篩選高級(jí)篩選； 方式1；在原有區(qū)域直接顯示篩選結(jié)果；不滿足條件部分隱藏不顯示； 列表區(qū)域：原始數(shù)據(jù)區(qū)域； 條件區(qū)域：篩選條件區(qū)域；,2、高級(jí)篩選,通配符：以下通配符可作為篩選以及查找和替換內(nèi)容時(shí)的比較條件:,3、數(shù)據(jù)有效性設(shè)置及應(yīng)用 舉例：在意見收集時(shí)，經(jīng)常碰到收集意見太發(fā)散的情況；比如以下情況；對(duì)一次Team bui

8、lding 進(jìn)行意見收集；得到左下圖的結(jié)果。作為一個(gè)活動(dòng)組織者，如何統(tǒng)計(jì)最終意見？,四、數(shù)據(jù)篩選（10Min）,解決辦法：在發(fā)出意見收集表之前，使用數(shù)據(jù)有效性設(shè)置功能，給出規(guī)定的幾類選項(xiàng)，要求Team成員做“選擇題”而非“自由回答”,設(shè)置方法： 數(shù)據(jù)菜單數(shù)據(jù)有效性 選擇設(shè)置序列,1、分類匯總的計(jì)算方法 分類匯總是對(duì)數(shù)據(jù)清單進(jìn)行數(shù)據(jù)分析的一種方法.在數(shù)據(jù)中實(shí)現(xiàn)對(duì)符合某一個(gè)相同特性的數(shù)據(jù)行，按照特性信息進(jìn)行數(shù)據(jù)匯總的功能；,四、數(shù)據(jù)匯總（10Min）,舉例：還是無作業(yè)工時(shí)：如果希望按照車間，來統(tǒng)計(jì)損耗工時(shí)以及損耗工時(shí)（HR）；,1）首先按照車間進(jìn)行排序 2）數(shù)據(jù)菜單-選擇分類匯總,四、數(shù)據(jù)匯總（1

9、0Min）,舉例：希望按照車間，來統(tǒng)計(jì)損耗工時(shí)以及損耗工時(shí)（HR）；,點(diǎn)擊按鈕2,點(diǎn)擊按鈕1,2、數(shù)據(jù)的組合與取消 在同一個(gè)Excel sheet表中；當(dāng)數(shù)據(jù)行/列非常多，對(duì)于大部分客戶來說，只希望閱讀到重點(diǎn)字段，可使用組合按鈕；使報(bào)表外觀更直觀，簡(jiǎn)潔；,四、數(shù)據(jù)匯總（10Min）,舉例：在下表中，如果只需要關(guān)注車間及損耗工時(shí)字段；除了使用隱藏按鈕外； 亦可使用組合按鈕,選中“車間”與“損耗工時(shí)”之間的數(shù)據(jù)列,單擊 數(shù)據(jù)-組及分級(jí)顯示-組合按鈕,點(diǎn)擊：“損耗工時(shí)”上方按鈕，自動(dòng)合并上一步驟選中列；按鈕由變成+,3、合并計(jì)算當(dāng)數(shù)據(jù)行不多時(shí)，合并計(jì)算是數(shù)據(jù)匯總的又一個(gè)好工具；,四、數(shù)據(jù)匯總（10M

10、in）,舉例：按照車間，匯總“損耗工時(shí)”以及“損耗工時(shí)（HR）,1）鼠標(biāo)點(diǎn)擊一處空白區(qū)域，存放合并計(jì)算結(jié)果；注意該區(qū)域不能和數(shù)據(jù)區(qū)域重疊,2）點(diǎn)擊數(shù)據(jù)菜單：合并計(jì)算： *引用位置中 起始位置為：“車間”（B列） 結(jié)束位置為：“損耗工時(shí)（HR)”(H列） *標(biāo)簽位置：選中“首行”及“最左列” 確定,3、合并計(jì)算當(dāng)數(shù)據(jù)行不多時(shí)，合并計(jì)算是數(shù)據(jù)匯總的又一個(gè)好工具；數(shù)據(jù)菜單，合并計(jì)算；,四、數(shù)據(jù)匯總（10Min）,舉例：按照車間，匯總“損耗工時(shí)”以及“損耗工時(shí)（HR）,3）得到如下結(jié)果；增添標(biāo)題行即可得到匯總結(jié)果；,4）在同一個(gè)sheet表對(duì)同一份數(shù)據(jù)二次合并計(jì)算時(shí)，注意需要?jiǎng)h除“所有引用位置”內(nèi)數(shù)據(jù)

11、；重新選擇引用位置；,線柱圖/MTB中的柏拉圖舉例；在KPI指標(biāo)的分解中，常需要了解各種不同因素的現(xiàn)狀（柱圖/餅圖）以及對(duì)結(jié)果的百分比影響；在Excel中可以使用線柱圖達(dá)到一圖展示的效果；,五、制作與使用圖表（5Min）,線柱圖/MTB中的柏拉圖舉例；在KPI指標(biāo)的分解中，常需要了解各種不同因素的現(xiàn)狀（柱圖/餅圖）以及對(duì)結(jié)果的百分比影響；在Excel中可以使用線柱圖達(dá)到一圖展示的效果；,五、制作與使用圖表（5Min）,Excel 2003版本,1）選中數(shù)值區(qū)域?yàn)槊Q，金額，及累計(jì)百分比,2）在“插入”菜單，“圖表”選項(xiàng)下選擇 自定義類型“兩軸線柱圖,線柱圖/MTB中的柏拉圖舉例；在KPI指標(biāo)的

12、分解中，常需要了解各種不同因素的現(xiàn)狀（柱圖/餅圖）以及對(duì)結(jié)果的百分比影響；在Excel中可以使用線柱圖達(dá)到一圖展示的效果；,五、制作與使用圖表（5Min）,Excel 2003版本,3）在圖表向?qū)е型晟聘鬟x項(xiàng)（標(biāo)題，坐標(biāo)軸等） 點(diǎn)擊“下一步”完成圖表繪制,線柱圖/MTB中的柏拉圖舉例；在KPI指標(biāo)的分解中，常需要了解各種不同因素的現(xiàn)狀（柱圖/餅圖）以及對(duì)結(jié)果的百分比影響；在Excel中可以使用線柱圖達(dá)到一圖展示的效果；,五、制作與使用圖表（5Min）,Excel 2007版本,1）選中數(shù)據(jù)，點(diǎn)擊“插入”“柱形圖”“二維柱形圖”；,線柱圖/MTB中的柏拉圖,五、制作與使用圖表（5Min）,Exc

13、el 2007版本,2）直方圖出現(xiàn)了，果然沒有折線關(guān)鍵的步驟來了，在直方圖上點(diǎn)擊右鍵，選擇“設(shè)置數(shù)據(jù)系列格式”； ；,線柱圖/MTB中的柏拉圖,五、制作與使用圖表（5Min）,Excel 2007版本,3）在“系統(tǒng)選項(xiàng)”“系列繪制在”中，選擇“次坐標(biāo)軸”。看到了嗎？表示累計(jì)百分比的紅色方出現(xiàn)了；,線柱圖/MTB中的柏拉圖,五、制作與使用圖表（5Min）,Excel 2007版本,4）在紅色方上單擊右鍵，選擇“更改系列圖表類型”； ；,5）在“更改圖標(biāo)類型”中，選擇“折線圖”；,線柱圖/MTB中的柏拉圖,五、制作與使用圖表（5Min）,Excel 2007版本,6）折線出現(xiàn)啦再在圖上單擊右鍵“添

14、加數(shù)據(jù)標(biāo)簽”，線柱雙軸圖大功造成！ ；,1、數(shù)據(jù)透視表的強(qiáng)大功能,六、制作與使用數(shù)據(jù)透視表（15Min）,還是用5月無作業(yè)工時(shí)作為基礎(chǔ)數(shù)據(jù)分析；應(yīng)用數(shù)據(jù)透視表，將實(shí)現(xiàn)強(qiáng)大的數(shù)據(jù)分析及展示能力,A：各車間的損耗工時(shí)和HR損耗工時(shí)各是多少？,B：各車間的在5月份各停線多少單,C：各車間停線工時(shí)占5月百分比情況,D：各機(jī)型停線情況如何？,E：,2、創(chuàng)建數(shù)據(jù)透視表,六、制作與使用數(shù)據(jù)透視表（15Min）,1）鼠標(biāo)點(diǎn)擊數(shù)據(jù)區(qū)域； 2）“數(shù)據(jù)”菜單點(diǎn)擊“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”按鈕,六、制作與使用數(shù)據(jù)透視表（15Min）,3）按照統(tǒng)計(jì)要求拖放相關(guān)字段到行/列/頁/數(shù)據(jù)區(qū)域；,3、編輯數(shù)據(jù)透視表,六、制作與

15、使用數(shù)據(jù)透視表（15Min）,數(shù)據(jù)透視表可通過拖放與隱藏字段實(shí)現(xiàn)按照不同字段匯總及比對(duì),1、宏的功能,七、宏的錄制與運(yùn)用（10Min）,在Excel中，宏是指一系列Excel能夠執(zhí)行的VBA語句；在語言結(jié)構(gòu)中，宏由一系列 的命令和函數(shù)組成，保存在VBA的模塊中。可以根據(jù)用戶需要及時(shí)運(yùn)行，執(zhí)行對(duì)應(yīng) 的任務(wù)。,VBA：是一種功能強(qiáng)大的腳本邊寫語言，主要功能是實(shí)現(xiàn)應(yīng)用程序的自動(dòng)化； VBA中可以包含宏，但不僅僅由宏構(gòu)成；,當(dāng)需要重復(fù)執(zhí)行一個(gè)或多個(gè)簡(jiǎn)單命令時(shí)：使用宏可提高工作效率,1、宏的功能,七、宏的錄制與運(yùn)用（10Min）,舉例： 將以下數(shù)據(jù)統(tǒng)一更改字體為“黑體”，字體大小設(shè)置為9號(hào)；增加邊框； 并且將“車間”列填充為黃色； 在第一次操作時(shí)使用宏錄制功能，當(dāng)以上設(shè)置完成后，停止宏錄制； 則在數(shù)據(jù)內(nèi)容更改后，重新運(yùn)行宏能瞬間完成相同的設(shè)置，而不是重新手動(dòng)設(shè)置；,2、錄制與運(yùn)行宏Excel 2003“工具”菜單；“宏”-“錄制宏”；Excel 2007“視圖”菜單；“宏” -“錄制宏”,七、宏的錄制與運(yùn)用（10Min）,1）、進(jìn)入右圖界面： 宏名：任意名稱 快捷鍵：運(yùn)行該宏時(shí)使用 保