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

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

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

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

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

為需要計算其中滿足條件的單元格數目的單元格區域。Criteria

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

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

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