excel高級(jí)應(yīng)用(經(jīng)典)

1、EXCEL高級(jí)應(yīng)用,——數(shù)據(jù)處理與分析平臺(tái),由淺入深 循序漸進(jìn),,第1章 導(dǎo)言,5個(gè)層次,新手：基本操作方法和常用功能：輸入數(shù)據(jù)、查找替換、單元格格式、排序、匯總、篩選、保存等初級(jí)用戶：建立表格、圖表化中級(jí)用戶：理解并熟練各個(gè)菜單命令、熟練使用數(shù)據(jù)透視表、掌握20個(gè)函數(shù)（含SUM、IF、VLOOKUP、INDEX、MATCH、OFFSET、TEXT）與函數(shù)的嵌套、宏；高級(jí)用戶：熟練運(yùn)用數(shù)組公式、使用VBA編寫(xiě)不太復(fù)雜的自定義

2、函數(shù)或過(guò)程；專家：高超的技術(shù)并擁有豐富的行業(yè)知識(shí)和經(jīng)驗(yàn)，屬于EXCELHOME網(wǎng)站版主或高級(jí)會(huì)員,你屬于哪一層次?,目前EXCEL使用狀況,使用了20%，常用的只有5%，原因：根本不知道還有其他功能知道功能但不知道如何使用暫時(shí)使用不上,不去了解,為什么學(xué)習(xí):,信息時(shí)代，數(shù)據(jù)量大，你是不是每天還在編制復(fù)雜的公式，還在一遍又一遍的重復(fù)著手工輸入，生怕有一個(gè)數(shù)據(jù)弄錯(cuò)？累吧？煩吧？想解脫嗎？Excel強(qiáng)大的數(shù)據(jù)處理功能已經(jīng)征服了每一個(gè)

3、Excel使用者,人力資源管理____應(yīng)用實(shí)例,動(dòng)態(tài)了解公司員工的流入和流出變化情況；分析員工今年薪酬變化，便于為明年的薪酬控制提供依據(jù)每天、每月都要計(jì)算員工的考勤，計(jì)算員工的工資和獎(jiǎng)金，制作工資條，把工資準(zhǔn)確無(wú)誤地發(fā)放到每個(gè)員工賬戶，并及時(shí)通知每個(gè)員工；每年都要把全公司上百人甚至上千人的工資進(jìn)行匯總，制作五險(xiǎn)一金匯總表，制作個(gè)稅代扣代繳表員工的生日、合同、退休日期快要到了，如何才能提前提醒，以免到時(shí)候手忙腳亂；新員工的試用期快

4、要到了，如何及時(shí)提醒聘用者簽訂正式勞動(dòng)合同如何評(píng)價(jià)每個(gè)業(yè)務(wù)人員的銷售業(yè)績(jī)，并根據(jù)業(yè)績(jī)計(jì)算薪酬,主講： 資深EXCEL實(shí)戰(zhàn)專家韓小良 1980元/人/兩天,金融財(cái)務(wù)建模____應(yīng)用實(shí)例,投資組合收益率和方差計(jì)算及其VBA實(shí)現(xiàn)投資組合有效邊界模型及其VBA實(shí)現(xiàn)投資組合風(fēng)險(xiǎn)優(yōu)化決策模型及其VBA實(shí)現(xiàn)投資組合風(fēng)險(xiǎn)價(jià)值模型及其VBA實(shí)現(xiàn)資本資產(chǎn)定價(jià)模型的建立及其VBA實(shí)現(xiàn)Black-Scholes期權(quán)定價(jià)模型及其VBA實(shí)現(xiàn)二叉樹(shù)（二

5、項(xiàng)式）期權(quán)定價(jià)模型及其VBA實(shí)現(xiàn)期貨套期保值計(jì)算的VBA實(shí)現(xiàn)投資項(xiàng)目決策與理財(cái)模型的建立及其VBA實(shí)現(xiàn)參考書(shū):《金融財(cái)務(wù)建模與計(jì)算——基于VBA與MATLAB實(shí)現(xiàn)》作者： 朱順泉 編著、出 版 社： 電子工業(yè)出版,其他應(yīng)用,高效數(shù)據(jù)處理分析高效財(cái)務(wù)管理企業(yè)管理中的高效運(yùn)用 高級(jí)金融建模,學(xué)習(xí)方法,1．循序漸進(jìn)2．善用資源,學(xué)以致用通過(guò)好書(shū)、幫助、網(wǎng)絡(luò)、BBS論壇 http://club.excelhome.net/

6、thread-117862-1-1.html博大精深,不必深究,但要了解3．多閱讀多實(shí)踐實(shí)踐、實(shí)踐、再實(shí)踐有問(wèn)題，要獨(dú)立解決、思考，提高自己能力歸納、總結(jié)、積累以EXCEL功底去學(xué)其他同類軟件，學(xué)習(xí)成本會(huì)非常低,主要內(nèi)容,基本功能特殊技巧函數(shù)與公式及其應(yīng)用數(shù)據(jù)分析圖表VBA,Excel2007功能改進(jìn),針對(duì)Excel存在的局限性進(jìn)行改進(jìn)，使其能夠創(chuàng)建現(xiàn)代風(fēng)格的文檔。 使格式化文檔更簡(jiǎn)便快捷 提供專門(mén)設(shè)計(jì)的可利用

7、的且具有獨(dú)創(chuàng)性的內(nèi)容 容易看到工作成果，就像已經(jīng)打印出來(lái)的一樣 更容易維護(hù)電子表格和進(jìn)行格式更新 滿足一些長(zhǎng)期從事與打印相關(guān)顧客的需求 提供一些美觀的文檔示例 更容易移動(dòng)內(nèi)容(例如，圖表)到其它Office應(yīng)用程序(例如，PowerPoint) 在Word，PowerPoint和Excel中的所有操作方法都是一致的，因此，用戶能夠?qū)⒛硞€(gè)應(yīng)用程序的方法應(yīng)用到另一個(gè)應(yīng)用程序中,主要特點(diǎn),增加在工作薄中可顯示的顏色數(shù)從(原先的)2

8、56色到(現(xiàn)在的)43億(32位色)格式化的“現(xiàn)場(chǎng)預(yù)覽” 極大的改進(jìn)了圖表,專業(yè)的“圖表”樣式改進(jìn)了單元格樣式特點(diǎn)，添加了條件格式到列表，數(shù)據(jù)透視表和圖表一個(gè)新的視圖——頁(yè)面布局視圖，增加了普遍需要的、與打印相關(guān)的特點(diǎn)，單擊即可輸入頁(yè)眉和頁(yè)腳 “文檔主題”(顏色，字體和效果變化能在Office應(yīng)用程序間共享)更新了Office界面外觀(繪圖工具條)和藝術(shù)字 25個(gè)美觀且具有獨(dú)創(chuàng)性的實(shí)用模板,非常酷的狀態(tài)欄和精美圖表,Exc

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

10、中可以把幾個(gè)或者全部的小計(jì)方式顯示在狀態(tài)欄，求和、最大值、最小值、計(jì)數(shù), 計(jì)數(shù)值,平均值的全部顯示或者顯示其任意組合。插入工作表按鈕 只要單擊這個(gè)按鈕就會(huì)在工作簿中新增一個(gè)工作表，這一點(diǎn)比較快捷 。,2. 幾個(gè)圖表,,,,,,———— 精髓：填充柄、單元格引用,第2章 基本功能,一、Excel基本操作工作簿屬性工作表屬性單元格屬性輸入數(shù)據(jù)技巧頁(yè)面設(shè)置與打印工作表編輯、格式條件格式選擇性粘貼導(dǎo)入與導(dǎo)出數(shù)據(jù)數(shù)據(jù)

11、有效性的應(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,…,XF

12、D(16384=214)工作表行:1,2,…,1048576=220工作表多個(gè)獨(dú)立單元格214×220單元格地址：列標(biāo)行標(biāo)；區(qū)域地址：左上角單元格地址:右下角單元格地址相對(duì)引用：例：B6，A4，C5：F8。絕對(duì)引用：例：$B$6,$A$4,$C$5:$F$8?；旌弦茫豪築$6,A$4,C$5:F$8、$B6,$A4,$C5:$F8工作表格式化工作表編輯,3. 單元格屬性單元格所在行、列的高度和寬度選

13、定單元格或區(qū)域單元格格式：數(shù)字格式、對(duì)齊字體、填充、邊框（斜線表頭）、4.導(dǎo)入與導(dǎo)出（文件類型）數(shù)據(jù),5. 輸入數(shù)據(jù)技巧（1）自動(dòng)填充（帶文本與數(shù)字混合、Ctrl輔助、等比、等差序列）（2）系統(tǒng)提供的序列數(shù)據(jù)（3）用戶自定義序列數(shù)據(jù)（4）記憶式輸入法（字符型，快捷菜單的“選擇列表”（5）多個(gè)單元格輸入相同內(nèi)容（不連續(xù)也可） Ctrl+Enter（6）日期輸入：輸入“1月1日”，用右鍵等。

14、（7）同時(shí)填充多個(gè)工作表,例:快速用“0”填充所有空白單元格,選擇區(qū)域開(kāi)始/編輯/查找和選擇/定位條件選中“空值”輸入”0”,按Ctrl+Enter,快速縮放數(shù)值,目標(biāo)：將1個(gè)大數(shù)變成萬(wàn)、千等單位表示的數(shù)值方法：通過(guò)自定義格式實(shí)現(xiàn)。B列：公式：=A2格式使用C列定義,6. 數(shù)據(jù)類型,,,,計(jì)算日期間隔：日之差：日期直接相減月之差：DATEDIF(A1, A2, "M")年之差：DATEDIF(A1

15、, A2, "y"),文本型數(shù)字轉(zhuǎn)換為數(shù)據(jù)型數(shù)字,點(diǎn)擊"智能標(biāo)記",選擇“轉(zhuǎn)換為數(shù)字”6個(gè)公式：=A1*1=A1/1=A1+0=A1-0=- -A1 減負(fù)運(yùn)算 （第1個(gè)-是減法，第2個(gè)-是負(fù)數(shù)）例：=SUMPRODUCT（--(LEFT（A2：A10）=“陳”))統(tǒng)計(jì)姓陳的員工數(shù)=VALUE（A1）,邏輯型轉(zhuǎn)換為數(shù)據(jù)型數(shù)字,四則運(yùn)算TRUE=1 FALSE=0例： =T

16、RUE+1等于2， =FALSE-1等于-1邏輯判斷0=FALSE 非零=TRUE6個(gè)公式：=A1*1 =A1/1=A1+0=A1-0=- -A1 減負(fù)、=N（）（N函數(shù)）,其他功能：,數(shù)據(jù)有效性條件格式選擇性粘貼排序、篩選、分類匯總、數(shù)據(jù)透視表,二、Excel高級(jí)技巧保護(hù)工作簿和工作表共享工作簿和合并工作簿公式（相對(duì)引用與絕對(duì)引用）函數(shù)、公式審核窗體控件的應(yīng)用VBA,1. 保護(hù)工作簿和工作表

17、保護(hù)工作簿 選擇“審閱”選項(xiàng)卡“更改”組的“保護(hù)工作簿”項(xiàng)選擇“保護(hù)結(jié)構(gòu)和窗口”。勾選“結(jié)構(gòu)”或“窗口”復(fù)選框保護(hù)結(jié)構(gòu): 不能插入、刪除工作表、更改工作表名稱等保護(hù)窗口: 保留窗口的大小及位置等,保護(hù)工作表: 保護(hù)工作表中數(shù)據(jù)不被任意修改鎖定+保護(hù)工作表：保護(hù)所有被鎖定的單元格。選擇“審閱”選項(xiàng)卡的“更改”組的“保護(hù)工作表”項(xiàng)，在對(duì)話框里輸入密碼在“保護(hù)工作表”對(duì)話框勾選所需的保護(hù)內(nèi)容保護(hù)工作表命令只對(duì)本工作表起作

18、用只允許用戶編輯指定單元格區(qū)域取消鎖定+保護(hù)工作表 選擇指定單元格，取消單元格的保護(hù)鎖定 此時(shí)只有可以被編輯，其它區(qū)域都被鎖住了隱藏公式隱藏+保護(hù)工作表,2. 共享工作簿共享工作簿：使用“審閱”選項(xiàng)卡的“更改”組選擇“共享工作簿”，打開(kāi)其對(duì)話框，勾選“允許多用戶…”復(fù)選框。共享工作簿+保護(hù)：以追蹤修訂方式共享：選擇“審閱”選項(xiàng)卡的“更改”組的“保護(hù)共享工作簿”項(xiàng)，打開(kāi)“保護(hù)共享工作簿”對(duì)話框。勾選

19、“以追蹤修訂方式共享”選項(xiàng)，輸入密碼突出顯示修訂: 當(dāng)數(shù)據(jù)被修改時(shí),像批注一樣標(biāo)示出,格式變了不標(biāo)示,———— 主要解決問(wèn)題：工作表工作簿關(guān)聯(lián)、引用數(shù)據(jù)區(qū)域,第3章 函數(shù)與公式基礎(chǔ),函數(shù)與公式學(xué)習(xí)方法————最有魅力的功能之一,初級(jí)階段：常用函數(shù)，如何填寫(xiě)參數(shù)；遇到了if函數(shù)，再遇到VLOOKUP函數(shù)（難：需要空間感、理解數(shù)據(jù)在不同方位的定位、查找和返回的過(guò)程）中級(jí)階段：?jiǎn)蝹€(gè)函數(shù)功能是有限的，多個(gè)函數(shù)的嵌套與組合才能完成比

20、較復(fù)雜的運(yùn)算高級(jí)階段：數(shù)組公式和多維引用,公式功能,計(jì)算建立數(shù)據(jù)之間的關(guān)聯(lián)單元格數(shù)據(jù)直接無(wú)關(guān)系各工作簿之間無(wú)關(guān)系各工作表之間無(wú)關(guān)系通過(guò)公式的邏輯關(guān)系，把它們關(guān)聯(lián)起來(lái)自動(dòng)重算原始數(shù)據(jù)的改變可以使用同一個(gè)計(jì)算模型,兩個(gè)重要思路,工作表、工作簿的邏輯關(guān)聯(lián)————公式建立EXCEL由行列數(shù)據(jù)構(gòu)成的，因此獲取所需的行列區(qū)域是要解決主要問(wèn)題—————數(shù)組、引用函數(shù)（OFFSET、ROW、COLUMN、INDEX、MATCH等

21、）,數(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）{=SMALL(IF(A1:B4>0,A1:B4),{1,2,3})}操作功能鍵：CTRL+SHIFT

22、+ENTER(完成的是多重計(jì)算),重新計(jì)算公式的時(shí)間和方式,自動(dòng)重新計(jì)算（默認(rèn)的設(shè)置）只有在公式所依賴的單元格發(fā)生更改第一次打開(kāi)工作簿以及編輯工作簿時(shí)——“Excel 選項(xiàng)”的“公式”類別的“計(jì)算選項(xiàng)”部分的“工作簿計(jì)算”下，單擊“自動(dòng)”除數(shù)據(jù)表外，自動(dòng)重算若要在每次更改值、公式或名稱時(shí)重新計(jì)算除數(shù)據(jù)表之外所有相關(guān)的公式手動(dòng)計(jì)算若要關(guān)閉自動(dòng)重新計(jì)算——單擊“手動(dòng)”時(shí)，Excel 將自動(dòng)選中“保存工作簿前重新計(jì)算”復(fù)選框。

23、如果保存工作簿需要很長(zhǎng)時(shí)間，那么清除“保存工作簿前重新計(jì)算”可縮短保存時(shí)間。,函數(shù)工具與技巧,公式復(fù)制拖曳填充柄雙擊填充柄（向下填充到鄰列第1個(gè)空單元格上方）選擇性粘貼公式函數(shù)工具提示 （選項(xiàng)設(shè)置）判斷參數(shù)是否可以省略（帶方括號(hào)的參數(shù)）逐步看計(jì)算結(jié)果F9: 當(dāng)選中單元格地址或函數(shù)時(shí)，在編輯欄顯示部分計(jì)算結(jié)果公式求值使用監(jiān)視窗口保護(hù)/隱藏工作表中的公式,函數(shù)分類,內(nèi)置函數(shù)——DateDif（）擴(kuò)展函數(shù)——通過(guò)加載宏

24、自定義函數(shù)例：隱藏內(nèi)置函數(shù)=NUMBERSTRING(1234567890,1)結(jié)果：一十二億三千四百五十六萬(wàn)七千八百九十 =NUMBERSTRING(1234567890,2)結(jié)果：壹拾貳億叁仟肆佰伍拾陸萬(wàn)柒仟捌佰玖拾 =NUMBERSTRING(1234567890,3)結(jié)果：一二三四五六七八九○ =DATESTRING("2008-8-8")08年08月08日 DATEDIF（）,公式分類,

25、普通公式數(shù)組公式命名公式=A1：A8=AVRAGER（DATA）,公式限制,公式內(nèi)容長(zhǎng)度不能超過(guò)1024個(gè)字符（2003）公式中函數(shù)嵌套不能超過(guò)7層（2003）將前6層定義名稱，然后引用公式中函數(shù)參數(shù)不能超過(guò)30使用括號(hào)括起多個(gè)參數(shù)，從而變成1個(gè)參數(shù)數(shù)字計(jì)算精度為15位單引號(hào)設(shè)置為文本型,名稱管理器,查看已有的名稱 查看名稱的引用范圍（“Refers to” 控件）適用范圍（“Scope” 欄）結(jié)果值（“Va

26、lue” 欄——注：錯(cuò)誤結(jié)果值也會(huì)顯示出來(lái)）確認(rèn)該名稱是否已在表格中使用（“In Use”欄） 創(chuàng)建名稱編輯已有的名稱 名稱可以重命名，不必再為了改名字而重新去定義一個(gè)名稱 可以很快地在編輯名稱對(duì)話框中修改名稱的適用范圍 快速刪除名稱 一次性選擇和刪除多個(gè)名稱名稱排序單擊欄標(biāo)題可以對(duì)名稱進(jìn)行排序 調(diào)整名稱對(duì)話框的大小根據(jù)需要調(diào)整refers-to box（引用范圍文本框）的寬度。這樣，名稱的可見(jiàn)程度僅僅取決于桌面窗

27、口的大小,使用名稱(6個(gè)原因),增強(qiáng)公式可讀性=單價(jià)*數(shù)量代替公式重復(fù)出現(xiàn)部分公式多次出現(xiàn)相同函數(shù)，使用名稱代替，簡(jiǎn)潔使用常量名稱代替單元格區(qū)域引用使用：=VLOOKUP（A1,等級(jí),2,1）作為條件格式或數(shù)據(jù)有效性序列跨表引用將此列數(shù)據(jù)命名為x,“來(lái)源”輸入：=x 宏表4.0函數(shù)在工作表中必須通過(guò)名稱調(diào)用2003版本的函數(shù)調(diào)用超過(guò)7層,使用名稱解決更多層,例:,在公式書(shū)寫(xiě)時(shí)，名稱是一個(gè)很實(shí)用的工具相對(duì)引用單元格區(qū)域

28、，在寫(xiě)公式的時(shí)候使用名稱不易出錯(cuò)，而且方便記憶（例如，用“Tax_Rate（稅率）” 而不用“G36”） 作用范圍工作簿級(jí)名稱（全局）含工作簿名稱工作表級(jí)名稱（局部）只含工作表名稱,篩選名稱,——快速顯示名稱子集（使用名稱管理器的“篩選”下拉列表）各選擇項(xiàng)含義：名稱擴(kuò)展到工作表范圍：只顯示 適用范圍為工作表的名稱名稱擴(kuò)展到工作簿范圍：只顯示工作簿范圍內(nèi)全局適用的名稱 有錯(cuò)誤的名稱 ：,只顯示值包含錯(cuò)誤（如 #RE

29、F、#VALUE 或 #NAME）的那些名稱沒(méi)有錯(cuò)誤的名稱：只顯示值不包含錯(cuò)誤的那些名稱 已定義名稱：只顯示由您或 Excel 定義的名稱，如打印區(qū)域表名稱：只顯示表名稱。,函數(shù)公式出錯(cuò)信息 （7個(gè)）#DIV/0——零作除數(shù)#NAME?——在公式中使用了不能識(shí)別的名稱刪除了公式中使用的名稱，或者使用了不存在的名稱。函數(shù)名的拼寫(xiě)錯(cuò)誤#VALUE！——使用了不正確的參數(shù)或運(yùn)算符在需要數(shù)字或邏輯值時(shí)輸入了文本#REF！

30、——引用了無(wú)效的單元格地址刪除了由其它公式引用的單元格將移動(dòng)單元格粘貼到由其它公式引用的單元格中。#NULL！——指定了兩個(gè)并不相交的區(qū)域，故無(wú)效使用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。,#N/A ——當(dāng)在函數(shù)或公式中引用了無(wú)法使用的數(shù)值內(nèi)部函數(shù)或自定義工作表函數(shù)中缺少一個(gè)或多個(gè)參數(shù)。使用的自定義工作表函數(shù)不存在。VLOOKUP（ ）函數(shù)中的查找值lookup_value、FALSE/TRUE參數(shù)指定了不正確的值域。

31、#NUM！——數(shù)字類型不正確在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)。由公式產(chǎn)生的數(shù)字太大或太?。涸?-10307和10307之間######！——輸入到單元格中的數(shù)值太長(zhǎng)，在單元格中顯示不下；單元格公式所產(chǎn)生的結(jié)果太長(zhǎng)，單元格容納不下；日期和時(shí)間產(chǎn)生了負(fù)值時(shí)將產(chǎn)生。,運(yùn)算符代替邏輯函數(shù),星號(hào)*代替“與”例: =IF（AND(A1>60,A160)*(A160),AND(B2="女",C2>

32、;55))等價(jià)于：=AND(B2=男",C2>60)+ AND(B2="女",C2>55)例2: =SUMPRODUCT((B2:B11={"江西","廣東"})*(C2:C11="男")*D2:D11) =SUMPRODUCT(((B2:B11="江西")+ (B2:B11="廣東“))*

33、(C2:C11="男")*D2:D11),不能用AND、OR代替*、+,原因：數(shù)組公式需要執(zhí)行多重計(jì)算，而AND、OR返回的是單值TRUE或FALSE，不能形成數(shù)組公式多區(qū)域之間的一一對(duì)應(yīng)關(guān)系。例：{=SUM（AND(C3:C7>70, C3:C770)*(C3:C7<80)= {TRUE;FALSE; TRUE; TRUE; TRUE}*{TRUE; TRUE; FALSE; TRUE; FALSE}

34、 ={1;0;1;1;1}*{1;1;0;1;0}={1;0;0;1;0} =sum ({1;0;0;1;0})=2,CELL函數(shù),CELL(info_type, [reference])功能：返回有關(guān)單元格的格式、位置或內(nèi)容的信息。info_type——必需。一個(gè)文本值，指定要返回的單元格信息的類型,邏輯函數(shù),AND函數(shù)——AND(logical1, [logical2], ...)功能：對(duì)參數(shù)值求并，邏輯與。log

35、ical1——必需。要檢驗(yàn)的第一個(gè)條件，其計(jì)算結(jié)果可以為 TRUE 或 FALSE。 logical2——可選。要檢驗(yàn)的其他條件，其計(jì)算結(jié)果可以為 TRUE 或 FALSE，最多可包含 255 個(gè)條件。OR函數(shù)——OR(logical1, [logical2], ...)功能：對(duì)參數(shù)值求或，邏輯或。logical1——必需。要檢驗(yàn)的第一個(gè)條件，其計(jì)算結(jié)果可以為 TRUE 或 FALSE。 logical2——可選。要檢驗(yàn)的其他

36、條件，其計(jì)算結(jié)果可以為 TRUE 或 FALSE，最多可包含 255 個(gè)條件。NOT函數(shù)——NOT(logical1)功能：對(duì)參數(shù)值求反，即邏輯非。Logical——為一個(gè)可以計(jì)算出 TRUE 或 FALSE 的邏輯值或邏輯表達(dá)式,SUMPRODUCT,功能：返回相應(yīng)幾組數(shù)組或區(qū)域乘積的和，若只有1組,則返回該數(shù)組之和。SUMPRODUCT（array1,[ array2],[ array3],…）Array1, array2

37、, array3, —— 為 1 到 255 個(gè)數(shù)組（數(shù)組參數(shù)必須具有相同的維數(shù)），其相應(yīng)元素需要進(jìn)行相乘并求和。注意: 括號(hào)問(wèn)題:含有=的值要加括號(hào),例: (B2:B11={“江西”,“廣東”})、(C2:C11="男")* 問(wèn)題: =SUMPRODUCT((B2:B11=“江西”)*1,(C2:C11=“男”)*1,(D2:D11)) ---，=SUMPRODUCT((B2:B11=“江

38、西”)*(C2:C11=“男”)*(D2:D11)) ----*,使用時(shí)特點(diǎn),參數(shù)時(shí)多個(gè)時(shí),大小必須相等若結(jié)果是0,是因?yàn)闆](méi)有轉(zhuǎn)換為數(shù)值型不需要CTRL+SHIFT+ENTER操作,它本身是數(shù)組公式替代{=SUM((B2:B11="一班")*(C2:C11>20))}=SUMPRODUCT((B2:B11="一班")*(C2:C11>20))免去*1: (兩個(gè)邏輯值數(shù)組*

39、運(yùn)算,直接轉(zhuǎn)換數(shù)值型)用 *: =SUMPRODUCT((B2:B11="一班")*C2:C11>20))=SUMPRODUCT((B2:B11="一班")*1,(C2:C11>20)*1),,第4章 引用與查找函數(shù),Offset(),功能：通過(guò)給定偏移量得到新的引用區(qū)域。 語(yǔ)法:OFFSET(reference，rows，cols，height，width)有5個(gè)參數(shù)：R

40、eference——基點(diǎn)或參照系，即引用區(qū)域左上角單元格；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),設(shè)置所需要的區(qū)域若結(jié)果為1個(gè)單元格,其值顯示在公式所在單元格往往用在單元格區(qū)域求和、平均、最大最小的

41、統(tǒng)計(jì)區(qū)域查找函數(shù)的范圍參數(shù),MATCH 函數(shù),功能:在單元格區(qū)域 中搜索指定項(xiàng)，然后返回該項(xiàng)在單元格區(qū)域中的相對(duì)位置。MATCH(lookup_value, lookup_array, [match_type])例如，如果單元格區(qū)域 A1:A3 包含值 5、25 和 38，則以下公式： =MATCH(25,A1:A3,0)會(huì)返回?cái)?shù)字 2，因?yàn)橹?25 是單元格區(qū)域中的第二項(xiàng)。match_type

42、0; 可選:-1:查找大于或等于 lookup_value 的最小值0 : 查找等于 lookup_value 的第一個(gè)值 1: (默認(rèn)值)查找小于或等于 lookup_value 的最大值,使用特點(diǎn),第2個(gè)參數(shù)：1行或1列數(shù)組是一個(gè)數(shù),表示查找值的行號(hào)或列號(hào)常常使用在Index函數(shù)的行號(hào)或列號(hào)OFFSET的參數(shù)（偏移量等）區(qū)別（解決同問(wèn)題）=INDEX(A1:D11,MATCH(F1,A1:A11,0)

43、,MATCH(G1,A1:D1,0)) =OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0)),INDEX,INDEX(array,row_num,column_num)功能：返回單元格或數(shù)組中的數(shù)據(jù)或元素值，此元素由行號(hào)和列號(hào)的索引值來(lái)給定。Array——單元格區(qū)域或數(shù)組常量。Row_num——數(shù)組中某行的行號(hào)，函數(shù)從該行返回?cái)?shù)值。如果省略 row_num，則必須有 column_n

44、um。Column_num——數(shù)組中某列的列標(biāo)，函數(shù)從該列返回?cái)?shù)值。如果省略 column_num，則必須有 row_num。,使用特點(diǎn),給出選定區(qū)域的行號(hào)或列號(hào)，獲取查找值多行多列單行單列常使用在指定行或列的查找,,ROW(reference)功能：返回引用的行號(hào)。Reference——需要得到其行號(hào)的單元格或單元格區(qū)域。COLUMN (reference)功能：返回引用的列號(hào)。Reference——需要得到其列

45、號(hào)的單元格或單元格區(qū)域。使用特點(diǎn)： 變化的特點(diǎn)：作為隨行/列變化的變量= COLUMN（），=ROW（） = COLUMN（A1），=ROW（A1） = COLUMN（A：G），=ROW（4：10）,,CHOOSE(index_num,value1,value2,...)功能：使用 index_num 返回?cái)?shù)值參數(shù)列表中的數(shù)值。Index_num——指定所選定的值參數(shù)。Index_num 必須為 1 到

46、 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í)行的操作。,LOOKUP,HLOOKUP(lookup_value,table_arr

47、ay,row_index_num,range_lookup)功能：在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并在表格或數(shù)組中指定行的同一列中返回一個(gè)數(shù)值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能：參數(shù)表示垂直方向LOOKUP(lookup_value,lookup_vector, result_vector)功能：向量形式:在單行區(qū)域或單列區(qū)域（稱

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

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

50、NTH($B2),DAY($B2))-TODAY())<=7,1）使用填充色標(biāo)記今天開(kāi)始7天要過(guò)生日的員工。,2）使用填充色標(biāo)記當(dāng)天要過(guò)生日的員工。,=AND(month($B2)=month(TODAY()),DAY($B2))=DAY(TODAY())),例1 : 生日提醒,例2：標(biāo)記重復(fù)值,選擇區(qū)域條件格式選擇“使用公式確定……”公式：=COUNTIF(A1:A$2,A1)>1設(shè)置填充色,例3：填充,國(guó)際象棋

51、棋盤(pán)=MOD(ROW()+COLUMN(),2)=0=MOD(ROW()+COLUMN(),2)=1奇偶行不同=MOD(ROW(),2)0動(dòng)態(tài)的間隔底紋=MOD(SUBTOTAL(3,A$2:A2),2)=0=MOD(SUBTOTAL(3,A$2:A2),2)=1,例4：比較不同區(qū)域數(shù)值,方法1： 條件格式的“只為包含以下內(nèi)容的單元格設(shè)置格式”項(xiàng)輸入公式：=A2，選擇“不等于方法2： （順序不同）選擇左側(cè)區(qū)域條

52、件格式的“使用公式確定要設(shè)置格式的單元格”項(xiàng)輸入公式=OR(EXACT(A2,B$2:B$11))=FALSE=NOT(OR(A2=B$2:B$11))（同理）,尤其體現(xiàn)在大區(qū)域數(shù)值的比較中,特點(diǎn): 1.復(fù)制:通過(guò)“選擇性粘貼”2 .數(shù)據(jù)有效性的設(shè)置僅對(duì)手工錄入有效，對(duì)復(fù)制粘貼輸入不生效例1: 輸入提框方法: “數(shù)據(jù)有效性”對(duì)話框中的“輸入信息”和“出錯(cuò)警告”選項(xiàng)卡，可以設(shè)置輸入提示和出錯(cuò)提示信息。例2: 標(biāo)識(shí)出已錄入數(shù)

53、據(jù)中不符合錄入范圍的數(shù)據(jù)： 先設(shè)置錄入數(shù)據(jù)范圍，再顯示出“公式審核”工具欄，單擊“圈釋無(wú)效數(shù)據(jù)”按鈕。,2.數(shù)據(jù)有效性的應(yīng)用,要求：按時(shí)序輸入日期方法：選擇區(qū)域，設(shè)置為日期格式數(shù)據(jù)有效性：自定義，公式：=N(A2)>=N(A1),例3：按日期順序輸入數(shù)據(jù),例4:為單元格設(shè)置下拉列表供錄入數(shù)據(jù)時(shí)選擇, 可以避免誤輸入數(shù)據(jù),,例5：切換不同數(shù)據(jù)列（數(shù)據(jù)有效性）,根據(jù)A1的輸入值1~3，可以在A2獲取由右側(cè)3列構(gòu)成的相應(yīng)下拉列

54、表數(shù)據(jù)方法：選擇D1：D11，定義名稱：List.1選擇A2設(shè)置數(shù)據(jù)有效性：序列、來(lái)源：=OFFSET（List.1 ,A1-1）,單字段排序多字段排序按列/行排序漢字筆畫(huà)排序按職務(wù)排序，順序?yàn)椤爸魅巍?gt;“科長(zhǎng)”>“職員”>“實(shí)習(xí)生”方法: 先“自定義序列”排序時(shí)選擇“自定義序列”,3. 排序的應(yīng)用,例1：按字符數(shù)量排序,方法：C列：C1輸入“字?jǐn)?shù)”C2輸入公式：=LEN（B2）排序： C

55、列升序刪除C列,例2：隨機(jī)排序,C列：輸入公式=RNAD（）按C列排序,例3：字母與數(shù)字混合排序,要求：先比較字母大小再比較數(shù)字大小方法：在B2輸入公式：=LEFT(A2,1) & RIGHT("000" & RIGHT(A2,LEN(A2)-1),3)復(fù)制到B3~B14對(duì)B列實(shí)現(xiàn)升序排序解釋數(shù)字部分變化為3位，不足的前面補(bǔ)0LEFT(A2,1)——取字母A~B單個(gè)字母，若多字母適當(dāng)