第11章

電子表格處理軟件ExcelExcel概述工作表格式設置工作表的編輯工作薄管理數據管理和圖表化1電子表格(中文Excel)

用途：以二維表格作為基本操作界面，供用戶方便地填寫和編輯表格內容，可對表格中的數據完成很多數據處理功能，如進行各種數學運算，制作各種統計圖表和進行一些數據分析工作

本章基本內容

Excel基本知識工作表的編輯和格式化公式與函數圖表的制作數據管理與統計其他有關功能

2

概述

Excel的啟動和退出Excel窗口

當前工作簿名稱為Book1；行號欄和列標欄工作表標簽Sheet1,Sheet2等標題欄菜單欄“常用”工具欄“格式”工具欄編輯欄工作表標簽Office助手狀態欄滾動條任務窗格“全選”按鈕3

1.工作簿

工作簿由一至多個相關的工作表組成

2.工作表工作表是由行和列組成的一張表格。工作表中的行以數字1，2，3…來表示，列以一個英文字母A，B，C…來表示每個工作表最大可達256列×65,536行切換當前工作表的方法3.單元格單元格是指工作表中的一個格子。每個單元格都有自己的坐標(即行列位置)，也稱為單元格地址。單元格地址表示為：列標+行號行號 單元格及其地址(如A1，B3等)4.單元格區域一組相鄰的呈矩形的單元格。如A1:C3，B2:D5等)基本概念

工作表工作表工作表......工作簿4示例統計學生的總評成績

5工作簿的基本操作新建一個工作簿

單擊“常用”工具欄上的“新建”按鈕選擇“文件”菜單中的“新建”命令，打開“新建工作簿”任務窗格，可以從中選擇一個命令來創建新工作簿新建的工作簿文件取名為Book2,Book3等文件名默認的擴展名為.xls

在工作表中輸入數據

1.選定單元格

2.輸入/編輯數據進入修改狀態：按F2或雙擊6

編輯欄用來指明當前被編輯單元的名稱和編輯內容

在單元格中輸入數據時，鍵入的內容會同時出現在單元格和編輯欄中

當前單元格內容函數按鈕確定按鈕取消按鈕當前單元格地址編輯欄A1B2當前單元格7數據類型

處理數據時，會遇到各種不同類型的數據。例如，一串文本(如姓名)、數值(如成績、年齡和體重等)、邏輯值(如是否大學畢業)等等五種基本數據類型

數值型、文本型、日期/時間型、邏輯型和數組型

(1)數值型數據(簡稱數字)用0～9和特殊字符構成的數字。右對齊科學記數法，如1.23457E+11、3.27835E-08

(2)文本型數據任何數字、字母、漢字及其他字符的組合。左對齊

(3)日期/時間型數據表示各種不同格式的日期和時間如21-Oct-96、1999-5-29等8

(4)邏輯型數據描述事物之間的成立與否的數據只有兩個值：FALSE(假值)和TRUE(真值）

(5)數組型數據作為一個整體處理的若干個數據通常一個數組用一個區域表示數據兩種形式：常量固定不變的量

公式可用于計算9輸入數據時要注意的一些問題

(1)輸入分數，應在分數前加入0和空格。如“02/3”(2)如果輸入的數字有效位超過單元格的寬度，必須調整列寬(3)把輸入的數字作為文本處理，可在數字前加單引號。如“′1234”(4)在單元格中既要輸入日期又要輸入時間，則用空格分隔開(5)輸入日期時，可按yyyy-mm-dd的格式輸入(6)在單元格中輸入當前系統日期時按Ctrl+；鍵輸入當前系統時間時按Ctrl+Shift+；鍵(7)希望以12小時制來顯示時間，需在鍵入的時間后加AM或PM如“8:20PM”(8)按Alt+Enter組合鍵可強制在某處換行(9)在一個選定區域內輸入數據，輸入結束后按Ctrl+Enter組合鍵確認10保存工作簿打開工作簿

例建立如下所示的工作表內容11工作表的編輯選定單元區

“先選定，后操作”是Excel重要的工作方式1.選定單元格常用的方法有：(1)單擊；(2)使用光標移動鍵

2.選定一個單元格區域3.選定不相鄰的多個區域4.選定行或列5.選定整個工作表6.撤消選定12單元格的插入和刪除

1.插入空白單元格或區域

(1)選定要插入新單元格的位置或區域。(2)選擇“插入”菜單中的“單元格”命令，打開對話框

(3)從對話框中選擇一種合適的插入方式13

插入空白行或列

例在工作表中的第4行處插入2行

3.刪除單元格或區域4.刪除行或列

14數據的復制、移動和清除

1.復制數據

方法一：使用“常用”工具欄上的命令按鈕復制數據方法二：用鼠標拖放來復制數據

例把A3:B4區域的數據復制到G4:H5區域中

2.移動數據

移動單元格區域的數據，其操作方法與復制操作基本相同

3.清除數據15

4.選擇性粘貼

一個單元格含有數值、格式、公式等多種特性，數據復制時通過“選擇性粘貼”可以只需要復制它的部分特性

先將數據復制到剪貼板，再選定待粘貼目標區域中的第一個單元格，選擇“編輯”菜單中的“選擇性粘貼”命令，打開對話框。從對話框中選擇相應選項即可16表格數據的查找與替換撤消與恢復17

1文本格式的設置

字體、字號、字形、對齊方式等

2數字格式的設置

例如，數字1234.56在格式#,###.#下為1,234.6

3調整單元格的行高和列寬

方法一：采用菜單命令選擇“格式”菜單中的“行”命令(或“列”命令)根據需要進行設置方法二：采用拖放操作拖動鼠標來調整行高(或列寬)

單元格格式設置18

表格框線的設置

使用“格式”工具欄上的“邊框”各按鈕，或執行“格式”菜單中的“單元格”命令，可以為表格設置線條和邊框線單擊“格式”工具欄上“邊框”按鈕右邊的小箭頭，屏幕出現“邊框”各小圖標

19公式和函數

公式的使用公式是用運算符將數據、單元格地址、函數等連接在一起的式子，以等號“=”開頭=5*10-20常數運算=A6+B1對單元格A6和B1中的值相加=SQRT(10+A2)使用Excel函數，SQRT表示求開方根1.運算符算術運算符、文本運算符和關系運算符20

=8^3*25%表示8的立方再乘以0.25，結果為128=5*8/2^3-B2/4若單元格B2的值是12，則結果為2=”計算機”&”電腦”運算結果是”計算機電腦”=A1&B2把單元格A1和A2中的文本進行連接而生成一個新的文本=B2＜42比較結果為TRUE=B2＞=32比較結果為TRUE=B2＜30比較結果為FALSE=A1+80假設A1為日期型數據，則A1+80表示日期A1加上80天

公式舉例21

公式的輸入和復制

當在一個單元格中輸入一個公式后，Excel會自動加以運算，并將運算結果存放在該單元中。以后當公式中引用的單元格數據發生變動時，公式所在單元格的值也會隨之變動

22

三種表示方式(1)相對地址：以列標和行號組成，如A1，B2，C3等公式復制時，若引用公式的單元格地址發生變動，公式中的相對地址會隨之變動(2)絕對地址：以列標和行號前加上符號“$”構成，如$A$1，$B$2等。公式復制時，公式中的絕對地址保持不變(3)混合地址：它是上述兩種地址的混合使用方式如$A1(絕對列相對行)，A$1(相對列絕對行)等。公式復制時，公式中相對行和相對列部分會隨引用公式的單元格地址變動而變動，而絕對行和絕對列部分保持不變4.出錯信息當公式(或函數)表達不正確時，系統將顯示出錯信息單元格地址表示方式23

格式：=<函數名>(<參數1>,<參數>,…..)舉例：=SUM(C2:C6) =AVERAGE(A1:A3,C2:C6)SUM求和數AVERAGE求平方值MAX求最大值MIN求最小值COUNT求數值數據的個數COUNTIF條件求數值數據的個數FREQUENCY頻率分布統計IF條件選擇函數的使用

24

函數應用舉例

例計算基本工資總數，并將結果存放在單元格C7中(1)單擊單元格C7，使之成為活動單元格(2)鍵入公式“=SUM(C2:C6)”，并按回車鍵

例計算補貼總數和扣除總數，并將結果分別存放在單元格D7和E7中(1)選定D2:E7區域(2)單擊“自動求和”按鈕∑，即可對這兩列分別求和，和數分別存放在兩列的最后一個單元格上25

函數應用舉例

例計算基本工資的平均值，并將結果存放在C8中例利用函數MAX求出基本工資數的最大值，并將結果存放在單元格C9中例假設單元格A1，A2，A3，A4的內容分別為1，2，空，“ABC”，則COUNT(A1:A4)的值為2例若要求出基本工資數大于等于500的職工人數，可以采用：=COUNTIF(C2:C6,”>=500”)例在工作表(A1:D9)中，要求出班號為21的班學生人數，可以采用：=COUNTIF(D2:D9,”21”)或=COUNTIF(D2:D9,D3)26

格式：IF(logical_test,value_if_true,value_if_false)功能：本函數對比較條件式(logical_test)進行測試，如果條件成立，則取第一個值(value_if_true)，否則取第二個值(value_if_false)例已知單元格E3中存放考試分數，現要根據該分數判斷學生是否及格=IF(E3<60,”不及格”,”及格”)例5.16成績等級與分數的關系：成績≥80——優良；60≤成績＜80——中；成績＜60——不及格。假設成績存放在單元格D3中，則可以采用如下函數來實現判斷和取得等級信息：=IF(D3＞=80,”優良”,IF(D3＞=60,”中”,”不及格”))條件選擇函數IF

27

圖表是工作表的一種圖形表示，它使工作表中數據表示形式更為清晰直觀。當工作表中的數據被修改時，Excel會自動更新相關的圖表內容圖表的基本知識可以生成嵌入式圖表和圖表工作表數據源中的數據可以按行或者按列分成若干個數據系列依據工作表區域A2:D6中的數據繪制的一個圖表

圖表的制作28

柱形圖圖表。有3個數據系列，分別對應于數據源中的3個列：B2:B6(計算機系)，C2:C6(電子系)，D2:D6(信管系)。

每個數據系列中都有4個數據點，因而構成4個數據分類:99級、00級、01級和02級圖表的基本元素圖表標題繪圖區圖表區域數值軸標題數值軸分類軸數據點數據系列圖表標題圖例標示圖例網絡線分類軸標題29

Excel提供了多種圖表類型，有平面圖表，也有立體圖表。常用的圖表類型主要有柱形圖、折線圖、餅圖和XY散點圖等(1)柱形圖：柱形圖是用柱形塊表示數據的圖表，通常用于反映數據之間的相對差異(2)餅圖:餅圖用于表示部分在整體中所占的百分比，能顯示出部分與整體的關系(3)折線圖:折線圖是用點以及點與點之間的連成的折線表示數據的圖表，它可以描述數值數據的變化趨勢(4)XY散點圖:散點圖中的點一般不連續，每一點代表了兩個變量的數值，適用于分析兩個變量之間是否相關

圖表類型30使用圖表向導來制作圖表

例利用1996年的數據來制作“生產產值表”柱形圖先選定區域A1:B5(其中包括列標題)，然后單擊“常用”工具欄上的“圖表向導”按鈕。用戶可以在“圖表向導”指引下一步一步地實現圖表制作31在圖表中增加數據系列

圖表完成后，用戶還可以對它加入同類型的數據系列例將1997年某工廠各車間的生產產值加入到例5.19所生成的圖表中

32圖表的調整

圖表完成后，可以改變圖表的大小、位置、類型等(1)改變圖表的位置(2)改變圖表的大小(3)改變圖表類型(4)改變圖表的圖案和字體(5)刪除圖表33

數據管理與統計Excel數據庫又稱數據清單，它按行和列來組織數據(二維表)行————記錄；列————字段數據清單的建立記錄增/刪/改直接鍵入/修改/刪除利用“記錄單”輸入/修改/刪除記錄的篩選使用到各種運算符西文符號比較符:=,>,<,<=,>=,<>邏輯符:AND(與),OR(或)通配符:?(代表單個字符),*(代表多個字符)34舉例:成績小于60:成績<60成績大于或等于90:成績>=90成績在60～80之間:成績>=60AND成績<=80判斷:●成績≥80的班11的學生A.成績>=80OR班號=11B.成績>=80AND班號=11●班11及班21的學生A.班=11OR班號=21B.班=11AND班號=21●班11所有不及格的學生A.成績<60OR班號=11B.成績<60AND班號=11351.自動篩選

自動篩選采用簡單條件來快速篩選記錄，將不滿足條件的記錄暫時隱藏起來，而將滿足條件的記錄顯示在工作表上通過“數據”菜單中“篩選”命令的“自動篩選”來實現的例在上述成績表中，篩選出成績大于等于80分的記錄選定數據清單，選擇“數據”菜單中的“篩選”命令從“篩選”級聯菜單中選擇“自動篩選”選項，此時在標題欄中每個字段名右邊出現了一個下拉箭頭單擊“成績”字段名的下拉箭頭，從下拉列表單擊“自定義…”選項，打開“自定義自動篩選方式”對話框本題篩選條件：成績＞=80。設置如下36

例在上述成績表中，查找成績在70～90分之間的記錄本例題篩選條件是：成績＞=70AND成績＜=90，因此在“自定義自動篩選方式”對話框中設置如下37

2.高級篩選

高級篩選采用復合條件來篩選記錄，并允許把滿足條件的記錄復制到另外的區域，以生成一個新的數據清單先建立條件區域。條件區域的第一行為條件字段標題，第二行開始是條件行(1)同一條件行的條件互為“與”(AND)的關系，表示篩選出同時滿足這些條件的記錄例查找成績大于等于80分，且班號為“11”的所有記錄篩選條件：成績＞=80AND班號=11，條件區域表示：成績班號>=801138

(2)不同條件行的條件互為“或”(OR)的關系，表示篩選出滿足任何一個條件的記錄例查找英語和計算機課程中至少有一科成績大于90分的記錄英語＞90OR計算機＞90，條件區域表示：(3)對相同的列(字段)指定一個以上的條件，或條件為一個數據范圍，則應重復列標題例查找成績大于等于60分，并且小于等于90分的姓“林”的記錄英語計算機>90

>90姓名成績成績林*>=60<=9039判斷(條件區域)●班11及班21的所有不及格學生A.(成績<60AND班號=11)OR(成績<60AND班號=21) B.(成績<60AND班號=11)AND(成績<60AND班號=21)成績班號<6011<6021

與或40判斷:●成績在60～80的班11學生成績>=60AND成績<=80AND班號=11

成績班號>=6011<=8011成績班號<=6011>=8011成績成績班號>=60<=8011與與或與或與41

例在成績表中，查找11班及21班中成績不及格的所有記錄篩選條件為：(成績＜60AND班號=11）OR（成績＜60AND班號=21)42記錄排序

按關鍵值從小到大(或從小到大)的順序進行排列排序時，最多可以同時使用三個關鍵字例將成績表按“成績”從高到低進行排序(1)選定數據清單(2)選擇“數據”菜單中的“排序”命令，打開“排序”對話框(3)設置對話框43

先要對數據清單按分類的關鍵字進行排序例在成績表中，按“班號”字段分類匯總“成績”的平均分(即統計出各個班的平均分)

分類匯總4445

格式：〈函數名〉(database,field,criteria)database指定數據清單的單元格區域；field指定函數所使用的字段，可以用字段代號(如1，2，…)表示，criteria指定條件例求出11班的平均分,使用的函數為:DAVERAGE(A1:D9,3,F1:F2)數據庫函數46重新命名重新命名工作表：右擊工作表標簽，從快捷菜單中選擇“重命名”命令，再鍵入新的名字重新命名單元格和單元格區域使用多工作表1.選定工作表2.插入工作表3.刪除工作表4.工作表的復制和移動

其他有關功能47數據自動填充

使用數據自動填充功能，可以在工作表中快速地輸入有一定規則的數據1）填充序列Excel能夠自動填充日期、時間及數字序列，包括數字和文本的組合序列

例如：星期一，星期二，…，星期日一月，二月，…，十二1，2，3，…48在單元格A1:A10中填入序列數字10,13,16,…,37(1)在單元格A1中輸入起始數“10”(2)選定區域A1:A10(3)選擇“編輯”菜單中的“填充”命令(4)從“填充”級聯菜單中選擇“序列”選項，打開“序列”對話框(5)選擇“序列產生在”為“列”，“類型”為“