模塊四Excel2010電子表格的應(yīng)用.ppt

1、計算機應(yīng)用基礎(chǔ)win7,志遠(yuǎn)教育 教材征訂與服務(wù)電話01082477073,志遠(yuǎn)教育作品,LOGO,1,LOGO,過渡頁,Excel 2010工作表,工作表數(shù)據(jù)輸入與工作表格式化,過渡頁,2,LOGO,Excel 2010基本概念,1,啟動Excel后，其操作界面如圖4-1所示。Excel的窗口主要包括快速訪問工具欄、標(biāo)題、窗口控制按鈕、選項卡、功能區(qū)、名稱框、編輯欄、工作區(qū)、行號、列標(biāo)、狀態(tài)欄和滾動條等,Excel 2010的用戶界面,圖4-1 Excel 的工作界面,3,LOGO,Excel 2010基本概念,1,Excel 2010專業(yè)術(shù)語,單元格引用,工作簿,單元格地址,單元格和單元格

2、區(qū)域,工作表,工作簿: 一般由3個工作表組成, 分別命名為：Sheet1Sheet3,最多可有255個工作表。當(dāng)前活動的白色,其余灰色，其擴(kuò)展名為.xlsx 工作表:工作的主要對象,由行,列組成的表格, 有：A,B,.Z,AA,AB,.AZ, .IA,IB,.IV(256)列; 1,2.65536行; 單元格:行和列的交叉處，表格的最小單位,每個單元格最多可填寫32767字符 活動單元格: 當(dāng)前正在操作的單元格，被黑線框住。 單元格區(qū)域：是一個矩形塊，由工作表中相鄰的若干個單元格組成。 單元格地址：每個單元格的行列地址，如，B2,A3; 單元格區(qū)域：B2:E5。引用B3、A2單元格,C4：F7

3、區(qū)域，就用B3,A2,C4:F7表示。 單元格引用：絕對坐標(biāo)：$B,$2,$D$6;相對坐標(biāo)：A1，C4; 混合坐標(biāo)：A$4,4,LOGO,正文 . 模塊四,Excel 2010工作表,目,一,Excel 2010基本操作,1,選擇“文件”“新建”命令，在彈出的窗口中可看到有“可用模板”和“O模板”兩大部分，如圖4-2所示，雙擊“可用模板”中的“樣本模板”可以看到本機上可用的模板?！癘模板”是放在指定服務(wù)器上的資源，用戶必須聯(lián)網(wǎng)才能使用這些功能,項,工作簿的操作,圖4-2 Excel模板,5,LOGO,正文 . 模塊四,Excel 2010工作表,目,一,Excel 2010基本操作,1,1）

4、新建工作表 新建工作表最快捷的方法是在現(xiàn)有工作表的末尾單擊屏幕底部的“新建工作表”按鈕。 （2）移動或復(fù)制工作表 移動工作表最快捷的方式：選中要移動的工作表，然后將其拖動到想要的位置。 復(fù)制工作表，右擊需要復(fù)制的一個或多個工作表，單擊右鍵，在彈出的快捷菜單中選擇“移動或復(fù)制工作表”命令，彈出如圖4-3所示的對話框，按圖示操作即可,項,工作表的操作,6,LOGO,Excel 2010工作表,目,一,1,3）刪除工作表 選中要刪除的一個或多個工作表，右擊，在彈出的快捷菜單中選擇“刪除”命令。 （4）重命名工作表 選中要重命名的工作表，右擊，在彈出的快捷菜單中選擇“重命名”命令，或者雙擊工作表標(biāo)簽，

5、均可對工作表表標(biāo)進(jìn)行重命名。 （5）改變工作表標(biāo)簽顏色 選中要改變標(biāo)簽顏色的工作表，右擊，在彈出的快捷菜單中選擇“工作表標(biāo)簽顏色”命令。 （6）更改新工作簿中的默認(rèn)工作表數(shù) 選擇“文件”“選項”命令，然后在“常規(guī)”類別中的“新建工作簿時”下的“包含的工作表數(shù)”文本框中，輸入新建工作簿時默認(rèn)情況下包含的工作表數(shù)。默認(rèn)新建工作表數(shù)最少為1，最多為255,項,工作表的操作,7,LOGO,Excel 2010基本操作,1,單元格及單元格區(qū)域的操作,選中單元格或單元格區(qū)域 單元格（或單元格區(qū)域）的插入與刪除：“開始”-“單元格” 清除：單擊“編輯”功能區(qū)的橡皮擦按鈕,8,1,單元格及單元格區(qū)域的操作,4

6、）移動與復(fù)制 移動操作：移動操作會移走除單元格本身之外的所有信息，包括公式及其結(jié)果值、單元格格式和批注等，粘貼時也包括所有信息。 操作方法為：選中要移動的單元格或單元格區(qū)域，單擊“開始”選項卡上的“剪貼板”功能區(qū)的“剪切”按鈕（也可以使用【Ctrl+X】組合鍵）。選中目標(biāo)單元格，再單擊粘貼按鈕即可。 在移動公式時，無論使用哪種單元格引用，公式內(nèi)的單元格引用都不會更改,9,LOGO,Excel 2010工作表,目,一,Excel 2010基本操作,1,項,單元格及單元格區(qū)域的操作,5）查找與替換 單擊“編輯”功能區(qū)的“查找”按鈕可對工作表進(jìn)行查找、替換、定位等操作，操作方法與Word類似，在此不

7、再贅述,10,LOGO,Excel 2010工作表,目,一,Excel 2010基本操作,1,項,工作表的保護(hù)和共享,1）保護(hù)工作簿的結(jié)構(gòu)和窗口 單擊“審閱”選項卡，在“更改”功能區(qū)單擊“保護(hù)工作簿”按鈕，彈出如圖4-12所示的對話框，用戶可以鎖定工作簿的結(jié)構(gòu)，以禁止用戶添加或刪除工作表，或顯示隱藏的工作表。同時，還可禁止用戶更改工作表窗口的大小或位置。工作簿結(jié)構(gòu)和窗口的保護(hù)可應(yīng)用于整個工作簿中的所有工作表,11,LOGO,Excel 2010工作表,目,一,Excel 2010基本操作,1,項,工作表的保護(hù)和共享,2）保護(hù)工作表 單擊圖4-11中的“保護(hù)工作表”按鈕，彈出如圖4-13所示的對

8、話框，在該對話框中可以設(shè)置“允許此工作表的所有用戶進(jìn)行”列表中，選擇希望用戶能夠進(jìn)行的操作,12,Excel 2010工作表,目,一,1,項,工作表的保護(hù)和共享,3）保護(hù)單元格或單元格區(qū)域 設(shè)置不受保護(hù)的區(qū)域（即用戶可以更改的區(qū)域），操作步驟如下： 選中要解除鎖定（或隱藏）的單元格或單元格區(qū)域。 在“開始”選項卡上的“單元格”功能區(qū)中，單擊“格式”按鈕，然后選擇彈出菜單最下端的“設(shè)置單元格格式”命令。 在“保護(hù)”選項卡上，根據(jù)需要選擇清除“鎖定”復(fù)選框或“隱藏”復(fù)選框，然后單擊“確定”按鈕。 在“審閱”選項卡上的“更改”功能區(qū)中單擊“保護(hù)工作表”按鈕。 設(shè)置不受保護(hù)區(qū)域也可以通過單擊“允許用戶

9、編輯區(qū)域”按鈕來設(shè)置選擇希望用戶能夠更改的元素。 設(shè)置受保護(hù)的區(qū)域（即用戶不可以更改的區(qū)域），操作步驟如下： 選中整張工作表中的所有單元格。 在“開始”選項卡上的“單元格”功能區(qū)中，單擊“格式”，然后選擇“設(shè)置單元格格式”命令。 單擊“保護(hù)”選項卡，根據(jù)需要選擇清除“鎖定”復(fù)選框或“隱藏”復(fù)選框，然后單擊“確定”按鈕。則此狀態(tài)下該工作表中所有的單元格都處于未被保護(hù)的狀態(tài)。 選中需要保護(hù)的單元格區(qū)域，例如A3:D5。 再次單擊“保護(hù)”選項卡，根據(jù)需要選中“鎖定”復(fù)選框或“隱藏”復(fù)選框，然后單擊“確定”按鈕，則該工作表中僅A3:D5單元格區(qū)域處于被保護(hù)的狀態(tài)。 在“審閱”選項卡上的“更改”功能區(qū)中

10、選擇“保護(hù)工作表”命令,13,LOGO,過渡頁,Excel 2010工作表,輸入Word文稿,過渡頁,工作表數(shù)據(jù)輸入與工作表格式化,14,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,要用Excel 2010進(jìn)行數(shù)據(jù)信息的統(tǒng)計和分析，首先要建立一個二維表格。二維表格可以稱為數(shù)據(jù)庫中的關(guān)系型數(shù)據(jù)庫。因此，一定要根據(jù)自己工作的目標(biāo)對數(shù)據(jù)進(jìn)行統(tǒng)計或分析前，選定要進(jìn)行統(tǒng)計的各個項目，并冠以名字，在數(shù)據(jù)庫中稱為“字段”，在二維表格中是“列名稱”，而參加統(tǒng)計的各元素，在同一行的所有單元格稱為“記錄,項,工作表數(shù)據(jù)輸入基礎(chǔ),建立工作表,15,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,使用Ex

11、cel工作表的目的是要利用工作表進(jìn)行數(shù)據(jù)的統(tǒng)計和分析。所以，在統(tǒng)計和分析之前，必須建立一個以統(tǒng)計或分析為目標(biāo)的工作表格。工作過程如下： 根據(jù)工作目標(biāo)要求，建立二維表格。尤其對目標(biāo)統(tǒng)計或分析的項目，要建立完整的列字段名稱。 對表格內(nèi)的數(shù)據(jù)進(jìn)行統(tǒng)計或分析。 可在表格中增加圖表，以增加形象說明的力度。 工作表格格式化，增加工作表的視覺效果,項,工作表數(shù)據(jù)輸入基礎(chǔ),Excel 2010數(shù)據(jù)統(tǒng)計分析過程,16,LOGO,正文 . 模塊四,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,Excel 2010中的文本通常是指字符或者是任何數(shù)字和字符的組合。任何輸入到單元格內(nèi)的字符集，只要不被系統(tǒng)識別成數(shù)字、公式、

12、日期、時間、邏輯值，則Excel一律將其視為文本。在Excel中輸入文本時，默認(rèn)對齊方式是單元格內(nèi)靠左對齊。在一個單元格內(nèi)最多可以存放32767個字符。 對于全部由數(shù)字組成的字符串，如郵政編碼、身份證號碼、電話號碼等這類字符串，為了避免輸入時被Excel認(rèn)為是數(shù)值型數(shù)據(jù)，Excel 2010提供了在這些輸入項前添加“”（英文的單引號）的方法，來區(qū)分是“數(shù)字字符串”而非“數(shù)值”數(shù)據(jù)。例如，要在“B5”單元格中輸入非數(shù)字的電話號“02088886666”，則可在輸入框中輸入“02088886666,項,文本輸入,文本輸入,17,LOGO,正文 . 模塊四,1,在Excel 2010中，當(dāng)建立新的工

13、作表時，所有單元格都采用默認(rèn)的通用數(shù)字格式。通用格式一般采用整數(shù)（無千位分隔符）、小數(shù)（二位，如7.89）、負(fù)數(shù)格式，而當(dāng)數(shù)字的長度超過單元格的寬度時，Excel將自動使用科學(xué)計數(shù)法來表示輸入的數(shù)字。 在Excel中，輸入單元格中的數(shù)字按常量處理。輸入數(shù)字時，自動將它沿單元格右對齊。有效數(shù)字包含09、+、-、（）、/ 、$、%、.、E、e等字符。輸入數(shù)據(jù)時可參照以下規(guī)則： 可以在數(shù)字中包括逗號，以分隔千分位。 輸入負(fù)數(shù)時，在數(shù)字前加一個負(fù)號（-），或者將數(shù)字置于括號內(nèi)。例如，輸入“-20”和“（20）”都可在單元格中得到-20。 Excel忽略數(shù)字前面的正號（+）。 輸入分?jǐn)?shù)（如2/3）時，應(yīng)

14、先輸入“0”及一個空格，然后輸入“2/3”。如果不輸入“0”，Excel會把該數(shù)據(jù)作為日期處理，認(rèn)為輸入的是“2月3日”。 當(dāng)輸入一個較長的數(shù)字時，在單元格中顯示為科學(xué)計數(shù)法（如2.56E09），意味著該單元格的列寬不能顯示整個數(shù)字，但實際數(shù)值仍不變,數(shù)字輸入,18,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,數(shù)字輸入,減少小數(shù)位數(shù),使用貨幣樣式,增加小數(shù)位數(shù),使用千位分隔樣式,使用百分比樣式,使用“開始”選項卡“數(shù)字”功能區(qū)按鈕快速格式化數(shù)字“格式,詳見：P143-P144,19,LOGO,1,數(shù)字輸入,使用“設(shè)置單元格格式”對話框設(shè)置數(shù)字格式,右擊使用快捷菜單或“數(shù)字”功能區(qū)右

15、下角的下拉按鈕，可打開“設(shè)置單元格格式”對話框,20,LOGO,1,公式輸入,算術(shù)運算符 算術(shù)運算符可以完成基本的數(shù)學(xué)運算，如加、減、乘、除等，還可以連接數(shù)字并產(chǎn)生數(shù)字結(jié)果。算術(shù)運算符包括：加號（+）、減號（-）、乘號（*）、除號（/）、百分號（%）以及乘冪（）. 文本運算符 在公式中使用文本運算符（）時，以等號開頭輸入文本的第一段（文本或單元格引用），加入文本運算符，輸入下一段（文本或單元格引用）。例如，用戶在單元格A1中輸入“第一季度”，在A2中輸入“銷售額”。在C3單元格中輸入“A1累計A2”，結(jié)果會在C3單元格顯示“第一季度累計銷售額”。 比較運算符 比較運算符可以比較兩個數(shù)值并產(chǎn)生邏

16、輯值TRUE或FALSE。比較運算符包括=（等于）、（大于）、（不等于）、=（大于等于） 引用運算符 一個引用位置代表工作表上的一個或者一組單元格，引用位置告訴Excel在哪些單元格中查找公式中要用的數(shù)值。有三種引用運算符：冒號(區(qū)域運算符)、逗號(聯(lián)合運算符)以及空格(交叉運算符)。見P146,在Excel工作表的單元格輸入公式時，必須以一個等號(=)作為開頭。等號后面的“公式”中可以包含各種運算符號、常量、變量、函數(shù)以及單元格的引用,21,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,公式和函數(shù)輸入,公式輸入,公式的修改和編輯 在Excel 2010編輯公式時，被該公式所引用的所

17、有單元格及單元格區(qū)域的引用都將以彩色顯示在公式單元格中，并在相應(yīng)單元格及單元格區(qū)域的周圍顯示具有相同顏色的邊框。當(dāng)用戶發(fā)現(xiàn)某個公式中含有錯誤時，需單擊選中要修改公式的單元格。按【F2】鍵使單元格進(jìn)入編輯狀態(tài)，或直接在編輯欄中對公式進(jìn)行修改。此時，被公式所引用的所有單元格都以對應(yīng)的彩色顯示在公式單元格中，使用戶很容易發(fā)現(xiàn)哪個單元格引用錯了。編輯完畢后，按【Enter】鍵確定或單擊編輯欄中的按鈕確定。如果要取消編輯，按【Esc】鍵或單擊編輯欄中的按鈕退出編輯狀態(tài),22,1,函數(shù)輸入,LOGO,23,LOGO,1,公式和函數(shù)輸入,函數(shù)輸入,例1】函數(shù)的使用。要求使用函數(shù)，求出圖4-15所示工資表中職

18、工號為51001職工的實發(fā)工資。 使用函數(shù)的操作: 第一步，選中需要使用函數(shù)的單元格F2，單擊編輯欄中的插入函數(shù)按鈕。 第二步，根據(jù)工作目標(biāo)，找到最適合目標(biāo)要求的函數(shù)，如本例要求求出工資表中第一個職工的實發(fā)工資，實際上，實發(fā)工資是由職務(wù)工資與補貼之和組成，所以要用求和函數(shù)SUM()。 第三步，要將合適的數(shù)據(jù)“填入”函數(shù)的括號中的參數(shù)，就可以完成或求出函數(shù)操作的結(jié)果數(shù)值,24,LOGO,1,公式和函數(shù)的復(fù)制單元格公式引用,1）相對引用 在輸入公式的過程中，除非用戶特別指明，Excel一般是使用相對地址來引用單元格的位置。所謂相對地址是指：如果將含有單元地址的公式復(fù)制到另一個單元格時，這個公式中的

19、各單元格地址將會根據(jù)公式移動到的單元格所發(fā)生的行、列的相差值，作同樣的改變，以保證這個公式對表格其他元素的運算的正確。 例如，將如圖4-17所示的F2單元格復(fù)制到F3:F9，把光標(biāo)移至F5單元格，會發(fā)現(xiàn)公式已經(jīng)變?yōu)椤?(C5+D5+E5)/3”，因為從F2到F5，列的偏移量沒有變，而行作了一行的偏移，所以公式中涉及的列的數(shù)值不變而行的數(shù)值自動加3。其他各個單元格也做出了改變,25,LOGO,1,公式和函數(shù)的復(fù)制單元格公式引用,2）絕對地址的使用 如果公式運算中，需要某個指定單元格的數(shù)值是固定的數(shù)值，在這種情況下，就必須使用絕對地址引用。所謂絕對地址引用，是指對于已定義為絕對引用的公式，無論把公

20、式復(fù)制到什么位置，總是引用起始單元格內(nèi)的“固定”地址。 在Excel中，通過在起始單元格地址的列號和行號前添加美元符“$”，如$A$1來表示絕對引用。 例如，在如圖4-17所示的例子中，如果將F2中輸入的相對地址改為絕對地址，當(dāng)F2復(fù)制到F3:F9時，會出現(xiàn)如圖4-18所示的結(jié)果，所有的學(xué)生的平均成績都是“李曉科”的平均成績,26,LOGO,1,公式和函數(shù)的復(fù)制單元格公式引用,3）混合地址引用 單元格的混合引用是指公式中參數(shù)的行采用相對引用、列采用絕對引用；或列采用絕對引用、行采用相對引用，如$A3、A$3。當(dāng)含有公式的單元格因插入、復(fù)制等原因引起行、列引用的變化時，公式中相對引用部分隨公式位

21、置的變化而變化，絕對引用部分不隨公式位置的變化而變化。例如，制作九九乘法表。步驟如下： 在B2單元格中輸入“=B$1&*&$A2&=&B$1*$A2”。 將B2復(fù)制到B3:B10。 將B3復(fù)制C3，再將C3復(fù)制到C4:C10。 將C4復(fù)制到D5，再將D5復(fù)制到D6:D10。 依此類推，可完成九九乘法表的制作，如圖4-19所示,27,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,創(chuàng)建迷你圖,什么是迷你圖,迷你圖是工作表單元格中的一個微型圖表（不是對象），可提供數(shù)據(jù)的直觀表示。使用迷你圖可以顯示一系列數(shù)值的趨勢（例如，季節(jié)性增加或減少、經(jīng)濟(jì)周期），或者可以突出顯示最大值和最小值。在數(shù)據(jù)旁

22、邊放置迷你圖可達(dá)到最佳效果。 下面將以一個市場咨詢發(fā)布的20082014中國網(wǎng)購市場交易規(guī)模及預(yù)測數(shù)據(jù)（見圖4-20）為例來創(chuàng)建“較易額”的迷你圖,28,LOGO,1,創(chuàng)建迷你圖,迷你圖的創(chuàng)建,選擇要在其中插入迷你圖中的一個空白單元格或單元格區(qū)域。本例選中I3。 單擊“插入”選項卡，在“迷你圖”功能區(qū)選擇要創(chuàng)建的迷你圖的類型“柱形圖”，如圖4-21所示。彈出如圖4-22所示的對話框。 在“創(chuàng)建迷你圖”對話框時 “數(shù)據(jù)范圍”框中，輸入包含創(chuàng)建迷你圖所基于的數(shù)據(jù)單元格區(qū)域B3:H3?！蔽恢梅秶睘?I$3，單擊“確定”按鈕。注：如果需要在多個單元格創(chuàng)建相同類型的迷你圖，此處也可以選擇一個單元格區(qū)域

23、。 用同樣的方法可在I4單元格中創(chuàng)建一個“同比增大”折線圖，結(jié)果如圖4-23所示,迷你圖的創(chuàng)建,29,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,創(chuàng)建迷你圖,迷你圖的編輯與格式化,在工作表中選中一個或多個迷你圖，將會出現(xiàn)“迷你圖工具”，并顯示“設(shè)計”選項卡。在“設(shè)計”選項卡中包括 “迷你圖”、“類型”、“顯示”、“樣式”和“分組”等多個功能區(qū)，如圖4-24所示,30,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,自動完成,在同一列中，對于在上面單元格曾經(jīng)輸入過的字符，在緊接的單元格如輸入其中的第一個字時，Excel能自動填入其后的字符，如圖

24、4-25所示。當(dāng)在C6單元格中輸入“人”后，在“人”后Excel能自動填入“事部”，并以反白顯示，按【Enter】鍵即可，否則無須理會，繼續(xù)輸入其他字符,31,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,選擇列表,在同一列中反復(fù)輸入相同的幾個字段值，如要反復(fù)輸入“人事部”和“銷售部”?？梢栽谳斎肓恕叭耸虏俊焙汀颁N售部”以后，在待輸入的新單元格上右擊，在彈出的快捷菜單中選擇“從下拉列表中選擇”命令，單元格下方就會彈出一下拉列表框，該列表中記錄了該列出現(xiàn)過的所有數(shù)據(jù)，如“人事部”和“銷售部”，如圖4-26所示，只要從列表中選擇“人事部”即可完成輸入,32

25、,LOGO,1,提高數(shù)據(jù)輸入正確性和效率的方法,利用“自定義序列”自動充填數(shù)據(jù),對于需要經(jīng)常使用的特殊數(shù)據(jù)序列，例如一組多次重復(fù)使用的字符或中文序列號，可以將其定義為一個序列，在輸入表格數(shù)據(jù)時，可使用“自動填充”功能，將數(shù)據(jù)自動輸入到工作表中。 （1）使用自動填充功能之前，必須利用“自定義序列”增加本次要輸入的數(shù)據(jù)系列。 操作步驟如下： 選擇“文件”“選項”命令，彈出“Excel選項”對話框， 選擇“Excel選項”對話框中的“高級”選項，在右側(cè)窗口中單擊“編輯自定義列表”按鈕，如圖4-27所示,33,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,利用

26、“自定義序列”自動充填數(shù)據(jù),在“輸入序列”文本框中輸入“主機”，然后按【Enter】鍵，然后輸入“顯示器”，再次按【Enter】鍵，重復(fù)操作該過程，直到輸入所有的數(shù)據(jù)。 單擊“添加”按鈕，就可以看到自定義的序列已經(jīng)出現(xiàn)在對話框中，如圖4-28所示,34,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,數(shù)據(jù)的自動填充,字符自動填充功能可以把單元格的內(nèi)容復(fù)制到同行或同列的相鄰單元格，也可以根據(jù)單元格的數(shù)據(jù)自動產(chǎn)生一串遞增或遞減序列。例如，在圖4-29中，把光標(biāo)移至C6單元格右下角的填充柄（此時鼠標(biāo)會變成十字形狀），拖動至C8單元格，那么C6單元格的內(nèi)容就被復(fù)

27、制到C7:C8區(qū)域，如圖4-29所示,35,LOGO,1,提高數(shù)據(jù)輸入正確性和效率的方法,序列填充,上面所列的自動填充一般是以列（或以行）為填充對象進(jìn)行有規(guī)律的填充。但對于等比數(shù)列或工作日的自動填充上述方法就很難完成。對于特殊情況可用下面的方法完成。 選擇初始單元格A2，填入第一個序列號，如輸入10001。 單擊“開始”選項卡“編輯”功能區(qū)的“填充”按鈕，選擇“序列”命令，彈出如圖4-30所示的對話框。 在對話框的“序列產(chǎn)生在”選項區(qū)域中選中“列”單選按鈕，之后在“類型”選項區(qū)域中選中“等差序列”單選按鈕。在“步長值”文本框中輸入“3”，終止值填入10019，單擊“確定”按鈕，就能看到如圖4-

28、31所示的序列,36,LOGO,1,提高數(shù)據(jù)輸入正確性和效率的方法,數(shù)據(jù)有效性輸入,1）數(shù)據(jù)有效性的設(shè)置 數(shù)據(jù)有效性的輸入提示信息和出錯提示信息功能，是利用數(shù)據(jù)有效性功能，在用戶選定的限定區(qū)域的單元格，或在單元格中輸入了無效數(shù)據(jù)時，顯示自定義的提示信息或出錯提示信息。 例如，在工作表中，為C4:C11單元格區(qū)域按如下步驟進(jìn)行數(shù)據(jù)有效性設(shè)置。 選擇單元格區(qū)域C4:C11。 選擇“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”功能區(qū)的“數(shù)據(jù)有效性”按鈕，如圖4-32所示。在彈出的對話框中選擇“設(shè)置”選項卡，在“有效性條件”選項區(qū)域的“允許”下拉列表框中選擇“整數(shù)”選項，然后完成如圖4-33所示的設(shè)置,37,LOG

29、O,1,提高數(shù)據(jù)輸入正確性和效率的方法,數(shù)據(jù)有效性輸入,1）數(shù)據(jù)有效性的設(shè)置 單擊“輸入信息”選項卡，在“標(biāo)題”文本框中輸入“成績”，在“輸入信息”文本框輸入“請輸入口語成績”。 單擊“出錯警告”選項卡，在“標(biāo)題”文本框中輸入“錯誤”，在“出錯信息”文本框輸入“必須介于0100之間”。 單擊“確定”按鈕。 設(shè)置完成后，當(dāng)指針指向該單元格時，就會出現(xiàn)如圖4-34所示的提示信息。如果在其中輸入了非法數(shù)據(jù)，系統(tǒng)還會給出警告信息,38,LOGO,1,提高數(shù)據(jù)輸入正確性和效率的方法,數(shù)據(jù)有效性輸入,2）特定數(shù)據(jù)序列 利用數(shù)據(jù)有效性功能，設(shè)置特定的數(shù)據(jù)系列。 例如，在“加班情況登記”工作表中，當(dāng)鼠標(biāo)指針指

30、向C2:C19單元格區(qū)域任意一個單元格的時候，顯示下拉列表框，提供“技術(shù)部”、“銷售部”、“辦公室”三個數(shù)據(jù)供選擇，如圖4-35所示。 設(shè)置特定數(shù)據(jù)序列的操作步驟如下： 選擇單元格區(qū)域C2:C19。 選擇“數(shù)據(jù)”選項卡，單擊“數(shù)據(jù)工具”功能區(qū)的“數(shù)據(jù)有效性”按鈕，在彈出的對話框中選擇“設(shè)置”選項卡，在“有效性條件”選項區(qū)域的“允許”下拉列表框中，選擇“序列”選項，在“來源”文本框中輸入“技術(shù)部,銷售部,辦公室”，需要注意的是各選項之間要用英文的逗號相隔。 單擊“確定”按鈕，如圖4-36所示,39,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,特定區(qū)域內(nèi)

31、一組數(shù)字的快速輸入,例2】請輸入學(xué)生成績表中6個同學(xué)語文、數(shù)學(xué)、英語和生物四科的成績，如圖4-37所示。 鼠標(biāo)在需要輸入的起始單元格C2開始拖動到F9。這個區(qū)域已變成藍(lán)色，只有起始單元格C2是白色。 在白色單元格輸入第一個學(xué)生的語文成績80后按【Tab】鍵，白色區(qū)域右移，再輸入該學(xué)生的數(shù)學(xué)成績，按【Tab】鍵，依此類推，直至全部成績輸入完成,40,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,自定義輸入,在數(shù)據(jù)輸入的過程中有許多的重復(fù)項，但又沒有很強的規(guī)律性，無法使用前面所講的方法進(jìn)行快速錄入。圖4-38所示的第F列的數(shù)據(jù)，如果每一個數(shù)據(jù)都重復(fù)錄入“p

32、00-000-”等內(nèi)容，將是一件很繁瑣的事。解決這個問題可以使用“單元格格式”對話框中的“數(shù)學(xué)”選項卡下的“自定義”。操作步驟如下,41,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,自定義輸入,選中第F列需要自定義格式的單元格區(qū)域，在本例中可選中F2:F7。 在“開始”選項卡下單擊“數(shù)字”功能區(qū)右下角的下拉按鈕彈出如圖4-39所示的對話框,42,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,自定義輸入,在“數(shù)字”選項卡下選“自定義”選項，在“類型”文本框中輸入“p00-000-0000”，單擊“確定”按鈕。

33、在F2:F7中分別輸入：123、24、8即可得到如圖4-38所示的結(jié)果。 自定義格式有很多的格式設(shè)置，限于篇幅本節(jié)不再講述，希望本例能起到拋磚引玉的作用,43,LOGO,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,獲取外部數(shù)據(jù),例3】將記事本文件中的“學(xué)生信息.txt”導(dǎo)入到Excel表中。操作步驟如下： 選擇“數(shù)據(jù)”選項卡，在“獲取外部數(shù)據(jù)”功能區(qū)單擊“自文本”按鈕，如圖4-40所示,44,LOGO,正文 . 模塊四,工作表數(shù)據(jù)輸入與工作表格式化,目,二,1,項,提高數(shù)據(jù)輸入正確性和效率的方法,獲取外部數(shù)據(jù),在彈出如圖4-41所示的對話框中，找到需要導(dǎo)入的數(shù)

34、據(jù)源“學(xué)生信息.txt”，單擊“導(dǎo)入”按鈕彈出“文本導(dǎo)入向?qū)А钡娜齻€步驟，可以分別對源文本的分隔符、導(dǎo)入起始行、目標(biāo)數(shù)據(jù)區(qū)的格式等項目進(jìn)行設(shè)置。單擊“完成”按鈕彈出如圖4-42所示的對話框,45,LOGO,1,提高數(shù)據(jù)輸入正確性和效率的方法,獲取外部數(shù)據(jù),在如圖4-42所示的對話框中設(shè)置導(dǎo)入數(shù)據(jù)的位置為“現(xiàn)有工作表”的$A$1單元格，單擊“確定”按鈕。操作結(jié)果如圖4-43所示,46,LOGO,1,工作表格式化,例4】請對“餐館進(jìn)貨單”進(jìn)行格式設(shè)置，設(shè)置前后的效果如圖4-44所示。具體要求如下： 將A1單元格中的表格標(biāo)題“某餐館進(jìn)貨單”格式設(shè)置為12號宋體加粗，并使用A1:D1單元格合并居中；

35、將A2:D2單元格區(qū)域跨列合并，單元格內(nèi)容右對齊； 將單價(即B4:B14)和金額(即D4:D15)設(shè)置為保留兩位小數(shù)，并在數(shù)據(jù)前加人民幣符號。 將A3:D14單元格區(qū)域的內(nèi)邊框設(shè)置為虛線，外加邊框設(shè)置為雙實線。 將B4:B14區(qū)域中超過5的數(shù)據(jù)設(shè)置為加粗傾斜并加12.5%的淺灰色底紋,47,LOGO,1,工作表格式化,獲取外部數(shù)據(jù),操作步驟如下： 選中A1:D1單元格區(qū)域，在“開始”選項卡中單擊“對齊方式”功能區(qū)中的“合并后居中”按鈕，在“字體”功能區(qū)將字體設(shè)置為“宋體”，字號設(shè)置為“12”，單擊按鈕使字體加粗。 選中A2:D2單元格區(qū)域，在“開始”選項卡，單擊“對齊方式”功能區(qū)中右面的下拉

36、按鈕，在彈出的選項中選“跨越合并”按鈕，再單擊使單元格中的數(shù)據(jù)右對齊,48,LOGO,1,工作表格式化,獲取外部數(shù)據(jù),同時選中B4:B14和D4:D15單元格區(qū)域(方法為：先選中B4:B14單元格區(qū)域，按住Ctrl鍵不放，再選中D4：D15單元格區(qū)域)，在“數(shù)字”功能區(qū)單擊按鈕“中文(中國)”按鈕。 選中A3:D14單元格區(qū)域，右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，在彈出的窗口中單擊“邊框”選項卡，如圖4-45所示。 選中B4:B14單元格區(qū)域，在如圖4-46所示的“樣式”功能區(qū)選擇“條件格式”“突出顯示單元格規(guī)則”“大于”命令，可彈出如圖4-47所示的對話框,49,LOGO,過

37、渡頁,工作表的數(shù)據(jù)統(tǒng)計和分析,輸入Word文稿,過渡頁,50,LOGO,要用好數(shù)據(jù)統(tǒng)計和數(shù)據(jù)分析，首先必須熟悉函數(shù)的使用功能以及參數(shù)設(shè)置規(guī)則，有針對性地利用單元格復(fù)制（地址引用）的相對地址和絕對地址的運用，準(zhǔn)確輸入函數(shù)統(tǒng)計分析用的數(shù)值或數(shù)據(jù)區(qū)域，才能使函數(shù)的統(tǒng)計分析有滿意的運行結(jié)果。“數(shù)據(jù)”選項卡中的排序、篩選、合并計算、模擬分析、分類匯總和“插入”選項卡中的數(shù)據(jù)透視表等分析工具一般是對整個工作表的統(tǒng)計或數(shù)據(jù)分析，所以在統(tǒng)計分析前，要選取連字段名在內(nèi)的工作表的全部區(qū)域,數(shù)據(jù)統(tǒng)計與分析概述,51,LOGO,數(shù)據(jù)統(tǒng)計與分析概述,52,LOGO,求平均分：求考試成績的平均值，用函數(shù)AVERAGE()

38、，結(jié)果放在單元格D11。 函數(shù)AVERAGE()的用法是：AVERAGE(number1,number2，），括號內(nèi)的參數(shù)可以是一組數(shù)字，最多為30個。如要求15、38、90的平均數(shù)，就可以寫AVERAGE(15,38,90），或數(shù)值所在單元格區(qū)域，如本例中，數(shù)學(xué)考試成績的區(qū)域在D2:D10，語文成績在E2:E10，就可用AVERAGE(D2:D10）和AVERAGE(E2:E10）。 （請大家分析數(shù)學(xué)考試中有個字符“缺考”，用了D2:D10區(qū)域，對數(shù)學(xué)的平均數(shù)有沒有影響？回答是沒有，因為區(qū)域內(nèi)只有數(shù)值才能參加統(tǒng)計運算） 在D11中直接輸入=AVERAGE(D2:D10）或者選中D11，單擊編

39、輯欄的插入函數(shù)按鈕，在彈出的對話框中選擇“統(tǒng)計”中的“AVERAGE函數(shù)”選項，在參數(shù)中輸入D2:D10，單擊“確定”按鈕,求學(xué)生每科的平均分、合格率、最高分、最低分、兩科總分,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,53,LOGO,求最高分：用MAX(number1,number2,函數(shù)，用法同平均值。 在D15中輸入=MAX(D2:D10)。 求最低分：用MIN(number1,number2,)函數(shù)。在D16中輸入=MIN(D2:D10)。 求參加考試人數(shù)：用COUNT(value1,value2, .)，求區(qū)域內(nèi)有數(shù)值的單元格個數(shù)，“有數(shù)值”是表示有考試成績，“缺考”不是數(shù)

40、值，是字符。 參加數(shù)學(xué)考試的人數(shù)：在D12中輸入COUNT(D2:D10)。 求合格人數(shù)：用COUNTIF(range,criteria)函數(shù)，求符合條件的單元格個數(shù)，放在D13單元格中,求學(xué)生每科的平均分、合格率、最高分、最低分、兩科總分,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,54,LOGO,求合格率：合格率是參加考試的合格人數(shù)除以參加考試的人數(shù)。本案例數(shù)學(xué)考試人數(shù)在D12，合格人數(shù)在D13，在單元格D14中輸入公式=D13/D12，“/”表示“除以”。 求每個學(xué)生的語文和數(shù)學(xué)兩科成績總分，有兩個方法： 方法1：如在F2單元格中輸入=D2+E2。 方法2：用函數(shù)SUM()。本題

41、是在F2中輸入=SUM（D2:E2,求學(xué)生每科的平均分、合格率、最高分、最低分、兩科總分,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,55,LOGO,將語文成績高于80分的學(xué)生選出。 操作步驟如下： 選中數(shù)據(jù)區(qū)域A1:F10或其中的任意一個單元格。 選擇“數(shù)據(jù)”選項卡，在“排序與篩選”功能區(qū)單擊“篩選”按鈕，此時每一個字段名的右邊都有一個下拉按鈕。 如圖4-51所示，單擊“語文”中的下拉按鈕，在下拉菜單中選擇“數(shù)字篩選”-“大于”命令，打開圖4-52 所示的對話框。 在對話框左邊的輸入框中選擇“大于”選項，在右邊的輸入框中輸入80，單擊“確定”按鈕,挑選出符合分析條件的學(xué)生,統(tǒng)計函數(shù)、

42、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,56,LOGO,將語文成績大于60分，且小于90分的學(xué)生選出。 分析：語文成績大于60分，且小于90分，是同一字段列中的“與”條件。因為是在同一列，可以用“自動篩選”完成。 操作步驟如下： 單擊“數(shù)據(jù)”選項卡，在“排序與篩選”功能區(qū)單擊“自動篩選”按鈕，取消先前的篩選結(jié)果。 選中A1:F11單元格區(qū)域或其中的任意一個單元格。 單擊“數(shù)據(jù)”選項卡，在“排序與篩選”功能區(qū)單擊“自動篩選”按鈕。單擊“數(shù)字篩選”-“自定義篩選”按鈕。 在圖4-54所示的對話框中分別輸入大于60，第二行輸入小于90，選中“與”單選按鈕，單擊“確定”按鈕，結(jié)果如圖4-55所示,挑

43、選出符合分析條件的學(xué)生,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,57,LOGO,將語文成績大于70分的男生選出。 分析：要將符合兩個以上不同列的條件的數(shù)據(jù)篩選出來，如果使用自動篩選來完成，需要對“語文”和“性別”兩個字段分別進(jìn)行篩選。方法與上兩例相似，在此不再贅述。 在“高級篩選”操作中，必須在工作表的一個位置設(shè)置“條件區(qū)域”。 “與”條件：將兩個條件放在同一行，表示語文成績大于70分的男生，如圖5-56所示。 “或”條件：將兩個條件放在不同行，表示語文成績大于70的學(xué)生或者是男生，如圖4-57所示,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,58,將語文成績大于70分的男

44、生選出。 操作步驟如下： 輸入條件區(qū)域：在D12處輸入“語文”，E12處輸入“性別”，對照在其下面的單元格D13中輸入“70”和E13中輸入“男”。 選中A1:F11單元格區(qū)域或其中的任意一個單元格。 單擊“數(shù)據(jù)”選項卡，在“排序與篩選”功能區(qū)單擊“高級”按鈕。彈出如圖4-58所示的對話框。 在打開的對話框中選中“將篩選結(jié)果復(fù)制到其他位置”單選按鈕。 如果列表區(qū)域為空白，可單擊“列表區(qū)域”右邊的按鈕，用鼠標(biāo)從條件區(qū)域的A1拖動到F10，輸入框中出現(xiàn)“$A$1:$F$10”。 再單擊“條件區(qū)域”右邊的按鈕，用鼠標(biāo)從條件區(qū)域的D12拖動到E13，輸入框中出現(xiàn)“$D$12:$E$13”。 單擊“復(fù)制

45、到”右邊的按鈕后，選擇篩選結(jié)果顯示區(qū)域的第一個單元格，如A15。 單擊“確定”按鈕，結(jié)果如圖4-59所示,挑選出符合分析條件的學(xué)生,59,LOGO,對兩科總分從高分到低分進(jìn)行排序。（用“數(shù)據(jù)”功能區(qū)的“排序”命令按鈕） 對兩科總分進(jìn)行排位操作。（用函數(shù)BANK()操作。） 對兩科總分高于和等于150分的同學(xué)設(shè)置“錄取”，小于150分的同學(xué)設(shè)置“不錄取”。（用函數(shù)IF()操作,請分別對圖4-48的成績表完成以下操作任務(wù),統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,60,LOGO,工作表的數(shù)據(jù)統(tǒng)計和分析,目,三,例5】對兩科總分從高分到低分進(jìn)行排序。 單擊F1:F10數(shù)據(jù)區(qū)域的任意一個單元

46、格。 單擊“數(shù)據(jù)”選項卡中“排序和篩選”功能區(qū)的“排序”按鈕（如果是升序則單擊按鈕）即可，如圖4-61所示,項,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,61,LOGO,如果是對多個關(guān)鍵字排序或者需要按別的方式排序，需要單擊“排序”按鈕，彈出圖4-62所示的對話框。在該對話框中： 單擊“添加條件”按鈕可設(shè)置多個關(guān)鍵字排序。 單擊“排序依據(jù)”按鈕可以設(shè)置按“數(shù)值”或“單元格顏色”或“字體顏色”排序。 單擊“次序”按鈕可以分別對每一個關(guān)鍵字設(shè)置排序方式。 單擊“選項”按鈕可以將排序“方向”設(shè)置為“按列排序”或“按行排序”。將排序“方法”設(shè)置為“字母”排序或“筆劃”排序,統(tǒng)計函數(shù)、IF函數(shù)

47、、BANK函數(shù)、排序、篩選的應(yīng)用,62,例6】對兩科總分進(jìn)行排位操作。（用RANK()函數(shù)） 在單元格G2中輸入函數(shù)RANK()，彈出圖4-63所示的對話框。 在第一個輸入框中輸入“F2”，即需要排位的數(shù)值所在的單元格。在第二個輸入框中輸入需要對它進(jìn)行排位的數(shù)值區(qū)域F2:F10，但必須用絕對地址表示，要寫成$F$2:$F$10。 單擊“確定”按鈕，結(jié)果如圖4-64所示。 選取G2后，用填充柄下拉，結(jié)果如圖4-65所示,63,LOGO,例3】對兩科總分高于或等于150分的同學(xué)添加“錄取”，小于150分的同學(xué)添加“不錄取”。（用IF()函數(shù)） 本例有兩種解決方法： 方法1：在G2單元格中直接輸入：

48、“=IF(F2=150,“錄取”,“不錄取”)”。 注意：輸入的字符常量都要有英文的雙引號。 方法2：選中G2單元格，插入IF函數(shù)IF()，在如圖4-66所示的對話框第一個輸入框中輸入“F2=150”，在第二個輸入框中輸入“錄取”，第三個輸入框中輸入“不錄取”。單擊“確定”按鈕，結(jié)果如圖4-67所示。（在該對話框中輸入的字符常量不必寫上英文的雙引號，系統(tǒng)會自動添加,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,64,LOGO,舉一反三：以工資表求“預(yù)扣基金”為例，IF()的判斷條件是兩個以上，應(yīng)該如何操作？ 例如在應(yīng)發(fā)工資中，如果應(yīng)發(fā)工資在4000以上，扣500做預(yù)扣基金；應(yīng)發(fā)工資在30

49、004000，扣400；20003000，扣300。在F2單元格中輸入完整的IF參數(shù)： =IF(E24000,500,IF(E23000,400,IF(E22000,300) E2是“應(yīng)發(fā)工資”列的第一個單元格。請大家注意三個IF參數(shù)格式的標(biāo)準(zhǔn)寫法，不能有錯誤，結(jié)果如圖4-68所示,統(tǒng)計函數(shù)、IF函數(shù)、BANK函數(shù)、排序、篩選的應(yīng)用,65,LOGO,題目：對圖4-69所示的工資表從入職時間算出該職工的在職工齡。如果應(yīng)發(fā)工資在4000以上，扣應(yīng)發(fā)工資的20%做預(yù)扣基金；應(yīng)發(fā)工資在30004000，扣應(yīng)發(fā)工資的15%做預(yù)扣基金；20003000，扣應(yīng)發(fā)工資的10%做預(yù)扣基金。計算每一位職工的實發(fā)工

50、資，并按部門分類求出各部門的實發(fā)工資總數(shù)和全體職工的實發(fā)工資總額。 在職工齡的計算步驟： 在D列的右邊增加一列，在E2中輸入“工齡”。 在E3中輸入=YEAR(NOW()-YEAR(D3)，單擊“確定”按鈕，再將E3中的分工復(fù)制到全列，結(jié)果如圖4-70所示,日期函數(shù)和分類匯總的應(yīng)用,從入職時間算出該職工的在職工齡,66,LOGO,工作表的數(shù)據(jù)統(tǒng)計和分析,目,三,操作：在G3中輸入“=IF(F34000,F3*20%,IF(F33000,F3*15%,IF（F32000,F3*10%)”。單擊編輯欄中的“”，結(jié)果如圖4-71所示,預(yù)扣基金的計算,項,日期函數(shù)和分類匯總的應(yīng)用,67,LOGO,工作

51、表的數(shù)據(jù)統(tǒng)計和分析,目,三,操作：在單元格H3中輸入表達(dá)式=F3-G3，結(jié)果如圖4-72所示,實發(fā)工資的計算,項,日期函數(shù)和分類匯總的應(yīng)用,68,LOGO,工作表的數(shù)據(jù)統(tǒng)計和分析,目,三,操作步驟如下： 選取A2:F12區(qū)域中的任意一個單元格。先對“部門”進(jìn)行排序(“數(shù)據(jù)”-“排序”)，使相同的部門排在一起，以便于按“部門”進(jìn)行匯總。 單擊“數(shù)據(jù)”選項卡“分級顯示”功能區(qū)的“分類匯總”按鈕，打開對話框如圖4-73所示，在“分類字段”下拉列表中選擇“部門”選項，在“匯總方式”下拉列表中選擇“求和”選項，在“選定匯總項”下拉列表中選擇“實發(fā)工資”復(fù)選框，單擊“確定”按鈕，運算結(jié)果如圖4-74所示,

52、各部門實發(fā)工資總數(shù)和全體職工的實發(fā)工資總額的計算,項,日期函數(shù)和分類匯總的應(yīng)用,69,LOGO,題目：制作一個文具公司的季度辦公用品銷售表，并計算每個品種的利潤和銷售額。用數(shù)據(jù)透視表以銷售人員、品種名稱和季度對銷售額進(jìn)行求和分析。原工作表如圖4-75所示,VLOOKUP函數(shù)和數(shù)據(jù)透視表的應(yīng)用,日期函數(shù)和分類匯總的應(yīng)用,70,LOGO,VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)中： lookup_value是銷售表中要查找的第一個品種所在的單元格，如本例是B2。 table_array是被查找的價格表所在的區(qū)域，

53、如本例是在D14:G19，但寫到參數(shù)區(qū)時，必須用絕對地址表示$D$14:$G$19。也可用選擇“插入”“名稱”命令定義區(qū)域D14:G19為“價格表”后，在參數(shù)table_array輸入框中輸入“價格表”。 col_index_num是價格參數(shù)表區(qū)域中要輸入的列數(shù)，如第一個操作要輸入“單位”，這個“單位”在D14:G19區(qū)域中的第2列，所以輸入2。如要輸入進(jìn)貨價，這個“進(jìn)貨價”在D14:G19區(qū)域中的第3列，則輸入3，輸入銷售價，則輸入4。 range_lookup中輸入“FALSE”。這樣就完成VLOOKUP()函數(shù)對品種名稱的復(fù)制操作的定義,VLOOKUP函數(shù),日期函數(shù)和分類匯總的應(yīng)用,71

54、,LOGO,單位”列的完整的輸入是：在E2中輸入=VLOOKUP(B2,$D$14:$G$19,2,false)。 在D14:G19區(qū)域建立如圖4-76所示的價格表，并按圖輸入所有數(shù)據(jù)。 在單元格E2中輸入=VLOOKUP(B2,$D$14:$G$19,2,false)，用拖動填充柄復(fù)制到E11，如圖4-76所示,VLOOKUP函數(shù),日期函數(shù)和分類匯總的應(yīng)用,72,LOGO,同理，要輸入“進(jìn)貨價”，在單元格F2中輸入=VLOOKUP(B2,$D$14:$G$19,3,false)，并拖動填充柄復(fù)制到F11。 要輸入“銷售價”，在單元格G2中輸入=VLOOKUP(B2,$D$14:$G$19,4

55、,false)，用拖動填充柄復(fù)制到G11。 求“利潤”。在H2中輸入=D2*(G2-F2)，用拖動填充柄復(fù)制到H11。 求“銷售額”，在I2中輸入=D2*G2，用拖動填充柄復(fù)制到I11,VLOOKUP函數(shù),日期函數(shù)和分類匯總的應(yīng)用,73,LOGO,數(shù)據(jù)透視表是比“分類匯總”更為靈活的一種數(shù)據(jù)分析方法。它可以同時靈活變換多個需要統(tǒng)計的字段對一組數(shù)值進(jìn)行統(tǒng)計分析，統(tǒng)計可以是求和、計數(shù)、平均值、最大值、最小值、乘積、數(shù)值計數(shù)、標(biāo)準(zhǔn)偏差、總體標(biāo)準(zhǔn)偏差、方差、總體方差。 題目：制作一個數(shù)據(jù)透視表，用圖4-77所示的辦公用品銷售表中的品種名稱、銷售人員、季度對銷售額作“求和”統(tǒng)計。 操作步驟： 選擇操作區(qū)

56、域A1:I11中的任意一個單元格。 在“插入”選項卡上的“表格”功能區(qū)中，單擊“數(shù)據(jù)透視表”按鈕，彈出如圖4-78所示的對話框,數(shù)據(jù)透視表,日期函數(shù)和分類匯總的應(yīng)用,圖4-78 “創(chuàng)建數(shù)據(jù)透視圖表”對話框,74,LOGO,工作表的數(shù)據(jù)統(tǒng)計和分析,目,三,對要分析的數(shù)據(jù)可以是當(dāng)前工作簿中的一張數(shù)據(jù)表或者一張表中的部分區(qū)域，甚至可以是外部數(shù)據(jù)源。數(shù)據(jù)透視表的位置可以放在現(xiàn)有工作表中也可以新建一張工作表來單獨存放數(shù)據(jù)透視表。 本例按圖示設(shè)置后單擊“確定”按鈕，彈出如圖4-79所示的布局對話框,數(shù)據(jù)透視表,項,日期函數(shù)和分類匯總的應(yīng)用,75,LOGO,在如圖4-79所示的布局的對話框中，將“銷售人員”

57、拖到“篩選字段”框，將“品種名稱”拖到“行”區(qū)域，將“季度”拖到“列”區(qū)域，將“銷售額”拖到“數(shù)據(jù)”區(qū)域，結(jié)果如圖4-80所示,數(shù)據(jù)透視表,日期函數(shù)和分類匯總的應(yīng)用,76,LOGO,如果在如圖4-79所示的布局對話框中將“季度”拖到“篩選字段”框，將“銷售人員”拖到“行”區(qū)域，將“品種名稱”拖到“列區(qū)域”，數(shù)據(jù)區(qū)仍是“銷售額”，得出透視表的另一個結(jié)果，如圖4-81所示,數(shù)據(jù)透視表,日期函數(shù)和分類匯總的應(yīng)用,77,LOGO,建立透視表后，可以通過三種方式進(jìn)行編輯和格式化。例如將求銷售額的和改為求平均值。 方法1：使用“數(shù)據(jù)透視表字段列表”，如圖4-82所示?？梢酝ㄟ^該對話框更改透視表布局、設(shè)計分

58、類匯總方式等操作,數(shù)據(jù)透視表,日期函數(shù)和分類匯總的應(yīng)用,78,LOGO,工作表的數(shù)據(jù)統(tǒng)計和分析,目,三,方法2：通過“數(shù)據(jù)透視表工具”來操作。選中數(shù)據(jù)透視表會自動彈出“數(shù)據(jù)透視表工具”，如圖4-83所示。“選項”選項卡可用于透視表的編輯，“設(shè)計”選項卡可用于透視表的格式化。 方法3：通過快捷菜單，選中操作對象，右擊彈出對該對象的相關(guān)操作,數(shù)據(jù)透視表,項,日期函數(shù)和分類匯總的應(yīng)用,79,LOGO,單擊數(shù)據(jù)透視表數(shù)據(jù)區(qū)域內(nèi)的任意一個單元格，激活“數(shù)據(jù)透視表工具”，單擊“選項”選項卡，如圖4-83所示。 單擊“選項”功能區(qū)中的“排序和篩選”組中的“插入切片器”按鈕，打開“插入切片器”對話框。根據(jù)要篩

59、選數(shù)據(jù)的類別，選中要創(chuàng)建切片器的字段名稱“季度”、“品種名稱”和“銷售人員”復(fù)選框，如圖4-84所示。 單擊“確定”按鈕，將在當(dāng)前工作表中創(chuàng)建與所選字段對應(yīng)的切片器，結(jié)果如圖4-85所示,切片器,日期函數(shù)和分類匯總的應(yīng)用,80,LOGO,此時可以單擊不同切片器中的選項來篩選當(dāng)前數(shù)據(jù)透視表，其效果與直接單擊數(shù)據(jù)透視表中的字段篩選按鈕是相同的，只是在切換器中進(jìn)行該操作更直觀也更方便。例如：單擊“李四”、“一季度”和“傳真機”，使用切片器對數(shù)據(jù)透視表篩選后的結(jié)果如圖4-86所示，它篩選出了所有“李四”在“一季度”銷售的“傳真機”情況,日期函數(shù)和分類匯總的應(yīng)用,切片器,81,LOGO,在銀行每月存款1

60、000元，銀行每年的利息是2.25，求2年后可以得本金和利息共多少？ 分析：求本息可用函數(shù)FV()計算。FV()函數(shù)基于固定利率及等額分期付款方式，返回某項投資的未來值。FV()函數(shù)的參數(shù)格式是FV(rate,nper,pmt,pv,type)。其中： rate為各期（以月計息）利率，是一固定值。如本案例年利率是2.25%,月息就為（2.25/12）%。 nper為總存款（或貸款）期。本例為2年，nper值為24。 pmt為各期所應(yīng)交的存款（或得到）的金額，本案例為1000元。其數(shù)值在整個年金期間（或投資期內(nèi)）保持不變。本案例是求存款，pmt值應(yīng)寫-1000，計算結(jié)果是正數(shù)。如果是借款，寫10