EXCEL高級(jí)應(yīng)用華南農(nóng)業(yè)大學(xué)圖書館—韓明明Email：華農(nóng)大研究生信息群：130082280EXCEL高級(jí)應(yīng)用華南農(nóng)業(yè)大學(xué)圖書館—韓明明Emai1序言Excel用戶水平的5個(gè)層次第2頁(yè)剛剛開始接觸基本掌握常用功能熟練使用常用功能+數(shù)據(jù)透視表+公式數(shù)組公式+VBA簡(jiǎn)單編程Excel技能+專業(yè)知識(shí)+行業(yè)經(jīng)驗(yàn)新手

初級(jí)用戶

中級(jí)用戶

高級(jí)用戶

專家序言Excel用戶水平的5個(gè)層次第2頁(yè)剛剛開始接觸基本掌握2知識(shí)結(jié)構(gòu)第3頁(yè)Excel數(shù)據(jù)處理與操作Excel函數(shù)與公式Excel圖表與圖形ExcelVBA與宏Excel數(shù)據(jù)分析知識(shí)結(jié)構(gòu)第3頁(yè)Excel數(shù)據(jù)處理與操作Excel函數(shù)與公式E3EXCEL使用狀況1、根本不知道其他功能2、知道功能但不知道如何使用3、暫時(shí)使用不上，不去了解第4頁(yè)使用了20%，常用的只有5%EXCEL使用狀況第4頁(yè)使用了20%，常用的只有5%4EXCEL學(xué)習(xí)方法1、興趣愛好，循序漸進(jìn)2、善用資源，學(xué)以致用

通過(guò)好書、網(wǎng)絡(luò)等資源學(xué)習(xí)

博大精深，不必深究但要了解3、多閱讀多實(shí)踐

實(shí)踐，實(shí)踐，再實(shí)踐

有問(wèn)題獨(dú)立思考解決，提高能力歸納，總結(jié)，積累第5頁(yè)EXCEL學(xué)習(xí)方法1、興趣愛好，循序漸進(jìn)第5頁(yè)5主要內(nèi)容一、基本功能二、單元格地址引用三、公式與函數(shù)四、數(shù)據(jù)分析五、圖表六、特殊技巧第6頁(yè)主要內(nèi)容一、基本功能第6頁(yè)6一、基本功能Excel版本兼容問(wèn)題第7頁(yè)一、基本功能Excel版本兼容問(wèn)題第7頁(yè)7一、基本功能功能區(qū)第8頁(yè)一、基本功能功能區(qū)第8頁(yè)8一、基本功能工作表區(qū)第9頁(yè)一、基本功能工作表區(qū)第9頁(yè)9一、基本功能工作表屬性第10頁(yè)1、工作表缺省數(shù)量（3）2、工作表缺省用戶名（sheet1、sheet2、sheet3）3、工作表列：214；工作表行：2204、單元格地址：列標(biāo)行標(biāo)單元格屬性1、單元格所在行、列的寬度和高度2、填充柄3、單元格格式：字體、填充、邊框等4、單元格批注：為單元格添加注釋，以便說(shuō)明單元格數(shù)據(jù)或公式的意義。一、基本功能工作表屬性第10頁(yè)1、工作表缺省數(shù)量（3）單元格10一、基本功能保護(hù)工作簿第11頁(yè)1、選擇“審閱”選項(xiàng)卡中“更改”組的“保護(hù)工作簿”2、保護(hù)結(jié)構(gòu)：不能插入、刪除工作表，更改工作表名稱3、保護(hù)窗口：保留窗口的大小及位置等保護(hù)工作表選擇“審閱”選項(xiàng)卡中“更改”組的“保護(hù)工作表”;保護(hù)工作表中數(shù)據(jù)不被任意修改一、基本功能保護(hù)工作簿第11頁(yè)1、選擇“審閱”選項(xiàng)卡中“更改11一、基本功能Excel打印第12頁(yè)1、小表格的打印設(shè)置

面積較小，不足一張紙，可以使用居中2、大表格的打印設(shè)置

超出一張紙，但超出有限，可以使用縮放的方式3、跨頁(yè)打印表頭設(shè)置

如果工作表的首行是標(biāo)題，而且工作表內(nèi)容較多，可能打印多頁(yè)?？梢酝ㄟ^(guò)頁(yè)面設(shè)置，將首行指定為標(biāo)題一、基本功能Excel打印第12頁(yè)1、小表格的打印設(shè)置12二、單元格地址引用引用第13頁(yè)引用即在公式中用到了其他單元格在表格中的位置。引用的作用在于標(biāo)識(shí)工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的單元格位置引用不同工作簿中的單元格稱為鏈接二、單元格地址引用引用第13頁(yè)引用即在公式中用到了其他單元格13二、單元格地址引用相對(duì)引用第14頁(yè)相對(duì)引用的公式復(fù)制到目標(biāo)單元格時(shí)，目標(biāo)單元格公式中單元格引用被更新，引用單元格與公式所在單元格的相對(duì)位置不變。例如：A1中的公式為：=B2*C3， A1中的公式復(fù)制到D3，D3中的公式變?yōu)椋?E4*F5二、單元格地址引用相對(duì)引用第14頁(yè)相對(duì)引用的公式復(fù)制到目標(biāo)單14二、單元格地址引用絕對(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二、單元格地址引用絕對(duì)引用第15頁(yè)絕對(duì)引用的公式復(fù)制到目標(biāo)單15二、單元格地址引用混合引用第16頁(yè)混合引用的單元格地址，一個(gè)是相對(duì)引用，另一個(gè)是絕對(duì)引用，如：$A1、A$1。二、單元格地址引用混合引用第16頁(yè)混合引用的單元格地址，一個(gè)16二、單元格地址引用三種引用類型的轉(zhuǎn)換第17頁(yè)將插入點(diǎn)定位在編輯欄某單元格的引用位置，按F4功能鍵可以實(shí)現(xiàn)相對(duì)引用、絕對(duì)引用、混合引用之間的轉(zhuǎn)換。例如：編輯欄上為：=B2*C3，選中公式中的B2，每按一下F4變?yōu)椋?$B$2*C3、=B$2*C3、=$B2*C3二、單元格地址引用三種引用類型的轉(zhuǎn)換第17頁(yè)將插入點(diǎn)定位在編17二、單元格地址引用三維引用第18頁(yè)格式：<工作表名>!<單元格引用>例如：=Sheet2!F5+Sheet3!C2說(shuō)明：如果工作表名稱包含空格，必須用單引號(hào)把工作表名稱括起來(lái)。例如：=‘匯總’!C25二、單元格地址引用三維引用第18頁(yè)格式：<工作表名>!<單元18二、單元格地址引用內(nèi)部引用與外部引用第19頁(yè)1、引用相同工作表中的單元格，例： =G3+G5+G10*102、引用同一工作簿不同工作表中的單元格，例： =Sheet1!G3+Sheet2!G5+Sheet2!E273、引用已打開的不同工作簿中的單元格，例： =[Book1]Sheet1!$IL$4+[Book2]Sheet2!$E$74、引用未打開的不同工作薄中的單元格，例： ='C:\dk\[Book1.xlsx]Sheet2'!$B$4+'C:\dk\[Book1.xlsx]Sheet1'!$C$6二、單元格地址引用內(nèi)部引用與外部引用第19頁(yè)1、引用相同工作19運(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)“=”開頭，即在Excel的單元格中，凡是以等號(hào)開頭的輸入數(shù)據(jù)都被認(rèn)為是公式。 3、在等號(hào)的后面可以跟數(shù)值、運(yùn)算符、變量或函數(shù)，在公式中還可以使用括號(hào)。

例：=SUM(A1:A20)高低運(yùn)算符優(yōu)先級(jí) 三、公式與函數(shù)第20頁(yè)公式的概念高低20三、公式與函數(shù)第21頁(yè)常見的錯(cuò)誤信息錯(cuò)誤值原因#DIV/0!當(dāng)數(shù)字被零(0)除時(shí)，出現(xiàn)錯(cuò)誤#N/A沒有可用的數(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ū)?，或者使用了?fù)的日期或負(fù)的時(shí)間時(shí)，出現(xiàn)錯(cuò)誤#VALUE!當(dāng)使用的參數(shù)或操作數(shù)類型錯(cuò)誤時(shí)，出現(xiàn)這種錯(cuò)誤三、公式與函數(shù)第21頁(yè)常見的錯(cuò)誤信息錯(cuò)誤值原因#DIV/0!21三、公式與函數(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ù)開始，則應(yīng)該像其它公式一樣，在函數(shù)前面加一個(gè)等號(hào)。

函數(shù)的左右兩個(gè)括號(hào)必須成對(duì)出現(xiàn)，括號(hào)前后都不能有空格。

一個(gè)公式最多可以嵌套七層函數(shù)。

三、公式與函數(shù)第22頁(yè)函數(shù)的概念22三、公式與函數(shù)第23頁(yè)函數(shù)的使用

格式：=<函數(shù)名>(<參數(shù)1>,<參數(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條件選擇

三、公式與函數(shù)第23頁(yè)函數(shù)的使用23四、數(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的記錄四、數(shù)據(jù)分析數(shù)據(jù)篩選第24頁(yè)1、自動(dòng)篩選：采用簡(jiǎn)單條件來(lái)快速24四、數(shù)據(jù)分析數(shù)據(jù)篩選第25頁(yè)2、高級(jí)篩選采用復(fù)合條件來(lái)篩選記錄，并允許把滿足條件的記錄復(fù)制到另外的區(qū)域，以生成一個(gè)新的數(shù)據(jù)清單。先建立條件區(qū)域。條件區(qū)域的第一行為條件標(biāo)記行，第二行開始是條件行。例：在產(chǎn)量單中查找糧食產(chǎn)量>100000，小麥產(chǎn)量>80000的記錄

篩選條件為：(糧食產(chǎn)量>100000，小麥產(chǎn)量>80000）四、數(shù)據(jù)分析數(shù)據(jù)篩選第25頁(yè)2、高級(jí)篩選采用復(fù)合條件來(lái)篩選記25四、數(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、排序方法

單一條件排序

多重條件排序

序列排序四、數(shù)據(jù)分析數(shù)據(jù)排序第26頁(yè)1、表格數(shù)據(jù)的排序原則26四、數(shù)據(jù)分析數(shù)據(jù)排序第27頁(yè)例：將成績(jī)表按“語(yǔ)文成績(jī)”從高到低進(jìn)行排序

(1)選定數(shù)據(jù)清單

(2)選擇“數(shù)據(jù)”菜單中的“排序”命令，打開“排序”對(duì)話框

(3)設(shè)置對(duì)話框四、數(shù)據(jù)分析數(shù)據(jù)排序第27頁(yè)例：將成績(jī)表按“語(yǔ)文成績(jī)”從高到27四、數(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)量四、數(shù)據(jù)分析數(shù)據(jù)分類匯總第28頁(yè)分類原則，要分類的項(xiàng)目必須單28四、數(shù)據(jù)分析第29頁(yè)例：在產(chǎn)量單中，按“地區(qū)”字段分類匯總面積和產(chǎn)量四、數(shù)據(jù)分析第29頁(yè)例：在產(chǎn)量單中，按“地區(qū)”字段分類匯總面29五、圖表第30頁(yè)圖表標(biāo)題坐標(biāo)軸Y坐標(biāo)軸X圖例繪圖區(qū)圖表區(qū)數(shù)據(jù)系列圖表的6要素五、圖表第30頁(yè)圖表標(biāo)題坐標(biāo)軸Y坐標(biāo)軸X圖例繪圖區(qū)30五、圖表Excel提供了多種圖表類型第31頁(yè)五、圖表Excel提供了多種圖表類型第31頁(yè)31五、圖表Excel圖表制作第32頁(yè)明確圖表分析的目標(biāo)與正確選擇表格數(shù)據(jù)的關(guān)系合理的確定圖表類型以正確表現(xiàn)分析的目標(biāo)圖表的編輯、修飾及打印五、圖表Excel圖表制作第32頁(yè)明確圖表分析的目標(biāo)與正確選32六、特殊技巧繪制斜線單元格第33頁(yè)1、利用Excel“邊框”選項(xiàng)卡的兩個(gè)斜線按鈕，可以在單元格中畫左、右斜線2、如果想在單元格中畫多條斜線，就必須利用“繪圖”工具。方法是:打開Excel的“繪圖”工具，單擊“直線”按鈕，待光標(biāo)變成小十字后拖動(dòng)光標(biāo)，即可畫出需要的多條斜線3、斜線單元格的數(shù)據(jù)輸入要麻煩一些，通常的做法是讓數(shù)據(jù)在單元格內(nèi)換行(按“Alt+回車鍵”)，再添加空格即可將數(shù)據(jù)放到合適位置4、還有一種數(shù)據(jù)輸入方法是添加文本框的方式六、特殊技巧繪制斜線單元格第33頁(yè)1、利用Excel“邊框”33六、特殊技巧在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ì)保持可見。六、特殊技巧在Excel中不丟掉列標(biāo)題的顯示第34頁(yè)如果工作34六、特殊技巧禁止復(fù)制隱藏行或列中的數(shù)據(jù)第35頁(yè)如果你復(fù)制了包含隱藏列(或行)的一個(gè)數(shù)據(jù)區(qū)域，然后把它粘貼到一個(gè)新的工作表，那么Excel把隱藏列也粘貼過(guò)來(lái)了。要想避免這種情況，可以選取你要復(fù)制的數(shù)據(jù)區(qū)域，然后選擇“編輯→定位”命令，單擊“定位條件”按鈕，出現(xiàn)“定位條件”對(duì)話框，選中“可見單元格”選項(xiàng)，再?gòu)?fù)制和粘貼這個(gè)選定區(qū)域就會(huì)得到你所希望的結(jié)果。六、特殊技巧禁止復(fù)制隱藏行或列中的數(shù)據(jù)第35頁(yè)如果你復(fù)制了包