【文章內(nèi)容簡介】 否有辦法去掉總計(jì)欄中的 0呢？你可能會說，不寫公式不就行了。當(dāng)然這是一個(gè)辦法，但是，如果我們利用了 IF函數(shù)的話，也可以在寫公式的情況下，同樣不顯示這些 0。如何實(shí)現(xiàn)呢？只需將總計(jì)欄中的公式（僅以單元格 G5為例）改寫成： =IF(SUM(C5:F5),SUM(C5:F5),) 通俗的解釋就是：如果 SUM(C5:F5)不等于零，則在單元格中顯示 SUM(C5:F5)的結(jié)果，否則顯示字符串。 幾點(diǎn)說明： （ 1） SUM(C5:F5)不等于零的正規(guī)寫法是 SUM(C5:F5)0，在 EXCEL中可以省略 0； （ 2） 表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。 圖 4 不同的條件返回不同的結(jié)果 如果對上述例子有了很好的理解后，我們就很容易將 IF 函數(shù)應(yīng)用到更廣泛的領(lǐng)域。比如，在成績表中根據(jù)不同的成績區(qū)分合格與不合格?，F(xiàn)在我們就以某班級的英語成績?yōu)槔唧w說明用法。 圖 6 某班級的成績?nèi)鐖D 6所示，為了做出最終的綜合評定 ，我們設(shè)定按照平均分判斷該學(xué)生成績是否合格的規(guī)則。如果各科平均分超過 60分則認(rèn)為是合格的，否則記作不合格。 根據(jù)這一規(guī)則，我們在綜合評定中寫公式（以單元格 B12為例）： =IF(B1160,合格 ,不合格 ) 語法解釋為，如果單元格 B11的值大于 60，則執(zhí)行第二個(gè)參數(shù)即在單元格 B12中顯示合格字樣，否則執(zhí)行第三個(gè)參數(shù)即在單元格 B12中顯示不合格字樣。 在綜合評定欄中可以看到由于 C列的同學(xué)各科平均分為 54分，綜合評定為不合格。其余均為合格。 多層嵌套函數(shù)的應(yīng)用 在上述的例子中，我們只是 將成績簡單區(qū)分為合格與不合格，在實(shí)際應(yīng)用中，成績通常是有多個(gè)等級的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來實(shí)現(xiàn)。仍以上例為例，我們設(shè)定綜合評定的規(guī)則為當(dāng)各科平均分超過 90時(shí)，評定為優(yōu)秀。如圖 7所示。 圖 7 說明：為了解釋起來比較方便，我們在這里僅做兩重嵌套 的示例，您可以按照實(shí)際情況進(jìn)行更多重的嵌套，但請注意 Excel的 IF函數(shù)最多允許七重嵌套。 根據(jù)這一規(guī)則，我們在綜合評定中寫公式（以單元格 F12為例）： =IF(F1160,IF(AND(F1190),優(yōu)秀 ,合格 ),不合格 ) 語法解釋為，如果單元格 F11的值大于 60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格 F11的值是否大于 90（為了讓大家體會一下 AND函數(shù)的應(yīng)用，寫成 AND(F1190)，實(shí)際上可以僅寫 F1190），如果滿足在單元格 F12中顯示優(yōu)秀字樣，不滿足顯示合格字 樣，如果 F11的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(shù)即在單元格 F12中顯示不合格字樣。 在綜合評定欄中可以看到由于 F列的同學(xué)各科平均分為 92分，綜合評定為優(yōu)秀。 （三）根據(jù)條件計(jì)算值 在了解了 IF函數(shù)的使用方法后，我們再來看看與之類似的 Excel提供的可根據(jù)某一條件來分析數(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可以用來計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。比如在成績表中計(jì)算每位學(xué)生取得優(yōu)秀成績的課程數(shù)。在工資表中求出所有基本工資在 2020元以上的員工數(shù)。 語法形式為 COUNTIF(range,criteria)。其中 Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。 Criteria確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 3 3 3 apples。 成績表 這里 仍以上述成績表的例子說明一些應(yīng)用方法。我們需要計(jì)算的是：每位學(xué)生取得優(yōu)秀成績的課程數(shù)。規(guī)則為成績大于 90分記做優(yōu)秀。如圖 8所示 圖 8 根據(jù)這一規(guī)則，我們在優(yōu)秀門數(shù)中寫公式（以單元格 B13為例）： =COUNTIF(B4:B10,90) 語法解釋為，計(jì)算 B4到 B10這個(gè)范圍，即 jarry的各科成績中有多少個(gè)數(shù)值大于 90的單元格。 在優(yōu)秀門數(shù)欄中可以看到 jarry的優(yōu)秀門數(shù)為兩門。其他人也可以依次看到。 銷售業(yè)績表 銷售業(yè)績表可能是綜合運(yùn)用 IF、 SUMIF、 COUNTIF非常典型的示例。比如，可能希望計(jì)算銷售人員的訂單數(shù)，然后匯總每個(gè)銷售人員的銷售額，并且根據(jù)總發(fā)貨量決定每次銷售應(yīng)獲得的獎(jiǎng)金。 原始數(shù)據(jù)表如圖 9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號排列） 圖 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) 語法解釋為計(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)訂單總額超過 5萬元時(shí)，獎(jiǎng)勵(lì)幅度為百分之十五，否則為百分之十。根據(jù)這一規(guī)則仍以銷售人員 ANNIE為例說明。公式為： =IF(C1750000,10%,15%)*C17 如果訂單總額小于 50000則獎(jiǎng)金為 10%；如果訂單總額大于等于 50000，則獎(jiǎng)金為 15%。 至此，我們已完全了解了 EXCEL函數(shù)的邏輯函數(shù)，相信大家在實(shí)際工作中會想出更多更有用的運(yùn)用。 Excel 函數(shù)應(yīng)用之文本 /日期 /時(shí)間函數(shù) . 編者語： Excel 是辦公室自動(dòng)化中非常重要的一款軟件，很多巨型國際企業(yè)都是依靠 Excel 進(jìn)行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對數(shù)據(jù)的自動(dòng)處理和計(jì)算，然而很多缺少理工科背景或是對 Excel強(qiáng)大數(shù)據(jù)處理 功能不了解的人卻難以進(jìn)一步深入。編者以為，對 Excel 函數(shù)應(yīng)用的不了解正是阻擋普通用戶完全掌握 Excel的攔路虎，然而目前這一部份內(nèi)容的教學(xué)文章卻又很少見，所以特別組織了這一個(gè)《 Excel 函數(shù)應(yīng)用》系列，希望能夠?qū)xcel 進(jìn)階者有所幫助。《 Excel 函數(shù)應(yīng)用》系列，將每周更新，逐步系統(tǒng)的介紹 Excel 各類函數(shù)及其應(yīng)用，敬請關(guān)注！ 所謂文本函數(shù)，就是可以在公式中處理文字串的函數(shù)。例如，可以改變大小寫或確定文字串的長度；可以替換某些字符或者去除某些字符等。而日期和時(shí)間函數(shù)則可以在公式中分析和處理日期值和時(shí)間 值。關(guān)于這兩類函數(shù)的列表參看附表，這里僅對一些常用的函數(shù)做簡要介紹。 一、文本函數(shù) （一）大小寫轉(zhuǎn)換 LOWER將一個(gè)文字串中的所有大寫字母轉(zhuǎn)換為小寫字母。 UPPER將文本轉(zhuǎn)換成大寫形式。 PROPER將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫。將其余的字母轉(zhuǎn)換成小寫。 這三種函數(shù)的基本語法形式均為 函數(shù)名（ text）。示例說明： 已有字符串為： pLease ComE Here! 可以看到由于輸入的不規(guī)范，這句話大小寫亂用了。 通過以上三個(gè)函數(shù)可以將文本轉(zhuǎn)換顯示樣式 ，使得文本變得規(guī)范。參見圖 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ù)從長字符串內(nèi)獲取一部分字符。具體語法格式為 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 圖 2 （三）去除字符串的空白 在字符串形態(tài)中，空白也是一個(gè)有效的字符，但是如果字符串中出現(xiàn)空白字符時(shí)，容易在判斷或?qū)Ρ葦?shù)據(jù)是發(fā)生錯(cuò)誤，在 Excel中您可以使用 Trim函數(shù)清除字符串中的空白。 語法形式為： TRIM(text)其中 Text為需要清除其中空格的文本。 需要注意的是， Trim函數(shù)不會清除單詞之間的單個(gè)空格，如果連這部分空格都需清除的話，建議使用替換功能。比如，從字符串 My name is Mary中清除空格的函數(shù)寫法為： TRIM(My name is Mary)=My name is Mary 參見圖 3 圖 3 （四）字符串的比較 在數(shù)據(jù)表中經(jīng)常會比對不同的字符串，此時(shí)您可以使用 EXACT函數(shù)來比較兩個(gè)字符串是否相同。該函數(shù)測試兩個(gè)字符串是否完全相同。如果 它們完全相同，則返回 TRUE；否則，返回 FALSE。函數(shù) EXACT 能區(qū)分大小寫，但忽略格式上的差異。利用函數(shù) EXACT 可以測試輸入文檔內(nèi)的文字。語法形式為：EXACT(text1,text2)Text1為待比較的第一個(gè)字符串。 Text2為待比較的第二個(gè)字符串。舉例說明：參見圖 4 EXACT(China,china)=False 圖 4 二、日期與時(shí)間函數(shù) 在數(shù)據(jù)表的處理過程中，日期與時(shí)間的函數(shù)是相當(dāng)重要的處理依據(jù)。而 Excel在這方面也提供了相當(dāng)豐富的函數(shù)供大家使用。 （一）取出當(dāng)前系統(tǒng)時(shí)間 /日期信息 用于取出當(dāng)前系統(tǒng)時(shí)間 /日期信息的函數(shù)主要有 NOW、 TODAY。 語法形式均為 函數(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è) 具體的示例來說明 Excel的文本函數(shù)與日期函數(shù)的用途。 三、示例：做一個(gè)美觀簡潔的人事資料分析表 示例說明 在如圖 6所示的某公司人事資料表中，除了編號、員工姓名、身份證號碼以及參加工作時(shí)間為手工添入外，其余各項(xiàng)均為用函數(shù)計(jì)算所得。 圖 6 在此例中我們將詳細(xì)說明如何通過函數(shù)求出： （ 1）自動(dòng)從身份證號碼中提取出生年月、性別信息。 （ 2）自動(dòng)從參加工作時(shí)間中提取工齡信息。 身份證號碼相關(guān)知識 在了解如何實(shí)現(xiàn)自動(dòng)從身份證號碼中提取出生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。我們知道，當(dāng)今的身份證號碼有 15/18位之分。早期簽發(fā)的身份證號碼是 15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴(kuò)展（由兩位變?yōu)樗奈唬┖?