Excel操作與數(shù)據(jù)分析技巧ppt課件

1、培訓(xùn)科培訓(xùn)科 張其建張其建2019-6-242019-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í)l功能區(qū)：新的控制中心，匯集了一些基本的命令并使這些功能區(qū)：新的控制中心，匯集了一些基本的命令并使這些命令非常直觀。命令非常直觀。1、EXCEL簡(jiǎn)介你用2019？不一樣？沒有關(guān)系，我這里為大家提供一個(gè)03-07對(duì)照工具Excel2019Excel2019中有許多更改。其中以窗口頂部的更改最為明顯。中有許多更改。其中以窗口頂部的更改最為明顯。功能區(qū)的三個(gè)組成部分是選項(xiàng)卡、組和命

2、令。 選項(xiàng)卡：選項(xiàng)卡呈現(xiàn)了可在 Excel 中執(zhí)行的核心任務(wù)。組：組是指顯示在選項(xiàng)卡上的相關(guān)命令集。命令：命令是指按鈕、菜單或信息輸入框。1、EXCEL簡(jiǎn)介隱藏的命令功能區(qū)上的命令是最常用的命令。lExcel 2019 并不總是顯示每一項(xiàng)命令，只有在可能需要使用某些命令時(shí)，才會(huì)顯示這些命令，以響應(yīng)所執(zhí)行的操作。1、EXCEL簡(jiǎn)介更多的選項(xiàng)l單擊對(duì)話框啟動(dòng)器單擊對(duì)話框啟動(dòng)器 ，您就會(huì)看到一個(gè)對(duì)話框或任務(wù)窗格。，您就會(huì)看到一個(gè)對(duì)話框或任務(wù)窗格。1、EXCEL簡(jiǎn)介對(duì)話框啟動(dòng)器l在首次啟動(dòng) Excel 2019 時(shí)，快速訪問工具欄位于功能區(qū)上方。在快速訪問工具欄中，命令始終可見.l我們可以把一些常用的

3、命令列入快速工具欄中方便應(yīng)用。快速訪問工具欄1、EXCEL簡(jiǎn)介鍵盤快捷方式l功能區(qū)上的每一個(gè)按鈕都有快捷方式。功能區(qū)上的每一個(gè)按鈕都有快捷方式。l新快捷方式都有提示。新快捷方式都有提示。l以以 Ctrl 開頭的舊鍵盤快捷方式?jīng)]有變化。開頭的舊鍵盤快捷方式?jīng)]有變化。1、EXCEL簡(jiǎn)介新視圖l在工作表直接看到：行在工作表直接看到：行/列標(biāo)題、頁邊距標(biāo)尺。列標(biāo)題、頁邊距標(biāo)尺。l在打印之前無需使用在打印之前無需使用“打印預(yù)覽來發(fā)現(xiàn)問題。打印預(yù)覽來發(fā)現(xiàn)問題。l添加頁眉和頁腳比以前更加簡(jiǎn)捷。添加頁眉和頁腳比以前更加簡(jiǎn)捷。1、EXCEL簡(jiǎn)介兼容性1、EXCEL簡(jiǎn)介類型97-20192019不帶宏的工作簿.x

4、ls.xlsx帶宏的工作簿.xls.xlsm文件格式為方便在使用新版excle時(shí)，可以設(shè)置中默認(rèn)的文件保存格式強(qiáng)烈推薦所有正在使用Excel2019的同事們，使用EXCEL2019或20191EXCEL簡(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 F13 C7絕對(duì)位置

5、:列絕對(duì)：$A2、$F13、$C7行絕對(duì)：A$2、F$13、C$7絕對(duì)位置：$A$2、$F$13、$C$7援用援用“$”表示對(duì)后面所表達(dá)的列或行的鎖定。留意：引用在我們拖動(dòng)公式或填充時(shí)有重大的影響留意：引用在我們拖動(dòng)公式或填充時(shí)有重大的影響只有理解了應(yīng)用，你才能更好的使用公式只有理解了應(yīng)用，你才能更好的使用公式2、EXCEL常用操作及功能援用名稱命名與引用我們可以對(duì)某些單元格定義名稱后直接使用名稱，以簡(jiǎn)化公式內(nèi)容。2、EXCEL常用操作及功能填充l當(dāng)數(shù)據(jù)量少的時(shí)候：直接采用鼠標(biāo)拖拉即可。l填充時(shí)尤其要注意引用方式l右鍵也是可以填充的當(dāng)數(shù)據(jù)量多的時(shí)候：需要采用鍵盤來操作（如果采取鼠標(biāo)填充的方式，

6、就會(huì)拖到地久天長(zhǎng)”)l具體快捷鍵：l快速移動(dòng)：ctrl+方向鍵（）l快速選中：ctrl+ shift+方向鍵（）l快速填充：ctrl + D向下） ctrl + R向右）2、EXCEL常用操作及功能左鍵填充右鍵填充復(fù)制復(fù)制公式、復(fù)制數(shù)據(jù)、復(fù)制格式行列互換不同的復(fù)制方式能起到神奇的工效。2、EXCEL常用操作及功能選定遇到數(shù)據(jù)中太多的空格時(shí)，有一種抓狂的感覺F5：定位條件可以為你帶來很多快樂2、EXCEL常用操作及功能數(shù)據(jù)有效性可以幫助我們?cè)O(shè)置可選的菜單，避免五花八門的描述產(chǎn)生。2、EXCEL常用操作及功能排序行也是可以排序的漢字可以按筆劃排序2、EXCEL常用操作及功能排序自定義序列OFFIC

7、E按鈕EXCEL 選項(xiàng)常用編輯自定義列表設(shè)定按照自己順序的排列2、EXCEL常用操作及功能挑選自動(dòng)篩選和高級(jí)篩選2、EXCEL常用操作及功能分列快速的將一些有規(guī)律的數(shù)據(jù)分成不同類別。2、EXCEL常用操作及功能分類匯總2、EXCEL常用操作及功能單元格格式快捷鍵：Ctrl+12、EXCEL常用操作及功能條件格式開場(chǎng)款式條件格式條件格式是2019里面新增加的一項(xiàng)功能，按要求對(duì)數(shù)據(jù)進(jìn)行分類顯示2、EXCEL常用操作及功能凍結(jié)和拆分這是查看大量數(shù)據(jù)顯示表頭的利器2、EXCEL常用操作及功能2、EXCEL常用操作及功能其他簡(jiǎn)便功能A、刪除重復(fù)數(shù)據(jù)照相機(jī)-把數(shù)據(jù)變?yōu)閳D片，防止泄露其他數(shù)據(jù)對(duì)象選擇工具-選

8、擇EXCEL中的圖形對(duì)象B、隱藏著的快捷工具按拼音排序1EXCEL簡(jiǎn)介2EXCEL常用操作及功能3EXCEL函數(shù)使用技巧4數(shù)據(jù)透視表5公式及函數(shù)6數(shù)組的運(yùn)用報(bào)告目錄宏與簡(jiǎn)單VBA源數(shù)據(jù)-一定是要規(guī)則清晰，無空格斷行的數(shù)據(jù)。l源數(shù)據(jù)的每列都將成為一個(gè)字段并且可在報(bào)表中使用。l報(bào)表字段的的名稱來自源數(shù)據(jù)中的列標(biāo)題，標(biāo)題下的其余各行應(yīng)該包含同一列中的類似項(xiàng)。l刪除用于數(shù)據(jù)塊相互隔開的空白行。數(shù)據(jù)透視表3、數(shù)據(jù)透視表一種可以動(dòng)態(tà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)區(qū)域

9、3、數(shù)據(jù)透視表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ù)等），顯示方式(百分比等等數(shù)據(jù)透視圖的使用方法和透視表一致，只是多了一個(gè)可以篩選的圖表1EXCEL簡(jiǎn)介2EXCEL常用操作及功能3EXCEL函數(shù)使用技巧4數(shù)據(jù)透視表5公式及函數(shù)6數(shù)組的運(yùn)用報(bào)告目錄宏與簡(jiǎn)單VBA1、公式簡(jiǎn)介EXCEL中公式分為了很多類四、公式及函數(shù)我們學(xué)習(xí)函數(shù)最大的幫助工具是EXCEl自帶的幫助編寫公式時(shí)你應(yīng)該具備基礎(chǔ)的程序邏輯思考技巧。四

10、、公式及函數(shù)2、函數(shù)與公式的基本概念EXCEL公式以“=“為引導(dǎo)，后面接上函數(shù)，以及函數(shù)所使用的參數(shù)=IF(logical_test, value_if_true, value_if_false)參數(shù)數(shù)據(jù)類型在EXCEL中可運(yùn)算的數(shù)據(jù)類型分為：文本，數(shù)值、日期、邏輯值、錯(cuò)誤值運(yùn)算符號(hào)符號(hào)類型符號(hào)案例算術(shù)運(yùn)算+；-；*；/； =62=3+2*5比較運(yùn)算;=;；=；=；=51, SUM(B1:G1),”)在這個(gè)公式中，sum函數(shù)是2級(jí)函數(shù)，它是if函數(shù)的參數(shù)Excel2019允許64層的嵌套，2019只能7層。=IF(MOD(ROW(),3)=1, 源數(shù)據(jù)!A$1,IF(MOD(ROW(),3)=

11、2,INDEX(源數(shù)據(jù)!$A$1:$G$349,(ROW()+4)/3,COLUMN(), )第1個(gè)IF的條件參數(shù)當(dāng)條件為真時(shí)返回該值當(dāng)條件為假時(shí)進(jìn)入下一個(gè)IF第2個(gè)IF的條件參數(shù)當(dāng)條件為真時(shí)進(jìn)入Index函數(shù)當(dāng)條件為假時(shí)返回空值5、如何解讀函數(shù)=IF(MOD(ROW(),3)=1,源數(shù)據(jù)!A$1,IF(MOD(ROW(),3)=2,INDEX(源數(shù)據(jù)!$A$1:$G$349,(ROW()+4)/3,COLUMN(), )復(fù)雜案例復(fù)雜案例A、設(shè)計(jì)目的：按規(guī)律切割工資條B、設(shè)計(jì)思路：結(jié)果的第一行：永遠(yuǎn)是原數(shù)據(jù)中的第一行。結(jié)果的第二行：結(jié)果第2行對(duì)應(yīng)原數(shù)據(jù)第2行，結(jié)果第5行對(duì)應(yīng)原數(shù)據(jù)第3行，第8對(duì)

12、應(yīng)第4，第11對(duì)應(yīng)第5。結(jié)果第三行：永遠(yuǎn)為空。這里面第二個(gè)最難算，我們看看他們之間有什么規(guī)律。案例:工資打印切割公式=IF(MOD(ROW(),3)=1,源數(shù)據(jù)!A$1,IF(MOD(ROW(),3)=2,INDEX(源數(shù)據(jù)!$A$1:$G$349,(ROW()+4)/3,COLUMN(), )四、公式及函數(shù)6、函數(shù)是怎么編出來的案例4.25、公式的查錯(cuò)錯(cuò)誤值含義#列寬不夠顯示，或使用了負(fù)的日期，和時(shí)間。#VALUE!參數(shù)或數(shù)值類型錯(cuò)誤#DIV/0!被零除錯(cuò)誤#NAME？無法識(shí)別的公式#N/A數(shù)值對(duì)函數(shù)或公式不可用#REF!單元格引用無效#NUM!無效的數(shù)字值#NULL!計(jì)算為空值時(shí)，無法得出

13、計(jì)算結(jié)果；例如：公式差錯(cuò)的工具：1、鍵盤上的F92、公式選項(xiàng)卡中的公式查錯(cuò)時(shí)的要訣：1、讀懂公式2、看看公式在拖動(dòng)過程中的引用方式是否正確3、當(dāng)嵌套太多太復(fù)雜時(shí)，把公式分層粘到單元格中四、公式及函數(shù)1EXCEL簡(jiǎn)介2EXCEL常用操作及功能3EXCEL函數(shù)使用技巧4數(shù)據(jù)透視表5公式及函數(shù)6數(shù)組的運(yùn)用報(bào)告目錄宏與簡(jiǎn)單VBA數(shù)組的概念五、數(shù)組的運(yùn)用數(shù)組是一個(gè)或多個(gè)元素按照行業(yè)排列方式組成的組合。在EXCEl中使用表達(dá)數(shù)組 =1;2;3;4;51、在一個(gè)單元格中輸入數(shù)組2、選中5個(gè)格子，按鍵盤上的F2。3、CTRL+SHIFT+ENTER按鍵：CTRL+SHIFT+ENTER數(shù)組的輸入數(shù)組的使用五、數(shù)組的運(yùn)用1、頻次統(tǒng)計(jì)2、不確定結(jié)果的查詢案例=FREQUENCY(C2:C6,E2:E5)=IF(ROW(1:1)COUNTIF(A:A,$C$1),INDEX(B:B,SMALL(IF(A$2:A$10=$C$1,ROW($2:$10),ROW(1:1)1EXCEL簡(jiǎn)介2EXCEL常用操作及功能3EX