第6章 电子表格处理软件

第6章电子表格处理软件Excel,主要内容,6.1表格处理平台概述6.2电子表格软件Excel基本操作6.3公式和函数的使用6.4工作表的编辑6.5Excel数据清单的管理与分析6.6Excel页面设置与打印6.7Excel与Word、PowerPoint的互动6.8WPS表格与Excel的对比,6.1表格处理平台概述,电子表格是一种表格式的计算机软件，适合用来编辑各种报表，它具有排序、计算、统计、分析及绘制图形表等功能，特别适合商业的应用。电子表格Excel是目前最优秀的电子制表处理软件之一，是Microsoft公司开发的办公自动化软件Office的重要成员。Excel不仅具有强大的表格制作及表格数据计算与分析能力，而且还可以对表格数据进行综合管理、制作图表，将表格中的数据以各种统计图表的形式展示出来，从而大大增强相关数据的可视性。同时它还提供了数据清单（数据库）操作，广泛应用于财务、统计、金融、审计等各个领域。,1.Excel电子表格处理技术特点操作方便：图形化用户界面用户可以相当轻松方便地用鼠标进行操作。运算快速准确：表格数据的计算速度非常之快，一旦修改了某个表格单元中的数据，表格内与此相关的所有数据将立即自动更新，并确保准确无误。丰富的数据处理函数：提供了大量的数据处理函数，可用于各种复杂的数据分析和报表统计。功能全面：可以做所有的数据处理工作，打印输出美观。,6.1表格处理平台概述,2.Excel电子表格处理技术的功能丰富的自动化功能：自动更正功能可在键入内容时，自动更正许多错误；并有自动拼写检查、自动套用格式、自动填充表格数据的功能；更有自动排序、自动筛选、自动分类汇总表格数据等功能。丰富的绘制图表功能：可以根据表格中的数据自动创建各种统计图表。当表格数据变化时，图表的内容将自动更新。方便的数据交换能力：可方便地与数据库软件、写字板等应用程序共享信息。版式处理功能：可利用软件提供的编辑功能完成对表格的样式处理，编辑出样式精美的、规格统一的表格，并能方便地打印输出。,6.2电子表格软件Excel基本操作,6.2.1Excel的运行,1、Excel窗口的组成,Excel窗口：选择“开始程序MicrosoftExcel”即可启动Excel，并进入Excel主画面，如图。Excel的工作环境与Word相似。（这里为读者介绍与Word不同的内容）（1）编辑栏一般地，在“功能区”下面、编辑工作区上方是编辑栏：编辑栏由名称框、编辑按钮和编辑框三部分组成。,（2）全选按钮编辑工作区左上角有一空白框，称为“全选”按钮。单击“全选”按钮会选定当前工作表的所有单元格，将光标移至其他地方后单击，取消该项选定。（3）列标和行号工作表是由列和行相交所形成的单元格组成的，每个单元格须用一个称为地址的名称来区别。单元格地址是列号和行号连接在一起来描述的。在为单元格命名地址时，先列标后行号，如A1、B3、C12、D26等。列号用字母AZ、AAAZ、BABZIAXFD表示，共16384列；行号用数字表示，从11048576。每一张表的单元格总数为：104857616384。（4）单元格与活动单元格单元格是电子表格的最小单元，用户可以把数据输入到单元格中保存起来。如果用鼠标指针“”单击某单元格，它便呈现黑色的边框，这个单元格就是活动单元格。活动单元格是用户输入、编辑数据的地方，每个单元格最多可以容纳32000个字符。,（5）工作簿工作簿是由工作表、图表等组成的。系统启动后，总是建立一个新的工作簿，而后可以随时建立多个新的工作簿或者打开一个或多个已存在的工作簿。工作簿名显示在Excel窗口的标题栏上，默认的文件名是Book1，其扩展名为.xlsx。类似一个账本，工作簿作为存储和处理数据的一个文件，将多个有内在联系的电子表格组成在一起。一个工作簿由多个工作表组成，系统默认每个工作簿有3个工作表，用户可以插入更多的工作表，但每个工作簿最多只能设置255张工作表。,(6)工作表：是Excel工作簿中的一页。工作表是由行和列构成的一个电子表格，每一个工作表有一个名称，系统默认为Sheet1、Sheet2、Sheet3等。在工作表标签栏上有工作表翻页按钮和工作表标签按钮。工作表用于输入数据、执行计算和组织信息。工作表又叫“电子表单”，表中的行坐标和列坐标构成若干空格，称为“单元格”。每个工作表最多可以有1048576行、16384列。可以同时打开多个工作表，打开工作表的多少取决于计算机内存的大小。显示工作表名称的标签叫“工作表标签”。单击相应的工作表标签，可以在工作表之间进行切换。当前选定为输入数据的单元格，称为“活动单元格”。活动单元格在Excel工作簿窗口的左上角的名称框内显示活动单元格所在列与行的位置。可以在同一工作簿内或两个工作簿之间对工作表进行改名、添加、删除、移动或复制操作。,2、工作簿的基本操作,（1）新建工作簿（2）打开已存在的工作簿（3）保存工作簿（加密）（4）关闭工作簿,6.2.2工作表的创建,工作表的创建就是掌握数据输入方法，用光标在单元格上进行操作而完成的。本节重点介绍光标、单元格和数据输入方法。,1光标定位Excel在启动之后，光标自动定位在第一个工作表Sheet1的A1单元格，它的周围呈现黑色边框“”，该单元格称为活动单元格，也称为当前单元格，即当前可以接受数据输入或编辑的单元格，其他单元格为非活动的单元格。,2选择单元格区域选择单元格区域主要是指单元格的选择、单元格内容的选择。单元格区域是输入、编辑的基础。（1）单元格、列与行的选择（2）选择连续单元格（3）选择不连续单元格（4）选择当前工作表的全部单元格（5）使用定位命令进行选择（6）单元格内容的选择,3工作表中的数据输入,在输入数据前，首先在单元格内定位光标，出现光标后即可输入数据。单元格中可以输入字符型、数值型、货币型、日期或日期时间型和逻辑型等多种类型的数据；也可以在单元格输入批注信息及公式。当在一个单元格内输入的内容需要分段时，按Alt+Enter键。如果要在同一单元格中显示多行文本，请选中“格式”菜单下的“单元格”命令中的“对齐”选项卡，并单击选中“自动换行”复选框。,（1）字符型数据的输入字符型数据是指由首字符为下划线、字母、汉字或其他符号组成的字符串。输入到单元格中默认对齐方式为左对齐。如果输入的字符数超过了该单元格宽度（默认宽度为10个字符）仍可继续输入，表面上它会覆盖右侧单元格中的数据，而实际上仍属于本单元格内容。显示时，如果右侧单元格为空，当前输入的数据照原样显示，否则显示本身的内容。有时，需要把一些纯数字组成的数字串当做字符型数据，比如身份证号。为避免系统把这些数字串误当做数据处理，在输入这些数字串时，前面要添加单引号或以=“数字串”的格式输入，如：87654321、=“610075”等。确认输入后，输入项前后添加的符号将会自动取消。,（2）数值型数据的输入像+34.56、123、1.23E3等有大小意义的数据，称为数值型数据。数值型数据的输入方法如下：在输入正数时，前面的加号可以省略，负数的输入则要用“（）”或以“”开始，如110或（110）。纯小数可以省略小数前面的0，如0.8可输入为：.8。允许加千分符，如12345可输入12,345。数的前面若加了￥或$符号就具有货币含义，计算时大小不受影响。若在数尾加%符号，表示该数除以100。例如12.34%，在单元格内虽然显示12.34%，但实际值是0.1234。可以以分数的形式输入数值，一个纯分数输入时必须以零开头，然后按一下空格键，再输入分数，如01/2。带分数输入时，先输入整数，按一下空格键，然后再输入分数。数值类型的数据在单元格中右对齐。,（3）输入日期与时间输入日期的格式为年/月/日或月/日，如2004/3/30或3/30；输入时间的格式为“时：分：秒”，如10：35。按下Ctrl+：键，或按下Ctrl+Shift+：键，则取当前系统日期或时间。输入的日期与时间在单元格中右对齐。（4）输入逻辑值可以直接输入逻辑值True（真）或False（假），一般是在单元格中进行数据之间比较运算时，Excel判断后自动产生结果，居中显示。,（5）插入一个批注信息单元格一旦输入批注信息，则在单元格右上角会出现一个红色的三角标记，当鼠标指针指向该单元格时就会显示这些备注信息。（6）数据有效性可以给要输入单元格的数据设定限制。（7）数据的取消在输入数据的过程中，如果取消输入，可按Esc键，或单击编辑栏左侧的取消“”按钮。,4Excel的快速输入数据方法,（1）在连续区域内输入数据在连续的区域内输入数据的方法为：首先选中所要输入数据的区域，在所选中的区域内，如果要沿着行的方向输入数据，则在每个单元格输入完后按Tab键；如果要沿着列的方向输入数据，则在每个单元格输入完后按Enter键。当输入的数据到达区域边界时，光标会自动到所选区域的下一行或下一列的开始处。（2）输入相同单元格内容内容相同的单元格的输入方法：按下Ctrl键不放，选择所要输入的相同内容的单元格。单元格选定后，输入数据并按下Ctrl+Enter键，则刚才所选的单元格内都将被填充同样的数据，与此同时，新数据将覆盖单元格中的已有数据。,（3）自动填充（4）使用“填充”命令输入数据（5）自定义序列（文件-选项-高级-编辑自定义列表）,6.3公式和函数的使用,6.3.1公式公式由运算符、常量、单元格引用值、名称和工作表函数等元素构成。,1运算符Excel为数据加工提供了一组运算指令，这些指令以某些特殊符号表示，称为运算算符，简称算符。用运算算符按一定的规则连接常量、变量和函数所组成的式子称为表达式。运算算符可分为数值、文本、比较和引用等四类。,（1）数值运算符用来完成基本的数学运算，数值运算符有：（）、（乘方）、（取正负）、%（百分比）、*（乘）、/（除）、+（加）、（减），其运算结果为数值型。(2)文本运算符文本运算符只有一个：&，其作用是将一个或多个文本连接成为有一定长度的文本串。如果连接的操作对象是文本常量，则该文本串须用一对英文引号括起来，例如：EXCEL&2003中文版，其结果为：EXCEL2003中文版。,(3)比较运算符用来对两个数值进行比较，产生的结果为逻辑值True（真）或逻辑False（假）。比较运算符有：（大于）、=（大于等于）、=（等于）、（不等于）。(4)引用运算符引用运算符用来将单元格区域合并运算，引用运算符有三个，它们分别为：“”、“，”和“空格”。对两个在区域内的所有单元格进行引用，例如AVERAGE（A1D8）。，将多个引用合并为一个引用，例如SUM（A1，B2，C3，D4，E5）。空格只处理各引用区域间相重叠的部分单元格。例如输入公式=SUM（A1:C3B2:D4），即求出这两个区域中重叠的单元格B2、B3、C2、C3的和。,2公式输入,如果要在选定的单元格中输入公式，应先以“=”或“+”开始，然后再输入公式。在输入公式内容时，如果公式中用到单元格中的数据，可用鼠标单击所需引用的单元格（也可直接输入所引用单元格）。如果输入错了，在未输入新的运算符之前，可再单击正确的单元格。,6.3.2单元格引用,复制公式可以避免大量重复输入公式的工作。当复制公式时，若公式中使用单元格或区域，应根据不同的情况使用不同的单元格引用。单元格的引用分三种：相对引用、绝对引用和混合引用。,1相对引用Excel中默认的引用为相对引用。相对引用反映了该单元格地址与引用单元格之间的相对位置关系，当将引用该地址的公式或函数复制到其他单元格时，这种相对位置关系也随之被复制。相对引用时，单元格的地址可用列标加行号表示，例如，A5、B6等。例如，现在单元格C7有公式“=A5+B6”，当将此公式复制到D7和C8时，单元格D7的公式此时变化为“=B5+C6”；单元格C8的公式这时变化为“=A6+B7”。,2绝对引用绝对地址是指某一单元格在工作表中的绝对位置。绝对引用要在行号和列标前加一个$符号。如：$A$1+$B$2等。例如，单元格C7有公式“=$A$5+$B$6”，当将此公式复制到D7和C8时，单元格D7和C8的公式仍为“=$A$5+$B$6”。,3混合引用混合引用是相对地址与绝对地址的混合使用。例如A$5表示对A列是相对引用，对第5行是绝对引用。例如，现在单元格C7有公“=$A5+B$6”，当将此公式复制到D7和C8时，单元格D7的公式此时变化为“=$A5+C$6”；单元格C8的公式这时变化为“=A6+B$6”。例：用单元格引用建一个九九乘法表。,6.3.3函数,所谓函数就是应用程序开发者为用户编写好了的一些常用数学、财务统计等学科的公式程序，它内置于Excel中，用户只要学会调用即可。这些函数包括：财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑等。函数由函数名后跟用括号括起来的参数组成，其语法格式为：函数名称（参数1，参数2）其中参数可以是常量、单元格、区域、区域名、公式或其他函数。,常用函数,函数是执行简单计算或复杂计算的预定义公式，它使用称为参数的特定数值按特定的顺序或结构进行计算。参数可以是数字、常量、公式、数字的文本形式、TRUE或FALSE的逻辑值、数组，或单元格引用例如，函数SUM(A4:A10)执行对单元格区域进行求和运算；AVERAGE(B2:F8)执行对单元格区域进行求平均运算。函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，在函数名称前面键入等号=，如：=函数名（参数1，参数2：参数3，参数4）如：=SUM（A10，B5：B10，50，37）以=开头，SUM是函数名，A10,B5：B10，50，37是四个参数，参数用小括号将参数括起来，不同参数之间用逗号隔开。,求和函数SUM格式：SUM(number1,number2,.)功能：返回某一单元格区域中所有数字之和。其中：Number1,number2,.为1到30个需要求和的参数。说明：如果直接在参数表中输入数字、逻辑值及数字的文本表达式则被计算；如果参数为数组或引用，则只有其中的数字被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换成数字的文本，将会导致错误。例：SUM(3,2)=？又例：SUM(“3”,2,TRUE)=？，“3”是数字的文本表达式将被转换成数字3，而逻辑值TRUE被转换成字1。但，如果A1包含“3”,B1包含TRUE，则：SUM(A1,B1,2)等于2，因为对引用的“3”和引用中的文本TRUE不能被转换成数值。,2、COUNT返回参数的个数格式：COUNT(value1,value2,.)功能：计算数组或单元格区域中数字项的个数。其中：Value1,value2,.是包含或引用各种类型数据的参数（130个），但只有数字类型的数据才被计数。COUNT计数时，把数字、日期或时间均计数；而错误值或其它无法转化成数字的文字则被忽略。（统计逻辑值、文字或错误值，使用函数COUNTA）例：A1=销售额；A2=1990-9-8；A3=；A4=19；A5=22.24；A6=TRUE；A7=DIV/0！COUNT(A1:A7)=？COUNT(A4:A7)=？COUNT(A1:A7,2)=？,3、AVERAGE返回参数平均值（求算术平均）,格式：AVERAGE(number1,number2,.)其中：Number1,number2,.要计算平均值的130个参数。说明：参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。如果单元格包含零值则计算在内。例:如果A1：A5命名为Scores，其中的数值分别为10、7、9、27和2，则：AVERAGE(A1:A5)=？AVERAGE(Scores)=？AVERAGE(A1:A5,5)=？AVERAGE(A1:A5，“5”)=？,4、MAX、MIN分别返回数据表中的最大数值、最小数值,格式：MAX(number1,number2,.)MIN(number1,number2,.)其中：Number1,number2,.为需要找出最大数值的1到30个数值。参数中可以指定为数字、空白单元格、逻辑值或数字的文本表达式。如果参数为错误值或不能转换成数字的文本，将产生错误；如果参数为数组或引用，则只有数组或引用中的数字将被计算；数组或引用中的空白单元格、逻辑值或文本将被忽略；如果参数不包含数字，函数MAX返回0。例：如果A1:A5包含数字10、7、9、27和2，则：MAX(A1:A5)=？MIN(A1:A5)=？MIN(A1:A5,0)=？MAX(A1:A5,30)=？MAX(A1:A5,“30”)=？,5.条件求和函数SUMIF格式：SUMIF(range,criteria,sum_range)功能：根据指定条件对若干单元格求和。其中：range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。条件可表示为56，“56”，“=56”，“apples”等。Sum_range为需要求和的实际单元格。只有当range中的相应单元格满足条件时，才对sum_range中的单元格求和。如果省略sum_range，则直接对range中的单元格求和。例：假设A1：A4的内容分别为：100，200，300，400。B1：B4的内容为：50，100，150，200，则：SUMIF(A1：A4，“160”，B1：B4）)=？。,6.COUNTIF函数格式：COUNTIF(range,criteria)功能：计算给定区域内满足特定条件的单元格数目。其中：range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。条件可表示为56，“56”，“=56”，“apples”等。例：假设A3：A6的内容分别为：“apples”、“oranges”、“peaches”、“apples”。则：COUNTIF(A3：A6，“apples”)？计算不及格的人数,7.IF函数格式：IF(logical_test,value_if_true,value_if_false)功能：执行真假值判断，根据逻辑测试值的真假值返回不同的结果。其中：logical_test表示计算结果为true或false的任意值或表达式。value_if_true是logical_test为true时返回的值。value_if_false是logical_test为false时返回的值。说明：IF函数可以嵌套7层，用value_if_true及value_if_false参数可以构造复杂的检测条件。例：IF（A100，则舍入到指定的小数位；如果num_digits等于0，则舍入到最接近的整数；如果num_digits小于0，则在小数点左侧进行舍入。例：ROUND(2.15,1)2.2ROUND(2.13,0)2ROUND(-2.15,1)-2.2ROUND(21.25,-1)20,10、HLOOKUP（）与VLOOKUP（）HLOOKUP与VLOOKUP是查找与引用函数,HLOOKUP是水平查表函数，在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值；VLOOKUP是垂直查表函数，在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值；HLOOKUP（Lookup_value,table_array,Row_index_num,Range_lookup）例：,6.4工作表的编辑,工作表的编辑主要包括：单元格内容的移动、复制、删除；单元格的插入；行、列的删除、插入；工作表的格式化；工作表的移动、复制、删除和插入等。编辑过程中，可以随时使用撤销“”按钮和恢复“”按钮来撤销误操作。,6.4.1单元格的基本编辑操作,1对单元格的编辑操作2对单元格的移动操作3使用选择性粘贴4插入、删除单元格5行、列的插入与删除,6.4.2工作表的格式化,1行高与列宽的调整（1）使用鼠标调整行高、列宽（2）精确设置行高、列宽2单元格、单元格区域的数字格式化（1）用工具栏按钮格式化数字（2）用菜单格式化数字,6.4.3单元格的其他设置,1单元格的对齐方式2单元格边框与底纹的设置3格式设置的自动化（1）使用格式刷按钮（2）使用“编辑”菜单格式化单元格（3）使用“自动套用格式”格式化单元格4条件格式的应用,4条件格式的应用,所谓条件格式是指当指定条件为真时，Excel自动应用于单元格的格式，例如，单元格底纹或字体颜色。如果想为某些符合条件的单元格应用某种特殊格式，使用条件格式功能可以比较容易实现。如果再结合使用公式，条件格式就会变得更加有用。例：某教师想在如下工作表中突出显示期末成绩高于期中成绩的学生。在该工作表中，有全体学生两次考试的成绩，条件格式功能将学生期末成绩高于期中成绩的高亮显示。这种格式是动态的：如果改变考试的分数，格式会自动调整。实现后的工作表如图6-4-12所示。,6.4.4工作表的编辑,1选择工作表(1)点Excel工作簿的左下角工作表标签，可以切换当前工作表。(2)按下Shift键的同时，单击选择的是连续的工作表。(3)按下Ctrl键的同时，分别单击选择的是不连续的工作表2工作表的插入3工作表的重命名4删除工作表5隐藏工作表6移动或复制工作表,6.5Excel数据清单的管理与分析,Excel除具有简单数据处理功能外，还具有对数据的排序、筛选、分类汇总等功能。6.5.1建立数据清单6.5.2数据排序在Excel中，可以根据一列或多列的内容按升序或降序对数据清单进行排序。在对数据进行排序时，Excel是按单元格大小进行排序的；在对文本进行排序时，Excel按字符逐个自左到右进行排序。,6.5.3数据筛选,筛选就是指从数据清单中，找出符合某些条件特征的一条或多条记录来，这时就需要使用Excel的数据筛选功能。自动筛选高级筛选,自动筛选：包括按选定内容筛选，它适用于简单条件。单击自动筛选箭头将显示该列中所有唯一的可见项目清单，这些项目包括空项（空格）和非空项。通过从清单中为特定列选择一个项目，您可以立即隐藏所有不含选定值的行。,高级筛选：适用于复杂条件。可以使用“高级筛选”命令对单个列应用多个条件、对多个列应用多个条件或通过公式创建条件。使用高级筛选时，首先要建立条件区域。在条件区域的第一行，输入含有待筛选数据的列的列标志，然后在列标志的下面输入要进行筛选的条件。条件区域建立后，就可以进行数据筛选了。,注意：1、筛选条件的表头标题需要和数据表中表头一致2、筛选条件输入在同一行表示为“与的关系3、筛选条件输入在不同的行表示为“或”的关系,6.5.4分类汇总可以得到数据清单