電子表格-Excel技能要點

《大學計算機基礎》2010第4章文檔處理電子表格——Excel技能要點Excel主要功能Excel2003操作環(huán)境Excel基本操作公式與函數Excel圖表數據處理教學基本要求●學生作品欣賞進出口總值動態(tài)分析學習成績統(tǒng)計公司產品銷售統(tǒng)計學生經濟調查表北京空氣質量評述圖表Excel2003操作環(huán)境Excel環(huán)境參數每個工作簿包含3個工作表，最多255個工作表。每個工作表256列、65536行Excel提供了16種不同格式的工作表每個單元格可存放65536個字符的文本信息行號編碼1～65536列標編碼A、B、……Z、AA、AB、……IVExcel提供了14類100多種基本的圖表True作為數值是1，False作為數值是0Excel提供有300多個內部函數在工具欄中排放著“常用”和“格式”工具欄中常用的菜單命令。Excel共有十幾個工具欄（在“視圖→工具欄”中)，可以執(zhí)行不同功能的各種操作。例如，窗體、控件工具箱、繪圖、圖表、數據透視表等。Excel地址地址：單元格及區(qū)域的位置標識符Excel基本操作創(chuàng)建工作簿、工作表選定操作對象設置單元格屬性、數據類型輸入數據、填充數據編輯操作（增、刪、改、復制等）格式化操作（字型、字體、字號、顏色等）創(chuàng)建工作簿、工作表創(chuàng)建工作簿選擇執(zhí)行“開始”→“程序”→“MicrosoftOffice”→“MicrosoftOfficeExcel”菜單命令，打開Excel窗口，即自動創(chuàng)建默認文件名為“BOOK1.XLS”的工作簿選擇執(zhí)行“文件”→“另存為”命令，將當前工作簿保存到用戶指定的文件名創(chuàng)建工作表每個新建的工作簿都包含3個默認文件名為“Sheet1”、“Sheet2”和“Sheet3”的工作表重命名工作表插入新工作表（選擇執(zhí)行“插入”→“工作表”命令）選擇指定對象操作規(guī)則：“先選擇、后操作”移動單元格選擇指定對象選擇范圍Excel工作表是由單元格組成的。對工作表的操作實際上是對當前活動單元格的操作。要想對指定的單元格進行操作，必須移動活動單元格?；顒拥膯卧裼么趾诳虮硎?。移動單元格操作可用下列方式：鼠標操作鍵盤操作使用菜單命令活動單元格單擊指定的單元即可。例如，選擇“C5”為活動單元格，單擊“C5”單元。大范圍內移動；先選擇移動到指定的頁面，再單擊指定的單元。例如，要選擇“A150”，先拖動“垂直滾動條”向下滾動，當出現“150”行號時，停止拖動，再單擊“A150”。移動活動單元格的操作是針對一個單元的。若要對多個的單元進行操作，則要先選擇操作范圍。范圍可以包括：一行、一列、一組連續(xù)的單元格區(qū)域、或不連續(xù)的單元格區(qū)域、甚至整個工作表。選擇范圍可用鼠標方式和鍵盤方式。選擇一行單擊工作表左側的行號選擇一列單擊工作表頂部的列標選擇一行（列）中的若干個連續(xù)單元單擊行（列）中起始單元，拖動鼠標向右（下）至終止單元。選擇一個矩形區(qū)域單擊矩形區(qū)域的左上角單元，然后拖動向右、下方移動至矩形區(qū)域的右下角。例如，選擇B3到D7的矩形區(qū)域。選擇幾個不連續(xù)的矩形區(qū)域先選擇一個矩形區(qū)域，然后按下“Ctrl”鍵不放，再選擇其它的矩形區(qū)域。例如，選擇A2：C5，D5：F8，A10：D14選擇整個工作表單擊工作表左上角行、列交叉按鈕。取消選擇單擊選擇范圍之外的任何一處即可。進入Excel環(huán)境，就可以往工作表中輸入數據了。所有鍵入的數據都將自動存入活動單元格中。Excel允許輸入兩種不同的基本類型：

常數和公式

常數文本、數字及日期和時間

公式對選擇的數據進行各種計算，包括Excel提供的函數計算下列符號被認為是數字：

+-.，（）%$/Ee+、-

表示正、負數，分位符；例如，1,234,567.表示小數點。$貨幣符號;例如$45.67/表示分數。例如，11/2表示為1.5E、e

表示科學記數法;例如,123.456被記為0.123456E+03輸入時，若單擊“取消”按鈕，則放棄當前輸入的數據；若單擊“確認”按鈕(按Enter)，則完成本次輸入操作；若輸入一個小于<1的分數值時，應輸入“0分數”；否則，則將其作為日期值處理。一般情況下，輸入的數字右對齊，并按普通格式顯示。取消按鈕確認按鈕編輯公式按鈕如果輸入的數據是以字母（漢字）打頭的，則被認為是正文信息。例如，HellO、E12345、姓名等。若想輸入字符串“12345”，則用字符串定界符將字符串括起來。輸入的正文信息自動左對齊顯示。在輸入諸如電話號碼、學號等數字文本數據時，數字前加上一個半角單引號“’”。Excel規(guī)定了日期和時間數據的格式：日期：XXXX-XX-XX年-月-日例如：1998-5-1XXXX/XX/XX年/月/日例如：1998/5/1X-XX月-日（年取當年的年份）XX-X日-月例如：20-5，顯示5月20日時間：XX：XX：XX時：分：秒例如:9：10pm下午9點10分輸入當前日期：Ctrl+分號輸入當前時間：Ctrl+冒號Excel自動填充填充柄自動格式化工作表數字格式數字字體加邊框打印預覽Excel提供了16種不同格式的工作表,可供隨意選擇。操作為：執(zhí)行“格式→自動套用格式”，選擇其中格式。在對話框的示例框中自動顯示實際效果。

（B2：B6）在Excel中，數字可用多種不同的方法來表述。例如，采用科學記數法、加百分號、貨幣符號等。操作為：選擇指定范圍內的數據執(zhí)行“格式→樣式”，彈出“樣式”對話框

樣式的類型單擊“更改”按鈕，修改“格式”工作表中的數據不僅可以改變格式，還可以改變字體及外觀，包括：字體、大小、字形、顏色等。操作方法：先選擇要編輯的數據對象選擇各種字體字體字號顏色下劃線斜體粗體執(zhí)行“格式→單元格→字體”命令在彈出的“字體”對話框中選擇執(zhí)行各種特殊效果設置先選擇數據對象選擇對齊方式“居中、左、右對齊”或執(zhí)行“格式→單元格→對齊”可以選擇設置“文字方向”等Excel中工作表的虛線框是為了便于表格操作而虛設的、實際上并不存在。要想呈現表格，要給工作表加邊框。執(zhí)行“格式→單元格→邊框”命令

（B2：B6）選線形、顏色選內、外邊框格式編輯、加邊框輸入公式的格式是：

=單元格引用操作符單元格引用（表達式）

“=”表示輸入的是公式，而不是數字或正文

單元格引用指定單元格內容，隨運算而改變

操作符所執(zhí)行的運算例如：輸入公式如下：=B5*（A2+A5）*10

含義為：計算表達式值，結果值為:“B5的值*（A2的值+A5的值）*10”,結果存入指定單元格。算數運算符關系運算符文本運算符函數運算單元格范圍運算符

+加-減*乘/除%百分號^乘方()括號舉例:

(（A1*5+B4/2）^2)%=(等于)<>(不等于)<(小于)>(大于)<=(小于等于)>=(大于等于)舉例

A1*A1<>B2*B2A1>=B5E3=D2

&連接兩個文本串串1&串2結果為“串1串2”舉例:

A1=‘Character’，A2=‘String’A3=A1&A2Excel提供了12種類型300多種函數，在運算中使用函數可以使復雜的計算變得非常簡單。函數調用格式為：函數名（[參數列表]）例如

SUM()求和AVERAGER()求均值STDEV()求標準偏差“:”區(qū)域引用符，“,”聯合引用符，“空格”交叉引用符舉例：求單元格A1～A100的和的函數調用可寫成

SUM（A1：A100）

求A、B、C、D、E的均值可寫成

AVERAGER（A，B，C，D，E）引用運算符有：區(qū)域、聯合和交叉三種：冒號“：”區(qū)域引用符例如,A1:B10，表示從A1、A2、…、B1、…B10。逗號“，”聯合引用符表示引用兩個對象之“和”；例如，=SUM（A1：A3，B1：B3）

求A1到A3和B1到B3的和空格“”交叉運算符

表示同屬于兩個引用區(qū)域的引用，例如，=SUM（A1：C3B1：B3）

因為A1：C3包含了B1：B3，所以只引用B1到B3。關系運算是計算機應用中經常用到的運算。其特點是運算的結果是邏輯值（“真”或“假”）。關系運算公式的格式為

表達式1關系運算符表達式2運算規(guī)則為：“表達式1”關系運算“表達式2”，如果關系成立，結果為“真”，否則，結果為“假”。舉例

=B3>=C3

求B3大于等于C3

=A1+A3<>B4*10

求A1+A3不等于B4*10=A5%

求A5的百分比=A2^2

求A2的平方=(B3+B4)/5

求B3的值加B4的值除以5=A4&B4

將A4和B4中的字符串連接=SUM(A1:B1000)

求A1到B1000矩形區(qū)域數據和=NOW()

返回系統(tǒng)當前日期和時間選擇了活動單元后,單擊公式欄中的“=”按鈕,即會彈出公式編輯器，在公式編輯器中輸入公式，然后選擇“確定”按鈕，即可自動計算公式的值。例如：輸入公式，計算=（A1+B1）*C1，公式編輯器可把單元格看成是變量，在計算中引用單元格要比直接引用單元中的數據方便的多。用字母（A~IV，256列）表示列，用數字（1~65536）表示行；例如，B10，表示第2列、第10行。相對引用是針對當前單元格的引用，是隨當前單元變化而變化的。絕對引用是針對起始單元的引用，它不隨當前單元的變化而變化。相對引用的表示：“A1+A2”A1+A2都是相對位置絕對引用的表示：$A$1第1列、第1行都是絕對位置混合引用：$B1第2列是絕對位置,第1行是相對位置D4

相對引用$D4

混合引用$D$4

絕對引用B:B

引用第2列A1:IV1

引用第1行Sheet1!$D$4

引用工作表Sheet1中的單元格D4[月報]21日!$D$4

引用月報工作簿中21日表的單元格D4

舉例:A1、B1分別存放1、2，計算A1+B1，結果存入C1，再將C1的結果存入C2。如果計算公式使用相對引用公式：“=A1+B1”，結果C1=3。但復制后C2的結果為0，在編輯欄中顯示為“=A2+B2”。如果使用絕對引用公式：“=$A$1+$B$1”，結果存入C1，再復制到C2，結果仍然是對的。使用函數，可以簡化操作，提高工作效率。例如，求例5中一月份各地銷售和，可寫成：=B3+C3+D3+E3

如果使用函數，可寫成：=SUM（B3：E3）

一般格式：

=函數名（[參數]）函數是特殊求值公式，必須以“=”開始函數名是Excel提供的保留字（有300多個）圓擴號必須緊隨函數名參數表是函數的自變量：無參函數，例如，TODAY（）一個參數的函數，例如，ABS（NUM）多個參數的函數，例如，SUM（A，B，C，D）

函數參數類型=SUM（B2，B15）單元格=SUM（B2：B15）范圍=SUM（3，10，-5）數字=SUM（SIN（A1），B2）其他函數=SUM（B2，COS（A1），1）混合類型選擇要輸入函數的單元格執(zhí)行“插入→函數”命令在彈出的“粘貼函數”對話框中，選擇函數（輸入參數）單擊“確定”

圖表是數據的圖形化描述，它使數據更加直觀、形象、易讀。Excel提供了根據數據自動繪制各種圖表的功能。創(chuàng)建圖表編輯圖表改變圖表的類型操作步驟為：選擇要創(chuàng)建圖表的數據執(zhí)行“插入→圖表”根據圖表向導創(chuàng)建圖表第一步選擇圖表類型共11類、103種第二步選擇圖表數據源以行或列為軸建立圖表第三步選擇圖表選項選標題、圖例、數據標志等第四步選擇圖表位置要插入的工作表Excel2003自帶了14種圖表（一）選擇圖表類型（三）選擇圖表選項（四）選擇圖表位置（二）選擇圖表數據源圖表應用提示柱形圖可展示工作表列或行中的數據，以比較數值大小、變化與比例折線圖可展示工作表列或行中的數據，用來反應數值與整體、數值自身的變化趨勢餅圖可展示在工作表的一列或一行中的數據，用來顯示數值局部變化情況氣泡圖可展示工作表列中的數據，用來顯示數值之間的變化趨勢數據排序數據篩選分類匯總數據透視表根據數據或數據類型對數據進行排列的一種方法?？梢赃x擇升序和降序排序。升序排序是從小到大的排序降序排序是從大到小的排序數據排序操作步驟：選擇要排序的數據區(qū)域執(zhí)行“數據→排序”在彈出的對話框中選擇設置“主關鍵字”，并選擇排序方式為“遞增”，單擊“確定”按鈕，即可。學生成績排序排序前排序后教師情況排序排序前排序后在對數據進行管理時，經常要在數據表中查找和分析滿足特定條件的記錄，篩選就是一種選取數據的快速方法。它是建立在準則（條件）之上的查詢，通過篩選獲得表中滿足準則條件的數據。篩選操作可通過“自動篩選”命令實現。自動篩選操作步驟：選擇數據項，單擊數據表中任一單元格執(zhí)行“數據→篩選→自動篩選”命令數據項頂部的字段名變?yōu)橄吕斜砜?，單擊列表框的下拉箭頭彈出下拉菜單，選擇“自定義”，彈出“自定義”對話框設置篩選準則最后，單擊“確定”按鈕篩選學生成績在70～90之間的數據執(zhí)行“數據→篩選→自動篩選”命令單擊成績項標題右端的下箭頭在彈出下拉菜單中選擇“自定義”，彈出“自定義”對話框設置篩選準則：“成績大于70與成績小于90”最后，單擊“確定”按鈕。高級篩選是在定義的條件區(qū)域內輸入篩選條件，即可選取出滿足條件的數據。篩選條件可以定義一個，也可以定義多個。在要篩選的數據列下方定義篩選條件；可以寫在一列上，也可以寫在多列上。單擊數據表中任一單元格，執(zhí)行“數據→篩選→高級篩選”命令在對話框的“條件區(qū)域”欄中，選出條件區(qū)域單擊“確定”按鈕，即可若想把篩選結果放到其它區(qū)域，可選擇“將篩選結果復制到其他位置”單選項篩選學習成績在80以上的數據。定義條件區(qū)域并輸入條件表達式單擊數據表任一單元格執(zhí)行“數據→篩選→高級篩選”，彈出對話框選擇“將篩選結果復制到其他位置”，在“復制到”欄中指定結果存放的起始單元單擊“確定”按鈕。條件區(qū)域

結果分類匯總是統(tǒng)計分析中經常用到的一種數據操作。它是將同類數據匯總在一起，并對同類數據進行求和、求均值等運算。分類匯總是建立在數據表有序的基礎上，因此分類匯總前，首先要對數據列表按匯總的字段進行排序。單擊要分類匯總的數據表對分類匯總的字段進行排序執(zhí)行“數據”→“分類匯總”命令，彈出對話框選擇“分類字段”選擇“匯總方式”指定“選定匯總項”可選擇復選框項最后，單擊“確定”注：如果要消除匯總結果，打開“分類匯總”對話框，執(zhí)行“全部刪除”操作即可。求教師按性別、職稱收入的平均值。操作步驟：先按“性別”遞減、“職稱”遞增排序選擇數據表第一次，分類匯總按“性別”求“均值”，選擇“匯總結果顯示在數據下方”第二次，分類匯總按“職稱”再求“均值”，撤消“替換當前分類匯總”復選框最后，單擊“確定”按鈕。

分類匯總后，根據分類匯總的級創(chuàng)建分級顯示，從而可以方便地看到匯總結果?！?”級只顯示“總和”與“列標”“2”級只顯示第一層次“分類匯總”和“總和”“3”級顯示第二層次的“分類匯總”和“總和”“4”級顯示全部分類匯總數據及結果“-”號隱藏明細數據“+”號顯示明細數據分級顯示選擇按鈕實驗三學生成績表數據處理1．實驗目的掌握Excel的數據處理及應用。2．實驗條件和環(huán)境PC機+Windows2003/XP/Vista操作系統(tǒng)+Office2003。3．實驗預習要求閱讀教材第3章有關Excel電子表格處理及應用的內容。4．實驗任務⑴創(chuàng)建學生成績工作簿，包含3個工作表，分別是“考試成績”、“平時成績”和“綜合成績”，按圖示要求輸入如表中所示數據。⑵根據“考試成績”和“平時成績”的數據產生“綜合成績”表，統(tǒng)計時考試成績占70%，平時成績占30%。⑶按要求格式化工作表。實驗三數據樣式圖3-30“考試成績”表樣圖圖3-31“平時成績”表樣圖圖3-32“綜合成績”表樣圖實驗四Excel綜合應用1．實驗目的掌握Excel的數據綜合處理及表格應用。2．實驗條件和環(huán)境PC機+Windows2003/XP/Vista操作系統(tǒng)+Office2003。3．實驗預習要求閱讀教材第3章有關Excel電子表格處理及應用的內容。4．實驗任務⑴創(chuàng)建“學生成績登記表”工作簿，創(chuàng)建“學生信息”工作表，如圖3-37所示。⑵在“學生信息”表的基礎上生成“數據統(tǒng)計”工作表。⑶在“數據統(tǒng)計”表的基礎上生成“成績評定”工作表。⑷在“數據統(tǒng)計”表的基礎上統(tǒng)計學生平均分頻率分布并產生分布直方圖。⑸在“數據統(tǒng)計”表的基礎上對學生成績的平均分按“專業(yè)”分類匯總。⑹在“數據統(tǒng)計”表的基礎上生成“總分排序”表。⑺在“數據統(tǒng)計”表的基礎上執(zhí)行條件篩選操作，生成“成績篩選”表。實驗四數據樣式圖3-37“學生成績登記表”示意圖圖3-39“數據統(tǒng)計”表示意圖圖3-42“成績評定”表示意圖圖3-44統(tǒng)計學生成績“頻率分布”示意圖圖3-43“學生成績分布”直方圖圖3-51學生成績“分類匯總”示意圖圖3-53“總分排序”示意圖圖3-55篩選條件區(qū)域示意圖本單元小結

本單元要點電子表格的用途電子表格的操作環(huán)境電子表格的基本操作單元格引用公式和函數計算圖表應用基本的數據分析操作Q&A提供有關文件的各種操作：新建

建立新的電子表格打開/關閉

指定的文件保存

保存文件另存

另起文件名保存文件……頁面設置

設置打印頁面打印預覽

預先查看打印頁面效果打印

打印指定文件或頁面發(fā)送

傳送文件到指定地方屬性

修改文件操作權限文件列表

最近使用文件列表退出

關閉文件，退出Excel提供各種編輯操作：撤消

取消前面操作，恢復原狀態(tài)

重復

重復執(zhí)行當前操作剪切

將選擇對象切下，送剪貼板復制

將選擇對象切下，送剪貼板粘貼

將剪貼板內容送當前位置……清除

消除選擇的內容全選

選擇當前范圍內的全部內容查找

在當前文檔中查找指定內容替換

查找并替換為需要的內容定位

查找指定內容并定到該位置鏈接

鏈接到指定的地址

普通以一般方式顯示文當內容分頁預覽

分頁顯示工具欄顯示并選擇所有工具菜單編輯欄顯示|取消編輯欄狀態(tài)欄顯示|取消狀態(tài)欄頁眉和頁腳設置定義頁眉、頁腳批注全屏顯示顯示比例

設置、定義顯示大小提供顯示方式和頁眉頁腳等操作：單元格插入單元格行插入行列插入列工作表插入工作表圖表插入圖表分頁符插入分頁符函數插入函數名稱批注圖片插入圖片對象插入指定對象超級鏈接定義超鏈接

單元格設置單元格格式

行設置行數據格式列設置列數據格式工作表對工作表操作自動套用格式選擇提供的格式條件格式定義條件格式樣式選擇指定樣式拼音信息設置拼音格式拼寫提供自動拼寫檢查自動更正提供自動替換功能共享工作簿在多工作簿之間共享數據修訂合并工作簿保護……...審核宏定義、使用“宏”操作加載宏自定義

自定義工具操作選項設置默認參數提供各種有關數據的操作：排序對選擇的數據排序篩選選出指定數據集記錄單分類匯總分類統(tǒng)計計算有效性定義有效性檢驗模擬運算表分列在多單元格中分列合并運算求各種運算組及分級顯示按數據分組顯示……獲取外部數據從外部導入數據更新數據對數據更新處理返回表格處理

可以快速地能實現各種對表格的處理。數據管理不僅可以建立、查詢數據信息，還可以對數據記錄進行添加、刪除、排序、統(tǒng)計等操作。圖表處理根據數據可自動生成十四種標準圖表類型以及用戶自定義圖表類型。數據分析與方案決策可以實現諸如模擬運算表、方案預測、方程求解、規(guī)劃求解、回歸分析等功能。數據共享和互聯網