大學計算機基礎（第六版）課件 05-2 辦公軟件基礎知識與功能設計-電子表格軟件

第5章辦公軟件基礎知識與功能設計5.3

電子表格軟件

電子表格（Spreadsheet）是指能夠將數據表格化顯示，并且對數據進行計算與統計分析以及圖表化分析的計算機應用軟件。

常見的電子表格有微軟Microsoft

Office家族中的Excel金山WPS

Office家族中的WPS表格永中科技Office家族中的永中表格。在眾多的電子表格中，Microsoft

Office家族的重要成員Excel是用途比較廣泛的辦公軟件之一。

Excel主要功能是能夠方便地制作出各種表格和報表。在電子表格中可使用公式對數據進行復雜的運算、用各種統計圖表的形式直觀、明了地表現數據，進一步學習，可直接進行數據的統計分析工作。5.3.1

電子表格概述（1）表格的構成：

一般由表的標題、表頭（行標題、列標題）和單元格內的數據四個主要部分組成。同類型數據為列，數據的增加為行。1．制作表格的基礎知識5.3.2

電子表格的基本概念（2）表格的結構

①

表的標題應放在表的上方，表示表的名稱。也可以以頁眉的形式體現出來。表頭（行標題和列標題）通常安排在表格的第一列和第一行，它所表示的是所研究問題的類別名稱和指標名稱，通常也被稱為“類”。表格可以顯示行列網格，也可以不顯示或根據需要顯示一部分。

②

表的列設計，表的結構設計和大?。恍械亩嗌俦硎颈頂祿亩嗌?。

③

單元格，行和列交叉的地方稱為單元格，單元格是存放數據和計算結果的地方，是構成表格的基本單位。

④

表外附加通常放在統計表的下方，主要包括資料來源、指標的注釋、必要的說明等內容。2．Excel電子表格的內容

Excel電子表格的內容主要是表格、單元格內部的內容和單元格外部的內容。1、單元格內部的內容包括數據、公式、函數和批注；2、單元格外部的內容包括各種對象，如常規(guī)對象、特殊對象、動態(tài)對象（域）以及ActiveX控件等，以及自帶的基于數據分析的常用工具。（1）表格

各種類型的表格或專業(yè)表格。其中需要設置表格樣式、單元格邊框和樣式、數據的字體和對齊方式等。（2）數據

在單元格里可以直接輸入數據，為了便于數據管理，Excel將其分為不同的數據類型。Excel中內置有常規(guī)、數值、貨幣、日期/時間、百分比、文本、自定義等12種數據類型，這些數據類型可劃分為4種大類：文字（字符）型、數字（數值）型、日期/時間型、公式與函數。2．Excel電子表格的內容文字（字符）型數據及輸入說明性與解釋性的數據描述稱為文字型。文字型包括：漢字、字母、數字、特殊符號等符號顯示格式以左對齊為缺省的格式顯示注意（1）如果輸入的字符串的首字符是“=”號，則應先輸入一個單引號“'”（注意必須是半角字符的單引號），再輸入等號和其他字符。(2)如果輸入的文字是類似于郵政編碼、電話號碼之類的文本型數字，則應先輸入單引號“’”（注意也必須是半角字符的單引號），再輸入數字。數值型包括：正號、負號、小數點、數字、百分號％、千分位號（，）、括號（）、$、E等正確表示數值的字符組合顯示格式以右對齊為缺省的格式顯示數值型數據及輸入日期和時間型包括：正確表示日期和時間的字符顯示格式以右對齊為缺省的格式顯示輸入操作日期的年、月、日之間用“-”分隔或用“/”分隔時間的時、分、秒之間用（：）分隔

AM（上午）PM（下午）日期和時間型數據及輸入算術運算符(%,^,×

,/,＋,－)優(yōu)先級從左到右比較運算符（≤,≥,＝,＜,＞,<>）比較運算符可以比較的數據有數字型、文字型、日期型。文本運算符（&）文本運算符（&）用來連接不同的文本產生新的文本單元格引用符（

“：”，“ ”

，“，”）。冒號：定義一個單元格區(qū)域。如：A1：A3空格：交集運算。如：A1：C2 C1：D2 等價于C1：C2逗號：并集運算。如：A1：A4，B1：B2在公式中可以使用的運算符

函數是具有獨立的計算方法或算法功能，并有確定結果的、可被共享的程序代碼片段。

一個完整的函數包括三部分：函數名、參數和結果

函數語法格式為：函數名稱（參數1,參數2,…）例如：Sum(b2:f6)函數批注是指對單元格或內容需要做解釋，而添加的相應注釋。批注

這部分內容主要包括：常規(guī)對象、特殊對象和ActiveX

控件等。電子表格中，基于數據分析的常用工具和自帶的對象，如常規(guī)對象的圖表或一個重要的ActiveX

控件（Microsoft

Graph，MS-Graph），均為圖表化工具。

①基于數據分析的常用工具：排序、篩選、分類匯總、分組、分列、數據透視表等。

②圖表化工具：圖表化是指數據的圖形化，即將一維、二維或三維數據以不同圖形（點、線或面、圖案等）的方式繪制出來的圖。圖表更能形象地表達數據間的關系和特點，并能夠做到層次分明、調理清楚、易于理解。單元格外部的內容在Excel中共有4種視圖：普通視圖、頁面布局視圖、分頁預覽視圖、自定義視圖和全屏顯示。（1）普通視圖

普通視圖是默認視圖，

有三個工作表，可方便查看全局數據及結構，是編輯數據和創(chuàng)建對象的主要界面。普通視圖下不顯示頁眉頁腳、頁邊距，其中人工分頁符顯示為一條水平虛線，在普通視圖下可以添加（取消）分組標記“+”和“-”，也可以添加（取消）分類匯總標記“+”和“-”。視圖（2）頁面布局視圖

頁面布局視圖是以頁面大小方式顯示工作表，并可編輯頁眉和頁腳內容。它不顯示其它符號和標記，如分頁符等。

一般認為頁面布局就是一種“所見即所得”的視圖，它可以顯示出頁面大小、布局排版、編輯頁眉和頁腳、查看調整頁邊距、處理圖表對象以及其它各種對象，在頁面布局中看到的外觀效果也就是打印結果外觀，因此，頁面布局適用于概覽整個工作表的總體效果并打印。（3）分頁預覽視圖

分頁預覽視圖是以縮小工作表的方式，全覽表格在工作表中的布局。同時顯示分頁符，并標記第幾頁，以便在工作表中人工拖動分頁符的位置設置頁面大小，或刪除分頁符。

在分頁預覽視圖下可以添加（取消）分組標記“+”和“-”，也可以添加（取消）分類匯總標記“+”和“-”。（4）自定義視圖

自定義視圖可以定位多個自定義視圖，根據用戶需要，保存不同的打印設置、隱藏行、列及篩選設置，更具個性化。（5）全屏顯示

全屏顯示隱藏了菜單及選項卡和功能區(qū)，使工作表幾乎放大到整個顯示瓶，充分利用屏幕顯示更多的數據。全屏顯示要與其他視圖配合使用，按ESC鍵可退出全屏顯示。（1）單元格地址標識①單元格地址

單元格地址是由列標和行號來標識組成的單元格空間（也稱為單元格名稱），可以直接被引用實現數據的讀寫。

表示單元格名稱的方法有兩種：一是用列標和行號，即列標在左，行號在右組合的字符串。例如，第2行第3列單元格的名字是C2，C2非常直接地指向了所屬單元格。另一種是直接命名。例如，C2單元格命名為“單價1”，C3單元格命名為“單價2”，間接地表示了C2和C3單元格。②單元格區(qū)域地址

單元格區(qū)域指的是由多個相鄰單元格形成的矩形區(qū)域，表示區(qū)域地址或名稱同樣有兩種方法：用單元格地址和直接命名。將單元格矩形區(qū)域左上角與右下角單元格的地址，中間以冒號隔開為區(qū)域地址。比如第2行第A(1)列至第5行第D(4)列區(qū)域，其區(qū)域地址即為A2:D5，表示4行×4列=16個單元格空間。同上也可以直接給單元格區(qū)域地址命名。（2）引用運算符

引用運算符在公式中主要用于選取單元格區(qū)域，共有3個：冒號“:”

,

逗號“，”,

空格“

”（3）地址標識引用的種類

單元格引用有相對引用、絕對引用、混合引用、三維地址引用等多種方式，在公式中不同方式的引用，其含義不同。相對引用是指當把一個含有單元格或單元格區(qū)域地址的公式復制到新的位置時，公式中的單元格或單元格區(qū)域地址隨著改變，公式的值將會依據更改后的單元格或單元格區(qū)域地址的值重新計算。絕對引用是指在公式中的單元格或單元格區(qū)域地址不隨著公式位置的改變而發(fā)生改變。不論公式的單元格處在什么位置，公式中所引用的單元格位置都是其在工作表中的確切位置。絕對單元格引用的形式是在每一個列標及行號前加一個$符號混合引用是指單元格或單元格區(qū)域的地址部分是相對引用，部分是絕對引用。如$B2、B$2在公式中，可以引用另一工作簿或同一工作簿的另一張工作表中的單元格，這就用到三維地址引用。三維地址引用的格式是：[工作簿名稱]工作表名稱!單元格地址。在此需要注意的是方括號“[

]”和感嘆號“!”均為英文字符。在同一個工作簿中引用數據，可省略“[工作簿名稱]”。

三維地址引用

例如：如果在當前工作表的D2單元格輸入公式：=[成績]Sheet3！A1+A2表示引用“成績”工作簿Sheet3工作表中的A1單元格的內容和當前工作簿的當前工作表中的A2單元格的內容相加不同工作表中的數據所在單元格地址的表示：工作表名稱!

單元格引用地址不同工作簿中的數據所在單元格地址的表示：[工作簿名稱]

工作表名稱!

單元格引用地址填充柄的功能非常強大，使用填充柄可以實現數字的復制或填充、字符的復制或填充、時間/日期的復制或填充、公式的復制等操作，使得計算得到大大的簡化。填充柄

Excel的文本對象有數字型文本，比如手機號碼、銀行帳號、學號和身份證號等。

要將這些數字轉換成文本，按文本型處理，方法是在輸入此類數字型文本時，應先輸入一個英文單引號“＇”，再輸入其數字.

如在B4輸入學號“‘201405352046”。數據單元格中將會在左上角顯示一個綠色三角“

”，是數字型文本標記。數字型文本標記分頁符是指將工作表按頁面設置大小分割成上下左右等幾個區(qū)域的貫穿整個屏幕的水平和垂直虛線，“------”。在分頁預覽視圖下，分頁符標記為藍色實線，可以拖動調整其位置，改變頁面大小。系統可自動加載分頁符，也可以人為插入分頁符。①自動分頁符------

當表格數據超過一頁的有效高度（或寬度）時系統會自動分頁，自動分頁符會隨著表格變化而動態(tài)變化，作用僅僅是分頁。②人工分頁符------

人工分頁符是指人為插入把表格行強行轉入下一頁顯示的標識。人工分頁符的位置不會隨著表格變化而變化。位置是在當前單元格的左上角插入水平和垂直分頁符。分頁符

凍結窗格是將工作表窗口分為上下左右四個窗格，其中滾動工作表時上部和左部窗格保持固定可見。而拆分窗口是將工作表窗口拆分為上下左右四個大小可調和獨立滾動的窗格。

當工作表中行數和列數較多時，一旦向下或向右滾屏時，則上面的標題行或左側的列標題跟著滾動隱藏，使得數據無法與標題對照查看或錯位。凍結窗格或拆分窗口把工作表窗口分為四個窗格，便可對照看解決這一問題。

凍結窗格標識為水平實線和垂直實線，拆分窗口標識為水平和垂直窗口邊線。標識位置是在當前單元格的左上角插入水平和垂直標識線。凍結窗格和拆分窗口標識布局可分成整體的頁面布局和局部的對象布局。（1）頁面布局：對頁面的文字、背景和頁面版面格式設置。包括以下幾個方面：

①主題：更改整個文檔的總體設計，包括顏色、字體和效果。

②頁面設置：包括頁面方向、頁邊距、紙張大小方向、頁眉頁腳、插入/刪除人工分頁符、頁面背景、打印區(qū)域和打印標題等設置。

③工作表設置：行高列寬、縮放比例的設置。網格線查看（打?。祟}查看（打印）的設置。（2）對象布局：對象與對象，表格與對象的平面幾何位置關系稱為對象布局。

①對象與對象呈現相鄰、疊加和覆蓋三種關系，在Excel中為上移一層、下移一層、對齊方式、組合和旋轉等關系；

②表格與對象呈現相鄰和覆蓋兩種關系。表格和對象要么是相鄰，要么表格始終位于對象底端。表格與對象關系。對象始終處于表格上方，處于浮動方式。布局（排版）（1）Excel文件數據來源

①用戶通過鍵盤輸入的原始數據。

②來自Access數據表數據，系統自動將其轉換為Excel表格數據。

③來自網站的.htm或.html，可將網頁文件直接轉換為Excel文件。

④來自文本文件.txt，將文本文件數據導入Excel工作表中。

⑤將.xml文件導入Excel工作表中。

⑥將其他數據庫（如Sql

Server）中的數據表數據導入到Excel工作表中。5.3.3

電子表格的功能設計與操作原理（2）Excel支持的文件類型設計

①Excel文件默認的保存類型是.xlsx（2016），除此之外也可以保存為.xls（2003），啟用宏的為.xlsm，被稱為工作簿（Book）。

②模板文件為.xlt（2003之前）或xltx（2016），啟用宏的模板文件.xltm。

③網頁文件為html或mhtml。

④兼容文件為記事本的txt、OpenDocument電子表格ods和可擴展標記語言的xml文件等。

⑤轉換文件：Excel文件可直接轉換為pdf。5.3.3

電子表格的功能設計與操作原理Excel文件（工作簿）組成結構設計工作?。ㄎ募鸛LSX）工作表1(表名1)工作表2(表名2)………工作表n（表名n）表(行數,列數)單元格A1(A列,1行)單元格A2(A列,2行)單元格An(A列,n行)列A B C D …行12…nExcel

2016工作窗口32選項卡

功能區(qū)單元格

工作表標簽插入函數編輯欄名稱框填充柄工作表編輯區(qū)Excel2010的工作窗口Excel電子表格的內容設計了3個部分，包括表格、單元格內部的內容和單元格外部對象。表格是指在工作表中制作各種類型的表格。單元格內部的內容包括：各種類型的數據、公式、函數和批注等編輯。單元格外部的內容包括各種對象如下：

①常規(guī)對象包括：占位符、文本框、圖形、圖像、剪貼畫、藝術字、圖表等。

②特殊對象包括：頁眉頁腳、批注等。

③ActiveX控件包括：ActiveX控件中的外來“其他控件”和窗體控件，如文本控件、圖片控件、組合框、列表框、單選鈕、復選框、命令按鈕、組框、日期選取控件等,以及圖表化工具（Microsoft

Graph）等。Excel數據表內容設計Excel功能界面設計與主要功能圖5-54

12個浮動（工具）選項卡Excel的函數庫中共分為11類，分別是：

數據庫函數、日期與時間函數、工程函數、財務函數、信息函數、邏輯函數、查詢和引用函數、數學和三角函數、統計函數、文本函數以及用戶自定義函數。要求掌握的常用函數：

AVERAGE（平均值）、SUM（求和）、MAX（最大值）、MIN（最小值）、MOD（取余）、RANK（獲取排位名次）、LEFT（獲取左

側指定長度字符）、MID（返回指定位置和長度的字符）、IF（條件判斷）、SUMIF

（條件求和）、COUNTIF（條件計數）、COUNT（數值計數）、COUNTA（文本計數）、DATE（日期）、YEAR（獲取日期中的年份）、MONTH（獲取日期中的月份）和DAY（獲取日期中的天數）、VLOOKUP（縱向查找函數）等。函數類型和功能創(chuàng)建數據清單（數據表）要遵循以下原則：

數據清單的第一行必須為文本類型，即必須要有標題行

一個數據清單最好占用一個工作表。

數據清單是一片連續(xù)的數據區(qū)域，不要出現空行和空列

每一列包含相同類型的數據。

工作表的數據清單與其他數據間至少應留出一個空列和一個空行；不要在單元格開頭和末尾輸入多余空格，否則會影響排序與搜索。5.3.3

電子表格的功能設計與操作原理規(guī)范的數據清單，數據便可被整體利用和分析，為此系統設計有：排序、篩選、分組、分列、分類匯總、數據透視表等常用工具，5.3.3

電子表格的功能設計與操作原理在Excel的圖表化工具MS-Graph中，提供了可以創(chuàng)建11個大類的圖表，每一個大類類型里又細分了若干個小類。11個大類分別是：

柱形圖、折線圖、餅圖、條形圖、面積圖、XY（散點圖）、股價圖、曲面圖、圓環(huán)圖、氣泡圖和雷達圖等。圖表產生的位置分為兩類：

以對象嵌入到已有的工作表中和形成新的工作表（稱圖表工作表）

嵌入圖表可看作一個圖形對象，浮動在工作表中，并作為工作表的一部分進行保存。

圖表工作表是整個工作表為一個獨立的圖表工作表。Excel圖表化嵌入圖表----柱型39圖表工作表—餅型40Excel2016電子表格的編輯和排版的主要步驟和流程如下：

（1）新建工作簿：默認空白工作簿，進行預期的頁面設置。