公式與函數(shù)的應(yīng)用

1、主講人：李丹一、在電子表格中應(yīng)用公式一、在電子表格中應(yīng)用公式算術(shù)運算符+ * / (乘方） %（百分比）比較運算符 = = 比較運算的結(jié)果為 TRUE 或 FALSE文本連接運算符: &假如B2單元格內(nèi)容：信息時代，D6單元格內(nèi)容：ABC。則=B2&D6 的值是信息時代ABC=“21世紀(jì)是”&B2 的值是21世紀(jì)是信息時代 一、在電子表格中應(yīng)用公式一、在電子表格中應(yīng)用公式運算符的優(yōu)先級( ) 算術(shù)運算 連接運算 比較運算二、相對引用、絕對引用、混合引用二、相對引用、絕對引用、混合引用對單元格地址的三種引用方式 相對引用：直接使用單元格或單元格區(qū)域的名稱作為引用名稱。A5+D6 =F4+F8絕對

2、引用：在單元格名稱的列標(biāo)與行號前加符號“$”表示絕對引用。$A$4+$B$5混合引用：相對引用與絕對引用相結(jié)合=F$3+$D4說明：1、當(dāng)公式使用相對引用時，把公式復(fù)制到一個新的位置，公式中的單元格引用也跟隨變化，但相對于公式所在單元格的位置不變。2、在公式中使用絕對引用，不論公式復(fù)制到何處，公式中所引用的單元格不發(fā)生變化。二、相對引用、絕對引用、混合引用二、相對引用、絕對引用、混合引用跨表格數(shù)據(jù)的引用1.引用同一工作薄其他工作表中的單元格或區(qū)域工作表標(biāo)簽名工作表標(biāo)簽名!單元格地址單元格地址2.引用同一工作薄多張工作表的相同單元格或區(qū)域第一個工作表名：最后一個工作表名第一個工作表名：最后一個工

3、作表名! 單元格地址單元格地址3.不同工作薄之間的數(shù)據(jù)引用 工作簿名工作簿名工作表標(biāo)簽名工作表標(biāo)簽名! 單元格地址單元格地址1、條件函數(shù)IF(條件，滿足條件時的輸出結(jié)果，不滿足條件時的輸出結(jié)果）例1：”學(xué)生成績表” ，條件如下：總分大于等于300分顯示及格，小于300分顯示不及格三、函數(shù)的應(yīng)用三、函數(shù)的應(yīng)用IF(F2=300,及格,不及格）也可用：插入菜單中“函數(shù)”命令來實現(xiàn)，如下圖所示例2：根據(jù)學(xué)生數(shù)學(xué)成績劃分等級。= IF(C2=80, “良好”, IF(C2=70, “中等”, IF(C2=60, “及格”,“不及格”)2、取整函數(shù)INT（ ） ：將任意實數(shù)向下取整為最接近的整數(shù)。 例：

4、 INT（8.9)例： INT（8.9）98.98-8-8.9-9將數(shù)字向下舍入到最接近的整數(shù)將數(shù)字向下舍入到最接近的整數(shù)0結(jié)果為8結(jié)果為93、ROUND：對數(shù)值進(jìn)取四舍五入語法：ROUND(需四舍五入的數(shù)字，保留的小數(shù)位)例：如果A1=65.25，則公式“=ROUND(A1，1)” =ROUND(82.149，2)=ROUND(21.5，-1)返回20；=Round(A1,0)返回6565.382.152065注：如果小于0，則在小數(shù)點左側(cè)按指定位數(shù)四舍五入。4、Rank排位函數(shù)（返回某數(shù)字在一列數(shù)字中相對于其他數(shù)值的大小排位）格式：=RANK(指定的單元格指定的單元格,排序的范圍排序的范圍

5、,0或非或非0)如，如，=RANK(A1,$A$1:$A$5,0) 5 5、ROWROW：返回給定引用的行號。：返回給定引用的行號。語法：ROW(單元格引用)?！皢卧褚谩睘樾枰玫狡湫刑柕膯卧窕騿卧駞^(qū)域。實例:利用row建立序號: 6、MOD：取余用途：返回兩數(shù)相除的余數(shù)，其結(jié)果的正負(fù)號與除數(shù)相同。語法：MOD(被除數(shù)，除數(shù))參數(shù)：除數(shù)不能為零。例：公式“=MOD(14，4)”返回?； “=MOD(-5，-2)”返回？。 2-17 7、Left/RightLeft/Right：截取字符串函數(shù)：截取字符串函數(shù)用途：根據(jù)指定的字符數(shù)返回文本串中的第一個或前幾個字符。此函數(shù)用于雙字節(jié)字符。語

6、法：LEFT(文本, 返回的字符數(shù)) Right(文本, 返回的字符數(shù))參數(shù)：返回的字符數(shù)必須大于或等于0。實例：如果A1=電腦愛好者，則LEFT(A1，2)返回? ;Right(A1,3)返回?8 8、MidMid：截取字符串函數(shù)：截取字符串函數(shù)用途：MID 返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。語法：MID(文本串,開始位置,返回字符的個數(shù)) 參數(shù)：開始位置是文本中要提取的第一個字符的位置，文本中第一個字符的開始位置為1，以此類推。實例1：如果a1=東南(福建)汽車工業(yè)有限公司，則公式“=MID(A1，4，2)”返回“福建” 。案例:利用MID從身份證號中提取出生年

7、份。如9 9、LENLEN：計算字符長度。：計算字符長度。用途：LEN 返回文本串的字符數(shù)。語法：LEN(text) 。參數(shù)：Text 待要查找其長度的文本。注意：此函數(shù)用于雙字節(jié)字符，且空格也將作為字符進(jìn)行統(tǒng)計。實例：如果A1=電腦愛好者，則公式“=LEN(A1)”返回？10、YEARMONTHDAYn用途：返回某日期的年份/月份/日期。n語法：YEAR/MONTH/DAY(日期值)n實例： 假設(shè)A2單元格存放的日期為2008-8-20u 公式“=YEAR(A2)返回2008”u 公式“ =MONTH(A2)返回8”u 公式“ =DAY(A2)返回20

8、”11、Date用途：返回代表特定日期的序列號。語法：DATE(year，month，day)參數(shù)：year 為一到四位,代表年份。Month 代表每年中月份的數(shù)字。Day 代表在該月份中第幾天的數(shù)字。實例：將身份證中提取出的年月日直接轉(zhuǎn)化成出生年月日: 假若A則 date(mid(a,7,4),mid(a,11,2),mid(a,13,2)返回1974-5-2512、Today用途：返回系統(tǒng)當(dāng)前日期的序列號。參數(shù)：無語法：TODAY()實例1：公式“=TODAY()”返回執(zhí)行公式時的系統(tǒng)時間。實例2：依出生日期計算年齡的示例 表示方法：（today()

9、-出生日期）/36513、weekday用途：返回某日期的星期數(shù)。在默認(rèn)情況下，它的值為1(星期天)到7(星期六)之間的一個整數(shù)。語法：WEEKDAY(日期，返回類型)參數(shù)：“返回類型 ”為確定返回值類型的數(shù)字，數(shù)字1 或省略則1 至7 代表星期天到數(shù)星期六，數(shù)字2 則1 至7 代表星期一到星期天（推薦使用），數(shù)字3 則0 至6 代表星期一到星期天。實例：公式“=WEEKDAY(”2008/8/20“，2)”返回3(星期三)，=WEEKDAY(“2008/8/20”，3)返回2(星期三)。公式返回的錯誤信息返回值公式返回的錯誤信息返回值錯誤提示原因#NAME?使用了EXCEL不能識別的名字#N

10、/A使用了不能使用的值#NULL!指定了無效的“空”區(qū)域#NUM！公式中使用了無效數(shù)字值#REF!引用了無效單元格#VALUE!不正確的參數(shù)或運算對象#列寬不夠DIV/0!除數(shù)為0四、工作表的安全與保護(hù)四、工作表的安全與保護(hù)1、鎖定與隱藏鎖定：工作表中選定的單元格區(qū)域不能被更改、移動、調(diào)整或刪除；隱藏：工作表中選定的單元格區(qū)域中的公式（包括函數(shù)）不會在編輯欄中顯示。注意：“鎖定”與“隱藏”操作只有在實施工作表保護(hù)后才有效，且必須先設(shè)置單元格格式中的保護(hù)功能，順序不能顛倒。四、工作表的安全與保護(hù)四、工作表的安全與保護(hù) 2、工作表保護(hù)操作過程選取所有單元格或需要保護(hù)的單元格區(qū)域，然后進(jìn)行如下設(shè)置：格式單元格保護(hù)鎖定、隱藏選項工具保護(hù)保護(hù)工作表3、工作表保護(hù)的其他選項設(shè)置單元格格式行、列的插入與刪除保護(hù)密碼的設(shè)置4、取消對工作表的保護(hù)四、工作表的安全與保護(hù)四、工作表的安全與保護(hù) 4、保護(hù)單元格中的部分區(qū)域注意：區(qū)域的保護(hù)也