大学计算机基础-Excel电子表格

第4章

Excel电子表格肖烨刚刚入职Contoso公司销售部，担任销售助理一职。工作上有点力不从心，她感觉压力很大，心情烦操。面对这样的工作开局，她决定向部门业务骨干老姬进行请教。目前存在以下不足。对Excel工作表的编辑、格式设置缺乏深入的应用；对Excel工作表中数据的计算，尤其是统计分析函数缺乏了解；对于直观表示表中数据状况的图表使用不自如；对数据分析操作一知半解；Excel工作表页眉设置与打印业务不熟悉；不知如何对工作表和工作簿进行数据保护。目录4.1

认识Excel

20104.2

工作表的制作4.3

表中数据的计算4.4

图表的制作4.5表中数据的管理4.6工作表的打印4.7数据的保护4.1

认识Excel

20104.1.1

Excel2010的基本功能与概念Excel2010的基本功能表格编辑制作图表数据管理公式与函数科学分析网络功能与发布工作簿Excel2010的基本概念工作簿Excel2010文件默认的扩展名是.xlsx工作表当新建一个Excel工作簿时，默认包含3张工作表单元格单元格的地址由工作表的行标号和列标号来标识，列标号在前，行标号在后4.1.2

Excel

2010的启动和退出可通过以下几种方式启动：可双击桌面上的Excel快捷方式图标。单击【开始】|【所有程序】|【MicrosoftOffice】|【MicrosoftExcel2010】命令。直接打开已存在的工作簿，则在启动Excel2010的同时也打开了该文件。可选择下列任意一种方法退出：单击【文件】菜单中的【退出】选项。单击标题栏左侧的图标，在出现的菜单中单击【关闭】选项。单击Excel窗口右上角的关闭图标。按【Alt+F4】组合键。在退出时，如果还没保存当前的工作簿，会出现一个【MicrosoftExcel】提示对话框，询问是否保存所做修改。4.1.3

Excel

2010的工作界面1.Excel2010工作窗口组成元素功能区标题栏快速启动工具栏选项卡组名称框编辑栏工作区状态栏2．Excel2010工作区操作术语当前活动单元格工作表标签活动工作表标签填充柄标签滚动按钮工作表标签分割线窗口水平和垂直拆分线4.1.4工作簿的基本操作1.工作簿的建立（1）建立空白工作簿3种方法启动Excel后，立即创建一个新的空白工作簿。单击快速访问工具栏上的【新建】按钮或按快捷键“Ctrl+N”，立即创建一个新的空白工作簿。打开【文件】选项卡|【新建】标签，在右侧【可用模板】中选择【空白工作簿】，单击【创建】按钮，立即创建一个新的空白工作簿。（2）根据模板建立工作簿

打开【文件】选项卡|【新建】标签。

选中【O模板】|【日历】|【永久年历】，在右侧单击【下载】按钮。

这样就可根据模板【日历】创建一个工作簿。（3）根据现有工作簿建立新的工作簿

单击【文件】选项卡|【新建】标签，在右侧【可用模板】中选择【根据现有内容新建】按钮。

打开【根据现有工作簿新建】对话框。

选择需要的工作簿文档，单击【新建】按钮即可。2．工作簿的打开2种方法直接双击工作簿图标先启动Excel，再打开工作簿3．工作簿的保存2种方法单击【文件】选项卡|【保存】按钮，即可保存工作簿。单击【快速访问工具栏】|【保存】按钮，即可保存工作簿。4.2

工作表的制作4.2.1

数据输入1.单元格、单元格区域的选定Excel2010遵循“先选定，再输入”的原则具体的选定方法如下选定对象选定方法单个单元格单击相应的单元格，或用方向键移动到相应的单元格连续单元格区域单击选定该区域的第一个单元格，然后拖动鼠标直至选定最后一个单元格所有单元格单击【全选】按钮，【全选】按钮位于工作表行标号与列标号相交处选定对象选定方法不相邻的单元格或单元格区域选定第一个单元格或单元格区域，然后按住Ctrl键再选定其他的单元格或单元格区域区域较大的单元格区域选定第一个单元格，然后按住Shift键再单击区域中最后一个单元格，通过滚动可以使单元格可见整行单击行号整列单击列标相邻的行或列沿行号或列标拖动鼠标。或者先选定第一行或第一列，然后按住Shift键再选定其他的行或列不相邻的行或列先选定第一行或第一列，然后按住Ctrl键再选定其他的行或列增加或减少活动区域中的单元格按住Shift键并单击新选定区域中最后一个单元格，在活动单元格和所单击的单元格之间的矩形区域将成为新的选定区域，取消单元格选定区域，单击工作表中其他任意一个单元格2．数据的输入（1）文本型数据字符文本：直接输入英文字母、汉字、数字和符号。如：BOOK、中国、b6。数字文本：由数字组成的字符串。先输入单引号，再输入数字。如：’200086。（2）数值型数据输入数值：直接输入数字，数字中可包含逗号，表示千位分隔符。如：1，245，210.89。输入分数：带分数的输入是在整数和分数之间加一个空格，真分数的输入是先输入0和空格，再输入分数。如：52/5、03/7。输入货币数值：先输入$或￥，再输入数字。如：$100、¥350。输入负数：先输入减号，再输入数字，或用圆括号（）把数括起来。如：−368、（250）。输入科学计数法表示的数：直接输入。如：1.46E+10。（3）日期/时间型数据日期数据输入：直接输入格式为“yyyy/mm/dd”或“yyyy-mm-dd”的数据，也可是“yy/mm/dd”或“yy-mm-dd”的数据。也可输入“mm/dd”的数据。时间数据输入：直接输入格式为“hh:mm［:ss］［AM/PM］”的数据。日期和时间数据输入：日期和时间用空格分隔。快速输入当前日期：按“Ctrl+；”组合键。快速输入当前时间：按“Ctrl+：”组合键。（4）逻辑型数据逻辑真值输入：直接输入“TRUE”。逻辑假值输入：直接输入“FALSE”。输入不同类型的数据3．自动填充（1）通过菜单命令填充数据（2）通过填充柄填充数据如果要让输入的数值递增，按下Ctrl键，再拖动填充柄填充即可。4.自定义序列

单击【开始】选项卡|【选项】按钮，打开“Excel选项”对话框。

单击左侧的【高级】选项卡，然后单击右侧的【编辑自定义列表】按钮，此时将会打开【自定义序列】对话框。

在“输入序列”下方输入要创建的自动填充序列。

单击【添加】按钮，则新的自定义填充序列出现在左侧“自定义序列”列表的最下方。

单击【确定】按钮，关闭对话框。4.2.2

工作表编辑1．移动和复制单元格2.移动和复制行或列3．插入单元格、行或列4．删除单元格、行或列5.对单元格中数据进行编辑（1）数据清单（2）记录单6.使用记录单编辑数据清单7.清除单元格格式或内容选定单元格执行右图菜单4.2.3

工作表的格式化1.数据的格式化2.数据的字体与对齐方式3.边框和底纹的设置

4.行高和列宽的设置（1）拖拉法（2）双击法（3）设置法（4）调整整个工作表列宽或行高5.设置条件格式条件格式是指当指定条件为真时，系统自动应用于单元格的格式，如单元格底纹或字体颜色。（1）设置条件格式（2）更改或删除条件格式6.单元格样式（1）应用样式（2）创建新样式7.套用表格样式系统预定义了60种表格的格式。4.2.4工作表的管理1.选择工作表选择单张工作表选择两张或多张相邻的工作表选择两张或多张不相邻的工作表工作簿中所有工作表2.添加工作表单击【开始】选项卡|【单元格】组|【插入】下拉按钮，选择【插入工作表】命令。在工作表标签栏中，用鼠标右键单击工作表名字，在弹出的快捷菜单中选择【插入】命令，打开【插入】对话框，在列表框中选中【工作表】，单击【确定】按钮。单击屏幕底部的【插入工作表】按钮。3.删除工作表单击【开始】选项卡|【单元格】组|【删除】下拉按钮，选择【删除工作表】命令，将删除工作表。在工作表标签栏中，用鼠标右键单击工作表名字，出现一个弹出式菜单，再选择【删除】命令。4.重命名工作表击【开始】选项卡|【单元格】组|【格式】下拉按钮，选择【重命名工作表】命令。在工作表标签栏中，用鼠标右键单击工作表名字，出现弹出式菜单，选择【重命名】命令。双击需要重命名的工作表标签。5.移动或复制工作表单击【开始】选项卡|【单元格】组|【格式】下拉按钮，在弹出的下拉菜单的【组织工作表】选项中单击【移动或复制工作表】命令，打开【移动或复制工作表】对话框。在【移动或复制工作表】对话框中，首先选择目标工作簿，然后选择一个放置位置，若要复制工作表，还需要选中【建立副本】复选框，单击【确定】按钮。拆分工作表单击【视图】选项卡|【窗口】组|【拆分】按钮。使用水平滚动条右侧或垂直滚动条上方的拆分框。6.拆分与冻结工作表工作表的冻结例：冻结前两行第一列4.2.5案例实战

通过对制作工作表的学习，小叶结合自己手头上的工作需要，决定在原有的“Contoso公司销售分析.xlsx”文件中，动手制作一张新的“图书定价”工作表。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）删除原来的“图书定价”工作表。（2）新建图4-30所示的“图书定价”工作表。要求：通过套用表格格式方法将所有的成绩记录调整为一致的外观格式，并对该工作表中的数据列表进行格式化操作：将"出版日期"列设为“****年**月**日”的显示方式，“定价”列设为保留两位小数的会计专业，并显示人民币符号，“规划教材数值表示”设置为整数表示，设置所有数据为居中对齐方式。（3）利用"条件格式"功能将“是否规划教材”为“TRUE”的单元格以一种颜色填充，所用颜色深浅以不遮挡数据为宜。图4-30新“图书定价”工作表4.3

表中数据的计算4.3.1

公式应用公式以“＝”开头，后面是用运算符把常数、函数、单元格引用等连接起来的有意义的表达式。1.运算符优先级：引用>算术＞字符＞关系算术运算符＋、-、*、/、%以及^（幂），计算顺序为先乘除后加减。比较运算符＝、＞、＞＝、＜、＜＝、＜＞，它们的优先级相同。文本运算符&（字符串连接），将两个文本值连接起来。引用运算符“：”区域运算符“，”联合运算符空格交叉运算符2.使用公式（1）创建和编辑公式例：如图4-31所示，根据“单价”和“销量”计算Contoso公司销售订单明细表中每笔订单的“销售额小计”，假设没有任何折扣政策。（2）更正公式监视窗口公式错误检查#DIV/0！：被除数字为0。#N/A：数值对函数或公式不可用。#NAME？：不能识别公式中的文本。#NULL！：使用了并不相交的两个区域的交叉引用。#NUM！：公式或函数中使用了无效数字值。#REF！：无效的单元格引用。#VALUE！：使用了错误的参数或操作数类型。常出现错误的值如下：（3）复制公式利用填充柄利用【复制】、【粘贴】命令或按钮3.单元格的引用相对引用I3绝对引用$E$3混合引用$C3或C$3三维引用［工作簿名！］工作表名！单元格引用4.3.2

函数应用1.常用函数一般形式：函数名（参数1，参数2，…）

函数语法功能说明数学函数SUM(number1,number2,...)返回某一单元格区域中数字、逻辑值、数字的文本表达式、直接键入的数字之和SUMIF(range,criteria,sum_range)根据指定条件对若干单元格求和SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)仅在sum_range参数中的单元格满足所有相应的指定条件时，才对该单元格求和PRODUCT(number1,number2，...)计算出作为参数指定的所有Number的乘积SUMPRODUCT(array1,array2,…)在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和INT(number)将数字向下舍入到最接近的整数ROUND(number,num_digits)计算保留按指定的位数四舍五入后的值ROUNDDOWN(number,num_digits)靠近零值,向下(绝对值减小的方向)舍入数字ROUNDUP(number,num_digits)远离零值,向上舍入数字

函数语法功能说明统计函数COUNT(value1,value2,...)计算日期和数值的有几格,即,能用SUM运算加进数值的内容有几格COUNTA(value1,value2,...)返回参数列表中非空值的单元格个数AVERAGE(number1,number2,...)返回参数的平均值(算术平均值)MAX(number1,number2,...)返回一组值中的最大值MIN(number1,number2,...)返回一组值中的最小值RANK(number,ref,order)返回一个数字在数字列表中的排位，order为0降序，为1升序文本函数LEFT(text,num_chars)基于所指定的字符数返回文本字符串中的左边几个字符RIGHT(text,num_chars)基于所指定的字符数返回文本字符串中的右边几个字符.MID(text,start_num,num_chars)返回文本字符串中从指定位置开始的特定数目的字符TEXT(value,format_text)将数值转换为按指定数字格式表示的文本

函数语法功能说明日期与时间函数TODAY()返回计算机系统的当前日期NOW()返回计算机系统的当前日期和时间YEAR(serial_number)将系列数转换为年MONTH(serial_number)将系列数转换为月DAY(serial_number)将系列数转换为日WEEKDAY(serial_number，return_type)返回某日期的星期数，在默认情况下,它的值为1(星期天)到7(星期六)之间的一个整数HOUR(serial_number)返回时间值的小时数，即一个介于0到23之间的整数MINUTE(serial_number)返回时间值的分钟数，即一个介于0到59之间的整数SECOND(serial_number)返回时间值的秒钟数，即一个介于0到59之间的整数逻辑与查找函数IF(logical_test,value_if_true,value_if_false)根据条件满足与否返回不同的值向量形式：LOOKUP(lookup_value,lookup_vector,result_vector)数组形式：LOOKUP(lookup_value,array)搜索单行、单列、区域、查找对应值VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值2.函数的使用（1）使用【插入函数】对话框输入函数使用VLOOKUP函数从“图书定价”表中查找“图书单价”向“Contoso公司销售订单明细表”填充“单价”值为例介绍【插入函数】对话框的使用，操作数据表如下图所示。((具体操作：12VLOOKUP函数Table_array参数的值必须是绝对地址。（2）利用【自动求和】按钮（3）手工输入函数使用LEFT函数和VLOOKUP函数从“城市对照”表中查找“销售区域”向“Contoso公司销售订单明细表”填充“所属区域”值为例介绍此方法的应用，操作数据表如下图所示。具体操作：

选中要插入函数的单元格，此例为H3。

在【编辑栏】中输入公式“=VLOOKUP(LEFT(G3,3),城市对照!$A$3:$B$25,2,FALSE)”，如图4-40所示。

按回车键或单击编辑栏中的“√”按钮即可。

选中H3单元格，使用拖动柄复制公式完成所有订单的“所属区域”填充。4.3.3案例实战

通过对计算表中数据的学习，小叶在“Contoso公司销售分析.xlsx”文件中，对2012年和2013年的公司产品销售情况进行统计分析过程时，使用公式和函数完成有关统计数据的计算。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）在“订单明细”工作表中，删除订单编号重复的记录(保留第一次出现的那条记录），但须保持原订单明细的记录顺序。（2）如果每订单的图书销量超过40本（含40本），则按照图书单价的9.3折进行销售；否则按照图书单价的原价进行销售。按照此规则，计算并填写“订单明细”工作表中每笔订单的“销售额小计”，保留2位小数。要求该工作表中的金额以显示精度参与后续的统计计算。（3）在“统计报告”工作表中，如图4-41所示，分别根据“统计项目”列的描述，计算并填写所对应的“统计数据”单元格中的信息。图4-41“统计报告”工作表4.4

图表的制作

Excel图表嵌入式图表独立图表

图表区

图表标题

数据系列标记

坐标轴

网格线

图例4.4.1

图表的创建例：为“图书定价参考表”中前5本图书的“价格”创建柱形图。

步骤如下：

选定要创建图表的数据区域。

单击【插入】选项卡|【图表】组|【柱形图】下拉按钮，选择【三维簇状柱形图】按钮即可，如下图所示。4.4.2

图表的编辑1．编辑图表中的数据（1）增加数据例：在图4-43中增加“规划教材数值表示”数据系列。步骤如下：

鼠标右键单击图表中任意位置，在弹出的右键菜单中选择【选择数据】命令，打开【选择数据源】对话框，如图4-44所示。图4-44【选择数据源】对话框

在【图例项（系列）】列表中单击【添加】按钮，打开【编辑数据系列】对话框。

将光标定位在【系列名称】文本框中，在表格中选中“G2”单元格，接着将光标定位在【系列值】文本框中，在表格中选中“G3:G7”单元格区域，如图4-45所示。图4-45选择系列名称和系列值

单击【确定】按钮，返回【选择数据源】对话框，在【水平（分类）轴标签】列表中单击【编辑】按钮，打开【轴标签】对话框，将光标定位在【轴标签区域】文本框中，在表格中选中“”单元格区域，如图4-46所示。图4-46编辑【水平（分类）轴标签】

单击【确定】按钮，返回【选择数据源】对话框，再次单击【确定】按钮。即可将“规划教材数值表示”数据系列添加到图表中，如图4-47所示。图4-47图表中添加“规划教材数值表示”数据系列（2）删除数据在【选择数据源】对话框中，选中要删除的数据系列，单击【删除】按钮。在图表中，先单击要删除的数据系列，再按Del键。右键单击数据系列，从快捷菜单中选择【删除】命令，如右图所示。（3）更改系列的名称打开如右图所示的【选择数据源】对话框。在【图例项（系列）】列表中选中需要更改的数据源，接着单击【编辑】按钮，打开【编辑数据系列】对话框，在该对话框中完成相应的修改即可。2．更改图表的类型3．设置图表格式4.4.3案例实战

通过对制作图表的学习，小叶在对2012年和2013年的公司产品销售情况进行统计分析的过程中，使用图表来更直观的反映数据的分布情况。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）在“图书定价”工作表中创建三维簇状条形图来表示教材是不是规划教材。（2）对所建的图表进行修饰使其美观。4.5表中数据的管理4.5.1

数据排序1．排序规则数值按数值的大小进行排序。字母按照英文字典中的先后顺序排序。日期按日期的先后顺序排序。汉字按汉语拼音的字典顺序或按笔画顺序，以笔画的多少作为排序依据。逻辑值升序时FALSE排在TRUE前面，降序时相反。空格总是排在最后。2．简单排序简单排序是指对单一字段按升序或降序排序。3．复杂数据排序复杂排序就是将数据清单按多关键字值的顺序进行排序。4．自定义排序首先应建立自定义序列，建立好自定义序列后，即可对数据进行排序。4.5.2

数据筛选1.自动筛选例：筛选出“图书定价参考表”中科学出版社的图书记录。2.高级筛选例：筛选出“图书定价参考表”中，科学出版社定价大于40元或清华大学出版社定价小于40元的图书记录。条件区域4.5.3

数据分类汇总分类汇总指的是按某一字段汇总有关数据，比如按出版社汇总图书数量，按书店名称汇总销售额等。分类汇总排序汇总1．简单汇总例：在“图书定价参考表”中按出版社汇总图书数量。2．分级显示汇总数据在分类汇总表的左侧可以看到分级显示的“123”3个按钮标志。“1”代表总计“2”代表分类合计“3”代表明细数据单击按钮“1”将显示全部数据的汇总结果，不显示具体数据。单击按钮“2”将显示总的汇总结果和分类汇总结果，不显示具体数据。单击按钮“3”将显示全部汇总结果和明细数据。单击“+”和“−”按钮可以打开或折叠某些数据。3．嵌套汇总对汇总的数据还想进行不同的汇总，如统计各出版社图书数量后，又想统计各出版社的图书均定价，可再次进行分类汇总。4．清除分类汇总如果要删除已经存在的分类汇总，在下图中单击【全部删除】按钮即可。4.5.4

数据透视表与数据透视图数据透视表是一种可以对大量数据快速汇总和建立交叉列表的交互式表格。是一种动态工作表功能对行和列进行转换以查看源数据的不同汇总结果显示不同页面以筛选数据根据需要显示区域中的明细数据设有“Contoso公司销售订单明细表”，如下图所示。以此数据表为例创建数据透视表。

单击工作表数据清单中的任一单元格。

单击【插入】选项卡|【表格】组|【数据透视表】按钮。

在弹出的【创建数据透视表】对话框中的【请选择要分析的数据】栏中选中【选择一个表或区域】单选项，在【表/区域】文本框中输入或使用鼠标选取引用位置，此处为“订单明细!$A$2:$I$636”。

在【选择放置数据透视表的位置】栏中选中【新工作表】单选项，如右图所示。

单击【确定】按钮，一个空的数据透视表将添加到一个新的工作表中，并显示数据透视表字段列表。【报表筛选】字段：此处选择“所属区域”。【行标签】：此处选择“图书名称”。【列标签】：此处选择“书店名称”。【数值】字段：此处选择“销售额小计”，并求和函数进行汇总。数据透视图以直观的图表的方式来展示数据之间的关联，具体有良好的可视效果。

选中数据透视表中的任意单元格。

单击【数据透视表工具】的【选项】上下文选项卡|【工具】组|【数据透视图】按钮。

在打开的【插入图表】对话框中，选择相应的图表类型。

单击【确定】按钮。数据透视图与数据透视表中的数据动态关联如右图所示，分析东部地区某三种图书的销售额情况。4.5.5案例实战通过对管理表中数据的学习，小叶在“Contoso公司销售分析.xlsx”文件中，针对2012年和2013年的公司产品销售情况进行统计分析。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）在“订单明细”工作表中，先按书店名称升序，再按订单编号升序对销售记录排序。（2）在“订单明细”工作表中，筛选鼎盛书店2012年的销售记录。（3）在“订单明细”工作表中，根据所属区域分类汇总不同区域的销售额。（4）根据“订单明细”工作表中的销售记录，分别创建名为“北区”、“南区”、“西区”和“东区”的工作表，这4个工作表中分别统计本销售区域各类图书的累计销售金额，统计格式请参考“统计样例”工作表，如图4-65所示。将这4个工作表中的金额设置为带千分位的、保留两位小数的数值格式。图4-65“统计样例”工作表4.6工作表的打印4.6.1页面设置【页面】选项卡【页边距】选项卡【页眉/页脚】选项卡【工作表】选项卡4.6.2打印区域的设置和分页1．设置打印区域

用鼠标拖动选定待打印的工作表区域。此例选择“图书定价”工作表的A2:F19数据区域。

单击【页面布局】选项卡|【页面设置】组|【打印区域】下拉按钮|【设置打印区域】选项，设置好打印区域，如右图所示，打印区域边框为虚线。2．添加、删除分页插入水平或垂直分页符

在要插入水平或垂直分页符的位置下边或右边选中一行或一列。

单击【页面布局】选项卡|【页面设置】组|【分隔符】下拉按钮|【插入分页符】命令。删除分页符操作选择分页虚线的下一行或右一列的任何单元格，再单击【页面布局】选项卡|【页面设置】组|【分隔符】下拉按钮|【删除分页符】命令。取消所有的手动分页符可选择整个工作表，再单击【页面布局】选项卡|【页面设置】组|【分隔符】下拉按钮|【重置所有分页符】命令。3．分页预览