函數(shù)的用法和技巧

函數(shù)的用法和技巧

此函數(shù)是excel預(yù)義的嵌入式公式。您可以使用設(shè)置函數(shù)參數(shù)方法來解決實際工作中的數(shù)據(jù)計算和統(tǒng)計問題。Excel根據(jù)不同的應(yīng)用提供了不同類型的函數(shù),如財務(wù)函數(shù)、數(shù)學(xué)函數(shù)、統(tǒng)計函數(shù)、數(shù)據(jù)庫函數(shù)、邏輯函數(shù)、文本函數(shù)和日期時間函數(shù)等。下面通過實例介紹幾個常用函數(shù)的使用技巧。一、利用raken函數(shù)自動填充過程序在Excel中,填充名次最簡單的方法是先按某一數(shù)值關(guān)鍵字(如均分)遞減排序,然后利用填充柄在名次單元格中依次填充1、2、3……這種方法雖然簡單,但如果有名次并列的情況則無法自動填充,還需要手動地修改相關(guān)的并列名次。而Excel中的RANK函數(shù)卻可以真正地做到名次的自動填充。RANK函數(shù)的格式是RANK(數(shù)值,單元格區(qū)域),其功能是返回數(shù)值在單元格區(qū)域中的排位。在使用RANK函數(shù)時,要求單元格區(qū)域必須是絕對引用。在表1(學(xué)生成績與等級評定表)中填充名次的方法是先將光標(biāo)定位到D3結(jié)果單元格中,輸入或設(shè)置函數(shù):=RANK(D3,$D$3:$D$7),然后利用填充柄填充D列中其余同學(xué)的名次單元格,即可實現(xiàn)所有學(xué)生名次的自動填充。利用RANK函數(shù)無需對表格排序,即可自動填充名次。二、真的結(jié)果,條件為假的結(jié)果Excel中IF函數(shù)的格式是IF(條件,條件為真的結(jié)果,條件為假的結(jié)果),其作用是根據(jù)條件的真假,填充不同的結(jié)果。如表1(學(xué)生成績與等級評定表)所示,可以根據(jù)均分的高低填充不同的等級。1.不同填充國先將光標(biāo)定位到E3結(jié)果單元格中,輸入或設(shè)置函數(shù):=IF(C3>=60,“及格”,“不及格”),然后利用填充柄填充E列中其余同學(xué)的等級單元格,即可實現(xiàn)兩種等級的填充。2.利用填充柄填充e列計算先將光標(biāo)定位到E3結(jié)果單元格中,輸入或設(shè)置函數(shù):=IF(C3>=60,IF(C3>=80,“好”,“中”“差”),然后利用填充柄填充E列中其余同學(xué)的等級單元格,即可實現(xiàn)三種等級的填充。3.等級填充先將光標(biāo)定位到E3結(jié)果單元格中,輸入或設(shè)置函數(shù):=IF(C3>=75,IF(C3>=85,“優(yōu)秀”,“良好”),IF(C3>=60,“及格”,“不及格”),然后利用填充柄填充E列中其余同學(xué)的等級單元格,即可實現(xiàn)四種等級的填充。從上面我們不難發(fā)現(xiàn),在Excel中一個IF可以實現(xiàn)兩個分支,兩個IF嵌套在一起可以實現(xiàn)三個分支,三個IF嵌套在一起可以實現(xiàn)四個分支,依次類推……Excel的IF函數(shù)最多可以嵌套七層。三、邏輯函數(shù)多進(jìn)路如表2(全國計算機等級考試成績表)所示,若筆試和機試均及格,合格否字段則填充“合格”,否則填充“不合格”。我們首先想到了用IF函數(shù)實現(xiàn)這種雙分支的填充問題。但是,這個填充問題還有一個難點就是條件由兩部分組成,一個是筆試及格,另一個是機試及格,而且這兩個條件必須同時成立。因此,解決這個填充問題還需要邏輯函數(shù)的支持。在Excel中,邏輯函數(shù)有邏輯與AND()、邏輯或OR()、邏輯非NOT()三種。在表2(全國計算機等級考試成績表)中填充合格否的方法是:先將光標(biāo)定位到E3結(jié)果單元格中,輸入或設(shè)置函數(shù):=IF(AND(C3>=60,D3>=60),“合格”,“不合格”),然后利用填充柄填充E列中其余同學(xué)的合格否單元格,即可實現(xiàn)所有考生“合格否”列的自動填充。也可以使用函數(shù)邏輯或OR()實現(xiàn),方法是先將光標(biāo)定位到E3結(jié)果單元格中,輸入或設(shè)置函數(shù):=IF(OR(C3<60,D3<60),“不合格”,“合格”)然后利用填充柄填充E列中其余同學(xué)的合格否單元格,也可實現(xiàn)所有考生“合格否”列的自動填充。四、身份證號的編碼身份證號是每個公民都擁有的能夠唯一識別身份的編碼。根據(jù)《中華人民共和國國家標(biāo)準(zhǔn)GB11643-1999》中有關(guān)公民身份號碼的規(guī)定,公民身份證號是一組特征組合碼,由十七位數(shù)字本體碼和一位數(shù)字校驗碼組成。排列順序從左至右依次為:六位數(shù)字的地址碼、八位數(shù)字的出生日期碼、三位數(shù)字的順序碼和一位數(shù)字的校驗碼。如表3(員工信息一覽表)所示,由于身份證號中含有出生日期、性別和年齡等信息,因此每個員工的身份證號必須逐個輸入,而每個員工的出生日期、性別和年齡等信息可以從身份證號中提取。1.指定字符子串從身份證號中提取出生日期,需要用到MID函數(shù),其格式為MID(文本,開始位置,字符個數(shù)),其功能是返回文本字符串中從指定位置開始的指定字符數(shù)目的子串。從身份證號中提取出生日期方法是:從身份證號的第7位開始取8個字符的子串即可。提取出生日期的操作步驟是:先將光標(biāo)定位到D3結(jié)果單元格中,輸入或設(shè)置函數(shù):=MID(C3,7,8),然后利用填充柄填充D列中其余員工的出生日期單元格,即可實現(xiàn)所有員工出生日期的自動填充。2.性別特征的提取從身份證號中提取性別,需要用到IF、MID和MOD函數(shù),MOD函數(shù)的格式為MOD(x,y),其功能是返回x除以y的余數(shù)。由于身份證號的第17位和性別有關(guān)(若第17位為奇數(shù),性別為男,否則為女),因此應(yīng)先利用MID函數(shù)提取出身份證號的第17位,再利用MOD函數(shù)判斷是否為奇數(shù),最后用IF函數(shù)根據(jù)第17位數(shù)的奇偶填充不同的性別。提取性別的操作步驟是:先將光標(biāo)定位到E3結(jié)果單元格中,輸入或設(shè)置函數(shù):=IF(MOD(MID(C3,17,1),2)=1,“男”,“女”)然后利用填充柄填充E列中其余員工的性別單元格,即可實現(xiàn)所有員工性別的自動填充。3.出生日期的提取由于身份證號中含有出生日期的年份信息,因此將當(dāng)前日期的年份減去出生日期的年份,即可得到該員工的年齡。要得到當(dāng)前日期的年份:需要用到兩個函數(shù),一個是NOW(),該函數(shù)返回當(dāng)前的日期和時間;另一個是YEAR(日期),該函數(shù)返回日期時間參數(shù)所對應(yīng)的年份。因此YEAR(NOW())返回的即是當(dāng)前日期時間的年份。從身