1、復(fù)習(xí)回顧,名字的應(yīng)用（行列交叉點(diǎn)查找、建立函數(shù)或公式時(shí)使用名字、事后套用定義的名字、整行整列定義名字） 函數(shù)的輸入（手動(dòng)、向?qū)В?Excel幫助（向?qū)Ю锏某溄印1） If函數(shù)（條件，正確地結(jié)果，錯(cuò)誤的結(jié)果） Sum函數(shù) 參數(shù)是文本、邏輯值參與計(jì)算 參數(shù)是數(shù)組或引用，只計(jì)算數(shù)值，不計(jì)算文本邏輯值等 Sumif函數(shù)（區(qū)域，條件，滿足條件需求和的區(qū)域） Average 不對(duì)文本和邏輯值類數(shù)據(jù)進(jìn)行計(jì)算； Averagea要對(duì)文本和邏輯函數(shù)進(jìn)行平均值計(jì)算 文本視為0，true視為1，false視為0，空文本（“”）也作為0計(jì)算 Count 統(tǒng)計(jì)數(shù)值型數(shù)據(jù)的個(gè)數(shù) CountA統(tǒng)計(jì)非空單元格的個(gè)數(shù) Co

2、untBlank統(tǒng)計(jì)空白單元格的個(gè)數(shù) Countif統(tǒng)計(jì)滿足條件Criteria的單元格個(gè)數(shù) Max與min,本節(jié)課主要內(nèi)容,邏輯函數(shù)（IF、AND、OR、NOT、TRUE、FALSE） 數(shù)學(xué)與三角公式 日期及時(shí)間函數(shù) 文本函數(shù),5.3 邏輯函數(shù),EXCEL 中的邏輯函數(shù)共有6個(gè)，分別是IF、AND、OR、NOT、TRUE和FALSE,1IF函數(shù) IF函數(shù)也稱為條件函數(shù)，其功能是對(duì)指定的條件判斷真假，根據(jù)參數(shù)的真假值，返回不同結(jié)果。數(shù)據(jù)處理中，經(jīng)常利用IF函數(shù)對(duì)公式和數(shù)值進(jìn)行條件檢測(cè)。其語法格式如下： IF (logical_test,value_if_true,value_if_false)

3、 其中，參數(shù)logical_test是一個(gè)條件表達(dá)式，可以是比較表達(dá)式和邏輯判斷表達(dá)式，其結(jié)果返回值為TRUE和FALSE；Value_if_true表示logical_test為TRUE時(shí)的顯示內(nèi)容；Value_if_false表示logical_test為FALSE時(shí)的顯示內(nèi)容。,例如：工作表C5單元格的公式為 =IF（B5=0,”,A5/B5）,這三個(gè)函數(shù)的語法格式分別為： AND (logical1,logical2, ) OR (logical1,logical2,) NOT (logical) 其中，AND和OR函數(shù)中的參數(shù)Logical1, logical2, 表示待測(cè)試的條件值

4、或表達(dá)式，其結(jié)果可為TRUE 或 FALSE，最多不超過30個(gè)。當(dāng)使用AND函數(shù)時(shí)，只有當(dāng)運(yùn)算對(duì)象都為TRUE的情況下，運(yùn)算結(jié)果才為TRUE，否則為FALSE；當(dāng)執(zhí)行OR運(yùn)算時(shí)，只有當(dāng)運(yùn)算對(duì)象都為FALSE的情況下，運(yùn)算結(jié)果才為FALSE，否則為TRUE。 NOT函數(shù)中的Logical為可以計(jì)算出 TRUE 或 FALSE 的邏輯值或邏輯表達(dá)式。NOT函數(shù)的功能是對(duì)該表達(dá)式運(yùn)算結(jié)果取相反值。,2、 AND、OR、 NOT函數(shù),假設(shè)在一個(gè)工作表的A5、B5、C5單元格中的數(shù)值分別為40、50、60，則有公式=AND(A5=60、B5=60、C5=60) =FALSE， =OR(A5=60、B5=

5、60、C5=60) =TRUE =NOT(AND(A5B5,B5C5) =FALSE,使用AND與OR函數(shù)時(shí)，說明,參數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用 如果指定的單元格區(qū)域包括非邏輯值，則AND函數(shù)將會(huì)返回錯(cuò)誤值”#VALUE!”。 在Excel中只有數(shù)字0被視為false,非0數(shù)字都被視為true。 邏輯函數(shù)一般需要結(jié)合IF、SUM、COUNT等需要判斷條件的函數(shù)使用。,TRUE函數(shù)和FALSE函數(shù)都是無參函數(shù)，其結(jié)果分別是返回邏輯值TRUE和FALSE。,3、TRUE和FALSE函數(shù),5.3 邏輯函數(shù),案例 某單位有3000名職工，要按其工資繳納個(gè)人收入所得稅，稅率與工資的關(guān)系如

6、表所示。 該單位的職工表如下所示，用IF函數(shù)求出各職工的稅率！,5.3 邏輯函數(shù),在E4中輸入編輯欄中的公式！,見1 邏輯函數(shù).xls,練習(xí)一：銷售提成計(jì)算,問題描述：如圖所示為某公司銷售部5月份各地辦事處的銷售額及其銷售費(fèi)用情況，請(qǐng)根據(jù)右邊的獎(jiǎng)金提成政策判別各個(gè)辦事處的獎(jiǎng)金提成比例，并計(jì)算獎(jiǎng)金提成金額。（見練習(xí)-邏輯函數(shù).xls） 銷售提成數(shù)據(jù)與政策表格,練習(xí)二：考試成績(jī)?cè)u(píng)判,（見練習(xí)-邏輯函數(shù).xls）,練習(xí)三,案例：如圖為跳水比賽的某一單輪成績(jī)計(jì)算表，按照跳水比賽計(jì)分規(guī)則：跳水比賽要進(jìn)行六輪，每輪分別打分，最后按照六輪成績(jī)累加的結(jié)果進(jìn)行排名。每輪成績(jī)的計(jì)算，需要八位裁判分別打分（滿分為1

7、0分），然后根據(jù)八位裁判打分的總分，減去最高分和最低分，最后在結(jié)果上難度系數(shù)乘3之后，得到本輪的選手成績(jī)（這里還乘上3，是因?yàn)殡y度系數(shù)一般在3.3左右，乘上3可以使得選手成績(jī)?cè)?00左右）。 見:練習(xí)邏輯函數(shù).xls,ABS(n) 返回?cái)?shù)字的絕對(duì)值 INT(n) 將數(shù)字向下舍入到最接近的整數(shù) TRUNC(n,n_d) 將數(shù)字的小數(shù)部分截去，返回整數(shù)(不四舍五入) ROUND 按指定位數(shù)四舍五入某個(gè)數(shù)字 SIGN 返回?cái)?shù)字符號(hào)。 PRODUCT(n1,n2,)將參數(shù)數(shù)字相乘，返回乘積值 MOD(n,div)返回兩數(shù)相除的余數(shù)，其結(jié)果符號(hào)與除數(shù)相同 SQRT(n)返回某一正數(shù)算術(shù)平方根 FACT(

8、n)返回?cái)?shù)的階乘 POWER(x,n)返回給定數(shù)字的乘冪（與“”運(yùn)算符相同） RAND() 返回一個(gè)大于等于0 小于1 的隨機(jī)數(shù) RANDBETWEEN（bottom，top）產(chǎn)生位于兩個(gè)指定數(shù)值之間的一個(gè)隨機(jī)數(shù) MINVERSE(array) 返回矩陣的逆 MMULT(array1, array2) 返回兩個(gè)或多個(gè)矩陣的乘積,5.4 數(shù)學(xué)和三角函數(shù),練習(xí)（交作業(yè)）,若某學(xué)生想做一個(gè)課程成績(jī)表，根據(jù)計(jì)算方法，表格設(shè)置如下，其中平時(shí)作業(yè)總共5次，每次滿分20分平時(shí)成績(jī)?yōu)?次之和；期中考試，期末上機(jī)成績(jī)和期末筆試成績(jī)總分為100分，學(xué)期總成績(jī)通過如下結(jié)構(gòu)公式加權(quán)計(jì)算求出： 學(xué)期總成績(jī)=平時(shí)成績(jī)*1

9、5%+期中考試*25%+（上機(jī)成績(jī)*30%+筆試成績(jī)*70%）*60% 本題的公式不算復(fù)雜，就是數(shù)據(jù)輸入量比較大，如果不是進(jìn)行真正的班級(jí)成績(jī)處理而僅僅是練習(xí)，請(qǐng)?jiān)O(shè)計(jì)如下形式的表格，并利用隨機(jī)函數(shù)產(chǎn)生相關(guān)數(shù)據(jù)（20個(gè)學(xué)生即可）,作業(yè)要求,建立工作表框架，制作表格框架，注意表格中邊框和底紋的設(shè)置效果，以及某些單元格的合并居中效果的設(shè)置，學(xué)生個(gè)數(shù)下面只設(shè)置到20行。 學(xué)號(hào)的輸入。采用自定義的方法輸入，形如PKM-001，PKM-002。 姓名的輸入。作為練習(xí)，姓名按實(shí)際輸入也沒有意義。此處采用“學(xué)生1、學(xué)生2、”的命名方式。 平時(shí)作業(yè)評(píng)分都在10-20分之間 期中考試都及格 期末考試成績(jī)?cè)?5-10

10、0之間 所有的分?jǐn)?shù)均取整數(shù),案例及練習(xí)：按實(shí)發(fā)工資總額計(jì)算每種面額鈔票的張數(shù),問題描述：在工資數(shù)據(jù)處理中，每位職工的實(shí)發(fā)工資已求出，需要進(jìn)行工資發(fā)放?，F(xiàn)在想在發(fā)給每個(gè)職工鈔票張數(shù)最少的情況下（沒有拖欠工資情況），根據(jù)所有職工實(shí)發(fā)工資的總數(shù)，計(jì)算出財(cái)務(wù)部門應(yīng)該準(zhǔn)備的每種面額的總張數(shù)，做成如下圖形式。,見2 數(shù)學(xué)與三角函數(shù)-案例及練習(xí).xls,5.5日期及時(shí)間函數(shù),1YEAR，MONTH，DAY函數(shù) 用法 YEAR(serial_number) MONTH(serial_number) DAY(serial_number) 其中的serial_number是一個(gè)日期或數(shù)字。 功能 YEAR函數(shù)返回

11、某日期的年份。 MONTH函數(shù)返回以系列數(shù)表示的日期中的月份。 DAY函數(shù)返回以系列數(shù)表示的某日期的天數(shù)，用整數(shù)131表示。 示例：公式“=YEAR(“2008/5/15”)”返回2008,5.5日期及時(shí)間函數(shù),2TODAY、NOW函數(shù) 用法 TODAY( ) NOW( ) 功能 TODAY函數(shù)返回系統(tǒng)的當(dāng)前日期。 NOW函數(shù)計(jì)算當(dāng)前日期和時(shí)間。 示例：在任意一個(gè)單元格中輸入公式“=TODAY（）”，都得到電腦系統(tǒng)的當(dāng)前日期。 說明：TODAY函數(shù)是一個(gè)無參函數(shù)，但是在應(yīng)用時(shí)候后面的左右括號(hào)不能漏掉。,3、 DATE函數(shù) DATE函數(shù)返回代表特定日期的序列號(hào)。其語法格式為：DATE(year,

12、month,day) 其中的三個(gè)參數(shù)中： Year為年份，也可以為年份的序列號(hào)，用一到四位數(shù)字表示。 Month為月份，如果所輸入的月份大于12，將從指定年份下一年的一月份開始往上加算。如：“DATE(2008,14,2) ”返回代表 2009年2月2日的序列號(hào)。 Day代表在該月份中的天數(shù)，如果 day 大于該月份的最大天數(shù)，則將從指定月份的下一月的第一天開始往上累加。如：“DATE(2008,5,50)” 將返回2008 年6月19日。 示例：在有些數(shù)據(jù)庫中，日期的年、月、日數(shù)字可能分為三個(gè)字段來保存，當(dāng)導(dǎo)入到Excel工作表后，就可以利用DATE函數(shù)將年、月、日合并成一個(gè)具體的日期數(shù)據(jù)。

13、假設(shè)單元格A1是年份數(shù)字，B1是月份數(shù)字，C1是日數(shù)，則公式“=DATE（A1，B1，C1）”就將A1的年份數(shù)字，B1的月份數(shù)字和C1的日數(shù)，合并到一起組成了一個(gè)具體的日期數(shù)據(jù)。,5.5日期及時(shí)間函數(shù),5.5日期及時(shí)間函數(shù),4、WEEKDAY 用法 WEEKDAY(serial_number, return_type) serial_number代表要查找的日期，或日期的系列數(shù)，以了解日期為星期幾；return_type確定返回值類型數(shù)字。 功能 WEEKDAY計(jì)算給定的日期是星期幾,示例：2008年5月15日是星期四，則： “=WEEKDAY(”2008/5/15“) ”返回“5”； “=W

14、EEKDAY(”2008/5/15“,2) ”返回“4”； “=WEEKDAY(2008/5/15,3) ”返回“3”,5.5日期及時(shí)間函數(shù),4、NETWORKDAYS函數(shù) 用法 NETWORKDAYS(start_date, end_date, holidays) 其中 start_date表示代表開始日期，end_date為終止日期， holidays表示不在工作日歷中的一個(gè)或多個(gè)日期所構(gòu)成的可選區(qū)域，如元旦節(jié)、五一節(jié)、春節(jié)。 示例：公式“=NETWORKDAYS(2008/5/1, 2008/5/10,2008/5/1)”返回6。,5.5日期及時(shí)間函數(shù),案例 某建筑公司按天數(shù)計(jì)算每個(gè)民工

15、的報(bào)酬，民工每個(gè)星期工作5天，工作時(shí)間和請(qǐng)假時(shí)間如圖的AH列所示，I列是每天的報(bào)酬，用日期函數(shù)計(jì)算每個(gè)民工的工作天數(shù)和總工資。,見3 時(shí)間日期函數(shù).xls,案例：某公司為體現(xiàn)人文關(guān)懷，想在員工生日當(dāng)天為其慶祝生日并送上禮物。為了能夠事先知道當(dāng)天有哪些人過生日，公司根據(jù)人事檔案制作了一個(gè)生日提醒表格。,5.5日期及時(shí)間函數(shù),見3 時(shí)間日期函數(shù).xls,問題描述：如圖所示，A2：G6單元格區(qū)域是某公司一個(gè)部門的加班記錄表，表中已經(jīng)記錄了每個(gè)人的加班日期以及加班的開始時(shí)間和結(jié)束時(shí)間。加班工資按小時(shí)計(jì)算，為了方便計(jì)算，若加班時(shí)間超過半個(gè)小時(shí)的就按一個(gè)小時(shí)計(jì)算，但是低于半小時(shí)的部分不再計(jì)算；一般工作日的

16、加班工資標(biāo)準(zhǔn)為20元/小時(shí)，節(jié)假日為40元/小時(shí)。 根據(jù)以上原則，請(qǐng)通過公式運(yùn)算將下圖中的E、F、G列進(jìn)行內(nèi)容填充；并計(jì)算每個(gè)員工的總加班時(shí)間、節(jié)假日加班時(shí)間以及加班工資。,練習(xí)：加班工資計(jì)算,文本函數(shù)(補(bǔ)充),常用文本函數(shù)介紹 1TEXT函數(shù) TEXT函數(shù)的功能是將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。其語法格式為： TEXT（value,format_text） 其中包括兩個(gè)參數(shù)，意義分別為： Value為數(shù)值、計(jì)算結(jié)果是數(shù)值的公式或?qū)?shù)值單元格的引用； format_text為所要選用的文本型數(shù)字格式，即“單元格格式”對(duì)話框“數(shù)字”選項(xiàng)卡的“分類”列表框中顯示的文本形式的數(shù)字格式，它不能包

17、含星號(hào)“*”。 示例：如果工作表中A1單元格的數(shù)值為2986.638，則公式“=TEXT(A1,#，#0.00)”返回字符串“2，986.64”。 說明：使用“單元格格式”對(duì)話框的“數(shù)字”選項(xiàng)卡設(shè)置單元格格式，只會(huì)改變單元格的格式而不會(huì)影響其中的數(shù)值。而使用TEXT函數(shù)可以將數(shù)值轉(zhuǎn)換為帶格式的文本，而其結(jié)果將不再作為數(shù)字參與計(jì)算。,常用文本函數(shù)介紹,2VALUE函數(shù) VALUE函數(shù)的功能是將代表數(shù)字的文本字符串轉(zhuǎn)換成數(shù)字。其語法格式為： VALUE（text） 其中參數(shù)text為帶引號(hào)的文本，或?qū)π枰M(jìn)行文本轉(zhuǎn)換的單元格的引用，還可以是Excel 中可識(shí)別的日期或時(shí)間格式。 如果text不是上

18、述格式，則VALUE函數(shù)返回錯(cuò)誤值“ #VALUE!”。 示例：公式“=VALUE (￥1,000)”返回1000。 3LOWER函數(shù) LOWER功能是將一個(gè)文本字符串中的所有大寫字母轉(zhuǎn)換為小寫字母。其語法格式為： LOWER（text） 其中參數(shù)text是要轉(zhuǎn)換為小寫字母的文本，它可以是引用或文字串。 示例：如果A1中有字符串“EXCEL”，則公式“=LOWER(A1)”返回字符串“excel”。 說明：LOWER函數(shù)不改變文本中的非字母字符。與LOWER函數(shù)類似，Excel還有UPPER函數(shù)和PROPER函數(shù)，其中：UPPER函數(shù)將一個(gè)文本字符串中所有小寫字母轉(zhuǎn)換為大寫字母的，而PROPE

19、R函數(shù)將文本字符串的首字母和任何非字母字符之后的首字母轉(zhuǎn)換成大寫、其余字母轉(zhuǎn)換成小寫，它們的語法格式和LOWER函數(shù)相同。,常用文本函數(shù)介紹,4TRIM函數(shù) TRIM函數(shù)清除文本中的所有空格（除了單詞之間的單個(gè)空格外）。其語法格式為： TRIM(text) 其中參數(shù)text是需要清除其中空格的文本。 示例：如果工作表A1單元格的數(shù)據(jù)為字符串“ First Quarter Earnings ”，則公式“=TRIM(A1)”返回字符串“First Quarter Earnings”。 5LEN函數(shù) LEN函數(shù)的功能是返回文本字符串中的字符數(shù)。其語法格式為：LEN(text) 其中參數(shù)text為要查

20、找其長(zhǎng)度的文本，空格也將作為字符進(jìn)行計(jì)數(shù)。 示例：如果A1單元格中字符串為“電腦 愛好者”，則公式“=LEN(A1)”返回6。 6CONCATENATE函數(shù) CONCATENATE函數(shù)的功能是將若干字符串合并成一個(gè)字符串，其功能與文本連接運(yùn)算符“&”相同。其語法格式為：CONCATENATE(text1,text2,) 其中的參數(shù)text1,text2,為1到30個(gè)將要合并成單個(gè)文本的文本項(xiàng)，這些文本項(xiàng)可以是文字串、數(shù)字或?qū)蝹€(gè)單元格的引用。 示例：公式“=CONCATENATE(97,千米)”返回“97千米”。,常用文本函數(shù)介紹,7LEFT函數(shù) LEFT函數(shù)的功能是根據(jù)所指定的字符數(shù)從文本字

21、符串左邊取出第一個(gè)或前幾個(gè)字符。也就是說，LEFT函數(shù)對(duì)字符串進(jìn)行“左截取”，其語法格式為： LEFT（text,num_chars） 其中參數(shù)Text是要提取字符的文本字符串； Num_chars是指定提取的字符個(gè)數(shù)，它必須大于或等于0。如果省略參數(shù) num_chars，默認(rèn)值為 1。如果參數(shù) num_chars 大于文本長(zhǎng)度，則LEFT函數(shù)返回所有文本。 示例：公式“=LEFT(“電腦愛好者”,2)”返回字符串“電腦”。 8MID函數(shù) MID函數(shù)的功能是返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。其語法格式為： MID(text,start_num,num_chars) 其中參數(shù)text為要提取字符的文本字符串；start_num為要提取的第一個(gè)字符的位置（文本中第一個(gè)字符的位置為 1，其余依此類推）；num_chars為指定提取的字符個(gè)數(shù)。 示例：公式“=MID(“電腦愛好者”,3,2)”返回字符串“愛好”。,常用文本函數(shù)介紹,9REPLACE函數(shù) REPLACE函數(shù)的功能是根據(jù)所指定的字符數(shù)，使用其它文本字符串替換當(dāng)前文本字符串中的部分文本。其語法格式為： REPLACE(old_text,start_num,num_chars,new_text) 其中參數(shù)Old_text為要替換其部分字符的文本；S