excel高級(jí)應(yīng)用教學(xué)課件

1、EXCEL 高 級(jí) 應(yīng) 用華南農(nóng)業(yè)大學(xué)圖書(shū)館韓明明Email：華農(nóng)大研究生信息群： 130082280第1頁(yè)，共38頁(yè)。序言Excel用戶水平的5個(gè)層次第2頁(yè)剛剛開(kāi)始接觸 基本掌握常用功能 熟練使用常用功能+數(shù)據(jù)透視表+公式 數(shù)組公式+VBA簡(jiǎn)單編程 Excel技能+專業(yè)知識(shí)+行業(yè)經(jīng)驗(yàn) 新手 初級(jí)用戶 中級(jí)用戶 高級(jí)用戶 專家 第2頁(yè)，共38頁(yè)。知識(shí)結(jié)構(gòu)第3頁(yè)Excel數(shù)據(jù)處理與操作Excel函數(shù)與公式Excel圖表與圖形Excel VBA與宏Excel 數(shù)據(jù)分析第3頁(yè)，共38頁(yè)。EXCEL使用狀況1、根本不知道其他功能2、知道功能但不知道如何使用3、暫時(shí)使用不上，

2、不去了解第4頁(yè)使用了20%，常用的只有5%第4頁(yè)，共38頁(yè)。EXCEL學(xué)習(xí)方法1、興趣愛(ài)好，循序漸進(jìn)2、善用資源，學(xué)以致用通過(guò)好書(shū)、網(wǎng)絡(luò)等資源學(xué)習(xí)博大精深，不必深究但要了解3、多閱讀多實(shí)踐實(shí)踐，實(shí)踐，再實(shí)踐有問(wèn)題獨(dú)立思考解決，提高能力歸納，總結(jié)，積累第5頁(yè)第5頁(yè)，共38頁(yè)。主要內(nèi)容一、基本功能二、單元格地址引用三、公式與函數(shù)四、數(shù)據(jù)分析五、圖表六、特殊技巧第6頁(yè)第6頁(yè)，共38頁(yè)。一、基本功能Excel版本兼容問(wèn)題第7頁(yè)第7頁(yè)，共38頁(yè)。一、基本功能功能區(qū)第8頁(yè)第8頁(yè)，共38頁(yè)。一、基本功能工作表區(qū)第9頁(yè)第9頁(yè)，共38頁(yè)。一、基本功能工作表屬性第10頁(yè)1、工作表缺省數(shù)量（3）2、工作表缺省用戶名

3、（sheet1、 sheet2、 sheet3）3、工作表列：214；工作表行：2204、單元格地址：列標(biāo)行標(biāo)單元格屬性1、單元格所在行、列的寬度和高度2、填充柄3、單元格格式：字體、填充、邊框等4、單元格批注：為單元格添加注釋，以便說(shuō)明單元格數(shù)據(jù)或公式的意義。第10頁(yè)，共38頁(yè)。一、基本功能保護(hù)工作簿第11頁(yè)1、選擇“審閱”選項(xiàng)卡中“更改”組的“保護(hù)工作簿”2、保護(hù)結(jié)構(gòu)：不能插入、刪除工作表，更改工作表名稱3、保護(hù)窗口：保留窗口的大小及位置等保護(hù)工作表選擇“審閱”選項(xiàng)卡中“更改”組的“保護(hù)工作表”;保護(hù)工作表中數(shù)據(jù)不被任意修改第11頁(yè)，共38頁(yè)。一、基本功能Excel打印第12頁(yè)1、小表格的

4、打印設(shè)置面積較小，不足一張紙，可以使用居中2、大表格的打印設(shè)置超出一張紙，但超出有限，可以使用縮放的方式3、跨頁(yè)打印表頭設(shè)置如果工作表的首行是標(biāo)題，而且工作表內(nèi)容較多，可能打印多頁(yè)。可以通過(guò)頁(yè)面設(shè)置，將首行指定為標(biāo)題第12頁(yè)，共38頁(yè)。二、單元格地址引用引用第13頁(yè)引用即在公式中用到了其他單元格在表格中的位置。引用的作用在于標(biāo)識(shí)工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的單元格位置 引用不同工作簿中的單元格稱為鏈接第13頁(yè)，共38頁(yè)。二、單元格地址引用相對(duì)引用第14頁(yè)相對(duì)引用的公式復(fù)制到目標(biāo)單元格時(shí)，目標(biāo)單元格公式中單元格引用被更新，引用單元格與公式所在單元格的相對(duì)位置不變。例如：

5、A1中的公式為：=B2*C3，A1中的公式復(fù)制到D3，D3中的公式變?yōu)椋?E4*F5第14頁(yè)，共38頁(yè)。二、單元格地址引用絕對(duì)引用第15頁(yè)絕對(duì)引用的公式復(fù)制到目標(biāo)單元格時(shí)，目標(biāo)單元格公式中單元格引用保持不變。絕對(duì)引用表示方法：行列前加$符號(hào)例如：A1中的公式為：=$B$2*$C$3，A1中的公式復(fù)制到D3，D3中的公式仍為：=$B$2*$C$3第15頁(yè)，共38頁(yè)。二、單元格地址引用混合引用第16頁(yè)混合引用的單元格地址，一個(gè)是相對(duì)引用，另一個(gè)是絕對(duì)引用，如：$A1、A$1。第16頁(yè)，共38頁(yè)。二、單元格地址引用三種引用類型的轉(zhuǎn)換第17頁(yè)將插入點(diǎn)定位在編輯欄某單元格的引用位置，按F4功能鍵可以實(shí)現(xiàn)

6、相對(duì)引用、絕對(duì)引用、混合引用之間的轉(zhuǎn)換。例如：編輯欄上為：=B2*C3，選中公式中的B2，每按一下F4變?yōu)椋?$B$2*C3、=B$2*C3、 =$B2*C3第17頁(yè)，共38頁(yè)。二、單元格地址引用三維引用第18頁(yè)格式：!例如：=Sheet2!F5 + Sheet3!C2說(shuō)明：如果工作表名稱包含空格，必須用單引號(hào)把工作表名稱括起來(lái)。例如：=匯 總!C25第18頁(yè)，共38頁(yè)。二、單元格地址引用內(nèi)部引用與外部引用第19頁(yè)1、引用相同工作表中的單元格，例：=G3+G5+G10*102、引用同一工作簿不同工作表中的單元格，例：=Sheet1!G3+Sheet2!G5+Sheet2!E273、引用已打開(kāi)的

7、不同工作簿中的單元格，例：=Book1Sheet1!$IL$4+Book2Sheet2!$E$74、引用未打開(kāi)的不同工作薄中的單元格，例：=C:dkBook1.xlsxSheet2!$B$4+C:dkBook1.xlsxSheet1!$C$6第19頁(yè)，共38頁(yè)。運(yùn)算符優(yōu)先級(jí)引用運(yùn)算符（：、，）算術(shù)運(yùn)算符（+、-、*、/）文本運(yùn)算符（&）比較運(yùn)算符（、=）三、公式與函數(shù)第20頁(yè)公式的概念1、Excel的公式由運(yùn)算符、數(shù)值、字符串、變量和函數(shù)組成2、公式必須以等號(hào)“=”開(kāi)頭，即在Excel的單元格中，凡是以等號(hào)開(kāi)頭的輸入數(shù)據(jù)都被認(rèn)為是公式。3、在等號(hào)的后面可以跟數(shù)值、運(yùn)算符、變量或函數(shù)，在公式中還

8、可以使用括號(hào)。例： =SUM(A1:A20)高低第20頁(yè)，共38頁(yè)。三、公式與函數(shù)第21頁(yè)常見(jiàn)的錯(cuò)誤信息錯(cuò)誤值原因#DIV/0!當(dāng)數(shù)字被零 (0) 除時(shí)，出現(xiàn)錯(cuò)誤#N/A沒(méi)有可用的數(shù)值。通常情況是，您將數(shù)值直接輸入某些工作表單元格。雖然這些單元格中會(huì)包含數(shù)據(jù)，但目前尚無(wú)數(shù)據(jù)引用這些單元格的公式將返回而不會(huì)計(jì)算數(shù)值#NAME?當(dāng) Excel 未識(shí)別公式中的文本時(shí)，出現(xiàn)錯(cuò)誤#NULL!指定的兩個(gè)區(qū)域不相交#NUM!公式或函數(shù)中使用無(wú)效數(shù)字值時(shí)，出現(xiàn)這種錯(cuò)誤#REF!公式引用了無(wú)效的單元格（比如我們刪除了有其他公式引用的單元格，或者把移動(dòng)單元格粘貼到了其他公式引用的單元格中。）#當(dāng)列不夠?qū)?，或者使?/p>

9、了負(fù)的日期或負(fù)的時(shí)間時(shí)，出現(xiàn)錯(cuò)誤#VALUE!當(dāng)使用的參數(shù)或操作數(shù)類型錯(cuò)誤時(shí)，出現(xiàn)這種錯(cuò)誤第21頁(yè)，共38頁(yè)。三、公式與函數(shù)第22頁(yè)函數(shù)的概念函數(shù)是Excel已經(jīng)定義好了的一些特殊公式，它們可以對(duì)一個(gè)或多個(gè)數(shù)據(jù)進(jìn)行計(jì)算，然后把計(jì)算的結(jié)果存放在某個(gè)單元格中函數(shù)的類型工作表函數(shù)、財(cái)務(wù)函數(shù)、日期函數(shù)、時(shí)間函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)類函數(shù)、數(shù)據(jù)庫(kù)管理函數(shù)、文本函數(shù)及信息類函數(shù)等函數(shù)的使用函數(shù)由函數(shù)名和參數(shù)組成，例：SUM(A1:A10)公式以一個(gè)函數(shù)開(kāi)始，則應(yīng)該像其它公式一樣，在函數(shù)前面加一個(gè)等號(hào)。函數(shù)的左右兩個(gè)括號(hào)必須成對(duì)出現(xiàn)，括號(hào)前后都不能有空格。 一個(gè)公式最多可以嵌套七層函數(shù)。第22頁(yè)，共38頁(yè)

10、。三、公式與函數(shù)第23頁(yè)函數(shù)的使用格式： =(, ,.)舉例： = SUM(C2:C6) = AVERAGE(A1:A3,C2:C6)常用的函數(shù)SUM 求和數(shù)AVERAGE 求平方值MAX 求最大值MIN 求最小值COUNT 求數(shù)值數(shù)據(jù)的個(gè)數(shù)COUNTIF 條件求數(shù)值數(shù)據(jù)的個(gè)數(shù)IF 條件選擇第23頁(yè)，共38頁(yè)。四、數(shù)據(jù)分析數(shù)據(jù)篩選第24頁(yè)1、自動(dòng)篩選：采用簡(jiǎn)單條件來(lái)快速篩選記錄，將不滿足條件的記錄暫時(shí)隱藏起來(lái)，而將滿足條件的記錄顯示在工作表上；通過(guò)“數(shù)據(jù)”菜單中“篩選”命令的“自動(dòng)篩選”來(lái)實(shí)現(xiàn)的例：篩選出糧食種植面積大于等于20的記錄第24頁(yè)，共38頁(yè)。四、數(shù)據(jù)分析數(shù)據(jù)篩選第25頁(yè)2、高級(jí)篩選

11、采用復(fù)合條件來(lái)篩選記錄，并允許把滿足條件的記錄復(fù)制到另外的區(qū)域，以生成一個(gè)新的數(shù)據(jù)清單。先建立條件區(qū)域。條件區(qū)域的第一行為條件標(biāo)記行，第二行開(kāi)始是條件行 。例：在產(chǎn)量單中查找糧食產(chǎn)量100000，小麥產(chǎn)量80000的記錄 篩選條件為： (糧食產(chǎn)量100000，小麥產(chǎn)量80000 ）第25頁(yè)，共38頁(yè)。四、數(shù)據(jù)分析數(shù)據(jù)排序第26頁(yè)1、表格數(shù)據(jù)的排序原則區(qū)域命名必須包括“表頭”區(qū)域排序前要添加連續(xù)編號(hào)，以保護(hù)原始序列多重排序時(shí)只有主要關(guān)健字重復(fù)時(shí)才進(jìn)行次要關(guān)健字的排序符號(hào)優(yōu)先，數(shù)字其次，日期由遠(yuǎn)而近為升序，邏輯值false在前，包含公式按結(jié)果排序2、排序方法 單一條件排序多重條件排序序列排序第26

12、頁(yè)，共38頁(yè)。四、數(shù)據(jù)分析數(shù)據(jù)排序第27頁(yè)例：將成績(jī)表按“語(yǔ)文成績(jī)”從高到低進(jìn)行排序 (1) 選定數(shù)據(jù)清單 (2) 選擇“數(shù)據(jù)”菜單中的“排序”命令，打開(kāi)“排序”對(duì)話框 (3) 設(shè)置對(duì)話框第27頁(yè)，共38頁(yè)。四、數(shù)據(jù)分析數(shù)據(jù)分類匯總第28頁(yè)分類原則，要分類的項(xiàng)目必須單獨(dú)一列凡是需要匯總的列，數(shù)據(jù)區(qū)域中不要有空白單元格，以免分類中遺漏數(shù)據(jù)區(qū)內(nèi)格式應(yīng)統(tǒng)一操作原則：先排序，再匯總例：在產(chǎn)量單中，按“地區(qū)”字段分類匯總面積和產(chǎn)量第28頁(yè)，共38頁(yè)。四、數(shù)據(jù)分析第29頁(yè)例：在產(chǎn)量單中，按“地區(qū)”字段分類匯總面積和產(chǎn)量第29頁(yè)，共38頁(yè)。五、圖表第30頁(yè)圖表標(biāo)題 坐標(biāo)軸Y 坐標(biāo)軸X 圖例 繪圖區(qū) 圖表區(qū)

13、數(shù)據(jù)系列 圖表的6要素第30頁(yè)，共38頁(yè)。五、圖表Excel提供了多種圖表類型第31頁(yè)第31頁(yè)，共38頁(yè)。五、圖表Excel圖表制作第32頁(yè)明確圖表分析的目標(biāo)與正確選擇表格數(shù)據(jù)的關(guān)系合理的確定圖表類型以正確表現(xiàn)分析的目標(biāo)圖表的編輯、修飾及打印第32頁(yè)，共38頁(yè)。六、特殊技巧繪制斜線單元格第33頁(yè)1、利用Excel“邊框”選項(xiàng)卡的兩個(gè)斜線按鈕，可以在單元格中畫(huà)左、右斜線2、如果想在單元格中畫(huà)多條斜線，就必須利用“繪圖”工具。方法是:打開(kāi)Excel的“繪圖”工具，單擊“直線”按鈕，待光標(biāo)變成小十字后拖動(dòng)光標(biāo)，即可畫(huà)出需要的多條斜線3、斜線單元格的數(shù)據(jù)輸入要麻煩一些，通常的做法是讓數(shù)據(jù)在單元格內(nèi)換行

14、(按“Alt+回車鍵”)，再添加空格即可將數(shù)據(jù)放到合適位置4、還有一種數(shù)據(jù)輸入方法是添加文本框的方式第33頁(yè)，共38頁(yè)。六、特殊技巧在Excel中不丟掉列標(biāo)題的顯示第34頁(yè)如果工作表中數(shù)據(jù)列超過(guò)一屏，當(dāng)我們利用滾動(dòng)條來(lái)瀏覽數(shù)據(jù)時(shí)，該數(shù)據(jù)文件的行標(biāo)題或列標(biāo)題就無(wú)法在下一屏顯示，也就是只看到數(shù)據(jù)，無(wú)法知道數(shù)據(jù)的具體意義，這樣是很不明了的。解決方法: 選擇緊鄰列標(biāo)題的下一行，單擊“窗口凍結(jié)窗格”命令，被凍結(jié)的列標(biāo)題不會(huì)滾動(dòng)，而且在你移動(dòng)工作簿的其他部分時(shí)，列標(biāo)題會(huì)保持可見(jiàn)。第34頁(yè)，共38頁(yè)。六、特殊技巧禁止復(fù)制隱藏行或列中的數(shù)據(jù)第35頁(yè)如果你復(fù)制了包含隱藏列(或行)的一個(gè)數(shù)據(jù)區(qū)域，然后把它粘貼到一

15、個(gè)新的工作表，那么Excel把隱藏列也粘貼過(guò)來(lái)了。要想避免這種情況，可以選取你要復(fù)制的數(shù)據(jù)區(qū)域，然后選擇“編輯定位”命令，單擊“定位條件”按鈕，出現(xiàn)“定位條件”對(duì)話框，選中“可見(jiàn)單元格”選項(xiàng)，再?gòu)?fù)制和粘貼這個(gè)選定區(qū)域就會(huì)得到你所希望的結(jié)果。第35頁(yè)，共38頁(yè)。六、特殊技巧將單元格轉(zhuǎn)換成圖片形式插入到Word中第36頁(yè)假如要把Excel中某些單元格區(qū)域轉(zhuǎn)換成圖片形式，可先選中要進(jìn)行轉(zhuǎn)換的單元格區(qū)域，單擊“開(kāi)始復(fù)制圖片”命令，在彈出的“復(fù)制圖片”對(duì)話框中，可選擇“如屏幕所示”和“如打印效果”兩種顯示方式在這里選擇“如屏幕所示”和“圖片”單選按鈕，單擊“確定”按鈕。然后進(jìn)入Word中，單擊“編輯粘貼”命令，即可將選中的Excel單元格區(qū)域以圖片形式粘貼過(guò)來(lái)。如果用戶沒(méi)用添加表格框線，那么選擇“如打印效果”后，在進(jìn)行“粘貼圖片”操作后圖片中沒(méi)有邊框;如果選擇“