《Excel中級(jí)培訓(xùn)教程.ppt》由會(huì)員分享，可在線閱讀，更多相關(guān)《Excel中級(jí)培訓(xùn)教程.ppt（52頁(yè)珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。
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） P3
2、7 使用數(shù)據(jù)透視表 （ 15Min） 創(chuàng)建數(shù)據(jù)透視表 編輯數(shù)據(jù)透視表 宏的錄制與運(yùn)用 （ 10Min） 宏的功能 錄制與運(yùn)行宏 Excel小技巧分享與討論（ 10Min） 課程大綱 前言 Excel用戶層次分類原則 剛剛開(kāi)始接觸 基本掌握常用功能 熟練使用常用功能 +數(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)算符 算術(shù)運(yùn)算符號(hào) 運(yùn)算符含義 示例 +(加號(hào) ) 加 2+3=5 -(減號(hào) ) 減 3-1=2 *(星號(hào) 乘 3*2=6 /
3、(斜杠 ) 除 6/2=3 %(百分號(hào) ) 百分號(hào) 50% (脫字號(hào) ) 乘方 43=43=64； 24=16 1、認(rèn)識(shí)公式 1.1、常量：指直接輸入公式中的數(shù)字或者文本值，即不用計(jì)算的值； 1.2、公式中的運(yùn)算符 比較運(yùn)算符 運(yùn)算符含義 示 例 =(等號(hào) ) 相等 B1=C1,若 B1中單元格內(nèi)的值確實(shí)與 C1中 的值相等 ,則產(chǎn)生邏輯真值 TRUE,若不相 等 ,則產(chǎn)生邏輯假值 FALSE (小于號(hào) ) 小于 B1(大于號(hào) ) 大于 B1C1,若 B1中數(shù)值為 6,C1中數(shù)值為 4,則 條件成立產(chǎn)生邏輯真值 TRUE,否則產(chǎn)生邏 輯假值 FALSE =(大于等于號(hào) ) 大于等于 B1=C1
4、 (不等號(hào) ) 不等于 B1C1 =(小于等于號(hào) ) 小于等于 B1=C1 一、公式與函數(shù)基礎(chǔ)（ 10Min） 1、認(rèn)識(shí)公式 3）、比較運(yùn)算符 2） . 文本運(yùn)算符： “ ” ，然后按空格鍵 最后按 “Ctrl+Shift+; 顯示當(dāng) 前日期和時(shí)間 區(qū)別： Now和 Today函數(shù)是動(dòng)態(tài)的； 但快捷鍵得到的是靜態(tài)的； 2、查詢函數(shù) 無(wú)作業(yè)工時(shí)的統(tǒng)計(jì)界面如下，如果想知道某個(gè)部門在某一個(gè)月的無(wú)作業(yè) 工時(shí)，如何解決？ Hlookup（水平查找） 三、使用函數(shù)（ 40Min） 需要在數(shù)據(jù)區(qū)域 首 行 搜索的值 原始的數(shù)據(jù) 區(qū)域 滿足條件 2的單元格 在數(shù)據(jù)區(qū)域的 行 數(shù) 0：找不到時(shí)：返回 #N/A；
5、 1：找不到時(shí)：返回 0 2、查詢函數(shù) 如果無(wú)作業(yè)工時(shí)的統(tǒng)計(jì)界面如下，想知道某個(gè)部門在某一個(gè)月的無(wú)作業(yè) 工時(shí)，又如何解決？ Vlookup（豎直查找） 三、使用函數(shù)（ 40Min） 需要在數(shù)據(jù)區(qū)域 首 列 搜索的值 - 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)擊單
6、元格右下角，填充公式后，手動(dòng)更改行數(shù) /列數(shù)即可 2、查詢函數(shù) 手中有一個(gè)部門的電話號(hào)碼清單，現(xiàn)在有其中一個(gè)人的電話號(hào)碼，需要 知道這個(gè)人是誰(shuí)？查找指定數(shù)值 lookup函數(shù) 三、使用函數(shù)（ 40Min） 注意：數(shù)據(jù)區(qū)域的值需要以升序排序，否則可能無(wú)法提供正確的值 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
7、，計(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，客戶的訂單情況 如下；想知道整包裝出貨后仍有多少數(shù)量剩余？ 4、數(shù)學(xué)函數(shù) 三、使用函數(shù)（ 40Min） 4）條件求和； SUMIf 各部門 Q1 無(wú)作業(yè)工時(shí)匯總?cè)缦?，如何快速取得一個(gè)或多個(gè)部門的 Q1無(wú)作 業(yè)工時(shí)之和？ 4、數(shù)學(xué)函數(shù) 三、使用函數(shù)（ 40Min） 5）舍入和取整函數(shù) Ceiling/Floor（按條件向上 /下舍入） Even/ODD
8、（按條件向上舍入為偶數(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ì)函數(shù) COUNT與 COUNTA 統(tǒng)計(jì)單元格個(gè)數(shù)； COUNTBLANK 統(tǒng)計(jì)空白單元格個(gè)數(shù)； COUNTIF：按條件統(tǒng)計(jì)； COUNTIFS：按多重條件統(tǒng)計(jì) 3）計(jì)算條件平均值 AVERAGEIF 1、自動(dòng)篩選： 快捷鍵： Alt+D+F+F 四、數(shù)據(jù)篩選（ 10Min） 2、高級(jí)篩選 數(shù)據(jù) 篩選 高級(jí)篩選
9、； 方式 1；在原有區(qū)域直接顯示篩選結(jié)果；不滿足條件部分隱藏不顯示； 列表區(qū)域：原始數(shù)據(jù)區(qū)域； 條件區(qū)域：篩選條件區(qū)域； 2、高級(jí)篩選 請(qǐng)使用 若要查找 ?（問(wèn)號(hào)） 任何單個(gè)字符 例如， sm?th 查找“ smith” 和“ smyth” *（星號(hào)） 任何字符數(shù) 例如， *east 查找“ Northeast” 和 “ Southeast” （波形符）后跟 ?、 * 或 問(wèn)號(hào)、星號(hào)或波形符 例如，“ fy91?” 將會(huì)查找“ fy91?” 通配符： 以下通配符可作為篩選以及查找和替換內(nèi)容時(shí)的比較條件 : 3、 數(shù)據(jù)有效性設(shè)置及應(yīng)用 舉例：在意見(jiàn)收集時(shí)，經(jīng)常碰到收集意見(jiàn)太發(fā)散的情況；比如以下情
10、況；對(duì)一 次 Team building 進(jìn)行意見(jiàn)收集；得到左下圖的結(jié)果。作為一個(gè)活動(dòng)組織者，如 何統(tǒng)計(jì)最終意見(jiàn)？ 四、數(shù)據(jù)篩選（ 10Min） 解決辦法：在發(fā)出意見(jiàn)收集表之前，使用數(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） 舉例：還是無(wú)作業(yè)工時(shí)：如果希望按照車間，來(lái)統(tǒng)計(jì)損耗工時(shí)以及損耗工時(shí)（ HR）； 1） 首先按照車間進(jìn)
11、行排序 2）數(shù)據(jù)菜單 -選擇分類匯總 四、數(shù)據(jù)匯總（ 10Min） 舉例：希望按照車間，來(lái)統(tǒng)計(jì)損耗工時(shí)以及損耗工時(shí)（ HR）； 點(diǎn)擊按鈕 2 點(diǎn)擊按鈕 1 2、數(shù)據(jù)的組合與取消 在同一個(gè) Excel sheet表中；當(dāng)數(shù)據(jù)行 /列非常多，對(duì)于大部分客戶來(lái)說(shuō)，只希 望閱讀到重點(diǎn)字段，可使用組合按鈕；使報(bào)表外觀更直觀，簡(jiǎn)潔； 四、數(shù)據(jù)匯總（ 10Min） 舉例：在下表中，如果只需要關(guān)注車間及損耗工時(shí)字段；除了使用隱藏按鈕外； 亦可使用組合按鈕 選中“車間”與“損耗工時(shí)”之間的數(shù)據(jù)列 單擊 數(shù)據(jù) -組及分級(jí)顯示 -組合按鈕 點(diǎn)擊：“損耗工時(shí)”上方按鈕，自動(dòng)合并上一步驟選中列；按鈕由 變成 + 3、合
12、并計(jì)算 當(dāng)數(shù)據(jù)行不多時(shí)，合并計(jì)算是數(shù)據(jù)匯總的又一個(gè)好工具； 四、數(shù)據(jù)匯總（ 10Min） 舉例：按照車間，匯總“損耗工時(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)題行即可得到匯
13、總結(jié)果； 4）在同一個(gè) sheet表對(duì)同一份數(shù)據(jù)二次合并計(jì)算時(shí)，注意需要?jiǎng)h除“ 所有引用位置”內(nèi)數(shù)據(jù)；重新選擇引用位置； 線柱圖 /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)槊?/p>
14、稱，金額，及累計(jì)百分比 2）在 “ 插入 ” 菜單， “ 圖表 ” 選項(xiàng)下選擇 自定義類型 “ 兩軸線柱圖 線柱圖 /MTB中的柏拉圖 舉例 ；在 KPI指標(biāo)的分解中，常需要了解各種不同因素的現(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á)到一圖
15、展示的效果； 五、制作與使用圖表（ 5Min） Excel 2007版本 1）選中數(shù)據(jù)，點(diǎn)擊“插入” “ 柱形圖” “ 二維柱形圖”； 線柱圖 /MTB中的柏拉圖 五、制作與使用圖表（ 5Min） Excel 2007版本 2）直方圖出現(xiàn)了，果然沒(méi)有折線 關(guān)鍵的步驟來(lái)了，在直方圖上點(diǎn)擊右 鍵，選擇“設(shè)置數(shù)據(jù)系列格式”； ； 線柱圖 /MTB中的柏拉圖 五、制作與使用圖表（ 5Min） Excel 2007版本 3）在“系統(tǒng)選項(xiàng)” “ 系列繪制在”中，選擇“次坐標(biāo)軸”?？吹搅藛?？表示累計(jì)百分比的紅色方出現(xiàn)了 ； 線柱圖 /MTB中的柏拉圖 五、制作與使用圖表（ 5Min） Excel 2007
16、版本 4） 在紅色方上單擊右鍵，選擇“更改系列圖表類型”； ； 5）在“更改圖標(biāo)類型”中，選擇“折線圖”； 線柱圖 /MTB中的柏拉圖 五、制作與使用圖表（ 5Min） Excel 2007版本 6） 折線出現(xiàn)啦 再在圖上單擊右鍵“添加數(shù)據(jù)標(biāo)簽”，線柱雙軸圖大功 造成！ ； 1、數(shù)據(jù)透視表的強(qiáng)大功能 六、制作與使用數(shù)據(jù)透視表（ 15Min） 還是用 5月無(wú)作業(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ù)
17、透視表 六、制作與使用數(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)字段到行 /列 /頁(yè) /數(shù)據(jù)區(qū)域； 3、編輯數(shù)據(jù)透視表 六、制作與使用數(shù)據(jù)透視表（ 15Min） 數(shù)據(jù)透視表可通過(guò)拖放與隱藏字段實(shí)現(xiàn)按照不同字段匯總及比對(duì) 1、宏的功能 七、宏的錄制與運(yùn)用（ 10Min） 在 Excel中，宏是指一系列 Excel能夠執(zhí)行的 VBA語(yǔ)句；在語(yǔ)言結(jié)構(gòu)中，宏由一系列 的命令和函數(shù)組成，保存在 VBA的模塊中。可以根據(jù)用戶需要及時(shí)運(yùn)行，執(zhí)行對(duì)應(yīng) 的任務(wù)。 VBA
18、：是一種功能強(qiáng)大的腳本邊寫語(yǔ)言，主要功能是實(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“視圖”菜單；“宏” -“錄制宏” 七、宏的錄制
19、與運(yùn)用（ 10Min） 1）、進(jìn)入右圖界面： 宏名：任意名稱 快捷鍵：運(yùn)行該宏時(shí)使用 保存在：默認(rèn)當(dāng)前工作表 說(shuō)明：任意備注 2）、確認(rèn)后：開(kāi)始進(jìn)行正常的數(shù)據(jù)處理；如，全選數(shù)據(jù)，增加邊框； 更改字體類型及大小，填充顏色； 3）、從菜單進(jìn)入“宏”，選擇“停止錄制”； 4）、當(dāng)數(shù)據(jù)區(qū)域數(shù)據(jù)變更后，從菜單進(jìn)入“宏”，選擇“查看宏”“執(zhí)行”；或直接 運(yùn)行快捷鍵，可運(yùn)行該宏； 2、錄制與運(yùn)行宏 打開(kāi)一個(gè)包含宏的 Excel文檔 ， 出現(xiàn)以下對(duì)話框時(shí)，從工具菜單 -選項(xiàng) -安全性 - 宏安全性中，將安全級(jí)別調(diào)低即可運(yùn)行； 七、宏的錄制與運(yùn)用（ 10Min） 八、 Excel小技巧分享與討論（ 15Min）
Excel中級(jí)培訓(xùn)教程.ppt
