第四章excel(公式及函數(shù))

第4章Excel電子表格教學(xué)內(nèi)容:Excel公式與函數(shù)學(xué)習(xí)重點(diǎn):公式與函數(shù)的復(fù)制、常用函數(shù)學(xué)習(xí)方法:看書(shū)、上機(jī)操作、課后練習(xí)授課時(shí)數(shù):4學(xué)時(shí)應(yīng)用公式邏輯函數(shù)公式編輯Excel公式與函數(shù)使用函數(shù)綜合練習(xí)數(shù)學(xué)函數(shù)統(tǒng)計(jì)函數(shù)日期與時(shí)間函數(shù)文本函數(shù)財(cái)務(wù)函數(shù)其他函數(shù)應(yīng)用公式公式介紹公式類型數(shù)值公式字符公式邏輯公式引用運(yùn)算公式中各運(yùn)算符的優(yōu)先級(jí)公式介紹與數(shù)學(xué)公式類似，對(duì)數(shù)據(jù)進(jìn)行各種四則運(yùn)算。以“=”開(kāi)頭，后面接各種表達(dá)式例:求計(jì)算機(jī)、英語(yǔ)和數(shù)學(xué)三門功課的總分輸入方法:在單元格中輸入，“回車”確認(rèn)在編輯欄內(nèi)輸入并確認(rèn)公式類型數(shù)值公式:對(duì)數(shù)字或單元格內(nèi)的數(shù)字進(jìn)行計(jì)算。(包括日期運(yùn)算)字符公式:對(duì)字符或單元格內(nèi)的字符進(jìn)行合并運(yùn)算。邏輯公式:進(jìn)行比較運(yùn)算，用于構(gòu)造條件。數(shù)值公式算術(shù)運(yùn)算符:+，－，*，/，^，%例:求總分總分＝平時(shí)成績(jī)*30%+期末成績(jī)*70%求給定數(shù)(如:2)的立方注意：EXCEL中使用的標(biāo)點(diǎn)符號(hào)都是英文狀態(tài)的標(biāo)點(diǎn)符號(hào)字符公式文本運(yùn)算符:用“&”進(jìn)行字符的連接運(yùn)算。例:產(chǎn)生1月份~12月份說(shuō)明:對(duì)于字符常量要加雙引號(hào)，單元格或區(qū)域內(nèi)的數(shù)值數(shù)據(jù)可直接引用。邏輯公式比較運(yùn)算符:>,<,>=,<=,=,<>例:比較兩門功課考試分?jǐn)?shù)的高低。說(shuō)明:邏輯公式均用在條件判斷式中，產(chǎn)生結(jié)果TRUE或FALSE。引用運(yùn)算引用運(yùn)算符:區(qū)域運(yùn)算符(:)，交叉運(yùn)算符(空格)，聯(lián)合運(yùn)算符(,)。例:count()A1:C1A1,C1A1:D4D4:F7各運(yùn)算符的優(yōu)先級(jí)公式處理復(fù)雜數(shù)據(jù)時(shí)，用到的多個(gè)運(yùn)算符的優(yōu)先級(jí)是不同的。數(shù)學(xué)運(yùn)算符>字符串運(yùn)算符>比較運(yùn)算符默認(rèn):根據(jù)公式中運(yùn)算符的特定順序從左到右計(jì)算公式。Q:如何更改求值的順序？公式編輯公式的復(fù)制和移動(dòng)單元格地址引用不同引用在公式復(fù)制時(shí)的變化確定公式的引用形式使用公式出錯(cuò)信息及原因公式的復(fù)制和移動(dòng)介紹公式通常引用單元格計(jì)算數(shù)據(jù)，當(dāng)對(duì)公式進(jìn)行復(fù)制時(shí)就不是一般數(shù)據(jù)的簡(jiǎn)單復(fù)制。例:求出所有同學(xué)的總分。例:求出所有女同學(xué)的總分。方法一:填充句柄方法二:粘貼方法三:選擇性粘貼單元格地址引用介紹EXCEL中單元格地址有不同的表示方法，可以直接用相應(yīng)的地址表示。單元格地址引用類型(三大地址)相對(duì)地址:如A3,A4:A8等形式；絕對(duì)地址:如$A$3,$a$4:$A$4等形式；混合地址:如$A3,A$3等形式。引用轉(zhuǎn)換:F4不同的引用方式只有在公式復(fù)制時(shí)有不同的作用。不同引用在公式復(fù)制時(shí)的變化公式單元格(A1)目的單元格(C10)A1=B1+C1C10=D10+E10A1=$B$1+$C$1C10=$B$1+$C$1A1=$B1+C$1C10=$B10+E$1確定公式的引用形式例:利用公式復(fù)制產(chǎn)生九九乘法表準(zhǔn)備:在B35:J35中輸入了9個(gè)數(shù)1~9在A36:A44中輸入了9個(gè)數(shù)1~9要求:在B2單元格中輸入一個(gè)公式，然后將該公式復(fù)制產(chǎn)生九九乘法表使用公式出錯(cuò)信息及原因出錯(cuò)信息說(shuō)明#DIV/0!公式中出現(xiàn)0作除數(shù)，可能是空單元格作除數(shù)引起的。#NAME?引用了Excel不能識(shí)別的文本，輸入錯(cuò)誤或使用了未定義的名稱#NULL!在不相交的區(qū)域中指定了一個(gè)交集#NUM!在公式或函數(shù)中使用了不適當(dāng)?shù)臄?shù)字，如不在函數(shù)定義域中的數(shù)字#REF!單元格引用無(wú)效，如引用的單元格不存在#VALUE!錯(cuò)誤的參數(shù)或運(yùn)算對(duì)象，如數(shù)據(jù)的類型出錯(cuò)######數(shù)值長(zhǎng)度超過(guò)了單元格列寬使用函數(shù)函數(shù)基礎(chǔ)使用函數(shù)舉例函數(shù)的結(jié)構(gòu)函數(shù)基礎(chǔ)函數(shù)是一些預(yù)定義的公式，方便用戶使用。函數(shù)運(yùn)算以“=”開(kāi)頭。函數(shù)輸入有兩種方法:手工直接輸入法粘貼函數(shù)法函數(shù)的結(jié)構(gòu)等號(hào)表示要輸入一個(gè)公式或函數(shù)進(jìn)行計(jì)算函數(shù)名左右括號(hào)表示函數(shù)的開(kāi)始和結(jié)束參數(shù)＝SUM(a1,25,”25”,b5:d7)說(shuō)明:

參數(shù)的個(gè)數(shù)可以是0個(gè)或多個(gè)不管函數(shù)是否有參數(shù)，其括號(hào)()不能少參數(shù)可以是具體值、含有值的單元格地址或區(qū)域地址形式等。使用函數(shù)舉例例:求三門功課的總分利用求和函數(shù):SUM()輸入函數(shù):直接輸入法:＝SUM(B31:D31)利用函數(shù)向?qū)?粘貼函數(shù)法)數(shù)學(xué)與三角函數(shù)ABS()函數(shù)INT()函數(shù)PI()函數(shù)ROUND()函數(shù)MOD()函數(shù)RAND()函數(shù)SQRT()函數(shù)SIN()函數(shù)綜合應(yīng)用ABS()函數(shù)功能:返回參數(shù)的絕對(duì)值。語(yǔ)法:ABS(number)

Number需要計(jì)算其絕對(duì)值的實(shí)數(shù)Q:如何用IF函數(shù)代替？INT()函數(shù)功能:返回接近于實(shí)數(shù)的最大整數(shù)。語(yǔ)法INT(number)number:需要進(jìn)行取整處理的實(shí)數(shù)。例如:int(8.9)=?int(-8.9)=?Q:如何用公式返回單元格中正實(shí)數(shù)的小數(shù)部分？PI()函數(shù)功能:返回圓周率π的值:3.14159265358979語(yǔ)法:PI()說(shuō)明:此函數(shù)無(wú)須參數(shù)，但函數(shù)名后面的括號(hào)不能少。ROUND()函數(shù)功能:對(duì)指定數(shù)值進(jìn)行四舍五入并保留指定小數(shù)位數(shù)。語(yǔ)法:ROUND(number，num_digits)number:需要進(jìn)行舍入的數(shù)字。Num_digits:指定保留的小數(shù)位數(shù)。例:round(88.56,1)=88.6round(88.56,-1)=90MOD()函數(shù)功能:返回兩數(shù)相除的余數(shù)。語(yǔ)法:MOD(number，divisor)Number為被除數(shù)、divisor為除數(shù)。除數(shù)為0，則返回錯(cuò)誤值#div/0!Q:1222211除以111？RAND()函數(shù)功能:返回一個(gè)〔0,1)均勻分布的隨機(jī)數(shù)。語(yǔ)法:RAND()無(wú)參數(shù)如何將公式永久性地改為隨機(jī)數(shù)？保持編輯狀態(tài)，然后按F9生成〔a，b〕之間的隨機(jī)整數(shù):＝INT(RAND()*(b－a＋1)＋a)Q:如何產(chǎn)生銀行帳號(hào)的初始密碼(六位)SQRT()函數(shù)功能:返回給定正數(shù)的平方根。語(yǔ)法:SQRT(number)

number:為負(fù)則函數(shù)返回錯(cuò)誤值:#Num！Q:如何用簡(jiǎn)單的公式代替？＝A1^(1/2)SIN()函數(shù)功能:返回給定角度的正弦值。語(yǔ)法:SIN(number)

注意:Number以弧度表示。Q:角度如何轉(zhuǎn)化成弧度？SUM函數(shù)功能:返回參數(shù)表中所有參數(shù)之和。語(yǔ)法:SUM(number1,number2，…)注意:如參數(shù)為邏輯值，則TRUE和FALSE分別轉(zhuǎn)換為1和0；文本型數(shù)字轉(zhuǎn)換為數(shù)值型數(shù)字；而引用的單元格中出現(xiàn)空白單元格、邏輯值、文本型數(shù)字該參數(shù)將被忽略。SUMIF函數(shù)功能:根據(jù)指定條件對(duì)若干單元格求和。語(yǔ)法:SUMIF(range,criteria,sum_range)例如:求所有工程師基本工資之和。綜合應(yīng)用例:x=46.057使用數(shù)學(xué)函數(shù)計(jì)算:統(tǒng)計(jì)函數(shù)average()函數(shù)count()countif()函數(shù)average()函數(shù)功能:返回參數(shù)平均值(算術(shù)平均)語(yǔ)法:average(number1,number2,…,number30)Max():返回給定參數(shù)表中的最大值。Min():返回給定參數(shù)表中的最小值例:求學(xué)生平均成績(jī)、最高分和最低分。count()函數(shù)功能:返回參數(shù)中數(shù)值型數(shù)據(jù)的個(gè)數(shù)。語(yǔ)法:count(value1,…,value30)Value1,…:是包含或引用各種類型數(shù)據(jù)的參數(shù)。只有數(shù)字類型的數(shù)據(jù)才被計(jì)算。Counta:返回參數(shù)中非空單元格個(gè)數(shù)例:統(tǒng)計(jì)學(xué)生人數(shù)countif()函數(shù)功能:計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。語(yǔ)法:COUNTIF(range,criteria)Range

為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria

為確定哪些單元格將被計(jì)算在內(nèi)的條件。例:統(tǒng)計(jì)男學(xué)生人數(shù)文本函數(shù)UPPER()函數(shù)LEFT()函數(shù)MID()函數(shù)LEN()函數(shù)綜合應(yīng)用UPPER()函數(shù)功能:將文本轉(zhuǎn)化成大寫字母。語(yǔ)法:UPPER(text)text:要轉(zhuǎn)化為小寫字母的文本串。函數(shù)并不改變文本串中的非文本字符。例:Upper(“Exercise1”)結(jié)果:EXERCISE1LOWER(text)函數(shù):將文本轉(zhuǎn)化成小寫字母LEFT()函數(shù)功能:返回字符串最左端的子字符串。語(yǔ)法:(text,num_chars)text:包含要提取字符的文字串。Num_chars:指定函數(shù)所要返回的字符串長(zhǎng)度說(shuō)明:省略num-chars則假設(shè)其值等于1。例:取字符串A1:”信息information”＝left(A1)為信，left(A1,5)為信息infRIGHT:返回某一文本串中最右側(cè)的子字符串。MID()函數(shù)功能:從文本串的某一指定位置開(kāi)始，返回指定長(zhǎng)度的子字符串。語(yǔ)法:MID(text,start_num,num_chars)text:用于從中提取字符的文本串。Start_num:要從文本串中提取字符的起始位置。Num_chars:指定要返回字符的個(gè)數(shù)。例:取字符串A1:”信息information”＝mid(A1,4,2)結(jié)果:foLEN()函數(shù)功能:返回文本字符串中的字符數(shù)。LENB返回文本字符串中用于代表字符的字節(jié)數(shù)。語(yǔ)法:LEN(text)Text

是要查找其長(zhǎng)度的文本(包含空格)。例:取字符串A1:”信息information”=LEN(A1)結(jié)果:13=LENB(A1)結(jié)果:15說(shuō)明:漢字占兩個(gè)字節(jié)，英文字母占一個(gè)字節(jié)綜合應(yīng)用使用文本函數(shù)時(shí)常用到文本的連接符號(hào)“&”。例:用公式將字符串左數(shù)第三個(gè)字符取出來(lái)。例:用公式將字符串右數(shù)第三個(gè)字符取出來(lái)。日期與時(shí)間函數(shù)日期與時(shí)間函數(shù)介紹date與time函數(shù)today與now函數(shù)year、month與day函數(shù)綜合應(yīng)用日期與時(shí)間函數(shù)介紹日期/時(shí)間數(shù)據(jù)本質(zhì)上是數(shù)值型數(shù)據(jù)，該數(shù)字稱為序列數(shù)。序列數(shù):小數(shù)點(diǎn)左邊的數(shù)字表示日期，從1900年1月1日到所輸入日期之間的總天數(shù)。小數(shù)點(diǎn)右邊的數(shù)字表示時(shí)間。例如，序列數(shù)367.5表示1901年1月1日晚12時(shí)。作用:常作為日期時(shí)間函數(shù)的返回值或參數(shù)。date與time函數(shù)功能:返回某一特定日期的序列數(shù)。語(yǔ)法:DATE(year,month,day)Year介于1900到9999之間的一個(gè)數(shù)字。Month代表月份的數(shù)字。Day代表在該月份中第幾天的數(shù)字。例:＝date(2006,04,10)結(jié)果為日期:2006/04/10time():返回某一特定時(shí)間的序列數(shù)，為小數(shù)today與now函數(shù)功能:返回系統(tǒng)當(dāng)天日期的序列數(shù)。語(yǔ)法:TODAY()－－－無(wú)參數(shù)例:＝today()結(jié)果為日期:2006/11/27插入靜態(tài)的日期和時(shí)間:當(dāng)前日期:Ctrl+;當(dāng)前時(shí)間:Ctrl+Shift+；當(dāng)前日期和時(shí)間按Ctrl+;，然后按空格鍵，最后按Ctrl+Shift+;now():返回系統(tǒng)當(dāng)前日期和時(shí)間的序列數(shù)。year、month與day函數(shù)功能:返回指定日期的年數(shù)、月份數(shù)和天數(shù)。語(yǔ)法:YEAR(serial_number)Serial_number可以為數(shù)字、文本，如“15-Apr-1993”或“4-15-1993”。例1:計(jì)算年齡(虛歲)例2:用公式得到月份加1的日期年齡的計(jì)算方法主要有：①虛歲年齡。中國(guó)在習(xí)慣上常用的年齡計(jì)算方法，按出生后所經(jīng)歷的日歷年頭計(jì)算,即生下來(lái)就算1歲,以后每過(guò)一次新年便增加1歲。一般按農(nóng)歷新年算，也有按公歷算的。日期時(shí)間函數(shù)綜合應(yīng)用例:計(jì)算周歲年齡。什么是周歲？②周歲年齡。又稱實(shí)足年齡，指從出生到計(jì)算時(shí)為止，共經(jīng)歷的周年數(shù)或生日數(shù)。例如，1990年7月1日零時(shí)進(jìn)行人口普查登記，一個(gè)1989年12月15日出生的嬰兒，按虛歲計(jì)算是2歲，實(shí)際剛剛6個(gè)多月，還未過(guò)一次生日，按周歲計(jì)算應(yīng)為不滿1周歲，即0歲。周歲年齡比虛歲年齡常常小1～2歲,它能正確反映人們的實(shí)際生存年歲,是人口統(tǒng)計(jì)中最常用的年齡計(jì)算方法。

邏輯函數(shù)IF()函數(shù)函數(shù)的嵌套AND()函數(shù)OR()函數(shù)AND、OR、NOT綜合比較IF()函數(shù)功能:對(duì)比較條件式Logical-test進(jìn)行測(cè)試，如果條件為邏輯值TRUE，則取value-if-true的值；否則取value-if-false的值。語(yǔ)法:IF(logical-test，value-if-true，value-if-false)例:判斷考試成績(jī)是否及格條件:考試成績(jī)>=60為及格，否則為不及格。嵌套IF函數(shù)在某些情況下，可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用。例:判斷成績(jī)的等級(jí)條件:考試成績(jī)>=85為優(yōu)秀，84>考試成績(jī)>=60為中等，考試成績(jī)<60為差A(yù)ND()函數(shù)功能:所有參數(shù)的邏輯值為真時(shí)返回TRUE；只要一個(gè)參數(shù)的邏輯值為假即返回FALSE。語(yǔ)法:AND(logical1,logical2,...)Logical1,logical2,...待檢測(cè)的1～30個(gè)條件值，各條件值或?yàn)門RUE，或?yàn)镕ALSE。例:判斷成績(jī)是否及格條件:機(jī)試和筆試都>=60為及格,否則為不及格。OR()函數(shù)功能:在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為TRUE，即返回TRUE，全為FALSE，才返回FALSE。語(yǔ)法:OR(logical1,logical2,...)Logical1,logical2,...為需要進(jìn)行檢驗(yàn)的1到30個(gè)條件，分別為TRUE或FALSE。例:判斷成績(jī)是否及格條件:機(jī)試和筆試任意<60為不及格,否則為及格。AND、OR、NOT綜合比較財(cái)務(wù)函數(shù)PV函數(shù)PMT函數(shù)PV函數(shù)功能：用于計(jì)算固定償還能力下的可貸款總數(shù)。語(yǔ)法：P