第5章电子表格Excel

第5章电子表格Excel本章学习目标•Excel的基本操作。•创建和编辑表格。•使用公式和函数。•格式化表格。•生成图表。•数据管理。•打印工作表。5.1Excel基本操作•电子表格Excel可以用来组织、计算和分析数据。•它可以胜任从简单的库存管理到企业复杂的会计账目管理。5.1.1Excel简介•在Excel电子表格中，当用户向单元格中输入信息时，Excel会自动判断信息的类型，并格式化单元格中的内容。•根据已知的单元格数据，利用计算公式可以计算出新单元格中的数据，并且自动添加到单元格中。5.1.2认识Excel窗口5.2工作表基本操作•在工作表中可以输入各种不同类型的数据；•修改数据内容；插入或删除行、列和单元格；•查找、替换等编辑操作；•创建、删除工作表等。5.2.1创建新的工作表•在Excel的单元格中输入的数据可以是常量、公式和函数。•常量数据包括数值、文本、日期和时间等格式。•通常Excel能够自动识别单元格中的数据类型，并根据单元格中数据的类型，以不同方式存储和显示单元格数据。数值类型的数据•在Excel中，数值类型的数据只可以为下列字符：–0123456789+-(),/$%.Ee•输入数值类型的数据约定如下：–如果在数字的前面以加号“+”开头，将删除加号。–如果在数字前面以减号“-”开头，如-40，或者为数字添加括号，如(40)，将数字作为负数。–可以使用逗号来分隔百位和千位、千位和兆位等。–可以直接用小数点表示小数。–如果要输入分数，必须用0+空格+分数来表示，如要输入5/7，在单元格中必须输入“05/7”。如果要表示带分数，则整数和分数之间用空格分隔，如115/7。–当输入的数字太大或太小时，单元格中的数据会自动用科学计数法表示，并根据单元格的宽度对数值四舍五入后显示相应的位数。文本类型的数据•文本类型的数据包含数字、字母、符号和文字等。•默认状态下，文本型数据左对齐单元格。•当输入文本型的数字时，为了区别数值型的数据，应在数字前加一个西文单引号’，例如’10005。输入完毕后，键入的’不出现在单元格中。文本格式数据•如果在输入数据之前将单元格数据的格式定义为文本格式，就可以直接在单元格中输入数字，而无需键入西文单引号’。日期/时间类型的数据•Excel内置了一些日期和时间的格式。当单元格中输入的数据与这些格式相匹配时，Excel将它们识别为日期/时间值，并按照预置的格式显示这些数据。•如果只用数字表示日期，可以使用分隔符“-”或“/”来分隔年、月、日。例如，9/25/97、1-Oct-92等。•在同一个单元格中允许同时输入日期和时间，但必须在它们之间键入空格，如96/4/163:13PM。时间/日期类型的数据•时间/日期类型的数据可以参加运算。•Excel内部以天为单位，将输入的日期/时间值转换为从1到65370之间的日期序列数。•序列数1表示的基准日期是1900年1月1日。一天的时间用小数表示，如中午12:00用0.5表示。•例如，2000年5月4日下午2:45，用序列数表示为36650.6145833333。•通过序列数表示日期/时间，Excel就可以进行复杂的日期/时间运算。日期和时间的格式•Excel中预置了一些日期和时间的格式，输入日期/时间值时，可以先定义需要的格式，这样，无论用哪一种格式输入这类数据时，Excel都会自动将其用指定的格式显示。数据的有效值•可以设置输入单元格数据的有效值。自动填充功能•在Excel中使用自动填充功能可以将一个重复值或一串具有一定规律的数据快速填充到单元格中。5.2.2编辑工作表•对于创建的工作表还可以进行修改、查找等编辑操作，在进行编辑操作之前，必须选中单元格对象。选中单元格•要选中某个单元格，可用鼠标直接单击之。•要选中一块连续的单元格，从左上角的单元格一直拖动右下角的单元格为止。或者按住Shift键分别单击左上角的单元格和右下角的单元格。•要选择不连续的几个单元格块，按住Ctrl键的同时分别选中不同的单元格块。•要选中整个工作表可以单击工作表左上角的工作表全选按钮。修改或清除单元格的内容•如果要修改单元格中的部分数据内容，双击该单元格，当编辑光标出现在单元格中时直接修改数据即可。•如果要用新的数据取代原来的数据，选中该单元格后，直接输入新数据即可。•如果要清除单元格的内容，选中该单元格后，按删除键。•如果要清除单元格的数字、格式、批注等内容，使用【清除】子菜单中的选项。插入和删除行、列以及单元格查找和替换单元格内容5.2.3工作表的操作•工作簿由若干个工作表组成。默认情况下，新建的工作簿提供了三个工作表Sheet1、Sheet2、Sheet3等。•为了便于识别这些工作表，可以为工作表重新命名。•由于每张空白的工作表占用500个字节的磁盘空间，所以，多余的工作表应该删除。•如果当前工作簿中的工作表不够用，可以插入新的工作表。在对工作表操作之前，一定要选择要操作的工作表。工作表操作•为重新命名工作表，可以直接双击工作表标签，然后输入新名字即可。•使用【删除工作表】可删除当前工作表。•使用【插入】、【工作表】菜单命令可插入工作表，并按顺序为工作表命名。•要改变工作表的顺序，拖动工作表标签到目的位置。工作表操作•保护工作表可以限制用户修改工作表的部分或全部数据。•如果工作表列或行的内容不能在一个窗口全部显示，可以冻结某些行或列，以便浏览。•通常Excel窗口中只能显示一个工作表，如果要同时显示多个工作表，新建一个窗口，然后选择平铺、水平或垂直排列窗口。工作表的操作•可以将当前的工作表复制或移动到其他工作簿中。•可以设置保护工作簿或工作表中的某部分内容不被修改。5.3使用公式和函数•公式是电子表格的灵魂，正是Excel复杂的公式运算功能，使其得到广泛的应用。•函数是一个预定义的公式，它对一个或多个值进行操作，并返回一个或多个值。5.3.1使用公式•在单元格中建立公式的标记是以等号“=”开头，然后键入公式，最后按回车键结束。•公式中可以包括数值、运算符号、单元格引用（即单元格地址）和函数。•例如可以用下面的公式计算单元格E1的数据：•=5*D3+C5–SUM(A1:D1)•在Excel的公式中可以使用运算符完成各种复杂运算。•运算符包括算术运算符、逻辑运算符和文本运算符等。算术运算符逻辑运算符文本运算符5.3.2公式中的单元格引用•单元格引用标识了工作簿中一个或一组单元格。•当创建的公式中包含单元格引用时，该公式与工作簿中的其他单元格产生了联系。•公式的值由所引用的单元格的数据产生，当所引用的单元格中的数据发生变化时，在公式中引用了该单元格数据的计算结果会自动更新。单元格地址引用•工作表中的公式通常使用列字母和行号所组成的坐标字符串来引用单元格。为了便于单元格的引用，可以采用相对引用、绝对引用和混合引用。•相对引用。指公式中引用的单元格按照被引用的单元格相对自己的位置来引用。如“本单元格三行以上的单元格”。单元格相对引用的表示方法是直接使用单元格地址，如B3。•绝对引用。根据单元格在工作表中的绝对位置来引用，如“列C行5位置的单元格。单元格绝对引用的标记是在列字母、行号之前添加符号$，如$c$5。•混合引用。同时包含相对引用和绝对引用的单元格，混合引用表示为列字母或行号之前添加符号$，如$D2、D$2。•如果正在输入和编辑单元格引用，按F4键可以更改引用的类型。R1C1引用•单元格的引用还可以使用R1C1样式，即用R行号和C列号来引用单元格。•例如R5C1表示第5行第1列的单元格，用单元格地址方式表示就是A5。•选择了R1C1引用样式后，公式中的单元格引用将更改为R1C1格式。如B3单元格将更改为R3C2。•在R1C1引用样式下，单元格的相对引用是将行号和列号的数字用方括号括起来。例如R[-1]C[2]。其中负的数字表示引用单元格位于公式单元格的上方或左边，正的数字表示引用单元格位于公式单元格的下方或右边。括号指示的是相对引用。•在R1C1格式下，单元格的绝对引用时，行或列数字不用括号。如R8C1。其他工作簿或工作表中单元格的引用•在公式中引用其他工作簿或工作表单元格中的数据时，应该在单元格的前面指明单元格所在的工作簿和工作表。•工作簿的引用使用方括号“[]”，工作表的引用使用“!”分隔。•例如，在工作簿Book2中要引用工作簿Book1的工作表Sheet2中C1单元格的数据，应表示为“[Book1]Sheet2!$C$1”。•如果使用公式的单元格与引用的单元格在相同的工作簿或工作表，在公式中可以省略工作簿或工作表。5.3.3移动和复制公式•当执行剪切操作移动公式时，无论采用何种单元格引用，公式中的单元格地址都不更改。•在复制公式时，采用了单元格的相对引用、绝对引用和混合引用的效果是不同的。–如果公式中使用的是单元格相对引用，那么，复制的公式所引用的单元格会根据单元格过去的位置对公式引用的单元格做相应的更改。–如果公式中采用的是单元格绝对引用，则复制的公式中所引用的单元格地址不会更改。–如果公式中使用了单元格的混合引用，则复制的公式会根据单元格引用的相对行或列更改相应的单元格行或列引用，而绝对引用的列或行不变。5.3.4使用函数•函数由一个函数名和包括在括号中的参数组成。•函数名表示要进行的计算（求和、求平均值等）；参数是函数运算所用的数值或者是代表单元格引用的单元格地址。•使用函数表达式应该注意：–所有的参数都应包括在括号“（）”中。–在函数名、括号和参数之间不能键入空格。–各参数之间用逗号“，”间隔。–连续的单元格参数在开始单元格和结束单元格之间加冒号“：”表示。•例如，用函数表示求B3，C7，A5以及D1～D9单元格数据中的平均值为：•=AVERGE(B3，C7，A5，D1：D9)自动求和•自动求和是电子表格中最常用计算。•在使用自动求和计算时，会根据当前单元格周围数据的情况自动推测求和的单元格计算范围。内部函数•在Excel中内置了许多内部函数，使用这些函数可以解决各种复杂的运算。5.3.5逻辑函数的应用•逻辑函数使用条件测试来判断指定的条件是真还是假。•条件测试是对两个数字、函数、公式或逻辑值等进行比较的公式。•所有的条件测试必须包括至少一个逻辑运算符，逻辑运算符定义了条件测试中元素的关系。IF函数•IF条件函数的表达式为•“=IF(条件，为真的返回值，为假的返回值)”。•例如：•=IF(D38,5,10)–表示当D3单元格的值小于8时返回5，否则返回10。•在IF函数中可以使用文本参数。•IF函数还可以嵌套使用，以创建多层测试。AND、OR和NOT函数•使用AND、OR、NOT函数可以帮助创建复杂的条件函数。•它们通常和简单的逻辑运算符一起使用，形式为：–=AND(逻辑值1,逻辑值2,…逻辑值30)（最多30个参数）–=OR(逻辑值1,逻辑值2,…逻辑值30)（最多30个参数）–=NOT(逻辑值)（最多1个参数）使用嵌套逻辑函数的计算结果5.3.6日期和时间运算•时间和日期可以相加、相减，并可以包含到其他运算中。•如果要在公式中使用日期或时间，用双引号括上日期或时间值。•例如，下面的公式得出差值为68：=2004/5/12-2004/3/5•在计算公式中，可以使用日期和时间格式的值。•TODAY函数总是返回当前日期。表达式为“=TODAY()”。5.3.7单变量求解•要根据使用公式计算单元格结果中的数，来求某个单元格引用中的数据，可使用单变量求解功能。•例如，要计算当利息为5000时的年利率，可使用单变量求解。5.4设置工作表格式•为使创建的表格易于查阅，通常在工作表的单元格中输入数据后，要将表格进行格式化。•格式化包括：格式化单元格、调整行宽和列高、设置分页符等。5.4.1格式化单元格•在单元格格式对话框中可以：–设置单元格中文本的水平和垂直对齐方式。–设置文本字体、字形、字号、颜色和背景。–设置单元格边框和图案。5.4.2使用样式和模板•通过样式可以快速定义工作表的格式。•Excel内置了一些工作表模板，可以使用这些模板快速创建