【文章內(nèi)容簡(jiǎn)介】 中根據(jù)不同的成績(jī)區(qū)分合格與不合格。現(xiàn)在我們就以某班級(jí)的英語(yǔ)成績(jī)?yōu)槔唧w說(shuō)明用法。 圖 6 某班級(jí)的成績(jī)?nèi)鐖D 6 所示，為了做出最終的綜合評(píng)定，我們?cè)O(shè)定按照平均分判斷該學(xué)生成績(jī)是否合格的規(guī)則。如果各科平均分超過(guò) 60 分則認(rèn)為是合格的，否則記作不合格。 根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫公 式（以單元格 B12 為例）： =IF(B1160,合格 ,不合格 ) 語(yǔ)法解釋為，如果單元格 B11 的值大于 60，則執(zhí)行第二個(gè)參數(shù)即在單元格 B12中顯示合格字樣，否則執(zhí)行第三個(gè)參數(shù)即在單元格 B12 中顯示不合格字樣。 在綜合評(píng)定欄中可以看到由于 C 列的同學(xué)各科平均分為 54 分，綜合評(píng)定為不合格。其余均為合格。 多層嵌套函數(shù)的應(yīng)用 在上述的例子中，我們只是將成績(jī)簡(jiǎn)單區(qū)分為合格與不合格，在實(shí)際應(yīng)用中，成績(jī)通常是有多個(gè)等級(jí)的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來(lái) 實(shí)現(xiàn)。仍以上例為例，我們?cè)O(shè)定綜合評(píng)定的規(guī)則為當(dāng)各科平均分超過(guò) 90 時(shí)，評(píng)定為優(yōu)秀。如圖 7 所示。 圖 7 說(shuō)明：為了解釋起來(lái)比較方便，我們?cè)谶@里僅做兩重嵌套的示例，您可以按照實(shí)際情況進(jìn)行更多重的嵌套，但請(qǐng)注意 Excel的 IF 函數(shù)最多允許七重嵌套。 根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫公式（以單元格 F12 為例）： =IF(F1160,IF(AND(F1190),優(yōu)秀 ,合格 ),不合格 ) 語(yǔ)法解釋為，如果單元格 F11 的值大于 60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格 F11 的值是否大 于 90（為了讓大家體會(huì)一下 AND 函數(shù)的應(yīng)用，寫成 AND(F1190)，實(shí)際上可以僅寫 F1190），如果滿足在單元格 F12 中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果 F11 的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(shù)即在單元格 F12 中顯示不合格字樣。 在綜合評(píng)定欄中可以看到由于 F 列的同學(xué)各科平均分為 92 分，綜合評(píng)定為優(yōu)秀。 （三）根據(jù)條件計(jì)算值 在了解了 IF 函數(shù)的使用方法后，我們?cè)賮?lái)看看與之類似的 Excel提供的可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使 用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF 工作表函數(shù)。關(guān)于 SUMIF 函數(shù)在數(shù)學(xué)與三角函數(shù)中以做了較為詳細(xì)的介紹。這里重點(diǎn)介紹 COUNTIF 的應(yīng)用。 COUNTIF 可以用來(lái)計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。比如在成績(jī)表中計(jì)算每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。在工資表中求出所有基本工資在 2020 元以上的員工數(shù)。 語(yǔ)法形式為 COUNTIF(range,criteria)。其中 Range 為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria 確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 33 3 apples。 成績(jī)表 這里仍以上述成績(jī)表的例子說(shuō)明一些應(yīng)用方法。我們需要計(jì)算的是：每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。規(guī)則為成績(jī)大于 90 分記做優(yōu)秀。如圖 8 所示 圖 8 根據(jù)這一規(guī)則，我們?cè)趦?yōu)秀門數(shù)中寫公式（以單元格 B13 為例）： =COUNTIF(B4:B10,90) 語(yǔ)法解釋為，計(jì)算 B4 到 B10 這個(gè)范圍，即 jarry 的各科成績(jī)中有多少個(gè)數(shù)值大于 90 的單元格。 在優(yōu)秀門 數(shù)欄中可以看到 jarry 的優(yōu)秀門數(shù)為兩門。其他人也可以依次看到。 銷售業(yè)績(jī)表 銷售業(yè)績(jī)表可能是綜合運(yùn)用 IF、 SUMIF、 COUNTIF 非常典型的示例。比如，可能希望計(jì)算銷售人員的訂單數(shù)，然后匯總每個(gè)銷售人員的銷售額，并且根據(jù)總發(fā)貨量決定每次銷售應(yīng)獲得的獎(jiǎng)金。 原始數(shù)據(jù)表如圖 9 所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號(hào)排列） 圖 9 原始數(shù)據(jù)表 按銷售人員匯總表如圖 10 所示 圖 10 銷售人員匯總表 如圖 10 所示的表完全是利用函數(shù)計(jì)算的方法自動(dòng)匯總的數(shù)據(jù)。首先建立一個(gè)按照銷售人員匯總的 表單樣式，如圖所示。然后分別計(jì)算訂單數(shù)、訂單總額、銷售獎(jiǎng)金。 （ 1） 訂單數(shù) 用 COUNTIF 計(jì)算銷售人員的訂單數(shù)。 以銷售人員 ANNIE 的訂單數(shù)公式為例。公式： =COUNTIF($C$2:$C$13,A17) 語(yǔ)法解釋為計(jì)算單元格 A17(即銷售人員 ANNIE)在 銷售人員 清單 $C$2:$C$13的范圍內(nèi)（即圖 9 所示的原始數(shù)據(jù)表）出現(xiàn)的次數(shù)。 這個(gè)出現(xiàn)的次數(shù)即可認(rèn)為是該銷售人員 ANNIE 的訂單數(shù)。 （ 2） 訂單總額 用 SUMIF 匯總每個(gè)銷售人員的銷售額。 以銷售人員 ANNIE 的訂單總 額公式為例。公式： =SUMIF($C$2:$C$13,A17,$B$2:$B$13) 此公式在 銷售人員 清單 $C$2:$C$13 中檢查單元格 A17 中的文本（即銷售人員 ANNIE），然后計(jì)算 訂單金額 列（ $B$2:$B$13）中相應(yīng)量的和。 這個(gè)相應(yīng)量的和就是銷售人員 ANNIE 的訂單總額。 （ 3） 銷售獎(jiǎng)金 用 IF 根據(jù)訂單總額決定每次銷售應(yīng)獲得的獎(jiǎng)金。 假定公司的銷售獎(jiǎng)金規(guī)則為當(dāng)訂單總額超過(guò) 5 萬(wàn)元時(shí)，獎(jiǎng)勵(lì)幅度為百分之十五，否則為百分之十。根據(jù)這一規(guī)則仍以銷售人員 ANNIE 為例說(shuō)明。公式為： =IF(C1750000,10%,15%)*C17 如果訂單總額小于 50000 則獎(jiǎng)金為 10%；如果訂單總額大于等于 50000，則獎(jiǎng)金為 15%。 至此，我們已完全了解了 EXCEL 函數(shù)的邏輯函數(shù)，相信大家在實(shí)際工作中會(huì)想出更多更有用的運(yùn)用。 所謂文本函數(shù)，就是可以在公式中處理文字串的函數(shù)。例如，可以改變大小寫或確定文字串的長(zhǎng)度；可以替換某些字符或者去除某些字符等。而日期和時(shí)間函數(shù)則可以在公式中分析和處理日期值和時(shí)間值。關(guān)于這兩類函數(shù)的列表參看附表，這里僅對(duì)一些常用的函數(shù)做簡(jiǎn)要介紹。 一、文本 函數(shù) （一）大小寫轉(zhuǎn)換 LOWER將一個(gè)文字串中的所有大寫字母轉(zhuǎn)換為小寫字母。 UPPER將文本轉(zhuǎn)換成大寫形式。 PROPER將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫。將其余的字母轉(zhuǎn)換成小寫。 這三種函數(shù)的基本語(yǔ)法形式均為 函數(shù)名（ text）。示例說(shuō)明： 已有字符串為： pLease ComE Here! 可以看到由于輸入的不規(guī)范，這句話大小寫亂用了。 通過(guò)以上三個(gè)函數(shù)可以將文本轉(zhuǎn)換顯示樣式，使得文本變得規(guī)范。參見(jiàn)圖 1 Lower（ pLease ComE Here!） = please e here! upper（ pLease ComE Here!） = PLEASE COME HERE! proper（ pLease ComE Here!） = Please Come Here! 圖 1 （二）取出字符串中的部分字符 您可以使用 Mid、 Left、 Right等函數(shù)從長(zhǎng)字符串內(nèi)獲取一部分字符。具體語(yǔ)法格式為 LEFT函數(shù)： LEFT(text,num_chars)其中 Text是包含要提取字符的文本串。 Num_chars指定要由 LEFT 所提取的字符數(shù)。 MID函數(shù)： MID(text,start_num,num_chars)其中 Text是包含要提取字符的文本串。Start_num是文本中要提 取的第一個(gè)字符的位置。 RIGHT函數(shù)： RIGHT(text,num_chars)其中 Text是包含要提取字符的文本串。 Num_chars指定希望 RIGHT 提取的字符數(shù)。 比如，從字符串 This is an apple.分別取出字符 This、 apple、 is的具體函數(shù)寫法為。 LEFT(This is an apple,4)=This RIGHT(This is an apple,5)=apple MID(This is an apple,6,2)=is Excel 函數(shù)應(yīng)用回顧 ● Excel 函數(shù)應(yīng)用之邏輯函數(shù) ● Excel 函數(shù)應(yīng)用之?dāng)?shù)學(xué)和三角函數(shù) ● Excel 函數(shù)應(yīng)用之函數(shù)簡(jiǎn)介 圖 2 （三）去除字符串的空白 在字符串形態(tài)中，空白也是一個(gè)有效的字符，但是如果字符串中出現(xiàn)空白字符時(shí)，容易在判斷或?qū)Ρ葦?shù)據(jù)是發(fā)生錯(cuò)誤，在 Excel中您可以使用 Trim函數(shù)清除字符串中的空白。 語(yǔ)法形式為： TRIM(text)其中 Text為需要清除其中空格的文本。 需要注意的是， Trim函數(shù)不會(huì)清除 單詞之間的單個(gè)空格，如果連這部分空格都需清除的話，建議使用替換功能。比如，從字符串 My name is Mary中清除空格的函數(shù)寫法為： TRIM(My name is Mary)=My name is Mary 參見(jiàn)圖 3 圖 3 （四）字符串的比較 在數(shù)據(jù)表中經(jīng)常會(huì)比對(duì)不同的字符串，此 時(shí)您可以使用 EXACT函數(shù)來(lái)比較兩個(gè)字符串是否相同。該函數(shù)測(cè)試兩個(gè)字符串是否完全相同。如果它們完全相同，則返回 TRUE；否則，返回 FALSE。函數(shù) EXACT 能區(qū)分大小寫，但忽略格式上的差異。利用函數(shù) EXACT 可以測(cè)試輸入文檔內(nèi)的文字。語(yǔ)法形式為：EXACT(text1,text2)Text1為待比較的第一個(gè)字符串。 Text2為待比較的第二個(gè)字符串。舉例說(shuō)明：參見(jiàn)圖 4 EXACT(China,china)=False 圖 4 二、日期與時(shí)間函數(shù) 在數(shù)據(jù)表的處理過(guò)程中，日期與時(shí)間的函數(shù)是相當(dāng)重要的處理依據(jù)。而 Excel在這方面也提供了相當(dāng)豐富的函數(shù)供大家使用。 （一）取出當(dāng)前系統(tǒng)時(shí)間 /日期信息 用于取出當(dāng)前系統(tǒng)時(shí)間 /日期信息的函數(shù)主要有 NOW、 TODAY。 語(yǔ)法形式均為 函數(shù)名（）。 （二）取得日期 /時(shí)間的部分字段值 如果需要單獨(dú)的年份、月份、日數(shù)或小時(shí)的數(shù)據(jù)時(shí)，可以 使用 HOUR、 DAY、 MONTH、 YEAR函數(shù)直接從日期 /時(shí)間中取出需要的數(shù)據(jù)。具體示例參看圖 5。 比如，需要返回 2020530 12:30 PM的年份、月份、日數(shù)及小時(shí)數(shù)，可以分別采用相應(yīng)函數(shù)實(shí)現(xiàn)。 YEAR(E5)=2020 MONTH(E5)=5 DAY(E5)=30 HOUR(E5)=12 圖 5 此外還有更多有用的日期 /時(shí)間函數(shù)，可以查閱附表。下面我們將以一個(gè)具體的示例來(lái)說(shuō)明 Excel的文本函數(shù)與日期函數(shù)的用途。 三、示例：做一個(gè)美觀簡(jiǎn)潔的人事資料分析表 示例說(shuō)明 在如圖 6所示的某公司人事資料表中，除了編號(hào)、員工姓名、身份證號(hào)碼以及參加工作時(shí)間為手工添入外，其余各項(xiàng)均為用函數(shù)計(jì)算所得。 圖 6 在此例中我們將詳細(xì)說(shuō)明如何通過(guò)函數(shù)求出： （ 1）自動(dòng)從身份證號(hào)碼中提取出生年月、性別信息。 （ 2）自動(dòng)從參加工作時(shí)間中提取工齡信息。 身份證號(hào)碼相關(guān)知識(shí) 在了解如何實(shí)現(xiàn)自動(dòng)從身份證號(hào)碼中提取出生年月、性別信息之前，首先需要了解身份證號(hào)碼所代表的含義。我們知道，當(dāng)今的身份證號(hào)碼有 15/18位之分。早期簽發(fā)的身份證號(hào)碼是 15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴(kuò)展（由兩位變?yōu)樗奈唬┖湍┪布恿诵?yàn)碼，就成了 18位。這兩種身份證號(hào)碼將在相當(dāng)長(zhǎng)的一段時(shí)期內(nèi)共存。兩 種身份證號(hào)碼的含義如下： （ 1） 15位的身份證號(hào)碼： 1~6位為地區(qū)代碼， 7~8位為出生年份 (2位 )， 9~10位為出生月份， 11~12位為出生日期，第 13~15位為順序號(hào)，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。 （ 2） 18位的身份證號(hào)碼： 1~6位為地區(qū)代碼， 7~10位為出生年份 (4位 )， 11~12位為出生月份， 13~14位為出生日期，第 15~17位為順序號(hào)，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。 18位為效驗(yàn)位。 應(yīng)用函數(shù) 在此例中為了實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)提取，應(yīng)用了如下幾個(gè) Excel函數(shù)。 （ 1） IF函 數(shù)：根據(jù)邏輯表達(dá)式測(cè)試的結(jié)果，返回相應(yīng)的值。 IF函數(shù)允許嵌套。 語(yǔ)法形式為： IF（ logical_test, value_if_true,value_if_false） （ 2） CONCATENATE：將若干個(gè)文字項(xiàng)合并至一個(gè)文字項(xiàng)中。 語(yǔ)法形式為： CONCATENATE(text1,text2??) （ 3） MID：從文本字符串中指定的起始位置起，返回指定長(zhǎng)度的字符。 語(yǔ)法形式為： MID(text,start_num,num_chars) （ 4） TODAY：返回計(jì)算機(jī)系統(tǒng)內(nèi)部的當(dāng)前日