第2章 EXCEL数据运算

Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉1第2章Excel数据运算本章学习目标本章主要讲解Excel2003的基础知识，通过本章的学习，应掌握如下内容：公式与函数Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉2一公式的运用主要内容：1．输入公式2．构造公式3．单元格的引用Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉31．输入公式方法：（1）单击将要在其中输入公式的单元格。（2）键入＝（等号）。（3）输入公式内容。（4）按Enter键或按公式编辑栏上的对勾。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉42．公式的构造由=、运算数和运算符组成①公式中元素的结构或次序决定了最终的计算结果。Excel中的公式遵循一个特定的语法或次序：最前面是等号（=），后面是参与计算的元素（运算数），这些参与计算的元素又是通过运算符隔开的。每个运算数可以是不改变的数值（常量数值）、单元格引用、或工作表函数。②Excel从等号开始（=）从左到右执行计算（根据运算符优先次序）。可以使用括号组合运算来控制计算的顺序，括号括起来的部分将优先执行计算。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉5③．公式中的运算符有一个重要的东西叫运算符，是对公式中的元素进行运算的。运算符的分类：（1）算术运算符：完成基本的数学运算。（^%*/+-）（2）比较操作符：比较运算符用于比较两个值。当用操作符比较两个值时，结果是一个逻辑值，不是TRUE就是FALSE。（3）文本运算符：使用和号（&）可以将文本连接起来。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉6③．公式中的运算符（4）引用操作符：引用运算符的作用是确定在公式中需要参与运算的数据在工作表中所处的位置，可以使用三个运算符：冒号、逗号和空格。①：（冒号）：区域运算符，对两个单元格之间，包括两个单元格在内的所有单元格参与运算。②，（逗号）：联合运算符，可将多个引用合并为一个引用。③空格：交叉运算符，它是将同时属于两个引用的单元格区域进行引用，即两个单元格引用相重叠的区域。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉73在公式中引用单元格主要内容：1．相对引用2．绝对引用3．混合引用4.切换5.三维引用Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉81．相对引用和绝对引用的区别包括绝对引用单元格的公式，无论将其复制到什么位置，总是引用特定的单元格。如果需要绝对引用某一单元格或单元格区域，就需要在单元格列或行标志前加一美元符号$。在输入公式时，用户如果是通过单击单元格的方法来确定公式中所包括的单元格，这时的单元格引用就是相对引用。如果用户将该公式进行复制操作，那么，随公式位置的改变，其引用的单元格也会相应发生变化。相对引用单元格无需在单元格行或列标志前加$符号。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉92．相对引用与绝对引用之间的切换如果创建了一个公式并希望将相对引用更改为绝对引用（反之亦然），有一种简便的方法，可先选定包含该公式的单元格，然后在编辑栏中选择要更改的引用并按F4键。每次按F4键时，Excel会在以下组合间切换：绝对列与绝对行（例如，$A$1）；相对列与绝对行(A$1)；绝对列与相对行($A1)以及相对列与相对行(A1)，当切换到用户所需的引用时，按回车键确认即可。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉103．三维引用三维引用包含单元格或区域引用，前面还加上工作表名称的范围，其格式一般为“工作表标签！单元格引用”。如果需要使用三维引用来引用多个工作表上的同一单元格或区域，可按以下步骤进行操作：（1）单击需要输入公式的单元格。（2）输入＝（等号），如果需要使用函数，再输入函数名称，接着再键入左圆括号。（3）单击需要引用的第一个工作表标签。（4）按住Shift键，单击需要引用的最后一个工作表标签。（5）选定需要引用的单元格或单元格区域。（6）完成公式，按回车键键确认。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉111．3函数的使用主要内容：1.3.1函数的基本构成1.3.2Excel2003提供的函数Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉121．3．1函数的基本构成函数的构成包括：1．函数的结构=函数名(参数序列)2．函数的嵌套七级Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉132．函数的嵌套在某些情况下，用户可能需要将某函数作为另一函数的参数使用。这就是函数的嵌套。当嵌套函数作为参数使用时，它返回的数值类型必须与参数使用的数值类型相同。否则，Excel将显示#VALUE!错误值。公式中最多可以包含七级嵌套函数。当函数B作为函数A的参数时，函数B称为第二级函数。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉141．函数的结构函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数是以公式的形式出现，需在函数名称前面输入等号（=）。函数处理数据的方式与公式处理数据的方式是相同的，函数通过引用参数接收数据，并返回结果。大多数情况下返回的是计算的结果，也可以返回文本、引用、逻辑值、数值或工作表的信息。在函数中使用参数可以是数字、文本、逻辑值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉151．函数的结构在创建包含函数的公式时，如果用户不清楚该函数的基本语法，可使用公式选项板的帮助输入正确的函数。操作步骤为：（1）单击需要输入函数的单元格。（2）单击编辑栏中“编辑公式”按钮。（3）单击“函数”下拉列表框右端的下拉箭头。（4）单击选定需要添加到公式中的函数。如果函数没有出现在列表中，可单击“其它函数”查看其它函数列表。当选定某一函数后，会打开公式选项板。（5）输入参数。如果用户不清楚函数中各参数的含义，可通过单击各参数右侧的文本后查阅公式选项板下方的提示。（6）完成输入公式后，按Enter键。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉161．3．1函数举例举例：Countif()Sumif()If()Index()Mod()Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉171.3.2Excel2003提供的函数1．数学函数2．文本函数3．子字符串函数4．逻辑函数5．查找和引用函数6．财务函数Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉18利用Excel的函数功能转换工资条从工资条的构成分析，每一个工资条要反映两类信息：即工资项目和工资项目所对应的工资数据，同时为了便于工资条的裁剪，在每一个工资条之间要用空行分开。这样每一个工资条在“工资条”表中占三行，而且“工资条”表中的第1，4，7，……行取值为“工资表”表中的第一行数据；“工资条”表中的第2，5，8，……行取值为“工资表”表中的第2，3，4……行数据；“工资条”表中的第3，6，9，……行取值为空。Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉192.4利用Excel的函数功能转换工资条通过工资条数据对应关系的分析可以得出：当行数除3余数为1时，取工资表的工资项目；当行数除3余数为2时，取工资表的工资数据。当行数除3余数为0时，取空值。利用Excel的函数功能转换生成工资条的具体方法是：Excel与财务管理目录上一页下一页退出实验作业演示2020年1月20日星期一湖南科技职业技术学院--杨学廉202.4利用Excel的函数功能转换工资条在“工资表”工作表后增加一个名为“工资条”的新工作表，在A1中输入如下公式：“=IF(MOD(ROW(),3)=1,工资表!$A$1:$J$1,IF(MOD(ROW(),3)=2,INDEX(工资表!$A$1:$J$17,(ROW()+4)/3,COLUMN()),))”，然后通过填充方法复制此公式到指定范围（填充范围视工资表的行数和列数来确定），这样就可以生成一个完整的工资条表。