【正文】 ber) 公式寫(xiě)法及解釋?zhuān)ㄒ詥T工 Andy 為例說(shuō)明） 說(shuō) 明：為避免公式中過(guò)多的嵌套，這里的身份證號(hào)碼限定為 15 位的。 語(yǔ)法形式為： VALUE(text) （ 7） RIGHT：根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。 語(yǔ)法形式為 ： TODAY（） （ 5） DATEDIF：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。 語(yǔ)法形式為： CONCATENATE(text1,text2??) （ 3） MID：從文本字符串中指定的起始位置起，返回指定長(zhǎng)度的字符。 IF 函數(shù)允許嵌套。 應(yīng)用函數(shù) 在此例中為了實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)提取，應(yīng)用了如下幾個(gè) excel 函數(shù)。 （ 2） 18 位的身份證號(hào)碼： 1~6 位為地區(qū)代碼， 7~10 位為出生年份 (4 位 )， 11~12位為出生月份， 13~14 位為出生日期，第 15~17 位為順序號(hào)，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。這兩種身份證號(hào)碼將在相當(dāng)長(zhǎng)的一段時(shí)期內(nèi)共存。我們知道，當(dāng)今的身份證號(hào)碼有 15/18 位之分。 （ 2）自動(dòng)從參加工作時(shí)間中提取工齡信息。 三、示例：做一個(gè)美觀(guān)簡(jiǎn)潔的人事資料分析表 示例說(shuō)明 在如圖 6所示的某公司人事資料表中，除了編號(hào)、員工姓名、身份證號(hào)碼以及參加工作時(shí)間為手工添入外，其余各項(xiàng)均為用函數(shù)計(jì)算所得。 YEAR(E5)=2020 MONTH(E5)=5 DAY(E5)=30 HOUR(E5)=12 圖 5 此外還有更多有用的日期 /時(shí)間函數(shù)，可以查閱附表。具體示例參看圖 5。 語(yǔ)法形式均為 函數(shù)名（）。而 excel在這方面也提供了相當(dāng)豐富的函數(shù)供大家使用。 Text2 為待比較的第二個(gè)字符串。利用函數(shù) EXACT 可以測(cè)試輸入文檔內(nèi)的文字。如果它們完全相同，則返回 TRUE；否則，返回 FALSE。比如，從字符串 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è)字符串是否相同。 語(yǔ)法形式為： TRIM(text)其中 Text 為需要清除其中空格的文本。 比如，從字符串 This is an apple.分別取出字符 This、 apple、 is的具體函數(shù)寫(xiě)法為。 RIGHT 函數(shù)： RIGHT(text,num_chars)其中 Text 是包含要提取字符的文本串。 MID函數(shù)： MID(text,start_num,num_chars)其中 Text 是包含要提取字符的文本串。具體語(yǔ)法格式為 LEFT 函數(shù)： LEFT(text,num_chars)其中 Text 是包含要提取字符的文本串。 通過(guò)以上三個(gè)函數(shù)可以將文本轉(zhuǎn)換顯示樣式，使得文本變得規(guī)范。 這三種函數(shù)的基本語(yǔ)法形式均為 函數(shù)名（ text）。 PROPER將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫(xiě)。 一、文本函數(shù) （一）大小寫(xiě)轉(zhuǎn)換 LOWER將一個(gè)文字串中的所有大寫(xiě)字母轉(zhuǎn)換為小寫(xiě)字母。而日期和時(shí)間函數(shù)則可以在公式中分析和處理日期值和時(shí)間值。 [dvnews_page=excel 函數(shù) 應(yīng)用之文本 /日期 /時(shí)間函數(shù) ]所謂文本函數(shù)，就是可以在公式中處理文字串的函數(shù)。公式為： =IF(C1750000,10%,15%)*C17 如果訂單總額小于 50000 則獎(jiǎng)金為 10%；如果訂單總額大于等于 50000，則獎(jiǎng)金為 15%。 假定公司的銷(xiāo)售獎(jiǎng)金規(guī)則為當(dāng)訂單總額超過(guò) 5萬(wàn)元時(shí)，獎(jiǎng)勵(lì)幅度為百分之十五，否則為百分之十。 這個(gè)相應(yīng)量的和就是銷(xiāo)售人員 ANNIE 的訂單總額。 以銷(xiāo)售人員 ANNIE 的訂單總額公式為例。 這個(gè)出現(xiàn)的次數(shù)即可認(rèn)為是該銷(xiāo)售人員 ANNIE 的訂單數(shù)。 以銷(xiāo)售人員 ANNIE 的訂單數(shù)公式為例。然后分別計(jì)算訂單數(shù)、 訂單總額、銷(xiāo)售獎(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ù)。 銷(xiāo)售業(yè)績(jī)表 銷(xiāo)售業(yè)績(jī)表可能是綜合運(yùn)用 IF、 SUMIF、 COUNTIF 非常典型的示例。 在優(yōu)秀門(mén)數(shù)欄中可以看到 jarry 的優(yōu)秀門(mén)數(shù)為兩門(mén)。規(guī)則為成績(jī)大于 90分記做優(yōu)秀。 成績(jī)表 這里仍以上述成績(jī)表的例子說(shuō)明一些應(yīng)用方法。 Criteria 確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。 語(yǔ)法形式為 COUNTIF(range,criteria)。比如在成績(jī)表中計(jì)算每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。這里重點(diǎn)介紹 COUNTIF 的應(yīng)用。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF 工作表 函數(shù)。 （三）根據(jù)條件計(jì)算值 在了解了 IF函數(shù)的使用方法后，我們?cè)賮?lái)看看與之類(lèi)似的 excel 提供的可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(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í)際上可以?xún)H寫(xiě) F1190），如果滿(mǎn)足在單元格 F12中顯示優(yōu)秀字樣，不滿(mǎn)足顯示合格字樣，如果 F11 的值以上條件都不滿(mǎn)足，則執(zhí)行第三個(gè)參數(shù)即在單元格 F12 中顯示不合 格字樣。如圖 7 所示。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來(lái)實(shí)現(xiàn)。其余均為合格。 根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫(xiě)公式（以單元格 B12 為例）： =IF(B1160,合格 ,不合格 ) 語(yǔ)法解釋為，如果單元格 B11 的值大于 60，則執(zhí)行第二個(gè)參數(shù)即在單元格 B12中顯示合格字樣，否則執(zhí)行第三個(gè)參數(shù)即在單元格 B12 中顯示不合格字樣。 圖 6 某班級(jí)的成績(jī)?nèi)鐖D 6所示，為了做出最終的綜合評(píng)定，我們?cè)O(shè)定按照平均分判斷該學(xué)生成績(jī)是否合格的規(guī)則。比如，在成績(jī)表中根據(jù)不 同的成績(jī)區(qū)分合格與不合格。 幾點(diǎn)說(shuō)明： （ 1） SUM(C5:F5)不等于零的正規(guī)寫(xiě)法是 SUM(C5:F5)0，在 excel 中可以省略0； （ 2） 表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。當(dāng)然這是一個(gè)辦法，但是，如果我們利用了 IF函數(shù)的話(huà)，也可以在寫(xiě)公式的情況下，同樣不顯示這些 0。如果這樣的表格打印出來(lái)就頁(yè)面的美觀(guān)來(lái)看顯示是不令人滿(mǎn)意的。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF 工作表函數(shù)。 excel 還提供了可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。 簡(jiǎn)言之，如果第一個(gè)參數(shù) logical_test 返回的結(jié)果為真的話(huà)，則執(zhí)行第二個(gè)參數(shù) Value_if_true 的結(jié)果，否則執(zhí)行第三個(gè)參數(shù) Value_if_false的結(jié)果。 Value_if_false logical_test 為 FALSE 時(shí)返回的值。本參數(shù)可使用任何比較運(yùn)算符。 它的語(yǔ)法為 IF(logical_test,value_if_true,value_if_false)。 三、 IF函數(shù) （一） IF 函數(shù)說(shuō)明 IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果，因此 If函數(shù)也稱(chēng)之為條件函數(shù)。 二、 TRUE、 FALSE 函數(shù) TRUE、 FALSE 函數(shù)用來(lái)返回參數(shù)的邏輯值，由于可以直接在單元格或公式中鍵入值 TRUE 或者 FALSE。當(dāng)要確保一個(gè)值不等于某一特定值時(shí)，可以使用 NOT 函數(shù)。而 OR函數(shù)僅需其中任何一個(gè)為真即可為真。 圖 1 AND 函數(shù)示例 1 如果 B1B3 單元格中的值為 TRUE、 FALSE、 TRUE，顯然三個(gè)參數(shù)并不都為真，所以 在 B4 單元格中的公式 =AND(B1:B3) 等于 FALSE 圖 2 AND 函數(shù)示例 2 （二） OR 函數(shù) OR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE。由于 B2等于 50 的確大于 小于 60。 參數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用。簡(jiǎn)言之，就是當(dāng) AND 的參數(shù)全部滿(mǎn)足某一條件時(shí)，返回結(jié)果為 TRUE，否則為 FALSE。 一、 AND、 OR、 NOT 函數(shù) 這三個(gè)函數(shù)都用來(lái)返回參數(shù)邏輯值。在 excel 中提供了六種邏輯函數(shù)。 最后需要說(shuō)明的是：本文所有公式均在 excel97和 excel2020 中驗(yàn)證通過(guò)，修改其中的單元格引用和邏輯條件值，可用于相似的其他場(chǎng)合。因此，用 INT函數(shù)進(jìn)行四舍五入還是需要一些技巧的，也就是要加上 ，才能達(dá)到取整的目的。在單元格 D2 中輸入 =ROUND(B2,4)，則小數(shù)點(diǎn)保留四位有效數(shù)字，得到 、 。如圖 3所示： 單元格 B2 中為初始數(shù)據(jù) ， B3 的初始數(shù)據(jù)為 ，將要對(duì)它們進(jìn)行四舍五入。這個(gè)函數(shù)有兩個(gè)參數(shù)，分別是 number 和 num_digits。那是否有簡(jiǎn)單可行的方法來(lái) 進(jìn)行真正的四舍五入呢？其實(shí)， excel 已經(jīng)提供這方面的函數(shù)了，這就是 ROUND 函數(shù)，它可以返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字。雖然，excel 的單元格格式中允許你定義小數(shù)位數(shù)，但是在實(shí)際操作中，我們發(fā)現(xiàn)，其實(shí)數(shù)字本身并沒(méi)有真正的四舍五入，只是顯示結(jié)果似乎四舍五入了。用同樣的方法，還可以制作二 次曲線(xiàn)、對(duì)數(shù)圖像等等。所有這些操作可以先用鼠標(biāo)選中相關(guān)部分，再單擊右鍵彈出快捷菜單，通過(guò)快捷菜單中的有關(guān)項(xiàng)目即可進(jìn)行操作。 編輯圖像 圖像生成后，字體、圖像大小、位置都不一定合適。如果有特殊要求例外。 圖表選項(xiàng)操作 圖表選項(xiàng)操作是制作函數(shù)曲線(xiàn)圖的重要步驟，在 圖表選項(xiàng) 窗口中進(jìn)行（如圖 3），依次進(jìn)行操作的項(xiàng)目有： 標(biāo)題 為圖表取標(biāo)題，本例中取名為 正弦和余弦函數(shù)圖像 ；為橫軸和縱軸取標(biāo)題。 求函數(shù)值 在第 2行和第三行分別輸入 SIN和 COS 函數(shù)，這里需要注意的是：由于 SIN 等三角函數(shù)在 excel 的定義是要弧度值，因此必須先將角度值轉(zhuǎn)為弧度值。 這里，筆者以正弦函數(shù)和余弦函數(shù)為例說(shuō)明函數(shù)圖像 的描繪方法。那個(gè)時(shí)候是用坐標(biāo)紙一點(diǎn)點(diǎn)描繪，常常因?yàn)橛?jì)算的疏忽，描不出平滑的函數(shù)曲線(xiàn)。則在 F15 種輸入公式為 =SUMIF($C$3:$C$12,銷(xiāo)售部 ,$F$3:$F$12) 其中 $C$3:$C$12為提供邏輯判斷依據(jù)的單元格區(qū)域， 銷(xiāo)售部 為判斷條件即只統(tǒng)計(jì) $C$3:$C$12 區(qū)域中部門(mén)為 銷(xiāo)售部 的單元格， $F$3:$F$12 為實(shí)際求和的單元格區(qū)域。 仍以上圖為例，在工資表中需要分別計(jì)算各個(gè)科室的工資發(fā)放情況。對(duì)需要參與求和的某個(gè)常數(shù)，可用 =SUM（單元格區(qū)域，常數(shù)） 的形式直接引用，一般不必絕對(duì)引用存放該常數(shù)的單元格。比如， H13 的公式還可以寫(xiě)成： =SUM(D3:D12,F3:F12)SUM(G3:G12) 注意 SUM函數(shù)中的參數(shù)，即被求和的單元格或單元格區(qū)域不能超過(guò) 30 個(gè)。對(duì)于需要減去的單元格，則可以按住 Ctrl 鍵逐個(gè)選中它們，然后用手工在公式引用的單元格前加上負(fù)號(hào)。此時(shí)你可以讓單元格指針停留在存放結(jié)果的單元格，然后在 excel 編輯欄輸入公式 =SUM（） ，用鼠標(biāo)在括號(hào)中間單 擊，最后拖過(guò)需要求和的所有單元格。 ) 圖 1 函數(shù)求和 SUM 行或列求和 以最常見(jiàn)的工資表（如上圖）為例，它的特點(diǎn)是需要對(duì)行或列內(nèi)的若干單元格求和。 這里筆者將以某單位工資表為例重點(diǎn)介紹 SUM（計(jì)算一組參數(shù)之和）、 SUMIF（對(duì)滿(mǎn)足某一條件的單元格區(qū)域求和）的使用。相信這也是大家最先學(xué)會(huì)使用的 excel 函數(shù)之一。這些函數(shù)的詳細(xì)用法，筆者不在這里一一贅述，下面從應(yīng)用的角度為大家演示一下這些函數(shù)的使用方法。筆者在這里以列表的形式列出 excel 提供的所有數(shù)學(xué)和三角函數(shù)，詳細(xì)請(qǐng)看附注的表格。畢竟這是我們非常熟悉的函數(shù)，這些正弦函數(shù)、余弦函數(shù)、取整函數(shù)等等從中學(xué)開(kāi)始，就一直陪伴著我們。但是由于 excel 的函數(shù)相當(dāng)多，因此也可能僅介紹幾種比較常用的函 數(shù)使用方法，其他更多的函數(shù)您可以從 excel 的在線(xiàn)幫助功能中了解更詳細(xì)的資訊。這些函數(shù)，稱(chēng)為用戶(hù)自定義函數(shù)， 可以通過(guò)使用 Visual Basic for Applicati