第5章表格数据处理







在信息化时代，计算机网络成为人们获取信息的重要工具。在互联网中所获取的数据资料往往要借助于智能软件工具进行相关的处理后才能进行进一步的统计与分析。根据数据类型的不同，可以使用不同的方式进行处理和分析，而Office正是进行不同类型数据和信息管理及处理的工具。在第4章中，介绍了如何使用Word和PowerPoint对于非结构化信息进行处理和展示。除非结构化数据外，以二维表结构进行逻辑表达和实现的结构化数据也是大数据分析的主要对象。
本章将以Excel 2016为主要工具，通过大量实操案例介绍如何利用Excel强大的数据计算、统计分析、辅助决策以及图表绘制功能，完成表格数据的存储、管理、运算、统计分析与制图输出等操作，从而对获取的原始数据进行预处理，使其满足大数据分析的需要。
5.1Excel基础
制作电子表格是Excel最基本的功能。电子表格同书面表格一样，可以存储和记录各种类型的数据及信息，满足人们对这些信息的存储、查询和管理需要。本节将介绍Excel 2016制作电子表格的基础知识和概念。
5.1.1Excel界面与基本术语
Excel 2016保持了与其他Office 2016组件一致的工作界面风格，Excel 2016基本操作界面如图51所示，包含基本的组成部分，如标题栏、功能区、状态栏等。根据自身功能定位，Excel 2016的工作界面也有一定的特殊性，下面将结合图51中的内容介绍Excel的工作界面和基本术语。


图51Excel 2016基本操作界面


(1) 工作簿。工作簿是一个Excel电子表格文件，用来存储并处理工作数据的文件，其默认的名称是Book，以xlsx为扩展名。
(2) 工作表。工作簿中有若干由水平方向的行与垂直方向的列构成的表格，称为工作表。一个工作簿可以包含多张工作表，工作表的默认名称为Sheet。当前显示的工作表称为活动工作表，单击不同的标签项，可以在不同的工作表之间切换显示。


(3) 功能区。Excel的基本功能都可以在功能区中通过不同类型的选项卡来实现，这些选项卡包括“文件”“开始”“插入”“页面布局”“公式”“数据”“审阅”“视图”“帮助”等。同时，功能区中的内容还可以通过“Excel选项”设置进行灵活的自定义调整。
(4) 工作区。工作区由行号、列标、工作表标签和单元格组成，可以输入不同类型的数据，是最直观显示所有输入内容的区域。
(5) 单元格。工作表的每一行和每一列交叉形成的小格称为单元格，它们是Excel中最基本的操作对象。可以在单元格中输入数字、文字、日期、公式等数据。每个单元格都有一个地址，由行号与列标组成。其中行号为数字1~1048576，列标为字母A~Z、AA~ZZ、AAA~XFD。例如，G3表示是第3行第7列的单元格地址。
(6) 名称框。名称框位于工作区的左上方，用于指示当前选定的单元格、图表项或绘图对象。
(7) 编辑栏。编辑栏位于工作区的右上方，用于显示、输入和编辑当前活动单元格中的数据或公式。单击“取消”按钮可以取消在编辑栏输入的内容，单击“输入”按钮可确定输入的内容，单击“插入函数”按钮可插入函数。
5.1.2Excel的基本操作
工作簿、工作表与单元格是组成Excel文件的三大要素，Excel中的操作主要是针对它们进行的。下面介绍工作簿、工作表与单元格的基本操作。
1. 工作簿的基本操作
Excel的工作簿实际上就是一个Excel文件，因此对工作簿的操作就是对文件的操作，包括创建、保存、打开与关闭工作簿。
1) 创建工作簿
可以使用多种方法创建工作簿，包括： 
（1） 在Excel工作窗口中创建。在现有的工作窗口上，有以下3种等效操作可以创建新工作簿。
① 由系统“开始”菜单或桌面快捷方式启动Excel，打开Excel程序窗体，单击“空白工作簿”命令。
②  在功能区上选择“文件”→“新建”命令，单击“空白工作簿”按钮。
③  按Ctrl+N组合键。
(2)  在系统中创建工作簿文件。
安装了Office 2016的Windows系统会在鼠标右键菜单中自动添加新建“Microsoft Excel工作表”的快捷命令，通过这一快捷命令也可以创建新的Excel工作簿文件。具体操作如下。在Windows桌面或文件夹窗口的空白处右击，在弹出的快捷菜单中选择“新建”→“Microsoft Excel工作表”命令，如图52所示。完成操作后可在当前位置创建一个新的Excel工作簿文件，双击此新建的文件，即可在Excel工作窗口中打开此工作簿。


图52“Microsoft Excel工作表”命令


2) 保存工作簿
在工作簿中输入数据或完成编辑工作后，可以对相关操作进行保存以防止数据的丢失。保存工作簿可使用多种方法。
(1) 单击“快速访问工具栏”中的“保存”按钮。这种方式下，文件的保存位置与上次保存的位置相同。
(2)  在功能区上选择“文件”→“保存”命令。这种方式下，文件的保存位置与上次保存的位置相同。
(3)  在功能区上选择“文件”→“另存为”命令，弹出“另存为”对话框。在“保存位置”下拉列表框中选择工作簿要存放的文件夹或磁盘名。在“文件名”下拉列表框中输入文件名，在“文件类型”下拉列表框中选择文件类型，最后单击“保存”按钮。
(4)  按Ctrl+S组合键。
(5)  按Shift+F12组合键。
3) 打开工作簿
打开现有工作簿的方法如下所述。

（1） 直接通过文件打开。利用Windows资源管理器找到工作簿文件所在路径，直接双击该文件图标即可打开。
（2）  使用“打开”对话框打开。先启动Excel 2016，在功能区上选择“文件”→“打开”命令，在“打开”对话框中选择要打开的工作簿文件，单击“打开”按钮即可。
另外，使用这种方法还可以打开其他类型的文件，并通过“另存为”命令将其保存为工作簿文件。下面将在Excel中打开“中国银行.csv”文件，该文件是使用第3章所介绍的爬虫工具采集到的中国银行股票数据信息，最后将其保存为工作簿文件“中国银行.xlsx”。打开并保存工作簿文件具体操作如图53所示。




图53打开并保存工作簿文件具体操作



图53（续）



4) 关闭工作簿和Excel程序
当用户结束工作后，可以关闭Excel工作簿。关闭工作簿可使用多种方法。
（1）  单击工作簿窗口中的“关闭”按钮。
（2）  在功能区上选择“文件”→“关闭”命令。
（3）  按Ctrl+W组合键。
（4）  按Alt+F4组合键。
（5）  在功能区右击，在弹出的快捷菜单中选择“关闭”命令。
2. 工作表的基本操作
1) 创建、重命名和删除工作表
在新建工作簿时，Excel将自动为该工作簿创建一个工作表。如果需要，可添加新的工作表，并完成工作表的重命名和删除等管理工作，具体操作如下。
（1） 单击工作表标签右侧的“新工作表”按钮，如图54所示，即可在工作表的末尾插入新工作表。单击“开始”选项卡“单元格”组中的“插入”按钮，在下拉列表中选择“插入工作表”命令，或在当前工作表标签上右击，在弹出的快捷菜单中选择“插入”命令，在“插入”对话框中选中“工作表”类型，再单击“确定”按钮，即可在当前工作表右侧插入新工作表。


图54单击工作表标签右侧的“新工作表”按钮

（2） 若需要为新工作表定义一个名称或者重命名现有的工作表，可以在该工作表标签上双击，或者先通过标签选中要命名的工作表，然后单击“开始”选项卡“单元格”组中的“格式”按钮，在下拉列表中选择“重命名工作表”命令，此时工作表标签名称将进入可编辑状态，输入新的工作表名后按Enter键确认。
（3）  当工作簿中有多张工作表时，可以为工作表标签设置颜色以突出显示部分工作表。在要改变颜色的工作表标签上右击，在弹出的快捷菜单中选择“工作表标签颜色”命令，或者单击“开始”选项卡“单元格”组中的“格式”按钮，在下拉列表中选择“工作表标签颜色”命令，打开颜色选择列表，在其中选择一种颜色作为工作表标签颜色。
（4）  如果要删除某一个工作表，可在要删除的工作表标签上右击，在弹出的快捷菜单中选择“删除”命令，或单击“开始”选项卡“单元格”组中的“删除”按钮，在下拉列表中选择“删除工作表”命令即可。工作表删除后将被永久删除，不能恢复。
2) 同时选定多个工作表
除了选定某个工作表作为当前工作表外，还可以同时选中多个工作表形成“组”。在工作组模式下，可以方便地同时对多个工作表对象进行复制、删除等操作。可以使用多种方法同时选定多个工作表。
（1） 按住Ctrl键，同时依次单击需要选定的工作表标签，即可同时选定多个工作表。
（2）  如果需要选定的工作表为连续排列的工作表，可以先单击其中的第一个工作表标签，然后按住Shift键，再单击连续工作表中的最后一个工作表标签，即可同时选定连续的多个工作表。
（3）  如果要选定当前工作簿中的所有工作表组成工作组，可以在任意工作表标签上右击，在弹出的快捷菜单中选择“选定全部工作表”命令。
（4）多个工作表被同时选中后，会在标题栏上显示“组”字样，被选定的工作表标签都将反白显示。单击任意一个工作表标签可取消工作组，标题栏的“组”字样也同时消失。
3) 移动和复制工作表
以下方法可以实现工作表的移动和复制操作。
（1） 使用菜单。


图55“移动或复制工作表”对话框

选中要移动或复制的工作表，单击“开始”选项卡“单元格”组中的“格式”按钮，在下拉列表中选择“移动或复制工作表”命令，或右击选中的工作表标签，在弹出的快捷菜单中选择“移动或复制”命令，都将会弹出“移动或复制工作表”对话框，如图55所示。在该对话框中选择好目标工作簿，再选择工作表要移动或复制的位置，并根据需要选择是否建立副本，最后单击“确定”按钮即可。
【注意】“建立副本”即为复制工作表，否则为移动工作表。
（2） 使用鼠标拖动。
使用鼠标拖动实现移动或复制工作表的方法是首先打开目标工作簿，选中要移动或复制的工作表，按住鼠标左键沿着标签栏拖动鼠标，当黑色小三角形移到目标位置时，释放鼠标左键即可移动工作表。若要复制工作表，则在拖动过程中按住Ctrl键。
4) 拆分工作表
当工作表中的数据比较多并且需要比较工作表中不同部分数据时，可以对工作表进行拆分，如图56所示，以使屏幕能同时显示工作表的不同部分，方便用户对较大的表格进行数据比较。


图56拆分工作表


（1） 水平拆分工作表。
单击要进行水平拆分的行号，再单击“视图”选项卡“窗口”组中的“拆分”按钮，工作表将被拆分成上、下两部分。
（2） 垂直拆分工作表。
单击要进行垂直拆分的列号，再单击“视图”选项卡“窗口”组中的“拆分”按钮，工作表将被拆分成左、右两部分。
（3） 同时进行水平、垂直拆分工作表。
单击工作表中某个单元格，再单击“视图”选项卡“窗口”组中的“拆分”按钮，工作表将被拆分成上、下、左、右4部分。
（4） 取消拆分。
将鼠标指针定位到拆分条上，按住鼠标左键即可移动拆分条，从而改变窗格的布局。在工作表处于拆分状态时，将拆分条拖到窗口边缘或在拆分条上双击，即可在窗口内去除某拆分条。再次单击“视图”选项卡“窗口”组中的“拆分”按钮，将同时取消水平拆分和垂直拆分的效果。
5) 冻结工作表窗格
（1） 单击“视图”选项卡“窗口”组中的“冻结窗格”按钮，将打开冻结窗格下拉列表，如图57所示，选择需要冻结的内容即可。若选择“冻结窗格”命令，则将在选中的单元格的上面和左边出现两条细实线，细实线的上面和左边部分单元格区域不再随着滚动条的滚动而移动。



图57冻结窗格下拉列表


（2） 若需要取消冻结，则再次单击“冻结窗格”按钮，在下拉列表中选择“取消冻结窗格”命令即可。
3. 单元格、行、列的基本操作
1) 选择单元格、行与列
单元格是存放输入数据或公式的区域。在对单元格进行编辑操作之前，首先要选择一个或多个单元格区域。
（1） 选择单个单元格。直接单击所要选择的单元格，选中的单元格将以黑色边框显示。
（2） 选择连续的单元格区域。在要选择区域的第一个单元格上按下鼠标左键并拖动鼠标到适当位置后释放鼠标。鼠标划过的连续的矩形区域即为选中的单元格区域。或者先单击要选择区域的第一个单元格，然后按下Shift键的同时单击最后一个单元格，此时两次单击之间的连续多个单元格即为选中的单元格区域。
（3） 选择不连续的单元格区域。单击任意一个要选择的单元格，然后按住Ctrl键继续单击其他需要选择的单元格。
（4） 选择一行或一列。单击所要选择行的行号或列的列标。
（5） 选择连续的多行(列)。选中第一行(列)后，按住鼠标左键并拖至要选择的最后一行(列)。或者选中第一行(列)后，按住Shift键再选中最后一行(列)。
（6） 选择不连续的多行(列)。选中第一行(列)后，按住Ctrl键的同时一一选中其他需要选择的行(列)。
（7） 全选。按Ctrl+A组合键或者单击工作表左上角的“全选”按钮，即可选择整个表格区域。
2) 插入单元格、行与列
当在工作表中插入单元格、行与列后，原有的单元格将发生移动。

（1） 插入单元格。首先选择要插入单元格的位置，单击“开始”选项卡“单元格”组中的“插入”按钮，在下拉列表中选择“插入单元格”命令，或右击要插入单元格的位置，在弹出的快捷菜单中选择“插入”命令，在对话框中选择一种插入方式后，单击“确定”按钮。
（2） 插入行或列。单击某单元格，单击“开始”选项卡“单元格”组中的“插入”按钮，在下拉列表中选择“插入工作表行”或“插入工作表列”命令，将在该单元格的上方插入一行或左侧插入一列。也可以通过插入单元格的方法，选择“整行”或“整列”插入方式插入行或列。
3) 合并单元格
合并单元格是指将相邻的两个或多个水平或垂直单元格区域合并为一个单元格。区域左上角单元格的名称和内容自动成为合并后的单元格的名称和内容，区域中其他单元格的内容将被删除。合并单元格的方法如下。
(1) 方法一。
先选中要进行合并操作的单元格区域，单击“开始”选项卡“对齐方式”组中的“合并后居中”按钮，或单击其右侧的倒三角按钮打开下拉列表。下拉列表中的命令功能如下。
①合并后居中： 将选中的所有单元格合并为一个单元格，且合并后单元格的内容默认为水平居中、垂直居中显示。
②  跨越合并： 将所选单元格的每行合并到一个更大的单元格。
③  合并单元格： 将选中的所有单元格合并为一个单元格，且保留合并前左上角单元格的对齐方式。
(2) 方法二。
先选中要进行合并操作的单元格区域，单击“开始”选项卡“对齐方式”组右下角的对话框启动器按钮，弹出“设置单元格格式”对话框，如图58所示。单击对话框中的“对齐”选项卡，勾选“合并单元格”复选框，单击“确定”按钮。通过此对话框可同时设置合并后的单元格内容的水平对齐、垂直对齐方式以及文本的方向。



图58“设置单元格格式”对话框

4) 删除单元格、行与列
先选中要删除的单元格、行或列，然后单击“开始”选项卡“单元格”组中的“删除”按钮，在下拉列表中选择“删除单元格”“删除工作表行”或“删除工作表列”命令； 或者右击要删除的一个单元格，在弹出的快捷菜单中选择“删除”命令，在弹出的“删除”对话框中选择一种删除方式后，单击“确定”按钮。



视频讲解



5.1.3在工作表中输入和导入数据
要用Excel处理数据，首先要获取数据，在空白的工作簿中添加数据的方式包括以下两种： 一种是手工输入，这种方式通常用于数据量较小的应用； 另一种是外部导入，即从其他数据源，如网页、文档、外部工作表中批量导入数据。
1. Excel的数据类型
Excel中的数据类型有文本型、数值型、日期型和时间型等。
1) 文本型数据
文本型也称字符型，由汉字、字母、数字及符号等字符组成，如企业的部门名称、学生的考试科目、姓名等。
文本的输入比较简单，一般文本直接输入即可。文本数据默认的对齐方式为左对齐。对于由纯数字组成的文本，如学号、手机号、身份证号码、邮政编码等，输入时应在数字前输入一个英文单引号作为纯数字文本的前导符(如'201713113001)； 或者在输入之前选中单元格，在“设置单元格格式”对话框的“数字”选项卡下将其设置为“文本”格式。
如果想要将所有文本显示在当前单元格中，可以在输入时按Alt+Enter组合键在单元格内换行； 或者在“设置单元格格式”对话框的“对齐”选项卡下将其设置为“自动换行”。
2) 数值型数据
数值型数据由数字0~9、正负号(+、-)、小数点(.)、百分号(%)、货币符号(如$)、指数符号(E)等组成。数值型数据默认的对齐方式为右对齐。
数值型数据输入时主要注意负数、分数的输入方法。可以直接输入负号及数字或用圆括号来进行负数的输入，如输入“(100)”就相当于输入了“-100”。而输入分数的方法为先输入一个0，然后输入一个空格，再输入该分数。如输入“0 2/3”即可输入分数23,否则系统会显示为“2月3日”。
3) 日期型和时间型数据
Excel中日期型和时间型数据是以一种特殊的数值形式存储的，这种数值形式称为“序列值”，范围为1~2958465，对应的日期为1900年1月1日~9999年12月31日。例如，1900年1月15日的序列值为15，2007年5月1日的序列值为39203。
(1) 日期存储为数值的形式，它继承了数值的所有运算功能，日期运算的实质是序列值的数值运算。例如，要计算两个日期之间相距的天数，可以直接在单元格中输入两个日期，再用减法运算的公式来计算即可。
(2)  时间型数据则被存储为小数，0对应0时，1/24对应1时，1/12对应2时。如1.5对应于1900年1月1日12∶00。
日期与时间的输入要遵循一定的格式，否则系统会把输入的时间当作文本来处理。
(3) 日期的标准输入方式： 使用斜线或短横线分隔日期的年、月、日。例如，在单元格中输入“2018/6/28”或“2018628”，按Enter键后，单元格最后显示的日期格式都是“2018/6/28”。如果输入“2018年6月28日”或者“6月28日”，Excel也会智能识别出这是日期型数据，只是不改变当前的显示格式，但在上方的编辑栏内这几种输入方式都会被自动转换为标准日期形式，如图59所示，其中B列是输入格式，D列是显示格式，而编辑栏内都会转换为标准输入格式，表示这是日期型数据。



图59标准日期形式


(4)  时间的标准输入方式： 使用冒号(:)分隔时、分、秒。如果采用12小时制的时间，Excel将把输入的时间默认为上午时间(AM)； 若输入的是下午时间，则应在时间后面加一空格，然后输入PM。系统默认为24小时制。如果要同时输入日期与时间，需要在日期与时间之间输入一个空格。
(5)  日期型和时间型数据默认的对齐方式为右对齐。按“Ctrl+；”组合键可以输入当前系统日期； 按“Ctrl+Shift+；”组合键可以输入当前系统时间。
2. 手动输入不同类型的数据
在工作表中输入信息最直接的方法就是手动输入数据。
【例51】使用Excel工作表管理某公司的员工基本信息，使用手动输入的方式完成部分信息的输入工作。
具体操作如下。
(1) 打开工作表Sheet1，在C2单元格上单击，使其变为活动单元格，在单元格内输入第一位员工的姓名“赵奇”，按Enter键。完成第一个姓名的输入后，活动单元格将自动向下跳转到C3单元格，在C3~C15单元格内依次输入所有员工的姓名。
(2) 在D2单元格内输入员工“赵奇”的出生年月信息“1970/2”，按Enter键确认后单元格将显示该日期为“1970/2/1”，更改日期显示方式为“1970年2月”(参考5.1.4节格式化工作表的内容)。在D2~D15单元格内依次输入每一位员工的出生年月。
(3) 依次在F2~F15单元格内输入所有员工的工龄信息。员工信息输入结果如图510所示。


图510员工信息输入结果


3. 自动填充数据
当工作表某个区域中的数据之间存在某种变化规律时，可使用自动填充的方法，提高数据的输入效率。下面在员工信息表中的序号列输入等差序列作为序号值。具体操作如下。
(1) 打开“员工基本信息”工作表，选中A2单元格，使其变为活动单元格，在单元格内输入“1”，在A3单元格内输入“2”。


图511自动填充序列结果

(2) 选中单元格区域A2:A3，将鼠标指针移至选中区域的右下角黑色小方块处(此处称为“填充柄”)，当鼠标指针显示为黑色加号时，按住鼠标左键向下拖动至A15单元格，将自动在单元格区域A2:A15内填充等差序列编号“1,2,3,…,14”，自动填充序列结果如图511所示。
除使用填充柄进行自动填充外，还可以使用填充命令在连续单元格中批量输入定义为序列的数据内容。单击“开始”选项卡“编辑”组中的“填充”按钮，在下拉列表中选择“序列”命令，在弹出的“序列”对话框中选择填充的方向为“列”，根据需要设置填充的类型，输入步长值和终止值。序列填充设置如图512所示。


图512序列填充设置

(3) Excel会根据活动单元格中的内容自动选择要填充的序列，包括： 
数字序列： 如1,2,3,…或者2,4,6,…，系统将自动根据选中的前两个单元格的差值确定等差序列的步长，如果只选中了一个单元格，等差步长将默认为1。
 日期序列： 如2016年1月、2016年2月……或者1日、2日、3日等。
 文本序列： 如一、二、三、……或者星期一、星期二、星期三、……、星期日等。
(4) 除使用系统内置的序列填充数据外，如果要填充数据之间的规律较复杂，也可以创建自定义的填充序列。在功能区选择“文件”→“选项”命令，弹出“Excel选项”对话框。在此对话框中单击左窗格中的“高级”选项卡，然后单击右侧窗格“常规”区域下方的“编辑自定义列表”按钮，弹出“自定义序列”对话框，如图513所示。


图513新建自定义序列


(5) 对话框的“自定义序列”列表中显示了包括Excel内置序列在内的现有序列，单击“自定义序列”列表中的“新序列”选项，在右侧的“输入序列”列表框中输入新建的序列，如“第一层，第二层，第三层，……，第六层”，每一项以Enter键分隔。输入完后，单击“添加”按钮，序列将被添加至“自定义序列”列表中，单击“确定”按钮完成序列的添加。
(6) 接下来就可以使用上面创建的序列在工作表中输入序列数据。在工作表中选中准备输入序列的起始单元格，在此单元格中输入“第一层”，拖动该单元格的填充柄，就可以实现自定义序列数据的自动填充(“第二层”“第三层”……)。
4. 批量输入相同数据
在Excel中还可以批量输入相同的数据。如在员工信息表的性别列中输入性别值。具体操作如下。


(1) 打开“员工基本信息”工作表，按住Ctrl键选中要输入“男”的单元格，然后在编辑栏中输入“男”，再按Ctrl+Enter组合键，这些选中的单元格就全部填充了相同的内容，操作结果如图514(a)所示。


图514批量输入相同的数据示例操作结果


(2) 按照相同的方法可在其他单元格内输入“女”。也可以利用“定位”功能来完成此操作。具体操作如下。鼠标移至E列上方，鼠标指针变为向下的黑色箭头时单击选中“性别”列，然后单击“开始”选项卡“编辑”组中的“查找和选择”按钮，在下拉列表中选择“定位条件”命令，弹出“定位条件”对话框，如图515所示，选择“空值”单选按钮，确定后就选中了所有剩余的单元格，再批量输入“女”即可，操作结果如图514(b)所示。


图515“定位条件”对话框

5. 数据验证
在向工作表输入数据的过程中，为了避免出现过多的错误与非法信息输入，可以使用数据验证功能，为单元格指定数据录入的规则，限制在单元格中输入数据的类型和范围。以员工信息表的数据输入为例，利用数据验证功能实现下述要求。
  将员工的工龄信息的输入范围限制在整数0~42范围内，无法输入超出此范围的其他值。
  将员工的部门信息的输入限制为指定序列“管理部，市场部，综合部，研发部”中的值，并通过下拉列表控制，无法输入除此之外的其他值。
具体操作如下。
(1) 对工龄信息的输入进行控制。选中“工龄”列所在的单元格区域F2:F15，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，弹出“数据验证”对话框。在“设置”选项卡的“允许”下拉列表框中选择“整数”选项，在“数据”下拉列表框中选择“介于”选项，在“最小值”和“最大值”输入框中分别输入允许输入的最小值0和最大值42。

(2) 在“出错警告”选项卡的“样式”下拉列表框中选择“警告”选项，在右侧的“标题”文本框中输入“工龄输入提示”，在“错误信息”列表框中输入“请输入0~42范围内的整数”，“数据验证”对话框设置结果如图516所示。单击“确定”按钮，完成数据验证。此时如果在单元格区域F2:F15内的任意一个单元格中输入除整数0~42之外的值，将会提示错误信息，如图517所示。


图516“数据验证”对话框设置结果




图517提示错误信息1


(3) 对员工部门信息的输入进行控制。选中“部门”列所在的单元格区域H2:H15，单击“数据”选项卡“数据工具”组中的“数据验证”按钮，弹出“数据验证”对话框。在“设置”选项卡的“允许”下拉列表框中选择“序列”选项，在“来源”文本框中输入指定序列值“管理部，市场部，综合部，研发部”，每个值之间用西文逗号分隔，“数据验证”对话框设置结果如图518所示。


图518“数据验证”对话框设置结果


(4) 在“出错警告”选项卡的“样式”下拉列表框中选择“警告”选项，在右侧的“标题”文本框中输入“部门输入提示”，在“错误信息”列表框中输入“请输入正确的部门信息”，单击“确定”按钮，完成数据验证。此时如果选中单元格区域H2:H15内的任意一个单元格，其右侧会出现一个下拉箭头，单击该箭头将出现序列“管理部，市场部，综合部，研发部”列表，在列表中选择一个值将自动填入活动单元格中，如果在单元格内手动输入了列表范围外的值，将会提示错误信息，如图519所示。


图519提示错误信息2


(5) 若需要取消对上述单元格区域的数据验证，可以再次选中单元格区域F2:F15和H2:H15，在“数据验证”对话框中单击左下角的“全部清除”按钮即可。
6. 导入外部数据
除了使用上述方法向工作表中录入数据以外，Excel还支持从外部数据源获取数据并导入工作表中，如文本文件、网页、Access数据库等。
【例52】“中国银行.csv”文件如图520所示，该文件是使用第3章所介绍的爬虫工具采集到的中国银行股票数据信息，需要将该文件导入Excel中。


图520“中国银行.csv”文件


具体操作如下。
(1) 新建一个Excel工作簿并打开，选中A1单元格。单击“数据”选项卡“获取外部数据”组中的“自文本”按钮，弹出“导入文本文件”对话框，在对话框中选择要导入的文本文件，这里选择“中国银行.csv”文件进行导入，如图521所示。


图521选择“中国银行.csv”文件进行导入


(2) 单击“导入”按钮，弹出“文本导入向导第1步，共3步”对话框，如图522所示。系统会自动判断数据中是否具有分隔符，单击“下一步”按钮。



图522“文本导入向导第1步，共3步”对话框


(3) 在打开的“文本导入向导第2步，共3步”对话框中设置分隔数据所包含的分隔符号，这里勾选“逗号”复选框，如图523所示。


图523勾选“逗号”复选框


(4) 单击“下一步”按钮，弹出“文本导入向导第3步，共3步”对话框。在此步骤中，可以取消对某列的导入，还可以设置每个导入列的数据格式。这里保持默认设置，列数据格式设置结果如图524所示。


图524列数据格式设置结果


(5) 单击“完成”按钮，在弹出的“导入数据”对话框中输入导入的开始位置，单击“确定”按钮完成导入，导入结果如图525所示。最后，将导入的文件另存为名为“中国银行.xlsx”的工作簿文档。


图525导入结果


数据导入工作表中后，将自动与外部数据源关联，这里如果“中国银行.csv”中的数据发生改变，单击“数据”选项卡“连接”组中的“全部刷新”按钮，即可更新工作表中的数据。如果要导入来自其他数据源的数据，可以单击“数据”选项卡“获取外部数据”组中的其他按钮，通过相应的对话框操作来实现。
7. 单元格数据的编辑和修改
在制作电子表格的过程中，如有需要可以对单元格内已输入的数据进行编辑和修改，方法为双击要进行编辑的单元格进入编辑状态，直接在单元格内进行修改，或者单击要修改的单元格，然后在编辑栏中进行修改。
如果要删除单元格或者单元格区域中的内容，可以选中该单元格或者区域，按Delete键完成删除，或者单击“开始”选项卡“编辑”组中的“清除”按钮，在打开的下拉列表中选择要清除的对象即可。
5.1.4格式化工作表

使用5.1.3节介绍的多种数据输入和编辑方法，可以完成基本表格数据的输入，得到基本表格数据的输入结果图526所示。显然这只是电子表格制作过程中的一个初步的结果，其样式、外观和布局并不工整。在Excel中，可以使用不同的方法对工作表进行格式化处理，让表格的外观更加整洁美观，增加数据的易读性。


图526基本表格数据的输入结果


1. 设置字体格式
选中需要进行字体设置的单元格，在“开始”选项卡“字体”组中通过相应按钮完成字体和颜色的设置，也可以单击“字体”组右下角的对话框启动器按钮，弹出“设置单元格格式”对话框，在“字体”选项卡下对字体格式进行设置，如图527所示。这里对工作表中的字体做出如下设置。
（1）  列名的字体设为“黑体”，字号为12，颜色为“自动”。
（2）  其他区域的字体设为“楷体”，字号为11，颜色为“深蓝”。


图527在“字体”选项卡下对字体格式进行设置


2. 设置数字格式
数字格式是各个单元格中数据的外观形式，设置数字格式只是更改单元格中数值的显示形式，并不影响其实际值。Excel中包含如下类型的数字格式。
常规： 不包含任何特定的数字格式，它是Excel中默认的数字格式。
数值： 用于表示一般数字，可以指定小数位数、是否使用千位分隔符以及负数的显示方式。
货币： 表示货币的数值，可以指定货币符号、小数位数以及负数的显示方式。
会计专用： 对一列数值进行货币符号或者小数点对齐显示。
日期： 按照不同的方式显示日期值。
时间： 按照不同的方式显示时间值。
百分比： 将单元格内的数值乘以100并用百分数形式显示，可以指定小数位数。
分数： 按照不同的类型以分数形式显示数字。
科学记数： 使用指数形式显示数字。
文本： 将单元格内容视为文本，数字也作为文本处理，其显示内容与输入完全一致。
特殊： 包括邮政编码、中文小写数字和中文大写数字。
自定义： 在现有数字格式基础上进行自定义设置。
这里对工作表中列的数字格式进行设置。具体操作如下。
(1) 选择“出生年月”列对应的单元格区域D2:D15，在“开始”选项卡“数字”组中单击右下角的对话框启动器按钮，打开“设置单元格格式”对话框的“数字”选项卡，在“分类”列表框中选择要设置的数字格式，这里选择“日期”，在右侧的“类型”列表框中选择日期显示的类型，这里选择英文日期的年月显示类型“Mar12”，单击“确定”按钮。此时工作表中“出生年月”列中的日期将以英文日期形式显示，单元格日期数字格式设置及结果如图528所示。


图528单元格日期数字格式设置及结果


(2) 选择“工龄”列对应的单元格区域F2:F15，打开“设置单元格格式”对话框的“数字”选项卡，按照相同的方式将数字格式设置为“数值”，并设置“小数位数”为0，单击“确定”按钮完成设置，对于工作表中的其他区域，直接单击“开始”选项卡“数字”组中的下拉箭头，在下拉列表框中设置数字格式为“文本”。
3. 设置对齐方式
对齐是指单元格内容相对于单元格上、下、左、右的位置，分为水平对齐和垂直对齐。水平对齐方式有常规、靠左、居中、靠右、填充、两端对齐、跨列居中等； 垂直对齐方式有靠上、居中、靠下、两端对齐等。
通过“开始”选项卡“对齐方式”组中的按钮可以设置单元格的对齐方式。也可以单击“对齐方式”组右下角的对话框启动器按钮，打开“设置单元格格式”对话框的“对齐”选项卡进行设置，如图529所示。这里选择“姓名”列对应的单元格区域C2:C15，在“设置单元格格式”对话框中“水平对齐”下拉列表中选择“分散对齐(缩进)”选项，在“垂直对齐”下拉列表中选择“居中”选项，单击“确定”按钮，让姓名的内容居中分散填满整个单元格，使其更加美观。对于其他单元格，设置其水平和垂直对齐方式都为“居中”。


图529“设置单元格格式”对话框的“对齐”选项卡


4. 设置行、列格式
默认情况下，Excel工作表中所有行的行高和所有列的列宽都是相等的。当在单元格中输入较多数据时，经常会出现内容显示不完整的情况(只有在编辑栏中才能看到完整数据，日期被显示为######)，此时就需要适当调整单元格的行高和列宽。
下面对员工信息表的行高和列宽进行设置。这里首先选中列标题所在的单元格区域A1:I1，单击“开始”选项卡“单元格”组中的“格式”按钮，在下拉列表中选择“行高”命令，弹出“行高”对话框，将行高设置为“20”，单击“确定”按钮，单元格行高设置如图530所示。接下来选择工作表的其他区域，以相同的方式打开单元格格式下拉列表，选择“自动调整行高”命令。最后，选择包括列标题在内的所有单元格区域A1:I15，选择“自动调整列宽”命令。这样Excel将自动根据单元格中的内容进行行高、列宽设置。


图530单元格行高设置


5. 设置边框和填充色
默认情况下，工作表中的网格线只用于显示而不会被打印，且所有的单元格都没有填充颜色，为了让表格更加美观，并突出显示重要的内容，可以设置工作表的边框和填充颜色。具体操作如下。
(1) 打开工作表，选中所有数据范围单元格区域A1:I15，单击“开始”选项卡“字体”组中的“边框”按钮，在下拉列表中选择“其他边框”命令，打开“设置单元格格式”对话框的“边框”选项卡，如图531所示。首先在选项卡中对选中范围的外边框进行设置，在“线条”的“样式”列表框中选中“双线”，在“线条”的“颜色”下拉列表框中选择“深红色”选项，单击“外边框”按钮确定设置的内容。接下来设置内边框的线条样式为“细实线”，线条颜色为“自动”，单击“内部”按钮确定设置的内容。最后单击“确定”按钮完成边框设置。


图531“设置单元格格式”对话框的“边框”选项卡

(2) 对列标题所在的单元格区域填充颜色。选择单元格区域A1:I1，单击“开始”选项卡“单元格”组中的“格式”按钮，在下拉列表中选择“设置单元格格式”命令，打开“设置单元格格式”对话框并选择“填充”选项卡，在右侧的“图案样式”下拉列表框中选择样式“6.25% 灰色”，在左侧的“背景色”中选择“浅蓝色”，单击“确定”按钮，完成列标题单元格颜色底纹的填充，“填充”选项卡的设置如图532所示。


图532“填充”选项卡的设置


员工信息表格式化效果如图533所示。


图533员工信息表格式化效果


6. 套用表格格式快速格式化工作表
除了手动完成格式化操作外，Excel还提供了多种自动格式化设置的功能，如使用单元格样式、表格格式和主题实现包括字体、颜色、填充、对齐方式等一系列格式的自动设置，以节省手动格式化的时间。现为员工信息表套用一种表格格式。具体操作如下。
(1) 打开工作表，选中数据表中任意单元格(如A2单元格)，单击“开始”选项卡“样式”组中的“套用表格格式”按钮，在展开的下拉列表中，Excel 2016提供了60种表格格式。选择其中的“表样式中等深浅9”格式，弹出“套用表格式”对话框，保留默认的选项，单击“确定”按钮，数据表即可被创建为“表格”并应用对应的样式效果。
(2) 单击“表格工具设计”选项卡“工具”组中的“转换为区域”按钮，如图534所示。弹出“是否将表格转换为普通区域”对话框，在弹出的对话框中单击“是”按钮，即可将表格转换为普通数据表，如图535所示。


图534单击“转换为区域”按钮





图535将表格转换为普通数据表


7. 条件格式设置
条件格式功能可以为满足某些条件的单元格或者单元格区域设置某种格式，以方便用户直观地查看表中符合条件的单元格数据。例如可以突出显示用户所关注的单元格、强调异常值，还可以使用数据条、颜色刻度和图标集等来直观地显示数据。以图533中的工作表为例，利用条件格式进行如下设置。
  在“出生年月”列中，使用突出单元格显示规则设置所有1980年以前出生的单元格填充色和字体。
  在“工龄”列中，使用数据条填充规则描述所有员工工龄的长短。
  在“岗位类型”列中，使用自定义条件格式根据不同的岗位类型设置不同的格式。
(1) 打开工作表，选择“出生年月”列对应的单元格区域D2:D15，单击“开始”选项卡“样式”组中的“条件格式”按钮，在下拉列表中选择“突出显示单元格规则”→“小于”命令，弹出“小于”对话框，如图536所示。在左侧的输入框中输入“Jan80”，即对出生年月小于1980年1月的单元格格式进行设置，在“设置为”下拉列表框中选择应设置的格式，这里选择“浅红填充色深红色文本”，单击“确定”按钮，这样单元格区域D2:D15中所有出生年月在1980年以前的单元格数据都将被设置为“浅红”色填充的“深红”颜色的字体。


图536弹出“小于”对话框



(2) 选择“工龄”列对应的单元格区域F2:F15，单击“开始”选项卡“样式”组中的“条件格式”按钮，在下拉列表中选择“数据条”→“实心填充”→“蓝色数据条”命令，数据条填充格式设置如图537所示。这样单元格区域F2:F15将按照工龄的大小填充蓝色数据条，数据条的长度反映了单元格中值的大小，数据条越长代表工龄越长，数据条越短则代表工龄越短。
(3) 选择“岗位类型”列对应的单元格区域G2:G15，单击“开始”选项卡“样式”组中的“条件格式”按钮，在下拉列表中选择“新建规则”命令，弹出“新建格式规则”对话框，如图538所示。在“选择规则类型”列表框中选择“只为包含以下内容的单元格设置格式”选项，在“编辑规则说明”中选择下拉列表中的内容，设置单元格值等于“A类”时满足条件，单击下方的“格式”按钮，弹出“设置单元格格式”对话框，在对话框中设置当单元格内容为“A类”时的显示格式，这里在“字体”选项卡中设置字体为“加粗”，颜色为“红色”，单击“确定”按钮回到“新建格式规则”对话框，再次单击“确定”按钮，完成格式规则的设定。按照同样的方法新建两条规则，分别设置单元格值等于“B类”(“加粗蓝色”)和“C类”(“加粗绿色”)时的格式，单击“确定”按钮即可完成自定义条件格式的应用。




图537数据条填充格式设置




图538新建格式规则设置



(4) 条件格式全部应用完成后，工作表中的特定单元格格式将发生变化，条件格式应用结果如图539所示。


图539条件格式应用结果


8. 格式的复制和删除
1) 格式的复制
在功能区单击“开始”选项卡“剪切板”组中的“格式刷”按钮，可以将相同的格式复制到其他单元格区域中。单击“格式刷”按钮可以复制一次格式，双击“格式刷”按钮可以将复制的格式多次应用到新的单元格中。
2) 删除单元格的格式
若需要一次性删除单元格的所有格式，有以下两种方法。
(1) 方法一。
先选中某一单元格区域，然后单击“开始”选项卡“编辑”组中的“清除”按钮，在下拉列表中选择“清除格式”命令，所选单元格区域将恢复到Excel默认格式效果。
(2) 方法二。
选中一个未编辑过的空白单元格，单击“格式刷”按钮，然后拖动鼠标去选中要删除格式的单元格区域。
使用本节介绍的格式化工作表方法，可以对导入到工作簿的中国银行股票数据进行格式设置，如字体字号，边框底纹等等，使其更加整洁美观，得到中国银行股票数据表格式化结果如图540所示(详见素材工作簿文件“中国银行(格式化结果).xlsx”)。读者可自行尝试完成相关的格式化操作，以达到类似的效果。


图540中国银行股票数据表格式化结果

5.2Excel公式与函数
Excel最突出的特点就是可以使用公式进行数据处理。公式可以由运算符、常量、单元格引用以及函数组成。函数是一些预定义的特殊算式，它们可以与各类运算符一起构成各种公式以满足数据处理的需要。本节将对Excel公式及函数的使用方法进行介绍。
5.2.1Excel公式的基本使用
公式本质上就是一组以等号开始的表达式，由运算符、常量、单元格引用以及函数组成。

1. 公式中的运算符
运算符是构成公式的基本元素之一，每个运算符分别代表一种元素方式。Excel中的运算符包括以下4种类型。
（1） 算术运算符。主要用于加、减、乘、除、百分比及乘幂等各种常规的算术运算。
（2） 比较运算符。主要用于比较数据的大小，包括对文本或数值的比较，并产生逻辑值TRUE（真）或FALSE（假）。
（3） 文本运算符。主要用于将一个或多个文本数据进行连接与合并起来，连接运算的结果类型仍然为文本类型。
（4） 引用运算符。主要用于产生单元格引用。
公式中的运算符的作用说明如表51所示。


表51公式中的运算符的作用说明



运 算 符说明操 作 示 例

-算术运算符： 负号=-5*6： 运算结果为-30
%算术运算符： 百分比=80*5%： 运算结果为4
^算术运算符： 乘幂=4^2： 运算结果为16
*和/算术运算符： 乘和除=4*3/2： 运算结果为6
+和-算术运算符： 加和减=3+2-4： 运算结果为1
=、<>、>、<、>=、<=比较运算符： 等于、不等于、大于、小于、大于或等于、小于或等于=A1=B1:判断A1和B1是否相等

=A1<>"ABC"： 判断A1是否不等于"ABC"

=B1>=6： 判断B1是否大于或等于6
&文本运算符： 连接文本="Hello"&"Excel"： 两个字符串连接得到"HelloExcel"
： （冒号）引用运算符的一种=SUM(A1:B8)： 表示引用以冒号两边的单元格为左上角和右下角的矩形单元格区域
 （空格）引用运算符的一种=SUM(A1:B5 A3:C7)： 引用A1:B5与A3:C7的重叠的单元格区域
，（逗号）引用运算符的一种=SUM(A1:B5，A3:C7)： 在公式中对不同参数进行间隔

当公式中使用多个运算符时，Excel将根据各个运算符的优先级顺序进行运算，对于同级运算符，则按从左到右的顺序运算，如表52所示。
2. 公式中的常量
常量就是公式中输入的数值和文本。例如，公式“=3+5”中的3、5都是常量，是数值常量； 再如公式“=A2&"武汉"”中的“武汉”也是常量，称为文本常量。


表52不同运算符的优先级



顺序运算符说明

1：，（空格），，引用运算符： 冒号、空格和逗号
2-算术运算符： 负号（取得与原值正负号相反的值）
3%算术运算符： 百分比
4^算术运算符： 乘幂
5*和/算术运算符： 乘和除
6+和-算术运算符： 加和减
7&文本运算符： 连接文本
8=，<，>，<=，>=，<>比较运算符： 比较两个值

3. 公式的输入、编辑与复制


图541某公司员工的工资信息表

【例53】某公司员工的工资信息表如图541所示。为了计算每个员工当月应扣除的公积金，可以使用公式对工作表中的数据进行处理和运算。
具体操作如下。
(1) 在“公积金”下方的第一个单元格上单击，使其成为活动单元格。此处单击选中D2单元格。
(2) 输入等号“=”，表示正在输入公式而非文本数据。按照政策，员工每月需要缴纳的公积金数额为基础工资的12%，因此可在等号后面输入“C2*12%”。其中C2为单元格引用，代表的是第C列(基础工资)第2行的值(4680.00)，该引用可以通过直接输入单元格地址或者单击对应的单元格生成。
(3) 公式输入完成后可按Enter键，计算结果将显示在公式所在的单元格中。
(4) 输入到单元格中的公式可以像普通数据一样，通过拖动单元格右下角的填充柄或者使用填充命令进行复制填充，此时填充和复制的是公式本身，公式计算结果会由于公式中引用的变化而发生改变。公式的输入、编辑与复制及相对引用的使用如图542所示。


图542公式的输入、编辑与复制及相对引用的使用


4. 单元格引用
单元格引用是Excel中最常用的运算对象。单元格引用表示的是一个或者多个单元格的地址，可通过该地址对相应单元格中已存储的数据进行运算。常用的单元格引用类型包括： 
(1) 相对引用。又称相对单元格引用，即直接给出列标与行号的引用方法，如A1、C5等。相对引用中的单元格地址不是固定地址，而是基于包含公式的单元格与被引用的单元格之间的相对位置的相对地址。在默认情况下，公式中对单元格的引用都是相对引用，如果复制公式，相对引用将自动调整。以图542中D2单元格中的公式“=C2*12%”为例，其中“C2”就是相对引用，表示在D2中引用位于同一行第3列单元格的值。当向下复制公式到单元格D3时，那么与D3位于同一行，第3列的单元格就变成了C3，因此D3中的公式将自动变为“=C3*12%”，如图542所示。同理，公式被复制到D5时即将变为“=C5*12%”。相对引用的好处是当公式位置发生变化时，公式中引用的单元格会自动发生变化，以便通过复制公式的方式快速完成批量计算。
(2) 绝对引用。与相对引用不同，绝对引用不会随着公式单元格位置的变化而变化。单元格的绝对引用是指在单元格标识符的行号和列标前都加上“$”符号，表示将行列冻结，如“$C$2”。绝对引用中被冻结的行列号不会随着公式的复制而发生改变，因此如果图542中D2单元格中的公式变为“=$C$2*12%”，那么当公式被向下复制到单元格D3时，公式的内容将仍为“=$C$2*12%”，因此计算得到的结果也不会发生改变。公式中的绝对引用如图543所示。


图543公式中的绝对引用


(3) 混合引用。混合引用是指单元格引用行或列中的某一项被冻结的情况。当需要将引用中的行冻结而允许列变化时，可以在行号前加“$”符号，如“C$2”。当需要将应用中的列冻结而允许行变化时，则可以在列标前加“$”符号，如“$C2”。
(4) 区域引用。在引用单元格区域时，可能用到引用运算符“：”“，”“ ”(空格)。
  用“：”运算符来引用单元格区域。例如E2:H8，表示以E2单元格为左上角顶点，以H8单元格为右下角顶点围成的矩形区域。
  用“，”运算符来引用若干个不连续的单元格区域。例如“A2:D5,G6”表示单元格区域A2:D5与G6单元格共同组成的区域。
  用“ ”(空格)运算符来引用前后两个单元格区域相交的单元格区域。例如“A1:C3［空格］B2:D4”，表示引用单元格区域A1:C3和B2:D4之间相交的部分，即单元格区域B2:C3。
(5) 不同工作表中单元格的引用。可以引用同一工作簿中不同工作表的单元格，其引用格式为： 工作表名!［$］列标［$］行号。例如，在工作表Sheet1的A1单元格中计算工作表Sheet2的A1单元格与A2单元格之和的操作为： 选定Sheet1工作表的A1单元格，输入公式“=Sheet2!A1+Sheet2!A2”后按Enter键。更简单的操作方法是在输入公式的“=”后，单击Sheet2的工作表标签，切换到Sheet2工作表，单击A1单元格，输入“+”后再单击A2单元格，最后按Enter键。
(6) 不同工作簿中单元格的引用。还可以引用不同工作簿中的单元格，其引用格式为： ［工作簿文件名］工作表名!［$］列标［$］行号。例如，在当前工作表A1单元格统计工作簿“招生统计.xlsx”中的A1单元格和A2单元格数值和的操作为： 选定当前工作表中的A1单元格，输入“=”后单击任务栏上“招生统计”工作簿任务按钮，单击A1单元格后输入“+”，再单击“招生统计”工作簿中的A2单元格，最后按Enter键。此时，在当前工作表中A1单元格的公式为“［招生统计.xlsx］Sheet1!$A$1+［招生统计.xlsx］Sheet1!$A$2”。注意，引用不同工作簿中的单元格时，默认是绝对引用。
5. 名称的定义与引用
一种常见的绝对引用使用方法是为单元格或者单元格区域指定一个名称，并在公式中使用这些名称表示绝对引用区域。在定义名称的过程中，应注意需要遵循以下语法原则： 
  新的名称不能与现有的名称重复(不区分大小写)，且不能与单元格的地址相同，如A1，$E6等。
  名称中不能使用空格，第一个字符只能是字母(或中文汉字)、下画线或反斜杠。
  一个名称的长度不能超过255个西文字符。
【例54】名称定义示例工作表如图544所示，使用不同的方法分别对工作表中的不同区域定义名称。
具体操作如下。
(1) 使用名称框定义名称。在定义名称前，首先选择要命名的单元格或者区域，这里选择图544工作表中的单元格区域A2:A7，在“名称框”中输入“月份”后按Enter键确认，即可将该单元格区域定义名称为“月份”。
(2) 根据所选内容批量创建名称。如要将工作表中的每一列以列标题来定义名称，可以选择要命名的数据区域，这里选择A1:E7单元格区域。单击“公式”选项卡“定义的名称”组中的“根据所选内容创建”按钮，弹出“根据所选内容创建名称”对话框，如图545所示。勾选“首行”复选框，最后单击“确定”按钮，即可分别创建以列标题“北京”“上海”等命名的4个名称。



图544名称定义示例工作表



图545弹出“根据所选内容创建名称”对话框



(3) 使用“新建名称”命令定义名称。单击“公式”选项卡“定义的名称”组中的“定义名称”按钮，弹出“新建名称”对话框，如图546所示。在输入框中输入想要定义的名称和备注信息，并指定名称所引用的范围。这里在“名称”文本框中输入“销量”，在“范围”下拉列表框中选择“工作簿”，在“批注”列表框中输入“一月份各地销量”，在“引用位置”文本框中选择工作表中第2行对应的单元格区域$B$2:$E$2，最后单击“确定”按钮完成名称定义。


图546弹出“新建名称”对话框

(4) 名称定义完成后，可以在公式中直接手工输入已定义的名称(使用鼠标选择已定义名称的区域作为需要插入公式中的单元格引用时，Excel会自动应用该单元格区域的名称)。此外在编辑公式的过程中，还可以单击“公式”选项卡“定义的名称”组中的“用于公式”按钮，打开名称下拉列表并单击所要使用的名称，将该名称添加进公式中。在公式中引用名称如图547所示。


图547在公式中引用名称


(5) 若要查看和管理已经定义的名称，则可单击“公式”选项卡“定义的名称”组中的“名称管理器”按钮，打开“名称管理器”对话框。在该对话框中可以查看所有已经定义的名称及其所引用的范围，并对它们进行编辑、修改和删除操作。
5.2.2Excel函数的基本使用
函数是公式中的运算对象，也是Excel中较为复杂的内容，Excel系统的真正功能可以说是由它的函数功能来体现的，合理地运用函数可以完成非常复杂的计算任务，数据的分析和处理一般都是使用函数来完成的。
1. 函数与函数分类
函数也可以理解为是一些预定义的公式，Excel提供了大量的函数，如求和函数SUM、求平均值函数AVERAGE、求最大值函数MAX等。这些函数按照功能的不同被分为12大类，函数分类及典型示例如表53所示。


表53函数分类及典型示例




类别典型函数示例
财务NPV,投资净现值计算函数; RATE,实际利率计算函数
逻辑IF,条件判断函数; AND,与逻辑判断函数; OR,或逻辑判断函数
文本LEFT/RIGHT,从左/右侧截取字符串函数; TRIM,删除字符串首尾空格函数; LEN,返回字符串长度函数
日期和时间DATE,获取日期函数; NOW,获取当前日期和时间函数
查找和引用VLOOKUP,垂直查询函数; HLOOKUP,水平查询函数
数学和三角函数SUM,求和函数; LOG,对数函数; SIN,正弦函数; COS,余弦函数; INT,取整函数; ROUND,四舍五入函数
统计AVERAGE,平均值函数; COUNT,计数函数; MAX/MIN,最大值/最小值函数; RANK.EQ,排名函数
工程CONVERT,度量系统转换函数
多维数据集CUBEVALUE,多维数据集汇总返回函数

续表




类别典型函数示例
信息函数TYPE,数据类型查看函数; ISBLANK,空值判断函数
兼容性RANK,排名函数(早期版本)
网络函数ENCODEURL函数，WEBSERVICE函数，FILTERXML函数

2. 函数的输入与编辑
在公式中输入函数时应遵循一些语法规则。
  函数通常表示为： 函数名(［参数1］,［参数2］,…)，括号中的参数可以有多个，中间用逗号分隔，其中方括号中的参数为可选参数，有的函数可以没有参数。
  函数名后面的括号必须成对出现，括号与函数名之间不能有空格。
  函数的参数可以是文本、数值、日期、时间、逻辑值或单元格的引用等，甚至可以是另一个或几个函数(函数的嵌套)。
在输入函数的过程中，我们可能无法准确地记忆每个函数的名称以及参数的组成，因此Excel提供了函数参考帮助我们输入函数。一般来说，可以通过以下两种方法输入函数： 
1) 手工输入
在编辑栏中采用手工输入函数，前提是用户必须熟悉函数名的拼写、函数参数的类型、次序以及含义。因此，对于初学者而言，更推荐使用下面的方法输入函数。
2) 使用函数向导输入
为方便用户输入函数，Excel提供了函数向导功能，具体操作为： 选择某一个单元格，进入公式编辑状态。单击“公式”选项卡“函数库”组中的“插入函数”按钮，或者单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框，如图548所示。



图548“插入函数”和“函数参数”对话框


以上两种方法均会弹出“插入函数”对话框。在“或选择类别”下拉列表框中选择所需要的函数类别，或者在“搜索函数”文本框中输入函数的简单描述后单击“转到”按钮，再单击“选择函数”列表框中所需要的函数名，单击“确定”按钮，即可弹出“函数参数”对话框，如图548所示。由于函数不同，函数的参数个数不同，类型也不同，因此“函数参数”对话框内容也有所不同。分别输入各个参数后，单击“确定”按钮即可。
除此以外，Excel将不同类别的函数封装成了不同的按钮，放置在“公式”选项卡的“函数库”组中。用户可以单击不同类别的函数按钮，在下拉列表中选择需要的函数，打开对应的函数对话框设置函数参数即可。
5.2.3常用函数的应用
1. 基础统计函数

Excel中提供了多种基础统计函数，可以完成诸多统计计算。常用的6个统计函数及其功能和语法如表54所示。


表54常用的统计函数及其功能和语法




函数说明语法
SUM将指定为参数的所有数字相加SUM(number1,［number2］,…)
AVERAGE返回参数的算术平均值AVERAGE(number1,［number2］,…)
MAX返回一组值中的最大值MAX(number1,［number2］,…)
MIN返回一组值中的最小值MIN(number1,［number2］,…)
COUNT计算参数列表中数字的个数COUNT(value1,［value2］,…)
COUNTA计算区域中不为空的单元格的个数COUNT(value1,［value2］,…)

其中，参数解释如下。
(1) number1、value1。必需参数，进行相应统计的第一个数字、单元格引用或区域。
(2) number2、value2、…。可选参数，进行相应统计的其他数字、单元格引用或区域。
【例55】某班级考试成绩表如图549所示，需要对此班级的考试成绩进行相应的统计。


图549某班级考试成绩表


具体操作如下。
(1) 选中G2单元格，单击编辑栏中的“插入函数”按钮，弹出“插入函数”对话框。在该对话框的“或选择类别”下拉列表框中选择“常用函数”类别，单击列表框中的SUM函数后单击“确定”按钮，打开SUM函数的“函数参数”对话框。
(2) 设置“函数参数”对话框中的参数，如图550所示。


图550设置“函数参数”对话框中的参数


(3) 单击“确定”按钮，关闭该对话框，此时G2单元格中的公式为“=SUM(D2:D11)”，计算出全班同学的考试总成绩，结果为711。
(4) 在G3单元格中输入公式“=AVERAGE(D2:D11)”，计算出全班的平均分，结果为79。
(5) 在G4单元格中输入公式“=MAX(D2:D11)”，计算出该班的最高分，结果为95。
(6) 在G5单元格中输入公式“=MIN(D2:D11)”，计算出该班的最低分，结果为55。
(7) 在G6单元格中输入公式“=COUNT(D2:D11)”，计算出本次参加考试的人数，结果为9。COUNT函数只统计数字的个数，所以D8单元格的“缺考”不统计在内。
(8) 在G7单元格中输入公式“=COUNTA(D2:D11)”，计算出该班的总人数，结果为10。COUNTA函数统计不为空的单元格的个数，所以数字和文本全部统计在内。基础统计函数应用结果如图551所示。


图551基础统计函数应用结果






视频讲解



2. 条件统计函数
条件统计函数包括单条件统计函数SUMIF、AVERAGEIF和COUNTIF，以及多条件统计函数SUMIFS、AVERAGEIFS和COUNTIFS。
1) SUMIF函数
SUMIF函数可以对指定单元格区域中符合指定条件的值求和。
SUMIF函数的表达式为SUMIF(range, criteria, ［sum_range］)，包含2个必需参数和1个可选参数。
（1） range。必需参数，用于求和条件计算的单元格区域。
（2） criteria。必需参数，表示条件计算的内容，即求和的条件。可以为数字、表达式、单元格引用和函数等。如“>3000”则表示当range中的值满足大于3000的条件时参与求和运算。在书写的过程中，如果条件中含有文本或者逻辑、数学符号，则必须使用英文半角双引号(")括起来，如果条件仅为数字，则无须使用双引号。
（3）  sum_range。可选参数，表示实际要进行求和计算的单元格范围。如果被省略，则将对range指定的单元格范围中的值进行求和。
2) SUMIFS函数
SUMIFS与SUMIF函数类似，可以对指定单元格区域中满足多个条件的单元格求和。
SUMIFS函数的表达式为SUMIFS(sum_range, criteria_range1, criteria1, ［criteria_range2，criteria2］,…)，包含3个必需参数和多个可选参数。
（1） sum_range。必需参数，表示实际要进行求和计算的单元格范围。
（2）  criteria_range1。必需参数，表示第一个求和条件计算对应的单元格区域。
（3）  criteria1。必需参数，表示第一个求和条件计算的内容。可以为数字、表达式、单元格引用和函数等。
（4）  criteria_range2，criteria2，…。可选参数，表示附加的求和条件计算单元格区域和附加的求和条件。最多允许附加127个求和条件计算内容，每个criteria_range区域所包含的行列个数必须与sum_range区域相同，所有求和计算条件相互关联，必须同时满足这些条件才能进行求和计算。
3) AVERAGEIF函数
AVERAGEIF函数可以对指定区域满足给定条件的所有单元格中的数值进行平均值计算。
AVERAGEIF函数的表达式为AVERAGEIF(range, criteria, ［average_range］)，包含2个必需参数和1个可选参数，其参数的构成与设置方法和SUMIF函数一致，此处不再赘述。
4) AVERAGEIFS函数
AVERAGEIFS函数与AVERAGEIF函数类似，可以对指定单元格区域中满足多个条件的单元格进行平均值统计。
AVERAGEIFS函数的表达式为AVERAGEIFS(average_range, criteria_range1, criteria1, ［criteria_range2, criteria2］, …)，包含3个必需参数和若干个可选参数，其参数构成与设置方法同样可参照SUMIFS函数。
5) COUNTIF函数
COUNTIF函数可以统计指定区域内满足某个条件的单元格的个数。
COUNTIF函数的表达式为COUNTIF(range,criteria)，包含2个必需参数。
（1） range。必需参数，用来确定要计数的单元格区域。
（2）  criteria。必需参数，表示计数的条件，可以为数字、表达式、单元格引用等，如“2000”则表示记录range内等于2000的值的个数。
6) COUNTIFS函数
COUNTIFS函数可以统计指定区域内满足多个条件的单元格的个数。
COUNTIFS函数的表达式为COUNTIFS(criteria_range1, criteria1, ［criteria_range2, criteria2］, …)，包含2个必需参数和若干个可选参数。
（1） criteria_range1。必需参数，用来确定要计数的单元格区域。
（2）  criteria1。必需参数，表示计数的条件(同COUNTIF函数)。
（3）  criteria_range2, criteria2,…。可选参数，表示附加的计数条件计算单元格区域和附加的计数条件(其设置方法同样可参照SUMIFS函数)。
【例56】某食品加工厂某月部分职工生产信息数据表如图552所示，需要对该厂职工的生产情况进行相应的统计。


图552职工生产信息数据表


具体操作如下。
(1) 在H2单元格中输入公式“=SUMIF(D2:D11,"第2车间",E2:E11)”，计算出“第2车间”生产的总件数为456。
该公式将“生产部门”列对应的单元格区域D2:D11作为求和条件判断区域range参数，利用criteria参数将“第2车间”作为判断条件，最后sum_range参数为“生产件数”列对应的单元格区域E2:E11，即对所有生产部门为“第2车间”的产品生产件数相加求和，将结果显示在H2单元格中。
(2) 在H3单元格中输入公式“=AVERAGEIF (D2:D11,"第2车间",E2:E11)”，计算出“第2车间”生产的平均件数为76。
(3) 在H4单元格中输入公式“=SUMIFS(E2:E11,B2:B11,">35",C2:C11,"女")”，计算出35岁以上女职工生产的总件数为260。
公式中sum_range参数为“生产件数”列对应的单元格区域E2:E11，第一个条件计算区域为“年龄”列对应的单元格区域B2:B11，求和条件为“>35”，第二个条件计算区域为“性别”列对应的单元格区域C2:C11，求和条件为“女”，即对所有年龄大于35岁的女职工的产品生产件数进行求和，将结果显示在H4单元格中。
(4) 在H5单元格中输入公式“=AVERAGEIFS (E2:E11,B2:B11,">35",C2:C11,"女")”，计算出35岁以上女职工生产的平均件数为65。
(5) 在H6单元格中输入公式“=COUNTIF(B2:B11,">35")”，计算出35岁以上职工的总人数为7。
公式中的range参数为记录每位职工“年龄”的单元格区域B2:B11，criteria参数的值则表示对年龄大于35岁的所有记录进行计数，即统计35岁以上职工的总人数，并将结果显示在H6单元格中。
(6) 在H7单元格中输入公式“=COUNTIFS(B2:B11,">35",C2:C11,"女")”，计算出35岁以上女职工的总人数为4。
在多条件统计时，每一个单元格区域都需要有相同的行数和列数。
以上职工生产信息统计结果如图553所示。


图553职工生产信息统计结果






视频讲解



3. 垂直查询函数
垂直查询函数VLOOKUP可以搜索指定单元格区域的第一列，然后返回符合条件的值对应的相同行上任意单元格内的值。
VLOOKUP函数表达式为VLOOKUP(lookup_value, table_array, col_index_num, ［ range_lookup］)，包含3个必需参数和1个可选参数。
（1） lookup_value。必需参数，表示要搜索的值。
（2）  table_array。必需参数，表示被搜索的单元格区域，函数需要在该区域第一列中寻找lookup_value对应的值。
（3）  col_index_num。必需参数，表示返回数据在table_array中的列号。如果col_index_num为2，则返回table_array中符合lookup_value的那一行中第2列的值，若col_index_num为3，则返回第3列的值，以此类推。
（4）  range_lookup。可选参数。取值为逻辑值TRUE或者FALSE，表示函数搜索的是与lookup_value精确匹配的值还是近似匹配的值。如果range_lookup为TRUE或者为默认值，则表示近似匹配，如果找不到与lookup_value精确匹配的值，则返回小于lookup_value的最大值； 如果range_lookup为FALSE，则表示精确匹配，如果找不到与lookup_value精确匹配的值，则返回错误值#N/A。如果在table_array的第1列中找到多个与lookup_value匹配的值，则返回第一个匹配值所在行对应col_index_num列的值。
【例57】某银行某季度的股票信息数据表如图554所示，可利用VLOOKUP函数查询每个交易日的“收盘价”和“成交金额”。



图554股票信息数据表


首先将单元格区域A2:A59的值复制到单元格区域M2:M59中，再来查询每个交易日的“收盘价”和“成交金额”。

为了查询“收盘价”和“成交金额”，应将单元格区域A2:K59设置为table_array，在其第一列中搜索每个交易日的时间，并返回当天的“收盘价”和“成交金额”。以交易日“2019/3/29”的“收盘价”和“成交金额”计算为例，先在N2单元格中输入公式“= VLOOKUP ($M2,$A$2:$K$59,5,FALSE)”，计算结果为3.77，VLOOKUP函数参数设置如图555所示。再在O2单元格中输入公式“= VLOOKUP ($M2,$A$2:$K$59,9,FALSE)”，计算结果为67257。


图555VLOOKUP函数参数设置



VLOOPUP的参数中，lookup_value对应的M2单元格存放的是交易日“2019/3/29”对应的时间，因此在table_array的第1列中搜索该时间，并分别返回第5列(“收盘价”)和第9列(“成交金额”)中对应的值，此时将分别返回3.77和67257。需要注意的是，函数的lookup_value参数使用了混合引用($M2)，以使搜索的值固定在M列上； table_array参数则使用了绝对引用($A$2:$K$61)，这是为了在复制公式时使搜索的范围固定不变。
分别使用填充柄复制N2和O2单元格中的公式到N61和O61单元格，即可使用垂直查询函数计算每一个交易日的“收盘价”和“成交金额”，得到股票信息计算结果如图556所示。



图556股票信息计算结果


4. 逻辑判断函数
逻辑判断函数IF可以根据逻辑表达式的结果返回特定的值。
IF函数的表达式为IF(logical_test, value_if_true, value_if_false)，包含3个必需参数。
(1) logical_test。必需参数，表示作为判断条件的逻辑表达式，如B2>200就是一个逻辑表达式，如果B2单元格中的值大于200，则该表达式的结果为TRUE，否则为FLASE。
(2)   value_if_true。必需参数，表示当logical_test判断结果为TRUE时的函数返回值。
(3)   value_if_false。必需参数，表示当logical_test判断结果为FALSE时的函数返回值。
【例58】某仓库货物库存信息表如图557所示，可利用IF函数根据每种货物的实际库存数量与预订数量，计算出其库存提示信息，即当某种货物库存数量小于预订数量时，在提示信息中记录“库存不足”，否则记录“有库存”。
以编号为0123的货物库存提示信息获取为例，在D2单元格中输入公式“=IF(B2>=C2,"有库存","库存不足")”，计算结果为“库存不足”。
该公式首先比较货物0123的库存数量(B2单元格)与预订数量(C2单元格)。若满足条件“B2>=C2”(即库存量不小于订货量)，则表示库存足够，此时返回信息“有库存”，否则表示库存不足，此时返回信息“库存不足”。返回的值将显示在D2单元格内。使用填充柄复制D2单元格中的公式到D9单元格，即可使用相同的方法对每一种货物的库存提示信息进行判断，IF函数应用结果如图558所示。



图557某仓库货物库存信息表



图558IF函数应用结果






视频讲解




5. 排序函数
排序函数RANK.EQ可以返回一个数值在指定数值列表中的排名，如果列表中有多个数值取值相同，则返回该值的并列最佳排名。
RANK.EQ函数的表达式为RANK.EQ(number,ref,［order］)，包含2个必需参数和1个可选参数。
(1) number。必需参数，表示要确定排名的数值或单元格引用。
(2)  ref。必需参数，表示排名需要参考的数值列表区域，区域中的非数字值将被省略。
(3)  order。可选参数，表示排名的方式，如果order为0或者默认值，则表示进行降序排序，如果order不为0，则表示进行升序排序。
【例59】某公司的月现金明细账目表如图559所示，可利用RANK.EQ函数计算每项账目的收支金额在当月的排序，并填入单元格区域G5:G18中，以便快速地了解当月现金明细账单中出现重大收支变化的账目位于何处。


图559某公司的月现金明细账目表



以当月2日的收支金额在当月的排序计算为例，在G5单元格内输入公式“=RANK.EQ(E5+F5,$E$5:$F$18)”，计算结果为11。
公式将计算当月2日的账目收支金额之和(E5+F5)在当月所有收支账目金额(单元格区域)E5:F18中的排名，并将结果显示在单元格G5中。由于order参数为默认值，则排名规则为降序，即收支金额越大，排名越靠前。需要注意的是，函数的ref参数使用了绝对引用($E$5:$F$18)，这是为了在复制公式时使排名参考的范围固定不变。使用填充柄复制G5单元格中的公式到G18单元格，可以得到每一项账目的收支金额在当月所有账目中的排名，RANK.EQ函数应用结果如图560所示。



图560RANK.EQ函数应用结果


由图560可以看出，现金明细账单中出现最大收支变化的账目出现在当月21日与31日，收支金额均达到了200000元，账目摘要内容分别为“出售知识产权”与“存款”。
5.3Excel数据处理工具
Excel提供了强大的数据管理功能，通过丰富的数据处理工具，如排序、分类汇总、筛选及图表等对数据进行组织、整理、分析等操作，从而完成对原始表格数据的预处理，获取更加实用的信息，为后续数据分析工作提供支持。本节将对Excel中的数据分析和处理功能进行介绍。
5.3.1数据清单
Excel中所有的数据管理操作都是基于数据清单进行的，因此在进行数据分析和处理操作前，必须构建符合要求的数据清单。一个数据清单的实例如图561所示。


图561数据清单的实例


数据清单具有以下特点。
(1) 数据清单一般是一个矩形区域，区域内不能出现空白的行或列，也不能包括合并单元格。
(2) 数据清单的第一行应该是标题行，用于描述所对应列的内容。
(3) 每列必须包含同类的信息，且每列的数据类型相同。
(4) 数据清单中不能存在重复的标题。
(5) 同一个工作表内可以存放多个数据清单，它们之间用空白的行列进行分隔。
5.3.2数据排序
对数据进行排序有助于快速、直观地显示数据并更好地理解数据内容，有助于组织、查找所需数据并进行相应的分析和决策。利用Excel提供的排序功能，可以使用不同的方式对数据清单的内容进行排序。
1. 单列简单排序
使用单列简单排序工具可以快速地对数据清单中的内容按照某一列的信息进行排序。
【例510】图561中的数据清单记录了某公司当月的职工工资表，若要对数据清单按照实发工资由高到低进行排序，可以使用单列简单排序方法。
具体操作如下。
(1) 选中数据清单中位于“实发工资”列的某一单元格(如G5单元格)，Excel将自动识别该单元格所处的数据清单作为参与排序的区域，并将首行指定为标题行。
(2) 单击“数据”选项卡“排序和筛选”组中的“降序”按钮，或者单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，在下拉列表中选择“降序”命令。排序相关按钮与命令如图562所示。


图562排序相关按钮与命令


(3) Excel将对数据清单中的数据按实发工资由高到低进行排序，单列简单排序结果如图563所示。


图563单列简单排序结果

进行单列简单排序时，Excel将根据数据类型采用不同的排序规则。
① 对于数字，按照数值由小到大或者由大到小排序。
②  对于文本，按照字母或汉字拼音A~Z或者Z~A排序。
③  对于日期或者时间，按照时间从早到晚或从晚到早排序。
2. 多条件复合排序
在进行排序的过程中，可能需要同时考虑多个排序条件。例如在职工工资表中，可以同时根据员工类别和基础工资进行排序。即首先根据员工类别的取值进行排序，对于同一类别的员工，再比较基础工资的高低。此时，需要采用多条件复合排序方法。具体操作如下。
(1) 选择数据清单中的任意一个单元格(如D7单元格)，单击“数据”选项卡“排序和筛选”组中的“排序”按钮，或者单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，在下拉列表中选择“自定义排序”命令，弹出“排序”对话框，如图564所示。


图564“排序”对话框


(2) 在“主要关键字”下拉列表框中选择一个列标题作为多条件排序的第一个条件，在这里选择“类别”列(首先根据员工类别排序)，在“排序依据”下拉列表框中选择按“单元格值”排序，在“次序”下拉列表框中选择排序的顺序，这里由于要按照员工类别进行排序，无法直接依据数字大小或者字母顺序来判断，因此需要依据“自定义序列”的顺序进行排序。选择“自定义序列”选项，弹出“自定义序列”对话框，如图565所示。
(3) 在“自定义序列”对话框中，在“输入序列”下方的输入框中输入序列“正式工，合同工，临时工”，以Enter键分隔每一项，单击“添加”按钮新建自定义序列。在“自定义序列”下方的列表框中选择新建的序列，单击“确定”按钮完成设置。设置完成后，将根据数据清单“类别”列中的单元格取值，按照“正式工”在前、“合同工”在中、“临时工”在后的顺序进行排序。
(4) 对于同属一个类别的员工工资信息，可将“基础工资”作为多条件排序的第二个条件，按照“基础工资”由高到低排序。在“排序”对话框中单击“添加条件”按钮为条件列表增加一行，在“次要关键字”下拉列表框中选择“基础工资”列，分别选择“单元格值”和“降序”作为对应的排序依据和次序，“次要关键字”设置如图566所示。如有需要，可以通过单击“添加条件”按钮增添更多的排序条件，并设置对应的参数。
(5) 完成排序条件设置后，单击“确定”按钮，Excel将对数据清单中的内容先按照员工类别排序，如果类别相同，则再按照基础工资由高到低排序，得到复合多列排序结果如图567所示。



图565“自定义序列”对话框




图566“次要关键字”设置




图567复合多列排序结果

5.3.3数据筛选
数据筛选是查询出满足条件的数据。可以按数值或文本值筛选，或按单元格颜色筛选那些设置了背景色或文本颜色的单元格中的数据。通过数据筛选功能，可以快速地在数据清单中提取出感兴趣的信息予以显示，同时隐藏其他暂时无须关注的数据。
Excel中主要有两种方法可以实现数据的筛选： 自动筛选和高级筛选。
1. 自动筛选
使用自动筛选的方法是选中数据清单中的任意一个单元格，单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，或者单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，在下拉列表中选择“筛选”命令，此时数据清单标题行中的每个单元格右侧出现“筛选”按钮。单击所需筛选的字段旁的按钮，可以在下拉列表中选择相关筛选命令进行筛选。
1) 在值列表中筛选
【例511】某专业学生信息表如图568所示，可以筛选出某一班级、全体男生或加权成绩高于90分的学生信息。下面筛选出所有班级为“信管1801”的相关数据。


图568某专业学生信息表


具体操作如下。
(1) 单击数据清单中的任意一个单元格。
(2) 单击“数据”选项卡“排序和筛选”组中的“筛选”按钮，或者单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，在下拉列表中选择“筛选”命令，数据清单进入筛选状态。
(3) 单击“班级”单元格右侧的“筛选”按钮，在下拉列表中取消勾选“全选”复选框，再勾选“信管1801”复选框，如图569所示，单击“确定”按钮。
2) 根据数据筛选
很多情况下，不仅需要按照现有的值列表来筛选，还要根据列数据中的数据大小、内容等来筛选，如在上述学生信息表中，筛选出入学成绩在年级前8名的学生信息，可以按下面的步骤操作： 打开“入学成绩”列“自动筛选”按钮的下拉列表，选择“数字筛选”→“10个最大的值”命令，弹出“自动筛选前10个”对话框，如图570所示。设置“最大”为“8”，单击“确定”按钮。又如在此表中筛选出加权成绩大于90分的学生信息，则可以参照下面的步骤操作： 打开“加权成绩”列“自动筛选”按钮的下拉列表，选择“数字筛选”→“大于”命令，弹出“自定义自动筛选方式”对话框，如图571所示。设置“大于”值为“90”，单击“确定”按钮。



图569勾选“信管1801”复选框




图570“自动筛选前10个”对话框





图571“自定义自动筛选方式”对话框


3) 多条件筛选
若需要对多列数据同时进行筛选，则反复多次执行筛选步骤即可。需要注意的是，多个筛选条件之间为“并且”的关系，也就是说，所有筛选条件都满足的数据才会显示。例如，筛选出学生信息表中入学成绩位于前8位，且所属班级为信管1801班的学生信息，多条件筛选过程如图572所示。



图572多条件筛选过程


4) 取消筛选
（1） 若为某一列设置了自动筛选条件，则该列(如“班级”列)的筛选箭头将变为，单击“班级”列的筛选箭头，在下拉列表中选择“从‘班级’中清除筛选”命令，则可以清除为“班级”列设置的自动筛选条件。
（2） 若要清除所有列的筛选条件，可单击“数据”选项卡“排序和筛选”组中的“清除”按钮； 或者单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，在下拉列表中选择“清除”命令。
（3）  若要退出自动筛选状态，则可以单击“开始”选项卡“编辑”组中的“排序和筛选”按钮，在下拉列表中再次选择“清除”命令即可。
（4）  取消筛选会令所有筛选结果都取消，即所有因筛选而被隐藏的行将全部显示出来。
2. 高级筛选
使用自动筛选功能可以满足数据清单中大部分的筛选需要，但部分条件较为复杂的筛选需求，则需要通过“高级筛选”功能来完成。在学生信息表中，对于下述类型的筛选，必须使用“高级筛选”来实现。
  对不同列之间的筛选条件，建立“或”逻辑关系。如在数据清单中筛选加权成绩大于85分或者入学成绩大于580分的学生信息。
  对于同一列的数据，构建多重范围的筛选条件。如筛选年龄小于18岁或大于21岁的学生信息。
  将筛选结果显示在原数据清单以外的区域。如保留数据清单的原有显示方式，并将筛选结果显示到其他空白区域或者另一个工作表中。
现假设需要使用“高级筛选”功能筛选出1802班年龄在20岁及以上或者1803班加权成绩在80分以下的女生信息，并将结果显示在该工作簿的工作表Sheet2中。具体操作如下。
(1) 在单独的单元格区域内构建高级筛选条件。在数据清单外的空白单元格区域I1:L3内输入筛选条件，如图573所示。注意,


图573筛选条件

在输入筛选条件的过程中，如果该条件以等号“=”开头，应在等号“=”前输入英文半角单引号“’”，以免Excel将等号后面的部分默认作为公式处理(也可以省略等号)。
高级筛选条件的构建应遵循以下原则： 
  条件区域内必须有列标题，且与数据列表中的列标题一致。
  在相应的列标题下输入查询条件，可以使用诸如>、<、=等运算符。
  条件区域中同一行的条件之间为“与”逻辑关系，即必须同时满足时才会被筛选出来，位于不同行的条件之间为“或”逻辑关系，即只要满足某一行的条件就可以被筛选出来。
根据上述原则，可对比上图中的筛选条件与需要进行的筛选内容的一致性。
(2) 确立了高级筛选条件后，单击需要显示筛选结果的位置，这里选择Sheet2工作表中的A1单元格。单击“数据”选项卡“排序和筛选”组中的“高级”按钮，弹出“高级筛选”对话框，其筛选结果如图574所示。


图574筛选结果


(3) 在“方式”下方的单选框中选择筛选结果的存放方式，这里选择“将筛选结果复制到其他位置”单选按钮； 在“列表区域”框中选择进行筛选的区域，这里选择Sheet1中学生信息数据清单对应的区域； 在“条件区域”框中选择筛选条件所在的区域，这里选择Sheet1中的筛选条件区域I1:L3； 在“复制到”框中设置筛选结果所在的区域，这里选中Sheet2中的A1单元格，此时筛选结果将从A1单元格开始向右向下填充。
(4) 单击“高级筛选”对话框中的“确定”按钮，符合条件的筛选结果将显示在Sheet2工作表中，筛选结果如图574所示。


5.3.4分类汇总
分类汇总可以实现对数据的分类统计，即先根据数据清单中的某一列(字段)进行排序，将分类字段中字段值相等的记录合并为一组，然后对其他字段的数值进行各种统计计算，如求和、计数，求平均值、最大值、最小值、乘积等。
1. 创建简单的分类汇总
【例512】某服务业连锁公司全年的客户及收入信息如图575所示。为了更加清晰、直观地显示数据行之间的联系，并进行统计与分析，可以使用Excel提供的分类汇总方法，对数据清单中的客户按照行业或者级别进行分类，统计计算全年各类客户的业务收入。


图575某服务业连锁公司全年的客户及收入信息


具体操作如下。
(1) 对数据清单中的内容按照“客户行业”进行分类，即以“客户行业”字段作为关键字排序(升序/降序均可)，目的是让同一种行业的客户信息排列在一起。
【注意】分类汇总的第一步为分类，即根据分类字段进行排序，这一步骤不能省略，否则将导致分类汇总结果出错。
(2) 完成数据的汇总操作。单击数据清单中的任意单元格(如C4)，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，弹出“分类汇总”对话框，如图576所示。
(3) 在“分类汇总”对话框中进行如下设置。
① 在“分类字段”下拉列表框中选择分类字段。此处选择“客户行业”。
②  在“汇总方式”下拉列表框中选择汇总方式。此处选择“求和”。
③  在“选定汇总项”列表框中选择汇总字段。此处勾选“业务收入(万元)”复选框，统计每种行业的所有客户业务收入的总和。
④  勾选“替换当前分类汇总”复选框，使新的汇总替换数据清单中已有的汇总结果。“每组数据分页”复选框可设置每组汇总数据之间是否自动插入分页符，这里保持未勾选状态。“汇总结果显示在数据下方”复选框可设置每组汇总结果显示在该组下方还是上方，此处将其勾选。
设置完成后的“分类汇总”对话框如图576所示。
(4) 单击“确定”按钮，完成分类汇总计算，分类汇总结果如图577所示(Excel会分析数据清单，插入包含SUBTOTAL函数的公式进行计算)。



图576“分类汇总”对话框




图577分类汇总结果



2. 多重分类汇总
如果需要为数据清单添加更多的分类汇总，则需要进行多重分类汇总。具体操作如下。
(1) 单击分类汇总求和后的数据清单中的任意单元格(如C7)，单击“数据”选项卡“分级显示”组中的“分类汇总”按钮，弹出“分类汇总”对话框。
(2) 在“分类汇总”对话框中进行如下设置。
① 保留“分类字段”和“选定汇总项”的原有设置。
②  在“汇总方式”下拉列表框中选择汇总方式。此处选择“平均值”。统计每种行业的所有客户业务收入的平均值。
③  取消勾选“替换当前分类汇总”复选框。多重分类汇总参数设置如图578所示。


图578多重分类汇总参数设置


(3) 单击“确定”按钮，完成操作。
(4) 重复以上操作，分别完成对“客户行业”进行最大值和最小值的分类汇总。多重分类汇总结果如图579所示。


图579多重分类汇总结果


3. 取消和替换当前的分类汇总
如果想要取消已经设置好的分类汇总，只需弹出“分类汇总”对话框，单击左下角的“全部删除”按钮即可。如果想要替换当前的分类汇总，则要在“分类汇总”对话框中勾选“替换当前分类汇总”复选框。
4. 数据分级显示
对数据清单进行分类汇总后，将自动进入分级显示状态。此外还可以为数据清单手工添加数据分级显示，Excel支持最多8个级别的分级显示。如对于图579中所示的多重分类汇总结果，可以通过数据分级显示功能，显示每种行业中客户业务收入的概要情况(最大值、合计值)等，如果需要重点关注某一行业的信息，则可以查阅该行业所有客户收入的明细数据。具体操作如下。
(1) 对图575中的数据清单进行分类汇总后，在数据区域的左侧会出现分级显示符号，表明数据区域正处于分级显示状态，如图580所示。


图580分级显示状态


(2) 分级显示符号最上方的表示分级的层数和级别，数字越小代表级别越大。通过单击不同的数字编号，可以改变右侧数据区域的显示层次。例如，单击“1”，分类汇总结果只显示最高层次的数据，即全年所有行业客户的业务收入总和及平均值等。单击“2”，分类汇总结果将显示第二层分级的数据，即来自每种行业的客户业务收入总计值，以此类推。若要进一步显示某个行业(如批发和零售业)的局部明细数据，可以单击对应汇总信息左侧的按钮，如单击“批发和零售业 汇总”行左侧对应的按钮，将展开显示来自“批发和零售业”的所有客户的详细的业务收入记录，这时原先的按钮将自动变换为按钮，若要隐藏已经显示的明细数据，则可单击该按钮实现。
5.3.5合并计算
当所要分析和处理的数据来自多个不同的数据清单时，可以通过“合并计算”功能对这些数据进行合并计算，并存放到另一个数据清单中去。
【例513】有3个数据清单存放于Sheet1工作表中，分别代表了某干洗连锁公司3个分店一季度的销售信息，如图581所示。如果要统计所有分店一季度的销售情况，可以利用Excel提供的合并计算功能来完成。


图581某干洗连锁公司3个分店一季度的销售信息



具体操作如下。
(1) 打开案例工作簿文件“合并计算.xlsx”，选中Sheet2工作表的A1单元格。
(2) 单击“数据”选项卡“数据工具”组中的“合并计算”按钮，弹出“合并计算”对话框，如图582所示。


图582“合并计算”对话框和合并结果



(3) 在“函数”下拉列表框中，选择“求和”选项。Excel提供了一系列在合并计算中可以使用的统计方法，如“求和”“求平均”“最大/最小值”“计数”“方差/标准差”等。这里选择“求和”方式，这样在合并计算中将对来自各个分店的同一类商品的销量和利润值进行求和计算，得到该公司一季度各类商品的销售统计信息。
(4) 在“引用位置”对话框中，选择要对其进行合并计算的数据清单区域。在这里选择工作表Sheet1左侧的数据清单对应的单元格区域A2:C8。本例中所有需要合并的数据清单都位于同一个工作簿中，如果需要合并的数据位于另一个工作簿的工作表中，可以单击“浏览”按钮找到该工作簿，并指定需要合并的数据区域。
(5) 单击“添加”按钮，先前选择的数据区域将显示在“所有引用位置”列表框中。
(6) 重复(4)和(5)中的操作，添加其他需要进行合并计算的数据区域。这里分别添加中间和右侧的数据清单对应的单元格区域E2： G8和I2： K8。需要注意的是，参与合并的数据清单不能与合并结果位于同一个工作表中，且它们应具有相同的布局。
(7) 在“标签位置”组中选择数据标签在参与合并的数据区域中的位置。这里由于需要根据商品类型统计每种商品的销量和利润，因此合并数据区域的“首行”与“最左列”均为数据标签，应同时勾选上述两个复选框。由于所有数据都处于同一工作簿中，因此无须勾选“创建指向源数据的链接”复选框。
(8) 单击“确定”按钮完成数据合并，得到合并结果如图582所示。
5.4科学制图
Excel在提供强大的数据处理功能的同时，也提供了丰富实用的图表功能。图表是数据的可视化表示，通过图表能够直观地显示工作表中的数据，形象地反映数据的差异和变化趋势。Excel图表与数据是动态关联的，即如果图表所关联的表格数据发生改变，图表会自动更新来反映那些变化。



视频讲解



5.4.1图表类型及组成
1. 图表的类型
Excel 2016提供了14种标准图表类型，每个类型下又包括若干个子类型，比早期版本更加丰富。Excel中的图表类型及其相关的功能描述如表55所示。


表55Excel中的图表类型及其相关的功能描述




图表类型功 能 描 述

柱形图柱形图用于显示一段时间内的数据变化或说明各数据项之间的比较情况。在柱形图中，通常沿横坐标轴组织类别(如时间、种类等)，沿纵坐标轴组织值
条形图条形图类似于水平的柱形图，用来比较不同类别数据之间的差异情况，显示各持续型数值之间的比较情况
折线图折线图常用来分析数据随时间的变化趋势，也可用来分析多组数据随时间变化的相互作用和相互影响
面积图面积图显示数值之间或其他类别数据变化的趋势，它强调数量随时间而变化的程度，也可用于引起人们对总值趋势的注意
饼图饼图可以显示一个数据系列中各项的大小在各项总和中的比例。一般而言，饼图比较适用于只有一组数据系列，系列中不超过7个类别且系列中没有负值或零值的情形
圆环图像饼图一样，圆环图显示各个部分与整体之间的关系，但是它可以包含多个数据系列
散点图散点图显示若干数据系列中各数值之间的关系，或者将两组数字绘制为XY坐标的一个系列。散点图通常用于显示和比较数值，例如科学数据、统计数据和工程数据
气泡图气泡图是散点图的扩展，可以比较成组的3个值，其中两个值确定气泡的位置，第3个值确定气泡点的大小，应用于更加复杂的数据关系
曲面图曲面图可以帮助寻找两组数据之间的最佳组合，如在某一XY坐标空间内描述地形高度最高的一个点
雷达图雷达图可以比较几个数据系列的聚合值以及各值相对于中心点的变化
股价图股价图通常用来显示股价的波动。不过，这种图表也可用于科学数据。例如，可以使用股价图来说明每天或每年温度的波动。必须按正确的顺序来组织数据才能创建股价图
瀑布图瀑布图是指通过巧妙的设置使图表中数据点的排列形状似瀑布，一般用于分类使用，便于反映各部分之间的差异。能够在反映数据多少的同时，直观地反映出数据的增减变化
树状图树状图作用于比较层级结构不同级别的值，以矩形显示层次结构级别中的比例。一般在数据按层级结构组织并具有较少类别时使用
旭日图旭日图作用于比较层级结构不同级别的值，以环形显示层次结构级别中的比例。一般在数据按层级结构组织并具有较多类别时使用
直方图直方图是一种统计报告图，一般用横轴表示数据类型，用纵轴表示分布情况
箱形图箱形图是一种用作显示一组数据分散情况的统计图，能提供有关数据位置和分散情况的关键信息，经常使用在品质管理等领域

2. 图表的组成
Excel的图表由诸多图表元素组成，包括图表区、绘图区、图表标题、图例、坐标轴、数据系列、网格线等，Excel图表的组成如图583所示。


图583Excel图表的组成



（1） 图表区： 包含整个图表及其中的全部元素。通过选定图表区可以对图表中的所有元素进行整体性的修改和编辑。
（2）  绘图区： 以坐标轴为界的图形绘制区域。
（3）  图表标题： 描述图表的名称，默认在图表的顶端。
（4）  数据系列和数据点： 数据系列是由数据点构成的，数据系列对应于工作表中选定区域的一行或一列数据，每个数据点则对应于该行或列中的某个单元格内的数据。可以在图表中绘制一个或者多个数据系列。
（5）  数据标签： 为各数据点提供附加信息的标签，表示数据源所在单元格的值。
（6）  坐标轴： 界定图表绘图区的线条，用作度量的参照框架，分为水平(类别)坐标轴与垂直(值)坐标轴。
（7）  坐标轴标题： 对坐标轴的说明性文本，包括水平轴标题、垂直轴标题。
（8）  图例： 图例是一个文本框，用于标识图表中相应数据系列的名称和数据系列在图表中的颜色。当图表只有一个数据系列时，默认不显示图例。
（9）  快捷按钮： 由上至下分别是图表元素按钮、图表样式按钮和图表筛选器按钮。其中，图表元素按钮可以快速添加、删除或更改图表元素； 图表样式按钮可以快速设置图表样式和配色方案； 图表筛选器按钮可以快速选择在图表上显示哪些数据系列和名称。



视频讲解



5.4.2图表操作
1. 在Excel中创建图表
创建图表就是将工作表中的数据以图形化的方式显示出来。插入的图表既可以嵌入工作表中，也可以显示在单独的图表工作表中。

【例514】某干洗店第一季度的销量数据如图584所示，可以根据此工作表中的数据来制作图表，从而直观形象地比较不同类型商品的销量。


图584某干洗店第一季度的销售数据


具体操作如下。
(1) 选择要创建图表的数据所在的单元格区域，可以选择不相邻的多个区域。这里为了比较图584中不同商品类型的销量情况，选择单元格区域A1:A7和D1:D7作为图表关联的数据区。
(2) 单击“插入”选项卡“图表”组中的“插入柱形图或条形图”按钮，在下拉列表中选择“簇状柱形图”命令，如图585所示，即可在工作表中插入柱形图，如图586所示。


图585选择“簇状柱形图”命令




图586插入柱形图


2. 在Excel中编辑和修改图表
图表创建完成后，得到的往往只是如图586所示的基本图表。如果需要用图表清晰地表达数据的含义，或制作个性化的图表，就需要对图表及图表中的不同元素进行编辑和修改。
（1） 更改布局和样式。
在创建基本图表后，可以重新对基本图表结构进行调整，选择更合适的布局方式显示图表。以图586中的基本图表为例，可对其布局和样式进行修改。具体操作如下。
①  使用Excel提供的11种预定义图表布局改变图表元素的位置，这里首先选中该基本图表，单击“图表工具设计”选项卡“图表布局”组中的“快速布局”按钮，打开所有预定义的布局类型，并选择要使用的图表布局，这里选择“布局2”，该布局将为图表添加数据标签，并移除垂直坐标轴，预定义图表布局设置结果如图587所示。


图587预定义图表布局设置结果


② Excel还会根据图表的类型提供不同的预定义图表样式以供选择和设置，这里选中图表，在“设计”选项卡的“图表样式”组中选择“样式4”。应用了样式的图表如图588所示。


图588应用了样式的图表


③ 除了按照Excel预定义的设置修改图表布局和样式外，还可以进行手动编辑。如果需要手动修改图表元素的布局，可以在图表区中单击选中特定的元素，通过单击“设计”选项卡“图表布局”组中的“添加图表元素”按钮打开下拉列表，并选择不同的命令完成布局的更改，也可以直接拖动选中的元素将其放置到想要的位置。
④ 如果需要手动修改图表元素的格式，同样可以先选中该元素，通过“格式”选项卡“当前所选内容”“形状样式”和“艺术字样式”组中的相应按钮完成特定元素样式的调整，也可以在所选元素上右击，在弹出的快捷菜单中选择相关的设置格式命令进行设置。
（2） 设置数据系列格式。
完成布局设置后，还可以对图表数据系列格式进行进一步的设置。具体操作如下。双击该图表的数据系列，打开“设置数据系列格式”窗格，在“系列选项”选项卡中，设置“间隙宽度”选项为0%，完成柱形大小与间距的调整。在“系列选项”选项卡中，依次选中“填充”下的“纯色填充”单选按钮，设置“颜色”为蓝色； 再依次选中“边框”下的“实线”单选按钮，设置“颜色”为白色，“宽度”为3磅。数据系列格式设置过程与结果如图589所示。



图589数据系列格式设置过程与结果


（3） 更改图表类型。
选择某种图表类型创建了相应的图表之后，可以根据应用的需要，将其调整或更改为新的图表类型。具体操作如下。
① 选择需要更改类型的图表或者图表中的某一个数据系列。
② 单击“设计”选项卡“类型”组中的“更改图表类型”按钮，弹出“更改图表类型”对话框，选择需要变更的图表类型，单击“确定”按钮即可。
需要注意，如果更改后的图表类型不支持原图表的数据源，可能会出现错误提示。
（4） 设置图表字体。
选中图表区，单击“开始”选项卡，可设置整个图表的字体。此处依次设置上述图表的“字体”为微软雅黑、“字号”为10、“字体颜色”为黑色。
（5） 编辑图表标题。
图表的标题一般在创建图表的过程中由系统自动生成，因此其内容、位置和格式可能不符合实际的表达需要。此时，可以使用如下方法对图表标题进行修改： 
① 如果图表中没有标题，则可以选中该图表，单击“图表工具设计”选项卡“图表布局”组中的“添加图表元素”按钮，从下拉列表中选择要添加标题的位置，此时代表图表标题的文本框将被添加至图表区中。
② 如果图表已有标题，可在标题文本框中输入文字以编辑修改其内容。此处，将标题改为“第一季度销量图”。
③ 在“开始”选项卡的“字体”组和“段落”组中，可以设置标题的字体、段落格式。这里将图表标题的字体颜色改为“绿色”。
（6） 添加数据标签。
要标识图表各数据系列中数据点的具体取值，可以为图表添加数据标签。具体操作如下。
① 选择需要添加数据标签的图表或者图表中的某一个数据系列，单击“图表工具设计”选项卡“图表布局”组中的“添加图表元素”按钮，从下拉列表中选择要添加数据标签的位置即可。
② 数据标签被添加后将自动与源数据表中对应单元格中的取值关联，当这些值发生变化时图表中的数据标签也会随之改变。
（7） 编辑图例和坐标轴。
在默认的情况下，创建图表过程中将自动创建并显示图例。可以对图例的位置、格式进行修改或者隐藏图例。具体操作如下
。
① 选择要进行图例设置的图表，单击“图表工具设计”选项卡“图表布局”组中的“添加图表元素”按钮，从下拉列表中选择要添加图例的位置或者隐藏图例(选择“无”命令)即可。
② 选择“更多图例选项”命令，打开“设置图例格式”窗格，可以进一步设置图例的格式。
③ 找到图例对应的文本框，可以对图例的内容进行编辑，并更改其字体和段落格式。
创建图表后，可以对坐标轴的显示、位置、刻度范围和标签进行编辑和修改。具体操作如下。
① 选择要进行坐标轴设置的图表，单击“图表工具设计”选项卡“图表布局”组中的“添加图表元素”按钮，在下拉列表中选择“坐标轴”命令，即可对横、纵坐标轴进行设置。
② 选择“更多轴选项”命令，打开“设置坐标轴格式”窗格，可以进一步设置坐标轴的格式。这里将图表的横坐标轴设置为“颜色”为黑色、“宽度”为2磅的“实线”，并设置“次刻度线类型”为“外部”。坐标轴格式设置如图590所示。


图590坐标轴格式设置


③ 为了让图表中每个数据点的取值之间的细微差距更容易被区分，可以为表格的横坐标轴和纵坐标轴添加网格线。单击“图表工具设计”选项卡“图表布局”组中的“添加图表元素”按钮，在下拉列表中选择“网格线”命令，即可对网格线进行设置。
④ 为了更好地描述各个坐标轴所代表的含义，还可以为坐标轴添加标题。单击“图表工具设计”选项卡“图表布局”组中的“添加图表元素”按钮，在下拉列表中选择“坐标轴标题”命令，即可对坐标轴标题进行设置。此处为图表添加横坐标轴标题，输入“商品类型”。通过“开始”选项卡的“字体”组和“段落”组，可以设置坐标轴标题的字体、段落格式。
本例中图表经过一系列的编辑和修改，最终图表编辑和修改结果如图591所示。


图591图表编辑和修改结果


（8） 移动图表。
如果图表与产生图表的数据位于同一个工作表中，该图表称为嵌入式图表。嵌入式图表可能会遮挡数据源中的数据。为了避免这种情况，可以将其移至合适的位置。具体操作如下。
① 在工作表中移动图表。
在图表区选中图表，将鼠标移到图表的边框位置，当鼠标指针变为形状时，拖动图表到新的位置。
② 在工作表间移动图表。
在图表区的空白处右击，在弹出的快捷菜单中选择“移动图表”命令，弹出“移动图表”对话框。在“对象位于”复合框的下拉列表框中选择目标工作表，单击“确定”按钮，即可将图表移动到目标工作表中。此外，也可以选择“新工作表”选项，Excel会新建一个Chart图表工作表。
5.4.3常用图表的应用
掌握了基本图表的创建及编辑方法后，就可以使用不同类型的图表显示比较不同的数据。下面将详细介绍折线图及迷你图的使用方法。
1. 折线图
折线图常用来描述随时间变化的数据系列，如某地区不同时段的温度变化，或者某企业每月的营业额变化等。
【例515】如今，全球气候变暖这一现象已成为人们关注的焦点，我国某城市1995—2005年2月及8月的平均温度数据如图592所示，利用Excel提供的折线图工具，可以对上述数据进行对比分析，了解其变化趋势。


图592我国某城市1995—2005年2月及8月的平均温度数据


具体操作如下。
(1) 选择要绘制图表的数据区域，这里选中工作表中的单元格区域A2:L3。按照本节介绍的创建基本图表方法，创建一个“带数据标记的折线图”，得到基本折线图，如图593所示。


图593基本折线图


(2) 对横坐标轴的标签进行设置，单击“图表工具设计”选项卡“数据”组中的“选择数据”按钮，弹出“选择数据源”对话框。单击“水平(分类)轴标签”下方的“编辑”按钮，弹出“轴标签”对话框对横坐标轴标签进行编辑。在“轴标签区域”下方的输入框右侧单击按钮，接下来拖动鼠标选择横坐标轴对应的区域，即代表年份数据的单元格区域B1:L1，单击“确定”按钮回到“选择数据源”对话框。此时“水平(分类)轴标签”下方的列表中将填入1995—2005年的年份信息，再次单击“确定”按钮，即可完成横坐标轴年份标签的设置。利用“选择数据源”和“轴标签”对话框设置横坐标轴标签过程如图594所示。



图594利用“选择数据源”和“轴标签”对话框设置横坐标轴标签过程


(3) 通过鼠标拖动图表边框的尺寸控制点调整图表区的大小，在图表标题文本框中输入标题“1995—2005年2月、8月平均气温变化”，并设置其字体为加粗、14号微软雅黑字体。
(4) 在绘图区右击，在弹出的快捷菜单中选择“设置绘图区格式”命令，打开“设置绘图区格式”窗格，单击“填充与线条”选项卡，设置绘图区的填充方式为“渐变填充”“预色渐变”“线性向上”等，单击“关闭”按钮，此时绘图区将变为淡蓝色渐变填充效果。
(5) 在绘图区中单击选中2月平均气温对应的数据系列，即颜色为蓝色的线条，为该数据系列添加数据标签，标签位置在数据点的正上方。可以结合数据标签看出该市11年间2月的平均气温基本在5~10℃变化，其中最高月平均气温为9.4℃，出现在2004年，最低月平均气温为4.8℃，出现在2000年。
(6) 如果需要分析数据随时间变化的趋势，可以为折线图中的数据系列添加趋势线。为了观察该市2月平均温度变化的趋势，再次选中2月平均气温对应的数据系列，右击，在弹出的快捷菜单中选择“添加趋势线”命令，打开“设置趋势线格式”窗格，在“趋势线选项”中选择“线性”单选按钮，设置线条颜色为“红色”、线型为“方点”、宽度为“1.5磅”，单击“关闭”按钮，最终得到月平均气温变化图及趋势线如图595所示。通过趋势线可以看出，该市2月的平均气温在11年间整体呈略微上升趋势。


图595月平均气温变化图及趋势线


2. 迷你图
迷你图是插入工作表单元格中的微型图表，用于快速显示一系列数据的变化趋势，并能够突出显示最大值和最小值等统计信息。迷你图与一般的Excel图表不同，迷你图不是对象，它实际上是单元格背景中的一部分，因此可以在迷你图所在的单元格中输入数字、文本等各种类型的数据。
【例516】某公司各分店2017—2018年各季度的商品销量总额如图596所示，利用迷你图功能可以将各分店2017—2018年各季度销量的变化趋势显示在单元格区域J3:J5中。


图596某公司各分店2017—2018年各季度的商品销量总额


具体操作如下。
(1) 单击选中工作表的J3单元格，单击“插入”选项卡“迷你图”组中的“柱形”按钮，弹出“创建迷你图”对话框，如图597所示。


图597弹出“创建迷你图”对话框


(2) 在“数据范围”框中输入迷你图所关联的数据区域，这里选择迷你图单元格同一行左侧的单元格区域B3:I3，代表的是江北店2两个季度的销量信息，在“位置范围”框中已经显示了当前选中的单元格位置，这里不做修改。单击“确定”按钮，迷你图将被绘制到选中的单元格中。
(3) 迷你图创建完成后，可以通过“设计”选项卡中的按钮和命令，对迷你图的类型、样式、数据点的显示进行设置。这里在“样式”组中将迷你图样式设置为“橙色，迷你图样式着色6，(无深色或浅色)”。在“显示”组中勾选“高点”复选框，即突出显示迷你图对应数据系列中的最大值。最后在“样式”组中的标记颜色下拉列表中设置高点显示颜色为“蓝色”。
(4) 如果相邻的区域还有其他的数据系列，拖动迷你图所在单元格右下方的填充柄可以像复制公式一样填充迷你图。这里向下拖动J3单元格的填充柄到J5单元格，生成反映江南店1和江南店2销量的迷你图。迷你图的创建和编辑结果如图598所示。



图598迷你图的创建和编辑结果


(5) 如果要删除迷你图，可以先选中迷你图所在的单元格，单击“迷你图工具设计”选项卡“分组”组中的“清除”按钮，即可删除选中的迷你图。
本章小结
通过互联网获取数据分析所需的原始数据后，使用Excel对以表格为代表的结构化数据进行存储和处理是数据分析的重要准备工作。作为Microsoft Office办公组件的核心组成部分之一，Excel 2016提供了一系列实用的表格数据处理工具，从而帮助用户完成从简单到复杂的结构化数据存储和管理工作，包括： 
数据的录入与编辑。Excel提供了多种表格数据录入的方式帮助用户节省表格制作的时间，包括手动输入、自动填充、由各类文档或数据库导入等。
工作表的格式化。通过Excel提供的格式化工具，用户可以根据自己的喜好对工作表进行诸如字体、颜色、边框、底纹等一系列格式化设置，让工作表更加美观整洁。
公式与函数。用户可以灵活地利用Excel中大量的公式函数完成不同类型的数据运算和统计操作，满足表格中海量数据处理的需要，简化数据计算的过程。
图表。以表格中的数据为基础添加不同类型的图表，完成不同数据之间的比较，以便更加清晰和直观地描述表格中的内容。
表格数据分析和处理。将数据表格作为小型的数据库进行管理，使用排序、筛选、分类汇总等工具对表格数据进行预处理，为数据分析做好准备。



第5章
扩展案例
视频



Excel不仅仅局限于实现基本的表格数据处理功能，而是提供了丰富的数据分析、处理和统计建模功能，让用户能够在大量相关数据中提取和挖掘关键信息，从而满足各类数据处理、统计分析和辅助决策的需要。在第6章中，将重点介绍使用Excel 2016完成数据分析的工具和方法。