Excel函數(shù)寶典

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

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

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

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

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

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

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

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

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

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

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

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

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

14、等于 FALSE （二）OR函數(shù)OR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE。它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個(gè)為真即可為真。比如，上面的示例2，如果在B4單元格中的公式寫為=OR(B1:B3)則結(jié)果等于TRUE （三）NOT函數(shù)NOT函數(shù)用于對(duì)參數(shù)值求反。當(dāng)要確保一個(gè)值不等于某一特定值時(shí)，可以使用 NOT 函數(shù)。簡(jiǎn)言之，就是當(dāng)參數(shù)值為TRUE時(shí)，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ù)說(shuō)明IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應(yīng)用很廣泛，可以使用函數(shù) IF 對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。它的語(yǔ)法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。本參數(shù)可使用任何比較運(yùn)算符。Value_if_true顯示logical_test 為 TRUE 時(shí)返回的值，Value_if_true 也可以是其他公式。Value_if_false顯示 log

16、ical_test 為 FALSE 時(shí)返回的值。Value_if_false 也可以是其他公式。簡(jiǎn)言之，如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真的話，則執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個(gè)參數(shù)Value_if_false的結(jié)果。IF函數(shù)可以嵌套七層，用 value_if_false 及 value_if_true 參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。Excel 還提供了可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用 COUNTIF 工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用 SUMIF

17、工作表函數(shù)。（二）IF函數(shù)應(yīng)用1、 輸出帶有公式的空白表單 以圖中所示的人事?tīng)顩r分析表為例，由于各部門關(guān)于人員的組成情況的數(shù)據(jù)尚未填寫，在總計(jì)欄（以單元格G5為例）公式為：=SUM(C5:F5)我們看到計(jì)算為0的結(jié)果。如果這樣的表格打印出來(lái)就頁(yè)面的美觀來(lái)看顯示是不令人滿意的。是否有辦法去掉總計(jì)欄中的0呢？你可能會(huì)說(shuō)，不寫公式不就行了。當(dāng)然這是一個(gè)辦法，但是，如果我們利用了IF函數(shù)的話，也可以在寫公式的情況下，同樣不顯示這些0。如何實(shí)現(xiàn)呢？只需將總計(jì)欄中的公式（僅以單元格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é)果我們?cè)O(shè)定按照平均分判斷該學(xué)生成績(jī)是否合格的規(guī)則。如果各科平均分超過(guò)60分則認(rèn)為是合格的，否則記作不合格：=IF(B11>60,"合格","不合格")語(yǔ)法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個(gè)參數(shù)即在單元格

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

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

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

22、=COUNTIF($C$2:$C$13,A17)語(yǔ)法解釋為計(jì)算單元格A17(即銷售人員ANNIE)在"銷售人員"清單$C$2:$C$13的范圍內(nèi)出現(xiàn)的次數(shù)，這個(gè)出現(xiàn)的次數(shù)即可認(rèn)為是該銷售人員ANNIE的訂單數(shù)。（2） 訂單總額-用SUMIF匯總每個(gè)銷售人員的銷售額。=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的訂單總額。（

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

44、，請(qǐng)用相同的列標(biāo)包括多列。例如，上面示例的條件區(qū)域顯示介于10和16之間的高度。（6）將公式結(jié)果用作條件Excel中可以將公式（公式：?jiǎn)卧裰械囊幌盗兄怠卧褚?、名稱或運(yùn)算符的組合，可生成新的值。公式總是以等號(hào) (=) 開(kāi)始。）的計(jì)算結(jié)果作為條件使用。用公式創(chuàng)建條件時(shí)，不要將列標(biāo)志作為條件標(biāo)記使用，應(yīng)該將條件標(biāo)記置空，或者使用清單中非列標(biāo)志的標(biāo)記。例如，下面的條件區(qū)域顯示在列 C 中，其值大于單元格區(qū)域 C7:C10 平均值的行。=C7>AVERAGE($C$7:$C$10) 需要注意的是用作條件的公式必須使用相對(duì)引用來(lái)引用列標(biāo)志（例如，“高度”），或者引用第一個(gè)記錄的對(duì)應(yīng)字段。公式

45、中的所有其他引用都必須是絕對(duì)引用并且公式必須計(jì)算出結(jié)果 TRUE 或 FALSE。在本公式示例中，C7 引用了數(shù)據(jù)清單中第一個(gè)記錄（行 7）的字段（列 C）。 當(dāng)然也可以在公式中使用列標(biāo)志來(lái)代替相對(duì)的單元格引用或區(qū)域名稱。當(dāng) Microsoft Excel 在包含條件的單元格中顯示錯(cuò)誤值 #NAME? 或 #VALUE! 時(shí)，您可以忽略這些錯(cuò)誤，因?yàn)樗鼈儾挥绊懥斜淼暮Y選。 此外Microsoft Excel 在計(jì)算數(shù)據(jù)時(shí)不區(qū)分大小寫?？焖俎D(zhuǎn)換學(xué)生考試成績(jī)等級(jí)有的時(shí)候，會(huì)遇到要將學(xué)生的考試成績(jī)按實(shí)際考試分?jǐn)?shù)轉(zhuǎn)換成相應(yīng)成績(jī)等級(jí)的情況，如將考試成績(jī)?cè)?0分以上的成績(jī)轉(zhuǎn)換成“A+”形式，85-89分的

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

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

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

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

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

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

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

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

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

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

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

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

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

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