【文章內(nèi)容簡(jiǎn)介】 法解釋為，如果單元格 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)定中寫(xiě)公式（以單元格 F12 為例）： =IF(F1160,IF(AND(F1190),優(yōu)秀 ,合格 ),不合格 ) 語(yǔ)法解釋為，如果單元格 F11 的值大于 60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格 F11 的值是否大于 90（為了讓大家體會(huì)一下 AND函數(shù)的應(yīng)用，寫(xiě)成 AND(F1190)，實(shí)際上可以僅寫(xiě) 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)看看與之類(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á)式或文本。例如，條件可以表示為 3 3 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)秀門(mén)數(shù)中寫(xiě)公式（以單元格 B13 為例）： =COUNTIF(B4:B10,90) 語(yǔ)法解釋為，計(jì)算 B4到 B10 這個(gè)范圍，即 jarry 的各科成績(jī)中有多少個(gè)數(shù)值大于 90 的單元格。 在優(yōu)秀門(mén)數(shù)欄中可以看到 jarry 的優(yōu)秀門(mén)數(shù)為兩門(mén)。其他人也可以依次看到。 銷(xiāo)售業(yè)績(jī)表 銷(xiāo)售業(yè)績(jī)表可能是綜合運(yùn)用 IF、 SUMIF、 COUNTIF 非常典型的示例。比如，可能希望計(jì)算銷(xiāo)售人員的訂單數(shù)，然后匯總每個(gè)銷(xiāo)售人員的銷(xiāo)售額，并且根據(jù)總發(fā)貨量決定每次銷(xiāo)售應(yīng)獲得的獎(jiǎng)金。 原始數(shù)據(jù)表如圖 9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號(hào)排列） 圖 9 原始數(shù)據(jù)表 按銷(xiāo)售人員匯總表如圖 10 所示 圖 10 銷(xiāo)售人員匯總表 如圖 10 所示的表完全是利用函數(shù)計(jì)算的方法自動(dòng)匯總的數(shù)據(jù)。首先建立一個(gè)按照銷(xiāo)售人員匯總的表單樣式，如圖所示。然后分別計(jì)算訂單數(shù)、 訂單總額、銷(xiāo)售獎(jiǎng)金。 （ 1） 訂單數(shù) 用 COUNTIF 計(jì)算銷(xiāo)售人員的訂單數(shù)。 以銷(xiāo)售人員 ANNIE 的訂單數(shù)公式為例。公式： =COUNTIF($C$2:$C$13,A17) 語(yǔ)法解釋為計(jì)算單元格 A17(即銷(xiāo)售人員 ANNIE)在 銷(xiāo)售人員 清單 $C$2:$C$13的范圍內(nèi)（即圖 9所示的原始數(shù)據(jù)表）出現(xiàn)的次數(shù)。 這個(gè)出現(xiàn)的次數(shù)即可認(rèn)為是該銷(xiāo)售人員 ANNIE 的訂單數(shù)。 （ 2） 訂單總額 用 SUMIF 匯總每個(gè)銷(xiāo)售人員的銷(xiāo)售額。 以銷(xiāo)售人員 ANNIE 的訂單總額公式為例。公式： =SUMIF($C$2:$C$13,A17,$B$2:$B$13) 此公式在 銷(xiāo)售人員 清單 $C$2:$C$13 中檢查單元格 A17 中的文本（即銷(xiāo)售人員ANNIE），然后計(jì)算 訂單金額 列（ $B$2:$B$13）中相應(yīng)量的和。 這個(gè)相應(yīng)量的和就是銷(xiāo)售人員 ANNIE 的訂單總額。 （ 3） 銷(xiāo)售獎(jiǎng)金 用 IF 根據(jù)訂單總額決定每次銷(xiāo)售應(yīng)獲得的獎(jiǎng)金。 假定公司的銷(xiāo)售獎(jiǎng)金規(guī)則為當(dāng)訂單總額超過(guò) 5萬(wàn)元時(shí)，獎(jiǎng)勵(lì)幅度為百分之十五，否則為百分之十。根據(jù)這一規(guī)則仍以銷(xiāo)售人員 ANNIE 為例說(shuō)明。公式為： =IF(C1750000,10%,15%)*C17 如果訂單總額小于 50000 則獎(jiǎng)金為 10%；如果訂單總額大于等于 50000，則獎(jiǎng)金為 15%。 至此，我們已完全了解了 excel 函數(shù)的邏輯函數(shù)，相信大家在實(shí)際工作中會(huì)想出更多更有用的運(yùn)用。 [dvnews_page=excel 函數(shù) 應(yīng)用之文本 /日期 /時(shí)間函數(shù) ]所謂文本函數(shù)，就是可以在公式中處理文字串的函數(shù)。例如，可以改變大小寫(xiě)或確定文字串的長(zhǎng)度；可以替換某些字符或者去除某些字符等。而日期和時(shí)間函數(shù)則可以在公式中分析和處理日期值和時(shí)間值。關(guān)于這兩類(lèi)函數(shù)的列表參看附表，這里僅對(duì)一些常用的函數(shù)做簡(jiǎn)要介紹。 一、文本函數(shù) （一）大小寫(xiě)轉(zhuǎn)換 LOWER將一個(gè)文字串中的所有大寫(xiě)字母轉(zhuǎn)換為小寫(xiě)字母。 UPPER將文本轉(zhuǎn)換成大寫(xiě)形式。 PROPER將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫(xiě)。將其余的字母轉(zhuǎn)換成小寫(xiě)。 這三種函數(shù)的基本語(yǔ)法形式均為 函數(shù)名（ text）。示例說(shuō)明： 已有字符串為： pLease ComE Here! 可以看到由于輸入的不規(guī)范，這句話大小寫(xiě)亂用了。 通過(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ù)寫(xiě)法為。 LEFT(This is an apple,4)=This RIGHT(This is an apple,5)=apple MID(This is an apple,6,2)=is 圖 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ù)寫(xiě)法為： 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ū)分大小寫(xiě)，但忽略格式上的差異。利用函數(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)前日期。 語(yǔ)法形式為 ： TODAY（） （ 5） DATEDIF：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。 語(yǔ)法形式為： DATEDIF(start_date,end_date,unit) （ 6） VALUE：將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。 語(yǔ)法形式為： VALUE(text) （ 7） RIGHT：根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多