陳 瑛鄭 毅

(1.武漢冶金管理干部學(xué)院信息部 湖北 武漢:430080;2.武漢鋼鐵工程技術(shù)集團(tuán)自動化有限責(zé)任公司 湖北 武漢:430080)

Excel 2007高級篩選功能的應(yīng)用

陳 瑛1鄭 毅2

(1.武漢冶金管理干部學(xué)院信息部 湖北 武漢:430080;2.武漢鋼鐵工程技術(shù)集團(tuán)自動化有限責(zé)任公司 湖北 武漢:430080)

通過對Excel 2007“數(shù)據(jù)”菜單中“高級篩選”命令的分析,設(shè)置高級篩選中條件區(qū)域的構(gòu)造方法,能夠彌補(bǔ)目前Excel 2007教材中對高級篩選功能過于簡單的不足。介紹如何掌握高級篩選的應(yīng)用技巧,便于有效地解決一些實(shí)際問題。

Excel 2007;高級篩選;條件區(qū)域

Excel 2007是一款功能強(qiáng)大的電子表格編輯制作軟件,是個人及辦公事物處理的理想工具,其篩選功能可以幫助我們從已有的復(fù)雜數(shù)據(jù)表中得到所需數(shù)據(jù)。

數(shù)據(jù)篩選是將工作表中所有不滿足條件的數(shù)據(jù)暫時隱藏起來,只顯示那些符合條件的數(shù)據(jù)。Excel提供了二種不同的篩選方式:自動篩選和高級篩選。其中“自動篩選”只能用于條件簡單的篩選操作,不能實(shí)現(xiàn)字段之間包含“或”關(guān)系的操作;“高級篩選”則能夠完成比較復(fù)雜的多條件查詢,并能將篩選結(jié)果復(fù)制到其它位置。目前市場上的Excel 2007教材對高級篩選功能介紹過于簡單,不利于使用者解決實(shí)際問題。

1 高級篩選的操作過程

高級篩選的操作過程分兩步進(jìn)行。

步驟一:輸入高級篩選條件,打開“高級篩選”對話框。

(1)在要篩選的工作表的空白位置處,輸入所要篩選的條件;

(2)在“數(shù)據(jù)”主菜單下的“排序和篩選”工具欄中(見圖1),單擊“高級”按鈕。

步驟二:設(shè)置篩選方式,篩選出滿足條件的結(jié)果。

(1)單擊上述“高級”按鈕后,打開“高級篩選”對話框(見圖2)。

圖1 排序和篩選

圖2 高級篩選

(2)在“方式”下,選中“將篩選結(jié)果復(fù)制到其它位置”的單選按鈕;

(3)單擊“列表區(qū)域”右側(cè)的拾取器按鈕,進(jìn)行單元格區(qū)域選取;

(4)單擊“條件區(qū)域”右側(cè)的拾取器按鈕,選取輸入的篩選條件單元格區(qū)域;

(5)單擊“復(fù)制到”右側(cè)的拾取器按鈕,設(shè)置顯示篩選結(jié)果的單元格區(qū)域;

(6)單擊“確定”按鈕。系統(tǒng)會自動將符合條件的記錄篩選出來并復(fù)制到指定的單元格區(qū)域。

注意:若要通過隱藏不符合條件的行來篩選區(qū)域,請單擊“在原有區(qū)域顯示篩選結(jié)果”,系統(tǒng)會自動將符合條件的記錄篩選出來并復(fù)制到指定的單元區(qū)域。

2 應(yīng)用實(shí)例

Excel 2007高級篩選的關(guān)鍵之處在于正確地設(shè)置篩選條件,即建立條件區(qū)域。條件區(qū)域可以是通配符、文本、數(shù)值、計(jì)算公式和比較式。

在Excel中,條件區(qū)域構(gòu)造的規(guī)則是:同一列中的條件表示“或”;同一行中的條件表示“與”。即AND只有所有條件都成立,整個條件都成立。還有“或”、“與”的復(fù)合條件,用公式創(chuàng)建條件等。為使讀者有直觀的認(rèn)識,下面通過實(shí)例具體進(jìn)行說明。

2.1 根據(jù)工作表某一列構(gòu)造一個條件,條件都必須為真

使用通配符“＊”進(jìn)行高級篩選?！埃贝砣舾蓚€字符。以《學(xué)生基本信息表》為例,查找姓“李”的所有學(xué)生的記錄(見表1)。

操作步驟:

(1)建立條件區(qū)域:在數(shù)據(jù)區(qū)域外的任一單元格中輸入被篩選的字段名稱“姓名”,在其下方的單元格中輸入篩選條件“李＊”(見表2)。

表1 學(xué)生基本信息表

表2 添加篩選區(qū)域

(2)使用“數(shù)據(jù)”選項(xiàng)卡上“排序和篩選”組中的“高級”命令,彈出“高級篩選”對話框,選擇篩選方式中的“將篩選結(jié)果復(fù)制到其它位置”單選按鈕(見圖3)。

圖3 高級篩選

(3)分別將“列表區(qū)域”設(shè)置為“＄A＄2:＄G＄13”;“條件區(qū)域”設(shè)置為“＄B＄15:＄B＄16”;“復(fù)制到”設(shè)置為“＄A＄18”。

(4)單擊“確定”按鈕,系統(tǒng)會自動將符合條件的記錄篩選出來并復(fù)制到指定的從A18開始的單元格區(qū)域中(見表3)。

2.2 根據(jù)工作表某一列可構(gòu)造區(qū)間查詢條件,條件都必須為真

再以《學(xué)生基本信息表》為例。篩選年齡在1981/3/15至1983/7/4之間出生的學(xué)生的記錄。

操作步驟:其它操作步驟同上,條件區(qū)域設(shè)置如下(見表4)。

表3 篩選結(jié)果

表4 條件區(qū)域

單擊“確定”按鈕,系統(tǒng)會自動將符合條件的記錄篩選出來(見表5)。

表5 篩選結(jié)果

2.3 根據(jù)工作表多列構(gòu)造單一條件,其中所有條件都必須為真

如果將篩選條件輸入在同一行中,篩選時系統(tǒng)會自動將同時滿足所有指定條件的記錄篩選出來。即條件之間的關(guān)系為“與”關(guān)系。在《學(xué)生基本信息表》中,查找“性別”為男性、“專業(yè)”為機(jī)械工程,“入學(xué)成績”在580分以上且是“籍貫”是江蘇的學(xué)生記錄。

操作步驟:其它操作步驟同上,在條件區(qū)域設(shè)置如下(見表6):

表6 條件區(qū)域

單擊“確定”按鈕,系統(tǒng)會自動將符合條件的記錄篩選出來(見表7)。

表7 篩選結(jié)果

2.4 根據(jù)工作表多列構(gòu)造復(fù)合條件,其中所有條件都必須為真

如果要表示“或”關(guān)系的條件,則要求在字段下方的不同行輸入條件,篩選結(jié)果只滿足其中任意一個條件。在《學(xué)生信息表》中,查找“性別”為男性、“專業(yè)”為機(jī)械工程,“入學(xué)成績”在580分以上,“籍貫”不一定是江蘇的學(xué)生記錄等。

操作步驟:其它操作步驟同上,在條件區(qū)域設(shè)置如下(見表8)。

單擊“確定”按鈕,系統(tǒng)會自動將符合條件的記錄篩選出來(見表9)。

表8 條件區(qū)域

2.5 根據(jù)公式的計(jì)算結(jié)果構(gòu)造篩選條件,篩選出滿足條件的學(xué)生紀(jì)錄

公式的計(jì)算結(jié)果可以作為篩選條件,在《學(xué)生基本信息表》中,需要找出進(jìn)入本校所有學(xué)生入學(xué)成績的平均分,并篩選出高于平均分的全體同學(xué)的記錄。

操作步驟:

建立條件區(qū)域:列標(biāo)題D16和E16不寫任何內(nèi)容,但在選擇“條件區(qū)域”時一定要選擇。在E17中輸入計(jì)算平均值的公式“=AVERA GE(F3:F13)”,回車后計(jì)算結(jié)果604直接顯示在E17單元格中。在D17中輸入篩選條件“=F3>=＄E＄17”,回車后D17顯示為“FLASE”。(因?yàn)镕3的數(shù)值601<604,見圖4)

圖4 條件區(qū)域

使用“數(shù)據(jù)”選項(xiàng)卡上“排序和篩選”組中的“高級”命令,選擇篩選方式中的“將篩選結(jié)果復(fù)制到其它位置”單選按鈕(見圖5)。

單擊“確定”。高于平均分的全體同學(xué)記錄顯示出來(見表10)。

將公式的計(jì)算結(jié)果作為篩選條件時務(wù)必注意如下兩點(diǎn):

(1)構(gòu)造條件區(qū)域時:要求標(biāo)題行不能和數(shù)據(jù)表中標(biāo)題行相同,既可空白又可書寫內(nèi)容。上例條件區(qū)域標(biāo)題D16和E16原是空白。現(xiàn)把列標(biāo)題補(bǔ)上,D16寫“滿足”E16寫“平均分”標(biāo)題不同于《學(xué)生基本信息表》中的標(biāo)題,其篩選結(jié)果和表12完全一致(見表11)。

圖5 高級篩選

(2)若構(gòu)造條件區(qū)域時,標(biāo)題行和數(shù)據(jù)表中標(biāo)題行相同,計(jì)算機(jī)則告知“引用無效”。

上例中找出進(jìn)入本校所有學(xué)生入學(xué)成績的平均分。用作條件的公式必須使用“相對引用”來引用列標(biāo)。如“=AVERAGE(F3:F13)”。公式中的其他所有引用必須為“絕對引用”,如“=F3>=＄E＄17”中的“＄E＄17”。并且公式的計(jì)算結(jié)果以“TRUE”或“FALSE”形式出現(xiàn)。

表9 篩選結(jié)果

表10 篩選結(jié)果

2.6 根據(jù)比較式構(gòu)造篩選條件,篩選出符合條件的記錄

比較式“<>”是不等于的意思。用它進(jìn)行高級篩選,可以解決很多實(shí)際問題。如每年收發(fā)教材會遇到如下情況,正常情況是應(yīng)收數(shù)量和實(shí)收數(shù)量相等,但假如出現(xiàn)應(yīng)收數(shù)量和實(shí)收數(shù)量不同,如何查漏補(bǔ)缺。用比較式很容易解決這個問題。

操作步驟:以《教材信息表》為例,見表12。

(1)建立條件區(qū)域:C16為條件區(qū)域的列標(biāo)題,空白,在C17中輸入“=C4<>D4”,單擊確定。因?yàn)镃4單元格數(shù)字不等于D4單元格的數(shù)字,C17單元格就顯示為“TRUE”。

表11 篩選結(jié)果

表12 教材信息表

(2)使用“數(shù)據(jù)”選項(xiàng)卡上“排序和篩選”工具中的“高級”命令,“列表區(qū)域”為＄A＄3:＄d＄13,“條件區(qū)域”為＄C＄16:＄C＄17,“復(fù)制到”為＄A＄18區(qū)域(見表13)。

表13 添加篩選區(qū)域

(3)通過篩選,查找出應(yīng)收數(shù)量和實(shí)收數(shù)量不符合的記錄(見表14)。

注意:其中C16中可以不錄入任何數(shù)據(jù)。構(gòu)造條件區(qū)域時:要求標(biāo)題行不能和數(shù)據(jù)表中標(biāo)題行相同,既可空白又可書寫非標(biāo)題內(nèi)容,但在選擇“條件區(qū)域”時一定要選擇。

3 結(jié)束語

利用Excel 2007高級篩選功能,關(guān)鍵在于巧妙構(gòu)造條件區(qū)域。

(1)非公式篩選,條件區(qū)域的標(biāo)題與數(shù)據(jù)區(qū)域標(biāo)題保持一致。高級篩選前應(yīng)將篩選條件輸入在工作表的空白區(qū)域,且條件區(qū)域的位置要與原數(shù)據(jù)區(qū)域保持至少空一行或一列的位置。

(2)用公式創(chuàng)建條件時,必須使用非數(shù)據(jù)表標(biāo)題,公式正確,就能計(jì)算出相應(yīng)正確的結(jié)果。

(3)用比較式完成篩選應(yīng)在條件區(qū)域的相應(yīng)單元格中鍵入“=條目”,否則將不能對數(shù)據(jù)做出正確篩選。

[1] 博學(xué)工作室.EXCEL2007數(shù)據(jù)處理與分析范例精講[M].北京:機(jī)械工業(yè)出版社,2008.

[2] 飛師教育.EXCEL2007中文版范例學(xué)[M].北京:電子工業(yè)出版社,2008.

[3] 沈 浩.EXCEL高級應(yīng)用與數(shù)據(jù)分析[M].北京:電子工業(yè)出版社,2008.

Application of Excel 2007 Advanced Filter Function

CHEN Ying ZHENG Yi

Through analysis of"advanced filter"order in Excel 2007"data"menu,criteria range creating method is set up in advanced filter,with which the drawback that advanced filter function was too simple in Excel 2007 textbooks can be compensated.Meanwhile,some practical skills in the application of advanced filter are introduced so as to solve some practical problems.

Excel 2007;advanced filter;criteria range

TP315

A

1671-3524(2010)03-0042-05

(責(zé)任編輯:栗 曉)

2010-06-01

2010-06-25

陳瑛(1962～),女,教師.E-mail:chyingo@163.com