EXCEL2007實(shí)戰(zhàn)技巧(公式與函數(shù))

Excel實(shí)戰(zhàn)技巧精粹公式與函數(shù)申明本教程主要針對(duì)Excel中、高級(jí)用戶，內(nèi)容主要涉及EXCEL公式與函數(shù)的根本使用方法及技巧。本教程對(duì)應(yīng)使用office2007版本李宗堯2013年6月Content目錄1公式和函數(shù)基礎(chǔ)2文本處理3數(shù)學(xué)計(jì)算4日期和時(shí)間計(jì)算5查找與引用6統(tǒng)計(jì)與求和1.公式和函數(shù)根底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公式的組成要素序號(hào)公式說明1=15*3+20*2包含常量運(yùn)算的公式2=A1*3+A2*2包含單元格引用的公式3=SUM（A1*3，A2*2）包含函數(shù)的公式4=單價(jià)*數(shù)量包含名稱的公式表1-1公式的組成要素1.1認(rèn)識(shí)公式1.1.3公式的輸入、編輯與刪除3種方法進(jìn)入單元格編輯狀態(tài)選中公式所在單元格，并按下<F2>鍵。雙擊公式所在單元格。選中公式所在單元格，單擊列標(biāo)上方的編輯欄。使用<Del>鍵與<Backspace>鍵可去除單元格中的內(nèi)容。1.1認(rèn)識(shí)公式1.1.4公式的復(fù)制與填充5種方法實(shí)現(xiàn)公式的復(fù)制與填充方法一：拖拽填充。方法二：雙擊填充柄。方法三：快捷鍵填充。選中需要填充的區(qū)域，按<Ctrl+D>。方法四：選擇性粘貼。方法五：多單元格同時(shí)輸入。示例1.2公式中的運(yùn)算符1.2.1認(rèn)識(shí)運(yùn)算符符號(hào)說明實(shí)例-算術(shù)運(yùn)算符：負(fù)號(hào)=8*-5結(jié)果為-40%算術(shù)運(yùn)算符：百分號(hào)=60*5%結(jié)果為3^算術(shù)運(yùn)算符：乘冪=10^3

結(jié)果為1000*和/算術(shù)運(yùn)算符：乘和除=3*2/4結(jié)果為1.5+和-算術(shù)運(yùn)算符：加和減=3+2結(jié)果為5=，<>>,<>=,<=比較運(yùn)算符：等于，不等于，大于，小于，大于等于和小于等于，=（A1=A2）判斷A1與A2相等=（B1<>“ABC”）判斷B1不等于ABC=（C1>=5）判斷C1大于等于5&文本運(yùn)算符：連接文本=“CP”&“IC”返回“CPIC”:區(qū)域運(yùn)算符：冒號(hào)=SUM（A1：A10）引用A1至A10區(qū)域_(空格)交叉運(yùn)算符：?jiǎn)蝹€(gè)空格=SUM（A1：B5A4：D9）引用交叉區(qū)域，聯(lián)合運(yùn)算符：逗號(hào)=if（3+2=5，true，false）分隔參數(shù)表1-2公式中的運(yùn)算符1.2公式中的運(yùn)算符1.2.2數(shù)據(jù)比較的原那么數(shù)據(jù)類型示例文本姓名、性別、住址、職務(wù)、職稱……數(shù)值0、1、2、3、4、5、6……邏輯值TRUE、FALSE錯(cuò)誤值#VALUE！、#DIV/0!、#NAME！、#N/A、#REF！、#NULL！注意：

數(shù)字與數(shù)值是兩個(gè)不同的概念！示例表1-3EXCEL中的數(shù)據(jù)類型1.2公式中的運(yùn)算符1.2.3運(yùn)算符的優(yōu)先順序序號(hào)符號(hào)說明1：_（空格）,引用運(yùn)算符：冒號(hào)、單個(gè)空格和逗號(hào)2-算術(shù)運(yùn)算符：負(fù)號(hào)3%算術(shù)運(yùn)算符：百分號(hào)4^算術(shù)運(yùn)算符：乘冪5*和/算術(shù)運(yùn)算符：乘和除6+和-算術(shù)運(yùn)算符：加和減7&文本運(yùn)算符：連接文本8=,<,>,<=,>=,<>比較運(yùn)算符：比較兩個(gè)值表1-4運(yùn)算符的優(yōu)先級(jí)優(yōu)先級(jí)次1.3公式中的常量1.3.1常量參數(shù)公式中的5種常量數(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.1A1引用樣式和R1C1引用樣式A1引用樣式在默認(rèn)情況下，Excel使用A1引用樣式。即使用字母A~XFD表示列標(biāo)，使用數(shù)字1~1048576表示行號(hào)。R1C1引用樣式單擊【Office】按鈕，【Excel選項(xiàng)】，【公式】選項(xiàng)卡，勾選“R1C1引用樣式”，如以下圖所示：1.4單元格引用1.4.2相對(duì)引用、絕對(duì)引用和混合引用相對(duì)引用附屬單元格與引用單元格的相對(duì)位置不變。=A1絕對(duì)引用公式所引用的單元格絕對(duì)位置不變。=＄A＄1混合引用所引用單元格的行或列方向之一的絕對(duì)位置不變，而另一方向位置發(fā)生變化。示例1.5函數(shù)1.5.1函數(shù)的概念函數(shù)的結(jié)構(gòu)=IF（A1>0,”正數(shù)”,IF（A1<0,”負(fù)數(shù)”,”零”））函數(shù)名參數(shù)1參數(shù)2參數(shù)3注意：

當(dāng)使用函數(shù)作為另一個(gè)函數(shù)的參數(shù)時(shí)，成為函數(shù)的嵌套！1.5函數(shù)可選參數(shù)與必需參數(shù)函數(shù)名稱參數(shù)位置及名稱省略參數(shù)后的默認(rèn)情況IF第3個(gè)參數(shù)【value_if_false】默認(rèn)為FALSELOOKUP第3個(gè)參數(shù)【result_vector】默認(rèn)為數(shù)組語法MATCH第3個(gè)參數(shù)【match_type】默認(rèn)為1VLOOKUP第4個(gè)參數(shù)【range_lookup】默認(rèn)為TRUEHLOOKUP第4個(gè)參數(shù)【range_lookup】默認(rèn)為TRUEFIND第3個(gè)參數(shù)【start_num】默認(rèn)為1LEFT第2個(gè)參數(shù)【num_chars】默認(rèn)為1RIGHT第2個(gè)參數(shù)【num_chars】默認(rèn)為1SUBSTITUTE第4個(gè)參數(shù)【instance_num】默認(rèn)為替換所有符合第二個(gè)參數(shù)的字符SUNIF第3個(gè)參數(shù)【sum_range】默認(rèn)對(duì)第一個(gè)參數(shù)range進(jìn)行求和表1-5常用函數(shù)省略可選參數(shù)情況1.5函數(shù)常用函數(shù)的分類文本函數(shù)信息函數(shù)邏輯函數(shù)查找和引用函數(shù)日期和時(shí)間函數(shù)統(tǒng)計(jì)函數(shù)數(shù)學(xué)和三角函數(shù)數(shù)據(jù)庫函數(shù)財(cái)務(wù)函數(shù)工程函數(shù)多維數(shù)據(jù)集函數(shù)根據(jù)函數(shù)的功能和應(yīng)用領(lǐng)域，內(nèi)置函數(shù)可分為11個(gè)類別根據(jù)來源的不同，Excel函數(shù)可分為內(nèi)4類：內(nèi)置函數(shù)擴(kuò)展函數(shù)自定義函數(shù)宏表函數(shù)1.5函數(shù)函數(shù)的輸入和編輯1.5函數(shù)使用公式的常見問題錯(cuò)誤值類型含義######當(dāng)列寬不夠顯示數(shù)字，或者使用了負(fù)的日期、時(shí)間時(shí)，出現(xiàn)錯(cuò)誤#VALUE當(dāng)使用的參數(shù)或操作數(shù)類型錯(cuò)誤時(shí)，出現(xiàn)錯(cuò)誤#DIV/0！當(dāng)數(shù)字被零（0）除時(shí)，出現(xiàn)錯(cuò)誤#NAME？當(dāng)未識(shí)別公式中的文本時(shí)，如未加載宏或定義名稱，出現(xiàn)錯(cuò)誤#N/A當(dāng)數(shù)值對(duì)函數(shù)或公式不可用時(shí)，出現(xiàn)錯(cuò)誤#REF!當(dāng)單元格引用無效時(shí)，出現(xiàn)錯(cuò)誤#NUM!公式或函數(shù)中使用無效數(shù)字值時(shí)，出現(xiàn)錯(cuò)誤#NULL!當(dāng)用空格表示兩個(gè)引用單元格之間的相交運(yùn)算符，但指定并不相交的兩個(gè)區(qū)域的交點(diǎn)時(shí)，出現(xiàn)錯(cuò)誤表1-6常見錯(cuò)誤值及其含義1.6名稱1.6.1名稱的概念為什么要使用名稱增強(qiáng)公式的可讀性。方便公式的統(tǒng)一修改。代替需要重復(fù)使用的公式，以簡(jiǎn)化公式。代替單元格區(qū)域存儲(chǔ)常量數(shù)據(jù)。解決數(shù)據(jù)有效性和條件格式中無法使用常量數(shù)組、交叉引用、跨表引用等問題。1.6名稱名稱命名的限制不能以數(shù)字開頭。不能以R、C、r、c作為名稱名。不能使用除下劃線、點(diǎn)號(hào)、和反斜線以外的其它符號(hào)。字符不能超過255個(gè)。字母不區(qū)分大小寫。1.6名稱定義名稱的方法方法一：使用名稱管理器定義名稱方法一：使用名稱框快速創(chuàng)立名稱1.6名稱定義名稱的對(duì)象1使用合并區(qū)域引用和交叉引用2使用常量3使用常量數(shù)組4使用函數(shù)與公式示例２.文本處理２.1合并與比較文本２.2大小寫、全半角轉(zhuǎn)換２.3字符與編碼轉(zhuǎn)換２.4提取與查找字符２.5替換與清理字符２.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合并與比較文本合并文本使用文本合并運(yùn)算符“&”使用CONCATENATE函數(shù)使用PHONETIC函數(shù)示例２.1合并與比較文本文本值的大小比較示例使用比較運(yùn)算符“=”使用EXACT函數(shù)注意：用比較運(yùn)算符比照文本時(shí)具有以下特性區(qū)分全角與半角字符。區(qū)分文本型數(shù)字與數(shù)值，文本始終大于數(shù)值。不區(qū)分字母大小寫。２.2大小寫、半全角轉(zhuǎn)換大小字母轉(zhuǎn)換LOWER函數(shù)：將所有字母轉(zhuǎn)換為小寫字母。UPPER函數(shù)：將所有字母轉(zhuǎn)換為大寫字母。PROPER函數(shù)：將所有單詞轉(zhuǎn)換為首字母大寫的格式。示例２.2大小寫、半全角轉(zhuǎn)換全角半角字符轉(zhuǎn)換〔ASC、WIDECHAR〕全角字符：又稱雙音節(jié)字符，占用2個(gè)標(biāo)準(zhǔn)字符位置。半角字符：又稱單音節(jié)字符，占用1個(gè)標(biāo)準(zhǔn)字符位置。所有的漢字都是全角字符示例２.3字符與編碼轉(zhuǎn)換字符與編碼轉(zhuǎn)換函數(shù)〔CHAR、CODE〕ASCⅡ字符集B2312字符集BIG5字符集GB18030字符集Unicode字符集A〔65〕、a〔97〕=CHAR〔64+COLUMN〔A1〕〕=CHAR〔96+COLUMN〔A1〕〕２.4提取與查找字符常用提取字符函數(shù)LEFT函數(shù)：從左端提取制定數(shù)量字符。RIGHT函數(shù)：從右端提取制定數(shù)量字符。MID函數(shù)：從中間提取制定數(shù)量字符。示例提取身份證信息。分離中英文對(duì)照。２.5替換與清理字符常用替換字符函數(shù)SUBSTITUTE函數(shù)：將目標(biāo)文本中的制定字符串替換為新的字符串。SUBSTITUDE〔text,old_text,new_text,instance_num〕REPLACE函數(shù)：根據(jù)起始位置和文本字符數(shù)來替換為指定的新字符串。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ù)判斷奇偶性語法：MOD〔number，divisor〕，返回兩數(shù)相除的余數(shù)。例：MOD〔69,7〕，返回結(jié)果為7。示例3.2數(shù)值取舍函數(shù)常用取舍函數(shù)匯總函數(shù)名稱功能描述INT取整函數(shù)，將數(shù)字向下舍入為最接近的整數(shù)TRUNC將數(shù)字直接截尾取整，與數(shù)值符號(hào)無關(guān)ROUND將數(shù)字四舍五入到指定位數(shù)ROUNDUP將數(shù)朝遠(yuǎn)離0的方向舍入，即向上舍入ROUNDDOWN將數(shù)朝0的方向舍入，即向下舍去CELLING將數(shù)字向上舍入為最接近的整數(shù)，或最接近的指定基數(shù)的整數(shù)倍FLOOR將數(shù)字向下舍入為最接近的整數(shù)，或最接近的指定基數(shù)的整數(shù)倍EVEN將數(shù)字向上舍入為最接近的偶型整數(shù)ODD將數(shù)字向上舍入為最接近的奇型整數(shù)3.3四舍五入例如取舍函數(shù)應(yīng)用例如函數(shù)名稱示例INT=INT（-28.2）=-29TRUNC=TRUNC（-28.2）=-28ROUND=ROUND（1256.78，-2）=1300ROUNDUP=ROUNDUP（23.416,1）=23.5ROUNDDOWN=ROUNDDOWN（23.416,1）=23.4CELLING=CELLING（123.456，0.25）=123.50FLOOR=FLOOR（123.456，0.25）=123.25EVEN=EVEN（123.456）=124ODD=ODD（123.456）=1253.4隨機(jī)函數(shù)RAND函數(shù)與RANDBETWEEN函數(shù)RAND函數(shù)：產(chǎn)生0到1之間的隨機(jī)數(shù)；RANDBETWEEN函數(shù)：依據(jù)給定上限和下限產(chǎn)生隨機(jī)整數(shù)。隨機(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ù)如下函數(shù)名稱功能描述TODAY、NOW用于生產(chǎn)當(dāng)前日期和時(shí)間DATE根據(jù)指定的年份、月份和日期數(shù)返回具體的日期值YEAR、MONTH、DAY用于從日期數(shù)據(jù)中提取年份、月份和日期數(shù)YEARFRAC用于計(jì)算兩個(gè)日期序列相差的年數(shù)，結(jié)果以實(shí)數(shù)顯示EDATE根據(jù)指定月份數(shù)返回指定日期之前或之后的日期值EOMONTH從任意一個(gè)日期返回指定月份數(shù)之前或之后的月末日期WORKDAY根據(jù)源日期，按指定工作日天數(shù)返回之前或之后的日期NETWORKDAYS計(jì)算兩個(gè)日期之間的工作日天數(shù)4.1年月日函數(shù)

使用TODAY和NOW快速生成當(dāng)前日期和時(shí)間使用<Ctrl+；>和<Ctrl+Shift+；>組合鍵快速輸入當(dāng)前日期和時(shí)間，與使用函數(shù)生成的日期與時(shí)間有何異同？使用DATE函數(shù)生成指定日期=DATE〔year,month,day〕示例使用YEAR、MONTH和DAY提取年份、月份和日期4.2認(rèn)識(shí)DATEDIF函數(shù)功能描述：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)和年數(shù)語法：DATEDIF〔start_date,end_date,unit〕Unit參數(shù)含義如下：Unit代碼函數(shù)返回值“y”時(shí)間段中的年數(shù)“m”時(shí)間段中的月數(shù)“d”時(shí)間段中的天數(shù)“md”時(shí)間段中天數(shù)的差。忽略日期中的年和月“ym”時(shí)間段中月數(shù)的差。忽略日期中的年和日“yd”時(shí)間段中天數(shù)的差。忽略日期中的年示例4.3星期相關(guān)函數(shù)WEEKDAY函數(shù)：返回指定日期的星期值WEEKNUM函數(shù)：返回指定日期屬于全年的第幾周語法：WEEKDAY〔serial_number,[return_type]〕return_type說明：數(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函數(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〔lookup_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_v