Excel中進(jìn)行數(shù)據(jù)分析的幾個(gè)常用函數(shù)

摘要:Excel具有強(qiáng)大的數(shù)據(jù)分析和管理功能，很多問題通過函數(shù)可以方便的解決，但是對于沒有理工科背景的工作人員使用函數(shù)是有一定困難的，本文介紹了幾個(gè)EXCEL中常用的進(jìn)行數(shù)據(jù)分析的統(tǒng)計(jì)函數(shù)和數(shù)學(xué)，并介紹了函數(shù)的功能、語法及使用方法。

關(guān)鍵詞:函數(shù) 公式 數(shù)據(jù)分析 參數(shù) 單元格

Excel是辦公室工作中很常用的一款軟件，很多企業(yè)都用Excel進(jìn)行數(shù)據(jù)分析和管理。Excel易學(xué)易用，并在數(shù)據(jù)和計(jì)算方面具有強(qiáng)大的功能，這其中的很多功能是通過函數(shù)實(shí)現(xiàn)的，在這里介紹幾個(gè)進(jìn)行數(shù)據(jù)分析時(shí)經(jīng)常用到的數(shù)學(xué)和統(tǒng)計(jì)函數(shù)

1 函數(shù)概述

Excel中的函數(shù)其實(shí)是一些預(yù)定義的公式，用戶可以直接用這些公式對指定區(qū)域的數(shù)據(jù)進(jìn)行指定的運(yùn)算。例如使用SUM函數(shù)可以對指定的區(qū)域進(jìn)行求和運(yùn)算，MAX函數(shù)可以在指定區(qū)域內(nèi)找到其中的數(shù)據(jù)最大值。函數(shù)在使用時(shí)并不是只給出函數(shù)名就可以完成運(yùn)算，用戶要給出函數(shù)應(yīng)用的單元格區(qū)域、條件等參數(shù)才能使用函數(shù)得到計(jì)算結(jié)果。

函數(shù)的表達(dá)形式一般是:函數(shù)名(參數(shù)1，參數(shù)2……)，函數(shù)名是已經(jīng)定義好的在使用時(shí)不可改變，函數(shù)所需的參數(shù)寫在括號里，多個(gè)參數(shù)間用西文逗號分隔，多個(gè)參數(shù)的順序也是定義好的，在使用時(shí)要嚴(yán)格遵守，如果沒有參數(shù)括號里可以是空。參數(shù)可以是數(shù)字、文本、邏輯值(TRUE或FALSE)、數(shù)組、單元格引用等，參數(shù)也可以是常量、公式或其它函數(shù)。

函數(shù)在使用時(shí)和公式的輸入方法是一樣的，先輸入等于號再輸入函數(shù)。在實(shí)際應(yīng)用中則更多使用“插入函數(shù)”，在對話框選擇使用所需函數(shù)。在插入函數(shù)對話框中有關(guān)于函數(shù)的功能、格式和參數(shù)及運(yùn)算結(jié)果的提示，可以幫助用戶完成函數(shù)的輸入。觀察“插入函數(shù)”對話框的選擇函數(shù)列表，會發(fā)現(xiàn)在Excel函數(shù)中會有這樣一種情況，兩個(gè)函數(shù)的差別只在最后一個(gè)字母即有一個(gè)函數(shù)最后多一個(gè)字母“A”，這樣的函數(shù)功能是基本相同的，區(qū)別是在運(yùn)算時(shí)帶A的函數(shù)都會對文本數(shù)據(jù)和邏輯值進(jìn)行處理，而不帶A的函數(shù)將忽略文本、邏輯值和空單元格。

2 進(jìn)行數(shù)據(jù)分析常用的統(tǒng)計(jì)函數(shù)

2.1 求平均值函數(shù)AVERAGE和求修剪平均值函數(shù)TRIMMEAN AVERAGE的功能是計(jì)算參數(shù)列表中數(shù)值的平均值(算數(shù)平均值)。其語法形式為AVERAGE(number1，number2...)其中number1，number2...為要計(jì)算平均值的1～30個(gè)參數(shù)。這些參數(shù)可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。如果參數(shù)是數(shù)組或單元格引用，其中有文字、邏輯值或空單元格，則忽略其值，如果包含零值則計(jì)算在內(nèi)。

Excel中還有一個(gè)函數(shù)AVERAGEA，其函數(shù)名只比AVERAGE多一個(gè)A，兩個(gè)函數(shù)的格式和功能及限定條件是一樣的，其不同一處是AVERAGEA會將包含文本的數(shù)組、單元格及空單元格和邏輯值進(jìn)行處理，計(jì)算時(shí)將邏輯真(TRUE)按數(shù)值1計(jì)算，邏輯假(FALSE)及文本和空單元格按數(shù)值0計(jì)算。

TRIMMEAN函數(shù)的功能是先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點(diǎn)，然后再求平均值。語法形式為TRIMMEAN(array，percent)，其中Array為需要進(jìn)行篩選并求平均值的數(shù)組或數(shù)據(jù)區(qū)域。Percent為計(jì)算時(shí)所要除去的數(shù)據(jù)點(diǎn)的比例。比如，在計(jì)算選手平均分?jǐn)?shù)時(shí)，通常用去掉一個(gè)最高分，去掉一個(gè)最低分，再算選手的平均得分，這種情況就可以使用此函數(shù)來計(jì)算，如果評委給出20個(gè)分?jǐn)?shù)，我們將percent參數(shù)值設(shè)為0.1，在 20 個(gè)數(shù)據(jù)點(diǎn)的集合中，就要除去2個(gè)數(shù)據(jù)點(diǎn)(20×0.1)，頭部除去1個(gè)，尾部除去1個(gè)。

2.2 求最大值MAX和最小值MIN MAX和MIN是用來求解數(shù)據(jù)集的極值，即最大值和最小值的函數(shù)。語法形式為MAX(number1，number2...)和MIN(number1，number2...)，其中的參數(shù)為1到30個(gè)，可以是數(shù)值、數(shù)組或單元格引用。

2.3 求中位數(shù)MEDIAN和眾數(shù)MODE 中位數(shù)是指在一組數(shù)據(jù)中居于中間的數(shù)，也就是說，在這組數(shù)據(jù)中，有一半的數(shù)據(jù)比它大，有一半的數(shù)據(jù)比它小。眾數(shù)是指在一組數(shù)據(jù)中出現(xiàn)頻率最多的數(shù)。

MEDIAN函數(shù)返回給定集合的中位數(shù)。語法形式為MEDIAN(number1，number2...)其中的參數(shù)形式與上面的函數(shù)相同。MEDIAN在求解中位數(shù)時(shí)會先按從小到大將數(shù)據(jù)排序，再找到位置在中間的數(shù)并返回其值，如果參數(shù)集合中包含有偶數(shù)個(gè)數(shù)字，函數(shù)將返回位于中間的兩個(gè)數(shù)的平均值。

MODE函數(shù)用來求眾數(shù)。語法形式為MODE(number1，number2...)，此函數(shù)會返回指定的數(shù)據(jù)、數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)的次數(shù)最多的數(shù)值。

2.4 求單元格個(gè)數(shù)的統(tǒng)計(jì)函數(shù)COUNT和COUNTIF COUNT函數(shù)的功能是統(tǒng)計(jì)單元格的個(gè)數(shù)，函數(shù)的語法形式為COUNT(value1，value2...)，其中value1，value2...為包含或引用各種類型數(shù)據(jù)的參數(shù)(1～30個(gè))，但只有數(shù)字類型的數(shù)據(jù)才被計(jì)數(shù)。COUNT函數(shù)在計(jì)數(shù)時(shí)，將把數(shù)字、零值及日期計(jì)算進(jìn)去;但是錯(cuò)誤值或其他無法轉(zhuǎn)化成數(shù)字的文字則被忽略。如果要統(tǒng)計(jì)文本型數(shù)據(jù)就要使用COUNTA函數(shù)。

COUNTIF函數(shù)可以用來統(tǒng)計(jì)給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。比如統(tǒng)計(jì)職稱是工程師的人數(shù)，統(tǒng)計(jì)女職工的人數(shù)等。語法形式為COUNTIF(range，criteria)。其中Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria確定計(jì)數(shù)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為 “工程師”、“>2000”、60等。

2.5 求數(shù)據(jù)的頻率分布FREQUENCY FREQUENCY可以計(jì)算在給定的分?jǐn)?shù)范圍內(nèi)測驗(yàn)分?jǐn)?shù)的個(gè)數(shù)，也就是說此函數(shù)可以統(tǒng)計(jì)給定數(shù)據(jù)集中各分段范圍內(nèi)數(shù)據(jù)的個(gè)數(shù)。語法形式為FREQUENCY(data_array，bins_array)，此函數(shù)以一列垂直數(shù)組返回各個(gè)區(qū)域中數(shù)據(jù)的頻率分布，由于函數(shù)FREQUENCY返回一個(gè)數(shù)組，所以以數(shù)組公式的形式輸入。其中Data_array為要進(jìn)行數(shù)據(jù)分段統(tǒng)計(jì)的一數(shù)組或?qū)σ唤M數(shù)值的引用，Bins_array為一數(shù)組或?qū)?shù)組區(qū)域的引用，設(shè)定對data_array進(jìn)行分段頻率計(jì)算的分段點(diǎn)。如果Bins_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回data_array元素的總個(gè)數(shù)。此函數(shù)非常有用，例如可以計(jì)算不同工資段的人員分布，職工年齡分布，學(xué)生成績分布情況等。

此函數(shù)在使用時(shí)必須以數(shù)組公式的形式輸入，具體操作是:先選定統(tǒng)計(jì)結(jié)果的單元格區(qū)域，然后點(diǎn)擊“插入函數(shù)”圖標(biāo)按鈕，選擇FREQUENCY函數(shù)，在“插入函數(shù)”對話框中輸入要進(jìn)行統(tǒng)計(jì)的數(shù)據(jù)區(qū)域和分段參數(shù)，如果分段參數(shù)不是數(shù)據(jù)區(qū)域而是具體分段數(shù)值則應(yīng)當(dāng)用大括號將各分段數(shù)值括起來，其中的各分段數(shù)值用逗號分隔。最后，在插入公式對話框輸入完參數(shù)后，要按Ctrl+Shift同時(shí)再單擊“確定”。這樣就完成了數(shù)組公式的輸入。

例如，要對學(xué)生成績進(jìn)行頻率分布分析，數(shù)據(jù)區(qū)域在C3到C40，分段的條件是“0～59，60～69，70～79，80～89，90及以上”。如果分段參數(shù)59，69，79，89在E3到E6單元格，則輸入的公式是:{=FREQUENCY(C3:C40，E3:E6)};如果分段參數(shù)沒有輸入到數(shù)據(jù)區(qū)域，則公式輸入應(yīng)為:{=FREQUENCY(C3:C40，{59，69，79，89｝)}。注意，公式是用大括號括起來的，這是數(shù)組公式的表示形式，在插入函數(shù)對話框的參數(shù)輸入完畢，按下Ctrl+Shift同時(shí)再單擊“確定”大括號就會自動(dòng)添加到公式兩端。

2.6 用來排位的函數(shù)RANK和PERCENTRANK RANK函數(shù)的功能是返回指定數(shù)字在數(shù)據(jù)列表中的排位，數(shù)值的排位是與其他數(shù)值的相對大小比較后的排序位，如果數(shù)據(jù)已經(jīng)排過序了，則數(shù)值的排位就是它當(dāng)前的位置。語法形式為RANK(number，ref，order)，其中Number為需要找到排位的數(shù)字;Ref為一組數(shù)字的數(shù)組或引用，其中的非數(shù)值型數(shù)據(jù)被忽略。Order為一個(gè)數(shù)字，用來指明排位的方式，如果order為0或省略，將按降序排列來確定指定數(shù)據(jù)的排位，如果order不為零，則按升序排列的數(shù)據(jù)列表進(jìn)行排位。函數(shù)RANK對重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如，在比賽排名里，如果兩個(gè)選手并列第3次，則沒有第4名，下一位選手的排名為第5名。RANK函數(shù)對排名的處理方式與此相同。

PERCENTRANK函數(shù)可用于查看指定數(shù)據(jù)在數(shù)據(jù)集中所處的百分比排位。語法形式為PERCENTRANK(array，x，significance) 其中Array為要對指定數(shù)值所在技術(shù)排位的數(shù)字?jǐn)?shù)組或數(shù)字區(qū)域。X為需要得到其排位的指定數(shù)值。Significance為可選項(xiàng)，表示返回的百分?jǐn)?shù)值的有效位數(shù)，其值應(yīng)大于1，如果省略，函數(shù)PERCENTRANK保留3位小數(shù)。此函數(shù)的返回值為小數(shù)形式，如果需要百分比形式則需要在單元格式中進(jìn)行設(shè)置。

3 用于數(shù)據(jù)分析的數(shù)學(xué)函數(shù)

3.1 求和函數(shù)SUM和SUMIF SUM是求和函數(shù)，返回某一單元格區(qū)域中所有數(shù)字之和。語法形式為:SUM(number1，number2...)，函數(shù)使用時(shí)直接鍵入到參數(shù)表中的數(shù)字、邏輯值(TRUE為1，F(xiàn)ALSE為0)及數(shù)字的文本表達(dá)式將被計(jì)算，出現(xiàn)其它數(shù)據(jù)將提示出錯(cuò)。如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字將被計(jì)算，數(shù)組或引用中的空白單元格、邏輯值、文本或錯(cuò)誤值將被忽略，而不出現(xiàn)錯(cuò)誤提示。

SUMIF函數(shù)是對滿足指定條件的單元格求和。語法形式為SUMIF(range，criteria，sum_range)，其中Range為進(jìn)行條件判斷的單元格區(qū)域。Criteria為進(jìn)行求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。Sum_range為需要求和的單元格區(qū)域。也就是說，只有Range指定區(qū)域的單元格中數(shù)據(jù)滿足Criteria所指定條件時(shí)，與這些單元格相對應(yīng)的Sum_range區(qū)域中的單元格才進(jìn)行求和運(yùn)算。其中的Sum_range如果忽略，則求和區(qū)域?yàn)镽ange指定的區(qū)域。

3.2 分類匯總函數(shù)SUBTOTAL SUBTOTAL函數(shù)返回列表或數(shù)據(jù)庫中的分類匯總。在使用“數(shù)據(jù)”菜單中的“分類匯總”命令對數(shù)據(jù)進(jìn)行分類匯總之后，如果選定分類匯總結(jié)果的單元格，就會發(fā)現(xiàn)此處使用的就是SUBTOTAL函數(shù)，如果想修改匯總方式，就可以通過編輯SUBTOTAL函數(shù)進(jìn)行修改。

函數(shù)的語法形式為:SUBTOTAL(function_num，ref1，ref2，...)，其中的參數(shù)Function_num為1到11(運(yùn)算時(shí)包含隱藏值)或101到 111(運(yùn)算時(shí)忽略隱藏值)之間的數(shù)字，指定使用何種函數(shù)進(jìn)行計(jì)算，見表1。Ref1，ref2，...為要進(jìn)行計(jì)算的1到29個(gè)區(qū)域或引用。SUBTOTAL函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域。不適用于數(shù)據(jù)行或水平區(qū)域。

SUBTOTAL函數(shù)不僅僅在分類匯總時(shí)可以使用，在沒有分類匯總情況下也可以使用，在對篩選結(jié)果進(jìn)行統(tǒng)計(jì)時(shí)也可以使用。SUBTOTAL函數(shù)中當(dāng)function_num為從1到11的常數(shù)時(shí)，SUBTOTAL 函數(shù)將計(jì)算包括通過“隱藏”命令所隱藏的行中的值，當(dāng)function_num為從101到111的常數(shù)時(shí)，SUBTOTAL函數(shù)將忽略通過“隱藏”命令所隱藏的行中的值。但是對于“篩選”后被隱藏的數(shù)據(jù)行，不論function_num取何值，SUBTOTAL函數(shù)都將忽略任何不包括在篩選結(jié)果中的行。例如，對C2到C40求平均值(應(yīng)用AVERAGE函數(shù))，如果計(jì)算包括“隱藏”命令隱藏的行中的值，則function_num取值為1，函數(shù)表示為:SUBTOTAL(1，C2:C40)，如果計(jì)算不包括“隱藏”命令隱藏的行中的值，則function_num取值為101，函數(shù)表示為:SUBTOTAL(101，C2:C40)。

在EXCEL中有很多功能強(qiáng)大的函數(shù)，例如各種概念分布統(tǒng)計(jì)函數(shù)、各種數(shù)學(xué)運(yùn)算函數(shù)、各種財(cái)務(wù)函數(shù)等等。在工作中根據(jù)工作性質(zhì)的不同會用到不同的函數(shù)，我們在使用時(shí)可以通過查閱EXCEL幫助來學(xué)習(xí)函數(shù)的使用，通過EXCEL中的函數(shù)可以幫助我們輕松實(shí)現(xiàn)數(shù)據(jù)分析和管理。

參考文獻(xiàn):

[1]周國彬.Excel函數(shù)應(yīng)用之統(tǒng)計(jì)函數(shù)，百度文庫.

[2]Microsoft Excel幫助.