第3章
电子表格处理（Excel）




目的与要求

（1） 掌握电子表格的建立、编辑和保存。

（2） 掌握数据的基本运算。

（3） 掌握数据的格式化。

（4） 掌握列表的操作。

（5） 掌握图表的建立和编辑。


3.1Excel的基本功能


案例31中使用的素材位于“第3章＼素材”文件夹，样张位于“第3章＼样张”文件夹，本案例的结果文件： yz_3_1.xlsx，使用的素材： lt_3_1.xlsx(每章配套素材包括两个文件夹，一个放原始素材，一个放样张文件)。

本案例对电子表格中单元格的数据进行编辑、计算和格式化的操作。编辑操作要注意单元格有属性； 计算中可以应用公式、函数、单元格引用和区域名称； 数据格式化可以选择自动套用格式、自定义格式和条件格式。



扫码观看


案例31
启动Excel 2016，打开lt_3_1.xlsx文件，按要求完成以下各题的操作。

（1） 添加工号，从001开始，前置0 要保留。

（2） 计算所有教职工的津贴(津贴=基本工资×职贴率)和实发工资(实发工资=基本工资+奖金+津贴-Sheet2工作表中的公积金)，均保留两位小数。

（3） 将职称为教授的基本工资区域定义为名称JSGZ，并计算其平均值，计算结果存放在I23单元格中，保留两位小数。

（4） 将批注移至C21单元格，并把批注的内容修改为“院士、博导”，显示批注。

（5） 取消D列数据的隐藏，隐藏第12行的数据。

（6） 对基本工资为3700元以下的工号填充“橙色，个性色6，淡色60%”，对实发工资为5500元以上的数据用“深红色”、粗体字表示。

（7） 将标题设置为黑体、粗体、20磅、红色、在A1:J1跨列居中，对A1、C1、E1、H1和J1单元格填充“水绿色，个性色5，淡色60%”颜色，“平均值”和“职贴率”两行填充“白色，背景1，深色15%”颜色，并添加“6.25%灰色”图案。为数据表外框添加蓝色最粗实线，内框添加蓝色双线。

（8） 对区域名称为“工资”的数据设置货币符号(人民币)、粗斜体、保留两位小数，并设置最合适的列宽。

（9） 以原文件名保存操作结果，如图31所示。




图31lt_3_1样张


操作步骤： 

第（1）题： 选择“文件”|“打开”命令，打开lt_3_1.xlsx文件。选中A3单元格，输入'001，确认后拖动填充柄至A21单元格，或双击填充柄。

第（2）题： 选中I3单元格，输入公式： =G3×$B$23，拖动填充柄至I21单元格。选中J3单元格，输入公式： =G3+H3+I3-Sheet2!B2，拖动填充柄至J21单元格。

第（3）题： 分别选中教授基本工资的单元格，在编辑栏的名称框中输入JSGZ。选中I23单元格，输入公式： =Average(JSGZ)。

第（4）题： 选中C11单元格，按Ctrl+C组合键，选中C21单元格，选择“开始”|“粘贴”|“选择性粘贴”命令，在对话框中选择“批注”单选项。右击C21单元格，在快捷菜单中选择“显示/隐藏批注”命令。将批注内容修改为“院士、博导”，并移到适当位置。右击C11单元格，在快捷菜单中选择“删除批注”命令。




图32“新建格式规则”对话框

第（5）题： 选中C、E列，选择“开始”|“格式”|“可见性”|“隐藏和取消隐藏”|“取消隐藏列”命令。选中第12行，选择“开始”|“格式”|“可见性”|“隐藏和取消隐藏”|“隐藏行”命令。

第（6）题： 选中A3:A21区域，选择“开始”|“条件格式”|“新建规则”命令，打开“新建格式规则”对话框，在“选择规则类型”中选择“使用公式确定要设置格式的单元格”选项，输入公式“=G3<3700”，单击“格式”按钮，设置单元格的填充色为“橙色，个性色6，淡色60%”，如图32所示，单击“确定”按钮。

选中J3:J21区域，选择“开始”|“条件格式”|“新建规则”命令，打开“新建格式规则”对话框，在“选择规则类型”中选择“只为包含以下内容的单元格设置格式”选项，满足条件为>5500，单击“格式”按钮，单元格的字体设置为粗体字、“深红色”，单击“确定”按钮。

第（7）题： 选中A1:J1区域，单击“开始”菜单，单击“对齐方式”组右下角的按钮，打开“设置单元格格式”对话框，在“字体”选项卡中设置文字的格式为“黑体、加粗、20磅、红色字”； 在“对齐”选项卡中，水平对齐选择“跨列居中”选项，单击“确定”按钮。按住Ctrl键的同时单击，分别选中A1、C1、E1、H1和J1单元格，单击“开始”|“填充颜色”按钮，选择“水绿色，个性色5，淡色60%”。选中A22:J23区域，单击“开始”菜单，单击“字体”组右下角的按钮，打开“设置单元格格式”对话框，在“填充”选项卡中背景色选择“白色，背景1，深色15%”，图案样式选择“6.25%灰色”。选中A1:J23区域，单击“开始”|“边框”按钮，选择“其他边框”，打开“设置单元格格式”对话框，在“边框”选项卡中设置内、外框线的样式和颜色。

第（8）题： 在编辑栏的名称框中选择“工资”，选择“开始”|“单元格样式”|“货币”，添加货币符号。单击“开始”|“加粗”按钮B和“倾斜”按钮I。选择“开始”|“格式”|“单元格大小”|“自动调整列宽”。

第（9）题： 选择“文件”|“保存”命令，将操作结果以原文件名保存。



3.1.1工作簿、工作表的管理
1. 创建和编辑工作簿

1) 创建新文件

(1)  启动Excel 2016，选择打开空白工作簿，默认文件名为“工作簿1”。选择“文件”|“新建”命令也能打开空白工作簿。

(2)  选择单元格或区域，输入数据。

(3)  选择“文件”|“保存”命令或选择“文件”|“另存为”命令，保存文件。

2) 编辑电子表格

(1)  选择“文件”|“打开”命令，打开已建好的电子表格文件。

(2)  按要求计算、编辑电子表格中的数据或对电子表格格式化。

(3)  选择“文件”|“保存”命令或选择“文件”|“另存为”命令，保存操作结果。

2. 工作表的操作

一个Excel文件(工作簿)可以由若干个工作表组成。

1) 工作表的选取

选取一张工作表： 单击表标签。选取连续多张工作表： 按住Shift键单击表标签。选取不连续多张工作表： 按住Ctrl键单击表标签。若工作簿含有多张工作表，可使用工作表选取按钮前后翻页。

2) 工作表的插入

单击“开始”菜单，在“单元格”组中，选择“插入”|“插入工作表”命令或者按Shift+F11组合键，在选中的工作表前增加了一张新工作表。或单击工作表标签栏右侧的“新工作表”按钮，插入一张新工作表。 

3) 工作表的移动

选中要移动的工作表标签，拖动至需要的位置。

4) 工作表的复制

选中要复制的工作表标签，按住Ctrl键，拖动至需要的位置。复制的工作表标签名在原表标签名后加(2)，表示同名的第二张表。例如复制Sheet1工作表,它的副本表标签名为Sheet1(2)。

若要复制或移动到另一个工作簿中去，单击“开始”菜单，在“单元格”组中，选择“格式”|“组织工作表”|“移动或复制工作表”命令，打开“移动或复制工作表”对话框，选中“建立副本”复选框表示复制，不选表示移动。

5) 工作表的重命名

双击工作表标签，输入新表标签名。或右击工作表标签，在快捷菜单中选择“重命名”命令。

6) 工作表的删除

选中要删除的工作表标签，单击“开始”菜单，在“单元格”组中，选择“删除”|“删除工作表”命令。或右击工作表标签，在快捷菜单中选择“删除”命令。注意，被删除的工作表不能恢复。

3.1.2单元格数据的输入和编辑


1) 单元格、区域的选取

(1)  单元格的选取： 单击所需选取的单元格。单元格的快速定位： 按功能键F5。

(2)  区域的选取： 单击左上角的单元格拖动至右下角的单元格，或者单击区域四个角的任何一个单元格，按住Shift键，再单击区域对角的单元格。

(3)  不相邻单元格或区域的选取： 单击第一个单元格或区域，按住Ctrl键，分别选取所需选取的单元格或区域。

(4)  行和列的选取： 单击行号选取一行，单击列标选取一列。

(5)  取消选取区域： 单击选区外的任意一个单元格即可取消区域的选取。

2) 数据的输入

(1)  输入文本： 选取单元格，输入字符，按Enter键。

字符型的数字的输入方法： 在数字前面加上半角的单引号，单元格左上角有个绿色小三角。例如输入012，正确的方法是： 选中单元格，输入'012，按Enter键确认。

(2)  输入数值： 数值只可以是下列字符： 0~9、+、-、( )、/、$、%、E、e，其他数字与非数字的组合将被视作文本，在默认情况下，所有的数值在单元格中均右对齐。

若输入分数，在分数前要输入0和空格，例如分数三分之一，正确的输入方法是： “0 1/3”。

若输入的数值长度超出单元格的宽度，采用科学记数法。例如输入12345678910，确认后，在单元格中显示的数据为1.23E+10。

(3)  输入日期和时间。日期格式： 年月日或年/月/日； 输入系统当前日期按快捷键Ctrl+； (分号)。时间格式： 时： 分； 输入系统当前时间按快捷键Ctrl+Shift+； （分号）。

(4)  自动填充输入系列数据： 单元格或区域右下角有一个小方块称为填充柄，它可以自动填充数据，例如星期、月份、季度、甲乙丙……单击填充柄右下角的快捷菜单按钮，打开快捷菜单，选择填充的方式。

3) 批注

(1)  插入批注。

选中单元格，打开“审阅”选项卡，在“批注”组中选择“新建批注”。或右击在打开的快捷菜单中选择“插入批注”命令。

(2)  编辑批注。

修改批注中的内容： 打开“审阅”选项卡，在“批注”组中选择“显示/隐藏批注”，显示批注，修改内容。

设置批注格式： 右击批注边框，在打开的快捷菜单中选择“设置批注格式”命令，打开设置批注格式对话框，在对话框中完成对文字、框线和填充颜色的设置。

(3)  删除批注。

选中单元格，打开“审阅”选项卡，在“批注”组中选择“删除”。或右击在弹出的快捷菜单中选择“删除批注”命令。

4) 数据的编辑

单元格有属性，其属性包括内容、批注和格式。

(1)  修改单元格内容。

双击单元格，在单元格中直接输入新内容。或单击单元格，输入内容，新内容取代原有的内容。

(2)  插入单元格、行或列。

插入单元格： 选中单元格，单击“开始”菜单，在“单元格”组中选择“插入”|“插入单元格”。 

插入行： 选中行，单击“开始”菜单，在“单元格”组中选择“插入”|“插入工作表行”，插入的行在该行的上面。

插入列： 选中列，单击“开始”菜单，在“单元格”组中选择“插入”|“插入工作表列”，插入的列在该列的左边。

(3)  移动单元格或区域的内容。

同一工作表： 选中要移动的单元格或区域，拖动至目标单元格。

不同工作表： 选中要移动的单元格或区域，单击“开始”菜单，在“剪贴板”组中选择“剪切”或按快捷键Ctrl+X； 再选择目标单元格，选择“开始”|“粘贴”或按快捷键Ctrl+V。此操作方法也可用于同一工作表中单元格或区域的移动。

(4)  复制单元格或区域的内容。

同一工作表： 选中要复制的单元格或区域，按住Ctrl键，拖动至目标单元格。

不同工作表： 选中要复制的单元格或区域，单击“开始”|“复制”或按快捷键Ctrl+C； 再选择目标单元格，单击“开始”|“粘贴”或按快捷键Ctrl+V。此操作方法也可用于同一工作表中单元格或区域的复制。

(5)  复制单元格或区域的属性。

选中要复制的单元格或区域，单击“开始”|“复制”或按快捷键Ctrl+C； 再选择目标单元格，选择“开始”|“粘贴”|“选择性粘贴”命令。

5) 清除单元格

清除单元格的内容： 选中单元格，按Delete键。

清除单元格的属性： 选中单元格，单击“开始”菜单，在“编辑”组中选择“清除”选项中要清除的单元格属性。

6) 删除单元格

若要连同单元格一起删除，选中要删除的单元格，单击“开始”菜单，在“单元格”组中选择“删除”|“删除单元格”命令。

7) 恢复

若操作有误，单击“快速访问工具栏”上的“撤销”按钮，撤销操作。


3.1.3公式与函数、单元格的引用

公式是电子表格的核心。

1. 公式

1) 创建公式

格式： 以等号开始，由常数、单元格引用、函数和运算符等组成。

(1)  运算符及优先级。

公式中运算符的优先级与数学中运算符的优先级相同。

(2)  输入公式。

单击要输入公式的单元格，键入“=”； 输入公式内容； 按Enter键锁定。计算的结果显示在单元格中，公式显示在编辑栏中，因此，公式的编辑可直接在编辑栏中进行。

若希望在单元格中显示公式而不是计算结果，打开“公式”选项卡，在“公式审核”组中选择“显示公式”。

(3)  单元格引用。

相对引用： 随单元格位置变化而自动变化的引用，用相对地址名A1表示。

绝对引用： 随单元格位置变化不变的引用，用绝对地址名$A$1表示。

混合引用： 随单元格位置变化行变列不变的引用，用混合地址名$A1表示； 随单元格位置变化列变行不变的引用，用混合地址名A$1表示。

不同类型的引用可以通过键盘直接输入或使用功能键F4切换，其次序为： 相对引用(A1)→绝对引用($A$1)→混合引用(A$1)→混合引用($A1)循环切换。

注意：  在公式中若变量是绝对引用，将其移动或复制到其他单元格，其值不变； 若变量是相对引用，移动到其他单元格其值不变； 复制到其他单元格，其值就会随单元格位置的变化而变化。据此，在运算中，同一种运算只需输一次公式，其余的通过填充柄复制。

(4)  工作表引用。

参与运算的其他工作表上的数据用工作表引用来表示，工作表引用又称为三维引用。工作表引用格式： <工作表名>！<单元格引用>。例如Sheet1表中C4的数据与Sheet2表中C2的数据相加，结果放在Sheet1表中的B2单元格中。操作如下： 

选中Sheet1表中B2单元格，输入“= Sheet1！C4+ Sheet2！C2”，按Enter键锁定。

2) 用名称和标志简化公式

在运算中工作表的区域通常用单元格引用来表示，当工作表的数据较多时就显得不方便，为此，系统提供用区域名称代替单元格引用来参与运算，简化公式。

(1)  创建区域名称的方法。

编辑栏名称框中命名： 选中区域，在编辑栏“名称”框中输入名字。

自定义区域名称或以工作表首行、首列作为区域名称： 打开“公式”选项卡，在“定义的名称”组中选择“定义名称”，打开“新建名称”对话框，如图33所示。在文本框中输入区域名称，单击“确定”按钮。


图33“新建名称”对话框

此命令还可用来编辑区域名称，即修改区域的名称和引用位置。

(2) 名称的应用。

参与公式运算： 将名称直接输入公式“=函数(名称)”或选择“公式”，在“定义的名称”组中选择“用于公式”|“粘贴名称”命令将名称粘贴到公式中。

对名称区域的数据格式化： 在编辑栏“名称”框中选中需格式化的区域名称，单击“开始”菜单，在“单元格”组中，选择“格式”|“设置单元格格式”。

3) 字符运算

格式： <字符串1>&<字符串2>&<字符串3>&……

说明： 字符串可以用单元格引用表示，若要添加空格或插入其他字符，空格和字符必须用半角的双引号括起来。

2. 函数

Excel函数类别分为财务函数、日期与时间函数、数学与三角函数、统计函数、查找与引用函数、数据库函数、文本函数、逻辑函数、信息函数、工程函数、多维数据集函数、兼容性函数和Web函数等。

1) 函数格式及使用说明

函数格式： 函数名(参数1，参数2，……)

说明： 

(1)  函数的参数可以是数值、文本、逻辑值和函数的返回值，也可以是单元格引用或区域名称，参数一定要放在圆括号里。

(2)  参数是区域，可用冒号连接区域首尾单元格引用，例如B3:D5。若对此区域已定义了名称，也可直接使用名称作为参数，参与运算，例如： =MIN(工龄)。

(3)  当参数多于一个时，必须用逗号把它们隔开，参数最多可达30个。例如： =SUM(B3:D5，E2:F4，G6)。

(4)  函数可以嵌套，不能交叉，函数括号要对应。

2) 输入函数

可以采取直接键盘输入、选择“公式”|“插入函数”命令、单击编辑栏上的“插入函数”按钮三种操作，后两种操作都会打开“插入函数”对话框，在“选择类别”框中选择函数类别，在“选择函数”框中选择函数。列表框下面给出了函数的格式和功能。

3) 常用函数举例

常用函数有SUM、AVERAGE、COUNTIF、MAX、MIN、RANK、IF、AND、OR、NOT等。下面给出几个例子。

（1） 求C3:C5和E3:E5区域的平均值，其公式为： = AVERAGE(C3:C5,E3:E5)。

（2） 求名称为“工资”区域的最大值，其公式为： = MAX(工资)。求A1:A5区域的最小值，其公式为： =MIN(A1:A5)。

（3） 显示当前系统日期和时间，其公式为： =NOW()。

（4） 评价学生成绩等级，总分大于或等于90分的为优秀，大于或等于60分的为合格，其余为不合格。其公式为： = IF (总分>=90,“优秀”，IF (总分>=60,“合格”,“不合格” ))。

（5） 数学、语文、外语三门课考试成绩，其中一门为90分以上(包括90分)的评价为单项优秀，其余的为一般。其公式为： = IF(OR(数学>=90，语文>=90，外语>=90 )，"单项优秀"，"一般")。

（6） 数学、语文、外语三门课考试成绩均在85分及以上的评价为优良，其余的为一般。其公式为： = IF(AND (数学>=85，语文>=85，外语>=85)，“优良”，“一般”)。

（7） 统计考试成绩90分及以上的学生人数： =COUNTIF( C2:C27,“>=90”)。

（8） 计算补贴，职称为“高级”的基本工资×25%，职称为“中级”的基本工资×15%，其余的基本工资×10%： =IF (职称=“高级”，基本工资*25%，IF (职称="中级"，基本工资*15%，基本工资*10%))。

（9） 根据C2栏学生的总分，对C2到C27单元格中全体学生的总分进行排名，顺序为升序： =RANK(C2,$C$2:$C$27,1)(RANK函数的第三个参数为1，表示升序； 为0，表示降序)。

3. 自动计算

1) 行和列数据的自动求和

选择邻近行或列的单元格，双击“公式”|“自动求和”(“函数库”组中)。

2) 数据的自动计算

选中需计算的数据区域，在状态栏中系统已给出计算结果，系统默认为平均值、计数和求和，右击计算结果显示区，在快捷菜单中选择其他函数。

3.1.4工作表、单元格的格式化

工作表格式化是指对数据的表示方法、字体、对齐方式、边框、颜色、行高、列宽等进行设置。Excel 2016在“开始”菜单的“样式”组和“单元格”组中，提供了对单元格的格式化操作。

1. 快速选择表格格式

(1) 选中需设置格式的区域。

(2) 单击“开始”菜单，在“样式”组，选择“套用表格格式”，弹出如图34所示的下拉列表，在列表中选择合适的格式。

2. 自动套用单元格样式

(1) 选中需设置格式的区域。

(2) 单击“开始”菜单，在“样式”组，选择“单元格样式”，弹出如图35所示的下拉列表，在列表中选择合适的样式。




图34套用表格格式                       




图35单元格样式


3. 自定义格式

(1) 选中需设置格式的区域。

(2) 单击“开始”菜单，在“单元格”组，选择“格式”|“设置单元格格式”，打开“设置单元格格式”对话框。单击“开始”菜单中“字体”“对齐方式”和“数字”组右下角的按钮也能打开“设置单元格格式”对话框。

1) 数字格式化

(1) 在“设置单元格格式”对话框的“数字”选项卡中，在左边列表框中选择数字格式的类型，在右边列表框中选择数字格式的样式。

(2) 在“开始”菜单中选择“数字”组中的“货币样式”“百分比”“千分位”等选项。

2) 字符格式化

(1) 在“设置单元格格式”对话框的“字体”选项卡中设置数据的字体、字形、字号、颜色等。

(2) 在“开始”菜单中选择“字体”组的“字体”“字形”“字号”“加粗”“倾斜”“颜色”等选项。

3) 对齐方式

(1) 在“设置单元格格式”对话框的“对齐”选项卡中设置数据对齐方式。

(2) 在“开始”菜单中单击“对齐方式”组中的“左对齐”“居中”“右对齐”和“合并后居中”等选项。标题的对齐方式有“合并居中”和“跨列居中”，请注意两者的区别。

4) 边框

(1) 在“设置单元格格式”对话框的“边框”选项卡中设置框线的颜色、线型，添加内外框线。

(2) 在“开始”菜单中单击“字体”组“边框”按钮。

5) 色彩与图案

(1) 打开“设置单元格格式”对话框中的“填充”选项卡，填充单元格的颜色和图案。

(2) 在“开始”菜单中单击“字体”组中的“填充颜色”按钮，此操作只能填充颜色，不能设置图案。

4. 条件格式

为了突出显示所要检查的动态数据或突出显示公式的结果，可以使用条件格式标记单元格。

(1) 选中要设置条件格式的区域。

(2) 单击“开始”菜单，在“样式”组选择“条件格式”|“新建规则”命令，打开“新建格式规则”对话框。

(3) 在“选择规则类型”框中选择条件格式类型，在“编辑规则说明”框中设置条件，条件可以是单元格数值或公式。单击“格式”按钮，打开“设置单元格格式”对话框，在该对话框中设置数据的字体、颜色、边框、背景色或图案。

若要添加条件格式可选择“开始”|“条件格式”|“管理规则”命令，打开“条件格式规则管理器”对话框，如图36所示。在该对话框中可以新建、编辑和删除规则。



图36“条件格式规则管理器”对话框


条件格式一旦设定，在被删除前对单元格一直起作用。更改或删除条件格式在“条件格式规则管理器”对话框中完成。

5. 调整单元格的列宽、行高

Excel 2016默认的列宽、行高由通用格式样式中设置的字形大小来决定。若单元格中输入字符太长就要被截，数字太长会出现“######”。为了能够完全显示单元格中的数据就需要调整单元格的列宽和行高。

1) 列宽、行高

(1)  将光标移到两个列标之间，当光标指针变成双箭头时，拖动鼠标调整单元格的列宽。

(2)  单击“开始”菜单，在“单元格”组中选择“格式”|“单元格大小”中的“列宽”“自动调整列宽”和“默认列宽”选项。

调整单元格行高的操作方法同列宽。

2) 行或列的隐藏和取消隐藏

选中需隐藏的列，单击“开始”菜单，在“单元格”组中选择“格式”|“可见性”|“隐藏和取消隐藏”|“隐藏列”，可将工作表中暂不使用的数据隐藏起来，需恢复时选择“取消隐藏列”。

行的隐藏和取消隐藏的操作同列。

3) 自动调整行高

单击“开始”菜单，在“单元格”组中选择“格式”|“设置单元格格式”，在弹出的对话框的“对齐”选项卡中选择“两端对齐”“自动换行”。

6. 格式的复制和删除

1) 复制格式

(1)  单击“开始”菜单，在“剪贴板”组中选择“格式刷”。

(2)  选择“开始”|“复制”和“开始”|“粘贴”|“选择性粘贴”，在弹出的对话框中选择需复制的格式选项。

2) 删除格式

单击“开始”菜单，在“编辑”组中选择“清除”|“清除格式”。


3.1.5页面设置和打印预览
1. 页面设置

1) 工作表页面设置

在工作表中选择需打印输出的区域，打开“页面布局”选项卡，单击“页面设置”组右下角的按钮，打开“页面设置”对话框。

“页面”选项卡用来设置打印方向、纸张大小、打印质量等参数。

“页边距”选项卡用来调整页边距，“垂直居中”和“水平居中”复选框用来确定工作表在页面居中的位置。

“页眉和页脚”选项卡用来设置页眉和页脚。选择已给定的页眉类型： 单击“页眉”下拉列表框； 自定义： 单击“自定义页眉”按钮。对页眉内容格式化： 单击“自定义页眉”对话框中的A按钮。页脚的操作同页眉。

“工作表”选项卡： 在“打印区域”文本框中输入要打印的单元格区域。若希望在每一页中都能打印对应的行或列标题，单击“打印标题”区域中的“顶端标题行”或“左端标题列”，选择或输入工作表中作为标题的行号、列标。选择“网格线”选项，在工作表中打印出水平和垂直的单元格网线。若要打印行号、列标，单击“行号列标”选项。若要打印批注，选择“工作表末尾”则在工作表末尾打印批注； 选择“如同工作表中的显示”则在工作表中出现批注的地方打印批注。

2) 图表页面设置

选中需打印的图表，打开“页面布局”选项卡，单击“页面设置”组右下角的按钮，打开“页面设置”对话框，前面三个选项卡的设置基本相同，第四个选项卡为“图表”选项卡。在此选项卡中设置图表的打印质量。

2. 打印

1)  打印预览

在打印前通过打印预览可提高打印的效率及质量。在“页面设置”对话框中单击“打印预览”按钮，或单击“快速访问工具栏”中的“打印预览和打印”按钮。

2)  打印

选择“文件”|“打印”命令，或在“页面设置”对话框中单击“打印”按钮。


3.1.6综合练习

1. 打开“新华电器厂季度产值表.xlsx”文件，按下列要求对工作表进行编辑，编辑结果以原文件名保存在磁盘上，样张参见图37。



图37练习题1样张


(1)  将二车间2月份的数据改为2198。

(2)  计算合计值、平均值(保留两位小数)和任务完成情况(合计值大于等于15000的为超额完成； 介于13000（含）与15000之间的为完成； 小于13000的为未完成)，水平居中，垂直居中。

(3)  将C3:F5区域定义为名称“第一季度”，并计算其最大值，计算结果存放在G10单元格中。

(4)  将标题按样张分为两行，主标题设置为黑体、18磅、加粗、白色，对B1:G1做合并居中； “万元”右对齐。单元格列宽设置为10。按样张标题行填充“紫色，个性色4，淡色40%”； B4:B11区域填充“白色，背景1，深色25%”，文字为12磅，粗体字，B11单元格内容设置为自动换行。

(5)  数据表外框线为“红色，个性色2，淡色60%”，最粗实线，内框线为“红色，个性色2，淡色60%”，最细实线，其他格式如样张。

(6)  对批注填充： 渐变预设颜色为“碧海青天”，框线为4.5磅双实线，紫罗兰色，显示批注。

(7)  在C4:F9区域，对小于1500的数据用橙色、加粗并浅蓝色填充表示。

(8)  复制B3:G11数据表数据，并在B14单元格进行行列转换，标题合并居中，隐藏批注。数据表外框线为蓝色粗实线，内框线为蓝色双线。

操作提示： 

第(1)题： 选中D4单元格，输入2198，按Enter键确认。

第(2)题： 选中C9单元格，双击“开始”|“求和”按钮，拖动填充柄至F9单元格。选中G3单元格，输入公式： =Average(C3:F3)，拖动填充柄至G8单元格。单击“开始”|“增加小数位数”按钮，保留两位小数。选中C10单元格，输入公式： =IF(C9>=15000，“超额完成”，IF(C9>=13000，“完成”，“未完成”))，拖动填充柄至F10单元格，选中数据水平居中，垂直居中。

第(3)题： 选中C3:F5区域，在编辑栏的名称框中输入“第一季度”； 选中G10单元格，输入公式： =Max(第一季度)。

第(4)题： 选中第2行，选择“开始”|“插入”|“插入工作表行”命令，插入一行。选中B1单元格，在编辑栏中剪切“万元”两字，将其复制到G2单元格，右对齐。选中B1:G1区域，选择“开始”|“格式”|“设置单元格格式”，在弹出的对话框的“字体”选项卡中选择“黑体、18磅、加粗”； 在“对齐”选项卡中水平对齐选择“跨列居中”。选中B1:G1区域，选择“开始”|“格式”|“单元格大小”|“列宽”，将列宽设置为10。选中B1:G3区域，单击“开始”|“字体颜色”按钮，将文字设置为白色。单击“开始”|“填充颜色”按钮，将区域填充为“紫色，个性色4，淡色40%”。选中B4:B11区域，将其填充为“白色，背景1，深色25%”，文字大小设置为12磅、粗体字。在B11单元格内自动换行。

第(5)题： 选中C4:F9区域，单击“开始”|“居中”按钮。选中B1:G11区域，选择“开始”|“格式”|“设置单元格格式”，在弹出的对话框的“边框”选项卡中设置外框线为红色，个性色2，淡色60%，最粗实线； 内框线为红色，个性色2，淡色60%，最细实线。选中B11单元格，单击“开始”|“自动换行”按钮。

第(6)题： 右击C3单元格，在打开的快捷菜单中选择“显示/隐藏批注”命令，右击文本框边框，在打开的快捷菜单中选择“设置批注格式”命令，在弹出的对话框的“颜色与线条”选项卡中填充颜色选择“填充效果”|“预设”|“碧海青天”选项； 线条选择4.5磅双实线，颜色为紫罗兰色。

第(7)题： 选中C4:F9区域，选择“开始”|“条件格式”|“新建规则”，在弹出的对话框中对小于1500的数据字体设置： 橙色、加粗； 填充颜色： 浅蓝色。

第(8)题： 选中B3:G11区域，单击“开始”|“复制”命令，选中A14单元格，选择“开始”|“粘贴”|“选择性粘贴”命令，在弹出的对话框中选中“转置”多选项。将标题复制粘贴到B13单元格，选中B13:J13区域，单击“开始”|“合并后居中”按钮。右击B15单元格，在打开的快捷菜单中选择“隐藏批注”命令。选中B13:J19区域，选择“开始”|“格式”|“设置单元格格式”，在弹出的对话框的“边框”选项卡中设置数据表外框线为蓝色粗实线，内框线为蓝色双线。

选择“文件”|“另存为”命令，在对话框中输入“新华电器厂季度产值表.xlsx”文件名，将编辑结果保存在C盘上。


2. 打开“计算机应用考试成绩表.xlsx”文件，按下列要求对工作表进行编辑，编辑结果以原文件名保存在磁盘上。样张参见图38和图39。

(1) 在sheet1中添加学号从01001到最后，前置0要保留。取消C列的隐藏。

(2) 在标题下插入一行，在G2单元格输入当前的日期，调整合适列宽，右对齐。

(3) 计算总分(期中成绩占30%，期末成绩占70%，四舍五入取整数)、班级平均分(不包括隐藏项)及评价(总分大于等于90分的为“优秀”，小于60分的为“不合格”，其余的为“合格”)。

(4) 计算女同学的平均分(即对名称为女的区域求平均值)。计算结果存放在G17单元格。

(5) 对成绩大于等于90分的数据用蓝色、加粗字体表示。 

(6) 对“杨丹妍”单元格插入批注“班长、课代表”。隐藏“百分比”行。

(7) 标题设置为幼圆、粗体、20磅、深蓝色、在A1:G1跨列居中，其余数据居中对齐。数据表外框线为“水绿色，个性色5，深色50%”粗实线，内框线为“水绿色，个性色5，深色50%”细实线。A1、C1、E1和G1单元格填充橙色底纹，“班级平均分”行填充“白色，背景色1，深色25%”底纹。A17单元格内容设置自动换行。其他格式如图38所示的练习题2样张1。



图38练习题2样张1


(8) 计算Sheet2工作表中的数据，要求输入一次公式，拖动两次完成所有数据的计算。并对数据添加人民币符号，保留两位小数，设置最合适的列宽，外框为红色双线。其他格式如图39所示的练习题2样张2。




图39练习题2样张2


操作提示： 

第(1)题： 选中A3单元格，输入'01001，拖动填充柄至A15单元格。选中B、D列，选择“开始”|“格式”|“可见性”|“隐藏和取消隐藏”|“取消隐藏列”命令。

第(2)题： 选中第2行，选择“开始”|“插入”|“插入工作表行”； 选中G2单元格，按Ctrl+； 输入当前日期。

第(3)题： 选中F4单元格，输入公式： =D4*$D$18+E4*$E$18，拖动填充柄至F16单元格。选中有小数的单元格，单击“开始”|“减少小数位数”按钮，四舍五入取整。选中D17单元格，输入公式： =Average(D4:D8,D10:D16)，拖动填充柄至F17单元格。选中G4单元格，输入公式： =IF(F4>=90，“优秀”，IF(F4<60，“不合格”，“合格”))，拖动填充柄至G16单元格。

第(4)题： 选中G17单元格，输入公式： =Average(女)。

第(5)题： 选中D4:F16区域，选择“开始”|“条件格式”|“新建规则”，在弹出的对话框的“选择规则类型”中选择“只为包含以下内容的单元格设置格式”选项，将大于等于90分的数据设置为蓝色、加粗字体。

第(6)题： 选中B10单元格，选择“审阅”|“新建批注”，输入文字“班长、团支部书记”。右击B10单元格，在打开的快捷菜单中选择“隐藏批注”命令。选中第18行，选择“开始”|“格式”|“可见性”|“隐藏和取消隐藏”|“隐藏行”命令。

第(7)题： 根据题目要求选中单元格或区域，选择“开始”|“格式”|“设置单元格格式”，完成对标题和表格格式的操作。

第(8)题： 单击Sheet2工作表，选中B4单元格，输入公式： =$A4*B$3，分别拖动填充柄至B14和F14单元格。选中B4:F14区域，选择“开始”|“单元格样式”|“货币”，对数据添加人民币符号、保留两位小数的样式。外框设置红色双线。其他格式参照图39所示的练习题2样张2所示。

选择“文件”|“另存为”命令，在对话框中输入“计算机应用考试成绩表.xlsx”文件名，将编辑结果保存在C盘上。


3.2Excel的数据管理


案例32对电子表格中的数据进行分析管理并对数字图表化，其中使用的素材lt_3_2.xlsx位于“第3章＼素材”文件夹，样张yz_3_2.xlsx位于“第3章＼样张”文件夹。

对电子表格的数据管理主要包括排序、筛选、分类汇总和创建数据透视表。对数据图表化操作的关键是正确选取转化为图表的数据和图表类型。

案例32案例要求如下： 

（1） 取消Sheet1工作表中F列的隐藏，复制Sheet1工作表，对复制的工作表重命名为“分类汇总”。

（2） 对Sheet1工作表筛选出所有高级职称的记录，保留“平均值”和“职贴率”行，以职称为主要关键字按笔画升序排列，基本工资为次关键字按降序排列。

（3） 将标题修改为“高级职称教师薪资统计表”，添加样式为“填充红色，着色2，轮廓着色2”的艺术字，文字格式为黑体、28磅，居中排列，设置行高为42。

（4） 按样张创建如图310左图所示的数据透视表，放在A28单元格。



图310数据透视表和图表样张


（5） 按样张创建如图310右图所示的图表，放在F28:L41，使用图表布局5。 输入标题“部分副教授奖金、津贴统计图表”，将标题设置为仿宋、14磅、加粗、红色字体，添加蓝色、3磅、由粗到细的边框线。图表区为蓝色圆角4.5磅边框线，填充纹理为“新闻纸”。

（6） 按样张创建如图311所示的分类汇总表，对“分类汇总”工作表按性别建立统计人数的分类汇总表。将标题修改为“教职工分类汇总表”，并设置格式为楷体、蓝色、20磅、粗体字。



图311分类汇总样张


（7） 对Sheet3工作表中的图表按样张图312编辑，选择图表样式5。图表标题为“第一季度一车间产值图表”，将标题格式设置为宋体、16磅、加粗、深蓝色字体。将二月的扇区边框设置为红色、4磅实线，无填充格式，其他样式参照样张。图表区为橙色、4磅圆角边框线，填充纹理为“信纸”。



图312图表样张

（8） 对Sheet1设置页眉“高级职称教师薪资统计表”，格式为楷体、14磅、粗斜体，居右排列。设置页脚为当前日期和时间，居中排列。

操作步骤： 

第（1）题： 选择“文件”|“打开”命令，打开Excel_案例2.xls。取消Sheet1工作表中F列的隐藏。按Ctrl键拖动Sheet1工作表，双击Sheet1(2)工作表，输入“分类汇总”。

第（2）题： 单击Sheet1工作表，选中列表中任一单元格，选择“开始”|“排序和筛选”|“筛选”命令，在字段名的右侧出现下拉列表按钮。单击“职称”右侧的按钮，在下拉列表中选择“文本筛选”|“自定义筛选”选项，打开“自定义自动筛选方式”对话框，按图313所示设置筛选条件，单击“确定”按钮。




图313“自定义自动筛选方式”对话框


选中列表中任一单元格，选择“开始”|“排序和筛选”|“自定义排序”命令，打开如图314所示的“排序”对话框，职称选择升序； 单击“选项”按钮，在“排序选项”对话框中选中“笔画排序”单选项。单击“添加条件”按钮，基本工资选择降序。




图314“排序”对话框


第（3）题： 选中A1单元格，删除标题，选择“插入”|“艺术字”命令，在弹出的选项中选择第1行第3列的选项“红色，着色2，轮廓着色2”，输入文字“高级职称教师薪资统计表”，单击“开始”菜单，利用“字体”组中的按钮将文字设置为“黑体、28磅”。选中第1行，选择“开始”|“格式”|“单元格大小”|“行高”，在对话框中将行高设为42。将标题移至上方，居中排列。

第（4）题： 选中A2:J21单元格，选择“插入”|“数据透视表”命令，打开“创建数据透视表”对话框，选中“现有工作表”单选项，单击A27单元格，单击“确定”按钮。在“数据透视表字段列表”中将“职称”和“性别”字段拖动到行标签区； 将“基本工资”和“奖金”字段拖动到数值区，单击“奖金”右侧的按钮，打开“值字段设置”对话框，“计算类型”选择“平均值”选项。透视表数据保留一位小数。

第（5）题： 选中创建图表的数据，选择“插入”|“折线图”|“带数据标记的折线图”，创建折线图表。单击“图表工具”|“图表布局”|“快速布局”|“布局5”，输入图表标题，选中文字，利用“开始”菜单中“字体”组的按钮完成对文字的格式化。右击标题，在打开的快捷菜单中选择“设置图表标题格式”命令，在对话框中按要求完成标题框线的设置。将图表拖动到D27:J41区域。

选中纵坐标，右击打开快捷菜单，选中“设置坐标轴格式”，在右侧“设置坐标轴格式”中设置边界的最大值为1500，单位的主要值为200。如图315所示。



图315设置纵坐标轴格式


双击绘图区域，打开“设置绘图区格式”对话框，在对话框中设置填充为纯色，颜色为“白色，背景1，深色25%”。

双击图表区域，打开“设置图表区格式”对话框，在对话框中分别对图表加上蓝色、4.5磅、圆角实线。填充纹理选择“新闻纸”。

第（6）题： 单击“分类汇总”工作表，取消列表中的隐藏行。选中“性别”列中的任一单元格，单击“数据”|“升序”或“降序”。选中A2:J21区域，选择“数据”|“分类汇总”命令，打开“分类汇总”对话框，“分类字段”选择性别，“汇总方式”选择计数，“选定汇总项”选择性别，单击“确定”按钮。选中A1单元格，在编辑栏里将标题修改为“教职工分类汇总表”，利用“开始”菜单的“字体”组中的按钮将标题设置为“楷体、蓝色、20磅、粗体字”。

注意：  在对列表分类汇总前，必须对分类汇总的关键字进行排序。由于平均值和职贴率不参与，在对数据分类汇总前要选择数据范围。

第（7）题： 单击Sheet3工作表，选中图表，单击“图表工具”|“设计”|“切换行/列”命令，分别选中二、三车间的数据系列，按Delete键将其删除。单击“图表工具”|“设计”|“更改图表类型”命令，打开“更改图表类型”对话框，选择“三维饼图”，单击“图表工具”|“设计”命令，在“图标样式”组中选择“样式5”。输入标题“第一季度一车间产值图表”； 利用“开始”菜单的“字体”组中的按钮将标题设置为“宋体、16磅、加粗、深蓝色”。

选中二月份数据系列，向下拖动，双击此区域，在对话框中，将框线设置为红色、4磅实线； 填充颜色为无。选中图例，选择“图表工具”|“图表布局”|“快速布局”|“布局3”命令。选中数据标签，在图表右上方有“+”，单击“+”选择“数据标签”|“更多选项”命令，在页面右侧出现“设置数据标签格式”对话框，选中“值”“百分比”“显示引导线”多选项。利用“开始”菜单的“字体”组中的按钮将数据标签的颜色设置为“深蓝，文字2，淡色40%”，大小为12磅、加粗。

双击图表区域，打开“设置图表区格式”对话框，在对话框中填充效果选择“信纸”； 对图表加上橙色、5磅、圆角的实线。

第（8）题： 打开“页面布局”选项卡，单击“页面设置”组右下角的按钮，打开“页面设置”对话框，在“页眉/页脚”选项卡中按要求设置页眉和页脚。

选择“文件”|“另存为”命令，在对话框中输入“例32.xlsx”文件名，单击“确定”按钮，保存电子表格。


3.2.1数据排序、筛选和条件格式
1. 数据排序

排序就是按照指定的列的数据顺序重新对行的位置进行调整。通常把指定的字段名称为关键字。

1) 单关键字排序

选中要排序的列中的任一单元格，打开“数据”选项卡，选择“排序和筛选”组中的“升序”按钮或“降序”按钮。

2) 多关键字排序

打开“数据”选项卡，在“排序和筛选”组中选择“排序”命令。打开“排序”对话框，在对话框中对主关键字和次关键字进行“升序”或“降序”的排列。例如： 对列表中的“职称”字段按升序排列，相同职称的“工龄”按降序排列。操作如下： 

选中列表中任一单元格。单击“数据”|“排序”命令，打开“排序”对话框，主要关键字选择“职称”，排序方式选择升序； 次要关键字选择“工龄”，排序方式选择降序。单击“确定”按钮。

注意，对整个列表排序只需选中列表中任一单元格，若列表中有部分数据不参与排序，应注意区域的选取，不能选择一行或一列。

对汉字的排序有按拼音排序和按笔画排序两种，系统默认按拼音排序，若要按笔画排序，单击“排序”对话框中的“选项”按钮，打开“排序选项”对话框，选中“笔画排序”单选项。

3) 取消排序

排序更改了数据原来排列的位置，若要恢复列表原来的排列顺序，单击“快速访问工具栏”中的“撤销”按钮。

2. 数据筛选

1) 自动筛选

(1)   选中列表中任一单元格。

(2)  打开“数据”选项卡，在“排序和筛选”组中选择“筛选”命令，列表字段名右侧出现一个下拉按钮。

(3)   单击下拉按钮，选择“文本/数字筛选”|“自定义筛选”，打开“自定义自动筛选方式”对话框，设置过滤条件，按条件筛选。

2) 取消筛选箭头

选中列表中任一单元格，单击“数据”|“筛选”，取消筛选结果，列表恢复原样。

3.   条件格式

为了突出显示所要检查的动态数据或突出显示公式的结果，可以使用条件格式标记单元格。

(1)  选中要设置条件格式的区域。

(2)  单击“开始”菜单，在“样式”组，选择“条件格式”|“新建规则”，打开“新建格式规则”对话框。

(3)  在“选择规则类型”框中选择条件格式类型，在“编辑规则说明”框中设置条件，条件可以是单元格数值或公式。单击“格式”按钮，打开“设置单元格格式”对话框，在对话框中设置数据的字体、颜色、边框、背景色或图案。

若要添加条件格式可选择“开始”|“条件格式”|“管理规则”，打开“条件格式规则管理器”对话框，如图316所示。在对话框中可以新建、编辑和删除规则。



图316“条件格式规则管理器”对话框



条件格式一旦设定，在被删除前对单元格一直起作用。更改或删除条件格式在“条件格式规则管理器”对话框中完成。

3.2.2数据透视表和分类汇总
1.  数据透视表

1) 数据透视表的建立

创建数据透视表的操作步骤如下： 

(1)  选中参与数据透视表的列表区域。

(2)  单击“插入”菜单，在“表格”组中选择“数据透视表”命令，打开“创建数据透视表”对话框，在对话框中首先选择表或区域，若在创建透视表前已选定了所需数据的列表区域，系统会自动输入数据区域。也可以选择使用外部数据源。其次是选择放置数据透视表的位置，选择“新建工作表”选项，透视表建立在新建工作表上，选择“现有工作表”选项，同时指定透视表存放的起始单元格位置，透视表建立在同一工作表上。单击“确定”按钮。

(3)  在选定的放置数据透视表的位置上显示如图317所示的空白数据透视表，右侧显示如图318所示的“数据透视表字段列表”。 




图317空白的数据透视表                  




图318数据透视表字段列表




(4)  根据要求分别将字段拖动到“报表筛选”区、“行标签”区、“列标签”区和“数值”区，数据透视表创建完毕。

2) 数据透视表的编辑

数据透视表建好后，在功能区自动激活“数据透视表工具”，数据透视表工具中包含“选项”和“设计”选项卡，用以编辑和格式设计数据透视表。

(1) 增加和删除数据。

选中数据透视表的任一单元格，系统自动在右侧显示“数据透视表字段列表”，将“数据透视表字段列表”中的字段拖动到“数值”区，增加数据。将字段拖动出“数值”区，删除数据。

(2) 拖动字段改变透视表的结构。

在“数据透视表字段列表”中改变行标签区、列标签区和报表筛选区中的字段即可改变数据透视表的结构，例如通过交换行标签区和列标签区中的字段改变透视表的行列结构。

(3) 隐藏和显示数据。

单击数据透视表中“行标签”“列标签”和“报表筛选”右侧的按钮，在下拉列表中选择需隐藏的数据。

(4) 改变数据汇总方式。

建立数据透视表时，系统默认求和方式，根据需要还可以改变为其他汇总方式，例如，平均值、最大值、最小值等。双击或单击“数值”区字段右侧的按钮，在下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框，在对话框中更改汇总方式。

(5) 更新数据透视表。

列表中的数据发生了变化，通过刷新操作即可更新透视表中的数据。单击“数据透视表工具”|“选项”|“刷新”或选择快捷菜单中的“刷新”命令。数据透视表中的汇总数据会随列表数据的改变而更新。

3) 数据透视表的格式化和图表化

(1) 格式化。

自动套用格式： 选择“数据透视表工具”|“设计”|“数据透视表样式”命令。

自定义： 选择“开始”|“格式”|“设置单元格格式”命令或选择快捷菜单中的“设置单元格格式”命令。

(2) 图表化。

操作同工作表的图表(参见3.2.3数据图表制作与格式化)。

4) 删除数据透视表

选中数据透视表，选择“开始”|“清除”|“全部清除”命令。


2.  分类汇总

1) 分类汇总表的建立




图319“分类汇总”对话框

对列表的数据分类汇总前，必须对分类汇总的关键字进行排序。例如统计不同职称的人数，操作步骤如下： 

(1)  选中“职称”列中的任一单元格，单击“数据”|“升序”或“降序”命令。

(2)  打开“数据”选项卡，在“分级显示”组中选择“分类汇总”，打开如图319所示的“分类汇总”对话框。

“分类字段”选择经过排序的字段，在此选择“职称”。
“汇总方式”求和、计数、平均值，……，在此选择“计数”。“选定汇总项”汇总数据存放的位置，在此选择“职称”。

(3)  单击“确定”按钮，分类汇总表建立完毕。

2) 分类汇总表的多级显示

分类汇总表共分三级显示： 第一级最高级，显示总的汇总结果，第二级显示总的汇总结果与分类汇总结果，第三级显示汇总结果和全部数据。

分级显示的操作通过汇总表左侧的表明分级范围的分级线、控制数据显示层次的分级按钮和显示明细数据的“+”按钮或隐藏明细数据的“-”按钮。

若要删除分类汇总表的分级显示，打开“数据”选项卡，在“分级显示”组中选择“取消组合”|“清除分级显示”命令。

3) 嵌套分类汇总表

在已建好的分类汇总表上再创建一个分类汇总表称为嵌套分类汇总表。例如在职称计数的分类汇总表上嵌套基本工资求和的汇总表。

单击“数据”|“分类汇总”命令，再次打开“分类汇总”对话框，正确地选择各选项，最后取消选中“替换当前分类汇总”多选框。若不去掉此选项新的汇总表将取代老的汇总表。

4) 分类汇总表的删除

打开“分类汇总”对话框，单击“全部删除”按钮，列表恢复原有数据，但排序结果不能恢复。

5) 分类汇总操作要点

(1)  列表中若有隐藏行必须先取消隐藏； 

(2)  分类的字段必须先排序； 

(3)  列表中若有不参与汇总的数据，必须选择汇总数据的区域。

3.2.3数据图表制作与格式化
1. 图表的创建和组成

1) 创建图表

图表有图表工作表和嵌入图表两种，其创建的方法不同。

创建图表工作表的方法： 选择数据，按功能键F11。图表工作表默认的表标签名分别为Chart1、Chart2等。

创建嵌入图表的方法： 单击“插入”菜单，在“图表”组中选择图表类型。操作步骤如下： 

(1) 选取需要用图表表示的数据区域。

(2) 单击“插入”菜单，插入“图表”组中推荐的图表类型和推荐类型。图表创建完毕，此时系统自动在功能区上方激活“图表工具”，图表工具包括“设计”选项卡和“格式”选项卡。

“设计”选项卡主要用于图表类型更改、数据系列的行列转换、图表布局、图表样式的选择。

“格式”选项卡用于设置和编辑形状样式、艺术字、排列和大小。

2) 图表的组成

参见图320，图表的各组成部分都能编辑和设置格式。





图320图表及图表的组成



2. 图表编辑

1) 缩放、移动、复制和删除图表

单击图表区，图表边框上有8个控制块。缩放图表： 拖动控制块。移动图表： 拖动图表区域。复制图表： 按住Ctrl+拖动图表。删除图表： 按Delete键。

2) 图表数据的编辑

(1) 增加数据系列： 单击“图表工具”|“设计”|“选择数据”命令，打开如图321所示的“选择数据源”对话框，在对话框中可以添加、编辑和删除数据系列，还可以进行数据系列的行列转换。




图321“选择数据源”对话框


选中需增加的数据系列，按Ctrl+C组合键； 选中图表，按Ctrl+V键。

(2) 删除数据系列： 选中数据系列按Delete键或在“选择数据源”对话框中单击“删除”按钮。

(3) 修改数据点： 修改了工作表中的数据，图表中的数据系列会自动更新。

(4) 重排数据系列： 为了突出数据系列之间的差异和相似对图表数据系列重新排列。选中任一数据系列，单击“图表工具”|“设计”|“选择数据”命令，在弹出的“选择数据源”对话框中单击“上移”或“下移”按钮来调整。

(5) 添加趋势线和误差线。

为了预测某些特殊数据系列的发展变化规律，可以对此数据系列加上趋势线和误差线。选中需预测的数据系列，选择“图表工具”|“布局”|“趋势线”|“误差线”列表中的趋势线或误差线类型。删除趋势线或误差线的操作： 选中趋势线或误差线，按Delete键。

注意：  三维图表、饼图等不能添加趋势线和误差线。

(6) 饼图或环形图的分解和旋转。

分解操作： 选中数据系列，拖动。

旋转操作： 双击数据点，打开“设置数据点格式”对话框，在“系列选项”选项中的“第一扇区起始角度”中输入需旋转的角度。

(7) 设置调整图表选项。

图表选项包括标题、主坐标轴、网格线、图例、数据标记、数据表。选中图表，选择“图表工具”中的“布局”选项卡中的选项。

3) 附加文字说明及图形

文字说明： 选择“图表工具”|“图表布局”|“文本框”下拉列表中的选项。图形和箭头： 选择“图表工具”|“图表布局”|“形状”下拉列表中的选项。删除附加对象： 选中，按Delete键。

4) 图表区格式

双击图表区，打开“设置图表区格式”对话框，在对话框中分别设置图表的填充颜色、边框颜色和边框样式、阴影、三维格式、属性等图表区格式。

5) 调整三维图形

选中三维图形，单击“图表工具”|“图表布局”|“三维旋转”，在对话框中输入旋转和透视的角度。

6) 改变图表类型

选中图表，单击“图表工具”|“设计”|“更改图表类型”命令，在弹出的对话框中选择图表类型。

3. 图表工作表的编辑

图表工作表的缩放、移动、复制和删除的操作方法同工作表，图表对象的编辑操作同嵌入图表。



扫码观看


3.2.4综合练习

1. 打开“学生成绩表.xlsx”文件，按下列要求对工作表进行编辑，编辑结果用原文件名保存。样张参见图321和图322。


(1) 计算总分、平均分(保留一位小数)和评价(三门课程中其中有一门大于等于90分的为“单项优秀”，其余的为“一般”)。

(2) 统计单项优秀的人数，计算结果存放在I28单元格。

对外语成绩按降序、总分为升序排列，平均分行不参与，见图322。

(3) 按样张(见图323左图)，在当前数据表中A39单元格建立数据透视表，样式选择数据透视表样式中等深浅13。

(4) 筛选出所有男学生的记录，保留平均分行。

(5) 按样张(见图322右图)在E37建立图表，图表标题“部分学生考试成绩统计图表”，格式为12磅、粗斜体、深红色； 边框颜色为绿色、3磅实线。图表区边框颜色为“水绿色，个性色5，深色25%”、3磅实线、圆角、阴影为预设“左下斜偏移”选项，填充效果为蓝色面巾纸。绘图区填充“白色，背景1，5%”。

(6) 在列表第1行前插入一行，输入标题“建新中学期末考试成绩表”并设置格式为华文新魏、18磅、粗体、紫色。外框为紫色双线，内框为紫色单细线。

(7) 文档设置为水平居中，页脚居中为文件名和数据表名。




图322练习题1①~③样张




图323综合练习1数据透视表和图表样张


操作提示： 

第(1)题： 选择“文件”|“打开”命令，打开学生成绩表.xlsx。选中H2单元格，双击“公式”|“自动求和”，拖动至H27单元格。选中E28单元格，输入公式： =Average(E2:E27)，单击“开始”菜单，在“数字”组中，将平均值保留一位小数，拖动至G28单元格。选中I2单元格，输入公式： =IF(OR(E2>=90，F2>=90，G2>=90)，“单项优秀”，“一般”)，拖动至I27单元格。

第(2)题： 选中I28单元格，输入公式： =COUNTIF(I2:I27，"单项优秀")。

选中A1:I27区域，选择“开始”|“排序和筛选”|“自定义排序”，在对话框中主要关键字选择外语、降序； 次要关键字选择总分、升序。

第(3)题： 选中A1:I27区域，单击“插入”|“数据透视表”，打开“创建数据透视表”对话框，选中“现有工作表”单选项，“位置”输入A39，单击“确定”按钮。在“数据透视表字段列表”中将“所在班级”字段拖动到报表筛选区； 将“评价”字段拖动到列标签区； 将“外语”“语文”“数学”字段拖动到数值区，单击“外语”右侧的按钮，打开“值字段设置”对话框，“计算类型”选择“最大值”选项。用同样的方法设置语文为最小值、数学为平均值。将“∑数值”拖动到行标签区。选中透视表，选择“数据透视表工具”|“设计”|“数据透视表样式”|“数据透视表样式中等深浅 13”，对透视表添加格式。

第(4)题： 选中列表中任一单元格，选择“开始”|“排序和筛选”|“筛选”，单击性别右侧的下拉按钮，选择“文本筛选”|“自定义筛选”，在对话框中性别选择“不等于女”。

第(5)题： 选中金敏、杜明生、吴欣对应的数学和外语的数据，选择“插入”|“柱形图”|“簇状柱形图”，在E37创建图表。单击“图表工具”|“设计”|“切换行/列”，切换数据系列。

输入标题“部分学生考试成绩统计图表”，利用“开始”菜单“字体”组中的按钮将标题的字体设置为“12磅、粗斜体、深红色”。右击图表标题，在打开的快捷菜单中选择“设置图表标题格式”命令，在对话框中“边框颜色”选择“实线”，“颜色”为“绿色”。“边框样式”选择3磅实线。

设置标题右击绘图区，在快捷菜单中选择“设置绘图区格式”，将绘图区的颜色填充为“白色，背景1，深色5%”。右击图表区，在打开的快捷菜单中选择“设置图表区域格式”，在对话框中“填充”选择“图片或纹理填充”|“蓝色面巾纸”。“边框颜色”选择“实线”选项，“颜色”为“水绿色，个性色5，深色25%”。“边框样式”选择3磅实线，圆角。“阴影”选择“预设”|“左下斜偏移”选项，颜色为“黑色，文字1”，距离为“10磅”。

右击坐标轴，在快捷菜单中选择“设置坐标轴格式”命令，在对话框中“主要刻度单位”固定20。

单击“插入”|“形状”|“标注”|“云形标注”，按要求对图表中的数据系列添加标注。将图表放在E37:H51区域。

第(6)题： 在列表第1行前插入一行，输入标题并按要求设置格式。

第(7)题： 打开“页面布局”选项卡，单击“页面设置”组右下角的按钮，打开“页面设置”对话框，在对话框的“页边距”选项卡中，居中方式选择“水平”多选项； 在“页眉/页脚”选项卡中，单击“自定义页脚”按钮，在“居中”对话框中插入“文件名”和“数据表名称”。

选择“文件”|“另存为”命令，在对话框中输入“学生成绩表.xlsx”文件名，单击“确定”按钮，保存操作结果。


2.  打开“2016—2021年某公司利润值及其增长速度.xlsx”文件，在E2:K16区域建立如图324和图325所示的柱状图、折线图组合图表。添加“2016—2021年某公司利润值及其增长速度”图表标题，将图表标题形状样式选择为“透明，彩色轮廓黑色，深色1”。将图表中最大值的数据显示出来，其他格式见样张，以同名保存。



图324柱形图、折线图组合图表样张




图325“组合”对话框


(1) 选中数据表中的数据，选择“插入”|“图表”|“所有图表”|“组合”，将“利润值”的图表类型选择为“簇状柱形图”，将“比上年实际增长”的图表类型选择为“带标记的折线图”，并勾选“次坐标轴”(如图324所示)，单击“确定”按钮，将增长速度用带标记的折线图表示。

(2) 选中右侧的次坐标轴右击，在打开的快捷菜单中选择“设置坐标轴格式”，在弹出的任务窗格中将“坐标轴选项”中的“最大值”改为“30”，单击“确认”按钮。

(3) 选中图表标题区域，将标题修改为“2016—2021年某公司利润值及其增长速度”。选中图表标题，选择“图表工具”|“格式”|“形状样式”，在形状样式库中选择“预设”中的“透明，彩色轮廓黑色，深色1”。

(4) 单击图表中的蓝色数据柱形图，再单击2021年的蓝色数据柱形图，在选中该最大值数据的状态下，单击图表的右上角“+”，勾选“数据标签”。

(5) 用同样的方法，先单击图表中的橙色折线，再单击2017年的数据标记，在选中该最大值数据的状态下，单击图表右上角的“+”，勾选“数据标签”。

(6) 选中刚建好的图表，将其拖动到指定位置E2:K16，调整大小。

(7) 以同文件名保存文件。

习题

1.	启动Excel，打开“\素材\用电情况表.xlsx”文件，按下列要求并参照样张操作，将结果以原文件名保存（计算必须用公式，否则不计分）。

（1） 设置表格标题为： 华文新魏、24磅、水绿色，个性色5，深色25%。在A1: I1区域中跨列居中，并设置表格的边框线，如图326所示。金额数值取2位小数，人民币符号。



图326习题1样张1

 
（2） 计算“日金额”(“日用量数”×sheet2中的“日单价”)、“夜金额”(“夜用量数”×sheet2中的“夜单价”)、“合计金额”(“日金额”+“夜金额”)、“已用电量”(“日用量数”+“夜用量数”)和“剩余电量”(sheet2中的“用电总量”-“已用电量”)。

（3） 取三楼的“日用量数”和“夜用量数”数据，在 A21:G33区域中生成条形图，图表标题为“三楼4户居民用电量比较”； 设置次要网格线，如图327所示。图表中所有文字及数据标记大小均为10磅。



图327习题1样张2

 
注意： 样张仅供参考，相关设置按题目要求完成即可。由于显示器颜色差异，做出结果与样张图片中存在色差也是正常的。


2.	启动Excel，打开“\素材\设备采购清单.xlsx”文件，对Sheet1中的表格按以下要求操作，将结果以原文件名保存（计算必须用公式，否则不计分）。

（1） 在“名称”列前插入一个列，命名为“类别编号”，并此列相应单元格中输入各商品的类别编号（编号方法： 抛光机类为“01”，直磨机类为“02”，砂轮机类为“03”，砂带机类为“04”）。

（2） 利用公式在相应单元格中计算金额（金额=单价×数量），保留1位小数。根据总额进行评价： 金额大于或等于3000元为较贵； 金额小于3000元且大于或等于1000元为正常； 金额小于1000元为便宜。设置A1: H13区域套用“表样式中等深浅10”的表格格式，单元格内容为水平居中对齐。效果如图328所示。



图328习题2样张1


（3） 设置除B列和C列为自动列宽外，其余各列的列宽均为8； 在B15开始的单元格中生成数据透视表，以“类别编号”为行标签来统计“各类别商品的平均金额”，设置数字为货币格式。效果如图329所示。



图329习题2样张2


3. 	启动Excel，打开“\素材\防疫用品统计表.xlsx”文件，对Sheet1按以下要求操作，将结果以原文件名保存在C:＼KS文件夹中（计算必须用公式，否则不计分）。

（1） 在A1单元格中输入文字“某公司防疫物资库存情况”，设置字体为楷体、大小为14，加粗； 蓝色，个性色5，深色25%； 并在A1:H1单元格区域中跨列居中。在标题行下增加一行，在H2单元格中输入系统日期。

（2） 在G2单元格中输入文字“库存量”。在H2单元格中输入文字“库存金额（元）”，利用公式计算各项物资的库存金额（库存金额＝库存量×价格），保留1位小数，设置数字为货币格式，人民币符号。设置各列列宽为“自动调整列宽”。计算库存金额的总计值，放入H14单元格。效果如图330所示。



图330习题3样张1

 
（3） 筛选出类别为“防护用品”的记录，并参照样张在B16: E26区域制作“防护用品库存数量”的簇状柱形图，修改图表标题为“防护用品库存数量”，无图例。坐标格式参见样张，如图331所示。




图331习题3样张2