大学计算机基础（第六版）课件 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、单元格外部的内容包括各种对象，如常规对象、特殊对象、动态对象（域）以及ActiveX控件等，以及自带的基于数据分析的常用工具。（1）表格

各种类型的表格或专业表格。其中需要设置表格样式、单元格边框和样式、数据的字体和对齐方式等。（2）数据

在单元格里可以直接输入数据，为了便于数据管理，Excel将其分为不同的数据类型。Excel中内置有常规、数值、货币、日期/时间、百分比、文本、自定义等12种数据类型，这些数据类型可划分为4种大类：文字（字符）型、数字（数值）型、日期/时间型、公式与函数。2．Excel电子表格的内容文字（字符）型数据及输入说明性与解释性的数据描述称为文字型。文字型包括：汉字、字母、数字、特殊符号等符号显示格式以左对齐为缺省的格式显示注意（1）如果输入的字符串的首字符是“=”号，则应先输入一个单引号“'”（注意必须是半角字符的单引号），再输入等号和其他字符。(2)如果输入的文字是类似于邮政编码、电话号码之类的文本型数字，则应先输入单引号“’”（注意也必须是半角字符的单引号），再输入数字。数值型包括：正号、负号、小数点、数字、百分号％、千分位号（，）、括号（）、$、E等正确表示数值的字符组合显示格式以右对齐为缺省的格式显示数值型数据及输入日期和时间型包括：正确表示日期和时间的字符显示格式以右对齐为缺省的格式显示输入操作日期的年、月、日之间用“-”分隔或用“/”分隔时间的时、分、秒之间用（：）分隔

AM（上午）PM（下午）日期和时间型数据及输入算术运算符(%,^,×

,/,＋,－)优先级从左到右比较运算符（≤,≥,＝,＜,＞,<>）比较运算符可以比较的数据有数字型、文字型、日期型。文本运算符（&）文本运算符（&）用来连接不同的文本产生新的文本单元格引用符（

“：”，“ ”

，“，”）。冒号：定义一个单元格区域。如：A1：A3空格：交集运算。如：A1：C2 C1：D2 等价于C1：C2逗号：并集运算。如：A1：A4，B1：B2在公式中可以使用的运算符

函数是具有独立的计算方法或算法功能，并有确定结果的、可被共享的程序代码片段。

一个完整的函数包括三部分：函数名、参数和结果

函数语法格式为：函数名称（参数1,参数2,…）例如：Sum(b2:f6)函数批注是指对单元格或内容需要做解释，而添加的相应注释。批注

这部分内容主要包括：常规对象、特殊对象和ActiveX

控件等。电子表格中，基于数据分析的常用工具和自带的对象，如常规对象的图表或一个重要的ActiveX

控件（Microsoft

Graph，MS-Graph），均为图表化工具。

①基于数据分析的常用工具：排序、筛选、分类汇总、分组、分列、数据透视表等。

②图表化工具：图表化是指数据的图形化，即将一维、二维或三维数据以不同图形（点、线或面、图案等）的方式绘制出来的图。图表更能形象地表达数据间的关系和特点，并能够做到层次分明、调理清楚、易于理解。单元格外部的内容在Excel中共有4种视图：普通视图、页面布局视图、分页预览视图、自定义视图和全屏显示。（1）普通视图

普通视图是默认视图，

有三个工作表，可方便查看全局数据及结构，是编辑数据和创建对象的主要界面。普通视图下不显示页眉页脚、页边距，其中人工分页符显示为一条水平虚线，在普通视图下可以添加（取消）分组标记“+”和“-”，也可以添加（取消）分类汇总标记“+”和“-”。视图（2）页面布局视图

页面布局视图是以页面大小方式显示工作表，并可编辑页眉和页脚内容。它不显示其它符号和标记，如分页符等。

一般认为页面布局就是一种“所见即所得”的视图，它可以显示出页面大小、布局排版、编辑页眉和页脚、查看调整页边距、处理图表对象以及其它各种对象，在页面布局中看到的外观效果也就是打印结果外观，因此，页面布局适用于概览整个工作表的总体效果并打印。（3）分页预览视图

分页预览视图是以缩小工作表的方式，全览表格在工作表中的布局。同时显示分页符，并标记第几页，以便在工作表中人工拖动分页符的位置设置页面大小，或删除分页符。

在分页预览视图下可以添加（取消）分组标记“+”和“-”，也可以添加（取消）分类汇总标记“+”和“-”。（4）自定义视图

自定义视图可以定位多个自定义视图，根据用户需要，保存不同的打印设置、隐藏行、列及筛选设置，更具个性化。（5）全屏显示

全屏显示隐藏了菜单及选项卡和功能区，使工作表几乎放大到整个显示瓶，充分利用屏幕显示更多的数据。全屏显示要与其他视图配合使用，按ESC键可退出全屏显示。（1）单元格地址标识①单元格地址

单元格地址是由列标和行号来标识组成的单元格空间（也称为单元格名称），可以直接被引用实现数据的读写。

表示单元格名称的方法有两种：一是用列标和行号，即列标在左，行号在右组合的字符串。例如，第2行第3列单元格的名字是C2，C2非常直接地指向了所属单元格。另一种是直接命名。例如，C2单元格命名为“单价1”，C3单元格命名为“单价2”，间接地表示了C2和C3单元格。②单元格区域地址

单元格区域指的是由多个相邻单元格形成的矩形区域，表示区域地址或名称同样有两种方法：用单元格地址和直接命名。将单元格矩形区域左上角与右下角单元格的地址，中间以冒号隔开为区域地址。比如第2行第A(1)列至第5行第D(4)列区域，其区域地址即为A2:D5，表示4行×4列=16个单元格空间。同上也可以直接给单元格区域地址命名。（2）引用运算符

引用运算符在公式中主要用于选取单元格区域，共有3个：冒号“:”

,

逗号“，”,

空格“

”（3）地址标识引用的种类

单元格引用有相对引用、绝对引用、混合引用、三维地址引用等多种方式，在公式中不同方式的引用，其含义不同。相对引用是指当把一个含有单元格或单元格区域地址的公式复制到新的位置时，公式中的单元格或单元格区域地址随着改变，公式的值将会依据更改后的单元格或单元格区域地址的值重新计算。绝对引用是指在公式中的单元格或单元格区域地址不随着公式位置的改变而发生改变。不论公式的单元格处在什么位置，公式中所引用的单元格位置都是其在工作表中的确切位置。绝对单元格引用的形式是在每一个列标及行号前加一个$符号混合引用是指单元格或单元格区域的地址部分是相对引用，部分是绝对引用。如$B2、B$2在公式中，可以引用另一工作簿或同一工作簿的另一张工作表中的单元格，这就用到三维地址引用。三维地址引用的格式是：[工作簿名称]工作表名称!单元格地址。在此需要注意的是方括号“[

]”和感叹号“!”均为英文字符。在同一个工作簿中引用数据，可省略“[工作簿名称]”。

三维地址引用

例如：如果在当前工作表的D2单元格输入公式：=[成绩]Sheet3！A1+A2表示引用“成绩”工作簿Sheet3工作表中的A1单元格的内容和当前工作簿的当前工作表中的A2单元格的内容相加不同工作表中的数据所在单元格地址的表示：工作表名称!

单元格引用地址不同工作簿中的数据所在单元格地址的表示：[工作簿名称]

工作表名称!

单元格引用地址填充柄的功能非常强大，使用填充柄可以实现数字的复制或填充、字符的复制或填充、时间/日期的复制或填充、公式的复制等操作，使得计算得到大大的简化。填充柄

Excel的文本对象有数字型文本，比如手机号码、银行帐号、学号和身份证号等。

要将这些数字转换成文本，按文本型处理，方法是在输入此类数字型文本时，应先输入一个英文单引号“＇”，再输入其数字.

如在B4输入学号“‘201405352046”。数据单元格中将会在左上角显示一个绿色三角“

”，是数字型文本标记。数字型文本标记分页符是指将工作表按页面设置大小分割成上下左右等几个区域的贯穿整个屏幕的水平和垂直虚线，“------”。在分页预览视图下，分页符标记为蓝色实线，可以拖动调整其位置，改变页面大小。系统可自动加载分页符，也可以人为插入分页符。①自动分页符------

当表格数据超过一页的有效高度（或宽度）时系统会自动分页，自动分页符会随着表格变化而动态变化，作用仅仅是分页。②人工分页符------

人工分页符是指人为插入把表格行强行转入下一页显示的标识。人工分页符的位置不会随着表格变化而变化。位置是在当前单元格的左上角插入水平和垂直分页符。分页符

冻结窗格是将工作表窗口分为上下左右四个窗格，其中滚动工作表时上部和左部窗格保持固定可见。而拆分窗口是将工作表窗口拆分为上下左右四个大小可调和独立滚动的窗格。

当工作表中行数和列数较多时，一旦向下或向右滚屏时，则上面的标题行或左侧的列标题跟着滚动隐藏，使得数据无法与标题对照查看或错位。冻结窗格或拆分窗口把工作表窗口分为四个窗格，便可对照看解决这一问题。

冻结窗格标识为水平实线和垂直实线，拆分窗口标识为水平和垂直窗口边线。标识位置是在当前单元格的左上角插入水平和垂直标识线。冻结窗格和拆分窗口标识布局可分成整体的页面布局和局部的对象布局。（1）页面布局：对页面的文字、背景和页面版面格式设置。包括以下几个方面：

①主题：更改整个文档的总体设计，包括颜色、字体和效果。

②页面设置：包括页面方向、页边距、纸张大小方向、页眉页脚、插入/删除人工分页符、页面背景、打印区域和打印标题等设置。

③工作表设置：行高列宽、缩放比例的设置。网格线查看（打印）、标题查看（打印）的设置。（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文件（工作簿）组成结构设计工作薄（文件名XLSX）工作表1(表名1)工作表2(表名2)………工作表n（表名n）表(行数,列数)单元格A1(A列,1行)单元格A2(A列,2行)单元格An(A列,n行)列A B C D …行12…nExcel

2016工作窗口32选项卡

功能区单元格

工作表标签插入函数编辑栏名称框填充柄工作表编辑区Excel2010的工作窗口Excel电子表格的内容设计了3个部分，包括表格、单元格内部的内容和单元格外部对象。表格是指在工作表中制作各种类型的表格。单元格内部的内容包括：各种类型的数据、公式、函数和批注等编辑。单元格外部的内容包括各种对象如下：

①常规对象包括：占位符、文本框、图形、图像、剪贴画、艺术字、图表等。

②特殊对象包括：页眉页脚、批注等。

③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（纵向查找函数）等。函数类型和功能创建数据清单（数据表）要遵循以下原则：

数据清单的第一行必须为文本类型，即必须要有标题行

一个数据清单最好占用一个工作表。

数据清单是一片连续的数据区域，不要出现空行和空列

每一列包含相同类型的数据。

工作表的数据清单与其他数据间至少应留出一个空列和一个空行；不要在单元格开头和末尾输入多余空格，否则会影响排序与搜索。5.3.3

电子表格的功能设计与操作原理规范的数据清单，数据便可被整体利用和分析，为此系统设计有：排序、筛选、分组、分列、分类汇总、数据透视表等常用工具，5.3.3

电子表格的功能设计与操作原理在Excel的图表化工具MS-Graph中，提供了可以创建11个大类的图表，每一个大类类型里又细分了若干个小类。11个大类分别是：

柱形图、折线图、饼图、条形图、面积图、XY（散点图）、股价图、曲面图、圆环图、气泡图和雷达图等。图表产生的位置分为两类：

以对象嵌入到已有的工作表中和形成新的工作表（称图表工作表）

嵌入图表可看作一个图形对象，浮动在工作表中，并作为工作表的一部分进行保存。

图表工作表是整个工作表为一个独立的图表工作表。Excel图表化嵌入图表----柱型39图表工作表—饼型40Excel2016电子表格的编辑和排版的主要步骤和流程如下：

（1）新建工作簿：默认空白工作簿，进行预期的页面设置。