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

第4章Excel电子表格,肖烨刚刚入职Contoso公司销售部，担任销售助理一职。工作上有点力不从心，她感觉压力很大，心情烦操。面对这样的工作开局，她决定向部门业务骨干老姬进行请教。目前存在以下不足。对Excel工作表的编辑、格式设置缺乏深入的应用；对Excel工作表中数据的计算，尤其是统计分析函数缺乏了解；对于直观表示表中数据状况的图表使用不自如；对数据分析操作一知半解；Excel工作表页眉设置与打印业务不熟悉；不知如何对工作表和工作簿进行数据保护。,目录,4.1认识Excel2010,4.1.1Excel2010的基本功能与概念Excel2010的基本功能,Excel2010的基本概念,4.1.2Excel2010的启动和退出,可通过以下几种方式启动：可双击桌面上的Excel快捷方式图标。单击【开始】|【所有程序】|【MicrosoftOffice】|【MicrosoftExcel2010】命令。直接打开已存在的工作簿，则在启动Excel2010的同时也打开了该文件。,可选择下列任意一种方法退出：单击【文件】菜单中的【退出】选项。单击标题栏左侧的图标，在出现的菜单中单击【关闭】选项。单击Excel窗口右上角的关闭图标。按【Alt+F4】组合键。,在退出时，如果还没保存当前的工作簿，会出现一个【MicrosoftExcel】提示对话框，询问是否保存所做修改。,4.1.3Excel2010的工作界面,1.Excel2010工作窗口组成元素,2Excel2010工作区操作术语,4.1.4工作簿的基本操作,1.工作簿的建立（1）建立空白工作簿,（2）根据模板建立工作簿打开【文件】选项卡|【新建】标签。选中【O模板】|【日历】|【永久年历】，在右侧单击【下载】按钮。这样就可根据模板【日历】创建一个工作簿。,（3）根据现有工作簿建立新的工作簿单击【文件】选项卡|【新建】标签，在右侧【可用模板】中选择【根据现有内容新建】按钮。打开【根据现有工作簿新建】对话框。选择需要的工作簿文档，单击【新建】按钮即可。,2工作簿的打开,3工作簿的保存,4.2工作表的制作,4.2.1数据输入1.单元格、单元格区域的选定Excel2010遵循“先选定，再输入”的原则具体的选定方法如下,2数据的输入,输入不同类型的数据,3自动填充（1）通过菜单命令填充数据（2）通过填充柄填充数据如果要让输入的数值递增，按下Ctrl键，再拖动填充柄填充即可。,4.自定义序列单击【开始】选项卡|【选项】按钮，打开“Excel选项”对话框。单击左侧的【高级】选项卡，然后单击右侧的【编辑自定义列表】按钮，此时将会打开【自定义序列】对话框。在“输入序列”下方输入要创建的自动填充序列。单击【添加】按钮，则新的自定义填充序列出现在左侧“自定义序列”列表的最下方。单击【确定】按钮，关闭对话框。,4.2.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工作表的管理,2.添加工作表单击【开始】选项卡|【单元格】组|【插入】下拉按钮，选择【插入工作表】命令。在工作表标签栏中，用鼠标右键单击工作表名字，在弹出的快捷菜单中选择【插入】命令，打开【插入】对话框，在列表框中选中【工作表】，单击【确定】按钮。单击屏幕底部的【插入工作表】按钮。,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）更正公式监视窗口公式错误检查,常出现错误的值如下：,（3）复制公式利用填充柄利用【复制】、【粘贴】命令或按钮,3.单元格的引用,4.3.2函数应用,1.常用函数一般形式：函数名（参数1，参数2，）,2.函数的使用（1）使用【插入函数】对话框输入函数使用VLOOKUP函数从“图书定价”表中查找“图书单价”向“Contoso公司销售订单明细表”填充“单价”值为例介绍【插入函数】对话框的使用，操作数据表如下图所示。,具体操作：,VLOOKUP函数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图表的制作,图表区图表标题数据系列标记坐标轴网格线图例,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更改图表的类型,3设置图表格式,4.4.3案例实战,通过对制作图表的学习，小叶在对2012年和2013年的公司产品销售情况进行统计分析的过程中，使用图表来更直观的反映数据的分布情况。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）在“图书定价”工作表中创建三维簇状条形图来表示教材是不是规划教材。（2）对所建的图表进行修饰使其美观。,4.5表中数据的管理,4.5.1数据排序1排序规则,2简单排序简单排序是指对单一字段按升序或降序排序。,3复杂数据排序复杂排序就是将数据清单按多关键字值的顺序进行排序。,4自定义排序首先应建立自定义序列，建立好自定义序列后，即可对数据进行排序。,4.5.2数据筛选,1.自动筛选例：筛选出“图书定价参考表”中科学出版社的图书记录。,2.高级筛选例：筛选出“图书定价参考表”中，科学出版社定价大于40元或清华大学出版社定价小于40元的图书记录。,4.5.3数据分类汇总,分类汇总指的是按某一字段汇总有关数据，比如按出版社汇总图书数量，按书店名称汇总销售额等。,1简单汇总例：在“图书定价参考表”中按出版社汇总图书数量。,2分级显示汇总数据在分类汇总表的左侧可以看到分级显示的“123”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分页预览单击【视图】选项卡|【工作簿视图】组|【分页预览】按钮，可以在分页预览视图中直接查看工作表分页的情况，如图所示,4.6.3打印预览和打印,1打印预览单击【文件】选项卡|【打印】标签，在右侧即可显示打印总页数和当前页码，如图4-72所示。2打印工作表,4.6.4案例实战,经过对打印工作表的学习，小叶决定对“Contoso公司销售分析.xlsx”文件中“订单明细”工作表中数据清单的实施打印，来巩固Excel中的打印技巧。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）设置“订单明细”工作表的页面布局，使一条完整记录能在一行完全显示，并设置页眉页脚。（2）打印“订单明细”工作表中的数据清单。,4.7数据的保护,4.7.1工作表的保护1.设置允许用户进行的操作打开需要保护的工作表，单击【审阅】选项卡|【更改】组|【保护工作表】按钮。在打开的【保护工作表】对话框中选中【保护工作表及锁定的单元格内容】复选框。在【取消工作表保护时使用的密码】文本框中输入一个密码。选中允许用户进行的菜单前的复选框，单击【确定】按钮，如图所示。在【确认密码】对话框中重新输入一次密码。单击【确定】按钮，接着保存工作簿即可。,2.隐藏含有重要数据的工作表,4.7.2工作簿的保护,1.保护工作簿不能被修改单击【审阅】选项卡|【更改】组|【保护工作簿】按钮，打开【保护结构和窗口】对话框。分别选中【结构】复选框和【窗口】复选框，在【密码】文本框中输入一个密码，如右图所示。单击【确定】按钮，接着在打开的【确认密码】对话框中重新输入一遍密码，单击【确定】按钮。保存工作簿即可。,2.加密工作簿打开需要设置打开权限密码的工作簿。单击【文件】选项卡|【另存为】标签，打开【另存为】对话框。单击【工具】下拉按钮，在弹出的菜单中选择【常规选项】命令，如右图所示，打开【常规选项】对话框。,在【常规选项】对话框中的【打开权限密码】文本框中输入一个密码，如右图所示。单击【确定】按钮，在打开的【确认密码】对话框中再次输入密码，单击【确定】按钮，返回到【另存为】对话框。设置文件的保存位置和文件名，单击【保存】按钮保存文件。,4.7.3案例实战,经过对保护数据的学习，小叶结合自身手头上的工作，决定对“Contoso公司销售分析.xlsx”文件实施数据保护，防止他人随意查看、修改该分析文件。1.操作要求打开“Contoso公司销售分析.xlsx”文件，之后所有的操作均在该文件中进行。（1）对“订单明细”工作表中的数据进行保护，只允许用户进行选定操作。（2）对“Contoso公司销售分析.xlsx”文件进行加密，使用户在打开该文件和修改该文件时均使用密码方可进行。,谢谢！再见！,