Excel中函數(shù)的使用技巧與實(shí)例

趙俊峰

（太原大學(xué)外語(yǔ)師范學(xué)院，山西 太原 030012）

Excel中函數(shù)的使用技巧與實(shí)例

趙俊峰

（太原大學(xué)外語(yǔ)師范學(xué)院，山西 太原 030012）

文章通過(guò)學(xué)生成績(jī)與等級(jí)評(píng)定表、全國(guó)計(jì)算機(jī)等級(jí)考試成績(jī)表、員工信息一覽表三個(gè)實(shí)例表格闡述了RANK、IF、MID、MOD、AND、YEAR和NOW等函數(shù)的用法和技巧。在利用Excel的函數(shù)解決較難的數(shù)據(jù)計(jì)算問(wèn)題時(shí)，往往需要多個(gè)函數(shù)的聯(lián)合使用才能實(shí)現(xiàn)。

Excel；函數(shù)；技巧；實(shí)例

函數(shù)是Excel預(yù)定義的內(nèi)置公式，可以通過(guò)設(shè)置函數(shù)參數(shù)的方法利用函數(shù)解決實(shí)際工作中有關(guān)數(shù)據(jù)計(jì)算和統(tǒng)計(jì)等問(wèn)題。Excel根據(jù)不同的應(yīng)用提供了不同類(lèi)型的函數(shù)，如財(cái)務(wù)函數(shù)、數(shù)學(xué)函數(shù)、統(tǒng)計(jì)函數(shù)、數(shù)據(jù)庫(kù)函數(shù)、邏輯函數(shù)、文本函數(shù)和日期時(shí)間函數(shù)等。下面通過(guò)實(shí)例介紹幾個(gè)常用函數(shù)的使用技巧。

一、利用RANK函數(shù)自動(dòng)填充名次

在Excel中，填充名次最簡(jiǎn)單的方法是先按某一數(shù)值關(guān)鍵字（如均分）遞減排序，然后利用填充柄在名次單元格中依次填充1、2、3……這種方法雖然簡(jiǎn)單，但如果有名次并列的情況則無(wú)法自動(dòng)填充，還需要手動(dòng)地修改相關(guān)的并列名次。而Excel中的RANK函數(shù)卻可以真正地做到名次的自動(dòng)填充。RANK函數(shù)的格式是RANK（數(shù)值，單元格區(qū)域），其功能是返回?cái)?shù)值在單元格區(qū)域中的排位。在使用RANK函數(shù)時(shí)，要求單元格區(qū)域必須是絕對(duì)引用。在表1（學(xué)生成績(jī)與等級(jí)評(píng)定表）中填充名次的方法是先將光標(biāo)定位到D3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=RANK（D3,$D$3:$D$7），然后利用填充柄填充D列中其余同學(xué)的名次單元格，即可實(shí)現(xiàn)所有學(xué)生名次的自動(dòng)填充。利用RANK函數(shù)無(wú)需對(duì)表格排序，即可自動(dòng)填充名次。

表1 學(xué)生成績(jī)與等級(jí)評(píng)定表

二、利用IF函數(shù)實(shí)現(xiàn)多分支填充

Excel中IF函數(shù)的格式是IF（條件，條件為真的結(jié)果，條件為假的結(jié)果），其作用是根據(jù)條件的真假，填充不同的結(jié)果。如表1（學(xué)生成績(jī)與等級(jí)評(píng)定表）所示，可以根據(jù)均分的高低填充不同的等級(jí)。

1.雙分支填充

先將光標(biāo)定位到E3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=IF（C3＞=60,“及格”,“不及格”），然后利用填充柄填充E列中其余同學(xué)的等級(jí)單元格，即可實(shí)現(xiàn)兩種等級(jí)的填充。

2.三分支填充

先將光標(biāo)定位到E3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=IF（C3＞=60,IF（C3＞=80,“好”,“中”“差”），然后利用填充柄填充E列中其余同學(xué)的等級(jí)單元格，即可實(shí)現(xiàn)三種等級(jí)的填充。

3.四分支填充

先將光標(biāo)定位到E3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=IF（C3＞=75,IF（C3＞=85,“優(yōu)秀”,“良好”）,IF（C3＞=60,“及格”,“不及格”），然后利用填充柄填充E列中其余同學(xué)的等級(jí)單元格，即可實(shí)現(xiàn)四種等級(jí)的填充。

從上面我們不難發(fā)現(xiàn)，在Excel中一個(gè)IF可以實(shí)現(xiàn)兩個(gè)分支，兩個(gè)IF嵌套在一起可以實(shí)現(xiàn)三個(gè)分支，三個(gè)IF嵌套在一起可以實(shí)現(xiàn)四個(gè)分支，依次類(lèi)推……Excel的IF函數(shù)最多可以嵌套七層。

三、巧用邏輯函數(shù)解決多條件問(wèn)題

如表2（全國(guó)計(jì)算機(jī)等級(jí)考試成績(jī)表）所示，若筆試和機(jī)試均及格，合格否字段則填充“合格”，否則填充“不合格”。我們首先想到了用IF函數(shù)實(shí)現(xiàn)這種雙分支的填充問(wèn)題。但是，這個(gè)填充問(wèn)題還有一個(gè)難點(diǎn)就是條件由兩部分組成，一個(gè)是筆試及格，另一個(gè)是機(jī)試及格，而且這兩個(gè)條件必須同時(shí)成立。因此，解決這個(gè)填充問(wèn)題還需要邏輯函數(shù)的支持。在Excel中，邏輯函數(shù)有邏輯與 AND（）、邏輯或 OR（）、邏輯非 NOT（）三種。

在表2（全國(guó)計(jì)算機(jī)等級(jí)考試成績(jī)表）中填充合格否的方法是：先將光標(biāo)定位到E3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=IF(AND(C3＞=60,D3＞=60),“合格”,“不合格”)，然后利用填充柄填充E列中其余同學(xué)的合格否單元格，即可實(shí)現(xiàn)所有考生“合格否”列的自動(dòng)填充。

表2 全國(guó)計(jì)算機(jī)等級(jí)考試成績(jī)表

也可以使用函數(shù)邏輯或OR（）實(shí)現(xiàn)，方法是先將光標(biāo)定位到E3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=IF(OR(C3＜60,D3＜60),“不合格”,“合格”)然后利用填充柄填充E列中其余同學(xué)的合格否單元格，也可實(shí)現(xiàn)所有考生“合格否”列的自動(dòng)填充。

四、利用函數(shù)提取身份證號(hào)中的相關(guān)信息

身份證號(hào)是每個(gè)公民都擁有的能夠唯一識(shí)別身份的編碼。根據(jù)《中華人民共和國(guó)國(guó)家標(biāo)準(zhǔn)GB 11643-1999》中有關(guān)公民身份號(hào)碼的規(guī)定，公民身份證號(hào)是一組特征組合碼，由十七位數(shù)字本體碼和一位數(shù)字校驗(yàn)碼組成。排列順序從左至右依次為：六位數(shù)字的地址碼、八位數(shù)字的出生日期碼、三位數(shù)字的順序碼和一位數(shù)字的校驗(yàn)碼。如表3（員工信息一覽表）所示，由于身份證號(hào)中含有出生日期、性別和年齡等信息，因此每個(gè)員工的身份證號(hào)必須逐個(gè)輸入，而每個(gè)員工的出生日期、性別和年齡等信息可以從身份證號(hào)中提取。

表3 員工信息一覽表

1.出生日期的提取

從身份證號(hào)中提取出生日期，需要用到MID函數(shù)，其格式為MID(文本,開(kāi)始位置,字符個(gè)數(shù))，其功能是返回文本字符串中從指定位置開(kāi)始的指定字符數(shù)目的子串。從身份證號(hào)中提取出生日期方法是：從身份證號(hào)的第7位開(kāi)始取8個(gè)字符的子串即可。提取出生日期的操作步驟是：先將光標(biāo)定位到D3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=MID（C3,7,8），然后利用填充柄填充D列中其余員工的出生日期單元格，即可實(shí)現(xiàn)所有員工出生日期的自動(dòng)填充。

2.性別的填充

從身份證號(hào)中提取性別，需要用到IF、MID和MOD函數(shù)，MOD函數(shù)的格式為MOD(x,y)，其功能是返回x除以y的余數(shù)。由于身份證號(hào)的第17位和性別有關(guān)（若第17位為奇數(shù)，性別為男，否則為女），因此應(yīng)先利用MID函數(shù)提取出身份證號(hào)的第17位，再利用MOD函數(shù)判斷是否為奇數(shù)，最后用IF函數(shù)根據(jù)第17位數(shù)的奇偶填充不同的性別。提取性別的操作步驟是：先將光標(biāo)定位到E3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=IF(MOD(MID(C3,17,1),2)=1,“男”,“女”)然后利用填充柄填充E列中其余員工的性別單元格，即可實(shí)現(xiàn)所有員工性別的自動(dòng)填充。

3.年齡的計(jì)算

由于身份證號(hào)中含有出生日期的年份信息，因此將當(dāng)前日期的年份減去出生日期的年份，即可得到該員工的年齡。要得到當(dāng)前日期的年份：需要用到兩個(gè)函數(shù)，一個(gè)是NOW（），該函數(shù)返回當(dāng)前的日期和時(shí)間；另一個(gè)是YEAR（日期），該函數(shù)返回日期時(shí)間參數(shù)所對(duì)應(yīng)的年份。因此YEAR（NOW（））返回的即是當(dāng)前日期時(shí)間的年份。從身份證號(hào)中利用MID即可提取出的出生日期的年份。計(jì)算年齡的操作步驟是：先將光標(biāo)定位到F3結(jié)果單元格中，輸入或設(shè)置函數(shù)：=YEAR(NOW())-MID(C3,7,4)，然后利用填充柄填充F列中其余員工的年齡單元格，即可實(shí)現(xiàn)所有員工年齡的自動(dòng)填充。

［1］薛學(xué)勤.全國(guó)計(jì)算機(jī)等級(jí)考試一級(jí)MS OFFICE教程［M］.天津：南開(kāi)出版社，2004.

［2］趙俊峰.山西省中職學(xué)校通用教材《計(jì)算機(jī)》［M］.太原：山西人民出版社，2008.

G642

A

1673-0046（2010）7-0126-02

該論文為課題“基于《計(jì)算機(jī)公共基礎(chǔ)》的網(wǎng)絡(luò)課程設(shè)計(jì)與研究”的課題成果］