Excel數(shù)據(jù)分析(加載宏)

1、用Excel做數(shù)據(jù)分析回歸分析實例某溶液濃度正比對應(yīng)于色譜儀器中的峰面積,現(xiàn)欲建立不同濃度下對應(yīng)峰面積的標準曲線以供測試未知樣品的實際濃度。已知8組對應(yīng)數(shù)據(jù),建立標準曲線,并且對此曲線進行評價,給出殘差等分析數(shù)據(jù)。這是一個很典型的線性擬合問題,手工計算就是采用最小二乘法求出擬合直線的待定參數(shù),同時可以得出R的值,也就是相關(guān)系數(shù)的大小。在Excel中,可以采用先繪圖再添加趨勢線的方法完成前兩步的要求。選擇成對的數(shù)據(jù)列,將它們使用“X、Y散點圖”制成散點圖。 在數(shù)據(jù)點上單擊右鍵,選擇“添加趨勢線”-“線性”,并在選項標簽中要求給出公式和相關(guān)系數(shù)等,可以得到擬合的直線。 由圖中可知,擬合的直線是y=

2、15620x+6606.1,R2的值為0.9994。因為R2 >0.99,所以這是一個線性特征非常明顯的實驗?zāi)Ｐ?即說明擬合直線能夠以大于99.99%地解釋、涵蓋了實測數(shù)據(jù),具有很好的一般性,可以作為標準工作曲線用于其他未知濃度溶液的測量。為了進一步使用更多的指標來描述這一個模型,我們使用數(shù)據(jù)分析中的“回歸”工具來詳細分析這組數(shù)據(jù)。 在選項卡中顯然詳細多了,注意選擇X、Y對應(yīng)的數(shù)據(jù)列?！俺?shù)為零”就是指明該模型是嚴格的正比例模型,本例確實是這樣,因為在濃度為零時相應(yīng)峰面積肯定為零。先前得出的回歸方程雖然擬合程度相當高,但是在x=0時,仍然有對應(yīng)的數(shù)值,這顯然是一個可笑的結(jié)論。所以我們選擇

3、“常數(shù)為零”?！盎貧w”工具為我們提供了三張圖,分別是殘差圖、線性擬合圖和正態(tài)概率圖。重點來看殘差圖和線性擬合圖。 在線性擬合圖中可以看到,不但有根據(jù)要求生成的數(shù)據(jù)點,而且還有經(jīng)過擬和處理的預(yù)測數(shù)據(jù)點,擬合直線的參數(shù)會在數(shù)據(jù)表格中詳細顯示。本實例旨在提供更多信息以起到拋磚引玉的作用,由于涉及到過多的專業(yè)術(shù)語,請各位讀者根據(jù)實際,在具體使用中另行參考各項參數(shù),此不再對更多細節(jié)作進一步解釋。殘差圖是有關(guān)于世紀之與預(yù)測值之間差距的圖表,如果殘差圖中的散點在中州上下兩側(cè)零亂分布,那么擬合直線就是合理的,否則就需要重新處理。 更多的信息在生成的表格中,詳細的參數(shù)項目完全可以滿足回歸分析的各項要求。下圖提供

4、的是擬合直線的得回歸分析中方差、標準差等各項信息。 用Excel做數(shù)據(jù)分析移動平均某化工反應(yīng)過程,每隔2分鐘對系統(tǒng)測取一次壓力數(shù)據(jù)。由于反應(yīng)的特殊性,需要考察每8分鐘的壓力平均值,如果該壓力平均值高于15MPa,則認為自屬于該平均值計算范圍內(nèi)的第一個壓力數(shù)據(jù)出現(xiàn)時進入反應(yīng)階段,請使用Excel給出反應(yīng)階段時間的區(qū)間。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作移動平均就是對一系列變化的數(shù)據(jù)按照指定的數(shù)據(jù)數(shù)量依次求取平均,并以此作為數(shù)據(jù)變化的趨勢供分析人員參考。移動平均在生活中也不乏見,氣象意義上的四季界定就是移動平均最好的應(yīng)用。注:本功能需要使用Excel擴展功能,如果您的Excel

5、尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤支持下加載“分析數(shù)據(jù)庫”。加載成功后,可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項。操作步驟1.打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)要求單列,請確認數(shù)據(jù)的類型。本實例為壓力隨時間變化成對數(shù)據(jù),在數(shù)據(jù)分析時僅采用壓力數(shù)據(jù)列。 需要注意的是,因為平均值的求取需要一定的數(shù)據(jù)量,那么就要求原始數(shù)據(jù)量不少于求取平均值的個數(shù),在Excel中規(guī)定數(shù)據(jù)量不少于4。2.選擇“工具”-“數(shù)據(jù)分析”-“直方圖”后,出現(xiàn)屬性設(shè)置框,依次選擇:輸入?yún)^(qū)域:原始數(shù)據(jù)區(qū)域;如果有數(shù)據(jù)標簽可以選擇“標志位于第一行”;輸出區(qū)域:移動平均數(shù)值顯示區(qū)域;間隔:指定使用幾組

6、數(shù)據(jù)來得出平均值;圖表輸出;原始數(shù)據(jù)和移動平均數(shù)值會以圖表的形式來顯示,以供比較;標準誤差:實際數(shù)據(jù)與預(yù)測數(shù)據(jù)(移動平均數(shù)據(jù)的標準差,用以顯示預(yù)測與實際值的差距。數(shù)字越小則表明預(yù)測情況越好。 3.輸入完畢后,則可立即生成相應(yīng)的數(shù)據(jù)和圖表。 從生成的圖表上可以看出很多信息。根據(jù)要求,生成的移動平均數(shù)值在9:02時已經(jīng)達到了15.55MPa,也就是說,包含本次數(shù)據(jù)在內(nèi)的四個數(shù)據(jù)前就已經(jīng)達到了15MPa,那么說明在8分鐘前,也就是8:56時,系統(tǒng)進入反應(yīng)階段;采用同樣的分析方法可以知道,反映階段結(jié)束于9:10,反應(yīng)階段時間區(qū)間為8:56-9:10,共持續(xù)14分鐘。單擊其中一個單元格“D6”,可以看出

7、它是“B3-B6”的平均值,而單元格“E11”則是“SQRT(SUMXMY2(B6:B9,D6:D9/4”,它的意思是B6-B9,D6-D9對應(yīng)數(shù)據(jù)的差的平方的平均值再取平方根,也就是數(shù)組的標準差。用Excel做數(shù)據(jù)分析抽樣分析工具省教育廳派專家組進行某校檢查學(xué)生考試試卷,專家組擬對總體進行抽樣調(diào)查,對學(xué)校某班的全體同學(xué)隨機抽取25名作為調(diào)查樣本。為了保證結(jié)果的非人為性,采用Excel幫助專家組做出抽查的結(jié)果。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作抽樣分析工具以數(shù)據(jù)源區(qū)域為總體,從而為其創(chuàng)建一個樣本。當總體太大而不能進行處理或繪制時,可以選用具有代表性的樣本。如果確認數(shù)據(jù)源區(qū)域

8、中的數(shù)據(jù)是周期性的,還可以對一個周期中特定時間段中的數(shù)值進行采樣。也可以采用隨機抽樣,滿足用戶保證抽樣的代表性的要求。注:本功能需要使用Excel擴展功能,如果您的Excel尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤的支持下加載“數(shù)據(jù)分析庫”。加載成功后,可以在工具的下拉菜單中看到“數(shù)據(jù)分析”選項。操作步驟:1. 打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)無特殊要求,只要滿足行或列中為同一屬性數(shù)值即可。實例中顯示的是學(xué)生學(xué)號。 2. 選擇“工具”“數(shù)據(jù)分析”“抽樣”后,出現(xiàn)對話框,依次選擇: 輸入?yún)^(qū)域:把原始總體數(shù)據(jù)放在此區(qū)域中,數(shù)據(jù)類型不限,數(shù)值型或者文本型均可;抽樣方法:有間

9、隔和隨機兩種。間隔抽樣需要輸入周期間隔,輸入?yún)^(qū)域中位于間隔點處的數(shù)值以及此后每一個間隔點處的數(shù)值將被復(fù)制到輸出列中。當?shù)竭_輸入?yún)^(qū)域的末尾時,抽樣將停止。(在本例題中沒有采用;隨機抽樣是指直接輸入樣本數(shù),電腦自行進行抽樣,不用受間隔的規(guī)律限制;樣本數(shù):在此輸入需要在輸出列中顯示需要抽取總體中數(shù)據(jù)的個數(shù)。每個數(shù)值是從輸入?yún)^(qū)域中的隨機位置上抽取出來的,請注意:任何數(shù)值都可以被多次抽取!所以抽樣所得數(shù)據(jù)實際上會有可能小于所需數(shù)量。本文末尾給出了一種處理方法;輸出區(qū)域:在此輸入對輸出表左上角單元格的引用。所有數(shù)據(jù)均將寫在該單元格下方的單列里。如果選擇的是“周期”,則輸出表中數(shù)值的個數(shù)等于輸入?yún)^(qū)域中數(shù)值的

10、個數(shù)除以“間隔”。如果選擇的是“隨機”,則輸出表中數(shù)值的個數(shù)等于“樣本數(shù)”;3.然后單擊確定就可以顯示結(jié)果了(這是電腦自行隨機抽樣的結(jié)果。 原始數(shù)據(jù)文檔在這里下載>>操作結(jié)果文檔在這里下載>>需要說明的情況:由于隨機抽樣時總體中的每個數(shù)據(jù)都可以被多次抽取,所以在樣本中的數(shù)據(jù)一般都會有重復(fù)現(xiàn)象,解決此問題有待于程序的完善。可以使用“篩選”功能對所得數(shù)據(jù)進行篩選。選中樣本數(shù)據(jù)列,依次執(zhí)行“數(shù)據(jù)”-“篩選”-“高級篩選”,如下圖所示。 最后的樣本結(jié)果如下圖所示,請您根據(jù)經(jīng)驗適當調(diào)整在數(shù)據(jù)樣本選取時的數(shù)量設(shè)置,以使最終所得樣本數(shù)量不少于所需數(shù)量。 用Excel做數(shù)據(jù)分析描述統(tǒng)計

11、某班級期中考試進行后,需要統(tǒng)計成績的平均值、區(qū)間,以及給出班級內(nèi)部學(xué)生成績差異的量化標準,借此來作為解決班與班之間學(xué)生成績的參差不齊的依據(jù)。要求得到標準差等統(tǒng)計數(shù)值。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作樣本數(shù)據(jù)分布區(qū)間、標準差等都是描述樣本數(shù)據(jù)范圍及波動大小的統(tǒng)計量,統(tǒng)計標準差需要得到樣本均值,計算較為繁瑣。這些都是描述樣本數(shù)據(jù)的常用變量,使用Excel數(shù)據(jù)分析中的“描述統(tǒng)計”即可一次完成。注:本功能需要使用Excel擴展功能,如果您的Excel尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤中加載“分析數(shù)據(jù)庫”。加載成功后,可以在“工具”下拉菜單中看到“數(shù)據(jù)分析

12、”選項。操作步驟1.打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)無特殊要求,只要滿足行或列中為同一屬性數(shù)值即可。 2. 選擇“工具”-“數(shù)據(jù)分析”-“描述統(tǒng)計”后,出現(xiàn)屬性設(shè)置框,依次選擇:輸入?yún)^(qū)域:原始數(shù)據(jù)區(qū)域,可以選中多個行或列,注意選擇相應(yīng)的分組方式;如果數(shù)據(jù)有標志,注意勾選“標志位于第一行”;如果輸入?yún)^(qū)域沒有標志項,該復(fù)選框?qū)⒈磺宄?Excel 將在輸出表中生成適宜的數(shù)據(jù)標志;輸出區(qū)域可以選擇本表、新工作表或是新工作簿;匯總統(tǒng)計:包括有平均值、標準誤差(相對于平均值、中值、眾數(shù)、標準偏差、方差、峰值、偏斜度、極差、最小值、最大值、總和、總個數(shù)、最大值、最小值和置信度等相關(guān)項目。其中:中值:排

13、序后位于中間的數(shù)據(jù)的值;眾數(shù):出現(xiàn)次數(shù)最多的值;峰值:衡量數(shù)據(jù)分布起伏變化的指標,以正態(tài)分布為基準,比其平緩時值為正,反之則為負;偏斜度:衡量數(shù)據(jù)峰值偏移的指數(shù),根據(jù)峰值在均值左側(cè)或者右側(cè)分別為正值或負值;極差:最大值與最小值的差。第K大(小值:輸出表的某一行中包含每個數(shù)據(jù)區(qū)域中的第k 個最大(小值。平均數(shù)置信度:數(shù)值95% 可用來計算在顯著性水平為5% 時的平均值置信度。 結(jié)果示例如下(本實例演示了雙列數(shù)據(jù)的描述統(tǒng)計結(jié)果:成績學(xué)習時間平均78.64285714 平均62.91428571標準誤差 2.408241878 標準誤差 1.926593502中位數(shù)85 中位數(shù)68眾數(shù)98 眾數(shù)78

14、.4標準差18.02163202 標準差14.41730562方差324.7792208 方差207.8587013峰度 1.464424408 峰度 1.464424408偏度-1.130551511 偏度-1.13055151區(qū)域85 區(qū)域68最小值15 最小值12最大值100 最大值80求和4404 求和3523.2觀測數(shù)56 觀測數(shù)56最大(1 100 最大(1 80最小(1 15 最小(1 12置信度(95.0% 4.826224539 置信度(95.0% 3.860979631用Excel做數(shù)據(jù)分析相關(guān)系數(shù)與協(xié)方差化學(xué)合成實驗中經(jīng)常需要考察壓力隨溫度的變化情況。某次實驗在兩個不同的

15、反應(yīng)器中進行同一條件下實驗得到兩組溫度與壓力相關(guān)數(shù)據(jù),試分析它們與溫度的關(guān)聯(lián)關(guān)系,并對在不同反應(yīng)器內(nèi)進行同一條件下反應(yīng)的可靠性給出依據(jù)。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作相關(guān)系數(shù)是描述兩個測量值變量之間的離散程度的指標。用于判斷兩個測量值變量的變化是否相關(guān),即,一個變量的較大值是否與另一個變量的較大值相關(guān)聯(lián)(正相關(guān);或者一個變量的較小值是否與另一個變量的較大值相關(guān)聯(lián)(負相關(guān);還是兩個變量中的值互不關(guān)聯(lián)(相關(guān)系數(shù)近似于零。設(shè)(X,Y為二元隨機變量,那么: 為隨機變量X與Y的相關(guān)系數(shù)。p是度量隨機變量X與Y之間線性相關(guān)密切程度的數(shù)字特征。注:本功能需要使用Excel擴展功能,如

16、果您的Excel尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤中加載“分析數(shù)據(jù)庫”。加載成功后,可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項。操作步驟1. 打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)需要滿足兩組或兩組以上的數(shù)據(jù),結(jié)果將給出其中任意兩項的相關(guān)系數(shù)。 2. 選擇“工具”-“數(shù)據(jù)分析”-“描述統(tǒng)計”后,出現(xiàn)屬性設(shè)置框,依次選擇:輸入?yún)^(qū)域:選擇數(shù)據(jù)區(qū)域,注意需要滿足至少兩組數(shù)據(jù)。如果有數(shù)據(jù)標志,注意同時勾選下方“標志位于第一行”;分組方式:指示輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列考慮,請根據(jù)原數(shù)據(jù)格式選擇;輸出區(qū)域可以選擇本表、新工作表組或是新工作簿; 3.點擊“確定”即可看到生成

17、的報表?？梢钥吹?在相應(yīng)區(qū)域生成了一個3×3的矩陣,數(shù)據(jù)項目的交叉處就是其相關(guān)系數(shù)。顯然,數(shù)據(jù)與本身是完全相關(guān)的,相關(guān)系數(shù)在對角線上顯示為1;兩組數(shù)據(jù)間在矩陣上有兩個位置,它們是相同的,故右上側(cè)重復(fù)部分不顯示數(shù)據(jù)。左下側(cè)相應(yīng)位置分別是溫度與壓力A、B和兩組壓力數(shù)據(jù)間的相關(guān)系數(shù)。 協(xié)方差的統(tǒng)計與相關(guān)系數(shù)的活的方法相似,統(tǒng)計結(jié)果同樣返回一個輸出表和一個矩陣,分別表示每對測量值變量之間的相關(guān)系數(shù)和協(xié)方差。不同之處在于相關(guān)系數(shù)的取值在-1 和+1 之間,而協(xié)方差沒有限定的取值范圍。相關(guān)系數(shù)和協(xié)方差都是描述兩個變量離散程度的指標?；瘜W(xué)合成實驗中經(jīng)常需要考察壓力隨溫度的變化情況。某次實驗在兩個不

18、同的反應(yīng)器中進行同一條件下實驗得到兩組溫度與壓力相關(guān)數(shù)據(jù),試分析它們與溫度的關(guān)聯(lián)關(guān)系,并對在不同反應(yīng)器內(nèi)進行同一條件下反應(yīng)的可靠性給出依據(jù)。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作相關(guān)系數(shù)是描述兩個測量值變量之間的離散程度的指標。用于判斷兩個測量值變量的變化是否相關(guān),即,一個變量的較大值是否與另一個變量的較大值相關(guān)聯(lián)(正相關(guān);或者一個變量的較小值是否與另一個變量的較大值相關(guān)聯(lián)(負相關(guān);還是兩個變量中的值互不關(guān)聯(lián)(相關(guān)系數(shù)近似于零。設(shè)(X,Y為二元隨機變量,那么: 為隨機變量X與Y的相關(guān)系數(shù)。p是度量隨機變量X與Y之間線性相關(guān)密切程度的數(shù)字特征。注:本功能需要使用Excel擴展功能

19、,如果您的Excel尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤中加載“分析數(shù)據(jù)庫”。加載成功后,可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項。操作步驟1. 打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)需要滿足兩組或兩組以上的數(shù)據(jù),結(jié)果將給出其中任意兩項的相關(guān)系數(shù)。 2. 選擇“工具”-“數(shù)據(jù)分析”-“描述統(tǒng)計”后,出現(xiàn)屬性設(shè)置框,依次選擇:輸入?yún)^(qū)域:選擇數(shù)據(jù)區(qū)域,注意需要滿足至少兩組數(shù)據(jù)。如果有數(shù)據(jù)標志,注意同時勾選下方“標志位于第一行”;分組方式:指示輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列考慮,請根據(jù)原數(shù)據(jù)格式選擇;輸出區(qū)域可以選擇本表、新工作表組或是新工作簿; 3.點擊“確定”即可看到

20、生成的報表?？梢钥吹?在相應(yīng)區(qū)域生成了一個3×3的矩陣,數(shù)據(jù)項目的交叉處就是其相關(guān)系數(shù)。顯然,數(shù)據(jù)與本身是完全相關(guān)的,相關(guān)系數(shù)在對角線上顯示為1;兩組數(shù)據(jù)間在矩陣上有兩個位置,它們是相同的,故右上側(cè)重復(fù)部分不顯示數(shù)據(jù)。左下側(cè)相應(yīng)位置分別是溫度與壓力A、B和兩組壓力數(shù)據(jù)間的相關(guān)系數(shù)。 內(nèi)的相同條件下反應(yīng)一致性很好,可以忽略因為更換反應(yīng)器造成的系統(tǒng)誤差。協(xié)方差的統(tǒng)計與相關(guān)系數(shù)的活的方法相似,統(tǒng)計結(jié)果同樣返回一個輸出表和一個矩陣,分別表示每對測量值變量之間的相關(guān)系數(shù)和協(xié)方差。不同之處在于相關(guān)系數(shù)的取值在-1 和+1 之間,而協(xié)方差沒有限定的取值范圍。相關(guān)系數(shù)和協(xié)方差都是描述兩個變量離散程度

21、的指標。用Excel做數(shù)據(jù)分析直方圖使用Excel自帶的數(shù)據(jù)分析功能可以完成很多專業(yè)軟件才有的數(shù)據(jù)統(tǒng)計、分析,這其中包括:直方圖、相關(guān)系數(shù)、協(xié)方差、各種概率分布、抽樣與動態(tài)模擬、總體均值判斷,均值推斷、線性、非線性回歸、多元回歸分析、時間序列等內(nèi)容。下面將對以上功能逐一作使用介紹,方便各位普通讀者和相關(guān)專業(yè)人員參考使用。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作注:本功能需要使用Excel擴展功能,如果您的Excel尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤中加載“分析數(shù)據(jù)庫”。加載成功后,可以在“工具”下拉菜單中看到“數(shù)據(jù)分析”選項。 實例1某班級期中考試進行后

22、,需要統(tǒng)計各分數(shù)段人數(shù),并給出頻數(shù)分布和累計頻數(shù)表的直方圖以供分析。以往手工分析的步驟是先將各分數(shù)段的人數(shù)分別統(tǒng)計出來制成一張新的表格,再以此表格為基礎(chǔ)建立數(shù)據(jù)統(tǒng)計直方圖。使用Excel中的“數(shù)據(jù)分析”功能可以直接完成此任務(wù)。操作步驟1.打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)要求單列,確認數(shù)據(jù)的范圍。本實例為化學(xué)成績,故數(shù)據(jù)范圍確定為0-100。2.在右側(cè)輸入數(shù)據(jù)接受序列。所謂“數(shù)據(jù)接受序列”,就是分段統(tǒng)計的數(shù)據(jù)間隔,該區(qū)域包含一組可選的用來定義接收區(qū)域的邊界值。這些值應(yīng)當按升序排列。在本實例中,就是以多少分數(shù)段作為統(tǒng)計的單元。可采用拖動的方法生成,也可以按照需要自行設(shè)置。本實例采用10分一個

23、分數(shù)統(tǒng)計單元。 3.選擇“工具”-“數(shù)據(jù)分析”-“直方圖”后,出現(xiàn)屬性設(shè)置框,依次選擇:輸入?yún)^(qū)域:原始數(shù)據(jù)區(qū)域;接受區(qū)域:數(shù)據(jù)接受序列;如果選擇“輸出區(qū)域”,則新對象直接插入當前表格中;選中“柏拉圖”,此復(fù)選框可在輸出表中按降序來顯示數(shù)據(jù);若選擇“累計百分率”,則會在直方圖上疊加累計頻率曲線; 4.輸入完畢后,則可立即生成相應(yīng)的直方圖,這張圖還需要比較大的調(diào)整。 主要是:橫縱坐標的標題、柱型圖的間隔以及各種數(shù)據(jù)的字體、字號等等。為了達到柱型圖之間無縫的緊密排列,需要將“數(shù)據(jù)系列格式”中的“選項”中“分類間距”調(diào)整為“0”。其余細節(jié),請雙擊要調(diào)整的對象按照常規(guī)方法進行調(diào)整,這里不再贅述。調(diào)整后的

24、直方圖參考如下 用Excel做數(shù)據(jù)分析排位與百分比排位某班級期中考試進行后,按照要求僅公布成績,但學(xué)生及家長要求知道排名。故欲公布成績排名,學(xué)生可以通過成績查詢到自己的排名,并同時得到該成績位于班級百分比排名(即該同學(xué)是排名位于前“X%”的學(xué)生。點這里看專題:用Excel完成專業(yè)化數(shù)據(jù)統(tǒng)計、分析工作排序操作是Excel的基本操作,Excel“數(shù)據(jù)分析”中的“排位與百分比排位”可以使這個工作簡化,直接輸出報表。注:本功能需要使用Excel擴展功能,如果您的Excel尚未安裝數(shù)據(jù)分析,請依次選擇“工具”-“加載宏”,在安裝光盤中加載“分析數(shù)據(jù)庫”。加載成功后,可以在“工具”下拉菜單中看到“數(shù)據(jù)分析

25、”選項。操作步驟1. 打開原始數(shù)據(jù)表格,制作本實例的原始數(shù)據(jù)無特殊要求,只要滿足行或列中為同一屬性數(shù)值即可。2. 選擇“工具”-“數(shù)據(jù)分析”-“描述統(tǒng)計”后,出現(xiàn)屬性設(shè)置框,依次選擇;輸入?yún)^(qū)域:選擇數(shù)據(jù)區(qū)域,如果有數(shù)據(jù)標志,注意同時勾選下方“標志位于第一行”;分組方式:指示輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列考慮,請根據(jù)原數(shù)據(jù)格式選擇;輸出區(qū)域可以選擇本表、新工作表組或是新工作簿。 3.點擊“確定”即可看到生成的報表?？梢钥吹?此時生成一個四列的新表格,其中“點”是指排序后原數(shù)據(jù)的序數(shù),在本實例中對應(yīng)與學(xué)號,這也是很實用的一個序列;“成績”即為排序后的數(shù)據(jù)系列;“排位”采取重復(fù)數(shù)據(jù)占用同一位置的統(tǒng)計

26、方法;“百分比”是按照降序排列的,為了得到真正的“百分比排位”,還需要稍微作一下調(diào)整。 4.在“百分比”列的下一列輸入“百分排名”,在第一個單元格中輸入公式“=1-G3(對應(yīng)于百分排名”,回車。選中該單元格,向下拖動直至填充完畢。這樣就達到了顯示百分比排名的目的。完成的報表實例如下圖所示。 Excel統(tǒng)計某電話號碼有多少人打過派出所的一個朋友在調(diào)查一起案件的時候,遇到了一個如題的問題,請我?guī)兔ΑＳ捎谝Ｃ芩荒芴峁┰紨?shù)據(jù),只是給我談了一下要求。目前他們通過一個嫌疑犯的通話記錄,從通話記錄中又列入了n個新嫌疑犯,而且也得到每個嫌疑犯的通話記錄?，F(xiàn)在就要將這些通話記錄進行統(tǒng)計,即同一個電話號碼,

27、每個嫌疑犯打了多少次,有多少個嫌疑犯同時打過同一個號碼。根據(jù)上述總結(jié),Excel表如下:圖一:原始數(shù)據(jù)表圖二:統(tǒng)計結(jié)果表上圖說明:圖一:用戶一、用戶二、用戶三、用戶四正面的數(shù)字為模擬的電話號碼;方向是指主叫還是被叫,沒有什么意義。圖二:用戶正面的數(shù)字是該電話所使用的次數(shù),如果一個電話只被某一用戶打過,這樣就不統(tǒng)計,換句話說就是統(tǒng)計結(jié)果表中的電話號碼至少被兩個以上的用戶打過。解決的思路:此統(tǒng)計無法使用函數(shù)、數(shù)據(jù)透視表等普通的方法來解決。我采用了VBA編程來實現(xiàn)的統(tǒng)計。首先將所有用戶的電話(不重復(fù),重復(fù)的只取一次,提取出來存放到統(tǒng)計結(jié)果表中。這樣結(jié)果表中的電話是唯一的。通過結(jié)果表的電話號碼為基礎(chǔ),

28、統(tǒng)計每個用戶使用該號碼的次數(shù)并將統(tǒng)計的結(jié)果存放到結(jié)果表該用戶下。刪除同一個電話號碼被兩個以下用戶使用的行。解決的方法:因為用戶的數(shù)量是未知的,但從第2列開始是已經(jīng)的,這樣我們就可以通過循環(huán)來進行統(tǒng)計。循環(huán)的條件通過第1行從第2列開始,單元格不空。每個用戶的電話號碼循環(huán)與類似具體的程序源代碼如下:Private Sub CommandButton1_Click(Sheets(2.Rows(2 & ":" & 65536 = ""Sheets(2.Columns("B:IV" = ""Dim Ls, i

29、, j, Isa, k, yhsIsa = Falsei = 2If Sheets(1.Cells(1, 2 = "" ThenMsgBox "沒有用戶,無法統(tǒng)計!", vbOKOnly + vbCritical, "錯誤提示"Exit SubElseDo While TrueIf Sheets(1.Cells(1, i <> "" ThenSheets(2.Cells(1, i = Sheets(1.Cells(1, ii = i + 1ElseExit DoEnd IfLoopyhs = i - 1

30、End IfLs = 2Do While Sheets(1.Cells(1, Ls <> ""i = 2Do While Sheets(1.Cells(i, Ls <> ""If Sheets(2.Cells(2, 1 = "" ThenSheets(2.Cells(2, 1 = Sheets(1.Cells(i, LsElsej = 2: Isa = FalseDo While Sheets(2.Cells(j, 1 <> ""If Sheets(2.Cells(j, 1 =

31、Sheets(1.Cells(i, Ls Then Isa = True: Exit Doj = j + 1LoopIf Not Isa Then Sheets(2.Cells(j, 1 = Sheets(1.Cells(i, LsEnd Ifi = i + 1LoopLs = Ls + 1LoopLs = 2Do While Sheets(2.Cells(1, Ls <> ""i = 2Do While Sheets(2.Cells(i, 1 <> ""j = 2: k = 0Do While Sheets(1.Cells(j,

32、 Ls <> ""If Sheets(2.Cells(i, 1 = Sheets(1.Cells(j, Ls Then k = k + 1j = j + 1LoopIf k <> 0 Then Sheets(2.Cells(i, Ls = ki = i + 1LoopLs = Ls + 1Loop'=' 刪除非同一電話多個用戶使用的行'=i = 2Do While Sheets(2.Cells(i, 1 <> ""j = 2: k = 0Do While j <= yhsIf Sheets

33、(2.Cells(i, j <> "" Then k = k + 1j = j + 1LoopIf CInt(k < 2 ThenSheets(2.Rows(i.Delete Shift:=xlUp '刪除i行Elsei = i + 1End IfLoop'=MsgBox "統(tǒng)計完畢!", vbOKOnly + vbInformation, "系統(tǒng)提示"Sheets(2.SelectEnd Sub Excel函數(shù)即是預(yù)先定義,執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。以常用的求和函數(shù)SUM為例,它的語法

34、是“SUM(number1,number2,.”。其中“SUM”稱為函數(shù)名稱,一個函數(shù)只有一個名稱,它決定了函數(shù)的功能和用途;函數(shù)名稱后緊跟左括號;接著是用逗號分隔的參數(shù);最后用一個右括號表示函數(shù)結(jié)束。查看函數(shù)概念的更多內(nèi)容>>Excel公式與函數(shù)既有區(qū)別又互相聯(lián)系。如果說前者是Excel預(yù)先定義好的特殊公式,后者就是由用戶自行設(shè)計對工作表進行計算和處理的計算式。查看函數(shù)公式的更多內(nèi)容>> 函數(shù)右邊括號中的部分稱為參數(shù)。假如一個函數(shù)可以使用多個參數(shù),那么參數(shù)與參數(shù)之間使用半角逗號進行分隔。參數(shù)可以是常量(數(shù)字和文本、邏輯值(例如TRUE或FALSE、數(shù)組、錯誤值(例如#

35、N/A或單元格引用(例如E1:H1,甚至可以是另一個或幾個函數(shù)等。參數(shù)的類型和位置必須滿足函數(shù)語法的要求,否則將返回錯誤信息。查看函數(shù)參數(shù)的詳細內(nèi)容>> 輸入Excel函數(shù),通常可以用兩種方法實現(xiàn),即“插入函數(shù)”對話框和編輯欄輸入。用“插入函數(shù)”對話框更形象直觀,只用鼠標即可完成輸入;如果要套用某個現(xiàn)成公式,或者輸入一些嵌套關(guān)系復(fù)雜的公式,利用編輯欄輸入更加快捷。查看函數(shù)參數(shù)輸入方法的詳細內(nèi)容>> 函數(shù)名功能ABS求出參數(shù)的絕對值。AND“與”運算,返回邏輯值,僅當有參數(shù)的結(jié)果均為邏輯“真(TRUE”時返回邏輯“真(TRUE”,反之返回邏輯“假(FALSE”。AVERA

36、GE求出所有參數(shù)的算術(shù)平均值。COLUMN顯示所引用單元格的列標號值。CONCATENATE將多個字符文本或單元格中的數(shù)據(jù)連接在一起,顯示在一個單元格中。COUNTIF統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目。DATE給出指定數(shù)值的日期。DATEDIF計算返回兩個日期參數(shù)的差值。DAY計算參數(shù)中指定日期或引用單元格中的日期天數(shù)。DCOUNT返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。FREQUENCY以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。IF根據(jù)對指定條件的邏輯判斷的真假結(jié)果,返回相對應(yīng)條件觸發(fā)的計算結(jié)果。INDEX返回列表或數(shù)組中的元素值,此元素由行序號和列序號的索

37、引值進行確定。INT將數(shù)值向下取整為最接近的整數(shù)。ISERROR用于測試函數(shù)式返回的數(shù)值是否有錯。如果有錯,該函數(shù)返回TRUE,反之返回FALSE。LEFT從一個文本字符串的第一個字符開始,截取指定數(shù)目的字符。LEN統(tǒng)計文本字符串中字符數(shù)目。MATCH返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。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返回列表或數(shù)據(jù)庫中的分類匯總。SUM求出一組數(shù)值的和。SUMIF計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。TEXT根據(jù)指定的數(shù)值格式將相應(yīng)的數(shù)字轉(zhuǎn)換為文本形式TODAY給出系統(tǒng)日期VALUE