《Office》课件-第四章 使用EXCEL 2010 制作电子表格

第四章

使用EXCEL2010制作电子表格★

4.1Excel2010的基本操作

★4.2数据输入与格式设置★4.3Excel公式和常用函数★4.4数据管理★4.5数据图表★4.6打印设置

4.1EXCEL2010的基本操作4.1.1Excel2010工作界面4.1.2创建、保存、关闭和打开工作薄4.1.3Excel工作表的基本操作4.1.1Excel2010工作界面工作区列标签行标签单元格活动单元格单元格区域名称框编辑栏工作表标签视图按钮4.1.2创建、保存、关闭和打开工作薄

启动Excel2010会自动创建一个空白工作薄“工作薄1”，以后建立的工作簿默认名称依次为工作薄2、工作薄3……。方法1：选择“空白工作薄”，可以新建空白工作薄。方法2：选择“样本模板”，可以创建由Excel提供的一些模板文件，如图4-3所示，创建具有特定格式的新工作薄。方法3：选择“根据现有内容新建”，可以根据已经存在的工作薄来创建新的工作薄。4.1.3Excel工作表的基本操作一个工作簿由多个工作表组成，最多允许包含255个工作表，新创建的工作簿中默认包含3个工作表：Sheet1、Sheet2和Sheet3。我们可以将工作薄看成是一个工作夹，在这个工作夹里面有许多工作纸，这些工作纸就是工作表。工作表是工作薄的基本组成单位，工作表不能独立存在，它必须存在于工作薄中。用户可以进行增加、重命名、移动和复制、删除工作表等操作。4.2数据输入与格式设置

4.2.1输入基本数据4.2.2自动填充数据4.2.3表格格式设置4.2.1输入基本数据工作表中每一个单元格都可以用来输入和存储数据，用户可以在单元格中输入多种类型的数据，如文本、数值、日期和时间、公式、函数等。下面将详细介绍不同类型数据的输入方法。1．输入文本文本类型的数据是最常见的数据类型之一，包含汉字、英文字母、具有文本性质的数字、空格以及符号。默认情况下，在单元格中输入文本类型数据时，会沿单元格左侧对齐。输入身份证号码，按下【Enter】键后，单元格中会显示“4.11423E+17”，此时系统将数据处理为数字，并以科学计数法显示。因此，正确的输入方法是，在输入前先输入一个单撇号，然后再输入内容。例如选择单元格A2，输入“，按下【Enter】键，系统会将该数据处理成文本类型。2．输入数字在Excel中，数值型数据由0~9、负号“-”、小数点“.”、分号“/”、百分号“%”、货币符号“¥”等组成。输入数值型数据时，会沿单元格右侧对齐。输入数值型数据的注意事项如下：输入分数，例如“3/4”，通过直接输入的方式并不能输入分数，系统会自动识别为日期类型的数据，应先输入一个“0”和一个空格，再输入分数部分。输入负数，必须在数字前面加一个负数“-”，或给数字加上圆括号。输入百分数，可以直接在数值后输入百分号。输入小数，一般可以直接在制定的位置输入小数点即可。输入的数字超长时，会以科学记数法显示。

3．输入日期和时间

在Excel中输入日期和时间时，注意以下几点：常用的日期格式有“yy-mm-dd”、“yy/mm/dd”、“yyyy年mm月dd日”等。输入时间，则“时、分、秒”之间的各部分使用冒号（“：”）间隔，如果要输入带日期的时间，就在日期和时间之间键入一个空格。如果按12小时制输入时间，需在时间后输入空格和“AM”或“PM”，否则Excel将以24小时制来处理时间。按快捷键“Ctrl+；”输入当前日期，按快捷键“Ctrl+shift+；”输入当前的系统时间。4.2.2自动填充数据在处理电子表格数据时，经常要输入相同或有规律的递增、递减、成比例数据，比如给班级的同学编号，通常是以“1”递增的等差数列。这些有规律的数据在Excel中可以自动填充，这样就能缩短编辑的时间和效率。自动填充数据有拖动“填充柄”填充和使用菜单“填充”功能填充两种方法。其中，填充柄是位于选定单元格或选定单元格区域右下角的小黑方块，利用它可在其相邻单元格中进行简单的自动填充数据，从而大大提高输入速度。1．使用“填充柄”填充相同数据和等差数列2．使用“填充”命令填充日期等差数列实例:填充从2014年12月20日开始,步长为7的等差日期数列。填充等比数列和填充等差数列的方法相似，在“序列”对话框中，选择“等比序列”，输入步长值，就可以填充等比数列。3．使用“填充”命令填充等比数列4.2.3表格格式设置1．设置字体、字号、字形和颜色单元格字体格式设置通常包括字体、字形、字号和颜色。在Excel2010中，这些命令都在【开始】选项卡的“字体”组中。方法和Word2010中字体设置方法相似。2．设置单元格对齐方式在默认情况下，在Excel2010中输入文本类型的数据系统会默认左对齐，输入数据类型的数据会默认右对齐。设置对齐方式用户可以根据需要，选择要设置对齐方式的单元格或单元格区域，在【开始】选项卡中的“对齐方式”组中设置单元格的垂直和水平对齐方式，设置数据旋转角度单元格中的数据也可进行角度旋转，在“方向”旁的下三角按钮。设置自动换行当单元格中的内容较长，不能完全显示，在不能改变列宽的情况下，可以设置单元格中的内容自动换行。3．合并与拆分单元格合并单元格就是将两个或两个以上水平或垂直相邻的单元格合并为一个跨多列或多行显示的大单元格，表格的标题栏常使用该方法获得；拆分单元格就是将一个跨多列或多行显示的大单元格拆分为多个小单元格。需要注意的是，未跨多列或多行显示的单元格无法进行拆分。4．调整行高与列宽默认情况下，Excel工作表中所有的行高和列宽都是相等的，用户可以根据实际情况进行调整。调整行高和列宽的方法有两种，一是使用鼠标拖动调整；二是利用“格式”菜单中的选项精确调整。5．设置表格边框和底纹6．设置工作表背景在Excel工作表中，用灰色网格线显示单元格，但是这种边框线只在显示器上显示，打印时不会被打印出来。因此，为了使表格的内容显示得更加清晰，增加表格视觉效果，就可为表格添加边框和底纹。可以为工作表设置背景图片，以增加工作表的美观程度。7．为表格添加条件格式

Excel“条件格式”功能可以根据单元格内容有选择地自动应用格式，可以让符合特定条件的单元格数据以醒目方式突出显示，便于我们对工作表数据更好地进行分析，给Excel工作带来很多方便。实例1：将性别为“女”的单元格设置“浅红填充色深红色文本”格式

实例2：将出生年月按顺序填充由“绿”到“黄”的底纹

实例3：将基本工资的数据添加蓝色“渐变数据条”，可以更加直观的看出工资水平。实例4：将奖金列中的前五名单元格数据设置为“绿填充色深绿色文本”。实例5：在迟到和旷工列，迟到和旷工次数为“0”的单元格使用“绿对号”，否则使用“红叉号”。4.3EXCEL公式和常用函数4.3.1单元格引用4.3.2公式的基础知识4.3.3函数的应用4.3.1单元格引用公式、函数与单元格引用是分不开的。单元格引用是指使用单元格地址对工作表中的单元格或单元格区域的引用，在公式、函数中使用单元格引用作为计算项，Excel可以找到需要计算的值或数据，代替单元格的实际数值。使用单元格引用不仅可以同一个工作表中的数据和同一个工作薄的不同工作表中的数据，还可以引用其它工作薄中的数据。在公式和函数中常见的单元格引用方式有三种：相对引用、绝对引用和混合引用。1.相对引用在输入公式和函数的过程中，除非用户特别指明，Excel一般使用相对引用来引用单元格的位置。例如，在单元格G3输入公式“=D3+E3+F3”，该公式中就是使用相对引用的方式，当序列向下填充公式时，公式中引用的单元格地址就会发生相应的变化。2.绝对引用在单元格地址列和行标志前加符号“$”，例如$D$3，即表示绝对引用单元格D3，无论复制到什么位置，总是引用不变的单元格。3.混合引用混合地址引用是指，在一个单元格的地址引用中，既有相对地址引用，也有绝对地址引用。例如，公式“=B$3+C3+F3”，第一个加数是对行的绝对引用，其余加数是相对引用。4.3.2公式的基础知识

1．公式的组成

公式的定义：公式是一个能够进行运算的计算表达式，一般以等号（=）开始，由算术运算符、比较运算符、文本运算符和数字、文字、用的单元格及括号组成。公式的输入：在EXCEL中，输入公式应以“=”或“+”开头，然后输入公式内容，否则EXCEL会把公式视为一般的文本，从而失去公式的计算能力。公式输入方法，例如公式：“=B1+C1*E1”，这是一个非常简单的公式，但如果每个字符都需要手工输入，比较费力，当遇到公式字符较多时，更是繁琐。对于公式，如果纯手工输入方式来编辑，那么输入差错就会让人崩溃，公式会不断报错。其实，这个公式可以如此编辑：F1中输入“=”，进入公式编辑状态，用鼠标单击单元格B1，此时字符B1被直接添加至F1单元格中光标活动位置，此时公式变成：“=B1"，而活动光标顺延其后，这里的关键就是使用鼠标单击动作替代了输入字符B1，这样极大的提高了输入准确率。2.运算符及优先级运算符号优先级说明:(冒号)(单个空格),(逗号)1引用运算符–2负号%3百分比^4乘幂*和/5乘和除+和–6加和减&7连接文本字符串=<><=>=<>8比较运算符4.3.3函数的应用1.函数的基础知识函数的定义：函数是Excel内部已经定义的公式，对指定的数据进行计算，可以进行简单或者复杂的运算。使用函数可以大大简化公式，并能实现一些一般公式无法实现的计算。函数组成：函数名（函数参数）。函数名指出要完成的运算，参数是传递函数使用的数值和单元格或区域地址。使用函数的时候要注意函数的语法，函数的一般语法如下：函数必须以“=”开头函数的参数用圆括号“（）”括起来函数的参数多于一个时，要用“，”分隔。参数的文本要用半角的双引号引起来。在Excel中有众多的函数，按其功能具体分为数学与三角函数、逻辑函数、文本函数、数据库函数、统计函数、日期与时间函数、财务函数、查找与引用函数、信息函数等类型。2.函数的使用1）统计函数统计函数功能示例SUM求和函数返回单元格区域中各元素的和=SUM（C3：F3）求C3：F3单元格区域中数据的和AVERAGE平均值函数返回单元格区域中各元素的平均值。=AVERAGE（C3：F3）求C3：F3单元格区域中数据的平均值MAX最大值函数返回单元格区域中各元素的最大值=MAX（C3：F3）求C3：F3单元格区域中数据的最大值MIN最小值函数返回单元格区域中各元素的最小值=MIN（C3：F3）求C3：F3单元格区域中数据的最小值COUNT计数函数统计单元格区域中各元素的数目=COUNT(B2:B8,E2:E8)求B2:B8和E2:E8单元格区域中数据的数目COUNTIF条件计数函数统计某个区域符合条件的单元格的数目=COUNTIF(B2:C8,">80")

表示统计B2:C8单元格区域中数据大于80的数目RANK.AVG函数返回某数字在一列数字中相对于其他数值的大小排名=RANK.AVG(L3,$L$3:$L$16)表示统计L3单元格在L3:L16区域中的排名2）日期函数在日常工作中处理一些实际问题时，经常会应用日期和时间，这时就需要用到日期和时间函数来解决与日期时间相关的运算。日期函数功能示例NOW函数返回当前电脑的日期和时间，此函数为无参数函数。=NOW()返回当前日期和时间，例如“2015/1/0921:29”TODAY函数返回当前日期，此函数为无参数函数。=TODAY()返回当前日期，例如“2015/1/09”YEAR函数返回指定日期数据的年份信息=YEAR（A1）A1单元格数据为“2015-01-5”，YEAR（A1）返回值为“2015”MONTH函数返回指定日期数据的月份信息=MONTH（A1）A1单元格数据为“2015-01-5”，MONTH（A1）返回值为“01”3）数学函数数学函数主要用来进行数学方面的计算，可用来解决日常生活和工作中的一些数学运算问题。数学函数功能示例INT函数去除小数点尾数，不可以调整小数尾数，整数舍弃小数，负数无条件进行进位。=INT(C4)如果C4单元格内容为“8.9”，返回值为“8”。如果C4单元格内容为“-8.9”，返回值为“-9”。RAND函数生成在0-1（不包含0和1）之间的随机小数，此函数为无参数函数。=RAND（）返回随机小数.4）逻辑函数常用的逻辑函数为IF函数。IF函数的功能为：根据条件判断值的真或假，返回值。IF函数的语法为：IF（logical_test,[value_if_true],[value_if_false]5）字符函数通过字符函数，可以处理字符串。字符函数功能示例LEFT函数根据指定的字符数返回文本串中的前几个字符。=Left(A1,1)A1单元格的内容是“王小天”,使用Left(A1,1)得到结果“王”，返回客户姓名中的姓氏。RIGHT函数根据指定的字符数返回文本串中的后几个字符。=Right(A1,2)A1单元格的内容是“王小天”,使用right(A1,2)得到结果“小天”，返回客户姓名中的名。LEN函数LEN返回文本的字符数，此函数用于双字节字符，且空格也将作为字符进行统计。=LEN(A1)A1单元格内容“4413251993082263190”是身份证号码，使用LEN(A1)得到结果19，说明身份证号码输入错误。MID函数返回文本串中从指定位置开始的特定数目的字符，该数目可以由用户指定。=Mid(A1,7,4)A1单元格内容是身份证号码，使用Mid(A1,7,4)得到出生年份“1993”。4.4数据管理4.4.1数据排序4.4.2数据筛选4.4.3数据分类汇总4.4.4数据透视表4.4.1数据排序在用Excel处理数据的时候，经常要对数据进行排序处理。用户可以选择排序数据的范围和排序方式，让数据以用户要求，按照一定的大小规律来重新进行显示，以便管理。数字升序：按从最小的负数到最大的正数进行排序。日期升序：按从最早的日期到最晚的日期进行排序。文本升序：按照特殊字符、数字（0…9）、小写英文字母（a…z）、大写英文字母（A…Z）、汉字（以拼音排序）排序。逻辑值升序：FALSE排在TRUE之前。错误值升序：所有错误值（如#NUM!和#REF!）的优先级相同。空白单元格升序：总是放在最后。降序排序与升序排序的顺序相反。1.简单排序简单排序是指排序时只涉及到一列数据，即只对一个字段进行排序。可选中要参与排序的列中的任意单元格，在【数据】选项卡下的“排序和筛选”分组中，单击升序排序按钮和降序排序按钮，可以实现对单个“关键字”进行“升序”或“降序”排序。2．复杂排序复杂排序就是指排序时要涉及到多个列。如果排序时，第一关键字的值相同，就可再使用另一关键字对这几条相同的数据再进行一次排序。4.4.2数据筛选数据筛选就是在工作表中查询满足特定条件的记录，Excel2010提供了“筛选”和“高级筛选”两种筛选方式。1.自动筛选自动筛选是一种快速的筛选方法，用户可以通过它快速地从中选出满足条件的数据。2.高级筛选如果筛选的条件要涉及几个字段，则运用“高级筛选”可快速的筛选出满足条件的记录。4.4.3数据分类汇总在日常应用中常常需对数据进行分类统计。例如，按照“所属部门”分类汇总统计各部门的费用情况、按学生的“年龄”分类汇总求学生总分的平均值等。Excel2010提供了分类汇总功能，可按求和、计数、求平均值和最大值等进行分类汇总。4.4.4数据透视表“数据透视表”是“分类汇总”的延伸，是进一步的分类汇总。4.5数据图表

4.5.1认识图表4.5.2创建图表4.5.3编辑图表4.5.1认识图表Excel图表是将工作表中数据根据所要表述的意图，选择适用的图表表述出来，这样能使工作表中的数据更加直观或趋势变得一目了然