Excel与数据处理(第3版)[电子教案].ppt

第1章Excel基础,Excel与数据处理第3版,本章学习目标,认识EXCEL的工作界面，窗口组成元素；了解工作薄、工作表、单元格等基本概念；掌握不同类型的数据输入方法；理解相对引用和绝对引用；掌握公式输入方法；掌握工作表的删除、插入、移动、改名等操作掌握单元格、行、列常用操作；掌握sumcountif等简单函数的应用方法,1.1Excel操作基础,1启动Excel的方法通过Windows任务栏选择“开始”按钮通过Excel快捷方式双击xcel文件的名字或图标、退出xcel的方法单击Excel窗口右上角的关闭按钮选择“文件”菜单中的“退出”菜单项。按快捷键Alt+F4。,1.1Excel操作基础,3、Excel界面,功能区,工作表区,1.1Excel操作基础,功能区的主要控件Excel2007用功能区取代了早期版本中的菜单、工具栏和大部分任务窗格，包括按钮、库和对话框内容。,功能区的主要控件,Office按钮取代了早期版本中的“文件”菜单，单击时，将弹出与Excel早期版本“文件”菜单相似的菜单项，它是Excel2007中唯一的菜单，提供了常用的Excel文件操作功能，包括的菜单命令有“新建、打开、保存、另存为、打印、准备、发送、发布、关闭”。选项卡功能区由“开始、插入、页面布局、公式、数据”等选项卡组成。各选项卡是面向任务的，每个选项卡以特定任务或方案为主题组织其中的功能控件。能区中的选项卡是动态的，为了减少屏幕混乱，某些选项卡平时是隐藏的，在执行相应的操作时，它们会自动显示出来。组Excel2007将以前版本中那些隐藏在菜单和工具栏中的命令和功能，以面向任务的方式放置在不同的逻辑组中，每个逻辑组能够完成某种类型的子任务。快速访问工具栏其中包含一组独立于当前所显示的选项卡的命令，无论用户选择哪个选项卡，它将一直显示，为用户提供操作的便利。用户可以根据需要设置其中的工具按钮.,1.1Excel操作基础,工作表区的主要对象,1.1Excel操作基础,工作表就是人们常说的电子表格，是Excel中用于存储和处理数据的主要文档。由一些横向和纵向的网格组成，横向的称为行，纵向的称为列。Excel2007的一个工作表最多可有1,048,576行、16,384列数据（而Excel2003的工作表最多有65536行、256列）。当前正在使用的工作表称为活动工作表。工作表标签每个工作表有一个名称，体现在工作表标签上。单击工作表标签按钮可使对应的工作表成为活动工作表，双击工作表标签按钮可改变它们的名称。插入工作表标签按钮在默认情况下，Excel只打开3个工作表：Sheet1，Sheet2，Sheet.单击插入新工作表按钮就会后面插入一个新工作表sheet4，再单击一次就会插入新工作表sheet5,1.1Excel操作基础,行号、列标题工作表由1048576行组成，每行的编号称为行号工作表的每列用英文字母标志，称为列标题单元格工作表中的网格，它用列标题和行号定位。如A1就表示列第行对应的单元格单元格区域是指多个连续单元格的组合。表示形式如下：左上角单元格:右下角单元格例如A2:B3代表一个单元格区域，包括A2、B2、A3、B3只包括行号或列号的单元格区域代表整行或整列。如，1:1表示第一行的全部单元格组成的区域，1:5则表示由第1到5行全部单元格组成的区域；A:A表示第一列全部单元格组成的区域，A:D则表由A、B、C、D四列的全部单元格组成的区域。,1.1Excel操作基础,编辑栏用于显示、修改活动单元格的内容全选按钮用于选定整个工作表所有的单元格。工作表查看方式工作表下边框的右侧提供的三个按钮用于切换工作表的查看方式工作表缩放,1.2构造第一个工作表,【例1.1】某单位的工资数据如下表所示，计算每个职工的总工资。假设个人所得税的税率为：收入1600以下，所得税率为0%；收入16002500，所得税率为5%；收入25003500，所得税率为8%；收入35004500，所得税率为12%；收入4500以上，所得税率为15%。计算各位职工应缴纳的所得税，以及缴税后的实际收入。,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列的税率,1.3工作薄和Excel文件管理,1.3.1工作薄与工作表的关系工作簿在Excel中创建的文件叫做工作簿，扩展名是.xlsx工作薄由工作表组成，新建工作薄只有默认的个工作表。但可以根据需要增加，个数不限。新建第一个工作薄的默认名：book1.xlsx。工作表由一些横向和纵向的网格组成的表格。一个工作表最多有1,048,576行、16,384列。工作表由工作表标签区别，如sheet1、sheet2,1.3.1工作薄与工作表的关系,工作薄与工作表的关系,1.3.2创建新工作薄,1、工作薄与模板模板模板即模型、样板，是一种已经建立好的特殊工作薄，已在其中的工作表中设计好了许多功能，如单元格中的字体、字型，工作表的样式和功能（如财务计算，学生成绩统计，会议安排等。工作薄创建Excel总是根据模板创建工作薄，创建的工作薄具有与模板结构、内容和功能。在没有指定建立工作薄模板的情况下，Excel会据默认模板建立工作薄，这种工作薄在默认情况下只有Sheet1、Sheet2和Sheet3三个工作表。,1.3.2创建新工作薄,2、新建默认的空工作薄，三种方法启动Excel，Excel就会自动建立一个名为Boolk1.xlsx;在已启动Excel的情况下，按Ctrl+N；单击功能区左上角的Office按钮，然后从弹出的文件菜单中选择“新建”；,1.3.2创建新工作薄,3、根据旧工作薄建立工作薄（1）单击Office按钮，从弹出的文件菜单中选择“新建”。Excel将弹出“新建工作薄”对话框。（2）单击“根据已有内容新建”，然后在弹出的“根据现有工作薄新建”对话框找到并选择已有工作薄的名称，单击该对话框中的“新建”按钮。,1.3.2创建新工作薄,4、根据模板建立工作薄Excel2007模板类型已安装的模板：安装Excel时安装在本地计算机中的模板；我的模板：用户自已创建的模板；在线模板：微软公司网站上的在线工作薄模板，在用这类模板创建新工作薄时，Excel会自动从微软的网站中下载相关模板到用户的本地计算机中，并用它创建新工作薄。当然，前提是用户计算机与Internet网络相连接（即Online，在线）。,1.3.2创建新工作薄,5、在线模板示例下面的图表是用MicrosoftOfficeOnline”中的“预算”模板类中的“商务旅行预算”模板建立的工作薄。在该工作薄中，只需要输入D列的费用和F列的数字，Excel就会自动计算出H列的费用，这一功能是由“商务旅行模板”提供的。,1.3.2创建新工作薄,5、在线模板示例下图是用“MicrosoftOfficeOnline”中的“库存控制”模板类中的“设备资产清单”模板建立的工作薄。在该工作薄中预设了许多功能，在L、N、P、Q、R列中都预设了计算公式，只要填定了其余列的数据，Excel就会自动计算出这些列的数据。,1.3.2创建新工作薄,5、在线模板的建立方法(1)确定计算机已连接上了Internet网络(2)单击“Office按钮”新建MicrosoftOfficeOnline.将连接到微软的在线模板网站。(3)从微软网站的在线模板网站中选择需要的模板，就会从网站中将选择的模板下载到本机中，同时将应用。,1.3.3保存和打开工作薄文件,1、保存文件方法一单击“快速访问工具栏”中的按钮保存文件。方法二单击Office按钮，从弹出的文件菜单，选择“另存为”菜单项，然后在“另存为”对话框中输入文件名。2、打开文件单击Office按钮，从弹出的文件菜单，选择“打开”菜单项，然后在“打开”对话框中选择要打开文件的名称。,1.3.4文件格式与兼容性,1、Excel2007的文件格式MicrosoftOffice2007引入了一种基于XML的新文件格式：MicrosoftOfficeOpenXMLFormats，适用于2007版的Word、Excel和PowerPoint。OfficeXMLFormats有许多优点：其一，文件自动压缩。某些情况下文档最多可缩小75%其二，OfficeXMLFormats格式以模块形式组织文件结构，是如果文件中的某个组件（如图表）受到损坏，文件仍然能够打开其三，更强的安全性。OfficeXMLFormats格式支持以保密方式共享文档，可避免用文档检查器工具获取信息。其四，更好的业务数据集成性和互操作性。在Office中创建的信息容易被其他业务应用程序所采用，只需一个ZIP工具和XML编辑器就可打开和编辑Office文件。,1.3.4文件格式与兼容性,2、Excel2007的文件扩展名和文件兼容性,1.4公式和单元格引用,、公式的概念Excel的公式由运算符、数值、字符串、变量和函数组成。公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。在等号的后面可以跟数值、运算符、变量或函数，在公式中还可以使用括号。例如：=10+4*6/2+(2+1)*50就是公式，可以在任何单元格中输入此公式Excel会把公式的计算结果显示在相应的单元格中,1.4公式,2、运算符在xcel公式中可以使用运算符。,1.4公式,3、说明括号的运算级别最高，在Excel的公式中只能使用小括号，无中括号和大括号。小括号可以嵌套使用，当有多重小括号时，最内层的表达式优先运算。同等级别的运算符从左到右依次进行。“;“零”;则当在此单元格中输入负数时，输入的负数就不会被显示出来（被隐藏了）。,4.4格式化数据,在格式码中指定的字符被用来作为占位符或格式指示符。0作为一个占位符，它在没有任何数字被显示的位置上显示一个0。符号“_)”跟在一个正数的格式后面，以保证这个正数右边会留下一个空格，空格的宽度与圆括号“）”的宽度一样宽。正数、负数和0靠单元格的右边线对齐。,4.4格式化数据,自定义格式码的例子,4.4格式化数据,6、Excel常用格式码的含义,4.4格式化数据,7、自定义格式案例,4.4格式化数据,8自定义格式的方法（1）选中要格式化的单元格。（2）单击“开始”|“数字”组右边的下三角形（3）选择弹出对话框的“数字”标签，然后选择“分类”列表框中的“自定义”选项。（4）在“类型”文本框中，输入自定义格式。（5）在单元格中输入数据，数据将以自定义格式显示。,4.5格式化日期,1、概述在Excel中，日期和时间实际上是以序列数字的形式存储，以日期形式显示的。所以当一个单元格的格式被设置为日期格式后，即使在其中输入纯粹的数字，该数字也会被显示为一个日期。,4.5格式化日期,2、日期的格式码,4.5格式化日期,3、日期格式化案例,4.6条件格式,关于条件格式对于不同的数据，可按不同的条件设置其显示格式。例如，把学生成绩表中所有不及格成绩显示为红色，可以非常轻松地看出其中不及格的成绩情况；对于企业的销售表，把其中利润较小的或无利润的数据设置为黄色，而将利润最大的数据设置为绿色，可以使人在查看这些数据时一目了然。,4.6条件格式,1、条件格式的意义对于不同的数据，可按不同的条件设置其显示格式。比如在一个学生成绩表中，把所有不及格的成绩显示为红色，就能够很容易找到有不及格科目的学生。下面是条件格式运用的一个例子：当单元格的数值大于200时，用黑色显示；当小于100时用红色显示；100200之间的数据用蓝色显示。此自定义条件的格式码如下：黑色=200#,#;红色=100#,#;蓝色#,#,4.6条件格式,2、条件设置案例在下图所示的成绩表中，060之间，6080之间，80100之间的分数分别以不同的颜色表示和背景表示，便于查询不同分数段的信息。,4.6.1Excel2007条件格式概述,条件格式指基于某种条件更改单元格区域中数据的表现形式，如果条件成立就基于该条件设置单元格区域的格式（如设置单元格的背景、用图形符号表示数据），如果条件不成立就不设置单元格区域的格式。Excel2007的条件格式Excel2007对条件格式进行了较大的改进，对条件格式的任务分得更细，涉及的内容更多，也更具实用性。Excel2007的条件格式更容易达到以下效果：突出显示所关注的单元格或单元格区域；强调异常值；使用数据条、颜色刻度和图标集来直观地显示数据。,4.6.1Excel2007条件格式概述,【例4.8】某中药材公司2009年的药材销售利润表如图4.26所示。Page96在图4.26所示工作表中突出显示以下信息：利润小于20的药材销售情况，销售利润最高或最低的10种药材是哪些，用条型图直观显示出各药材在各省的销售情况，用不同的图形显示出各药材在各省的销售情况，标识出利润占前30%的药材销售情况。在Excel2007中，通过条件格式可以轻松地实现上述要求。条件格式位于“开始”选项卡的“样式”组中，单击该组中的“条件格式”按钮，将显示条件格式的命令列表，如图4.27所示。,4.6.1Excel2007条件格式概述,Excel2007条件格式的内容,Excel2007条件格式案例,突出显示利润小于20的药材,用数据条显示单元格数据,突出显示利润10%的药材,用图标集和色阶显示不同比例部分的数据,4.6.1Excel2007条件格式概述,Excel2007条件格式中的几个概念（1）突出显示单元格规则对单元格区域设置一定的条件，将按指定的现则突出显示该区域中满足条件的单元格内容，默认的规则用某种色彩填充单元格背景。例如，如果单元格的值大于、小于或等于某个指定值，就将单元格的背景填充为绿色，突出显示该单元格中的值。（2）项目选取规则对选中单元格区域中小于或大于某个给定阈值的单元格实施条件格式。单击此规则中的“值最大的10项”、“值最大的10%项”、“高于平均值”等条件，Excel都会显示出一个条件设置对话框，可通过它设置单元格中的条件格式。,4.6.1Excel2007条件格式概述,Excel2007条件格式中的几个概念（3）数据条数据条以彩色条型图直观地显示单元格数据。数据条的长度代表单元格中的数值。数据条越长，表示值越高，数据条越短，表示值越低。（4）色阶色阶用颜色的深浅表示数据的分布和变化，包括双色阶和三色阶。双色刻度使用两种颜色的深浅程度比较某个区域的单元格。颜色的深浅表示值的高低。（5）图标集使用图标集可以对数据进行注释，并可以按阈值将数据分为三到五个类别。每个图标代表一个值的范围，其形状或颜色表示的是当前单元格中的值相对于使用了条件格式的单元格区域中的值的比例。,4.6.2条件格式规则,条件格式规则Excel2007中的条件格式是根据一组规则来实施完成的，这组规则称为条件格式规则。条件格式规则类型基于各自值设置所有单元格的格式只为包含以下内容的单元格设置格式”仅对排名靠前或靠后的数值设置格式仅对高于或低于平均值的数值设置格式仅对唯一值或重复值设置格式使用公式确定要设置格式的单元格,单击“开始”选项卡中的“条件格式”按钮，从弹出的菜单中选择“新建规则”,4.6.3自定义条件格式,自定义条件格式默认的条件格式能够解决大多数问题，但有些时候并不如愿，例如默认的图标集用三种图标表示不同比例的数据：前33%、中33%和后33%各用一种图表表示，如果希望的比例为前15%、中45%后40%各用一种图标表示。就需要更改条件格式的规则，这可以通过自定义条件格式完成。,4.6.3自定义条件格式,【例4.9】某校采用学评教制度，学生对其任课教师进行评分。某次学评教成绩如图4.33左图所示。对该工作表进行格式化，标识出学评教分数最差的20%，最好的15%，以及中间的学评教成绩，以便于实施奖励与惩罚。,最好的15%最后的20%！中间的65%,4.6.3自定义条件格式,自定义条件格式的建立过程,（1）选择要建立条件格式的区域，单击“开始”“样式”组“条件格式”“新建规则”命令。弹出图示“新建格式规则”对话框。,（2）在“格式样式”列表中选择条件格式类型,（3）在“类型（T）”的列表中选择“百分点值”，然后在“值”下面的文框中输入值的范围，如图所示。,4.6.3自定义条件格式,重复应用条件格式在Excel2007中，对同一单元格区域，可以多次应用不同的条件格式。例如，对一个已经应用了色阶的单元格区域，可以再次应用图标集、数据条等条件格式。自定义条件格式也可以叠加，请看下面的例子。【例4.10】某班期末考试如图4.35左图所示。对它进行格式化，用红色粗体显示060之间的成绩，对90100之间的单元格设置背景填充图案。,多次应用了条件格式的工作表,条件格式规则1：060分为红色，加粗，倾斜,条件格式规则2：90分以下填充图案背景,（1）选中C3:E12区域，单击“开始”“条件格式”|“突出显示单元格规则”|“小于”命令，,在“小于”中输入60，然后在“设置为”的下拉列表中选择“自定义格式”，Excel将弹出“设置单元格格式”对话框，,（2）在“小于”中输入60，然后在“设置为”的下拉列表中选择“自定义格式”，Excel将弹出“设置单元格格式”对话框，,（3）单击“字体”选项，选中“字形”中的“加粗倾斜”，并从“颜色”列表中选择经色。,（4）再次选中C3:E12区域。设置大于90的条件条件格式。通过“设置单元格格式”|“填充”对话框中的“图案样式”列表中，为成绩大于90的单元格选择一背景填充图案。,4.6.4条件格式规则的管理,1、条件格式的限制在Excel2007中，条件格式最多可以包含六十四个条件。2、条件格式的优先级当对相同的单元格或区域设置了多重条件后，有可能引起条件规则的冲突。例如，一个规则将单元格字体颜色设置为红色，而另一个规则将单元格字体颜色设置为绿色。因为这两个规则冲突，所以只应用一个规则。但到底应用哪个规则呢，要根据各条件规则的优先级来确定3、条件格式规则管理条件规则的优先级可通过“条件格式规则管理器”对话框进行设置和管理，通过此对话框，还可以在工作簿中创建、编辑、删除和查看所有条件格式规则。,4.6.4条件格式规则的管理,1、本题应用了图标集、色阶、数据条等多重条件,2、单击“开始”“样式”组“条件格式”旁边的箭头“管理规则”。Excel将弹出“条件格式规则管理器”对话框,3、选择“新建规则”或以建立新的规则；选择“编辑规则”可对条件格式进行修改,5、单击上、下箭头可以提高或降低当前稳定中条件格式规则的优先级,4、条件格式规则：这些规则都是应用于同一单元格或区域的，排在前面的优先级高；排在后面的优级低，可以通过“删除规则”右边的上下箭头移动规则，更改优先级,4.7自定义格式的应用,【例4.11】在数字单元格中增加文本。如果有较多的单元格或单元格区域需要输入相同的文本，如学生编号、公司的职工编号等，可以通过设置单元格的自定义格式，把编号中相同的部分或文本中相同的部分固定为格式，这样可以减少输入，而且可使数据更为准确。,定义A列学号的自定义格式为：32990100,4.7自定义格式的应用,【例4.12】设置自定义格式，控制用户输入（使用户输入的数据在有效的范围内）。,设置姓名列的自定义格式：红色姓名不能输入数字;红色人名不能是数字;红色姓名不能是0,设置工作年龄列的自定义格式：G/通用格式;红色工作年龄不能是负数;红色0;红色工作年龄不能是文本,4.7自定义格式的应用,【例4.13】隐藏不需要的0值。在输入学生成绩之前，预先在该列中输入了一个成绩计算公式“=SUM(C5:E5*0.7)+F5*0.3”。所以G列出现了许多0,设置G列总分区域的自定义格式为：G/通用格式;综合成绩不能为负;,2.6自定义格式的应用,【例】定义电话、保险号码的简化输入,TheEnd,谢谢大家！,第5章公式、名称与函数,Excel与数据处理第3版,Excel与数据处理第3版,第6章图表处理,