王華偉++楊云

摘要：Excel函數(shù)是Excel預(yù)定義的內(nèi)置公式，用戶(hù)可通過(guò)設(shè)置函數(shù)參數(shù)的方法解決實(shí)際工作中有關(guān)數(shù)據(jù)計(jì)算和統(tǒng)計(jì)等問(wèn)題。該文以中職學(xué)生學(xué)籍信息表和中職學(xué)生體能測(cè)試成績(jī)統(tǒng)計(jì)表為例，詳細(xì)介紹了Excel函數(shù)在解決實(shí)際問(wèn)題中的應(yīng)用技巧。

關(guān)鍵詞： Excel函數(shù)；函數(shù)參數(shù)；應(yīng)用技巧

中圖分類(lèi)號(hào)：TP37 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2015）25-0096-03

隨著計(jì)算機(jī)的日益普及，辦公自動(dòng)化已深入到各行各業(yè)，多數(shù)辦公人員掌握了制作表格的基本操作，如表格內(nèi)容錄入、編輯打印及簡(jiǎn)單常見(jiàn)函數(shù)的使用等，但很少涉及函數(shù)庫(kù)中功能更強(qiáng)大的函數(shù)，如“邏輯”類(lèi)、“數(shù)學(xué)”類(lèi)、“文本”類(lèi)函數(shù)等。本文通過(guò)中職學(xué)校教務(wù)管理中的兩個(gè)案例來(lái)介紹相關(guān)函數(shù)的應(yīng)用技巧。

1 中職學(xué)生學(xué)籍信息表案例

每年新生入學(xué)，學(xué)校都要采集新生信息，制作學(xué)生學(xué)籍信息表。利用Excel函數(shù)從身份證號(hào)和家庭住址中提取信息，大大減少了信息錄入人員的工作量。

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

身份證號(hào)是一組特征組合碼，由17位數(shù)字本體碼和1位數(shù)字校驗(yàn)碼組成。排列順序從左至右依次為：6位地址碼、8位出生日期碼、3位順序碼和1位校驗(yàn)碼。當(dāng)每個(gè)學(xué)生的身份證號(hào)逐個(gè)錄入后，學(xué)生的出生日期、性別和年齡等信息就可以從身份證號(hào)中提取。中職學(xué)生學(xué)籍信息表如圖1所示。

1）提取出生日期

從身份證號(hào)中提取出生日期，需要用到兩個(gè)文本類(lèi)函數(shù)：MID與TEXT。MID函數(shù)，其語(yǔ)法格式為：MID（文本字符串，開(kāi)始位置，字符個(gè)數(shù)），功能是返回文本字符串中從指定位置開(kāi)始的指定字符個(gè)數(shù)的子串。TEXT函數(shù)，語(yǔ)法格式為：TEXT（數(shù)值，用引號(hào)括起的文本字符串的數(shù)字格式），功能是將數(shù)值內(nèi)容轉(zhuǎn)換為帶格式的文本，而其結(jié)果將不再作為數(shù)字參與計(jì)算。

從身份證號(hào)中提取出生日期的方法是：從身份證號(hào)的第7位開(kāi)始連續(xù)取8個(gè)數(shù)字的子串。操作步驟：先將光標(biāo)定位到圖1所示F4結(jié)果單元格中，輸入公式：=TEXT（MID（C4，7，8），"0000-00-00"），回車(chē)確認(rèn)，在F4單元格中就顯示出YYYY-MM-DD格式的數(shù)字文本，然后拖動(dòng)填充柄填充F列中其余學(xué)生的出生日期單元格，即可實(shí)現(xiàn)所有學(xué)生出生日期的自動(dòng)填充，結(jié)果如圖2所示。

2）填充性別

從身份證號(hào)中提取性別，需要用到三個(gè)函數(shù)：邏輯函數(shù)IF、文本函數(shù)MID和數(shù)學(xué)函數(shù)MOD。IF函數(shù)，其語(yǔ)法格式為：IF（條件表達(dá)式，滿(mǎn)足條件的結(jié)果，不滿(mǎn)足條件的結(jié)果），其功能是用來(lái)判斷是否滿(mǎn)足某個(gè)條件，滿(mǎn)足時(shí)返回一個(gè)值，不滿(mǎn)足時(shí)返回另一個(gè)值。它還可以和許多其他函數(shù)進(jìn)行嵌套，在Excel 2010中你可以在一個(gè) IF 函數(shù)內(nèi)最多嵌套 64 個(gè) IF 函數(shù)。而MOD函數(shù)，其語(yǔ)法格式為：MOD（被除數(shù)，除數(shù)），其功能是用于返回兩數(shù)相除的余數(shù)，返回結(jié)果的符號(hào)與除數(shù)的符號(hào)相同。

由于身份證號(hào)的第17位和性別有關(guān)，如果第17位為偶數(shù)，則性別為女，否則為男，因此先利用MID函數(shù)提取出身份證號(hào)的第17位，再利用MOD函數(shù)判斷是否為偶數(shù)，最后用IF函數(shù)根據(jù)第17位數(shù)的奇偶性填充不同的性別。

提取性別的操作步驟是：先將光標(biāo)定位到圖1所示E4結(jié)果單元格中，輸入公式：=IF（MOD（MID（C4，17，1），2）=0，"女"，"男"），回車(chē)確認(rèn)，在E4單元格中就顯示出性別，然后拖動(dòng)填充柄填充E列中其余學(xué)生的性別單元格，實(shí)現(xiàn)所有學(xué)生性別的自動(dòng)填充。

3）計(jì)算年齡

利用出生日期的年份減去當(dāng)前日期的年份，即可得到該學(xué)生的年齡。完成這一操作要用到三個(gè)函數(shù)：日期與時(shí)間函數(shù)NOW、YEAR和文本函數(shù)MID。函數(shù)NOW（）返回當(dāng)前的日期和時(shí)間；函數(shù)YEAR（日期）返回日期時(shí)間參數(shù)所對(duì)應(yīng)的年份。因此YEAR（NOW（））返回的即是當(dāng)前日期的年份。從身份證號(hào)中利用MID函數(shù)可提取出生日期的年份。

計(jì)算年齡的操作步驟是：先將光標(biāo)定位到圖1所示G4結(jié)果單元格中，輸入公式：=YEAR（NOW（））-MID（C4，7，4），回車(chē)確認(rèn)，在G4單元格中就顯示年齡的數(shù)值，然后拖動(dòng)填充柄填充G列中其余學(xué)生的年齡單元格，實(shí)現(xiàn)所有學(xué)生年齡的自動(dòng)填充。

1.2 從家庭住址中提取新生生源所在省、市、縣等信息

學(xué)生的家庭住址一般是根據(jù)戶(hù)口本首頁(yè)或身份證上的信息錄入的，是一個(gè)準(zhǔn)確詳實(shí)的信息，從中提取新生生源所在省、市、縣等信息，既減少了重復(fù)錄入的過(guò)程，又保證了信息的一致性。

1）提取省名

從家庭住址中提取省名，需要用到文本函數(shù)LEFT。LEFT函數(shù)，其語(yǔ)法格式為：LEFT（文本字符串，從左開(kāi)始截取的字符個(gè)數(shù)），功能是返回文本字符串中從左邊第1個(gè)字符開(kāi)始截取指定長(zhǎng)度的字符子串。利用LEFT函數(shù)從家庭住址字符串中，從左第1個(gè)字符起截取3個(gè)字符即得到新生生源所在省的名稱(chēng)。

提取省名的操作步驟是：先將光標(biāo)定位到圖1所示I4結(jié)果單元格中，輸入公式：=LEFT（H4，3），回車(chē)確認(rèn)，在I4單元格中就顯示出新生生源所在省的名稱(chēng)，然后拖動(dòng)填充柄填充I列中其余學(xué)生的生源所在省單元格，即可實(shí)現(xiàn)所有學(xué)生生源所在省的自動(dòng)填充， 結(jié)果如圖2所示。

2）提取市名、縣/區(qū)名及詳細(xì)地址

利用文本函數(shù)MID可從家庭住址中提取市名、縣/區(qū)名及詳細(xì)地址。從家庭住址字符串中第4個(gè)字符起截取3個(gè)字符即得到新生生源所在的市名，從第7個(gè)字符起截取3個(gè)字符即得到新生生源所在的縣/區(qū)名，而從第10個(gè)字符起截提取7個(gè)字符即得到新生生源的詳細(xì)地址名稱(chēng)。

其操作步驟是：先將光標(biāo)定位到圖1所示J4結(jié)果單元格中，輸入公式：=MID（H4，4，3），回車(chē)確認(rèn)；再將光標(biāo)定位到K4結(jié)果單元格中，輸入公式：=MID（H4，7，3），回車(chē)確認(rèn)；最后將光標(biāo)定位到L4結(jié)果單元格中，輸入公式：=MID（H4，10，16），回車(chē)確認(rèn)，則在J4、K4、L4單元格中就顯示新生的市名、縣/區(qū)名及詳細(xì)地址。然后選中J4、K4、L4單元格，拖動(dòng)填充柄填充J、L、K列中其余學(xué)生的相關(guān)信息單元格，結(jié)果如圖2所示。

2 中職學(xué)生體能測(cè)試成績(jī)統(tǒng)計(jì)表案例

為全面推進(jìn)素質(zhì)教育，國(guó)家對(duì)在校學(xué)生體質(zhì)健康方面提出了基本要求，要求各類(lèi)學(xué)校對(duì)在校學(xué)生進(jìn)行體能測(cè)試，測(cè)試成績(jī)由測(cè)試人員匯總，按要求錄入到電子表格并按照《國(guó)家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》進(jìn)行等級(jí)轉(zhuǎn)換，得出測(cè)評(píng)結(jié)果，這些數(shù)據(jù)最終通過(guò)“中國(guó)學(xué)生體質(zhì)健康網(wǎng)”按時(shí)上報(bào)至國(guó)家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)數(shù)據(jù)管理系統(tǒng)。測(cè)試人員錄入測(cè)試原始數(shù)據(jù)后，逐人逐項(xiàng)目填寫(xiě)健康等級(jí)，工作量極大。如圖3所示，利用IF邏輯函數(shù)轉(zhuǎn)換各測(cè)試項(xiàng)目的健康等級(jí)，極大地減少錄入工作量，提高正確率。

1）體重指數(shù)（BMI）及體重健康等級(jí)填充

體重指數(shù)（BMI）是用體重公斤數(shù)除以身高米數(shù)平方得出的數(shù)字。體重的健康等級(jí)由體重指數(shù)來(lái)決定，中職學(xué)生體重健康標(biāo)準(zhǔn)如表1所示。

體重指數(shù)填充的操作是：先將光標(biāo)定位到圖3所示G3結(jié)果單元格中，輸入公式：=F3/（E3/100*E3/100），回車(chē)確認(rèn)，在G3單元格中就顯示出體重指數(shù)，再根據(jù)體重指數(shù)填充體重健康等級(jí)，將光標(biāo)定位到H3結(jié)果單元格中，輸入公式：=IF（C3=1，IF（G3<=23.8，IF（G3>=17.2，"正常"，"低體重"），IF（G3>=27.4，"肥胖"， IF（G3>=23.9，"超重"））），IF（G3<=23.4，IF（G3>=17，"正常"，"低體重"），IF（G3>=25.8，"肥胖"，IF（G3>=23.4，"超重"）））），回車(chē)確認(rèn)，在H3單元格中就顯示出體重健康等級(jí)，然后選中G3、H3單元格拖動(dòng)填充柄填充G、H列中其余學(xué)生的體重指數(shù)及體重健康等級(jí)單元格，實(shí)現(xiàn)所有學(xué)生體重指數(shù)及體重健康等級(jí)的自動(dòng)填充，操作結(jié)果如圖4所示。

2）肺活量等測(cè)試項(xiàng)目的健康等級(jí)填充

根據(jù)《國(guó)家學(xué)生體質(zhì)健康標(biāo)準(zhǔn)》，使用同樣的操作步驟，對(duì)肺活量、50米跑、立定跳遠(yuǎn)、坐位體前屈等測(cè)試項(xiàng)目的健康等級(jí)使用IF函數(shù)嵌套的方法進(jìn)行填充，操作結(jié)果如圖4所示。

3 結(jié)束語(yǔ)

通過(guò)兩個(gè)案例的實(shí)現(xiàn)，詳細(xì)介紹了Excel函數(shù)在解決實(shí)際問(wèn)題中的應(yīng)用技巧。Excel函數(shù)在解決較難的實(shí)際問(wèn)題時(shí)往往需要多個(gè)函數(shù)嵌套使用才能實(shí)現(xiàn)，在公式中所有的符號(hào)一律使用英文符號(hào)，如果使用了中文符號(hào)，系統(tǒng)會(huì)給出錯(cuò)誤信息，導(dǎo)致無(wú)法完成操作。工作中，我們可通過(guò)查看Excel函數(shù)幫助進(jìn)行學(xué)習(xí)，積累解決實(shí)際問(wèn)題的能力，達(dá)到了事半功倍，提高工作效率的目的。

參考文獻(xiàn)：

[1] 李斌. Excel 2010應(yīng)用大全[M].機(jī)械工業(yè)出版社，2010.

[2] 張玉鑫. Excel函數(shù)在教務(wù)管理中的應(yīng)用實(shí)例[J].中國(guó)商界，2010（5）.