Excel 计算基础.doc

Excel 计算基础第一节 表达式及基本运算一、公式概述公式是Excel工作表中进行各种计算的公式，由操作数、运算符、函数、括号等组成。公式存储于单元格中时必须以等号（=）开始，表示紧随等号之后的各种符号、数据等，是需要进行计算的要素（操作数，运算符等）。公式的计算结果就存储于公式所在的单元格中，并在相应位置显示出来。例：=3+5=3+A5=3+（A3+A5）/2+SUM(B1:B5)二、Excel的运算Excel可以做各种算术运算、字符运算和运算运算，不同的运算，运算符不，书写格式不同，运算结果也不同。下面将分别加以叙述。1、数值数据与算术运算Excel的数据值主要：整数、实数、日期、时间等。在excel中日期数据在输入时一般以减号（-）作为年月日的分隔符，且认为日期是一个整数，日期与整数的对应规则是：1900年1月1日对应整数1，1900年1月2日对应整数2，余此类推。比1900年1月1日更早的日期excel就不处理了。如下表所示：日期对应的整数1900年1月1日11900年1月2日21900年1月3日31900年1月4日41900年1月5日52012年3月1日40969在excel中时间数据中“时分秒”的分隔符为英文的冒号“：”，且认为时间是一个01之间的纯小数，时间与小数的对应规则为：“0：0：0”对数据值0，“12：0：0”对应0.5，“23：59：59”对应0.999988日期数据、时间数据在参与计算时将自动转换为对应的整数或小数。在显示时，可通过单元格格式设置，将日期转换成整数或将整数（0）转换成日期进行显示，同样，时间数据与纯小数（01之间）也可通过格式显示进行转换。如下是日期数据转换的例子：转换前转换后参与算术运算的数据一般是数值类型，其计算结果也是数值数据，excel的算术运算如下表所示：Excel公式中的算术运算符算术运算符含义示例+加法8+2-减法负号5-3-7*乘法9*3/除法7/3乘方23（2的3次方）%百分比34%例：销售利润率的计算公式为：销售利润率=（销售收入-销售成本）/销售收入在“销售利润率1”的计算公式中，直接用数据进行计算，在“销售利率2”的计算公式中，则是用销售收入及销售成本数据存放的单元格的坐标说明要用哪些数据进行计算。单元格坐标用在计算公式中，称为“单元格引用”。这种在计算公式中用单元格的坐标说明要用哪个单元格的坐标参与计算的方法更好，因为，如果销售数据改变了，可以直接修改单元格中的数据，excel就可自动重新计算，而不需要修改计算公式。例：设有函数，计算当x=2.5时，函数f(x)的值。计算可以用excel的函数EXP（），计算时，运算符不能省略，例如中，3与X之间的乘号就不能省略。计算时，可以直接用X的值代入函数表达式中进行计算，也可以将X的值存入单元格中（例如B2单元格），而在单元格中用X的值所在单元格的坐标代替X的位置，后一种方法是普遍使用的。例：在复利计算规则下，如果年利率为i，那么现在存入本金PV，在第n年末的本息和FV可用下列公式计算：试计算年利率为i=6.5%，本金PV=10000元，计算第10年末的本息和。例：设B68单元格存放的日期数据为：“2003-10-8”，E68单元格存放的日期数据为：“2003-9-29”，则下列计算的结果为计算公式结果含义=B68-E6892003年10月8日比2003年9月29日晚9天=E68-B68-9 2003年10月8日比2003年9月29日早9天=B68+72003-10-152003年10月8日后的第7天是2003年10月15日例：设B82单元格中存放了时间数据：“14:34:00”，E82单元格中存放了时间数据“12:12:23”，则下计算的结果为：计算公式运算结果含义=B82+E822：46：2314:34:00之后的12:12:23的时间是26:46:23，减去24小时就是2:46:23=B82-E822：21：3714:34:00与12:12:23之间相差2:21:37注意：在日期和时间计算中，公式的日期和时间数据不能用常量，只能用单元格引用例：某汽车租赁公司5座位小汽车每天的租金为198元，一辆车租金的计算公式为：用excel编制一个计算表用于计算该公司一辆车一次应收的租金总额。3、文本运算Excel的文本运算仅有一个，“ & ”，其作用是将两个文本连接成一个文本。文本运算的基本格式： 文本1 & 文本2当文本运算符运用到计算公式时，excel自动将文本运算符两边单元格中的数据认为是文本数据，也可在运算符两边使用文本常量，文本常量要用英文的双引号“ “ ”括起来。如下所示：4、比较运算excel的逻辑值有两个：FALSE（逻辑假）和TRUE(逻辑真)。Excel可以进行多种关系运算，运算结果为逻辑值，有关的比较运算符及运算规则如下表所示：比较运算符格式含义=数据1=数据2判断2个数据是否相等，如果相等，则结果为TRUE，否则为FALSE数据1数据2判断数据1是否大于数据2，如果是，则结果为TRUE，否则为FALSE=数据1=数据2判断数据1是否大于或等于数据2，如果是，则结果为TRUE，否则为FALSE数据1数据2判断数据1是否小于数据2，如果是，则结果为TRUE，否则为FALSE=数据1=数据2判断数据1是否小于或等于数据2，如果是，则结果为TRUE，否则为FALSE数据1数据2判断2个数据是否相等，如果不相等，则结果为TRUE，否则为FALSE例运算结果334TRUE334FALSE433TRUEACD=acdTRUE7799TRUE77=99FALSEACD=ABCFALSEACD=ACD123FALSEABC=ABCDFALSEABC=ABCTRUE注：ASCII字符的比较是按字典顺序进行，且不分大小写。例：根据下表，写出（1）判断“张重庆”的性别是否是“男”的关系表达式；（2）判断“张重庆”的基本工资是否低于4000元的关系表达式；（3）判断“张重庆”身份证号的前两位是否是“43”的关系表达式。解：（1）判断“张重庆”的性别是否是“男”的关系表达式：C90=”男”注：excel中文本常量的两端要用英文的双引号作为分界符。（2）判断“张重庆”的基本工资是否低于4000元的关系表达式： F90=4000（3）判断“张重庆”身份证号的前两位是否是“43”的关系表达式：LEFT(G90,2)=”43”注：函数LEFT（文本，子文本长度）的功能是从“文本”的左端开始取指定长度的一个子文本。因为身份证号是文本，所以LEFT()得到的是一个子文本，要判断一个文本是否等于43，则43也是一个文本，所以在表达式中要将43的两端要加英文的双引号Excel没有逻辑运算符，但有逻辑函数，有关函数在后面介绍。三、运算符的优先级如果一个公式中同时用到多个运算符，Excel将按下表所示的顺序进行运算。如果公式中包含相同优先级的运算符，则按从左到右的顺序进行计算。Excel的运算次序表运算符说明运算顺序：（冒号）引用运算符先运算 （单个空格），（逗号）-负号运算符%百分比乘幂*和/乘和除+和-加和减&字符串连接= = 比较运算符后运算四、excel的运算错误信息如果Excel根据单元格中公式进行计算得到一个正确的结果，就将该单元格的数据显示在该单元格中。如果不能得到一个正确结果，则在公式所在单元格显示一个错误信息。Excel的错误信息表错误信息含义例#DIV/0!这个公式试图使用0作为除数如果公式中使用一个空单元格作为除数时，就可能会出现这个信息#NAME?公式使用了一个Excel不能识别的名称。如果删除了公式中使用的名称或误输入某个函数名时，就可能出现这个信息。#N/A公式中引用的（直接或间接）单元格中使用NA函数所标识的不能使用的数据。如果查找函数没有找到匹配数据时，就可能出现这个信息。#NULL!公式使用了一种不允许出现交叉但交叉在一起的两个区域#NUM!使用的数据有问题例如，在应该使用正值的情况下，使用了负数。#REF!公式引用了一个无效的单元格如果单元格从工作表中删除了，则经常会出现这种信息。#VALUE!公式包括了错误形式的变量或运算（使用错误的参数或运算数的类型错误）一个运算对象引用一个值或单元格引用，但是它们中却需要使用公式计算结果。#!单元格要显示的数据太长，单元格现在的宽度不能完整显示这个数据第二节 单元格引用及单元格区域引用一、单元格引用的概念及基本格式在excel的计算公式中，用单元格的坐标来说明在计算公式中要使用某个单元格的数据，或说明要使用某个单元格区域的数据来进行计算。所以将计算公式中的单元格坐标叫做单元格引用，如果这些坐标代表的是一个区域，则叫做单元格区域引用。1、单元格引用单元格引用的基本格式： 列名行号单元格引用代表一个单元格。例：A1代表工作表左上角的第一个单元格，即第一行第一列的单元格B4代表工作表第2列第4行的单元格2、单元格区域引用单元格区域引用的基本格式： 单元格引用1：单元格引用2作用：代表以“单元格引用1”为左上角，以“单元格引用2”为右下角的一个矩形单元格区域。例如：A2:C6 代表A2、A3、A4、A5、A6、B2、B3、B4、B5、B6、C2、C3、C4、C5、C6这些单元格所组成的一个矩形区域。如图所示的灰色区域。3、由一行或若干行中所有单元格组成的区域的引用基本格式： 行号1：行号2代表由“行号1”到“行号2”所在区域的所有单元格组成的区域，“行号1”与“行号2”可以相同，这时则说明是由一行组成的单元格区域。例如： 3：5 表示由第3行至第5行的所有单元格组成单元格区域，如图所示：例如： 3：3 表示由第3行的所有单元格组成的单元格区域，如图所示：4、由一列或若干列中所有单元格组成的单元格区域基本格式：列名1：列名2代表由“列名1”至“列名2”所有列组成的单元格区域，如图所示：例： C：D 表示由第C列和第D列的所有单元格组成的单元格区域，如图所示。5、不同工作表之间单元格数据的引用格式：工作表名！单元格引用例：Sheet1!A1表示工作表“Sheet1”A1单元格的引用 职工信息表！C3表示“职工信息表”中C3单元格的引用6、三维引用用一个区域引用表示连续的若干张工作表中的同一个单元格的集合。格式I： 工作表1：工作表2！单元格引用作用：代表由“工作表1”至“工作表2”连续的若干张工作表中的同一单元格格式II：工作表1：工作表2！单元格区域引用作用：代表由“工作表1”至“工作表2”连续的若干张工作表中的同一区域中的所有单元格例：1月份工资表:3月份工资表 !C3代表1月份工资表、2月份工资表及3月份工资中的C3单元格，也就是说这个单元格引用代表了3个单元格，如下图所示：根据上图，假设每月工资表格式相同，且张千一在这3个月的工资表中都的位置都相同，那么，计算公式：=SUM(1月份工资表:3月份工资表 !C3)表示求张千一这个职工13月份的基本工资合计。7、相对引用、绝对引用、混合引用Excel将单元格引用分为相对引用、绝对引用和混合引用，相对引用是如前面介绍的这些引用的格式均为相对引用，绝对引用则是在列坐标和行坐标的前面加上美元符号“$”，混合引用则仅在列坐标前美元符号“$”或在行号前美元符号“$”，如下所示：A1相对引用$A$1绝对引用$A1混合引用A$1混合引用这四种引用，都代表工作表中的同一个单元格A1单元格（第1行第1列所对应的单元格）。excel的计算公式中使用这四种单元格引用，对于计算的作用是相同的，例如： A1+B1，A1+$B1，$A$1+$B$1，其效果是相同的，都是将A1单元格的数据与B1单元格中的数据相加，计算结果也是相同的。那么在excel中使用这么多种引用有什么作用呢？这四种引用的不同之处在于，当一个单元格中的计算公式复制到另一个单元格中时，公式中的单元格引用是否发生变化以及如何发生变化。如果是计算公式中使用的是相对引用，那么当某个单元格中的公式复制到这个单元格的上方单元格中时，公式中单元格引用的行号将减少，新单元格如果在原单元格上方n行，则单元格引用中的行号将减少n，而单元格中计算公式向下方的单元格复制时，则公式中的单元格引用将增加，新单元格如果在原单元格下方n行，则单元格引用中的行号将增加n。同理，左右复制时，则计算公式中的单元格引用的列坐标将发生相应的变化，向左复制，列坐标减少，向右复制时，列坐标增加，减少或增加的规律与行坐标变化的规律相同。在绝对引用、或混合引用中，坐标前面的美元符号“$”是使计算公式在复制时，计算公式中的单元格引用中的相应的坐标不发生变化，如果列坐标前加了美元符号“$”，则在左右复制时，列坐标不变；如果在行号前加了美元符号“$”，则在上下复制时，行坐标不发生变化。例如：D5单元格中如果有计算公式：“=E7+F8”，那么，将该公式复制到B1单元格时，B1单元格中的计算公式将变成：“=C3+D4”，这是因为计算公式向左复制到了2列，所以，计算公式中的相对引用的列坐标就相应减少2列，从原来的E列变为C列，从原来的F列变成D列；而计算公式又向上复制了4行，所以计算公式中的相对引用的行坐标就相应减少4行，从原来的7行将减少为3行，以及从原来的8行减少为5行。而如果D5单元格中的计算公式是：“=$E$7+$F$8”，则计算公式复制到B1单元格，还是原来的计算公式，并不发生改变。如果D5单元格中的计算公式是：“=$E7+F$8”，则计算公式复制到B1单元格后，公式变为：=$E3+D$8，因为列坐标E前面加了美元符号“$”，和行号8前面加了美元符号“$”，所以复制后，这两个坐标没有发生变化。当在excel的工作表中有大量相似或相同的计算公式时，灵活运用，可以极大地减少输入计算公式的工作量。单元格引用应用举例例1：引用同一工作表中的数据进行计算工资计算在该表中，“缺勤应扣工资”的计算公式为：“应发工资”的计算公式为：（1）因为张千一的“基本工资”在C3单元格，“津贴”在D3单元格，“书报费”在E3单元格，“缺勤天数”在F3单元格，“缺勤应扣工资”在G3单元格，所以， “缺勤应扣工资”（G3单元格）中的计算公式为：=F3*（C3/22.5）“应发工资”（H3单元格）中的计算公式为：=C3+D3+E3-G3（2）同理，因为李东的“基本工资”在C4单元格，“津贴”在D4单元格，“书报费”在E4单元格，“缺勤天数”在F4单元格，“缺勤应扣工资”在G4单元格，所以， “缺勤应扣工资”（G3单元格）中的计算公式为：=F4*（C4/22.5）“应发工资”（H3单元格）中的计算公式为：=C4+D4+E4-G4（3）根据上述规律，其余人的“缺勤应扣工资”和“应发工资”栏的计算公式如下表所示：姓名缺勤应扣工资应发工资张千一=F3*(C3/22.5)=C3+D3+E3-G3李东=F4*(C4/22.5)=C4+D4+E4-G4张车车=F5*(C5/22.5)=C5+D5+E5-G5王万国=F6*(C6/22.5)=C6+D6+E6-G6陈山=F7*(C7/22.5)=C7+D7+E7-G7张东风=F8*(C8/22.5)=C8+D8+E8-G8李四喜=F9*(C9/22.5)=C9+D9+E9-G9刘爱舞=F10*(C10/22.5)=C10+D10+E10-G10陈琪=F11*(C11/22.5)=C11+D11+E11-G11从上表中可以看出，计算公式从上往下是很有规律变化，根据excel相对引用在公式复制时的特点，可以只输入张千一的“缺勤应扣工资”和“应发工资”计算公式，然后通过“复制”、“粘贴”操作就可得到其余人员的“缺勤应扣工资”和“应发工资”计算公式。这样可以大量节省录入计算公式的时间。例2：计算函数在x=0，0.1，0.2，的值，并列成一个函数值表。从上表可以看出：（1）计算函数值时，计算公式中用自变量x的值所在单元格的引用代替代替函数表达式中的自变量x就可计算函数的值。（2）从函数值计算公式列表中可以看出，由于自变量x的每一个值在不同的单元格，所以计算公式中代替自变量x的单元格引用也不同。（3）由于自变量x的值存放是有规律变化的，所以计算公式中，代替自变量x的单元格引用也是有规律的变化的，所以利用excel的相对引用，只需输入计算f(x)函数值的第1个公式，然后通过“复制”、“粘贴”命令就可得到其余函数值的计算公式。例3：增长率计算（环比增长率相对应用和定比增长率绝对引用）。（1）计算“比2006年第1季度增长（%）”的计算公式：（2）计算“比上一季度的增长（%）”的计算公式：根据上述计算公式，可以得到表中各个增长率的计算公式如下表：税收增长率计算公式季度比2006年第1季度增长（%）比上一季度增长（%）2006年第1季度2006年第2季度=(C4-C3)/C3=(C4-C3)/C32006年第3季度=(C5-C3)/C3=(C5-C4)/C42006年第4季度=(C6-C3)/C3=(C6-C5)/C52007年第1季度=(C7-C3)/C3=(C7-C6)/C62007年第2季度=(C8-C3)/C3=(C8-C7)/C72007年第3季度=(C9-C3)/C3=(C9-C8)/C82007年第4季度=(C10-C3)/C3=(C10-C9)/C92008年第1季度=(C11-C3)/C3=(C11-C10)/C102008年第2季度=(C12-C3)/C3=(C12-C11)/C112008年第3季度=(C13-C3)/C3=(C13-C12)/C122008年第4季度=(C14-C3)/C3=(C14-C13)/C132009年第1季度=(C15-C3)/C3=(C15-C14)/C142009年第2季度=(C16-C3)/C3=(C16-C15)/C152009年第3季度=(C17-C3)/C3=(C17-C16)/C162009年第4季度=(C18-C3)/C3=(C18-C17)/C172010年第1季度=(C19-C3)/C3=(C19-C18)/C182010年第2季度=(C20-C3)/C3=(C20-C19)/C192010年第3季度=(C21-C3)/C3=(C21-C20)/C202010年第4季度=(C22-C3)/C3=(C22-C21)/C212011年第1季度=(C23-C3)/C3=(C23-C22)/C222011年第2季度=(C24-C3)/C3=(C24-C23)/C232011年第3季度=(C25-C3)/C3=(C25-C24)/C242011年第4季度=(C26-C3)/C3=(C26-C25)/C252012年第1季度=(C27-C3)/C3=(C27-C26)/C262012年第2季度=(C28-C3)/C3=(C28-C27)/C27（1）从上表中可以看出，“比上一季度增长（%）”的计算公式中，上一计算公式中单元格引用的行号在下一行对应位置增长1，所以可以用相对引用输入这一列中的第1个计算公式，然后通过“复制”、“粘贴”操作得到本列的其余计算公式。（2）“比2006年第1季度增长（%）”列的计算公式中，只有第1个单元格引用为有规律的变化，而第2个、第3个单元格引用均为C3，为了使“复制”、“粘贴”操作不改变计算公式中的单元格引用C3，则C3使用绝对引用，使该列的第1个计算公式变为：=(C4-$C$3)/$C$3在输入这一列的第1个计算公式后，在通过“复制”、“粘贴”操作就可得到这一列的其余计算公式。如下图所示：图：输入计算增长率的第1行的计算公式图：输入计算公式后的计算结果图：复制第1行并粘贴其余行后的效果例4：按学分加权的平均成绩的计算（绝对引用与相对引用）计算方法根据成绩统计表的布局，可以得到各个学生的学分加权平均成绩的计算公式如下表所示：姓名学分加权平均成绩计算公式陈曦阳=(B4*C3+D4*E3+F4*G3+H4*I3)/(C3+E3+G3+I3)李朝阳=(B5*C