EXCEL高級應(yīng)用(經(jīng)典)_第1頁
EXCEL高級應(yīng)用(經(jīng)典)_第2頁
EXCEL高級應(yīng)用(經(jīng)典)_第3頁
EXCEL高級應(yīng)用(經(jīng)典)_第4頁
EXCEL高級應(yīng)用(經(jīng)典)_第5頁
已閱讀5頁,還剩77頁未讀 繼續(xù)免費閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認(rèn)領(lǐng)

文檔簡介

1、數(shù)據(jù)處理與分析平臺數(shù)據(jù)處理與分析平臺由淺入深 循序漸進第第1章章 導(dǎo)言導(dǎo)言5 5個層次個層次新手:新手:基本操作方法和常用功能:輸入數(shù)據(jù)、查找替基本操作方法和常用功能:輸入數(shù)據(jù)、查找替換、單元格格式、排序、匯總、篩選、保存等換、單元格格式、排序、匯總、篩選、保存等初級用戶:初級用戶:建立表格、圖表化建立表格、圖表化中級用戶:中級用戶:理解并熟練各個菜單命令、熟練使用數(shù)據(jù)理解并熟練各個菜單命令、熟練使用數(shù)據(jù)透視表、掌握透視表、掌握2020個函數(shù)(含個函數(shù)(含SUMSUM、IF IF、VLOOKUPVLOOKUP、INDEXINDEX、MATCHMATCH、OFFSETOFFSET、TEXTTEX

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

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

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

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

6、A實現(xiàn)實現(xiàn)資本資產(chǎn)定價模型的建立及其資本資產(chǎn)定價模型的建立及其VBAVBA實現(xiàn)實現(xiàn)Black-ScholesBlack-Scholes期權(quán)定價模型及其期權(quán)定價模型及其VBAVBA實現(xiàn)實現(xiàn)二叉樹(二項式)期權(quán)定價模型及其二叉樹(二項式)期權(quán)定價模型及其VBAVBA實現(xiàn)實現(xiàn)期貨套期保值計算的期貨套期保值計算的VBAVBA實現(xiàn)實現(xiàn)投資項目決策與理財模型的建立及其投資項目決策與理財模型的建立及其VBAVBA實現(xiàn)實現(xiàn)參考書:金融財務(wù)建模與計算基于VBA與MATLAB實現(xiàn)作者: 朱順泉 編著、出 版 社: 電子工業(yè)出版其他應(yīng)用其他應(yīng)用高效數(shù)據(jù)處理分析高效數(shù)據(jù)處理分析高效財務(wù)管理高效財務(wù)管理企業(yè)管理中的高效

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

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

9、些美觀的文檔示例提供一些美觀的文檔示例 更容易移動內(nèi)容更容易移動內(nèi)容( (例如,圖表例如,圖表) )到其它到其它OfficeOffice應(yīng)用程序應(yīng)用程序( (例如,例如,PowerPoint) PowerPoint) 在在WordWord,PowerPointPowerPoint和和ExcelExcel中的所有操作方法都是中的所有操作方法都是一致的,因此,用戶能夠?qū)⒛硞€應(yīng)用程序的方法應(yīng)一致的,因此,用戶能夠?qū)⒛硞€應(yīng)用程序的方法應(yīng)用到另一個應(yīng)用程序中用到另一個應(yīng)用程序中 主要特點主要特點增加在工作薄中可顯示的顏色數(shù)從(原先的)256色到(現(xiàn)在的)43億(32位色)格式化的“現(xiàn)場預(yù)覽” 極大的改進

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

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

12、007Excel 2007中可以中可以把幾個或者全部的把幾個或者全部的小計方式顯示在狀態(tài)小計方式顯示在狀態(tài)欄,求和、最大值、最小值、計數(shù)欄,求和、最大值、最小值、計數(shù), , 計數(shù)值計數(shù)值, ,平均值的全平均值的全部顯示或者顯示其任意組合。部顯示或者顯示其任意組合。插入工作表按鈕插入工作表按鈕 只要單擊這個按鈕就會在工作簿中新增一個工作表,這一只要單擊這個按鈕就會在工作簿中新增一個工作表,這一點比較快捷點比較快捷 。2. 2. 幾個圖表幾個圖表 精髓:填充柄、單元格引用精髓:填充柄、單元格引用第第2章章 基本功能基本功能一、一、ExcelExcel基本操作基本操作 工作簿屬性 工作表屬性 單元格

13、屬性 輸入數(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è)置文檔屬性 重點掌握其中的、主題、 關(guān)鍵詞、作者 標(biāo)記為最終狀態(tài)(只讀方式,不可修改) 密碼: 方法1:另存為對話框設(shè)置方法2:準(zhǔn)備/加密文檔”菜單設(shè)置,若取消 進入設(shè)置對話框,刪去密碼即可2. 工作表屬性 工作表列:A,B,XFD(16384=214) 工作表行:1,2,1048576=220 工作表多個

14、獨立單元格214220 單元格地址:列標(biāo)行標(biāo); 區(qū)域地址:左上角單元格地址:右下角單元格地址相對引用:例:B6,A4,C5:F8。絕對引用:例:$B$6,$A$4,$C$5:$F$8?;旌弦茫豪築$6,A$4,C$5:F$8、$B6,$A4,$C5:$F8 工作表格式化工作表編輯3. 單元格屬性 單元格所在行、列的高度和寬度 選定單元格或區(qū)域 單元格格式: 數(shù)字格式、對齊 字體、填充、邊框(斜線表頭)、4.導(dǎo)入與導(dǎo)出(文件類型)數(shù)據(jù)5. 輸入數(shù)據(jù)技巧(1)自動填充(帶文本與數(shù)字混合、Ctrl輔助、等比、等差序列)(2)系統(tǒng)提供的序列數(shù)據(jù)(3)用戶自定義序列數(shù)據(jù)(4)記憶式輸入法(字符型,快

15、捷菜單的“選擇列表”(5)多個單元格輸入相同內(nèi)容(不連續(xù)也可) Ctrl+Enter(6)日期輸入:輸入“1月1日”,用右鍵等。(7)同時填充多個工作表 例例: :快速用快速用“0”0”填充所有空白單元格填充所有空白單元格選擇區(qū)域選擇區(qū)域開始開始/ /編輯編輯/ /查找和選擇查找和選擇/ /定位定位條件條件選中選中“空值空值”輸入輸入”0”,”0”,按按Ctrl+EnterCtrl+Enter快速縮放數(shù)值快速縮放數(shù)值目標(biāo):將目標(biāo):將1 1個大數(shù)個大數(shù)變成萬、千等單變成萬、千等單位表示的數(shù)值位表示的數(shù)值方法:通過自定方法:通過自定義格式實現(xiàn)。義格式實現(xiàn)。B B列:列:u公式:公式:=A2=A2u

16、格式使用格式使用C C列定列定義義6. 6. 數(shù)據(jù)類型數(shù)據(jù)類型計算日期間隔:日之差:日期直接相減月之差:DATEDIF(A1, A2, M)年之差:DATEDIF(A1, A2, y)文本型數(shù)字轉(zhuǎn)換為數(shù)據(jù)型數(shù)字文本型數(shù)字轉(zhuǎn)換為數(shù)據(jù)型數(shù)字點擊點擊 智能標(biāo)記智能標(biāo)記, ,選擇選擇“轉(zhuǎn)換為數(shù)字轉(zhuǎn)換為數(shù)字”6 6個公式:個公式:u=A1=A1* *1 1u=A1/1=A1/1u=A1+0=A1+0u=A1-0=A1-0u=- -A1 =- -A1 減負(fù)運算減負(fù)運算 (第(第1 1個個- -是減法,第是減法,第2 2個個- -是負(fù)數(shù))是負(fù)數(shù))例:例:=SUMPRODUCT(-(LEFT(A2:A10)=

17、“陳”)統(tǒng)計姓陳的員工數(shù)u=VALUE=VALUE(A1A1)邏輯型轉(zhuǎn)換為數(shù)據(jù)型數(shù)字邏輯型轉(zhuǎn)換為數(shù)據(jù)型數(shù)字四則運算四則運算uTRUE=1TRUE=1 FALSE=0FALSE=0u例:例: =TRUE+1 =TRUE+1等于等于2 2, =FALSE-1 =FALSE-1等于等于-1-1邏輯判斷邏輯判斷u0=FALSE0=FALSE 非零非零=TRUE=TRUE6 6個公式:個公式:u=A1=A1* *1 1 u=A1/1=A1/1u=A1+0=A1+0u=A1-0=A1-0u=- -A1 =- -A1 減負(fù)、減負(fù)、=N=N()()(N N函數(shù))函數(shù))其他功能:其他功能:數(shù)據(jù)有效性數(shù)據(jù)有效性條

18、件格式條件格式選擇性粘貼選擇性粘貼排序、篩選、分類匯總、數(shù)據(jù)透視表排序、篩選、分類匯總、數(shù)據(jù)透視表二、二、ExcelExcel高級技巧高級技巧 保護工作簿和工作表 共享工作簿和合并工作簿 公式(相對引用與絕對引用) 函數(shù)、公式審核 窗體控件的應(yīng)用 VBA1. 保護工作簿和工作表 保護工作簿 選擇“審閱”選項卡“更改”組的“保護工作簿”項選擇“保護結(jié)構(gòu)和窗口”。勾選“結(jié)構(gòu)”或“窗口”復(fù)選框保護結(jié)構(gòu): 不能插入、刪除工作表、更改工作表名稱等保護窗口: 保留窗口的大小及位置等 保護工作表: 保護工作表中數(shù)據(jù)不被任意修改 鎖定鎖定+ +保護工作表保護工作表:保護所有被鎖定的單元格。 選擇“審閱”選項卡

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

20、密碼突出顯示修訂: 當(dāng)數(shù)據(jù)被修改時,像批注一樣標(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í)方法最有魅力的功能之一最有魅力的功能之一初級階段:初級階段:常用函數(shù),如何填寫參數(shù);遇到常用函數(shù),如何填寫參數(shù);遇到了了if if函數(shù),再遇到函數(shù),再遇到VLOOKUPVLOOKUP函數(shù)(難:需要函數(shù)(難:需要空間感、理解數(shù)據(jù)在不同方位的定位、查找空間感、理解數(shù)據(jù)在不同方位的定位、查找和返回的過程)和返回的過程)中級階段:中級階段:單個函數(shù)功能是有限的,多個函單個

21、函數(shù)功能是有限的,多個函數(shù)的嵌套與組合才能完成比較復(fù)雜的運算數(shù)的嵌套與組合才能完成比較復(fù)雜的運算高級階段:高級階段:數(shù)組公式和多維引用數(shù)組公式和多維引用公式功能公式功能計算計算建立數(shù)據(jù)之間的關(guān)聯(lián)建立數(shù)據(jù)之間的關(guān)聯(lián)u單元格數(shù)據(jù)直接無關(guān)系單元格數(shù)據(jù)直接無關(guān)系u各工作簿之間無關(guān)系各工作簿之間無關(guān)系u各工作表之間無關(guān)系各工作表之間無關(guān)系l通過公式的邏輯關(guān)系,把它們關(guān)聯(lián)起來通過公式的邏輯關(guān)系,把它們關(guān)聯(lián)起來l自動重算自動重算l原始數(shù)據(jù)的改變可以使用同一個計算模型原始數(shù)據(jù)的改變可以使用同一個計算模型兩個重要思路兩個重要思路工作表、工作簿的邏輯關(guān)聯(lián)工作表、工作簿的邏輯關(guān)聯(lián)公式建立公式建立EXCEL由行列數(shù)據(jù)

22、構(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列)u1,2,3,4,5 、COLUMN(A:E)垂直數(shù)組(5行1列)u1;2;3;4;5、ROW(1:5)單元素數(shù)組u1、row(1:1)、column(A:A) =SMALL(IF(A1:B40,A1:B4),1,2,3) 操作功能鍵:CTRL+SHIFT+ENTER(完成的是多重計算

23、完成的是多重計算) )重新計算公式的時間和方式重新計算公式的時間和方式自動重新計算(默認(rèn)的設(shè)置)自動重新計算(默認(rèn)的設(shè)置)u只有在公式所依賴的單元格發(fā)生更改只有在公式所依賴的單元格發(fā)生更改u第一次打開工作簿以及編輯工作簿時第一次打開工作簿以及編輯工作簿時“Excel 選項”的“公式”類別的“計算選項”部分的“工作簿計算”下,單擊“自動”除數(shù)據(jù)表外,自動重算除數(shù)據(jù)表外,自動重算u若要在每次更改值、公式或名稱時重新計算若要在每次更改值、公式或名稱時重新計算除數(shù)據(jù)表之外除數(shù)據(jù)表之外所有相關(guān)的公式所有相關(guān)的公式手動計算手動計算u若要關(guān)閉自動重新計算若要關(guān)閉自動重新計算單擊“手動”時,Excel 將自動

24、選中“保存工作簿前重新計算”復(fù)選框。如果保存工作簿需要很長時間,那么清除“保存工作簿前重新計算”可縮短保存時間。函數(shù)工具與技巧函數(shù)工具與技巧公式復(fù)制公式復(fù)制u拖曳填充柄拖曳填充柄u雙擊填充柄(向下填充到鄰列第雙擊填充柄(向下填充到鄰列第1 1個空單元格上方)個空單元格上方)u選擇性粘貼公式選擇性粘貼公式函數(shù)工具提示函數(shù)工具提示 (選項設(shè)置)(選項設(shè)置)判斷參數(shù)是否可以省略(帶方括號的參數(shù))判斷參數(shù)是否可以省略(帶方括號的參數(shù))逐步看計算結(jié)果逐步看計算結(jié)果uF9: F9: 當(dāng)選中當(dāng)選中單元格地址或函數(shù)時,在編輯欄顯示部分計算結(jié)單元格地址或函數(shù)時,在編輯欄顯示部分計算結(jié)果果u公式求值公式求值使用監(jiān)

25、視窗口使用監(jiān)視窗口保護保護/ /隱藏工作表中的公式隱藏工作表中的公式函數(shù)分類函數(shù)分類內(nèi)置函數(shù)內(nèi)置函數(shù)DateDifDateDif()()擴展函數(shù)擴展函數(shù)通過加載宏通過加載宏自定義函數(shù)自定義函數(shù)例:隱藏內(nèi)置函數(shù)例:隱藏內(nèi)置函數(shù)u=NUMBERSTRING(1234567890,1)=NUMBERSTRING(1234567890,1)l 結(jié)果:一十二億三千四百五十六萬七千八百九十 u=NUMBERSTRING(1234567890,2)=NUMBERSTRING(1234567890,2)l 結(jié)果:壹拾貳億叁仟肆佰伍拾陸萬柒仟捌佰玖拾 u=NUMBERSTRING(1234567890,3)=N

26、UMBERSTRING(1234567890,3)l 結(jié)果:一二三四五六七八九 u=DATESTRING(2008-8-8)=DATESTRING(2008-8-8)l 08年08月08日 uDATEDIFDATEDIF()()公式分類公式分類普通公式普通公式數(shù)組公式數(shù)組公式命名公式命名公式u=A1=A1:A8A8u=AVRAGER=AVRAGER(DATADATA)公式限制公式限制公式內(nèi)容長度不能超過公式內(nèi)容長度不能超過10241024個字符(個字符(20032003)公式中函數(shù)嵌套不能超過公式中函數(shù)嵌套不能超過7 7層(層(20032003)u將前將前6 6層定義名稱,然后引用層定義名稱,

27、然后引用公式中函數(shù)參數(shù)不能超過公式中函數(shù)參數(shù)不能超過3030u使用括號括起多個參數(shù),從而變成使用括號括起多個參數(shù),從而變成1 1個參數(shù)個參數(shù)數(shù)字計算精度為數(shù)字計算精度為1515位位u單引號單引號u設(shè)置為文本型設(shè)置為文本型名稱管理器名稱管理器 查看已有的名稱查看已有的名稱 u查看名稱的引用范圍(查看名稱的引用范圍(“Refers to” Refers to” 控件)控件)u適用范圍(適用范圍(“Scope” Scope” 欄)欄)u結(jié)果值(結(jié)果值(“Value” Value” 欄欄注:錯誤結(jié)果值也會顯示出來)注:錯誤結(jié)果值也會顯示出來)u確認(rèn)該名稱是否已在表格中使用(確認(rèn)該名稱是否已在表格中使用

28、(“In Use”In Use”欄)欄) 創(chuàng)建名稱創(chuàng)建名稱 編輯已有的名稱編輯已有的名稱 u名稱可以重命名,不必再為了改名字而重新去定義一個名稱名稱可以重命名,不必再為了改名字而重新去定義一個名稱 u可以很快地在編輯名稱對話框中修改名稱的適用范圍可以很快地在編輯名稱對話框中修改名稱的適用范圍 快速刪除名稱快速刪除名稱 u一次性選擇和刪除多個名稱一次性選擇和刪除多個名稱u名稱排序名稱排序u單擊欄標(biāo)題可以對名稱進行排序單擊欄標(biāo)題可以對名稱進行排序u 調(diào)整名稱對話框的大小調(diào)整名稱對話框的大小u根據(jù)需要調(diào)整根據(jù)需要調(diào)整refers-to boxrefers-to box(引用范圍文本框)的寬度。這樣,

29、名稱(引用范圍文本框)的寬度。這樣,名稱的可見程度僅僅取決于桌面窗口的大小的可見程度僅僅取決于桌面窗口的大小使用名稱使用名稱(6(6個原因個原因) ) 增強公式可讀性增強公式可讀性u= =單價單價* *數(shù)量數(shù)量代替公式重復(fù)出現(xiàn)部分代替公式重復(fù)出現(xiàn)部分u公式多次出現(xiàn)相同函數(shù),使用名稱代替,簡潔公式多次出現(xiàn)相同函數(shù),使用名稱代替,簡潔使用常量名稱代替單元格區(qū)域引用使用常量名稱代替單元格區(qū)域引用u使用:使用:=VLOOKUP=VLOOKUP(A1,A1,等級等級,2,1,2,1)作為條件格式或數(shù)據(jù)有效性序列跨表引用作為條件格式或數(shù)據(jù)有效性序列跨表引用u將此列數(shù)據(jù)命名為將此列數(shù)據(jù)命名為x, x,“來源

30、來源”輸入:輸入:=x =x 宏表宏表4.04.0函數(shù)在工作表中必須通過名稱調(diào)用函數(shù)在工作表中必須通過名稱調(diào)用20032003版本的函數(shù)調(diào)用超過版本的函數(shù)調(diào)用超過7 7層層, ,使用名稱解決更使用名稱解決更多層多層例例: : 在公式書寫時,名稱是一個很實用的工具在公式書寫時,名稱是一個很實用的工具相對引用單元格區(qū)域,在寫公式的時候使用相對引用單元格區(qū)域,在寫公式的時候使用名稱不易出錯,而且方便記憶(例如,用名稱不易出錯,而且方便記憶(例如,用“Tax_RateTax_Rate(稅率)(稅率)” ” 而不用而不用“G36”G36”) 作用范圍作用范圍u工作簿級名稱(全局)工作簿級名稱(全局)l含

31、工作簿名稱含工作簿名稱u工作表級名稱(局部)工作表級名稱(局部)l只含工作表名稱只含工作表名稱篩選名稱篩選名稱快速顯示名稱子集快速顯示名稱子集(使用(使用名稱管理器的名稱管理器的“篩選篩選”下拉列表)下拉列表)各選擇項含義:各選擇項含義: 名稱擴展到工作表范圍:只顯示 適用范圍為工作表的名稱 名稱擴展到工作簿范圍:只顯示工作簿范圍內(nèi)全局適用的名稱 有錯誤的名稱 :,只顯示值包含錯誤(如 #REF、#VALUE 或 #NAME)的那些名稱 沒有錯誤的名稱:只顯示值不包含錯誤的那些名稱 已定義名稱:只顯示由您或 Excel 定義的名稱,如打印區(qū)域 表名稱:只顯示表名稱。 # #DIV/0DIV/0

32、零作除數(shù)零作除數(shù) # #NAME?NAME?在公式中使用了不能識別的名稱在公式中使用了不能識別的名稱 刪除了公式中使用的名稱,或者使用了不存在的名稱。刪除了公式中使用的名稱,或者使用了不存在的名稱。 函數(shù)名的拼寫錯誤函數(shù)名的拼寫錯誤# #VALUEVALUE!使用了不正確的參數(shù)或運算符使用了不正確的參數(shù)或運算符 在需要數(shù)字或邏輯值時輸入了文本在需要數(shù)字或邏輯值時輸入了文本# #REFREF!引用了無效的單元格地址引用了無效的單元格地址 刪除了由其它公式引用的單元格刪除了由其它公式引用的單元格 將移動單元格粘貼到由其它公式引用的單元格中。將移動單元格粘貼到由其它公式引用的單元格中。# #NULL

33、NULL!指定了兩個并不相交的區(qū)域,故無效指定了兩個并不相交的區(qū)域,故無效 使用了不正確的區(qū)域運算符或不正確的單元格引用。使用了不正確的區(qū)域運算符或不正確的單元格引用。# #N/AN/A 當(dāng)在函數(shù)或公式中引用了無法使用的數(shù)值當(dāng)在函數(shù)或公式中引用了無法使用的數(shù)值 內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個或多個參數(shù)。內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個或多個參數(shù)。 使用的自定義工作表函數(shù)不存在。使用的自定義工作表函數(shù)不存在。 VLOOKUPVLOOKUP( )函數(shù)中的查找值函數(shù)中的查找值lookup_valuelookup_value、FALSE/TRUEFALSE/TRUE參數(shù)參數(shù)指定了不正確的值域

34、。指定了不正確的值域。# #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é)果太長,單元格容納不下;日期和時間單元格公式所產(chǎn)生的結(jié)果太長,單元格容納不下;日期和時間產(chǎn)生了負(fù)值時將產(chǎn)生。產(chǎn)生了負(fù)值時將產(chǎn)生。運算符代替邏輯函數(shù)運算符代替邏輯函數(shù)星號星號* *代替代替“與與”例例:

35、: =IF=IF(AND(A160,A160,A160)=IF(A160)* *(A1=70), (A160),AND(B2=,C260),AND(B2=女女,C255),C255)等價于:等價于:=AND(B2=AND(B2=男男,C260),C260)+ + AND(B2=AND(B2=女女,C255),C255)例例2 2: : =SUMPRODUCT(B2:B11=SUMPRODUCT(B2:B11=江西江西, ,廣東廣東)* *(C2:C11=(C2:C11=男男) )* *D2:D11)D2:D11) =SUMPRODUCT(B2:B11=SUMPRODUCT(B2:B11=江西江

36、西) )+ + (B2:B11=(B2:B11=廣東廣東“)“)* *(C2:C11=(C2:C11=男男) )* *D2:D11)D2:D11)不能用不能用AND、OR代替代替*、+原因:數(shù)組公式需要執(zhí)行多重計算,而原因:數(shù)組公式需要執(zhí)行多重計算,而AND、OR返回的是單值返回的是單值TRUE或或FALSE,不能形成數(shù),不能形成數(shù)組公式多區(qū)域之間的一一對應(yīng)關(guān)系。組公式多區(qū)域之間的一一對應(yīng)關(guān)系。例:例:=SUM(AND(C3:C770, C3:C770)*(C3:C720)u=SUMPRODUCT(B2:B11=一班一班)*(C2:C1120)免去免去*1: (兩個邏輯值數(shù)組*運算,直接轉(zhuǎn)換數(shù)

37、值型)u用用 *: =SUMPRODUCT(B2:B11=一班一班)*C2:C1120)u=SUMPRODUCT(B2:B11=一班一班)*1,(C2:C1120)*1)第第4 4章章 引用與查找函數(shù)引用與查找函數(shù)Offset()Offset()功能:通過給定偏移量得到新的引用區(qū)域。 語法語法:OFFSET(reference,rows,cols,height,width)有5個參數(shù):uReference基點或參照系,即引用區(qū)域左上角單元格;uRows偏移的行數(shù)。l行數(shù)正數(shù):在基點的下方;負(fù)數(shù):在基點的上方uCols偏移的列數(shù)。l列數(shù)正數(shù):基點的右邊;負(fù)數(shù):在基點的左邊。uHeight高度,即

38、返回的引用區(qū)域的行數(shù),必須為正數(shù)。uWidth寬度,即所要返回的引用區(qū)域的列數(shù),必須為正數(shù)使用特點使用特點設(shè)置所需要的區(qū)域設(shè)置所需要的區(qū)域若結(jié)果為若結(jié)果為1個單元格個單元格,其值顯示在公式所在單其值顯示在公式所在單元格元格往往用在往往用在u單元格區(qū)域單元格區(qū)域u求和、平均、最大最小的統(tǒng)計區(qū)域求和、平均、最大最小的統(tǒng)計區(qū)域u查找函數(shù)的范圍參數(shù)查找函數(shù)的范圍參數(shù)MATCHMATCH 函數(shù)函數(shù)功能功能: :在單元格區(qū)域在單元格區(qū)域 中搜索指定項,然后返回該項中搜索指定項,然后返回該項在單元格區(qū)域中的相對位置。在單元格區(qū)域中的相對位置。 MATCH(lookup_value, lookup_array

39、, match_type)例如,如果單元格區(qū)域例如,如果單元格區(qū)域 A1:A3 A1:A3 包含值包含值 5 5、25 25 和和 3838,則以,則以下公式:下公式: =MATCH(25,A1:A3,0)=MATCH(25,A1:A3,0)會返回數(shù)字會返回數(shù)字 2 2,因為值,因為值 25 25 是單元格區(qū)域中的第二項。是單元格區(qū)域中的第二項。match_typematch_type可選可選: :-1:查找大于或等于 lookup_value 的最小值0 : 查找等于 lookup_value 的第一個值 1: (默認(rèn)值)查找小于或等于 lookup_value 的最大值使用特點使用特點第第

40、2個參數(shù):個參數(shù):1行或行或1列數(shù)組列數(shù)組是一個數(shù)是一個數(shù),表示查找值的行號或列號表示查找值的行號或列號常常使用在常常使用在uIndex函數(shù)的行號或列號函數(shù)的行號或列號uOFFSET的參數(shù)(偏移量等)的參數(shù)(偏移量等)區(qū)別(解決同問題)區(qū)別(解決同問題)u=INDEX(A1:D11,MATCH(F1,A1:A11,0),MATCH(G1,A1:D1,0) u=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0)INDEXINDEX(array,row_num,column_num)功能:返回單元格或數(shù)組中的數(shù)據(jù)或元素值,功能:返回單元格或數(shù)組中的數(shù)據(jù)或元

41、素值,此元素由行號和列號的索引值來給定。Array單元格區(qū)域或數(shù)組常量。Row_num數(shù)組中某行的行號,函數(shù)從該行返回數(shù)值。如果省略 row_num,則必須有 column_num。Column_num數(shù)組中某列的列標(biāo),函數(shù)從該列返回數(shù)值。如果省略 column_num,則必須有 row_num。使用特點使用特點給出選定區(qū)域的行號或列號,獲取查找值給出選定區(qū)域的行號或列號,獲取查找值u多行多列多行多列u單行單行u單列單列常使用在常使用在u指定行或列的查找指定行或列的查找ROW(reference)功能:返回引用的行號。功能:返回引用的行號。Reference需要得到其行號的單元格或單元格區(qū)域。

42、COLUMN (reference)功能:返回引用的列號。功能:返回引用的列號。Reference需要得到其列號的單元格或單元格區(qū)域。使用特點: 變化的特點:作為隨行/列變化的變量= COLUMN(),(),=ROW()() = COLUMN(A1),),=ROW(A1) = COLUMN(A:G),),=ROW(4:10)CHOOSE(index_num,value1,value2,.)功能:使用功能:使用 index_num 返回數(shù)值參數(shù)列表中返回數(shù)值參數(shù)列表中的數(shù)值。的數(shù)值。Index_num指定所選定的值參數(shù)。Index_num 必須為 1 到 254 之間的數(shù)字,或者是包含數(shù)字 1

43、到 254 的公式或單元格引用。如果 index_num 為 1,函數(shù) CHOOSE 返回 value1;如果為 2,函數(shù) CHOOSE 返回 value2,以此類推。Value1,value2,.為 1 到 254 個數(shù)值參數(shù),函數(shù) CHOOSE 基于 index_num,從中選擇一個數(shù)值或一項要執(zhí)行的操作。LOOKUP HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)功能:在表格或數(shù)值功能:在表格或數(shù)值數(shù)組數(shù)組的首行查找指定的數(shù)值,并在表格的首行查找指定的數(shù)值,并在表格或數(shù)組中指定行的同一列中返回一個數(shù)值。或數(shù)組中指定

44、行的同一列中返回一個數(shù)值。 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能:功能:參數(shù)表示垂直方向參數(shù)表示垂直方向 LOOKUP(lookup_value,lookup_vector, result_vector)功能:功能:向量形式:在單行區(qū)域或單列區(qū)域(稱為“向量”)中查找值,然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的值。數(shù)組形式:在數(shù)組的第一行或第一列中查找指定的值,然后返回數(shù)組的最后一行或最后一列中相同位置的值使用特點使用特點VLOOKUP使用技巧使用技巧u1、2列互換列互換if(1,2,)u連接連接2個查

45、找值個查找值 拼接拼接& “|”u部分含有部分含有通配符通配符“*”uOFFSET構(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ù)值、(限制錄入數(shù)據(jù)范圍、重復(fù)值、創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等)創(chuàng)建下拉列表、快捷輸入數(shù)據(jù)等)選擇性粘貼選擇性粘貼( (四則運算、轉(zhuǎn)置、復(fù)制格式、公式粘貼四則運算、轉(zhuǎn)置、復(fù)制格式、公式粘貼為數(shù)值等)為數(shù)值等)排序應(yīng)用排序應(yīng)用( (

46、字母、筆畫、字符數(shù)量、隨機等)字母、筆畫、字符數(shù)量、隨機等)函數(shù)應(yīng)用函數(shù)應(yīng)用1 11. 1.條件格式條件格式當(dāng)單元格數(shù)據(jù)滿足某種特定條件,自動顯當(dāng)單元格數(shù)據(jù)滿足某種特定條件,自動顯示指定的格式示指定的格式特點:特點:動態(tài)的動態(tài)的公式設(shè)置:公式設(shè)置:若對某列或某區(qū)域,多數(shù)情況只要對左上角單元格(相對引用)設(shè)置條件,EXCEL會自動擴展到選區(qū)中=ABS(DATE(YEAR(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è)

47、置填充色例例3 3:填充:填充國際象棋棋盤國際象棋棋盤u=MOD(ROW()+COLUMN(),2)=0=MOD(ROW()+COLUMN(),2)=0u=MOD(ROW()+COLUMN(),2)=1=MOD(ROW()+COLUMN(),2)=1奇偶行不同奇偶行不同u=MOD(ROW(),2)0=MOD(ROW(),2)0動態(tài)的間隔底紋動態(tài)的間隔底紋u=MOD(SUBTOTAL(3,A$2:A2),2)=0=MOD(SUBTOTAL(3,A$2:A2),2)=0u=MOD(SUBTOTAL(3,A$2:A2),2)=1=MOD(SUBTOTAL(3,A$2:A2),2)=1例例4 4:比較不同區(qū)域數(shù)值:比較不同區(qū)域數(shù)值方法方法1 1:u 條件格式的條件格式的“只為包含以下內(nèi)只為包含以下內(nèi)容的單元格

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

評論

0/150

提交評論