第4單元Excel2003的使用技巧

1、1第4單元Excel2003的使用技巧2p學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)會對數(shù)據(jù)表進行數(shù)據(jù)有效性設(shè)置；會利用相關(guān)函數(shù)對數(shù)據(jù)表中數(shù)據(jù)進行統(tǒng)計計算；會對數(shù)據(jù)清單中的數(shù)據(jù)進行排序、篩選及分類匯總；能夠利用數(shù)據(jù)透視表對數(shù)據(jù)清單匯總、繪制圖表3目錄案例1 數(shù)據(jù)填充與數(shù)據(jù)有效性設(shè)置案例2 成績計算與統(tǒng)計案例3 成績分析案例4 成績表格式化案例5 投資理財案例6 產(chǎn)品銷售數(shù)據(jù)管理案例7 教工信息統(tǒng)計與分析4任務(wù)一 學(xué)生成績錄入與數(shù)據(jù)有效性控制p在錄入含有大量數(shù)據(jù)記錄的過程中，不可避免地出現(xiàn)錄入錯誤的現(xiàn)象。為了盡可能地減少錄入錯誤，可通過對數(shù)據(jù)表單元格區(qū)域設(shè)置有效性校驗條件，一旦出現(xiàn)錄入錯誤，系統(tǒng)會自動提示，以便修改。5案

2、例1 數(shù)據(jù)填充與數(shù)據(jù)有效性設(shè)置61案例分析案例中（1）（2）的要求可以通過自動填充或手動填充來實現(xiàn)；（3）（4）中的要求可以通過“數(shù)據(jù)”菜單下的“有效性”命令來實現(xiàn)。72相關(guān)知識點（1）工作簿、工作表、單元格（單元格區(qū)域）Excel文檔稱為Excel工作簿，其擴展名是“.xls”，每一個工作簿可包含多張工作表。工作表默認的標(biāo)簽名稱是Sheet1、sheet2、Sheet3等。每張工作表有256列、2562=65536行，列號用字母A、B、標(biāo)識，行號用1、2標(biāo)識。單元格地址用列號加行號表示，如A1、A65536等。單元格區(qū)域表示：左上角單元格地址：右下角單元格地址表示，如A1:D100。8（2）

3、文本型數(shù)值數(shù)據(jù)的輸入 在中文Excel中，文本是指當(dāng)作字符串處理的數(shù)據(jù)。對于不參與運算的數(shù)字型數(shù)據(jù)有時必須把它們作為文本型數(shù)據(jù)處理，否則可能顯示不正確或造成某些數(shù)據(jù)位的丟失。如身份證號（若作為數(shù)值型數(shù)據(jù)處理，身份證最后幾位數(shù)將作0處理）、課程代碼06040701（若作為數(shù)值型數(shù)據(jù)左面的0將丟失）等。在輸入這些作為文本的數(shù)字型數(shù)據(jù)時，應(yīng)先輸入英文單引號“”，再輸入數(shù)字串。如060407019（3）數(shù)據(jù)填充對于工作表中同行或同列數(shù)據(jù)有規(guī)律時，可以使用填充功能快速輸入數(shù)據(jù)。n單擊“編輯填充向下（上）填充、向右（左）填充”命令，實現(xiàn)數(shù)據(jù)快速復(fù)制；1011n單擊“編輯填充序列”命令，根據(jù)需要可在“序列”

4、對話框選擇“等差序列”、“等比序列”、“日期”之一進行數(shù)據(jù)填充。12自定義序列單擊“工具選項”命令，在“自定義序列中添加或?qū)?，如下圖所示。13p注意：老師在講課時，可講完一個知識點，接著就講實現(xiàn)方法，不一定嚴(yán)格按照先講完知識點后，才講實現(xiàn)方法，這樣效果不好。但課件還是按照這個順序來寫。p這里先講案例（1）、（2）的實現(xiàn)方法。p課堂實踐114（4）數(shù)據(jù)的有效性就是對某單元格區(qū)域預(yù)先設(shè)置好數(shù)據(jù)應(yīng)滿足的條件，一旦用戶輸入數(shù)據(jù)不符合有效性條件時，系統(tǒng)會自動彈出錯誤信息對話框，以提示用戶修改輸入數(shù)據(jù)，這樣可盡可能減少人工輸入錯誤。方法：單擊“數(shù)據(jù)有效性”命令，在彈出“數(shù)據(jù)有效性”對話框中進行各種設(shè)置，

5、詳見3、4中步驟。15163實現(xiàn)方法17數(shù)據(jù)格式在“單元格格式”對話框中設(shè)置18（3）”性別“列數(shù)據(jù)有效性的實現(xiàn)步驟：19（4）“成績”列的數(shù)據(jù)有效性設(shè)置步驟：20p注意：刪除標(biāo)題E1-I1單元格的有效性設(shè)置（這里的設(shè)置不合情理）：選擇E1-I1單元格區(qū)域，在如圖4-5所示的數(shù)據(jù)有效性設(shè)置中，單擊“全部清除”按鈕，即可清除這里的有效性設(shè)置。光標(biāo)移到相關(guān)列，在相關(guān)單元格中輸入任意不符合要求的值，觀察有效性設(shè)置效果。214課堂實踐225評價與總結(jié) 教師根據(jù)實踐情況，或強調(diào)、演示或讓學(xué)生上臺操作 老師或?qū)W生總結(jié)本單元的知識點236課外延伸在“數(shù)據(jù)有效性”對話框“設(shè)置”選項卡“允許”列表框中選擇“自定

6、義”選項，如何實現(xiàn)案例1中的有效性要求？試試看?。ㄌ崾荆河玫竭壿嫼瘮?shù)and及or）提示：快速輸入分數(shù)及當(dāng)前日期及時間數(shù)據(jù)請看備注欄內(nèi)容。24任務(wù)二任務(wù)二 學(xué)生成績表的統(tǒng)計與分析學(xué)生成績表的統(tǒng)計與分析 教師在某門課程教學(xué)結(jié)束時，通常都要對教學(xué)進行評價，對學(xué)生的平時考查成績、期末成績、平時表現(xiàn)分數(shù)等進行統(tǒng)計計算，最后給每一位同學(xué)一個最終成績或等級。這些繁瑣的工作其實利用Excel函數(shù)計算就很容易搞定了。25案例案例2 成績計算與統(tǒng)計成績計算與統(tǒng)計261案例分析Excel系統(tǒng)提供大量的函數(shù)，給人們計算帶來了極大方便。利用Sum()及Average()函數(shù)可以完成（1）、（2）中的任務(wù)；函數(shù)Count

7、()與Countif()可以進行計數(shù)和有條件的計數(shù)；if（）函數(shù)可以進行邏輯判斷，根據(jù)判定不同的結(jié)果從而返回不同的值。Sumif()函數(shù)可以進行有條件的求和，如（6）中的要求。272相關(guān)知識點Excel提供了大量的系統(tǒng)函數(shù)，功能非常豐富。按照其功能來劃分主要有統(tǒng)計函數(shù)、日期與時間函數(shù)、數(shù)學(xué)與三角函數(shù)、財務(wù)函數(shù)、邏輯函數(shù)、文本函數(shù)、數(shù)據(jù)庫函數(shù)等。在輸入函數(shù)時，必須以“=”開頭，輸入函數(shù)的一般格式為：=函數(shù)名（參數(shù)1,參數(shù)2，）28(1)常用函數(shù)（不一定屬于模板中常用函數(shù)）nSUM(number1,number2,.)，返回參數(shù)之和nAVERAGE(number1,number2,.)，返回參數(shù)的

8、平均值 nCOUNT(value1,value2,.)，返回包含數(shù)字數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個數(shù) ;nCOUNTIF(range,criteria),返回區(qū)域中滿足給定條件的單元格的個數(shù)，其中：urange是統(tǒng)計的單元格區(qū)域；29ucriteria為確定哪些單元格將被計算在內(nèi)的條件。其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為32、“32”、“32”或“apples”等。nSUMIF(range,criteria,sum_range)，返回滿足條件的若干單元格的和，其中：uRange為用于條件判斷的單元格區(qū)域；uCriteria為確定哪些單元格將被相加求和的條件；uSum

9、_range是需要求和的實際單元格（區(qū)域）。30（2）常用數(shù)學(xué)函數(shù)31 此外，常用數(shù)學(xué)函數(shù)還有取整函數(shù)INT、平方根函數(shù)SQRT、絕對值函數(shù)ABS、冪函數(shù)POWER、隨機函數(shù)Rand等，詳細請參考Excel幫助中的相關(guān)實例（在Excel中，按F1鍵后在“搜索”框中輸入關(guān)鍵字，回車）。32（3）邏輯函數(shù)IF(logical_test,value_if_true,value_if_false)，執(zhí)行真假值判斷，根據(jù)邏輯計算的真假值，返回不同結(jié)果。其中：nlogical_test表示計算結(jié)果為TRUE或FALSE的任意值或表達式；nValue_if_true是logical_test為TRUE時返回

10、的值，nValue_if_false是logical_test為FALSE時返回的值。如：=if(53,”Y”,”N”)返回”Y”,而=if(530,”Y”,”N”)返回”N”。33AND(logical1,logical2, .)所有參數(shù)的邏輯值為真時，返回 TRUE；只要有一個參數(shù)的邏輯值為假，即返回 FALSE。如：=AND（53, 10050）返回TRUE，而公式：=AND（53, 100500）返回 FALSE。34OR(logical1,logical2, .) 所有參數(shù)的計算值均為FALSE時，才返回FALSE；只要有一個參數(shù)的邏輯值為真時，即返回 TRUE。如： 公式：=OR（

11、53, 100500）返回TRUE， 公式：=OR（530, 100500）返回 FALSE。35（4）文本與數(shù)據(jù)函數(shù)LEN(text)，返回文本字符串中的字符數(shù)。如：=len(abcd)返回4, =LEN(英語abc)返回5 MID(text,start_num,num_chars) 返回文本字符串中從指定位置開始的特定數(shù)目的字符串。 =MID(英語abc123,3,3)返回abc。 36TEXT(value,format_text),將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。 如：=TEXT(20000,“$0.00”)，返回值“$20000.00”。VALUE(text)，返回由數(shù)字文本字符

12、串轉(zhuǎn)換成的數(shù)字說明：Text可以是MicrosoftExcel中可識別的任意常數(shù)、日期或時間格式。如果Text不是這些格式的數(shù)據(jù)，則函數(shù)VALUE返回錯誤值#VALUE!。如：=VALUE($1,000)，返回數(shù)值1000。37常用的文本函數(shù)還有LOWER(將文本轉(zhuǎn)換成大寫形式)、UPPER（將文本轉(zhuǎn)換成大寫形式）、TRIME（除了單詞之間的單個空格外，清除文本中所有的空格）等，更多文本函數(shù)請參考幫助 “文本與數(shù)據(jù)函數(shù)”中有關(guān)函數(shù)。383實現(xiàn)方法（1）將光標(biāo)定位于J2單元格中，單擊工具欄上的“自動求和”按鈕，回車。再拖動單元格J2右下角的填充柄直到J22單元格。（2）將光標(biāo)定位于E23單元格中

13、 ，單擊工具欄上“自動求和”右側(cè)的下拉按鈕，單擊“平均值”命令，圈選求平均值的單元格區(qū)域后回車 ，將公式橫向填充到F23：I23即可。 39（3）操作步驟：40（4）操作步驟：41（5）的操作步驟：42（6）的操作步驟：43有問題，查幫助444課堂實踐455評價與總結(jié)（視情況而定）第一組同學(xué)（2位為一組）主動上臺演示（1）-（3）。第二組同學(xué)主動上臺演示（4）-（6）。鼓勵同學(xué)總結(jié)本案例解決思路，學(xué)生或老師補充。466課外延伸（1）時間日期函數(shù)Now()返回當(dāng)前日期和時間所對應(yīng)的序列號或日期時間數(shù)據(jù)（與單元格格式有關(guān)）。如：=NOW()47TODAY()返回當(dāng)前日期的序列號或日期格式數(shù)據(jù)。如=

14、 TODAY()。DATE(year,month,day)返回代表特定日期的序列號。如果在輸入函數(shù)前，單元格格式為“常規(guī)”，則結(jié)果將顯示為日期數(shù)據(jù)。如：=date(2010,11,12)。 48YEAR(serial_number)，返回某日期對應(yīng)的年份。 =year(now()與year函數(shù)具有相同參數(shù)的日期時間函數(shù)還有month、day、hour、minute、time函數(shù)。（2）信息函數(shù)請參見教材49（3）課外練習(xí)50案例案例3 成績分析成績分析【場景描述】511案例分析522相關(guān)知識點（1）復(fù)制、移動單元格（區(qū)域）中的內(nèi)容53選擇性粘貼單擊“編輯選擇性粘貼”命令在“選擇性粘貼”對話框中

15、選擇相關(guān)選項即可，如圖4-13所示。以案例（1）示范即可54（3）單元格的引用n 單元格的絕對引用：是指對含有公式的單元格進行復(fù)制，不論復(fù)制到什么位置，公式中所引用的單元格地址都不發(fā)生變化。 如公式：=SUM($E$2:$I$2)，就是對單元格的絕對引用實例。絕對引用單元格地址的列號和行號前都加“$”符號，如$E$2、$A$1等。 55單元格的相對引用：是指將包含公式的單元格復(fù)制到其他單元格時，公式中所引用的單元格地址也隨之發(fā)生變化。 例如，將工作表中J2單元格中的計算公式SUM(E2:I2)復(fù)制到J10單元格中就變成了SUM(E10:I10)。56相對引用單元格地址變化規(guī)律：相對引用的公式被

16、復(fù)制到其他單元時，公式所在單元格與引用單元格之間仍保持行列相對位置關(guān)系不變，相當(dāng)于對單元格做平移操作。于是相對引用單元格地址變化有如下規(guī)律：原行號+行地址偏移量新行地址原列號+列地址偏移量新列地址57單元格的混合引用：是指在公式引用的單元格地址中，既有絕對地址引用又有相對地址引用。 如本例單元格K2中有公式“=SUM(N$3:R$3)”，當(dāng)將公式復(fù)制到L2中時，公式將變?yōu)椤?SUM(O$3:S$3)”，對單元格列的引用為相對引用，對行的引用為絕對引用。公式復(fù)制時，相當(dāng)于向右平移一個單元格的位置，絕對引用的地址不變。思考：什么時候用絕對、相對及混合引用？583實現(xiàn)方法59（3）在J2單元格中插入

17、rank 函數(shù)，由于公式在填充時，總分隨著學(xué)生而變，而每個分數(shù)都要與全體分數(shù)相比較，所以在Number中使用相對引用，Ref 中行號使用絕對引用 ，設(shè)置如下圖所示：60614課堂實踐（1）打開“成績計算2.xls”文件，按本案例各項要求，完成各步計算。5評價與總結(jié)626課外延伸請打開工作薄文件CFB.XLS，用公式完成繪制九九乘法表： 請在B2:J10區(qū)域制作，要求在B2單元格輸入公式，然后復(fù)制到其他單元格.63案例案例4 成績表格式化成績表格式化【場景描述】 張老師對成績表的計算、統(tǒng)計等工作都已完成，他還要想對成績表進行格式化工作，如設(shè)置單元格數(shù)據(jù)格式、字體、對齊方式、邊框底紋等，還需要將那

18、些不及格的同學(xué)用紅顏色標(biāo)示出來。打開“成績表3.xls”工作簿文件，按照張老師格式化工作表的要求完成如下設(shè)置：64651案例分析 本案例的前4個問題主要是單元格（區(qū)域）的格式化問題，在“單元格格式”對話框中可以完成；（5）中主要是條件格式問題；最后一個問題是對工作表復(fù)制、移動等操作 662相關(guān)知識點（1）單元格的格式化單擊“格式單元格”命令，彈出“單元格格式”對話框，如圖4-16所示。在該對話框中，可以對單元格（區(qū)域）數(shù)據(jù)的數(shù)字顯示方式、對齊方式、字體、邊框、圖案等進行設(shè)置。67數(shù)字格式：數(shù)字格式：在“數(shù)字”選項卡中，可為單元格數(shù)據(jù)設(shè)置顯示格式，如：常規(guī)、數(shù)值、會計專用、日期、文本、特殊等模式

19、，還要自定義顯示格式，如圖4-16所示。對齊方式：對齊方式：在“對齊”選項卡中，可為單元格數(shù)據(jù)設(shè)置對齊方式，如：水平對齊、垂直對齊、自動換行、合并單元格等。68（2）條件格式就是根據(jù)本單元格或其它單元格中的數(shù)據(jù)是否滿足一定條件來設(shè)置單元格的數(shù)據(jù)格式，如將單元格區(qū)域E2：I23中小于60的數(shù)據(jù)用紅色顯示，若單元格內(nèi)數(shù)據(jù)值小于60，則字體顯示為紅色，否則不變。69操作步驟：選擇要設(shè)置的單元格（區(qū)域）單擊“格式條件格式”命令在“條件格式”對話框中，構(gòu)造要滿足的條件單擊“格式”按鈕在彈出的對話框中設(shè)置字體、邊框、圖案等 703實現(xiàn)方法7172（2）選擇字段名區(qū)域A2：K2單擊右鍵單擊快捷菜單中的“設(shè)置

20、單元格格式”命令在“單元格格式”對話框的“字體”選項卡中設(shè)置字體：宋體、加粗、字號大小12；在“對齊”選項卡中設(shè)置文本對齊方式：“水平對齊”居中、“垂直對齊”居中；選擇單元格區(qū)域B3：B23，同前面操作一樣，在“對齊”選項卡中的“水平對齊”選項中，從下拉列表框中選擇“分散對齊”。73（3）選擇單元格區(qū)域E3：J23單擊右鍵，單擊“設(shè)置單元格格式”命令在“單元格格式”對話框的“數(shù)字”選項卡的“分類”列表中選擇“數(shù)值”，在“小數(shù)位數(shù)”框中設(shè)置為1，如圖4-16所示。選擇單元格區(qū)域D3：D23，同樣在“單元格格式”對話框“數(shù)字”選項卡的“分類”列表中選擇“日期”，在右邊“類型”框中選擇“2001-3

21、-14”，“確定”。74（4）選擇單元格區(qū)域A2：K23，在如圖4-16所示的“單元格格式”對話框中，選擇“邊框”選項卡，在“線條”框的“樣式”中選粗實線，在“顏色”列表框中單擊紅色，單擊“外邊框”按鈕，同樣，選擇藍色細實線，單擊“內(nèi)部”按鈕，單擊“確定”按鈕。75（5）選擇單元格區(qū)域E2：I23單擊“格式條件格式”命令在“條件格式”對話框的第1個列表框中選擇“單元格數(shù)值”，在第2個列表框中選擇運算符“小于”，在第3個框中輸入60單擊“格式”按鈕在“單元格格式”的“字體”選項卡 “顏色”框中選擇紅色“確定”，如圖4-17”所示。7677784課堂實踐（1）實踐案例（1）-（4）；（2）實踐案例

22、（5）-（7）；5評價與總結(jié)第一組同學(xué)（2位為一組）主動上臺演示（1）-（4）操作中部分操作；第二組同學(xué)上臺演示（5）-（7）全部或部分操作，同學(xué)或老師點評；鼓勵同學(xué)總結(jié)本案例主要知識要點，學(xué)生或老師補充。796課外延伸80案例案例5 投資理財與貸款計算投資理財與貸款計算【場景描述】【場景描述】811案例分析 在本案例中，可應(yīng)用PMT（）函數(shù)計算基于固定利率及等額分期付款方式，返回貸款的每期付款額來解決(1)中的問題。FV()函數(shù)可用于解決基于固定利率及等額分期付款方式，返回某項投資的未來值，可解決(2)中的問題。函數(shù)PV（）函數(shù)返回投資的現(xiàn)值?，F(xiàn)值為一系列未來付款的當(dāng)前值的累積和，可解決(3

23、)中的問題。822相關(guān)知識點（1）PV(Rate,Nper,Pmt,Fv,Type)函數(shù) 返回投資的現(xiàn)值?，F(xiàn)值為一系列未來付款的當(dāng)前值的累積和。例如，借入方的借入款即為貸出方貸款的現(xiàn)值。 其中各參數(shù)意義如下：83 注意注意：這里將參數(shù)與英文單詞聯(lián)系起來就好記了843實現(xiàn)方法（1）打開電子工作簿文件“投資理財.xls”，光標(biāo)定位于B8單元格中，單擊數(shù)據(jù)編輯欄上的插入函數(shù)“”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”框中輸入“PMT”單擊“轉(zhuǎn)到”按鈕，單擊“確定”。在彈出的“函數(shù)參考”對話框的Rate中輸入A4/12，在Nper框中輸入A5*12，在PV框中輸入1000或A6，如圖4-22所示，

24、單擊“確定”按鈕。8586（2）光標(biāo)定位于B19中，單擊數(shù)據(jù)編輯欄上的插入函數(shù)“”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”框中輸入“FV”，單擊“轉(zhuǎn)到”按鈕，“確定”。在彈出的“函數(shù)參考”對話框的Rate中輸入A14/12，在Nper框中輸入A15*12，在PMT框中輸入-2000，在Type框中輸入1，如圖4-23所示，單擊“確定”按鈕。在B20中，輸入公式“=A16+2000*5*12+(B19-A16-2000*60)*0.8”（即成本+80%的利息），即可算出稅后本息總數(shù)。8788（3）光標(biāo)定位于B30中，單擊數(shù)據(jù)編輯欄上的插入函數(shù)“”按鈕，彈出“插入函數(shù)”對話框，在“搜索函數(shù)”框

25、中輸入“PV”單擊“轉(zhuǎn)到”按鈕，單擊“確定”。在彈出的“函數(shù)參考”對話框的Rate中輸入A26/12，在Nper框中輸入A27*12，在PMT框中輸入-A28，如圖4-24所示，單擊“確定”按鈕。89904課堂實踐實踐本案例5評價與總結(jié)鼓勵同學(xué)主動報告各小題的計算結(jié)果，若不正確錯在什么地方？ 鼓勵同學(xué)總結(jié)本案例主要知識要點，學(xué)生或老師補充。6課外延伸訪問某企業(yè)財務(wù)室，咨詢他們平時在實際財務(wù)工作還常用到哪些財務(wù)函數(shù)，試列舉一個案例。研究IPMT（）函數(shù)、ISPMT（）及NPV（）的用法及意義，試舉例。91案例案例6 產(chǎn)品銷售數(shù)據(jù)管理產(chǎn)品銷售數(shù)據(jù)管理【場景描述】 樂佳電器公司主要經(jīng)營各種家用電器業(yè)

26、務(wù)，銷售業(yè)務(wù)遍及廣東省的東南西北各地區(qū)。公司總經(jīng)理為了及時掌握各個業(yè)務(wù)員及各種電器的銷售情況，不時地要求銷售部管理人員進行數(shù)據(jù)統(tǒng)計匯總等數(shù)據(jù)管理、分析工作。經(jīng)常性數(shù)據(jù)分析管理工作可以歸納為以下幾個方面： 電子工作簿文件“電器銷售表.xls”是公司3、4季度的電器產(chǎn)品銷售數(shù)據(jù)清單，打開此文件按下面要求完成各項任務(wù)：929394p1案例分析 本案例主要涉及數(shù)據(jù)清單的排序、自動篩選、高級篩選、分類匯總及透視表的相關(guān)功能。利用“數(shù)據(jù)”菜單下的“排序”、“篩選”、“分類匯總”、“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令可以完成本案例中的任務(wù)。951.相關(guān)知識點（1）數(shù)據(jù)清單Excel數(shù)據(jù)清單是一個特殊的表格，是包含

27、列標(biāo)題的一組連續(xù)數(shù)據(jù)行的工作表。數(shù)據(jù)清單由兩部組成，即表結(jié)構(gòu)和純數(shù)據(jù)。表結(jié)構(gòu)就是數(shù)據(jù)清單中的第一行，即為列標(biāo)題。數(shù)據(jù)清單的每一列稱為一個字段，列標(biāo)題稱為字段名，從數(shù)據(jù)清單第二行開始的每一行都稱為一條記錄。如圖4-25所示。96單擊“數(shù)據(jù)記錄單”命令，彈出記錄編輯對話框，如圖4-26所示。97（2）數(shù)據(jù)清單的排序按一個字段排序（單擊工具上”升序“、降序按鈕”）按多個字段排序單擊“數(shù)據(jù)排序”命令，在“排序”對話框中，選擇“主要關(guān)鍵字”、“升序/降序”，還可選擇“次要關(guān)鍵字”、“升序/降序”，依此類推，如圖4-27所示。 98以本案例（1）進行演示。（3）數(shù)據(jù)篩選自動篩選 自動篩選只能將篩選出的記錄

28、在原位置上顯示，并且一次只能對一個字段設(shè)置篩選條件。若篩選涉及到多個字段條件時，必須經(jīng)過多次自動篩選才完成篩選任務(wù)。詳細操作步驟見實現(xiàn)方法。如：篩選銷售量在3000以上（包括3000），銷售員為姓王的記錄。99高級篩選高級篩選可一次完成篩選條件較為復(fù)雜的記錄篩選。高級篩選首先要創(chuàng)建篩選條件區(qū)域，如果篩選時要求多個條件同時滿足，則稱這些條件為“與”的關(guān)系；如果篩選只要求滿足多個條件之一時，則稱這些條件為“或”關(guān)系n創(chuàng)建條件區(qū)域的步驟：u將條件涉及的字段復(fù)制到某一行中，若某一字段涉及多次，就復(fù)到多列u在復(fù)制字段的下方，設(shè)置條件100u同行的條件表示“與”，不同行的條件表示“或”條件區(qū)域設(shè)置如圖4-

29、28所示，左邊條件區(qū)域中兩個條件是與的關(guān)系，右邊條件區(qū)域中的兩個是或的關(guān)系。圖4-28 條件區(qū)域101提示：n直接跳轉(zhuǎn)到實現(xiàn)方法部分（4）、（5）部分講解演示、實踐。n跳轉(zhuǎn)：課堂實踐（4）n老師可根據(jù)學(xué)生掌握情況重構(gòu)練習(xí)。102（4）分類匯總分類匯總就是先將記錄按某個字段進行分類，然后在每一類進行匯總（如計數(shù)、求和、求平均值等），如匯總不同職稱教師在某年的發(fā)表論文數(shù)。分類匯總的操作步驟如下：n先按分類字段排序光標(biāo)定位于任一字段名上單擊“數(shù)據(jù)分類匯總”命令，直接跳轉(zhuǎn)實現(xiàn)方法（2）。103跳轉(zhuǎn)：課堂實踐部分（5）數(shù)據(jù)透視表分類匯總只能解決按一個字段分類匯總問題，如要解決按多個字段分類匯總的問題分類

30、匯總已無能為力，如統(tǒng)計不同職稱、不同學(xué)歷、不同性別教師發(fā)表論文數(shù)。透視表制作步驟如下：單擊“數(shù)據(jù)數(shù)據(jù)透視表和數(shù)據(jù)透視圖”命令“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А?， 轉(zhuǎn)操作步驟見實現(xiàn)方法。1043實現(xiàn)方法（1）排序操作步驟：105106（2）分類匯總的操作步驟：107108109（3）自動篩選的操作步驟：110111（4）高級篩選操作步驟：選擇sheet4工作表，在單元格區(qū)域I1：K2設(shè)置篩選條件如圖4-35所示。 然后進行高級篩選。112（5）高級篩選操作步驟：選擇sheet5工作表，在單元格區(qū)域：I1：K3設(shè)置篩選條件如圖4-36所示 ，然后進行高級篩選，過程如下圖所示：113（6）透視表創(chuàng)建與

31、編輯操作步驟：114115116117118（7）頁面設(shè)置操作步驟：1194課堂實踐1205評價與總結(jié)6課外延伸根據(jù)本案例（6）中的透視表，年銷售業(yè)績最好（訂貨量最大）與銷售業(yè)績最差銷售員所在行分別用的圖案顏色分別設(shè)置為淺綠與淺黃色。訂貨量最大產(chǎn)品與訂貨量最少產(chǎn)品名稱分別用紅色和藍色顯示。用函數(shù)方法統(tǒng)計不同銷售員不同產(chǎn)品的訂貨量。121案例案例7 教工信息統(tǒng)計與分析教工信息統(tǒng)計與分析【場景描述】志遠學(xué)院是一所教師眾多的高校，人力資源部門經(jīng)常需要對教師隊伍的學(xué)歷、職稱構(gòu)成、科研論文等數(shù)據(jù)進行統(tǒng)計和分析，以便為領(lǐng)導(dǎo)引進人才、科學(xué)用人提供科學(xué)依據(jù)。教師人數(shù)眾多，統(tǒng)計和管理大量數(shù)據(jù)工作是很辛苦的事情。

32、但如果能熟練地應(yīng)用Excel中的各種統(tǒng)計函數(shù)、數(shù)據(jù)透視表及圖表功能，一定能起到事半功倍的效果。下面就以學(xué)院部分教師的科研數(shù)據(jù)為例，以窺探幾分人力資源部門的部分日常管理工作。 122打開“teacher_inf.xls”文件，完成下列各項工作：1231241案例分析本案例（1）、（2）等涉及到數(shù)據(jù)庫統(tǒng)計函數(shù)知識點，如統(tǒng)計博士或教授發(fā)表論文之和等；（3）中的問題可以用前面已學(xué)過的條件格式知識來解決；（4）中涉及到數(shù)據(jù)查詢函數(shù)vlookup 與lookup的應(yīng)用問題；（5）可以用前面學(xué)習(xí)過Countif 、Sumif等進行統(tǒng)計，也可以用本案例學(xué)習(xí)的數(shù)據(jù)庫函數(shù)進行統(tǒng)計。（6）、（7）主要涉及到創(chuàng)建和編

33、輯圖表有關(guān)知識；最后一個問題涉及到保護工作簿及工作表問題。1252相關(guān)知識點（1）數(shù)據(jù)庫函數(shù) 數(shù)據(jù)庫函數(shù)專門用于數(shù)據(jù)清單帶有附加統(tǒng)計條件的一類統(tǒng)計函數(shù)，在實際統(tǒng)計工作中有著廣泛的應(yīng)用。常用的數(shù)據(jù)庫函數(shù)有DCOUNTA（DCOUNT）、DSUM、DMAX、DMIN、DAVERAGE等，這些函數(shù)的參數(shù)都是Database,Field,Criteria126DCOUNTA(database,field,criteria)返回數(shù)據(jù)庫或列表的列中滿足指定條件的非空單元格個數(shù)。其中：Database構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域，如A1：E31；Field指定函數(shù)所使用的數(shù)據(jù)列。Field可以是文本，如“職

34、稱”，也可以是代表列表中數(shù)據(jù)列位置的數(shù)字：1表示第一列，2表示第二列，等等；Criteria為一組包含給定條件的單元格區(qū)域，如H1:H2。127DSUM(database,field,criteria)返回列表或數(shù)據(jù)庫的列中滿足指定條件的數(shù)字之和。參數(shù)說明同DOUNTA。DMAX(database,field,criteria)，返回列表或數(shù)據(jù)庫的列中滿足指定條件的最大數(shù)值。參數(shù)說明同DOUNTA。DMIN(database,field,criteria)，返回列表或數(shù)據(jù)庫的列中滿足指定條件的最小數(shù)。DAVERAGE(database,field,criteria)返回列表或數(shù)據(jù)庫中滿足指定條

35、件的列中數(shù)值的平均值。128跳轉(zhuǎn)實現(xiàn)方法（1）修改案例中數(shù)據(jù)庫函數(shù)的名字，觀看統(tǒng)計結(jié)果。（2）查找函數(shù)LOOKUP(lookup_value,lookup_vector,result_vector)返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值。其中：129nLookup_value為函數(shù)LOOKUP在第一個向量中所要查找的數(shù)值。Lookup_value可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。nLookup_vector為只包含一行或一列的區(qū)域。如，查找發(fā)表論文最多是哪位教師：=LOOKUP(MAX(E2:E31),E2:E31,A2:A31)，返回第一個向量中最大值對應(yīng)行與第二個向量所

36、對應(yīng)列交叉單元格的值“李文如”，要求E列排序。 130提示：提示：nLookup_vector的數(shù)值必須按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否則，函數(shù)LOOKUP不能返回正確的結(jié)果。n如果函數(shù)LOOKUP找不到lookup_value，則查找lookup_vector中小于或等于lookup_value的最大數(shù)值。n如果lookup_value小于lookup_vector中的最小值，函數(shù)LOOKUP返回錯誤值#N/A。131n跳轉(zhuǎn)實現(xiàn)方法（4）n用“teacher_inf.xls” 進一步練習(xí)LOOKUP函數(shù)的用法。（3）圖表的創(chuàng)建與編輯圖表的創(chuàng)建操作步驟：選擇數(shù)據(jù)區(qū)域單擊工具欄上的“圖表向?qū)А卑粹o在“圖表向?qū)?步驟之1圖表類型”對話框中選擇圖表類型及子類型 132圖表的編輯跳轉(zhuǎn)本案例實現(xiàn)方法（6）、（7）1333實現(xiàn)方法（1）操作步驟：134 同樣，在I8輸入公式“=DAVERAGE(A1:E31,E1,I1:J2)”求女副教發(fā)表論文的平均值。135（2）操作步聚：單擊K2單元格，用DMAX求出“性別”為女發(fā)表論文的最大值64；以“性別”為女