Excel使用技巧大全ppt课件.ppt

Excel数据处理,1,总体学习内容,第一章Excel基础第二章数据录入第三章日常操作第四章数据格式化第五章公式与函数第六章图表处理第七章数据管理与数据透视表,2,第一章Excel基础,3,1本章学习目标,认识EXCEL的工作界面，窗口组成元素；了解工作薄等基本概念；掌握不同类型的数据输入方、工作表、单元格法；理解相对引用和绝对引用；掌握公式输入方法；掌握工作表的删除、插入、移动、改名等操作；掌握单元格、行、列常用操作；掌握sumcountif等简单函数的应用方法。,4,1.1Excel操作基础,（1）启动Excel的方法通过Windows任务栏选择“开始”按钮通过Excel快捷方式双击xcel文件的名字或图标（2）退出xcel的方法单击Excel窗口右上角的关闭按钮选择“文件”菜单中的“退出”菜单项。按快捷键Alt+F4。,5,（3）Excel界面,功能区,工作表区,1.1Excel操作基础,6,功能区的主要控件Excel2007用功能区取代了早期版本中的菜单、工具栏和大部分任务窗格，包括按钮、库和对话框内容。,1.1Excel操作基础,7,Office按钮：取代了早期版本中的“文件”菜单，单击时，将弹出与Excel早期版本“文件”菜单相似的菜单项，它是Excel2007中唯一的菜单，提供了常用的Excel文件操作功能，包括的菜单命令有“新建、打开、保存、另存为、打印、准备、发送、发布、关闭”。选项卡：功能区由“开始、插入、页面布局、公式、数据”等选项卡组成。各选项卡是面向任务的，每个选项卡以特定任务或方案为主题组织其中的功能控件。功能区中的选项卡是动态的，为了减少屏幕混乱，某些选项卡平时是隐藏的，在执行相应的操作时，它们会自动显示出来。组：Excel2007将以前版本中那些隐藏在菜单和工具栏中的命令和功能，以面向任务的方式放置在不同的逻辑组中，每个逻辑组能够完成某种类型的子任务。快速访问工具栏：其中包含一组独立于当前所显示的选项卡的命令，无论用户选择哪个选项卡，它将一直显示，为用户提供操作的便利。用户可以根据需要设置其中的工具按钮.,1.1Excel操作基础,8,工作表区的主要对象,1.1Excel操作基础,9,工作表：就是人们常说的电子表格，是Excel中用于存储和处理数据的主要文档。由一些横向和纵向的网格组成，横向的称为行，纵向的称为列。Excel2007的一个工作表最多可有1,048,576行、16,384列数据（而Excel2003的工作表最多有65536行、256列）。当前正在使用的工作表称为活动工作表。工作表标签：每个工作表有一个名称，体现在工作表标签上。单击工作表标签按钮可使对应的工作表成为活动工作表，双击工作表标签按钮可改变它们的名称。插入工作表标签按钮：在默认情况下，Excel只打开3个工作表：Sheet1，Sheet2，Sheet3.单击插入新工作表按钮就会后面插入一个新工作表sheet4，再单击一次就会插入新工作表sheet5,1.1Excel操作基础,10,行号、列标题：工作表由1048576行组成，每行的编号称为行号；工作表的每列用英文字母标志，称为列标题。单元格：工作表中的网格，它用列标题和行号定位。如A1就表示列第行对应的单元格。单元格区域：是指多个连续单元格的组合。表示形式为：左上角单元格:右下角单元格。例如A2:B3代表一个单元格区域，包括A2、B2、A3、B3只包括行号或列号的单元格区域代表整行或整列。如，1:1表示第一行的全部单元格组成的区域，1:5则表示由第1到5行全部单元格组成的区域；A:A表示第一列全部单元格组成的区域，A:D则表由A、B、C、D四列的全部单元格组成的区域。,1.1Excel操作基础,11,编辑栏：用于显示、修改活动单元格的内容。全选按钮：用于选定整个工作表所有的单元格。工作表查看方式：工作表下边框的右侧提供的三个按钮用于切换工作表的查看方式。工作表缩放：,1.1Excel操作基础,12,1.2构造第一个工作表,【例1.1】某单位的工资数据如下表所示，计算每个职工的总工资。假设个人所得税的税率为：收入1600以下，所得税率为0%；收入16002500，所得税率为5%；收入25003500，所得税率为8%；收入35004500，所得税率为12%；收入4500以上，所得税率为15%。计算各位职工应缴纳的所得税，以及缴税后的实际收入。,13,1.2构造第一个工作表,在EXCEL中可以轻松制作出例1-1的报表，如图所示,输入A1：H9中的原始档案数据,1、在I2中输入工资计算公式：=D2-E2-F2-G2+H22、向下复制此公式到I9,1、在H2中输入计算公式：=I2-K2、向下复制此公式到H9,1、在K2中输入工资计算公式：=I2*J22、向下复制此公式到K9,输入J列的税率,14,1.3工作薄和Excel文件管理,1.3.1工作薄与工作表的关系工作簿：在Excel中创建的文件叫做工作簿，扩展名是.xlsx，工作薄由工作表组成，新建工作薄只有默认的个工作表。但可以根据需要增加，个数不限。新建第一个工作薄的默认名：book1.xlsx。工作表：由一些横向和纵向的网格组成的表格。一个工作表最多有1,048,576行、16,384列。工作表由工作表标签区别，如sheet1、sheet2,15,1.3.1工作薄与工作表的关系,工作薄与工作表的关系,16,1.3.2创建新工作薄,1.3.2.1工作薄与模板模板：模板即模型、样板，是一种已经建立好的特殊工作薄，已在其中的工作表中设计好了许多功能，如单元格中的字体、字型，工作表的样式和功能（如财务计算，学生成绩统计，会议安排等。工作薄创建：Excel总是根据模板创建工作薄，创建的工作薄具有与模板结构、内容和功能。在没有指定建立工作薄模板的情况下，Excel会据默认模板建立工作薄，这种工作薄在默认情况下只有Sheet1、Sheet2和Sheet3三个工作表。,17,1.3.2.2新建默认的空工作薄，三种方法启动Excel，Excel就会自动建立一个名为Boolk1.xlsx;在已启动Excel的情况下，按Ctrl+N；单击功能区左上角的Office按钮，然后从弹出的文件菜单中选择“新建”；,1.3.2创建新工作薄,18,1.3.2.3根据旧工作薄建立工作薄（1）单击Office按钮，从弹出的文件菜单中选择“新建”。Excel将弹出“新建工作薄”对话框。（2）单击“根据已有内容新建”，然后在弹出的“根据现有工作薄新建”对话框找到并选择已有工作薄的名称，单击该对话框中的“新建”按钮。,1.3.2创建新工作薄,19,1.3.2.4根据模板建立工作薄Excel2007模板类型已安装的模板：安装Excel时安装在本地计算机中的模板；我的模板：用户自已创建的模板；在线模板：微软公司网站上的在线工作薄模板，在用这类模板创建新工作薄时，Excel会自动从微软的网站中下载相关模板到用户的本地计算机中，并用它创建新工作薄。当然，前提是用户计算机与Internet网络相连接（即Online，在线）。,1.3.2创建新工作薄,20,1.3.2.5在线模板示例下面的图表是用MicrosoftOfficeOnline”中的“预算”模板类中的“商务旅行预算”模板建立的工作薄。在该工作薄中，只需要输入D列的费用和F列的数字，Excel就会自动计算出H列的费用，这一功能是由“商务旅行模板”提供的。,1.3.2创建新工作薄,21,1.3.2创建新工作薄,下图是用“MicrosoftOfficeOnline”中的“库存控制”模板类中的“设备资产清单”模板建立的工作薄。在该工作薄中预设了许多功能，在L、N、P、Q、R列中都预设了计算公式，只要填定了其余列的数据，Excel就会自动计算出这些列的数据。,22,1.3.2.6在线模板的建立方法（1）确定计算机已连接上了Internet网络。（2）单击“Office按钮”新建MicrosoftOfficeOnline.将连接到微软的在线模板网站。（3）从微软网站的在线模板网站中选择需要的模板，就会从网站中将选择的模板下载到本机中，同时将应用。,1.3.2创建新工作薄,23,1.3.3保存和打开工作薄文件,1.3.3.1保存文件方法一：单击“快速访问工具栏”中的保存按钮保存文件。方法二：单击Office按钮，从弹出的文件菜单，选择“另存为”菜单项，然后在“另存为”对话框中输入文件名。1.3.3.2打开文件单击Office按钮，从弹出的文件菜单，选择“打开”菜单项，然后在“打开”对话框中选择要打开文件的名称。,24,1.3.4文件格式与兼容性,MicrosoftOffice2007引入了一种基于XML的新文件格式：MicrosoftOfficeOpenXMLFormats，适用于2007版的Word、Excel和PowerPoint。OfficeXMLFormats有许多优点：其一，文件自动压缩。某些情况下文档最多可缩小75%其二，OfficeXMLFormats格式以模块形式组织文件结构，是如果文件中的某个组件（如图表）受到损坏，文件仍然能够打开其三，更强的安全性。OfficeXMLFormats格式支持以保密方式共享文档，可避免用文档检查器工具获取信息。其四，更好的业务数据集成性和互操作性。在Office中创建的信息容易被其他业务应用程序所采用，只需一个ZIP工具和XML编辑器就可打开和编辑Office文件。,25,1.3.4文件格式与兼容性,2、Excel2007的文件扩展名和文件兼容性,26,1.4公式和单元格引用,1.4.1公式(1)公式的概念Excel的公式由运算符、数值、字符串、变量和函数组成。公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。例如：“=10+4*6/2+(2+1)*50”就是公式，可以在任何单元格中输入此公式Excel会把公式的计算结果显示在相应的单元格中。,27,（2）运算符在xcel公式中可以使用运算符。,1.4.1公式,28,1.4.3说明括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别的运算符从左到右依次进行。“;“零”;则当在此单元格中输入负数时，输入的负数就不会被显示出来（被隐藏了）。,167,4.4格式化数据,在格式码中指定的字符被用来作为占位符或格式指示符。0作为一个占位符，它在没有任何数字被显示的位置上显示一个0。符号“_)”跟在一个正数的格式后面，以保证这个正数右边会留下一个空格，空格的宽度与圆括号“）”的宽度一样宽。正数、负数和0靠单元格的右边线对齐。,168,4.4格式化数据,自定义格式码的例子,169,4.4格式化数据,6、Excel常用格式码的含义,170,4.4格式化数据,（7）自定义格式案例,171,4.4格式化数据,（8）自定义格式的方法选中要格式化的单元格。单击“开始”|“数字”组右边的下三角形选择弹出对话框的“数字”标签，然后选择“分类”列表框中的“自定义”选项。在“类型”文本框中，输入自定义格式。在单元格中输入数据，数据将以自定义格式显示。,172,4.5格式化日期,（1）概述在Excel中，日期和时间实际上是以序列数字的形式存储，以日期形式显示的。所以当一个单元格的格式被设置为日期格式后，即使在其中输入纯粹的数字，该数字也会被显示为一个日期。,173,4.5格式化日期,（2）日期的格式码,174,4.5格式化日期,（3）日期格式化案例,175,4.6条件格式,对于不同的数据，可按不同的条件设置其显示格式。例如，把学生成绩表中所有不及格成绩显示为红色，可以非常轻松地看出其中不及格的成绩情况；对于企业的销售表，把其中利润较小的或无利润的数据设置为黄色，而将利润最大的数据设置为绿色，可以使人在查看这些数据时一目了然。,176,4.6条件格式,（1）条件格式的意义对于不同的数据，可按不同的条件设置其显示格式。比如在一个学生成绩表中，把所有不及格的成绩显示为红色，就能够很容易找到有不及格科目的学生。下面是条件格式运用的一个例子：当单元格的数值大于200时，用黑色显示；当小于100时用红色显示；100200之间的数据用蓝色显示。此自定义条件的格式码如下：黑色=200#,#;红色=100#,#;蓝色#,#,177,4.6条件格式,（2）条件设置案例在下图所示的成绩表中，060之间，6080之间，80100之间的分数分别以不同的颜色表示和背景表示，便于查询不同分数段的信息。,178,4.6.1Excel2007条件格式概述,条件格式指基于某种条件更改单元格区域中数据的表现形式，如果条件成立就基于该条件设置单元格区域的格式（如设置单元格的背景、用图形符号表示数据），如果条件不成立就不设置单元格区域的格式。Excel2007的条件格式Excel2007对条件格式进行了较大的改进，对条件格式的任务分得更细，涉及的内容更多，也更具实用性。Excel2007的条件格式更容易达到以下效果：突出显示所关注的单元格或单元格区域；强调异常值；使用数据条、颜色刻度和图标集来直观地显示数据。,179,4.6.1Excel2007条件格式概述,Excel2007条件格式的内容,180,4.6.1Excel2007条件格式概述,【例4.8】某中药材公司2009年的药材销售利润表如下图所示。在下图所示工作表中突出显示以下信息：利润小于20的药材销售情况，销售利润最高或最低的10种药材是哪些，用条型图直观显示出各药材在各省的销售情况，用不同的图形显示出各药材在各省的销售情况，标识出利润占前30%的药材销售情况。在Excel2007中，通过条件格式可以轻松地实现上述要求。条件格式位于“开始”选项卡的“样式”组中，单击该组中的“条件格式”按钮，将显示条件格式的命令列表，如图4.27所示。,181,4.6.1Excel2007条件格式概述,突出显示利润小于20的药材,用数据条显示单元格数据,突出显示利润10%的药材,用图标集和色阶显示不同比例部分的数据,182,4.6.1Excel2007条件格式概述,Excel2007条件格式中的几个概念（1）突出显示单元格规则对单元格区域设置一定的条件，将按指定的现则突出显示该区域中满足条件的单元格内容，默认的规则用某种色彩填充单元格背景。例如，如果单元格的值大于、小于或等于某个指定值，就将单元格的背景填充为绿色，突出显示该单元格中的值。（2）项目选取规则对选中单元格区域中小于或大于某个给定阈值的单元格实施条件格式。单击此规则中的“值最大的10项”、“值最大的10%项”、“高于平均值”等条件，Excel都会显示出一个条件设置对话框，可通过它设置单元格中的条件格式。,183,4.6.1Excel2007条件格式概述,（3）数据条数据条以彩色条型图直观地显示单元格数据。数据条的长度代表单元格中的数值。数据条越长，表示值越高，数据条越短，表示值越低。（4）色阶色阶用颜色的深浅表示数据的分布和变化，包括双色阶和三色阶。双色刻度使用两种颜色的深浅程度比较某个区域的单元格。颜色的深浅表示值的高低。（5）图标集使用图标集可以对数据进行注释，并可以按阈值将数据分为三到五个类别。每个图标代表一个值的范围，其形状或颜色表示的是当前单元格中的值相对于使用了条件格式的单元格区域中的值的比例。,184,4.6.2条件格式规则,（1）条件格式规则Excel2007中的条件格式是根据一组规则来实施完成的，这组规则称为条件格式规则。（2）条件格式规则类型基于各自值设置所有单元格的格式只为包含以下内容的单元格设置格式仅对排名靠前或靠后的数值设置格式仅对高于或低于平均值的数值设置格式仅对唯一值或重复值设置格式使用公式确定要设置格式的单元格,185,单击“开始”选项卡中的“条件格式”按钮，从弹出的菜单中选择“新建规则”,186,4.6.3自定义条件格式,默认的条件格式能够解决大多数问题，但有些时候并不如愿，例如默认的图标集用三种图标表示不同比例的数据：前33%、中33%和后33%各用一种图表表示，如果希望的比例为前15%、中45%后40%各用一种图标表示。就需要更改条件格式的规则，这可以通过自定义条件格式完成。,187,4.6.3自定义条件格式,【例4.9】某校采用学评教制度，学生对其任课教师进行评分。某次学评教成绩如图4.33左图所示。对该工作表进行格式化，标识出学评教分数最差的20%，最好的15%，以及中间的学评教成绩，以便于实施奖励与惩罚。,最好的15%最后的20%！中间的65%,188,4.6.3自定义条件格式,（1）自定义条件格式的建立过程,（1）选择要建立条件格式的区域，单击“开始”“样式”组“条件格式”“新建规则”命令。弹出图示“新建格式规则”对话框。,（2）在“格式样式”列表中选择条件格式类型,（3）在“类型（T）”的列表中选择“百分点值”，然后在“值”下面的文框中输入值的范围，如图所示。,189,4.6.3自定义条件格式,（2）重复应用条件格式在Excel2007中，对同一单元格区域，可以多次应用不同的条件格式。例如，对一个已经应用了色阶的单元格区域，可以再次应用图标集、数据条等条件格式。自定义条件格式也可以叠加，请看下面的例子。【例4.10】某班期末考试如图4.35左图所示。对它进行格式化，用红色粗体显示060之间的成绩，对90100之间的单元格设置背景填充图案。,190,条件格式规则1：060分为红色，加粗，倾斜,条件格式规则2：90分以下填充图案背景,4.6.3自定义条件格式,191,（1）选中C3:E12区域，单击“开始”“条件格式”|“突出显示单元格规则”|“小于”命令，,（2）在“小于”中输入60，然后在“设置为”的下拉列表中选择“自定义格式”，Excel将弹出“设置单元格格式”对话框，,（3）单击“字体”选项，选中“字形”中的“加粗倾斜”，并从“颜色”列表中选择红色。,（4）再次选中C3:E12区域。设置大于90的条件条件格式。通过“设置单元格格式”|“填充”对话框中的“图案样式”列表中，为成绩大于90的单元格选择一背景填充图案。,192,4.6.4条件格式规则的管理,（1）条件格式的限制在Excel2007中，条件格式最多可以包含六十四个条件。（2）条件格式的优先级当对相同的单元格或区域设置了多重条件后，有可能引起条件规则的冲突。例如，一个规则将单元格字体颜色设置为红色，而另一个规则将单元格字体颜色设置为绿色。因为这两个规则冲突，所以只应用一个规则。但到底应用哪个规则呢，要根据各条件规则的优先级来确定（3）条件格式规则管理条件规则的优先级可通过“条件格式规则管理器”对话框进行设置和管理，通过此对话框，还可以在工作簿中创建、编辑、删除和查看所有条件格式规则。,193,1、本题应用了图标集、色阶、数据条等多重条件,2、单击“开始”“样式”组“条件格式”旁边的箭头“管理规则”。Excel将弹出“条件格式规则管理器”对话框,3、选择“新建规则”或以建立新的规则；选择“编辑规则”可对条件格式进行修改,5、单击上、下箭头可以提高或降低当前稳定中条件格式规则的优先级,4、条件格式规则：这些规则都是应用于同一单元格或区域的，排在前面的优先级高；排在后面的优级低，可以通过“删除规则”右边的上下箭头移动规则，更改优先级,4.6.4条件格式规则的管理,194,第五章公式与函数,195,（1）公式与函数基础（2）文本函数（3）信息与逻辑函数（4）日期与时间函数（5）查找与引用函数（6）数学计算函数（7）统计与求和函数（8）财务与工程函数,本章主要学习内容,5.1.1公式与函数的概念,公式是对工作表中的数值执行计算的等式。公式以等号(=)开头。例如，在单元格中输入公式：=5+2*3，结果等于11,函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。,5.1公式与函数基础,197,5.1.2公式的组成包含下列部分或全部内容：常量、运算符、函数和引用,函数：PI()函数返回值：3.141.引用：A2返回单元格A2中的值。常量：直接输入公式中的数字或文本值，如2。运算符：（脱字号）运算符表示将数字乘方，*（星号）运算符表示相乘。,5.1公式与函数基础,198,（1）公式中的常量,五种数据类型（EXCEL和VisualBasic数据类型的比较）,5.1公式与函数基础,199,5.1公式与函数基础,数值型,200,5.1公式与函数基础,日期、文本、逻辑及错误值,201,5.1公式与函数基础,数值类型转换,202,5.1公式与函数基础,203,（2）公式中的运算符（算术、比较、文本连接和引用）,算术运算符,5.1公式与函数基础,204,当用这些运算符比较两个值时，结果为逻辑值：TRUE或FALSE逻辑值的运算通过函数实现（AND()，OR()，NOT()）,5.1公式与函数基础,比较运算符,205,text1&text2.与函数CONCATENATE(text1,text2,.)运行结果等同,5.1公式与函数基础,文本运算符,206,EXCEL独有的运算符，对单元格区域进行合并计算,5.1公式与函数基础,引用运算符,207,相同级别的运算符，按从左至右计算用括号()改变运算次序,5.1公式与函数基础,运算符的优先级,208,（3）公式中的函数,函数结构。函数的结构以等号(=)开始，后面紧跟函数名称和左括号，然后以逗号分隔输入该函数的参数，最后是右括号。函数名称。如果要查看可用函数的列表，可单击一个单元格并按Shift+F3。函数参数。参数可以是数字、文本、TRUE或FALSE等逻辑值、数组、错误值或单元格引用。指定的参数都必须为有效参数值。参数也可以是常量、公式或其他函数。输入提示。在键入函数时，会出现一个带有语法和参数的工具提示。例如，键入=ROUND()时，工具提示就会出现。工具提示只在使用内置函数时出现。,5.1公式与函数基础,函数的结构,209,有效的返回值当嵌套函数作为参数使用时，它返回的数值类型必须与参数使用的数值类型相同。例如，如果参数返回一个TRUE或FALSE值，那么嵌套函数也必须返回一个TRUE或FALSE值。否则，MicrosoftExcel将显示#VALUE!错误值。嵌套级别限制公式可包含多达七级的嵌套函数。当函数B在函数A中用作参数时，函数B则为第二级函数。Excel2003嵌套级别限制为7层，Excel2007嵌套级别限制为64层,AVERAGE和SUM函数嵌套在IF函数中。,观察键入函数的运行结果：先选中后按F9键。按Ctrl+Z键取消,5.1公式与函数基础,函数的嵌套,210,（4）公式中的引用,A1引用样式,列标在前（从A到XFD，共214=16,384列）行号在后（从1到220=1,048,576）。,5.1公式与函数基础,211,相对引用：如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制或填充公式，引用会自动调整,绝对引用：如果公式所在单元格的位置改变，绝对引用将保持不变,混合引用：具有绝对列和相对行或绝对行和相对列。,在输入时按F4键可自动快速切换,5.1公式与函数基础,相对引用、绝对引用和混合引用,212,引用名为“市场”的工作表引用B1和B10之间的单元格（包括B1和B10）工作表的引用符号“!”,5.1公式与函数基础,引用其他工作表中的单元格,213,便于引用多个工作表分析同一工作簿中多个工作表上相同单元格或单元格区域中的数据三维引用包含单元格或区域引用，前面加上工作表名称的范围。Excel使用存储在引用开始名和结束名之间的任何工作表。例如，,=SUM(Sheet3:Sheet6!B5),将计算B5单元格内包含的所有值的和，单元格取值范围是从工作表3到工作表6,5.1公式与函数基础,三维引用样式,214,行号在前（从1到220=1,048,576行）列号在后（从1到214=16,384列）,5.1公式与函数基础,R1C1引用样式,215,（5）数组,5.1公式与函数基础,数组的概念,216,5.1公式与函数基础,输入、更改及扩展数组公式,217,函数存储数组运算符数组常量,按Ctrl+Shift+Enter输入数组公式。上面的公式等价于：=SUM(A1*1,B1*2,C1*3,D1*4,E1*5),A1至E1单元格输入3,4,5,6,7,5.1公式与函数基础,常量数组,218,5.2文本函数,219,5.2文本函数,220,5.2文本函数,221,5.2文本函数,222,5.2文本函数,223,5.2文本函数,224,5.2文本函数,225,5.2文本函数,226,5.2文本函数,227,5.2文本函数,228,5.2文本函数,229,5.2文本函数,230,5.2文本函数,231,5.2文本函数,232,5.3信息与逻辑函数,233,5.3信息与逻辑函数,234,5.3信息与逻辑函数,235,5.3信息与逻辑函数,236,5.4日期与时间函数,237,5.4日期与时间函数,238,5.4日期与时间函数,239,5.4日期与时间函数,240,5.4日期与时间函数,241,5.4日期与时间函数,242,5.4日期与时间函数,243,5.4日期与时间函数,244,5.4日期与时间函数,245,5.4日期与时间函数,246,5.4日期与时间函数,247,5.4日期与时间函数,248,5.4日期与时间函数,249,5.5查找与引用函数,250,5.5查找与引用函数,251,5.5查找与引用函数,252,5.5查找与引用函数,253,5.5查找与引用函数,254,5.5查找与引用函数,255,5.5查找与引用函数,256,5.5查找与引用函数,257,5.5查找与引用函数,258,5.5查找与引用函数,259,5.5查找与引用函数,260,5.5查找与引用函数,261,5.6数学计算,262,5.6数学计算,263,5.6数学计算,264,5.6数学计算,265,5.6数学计算,266,5.6数学计算,267,5.6数学计算,268,5.6数学计算,269,5.6数学计算,270,5.7统计与求和,271,5.7统计与求和,272,5.7统计与求和,273,5.7统计与求和,274,5.7统计与求和,275,5.7统计与求和,276,第六章图表处理,277,本章学习目标,（1）了解Excel图表的组成元素（2）了解Excel的图表类型和图表功能（3）掌握图表的制作过程（4）掌握图表的修改方法（5）掌握格式化数据系列的方法（6）掌握复杂图表的建立方法（7）了解分析工具库中的图表功能,278,概述,Excel的图表功能并不逊色于一些专业的图表软件，它不但可以创建条形图、折线图、饼图这样的标准图形，还可以生成较复杂的三维立体图表。Excel提供了许多工具，用户运用它们可以修饰、美化图表，如设置图表标题，修改图表背景色，加入自定义符号，设置字体、字型等利用Excel的图表向导可以快捷了建立各种类型的图表。,279,6.1认识Excel图表,（1）图表组成,280,二、图表术语数据点数据系列网格线轴刻度线与刻度线标志误差线图例图表中的标题,6.1认识Excel图表,281,6.2图表类型,（1）嵌入式图表和图表工作表嵌入式图表是把图表直接插入到数据所在的工作表中，主要用于说明数据与工作表的关系，用图表来说明和解释工作表中的数据.,282,图表工作表图表与源数据表分开存放，图表放在一个独立的工作表中，图表中的数据存在于另一个独立的工作表中,6.2图表类型,283,（2）Excel的图表类型Excel2007的标准类型提供包括直线图、面积图、折线图、柱形图等图形种类，大约10多种类型。每种图表类型中又包括多种二维的平面图形和三维的立体图形,6.2图表类型,284,（2）Excel标准图表类型面积图：面积图用于显示不同数据系列之间的对比关系，同时也显示各数据系列与整体的比例关系，尤其强调随时间的变化幅度,6.2图表类型,285,6.2图表类型,柱形图：柱形图也就是常说的直方图，柱形图用于表示不同项目之间的比较结果，也可以说明一段时间内的数据变化。,286,6.2图表类型,条形图：条形图显示了各个项目之间的比较情况，纵轴表示分类，横轴表示值。它主要强调各个值之间的比较，并不太关心时间,287,折线图：折线图常用于描绘连续数据系列，对于确定数据的发展趋势比较有用。线性图表示数据随时间而产生的变化情况。线性图的分类轴常常是时间，如年、季度、月份、日期等,6.2图表类型,288,6.2图表类型,饼图和圆环图：饼图和圆环图都常用于表示总体与部分比例关系，以直观的图形方式表示出各部分与总体的百分比。饼图只能表示一个数据系列，而圆环图可以包含多个数据系列。,289,6.2图表类型,X-Y散点图：X-Y散点图主要用来比较在不均匀时间或测量间隔上的数据变化趋势（如果间隔均匀，应该使用折线图）。,290,6.2图表类型,气泡图：气泡图实质上是一种XY散点图。数据标记的大小反映了第三个变量的大小。气泡图的数据应包括三行或三列，将X值放在一行或一列中，并在相邻的行或列中输入对应的Y值，第三行或列数据就表示气泡大小。,291,6.3图表的建立,（1）图表建立案例【例6.1】某录像带租借店经营以下几种类型的录像带：武打片、爱情片、恐怖片、枪战片、喜剧片，现要统计各类录像带的利润，并以饼图来表示各类录像带的利润比例。第一步骤：建立数据表,292,6.3图表的建立,步骤二：选择图表类型选择“插入”选项卡,步骤三：在图表组中找到需要的图表，单击它可显示出该类图表中的所有类型,步骤四：如果在图表组中没历找到需要的图表类型，单击“其它类型”可显示出所有图表类型,单击图表组中“其它类型”显示出的图表对话框,293,6.3图表的建立,最终建立的饼图,294,6.4图表设计,Excel2007图表功能的改进Excel2007在图表方面比以前的版本有了较大的改进，简化了图表向导的过程。并为图表提供的“设计”和“布局”功能选项卡，可以轻松完成图表的各种修改，设计出布局合理，内容恰当的高质量图表。能够方便地进行标题，数据系列，图例显示位置，图表中的文字大小等内容进行修改。,295,6.4.1图表布局,应用Excel预定义的图表布局和图表样式设置图表，能够很轻松地创建具有专业外观的图表。应用预定义的图表布局时，会有一组特定的图表元素，如标题（图表标题是说明性的文本，可以自动与坐标轴对齐或在图表顶部居中）、图例、数据表或数据标签（为数据标记提供附加信息的标签，数据标签代表源于数据表单元格的单个数据点或值）按特定的排列顺序在图表中显示，可以从为每种图表类型提供的各种布局中进行选择。,296,单击图表，显示出图表工具,单击布局选项卡,“布局”工具，可以设置图表标题，添加数据标签，设置或修改坐标轴，添加图表背景图等，操作非常方便,297,6.4.1图表布局,【例6.2】例6.1建立的图表，没有标题，也没有在饼图中显示出各部分所占的百分比，现在通过图表布局为该图添加标题和数据系列标志。,298,6.4.2图表标题和图表式样,（1）图表标题在默认情况下插入的图表没有标题，按指定的图表布局式样设置图表可以给图表添加标题。但有的图表布局式样并没有标题，图6.7中的第四个饼图就是这种情况。在这种情况下，可以通过“图表工具”|“布局”选项卡“标签”组中的“标题”按钮为图表设置标题。（2）图表式样Excel2007为各种类型的图表都提供了许多图表样式，应用这些图表样式可以很快设计出精美的图表来。Excel提供的图表样式中有二维样式，也有三维图形的样式，从黑白到各种彩色图表样式应用尽有。,299,6.4.2图表标题和图表式样,饼图的“图表式样”,激活设计的饼图后，用鼠标指向饼图式样中的某个图形，就会立即看出图表实施该式样后的效果，发现满意的式样后，单击鼠标就可以应用该式样到图表上。,300,6.4.3修改图表类型,对于大多数二维图表（包括嵌入式图表和图表工作表），可以更改整个图表的图表类型以赋予其完全不同的外观，也可以为任何单个数据系列选择另一种图表类型，使图表转换为组合图表。对于气泡图和大多数三维图表，只能更改整个图表的图表类型。【例6.3】图6.9中的第一个图表是某班期末考试的学生成绩柱形图。由于成绩数据较多，该图显得非常零乱（有太多的“柱子”）。现对该图进行修改，用柱形堆积图来表示各位同学的成绩，如图6.9中的第二个图表所示。,301,单击”更改图表类型”,弹出图表对话框,从中选择需要的图表类型,单击图表,显示出”图表工具”,302,6.4.4数据系列操作,（1）添加数据系列在Excel中可以添加图表的数据系列。如果是嵌入式图表，可先在工作表中选定要添加的数据，然后直接将数据拖到嵌入式图表中；如果正在使用图表工作表，可以使用“图表”菜单中的“添加数据”菜单项。,303,6.4.4数据系列操作,案例：【例6.4】某酒厂生产的酒主要销售在四川、重庆、山东等地，图6.10是2002年、2003年各地的销售表及销售利润柱形图。现在要把2004年的销售利润加入该柱形图中。,304,现在将2004年的销售利润加入该柱形图中。,6.4.4数据系列操作,1.输入第5行数据；2.单击图表中的数据系列,当看见数据表的周围出现边框线时,向下拖动边框组,使它包括第5行数据就行了,305,6.4.4数据系列操作,（2）删除数据系列单击图表中要删除的数据系列。按Delete键或者直接删除数据表中的对应数据行（或列）,306,（3）插入文本可以在图表中加入文本信息，以对图表中的某些信息进行解释，使图表所蕴藏的信息更易被接受。,1.插入一文本框。2.在文本框中输入文字3.画箭头线,6.4.4数据系列操作,307,6.4.4数据系列操作,（4）修改数据系列的产生方式在Excel中插入图表时，数据系列一般按行方式构成，但也可以按列方式形成，即据列中的数据生成图表。如果要按列方式组织数据系列，就需要修改数据系列的产生方式。【例6.5】某酒厂20022004年间在四川、山东、重庆的销售利润如图6.12的A1:D5所示。现在需要按年份对各地区的销售利润进行比较，制作各地区在每年的利润对比柱形图，如图6.12（b）所示，图表中的同一数据系列采用相同的色彩，因此可以看出图6.12（b）的数据系列是按列构造的，即同一地区的数据是一个数据系列。,308,激活图表单击”切换行/列“可以更改图表的数据系列产生方式,以行方式构成数据系列的图表,以列方式构成数据系列的图表,309,6.5图表布局,图表布局是指影响图表标题、图例、坐标轴、坐标轴标题、网格表、图表标注和误差线等图表元素的选项组合，合理布局可使图表表达的信息准确、完善和美观，并能突出显示某些重点信息。,310,6.5.1数据标签,添加数据标筌,没有标志的图表,添加了标志的图表,311,选中图表,单击数据标签从弹出的菜单中选择一种标签类型,312,6.5.2图例和数据表,图例处理在Excel中可以删除、添加图例，还可以修改图例在图表中的位置。单击进行图例操作的图表显示出“图表工具”，然后再单击“布局”选项卡“标签”组中的|“图例”按钮，Excel会弹出设置图例的菜单命令列表。,313,314,6.5.3图表网格线,为了便于看清楚图表中的数据，可以在图表的绘图区中显示水平和垂直网格线。网格线包括为主要网格线和次要网格线，主网格线是从坐标轴的主要刻度向水平或垂直方向延伸出来的直线，次网格线是从坐标轴上的次要刻度延伸出来的直线，它们分别与坐标轴上显示的主要和次要刻度线对齐。,315,6.5.4坐标轴,坐标轴是界定图表绘图区的线条，用作度量的参照框架，通常包括水平轴（X轴）和垂直轴（Y轴）。Y通常包含数据，称为数值轴；X轴通常包含分类，也称分类轴。三维图表还有第三个坐标轴，即竖坐标轴，也称系列轴或z轴。一般的图表通常用Y轴对数据进行大小度量，用X轴对数据进行分类。不是所有图表类型都以相同方式显示坐标轴，如xy散点图和气泡图在水平轴和垂直轴上都显示数值，雷达图没有水平（分类）轴，而饼图和圆环图没有任何坐标轴。,316,修改横坐标轴的显示方向或纵坐标轴的刻度单位的方法如下：（1）单击图表显示出“图表工具”选项卡；（2）单击“布局”选项卡“坐标轴”组中的“坐标轴”命令按钮，Excel会弹出包括有“主要横坐标轴”和“主要纵坐标轴”命令的快捷菜单。（3）根据需要（修改横坐标轴还是纵坐标轴）选择对应的菜单项，然后再从弹出的二级菜单中选择相应的坐标轴设置命令。,默认坐标轴,添加了坐标轴标题,坐标轴靠右边,重置坐标轴刻度值,6.5.4坐标轴,317,6.6格式化图表,6.1.1格式化图表的任务在利用Excel预定义的图表布局和图表样式对插入图表进行初步处理之后，图表可能还会有一些不恰当的地方。如例，图例的位置和大小不当，标题字体太小，色彩搭配不当，数据标签字体大小不合理，坐标轴刻度间距太小等，通过图表格式化功能对不满意的图表部分进行修改，很快就能制作出精美的图表。添加或取消垂直线、趋势线等使图表更加美观,318,6.6格式化图表,6.6.2改变图表颜色、图案、边框在Excel中对图表颜色、图案、边框的设置非常简单，用鼠标双击要修改的图形对象，系统会弹出设置该图形对象格式的对话框。通过该对话框就能进行相应图形对象的格式化。,319,6.6.3图表格式化内容各个图表对象（如标题、坐标轴、绘图区、图表区及图例）的默认颜色、图案、边框等的设置都非常简单，为了取得较好的图形效果，可以对它们进行格式化或重新设置。对图表对象的格式化或重新设置的主要方法包括填充和添加效果。填充改变的是图表对象的内部颜色。在更改图表对象的填充颜色时，还可以向填充添加纹理、图片或渐变。渐变是颜色和底纹的逐渐过渡，通常是从一种颜色过渡到另一种颜色，或者从一种底纹过渡到同一颜色的另一种底纹。,6.6格式化图表,320,（1）填充对各类图表对象的填充方法基本相同，它们的格式化对话框也大同小异。过程如下：用鼠标右键单击要格式化的图表对象从弹出的快捷菜单中选择“设置XX格式”，Excel将会弹出“设置XX对象格式”的对话框，通过该对话框就能够完成对象各种格式的设置。,6.6.3图表格式化内容,321,【例6.7】某地四座高山的海拔高度如图6.19的A1:D3区域所示。图6.19（a）是用颜色渐变方式填充了背景墙的三维锥形图。图6.19（b）是用纹理图片填充了图表区域，并用单色填充一数轴的三维锥形图表。图6.19（c）用图片填充了数据系列的锥形图表。,6.6.3图表格式化内容,322,用鼠标右击图表的绘图区域，然后从弹出的快捷菜单中选择“设置背景墙格式”菜单命令，弹出“设置背景墙格式”对话框,通过此对话框可以设置图表的各种格式,填充图表,添加背景图片,设置光学效果等,323,（2）效果填充只影响图表对象的内部或正面，而效果（如阴影、发光、反射、柔化边缘、棱台和三维(3-D)旋转等）则可用来更改形状的外观。三维效果还可增加形状的深度，在Excel2007中可以向图表对象添加内置的三维效果组合，也可以添加单个效果。在图6.21中，图(a)中的标题应用了形式状样式效果中的“强调颜色6”，图(b)中的标题首先应用了艺术字样式，然后再应用了形状效果中的某种“发光”效果，图(c)中的图表区应用了形状效果中的某种“透视”效果。,6.6.3图表格式化内容,324,325,6.6.4数据系列在纵坐标轴上的对齐设置,关于数据系列的对齐方式在默认情况下插入的图表，其各数据系列中的第一个数据点常常不在纵坐标轴的刻度线上，如图6.22（a）所示。但在实际应用中，常常需要将某些类别图表（如折线图、柱形图、XY散点图、股价图、曲面图等）中数据系列的第一个数据点绘制在纵坐标轴的刻度线上，如图6.22（b）所示。【例6.9】某书店的图书销售记录如图6.22的A1:M4所示，制作销售折线图，并让折线图中的数据系列中的第一个数据点位于纵坐轴上。,326,6.6.4数据系列在纵坐标轴上的对齐设置,默认折线图,第一个数据点并不在Y轴上,通过”设置坐标轴格式”将第一个数据点绘画制在Y轴上,327,6.7复杂图的建立,6.7.1添加涨跌柱线、垂直线、系列线等涨跌线是具有两个以上数据系列的折线图中的条形柱，可以清晰地指明初始数据系列和终止数据系列中数据点之间的差别。系列线是在二维堆积条形图和柱形图中，每个数据系列中连接数据标记的线条。垂直线是在折线图和面积图中，从数据点到分类(X)轴的垂直线。高低点连接线是在二维折线图中，每个分类中从最高值到最低值之间的连线。使用高低点连线可以表示数值变化的范围。,328,添加添跌线前的图表,添加添跌线后的图表,单击要添加”涨/跌柱线”的数据系列,再单击”涨/跌柱线”按钮,329,趋势线以图形的方式显示了数据的发展趋势，它常用于预测分析（也称回归分析，在图表中扩展趋势线，根据实际数据预测未来数据）。支持趋势线的图表类型有非堆积型二维面积图、条形图、柱形图、折线图、股价图、气泡图和X-Y散点图，不能向三维图表、堆积型图表、雷达图、饼图、圆环图中添加趋势线。【例6.10】图6.24（a）是某公司2001年至2004年电视销售利润的折线图，根据该折线图作出公司销售利润的趋势线，并估计2005年、2006年利润的发展趋势。,6.7.2添加趋势线,6.7.2添加趋势线,单击图表的数据系统以激活图表,单击”布局”选项卡中的”趋势线”命令,从下拉菜单中选中一种趋势线类型,利用趋势线的公式可以预测未来值,331,6.7.3建立组合图,组合图表是指在一个图表中表示两个或两个以上的数据系列，不同的数据系列用不同的图表类型表示。建立方法：右击某一数据系列，从弹出的快捷功能单中，选择“更改系列图表类型”，就可为该数据系列设置新的图表类型。,柱形图,柱形图和折线图的组合图,332,6.7.3.1建立双轴图,双Y轴组合图：有时为了便于对比分析某些数据，需要在同一图表中表达几种具有一定相关性的数据。但由于数据的衡量单位不同，所以就很难清晰地表达图表的意图。双轴图能够解决这一矛盾。,333,6.7.3.1建立双轴图,1、右击图标中的某一数据系列,将它修改为另一种图表类型,即组合图,2、右击要使用次坐轴的数据系列,从弹出的菜单中选择”设置数据系列格式”菜单,弹出”设置数据系列格式”对话框,3、选择”系列选项”中的“次坐标轴”,334,6.7.3.2建立图片图表,图片图表：使用图片代替柱形图、条形图或折线图中默认的图形标记，就更容易理解和辨别图表所蕴藏的信息。建立方法,1.打开图片文件，并复制它。2.用鼠标单击数据系列，选中该数据系列。3.单击常用工具条中的“粘贴”按钮，结果如右图所示。,335,6.7.3.3建立甘特图,甘特图：是一种以工作任务和任务完成时间为依据的图形，主要用来表示工作项