第二講 Excel應(yīng)用技巧(3、函數(shù)應(yīng)用)

1、第三章第三章 函數(shù)應(yīng)用函數(shù)應(yīng)用一、認(rèn)識(shí)函數(shù)l 函數(shù)是預(yù)先定義，執(zhí)行計(jì)算、分析等數(shù)據(jù)處理的特殊公函數(shù)是預(yù)先定義，執(zhí)行計(jì)算、分析等數(shù)據(jù)處理的特殊公式。式。l 公式是用戶(hù)自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的算式。公式是用戶(hù)自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的算式。l 函數(shù)公式結(jié)構(gòu)函數(shù)公式結(jié)構(gòu) 以以“=”開(kāi)始開(kāi)始)G1),:SUM(B10,IF(A1參數(shù)參數(shù)函數(shù)結(jié)構(gòu)函數(shù)結(jié)構(gòu)嵌套函數(shù)嵌套函數(shù)函數(shù)名稱(chēng)函數(shù)名稱(chēng)一、認(rèn)識(shí)函數(shù)l 函數(shù)的輸入函數(shù)的輸入使用工具欄按鈕使用插入函數(shù)向?qū)止ぽ斎雔 函數(shù)的編輯與復(fù)制函數(shù)的編輯與復(fù)制編輯：在編輯欄中修改或“函數(shù)參數(shù)”對(duì)話(huà)框中修改復(fù)制：雙擊、或拖動(dòng)填充柄、或復(fù)制粘貼進(jìn)行復(fù)制l 顯示

2、完整語(yǔ)法顯示完整語(yǔ)法在單元格中輸入函數(shù)時(shí)，按下Ctrl+Shift+A可得到包含該函數(shù)完整語(yǔ)法的公式l 公式與結(jié)果切換公式與結(jié)果切換Ctrl+()(鍵盤(pán)左側(cè)的)?；颉肮ぞ摺?，“選項(xiàng)”命令，“視圖”選項(xiàng)卡設(shè)置“窗口選項(xiàng)”欄下的“公式”項(xiàng)有效“確定” 一、認(rèn)識(shí)函數(shù)l神奇的神奇的F9鍵鍵在編輯欄選中公式中需要顯示計(jì)算結(jié)果的部分（即用光標(biāo)“抹黑”），按下F9，即可在編輯欄顯示該部分的計(jì)算結(jié)果，這樣可以進(jìn)行部分公式預(yù)演，以便檢查錯(cuò)誤另外，使用F9鍵還可以將單元格引用轉(zhuǎn)換為常量數(shù)組。例如，A1、B1中分別輸入字符串”Word”、”Excel”，C1中輸入公式=A1:B1，抹黑公式中的A1:B1，按下F9，

3、公式則變成=“Word”,”Excel”一、認(rèn)識(shí)函數(shù)l 保護(hù)和隱藏工作表中的公式保護(hù)和隱藏工作表中的公式1. 按F5鍵調(diào)出“定位”對(duì)話(huà)框，單擊“定位條件”，選“公式”項(xiàng)，確定，將選中工作表中所有包含公式的單元格2. “格式”“單元格”，彈出“單元格格式”，“保護(hù)”中勾選“鎖定”和“隱藏”，確定3. “工具”“保護(hù)”“保護(hù)工作表”，如果需要可設(shè)置密碼，確定二、函數(shù)基礎(chǔ)l 單元格的引用方法單元格的引用方法A1引用樣式引用樣式：默認(rèn)情況下，Excel使用A1引用樣式。如“C5”。若引用整行或整列，可省去列標(biāo)或行號(hào)，如“1:1”表示第一行；“A:A”表示A列R1C1引用樣式引用樣式：“工具”“選項(xiàng)”“

4、常規(guī)”中勾選“R1C1引用樣式”，可以使用“R”與數(shù)字的組合來(lái)表示行號(hào)，“C”與數(shù)字的組合則表示列標(biāo)。此樣式能更直觀地體現(xiàn)單元格的“坐標(biāo)”概念A(yù)1樣式與樣式與R1C1引用類(lèi)型的區(qū)別引用類(lèi)型的區(qū)別A1樣式 R1C1樣式A1R*C*$A$1R1C1A$1R1C*$A1R*C1 *代表數(shù)值二、函數(shù)基礎(chǔ)l單元格的引用方法單元格的引用方法三維引用三維引用l一維引用：同一行或列，如A1:D1或A1:A5l二維引用：同一工作表中的一個(gè)區(qū)域，如A1:C5l三維引用： =SUM(sheet1:sheet2!A1:C5)，或者=Sheet1!A1:C5+Sheet2!A1:C5。為跨工作表引用l快速切換引用類(lèi)型快

5、速切換引用類(lèi)型相對(duì)引用、絕對(duì)引用、混合引用相對(duì)引用、絕對(duì)引用、混合引用l相對(duì)引用相對(duì)引用：某一單元格相對(duì)于當(dāng)前單元格的相對(duì)位置。如：=d10l絕對(duì)引用絕對(duì)引用：某一單元格在工作表中的絕對(duì)位置，行列標(biāo)前加“$”。如：=$A$1l混合引用混合引用：如：=C$8、=$C8不同類(lèi)型間快速切換不同類(lèi)型間快速切換：編輯欄選中A1，按F4二、函數(shù)基礎(chǔ)l數(shù)據(jù)類(lèi)型區(qū)分及轉(zhuǎn)換數(shù)據(jù)類(lèi)型區(qū)分及轉(zhuǎn)換數(shù)據(jù)類(lèi)型及排序規(guī)則數(shù)據(jù)類(lèi)型及排序規(guī)則：數(shù)據(jù)類(lèi)型可分為文本、數(shù)值、日期、邏輯、錯(cuò)誤等。數(shù)值文本0,A1=10) 或（或（OR關(guān)系）關(guān)系）：多個(gè)條件只要有一個(gè)成立就判定為真，如：=OR(A160,B160,C160) 非（非（N

6、OT關(guān)系）關(guān)系）：條件只要成立就判定為假，如：=NOT(A1=“碩士”)，或者：=A1”碩士”，篩選學(xué)歷為碩士以外的記錄 邏輯關(guān)系的嵌套邏輯關(guān)系的嵌套：如：=IF(A1=60,A1=80,A1=90,A1=100),”優(yōu)”)。此公式有多個(gè)冗余判斷，可整理為：=IF(A160,”不及格”,IF(A180,”及格”,IF(A190”良”,”優(yōu)”)二、函數(shù)基礎(chǔ)l運(yùn)算符號(hào)運(yùn)算符號(hào)算術(shù)運(yùn)算符算術(shù)運(yùn)算符：+、-、*、/、% 比較運(yùn)算符比較運(yùn)算符：、=、= 文本運(yùn)算符文本運(yùn)算符：& 引用運(yùn)算符引用運(yùn)算符：區(qū)域(:)、聯(lián)合（或合并）(,)、交叉 (單個(gè)空格) 運(yùn)算順序運(yùn)算順序：從左到右；如果同時(shí)用到多

7、個(gè)運(yùn)算符，則：引用運(yùn)算符算術(shù)運(yùn)算符（負(fù)號(hào)(-)(%)乘冪()乘(*)和除(/)加(+)和減(-)）文本運(yùn)算符比較運(yùn)算符通配符通配符l*表示任何字符，如：=COUNTIF(A1:A10,”A*”)l?表示任何單個(gè)字符，如：=COUNTIF(A1:A10,”?A*”)l用于解除字符的通配性，即當(dāng)要查找或統(tǒng)計(jì)的是*、?或本身時(shí)，須在其前面加上，以通知Excel不將后面所跟的*、?、作為通配符處理三、函數(shù)技巧1.數(shù)據(jù)庫(kù)函數(shù)（數(shù)據(jù)庫(kù)函數(shù)（03中有中有13個(gè)）個(gè)）2.日期與時(shí)間函數(shù)（日期與時(shí)間函數(shù)（20個(gè)）個(gè)）3.外部函數(shù)（外部函數(shù)（2個(gè)）個(gè)）4.工程函數(shù)（工程函數(shù)（39個(gè)）個(gè)）5.財(cái)務(wù)函數(shù)（財(cái)務(wù)函數(shù)（5

8、2個(gè)）個(gè)）6.信息函數(shù)（信息函數(shù)（9個(gè)）個(gè)）7.邏輯運(yùn)算符（邏輯運(yùn)算符（6個(gè)）個(gè)）8.查找與引用函數(shù)（查找與引用函數(shù)（17個(gè)）個(gè)）9.數(shù)學(xué)與三角函數(shù)（數(shù)學(xué)與三角函數(shù)（60個(gè)）個(gè)）10. 統(tǒng)計(jì)函數(shù)（統(tǒng)計(jì)函數(shù)（80個(gè)）個(gè)）11. 文本與數(shù)據(jù)函數(shù)（文本與數(shù)據(jù)函數(shù)（28個(gè)）個(gè)）三、函數(shù)技巧l 文本處理 字符轉(zhuǎn)換字符轉(zhuǎn)換l 英文字母大小寫(xiě)轉(zhuǎn)換英文字母大小寫(xiě)轉(zhuǎn)換：=UPPER(A1)，A1中的字母全部大寫(xiě)；=LOWER(A1)，A1中的字母全部小寫(xiě)；=PROPER(A1)，A1中的英文單詞首字母大寫(xiě)，其余的小寫(xiě)l 半角與全角轉(zhuǎn)換半角與全角轉(zhuǎn)換：=ASC(A1)，A1中的全角字符返回半角字符；=WIDECH

9、AR(A1)，A1中的半角字符返回全角字符（例子1）三、函數(shù)技巧l 文本處理 制作文本直方圖制作文本直方圖1. A2:C13存放每月計(jì)劃進(jìn)度和實(shí)際進(jìn)度數(shù)據(jù)，D2輸入公式：=IF(B20,(C2-B2)/B2,0)，并設(shè)置為百分比格式得到進(jìn)度對(duì)比數(shù)據(jù)2. 在F2、H2中分別輸入公式：=IF(D20,REPT(“”,ROUND(D2*100,0),”) ，可將D列的每個(gè)百分點(diǎn)轉(zhuǎn)為一個(gè)小方塊3. 將F2、H2的公式向下填充，設(shè)置FH列的填充色、字體顏色、對(duì)齊方式。 （例子2）按給定次數(shù)重復(fù)顯示文本按給定次數(shù)重復(fù)顯示文本按指定位數(shù)四舍五入按指定位數(shù)四舍五入三、函數(shù)技巧l 文本處理 文本合并符號(hào)文本合并

10、符號(hào)“&”的妙用的妙用l 連接名字與姓氏連接名字與姓氏：=A1&” ”&B1l 連接注釋文字與計(jì)算結(jié)果連接注釋文字與計(jì)算結(jié)果：=“農(nóng)學(xué)院”&MONTH(NOW()&”月考勤表”l 去除引用空單元格返回的去除引用空單元格返回的0：=IF(A1=“”,”,A1)或者：=A1&”。不過(guò)后一個(gè)公式得到的結(jié)果是文本l 合并計(jì)算條件合并計(jì)算條件：如A列為產(chǎn)品名稱(chēng)，B列為產(chǎn)品規(guī)格，C列為數(shù)量，如果要按單品單規(guī)單品單規(guī)來(lái)統(tǒng)計(jì)數(shù)量，則引入輔助D列，并在D2中輸入：=A2&B2，最后在E列輸入條件求和公式：=SUMIF(D:D,D2,C:C)。（例子3）三、

11、函數(shù)技巧l 文本處理 解讀身份證編碼信息解讀身份證編碼信息l 提取籍貫地區(qū)信息提取籍貫地區(qū)信息：先根據(jù)編碼（身份證前6位）與省份地區(qū)的資料建立一個(gè)索引表，就可以簡(jiǎn)單地用查找引用函數(shù)（如VLOOKUP）來(lái)提取身份證中的地區(qū)信息l 提取生日提取生日：=IF(A1,-TEXT(MID(A1,7,IF(LEN(A1)=15,6,8),#-00-00),)l 提取性別提取性別：=IF(A1,IF(MOD(RIGHT(LEFT(A1,17),2),“男”,“女”),) （例子4）在表格或數(shù)值數(shù)組的首列查找指定的在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行數(shù)值，并由此返回表格或數(shù)組當(dāng)前行

12、中指定列處的數(shù)值中指定列處的數(shù)值返回文本串中從指定位置返回文本串中從指定位置開(kāi)始的特定數(shù)目的字符開(kāi)始的特定數(shù)目的字符返回文本串的字符數(shù)返回文本串的字符數(shù)返回兩數(shù)相除的余數(shù)返回兩數(shù)相除的余數(shù)三、函數(shù)技巧l 日期與時(shí)間計(jì)算 日期格式代碼日期格式代碼l Excel的日期表示的日期表示：默認(rèn)1900年1月1日9999年12月31日之間的序列值，是數(shù)值的特殊形式l 日期的輸入方式日期的輸入方式：以“-”或“/”間隔數(shù)值l 3個(gè)隱秘的日期格式個(gè)隱秘的日期格式：輸入2008-8-8格式代碼顯示效果備注e20084位年份b512位佛歷年份bbbb25514位佛歷年份dbnum1yyyy年m月d日 二八年八月八

13、日dbnum2yyyy年m月d日 貳零零捌年捌月捌日三、函數(shù)技巧l 日期與時(shí)間計(jì)算 文本轉(zhuǎn)換為日期文本轉(zhuǎn)換為日期l 連續(xù)數(shù)字的日期：如：A2=20080808=-TEXT(A2,”#-00-00”)，顯示為2008-8-8l 半角句點(diǎn)間隔的日期：如：A2=2008.8.8=-SUBSTITUTE(A2,”.”,”-”)，顯示為2008-8-8 （例子6） 日期轉(zhuǎn)換為文本日期轉(zhuǎn)換為文本l 如：A2為2008-8-8=TEXT(A2,”yyyymmdd”)，或=TEXT(A2,”emmdd”)，顯示為20080808l 若： =TEXT(A2,”yyyy.m.d”)，或=TEXT(A2,”e.m.

14、d”)，則顯示為2008.8.8在文字串在文字串中用中用new_text 替代替代old_text三、函數(shù)技巧l 日期與時(shí)間計(jì)算 英文月份轉(zhuǎn)換為數(shù)字英文月份轉(zhuǎn)換為數(shù)字l 如：A2為”August”或”Aug”，=MONTH(-(A2&1)，顯示為“8”（例子7）三、函數(shù)技巧l 日期與時(shí)間計(jì)算 計(jì)算指定間隔的日期計(jì)算指定間隔的日期l 計(jì)算間隔多少天的日期：如：此日后12天的日期：=A2+12l 計(jì)算下個(gè)月的日期：=DATE(YEAR(A2),MONTH(A2)+1,DAY(A2)l 閏年判斷：=IF(MONTH(DATE(A2,2,29)=2,”閏年”,”平年”)（例子8）三、函數(shù)技巧l

15、 日期與時(shí)間計(jì)算 返回月末的日期返回月末的日期l 返回本月末最后一天的日期：=DATE(YEAR(NOW(),MONTH(NOW()+1,0)或=EOMONTH(NOW(),0)（此函數(shù)需要加載宏分析工此函數(shù)需要加載宏分析工具庫(kù)）具庫(kù)）l 返回上個(gè)月月末的日期：=TODAY()-DAY(NOW()l 返回當(dāng)月的天數(shù)：=DAY(EOMONTH(NOW(),0)l 得到上月的天數(shù)：=DAY(TODAY()-DAY(NOW()（例子9）三、函數(shù)技巧l 日期與時(shí)間計(jì)算 計(jì)算年齡、月齡、日齡計(jì)算年齡、月齡、日齡l =DATEDIF(前一日期（如生日）前一日期（如生日）,當(dāng)前日期當(dāng)前日期, “y”)。公式

16、中“y”為實(shí)足年，用“m”為實(shí)足月，用“d”為實(shí)足日，用“ym”為不足一年的月數(shù)，用“md”為不足一月日數(shù)（例子10） 三、函數(shù)技巧l 日期與時(shí)間計(jì)算 計(jì)算一個(gè)日期對(duì)應(yīng)的星期一個(gè)日期對(duì)應(yīng)的星期l 方法方法1： A1輸入輸入“1998-4-1” “格式格式”單元格（右單元格（右擊擊設(shè)置單元格格式）設(shè)置單元格格式）數(shù)字選項(xiàng)卡數(shù)字選項(xiàng)卡“分類(lèi)分類(lèi)”中的中的日日期期；“類(lèi)型類(lèi)型”中的中的星期三星期三確定確定l 方法方法2：=WEEKDAY(A1,2)，返回?cái)?shù)字表示的星期；，返回?cái)?shù)字表示的星期；函數(shù)中的參數(shù)函數(shù)中的參數(shù)“2”表示數(shù)字表示數(shù)字17代表星期一代表星期一日，若參日，若參數(shù)為數(shù)為“1”或省略表示

17、數(shù)字或省略表示數(shù)字17代表星期日代表星期日六六l 方法方法3：=MOD(A1-2,7)+1，返回?cái)?shù)字表示的星期，返回?cái)?shù)字表示的星期l 方法方法4：=TEXT(A1,”dddd”)（英文）（英文）或：或：=TEXT(A1,”aaaa“)（中文）（返回的是文本的星（中文）（返回的是文本的星期）期）（例子（例子11）三、函數(shù)技巧l 日期與時(shí)間計(jì)算 計(jì)算一個(gè)日期是一年中的第幾周一個(gè)日期是一年中的第幾周l =WEEKNUM(A1,1)。（。（1表示周日為一周開(kāi)始，表示周日為一周開(kāi)始，2為為周一為一周開(kāi)始）周一為一周開(kāi)始）三、函數(shù)技巧l 日期與時(shí)間計(jì)算 計(jì)算兩個(gè)日期之間的天數(shù)兩個(gè)日期之間的天數(shù)l A1、B

18、1分別輸入日期分別輸入日期C1輸入輸入“=B1-A1” 格式格式單元格格式單元格格式“數(shù)字?jǐn)?shù)字”選項(xiàng)卡的選項(xiàng)卡的“分類(lèi)分類(lèi)”列表框中選擇列表框中選擇“常規(guī)常規(guī)”確定。確定。l =NETWORKDAYS(start_date,end_date,holidays)（參數(shù)分別引用單元格中的日期或雙引號(hào)引住的日期；（參數(shù)分別引用單元格中的日期或雙引號(hào)引住的日期；此函數(shù)需要加載宏分析工具庫(kù)）可計(jì)算工作日數(shù)（周此函數(shù)需要加載宏分析工具庫(kù)）可計(jì)算工作日數(shù)（周一到周五）一到周五）（例子（例子12）三、函數(shù)技巧l數(shù)學(xué)與三角計(jì)算常用數(shù)值舍入計(jì)算l按指定小數(shù)位數(shù)舍入按指定小數(shù)位數(shù)舍入ROUND(數(shù)值數(shù)值,指定小數(shù)位

19、數(shù)指定小數(shù)位數(shù))，四舍五入，四舍五入ROUNDUP(數(shù)值數(shù)值,指定小數(shù)位數(shù)指定小數(shù)位數(shù))，不為，不為0數(shù)值進(jìn)一數(shù)值進(jìn)一ROUNDDOWN(數(shù)值數(shù)值,指定小數(shù)位數(shù)指定小數(shù)位數(shù))，指定小數(shù)位數(shù)右邊的數(shù)值，指定小數(shù)位數(shù)右邊的數(shù)值全部舍去全部舍去l按指定基數(shù)倍數(shù)舍入按指定基數(shù)倍數(shù)舍入CEILING(number,significance)（向上舍入運(yùn)算）（向上舍入運(yùn)算）FLOOR(number,significance) （向下舍入運(yùn)算）（向下舍入運(yùn)算）l數(shù)值取整數(shù)值取整INT(number)，只返回整數(shù)，且按絕對(duì)值增大方向舍去，只返回整數(shù)，且按絕對(duì)值增大方向舍去TRUNC(number,num_digits)，根據(jù)指定小數(shù)位數(shù)進(jìn)行截位運(yùn)算，根據(jù)指定小數(shù)位數(shù)進(jìn)行截位運(yùn)算，與與ROUNDDOWN功能相似。功能相似。（例子（例子13）三、函數(shù)技巧l 數(shù)學(xué)與三角計(jì)算 余數(shù)的妙用l 判斷奇偶性：判斷奇偶性：=IF(MOD(A2,2),”奇數(shù)奇數(shù)”,”偶數(shù)偶數(shù)”)l 判斷星期幾：判斷星期幾：=MOD(A2-1,7)，06表示星期日表示星期日六六l 還可用于隔行（列）計(jì)算、條