1、Excel實(shí)戰(zhàn)技巧精粹,公式與函數(shù),申 明,本教程主要針對(duì)Excel中、高級(jí)用戶，內(nèi)容主要涉及EXCEL公式與函數(shù)的基本使用方法及技巧。 本教程對(duì)應(yīng)使用office2007版本 李宗堯 2013年6月,Content,目錄,1. 公式和函數(shù)基礎(chǔ),1.1認(rèn)識(shí)公式 1.2公式中的運(yùn)算符 1.3公式中的常量 1.4單元格引用 1.5函數(shù) 1.6名稱,1.1認(rèn)識(shí)公式,1.1.1公式的概念 1.1.2公式的組成要素,表1-1 公式的組成要素,1.1認(rèn)識(shí)公式,1.1.3公式的輸入、編輯與刪除,3種方法進(jìn)入單元格編輯狀態(tài) 選中公式所在單元格，并按下鍵。 雙擊公式所在單元格。 選中公式所在單元格，單擊列標(biāo)上方

2、的編輯欄。 使用鍵與鍵可清除單元格中的內(nèi)容。,1.1認(rèn)識(shí)公式,1.1.4公式的復(fù)制與填充,5種方法實(shí)現(xiàn)公式的復(fù)制與填充 方法一：拖拽填充。 方法二：雙擊填充柄。 方法三：快捷鍵填充。選中需要填充的區(qū)域，按。 方法四：選擇性粘貼。 方法五：多單元格同時(shí)輸入。,示例,1.2公式中的運(yùn)算符,1.2.1認(rèn)識(shí)運(yùn)算符,表1-2 公式中的運(yùn)算符,1.2公式中的運(yùn)算符,1.2.2數(shù)據(jù)比較的原則,注意： 數(shù)字與數(shù)值是兩個(gè)不同的概念！,示例,表1-3 EXCEL中的數(shù)據(jù)類型,1.2公式中的運(yùn)算符,1.2.3運(yùn)算符的優(yōu)先順序,表1-4 運(yùn)算符的優(yōu)先級(jí)優(yōu)先級(jí)次,1.3公式中的常量,1.3.1常量參數(shù),公式中的5種常量

3、 數(shù)值常量，如：=（5+8）*3。 日期常量，如：=DATEDIF（“2012-1-1”,NOW（），“m”）。 文本常量，如：=“國(guó)際”&“復(fù)合”。 邏輯值常量，如：=VLOOKUP（“張三豐”，A:B，2，F(xiàn)ALSE）。 錯(cuò)誤值常量，如：=COUNTIF（A:A，#DIV/0!）,注意： 邏輯值與文本型數(shù)字在運(yùn)算中會(huì)被自動(dòng)轉(zhuǎn)換為數(shù)值！,1.4單元格引用,1.4.1 A1引用樣式和R1C1引用樣式,A1引用樣式 在默認(rèn)情況下，Excel使用A1引用樣式。即使用字母AXFD表示列標(biāo)，使用數(shù)字11048576表示行號(hào)。 R1C1引用樣式 單擊【Office】按鈕， 【Excel選項(xiàng)】， 【公式】

4、選項(xiàng)卡，勾選“R1C1引 用樣式”，如下圖所示：,1.4單元格引用,1.4.2 相對(duì)引用、絕對(duì)引用和混合引用,相對(duì)引用 從屬單元格與引用單元格的相對(duì)位置不變。=A1 絕對(duì)引用 公式所引用的單元格絕對(duì)位置不變。=A1 混合引用 所引用單元格的行或列方向之一的絕對(duì)位置不變，而另一方向位置發(fā)生變化。,示例,1.5函數(shù),1.5.1函數(shù)的概念 1.5.2函數(shù)的結(jié)構(gòu),注意： 當(dāng)使用函數(shù)作為另一個(gè)函數(shù)的參數(shù)時(shí)，成為函數(shù)的嵌套！,1.5函數(shù),1.5.3可選參數(shù)與必需參數(shù),表1-5 常用函數(shù)省略可選參數(shù)情況,1.5函數(shù),1.5.4常用函數(shù)的分類,根據(jù)函數(shù)的功能和應(yīng)用領(lǐng)域，內(nèi)置函數(shù)可分為11個(gè)類別,根據(jù)來(lái)源的不同

5、，Excel函數(shù)可分為內(nèi)4類：,內(nèi)置函數(shù),擴(kuò)展函數(shù),自定義函數(shù),宏表函數(shù),1.5函數(shù),1.5.5函數(shù)的輸入和編輯,1.5函數(shù),1.5.6使用公式的常見問(wèn)題,表1-6 常見錯(cuò)誤值及其含義,1.6名稱,1.6.1名稱的概念 1.6.2為什么要使用名稱,增強(qiáng)公式的可讀性。 方便公式的統(tǒng)一修改。 代替需要重復(fù)使用的公式，以簡(jiǎn)化公式。 代替單元格區(qū)域存儲(chǔ)常量數(shù)據(jù)。 解決數(shù)據(jù)有效性和條件格式中無(wú)法使用常量數(shù)組、交叉引用、跨表引用等問(wèn)題。,1.6名稱,1.6.3名稱命名的限制,不能以數(shù)字開頭。 不能以R、C、r、c作為名稱名。 不能使用除下劃線、點(diǎn)號(hào)、和反斜線以外的其它符號(hào)。 字符不能超過(guò)255個(gè)。 字母不

6、區(qū)分大小寫。,1.6名稱,1.6.4定義名稱的方法,方法一：使用名稱管理器定義名稱,方法一：使用名稱框快速創(chuàng)建名稱,1.6名稱,1.6.5定義名稱的對(duì)象,示例,. 文本處理,.1合并與比較文本 .2大小寫、全半角轉(zhuǎn)換 .3字符與編碼轉(zhuǎn)換 .4提取與查找字符 .5替換與清理字符,.1合并與比較文本,2.1.1區(qū)分文本型數(shù)字與數(shù)值,文本值默認(rèn)左對(duì)齊 數(shù)值默認(rèn)右對(duì)齊 邏輯值與錯(cuò)誤值默認(rèn)居中 日期與時(shí)間默認(rèn)右對(duì)齊,數(shù)據(jù)默認(rèn)對(duì)齊方式,文本形式數(shù)字與數(shù)值導(dǎo)致查詢出錯(cuò),示例,.1合并與比較文本,2.1.2合并文本,使用文本合并運(yùn)算符 “&” 使用CONCATENATE函數(shù) 使用PHONETIC函數(shù),示例,.

7、1合并與比較文本,2.1.3文本值的大小比較,示例,使用比較運(yùn)算符 “=” 使用EXACT函數(shù),注意：用比較運(yùn)算符對(duì)比文本時(shí)具有以下特性 區(qū)分全角與半角字符。 區(qū)分文本型數(shù)字與數(shù)值，文本始終大于數(shù)值。 不區(qū)分字母大小寫。,.2大小寫、半全角轉(zhuǎn)換,2.2.1大小字母轉(zhuǎn)換,LOWER函數(shù)：將所有字母轉(zhuǎn)換為小寫字母。 UPPER函數(shù)：將所有字母轉(zhuǎn)換為大寫字母。 PROPER函數(shù)：將所有單詞轉(zhuǎn)換為首字母大寫的格式。,示例,.2大小寫、半全角轉(zhuǎn)換,2.2.2全角半角字符轉(zhuǎn)換（ASC、WIDECHAR）,全角字符：又稱雙音節(jié)字符，占用2個(gè)標(biāo)準(zhǔn)字符位置。 半角字符：又稱單音節(jié)字符，占用1個(gè)標(biāo)準(zhǔn)字符位置。 所

8、有的漢字都是全角字符,示例,.3字符與編碼轉(zhuǎn)換,2.3.1字符與編碼轉(zhuǎn)換函數(shù)（CHAR、CODE）,ASC字符集 B2312字符集 BIG5字符集 GB18030字符集 Unicode字符集 A（65）、a（97）,=CHAR（64+COLUMN（A1） =CHAR（96+COLUMN（A1）,.4提取與查找字符,2.4.1常用提取字符函數(shù),LEFT函數(shù)：從左端提取制定數(shù)量字符。 RIGHT函數(shù)：從右端提取制定數(shù)量字符。 MID函數(shù)：從中間提取制定數(shù)量字符。,示例,提取身份證信息。 分離中英文對(duì)照。,.5替換與清理字符,2.5.1常用替換字符函數(shù),SUBSTITUTE函數(shù)：將目標(biāo)文本中的制定字

9、符串替換為新的字符串。 SUBSTITUDE（text,old_text,new_text,instance_num） REPLACE函數(shù)：根據(jù)起始位置和文本字符數(shù)來(lái)替換為指定的新字符串。 REPLACE(old_text,start_num,num_chars,new_text),示例,1.如何將英文句子首字母轉(zhuǎn)換為大寫？ 2.如何清除字符串中間的空格？,.數(shù)學(xué)計(jì)算,3.1取余函數(shù) 3.2數(shù)值取舍函數(shù) 3.3四舍五入函數(shù) 3.4隨機(jī)函數(shù),3.1取余函數(shù),使用MOD函數(shù)判斷奇偶性 語(yǔ)法：MOD（number，divisor），返回兩數(shù)相除的余數(shù)。 例：MOD（69,7），返回結(jié)果為7。,示例,

10、3.2數(shù)值取舍函數(shù),常用取舍函數(shù)匯總,3.3四舍五入示例,取舍函數(shù)應(yīng)用示例,3.4隨機(jī)函數(shù),RAND函數(shù)與RANDBETWEEN函數(shù) RAND函數(shù)：產(chǎn)生0到1之間的隨機(jī)數(shù)； RANDBETWEEN函數(shù)：依據(jù)給定上限和下限產(chǎn)生隨機(jī)整數(shù)。,抽取試題,安排座位,生成密碼,生成模擬測(cè)試數(shù)據(jù),隨機(jī)函數(shù)用途,4. 日期和時(shí)間計(jì)算,4.1年月日函數(shù) 4.2認(rèn)識(shí)DATEDIF函數(shù) 4.3星期相關(guān)函數(shù) 4.4時(shí)間的計(jì)算,4.1年月日函數(shù),日期數(shù)據(jù)是一種數(shù)值的特殊表現(xiàn)形式，數(shù)值1代表1900-1-1。 日期是一個(gè)整數(shù)值，時(shí)間則是小數(shù)部分，數(shù)據(jù)0.5對(duì)應(yīng)時(shí)間序數(shù)12:00:00。,常用日期函數(shù)如下,4.1年月日函數(shù)

11、,4.1.1使用TODAY和NOW快速生成當(dāng)前日期和時(shí)間,使用和組合鍵快速輸入當(dāng)前日期和時(shí)間，與使用 函數(shù)生成的日期與時(shí)間有何異同？,4.1.2使用DATE函數(shù)生成指定日期,=DATE（year,month,day）,示例,4.1.3使用YEAR、MONTH和DAY提取年份、月份和日期,4.2認(rèn)識(shí)DATEDIF函數(shù),功能描述：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)和年數(shù) 語(yǔ)法：DATEDIF（start_date,end_date,unit） Unit參數(shù)含義如下：,示例,4.3星期相關(guān)函數(shù),WEEKDAY函數(shù)：返回指定日期的星期值 WEEKNUM函數(shù)：返回指定日期屬于全年的第幾周,語(yǔ)法：WEEKDAY

12、（serial_number,return_type） return_type說(shuō)明： 數(shù)字1 ：1至7 對(duì)應(yīng)星期天到數(shù)星期六（1可省略） 數(shù)字2 ：1至7 代表星期一到星期天（中國(guó)人的習(xí)慣） 數(shù)字3： 0至6代表星期一到星期天,示例,4.4時(shí)間的計(jì)算,TIME函數(shù)：用于按指定數(shù)字生成具體時(shí)間 HOUR函數(shù)：用于提取時(shí)間系列值中的小時(shí)數(shù) MINUTE函數(shù)：用于提取時(shí)間系列值中的分鐘數(shù) SECOND函數(shù)：用于提取時(shí)間系列值中的秒數(shù),5.查找與引用,5.1基本的查找函數(shù) 5.2常用的定位函數(shù) 5.3行號(hào)和列號(hào)函數(shù) 5.4特殊的查找函數(shù) 5.5認(rèn)識(shí)OFFSET函數(shù),5.1基本的查找函數(shù),VLOOKUP

13、函數(shù)：搜索查找范圍中的首列中滿足條件的數(shù)據(jù) VLOOKUP（lookup_value,table_array,col_index_num,rang_lookup） HLOOKUP函數(shù)：搜索查找范圍中的首行中滿足條件的數(shù)據(jù) HLOOKUP（lookup_value,table_array,row_index_num,rang_lookup）,示例,注意： rang_lookup參數(shù)為0或FALSE代表精確查找。 rang_lookup參數(shù)1或TRUE代表模糊匹配查找。 查找值與查找范圍的數(shù)據(jù)類型相匹配。,5.2常用的定位函數(shù),MATCH函數(shù)：確定查找值在查找范圍中的位置序號(hào) MATCH（look

14、up_value,lookup_array,match_type）,示例,判斷數(shù)據(jù)是否被重復(fù)錄入,5.3行號(hào)和列號(hào)函數(shù),ROW函數(shù)：返回單元格引用的行號(hào) COLUMN函數(shù)：返回單元格引用的列號(hào),示例,1.如何隔行設(shè)置背景顏色？ 2.與VLOOKUP函數(shù)共同設(shè)置數(shù)據(jù)查詢。,5.4特殊的查找函數(shù),LOOKUP函數(shù)：在查找范圍中查找用戶指定的查找值 LOOKUP（lookup_value,lookup_vector,result_vector）,示例,5.5 認(rèn)識(shí)OFFSET函數(shù),OFFSET函數(shù)：以指定的引用為參照，最終通過(guò)給定的偏移量 得到一個(gè)新的單元格引用或單元格區(qū)域引用 OFFSET（ref

15、erence,rows,cols,height,width） 如何設(shè)置動(dòng)態(tài)下拉菜單？,示例,6.統(tǒng)計(jì)與求和,6.1基本的計(jì)數(shù)函數(shù) 6.2條件統(tǒng)計(jì)類函數(shù) 6.3條件求和類函數(shù) 6.4極值應(yīng)用函數(shù) 6.5排名應(yīng)用,6.1基本的計(jì)數(shù)函數(shù),COUNT：對(duì)數(shù)值進(jìn)行統(tǒng)計(jì)，忽略錯(cuò)誤值、邏輯值及文本值 COUNTA：統(tǒng)計(jì)非空單元格的個(gè)數(shù) COUNTBLANK：統(tǒng)計(jì)數(shù)據(jù)表中的空單元格個(gè)數(shù),示例,6.2條件統(tǒng)計(jì)類函數(shù),COUNTIF：按指定條件（一個(gè)）對(duì)區(qū)域內(nèi)數(shù)值個(gè)數(shù)進(jìn)行統(tǒng)計(jì) Countif（range,criteria） COUNTIFS：按指定條件（多個(gè)）對(duì)區(qū)域內(nèi)數(shù)值個(gè)數(shù)進(jìn)行統(tǒng)計(jì) Countifs(criteria_range1, criteria1),示例,6.3條件求和類函數(shù),SUMIF：按指定條件（一個(gè)）對(duì)區(qū)域內(nèi)數(shù)值進(jìn)行求和 sumif（range,criteria,sum_range） SUMIFS：按指定條件（一個(gè)）對(duì)區(qū)域內(nèi)數(shù)值進(jìn)行求和 sumifs（Countifs(criteria_range1,