Excel數(shù)據(jù)處理常用方法分類解析

劉敬偉

摘要：Excel中，我們經(jīng)常要用到有關(guān)數(shù)據(jù)處理的函數(shù)和公式，用以處理紛繁復(fù)雜的數(shù)據(jù)信息。數(shù)據(jù)處理的方法包括數(shù)據(jù)篩選、數(shù)據(jù)統(tǒng)計(jì)和數(shù)據(jù)計(jì)算等等。這些數(shù)據(jù)處理的方法很實(shí)用，為我們處理龐大的數(shù)據(jù)信息帶來(lái)了很大的便利。對(duì)這些方法進(jìn)行歸類和解析，更有助于初學(xué)者更快更迅速地掌握和理解這些數(shù)據(jù)處理的方法。

關(guān)鍵詞：?jiǎn)卧瘢?數(shù)據(jù)；條件

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1009-3044（2014）02-0426-02

Excel中，根據(jù)數(shù)據(jù)處理的目的和要求，可以把數(shù)據(jù)處理的方法分為如下幾類：

1 數(shù)據(jù)篩選

1.1 使用“自動(dòng)篩選”命令篩出符合條件的數(shù)據(jù)

選擇命令：數(shù)據(jù)—篩選—自動(dòng)篩選，在要進(jìn)行數(shù)據(jù)篩選的列上單擊黑色三角，選擇一種條件，或者自定義篩選的條件，即可按條件進(jìn)行數(shù)據(jù)篩選。若不想破壞原有的數(shù)據(jù)表格，可先復(fù)制一個(gè)數(shù)據(jù)表的副本，在副本上進(jìn)行操作即可保留原數(shù)據(jù)表。

1.2 使用“高級(jí)篩選”命令進(jìn)行數(shù)據(jù)的復(fù)雜篩選

在數(shù)據(jù)表之外的一處單元格如H3中，輸入要進(jìn)行篩選的數(shù)據(jù)所在的字段名稱，緊靠這個(gè)單元格下方H4中，輸入篩選的條件。選擇命令：數(shù)據(jù)—篩選—高級(jí)篩選，勾選“將篩選結(jié)果復(fù)制到其他位置”選項(xiàng)，設(shè)定“列表區(qū)域”為全部數(shù)據(jù)表，設(shè)定“條件區(qū)域”為“H3：H4”，設(shè)定“復(fù)制到”位置為H5單元格。按確定之后，按照指定條件篩選出來(lái)的數(shù)據(jù)項(xiàng)就會(huì)顯示在H5單元格的右下方。如果有多個(gè)篩選條件并存，則篩選條件放在同一行上為“且”的關(guān)系，放在錯(cuò)開的行上為“或者”的關(guān)系。

1.3 使用“條件格式”命令為符合條件的單元格作標(biāo)記

例：成績(jī)<60的單元格顯示紅色。

選中成績(jī)表所有單元格，點(diǎn)“格式”—“條件格式”，條件設(shè)為：

單元格數(shù)值，小于，60。

選格式—圖案，點(diǎn)擊紅色后點(diǎn)“確定”。

2 數(shù)據(jù)計(jì)算

2.1 對(duì)一列數(shù)據(jù)求和

在存放結(jié)果的單元格中輸入公式：=SUM（開始格：結(jié)束格）

對(duì)開始格到結(jié)束格這一區(qū)域數(shù)值進(jìn)行求和；也可以使用從開始格拖拽到結(jié)束格的方式輸入求和的范圍。

2.2 對(duì)一列數(shù)據(jù)求平均數(shù)

在存放結(jié)果的單元格中輸入公式：=AVERAGE（開始格：結(jié)束格）

對(duì)開始格到結(jié)束格這一區(qū)域數(shù)值求平均數(shù)；也可以使用從開始格拖拽到結(jié)束格的方式輸入求和的范圍。若有幾列數(shù)據(jù)求平均數(shù)，可以使用拖拽復(fù)制的方式實(shí)現(xiàn)。

2.3 對(duì)一列數(shù)據(jù)標(biāo)志等級(jí)

在存放標(biāo)志結(jié)果的第一個(gè)單元格中輸入公式：=IF（格>=90，"優(yōu)"，IF（格>=80，"良"，IF（格>=60，"及格"， "不及格"）））

最后使用拖拽復(fù)制的方式實(shí)現(xiàn)對(duì)這一列數(shù)據(jù)標(biāo)志等級(jí)。

2.4 已知每位學(xué)生的“平時(shí)”、“實(shí)踐”、“期末”三項(xiàng)成績(jī)，計(jì)算學(xué)期總成績(jī)

在存放結(jié)果的單元格中輸入公式：=格1*0.3+格2*0.4+格3*0.3

假設(shè)格1列、格2列和格3列分別存放著學(xué)生的“平時(shí)”、“實(shí)踐”、“期末”三項(xiàng)成績(jī)。最后使用拖拽復(fù)制的方式實(shí)現(xiàn)對(duì)所有學(xué)生求學(xué)期總成績(jī)。

2.5 求最高分

在存放結(jié)果的單元格中輸入公式：=MAX（開始格：結(jié)束格）

則在該單元格中顯示從開始格到結(jié)束格中的最高分?jǐn)?shù)。

2.6 求最低分

在存放結(jié)果的單元格中輸入公式：=MIN（開始格：結(jié)束格）

則在該單元格中顯示從開始格到結(jié)束格中的最低分?jǐn)?shù)。

2.7 對(duì)某一列數(shù)據(jù)按條件求和

在存放結(jié)果的單元格中輸入公式： =SUMIF（性別列開始格：性別列結(jié)束格，"男"，課時(shí)列開始格：課時(shí)列結(jié)束格）

假設(shè)性別列存放老師的性別，課時(shí)列存放老師的課時(shí)數(shù)，則此函數(shù)返回的結(jié)果為全部男老師的課時(shí)總數(shù)。

2.8 根據(jù)出生年月來(lái)計(jì)算年齡公式

在存放結(jié)果的單元格中輸入公式：=TRUNC（（DAYS360（出生日期格，NOW（），F(xiàn)ALSE））/360，0）

在存放結(jié)果的單元格中返回值即為年齡。最后使用拖拽復(fù)制的方式實(shí)現(xiàn)多個(gè)計(jì)算。

2.9 根據(jù)18位身份證號(hào)碼自動(dòng)判斷性別

在存放結(jié)果的單元格中輸入公式：= IF（MOD（MID（身份證號(hào)碼格，17，1），2）=1，"男”， "女”）

在存放結(jié)果的單元格中返回值即為性別。最后使用拖拽復(fù)制的方式實(shí)現(xiàn)多個(gè)判斷。

2.10 判斷單元格里是否包含指定文本

在存放結(jié)果的單元格中輸入公式：=IF（COUNTIF（目標(biāo)格，"張"&"*"）=1，"是"，"否"）

假定目標(biāo)格中存放的是姓名，則在存放結(jié)果的單元格中返回值即為判斷結(jié)果，即是否姓張。最后使用拖拽復(fù)制的方式實(shí)現(xiàn)多個(gè)判斷。

3 數(shù)據(jù)統(tǒng)計(jì)

3.1 統(tǒng)計(jì)成績(jī)表中各分?jǐn)?shù)段人數(shù)

假設(shè)A1：A50存放的是學(xué)生成績(jī)，則

1） 求A1到A50區(qū)域中成績(jī)?yōu)?00分的人數(shù)，在存放結(jié)果的單元格中輸入公式為： =COUNTIF（A1：A50，"100"）；假設(shè)把結(jié)果存放于A52單元格。

2） 求A1到A50區(qū)域中成績(jī)?yōu)?0～99.5分的人數(shù)，在存放結(jié)果的單元格中輸入公式為：=COUNTIF（A1：A50，">=90"）-A52；假設(shè)把結(jié)果存放于A53單元格；

3） 求A1到A50區(qū)域中成績(jī)?yōu)?0～89.5分的人數(shù)，在存放結(jié)果的單元格中輸入公式為：=COUNTIF（A1：A50，">=80"）-SUM（A52：A53）；假設(shè)把結(jié)果存放于A54單元格；

3.2 統(tǒng)計(jì)成績(jī)表中男、女生人數(shù)

在存放結(jié)果的單元格中輸入公式為：COUNTIF（開始格：結(jié)束格，"男"），則在存放結(jié)果的單元格中返回性別為男性的人數(shù)。

3.3 統(tǒng)計(jì)成績(jī)表中的優(yōu)秀率

在存放結(jié)果的單元格中輸入公式為：=SUM（A52：A54）/總?cè)藬?shù)*100。

在存放結(jié)果的單元格中返回值即為優(yōu)秀率，即80分以上學(xué)生所占的比例。

3.4 統(tǒng)計(jì)成績(jī)表中的及格率

在存放結(jié)果的單元格中輸入公式為：=SUM（A52：A56）/總?cè)藬?shù)*100。假設(shè)A55和A56單元格分別存放的是70分以上和60分以上的學(xué)生人數(shù)。

則存放結(jié)果的單元格中返回值即為及格率，即60分以上學(xué)生所占的比例。

3.5 統(tǒng)計(jì)成績(jī)表中的學(xué)生成績(jī)上下浮動(dòng)情況，即求標(biāo)準(zhǔn)差

在存放結(jié)果的單元格中輸入公式為：=STDEV（A1：A50）

則存放結(jié)果的單元格中返回值即表示成績(jī)波動(dòng)情況，數(shù)值越小，說(shuō)明學(xué)生間的成績(jī)差異較小，反之，說(shuō)明學(xué)生的成績(jī)存在兩極分化。

3.6 統(tǒng)計(jì)成績(jī)表中同時(shí)符合多重條件的人數(shù)

例：如果想統(tǒng)計(jì)成績(jī)表中，性別為男，語(yǔ)文成績(jī)?cè)?0分以上，數(shù)學(xué)成績(jī)?cè)?0分以上的學(xué)生人數(shù)。假設(shè)A1-A50存放性別信息，B1-B50存放語(yǔ)文成績(jī)，C1-C50存放數(shù)學(xué)成績(jī)，則在要存放結(jié)果的單元格中輸入公式：=SUM（IF（（A1：A50="男"）*（ B1：B50〉90）*（ C1：C50>80），1，0）），輸入完公式后按Ctrl+Shift+Enter組合鍵，讓它自動(dòng)加上數(shù)組公式符號(hào)”{}”。

則在存放結(jié)果的單元格中返回值即為同時(shí)符合多重條件的人數(shù)。

在Excel中有很多功能強(qiáng)大的函數(shù)，例如各種概念分布統(tǒng)計(jì)函數(shù)、各種數(shù)學(xué)運(yùn)算函數(shù)、

各種財(cái)務(wù)函數(shù)等等。在工作中根據(jù)工作性質(zhì)的不同會(huì)用到不同的函數(shù)，我們?cè)谑褂脮r(shí)可以通過(guò)查閱Excel幫助來(lái)學(xué)習(xí)函數(shù)的使用，通過(guò)Excel中的函數(shù)可以幫助我們輕松實(shí)現(xiàn)數(shù)據(jù)分析和管理。

參考文獻(xiàn)：

[1] 馮博琴，姚普選.計(jì)算機(jī)文化基礎(chǔ)教程[M].北京：清華大學(xué)出版社，2001.

[2] 韓小良.Excel高效辦公應(yīng)用大全[M].北京：中國(guó)鐵道出版社，2008.