Excel函數(shù)寶典

1、Excel函數(shù)簡介一、什么是函數(shù)什么是參數(shù)？參數(shù)可以是數(shù)字、文本、形如 TRUE 或 FALSE 的邏輯值、數(shù)組、形如 #N/A 的錯誤值或單元格引用。給定的參數(shù)必須能產(chǎn)生有效的值。參數(shù)也可以是常量、公式或其它函數(shù)。參數(shù)不僅僅是常量、公式或函數(shù)，還可以是數(shù)組、單元格引用等：1.數(shù)組-用于建立可產(chǎn)生多個結(jié)果或可對存放在行和列中的一組參數(shù)進行運算的單個公式。在 Microsoft Excel有兩類數(shù)組：區(qū)域數(shù)組和常量數(shù)組。區(qū)域數(shù)組是一個矩形的單元格區(qū)域，該區(qū)域中的單元格共用一個公式；常量數(shù)組將一組給定的常量用作某個公式中的參數(shù)。2.單元格引用-用于表示單元格在工作表所處位置的坐標值。例如，顯示在第

2、 B 列和第 3 行交叉處的單元格，其引用形式為"B3"。3.常量-常量是直接鍵入到單元格或公式中的數(shù)字或文本值，或由名稱所代表的數(shù)字或文本值。例如，日期 10/9/96、數(shù)字 210 和文本"Quarterly Earnings"都是常量。公式或由公式得出的數(shù)值都不是常量。函數(shù)是否可以是多重的呢？也就是說一個函數(shù)是否可以是另一個函數(shù)的參數(shù)呢？當然可以，這就是嵌套函數(shù)的含義。所謂嵌套函數(shù)，就是指在某些情況下，您可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用。如圖所示的公式使用了嵌套的 AVERAGE 函數(shù)，并將結(jié)果與 50 相比較。這個公式的含義是：如果單元格F

3、2到F5的平均值大于50，則求G2到G5的和，否則顯示數(shù)值0。 函數(shù)的結(jié)構(gòu)以函數(shù)名稱開始，后面是左圓括號、以逗號分隔的參數(shù)和右圓括號。如果函數(shù)以公式的形式出現(xiàn)，請在函數(shù)名稱前面鍵入等號（=） 公式選項板-幫助創(chuàng)建或編輯公式的工具，還可提供有關(guān)函數(shù)及其參數(shù)的信息。單擊編輯欄中的"編輯公式"按鈕，或是單擊"常用"工具欄中的"粘貼函數(shù)" 按鈕之后，就會在編輯欄下面出現(xiàn)公式選項板。整個過程如圖3所示。 什么是公式？函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預先定義好的特殊公式，后者就是由用戶自行設(shè)計對工作表進行計算和處理的計算式。以

4、公式“=SUM(E1:H1)*A1+26”為例，它要以等號“=”開始，其內(nèi)部可以包括函數(shù)、引用、運算符和常量。上式中的“SUM(E1:H1)”是函數(shù)，“A1”則是對單元格A1的引用(使用其中存儲的數(shù)據(jù))，“26”則是常量，“*”和“+”則是算術(shù)運算符(另外還有比較運算符、文本運算符和引用運算符) 二、使用函數(shù)的步驟1.單擊需要輸入函數(shù)的單元格2.點擊fx3.從彈出的菜單中選擇所需要的函數(shù)三、函數(shù)的種類1.數(shù)據(jù)庫函數(shù) - 當需要分析數(shù)據(jù)清單中的數(shù)值是否符合特定條件時，可以使用數(shù)據(jù)庫工作表函數(shù)。例如，在一個包含銷售信息的數(shù)據(jù)清單中，可以計算出所有銷售數(shù)值大于 1,000 且小于 2,500 的行或

5、記錄的總數(shù)。Microsoft Excel 共有 12 個工作表函數(shù)用于對存儲在數(shù)據(jù)清單或數(shù)據(jù)庫中的數(shù)據(jù)進行分析，這些函數(shù)的統(tǒng)一名稱為 Dfunctions，也稱為 D 函數(shù)，每個函數(shù)均有三個相同的參數(shù)：database、field 和 criteria，這些參數(shù)指向數(shù)據(jù)庫函數(shù)所使用的工作表區(qū)域。其中參數(shù) database 為工作表上包含數(shù)據(jù)清單的區(qū)域；參數(shù) field 為需要匯總的列的標志；參數(shù) criteria 為工作表上包含指定條件的區(qū)域。2.日期與時間函數(shù) - 通過日期與時間函數(shù)，可以在公式中分析和處理日期值和時間值。3.工程函數(shù) - 工程工作表函數(shù)用于工程分析。這類函數(shù)中的大多數(shù)可分

6、為三種類型：對復數(shù)進行處理的函數(shù)、在不同的數(shù)字系統(tǒng)（如十進制系統(tǒng)、十六進制系統(tǒng)、八進制系統(tǒng)和二進制系統(tǒng)）間進行數(shù)值轉(zhuǎn)換的函數(shù)、在不同的度量系統(tǒng)中進行數(shù)值轉(zhuǎn)換的函數(shù)。4.財務函數(shù)-財務函數(shù)可以進行一般的財務計算，如確定貸款的支付額、投資的未來值或凈現(xiàn)值，以及債券或息票的價值5.信息函數(shù) - 可以使用信息工作表函數(shù)確定存儲在單元格中的數(shù)據(jù)的類型。信息函數(shù)包含一組稱為 IS 的工作表函數(shù)，在單元格滿足條件時返回 TRUE。例如，如果單元格包含一個偶數(shù)值，ISEVEN 工作表函數(shù)返回 TRUE。如果需要確定某個單元格區(qū)域中是否存在空白單元格，可以使用 COUNTBLANK 工作表函數(shù)對單元格區(qū)域中的空

7、白單元格進行計數(shù)，或者使用 ISBLANK 工作表函數(shù)確定區(qū)域中的某個單元格是否為空。6.邏輯函數(shù) - 使用邏輯函數(shù)可以進行真假值判斷，或者進行復合檢驗。例如，可以使用 IF 函數(shù)確定條件為真還是假，并由此返回不同的數(shù)值。7.查詢和引用函數(shù)-當需要在數(shù)據(jù)清單或表格中查找特定數(shù)值，或者需要查找某一單元格的引用時，可以使用查詢和引用工作表函數(shù)。例如，如果需要在表格中查找與第一列中的值相匹配的數(shù)值，可以使用 VLOOKUP 工作表函數(shù)。如果需要確定數(shù)據(jù)清單中數(shù)值的位置，可以使用 MATCH 工作表函數(shù)。8.數(shù)學和三角函數(shù)-通過數(shù)學和三角函數(shù)，可以處理簡單的計算，例如對數(shù)字取整、計算單元格區(qū)域中的數(shù)值

8、總和或復雜計算。9.統(tǒng)計函數(shù)-統(tǒng)計工作表函數(shù)用于對數(shù)據(jù)區(qū)域進行統(tǒng)計分析。例如，統(tǒng)計工作表函數(shù)可以提供由一組給定值繪制出的直線的相關(guān)信息，如直線的斜率和 y 軸截距，或構(gòu)成直線的實際點數(shù)值。10.文本函數(shù)-通過文本函數(shù)，可以在公式中處理文字串。例如，可以改變大小寫或確定文字串的長度?？梢詫⑷掌诓迦胛淖执蜻B接在文字串上。下面的公式為一個示例，借以說明如何使用函數(shù) TODAY 和函數(shù) TEXT 來創(chuàng)建一條信息，該信息包含著當前日期并將日期以"dd-mm-yy"的格式表示。11.用戶自定義函數(shù)-如果要在公式或計算中使用特別復雜的計算，而工作表函數(shù)又無法滿足需要，則需要創(chuàng)建用戶自定

9、義函數(shù)。這些函數(shù)，稱為用戶自定義函數(shù)，可以通過使用 Visual Basic for Applications 來創(chuàng)建。函數(shù)名 功能 ABS 求出參數(shù)的絕對值。 AND “與”運算，返回邏輯值，僅當所有參數(shù)的結(jié)果均為邏輯“真（TRUE）”時返回邏輯“真（TRUE）”，反之返回邏輯“假（FALSE）”。 AVERAGE 求出所有參數(shù)的算術(shù)平均值。 COLUMN 顯示所引用單元格的列標號值。 CONCATENATE 將多個字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個單元格中。 COUNTIF 統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目。 DATE 給出指定數(shù)值的日期。 DATEDIF 計算返回

10、兩個日期參數(shù)的差值。 DAY 計算參數(shù)中指定日期或引用單元格中的日期天數(shù)。 DCOUNT 返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。 FREQUENCY 以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。 IF 根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應條件觸發(fā)的計算結(jié)果。 INDEX 返回列表或數(shù)組中的元素值，此元素由行序號和列序號的索引值進行確定。 INT 將數(shù)值向下取整為最接近的整數(shù)。 ISERROR 用于測試函數(shù)式返回的數(shù)值是否有錯。如果有錯，該函數(shù)返回TRUE，反之返回FALSE。 LEFT 從一個文本字符串的第一個字符開始，截取指定數(shù)目的字符。 LEN 統(tǒng)計文本字符

11、串中字符數(shù)目。 MATCH 返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應位置。 MAX 求出一組數(shù)中的最大值。 MID 從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。 MIN 求出一組數(shù)中的最小值。 MOD 求出兩數(shù)相除的余數(shù)。 MONTH 求出指定日期或引用單元格中的日期的月份。 NOW 給出當前系統(tǒng)日期和時間。 OR 僅當所有參數(shù)值均為邏輯“假（FALSE）”時返回結(jié)果邏輯“假（FALSE）”，否則都返回邏輯“真（TRUE）”。 RANK 返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位。 RIGHT 從一個文本字符串的最后一個字符開始，截取指定數(shù)目的字符。 SUBTOTAL 返回

12、列表或數(shù)據(jù)庫中的分類匯總。 SUM 求出一組數(shù)值的和。 SUMIF 計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。 TEXT 根據(jù)指定的數(shù)值格式將相應的數(shù)字轉(zhuǎn)換為文本形式 TODAY 給出系統(tǒng)日期 VALUE 將一個代表數(shù)值的文本型字符串轉(zhuǎn)換為數(shù)值型。 VLOOKUP 在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值 WEEKDAY 給出指定日期的對應的星期數(shù)。 Excel邏輯函數(shù)一、AND、OR、NOT函數(shù)（一）AND函數(shù)所有參數(shù)的邏輯值為真時返回 TRUE；只要一個參數(shù)的邏輯值為假即返回 FALSE。簡言之，就是當AND的參數(shù)全部滿足某一條件時，返回結(jié)果為TRUE，否則為FA

13、LSE。語法為AND(logical1,logical2, .)，其中Logical1, logical2, . 表示待檢測的 1 到 30 個條件值，各條件值可能為TRUE，可能為 FALSE。 參數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用。舉例說明：1、 在B2單元格中輸入數(shù)字50，在C2中寫公式=AND(B2>30,B2<60)。由于B2等于50的確大于30、小于60。所以兩個條件值（logical）均為真，則返回結(jié)果為TRUE。 2、 如果 B1-B3 單元格中的值為 TRUE、FALSE、TRUE，顯然三個參數(shù)并不都為真，所以 在B4單元格中的公式=AND(B1:B3)

14、等于 FALSE （二）OR函數(shù)OR函數(shù)指在其參數(shù)組中，任何一個參數(shù)邏輯值為 TRUE，即返回 TRUE。它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個為真即可為真。比如，上面的示例2，如果在B4單元格中的公式寫為=OR(B1:B3)則結(jié)果等于TRUE （三）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é)

15、果與之相反，為FALSE。二、IF函數(shù)（一）IF函數(shù)說明IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應用很廣泛，可以使用函數(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_false顯示 log

16、ical_test 為 FALSE 時返回的值。Value_if_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 還提供了可根據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。例如，如果要計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF

17、工作表函數(shù)。（二）IF函數(shù)應用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(C

18、5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結(jié)果，否則顯示字符串。（1） SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；（2） ""表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。 2、 不同的條件返回不同的結(jié)果我們設(shè)定按照平均分判斷該學生成績是否合格的規(guī)則。如果各科平均分超過60分則認為是合格的，否則記作不合格：=IF(B11>60,"合格","不合格")語法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個參數(shù)即在單元格

19、B12中顯示合格字樣，否則執(zhí)行第三個參數(shù)即在單元格B12中顯示不合格字樣。3、 多層嵌套函數(shù)的應用(Excel的IF函數(shù)最多允許七重嵌套)我們設(shè)定綜合評定的規(guī)則為當各科平均分超過90時，評定為優(yōu)秀=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格"),"不合格")語法解釋為，如果單元格F11的值大于60，則執(zhí)行第二個參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格F11的值是否大于90（為了讓大家體會一下AND函數(shù)的應用，寫成AND(F11>90)，實際上可以僅寫F11>90），如果滿足在單元格F12中

20、顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個參數(shù)即在單元格F12中顯示不合格字樣。（三）根據(jù)條件計算值COUNTIF可以用來計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。比如在成績表中計算每位學生取得優(yōu)秀成績的課程數(shù)。在工資表中求出所有基本工資在2000元以上的員工數(shù)。語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為 32、"32"、">32"、&

21、quot;apples"。1、成績表=COUNTIF(B4:B10,">90")語法解釋為，計算B4到B10這個范圍中有多少個數(shù)值大于90的單元格2、 銷售業(yè)績表銷售業(yè)績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計算銷售人員的訂單數(shù)，然后匯總每個銷售人員的銷售額，并且根據(jù)總發(fā)貨量決定每次銷售應獲得的獎金。 按銷售人員匯總表 如圖10所示的表完全是利用函數(shù)計算的方法自動匯總的數(shù)據(jù)。首先建立一個按照銷售人員匯總的表單樣式，如圖所示。然后分別計算訂單數(shù)、訂單總額、銷售獎金。（1） 訂單數(shù) -用COUNTIF計算銷售人員的訂單數(shù)。

22、=COUNTIF($C$2:$C$13,A17)語法解釋為計算單元格A17(即銷售人員ANNIE)在"銷售人員"清單$C$2:$C$13的范圍內(nèi)出現(xiàn)的次數(shù)，這個出現(xiàn)的次數(shù)即可認為是該銷售人員ANNIE的訂單數(shù)。（2） 訂單總額-用SUMIF匯總每個銷售人員的銷售額。=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在"銷售人員"清單$C$2:$C$13中檢查單元格A17 中的文本（即銷售人員ANNIE），然后計算"訂單金額"列（$B$2:$B$13）中相應量的和。這個相應量的和就是銷售人員ANNIE的訂單總額。（

23、3） 銷售獎金-用IF根據(jù)訂單總額決定每次銷售應獲得的獎金。假定公司的銷售獎金規(guī)則為當訂單總額超過5萬元時，獎勵幅度為百分之十五，否則為百分之十。根據(jù)這一規(guī)則仍以銷售人員ANNIE為例說明。公式為：=IF(C17<50000,10%,15%)*C17 - 如果訂單總額小于 50000則獎金為 10%；如果訂單總額大于等于 50000，則獎金為 15%。Excel統(tǒng)計函數(shù)在介紹統(tǒng)計函數(shù)之前，請大家先看一下附表中的函數(shù)名稱。是不是發(fā)現(xiàn)有些函數(shù)是很類似的，只是在名稱中多了一個字母A？比如，AVERAGE與AVERAGEA；COUNT與COUNTA。基本上，名稱中帶A的函數(shù)在統(tǒng)計時不僅統(tǒng)計數(shù)字，

24、而且文本和邏輯值（如TRUE 和 FALSE）也將計算在內(nèi)一、用于求平均值的統(tǒng)計函數(shù)AVERAGE、TRIMMEAN1、求參數(shù)的算術(shù)平均值函數(shù)AVERAGE語法形式為AVERAGE(number1,number2, .) 其中Number1, number2, .為要計算平均值的 130 個參數(shù)。這些參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用。如果數(shù)組或單元格引用參數(shù)中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計算在內(nèi)。2、求數(shù)據(jù)集的內(nèi)部平均值TRIMMEAN函數(shù)TRIMMEAN先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點，然后再求平均值。當希望在分析中剔除一部分數(shù)

25、據(jù)的計算時，可以使用此函數(shù)。比如，我們在計算選手平均分數(shù)中常用去掉一個最高分，去掉一個最低分，XX號選手的最后得分，就可以使用該函數(shù)來計算。語法形式為TRIMMEAN(array,percent)其中Array為需要進行篩選并求平均值的數(shù)組或數(shù)據(jù)區(qū)域。Percent為計算時所要除去的數(shù)據(jù)點的比例，例如，如果，在 20 個數(shù)據(jù)點的集合中，就要除去 4 個數(shù)據(jù)點（），頭部除去 2 個，尾部除去 2 個。函數(shù) TRIMMEAN 將除去的數(shù)據(jù)點數(shù)目向下舍為最接近的 2 的倍數(shù)。二、用于求單元格個數(shù)的統(tǒng)計函數(shù)COUNT語法形式為COUNT(value1,value2, .)其中Value1, value

26、2, .為包含或引用各種類型數(shù)據(jù)的參數(shù)（130個），但只有數(shù)字類型的數(shù)據(jù)才被計數(shù)。函數(shù) COUNT 在計數(shù)時，將把數(shù)字、空值、邏輯值、日期或以文字代表的數(shù)計算進去；但是錯誤值或其他無法轉(zhuǎn)化成數(shù)字的文字則被忽略。如果參數(shù)是一個數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字；數(shù)組中或引用的空單元格、邏輯值、文字或錯誤值都將忽略。如果要統(tǒng)計邏輯值、文字或錯誤值，應當使用函數(shù) COUNTA: 要計算一共有多少評委參與評分（用函數(shù)COUNTA），以及有幾個評委給出了有效分數(shù)（用函數(shù)COUNT）。 三、求區(qū)域中數(shù)據(jù)的頻率分布FREQUENCY由于函數(shù) FREQUENCY 返回一個數(shù)組，必須以數(shù)組公式的形式輸入。

27、 語法形式為FREQUENCY(data_array,bins_array)其中Data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來計算頻率。如果 data_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回零數(shù)組。Bins_array為一數(shù)組或?qū)?shù)組區(qū)域的引用，設(shè)定對 data_array 進行頻率計算的分段點。如果 bins_array 中不包含任何數(shù)值，函數(shù) FREQUENCY 返回 data_array 元素的數(shù)目。以計算某公司的員工年齡分布情況為例說明。在工作表里列出了員工的年齡。這些年齡為 28、25、31、21、44、33、22 和 35，并分別輸入到單元格 C4:C1

28、1。這一列年齡就是 data_array。Bins_array 是另一列用來對年齡分組的區(qū)間值。在本例中，bins_array 是指 C13:C16 單元格，分別含有值 25、30、35、和 40。以數(shù)組形式輸入函數(shù) FREQUENCY，就可以計算出年齡在 25歲以下、2630歲、3135歲、3640歲和40歲以上各區(qū)間中的數(shù)目。本例中選擇了5個垂直相鄰的單元格后，即以數(shù)組公式輸入下面的公式。返回的數(shù)組中的元素個數(shù)比 bins_array（數(shù)組）中的元素個數(shù)多 1。第五個數(shù)字1表示大于最高間隔 (40) 的數(shù)值（44）的個數(shù)。函數(shù) FREQUENCY 忽略空白單元格和文本值。=FREQUENC

29、Y(C4:C11,C13:C16)等于 2;2;2;1;1 四、一組用于求數(shù)據(jù)集的滿足不同要求的數(shù)值的函數(shù)1、求數(shù)據(jù)集的最大值MAX與最小值MIN這兩個函數(shù)MAX、MIN就是用來求解數(shù)據(jù)集的極值（即最大值、最小值）。函數(shù)的用法非常簡單。語法形式為 函數(shù)（number1,number2,.），其中Number1,number2,. 為需要找出最大數(shù)值的 1 到 30 個數(shù)值。如果要計算數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。因此如果邏輯值和文本不能忽略，請使用帶A的函數(shù)MAXA或者MINA 來代替。2、求數(shù)據(jù)集中第K個最大值LARGE與第k個最小值SMALL這兩個函數(shù)LARGE、SMAL

30、L與MAX、MIN非常相像，區(qū)別在于它們返回的不是極值，而是第K個值。語法形式為：函數(shù)(array,k)，其中Array為需要找到第 k 個最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。K為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置（如果是LARGE為從大到小排，若為SMALL函數(shù)則從小到大排）。說到這，大家可以想得到吧。如果K=1或者K=n（假定數(shù)據(jù)集中有n個數(shù)據(jù)）的時候，是不是就可以返回數(shù)據(jù)集的最大值或者最小值了呢。3、 求數(shù)據(jù)集中的中位數(shù)MEDIANMEDIAN函數(shù)返回給定數(shù)值集合的中位數(shù)。所謂中位數(shù)是指在一組數(shù)據(jù)中居于中間的數(shù)，換句話說，在這組數(shù)據(jù)中，有一半的數(shù)據(jù)比它大，有一半的數(shù)據(jù)比它小。語法形式為MEDI

31、AN(number1,number2, .)其中Number1, number2,.是需要找出中位數(shù)的 1 到 30 個數(shù)字參數(shù)。如果數(shù)組或引用參數(shù)中包含有文字、邏輯值或空白單元格，則忽略這些值，但是其值為零的單元格會計算在內(nèi)。 需要注意的是，如果參數(shù)集合中包含有偶數(shù)個數(shù)字，函數(shù) MEDIAN 將返回位于中間的兩個數(shù)的平均值。4、 求數(shù)據(jù)集中出現(xiàn)頻率最多的數(shù)MODEMODE函數(shù)用來返回在某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值。跟 MEDIAN 一樣，MODE 也是一個位置測量函數(shù)。語法形式為MODE(number1,number2, .)其中Number1, number2, . 是用于眾數(shù)（

32、眾數(shù)指在一組數(shù)值中出現(xiàn)頻率最高的數(shù)值）計算的 1 到 30 個參數(shù)，也可以使用單一數(shù)組（即對數(shù)組區(qū)域的引用）來代替由逗號分隔的參數(shù)。5、 以上函數(shù)的示例以某單位年終獎金分配表為例說明。在示例中，我們將利用這些函數(shù)求解該單位年終獎金分配中的最高金額、最低金額、平均金額、中間金額、眾數(shù)金額以及第二高金額等。詳細的公式寫法可從圖中清楚的看出，在此不再贅述五、用來排位的函數(shù)RANK、PERCENTRANK1、一個數(shù)值在一組數(shù)值中的排位的函數(shù)RANK數(shù)值的排位是與數(shù)據(jù)清單中其他數(shù)值的相對大小，當然如果數(shù)據(jù)清單已經(jīng)排過序了，則數(shù)值的排位就是它當前的位置。數(shù)據(jù)清單的排序可以使用Excel提供的排序功能完成。

33、語法形式為RANK(number,ref,order) 其中Number為需要找到排位的數(shù)字；Ref 為包含一組數(shù)字的數(shù)組或引用。Order為一數(shù)字用來指明排位的方式。如果 order 為 0 或省略，則Excel 將 ref 當作按降序排列的數(shù)據(jù)清單進行排位。如果 order 不為零，Microsoft Excel 將 ref 當作按升序排列的數(shù)據(jù)清單進行排位。 需要說明的是，函數(shù) RANK 對重復數(shù)的排位相同。但重復數(shù)的存在將影響后續(xù)數(shù)值的排位。嗯，這就好像并列第幾的概念啊。例如，在一列整數(shù)里，如果整數(shù) 10 出現(xiàn)兩次，其排位為 5，則 11 的排位為 7（沒有排位為 6 的數(shù)值）。2、求

34、特定數(shù)值在一個數(shù)據(jù)集中的百分比排位的函數(shù)PERCENTRANK此PERCENTRANK函數(shù)可用于查看特定數(shù)據(jù)在數(shù)據(jù)集中所處的位置。例如，可以使用函數(shù) PERCENTRANK 計算某個特定的能力測試得分在所有的能力測試得分中的位置。語法形式為PERCENTRANK(array,x,significance) 其中Array為彼此間相對位置確定的數(shù)字數(shù)組或數(shù)字區(qū)域。X為數(shù)組中需要得到其排位的值。Significance為可選項，表示返回的百分數(shù)值的有效位數(shù)。如果省略，函數(shù) PERCENTRANK 保留 3 位小數(shù)。3、與排名有關(guān)的示例仍以某單位的年終獎金分配為例說明，這里以員工Annie的排名為例

35、說明公式的寫法。獎金排名的公式寫法為：=RANK(C3,$C$3:$C$12)百分比排名的公式寫法為：=PERCENTRANK($C$3:$C$12,C3) Excel數(shù)據(jù)庫函數(shù)在Microsoft Excel 中包含了一些工作表函數(shù)，它們用于對存儲在數(shù)據(jù)清單或數(shù)據(jù)庫中的數(shù)據(jù)進行分析，這些函數(shù)統(tǒng)稱為數(shù)據(jù)庫函數(shù)Dfunctions。一、函數(shù)的共同特點（1）每個函數(shù)均有三個參數(shù)：database、field 和 criteria。這些參數(shù)指向函數(shù)所使用的工作表區(qū)域。（2）除了GETPIVOTDATA函數(shù)之外，其余十二個函數(shù)都以字母D開頭。（3）如果將字母D去掉，可以發(fā)現(xiàn)其實大多數(shù)數(shù)據(jù)庫函數(shù)已經(jīng)在E

36、xcel的其他類型函數(shù)中出現(xiàn)過了。比如，DAVERAGE將D去掉的話，就是求平均值的函數(shù)AVERAGE。二、數(shù)據(jù)庫函數(shù)列表 三、數(shù)據(jù)庫函數(shù)的參數(shù)含義該類函數(shù)的語法形式為 函數(shù)名稱(database,field,criteria)。Database為構(gòu)成數(shù)據(jù)清單或數(shù)據(jù)庫的單元格區(qū)域。數(shù)據(jù)庫是包含一組相關(guān)數(shù)據(jù)的數(shù)據(jù)清單，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。數(shù)據(jù)清單的第一行包含著每一列的標志項。Field為指定函數(shù)所使用的數(shù)據(jù)列。數(shù)據(jù)清單中的數(shù)據(jù)列必須在第一行具有標志項。Field 可以是文本，即兩端帶引號的標志項，如“使用年數(shù)”或“產(chǎn)量”；此外，F(xiàn)ield 也可以是代表數(shù)據(jù)清單中數(shù)據(jù)

37、列位置的數(shù)字：1 表示第一列，2 表示第二列，等等。Criteria為一組包含給定條件的單元格區(qū)域?？梢詾閰?shù) criteria 指定任意區(qū)域，只要它至少包含一個列標志和列標志下方用于設(shè)定條件的單元格。四、舉例說明1、例：某果園的果樹的高度、使用年數(shù)、產(chǎn)量與利潤的統(tǒng)計數(shù)據(jù)表如圖所示， （1） 有多少種蘋果樹的樹高在1016英尺之間（2） 蘋果樹與梨樹的最大利潤值是多少（3） 高度大于 10 英尺的蘋果樹的最小利潤是多少（4） 蘋果樹的總利潤（5） 高度大于 10 英尺的蘋果樹的平均產(chǎn)量（6） 果園中所有樹種的平均使用年數(shù)（7） 求蘋果樹和梨樹產(chǎn)量的估算標準偏差、真實標準偏差、估算方差、真實方差

38、。2、求解步驟（1） 創(chuàng)建空白工作簿或工作表，將數(shù)據(jù)錄入Excel中 （2） 由于第一問需要求解有多少種蘋果樹的樹高在1016英尺之間，因此建立查詢條件高度在10與16之間 （3） 利用函數(shù)DCOUNT求解滿足條件的單元格數(shù)來計算高度在1016英尺的蘋果樹的種類。公式為：DCOUNT(A4:E10,"使用年數(shù)",A1:F2)，求出有一種蘋果樹滿足條件。（4） 由于第二問為求蘋果樹與梨樹的最大利潤值，因此再建立一個查詢條件梨樹（5） 利用函數(shù)DMAX求出蘋果樹與梨樹的最大利潤，同理可以利用函數(shù)DMIN求出高度大于10英尺蘋果樹的最小利潤。具體公式為：=DMAX(A4:E10,

39、"利潤",A1:A3) 求出蘋果樹與梨樹的最大利潤為105=DMIN(A4:E10,"利潤",A1:B2) 求出高度大于10英尺蘋果樹的最小利潤為75（6） 利用函數(shù)DSUM可以求出所有蘋果樹的總利潤。公式為：=DSUM(A4:E10,"利潤",A1:A2) 總利潤為225（7） 其他各問均可采用類似的函數(shù)求解，詳細的公式如圖所示。 五、需要注意的地方1、可以為參數(shù) criteria 指定任意區(qū)域，只要它至少包含一個列標志和列標志下方用于設(shè)定條件的單元格。 例如，如果區(qū)域 A1:A2 在 A1 中包含列標志“樹種”，在A2中包含名稱蘋

40、果樹，可將此區(qū)域命名為蘋果樹樹種，那么在數(shù)據(jù)庫函數(shù)中就可使用該名稱作為參數(shù) criteria。 2、雖然條件區(qū)域可以在工作表的任意位置，但不要將條件區(qū)域置于數(shù)據(jù)清單的下方。因為如果使用“數(shù)據(jù)”菜單中的“記錄單”命令在數(shù)據(jù)清單中添加信息，新的信息將被添加在數(shù)據(jù)清單下方的第一行上。如果數(shù)據(jù)清單下方的行非空，Microsoft Excel 將無法添加新的信息。 3、確定條件區(qū)域沒有與數(shù)據(jù)清單相重疊。4、若要對數(shù)據(jù)庫的整個列進行操作，需要在條件區(qū)域中的列標志下方輸入一個空白行。六、關(guān)于條件的建立在上面的示例中，我們簡單介紹了條件區(qū)域的建立，在這里詳細介紹有關(guān)在Excel中利用高級條件進行數(shù)據(jù)篩選的方法

41、。1、 有關(guān)概念條件是指所指定的限制查詢或篩選的結(jié)果集中包含哪些記錄的條件。例如，上面示例中條件選擇“高度”字段的值大于10的記錄：高度>10。清單是指包含相關(guān)數(shù)據(jù)的一系列工作表行，例如，發(fā)票數(shù)據(jù)庫或一組客戶名稱和 號碼。清單的第一行具有列標志。2、 建立條件區(qū)域的基本要求（1）在可用作條件區(qū)域的數(shù)據(jù)清單上插入至少三個空白行。（2）條件區(qū)域必須具有列標志。（3）請確保在條件值與數(shù)據(jù)清單之間至少留了一個空白行。如在上面的示例中A1:F3就是一個條件區(qū)域，其中第一行為列標志，如樹種、高度。3、 篩選條件的建立在列標志下面的一行中，鍵入所要匹配的條件。所有以該文本開始的項都將被篩選。例如，如果

42、您鍵入文本“Dav”作為條件，Microsoft Excel 將查找“Davolio”、“David”和“Davis”。如果只匹配指定的文本，可鍵入公式=''=text''，其中“text”是需要查找的文本。如果要查找某些字符相同但其他字符不一定相同的文本值，則可使用通配符。Excel中支持的通配符為： 4、 幾種不同條件的建立（1）單列上具有多個條件如果對于某一列具有兩個或多個篩選條件，那么可直接在各行中從上到下依次鍵入各個條件。例如，上面示例的條件區(qū)域顯示“樹種”列中包含“蘋果樹”或“梨樹”的行。（2）多列上具有單個條件若要在兩列或多列中查找滿足單個條件的數(shù)

43、據(jù)，請在條件區(qū)域的同一行中輸入所有條件。例如，下面示例的條件區(qū)域顯示所有在“高度”列中大于10且“產(chǎn)量”大于10的數(shù)據(jù)行。 （3）某一列或另一列上具有單個條件若要找到滿足一列條件或另一列條件的數(shù)據(jù)，請在條件區(qū)域的不同行中輸入條件。例如，上面示例的條件區(qū)域顯示所有在“高度”列中大于10的數(shù)據(jù)行。（4）兩列上具有兩組條件之一若要找到滿足兩組條件（每一組條件都包含針對多列的條件）之一的數(shù)據(jù)行，請在各行中鍵入條件。例如，下面的條件區(qū)域?qū)@示所有在“樹種”列中包含“蘋果樹”且“高度”大于10的數(shù)據(jù)行，同時也顯示“櫻桃樹”的“使用年數(shù)”大于10年的行。 （5）一列有兩組以上條件若要找到滿足兩組以上條件的行

44、，請用相同的列標包括多列。例如，上面示例的條件區(qū)域顯示介于10和16之間的高度。（6）將公式結(jié)果用作條件Excel中可以將公式（公式：單元格中的一系列值、單元格引用、名稱或運算符的組合，可生成新的值。公式總是以等號 (=) 開始。）的計算結(jié)果作為條件使用。用公式創(chuàng)建條件時，不要將列標志作為條件標記使用，應該將條件標記置空，或者使用清單中非列標志的標記。例如，下面的條件區(qū)域顯示在列 C 中，其值大于單元格區(qū)域 C7:C10 平均值的行。=C7>AVERAGE($C$7:$C$10) 需要注意的是用作條件的公式必須使用相對引用來引用列標志（例如，“高度”），或者引用第一個記錄的對應字段。公式

45、中的所有其他引用都必須是絕對引用并且公式必須計算出結(jié)果 TRUE 或 FALSE。在本公式示例中，C7 引用了數(shù)據(jù)清單中第一個記錄（行 7）的字段（列 C）。 當然也可以在公式中使用列標志來代替相對的單元格引用或區(qū)域名稱。當 Microsoft Excel 在包含條件的單元格中顯示錯誤值 #NAME? 或 #VALUE! 時，您可以忽略這些錯誤，因為它們不影響列表的篩選。 此外Microsoft Excel 在計算數(shù)據(jù)時不區(qū)分大小寫?？焖俎D(zhuǎn)換學生考試成績等級有的時候，會遇到要將學生的考試成績按實際考試分數(shù)轉(zhuǎn)換成相應成績等級的情況，如將考試成績在90分以上的成績轉(zhuǎn)換成“A+”形式，85-89分的

46、成績轉(zhuǎn)換成“A”形式.。一般情況，在excel表格中大家會采用IF()函數(shù)來設(shè)計公式進行轉(zhuǎn)換，這樣所設(shè)計的公式會變得很復雜，如果進行轉(zhuǎn)換的成績等級類型超過IF()函數(shù)的最大嵌套(7層)時，IF()函數(shù)就無能為力了。這時我們可用如下的方法來簡化操作。1、打開學生成績工作表2、在G2到I12單元格錄入考試成績分數(shù)段與考試成績等級對照表。3、在D3單元格錄入公式“=INDEX(I$3:I$12,MATCH(1,(C3>=G$3:G$12)*(C3<=H$3:H$12),0)”，由于該公式為數(shù)組公式，在錄入完上述內(nèi)容后，必須同時按下“Ctrl+Shift+Enter”鍵，為上述公式內(nèi)容加上

47、數(shù)組公式標志即大括號“”。該公式的作用就是，根據(jù)C3單元格中的學生成績，在D3單元格自動將該成績轉(zhuǎn)換成相應的成績等級。4、將光標移到D3單元格，向下拖動填充柄至D12單元格，將公式進行快速復制，這樣就可以迅速完成轉(zhuǎn)換學生成績等級的工作(圖2)。5、還可以按照自己的喜好，將G2至I12的單元格區(qū)域設(shè)置為“隱藏”，以使表格更加美觀。Excel的宏管理倉庫的事例      一位朋友從事汽車小配件批發(fā)經(jīng)營，在倉庫管理方面令他很傷腦筋。他經(jīng)營有百多種不同規(guī)格的產(chǎn)品且各種規(guī)格產(chǎn)品每天都有數(shù)萬至數(shù)十萬的入庫出庫量，針對這些龐大的數(shù)字想查詢各種產(chǎn)品的庫存情況卻

48、無從下手，導致了經(jīng)常出現(xiàn)某些產(chǎn)品庫存短缺而某些產(chǎn)品庫存過剩的情況。為此，他希望建立一個工作表能隨時查詢各種產(chǎn)品的庫存情況而操作要求盡量方便。下面以四種不同規(guī)格產(chǎn)品五天的庫存情況為例來介紹我的解決方案。      1.數(shù)據(jù)清單的建立      根據(jù)具體情況用Excel建立數(shù)據(jù)清單(圖1)，表中每種不同規(guī)格產(chǎn)品的庫存量即為該產(chǎn)品所有時間當日結(jié)存的總和。      2.宏的應用      點擊“工具”“

49、宏”“錄制新宏”彈出“錄制新宏”對話框，在“宏名”框內(nèi)輸入宏名“匯總”，在快捷鍵下的框內(nèi)輸入“H”將此宏的快捷操作定為“Ctrl+Shift+H”，單擊“確定”后系統(tǒng)會出現(xiàn)“停止錄制”的工具條。將光標定在數(shù)據(jù)清單內(nèi)任一單元格，點擊“數(shù)據(jù)”菜單“排序”彈出排序?qū)υ捒?，在“主要關(guān)鍵字”的下拉框內(nèi)選定“品名”、在“次要關(guān)鍵字”的下拉框內(nèi)選定“規(guī)格”后單擊“確定”。再次點擊“數(shù)據(jù)”菜單“分類匯總”彈出對話框，在“分類字段”的下拉框內(nèi)選定“品名”、在“匯總”的下拉框內(nèi)選定“求和”、在“選定匯總項”框內(nèi)選定“當日結(jié)存”       復選框、將“替換當前分類

50、匯總”和“匯總結(jié)果顯示在數(shù)據(jù)下方”兩項的復選框打鉤并單擊“確定”。再次點擊“數(shù)據(jù)”菜單“分類匯總”彈出對話框，在“分類字段”的下拉框內(nèi)選定“規(guī)格”，去掉“替換當前分類匯總”前復選框的鉤，其他選項不變，單擊“確定”，在分類匯總表的左側(cè)出現(xiàn)的分類層次的選擇按鈕中選“3”，便可出現(xiàn)如圖2的工作表，此表可一目了然顯示各種產(chǎn)品的庫存情況。單擊“停止錄制”工具條上“停止錄制”命令按鈕，便完成了第一個宏的錄制工作。      依照第點方法再錄制一個名為“一覽表”的宏，將快捷操作定為“Ctrl+Shift+Y”，將光標定在圖2分類匯總表內(nèi)任一單元格，點擊“數(shù)據(jù)

51、”菜單“分類匯總”彈出對話框，單擊“全部刪除”“數(shù)據(jù)”“排序”彈出排序?qū)υ捒?，在“主要關(guān)鍵字”的下拉框內(nèi)選定“日期”，單擊“確定”后再單擊“停止錄制”的工具條上“停止錄制”命令按鈕，便完成了第二個宏的錄制工作。      3.建立新菜單      為了更方便操作，我們將建立新菜單來執(zhí)行“宏”的操作，具體步驟如下：      在工具欄或菜單欄的任一處點擊右鍵“自定義”選定“命令”選項卡，在左側(cè)“類別”框內(nèi)選定“新菜單”，   &

52、#160;   在右側(cè)的“命令”框內(nèi)將“新菜單”用鼠標拖到菜單欄上“數(shù)據(jù)”菜單的右側(cè)，單擊“自定義”對話框內(nèi)“更改所選內(nèi)容”下拉箭頭，在“命名”框內(nèi)輸入“倉存”后確定，這時菜單欄上多了個名為“倉存”的菜單(圖2)。      在“自定義”對話框內(nèi)“命令”選項左側(cè)“類別”框內(nèi)選定“宏”，在右側(cè)的“命令”框內(nèi)將“自定義按鈕”用鼠標拖到菜單欄上“倉存”菜單的下方，單擊“更改所選內(nèi)容”下拉箭頭，在“命名”框內(nèi)輸入“匯總”，在“更改按鈕圖”選項選定如圖2的圖標，在“指定宏”選項指定“匯總”宏，單擊“確定”后便完成“倉存”菜單下“匯總”

53、菜單命令設(shè)計工作。用同樣的方法在“倉存”菜單下再設(shè)計名為“一覽表”的菜單命令。至此，我們就完成了全部設(shè)計工作，只要點擊“倉存”菜單下“匯總”菜單命令系統(tǒng)便可自動將各種規(guī)格的產(chǎn)品倉存情況顯示出來，再點擊“一覽表”菜單命令便可還原到明細表狀態(tài)。這樣我們就可方便地隨時查詢各種產(chǎn)品的庫存情況了。讓Excel錄入成績更智能化功能一：在“成績”列輸入成績后，在“等第”列就能智能地顯示出相應的“等第”，如果“等第”為“不及格”，還會用紅色字體提醒。 功能二：在“成績”列中誤輸入文字或者輸入的成績數(shù)值不符合具體要求時（小學考試多采用100分制，數(shù)值大于100或者小于0時都是錯誤的），在“等第”列就會用醒目的灰

54、底黃字顯示提示信息：“分數(shù)輸入錯誤”。 功能三：當某位學生因病或因事缺考，“成績”列中的分數(shù)為空時，相應的“等第”也為空，不會出現(xiàn)因為學生缺考而導致“等第”是“不及格”的現(xiàn)象。 制作方法 1. 新建Excel工作簿，在單元格A1、B1、C1中分別輸入“姓名”、“成績”、“等第”。 2. 從A2開始輸入學生姓名，從B2開始輸入學生成績，C2顯示相應的“等第”，在C2單元格中輸入如下內(nèi)容：“=IF(ISTEXT(B2),"分數(shù)輸入錯誤",IF(OR(B2<0,B2>100),"分數(shù)輸入錯誤",IF(B2>=85,"優(yōu)秀"

55、,IF(B2>=75,"良好",IF(B2>=60,"及格",IF(ISNUMBER(B2),"不及格",IF(ISBLANK(B2)," ",)”（如圖2）。 說明：這里應用了IF函數(shù)的嵌套，如果第一個邏輯判斷表達式“ISTEXT(B2)”為真時，在C2中就顯示“分數(shù)輸入錯誤”，如果為假，就執(zhí)行第二個IF語句；如果第二個IF語句中的邏輯表達式“OR(B2<0,B2>100)”為真，在C2中就顯示“分數(shù)輸入錯誤”，如果為假，就執(zhí)行第三個IF語句中的邏輯表達式依此類推，直至結(jié)束。整個IF語句的

56、意思是：當您在B2單元格輸入的內(nèi)容是文字時，在C2單元格就顯示“分數(shù)輸入錯誤”；當您輸入的數(shù)值比0小或者比100大時，也顯示“分數(shù)輸入錯誤”，當B2的數(shù)值大于或等于85時就顯示“優(yōu)秀”，當B2的數(shù)值大于或等于75時就顯示“良好”，當B2的數(shù)值大于或等于60時就顯示“及格”，如果是其他數(shù)值就顯示“不及格”；如果B2單元格內(nèi)容為空，那么C2也為空。 3. 拖動C2的“填充柄”，利用Excel的自動填充功能將這個公式復制到下面的單元格中。 4. 同時選中列B和列C，點擊“格式/條件格式”，出現(xiàn)“條件格式”設(shè)置窗口，（點擊“添加條件”可以添加更多的條件，但最多是3個條件），對“條件格式”進行如圖3所示

57、設(shè)置（點擊相應條件中的“格式”就能設(shè)置文字的格式），并保存文檔。 說明：“條件1”限定所有介與0和59之間的數(shù)值變?yōu)榧t色字體；“條件2”限定所有等于“不及格”的單元格文字變?yōu)榧t色字體；“條件3”限定所有等于“分數(shù)輸入錯誤”的單元格文字為藍底黃字。用Excel函數(shù)快速整理錯亂成績表 (1) “學生基本信息表”的姓名與“成績表”中的姓名不一樣，“學生基本信息表”中的“王一”在“成績表”中為“ 王一”，出現(xiàn)了全角或半角空格。 (2) “學生基本信息表”中王小平在“成績表”中無此人，即“學生基本信息表”的人數(shù)多于“成績表”的人數(shù)。 (3) “成績表”中成績列為文本方式，且出現(xiàn)了全角數(shù)字。 (4) 每個

58、表的數(shù)據(jù)為幾千條。如果對“成績表”中的姓名列進行排序，把成績列進行復制粘貼到“學生基本信息表”中的成績列，出現(xiàn)錯位。我通過Excel函數(shù)SUBSTITUTE和LOOKUP來解決，將“學生基本信息表”和“成績表”進行了一些修改，實現(xiàn)將“成績表”中的數(shù)據(jù)復制到“學生基本信息表”中，并且保持最終表格的清爽和數(shù)據(jù)的正確。除去“成績表”中全角或半角空格：讓“成績表”中的學生姓名顯示和“學生基本信息表”中的一樣。此時我利用替換公式SUBSTITUTE(SUBSTITUTE(A2，"半角空格 "，"")，"全角空格"，"")。在

59、D2單元格輸入公式=SUBSTITUTE(SUBSTITUTE(A2，" "，"")，""，"")，然后在整個D列復制公式。選擇D列數(shù)據(jù)進行復制，再選擇A列所有數(shù)據(jù)選擇性粘貼值和數(shù)字格式。轉(zhuǎn)化“成績表”中成績列為數(shù)字：刪除了空格，下面的工作就是將“成績表”中的數(shù)字規(guī)范為半角形式。同樣利用函數(shù)SUBSTITUTE。在E2單元格輸入公式=(SUBSTITUTE(C2，"。"，".")*1，其中SUBSTITUTE(C2，"。"，".")表示句號“?！鞭D(zhuǎn)化為點號“.”，“*1”表示轉(zhuǎn)化為數(shù)字。然后在E列復制公式。同樣進行選擇性粘貼。選擇E列數(shù)據(jù)進行復制，再選擇C列所有數(shù)據(jù)選擇性粘貼值和數(shù)字格式。刪除“成績表”中D列、E列。復制“成績表”中數(shù)據(jù)到“學生基本信息表”：最后一步就是復制“成績表”中的數(shù)據(jù)到“學生基本信息表”了，但是我們不能簡單地利用復制粘貼來實