電子表格軟件Excel詳細教程

2023年12月25日發(作者：科技美容)

-

電子表格軟件E*cel

1 認識 E*cel

1.1 E*cel 簡介

E*cel 是功能強大的電子表格軟件。我們在日常學習和工作中經常會用到表格，這些表格通常是用來處理數據并且需要計算的。例如，在教學工作中經常需要制作學生成績統計表，任課教師要統計學生的考勤、平時成績、期末成績，并以此計算學生的綜合成績，并進展成績的排序、求平均分等；班主任需要匯總學生的各門成績，得出學生的總分和總成績排名；教務部門要計算各個班學生成績的排名等。財務部門需要制作職工的工資表，每個家庭會有家庭收支統計表。在這些數據表中，不僅僅要輸入數據，還經常要對數據進展一定的運算。如計算學生成績的總分，要進展求和運算；要求學生成績的平均分，要進展求平均數運算；要得出學生成績的排名，要進展排序運算；要得出成績的最高分和最低分，要進展最大值和最小值運算；將成績按一定的條件進展選擇，要進展篩選運算。

在人工表格中，所有計算由人工完成，十分麻煩，稍有疏漏就可能出現錯誤。當發現錯誤需要改動時，例如有一名學生的成績在錄入時出現了錯誤，就要把與該學生相關的所有數據進展重新計算，如該學生的總分、平均分、排名，還會對全班的排名造成影響，運算量十分龐大。

而引入電子表格軟件可以我們提高工作效率，電子表格軟件的一個重要的特征就是數據處理自動化。它不需要人進展手工計算，通過公式、函數自動完成數據的. z

-

運算。任何一個參加運算的數據發生了改變，其他與這個數據相關的數據都會自動的、實時的更新。

E*cel 作為應用廣泛、功能強大的電子表格軟件，它的功能包括：

⑴數據采集。可以在 E*cel 中錄入數據，也可以將其他形式的數據導入到 E*cel

中，如記事本、 Word 、 Access 數據庫中的數據等。

⑵數據編輯。可以根據需要對數據進展修改，與修改數據相關的數據都可以得到相應的更新。

⑶數據運算。可以通過函數和工作來實現數據運算的自動化。

⑷數據圖表化。 E*cel 提供了十分強大的圖表功能，如可以生成學習成績的分布圖，可以直觀的表示出各個分數段的人數在總人數中的比例，圖形可以以圓餅圖、折線圖等多種形式呈現。

⑸數據分析處理。

1.2 啟動和關閉 E*cel

E*cel 是 Office 套件中的軟件，因此在啟動 E*cel 之前需要安裝 Office 軟件。以 Office2003 為例，安裝好 Office 軟件后，可以選擇"開場菜單〞→"程序〞→" Microsoft Office 2003 〞，就可以啟動E*cel 軟件。如圖 5 - 1 所示。

圖 5 - 1 翻開 E*cel

. z

-

可以在桌面上為 E*cel 創立一個快捷方式。在" Microsoft Office 2003 〞菜單上單擊鼠標右鍵，在彈出的快捷菜單中選擇"發送到〞→"桌面快捷方式〞，如圖 5 - 2 所示。

圖 5 - 2 創立快捷方式

這樣在桌面上就會出現一個快捷方式。雙擊快捷方式可以翻開 E*cel 。如果已經有 E*cel 文件，則雙擊 E*cel 文件也可以翻開 E*cel 。

關閉 E*cel 的方法是單擊程序右上角的關閉按鈕，或者在 E*cel 中選擇菜單"文件〞→"退出〞命令。

1.3 E*cel 界面

E*cel 主界面如圖 5 - 3 所示。和其他 Office 系列軟件一樣，由標題欄、菜單欄、快捷工具欄和工作區組成。 E*cel 與眾不同的有如下局部。

圖 5 - 3 E*cel 界面

⑴公式編輯行。公式編輯行里有兩個對話框，如圖 5 - 4 所示，左邊的小對話框稱為單元格地址對話框，顯示的是當前活動單元格的地址，右邊的大對話框用于輸入與編輯單元格的容或運算公式。

圖 5 - 4 公式編輯行

. z

-

⑵工作簿。 E*cel 軟件把一個文檔稱為一個工作簿，英語是 Book 。一個工作簿最多可以由 255 個工作表〔 Sheet 〕組成。在默認狀態下，每個工作簿里有

3 工作表，分別用 Sheet1 、 Sheet2 、 Sheet3 來表示，如圖 5-5 所示。如有需要，工作表還可以再增加。當工作表的個數超過 4 個時，工作表標簽滾動按鈕才開場起作用。

圖 5 - 5 一個工作簿默認有 3 個工作表

⑶工作表。工作表實際上是一巨大的二維表格，它由許許多多的單元格組成，如圖 1-6 所示。在工作表里，需要掌握四個根本概念：單元格、單元格的名字、當前活動單元格、滾動條。

圖 5 - 6 一個工作表的局部

①單元格。工作表中行和列穿插的地方叫做單元格。工作表上方的大寫英文字母表示的是列坐標： A 、 B 、 C 、 D 、 ......Z 、 AA 、 AB......IV ，共有 256 列；工作表左側的數字表示的是行坐標： 1 、 2 、 3 …… 65536 ，共有 65536 行。因此，一工作表共有 16777216 〔 65536 × 256 〕個單元格。

②單元格的名字。為了表達和處理的方便，工作表中每一個單元格都有一個唯一確定的名字，給單元格命名的方法是：列坐標 + 行坐標〔即先寫字母后寫數字〕。例如，第一列第一行單元格的名字為 A1 。工作表最右下角單元格的名字為

IV65536 。

. z

-

③當前活動單元格。工作表中有一個可以自由移動的、加粗的方框，用鼠標或鍵盤上的↑、↓、←、→四個箭頭鍵，可以將其移到工作表中任一單元格，這個活動方框所在的單元格，稱為"當前活動單元格〞。

在"當前活動單元格〞中，可以直接輸入文字、數字和數學表達式。當前活動單元格的名稱和所輸入的容都顯示在工作表上方的公式編輯行。

電子表格軟件E*cel

2 數據錄入

工作簿的新建、翻開、關閉與 Office 其他軟件的操作類似，不再詳述。

2.1 選定單元格

⑴選定單個單元格。將鼠標指針移到任意一個單元格上，單擊鼠標左鍵，當該單元格被活動方框框住的時候，說明該單元格已經被選中。

如要選擇的單元格距離當前單元格非常遙遠，可以在公式編輯行里的單元格地址對話框輸入單元格地址。

⑵選定多個連續單元格。拖曳鼠標可以選取多個連續的單元格。或者用鼠標單擊選擇區域的左上角單元格，按住 Shift 鍵再用鼠標單擊右下角單元格。選中*一行〔列〕可單擊工作表相應的行〔列〕號；選擇整個工作表可單擊工作表左上角行列穿插的按鈕；選擇相鄰的行〔列〕可用鼠標拖曳行號或列號。

. z

-

⑶選取多個不連續單元格。選擇一個區域，再按住 Ctrl 鍵不放，然后選擇其他區域。在工作表中任意單擊一個單元格即可去除單元區域的選取。

2.2 輸入數據

當單元格被選定之后，就可以往該單元格輸入數據。數據輸入完畢可用鼠標單擊公式編輯欄中的輸入按鈕制鍵↑、↓、←、→，將單擊公式編輯欄中的"取消〞按鈕確認，或者按 Tab 鍵確認，還可以用鼠標或光標控移到其他單元格。當發現輸入有誤，可用鼠標，或者按 Esc 鍵取消輸入。

2.3 E*cel 中的數據類型

E*cel 表格中的數據一般分為兩大類：數值型數據和文本型數據。

2.3.1 數值型數據

數值型數據可以參加數學運算。數值型數據主要包括以下幾類：

⑴數字符號： 0 、 1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 100 ；

⑵數學公式：在 E*cel 中，公式是以"＝〞開場的；

⑶數學函數：是系統已經幫助我們編寫好的公式；如 sum 、 average ；

⑷日期：輸入日期時，用反斜線〔 / 〕或連字符〔 - 〕作為間隔符，如

2008-12-15 ；

⑸時間：輸入時間時，以冒號〔 : 〕作為時、分、秒的間隔符，例如， 12:35:21;

. z

-

⑹貨幣：如￥ 123.45 、 $56.78 ；

⑺運算符號：如 + 、 - 、 * 、 / 、〔、〕、 $ 、 % 、，、 E 、 e 等。

數值型數據一般采用右對齊格式顯示方式。在單元格上單擊鼠標右鍵，在彈出的快捷菜單中選擇"設置單元格格式〞，會彈出"單元格格式〞對話框。在這里可以不同數據類型進展進一步設置。如圖 5 - 7 所示可以對貨幣型數據進展設置。

圖 5 - 7 設置數據格式

2.3.2 數值型數據的長度

如輸入的數值型數據的長度超過了單元格的顯示寬度時， E*cel 會自動將數據轉化成科學計數法的形式顯示；當單元格里的數據用科學計數法也不可能正常顯示時， E*cel 會自動在單元格里顯示一串 ### 號表示數據超長無常顯示。如圖

5 - 8 所示。

圖 5 - 8 同一個數據在不同寬度的單元格顯示不一樣

2.3.3 文本型數據

文本型數據包括：漢字、英文大小寫字母、數字及其他字符，即除了數值型數據之外的所有數據都是文本型數據。日常生活中有一類數據，雖然外表看是數字，實際上是不參與數學計算的文本型數據。例如，、汽車牌號、等。對于顯示為數字的文本型數據，輸入時，需在這些數據前面加上西文單引號〔 ’ 〕，如 ’123 ，或采用 =〞 123〞的方式將數字轉化成文本型數據。

. z

-

在單元格中，所有文本型數據均默認采用左對齊格式顯示。一個單元格最多允許輸入 255 個字符，當文本型數據的字符長度超過了單元格的顯示寬度時，如果右側相鄰的單元格中還沒有數據，則 E*cel 允許多出來的數據臨時占用相鄰的單元格，如圖 5 - 9 〔 a 〕所示；如右側相鄰的單元格中已有數據，則多出來的數據無法顯示出來，如圖 5 - 9 〔 b 〕所示。

(a) 多出來的數據占用相鄰單元格 (b) 右側鄰格有數據時超長局部被遮蓋

圖 5 - 9 文本型數據字符長度超過單元格顯示寬度的情況

此類問題可以通過調整單元格的寬度或對字符進展換行來解決。在 E*cel 中，對字符進展換行有兩種方式，一是在輸入過程中使用快捷鍵 Alt ＋ Enter 換行；二是選擇菜單"格式〞→"單元格〞命令，在彈出的對話框中選擇"對齊〞，將"文本控制〞中的"自動換行〞選中，如圖 5 - 10 所示。

圖 5 - 10 設置自動換行

2.4 數據的顯示格式

在 E*cel 表格中，數值型數據和文本型數據默認采用不同的顯示方式，數值型數據一般采用右對齊格式，而文本型數據一般采用左對齊格式。在*些情況下，為了美化表格或特殊的需要，可以改變數據的顯示格式。

常用的數據顯示格式按鈕有 9 個，它們的名稱如圖 5 - 11 所示。需要使用哪種顯示格式，只要先選中單元格，再單擊相應的格式按鈕，該單元格里的數據馬上按選中的格式顯示。

. z

-

圖 5 - 11 顯示格式按鈕

2.4.1 對齊方式按鈕

當要更改*個單元格里的數據對齊格式時，可以先選中要更改對齊格式的單元格區域，然后單擊格式工具欄中的*一個"對齊〞按鈕，被選中的單元格區域里的數據就按新的對齊格式顯示。

2.4.2 合并及居中格式

在制作表格時，除了要往表格里輸入各種數據之外，經常需要用一個表頭來描述表格的容，但表頭的文字一般比擬多，超過了一個單元格的寬度，此時可用"合并及居中格式〞將幾個單元格合并成一個單元格來顯示標題。操作步驟如下：

⑴鼠標選中要設置格式的單元格區域；

⑵按住鼠標左鍵，拖動鼠標向水平方向移動，直到足夠顯示標題為止；

⑶單擊"格式〞工具欄的"合并及居中〞按鈕；

被選中的假設干個單元格被合并成一個單元格，同時，標題在合并后的單元格居中顯示，如圖 5 - 12 所示。

圖 5 - 12 合并及居中單元格

. z

-

2.4.3 貨幣格式

將數字用貨幣格式顯示的方法如下，選中要采用貨幣方式顯示的所有單元格，單擊格式工具欄中的"貨幣樣式〞按鈕，被選中的單元格區域里的數值型數據即按貨幣格式顯示。如圖 5 - 13 所示。

圖 5 - 13 貨幣格式顯示數據

2.4.4 百分比格式

當表格中的*些數據需要顯示為百分比的形式時就可使用"百分比樣式〞按鈕。選中需用百分比方式顯示的單元格區域，鼠標單擊"格式〞工具欄中的"百分比樣式〞按鈕，被選中的單元格區域的數值型數據即以呈百分比格式顯示，如圖 5 - 14 所示。

圖 5 - 14 百分比格式顯示數據

2.4.5 千位分隔格式

在使用計算機處理各種數值問題時，往往會出現多位數的情況，例如，世界人口統計表、國土資源統計表、國民經濟總產值統計表等，這一類數據的特點是位數比擬長，如果用千位分隔格式，可以使數據顯示更加清晰。操作步驟是選中要采用千位分隔格式顯示的單元格區域，單擊格式工具欄中的"千位分隔樣式〞按鈕，被選中的單元格區域的數值型數據即按千位分隔格式顯示，如圖 5 - 15 所示。

. z

-

圖 5 - 15 千位分隔格式

2.4.6 增加或減少小數位數

在 E*cel 表格里，如數值型數據需要以帶有假設干位小數的形式顯示，可用"增加小數位數〞按鈕快速實現，無須一個個單元格去調整。假設要減少小數來實現。具體步驟是選中需改變顯示格式的〔或"減少小數位位數，可用"減少小數位數〞按鈕單元格區域，單擊"格式〞工具欄中的"增加小數位數〞按鈕數〞按鈕〕，每單擊一次，被選中的單元格區域中所有的數值型數據就增加〔或減少〕一位小數位。

2.5 數據的自動填充

當往表格里輸入數據時，往往會遇到輸入一系列連續的數據的情況，例如，連續的編號、年度序號、月份序列、星期序列、日期序列、時間序列、連續的文字序列等。如用手工輸入這些序列，顯然很麻煩，但如果利用 E*cel 軟件提供的自動填充功能來完成這些輸入工作，就會又快又省事。

在當前活動單元格的右下角有一個黑色的小方塊，稱為填充柄。利用填充柄，可以完成許多數據的自動填充。

2.5.1 復制填充

復制填充即填充的數據都是一樣的。復制填充的步驟為：①在*個單元格填入序列的第一個數據；②單擊該單元格，使其成為當前單元格；③將空心十字狀的鼠標光標指向填充柄，鼠標指針變成了黑色的十字光標；④按住鼠標左鍵沿著水平. z

-

方向或垂直方向拖動鼠標；鼠標經過的地方屏幕上出現一個不斷延伸的虛線框；⑤當拖動到適宜的單元格后，松開鼠標，一樣的數據就會出現在鼠標經過的所有表格區域。

2.5.2 序列填充

序列填充是指填充的數據具有一定的變化規律，例如等差數列、等比數列等。選擇菜單命令"編輯〞→"填充〞→"序列〞調出"序列〞對話框，如圖 5 - 16 所示，可以設置需要填充的序列。序列填充的步驟是：

圖 5 - 16 序列對話框

⑴在單元格中輸入起始數字；

⑵將鼠標移到填充柄處，此時光標變成黑色十字光標；按住鼠標拖動填充柄到完畢單元格處；

⑶在序列對話框中設置擇序列產生的方向、類型〔等差序列、等比序列、日期等〕、步長值；單擊對話框中的按鈕。

對于步長為" 1 〞的數值型數據的等差序列，可以在選中單元格的同時按 Ctrl

鍵，再拖動鼠標，即可方便的生成等差序列。但對于顯示為數字的字符型數據，直接拖動即可產生等差序列，假設要等值填充，則需要按住 Ctrl 鍵。

2.6 導入外部數據

. z

-

在 E*cel 中，不但可以直接輸入數據，還可以將存儲在外部的數據直接導入進來，如 t*t 文件中的數據， Access 、 Word 或其他 E*cel 文件中的數據。

例如我們有如圖 5 - 17 所示的 t*t 文件，文件中的數據是用" Tab 〞分隔開的。將 t*t 文件中數據導入到 E*cel 中的步驟如下：

圖 5 - 17 外部 t*t 數據

⑴選擇菜單命令"數據〞→"導入外部數據〞→"導入數據〞，會彈出"選取數據源〞對話框，如圖 5 - 18 所示。選中 t*t 文件所在的目錄，單擊"翻開〞按鈕。

圖 5 - 18 "讀取數據源〞對話框

⑵出現"文本導入向導〞對話框，如圖 5 - 19 所示，由于原文件用 Tab 分隔，所以選擇類型"分隔符號〞，文件原始格式選擇簡體中文，單擊"下一步〞按鈕。

圖 5 - 19 文本導入向導步驟 1

⑶出現圖 5 - 20 所示對話框，選擇分隔符號，本例中為 Tab 鍵，單擊"下一步〞按鈕。

圖 5 - 20 文本導入向導步驟 2

⑷出現圖 5 - 21 所示對話框，在這里可以設置數據的格式，例如可以設置列的格式為文本型。設置完成后單擊"完成〞按鈕。

圖 5 - 21 文本導入向導步驟 3

. z

-

⑸出現如圖 5 - 22 所示的導入數據對話框，可以選擇導入數據的存放位置。我們選擇將數據放在現有工作表中。完成后單擊"確定〞按鈕完成導入工作。導入后的結果如圖 5 - 23 所示。

圖 5 - 22 導入數據

圖 5 - 23 數據導入結果

電子表格軟件E*cel

3 數據表編輯

3.1 單元格的插入與刪除

當設計一數據表時，有時由于考慮不周，待數據表建立好后才發現還需要再增加或刪除一些行或列，此時就需要使用插入和刪除單元格的功能。

3.1.1 插入一個單元格

操作步驟為：

⑴鼠標單擊要插入單元格的位置，如 A1 單元格；

⑵選擇"插入〞→"單元格〞命令，或者單擊鼠標右鍵，出現"插入〞對話框，有四個選項，如圖 5 - 24 所示：

活動單元格右移：一個空白的單元格出現在 A1 單元格， A1 單元格原有的數據及其右側的其他數據都向右移動一個單元格；

. z

-

活動單元格下移：一個空白的單元格出現在 A1 單元格， A1 單元格原有的數據以及其下方的其他數據都向下移動一個單元格；

整行：插入一個空白行， A1 單元格所在的行整行向下移；

整列：插入一個空白列， A1 單元格所在的列整列向右移；

圖 5 - 24 "插入〞對話框

3.1.2 插入行、列

插入一行或一列的操作步驟為：

⑴鼠標單擊要插入新行或新列的單元格；

⑵選擇"插入〞→"行〞命令或"插入〞→"列〞命令；選中單元格所在行向下移動一行或者所在列向右移動一列，以騰出位置插入一空行或空列。

此外，在如圖 5 - 24 所示的"插入〞單元格對話框中選擇"整行〞或"整列〞也可插入一空行或空列。

3.1.3 刪除單元格、行、列

選取單元格或一個區域，選擇"編輯〞→"刪除〞命令，出現如圖 5 - 25 所示"刪除〞對話框，操作與上面類似。

圖 5 - 25 "刪除〞對話框

. z

-

3.1.4 去除單元格

刪除和去除是兩個容易混淆的概念，"刪除單元格〞是從工作表中移去這些單元格，并調整周圍的單元格填補刪除后產生的空缺；而"去除單元格〞只是刪除了單元格中的具體容 ( 公式和數據 ) 、格式或批注，去除后的單元格仍然保存在工作表中。

3.2 單元格的復制與移動

如需要把數據從一個單元格復制或移動到另一個單元格，可以首先選擇需要復制或移動的數據，單擊"復制〞單元格，再單擊"粘貼〞〔或"剪貼〞〕按鈕，然后單擊目標位置的首按鈕即可。

3.3 工作表的插入與刪除

E*cel 默認一個工作簿有三工作表，用戶可以根據需要增加工作表的個數，一個工作簿最多允許有 255 工作表。用戶也可以從工作簿中刪除不需要的工作表或更改工作表的名稱。

3.3.1 插入工作表

如需在*個工作表前插入一空白工作表，只需在標簽欄單擊該工作表的標簽〔如

Sheet1 〕，在菜單欄選擇"插入〞→"工作表〞命令，就可以在" Sheet1 〞之前插入一個空白的新工作表。

. z

-

3.3.2 刪除工作表

如需刪除整個工作表，只要選中要刪除的工作表的標簽，選擇"編輯〞→"刪除工作表〞命令，屏幕上彈出一個對話框，詢問是否真的要刪除工作表，按下"確定〞按鈕，整個工作表被刪除且相應標簽也從標簽欄中消失。

3.3.3 重命名工作表

重命名工作表名稱的操作步驟為：用鼠標雙擊要更改名稱的工作表標簽，工作表名將突出顯示，或單擊鼠標右鍵，在彈出的快捷菜單中選擇"重命名〞；輸入新的工作表名替代原來的默認的工作表名字；按回車鍵，新的工作表名稱就生效了。

3.4 工作表的復制與移動

通過工作表數據的復制與移動功能可把*工作表中的所有數據復制或移到另一工作表中，由此大大減少數據輸入的工作量。

3.4.1 多工作表之間的轉換

每工作表的名稱都顯示在工作簿窗口底部的工作表標簽里。如想對*工作表進展編輯，只需鼠標單擊工作表標簽。活開工作表的名稱以單下劃線的形式顯示。

3.4.2 工作表數據的移動或復制

如需在同一個工作簿移動或復制工作表，則只需單擊需要移動或復制的工作表標簽，將它拖動到適宜的標簽位置即可；如在拖動的同時按住 Ctrl 鍵即可產生原工作表的副本，在實現工作表復制的同時， E*cel 將自動為副本命名。

. z

-

如需將一個工作表移動或復制到不同的工作簿時，則兩個工作簿必須都是翻開的。選中需移動或復制的工作表，選擇"編輯〞→"移動或復制工作表〞選項，在翻開的對話框中選擇需要目標工作簿以及放置位置，如圖 5 - 26 所示。

圖 5 - 26 "移動或復制工作表〞對話框

電子表格軟件E*cel

4 工作表的美化

E*cel 軟件提供了多種美化工作表的功能，例如，更改文本的字體、大小和顏色、調整單元格的行高和列寬、給工作表添加邊框線和背風光等。

4.1 字體、字號和字體顏色

在格式工具欄里有幾個與字體、字號、邊框、顏色有關的按鈕，如圖 5 - 27 所示，這幾個按鈕能夠快速實現工作表的美化。

圖 5 - 27 工具欄格式按鈕

除了通過"格式〞工具欄中的按鈕設置格式外，還可以通過菜單欄選擇"格式〞→"單元格〞，在"單元格格式〞對話框中選擇"字體〞選項卡，根據需要進展適當的設置，如圖 5 - 28 所示。

圖 5 - 28 字體設置對話框

4.2 為單元格增加邊框和背風光

. z

-

4.2.1 給單元格增加邊框線

當創立一個新的工作表時， E*cel 單元格的邊框線顯示為淺灰色的網格線，該網格線在打印時一般是不顯示的。為了使表格更醒目、更美觀，可以給工作表設定邊框線。操作步驟如下：

⑴選擇要添加邊框線的單元格區域；

⑵單擊"格式〞工具欄中"邊框〞按鈕的下拉箭頭，選擇適宜的邊框樣式；或者在如圖 5 - 28 所示的"單元格格式〞對話框中選擇"邊框〞選項卡，進展適當的設置。

4.2.2 給單元格填充背風光、圖案底紋

如只是簡單地給單元格填充背風光，可以通過單擊"格式〞工具欄的"填充顏色〞按鈕；如要給單元格中填充圖案底紋，則需在如圖 5 - 28 所示的"單元格格式〞對話框中選擇"圖案〞選項卡，進展相應的設置。

4.3 改變單元格的行高和列寬

當翻開一個新的工作簿時， E*cel 的每一個單元格都具有同樣的高度和寬度。但在實際應用的過程中，由于數據長短不一，字體大小不一，需要經常調整表格的行高和列寬。

改變單元格的行高有三種方法。列寬的調整與之類似。

. z

-

方法一是將鼠標指針指向*個行坐標的下沿，鼠標指針變成了黑色的十字，按住鼠標左鍵上下拖動，可以快速調整該行的行高。

方法二是將鼠標指向*一行的行坐標，單擊鼠標右鍵，在彈出的快捷菜單中選擇"行高〞，如圖 5 - 29 所示，在彈出的對話框中輸入行高的數值，如圖 5 - 30 所示。

圖 5 - 29 設置行高快捷菜單

圖 5 - 30 "行高〞對話框

方法三是選擇菜單命令"格式〞→"行〞→"行高〞，在"行高〞對話框中輸入行高的數值。如選擇"最適合的行高〞，則系統會根據單元格容自動調整行高。如圖 5

- 31 所示。

圖 5 - 31 設置行高的菜單項

電子表格軟件E*cel

5 公式與函數

1 輸入公式

E*cel 具有強大的計算和統計功能，用 E*cel 制作工作表時，只需要輸入原始數據，其余的復雜計算就由 E*cel 完成。 E*cel 的計算功能是通過公式和函數來實現的。

. z

-

從前面幾節的介紹中，可以看出，單元格十分重要，它集計算、顯示、存儲三大功能于一身，在 E*cel 中占有舉足輕重的地位。 E*cel 最大的特點是在公式計算中引入了單元格地址的概念，即在計算公式中用包含數值的單元格地址代替具體的數值。

5.1.1 輸入公式的根本步驟

在 E*cel 中輸入數學公式時，要遵循 E*cel 規定的語法格式，即公式最前面是等號〔 = 〕，然后是參與運算的數值和運算符。操作步驟為：選中要輸入計算公式的單元格；輸入公式的標志" = 〞，或者單擊公式編輯行中的公式標志；在公式編輯行或單元格依次輸入組成公式的數值及運算符；按回車鍵確認輸入完成；被選中的單元格中立刻顯示出計算結果，公式編輯行顯示計算公式。

例如在圖 5 - 32 在 F3 單元格里求 B3 、 C3 、 D3 、 E3 單元格數值之和。

圖 5 - 32 求單元格數值之和

操作步驟如下：單擊要輸入求和公式的單元格 F3 ；從鍵盤輸入公式： =B3+C3+D3

＋ E3 ，該公式同時顯示在公式編輯行中，按回車鍵確認輸入完成， E3 單元格顯示計算結果，如圖 5-32 所示。在輸入要引用的單元格時，也可以用鼠標單擊要引用的單元格，這是單元格被一個虛線框套住，同時單元格的地址顯示在公式所在單元格中。

2 公式的復制

. z

-

前面我們曾經學習過單元格數據的復制， E*cel 不僅可以復制一個單元格里的數據，還可以復制整行或整列單元格里的數據，并且復制的速度相當快。對于公式的復制， E*cel 更有獨到之處，可以在復制公式的同時，自動改變公式中所涉及的單元格地址。

例如上節例子中計算學生期末考試成績表中其余同學的總分，當用求和公式求出第一個同學的總分之后，就可以利用公式復制功能，快速求出其余同學的總分。步驟如下：翻開學生成績統計表；選擇被復制公式所在的單元格 F3 ；將鼠標指向單元格右下角的填充柄；按住鼠標左鍵，拖動鼠標向下移動到 F9 單元格，如圖 5 - 33 所示；松開鼠標左鍵，完成公式的復制。

圖 5 - 33 用公式復制功能計算總分

復制公式時， E*cel 并不是簡單地復制計算結果本身，而是將運算關系復制到其他的單元格。公式中引用的單元格地址發生了相對位置的改變。 E*cel 之所以有如此功能是由單元格的相對引用地址和絕對引用地址所致。這是公式應用中很重要的概念。

單元格地址有三種表現形式：相對引用地址、絕對引用地址和混合引用地址。

5.2.1 相對引用地址

E*cel 中默認的單元格引用為相對地址引用，如 A1,A2 等。相對引用是當公式在移動或者復制時，該地址相對目的單元格發生變化，相對引用地址由列名行號表示，如 A1 。

. z

-

如圖 5 - 33 的例子，單元格 F3 中的公式"＝ B3+C3+D3 ＋ E 3 〞, 當被復制到 F4 單元格時，公式會隨目的位置自動變化為"＝ B4+C4+D4 ＋ E 4 〞。這是由于目的位置相對源位置發生了下移一行的變化，導致參加運算的對象分別均做了下移一行的調整，最后得到"＝B4+C4+D4 ＋ E 4 〞的公式復制結果。

5.2.2 絕對引用地址

在行號和列號前均加上" $ 〞符號，則代表絕對引用。公式復制時，絕對引用單元格不隨著公式位置變化改變。如上例如果 F3 中的公式" =$B$3+$C$3+$D$3 ＋

$E$ 3 〞, 再將公式復制到 F4 ，你就會發現 F4 的值仍為 F3 中值，公式沒有變。

5.2.3 混合引用地址

混合引用是指單元格地址的行號或列號前加上"$ 〞 , 如 $A1,A$1 。當公式單元因為復制或插入而引起行列變化時，公式的相對地址局部會隨位置變化，而絕對地址局部仍保持不變。

復制功能是 E*cel 最具特色的地方，也是電子表格與人工表格的最大區別之一。掌握好公式復制的操作，可以減少許多重復性的操作，從而大大提高制表工作的效率。

3 函數

. z

-

5.3.1 函數

為了便于各行各業的應用， E*cel 提供了大量的函數。比方，求和函數〔 SUM 〕、平均數函數〔 AVERAGE 〕、最大值函數〔 MA* 〕、最小值函數〔 MIN 〕等。函數的一般表達式為：

函數名〔參數列表〕

如果參數表中的參數是連續的單元格，可以寫成 ( 參數 1: 參數 2) 的形式，其中，參數 1 為起始單元格地址，參數 2 為完畢單元格地址，兩個單元格地址之間用冒號〔 : 〕分隔。例如， SUM(A1:A10) 表示求 A1 單元格到 A10 單元格數值之和。

如果參數表中的參數是離散的單元格，可以寫成 ( 參數 1 ，參數 2 ，參數

3 …… ) 的形式。各個參數之間用逗號〔，〕分隔。比方， MA* 〔 A1 ， C2 ，

D3 ， E4 〕是求 A1 ， C2 ， D3 ，E4 這四個單元格中數值的最大值。

用求和函數〔 SUM 〕統計圖 5 - 33 中成績的總分，操作步驟如下：

⑴單擊要計算總分的單元格 F3 ；

⑵單擊常用工具欄里的"自動求和〞按鈕；

⑶ E*cel 自動搜索出當前單元格左側的區域作為求和對象，并用一個虛線框將求和區域套住，如圖 5 - 34 所示； F3 單元格和公式編輯欄同時顯示函數表達式 =SUM(B3:E3) ；

. z

-

按回車鍵確認公式有效， F3 單元格顯示計算結果。

圖 5 - 34 利用求和公式計算總分

或者在第⑵步選擇菜單命令"插入〞→"函數〞，在彈出的"插入函數〞對話框中選擇"數學與三角函數〞中的 SUM 函數，如圖 5 - 35 所示。單擊確定按鈕，會彈出如圖 5 - 36 所示"函數參數〞對話框，設定參數的圍，如本例中為 B3:E3 。或者直接在 F3 中輸入求和公式＝ sum(B3:E3) 。

圖 5 - 35 在函數庫中選擇 sum 函數

圖 5 - 36 "函數參數〞對話框

5.3.2 函數庫

由于手工編寫函數比擬繁瑣， E*cel 為用戶提供了一個自帶的函數庫。選擇菜單命令"插入〞→"函數〞，即可彈出如圖5 - 37 所示的函數對話框。

函數對話框分為上下兩個窗口，上面顯示的是函數分類，下面顯示的是每一類函數中的子函數。 E*cel 軟件一共提供了 10 類 200 多個函數。

圖 5 - 37 E*cel 函數庫

在選中了*一函數后，在"選擇函數〞框下方會出現關于該函數的簡要介紹。例如函數 SUM ，對它的注釋是：

SUM(number1,number2,…)

. z

-

計算單元格區域中所有數值的和。

5.3.3 數學與三角函數

⑴ PI 函數。

圓周率是數學中用的比擬多的常數，在 E*cel 中，是通過函數的形式來返回

PI 的值的。函數格式為：

PI();

返回數字，即數學常量 pi ，準確到小數點后 14 位。在圖 5 - 38 中為函數 PI

的結果。

圖 5 - 38 PI

⑵ RAND 函數。

返回大于等于 0 且小于 1 的平均分布的隨機數，函數的格式為：

RAND ()

如果想產生 1 － 100 之間的隨機數，可以對函數進展 100 倍的放大。如圖 5 -

39 所示。

圖 5 - 39 隨機數舉例

⑶ INT 函數。

. z

-

如果要產生 1 － 100 之間的隨機整數，則要用到取整函數 INT ，函數格式為：

INT(number)

函數將數值 number 向下取整為最接近的整數。在上例中，對隨機數進展取整操作，即可得到 1 － 100 之間的隨機整數。如圖 5 - 40 所示。

圖 5 - 40 隨機整數例子

⑷ SIN 函數。

返回給定角度的正弦值。語法為：

SIN(number)

number 為需要求正弦的角度，以弧度表示。如果參數的單位是度，則可以乘以

PI()/180 或使用 RADIANS 函數將其轉換為弧度。

⑸ COS 函數。

返回給定角度的余弦值。語法為：

COS(number)

number 為需要求正弦的角度，以弧度表示。

⑹ TAN 函數。

返回給定角度的正切值。語法為：

. z

-

TAN(number)

Number 為需要求正切的角度，以弧度表示。

如圖 5-41 所示為三角函數的例子。

圖 5 - 41 三角函數例子

5.3.4 日期時間函數

⑴ DATE 函數。

函數格式為：

DATE(year,month,day)

函數功能是返回在 Microsoft Office E*cel 日期時間代碼中代表日期的數字。從簡短的函數說明中讀者可能不太容易理解函數確實切功能， E*cel 還提供了更詳細的幫助。如圖 5 - 42 所示，單擊"有關該函數的幫助〞，即可獲得 DATE

函數更詳細的說明，如圖 5 - 43 所示。通過查看幫助我們可以知道，日期時間代碼是指 Microsoft E*cel 可將日期存儲為可用于計算的序列號，默認情況下，

1900 年 1 月 1 日的序列號是 1 ， 2008 年 1 月 1 日的序列號是 39448 ，這是因為它距 1900 年 1 月 1 日有 39448 天。

圖 5 - 42 查看函數的幫助

圖 5 - 43 函數 DATE 的幫助

. z

-

⑵ NOW 函數。

函數語法為：

NOW( )

函數功能是返回日期時間格式的當前日期和時間。如果在輸入函數前，單元格的格式為"常規〞，則結果將設為日期格式。如圖 5 - 44 所示。

⑶ YEAR 、 MONTH 、 DAY 、 HOUR 、 MINUTE 、 SECOND 。函數功能是返回參數所表示時間的年份、月份、日期、小時、分、秒的值。函數格式為：

YEAR/MONTH/DAY/HOUR/MINUTE/SECOND(rial_number)

如圖 5 - 44 所示即返回當前時間的年、月、日、小時、分、秒的值

圖 5 - 44 日期時間函數例子

5.3.5 統計函數

⑴ SUM 函數。

返回計算機單元格區域中所有數值的和。函數格式為：

SUM(number1,number2, ...)

⑵ AVERAGE 函數。

返回參數的平均值〔算術平均值〕。函數格式為：

. z

-

AVERAGE(number1,number2,...)

⑶ MA* 函數。

返回一組數值中的最大值。函數格式為：

MA*(number1,number2,...)

⑷MIN 函數。

返回一組數值中的最小值。函數格式為：

MIN(number1,number2,...)

⑸ COUNTA 函數。

返回參數列表中非空值的單元格個數。利用函數 COUNTA 可以計算單元格區域或數組中包含數據的單元格個數。函數格式為：

COUNTA(value1,value2,...)

⑹ COUNTBLANK 函數。

計算指定單元格區域中空白單元格的個數。 Range 為需要計算其中空白單元格個數的區域。函數格式為

COUNTBLANK(range)

. z

-

如圖 5 - 45 所示，要求所有同學的總分，在 H3 輸入公式" =SUM(C3:G3) 〞，然后復制公式到 H3-H16 即可。計算每門課程的平均分，如語文成績平均分，在

C18 中輸入公式" =AVERAGE(C3:C16) 〞；計算語文成績的最高分，在 C19 中輸入公式" =MA*(C3:C16) 〞；計算語文成績的最低分，在 C20 中輸入公式"

=MIN(C3:C16) 〞；計算語文科目的參考人數，在 C21 中輸入公式"

=COUNTA(C3:C16) 〞；計算語文科目的缺考人數，在 C22 中輸入公式"

=COUNTBLANK(C3:C16) 〞。其他的科目將公式復制即可。

圖 5 - 45 統計函數舉例

⑺ COUNTIF 函數。

計算區域中滿足給定條件的單元格的個數。函數格式為：

COUNTIF(range, criteria)

其中， Range 為需要計算其中滿足條件的單元格數目的單元格區域； Criteria

為確定哪些單元格將被計算在的條件，其形式可以為數字、表達式或文本。例如，條件可以表示為 32 、 "32" 、 ">32" 或 "apples" 。

例如要分別統計各門課程不及格的人數、 60 － 70 分〔大于等于 60 且小于

70 〕、、 70 － 80 、 80 － 90 、 90 － 100 之間的人數，在如圖 5 - 46 所例如子中，在 C24 中輸入公式" =COUNTIF(C3:C16,"<60") 〞即可。要計算在*個區域分布的值，如 60 － 70 之間，使用差值計算即可，用所有小于 70 分的. z

-

人數減去所有小于 60 分的人數，即得 60 － 70 之間的人數，在 C25 中輸入公式" =COUNTIF(C3:C16,"<70")-COUNTIF(C3:C16,"<60") 〞即可。

⑻ FREQUENCY 函數。

以一列垂直數組返回*個區域中數據的頻率分布。例如，使用函數 FREQUENCY 可以計算在給定的"分數圍〞測驗分數的個數。由于函數 FREQUENCY 返回一個數組，所以必須以數組公式的形式輸入。函數語法為：

FREQUENCY(data_array, bins_array)

其中 Data_array 為一數組或對一組數值的引用，用來計算頻率。如果

data_array 中不包含任何數值，函數 FREQUENCY 返回零數組。 Bins_array 為間隔的數組或對間隔的引用，該間隔用于對 data_array 中的數值進展分組。如果 bins_array 中不包含任何數值，函數 FREQUENCY 返回 data_array 中元素的個數。

例如仍統計各門課程不及格的人數、 60 － 70 、 70 － 80 、 80 － 90 、 90

－ 100 之間的人數，由于成績均為整數，可以把分割數組定為｛ 59.9 ， 69.9 ，

79.9 ， 89.9 ｝，填在單元格 B29 － B32 中，在 C29 中輸入公式"

=FREQUENCY(C3:C16,$B29:$B32) 〞，在 C29 中會顯示出分組中的第一個結果。由于結果數組一共包含 5 個結果，現在只顯示了一個，假設想將其余 4 個數字全部顯示，選中單元格 C29 － C33 ，按 F2 ，會將公式中的區域再次選中，再按" Ctrl ＋ Shift ＋ Enter 〞，數組中的 5 個數字就可全部顯示出來。

. z

-

注意，在公式中分割數組的地址要用絕對地址，否則復制公式時會出錯。

⑼ RANK 函數。

返回一個數字在數字列表中的排位。數字的排位是其大小與列表中其他值的比值〔如果列表已排過序，則數字的排位就是它當前的位置〕。語法格式為：

RANK(number, ref, order)

其中， Number 為需要找到排位的數字； Ref 為數字列表數組或對數字列表的引用， Ref 中的非數值型參數將被忽略； Order 為一數字，指明排位的方式。如果 order 為 0 〔零〕或省略， Microsoft E*cel 對數字的排位是基于 ref 為按照降序排列的列表；如果 order 不為零， Microsoft E*cel 對數字的排位是基于 ref 為按照升序排列的列表。

函數 RANK 對重復數的排位一樣。但重復數的存在將影響后續數值的排位。例如，在一列按升序排列的整數中，如果整數 10 出現兩次，其排位為 5 ，則 11 的排位為 7 〔沒有排位為 6 的數值〕。

例如在圖 5 - 46 中，根據學生的總分計算名次，在 I5 中輸入公式"

=RANK(H3,H$3:H$16) 〞，計算 H3 在 H3:H16 中的排名。注意在這里同樣要使用行絕對地址，請讀者自己分析原因。

圖 5 - 46 統計函數舉例 2

. z

-

5.3.6 查找與引用函數

⑴ VLOOKUP 函數。

在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。在 VLOOKUP 中的 V 代表垂直。函數語法為：

VLOOKUP(lookup_value,table_array,col_inde*_num,range_lookup)

其中， Lookup_value 為需要在數組第一列中查找的數值， Lookup_value 可以為數值、引用或文本字符串； Table_array 為需要在其中查找數據的數據表，可以使用對區域或區域名稱的引用，例如數據庫或列表。

Col_inde*_num 為 table_array 中待返回的匹配值的列序號。 Col_inde*_num

為 1 時，返回 table_array 第一列中的數值； col_inde*_num 為 2 ，返回

table_array 第二列中的數值，以此類推。如果 col_inde*_num 小于 1 ，函數

VLOOKUP 返回錯誤值值 #VALUE! ；如果 col_inde*_num 大于 table_array 的列數，函數 VLOOKUP 返回錯誤值 #REF! 。

Range_lookup 為一邏輯值，指明函數 VLOOKUP 返回時是準確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到準確匹配值，則返回小于 lookup_value 的最大數值；如果 range_value 為 FALSE ，函數 VLOOKUP 將返回準確匹配值。如果找不到，則返回錯誤值 #N/A 。

. z

-

如果 range_lookup 為 TRUE ，則 table_array 的第一列中的數值必須按升序排列；否則，函數 VLOOKUP 不能返回正確的數值。如果 range_lookup 為

FALSE ， table_array 不必進展排序。

通過在"數據〞菜單中的"排序〞中選擇"升序〞，可將數值按升序排列。

Table_array 的第一列中的數值可以為文本、數字或邏輯值。文本不區分大小寫。

如圖 5 - 47 所示，通過**或查找*名同學的總分和名次。在 B19 中輸入公式"

=VLOOKUP(A19,A3:H16,8) 〞，其中 A19 是要查找的容； H3:H16 是查找區域，保證**列是區域中的第一列；" 8 〞表示返回第 8 行的容，即總分。同理，在 C19

中輸入公式" =VLOOKUP(A19,A3:I16,9) 〞返回查找的名次。因為在例子中**是按照升序排列的，所以 range_lookup 可以不必設置。

假設要按照查找，則需在 B22 中輸入公式"

=VLOOKUP(A22,B3:H16,7,FALSE) 〞。

圖 5 - 47 VLOOKUP 實例

⑵ HLOOKUP 函數。

HLOOKUP 與 VLOOKUP 類似，不同的是在行中查找。讀者可參照幫助文件學習

HLOOKUP 的使用。

⑶ TRANSPOSE 函數。

有時候需要將 E*cel 表格轉置，即將行變成列，將列變成行。例如將圖 5 - 47

中的成績表變成如圖 5 - 48 所示的形式，即為轉置。

. z

-

圖 5 - 48 轉置的結果

轉置可以使用菜單，也可以使用函數。將圖 5 - 47 中成績局部選中，復制到剪貼板，然后將鼠標移到要粘貼的位置，選擇菜單命令"編輯〞→"選擇性粘貼〞，在彈出的如圖 5 - 49 所示的選擇性粘貼對話框中將"轉置〞勾選，單擊"確定〞按鈕，即可將所選擇容轉置粘貼到指定位置。

圖 5 - 49 選擇性粘貼

但此種粘貼方法只是粘貼了原表的值。如果數據有變化，在轉置后的數據中不能實時更新。 E*cel 提供了轉置函數 TRANSPOSE 來完成此任務。函數格式為：

TRANSPOSE(array)

Array 為需要進展轉置的數組或工作表中的單元格區域。例如將圖 5 - 47 中

A2:I16 區域中的數據轉置粘貼到一個新的工作表中，假定所在的工作表為

Sheet2 ，要粘貼到工作表 Sheet3 中，則在 Sheet 3A1 單元格中輸入公式"

=teanspo(Sheet2!A3:I16) 〞，回車后在單元格中顯示，即表示結果沒有顯示。這是因為 TRANSPOSE 函數同 FREQUENCY 函數類似，結果為一個數組。原數據為 15 行 9 列，轉置后數據應為 9 行 15 列，因此在 Sheet3 中，選中 A1:O9 區域，按 F2 鍵，然后按" Ctrl ＋ Shift ＋ Enter 〞，即可顯示出轉置結果。

5.3.7 文本函數

⑴ LEN 函數。

. z

-

LEN 返回文本字符串中的字符數。函數語法為：

LEN(te*t)

Te*t 是要查找其長度的文本。空格將作為字符進展計數。

⑵ LEFT 函數。

LEFT 基于所指定的字符數返回文本字符串中的第一個或前幾個字符。函數語法為：

LEFT(te*t,num_chars)

Te*t 是包含要提取字符的文本字符串。 Num_chars 指定要由 LEFT 所提取的字符數。 Num_chars 必須大于或等于 0 。如果 num_chars 大于文本長度，則 LEFT

返回所有文本。如果省略 num_chars ，則假定其為 1 。

⑶ RIGHT 函數。

根據所指定的字符數返回文本字符串中最后一個或多個字符。用法與 LEFT 類似。

⑷ MID 函數。

返回文本字符串中從指定位置開場的特定數目的字符，該數目由用戶指定。函數語法為：

MID(te*t,start_num,num_chars)

. z

-

Te*t 是包含要提取字符的文本字符串。 Start_num 是文本中要提取的第一個字符的位置。文本中第一個字符的 start_num 為 1 ，以此類推。 Num_chars 指定希望 MID 從文本中返回字符的個數。

⑸ TRIM 函數。

作用是除了單詞之間的單個空格外，去除文本中所有的空格。在從其他應用程序中獲取帶有不規則空格的文本時，可以使用函數 TRIM 。函數語法為：

TRIM(te*t)

Te*t 為需要去除其中空格的文本。

5.3.8 邏輯函數

⑴ TRUE 函數。

返回邏輯值 TRUE 。語法為：

TRUE( )

⑵ FALSE 函數。

返回邏輯值 FALSE 。語法為：

FALSE()

⑶ AND 函數。

. z

-

所有參數的邏輯值為真時，返回 TRUE ；只要一個參數的邏輯值為假，即返回

FALSE 。語法為：

AND(logical1,logical2, ...)

Logical1, logical2, ... 表示待檢測條件值，各條件值可為 TRUE 或 FALSE 。參數必須是邏輯值 TRUE 或 FALSE, 或者包含邏輯值的數組或引用。如果數組或引用參數中包含文本或空白單元格，則這些值將被忽略。如果指定的單元格區域包括非邏輯值，則 AND 將返回錯誤值 #VALUE! 。

⑷ OR 函數。

在其參數組中，任何一個參數邏輯值為 TRUE ，即返回 TRUE ；只有當所有參數值均為 FALSE 時才返回 FALSE 。函數語法為：

OR(logical1,logical2,...)

Logical1,logical2,... 為需要進展檢驗的條件，分別為 TRUE 或 FALSE 。

⑸ NOT 函數。

對參數的邏輯值求反：參數為 TRUE 時返回 FALSE ；參數為 FALSE 是返回

TRUE 。函數格式為：

NOT(logical)

Logical 為一個可以計算出 TRUE 或 FALSE 的邏輯值或邏輯表達式。

. z

-

⑹ IF 函數。

執行真假值判斷，根據邏輯計算的真假值，返回不同結果。可以使用函數 IF 對數值和公式進展條件檢測。語法為：

IF(logical_test,value_if_true,value_if_fal)

Logical_test 表示計算結果為 TRUE 或 FALSE 的任意值或表達式。例如，

A10=100 就是一個邏輯表達式，如果單元格 A10 中的值等于 100 ，表達式即為

TRUE ，否則為 FALSE 。本參數可適用任何比擬運算符。Value_if_true 是

logical_test 為 TRUE 時返回的值。 Value_if_fal 是 logical_test 為

FALSE 時返回的值。 IF 函數可以嵌套使用。

例如在圖 5 - 50 中，假設總分大于等于 300 分，則顯示"通過〞，否則顯示"不通過〞。則可在 J3 單元格中輸入公式" =IF(H10<300," 不及格 "," 及格

") 〞。如果將成績分為四個等級"優秀〔大于等于 450 分〕〞、"良好〔大于等于 400 小于 450 〕〞、"及格〔大于等于 300 小于 400 〕〞、"不及格〔小于

300 〕〞，則可輸入嵌套公式" =IF(H4<300," 不及格 ",IF(H4<400," 及格

",IF(H4<450," 良好 "," 優秀 "))) 〞。讀者可自己分析。

圖 5 - 50 IF 函數舉例

5.3.9 財務函數

財務函數很多，這里以 PMT 函數為例。

. z

-

PMT 函數基于固定利率及等額分期付款方式，返回貸款的每期付款額。函數語法為：

PMT(rate,nper,pv,fv,type)

其中， Rate 為貸款利率； Nper 為該項貸款的付款總數； Pv 為現值，或一系列未來付款的當前值的累積和，也稱為本金； Fv 為未來值，或在最后一次付款后希望得到的現金余額，如果省略 fv ，則假設其值為零，也就是一筆貸款的未來值為零。 Type 為數字 0 或 1 ，用以指定各期的付款時間是在期初還是期末。如圖 5 - 51 所示，貸款總額為 60 萬，貸款年利率為 5.22 ％，還款期數為 30

年，即 360 個月，則月還款額的計算公式為" =PMT(B2/12,B3,B1) 〞。

圖 5 - 51 PMT 函數舉例

電子表格軟件E*cel

6 排序和篩選

6.1 排序

排序是計算機數據處理中的一個重要方面，排序一般分為升序和降序兩個方向。升序是把數據按從小到大的順序〔如：從 1 到 9 或從 A 到 Z 〕排列，而降序則是把數據按從大到小的順序〔如：從 9 到 1 或從 Z 到 A 〕排列。

E*cel 具有對工作表中的數據進展排序的功能，既可以對數值型數據進展排序，也可以對文本型數據進展排序。借助于 E*cel 提供的排序功能，可以十分方便. z

-

地把雜亂無章的數據很快地排好序。排序一般有兩種方法：使用"常用〞工具欄里的一對排序按鈕或使用命令菜單"數據〞→"排序〞。

將如圖 5 - 52 模擬考試成績表，按總分從高到低的順序排列，操作步驟為：將"總分〞單元格設置為活動單元格，單擊"常用〞工具欄中的降序按鈕擬考試成績表中的總分按從高分到低分的順序排列。

，則模圖 5 - 52 排序舉例

或在"模擬考試成績表〞中，單擊任意一個單元格，選擇"數據〞→"排序〞命令，彈出"排序〞對話框，如圖 5 - 53 所示，在"主要關鍵字〞下拉列表框中，選擇"總分〞，并選擇"降序〞。單擊"確定〞按鈕，即可按總分的降序對成績表進展排序。

圖 5 - 53 排序對話框

利用排序對話框還可以實現多重排序。在"排序〞對話框中，在"主要關鍵字〞、"次要關鍵字〞以及"第三關鍵字〞下拉列表框中，選擇需要排序的項，并選擇排序的方向〔遞增或遞減〕，可實現多重排序。如在圖 5-54 所示模擬考試成績表中，擬實現當有總分一樣的學生時，按照數學成績的上下來決定其排名。操作步驟為：選中任意一個單元格，選擇"數據〞→"排序〞命令，在彈出的"排序〞對話框中，在"主要關鍵字〞下拉列表框中，選擇"總分〞，并選擇"降序〞；在"次要關鍵字〞下拉列表框中，選擇"數學〞，并選擇"降序〞，單擊"排序〞對話框中的"確定〞按鈕，多重排序就完成了。

. z

-

圖 5 - 54 多重排序舉例

6.2 篩選

所謂篩選是從工作表中找出滿足*些條件的記錄。例如，從工資表中找出工資總額超過 1000 元的員工，在學生成績統計表中找出總分超過 300 分的學生等。當工作表中的數據量非常大時，人工篩選會消耗大量時間，如運用 E*cel 提供的篩選功能，能有效提高工作效率。

6.2.1 自動篩選

在"模擬考試成績表〞中篩選出總分超過 400 分的學生，操作步驟如下：在"模擬考試成績表〞中需要進展篩選的單元格區域中的任意一個單元格中單擊鼠標，選擇"數據〞→"篩選〞→"自動篩選〞命令，工作表進入篩選狀態，每個列標題的右下角都出現一個下拉篩選箭頭，如圖 5 - 55 所示。

圖 5 - 55 篩選狀態

鼠標單擊需要進展篩選的列標題右側的篩選箭頭，在該列位置上出現一個下拉列表框，里面顯示假設干個選項，如圖 5-56 所示，其中定義了可以對數據進展的一些操作，如想顯示含有特定值的數據行，用鼠標單擊需要顯示的數值，如想顯示滿足*一條件的數據行，用鼠標選擇其中的"自定義〞，屏幕上出現"自定義自動篩選方式〞對話框，如圖 5-57 所示，對話框的左邊是比擬運算符列表框，右邊是取值圍。本例中，在比擬運算符列表框里選擇符合要求的運算符"大于〞，. z

-

在右邊的取值圍輸入 400 ，如圖 5-58 所示。單擊對話框的"確定〞按鈕，數據表中只顯示符合條件的數據項，如圖 5 - 59 所示。

圖 5 - 56 選擇列表框

圖 5 - 57 "自定義自動篩選方式〞對話框

圖 5 - 58 自定義篩選方式舉例

圖 5 - 59 篩選結果－總分大于 400 分

如有多個條件，可根據需要在第一個條件的下方選擇后再用同樣的方法構造第二個條件式。

或者，然6.2.2 取消篩選

當工作表處于篩選狀態時，用鼠標單擊數據區域中的任意一個單元格，選擇"數據〞→"篩選〞命令，單擊"自動篩選〞，取消"自動篩選〞命令前面的對勾，即可退出篩選狀態，工作表恢復正常顯示。

6.3 高級篩選

利用高級篩選可以使篩選更為靈活。例如要查找語文成績大于 85 分的學生，如圖 5 - 60 所示，在 A18 、 A19 〔也可以是其他位置〕中輸入列名"語文〞和條件" >85 〞。

圖 5 - 60 高級篩選舉例

. z

-

選擇菜單命令"數據〞→"篩選〞→"高級篩選〞，彈出高級篩選對話框，如圖 5 -

61 所示，結果的顯示可以是"在原有區域顯示篩選結果〞，也可以"將篩選結果復制到其他位置〞。列表區域選中數據區 $A$2:$I$16 ，條件區域選中

$A$18:$A$19 。單擊"確定〞按鈕，在原數據區顯示語文成績大于 85 的學生，如圖 5 - 62 所示。

圖 5 - 61 高級篩選對話框

圖 5 - 62 高級篩選結果

如果條件不止一個，則需要將條件都表示出來。如果多個條件之間是"與〞的關系，則要將條件放在一行，如果是"或〞的關系，則條件不能放在一行。

例如要查找語文和數學成績都大于 85 分的學生，則設置條件如圖 5 - 63 所示。在高級篩選對話框中設定"列表區域〞為" $A$2:$I$ 16 〞，設定"條件區域〞為"$A$18:$B$ 19 〞。結果如圖 5 - 64 所示。

圖 5 - 63 多條件高級篩選

圖 5 - 64 多條件高級篩選結果

如果設定"或〞的條件關系，則可輸入條件如圖 5 - 65 所示。讀者可自行完成操作。

圖 5 - 65 高級篩選"或〞條件的設定

電子表格軟件E*cel

. z

-

7 統計圖表的生成

用戶不僅可以對數據表中的數據進展快速的統計和計算，還可以用這些數據自動生成各種形式的統計圖表，使工作表中的數據更具有直觀性。

用 E*cel 自動生成統計圖表的方法非常簡單，只要按照"圖表向導〞一步一步操作即可。同時，這些統計圖表會隨著原始數據的變化自動發生相應的變化。

7.1 統計圖表生成實例

以各門成績的平均分為例，制作一統計圖表，原始數據如圖 5 - 66 所示，操作步驟如下：

圖 5 - 66 利用成績平均分制作柱形圖

⑴選擇用于生成統計圖表所需要的數據區域，如圖 5 - 67 所示。

圖 5 - 67 選擇要生成統計圖表所需要的區域

⑵單擊常用工具欄中的"圖表向導〞按鈕，或選擇菜單命令"插入〞→"圖表〞。屏幕上出現圖表向導之一—"圖表類型〞對話框，如圖 5 - 68 所示。

圖 5 - 68 "圖表類型〞對話框

圖表類型對話框左側窗口里顯示的是 E*cel 提供的 14 種圖表類型：柱形圖、條形圖、折線圖、餅圖、 *Y 散點圖、面積圖、圓環圖、雷達圖、曲面圖、氣泡圖、股價圖、圓柱圖、圓錐圖和棱錐圖，右側窗口里顯示的是被選中的*一類圖表的子圖表類型，如圖 5 - 69 所示。

. z

-

圖 5 - 69 各種常見的子圖表類型

⑶在圖表類型對話框的左側窗口選擇一種圖表類型，例如，柱形圖。此時右側窗口同步顯示柱形圖表類型的 7 個子圖表類型，選擇*一個子圖表類型，如第一個，單擊"下一步〞按鈕。屏幕上顯示圖表向導之二—"圖表源數據〞對話框，如圖 5

- 70 所示，其中的數據區域顯示先前所選擇的數據區域，如需修改數據源，可以通過"數據區域〞重新選擇。選擇圖中的"系列〞選項卡，圖 5 - 71 所示，在這里我們可以設置"分類〔 * 〕軸標志〞，如圖 5 - 72 所示，設定好后，單擊"下一步〞按鈕。

圖 5 - 70 "圖表數據源〞對話框

圖 5 - 71 "圖表數據源〞對話框"系列〞選項卡

圖 5 - 72 設置分類 * 軸標志

⑷屏幕上顯示圖表向導之三—"圖表選項〞對話框，如

圖 5-73 所示。該對話框中共有六個選項卡，可以對圖表的標題、坐標軸、網格線、圖例、數據標志、數據表等進展設置。

圖 5 - 73 "圖表選項〞對話框

在圖表標題框里輸入"課程平均分統計圖〞，在分類 * 軸輸入"學科〞，在數值 Y

軸輸入"分數〞，單擊"下一步〞按鈕。

. z

-

⑸屏幕上出現圖表向導之四 - "圖表位置〞對話框，如圖 5 - 74 所示。用戶可選擇將圖標"作為新工作表〞插入或"作為其中的對象插入。如選擇"作為其中的對象插入，單擊"完成〞按鈕，圖表就被嵌入到工作表，如圖 5 - 75 所示；如選擇"作為新工作表〞，單擊"完成〞按鈕，圖表就建立在一新的工作表。

圖 5 - 74 "圖表位置〞對話框

圖 5 - 75 圖表嵌入到表格中

7.2 統計圖表中的根本元素

統計圖表中有 8 個根本元素，如圖 5 - 76 所示：

標題：每圖表里可以有三種標題：圖表標題、 * 軸標題、 Y 軸標題。

圖例：對繪圖區中各個元素的含義加以解釋，圖例可以放在 5 個不同的位置：底部、右上角、靠上、靠右、靠左。

繪圖區：用來顯示圖表。

坐標軸：分為類型軸和數值軸兩種，其中，類型軸也叫做 * 軸，一般用來表示時間單位；數值軸也叫做 Y 軸，一般用來表示銷售額、產量或分數等。

網格線：統計圖表中有兩種網格線：主要網格線和次要網格線。主要用于襯托圖形元素，有助于數據的分析和比擬。

數據表：數值與圖表同時顯示，如圖 5 - 77 所示。

. z

-

數據標志：在圖表中顯示數值大小或數據的標志，用戶可根據實際情況決定是否添加。

圖表區域：上述各元素所在的區域。

圖 5 - 76 圖表中的各個元素

圖 5 - 77 數據表和圖表同時顯示

7.3 修改統計圖表

當統計圖表生成后，如對圖表中的*些元素不滿意，如：圖例的顯示位置、大小、格式，標題的字體、字號、顏色、繪圖區的大小等，可以對統計圖表做進一步的修改。修改統計圖表的根本方法如下：

7.3.1 將*個元素移動位置

單擊圖表中需移動位置的元素所在的區域，該元素周圍出現一個粗虛線框，將鼠標光標對準虛線框，按住鼠標左鍵不放，并拖動鼠標，可將被選中的元素移動位置。

7.3.2 改變*個元素的大小

單擊要改變大小的元素，該元素周圍出現一個方框，方框周圍有 8 個尺寸柄，用鼠標拖動尺寸柄，即可改變該元素的大小。文字元素的大小可以通過改變文字的字號來實現。

. z