大學(xué)計算機(jī)基礎(chǔ)（第六版）課件 05-2 辦公軟件基礎(chǔ)知識與功能設(shè)計-電子表格軟件

第5章辦公軟件基礎(chǔ)知識與功能設(shè)計5.3

電子表格軟件

電子表格（Spreadsheet）是指能夠?qū)?shù)據(jù)表格化顯示，并且對數(shù)據(jù)進(jìn)行計算與統(tǒng)計分析以及圖表化分析的計算機(jī)應(yīng)用軟件。

常見的電子表格有微軟Microsoft

Office家族中的Excel金山WPS

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

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

Excel主要功能是能夠方便地制作出各種表格和報表。在電子表格中可使用公式對數(shù)據(jù)進(jìn)行復(fù)雜的運(yùn)算、用各種統(tǒng)計圖表的形式直觀、明了地表現(xiàn)數(shù)據(jù)，進(jìn)一步學(xué)習(xí)，可直接進(jìn)行數(shù)據(jù)的統(tǒng)計分析工作。5.3.1

電子表格概述（1）表格的構(gòu)成：

一般由表的標(biāo)題、表頭（行標(biāo)題、列標(biāo)題）和單元格內(nèi)的數(shù)據(jù)四個主要部分組成。同類型數(shù)據(jù)為列，數(shù)據(jù)的增加為行。1．制作表格的基礎(chǔ)知識5.3.2

電子表格的基本概念（2）表格的結(jié)構(gòu)

①

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

②

表的列設(shè)計，表的結(jié)構(gòu)設(shè)計和大小；行的多少表示表數(shù)據(jù)的多少。

③

單元格，行和列交叉的地方稱為單元格，單元格是存放數(shù)據(jù)和計算結(jié)果的地方，是構(gòu)成表格的基本單位。

④

表外附加通常放在統(tǒng)計表的下方，主要包括資料來源、指標(biāo)的注釋、必要的說明等內(nèi)容。2．Excel電子表格的內(nèi)容

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

各種類型的表格或?qū)I(yè)表格。其中需要設(shè)置表格樣式、單元格邊框和樣式、數(shù)據(jù)的字體和對齊方式等。（2）數(shù)據(jù)

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

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

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

“：”，“ ”

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

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

一個完整的函數(shù)包括三部分：函數(shù)名、參數(shù)和結(jié)果

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

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

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

控件（Microsoft

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

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

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

普通視圖是默認(rèn)視圖，

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

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

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

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

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

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

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

單元格地址是由列標(biāo)和行號來標(biāo)識組成的單元格空間（也稱為單元格名稱），可以直接被引用實現(xiàn)數(shù)據(jù)的讀寫。

表示單元格名稱的方法有兩種：一是用列標(biāo)和行號，即列標(biāo)在左，行號在右組合的字符串。例如，第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）引用運(yùn)算符

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

,

逗號“，”,

空格“

”（3）地址標(biāo)識引用的種類

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

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

三維地址引用

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

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

工作表名稱!

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

Excel的文本對象有數(shù)字型文本，比如手機(jī)號碼、銀行帳號、學(xué)號和身份證號等。

要將這些數(shù)字轉(zhuǎn)換成文本，按文本型處理，方法是在輸入此類數(shù)字型文本時，應(yīng)先輸入一個英文單引號“＇”，再輸入其數(shù)字.

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

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

當(dāng)表格數(shù)據(jù)超過一頁的有效高度（或?qū)挾龋r系統(tǒng)會自動分頁，自動分頁符會隨著表格變化而動態(tài)變化，作用僅僅是分頁。②人工分頁符------

人工分頁符是指人為插入把表格行強(qiáng)行轉(zhuǎn)入下一頁顯示的標(biāo)識。人工分頁符的位置不會隨著表格變化而變化。位置是在當(dāng)前單元格的左上角插入水平和垂直分頁符。分頁符

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

當(dāng)工作表中行數(shù)和列數(shù)較多時，一旦向下或向右滾屏?xí)r，則上面的標(biāo)題行或左側(cè)的列標(biāo)題跟著滾動隱藏，使得數(shù)據(jù)無法與標(biāo)題對照查看或錯位。凍結(jié)窗格或拆分窗口把工作表窗口分為四個窗格，便可對照看解決這一問題。

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

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

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

③工作表設(shè)置：行高列寬、縮放比例的設(shè)置。網(wǎng)格線查看（打?。?、標(biāo)題查看（打?。┑脑O(shè)置。（2）對象布局：對象與對象，表格與對象的平面幾何位置關(guān)系稱為對象布局。

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

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

①用戶通過鍵盤輸入的原始數(shù)據(jù)。

②來自Access數(shù)據(jù)表數(shù)據(jù)，系統(tǒng)自動將其轉(zhuǎn)換為Excel表格數(shù)據(jù)。

③來自網(wǎng)站的.htm或.html，可將網(wǎng)頁文件直接轉(zhuǎn)換為Excel文件。

④來自文本文件.txt，將文本文件數(shù)據(jù)導(dǎo)入Excel工作表中。

⑤將.xml文件導(dǎo)入Excel工作表中。

⑥將其他數(shù)據(jù)庫（如Sql

Server）中的數(shù)據(jù)表數(shù)據(jù)導(dǎo)入到Excel工作表中。5.3.3

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

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

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

③網(wǎng)頁文件為html或mhtml。

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

⑤轉(zhuǎn)換文件：Excel文件可直接轉(zhuǎn)換為pdf。5.3.3

電子表格的功能設(shè)計與操作原理Excel文件（工作簿）組成結(jié)構(gòu)設(shè)計工作薄（文件名XLSX）工作表1(表名1)工作表2(表名2)………工作表n（表名n）表(行數(shù),列數(shù))單元格A1(A列,1行)單元格A2(A列,2行)單元格An(A列,n行)列A B C D …行12…nExcel

2016工作窗口32選項卡

功能區(qū)單元格

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

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

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

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

Graph）等。Excel數(shù)據(jù)表內(nèi)容設(shè)計Excel功能界面設(shè)計與主要功能圖5-54

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

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

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

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

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

數(shù)據(jù)清單的第一行必須為文本類型，即必須要有標(biāo)題行

一個數(shù)據(jù)清單最好占用一個工作表。

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

每一列包含相同類型的數(shù)據(jù)。

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

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

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

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

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

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

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

（1）新建工作簿：默認(rèn)空白工作簿，進(jìn)行預(yù)期的頁面設(shè)置。