Excel常用函數(shù)應(yīng)用

千里之行，始于腳下。第2頁/共2頁精品文檔推薦Excel常用函數(shù)應(yīng)用

編號：時間：2023年x月x日書山有路勤為徑，學(xué)海無涯苦作舟頁碼：第53頁共53頁Excel常用函數(shù)應(yīng)用

一、與求和有關(guān)的函數(shù)的應(yīng)用SUM函數(shù)是Excel中使用最多的函數(shù)，利用它進行求和運算可以忽視存有文本、空格等數(shù)據(jù)的單元格，語法簡潔、使用便利。

信任這也是大家最先學(xué)會使用的Excel函數(shù)之一。但是實際上，Excel所供應(yīng)的求和函數(shù)不僅僅只有SUM一種，還包括SUBTOTAL、SUM、SUMI

F、SUMPRODUCT、SUMSQ、SUMX2MY

2、SUMX2PY

2、SUMXMY2幾種函數(shù)。

這里筆者將以某單位工資表為例重點介紹SUM（計算一組參數(shù)之和）、SUMIF（對滿意某一條件的單元格區(qū)域求

和）的使用。

(說明：為力求簡潔，示例中忽視稅金的計算。

)圖1函數(shù)求和SUM

1、行或列求和以最常見的工資表（如上圖）為例，它的特點是需要對行或列內(nèi)的若干單元格求和。

比如，求該單位2023年5月的實際發(fā)放工資總額，就可以在H13中輸入公式：=SUM(H3:H12)

2、區(qū)域求和區(qū)域求和常用于對一張工作表中的全部數(shù)據(jù)求總計。

此時你可以讓單元格指針停留在存放結(jié)果的單元格，然后在Excel編輯欄輸入公式=SUM（），用鼠標在括號中間單擊，最終拖過需要求和的全部單元格。

若這些單元格是不連續(xù)的，可以按住Ctrl鍵分別拖過它們。

對于需要減去的單元格，則可以按住Ctrl鍵逐個選中它們，然后用手工在公式引用的單

元格前加上負號。

當然你也可以用公式選項板完成上述工作，不過對于SUM函數(shù)來說手工還是來的快一些。

比如，H13的公式還可以寫成：=SUM(D3:D12,F3:F12)-SUM(G3:G12)

3、留意SUM函數(shù)中的參數(shù)，即被求和的單元格或單元格區(qū)域不能超過30個。換句話說，SUM函數(shù)括號中消失的分隔符（逗號）不能多于29個，否則Excel就會提示參數(shù)太多。對需要參加求和的某個常數(shù)，可用=SUM（單元格區(qū)域，常數(shù)）的形式直接引用，一般不必肯定引用存放該常數(shù)的單元格。

SUMIFSUMIF函數(shù)可對滿意某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達式，可以應(yīng)用在人事、工資和成果統(tǒng)計中。

仍以上圖為例

，在工資表中需要分別計算各個科室的工資發(fā)放狀況。

要計算銷售部2023年5月加班費狀況。則在F15種輸入公式為=SUMIF($C$3:$C$12,銷售部,$F$3:$F$12)其中$C$3:$C$12為供應(yīng)規(guī)律推斷依據(jù)的單元格區(qū)域，銷售部為推斷條件即只統(tǒng)計$C$3:$C$12區(qū)域中部門為銷售部的單元格，$F$3:$F$12為實際求和的單元格區(qū)域。

二、與函數(shù)圖像有關(guān)的函數(shù)應(yīng)用我想大家肯定還記得我們在學(xué)中學(xué)數(shù)學(xué)時，經(jīng)常需要畫各種函數(shù)圖像。那個時候是用坐標紙一點點描繪，經(jīng)常由于計算的疏忽，描不出平滑的函數(shù)曲線?，F(xiàn)在，我們已經(jīng)知道Excel幾乎囊括了我們需要的各種數(shù)學(xué)和三角函數(shù)，那是否可以利用Exce

l函數(shù)與Excel圖表功能描繪函數(shù)圖像呢？當然可以。

這里，筆者以正弦函數(shù)和余弦函數(shù)為例說明函數(shù)圖像的描繪方法。

圖2函數(shù)圖像繪制

1、錄入數(shù)據(jù)-如圖所示，首先在表中錄入數(shù)據(jù)，自B1至N1的單元格以30度遞增的方式錄入從0至360的數(shù)字，共13個數(shù)字。

2、求函數(shù)值-在第2行和第三行分別輸入SIN和COS函數(shù)，這里需要留意的是：由于SIN等三角函數(shù)在Excel的定義是要弧度值，因此必需先將角度值轉(zhuǎn)為弧度值。詳細公式寫法為（以D2為例）：=SIN(D1*PI()/180)

3、選擇圖像類型-首先選中制作函數(shù)圖像所需要的表中數(shù)據(jù)，利用Excel工具欄上的圖表向?qū)О粹o（也可利用插入圖表），在圖表類

型中選擇XY散點圖，再在右側(cè)的子圖表類型中選擇很多據(jù)點平滑線散點圖，單擊下一步，消失圖表數(shù)據(jù)源窗口，不作任何操作，直接單擊下一步。

4、圖表選項操作-圖表選項操作是制作函數(shù)曲線圖的重要步驟，在圖表選項窗口中進行（如圖3），依次進行操作的項目有：標題-為圖表取標題，本例中取名為正弦和余弦函數(shù)圖像；為橫軸和縱軸取標題。

坐標軸-可以不做任何操作；網(wǎng)格線-可以做出類似坐標紙上網(wǎng)格，也可以取消網(wǎng)格線；圖例-本例選擇圖例放在圖像右邊，這個可隨詳細狀況選擇；數(shù)據(jù)標志-本例未將數(shù)據(jù)標志在圖像上，主要緣由是影響美觀。假如有特別要求例外。

5、完成圖像-操作結(jié)束后單擊完成，一幅圖像就插入Excel的工作區(qū)了。

6

、編輯圖像-圖像生成后，字體、圖像大小、位置都不肯定合適。可選擇相應(yīng)的選項進行修改。

全部這些操作可以先用鼠標選中相關(guān)部分，再單擊右鍵彈出快捷菜單，通過快捷菜單中的有關(guān)項目即可進行操作。

至此，一幅正弦和余弦函數(shù)圖像制作完成。用同樣的方法，還可以制作二次曲線、對數(shù)圖像等等。

三、常見數(shù)學(xué)函數(shù)使用技巧-四舍五入在實際工作的數(shù)學(xué)運算中，特殊是財務(wù)計算中經(jīng)常遇到四舍五入的問題。

雖然，excel的單元格格式中允許你定義小數(shù)位數(shù)，但是在實際操作中，我們發(fā)覺，其實數(shù)字本身并沒有真正的四舍五入，只是顯示結(jié)果好像四舍五入了。

假如采納這種四舍五入方法的話，在財務(wù)運算中經(jīng)常會消失幾分錢的誤差，而這是財務(wù)運算不允許的。

那是否有簡潔可行的方法來進行真正的四舍五入呢？其實，Excel已經(jīng)供應(yīng)這方面的函數(shù)了，這就是ROUND函數(shù)，它可以返回某個數(shù)字按指定位數(shù)舍入后的數(shù)字。

在Excel供應(yīng)的數(shù)學(xué)與三角函數(shù)中供應(yīng)了一個名為ROUND(number,num_digits)的函數(shù)，它的功能就是依據(jù)指定的位數(shù)，將數(shù)字四舍五入。

這個函數(shù)有兩個參數(shù)，分別是number和num_digits。其中number就是將要進行四舍五入的數(shù)字；num_digits則是盼望得到的數(shù)字的小數(shù)點后的位數(shù)。

如圖3所示：單元格B2中為初始數(shù)據(jù)0.***，B3的初始數(shù)據(jù)為0.***，將要對它們進行四舍五入。

在單元格C2中輸入=ROUND

(B2,2)，小數(shù)點后保留兩位有效數(shù)字，得到0.1

2、0.23。

在單元格D2中輸入=ROUND(B2,4)，則小數(shù)點保留四位有效數(shù)字，得到0.123

5、0.2346。

圖3對數(shù)字進行四舍五入對于數(shù)字進行四舍五入,還可以使用INT（取整函數(shù)），但由于這個函數(shù)的定義是返回實數(shù)舍入后的整數(shù)值。

因此，用INT函數(shù)進行四舍五入還是需要一些技巧的，也就是要加上0.5，才能達到取整的目的。

仍舊以圖3為例，假如采納INT函數(shù)，則C2公式應(yīng)寫成：=INT(B2*100+0.5)/100。最終需要說明的是：本文全部公式均在Excel97和Excel2000中驗證通過，修改其中的單元格引用和規(guī)律條件值，可用于相像

的其他場合。附注：Excel的數(shù)學(xué)和三角函數(shù)一覽表ABS工作表函數(shù)返回參數(shù)的肯定值A(chǔ)COS工作表函數(shù)返回數(shù)字的反余弦值A(chǔ)COSH工作表函數(shù)返回參數(shù)的反雙曲余弦值A(chǔ)SIN工作表函數(shù)返回參數(shù)的反正弦值A(chǔ)SINH工作表函數(shù)返回參數(shù)的反雙曲正弦值A(chǔ)TAN工作表函數(shù)返回參數(shù)的反正切值A(chǔ)TAN2工作表函數(shù)返回給定的X及Y坐標值的反正切值A(chǔ)TANH工作表函數(shù)返回參數(shù)的反雙曲正切值CEILING工作表函數(shù)將參數(shù)Number沿肯定值增大的方向，舍入為最接近的整數(shù)或基數(shù)COMBIN工作表函數(shù)計算從給定數(shù)目的對象集合中提取若干對象的組合數(shù)COS工作表函數(shù)返回給定角度的余弦值COSH

工作表函數(shù)返回參數(shù)的雙曲余弦值COUNTIF工作表函數(shù)計算給定區(qū)域內(nèi)滿意特定條件的單元格的數(shù)目DEGREES工作表函數(shù)將弧度轉(zhuǎn)換為度EVEN工作表函數(shù)返回沿肯定值增大方向取整后最接近的偶數(shù)EXP工作表函數(shù)返回e的n次冪常數(shù)e等于

2.***，是自然對數(shù)的底數(shù)FACT工作表函數(shù)返回數(shù)的階乘，一個數(shù)的階乘等于1*2*3*.*該數(shù)FACTDOUBLE工作表函數(shù)返回參數(shù)Number的半階乘FLOOR工作表函數(shù)將參數(shù)Number沿肯定值減小的方向去尾舍入，使其等于最接近的significance的倍數(shù)GCD工作表函數(shù)返回兩個或多個整數(shù)的最

大公約數(shù)INT工作表函數(shù)返回實數(shù)舍入后的整數(shù)值LCM工作表函數(shù)返回整數(shù)的最小公倍數(shù)LN工作表函數(shù)返回一個數(shù)的自然對數(shù)自然對數(shù)以常數(shù)項e（

2.***）為底LOG工作表函數(shù)按所指定的底數(shù)，返回一個數(shù)的對數(shù)LOG10工作表函數(shù)返回以10為底的對數(shù)MDETERM工作表函數(shù)返回一個數(shù)組的矩陣行列式的值MINVERSE工作表函數(shù)返回數(shù)組矩陣的逆距陣MMULT工作表函數(shù)返回兩數(shù)組的矩陣乘積結(jié)果MOD工作表函數(shù)返回兩數(shù)相除的余數(shù)結(jié)果的正負號與除數(shù)相同MROUND工作表函數(shù)返回參數(shù)按指定基數(shù)舍入后的數(shù)值MULTINOMIAL工作表函數(shù)返回參數(shù)和的階乘與各參數(shù)階乘

乘積的比值ODD工作表函數(shù)返回對指定數(shù)值進行舍入后的奇數(shù)PI工作表函數(shù)返回數(shù)字

3.***，即數(shù)學(xué)常數(shù)pi，精確到小數(shù)點后15位POWER工作表函數(shù)返回給定數(shù)字的乘冪PRODUCT工作表函數(shù)將全部以參數(shù)形式給出的數(shù)字相乘，并返回乘積值QUOTIENT工作表函數(shù)回商的整數(shù)部分，該函數(shù)可用于舍掉商的小數(shù)部分RADIANS工作表函數(shù)將角度轉(zhuǎn)換為弧度RAND工作表函數(shù)返回大于等于0小于1的勻稱分布隨機數(shù)RANDBETWEEN工作表函數(shù)返回位于兩個指定數(shù)之間的一個隨機數(shù)ROMAN工作表函數(shù)將阿拉伯數(shù)字轉(zhuǎn)換為文本形式的羅馬數(shù)字ROUND工作表函數(shù)返回

某個數(shù)字按指定位數(shù)舍入后的數(shù)字ROUNDDOWN工作表函數(shù)靠近零值，向下（肯定值減小的方向）舍入數(shù)字ROUNDUP工作表函數(shù)遠離零值，向上（肯定值增大的方向）舍入數(shù)字SERIESSUM工作表函數(shù)返回基于以下公式的冪級數(shù)之和：SIGN工作表函數(shù)返回數(shù)字的符號當數(shù)字為正數(shù)時返回1，為零時返回0，為負數(shù)時返回-1SIN工作表函數(shù)返回給定角度的正弦值SINH工作表函數(shù)返回某一數(shù)字的雙曲正弦值SQRT工作表函數(shù)返回正平方根SQRTPI工作表函數(shù)返回某數(shù)與pi的乘積的平方根SUBTOTAL工作表函數(shù)返回數(shù)據(jù)清單或數(shù)據(jù)庫中的分類匯總SUM工作表函數(shù)返回某一單元格區(qū)域中全部數(shù)字之

和SUMIF工作表函數(shù)依據(jù)指定條件對若干單元格求和SUMPRODUCT工作表函數(shù)在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和SUMSQ工作表函數(shù)返回全部參數(shù)的平方和SUMX2MY2工作表函數(shù)返回兩數(shù)組中對應(yīng)數(shù)值的平方差之和SUMX2PY2工作表函數(shù)返回兩數(shù)組中對應(yīng)數(shù)值的平方和之和，平方和加總在統(tǒng)計計算中常常使用SUMXMY2工作表函數(shù)返回兩數(shù)組中對應(yīng)數(shù)值之差的平方和TAN工作表函數(shù)返回給定角度的正切值TANH工作表函數(shù)返回某一數(shù)字的雙曲正切值TRUNC工作表函數(shù)將數(shù)字的小數(shù)部分截去，返回整數(shù)用來推斷真假值，或者進行復(fù)合檢驗的Excel函數(shù)，我們稱為規(guī)律函數(shù)。

在E

xcel中供應(yīng)了六種規(guī)律函數(shù)。

即AND、OR、NOT、FALSE、I

F、TRUE函數(shù)。

一、AND、OR、NOT函數(shù)這三個函數(shù)都用來返回參數(shù)規(guī)律值。具體介紹見下：

（一）AND函數(shù)全部參數(shù)的規(guī)律值為真時返回TRUE；只要一個參數(shù)的規(guī)律值為假即返回FALSE。

簡言之，就是當AND的參數(shù)全部滿意某一條件時，返回結(jié)果為TRUE，否則為FALSE。

語法為AND(logical1,logical2,.)，其中Logical1,logical2,.表示待檢測的1到30個條件值，各條件值可能為TRUE，可能為FALSE。

參數(shù)必需是規(guī)律值，或者包含規(guī)律值的數(shù)組或引用。

舉例說明：

1、在B2

單元格中輸入數(shù)字50，在C2中寫公式=AND(B230,B260)。

由于B2等于50的確大于30、小于60。所以兩個條件值（logical）均為真，則返回結(jié)果為TRUE。

圖1AND函數(shù)示例1

2、假如B1-B3單元格中的值為TRUE、FALSE、TRUE，明顯三個參數(shù)并不都為真，所以在B4單元格中的公式=AND(B1:B3)等于FALSE圖2AND函數(shù)示例2

（二）OR函數(shù)OR函數(shù)指在其參數(shù)組中，任何一個參數(shù)規(guī)律值為TRUE，即返回TRUE。

它與AND函數(shù)的區(qū)分在于，AND函數(shù)要求全部函數(shù)規(guī)律值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個為真即可為真。

比如，上面的示例2，

假如在B4單元格中的公式寫為=OR(B1:B3)則結(jié)果等于TRUE圖3OR函數(shù)示例

（三）NOT函數(shù)NOT函數(shù)用于對參數(shù)值求反。

當要確保一個值不等于某一特定值時，可以使用NOT函數(shù)。

簡言之，就是當參數(shù)值為TRUE時，NOT函數(shù)返回的結(jié)果恰與之相反，結(jié)果為FALSE.比如NOT(2+2=4)，由于2+2的結(jié)果的確為4，該參數(shù)結(jié)果為TRUE，由于是NOT函數(shù)，因此返回函數(shù)結(jié)果與之相反，為FALSE。

二、TRUE、FALSE函數(shù)TRUE、FALSE函數(shù)用來返回參數(shù)的規(guī)律值，由于可以直接在單元格或公式中鍵入值TRUE或者FALSE。

因此這兩個函數(shù)通?？梢圆皇褂?。

三、IF函數(shù)

（一）IF函數(shù)說明IF

函數(shù)用于執(zhí)行真假值推斷后，依據(jù)規(guī)律測試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。

它的應(yīng)用很廣泛，可以使用函數(shù)IF對數(shù)值和公式進行條件檢測。它的語法為IF(logical_test,value_if_true,value_if_false)。

其中Logical_test表示計算結(jié)果為TRUE或FALSE的任意值或表達式。

本參數(shù)可使用任何比較運算符。Value_if_true顯示在logical_test為TRUE時返回的值，Value_if_true也可以是其他公式。Value_if_falselogical_test為FALSE時返回的值。

Value_i

f_false也可以是其他公式。簡言之，假如第一個參數(shù)logical_test返回的結(jié)果為真的話，則執(zhí)行其次個參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個參數(shù)Value_if_false的結(jié)果。IF函數(shù)可以嵌套七層，用value_if_false及value_if_true參數(shù)可以構(gòu)造簡單的檢測條件。

Excel還供應(yīng)了可依據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。

例如，假如要計算單元格區(qū)域中某個文本串或數(shù)字消失的次數(shù)，則可使用COUNTIF工作表函數(shù)。假如要依據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。

（二）IF函數(shù)應(yīng)用

1、輸出帶有公式的空白表單圖

5人事分析表1以圖中所示的人事狀況分析表為例，由于各部門關(guān)于人員的組成狀況的數(shù)據(jù)尚未填寫，在總計欄（以單元格G5為例）公式為：=SUM(C5:F5)我們看到計算為0的結(jié)果。

假如這樣的表格打印出來就頁面的美觀來看顯示是不令人滿足的。是否有方法去掉總計欄中的0呢？你可能會說，不寫公式不就行了。

當然這是一個方法，但是，假如我們利用了IF函數(shù)的話，也可以在寫公式的狀況下，同樣不顯示這些0。

如何實現(xiàn)呢？只需將總計欄中的公式（僅以單元格G5為例）改寫成：=IF(SUM(C5:F5),SUM(C5:F5),)通俗的解釋就是：假如SUM(C5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結(jié)果，否則

顯示字符串。

幾點說明：

（1）SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)0，在EXCEL中可以省略0；

（2）表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。

圖4

2、不同的條件返回不同的結(jié)果假如對上述例子有了很好的理解后，我們就很簡單將IF函數(shù)應(yīng)用到更廣泛的領(lǐng)域。

比如，在成果表中依據(jù)不同的成果區(qū)分合格與不合格。

現(xiàn)在我們就以某班級的英語成果為例詳細說明用法。

圖6某班級的成果如圖6所示，為了做出最終的綜合評定，我們設(shè)定根據(jù)平均分推斷該同學(xué)成果是否合格的規(guī)章。假如各科平均分超過60分則認為是合格的，否則記作不合格。

依據(jù)這一規(guī)章，我們在綜合評定中寫公式（以單元格

B12為例）：=IF(B1160,合格,不合格)語法解釋為，假如單元格B11的值大于60，則執(zhí)行其次個參數(shù)即在單元格B12中顯示合格字樣，否則執(zhí)行第三個參數(shù)即在單元格B12中顯示不合格字樣。

在綜合評定欄中可以看到由于C列的同學(xué)各科平均分為54分，綜合評定為不合格。其余均為合格。

3、多層嵌套函數(shù)的應(yīng)用在上述的例子中，我們只是將成果簡潔區(qū)分為合格與不合格，在實際應(yīng)用中，成果通常是有多個等級的，比如優(yōu)、良、中、及格、不及格等。

有方法一次性區(qū)分嗎？可以使用多層嵌套的方法來實現(xiàn)。仍以上例為例，我們設(shè)定綜合評定的規(guī)章為當各科平均分超過90時，評定為優(yōu)秀。

如圖7所示。

圖7說明：為了解釋起來比較便利，我們

在這里僅做兩重嵌套的示例，您可以根據(jù)實際狀況進行更多重的嵌套，但請留意Excel的IF函數(shù)最多允許七重嵌套。

依據(jù)這一規(guī)章，我們在綜合評定中寫公式（以單元格F12為例）：=IF(F1160,IF(AND(F1190),優(yōu)秀,合格),不合格)語法解釋為，假如單元格F11的值大于60，則執(zhí)行其次個參數(shù)，在這里為嵌套函數(shù)，連續(xù)推斷單元格F11的值是否大于90（為了讓大家體會一下AND函數(shù)的應(yīng)用，寫成AND(F1190)，實際上可以僅寫F1190），假如滿意在單元格F12中顯示優(yōu)秀字樣，不滿意顯示合格字樣，假如F11的值以上條件都不滿意，則執(zhí)行第三個參數(shù)即在單元格F12中顯示不合格字樣。在綜合評定欄中可

以看到由于F列的同學(xué)各科平均分為92分，綜合評定為優(yōu)秀。

（三）依據(jù)條件計算值在了解了IF函數(shù)的使用方法后，我們再來看看與之類似的Excel供應(yīng)的可依據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。

例如，假如要計算單元格區(qū)域中某個文本串或數(shù)字消失的次數(shù)，則可使用COUNTIF工作表函數(shù)。假如要依據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。

關(guān)于SUMIF函數(shù)在數(shù)學(xué)與三角函數(shù)中以做了較為具體的介紹。

這里重點介紹COUNTIF的應(yīng)用。

COUNTIF可以用來計算給定區(qū)域內(nèi)滿意特定條件的單元格的數(shù)目。比如在成果表中計算每位同學(xué)取得優(yōu)秀成果的課程數(shù)。

在工資表中求出全部基本工資在2000元以上

的員工數(shù)。

語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿意條件的單元格數(shù)目的單元格區(qū)域。Criteria確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。

例如，條件可以表示為3

2、3

2、3

2、apples。

1、成果表這里仍以上述成果表的例子說明一些應(yīng)用方法。我們需要計算的是：每位同學(xué)取得優(yōu)秀成果的課程數(shù)。

規(guī)章為成果大于90分記做優(yōu)秀。

如圖8所示圖8依據(jù)這一規(guī)章，我們在優(yōu)秀門數(shù)中寫公式（以單元格B13為例）：=COUNTIF(B4:B10,90)語法解釋為，計算B4到B10這個范圍，即jarry的各科成果中有多少個數(shù)值大于90的單元格

。在優(yōu)秀門數(shù)欄中可以看到j(luò)arry的優(yōu)秀門數(shù)為兩門。其他人也可以依次看到。

2、銷售業(yè)績表銷售業(yè)績表可能是綜合運用I

F、SUMI

F、COUNTIF特別典型的示例。

比如，可能盼望計算銷售人員的訂單數(shù)，然后匯總每個銷售人員的銷售額，并且依據(jù)總發(fā)貨量打算每次銷售應(yīng)獲得的獎金。原始數(shù)據(jù)表如圖9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號排列）圖9原始數(shù)據(jù)表按銷售人員匯總表如圖10所示圖10銷售人員匯總表如圖10所示的表完全是利用函數(shù)計算的方法自動匯總的數(shù)據(jù)。

首先建立一個根據(jù)銷售人員匯總的表單樣式，如圖所示。

然后分別計算訂單數(shù)、訂單總額、銷售獎金。

（1）訂單數(shù)-用COUNTIF計算銷售人員

的訂單數(shù)。以銷售人員ANNIE的訂單數(shù)公式為例。

公式：=COUNTIF($C$2:$C$13,A17)語法解釋為計算單元格A17(即銷售人員ANNIE)在銷售人員清單$C$2:$C$13的范圍內(nèi)（即圖9所示的原始數(shù)據(jù)表）消失的次數(shù)。

這個消失的次數(shù)即可認為是該銷售人員ANNIE的訂單數(shù)。

（2）訂單總額-用SUMIF匯總每個銷售人員的銷售額。以銷售人員ANNIE的訂單總額公式為例。

公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在銷售人員清單$C$2:$C$13中檢查單元格A17中的文本（即銷售人員ANNIE），然后計算訂單金額列（$B$2:$B$13）中相應(yīng)量的

和。

這個相應(yīng)量的和就是銷售人員ANNIE的訂單總額。

（3）銷售獎金-用IF依據(jù)訂單總額打算每次銷售應(yīng)獲得的獎金。

假定公司的銷售獎金規(guī)章為當訂單總額超過5萬元時，嘉獎幅度為百分之十五，否則為百分之十。

依據(jù)這一規(guī)章仍以銷售人員ANNIE為例說明。

公式為：=IF(C***,10%,15%)*C17假如訂單總額小于***則獎金為10%；假如訂單總額大于等于***，則獎金為15%。

至此，我們已完全了解了EXCEL函數(shù)的規(guī)律函數(shù)，信任大家在實際工作中會想出更多更有用的運用。

所謂文本函數(shù)，就是可以在公式中處理文字串的函數(shù)。例如，可以轉(zhuǎn)變大小寫或確定文字串的長度；可以替換某些字符或者

去除某些字符等。

而日期和時間函數(shù)則可以在公式中分析和處理日期值和時間值。

關(guān)于這兩類函數(shù)的列表參看附表，這里僅對一些常用的函數(shù)做簡要介紹。

一、文本函數(shù)

（一）大小寫轉(zhuǎn)換LOWER-將一個文字串中的全部大寫字母轉(zhuǎn)換為小寫字母。UPPER-將文本轉(zhuǎn)換成大寫形式。PROPER-將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫。

將其余的字母轉(zhuǎn)換成小寫。

這三種函數(shù)的基本語法形式均為函數(shù)名（text）。示例說明：已有字符串為：pLeaseComEHere!可以看到由于輸入的不規(guī)范，這句話大小寫亂用了。

通過以上三個函數(shù)可以將文本轉(zhuǎn)換顯示樣式，使得文本變得規(guī)范。

參見圖1Lower（pLeaseCo

mEHere!）=pleasecomehere!upper（pLeaseComEHere!）=PLEASECOMEHERE!proper（pLeaseComEHere!）=PleaseComeHere!圖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是文本中要提取的第一個字符的位置。RIGHT函數(shù)：RIGHT(text,num_chars)其中Text是包含要提取字符的文本串。

Num_chars指定盼望RIGHT提取的字符數(shù)。

比如，從字符串Thisisanapple.分別取出字符This、apple、is的詳細函數(shù)寫法為。LEFT(Thisisanapple,4)=ThisRIGHT(Thisisanapple,5)=appleMID(Thisisanapple,6,2)=is圖2

（三）去除字符串的空白在字符串形態(tài)中，空白也是一個有效的字符，但是假如字符串中消失空白字符時，簡單在推斷或?qū)?/p>

比數(shù)據(jù)是發(fā)生錯誤，在Excel中您可以使用Trim函數(shù)清除字符串中的空白。語法形式為：TRIM(text)其中Text為需要清除其中空格的文本。

需要留意的是，Trim函數(shù)不會清除單詞之間的單個空格，假如連這部分空格都需清除的話，建議使用替換功能。

比如，從字符串MynameisMary中清除空格的函數(shù)寫法為：TRIM(MynameisMary)=MynameisMary參見圖3圖3

（四）字符串的比較在數(shù)據(jù)表中常常會比對不同的字符串，此時您可以使用EXACT函數(shù)來比較兩個字符串是否相同。

該函數(shù)測試兩個字符串是否完全相同。

假如它們完全相同，則返回TRUE；否則，返回FALS

E。

函數(shù)EXACT能區(qū)分大小寫，但忽視格式上的差異。利用函數(shù)EXACT可以測試輸入文檔內(nèi)的文字。語法形式為：EXACT(text1,text2)Text1為待比較的第一個字符串。

Text2為待比較的其次個字符串。舉例說明：參見圖4EXACT(China,china)=False圖4

二、日期與時間函數(shù)在數(shù)據(jù)表的處理過程中，日期與時間的函數(shù)是相當重要的處理依據(jù)。而Excel在這方面也供應(yīng)了相當豐富的函數(shù)供大家使用。

（一）取出當前系統(tǒng)時間/日期信息用于取出當前系統(tǒng)時間/日期信息的函數(shù)主要有NOW、TODAY。

語法形式均為函數(shù)名（）。

（二）取得日期/時間的部分字段值假如需要單獨的年份、月份

、日數(shù)或小時的數(shù)據(jù)時，可以使用HOUR、DAY、MONTH、YEAR函數(shù)直接從日期/時間中取出需要的數(shù)據(jù)。詳細示例參看圖5。

比如，需要返回2023-5-3012:30PM的年份、月份、日數(shù)及小時數(shù)，可以分別采納相應(yīng)函數(shù)實現(xiàn)。

YEAR(E5)=2023MONTH(E5)=5DAY(E5)=30HOUR(E5)=12圖5此外還有更多有用的日期/時間函數(shù)，可以查閱附表。

下面我們將以一個詳細的示例來說明Excel的文本函數(shù)與日期函數(shù)的用途。

三、示例：做一個美觀簡潔的人事資料分析表

1、示例說明在如圖6所示的某公司人事資料表中，除了編號、員工姓名、身份證號碼以及參與工作時間為手工添入外，其余各項均

為用函數(shù)計算所得。

圖6在此例中我們將具體說明如何通過函數(shù)求出：

（1）自動從身份證號碼中提取誕生年月、性別信息。

（2）自動從參與工作時間中提取工齡信息。

2、身份證號碼相關(guān)學(xué)問在了解如何實現(xiàn)自動從身份證號碼中提取誕生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。

我們知道，當今的身份證號碼有15/18位之分。

早期簽發(fā)的身份證號碼是15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴展（由兩位變?yōu)樗奈唬┖湍┪布恿诵灤a，就成了18位。這兩種身份證號碼將在相當長的一段時期內(nèi)共存。

兩種身份證號碼的含義如下：

（1）15位的身份證號碼：16位為地區(qū)代碼，78位為誕生年份(2位)，910位為誕生月份，1112位為

誕生日期，第1315位為挨次號，并能夠推斷性別，奇數(shù)為男，偶數(shù)為女。

（2）18位的身份證號碼：16位為地區(qū)代碼，710位為誕生年份(4位)，1112位為誕生月份，1314位為誕生日期，第1517位為挨次號，并能夠推斷性別，奇數(shù)為男，偶數(shù)為女。18位為效驗位。

3、應(yīng)用函數(shù)在此例中為了實現(xiàn)數(shù)據(jù)的自動提取，應(yīng)用了如下幾個Excel函數(shù)。

（1）IF函數(shù)：依據(jù)規(guī)律表達式測試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。

語法形式為：IF（logical_test,value_if_true,value_if_false）

（2）CONCATENATE：將若干個文字項合并至一個文字項中。

語法形式為：CONCAT

ENATE(text1,text2)

（3）MID：從文本字符串中指定的起始位置起，返回指定長度的字符。

語法形式為：MID(text,start_num,num_chars)

（4）TODAY：返回計算機系統(tǒng)內(nèi)部的當前日期。

語法形式為：TODAY（）

（5）DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。語法形式為：DATEDIF(start_date,end_date,unit)

（6）VALUE：將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。

語法形式為：VALUE(text)

（7）RIGHT：依據(jù)所指定的字符數(shù)返回文本串中最終一個或多個字符。

語法形式為：RIGHT(text,num_chars)

（8）INT：

返回實數(shù)舍入后的整數(shù)值。語法形式為：INT(number)

4、公式寫法及解釋（以員工Andy為例說明）說明：為避開公式中過多的嵌套，這里的身份證號碼限定為15位的。

假如您看懂了公式的話，可以進行簡潔的修改即可適用于18位的身份證號碼，甚至可適用于1

5、18兩者并存的狀況。

（1）依據(jù)身份證號碼求性別=IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),女,男)公式解釋：a.RIGHT(E4,3)用于求出身份證號碼中代表性別的數(shù)字，實際求得的為代表數(shù)字的字符串b.VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符串轉(zhuǎn)換為數(shù)字c.V

ALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2用于推斷這個身份證號碼是奇數(shù)還是偶數(shù)，當然你也可以用Mod函數(shù)來做出推斷。d.=IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),女,男)及假如上述公式推斷出這個號碼是偶數(shù)時，顯示女，否則，這個號碼是奇數(shù)的話，則返回男。

（2）依據(jù)身份證號碼求誕生日期=CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)公式解釋：a.MID(E4,7,2)為在身份證號碼中獵取表示年份的數(shù)字的字符串b.MID(E4,9,2

)為在身份證號碼中獵取表示月份的數(shù)字的字符串c.MID(E4,11,2)為在身份證號碼中獵取表示日期的數(shù)字的字符串d.CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)目的就是將多個字符串合并在一起顯示。

（3）依據(jù)參與工作時間求年資（即工齡）=CONCATENATE(DATEDIF(F4,TODAY(),y),年,DATEDIF(F4,TODAY(),ym),個月)公式解釋：a.TODAY()用于求出系統(tǒng)當前的時