(完整版)EXCEL高級(jí)應(yīng)用(經(jīng)典)

1、數(shù)據(jù)處理與分析平臺(tái)數(shù)據(jù)處理與分析平臺(tái)由淺入深 循序漸進(jìn)第第1章章 導(dǎo)言導(dǎo)言5 5個(gè)層次個(gè)層次新手：新手：基本操作方法和常用功能：輸入數(shù)據(jù)、查找替基本操作方法和常用功能：輸入數(shù)據(jù)、查找替換、單元格格式、排序、匯總、篩選、保存等換、單元格格式、排序、匯總、篩選、保存等初級(jí)用戶：初級(jí)用戶：建立表格、圖表化建立表格、圖表化中級(jí)用戶：中級(jí)用戶：理解并熟練各個(gè)菜單命令、熟練使用數(shù)據(jù)理解并熟練各個(gè)菜單命令、熟練使用數(shù)據(jù)透視表、掌握透視表、掌握2020個(gè)函數(shù)（含個(gè)函數(shù)（含sumsum、if if、vlookupvlookup、indexindex、matchmatch、offsetoffset、texttex

2、t）與函數(shù)的嵌套、宏；）與函數(shù)的嵌套、宏；高級(jí)用戶：高級(jí)用戶：熟練運(yùn)用數(shù)組公式、使用熟練運(yùn)用數(shù)組公式、使用vbavba編寫不太復(fù)雜編寫不太復(fù)雜的自定義函數(shù)或過程；的自定義函數(shù)或過程；專家：專家：高超的技術(shù)并擁有豐富的行業(yè)知識(shí)和經(jīng)驗(yàn)，屬高超的技術(shù)并擁有豐富的行業(yè)知識(shí)和經(jīng)驗(yàn)，屬于于excelhomeexcelhome網(wǎng)站版主或高級(jí)會(huì)員網(wǎng)站版主或高級(jí)會(huì)員你屬于哪一層次你屬于哪一層次?目前目前excelexcel使用狀況使用狀況 使用了使用了20%20%，常用的只有，常用的只有5%5%，原因：，原因： 根本不知道還有其他功能根本不知道還有其他功能 知道功能但不知道如何使用知道功能但不知道如何使用 暫時(shí)

3、使用不上暫時(shí)使用不上, ,不去了解不去了解為什么學(xué)習(xí)為什么學(xué)習(xí): : 信息時(shí)代，數(shù)據(jù)量大，你是不是每天還在編信息時(shí)代，數(shù)據(jù)量大，你是不是每天還在編制復(fù)雜的公式，還在一遍又一遍的重復(fù)著手制復(fù)雜的公式，還在一遍又一遍的重復(fù)著手工輸入，生怕有一個(gè)數(shù)據(jù)弄錯(cuò)？累吧？煩吧工輸入，生怕有一個(gè)數(shù)據(jù)弄錯(cuò)？累吧？煩吧？想解脫嗎？想解脫嗎？ excel強(qiáng)大的數(shù)據(jù)處理功能已經(jīng)征服了每一個(gè)強(qiáng)大的數(shù)據(jù)處理功能已經(jīng)征服了每一個(gè)excel使用者使用者人力資源管理人力資源管理_應(yīng)用實(shí)例應(yīng)用實(shí)例 動(dòng)態(tài)了解公司員工的流入和流出變化情況；動(dòng)態(tài)了解公司員工的流入和流出變化情況； 分析員工今年薪酬變化，便于為明年的薪酬控制提供依據(jù)分析員

4、工今年薪酬變化，便于為明年的薪酬控制提供依據(jù) 每天、每月都要計(jì)算員工的考勤，計(jì)算員工的工資和獎(jiǎng)金每天、每月都要計(jì)算員工的考勤，計(jì)算員工的工資和獎(jiǎng)金，制作工資條，把工資準(zhǔn)確無誤地發(fā)放到每個(gè)員工賬戶，制作工資條，把工資準(zhǔn)確無誤地發(fā)放到每個(gè)員工賬戶，并及時(shí)通知每個(gè)員工；并及時(shí)通知每個(gè)員工； 每年都要把全公司上百人甚至上千人的工資進(jìn)行匯總，制每年都要把全公司上百人甚至上千人的工資進(jìn)行匯總，制作五險(xiǎn)一金匯總表，制作個(gè)稅代扣代繳表作五險(xiǎn)一金匯總表，制作個(gè)稅代扣代繳表 員工的生日、合同、退休日期快要到了，如何才能提前提員工的生日、合同、退休日期快要到了，如何才能提前提醒，以免到時(shí)候手忙腳亂；新員工的試用期快

5、要到了，如醒，以免到時(shí)候手忙腳亂；新員工的試用期快要到了，如何及時(shí)提醒聘用者簽訂正式勞動(dòng)合同何及時(shí)提醒聘用者簽訂正式勞動(dòng)合同 如何評(píng)價(jià)每個(gè)業(yè)務(wù)人員的銷售業(yè)績，并根據(jù)業(yè)績計(jì)算薪酬如何評(píng)價(jià)每個(gè)業(yè)務(wù)人員的銷售業(yè)績，并根據(jù)業(yè)績計(jì)算薪酬主講： 資深excel實(shí)戰(zhàn)專家韓小良 1980元/人/兩天 金融財(cái)務(wù)建模金融財(cái)務(wù)建模_應(yīng)用實(shí)例應(yīng)用實(shí)例 投資組合收益率和方差計(jì)算及其投資組合收益率和方差計(jì)算及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) 投資組合有效邊界模型及其投資組合有效邊界模型及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) 投資組合風(fēng)險(xiǎn)優(yōu)化決策模型及其投資組合風(fēng)險(xiǎn)優(yōu)化決策模型及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) 投資組合風(fēng)險(xiǎn)價(jià)值模型及其投資組合風(fēng)險(xiǎn)價(jià)值模型

6、及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) 資本資產(chǎn)定價(jià)模型的建立及其資本資產(chǎn)定價(jià)模型的建立及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) black-scholesblack-scholes期權(quán)定價(jià)模型及其期權(quán)定價(jià)模型及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) 二叉樹（二項(xiàng)式）期權(quán)定價(jià)模型及其二叉樹（二項(xiàng)式）期權(quán)定價(jià)模型及其vbavba實(shí)現(xiàn)實(shí)現(xiàn) 期貨套期保值計(jì)算的期貨套期保值計(jì)算的vbavba實(shí)現(xiàn)實(shí)現(xiàn) 投資項(xiàng)目決策與理財(cái)模型的建立及其投資項(xiàng)目決策與理財(cái)模型的建立及其vbavba實(shí)現(xiàn)實(shí)現(xiàn)參考書:金融財(cái)務(wù)建模與計(jì)算基于vba與matlab實(shí)現(xiàn)作者： 朱順泉 編著、出 版 社： 電子工業(yè)出版其他應(yīng)用其他應(yīng)用 高效數(shù)據(jù)處理分析高效數(shù)據(jù)處理分析 高效財(cái)務(wù)管理

7、高效財(cái)務(wù)管理 企業(yè)管理中的高效運(yùn)用企業(yè)管理中的高效運(yùn)用 高級(jí)金融建模高級(jí)金融建模 學(xué)習(xí)方法學(xué)習(xí)方法1 1循序漸進(jìn)循序漸進(jìn)2 2善用資源善用資源, ,學(xué)以致用學(xué)以致用通過好書、幫助、網(wǎng)絡(luò)、通過好書、幫助、網(wǎng)絡(luò)、bbsbbs論壇論壇 http:/ 博大精深博大精深, ,不必深究不必深究, ,但要了解但要了解3 3多閱讀多實(shí)踐多閱讀多實(shí)踐 實(shí)踐、實(shí)踐、再實(shí)踐實(shí)踐、實(shí)踐、再實(shí)踐 有問題，要獨(dú)立解決、思考，提高自己能力有問題，要獨(dú)立解決、思考，提高自己能力 歸納、總結(jié)、積累歸納、總結(jié)、積累以以excelexcel功底去學(xué)其他同類軟件，學(xué)習(xí)成本會(huì)非常低功底去學(xué)其他同類軟件，學(xué)習(xí)成本會(huì)非常低 主要內(nèi)容主要內(nèi)

8、容 基本功能基本功能 特殊技巧特殊技巧 函數(shù)與公式及其應(yīng)用函數(shù)與公式及其應(yīng)用 數(shù)據(jù)分析數(shù)據(jù)分析 圖表圖表 vbavbaexcel2007excel2007功能改進(jìn)功能改進(jìn) 針對(duì)針對(duì)excelexcel存在的局限性進(jìn)行改進(jìn)，使其能夠創(chuàng)建現(xiàn)存在的局限性進(jìn)行改進(jìn)，使其能夠創(chuàng)建現(xiàn)代風(fēng)格的文檔。代風(fēng)格的文檔。 使格式化文檔更簡便快捷使格式化文檔更簡便快捷 提供專門設(shè)計(jì)的可利用的且具有獨(dú)創(chuàng)性的內(nèi)容提供專門設(shè)計(jì)的可利用的且具有獨(dú)創(chuàng)性的內(nèi)容 容易看到工作成果，就像已經(jīng)打印出來的一樣容易看到工作成果，就像已經(jīng)打印出來的一樣 更容易維護(hù)電子表格和進(jìn)行格式更新更容易維護(hù)電子表格和進(jìn)行格式更新 滿足一些長期從事與打印

9、相關(guān)顧客的需求滿足一些長期從事與打印相關(guān)顧客的需求 提供一些美觀的文檔示例提供一些美觀的文檔示例 更容易移動(dòng)內(nèi)容更容易移動(dòng)內(nèi)容( (例如，圖表例如，圖表) )到其它到其它officeoffice應(yīng)用程序應(yīng)用程序( (例如，例如，powerpoint) powerpoint) 在在wordword，powerpointpowerpoint和和excelexcel中的所有操作方法都是中的所有操作方法都是一致的，因此，用戶能夠?qū)⒛硞€(gè)應(yīng)用程序的方法應(yīng)一致的，因此，用戶能夠?qū)⒛硞€(gè)應(yīng)用程序的方法應(yīng)用到另一個(gè)應(yīng)用程序中用到另一個(gè)應(yīng)用程序中 主要特點(diǎn)主要特點(diǎn) 增加在工作薄中可顯示的顏色數(shù)從(原先的)256色到

10、(現(xiàn)在的)43億(32位色) 格式化的“現(xiàn)場預(yù)覽” 極大的改進(jìn)了圖表,專業(yè)的“圖表”樣式 改進(jìn)了單元格樣式特點(diǎn)，添加了條件格式到列表，數(shù)據(jù)透視表和圖表 一個(gè)新的視圖頁面布局視圖，增加了普遍需要的、與打印相關(guān)的特點(diǎn)，單擊即可輸入頁眉和頁腳 “文檔主題”(顏色，字體和效果變化能在office應(yīng)用程序間共享) 更新了office界面外觀(繪圖工具條)和藝術(shù)字 25個(gè)美觀且具有獨(dú)創(chuàng)性的實(shí)用模板非?？岬臓顟B(tài)欄和精美圖表非?？岬臓顟B(tài)欄和精美圖表 excel2007excel2007質(zhì)的突破：質(zhì)的突破：1. 1. 靈巧變化的狀態(tài)欄靈巧變化的狀態(tài)欄狀態(tài)欄縮放控制：狀態(tài)欄縮放控制： 增加了一個(gè)不需彈出窗口的控制

11、滑塊來調(diào)整增加了一個(gè)不需彈出窗口的控制滑塊來調(diào)整文件的縮放比例，當(dāng)調(diào)整控制滑塊時(shí)文件的縮放比例，當(dāng)調(diào)整控制滑塊時(shí), ,文件同時(shí)文件同時(shí)改變顯示比例。也可以使用改變顯示比例。也可以使用“+”“+”和和“-”-”按鈕來按鈕來放大或縮小顯示比例放大或縮小顯示比例, ,每點(diǎn)擊一次調(diào)節(jié)每點(diǎn)擊一次調(diào)節(jié)10%10%。多樣化的計(jì)算狀態(tài)欄確多樣化的計(jì)算狀態(tài)欄確 在之前的在之前的excelexcel版本中，當(dāng)你選中了數(shù)值數(shù)據(jù)時(shí)，可以在版本中，當(dāng)你選中了數(shù)值數(shù)據(jù)時(shí)，可以在狀態(tài)欄看到這些數(shù)據(jù)的小計(jì)狀態(tài)欄看到這些數(shù)據(jù)的小計(jì) 求和，計(jì)數(shù)，平均值等等求和，計(jì)數(shù)，平均值等等，可以選擇，可以選擇6 6種不同的小計(jì)方式，但一次只

12、能看到一種種不同的小計(jì)方式，但一次只能看到一種。 excel 2007excel 2007中可以中可以把幾個(gè)或者全部的把幾個(gè)或者全部的小計(jì)方式顯示在狀態(tài)小計(jì)方式顯示在狀態(tài)欄，求和、最大值、最小值、計(jì)數(shù)欄，求和、最大值、最小值、計(jì)數(shù), , 計(jì)數(shù)值計(jì)數(shù)值, ,平均值的全平均值的全部顯示或者顯示其任意組合。部顯示或者顯示其任意組合。插入工作表按鈕插入工作表按鈕 只要單擊這個(gè)按鈕就會(huì)在工作簿中新增一個(gè)工作表，這一只要單擊這個(gè)按鈕就會(huì)在工作簿中新增一個(gè)工作表，這一點(diǎn)比較快捷點(diǎn)比較快捷 。2. 2. 幾個(gè)圖表幾個(gè)圖表 精髓：填充柄、單元格引用精髓：填充柄、單元格引用第第2章章 基本功能基本功能一、一、ex

13、celexcel基本操作基本操作 工作簿屬性 工作表屬性 單元格屬性 輸入數(shù)據(jù)技巧 頁面設(shè)置與打印 工作表編輯、格式 條件格式 選擇性粘貼 導(dǎo)入與導(dǎo)出數(shù)據(jù) 數(shù)據(jù)有效性的應(yīng)用 排序、篩選、分類匯總、數(shù)據(jù)透視表1. 工作簿屬性 工作表缺省數(shù)量(3) 工作表缺省用戶名(sheet1、sheet2、sheet3） 使用“office按鈕”下的“準(zhǔn)備/屬性”菜單設(shè)置文檔屬性 重點(diǎn)掌握其中的、主題、 關(guān)鍵詞、作者 標(biāo)記為最終狀態(tài)(只讀方式,不可修改) 密碼： 方法1：另存為對(duì)話框設(shè)置方法2：準(zhǔn)備/加密文檔”菜單設(shè)置，若取消 進(jìn)入設(shè)置對(duì)話框，刪去密碼即可2. 工作表屬性 工作表列：a,b,xfd(16384

14、=214) 工作表行:1,2,1048576=220 工作表多個(gè)獨(dú)立單元格214220 單元格地址：列標(biāo)行標(biāo)； 區(qū)域地址：左上角單元格地址:右下角單元格地址相對(duì)引用：例：b6，a4，c5：f8。絕對(duì)引用：例：$b$6,$a$4,$c$5:$f$8。混合引用：例：b$6,a$4,c$5:f$8、$b6,$a4,$c5:$f8 工作表格式化工作表編輯3. 單元格屬性 單元格所在行、列的高度和寬度 選定單元格或區(qū)域 單元格格式： 數(shù)字格式、對(duì)齊 字體、填充、邊框（斜線表頭）、4.導(dǎo)入與導(dǎo)出（文件類型）數(shù)據(jù)5. 輸入數(shù)據(jù)技巧（1）自動(dòng)填充（帶文本與數(shù)字混合、ctrl輔助、等比、等差序列）（2）系統(tǒng)提供

15、的序列數(shù)據(jù)（3）用戶自定義序列數(shù)據(jù)（4）記憶式輸入法（字符型，快捷菜單的“選擇列表”（5）多個(gè)單元格輸入相同內(nèi)容（不連續(xù)也可） ctrl+enter（6）日期輸入：輸入“1月1日”，用右鍵等。（7）同時(shí)填充多個(gè)工作表 例例: :快速用快速用“0”0”填充所有空白單元格填充所有空白單元格 選擇區(qū)域選擇區(qū)域 開始開始/ /編輯編輯/ /查找和選擇查找和選擇/ /定位定位條件條件 選中選中“空值空值” 輸入輸入”0”,”0”,按按ctrl+enterctrl+enter快速縮放數(shù)值快速縮放數(shù)值 目標(biāo)：將目標(biāo)：將1 1個(gè)大數(shù)個(gè)大數(shù)變成萬、千等單變成萬、千等單位表示的數(shù)值位表示的數(shù)值 方法：通過自定方法

16、：通過自定義格式實(shí)現(xiàn)。義格式實(shí)現(xiàn)。 b b列：列： 公式：公式：=a2=a2 格式使用格式使用c c列定列定義義6. 6. 數(shù)據(jù)類型數(shù)據(jù)類型計(jì)算日期間隔：日之差：日期直接相減月之差：datedif(a1, a2, m)年之差：datedif(a1, a2, y)文本型數(shù)字轉(zhuǎn)換為數(shù)據(jù)型數(shù)字文本型數(shù)字轉(zhuǎn)換為數(shù)據(jù)型數(shù)字 點(diǎn)擊點(diǎn)擊 智能標(biāo)記智能標(biāo)記, ,選擇選擇“轉(zhuǎn)換為數(shù)字轉(zhuǎn)換為數(shù)字” 6 6個(gè)公式：個(gè)公式： =a1=a1* *1 1 =a1/1=a1/1 =a1+0=a1+0 =a1-0=a1-0 =- -a1 =- -a1 減負(fù)運(yùn)算減負(fù)運(yùn)算 （第（第1 1個(gè)個(gè)- -是減法，第是減法，第2 2個(gè)個(gè)-

17、 -是負(fù)數(shù)）是負(fù)數(shù)）例：例：=sumproduct（-(left（a2：a10）=“陳”)統(tǒng)計(jì)姓陳的員工數(shù) =value=value（a1a1）邏輯型轉(zhuǎn)換為數(shù)據(jù)型數(shù)字邏輯型轉(zhuǎn)換為數(shù)據(jù)型數(shù)字 四則運(yùn)算四則運(yùn)算 true=1true=1 false=0false=0 例：例： =true+1 =true+1等于等于2 2， =false-1 =false-1等于等于-1-1 邏輯判斷邏輯判斷 0=false0=false 非零非零=true=true 6 6個(gè)公式：個(gè)公式： =a1=a1* *1 1 =a1/1=a1/1 =a1+0=a1+0 =a1-0=a1-0 =- -a1 =- -a1 減負(fù)

18、、減負(fù)、=n=n（）（）（n n函數(shù)）函數(shù)）其他功能：其他功能： 數(shù)據(jù)有效性數(shù)據(jù)有效性 條件格式條件格式 選擇性粘貼選擇性粘貼 排序、篩選、分類匯總、數(shù)據(jù)透視表排序、篩選、分類匯總、數(shù)據(jù)透視表二、二、excelexcel高級(jí)技巧高級(jí)技巧 保護(hù)工作簿和工作表 共享工作簿和合并工作簿 公式（相對(duì)引用與絕對(duì)引用） 函數(shù)、公式審核 窗體控件的應(yīng)用 vba1. 保護(hù)工作簿和工作表 保護(hù)工作簿 選擇“審閱”選項(xiàng)卡“更改”組的“保護(hù)工作簿”項(xiàng)選擇“保護(hù)結(jié)構(gòu)和窗口”。勾選“結(jié)構(gòu)”或“窗口”復(fù)選框保護(hù)結(jié)構(gòu): 不能插入、刪除工作表、更改工作表名稱等保護(hù)窗口: 保留窗口的大小及位置等 保護(hù)工作表: 保護(hù)工作表中數(shù)據(jù)

19、不被任意修改 鎖定鎖定+ +保護(hù)工作表保護(hù)工作表：保護(hù)所有被鎖定的單元格。 選擇“審閱”選項(xiàng)卡的“更改”組的“保護(hù)工作表”項(xiàng)，在對(duì)話框里輸入密碼 在“保護(hù)工作表”對(duì)話框勾選所需的保護(hù)內(nèi)容 保護(hù)工作表命令只對(duì)本工作表起作用 只允許用戶編輯指定單元格區(qū)域 取消鎖定取消鎖定+ +保護(hù)工作表保護(hù)工作表 選擇指定單元格，取消單元格的保護(hù)鎖定 此時(shí)只有可以被編輯，其它區(qū)域都被鎖住了 隱藏公式 隱藏隱藏+ +保護(hù)工作表保護(hù)工作表2. 共享工作簿共享工作簿：使用“審閱”選項(xiàng)卡的“更改”組選擇“共享工作簿”，打開其對(duì)話框，勾選“允許多用戶”復(fù)選框。共享工作簿+保護(hù)：以追蹤修訂方式共享：選擇“審閱”選項(xiàng)卡的“更改

20、”組的“保護(hù)共享工作簿”項(xiàng)，打開“保護(hù)共享工作簿”對(duì)話框。勾選“以追蹤修訂方式共享”選項(xiàng)，輸入密碼突出顯示修訂: 當(dāng)數(shù)據(jù)被修改時(shí),像批注一樣標(biāo)示出,格式變了不標(biāo)示 主要解決問題：工作表工作簿關(guān)主要解決問題：工作表工作簿關(guān)聯(lián)、引用數(shù)據(jù)區(qū)域聯(lián)、引用數(shù)據(jù)區(qū)域第第3 3章章 函數(shù)與公式基礎(chǔ)函數(shù)與公式基礎(chǔ)函數(shù)與公式學(xué)習(xí)方法函數(shù)與公式學(xué)習(xí)方法最有魅力的功能之一最有魅力的功能之一 初級(jí)階段：初級(jí)階段：常用函數(shù)，如何填寫參數(shù)；遇到常用函數(shù)，如何填寫參數(shù)；遇到了了if if函數(shù)，再遇到函數(shù)，再遇到vlookupvlookup函數(shù)（難：需要函數(shù)（難：需要空間感、理解數(shù)據(jù)在不同方位的定位、查找空間感、理解數(shù)據(jù)在不同

21、方位的定位、查找和返回的過程）和返回的過程） 中級(jí)階段：中級(jí)階段：單個(gè)函數(shù)功能是有限的，多個(gè)函單個(gè)函數(shù)功能是有限的，多個(gè)函數(shù)的嵌套與組合才能完成比較復(fù)雜的運(yùn)算數(shù)的嵌套與組合才能完成比較復(fù)雜的運(yùn)算 高級(jí)階段：高級(jí)階段：數(shù)組公式和多維引用數(shù)組公式和多維引用公式功能公式功能 計(jì)算計(jì)算 建立數(shù)據(jù)之間的關(guān)聯(lián)建立數(shù)據(jù)之間的關(guān)聯(lián) 單元格數(shù)據(jù)直接無關(guān)系單元格數(shù)據(jù)直接無關(guān)系 各工作簿之間無關(guān)系各工作簿之間無關(guān)系 各工作表之間無關(guān)系各工作表之間無關(guān)系 通過公式的邏輯關(guān)系，把它們關(guān)聯(lián)起來通過公式的邏輯關(guān)系，把它們關(guān)聯(lián)起來 自動(dòng)重算自動(dòng)重算 原始數(shù)據(jù)的改變可以使用同一個(gè)計(jì)算模型原始數(shù)據(jù)的改變可以使用同一個(gè)計(jì)算模型兩個(gè)

22、重要思路兩個(gè)重要思路 工作表、工作簿的邏輯關(guān)聯(lián)工作表、工作簿的邏輯關(guān)聯(lián)公式建立公式建立 excel由行列數(shù)據(jù)構(gòu)成的，因此獲取所需的由行列數(shù)據(jù)構(gòu)成的，因此獲取所需的行列區(qū)域是要解決主要問題行列區(qū)域是要解決主要問題數(shù)組、引用函數(shù)（數(shù)組、引用函數(shù)（offset、row、column、index、match等）等）數(shù)組數(shù)組_用行數(shù)(高)和列數(shù)(寬)確定的數(shù)據(jù)矩形 間隔行； 間隔列，數(shù)組常量：1,2、 15,18 水平數(shù)組(1行5列) 1,2,3,4,5 、column（a：e） 垂直數(shù)組(5行1列) 1；2；3；4；5、row（1：5） 單元素?cái)?shù)組 1、row（1：1）、column（a：a） =sm

23、all(if(a1:b40,a1:b4),1,2,3) 操作功能鍵：ctrl+shift+enter(完成的是多重計(jì)算完成的是多重計(jì)算) )重新計(jì)算公式的時(shí)間和方式重新計(jì)算公式的時(shí)間和方式 自動(dòng)重新計(jì)算（默認(rèn)的設(shè)置）自動(dòng)重新計(jì)算（默認(rèn)的設(shè)置） 只有在公式所依賴的單元格發(fā)生更改只有在公式所依賴的單元格發(fā)生更改 第一次打開工作簿以及編輯工作簿時(shí)第一次打開工作簿以及編輯工作簿時(shí)“excel 選項(xiàng)”的“公式”類別的“計(jì)算選項(xiàng)”部分的“工作簿計(jì)算”下，單擊“自動(dòng)” 除數(shù)據(jù)表外，自動(dòng)重算除數(shù)據(jù)表外，自動(dòng)重算 若要在每次更改值、公式或名稱時(shí)重新計(jì)算若要在每次更改值、公式或名稱時(shí)重新計(jì)算除數(shù)據(jù)表之外除數(shù)據(jù)表之

24、外所有相關(guān)的公式所有相關(guān)的公式 手動(dòng)計(jì)算手動(dòng)計(jì)算 若要關(guān)閉自動(dòng)重新計(jì)算若要關(guān)閉自動(dòng)重新計(jì)算單擊“手動(dòng)”時(shí)，excel 將自動(dòng)選中“保存工作簿前重新計(jì)算”復(fù)選框。如果保存工作簿需要很長時(shí)間，那么清除“保存工作簿前重新計(jì)算”可縮短保存時(shí)間。函數(shù)工具與技巧函數(shù)工具與技巧 公式復(fù)制公式復(fù)制 拖曳填充柄拖曳填充柄 雙擊填充柄（向下填充到鄰列第雙擊填充柄（向下填充到鄰列第1 1個(gè)空單元格上方）個(gè)空單元格上方） 選擇性粘貼公式選擇性粘貼公式 函數(shù)工具提示函數(shù)工具提示 （選項(xiàng)設(shè)置）（選項(xiàng)設(shè)置） 判斷參數(shù)是否可以省略（帶方括號(hào)的參數(shù)）判斷參數(shù)是否可以省略（帶方括號(hào)的參數(shù)） 逐步看計(jì)算結(jié)果逐步看計(jì)算結(jié)果 f9:

25、f9: 當(dāng)選中當(dāng)選中單元格地址或函數(shù)時(shí)，在編輯欄顯示部分計(jì)算結(jié)單元格地址或函數(shù)時(shí)，在編輯欄顯示部分計(jì)算結(jié)果果 公式求值公式求值 使用監(jiān)視窗口使用監(jiān)視窗口 保護(hù)保護(hù)/ /隱藏工作表中的公式隱藏工作表中的公式函數(shù)分類函數(shù)分類 內(nèi)置函數(shù)內(nèi)置函數(shù)datedifdatedif（）（） 擴(kuò)展函數(shù)擴(kuò)展函數(shù)通過加載宏通過加載宏 自定義函數(shù)自定義函數(shù)例：隱藏內(nèi)置函數(shù)例：隱藏內(nèi)置函數(shù) =numberstring(1234567890,1)=numberstring(1234567890,1) 結(jié)果：一十二億三千四百五十六萬七千八百九十 =numberstring(1234567890,2)=numberstrin

26、g(1234567890,2) 結(jié)果：壹拾貳億叁仟肆佰伍拾陸萬柒仟捌佰玖拾 =numberstring(1234567890,3)=numberstring(1234567890,3) 結(jié)果：一二三四五六七八九 =datestring(2008-8-8)=datestring(2008-8-8) 08年08月08日 datedifdatedif（）（）公式分類公式分類 普通公式普通公式 數(shù)組公式數(shù)組公式 命名公式命名公式 =a1=a1：a8a8 =avrager=avrager（datadata）公式限制公式限制 公式內(nèi)容長度不能超過公式內(nèi)容長度不能超過10241024個(gè)字符（個(gè)字符（2003

27、2003） 公式中函數(shù)嵌套不能超過公式中函數(shù)嵌套不能超過7 7層（層（20032003） 將前將前6 6層定義名稱，然后引用層定義名稱，然后引用 公式中函數(shù)參數(shù)不能超過公式中函數(shù)參數(shù)不能超過3030 使用括號(hào)括起多個(gè)參數(shù)，從而變成使用括號(hào)括起多個(gè)參數(shù)，從而變成1 1個(gè)參數(shù)個(gè)參數(shù) 數(shù)字計(jì)算精度為數(shù)字計(jì)算精度為1515位位 單引號(hào)單引號(hào) 設(shè)置為文本型設(shè)置為文本型名稱管理器名稱管理器 查看已有的名稱查看已有的名稱 查看名稱的引用范圍（查看名稱的引用范圍（“refers to” refers to” 控件）控件） 適用范圍（適用范圍（“scope” scope” 欄）欄） 結(jié)果值（結(jié)果值（“value

28、” value” 欄欄注：錯(cuò)誤結(jié)果值也會(huì)顯示出來）注：錯(cuò)誤結(jié)果值也會(huì)顯示出來） 確認(rèn)該名稱是否已在表格中使用（確認(rèn)該名稱是否已在表格中使用（“in use”in use”欄）欄） 創(chuàng)建名稱創(chuàng)建名稱 編輯已有的名稱編輯已有的名稱 名稱可以重命名，不必再為了改名字而重新去定義一個(gè)名稱名稱可以重命名，不必再為了改名字而重新去定義一個(gè)名稱 可以很快地在編輯名稱對(duì)話框中修改名稱的適用范圍可以很快地在編輯名稱對(duì)話框中修改名稱的適用范圍 快速刪除名稱快速刪除名稱 一次性選擇和刪除多個(gè)名稱一次性選擇和刪除多個(gè)名稱 名稱排序名稱排序 單擊欄標(biāo)題可以對(duì)名稱進(jìn)行排序單擊欄標(biāo)題可以對(duì)名稱進(jìn)行排序 調(diào)整名稱對(duì)話框的大小

29、調(diào)整名稱對(duì)話框的大小 根據(jù)需要調(diào)整根據(jù)需要調(diào)整refers-to boxrefers-to box（引用范圍文本框）的寬度。這樣，名稱（引用范圍文本框）的寬度。這樣，名稱的可見程度僅僅取決于桌面窗口的大小的可見程度僅僅取決于桌面窗口的大小使用名稱使用名稱(6(6個(gè)原因個(gè)原因) ) 增強(qiáng)公式可讀性增強(qiáng)公式可讀性 = =單價(jià)單價(jià)* *數(shù)量數(shù)量 代替公式重復(fù)出現(xiàn)部分代替公式重復(fù)出現(xiàn)部分 公式多次出現(xiàn)相同函數(shù)，使用名稱代替，簡潔公式多次出現(xiàn)相同函數(shù)，使用名稱代替，簡潔 使用常量名稱代替單元格區(qū)域引用使用常量名稱代替單元格區(qū)域引用 使用：使用：=vlookup=vlookup（a1,a1,等級(jí)等級(jí),2,

30、1,2,1） 作為條件格式或數(shù)據(jù)有效性序列跨表引用作為條件格式或數(shù)據(jù)有效性序列跨表引用 將此列數(shù)據(jù)命名為將此列數(shù)據(jù)命名為x, x,“來源來源”輸入：輸入：=x =x 宏表宏表4.04.0函數(shù)在工作表中必須通過名稱調(diào)用函數(shù)在工作表中必須通過名稱調(diào)用 20032003版本的函數(shù)調(diào)用超過版本的函數(shù)調(diào)用超過7 7層層, ,使用名稱解決更使用名稱解決更多層多層例例: : 在公式書寫時(shí)，名稱是一個(gè)很實(shí)用的工具在公式書寫時(shí)，名稱是一個(gè)很實(shí)用的工具 相對(duì)引用單元格區(qū)域，在寫公式的時(shí)候使用相對(duì)引用單元格區(qū)域，在寫公式的時(shí)候使用名稱不易出錯(cuò)，而且方便記憶（例如，用名稱不易出錯(cuò)，而且方便記憶（例如，用“tax_ra

31、tetax_rate（稅率）（稅率）” 而不用而不用“g36”g36”） 作用范圍作用范圍 工作簿級(jí)名稱（全局）工作簿級(jí)名稱（全局） 含工作簿名稱含工作簿名稱 工作表級(jí)名稱（局部）工作表級(jí)名稱（局部） 只含工作表名稱只含工作表名稱篩選名稱篩選名稱快速顯示名稱子集快速顯示名稱子集（使用（使用名稱管理器的名稱管理器的“篩選篩選”下拉列表）下拉列表）各選擇項(xiàng)含義：各選擇項(xiàng)含義： 名稱擴(kuò)展到工作表范圍：只顯示 適用范圍為工作表的名稱 名稱擴(kuò)展到工作簿范圍：只顯示工作簿范圍內(nèi)全局適用的名稱 有錯(cuò)誤的名稱 ：,只顯示值包含錯(cuò)誤（如 #ref、#value 或 #name）的那些名稱 沒有錯(cuò)誤的名稱：只顯示

32、值不包含錯(cuò)誤的那些名稱 已定義名稱：只顯示由您或 excel 定義的名稱，如打印區(qū)域 表名稱：只顯示表名稱。 # #div/0div/0零作除數(shù)零作除數(shù) # #name?name?在公式中使用了不能識(shí)別的名稱在公式中使用了不能識(shí)別的名稱 刪除了公式中使用的名稱，或者使用了不存在的名稱。刪除了公式中使用的名稱，或者使用了不存在的名稱。 函數(shù)名的拼寫錯(cuò)誤函數(shù)名的拼寫錯(cuò)誤# #valuevalue！使用了不正確的參數(shù)或運(yùn)算符使用了不正確的參數(shù)或運(yùn)算符 在需要數(shù)字或邏輯值時(shí)輸入了文本在需要數(shù)字或邏輯值時(shí)輸入了文本# #refref！引用了無效的單元格地址引用了無效的單元格地址 刪除了由其它公式引用的單

33、元格刪除了由其它公式引用的單元格 將移動(dòng)單元格粘貼到由其它公式引用的單元格中。將移動(dòng)單元格粘貼到由其它公式引用的單元格中。# #nullnull！指定了兩個(gè)并不相交的區(qū)域，故無效指定了兩個(gè)并不相交的區(qū)域，故無效 使用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。使用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。# #n/an/a 當(dāng)在函數(shù)或公式中引用了無法使用的數(shù)值當(dāng)在函數(shù)或公式中引用了無法使用的數(shù)值 內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個(gè)或多個(gè)參數(shù)。內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個(gè)或多個(gè)參數(shù)。 使用的自定義工作表函數(shù)不存在。使用的自定義工作表函數(shù)不存在。 vlookupvlookup（ ）函數(shù)中的

34、查找值函數(shù)中的查找值lookup_valuelookup_value、false/truefalse/true參數(shù)參數(shù)指定了不正確的值域。指定了不正確的值域。# #numnum！數(shù)字類型不正確數(shù)字類型不正確 在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)。在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)。 由公式產(chǎn)生的數(shù)字太大或太?。涸谟晒疆a(chǎn)生的數(shù)字太大或太?。涸?-10 -10307307和和1010307307之間之間#！輸入到單元格中的數(shù)值太長，在單元格中顯示不下；輸入到單元格中的數(shù)值太長，在單元格中顯示不下；單元格公式所產(chǎn)生的結(jié)果太長，單元格容納不下；日期和時(shí)間單元格公式所產(chǎn)生的結(jié)果太長，單元

35、格容納不下；日期和時(shí)間產(chǎn)生了負(fù)值時(shí)將產(chǎn)生。產(chǎn)生了負(fù)值時(shí)將產(chǎn)生。運(yùn)算符代替邏輯函數(shù)運(yùn)算符代替邏輯函數(shù) 星號(hào)星號(hào)* *代替代替“與與”例例: : =if=if（and(a160,a160,a160)=if(a160)* *(a1=70), (a160),and(b2=,c260),and(b2=女女,c255),c255)等價(jià)于：等價(jià)于：=and(b2=and(b2=男男,c260),c260)+ + and(b2=and(b2=女女,c255),c255)例例2 2: : =sumproduct(b2:b11=sumproduct(b2:b11=江西江西, ,廣東廣東)* *(c2:c11=(c

36、2:c11=男男) )* *d2:d11)d2:d11) =sumproduct(b2:b11=sumproduct(b2:b11=江西江西) )+ + (b2:b11=(b2:b11=廣東廣東“)“)* *(c2:c11=(c2:c11=男男) )* *d2:d11)d2:d11)不能用不能用andand、oror代替代替* *、+ +原因：數(shù)組公式需要執(zhí)行多重計(jì)算，而原因：數(shù)組公式需要執(zhí)行多重計(jì)算，而and、or返回的是單值返回的是單值true或或false，不能形成數(shù)組公式，不能形成數(shù)組公式多區(qū)域之間的一一對(duì)應(yīng)關(guān)系。多區(qū)域之間的一一對(duì)應(yīng)關(guān)系。例：例：=sum（and(c3:c770, c

37、3:c770)*(c3:c720) =sumproduct(b2:b11=一班一班)*(c2:c1120) 免去免去* *1: (兩個(gè)邏輯值數(shù)組*運(yùn)算,直接轉(zhuǎn)換數(shù)值型) 用用 * *: =sumproduct(b2:b11=一班一班)*c2:c1120) =sumproduct(b2:b11=一班一班)*1,(c2:c1120)* *1)第第4 4章章 引用與查找函數(shù)引用與查找函數(shù)offset()offset()功能：通過給定偏移量得到新的引用區(qū)域。 語法語法:offset(reference，rows，cols，height，width)有5個(gè)參數(shù)： reference基點(diǎn)或參照系，即引用區(qū)

38、域左上角單元格； rows偏移的行數(shù)。 行數(shù)正數(shù)：在基點(diǎn)的下方；負(fù)數(shù)：在基點(diǎn)的上方 cols偏移的列數(shù)。 列數(shù)正數(shù)：基點(diǎn)的右邊；負(fù)數(shù)：在基點(diǎn)的左邊。 height高度，即返回的引用區(qū)域的行數(shù)，必須為正數(shù)。 width寬度，即所要返回的引用區(qū)域的列數(shù)，必須為正數(shù)使用特點(diǎn)使用特點(diǎn) 設(shè)置所需要的區(qū)域設(shè)置所需要的區(qū)域 若結(jié)果為若結(jié)果為1個(gè)單元格個(gè)單元格,其值顯示在公式所在單其值顯示在公式所在單元格元格 往往用在往往用在 單元格區(qū)域單元格區(qū)域 求和、平均、最大最小的統(tǒng)計(jì)區(qū)域求和、平均、最大最小的統(tǒng)計(jì)區(qū)域 查找函數(shù)的范圍參數(shù)查找函數(shù)的范圍參數(shù)matchmatch 函數(shù)函數(shù)功能功能: :在單元格區(qū)域在單元格

39、區(qū)域 中搜索指定項(xiàng)，然后返回該項(xiàng)中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對(duì)位置。在單元格區(qū)域中的相對(duì)位置。 match(lookup_value, lookup_array, match_type)例如，如果單元格區(qū)域例如，如果單元格區(qū)域 a1:a3 a1:a3 包含值包含值 5 5、25 25 和和 3838，則以，則以下公式：下公式： =match(25,a1:a3,0)=match(25,a1:a3,0)會(huì)返回?cái)?shù)字會(huì)返回?cái)?shù)字 2 2，因?yàn)橹?，因?yàn)橹?25 25 是單元格區(qū)域中的第二項(xiàng)。是單元格區(qū)域中的第二項(xiàng)。match_typematch_type可選可選: :-1:查找大于或等于

40、lookup_value 的最小值0 : 查找等于 lookup_value 的第一個(gè)值 1: (默認(rèn)值)查找小于或等于 lookup_value 的最大值使用特點(diǎn)使用特點(diǎn) 第第2個(gè)參數(shù)：個(gè)參數(shù)：1行或行或1列數(shù)組列數(shù)組 是一個(gè)數(shù)是一個(gè)數(shù),表示查找值的行號(hào)或列號(hào)表示查找值的行號(hào)或列號(hào) 常常使用在常常使用在 index函數(shù)的行號(hào)或列號(hào)函數(shù)的行號(hào)或列號(hào) offset的參數(shù)（偏移量等）的參數(shù)（偏移量等） 區(qū)別（解決同問題）區(qū)別（解決同問題） =index(a1:d11,match(f1,a1:a11,0),match(g1,a1:d1,0) =offset(a1,match(f1,a2:a11,0)

41、,match(g1,b1:d1,0)indexindex index(array,row_num,column_num)功能：返回單元格或數(shù)組中的數(shù)據(jù)或元素值，功能：返回單元格或數(shù)組中的數(shù)據(jù)或元素值，此元素由行號(hào)和列號(hào)的索引值來給定。array單元格區(qū)域或數(shù)組常量。row_num數(shù)組中某行的行號(hào)，函數(shù)從該行返回?cái)?shù)值。如果省略 row_num，則必須有 column_num。column_num數(shù)組中某列的列標(biāo)，函數(shù)從該列返回?cái)?shù)值。如果省略 column_num，則必須有 row_num。使用特點(diǎn)使用特點(diǎn) 給出選定區(qū)域的行號(hào)或列號(hào)，獲取查找值給出選定區(qū)域的行號(hào)或列號(hào)，獲取查找值 多行多列多行多列

42、 單行單行 單列單列 常使用在常使用在 指定行或列的查找指定行或列的查找 row(reference)功能：返回引用的行號(hào)。功能：返回引用的行號(hào)。reference需要得到其行號(hào)的單元格或單元格區(qū)域。 column (reference)功能：返回引用的列號(hào)。功能：返回引用的列號(hào)。reference需要得到其列號(hào)的單元格或單元格區(qū)域。使用特點(diǎn)： 變化的特點(diǎn)：作為隨行/列變化的變量= column（），（），=row（）（） = column（a1），），=row（a1） = column（a：g），），=row（4：10） choose(index_num,value1,value2,.)功能

43、：使用功能：使用 index_num 返回?cái)?shù)值參數(shù)列表中的返回?cái)?shù)值參數(shù)列表中的數(shù)值。數(shù)值。index_num指定所選定的值參數(shù)。index_num 必須為 1 到 254 之間的數(shù)字，或者是包含數(shù)字 1 到 254 的公式或單元格引用。如果 index_num 為 1，函數(shù) choose 返回 value1；如果為 2，函數(shù) choose 返回 value2，以此類推。value1,value2,.為 1 到 254 個(gè)數(shù)值參數(shù)，函數(shù) choose 基于 index_num，從中選擇一個(gè)數(shù)值或一項(xiàng)要執(zhí)行的操作。lookuplookup hlookup(lookup_value,table_ar

44、ray,row_index_num,range_lookup)功能：在表格或數(shù)值功能：在表格或數(shù)值數(shù)組數(shù)組的首行查找指定的數(shù)值，并在表格的首行查找指定的數(shù)值，并在表格或數(shù)組中指定行的同一列中返回一個(gè)數(shù)值?；驍?shù)組中指定行的同一列中返回一個(gè)數(shù)值。 vlookup(lookup_value,table_array,col_index_num,range_lookup)功能：功能：參數(shù)表示垂直方向參數(shù)表示垂直方向 lookup(lookup_value,lookup_vector, result_vector)功能：功能：向量形式:在單行區(qū)域或單列區(qū)域（稱為“向量”）中查找值，然后返回第二個(gè)單行區(qū)域或

45、單列區(qū)域中相同位置的值。數(shù)組形式:在數(shù)組的第一行或第一列中查找指定的值，然后返回?cái)?shù)組的最后一行或最后一列中相同位置的值使用特點(diǎn)使用特點(diǎn) vlookup使用技巧使用技巧 1、2列互換列互換if(1,2,) 連接連接2個(gè)查找值個(gè)查找值 拼接拼接& “|” 部分含有部分含有通配符通配符“* *” offset構(gòu)造范圍構(gòu)造范圍在條件格式、數(shù)據(jù)有效性、排序等應(yīng)用在條件格式、數(shù)據(jù)有效性、排序等應(yīng)用第第5 5章章 函數(shù)應(yīng)用函數(shù)應(yīng)用1 1條件格式的應(yīng)用條件格式的應(yīng)用（生日提醒、標(biāo)記重復(fù)值、填充不（生日提醒、標(biāo)記重復(fù)值、填充不同色等）同色等）數(shù)據(jù)有效性的應(yīng)用數(shù)據(jù)有效性的應(yīng)用（限制錄入數(shù)據(jù)范圍、重復(fù)值、（

46、限制錄入數(shù)據(jù)范圍、重復(fù)值、創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等）創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等）選擇性粘貼選擇性粘貼( (四則運(yùn)算、轉(zhuǎn)置、復(fù)制格式、公式粘貼四則運(yùn)算、轉(zhuǎn)置、復(fù)制格式、公式粘貼為數(shù)值等）為數(shù)值等）排序應(yīng)用排序應(yīng)用( (字母、筆畫、字符數(shù)量、隨機(jī)等）字母、筆畫、字符數(shù)量、隨機(jī)等）函數(shù)應(yīng)用函數(shù)應(yīng)用1 11. 1.條件格式條件格式當(dāng)單元格數(shù)據(jù)滿足某種特定條件，自動(dòng)顯當(dāng)單元格數(shù)據(jù)滿足某種特定條件，自動(dòng)顯示指定的格式示指定的格式特點(diǎn)：特點(diǎn)： 動(dòng)態(tài)的動(dòng)態(tài)的 公式設(shè)置：公式設(shè)置：若對(duì)某列或某區(qū)域，多數(shù)情況只要對(duì)左上角單元格（相對(duì)引用）設(shè)置條件，excel會(huì)自動(dòng)擴(kuò)展到選區(qū)中 =abs(date(year(

47、today(),month($b=abs(date(year(today(),month($b2),day($b2)-today()=72),day($b2)-today()1=countif(a1:a$2,a1)1 設(shè)置填充色設(shè)置填充色例例3 3：填充：填充 國際象棋棋盤國際象棋棋盤 =mod(row()+column(),2)=0=mod(row()+column(),2)=0 =mod(row()+column(),2)=1=mod(row()+column(),2)=1 奇偶行不同奇偶行不同 =mod(row(),2)0=mod(row(),2)0 動(dòng)態(tài)的間隔底紋動(dòng)態(tài)的間隔底紋 =mod(subtotal(3,a$2:a2),2)=0=mod(subtotal(3,a$2:a2),2)=0 =mod(subtotal(3,a$2:a2),2)=1=mod(subtotal(3,a$2:a2),2)=1例例4 4：比較不同區(qū)域數(shù)值：比較不同區(qū)域數(shù)值 方法方法1 1： 條件格式的條件格式的“只為包含以下內(nèi)容只為包含以下內(nèi)容的單元格設(shè)置格式的單元格設(shè)置格式”項(xiàng)輸入公式