EXCEL工作表的熟練應(yīng)用技巧課件

EXCEL在財(cái)務(wù)銷售及管理工作中的高級運(yùn)用EXCEL在財(cái)務(wù)銷售及管理工作中的高級運(yùn)用1企業(yè)管理軟件(ERP)與EXCEL1、Excel散2、ERP思想

3、ERPEXCEL結(jié)合培訓(xùn)前言企業(yè)管理軟件(ERP)與EXCEL培訓(xùn)前言2讓Excel與眾不同的技巧各類公式和函數(shù)的使用制作讓領(lǐng)導(dǎo)滿意的圖表宏和自定義函數(shù)其他實(shí)用工具及技巧內(nèi)容讓Excel與眾不同的技巧內(nèi)容3一．讓Excel與眾不同的技巧高效（工具欄、快捷鍵）安全（單元格、工作表和工作簿保護(hù)大全）便捷（引用、鏈接、超級鏈接和高級打印格式）美觀（專業(yè)、規(guī)范）內(nèi)容一．讓Excel與眾不同的技巧內(nèi)容4第一步：打開自定義開關(guān)（三種方法）右鍵菜單或工具欄–>自定義工具->自定義視圖->工具欄->自定義一(1).高效優(yōu)化自己的工具欄和菜單第一步：打開自定義開關(guān)（三種方法）一(1).高效5一(1).高效優(yōu)化自己的工具欄和菜單第二步：選擇命令--類別，通過拖動可實(shí)現(xiàn)：在現(xiàn)有的工具欄內(nèi)添加按鈕在現(xiàn)有的工具欄內(nèi)刪除按鈕在現(xiàn)有的菜單內(nèi)添加命令在現(xiàn)有的菜單內(nèi)刪除命令創(chuàng)建自己的工具欄創(chuàng)建自己的菜單一(1).高效優(yōu)化自己的工具欄和菜單第二步：選擇命6一(1).高效優(yōu)化自己的工具欄和菜單練習(xí)：把下列工具按鈕拖入工具欄內(nèi)一(1).高效優(yōu)化自己的工具欄和菜單練習(xí)：把下列工具7一(1).高效熟練使用常用鍵盤快捷鍵Excel常用鍵盤快捷鍵：Alt+Enter在同一單元格中新增一行F4重復(fù)上一個(gè)操作Alt+F11顯示VB編輯器Ctrl+空格選中整列Shift+空格選中整行Ctrl+C將所選項(xiàng)復(fù)制到剪貼板Ctrl+V從剪貼板中復(fù)制內(nèi)容到選中單元格

Ctrl+Z快速撤消操作Ctrl+Y快速恢復(fù)操作詳細(xì)鍵盤快捷鍵見文件：<<Excel鍵盤快捷鍵大全>>一(1).高效熟練使用常用鍵盤快捷鍵Excel常用8一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格公式的步驟：第一步：設(shè)置單元格格式（格式-單元格-保護(hù)）參見文件案例--保護(hù).xls一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格9一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格公式的步驟：第二步：保護(hù)工作表（工具-保護(hù)-保護(hù)工作表）一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格10一(2).安全單元格保護(hù)2.隱藏整行或整列的步驟：（方法一）選中某行或某列單擊鼠標(biāo)右鍵選擇：隱藏一(2).安全單元格保護(hù)2.隱藏整行或整列的步驟：11一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：（方法二：分級顯示）選中需要隱藏幾行或幾列數(shù)據(jù)-組及分級顯示-組合一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：12一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：（方法二：分級顯示）單擊或可在顯示和隱藏之間進(jìn)行切換一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：13一(2).安全單元格保護(hù)4.隱藏部分單元格內(nèi)容的技巧（將文字顏色設(shè)為和背景色一樣）一(2).安全單元格保護(hù)4.隱藏部分單元格內(nèi)容的技14一(2).安全單元格保護(hù)5.隱藏部分單元格內(nèi)容的技巧（將單元格式修改為自定義；；；）一(2).安全單元格保護(hù)5.隱藏部分單元格內(nèi)容的技15一(2).安全工作表保護(hù)1.隱藏工作表步驟：（方法一）格式-工作表-隱藏一(2).安全工作表保護(hù)1.隱藏工作表步驟：（方法16一(2).安全工作表保護(hù)2.隱藏工作表步驟：（方法二）切換到VB編輯器(Alt+F11)選中需隱藏的工作表將Visible屬性改為２一(2).安全工作表保護(hù)2.隱藏工作表步驟：（方法17一(2).安全工作簿保護(hù)1.設(shè)置工作簿密碼的步驟：(Excel2000)文件-另存為-工具-常規(guī)選項(xiàng)設(shè)置打開權(quán)限密碼或修改權(quán)限密碼一(2).安全工作簿保護(hù)1.設(shè)置工作簿密碼的步驟：18一(2).安全工作簿保護(hù)2.設(shè)置工作簿密碼的步驟:(Excel2003)工具-選項(xiàng)-安全性設(shè)置打開權(quán)限密碼或修改權(quán)限密碼一(2).安全工作簿保護(hù)2.設(shè)置工作簿密碼的步驟:19一(3).便捷引用、鏈接和超級鏈接引用同一工作表中其他單元格，或同一工作簿中其他工作表中的單元格稱為“引用”。引用其他工作簿中的單元格稱“外部引用”，或“鏈接”。超鏈接則是完全不同的概念。它類似于Web瀏覽器中超鏈接：單擊一個(gè)超鏈接就會打開鏈接的目標(biāo)，而不會把任何值傳送到鏈接的目標(biāo)。一(3).便捷引用、鏈接和超級鏈接引用同一工作表中其20幾乎所有的公式都有單元格或區(qū)域引用.有三種類型的引用:

相對引用:復(fù)制公式時(shí),引用單元格的行或列會改變

絕對引用:復(fù)制公式時(shí),引用單元格的行和列都不會改變

混合引用:行或列中,有一個(gè)是相對引用,另一個(gè)是絕對引用在單元格引用的列字母或行號前加入美元符號$便可創(chuàng)建絕對引用或混合引用,可用F4鍵在四種引用類型中循環(huán)選擇.選用適當(dāng)?shù)囊妙愋涂梢蕴岣咻斎?拷貝公式的效率.引用一(3).便捷幾乎所有的公式都有單元格或區(qū)域引用.引用一(3).便21一個(gè)簡單的例子(相對引用):一(3).便捷參見文件案例--引用.xls一個(gè)簡單的例子(相對引用):一(3).便捷參見文件22復(fù)雜一點(diǎn)的例子(混合引用):一(3).便捷參見文件案例--引用.xls復(fù)雜一點(diǎn)的例子(混合引用):一(3).便捷參見文件案23練習(xí)：在D4單元格中輸入公式，然后拷貝到D4:I13區(qū)域中所有的單元格一(3).便捷參見文件案例--引用.xls練習(xí)：在D4單元格中輸入公式，然后拷貝到D4:I13區(qū)域中所24一(3).便捷鏈接當(dāng)打開含有鏈接的Excel文件時(shí)，通常Excel會提示您需不需要更新鏈接，最好選擇不更新。當(dāng)確實(shí)需要更新鏈接的內(nèi)容時(shí)，可手動更新：編輯-〉鏈接（如下頁所示）我們甚至可以允許鏈接的文件更名一(3).便捷鏈接當(dāng)打開含有鏈接的Excel文件25一(3).便捷鏈接參見文件案例--鏈接.xls一(3).便捷鏈接參見文件案例--鏈接.xls26一(3).便捷超級鏈接在Excel中可以建立下列超鏈接：鏈接到Web站點(diǎn)鏈接到其他已有文檔鏈接到一個(gè)還不存在的文檔（Excel將在您單擊超鏈接時(shí)創(chuàng)建文檔）鏈接到本文檔其他的地方鏈接到一個(gè)電子郵件地址（單擊超鏈接時(shí)，Excel會啟動默認(rèn)的郵件程序）一(3).便捷超級鏈接在Excel中可以建立下列27一(3).便捷引用、鏈接和超級鏈接鏈接引用超鏈接參見文件案例--鏈接.xls一(3).便捷引用、鏈接和超級鏈接鏈接引用超鏈接參見28打印格式的設(shè)定盡量把內(nèi)容打印在一頁內(nèi)如果內(nèi)容較多需分頁打印,則應(yīng)為每頁設(shè)置好行列標(biāo)題打印的文件應(yīng)有公司的Logo,文件名,頁碼,日期等如果需要,可打印行號列標(biāo)用黑白打印機(jī)打印時(shí),最好選用單色打印一個(gè)文件的多張表可同時(shí)打印文件路徑一(3).便捷打印格式的設(shè)定一(3).便捷29可以考慮:不顯示網(wǎng)格線選用您獨(dú)特的字體,如Tahoma,Verdana(公司另有規(guī)定除外)表頭,標(biāo)題和正文應(yīng)選用不同的字號和圖案以示區(qū)別規(guī)范使用數(shù)字格式?jīng)]有必要把所有的數(shù)據(jù)都放在一張表內(nèi)盡量縱向安排數(shù)據(jù),以方便瀏覽設(shè)置合適的顯示比例,以盡量在一屏顯示全部數(shù)據(jù)(Ctrl+鼠標(biāo)滾動輪)固定窗格為每一張工作表命名一個(gè)有意義的名稱刪除多余的工作表一(4).美觀參見文件案例--美觀可以考慮:一(4).美觀參見文件案例--美觀30內(nèi)容二．各類公式和函數(shù)的使用公式的定義及使用公式的基本技巧不使用公式就知道某些計(jì)算結(jié)果的技巧計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧如何定義名稱和在公式中使用名稱幾個(gè)常用函數(shù)的定義及實(shí)例數(shù)據(jù)庫函數(shù)六種公式錯(cuò)誤值的理解及解決內(nèi)容二．各類公式和函數(shù)的使用31定義所有的公式都以等號（=）開始公式中可使用的運(yùn)算符有：加（+）、減（-）、乘（*）、除（/）、乘方（^)、括號（()），運(yùn)算規(guī)則和數(shù)學(xué)中的一樣二(1).公式的定義及基本技巧定義二(1).公式的定義及基本技巧32基本技巧在公式中盡量使用引用而少輸入數(shù)值可用鼠標(biāo)點(diǎn)擊單元格來輸入引用，而不必手工輸入適當(dāng)使用括號或空格以易于閱讀和理解當(dāng)公式較長時(shí)，可用Alt+Enter強(qiáng)行換行，以方便閱讀按F2或雙擊單元格可進(jìn)入公式編輯狀態(tài)可按F9進(jìn)行公式的重新計(jì)算既可以復(fù)制公式，也可以復(fù)制公式的計(jì)算值二(1).公式的定義及基本技巧基本技巧二(1).公式的定義及基本技巧33 在Excel的工作表中，選中某些連續(xù)或不連續(xù)的單元格，那么這些單元格的一些計(jì)算（求和、平均值、最大或最小等）會自動顯示在狀態(tài)欄，而不用輸入公式計(jì)算。如下圖：二(2).不使用公式就知道計(jì)算結(jié)果的技巧 在Excel的工作表中，選中某些連續(xù)或不連續(xù)的單元格，34 如果只想知道某個(gè)復(fù)雜公式中某部分的計(jì)算結(jié)果，可選中該部分然后按F9，該部分公式就會顯示為計(jì)算值。如下圖：二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--薪酬.xls 如果只想知道某個(gè)復(fù)雜公式中某部分的計(jì)算結(jié)果，可選中該部分然35注意：該部分應(yīng)該是完整的能計(jì)算出結(jié)果的公式檢查完后可按ESC鍵退出，而不能按回車鍵，否則選中的部分就會被計(jì)算結(jié)果代替二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--薪酬.xls注意：二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案36 在Excel2003中可用公式求值工具一步步測算每一部分的計(jì)算結(jié)果。如下圖：二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧 在Excel2003中可用公式求值工具一步步測算每一部37二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--函數(shù)應(yīng)用.xls二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--38二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--函數(shù)應(yīng)用.xls二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--39一個(gè)例子二(4).定義和使用名稱1/4參見文件案例--名稱.xls一個(gè)例子二(4).定義和使用名稱1/4參見文件案例--名稱40定義名稱 Ctrl+F3二(4).定義和使用名稱2/4定義名稱 Ctrl+F3二(4).定義和使用名稱2/441在公式中插入名稱F3二(4).定義和使用名稱3/4在公式中插入名稱F3二(4).定義和使用名稱3/442創(chuàng)建名稱列表F3二(4).定義和使用名稱4/4創(chuàng)建名稱列表F3二(4).定義和使用名稱4/443IFANDCOUNTACOUNTIFSUMIFSUMPRODUCTVLOOKUPHLOOKUPROUND

二(5).熟練使用常用函數(shù)1/9參見文件案例--函數(shù).xlsIF二(5).熟練使用常用函數(shù)1/9參見文件案例--函數(shù).44IF

二(5).熟練使用常用函數(shù)2/9參見文件案例--函數(shù).xlsIF 二(5).熟練使用常用函數(shù)2/9參見文件案例--函數(shù)45AND二(5).熟練使用常用函數(shù)3/9參見文件案例--函數(shù).xlsAND二(5).熟練使用常用函數(shù)3/9參見文件案例--函數(shù)46COUNTA二(5).熟練使用常用函數(shù)4/9參見文件案例--函數(shù).xlsCOUNTA二(5).熟練使用常用函數(shù)4/9參見文件案例-47COUNTIF二(5).熟練使用常用函數(shù)5/9參見文件案例--函數(shù).xls/練習(xí)--統(tǒng)計(jì)不及格人數(shù).xlsCOUNTIF二(5).熟練使用常用函數(shù)5/9參見文件案例48SUMIF

二(5).熟練使用常用函數(shù)6/9參見文件案例--函數(shù).xlsSUMIF 二(5).熟練使用常用函數(shù)6/9參見文件案例-49SUMPRODUCT

二(5).熟練使用常用函數(shù)7/9參見文件案例--函數(shù).xlsSUMPRODUCT 二(5).熟練使用常用函數(shù)7/9參見50VLOOKUP二(5).熟練使用常用函數(shù)8/9參見文件案例--函數(shù).xls//所得稅模型.xlsVLOOKUP二(5).熟練使用常用函數(shù)8/9參見文件案例51ROUND二(5).熟練使用常用函數(shù)9/9參見文件案例--函數(shù).xlsROUND二(5).熟練使用常用函數(shù)9/9參見文件案例--52什么是數(shù)據(jù)庫?數(shù)據(jù)庫是一個(gè)內(nèi)涵比較廣泛的單詞,在Excel中我們通常指數(shù)據(jù)列表Excel中的數(shù)據(jù)列表通常滿足下列條件:首行由標(biāo)簽組成,每一個(gè)標(biāo)簽描述的是下面整列的內(nèi)容.該標(biāo)簽被稱為字段每一個(gè)字段都是唯一的除第一行外，其他的行被稱為記錄不包含空白行或列見下頁示例二(6).數(shù)據(jù)庫及其函數(shù)1/4什么是數(shù)據(jù)庫?二(6).數(shù)據(jù)庫及其函數(shù)1/453二(6).數(shù)據(jù)庫及其函數(shù)2/4參見文件案例--函數(shù).xls二(6).數(shù)據(jù)庫及其函數(shù)2/4參見文件案例--函數(shù).xls54數(shù)據(jù)庫函數(shù)二(6).數(shù)據(jù)庫及其函數(shù)3/4參見文件案例--函數(shù).xls數(shù)據(jù)庫函數(shù)二(6).數(shù)據(jù)庫及其函數(shù)3/4參見文件案例--函55數(shù)據(jù)庫函數(shù)中條件的寫法:可以放在數(shù)據(jù)列表之外的工作表的任意位置,但通常在數(shù)據(jù)列表的上面或下面至少包含兩行,第一行只包含某些或全部字段名其他行包括條件同一行的條件意味”與”不同行的條件意味”或”條件中可包含=,>,<,>=,<=等比較符條件中也可包含公式或單元格引用二(6).數(shù)據(jù)庫及其函數(shù)4/4參見文件案例--函數(shù).xls數(shù)據(jù)庫函數(shù)中條件的寫法:二(6).數(shù)據(jù)庫及其函數(shù)4/4參見56六種公式錯(cuò)誤值:二(7).公式錯(cuò)誤值的理解參見文件案例--錯(cuò)誤值.xls六種公式錯(cuò)誤值:二(7).公式錯(cuò)誤值的理解參見文件案例--57三．制作讓領(lǐng)導(dǎo)滿意的圖表創(chuàng)建圖表快捷鍵和創(chuàng)建圖表標(biāo)準(zhǔn)四步曲識別、修改及美化圖表的八要素如何在圖表中使用次坐標(biāo)軸如何在圖表中插入對象如何創(chuàng)建動態(tài)圖表復(fù)合餅圖等其他圖形的運(yùn)用內(nèi)容三．制作讓領(lǐng)導(dǎo)滿意的圖表內(nèi)容58F11三(1).創(chuàng)建圖表快捷鍵參見文件案例--圖表.xlsF11三(1).創(chuàng)建圖表快捷鍵參見文件案例--圖表.xls59三(1).創(chuàng)建圖表標(biāo)準(zhǔn)四步曲3.設(shè)置圖表選項(xiàng)2.指定數(shù)據(jù)源1.選擇圖表類型4.指定圖表放置位置三(1).創(chuàng)建圖表標(biāo)準(zhǔn)四步曲1.選擇圖表類型60三(2).圖表的八要素圖表標(biāo)題網(wǎng)格線數(shù)據(jù)系列背景區(qū)域圖例坐標(biāo)軸數(shù)據(jù)表數(shù)據(jù)標(biāo)志識別圖表的八要素三(2).圖表的八要素圖表標(biāo)題網(wǎng)格線數(shù)據(jù)系列背景區(qū)域圖例坐61修改圖表的八要素圖表標(biāo)題:命名,設(shè)置圖案,字體和對齊方式坐標(biāo)軸:顯示或隱藏,設(shè)置圖案,刻度,字體,數(shù)字格式和對齊方式網(wǎng)格線:顯示或隱藏,設(shè)置圖案,刻度圖例:顯示或隱藏,設(shè)置圖案,字體和位置數(shù)據(jù)標(biāo)志:顯示或隱藏,設(shè)置圖案,字體,數(shù)字格式和對齊方式數(shù)據(jù)表:顯示或隱藏,設(shè)置圖案,字體背景區(qū)域:設(shè)置圖案數(shù)據(jù)系列:設(shè)置圖案,次序,重疊比例,分類間距和系列線三(2).圖表的八要素修改圖表的八要素圖表標(biāo)題:命名,設(shè)置圖案,62三(3).在圖表中使用次坐標(biāo)軸參見文件案例--圖表.xls三(3).在圖表中使用次坐標(biāo)軸參見文件案例--圖表.xls63(1)選中某一數(shù)據(jù)系列,右鍵進(jìn)入數(shù)據(jù)系列格式->坐標(biāo)軸->次坐標(biāo)軸三(3).在圖表中使用次坐標(biāo)軸參見文件案例--圖表.xls(1)選中某一數(shù)據(jù)系列,右鍵進(jìn)入數(shù)據(jù)系列格式->坐標(biāo)軸-64(2)選中該數(shù)據(jù)系列,右鍵進(jìn)入圖表類型,選擇另一種圖型三(3).在圖表中使用次坐標(biāo)軸(2)選中該數(shù)據(jù)系列,右鍵進(jìn)入圖表類型,選擇另一種圖型65三(3).在圖表中使用次坐標(biāo)軸三(3).在圖表中使用次坐標(biāo)軸66三(4).在圖表中插入對象三(4).在圖表中插入對象67窗體按扭三(5).創(chuàng)建動態(tài)圖表窗體按扭三(5).創(chuàng)建動態(tài)圖表68窗體按鈕：練習(xí)三(5).創(chuàng)建動態(tài)圖表參見文件練習(xí)--動態(tài)圖表.xls窗體按鈕：練習(xí)三(5).創(chuàng)建動態(tài)圖表參見文件練習(xí)--動態(tài)69三(5).創(chuàng)建動態(tài)圖表函數(shù)COLUMN函數(shù)CELL函數(shù)ADDRESS函數(shù)INDIRECT圖表函數(shù)SERIES函數(shù)OFFSET參見文件案例--動態(tài)圖表.xls三(5).創(chuàng)建動態(tài)圖表參見文件案例--動態(tài)圖表.xls70三(5).創(chuàng)建動態(tài)圖表Column返回給定引用的列標(biāo)。語法COLUMN(reference)Reference

為需要得到其列標(biāo)的單元格。如果省略reference，則假定為是對函數(shù)COLUMN所在單元格的引用。三(5).創(chuàng)建動態(tài)圖表Column71三(5).創(chuàng)建動態(tài)圖表Cell返回某一引用區(qū)域的左上角單元格的格式、位置或內(nèi)容等信息。語法CELL(info_type,reference)Info_type

為一個(gè)文本值，指定所需要的單元格信息的類型。Info_type為“row”表示引用中單元格的行號。Reference

表示要獲取其有關(guān)信息的單元格。如果忽略，則在info_type中所指定的信息將返回給最后更改的單元格。三(5).創(chuàng)建動態(tài)圖表Cell72三(5).創(chuàng)建動態(tài)圖表Address按照給定的行號和列標(biāo)，建立文本類型的單元格地址。語法ADDRESS(row_num,column_num,abs_num)Row_num

在單元格引用中使用的行號。Column_num

在單元格引用中使用的列標(biāo)。Abs_num

指定返回的引用類型。Abs_num返回的引用類型:1或省略-絕對引用;2絕對行號，相對列標(biāo);3相對行號，絕對列標(biāo);4相對引用三(5).創(chuàng)建動態(tài)圖表Address73三(5).創(chuàng)建動態(tài)圖表Indirect返回由文本字符串指定的引用。此函數(shù)立即對引用進(jìn)行計(jì)算，并顯示其內(nèi)容。語法INDIRECT(ref_text,a1)Ref_text

為對單元格的引用如果a1為TRUE或省略，ref_text被解釋為A1-樣式的引用。如果a1為FALSE，ref_text被解釋為R1C1-樣式的引用。三(5).創(chuàng)建動態(tài)圖表Indirect74三(5).創(chuàng)建動態(tài)圖表Series該函數(shù)是一個(gè)特殊的函數(shù),不能在工作表中使用.它只在生成Excel圖表時(shí)自動生成,用來定義圖表系列.但可以對它進(jìn)行修改.語法Series(名稱,分類標(biāo)志,值,次序)名稱 可選 出現(xiàn)在圖例中的名稱分類標(biāo)志 可選 出現(xiàn)在分類軸上的標(biāo)志值 必需 Excel將繪制的值次序 必需 系列的繪制次序三(5).創(chuàng)建動態(tài)圖表Series75三(5).創(chuàng)建動態(tài)圖表Offset以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū)域。語法OFFSET(reference,rows,cols,height,width)Reference

作為偏移量參照系的引用區(qū)域。Reference必須為對單元格或相連單元格區(qū)域的引用；否則，函數(shù)OFFSET返回錯(cuò)誤值#VALUE!。三(5).創(chuàng)建動態(tài)圖表Offset76三(5).創(chuàng)建動態(tài)圖表OffsetRows

相對于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。行數(shù)可為正數(shù)（代表在起始引用的下方）或負(fù)數(shù)（代表在起始引用的上方）。Cols

相對于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。列數(shù)可為正數(shù)（代表在起始引用的右邊）或負(fù)數(shù)（代表在起始引用的左邊）。Height

高度，即所要返回的引用區(qū)域的行數(shù)。Height必須為正數(shù)。Width

寬度，即所要返回的引用區(qū)域的列數(shù)。Width必須為正數(shù)。如果省略height或width，則假設(shè)其高度或?qū)挾扰creference相同。三(5).創(chuàng)建動態(tài)圖表Offset77三(6).其他圖表-復(fù)合餅圖參見文件案例--其他圖表.xls三(6).其他圖表-復(fù)合餅圖參見文件案例--其他圖表.78三(6).其他圖表-雷達(dá)圖參見文件案例--其他圖表.xls三(6).其他圖表-雷達(dá)圖參見文件案例--其他圖表.xls79三(6).其他圖表-變化分析圖參見文件案例--其他圖表.xls三(6).其他圖表-變化分析圖參見文件案例--其他圖表.x80三(6).其他圖表-氣泡圖參見文件案例--其他圖表.xls三(6).其他圖表-氣泡圖參見文件案例--其他圖表.xl81四．宏和自定義函數(shù)什么是宏怎樣錄制宏怎樣編寫宏常用宏編寫語句自定義函數(shù)的編寫及實(shí)例內(nèi)容四．宏和自定義函數(shù)內(nèi)容82定義

宏:一組指令,告訴Excel執(zhí)行一個(gè)或多個(gè)操作.兩種創(chuàng)建宏的方法

a.錄制 b.在VBE中新建四(1).什么是宏定義四(1).什么是宏83錄制宏的步驟 a.啟動宏錄制器 b.命名宏,指定快捷鍵,保存位置及描述 c.執(zhí)行所要錄制的操作 d.停止宏錄制器使用宏錄制器的優(yōu)點(diǎn):快速;幫助學(xué)習(xí)使用宏錄制器的缺點(diǎn):某些語句不能通過錄制而必須編寫;常產(chǎn)生多余的代碼.四(2).錄制宏錄制宏的步驟四(2).錄制宏84編寫宏的步驟啟動VBE編輯器選擇:插入->模塊輸入Sub,后面加宏的名稱和()輸入VBA代碼輸入EndSub結(jié)束四(3).編寫宏1/2編寫宏的步驟四(3).編寫宏1/285VBE窗口介紹四(3).編寫宏2/2VBE窗口介紹四(3).編寫宏2/286最常用的宏語句MsgBox“text”If…Then…ElseFor…Next四(4).宏語句參見文件案例--宏.xls最常用的宏語句四(4).宏語句參見文件案例--宏.xls87四(4).宏語句語法If

condition

ThenstatementsEndif或If

condition

Then

statementsElse

elsestatementsEndIf參見文件案例--宏.xls四(4).宏語句語法參見文件案例--宏.xls88四(4).宏語句語法For

counter

=

start

To

end[Step

step]

[statements]

Next[counter]參見文件案例--宏.xls四(4).宏語句語法參見文件案例--宏.xls89定義:由用戶自己編寫是兩種VBA程序中的一種和其他任何內(nèi)置工作表函數(shù)一樣可被公式調(diào)用以Function開始,以EndFunction結(jié)束需指定參數(shù)只執(zhí)行計(jì)算而不執(zhí)行操作四(5).自定義函數(shù)1/2定義:四(5).自定義函數(shù)1/290創(chuàng)建自定義函數(shù)的步驟:打開VBE編輯器選擇:插入->模塊輸入Function,后面加上函數(shù)名,并在括號內(nèi)輸入?yún)?shù)(如果需要)輸入VBA代碼輸入EndFunction結(jié)束四(5).自定義函數(shù)2/2參見文件案例--自定義函數(shù).xls創(chuàng)建自定義函數(shù)的步驟:四(5).自定義函數(shù)2/2參見文件案91五．其他實(shí)用工具及技巧1.自動更正2.按照特定的順序排序3.公式審核及追蹤引用4.三維公式5.合并計(jì)算6.條件格式7.數(shù)據(jù)的有效性8.高級篩選9.數(shù)據(jù)透視表內(nèi)容五．其他實(shí)用工具及技巧內(nèi)容92五(1).自動更正“工具”----“自動更正選項(xiàng)”五(1).自動更正“工具”----“自動更正選項(xiàng)”93五(2).按照特定的順序排序參見文件案例--按照特定的順序排序.xls五(2).按照特定的順序排序參見文件案例--按照特定的順序排94五(3).公式審核及追蹤引用1/3追蹤引用單元格追蹤從屬單元格取消所有追蹤箭頭五(3).公式審核及追蹤引用1/3追蹤引用單元格追蹤從屬單95追蹤引用單元格五(3).公式審核及追蹤引用2/3參見文件案例--函數(shù).xls--SUMIF追蹤引用單元格五(3).公式審核及追蹤引用2/3參見文件案96追蹤從屬單元格五(3).公式審核及追蹤引用3/3參見文件案例--函數(shù).xls--SUMIF追蹤從屬單元格五(3).公式審核及追蹤引用3/3參見文件案97五(4).三維公式參見文件案例--三維公式.xls五(4).三維公式參見文件案例--三維公式.xls98按位置合并:源工作表布局同目標(biāo)工作表完全相同打開目標(biāo)工作表選中將要接受合并數(shù)據(jù)的區(qū)域選擇數(shù)據(jù)->合并計(jì)算,選擇函數(shù)類型用鼠標(biāo)選擇源數(shù)據(jù)區(qū)域單擊“添加“按鈕單擊“確定“按鈕更新:選中合并數(shù)據(jù)區(qū)域選擇數(shù)據(jù)->合并計(jì)算單擊“確定“按鈕五(5).合并計(jì)算1/2參見文件案例--合并計(jì)算函數(shù).xls按位置合并:源工作表布局同目標(biāo)工作表完全相同五(5).合99按類別合并:源工作表布局同目標(biāo)工作表不完全相同打開目標(biāo)工作表選中將要接受合并數(shù)據(jù)的區(qū)域的首個(gè)單元格選擇數(shù)據(jù)->合并計(jì)算,選擇函數(shù)類型選中標(biāo)簽位置->最左列用鼠標(biāo)選擇源數(shù)據(jù)區(qū)域(包括行標(biāo)簽)單擊“添加“按鈕單擊“確定“按鈕更新:選中合并數(shù)據(jù)區(qū)域首個(gè)單元格選擇數(shù)據(jù)->合并計(jì)算單擊“確定“按鈕五(5).合并計(jì)算2/2參見文件案例--合并計(jì)算類別.xls按類別合并:源工作表布局同目標(biāo)工作表不完全相同五(5).100條件格式是一種“暗含”的格式，即只有當(dāng)單元格中的數(shù)值符合某一給定的條件時(shí)，這種格式才顯現(xiàn)出來。具體步驟參見文件設(shè)置單元格條件格式的步驟.doc五(6).條件格式1/2條件格式是一種“暗含”的格式，即只有當(dāng)單元格中的數(shù)值符合某一101最多可設(shè)置三個(gè)條件條件可以是判斷單元格的值…也可以是根據(jù)單元格的公式五(6).條件格式2/2參見文件案例--條件格式.xls最多可設(shè)置三個(gè)條件條件可以是判斷單元格的值…也可以是根據(jù)單1021．設(shè)置五(7).數(shù)據(jù)的有效性參見文件案例—數(shù)據(jù)有效性.xls1．設(shè)置五(7).數(shù)據(jù)的有效性參見文件案例—數(shù)據(jù)有效性.x1032．輸入信息五(7).數(shù)據(jù)的有效性2．輸入信息五(7).數(shù)據(jù)的有效性1043．出錯(cuò)警告五(7).數(shù)據(jù)的有效性3．出錯(cuò)警告五(7).數(shù)據(jù)的有效性105高級篩選是對數(shù)據(jù)列表進(jìn)行操作，數(shù)據(jù)列表一般符合下列原則：首行由標(biāo)簽組成,每一個(gè)標(biāo)簽描述的是下面整列的內(nèi)容.該標(biāo)簽被稱為字段每一個(gè)字段都是唯一的除第一行外，其他的行被稱為記錄不包含空白行或列具體步驟參見文件：高級篩選操作步驟.doc高級篩選的條件一般單獨(dú)放置在數(shù)據(jù)列表之外的某個(gè)地方，寫法如下：至少包含兩行,第一行只包含某些或全部字段名其他行包括條件同一行的條件意味”與”不同行的條件意味”或”條件中可包含=,>,<,>=,<=等比較符條件中也可包含公式或單元格引用五(8).高級篩選1/2高級篩選是對數(shù)據(jù)列表進(jìn)行操作，數(shù)據(jù)列表一般符合下列原則：五(106高級篩選的結(jié)果既可以顯示在原列表位置,也可以拷貝到別的指定位置條件五(8).高級篩選2/2參見文件案例—高級篩選.xls高級篩選的結(jié)果既可以顯示在原列表位置,也可以拷貝到別的指定107入門-創(chuàng)建數(shù)據(jù)透視表的步驟:規(guī)范您的數(shù)據(jù)列表（數(shù)據(jù)庫）明確您的問題/要求使用數(shù)據(jù)透視表向?qū)?chuàng)建數(shù)據(jù)透視表報(bào)告五(9).數(shù)據(jù)透視表1/13入門-創(chuàng)建數(shù)據(jù)透視表的步驟:五(9).數(shù)據(jù)透視表1/1108規(guī)范您的數(shù)據(jù)列表（數(shù)據(jù)庫）:首行由標(biāo)簽組成,每一個(gè)標(biāo)簽描述的是下面整列的內(nèi)容.該標(biāo)簽被稱為字段每一個(gè)字段都是唯一的不包含空白行或列五(9).數(shù)據(jù)透視表2/13規(guī)范您的數(shù)據(jù)列表（數(shù)據(jù)庫）:五(9).數(shù)據(jù)透視表2/13109明確您的問題/要求:每個(gè)銷售員的總訂單額是多少?每個(gè)銷售員一月份的訂單額是多少?每一地區(qū)總訂單額是多少?……五(9).數(shù)據(jù)透視表3/13參見文件案例—數(shù)據(jù)透視表.xls明確您的問題/要求:五(9).數(shù)據(jù)透視表3/13參見文件案110使用數(shù)據(jù)透視表向?qū)?五(9).數(shù)據(jù)透視表4/13參見文件案例—數(shù)據(jù)透視表.xls使用數(shù)據(jù)透視表向?qū)?五(9).數(shù)據(jù)透視表4/13參見文件案111使用數(shù)據(jù)透視表向?qū)?你也可以在第一步直接按“完成”鍵取默認(rèn)值:使用Excel列表或數(shù)據(jù)庫作為數(shù)據(jù)來源創(chuàng)建數(shù)據(jù)透視表報(bào)告(而非數(shù)據(jù)透視圖)使用列表里的所有數(shù)據(jù)新建一個(gè)新的工作表以放置數(shù)據(jù)透視表五(9).數(shù)據(jù)透視表5/13使用數(shù)據(jù)透視表向?qū)?五(9).數(shù)據(jù)透視表5/13112創(chuàng)建數(shù)據(jù)透視表報(bào)告:五(9).數(shù)據(jù)透視表6/13頁字段字段列表列字段行字段數(shù)據(jù)區(qū)域數(shù)據(jù)透視表工具欄參見文件案例—數(shù)據(jù)透視表.xls創(chuàng)建數(shù)據(jù)透視表報(bào)告:五(9).數(shù)據(jù)透視表6/13頁字段字段113修改數(shù)據(jù)透視表的基本技巧:增加字段（拖放）在行字段,列字段,頁字段之間轉(zhuǎn)換(拖,放)合并標(biāo)志（表選項(xiàng)-〉合并標(biāo)志）重命名字段（Justdoit)設(shè)置數(shù)字格式（字段設(shè)置-〉數(shù)字）顯示明細(xì)數(shù)據(jù)（雙擊）創(chuàng)建項(xiàng)組合(數(shù)據(jù)組合)排序更改匯總方式添加新匯總字段增加計(jì)算字段五(9).數(shù)據(jù)透視表7/13參見文件案例—數(shù)據(jù)透視表.xls修改數(shù)據(jù)透視表的基本技巧:五(9).數(shù)據(jù)透視表7/13參見114數(shù)據(jù)組合：五(9).數(shù)據(jù)透視表8/13數(shù)據(jù)組合：五(9).數(shù)據(jù)透視表8/13115排序：五(9).數(shù)據(jù)透視表9/13排序：五(9).數(shù)據(jù)透視表9/13116更改匯總方式：選中任一數(shù)據(jù)區(qū)域單元格-〉“字段設(shè)置”-〉更改匯總函數(shù)五(9).數(shù)據(jù)透視表10/13更改匯總方式：五(9).數(shù)據(jù)透視表10/13117添加新匯總字段：從字段列表中選中你想要匯總的字段（可以重復(fù)）拖入數(shù)據(jù)區(qū)域選中對應(yīng)匯總行的任一數(shù)據(jù)區(qū)域單元格-〉“字段設(shè)置”-〉更改匯總函數(shù)五(9).數(shù)據(jù)透視表11/13添加新匯總字段：五(9).數(shù)據(jù)透視表11/13118增加計(jì)算字段：選中對應(yīng)匯總行的任一數(shù)據(jù)區(qū)域單元格-〉“字段設(shè)置”-〉選項(xiàng)五(9).數(shù)據(jù)透視表12/13增加計(jì)算字段：五(9).數(shù)據(jù)透視表12/13119問題：如何按下列方式顯示？BymonthSubtotalbyregionSubtotalbyMonth五(9).數(shù)據(jù)透視表13/13問題：如何按下列方式顯示？BymonthSubtotal120步驟(按月顯示)：1.將訂單日期拖入行字段五(9).數(shù)據(jù)透視表步驟(按月顯示)：1.將訂單日期拖入行字段五(9).數(shù)據(jù)121步驟(按月顯示)：2.右鍵單擊訂單日期字段，選擇……組合五(9).數(shù)據(jù)透視表步驟(按月顯示)：2.右鍵單擊訂單日期字段，選擇……122步驟(按月顯示)：3.選擇起止日期和步長五(9).數(shù)據(jù)透視表步驟(按月顯示)：3.選擇起止日期和步長五(9).數(shù)據(jù)透123步驟(按月顯示)：4.結(jié)果如下五(9).數(shù)據(jù)透視表步驟(按月顯示)：4.結(jié)果如下五(9).數(shù)據(jù)透視表124步驟(按地區(qū)匯總)：1.右鍵單擊地區(qū)字段，選擇字段設(shè)置五(9).數(shù)據(jù)透視表步驟(按地區(qū)匯總)：1.右鍵單擊地區(qū)字段，選擇字段設(shè)置五(125步驟(按地區(qū)匯總)：2.選擇求和五(9).數(shù)據(jù)透視表步驟(按地區(qū)匯總)：2.選擇求和五(9).數(shù)據(jù)透視表126步驟(按地區(qū)匯總)：3.結(jié)果如下五(9).數(shù)據(jù)透視表步驟(按地區(qū)匯總)：3.結(jié)果如下五(9).數(shù)據(jù)透視表127EXCEL在財(cái)務(wù)銷售及管理工作中的高級運(yùn)用EXCEL在財(cái)務(wù)銷售及管理工作中的高級運(yùn)用128企業(yè)管理軟件(ERP)與EXCEL1、Excel散2、ERP思想

3、ERPEXCEL結(jié)合培訓(xùn)前言企業(yè)管理軟件(ERP)與EXCEL培訓(xùn)前言129讓Excel與眾不同的技巧各類公式和函數(shù)的使用制作讓領(lǐng)導(dǎo)滿意的圖表宏和自定義函數(shù)其他實(shí)用工具及技巧內(nèi)容讓Excel與眾不同的技巧內(nèi)容130一．讓Excel與眾不同的技巧高效（工具欄、快捷鍵）安全（單元格、工作表和工作簿保護(hù)大全）便捷（引用、鏈接、超級鏈接和高級打印格式）美觀（專業(yè)、規(guī)范）內(nèi)容一．讓Excel與眾不同的技巧內(nèi)容131第一步：打開自定義開關(guān)（三種方法）右鍵菜單或工具欄–>自定義工具->自定義視圖->工具欄->自定義一(1).高效優(yōu)化自己的工具欄和菜單第一步：打開自定義開關(guān)（三種方法）一(1).高效132一(1).高效優(yōu)化自己的工具欄和菜單第二步：選擇命令--類別，通過拖動可實(shí)現(xiàn)：在現(xiàn)有的工具欄內(nèi)添加按鈕在現(xiàn)有的工具欄內(nèi)刪除按鈕在現(xiàn)有的菜單內(nèi)添加命令在現(xiàn)有的菜單內(nèi)刪除命令創(chuàng)建自己的工具欄創(chuàng)建自己的菜單一(1).高效優(yōu)化自己的工具欄和菜單第二步：選擇命133一(1).高效優(yōu)化自己的工具欄和菜單練習(xí)：把下列工具按鈕拖入工具欄內(nèi)一(1).高效優(yōu)化自己的工具欄和菜單練習(xí)：把下列工具134一(1).高效熟練使用常用鍵盤快捷鍵Excel常用鍵盤快捷鍵：Alt+Enter在同一單元格中新增一行F4重復(fù)上一個(gè)操作Alt+F11顯示VB編輯器Ctrl+空格選中整列Shift+空格選中整行Ctrl+C將所選項(xiàng)復(fù)制到剪貼板Ctrl+V從剪貼板中復(fù)制內(nèi)容到選中單元格

Ctrl+Z快速撤消操作Ctrl+Y快速恢復(fù)操作詳細(xì)鍵盤快捷鍵見文件：<<Excel鍵盤快捷鍵大全>>一(1).高效熟練使用常用鍵盤快捷鍵Excel常用135一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格公式的步驟：第一步：設(shè)置單元格格式（格式-單元格-保護(hù)）參見文件案例--保護(hù).xls一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格136一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格公式的步驟：第二步：保護(hù)工作表（工具-保護(hù)-保護(hù)工作表）一(2).安全單元格保護(hù)1.鎖定單元格和隱藏單元格137一(2).安全單元格保護(hù)2.隱藏整行或整列的步驟：（方法一）選中某行或某列單擊鼠標(biāo)右鍵選擇：隱藏一(2).安全單元格保護(hù)2.隱藏整行或整列的步驟：138一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：（方法二：分級顯示）選中需要隱藏幾行或幾列數(shù)據(jù)-組及分級顯示-組合一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：139一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：（方法二：分級顯示）單擊或可在顯示和隱藏之間進(jìn)行切換一(2).安全單元格保護(hù)3.隱藏整行或整列的步驟：140一(2).安全單元格保護(hù)4.隱藏部分單元格內(nèi)容的技巧（將文字顏色設(shè)為和背景色一樣）一(2).安全單元格保護(hù)4.隱藏部分單元格內(nèi)容的技141一(2).安全單元格保護(hù)5.隱藏部分單元格內(nèi)容的技巧（將單元格式修改為自定義；；；）一(2).安全單元格保護(hù)5.隱藏部分單元格內(nèi)容的技142一(2).安全工作表保護(hù)1.隱藏工作表步驟：（方法一）格式-工作表-隱藏一(2).安全工作表保護(hù)1.隱藏工作表步驟：（方法143一(2).安全工作表保護(hù)2.隱藏工作表步驟：（方法二）切換到VB編輯器(Alt+F11)選中需隱藏的工作表將Visible屬性改為２一(2).安全工作表保護(hù)2.隱藏工作表步驟：（方法144一(2).安全工作簿保護(hù)1.設(shè)置工作簿密碼的步驟：(Excel2000)文件-另存為-工具-常規(guī)選項(xiàng)設(shè)置打開權(quán)限密碼或修改權(quán)限密碼一(2).安全工作簿保護(hù)1.設(shè)置工作簿密碼的步驟：145一(2).安全工作簿保護(hù)2.設(shè)置工作簿密碼的步驟:(Excel2003)工具-選項(xiàng)-安全性設(shè)置打開權(quán)限密碼或修改權(quán)限密碼一(2).安全工作簿保護(hù)2.設(shè)置工作簿密碼的步驟:146一(3).便捷引用、鏈接和超級鏈接引用同一工作表中其他單元格，或同一工作簿中其他工作表中的單元格稱為“引用”。引用其他工作簿中的單元格稱“外部引用”，或“鏈接”。超鏈接則是完全不同的概念。它類似于Web瀏覽器中超鏈接：單擊一個(gè)超鏈接就會打開鏈接的目標(biāo)，而不會把任何值傳送到鏈接的目標(biāo)。一(3).便捷引用、鏈接和超級鏈接引用同一工作表中其147幾乎所有的公式都有單元格或區(qū)域引用.有三種類型的引用:

相對引用:復(fù)制公式時(shí),引用單元格的行或列會改變

絕對引用:復(fù)制公式時(shí),引用單元格的行和列都不會改變

混合引用:行或列中,有一個(gè)是相對引用,另一個(gè)是絕對引用在單元格引用的列字母或行號前加入美元符號$便可創(chuàng)建絕對引用或混合引用,可用F4鍵在四種引用類型中循環(huán)選擇.選用適當(dāng)?shù)囊妙愋涂梢蕴岣咻斎?拷貝公式的效率.引用一(3).便捷幾乎所有的公式都有單元格或區(qū)域引用.引用一(3).便148一個(gè)簡單的例子(相對引用):一(3).便捷參見文件案例--引用.xls一個(gè)簡單的例子(相對引用):一(3).便捷參見文件149復(fù)雜一點(diǎn)的例子(混合引用):一(3).便捷參見文件案例--引用.xls復(fù)雜一點(diǎn)的例子(混合引用):一(3).便捷參見文件案150練習(xí)：在D4單元格中輸入公式，然后拷貝到D4:I13區(qū)域中所有的單元格一(3).便捷參見文件案例--引用.xls練習(xí)：在D4單元格中輸入公式，然后拷貝到D4:I13區(qū)域中所151一(3).便捷鏈接當(dāng)打開含有鏈接的Excel文件時(shí)，通常Excel會提示您需不需要更新鏈接，最好選擇不更新。當(dāng)確實(shí)需要更新鏈接的內(nèi)容時(shí)，可手動更新：編輯-〉鏈接（如下頁所示）我們甚至可以允許鏈接的文件更名一(3).便捷鏈接當(dāng)打開含有鏈接的Excel文件152一(3).便捷鏈接參見文件案例--鏈接.xls一(3).便捷鏈接參見文件案例--鏈接.xls153一(3).便捷超級鏈接在Excel中可以建立下列超鏈接：鏈接到Web站點(diǎn)鏈接到其他已有文檔鏈接到一個(gè)還不存在的文檔（Excel將在您單擊超鏈接時(shí)創(chuàng)建文檔）鏈接到本文檔其他的地方鏈接到一個(gè)電子郵件地址（單擊超鏈接時(shí)，Excel會啟動默認(rèn)的郵件程序）一(3).便捷超級鏈接在Excel中可以建立下列154一(3).便捷引用、鏈接和超級鏈接鏈接引用超鏈接參見文件案例--鏈接.xls一(3).便捷引用、鏈接和超級鏈接鏈接引用超鏈接參見155打印格式的設(shè)定盡量把內(nèi)容打印在一頁內(nèi)如果內(nèi)容較多需分頁打印,則應(yīng)為每頁設(shè)置好行列標(biāo)題打印的文件應(yīng)有公司的Logo,文件名,頁碼,日期等如果需要,可打印行號列標(biāo)用黑白打印機(jī)打印時(shí),最好選用單色打印一個(gè)文件的多張表可同時(shí)打印文件路徑一(3).便捷打印格式的設(shè)定一(3).便捷156可以考慮:不顯示網(wǎng)格線選用您獨(dú)特的字體,如Tahoma,Verdana(公司另有規(guī)定除外)表頭,標(biāo)題和正文應(yīng)選用不同的字號和圖案以示區(qū)別規(guī)范使用數(shù)字格式?jīng)]有必要把所有的數(shù)據(jù)都放在一張表內(nèi)盡量縱向安排數(shù)據(jù),以方便瀏覽設(shè)置合適的顯示比例,以盡量在一屏顯示全部數(shù)據(jù)(Ctrl+鼠標(biāo)滾動輪)固定窗格為每一張工作表命名一個(gè)有意義的名稱刪除多余的工作表一(4).美觀參見文件案例--美觀可以考慮:一(4).美觀參見文件案例--美觀157內(nèi)容二．各類公式和函數(shù)的使用公式的定義及使用公式的基本技巧不使用公式就知道某些計(jì)算結(jié)果的技巧計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧如何定義名稱和在公式中使用名稱幾個(gè)常用函數(shù)的定義及實(shí)例數(shù)據(jù)庫函數(shù)六種公式錯(cuò)誤值的理解及解決內(nèi)容二．各類公式和函數(shù)的使用158定義所有的公式都以等號（=）開始公式中可使用的運(yùn)算符有：加（+）、減（-）、乘（*）、除（/）、乘方（^)、括號（()），運(yùn)算規(guī)則和數(shù)學(xué)中的一樣二(1).公式的定義及基本技巧定義二(1).公式的定義及基本技巧159基本技巧在公式中盡量使用引用而少輸入數(shù)值可用鼠標(biāo)點(diǎn)擊單元格來輸入引用，而不必手工輸入適當(dāng)使用括號或空格以易于閱讀和理解當(dāng)公式較長時(shí)，可用Alt+Enter強(qiáng)行換行，以方便閱讀按F2或雙擊單元格可進(jìn)入公式編輯狀態(tài)可按F9進(jìn)行公式的重新計(jì)算既可以復(fù)制公式，也可以復(fù)制公式的計(jì)算值二(1).公式的定義及基本技巧基本技巧二(1).公式的定義及基本技巧160 在Excel的工作表中，選中某些連續(xù)或不連續(xù)的單元格，那么這些單元格的一些計(jì)算（求和、平均值、最大或最小等）會自動顯示在狀態(tài)欄，而不用輸入公式計(jì)算。如下圖：二(2).不使用公式就知道計(jì)算結(jié)果的技巧 在Excel的工作表中，選中某些連續(xù)或不連續(xù)的單元格，161 如果只想知道某個(gè)復(fù)雜公式中某部分的計(jì)算結(jié)果，可選中該部分然后按F9，該部分公式就會顯示為計(jì)算值。如下圖：二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--薪酬.xls 如果只想知道某個(gè)復(fù)雜公式中某部分的計(jì)算結(jié)果，可選中該部分然162注意：該部分應(yīng)該是完整的能計(jì)算出結(jié)果的公式檢查完后可按ESC鍵退出，而不能按回車鍵，否則選中的部分就會被計(jì)算結(jié)果代替二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--薪酬.xls注意：二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案163 在Excel2003中可用公式求值工具一步步測算每一部分的計(jì)算結(jié)果。如下圖：二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧 在Excel2003中可用公式求值工具一步步測算每一部164二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--函數(shù)應(yīng)用.xls二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--165二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--函數(shù)應(yīng)用.xls二(3).計(jì)算復(fù)雜公式中某些中間結(jié)果的技巧參見文件案例--166一個(gè)例子二(4).定義和使用名稱1/4參見文件案例--名稱.xls一個(gè)例子二(4).定義和使用名稱1/4參見文件案例--名稱167定義名稱 Ctrl+F3二(4).定義和使用名稱2/4定義名稱 Ctrl+F3二(4).定義和使用名稱2/4168在公式中插入名稱F3二(4).定義和使用名稱3/4在公式中插入名稱F3二(4).定義和使用名稱3/4169創(chuàng)建名稱列表F3二(4).定義和使用名稱4/4創(chuàng)建名稱列表F3二(4).定義和使用名稱4/4170IFANDCOUNTACOUNTIFSUMIFSUMPRODUCTVLOOKUPHLOOKUPROUND

二(5).熟練使用常用函數(shù)1/9參見文件案例--函數(shù).xlsIF二(5).熟練使用常用函數(shù)1/9參見文件案例--函數(shù).171IF

二(5).熟練使用常用函數(shù)2/9參見文件案例--函數(shù).xlsIF 二(5).熟練使用常用函數(shù)2/9參見文件案例--函數(shù)172AND二(5).熟練使用常用函數(shù)3/9參見文件案例--函數(shù).xlsAND二(5).熟練使用常用函數(shù)3/9參見文件案例--函數(shù)173COUNTA二(5).熟練使用常用函數(shù)4/9參見文件案例--函數(shù).xlsCOUNTA二(5).熟練使用常用函數(shù)4/9參見文件案例-174COUNTIF二(5).熟練使用常用函數(shù)5/9參見文件案例--函數(shù).xls/練習(xí)--統(tǒng)計(jì)不及格人數(shù).xlsCOUNTIF二(5).熟練使用常用函數(shù)5/9參見文件案例175SUMIF

二(5).熟練使用常用函數(shù)6/9參見文件案例--函數(shù).xlsSUMIF 二(5).熟練使用常用函數(shù)6/9參見文件案例-176SUMPRODUCT

二(5).熟練使用常用函數(shù)7/9參見文件案例--函數(shù).xlsSUMPRODUCT 二(5).熟練使用常用函數(shù)7/9參見177VLOOKUP二(5).熟練使用常用函數(shù)8/9參見文件案例--函數(shù).xls//所得稅模型.xlsVLOOKUP二(5).熟練使用常用函數(shù)8/9參見文件案例178ROUND二(5).熟練使用常用函數(shù)9/9參見文件案例--函數(shù).xlsROUND二(5).熟練使用常用函數(shù)9/9參見文件案例--179什么是數(shù)據(jù)庫?數(shù)據(jù)庫是一個(gè)內(nèi)涵比較廣泛的單詞,在Excel中我們通常指數(shù)據(jù)列表Excel中的數(shù)據(jù)列表通常滿足下列條件:首行由標(biāo)簽組成,每一個(gè)標(biāo)簽描述的是下面整列的內(nèi)容.該標(biāo)簽被稱為字段每一個(gè)字段都是唯一的除第一行外，其他的行被稱為記錄不包含空白行或列見下頁示例二(6).數(shù)據(jù)庫及其函數(shù)1/4什么是數(shù)據(jù)庫?二(6).數(shù)據(jù)庫及其函數(shù)1/4180二(6).數(shù)據(jù)庫及其函數(shù)2/4參見文件案例--函數(shù).xls二(6).數(shù)據(jù)庫及其函數(shù)2/4參見文件案例--函數(shù).xls181數(shù)據(jù)庫函數(shù)二(6).數(shù)據(jù)庫及其函數(shù)3/4參見文件案例--函數(shù).xls數(shù)據(jù)庫函數(shù)二(6).數(shù)據(jù)庫及其函數(shù)3/4參見文件案例--函182數(shù)據(jù)庫函數(shù)中條件的寫法:可以放在數(shù)據(jù)列表之外的工作表的任意位置,但通常在數(shù)據(jù)列表的上面或下面至少包含兩行,第一行只包含某些或全部字段名其他行包括條件同一行的條件意味”與”不同行的條件意味”或”條件中可包含=,>,<,>=,<=等比較符條件中也可包含公式或單元格引用二(6).數(shù)據(jù)庫及其函數(shù)4/4參見文件案例--函數(shù).xls數(shù)據(jù)庫函數(shù)中條件的寫法:二(6).數(shù)據(jù)庫及其函數(shù)4/4參見183六種公式錯(cuò)誤值:二(7).公式錯(cuò)誤值的理解參見文件案例--錯(cuò)誤值.xls六種公式錯(cuò)誤值:二(7).公式錯(cuò)誤值的理解參見文件案例--184三．制作讓領(lǐng)導(dǎo)滿意的圖表創(chuàng)建圖表快捷鍵和創(chuàng)建圖表標(biāo)準(zhǔn)四步曲識別、修改及美化圖表的八要素如何在圖表中使用次坐標(biāo)軸如何在圖表中插入對象如何創(chuàng)建動態(tài)圖表復(fù)合餅圖等其他圖形的運(yùn)用內(nèi)容三．制作讓領(lǐng)導(dǎo)滿意的圖表內(nèi)容185F11三(1).創(chuàng)建圖表快捷鍵參見文件案例--圖表.xlsF11三(1).創(chuàng)建圖表快捷鍵參見文件案例--圖表.xls186三(1).創(chuàng)建圖表標(biāo)準(zhǔn)四步曲3.設(shè)置圖表選項(xiàng)2.指定數(shù)據(jù)源1.選擇圖表類型4.指定圖表放置位置三(1).創(chuàng)建圖表標(biāo)準(zhǔn)四步曲1.選擇圖表類型187三(2).圖表的八要素圖表標(biāo)題網(wǎng)格線數(shù)據(jù)系列背景區(qū)域圖例坐標(biāo)軸數(shù)據(jù)表數(shù)據(jù)標(biāo)志識別圖表的八要素三(2).圖表的八要素圖表標(biāo)題網(wǎng)格線數(shù)據(jù)系列背景區(qū)域圖例坐188修改圖表的八要素圖表標(biāo)題:命名,設(shè)置圖案,字體和對齊方式坐標(biāo)軸:顯示或隱藏,設(shè)置圖案,刻度,字體,數(shù)字格式和對齊方式網(wǎng)格線:顯示或隱藏,設(shè)置圖案,刻度圖例:顯示或隱藏,設(shè)置圖案,字體和位置數(shù)據(jù)標(biāo)志:顯示或隱藏,設(shè)置圖案,字體,數(shù)字格式和對齊方式數(shù)據(jù)表:顯示或隱藏,設(shè)置圖案,字體背景區(qū)域:設(shè)置圖案數(shù)據(jù)系列:設(shè)置圖案,次序,重疊比例,分類間距和系列線三(2).圖表的八要素修改圖表的八要素圖表標(biāo)題:命名,設(shè)置圖案,189三(3).在圖表中使用次坐標(biāo)軸參見文件案例--圖表.xls三(3).在圖表中使用次坐標(biāo)軸參見文件案例--圖表.xls190(1)選中某一數(shù)據(jù)系列,右鍵進(jìn)入數(shù)據(jù)系列格式->坐標(biāo)軸->次坐標(biāo)軸三(3).在圖表中使用次坐標(biāo)軸參見文件案例--圖表.xls(1)選中某一數(shù)據(jù)系列,右鍵進(jìn)入數(shù)據(jù)系列格式->坐標(biāo)軸-191(2)選中該數(shù)據(jù)系列,右鍵進(jìn)入圖表類型,選擇另一種圖型三(3).在圖表中使用次坐標(biāo)軸(2)選中該數(shù)據(jù)系列,右鍵進(jìn)入圖表類型,選擇另一種圖型192三(3).在圖表中使用次坐標(biāo)軸三(3).在圖表中使用次坐標(biāo)軸193三(4).在圖表中插入對象三(4).在圖表中插入對象194窗體按扭三(5).創(chuàng)建動態(tài)圖表窗體按扭三(5).創(chuàng)建動態(tài)圖表195窗體按鈕：練習(xí)三(5).創(chuàng)建動態(tài)圖表參見文件練習(xí)--動態(tài)圖表.xls窗體按鈕：練習(xí)三(5).創(chuàng)建動態(tài)圖表參見文件練習(xí)--動態(tài)196三(5).創(chuàng)建動態(tài)圖表函數(shù)COLUMN函數(shù)CELL函數(shù)ADDRESS函數(shù)INDIRECT圖表函數(shù)SERIES函數(shù)OFFSET參見文件案例--動態(tài)圖表.xls三(5).創(chuàng)建動態(tài)圖表參見文件案例--動態(tài)圖表.xls197三(5).創(chuàng)建動態(tài)圖表Column返回給定引用的列標(biāo)。語法COLUMN(reference)Reference

為需要得到其列標(biāo)的單元格。如果省略reference，則假定為是對函數(shù)COLUMN所在單元格的引用。三(5).創(chuàng)建動態(tài)圖表Column198三(5).創(chuàng)建動態(tài)圖表Cell返回某一引用區(qū)域的左上角單元格的格式、位置或內(nèi)容等信息。語法CELL(info_type,reference)Info_type

為一個(gè)文本值，指定所需要的單元格信息的類型。Info_type為“row”表示引用中單元格的行號。Reference

表示要獲取其有關(guān)信息的單元格。如果忽略，則在info_type中所指定的信息將返回給最后更改的單元格。三(5).創(chuàng)建動態(tài)圖表Cell199三(5).創(chuàng)建動態(tài)圖表Address按照給定的行號和列標(biāo)，建立文本類型的單元格地址。語法ADDRESS(row_num,column_num,abs_num)Row_num

在單元格引用中使用的行號。Column_num

在單元格引用中使用的列標(biāo)。Abs_num

指定返回的引用類型。Abs_num返回的引用類型:1或省略-絕對引用;2絕對行號，相對列標(biāo);3相對行號，絕對列標(biāo);4相對引用三(5).創(chuàng)建動態(tài)圖表Address200三(5).創(chuàng)建動態(tài)圖表Indirect返回由文本字符串指定的引用。此函數(shù)立即對引用進(jìn)行計(jì)算，并顯示其內(nèi)容。語法INDIRECT(ref_text,a1)Ref_text

為對單元格的引用如果a1為TRUE或省略，ref_text被解釋為A1-樣式的引用。如果a1為FALSE，ref_text被解釋為R1C1-樣式的引用。三(5).創(chuàng)建動態(tài)圖表Indirect201三(5).創(chuàng)建動態(tài)圖表Series該函數(shù)是一個(gè)特殊的函數(shù),不能在工作表中使用.它只在生成Excel圖表時(shí)自動生成,用來定義圖表系列.但可以對它進(jìn)行修改.語法Series(名稱,分類標(biāo)志,值,次序)名稱 可選 出現(xiàn)在圖例中的名稱分類標(biāo)志 可選 出現(xiàn)在分類軸上的標(biāo)志值 必需 Excel將繪制的值次序 必需 系列的繪制次序三(5).創(chuàng)建動態(tài)圖表Series202三(5).創(chuàng)建動態(tài)圖表Offset以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個(gè)單元格或單元格區(qū)域。語法OFFSET(reference,rows,cols,height,width)Reference

作為偏移量參照系的引用區(qū)域。Reference必須為對單元格或相連單元格區(qū)域的引用；否則，函數(shù)OFFSET返回錯(cuò)誤值#VALUE!。三(5).創(chuàng)建動態(tài)圖表Offset203三(5).創(chuàng)建動態(tài)圖表OffsetRows

相對于偏移量參照系的左上角單元格，上（下）偏移的行數(shù)。行數(shù)可為正數(shù)（代表在起始引用的下方）或負(fù)數(shù)（代表在起始引用的上方）。Cols

相對于偏移量參照系的左上角單元格，左（右）偏移的列數(shù)。列數(shù)可為正數(shù)（代表在起始引用的右邊）或負(fù)數(shù)（代表在起始引用的左邊）。Height

高度，即所要返回的引用區(qū)域的行數(shù)。Height必須為正數(shù)。Width

寬度，即所要返回的引用區(qū)域的列數(shù)。Width必須為正數(shù)。如果省略height或width，則假設(shè)其高度或?qū)挾扰creference相同。三(5).創(chuàng)建動態(tài)圖表Offset204三(6).其他圖表-復(fù)合餅圖參見文件案例--其他圖表.xls三(6).其他圖表-復(fù)合餅圖參見文件案例--其他圖表.205三(6).其他圖表-雷達(dá)圖參見文件案例--其他圖表.xls三(6).其他圖表-雷達(dá)圖參見文件案例--其他圖表.xls206三(6).其他圖表-變化分析圖參見文件案例--其他圖表.xls三(6).其他圖表-變化分析圖參見文件案例--其他圖表.x207三(6).其他圖表-氣泡圖參見文件案例--其他圖表.xls三(6).其他圖表-氣泡圖參見文件案例--其他圖表.xl208四．宏和自定義函數(shù)什么是宏怎樣錄制宏怎樣編寫宏常用宏編寫語句自定義函數(shù)的編寫及實(shí)例內(nèi)容四．宏和自定義函數(shù)內(nèi)容209定義

宏:一組指令,告訴Excel執(zhí)行一個(gè)或多個(gè)操作.兩種創(chuàng)建宏的方法

a.錄制 b.在VBE中新建四(1).什么是宏定義四(1).什么是宏210錄制宏的步驟 a.啟動宏錄制器 b.命名宏,指定快捷鍵,保存位置及描述 c.執(zhí)行所要錄制的操作 d.停止宏錄制器使用宏錄制器的優(yōu)點(diǎn):快速;幫助學(xué)習(xí)使用宏錄制器的缺點(diǎn):某些語句不能通過錄制而必須編寫;常產(chǎn)生多余的代碼.四(2).錄制宏錄制宏的步驟四(2).錄制宏211編寫宏的步驟啟動VBE編輯器選擇:插入->模塊輸入Sub,后面加宏的名稱和()輸入VBA代碼輸入EndSub結(jié)束四(3).編寫宏1/2編寫宏的步驟四(3).編寫宏1/2212VBE窗口介紹四(3).編寫宏2/2VBE窗口介紹四(3).編寫宏2/2213最常用的宏語句MsgBox“text”If…Then…ElseFor…Next四(4).宏語句參見文件案例--宏.xls最常用的宏語句四(4).宏語句參見文件案例--宏.xls214四(4).宏語句語法If

condition

ThenstatementsEndif或If

condition

Then

statementsElse

elsestatementsEndIf參見文件案例--宏.xls四(4).宏語句語法參見文件案例--宏.xls215四(4).宏語句語法For

counter

=

start

To

end[Step

step]

[statements]

Next[counter]參見文件案例--宏.xls四(4).宏語句語法參見文件案例--宏.xls216定義:由用戶自己編寫是兩種VBA程序中的一種和其他任何內(nèi)置工作表函數(shù)一樣可被公式調(diào)用以Function開始,以EndFunction結(jié)束需指定參數(shù)只執(zhí)行計(jì)算而不執(zhí)行操作四(5).自定義函數(shù)1/2定義:四(5).自定義函數(shù)1/2217創(chuàng)建自定義函數(shù)的步驟:打開VBE編輯器選擇:插入->模塊輸入Function,后面加上函數(shù)名,并在括號內(nèi)輸入?yún)?shù)(如果需要)輸入VBA代碼輸入EndFunction結(jié)束四(5).自定義函數(shù)2/2參見文件案例--自定義函數(shù).xls創(chuàng)建自定義函數(shù)的步驟:四(5).自定義函數(shù)2/2參見文件案218五．其他實(shí)用工具及技巧1.自動更正2.按照特定的順序排序3.公式審核及追蹤引用4.三維公式5.合并計(jì)算6.條件格式7.數(shù)據(jù)的有效性8.高級篩選9.數(shù)據(jù)透視表內(nèi)容五．其他實(shí)用工具及技巧內(nèi)容219五(1).自動更正“工具”----“自動更正選項(xiàng)”五(1).自動更正“工具”----“自動更正選項(xiàng)”220五(2).按照特定的順序排序參見文件案例--按照特定的順序排序.xls五(2).按照特定的順序排序參見文件案例--按照特定的順序排221五(3).公式審核及追蹤引用1/3追蹤引用單元格追蹤從屬單元格取消所有追蹤箭頭五(3).公式審核及追蹤引用1/3追蹤引用單元格追蹤從屬單222追蹤引用單元格五(3).公式審核及追蹤引用2/3參見文件案例--函數(shù).xls--SUMIF追蹤引用單元格五(3).公式審核及追蹤引用2/3參見文件案223追蹤從屬單元格五(3).公式審核及追蹤引用3/3參見文件案例--函數(shù).xls--SUMIF追蹤從屬單元格五(3).公式審核及追蹤引用3/3參見文件案224五(4).三維公式參見文件案例--三維公式.xls五(4).三維公式參見文件案例--三維公式.xls225按位置合并:源工作表布局同目標(biāo)工作表完全相同打開目標(biāo)工作表選中將要接受合并數(shù)據(jù)的區(qū)域選擇數(shù)據(jù)->合并計(jì)算,選擇函數(shù)類型用鼠標(biāo)選擇源數(shù)據(jù)區(qū)域單擊“添加“按鈕單擊“確定“按鈕更新:選中合并數(shù)據(jù)區(qū)域選擇數(shù)據(jù)->合并計(jì)算單擊“確定“按鈕五(5).合并計(jì)算1/2參見文件案例--合并計(jì)算函數(shù).xls按位置合并:源工作表布局同目標(biāo)工作表完全相同五(5).合226按類別合并:源工作表布局同目標(biāo)工作表不完全相同打開目標(biāo)工作表選中將要接受合并數(shù)據(jù)的區(qū)域的首個(gè)單元格選擇