excel公式及函数.doc

Excel 2007公式和函数基础作者：刘承松 文章来源：未知 点击数： 101 更新时间：2008-11-18 14:11:33 1.基本概念公式：公式是对工作表中的数值执行计算的等式，以等号“”开头。常量：常量是指在运算过程中不发生变化的量，如数字20以及“月平均收入”等都是常量。函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，特别是在用公式执行很长或复杂的计算时。运算符：运算符指一个标记或符号，指定表达式内执行的计算类型。2.公式的组成公式可以包括函数、单元格引用、运算符和常量等。等号“”：这是公式开头的符号，不可缺省。函数：如SUM(A1:A5)，函数返回值为A1到A5单元格区域中数值之和。引用：指定的某个或者某些单元格中的数据，用单元格地址来指定。如“A2”返回第A列、第2行单元格中的值。常量：直接输入公式中的值。运算符：指明运算类型的符号，如“*”表示将数字乘方，“*”表示相乘。3.运算符的种类在Excel中有算术运算符、比较运算符、文本连接运算符和引用运算符等类型。（1）算术运算符若要完成基本的数学运算（如加法、减法、乘法等）、合并数字以及生成数值结果，可使用以下算术运算符，具体见下表。（2）比较运算符比较运算符用来比较两个值，结果为逻辑值“TRUE”（真）或“FALSE”（假）。比较运算符见下表。（3）文本连接运算符可以使用与号“&”连接两个或多个字符串，生成一新的字符串，具体如下表所示。（4）引用运算符使用下表中的引用运算符可对单元格区域进行合并计算，具体如下表所示。（5）运算的顺序执行计算的顺序会影响公式的返回值，因此，了解如何确定计算顺序以及如何更改顺序以获得所需结果非常重要。计算顺序：Excel中的公式始终以等号“=”开头，这个等号告诉Excel随后的字符组成一个公式。等号后面是要计算的元素（即操作数），各操作数之间由运算符连接。Excel按照公式中每个运算符的特定顺序从左到右依次计算。运算符优先级：如果一个公式中有若干个不同优先级的运算符，则按下表中从上到下的顺序进行运算；如果一个公式中的若干个运算符具有相同的优先级，则从左到右依次计算。使用括号：若要更改运算的顺序，可将公式中要先运算的部分用一对括号括起来。例如，公式=5+2*3的结果是11，因为Excel先进行乘法运算后进行加法运算；如果用括号将其更改为=(5+2)*3，将先求出5加2之和，再用结果乘以3得21。在下面的公式中，前面的括号强制先计算B4+25，然后再除以单元格D5、E5和F5中的值之和。前后两个括号的意义不一样，前者用来改变运算顺序，而后者是函数的组成部分。=(B4+25)/SUM(D5:F5)4.常用函数Excel 2007提供了成百上千个函数，不可能也没必要全都掌握，常用的也不是很多，这里介绍一些常用的函数。格式中用方括号 括起来的参数项为可选项，要根据情况确定用不用可选项。参数中的省略号表示还可以有若干个类似的选项。（1）ABS函数返回数值参数的绝对值。格式为：ABS(数值表达式)例如，单元格A2中有数值95，单元格B2中有数值-2，函数“ABS（A2*B2）”的返回值为数值190。（2）AND函数仅当所有参数的结果值均为逻辑真（TRUE）时返回逻辑真（TRUE），否则返回逻辑假（FALSE）。格式为：(AND逻辑表达式1,逻辑表达式2)例如，函数“AND(8=4*2,96,3*65)”返回A1至C6单元格区域中数字大于或等于5的单元格数目。（7）DATE函数返回指定数值的日期。格式为：DATE(年份,月份,日子)例如，函数“DATE(2007,02,28)”将返回日期数据2007-2-28。注意，输入年份参数时，若是20世纪及以前的则可以只输入后2位数字，也可以输入4位数字；若是21世纪及以后的年份则要输入4位数字。（8）DAY函数返回参数中指定的日期或者引用日期数据单元格中的日子（131）。格式为：DAY(日期表达式)例如，在D1单元格中有日期数据“1988-2-26”，函数“DAY(D1)”将返回26。（9）IF函数对于给出的条件表达式进行逻辑判断，结果若为逻辑真（TRUE）则返回第二个参数的值，判断结果若为逻辑假（FALSE）则返回第三个参数的值。格式为：IF(条件表达式,表达式1,表达式2)例如，假设在单元格C8、C9、C10中分别有数值数据，在单元格D8、D9、D10中分别有文本字符串。函数“IF(A10,SUM(C8:C10),D8&D9&D10)”判断A1的数值是否等于0，若不等于0（即满足表达式的意义，为逻辑真）则计算C8、C9、C10三个单元格中的数值之和并返回，若等于0（即不满足条件，为逻辑假）则返回D8、D9、D10单元格中文本字符的连接结果。（10）INDEX函数返回指定的单元格区域中，由给定的行序号和列序号交叉处的单元格的值或者引用。格式为：INDEX(单元格区域引用,行序号数字,列序号数字) 例如，函数“INDEX(A1:D8,5,3)”将返回A1到D8区域中第五行与第三列交叉处的单元格（C5）的值或者引用。（11）INT函数将数值参数的小数部分去掉，且不进行四舍五入，只返回整数部分，称为“取整”。格式为：INT(数值表达式)例如，函数“IN(52.9992)”将返回数值52。（12）ISERROR函数用于测试指定单元格的值是否有错。如果有错，则函数返回逻辑值TRUE，无错则返回逻辑值FALSE。格式为：ISERROR(单元格引用)例如，A3单元格中若显示“#NAME?”，函数“ISERROR(A3)”返回逻辑值TRUE。说明A3有错误。（13）ISNUMBER函数测试参数是否是数值，若是数值则返回逻辑真值TRUE，否则返回逻辑假值FALSE。格式为：ISNUMBER(参数表达式)例如，函数“ISNUMBER(”中国”)”将返回FALSE。（14）LEFT函数从一个文本字符串的第一个字符开始，返回指定数值的子字符串。格式为：LEFT(文本表达式,数值表达式)例如，函数“LEFT(“GDTYWUI”,4)”将返回子字符串“GDTY”。（15）LEN函数返回文本字符串中字符的个数。格式为：LEN(文本表达式)例如，在A5单元格中存有文本数据“KJDU上下 左右！”，函数“LEN（A5）”将返回数值11。注意，一个标点符号及空格也要算作一个字符。（16）MAX或MIN函数求出一组数中的最大值或者最小值。格式为：MAX(数值表达式1,数值表达式2)或者MIN(数值表达式1,数值表达式2)例如，函数“MAX（3/2，3，6/3）”将返回数值3.（17）MID函数从一个文本字符串的指定位置开始，返回指定个数的子字符串。格式为：MID(文本表达式,数值表达式1,数值表达式2)例如，在A2单元格中有文本数据“中华人民共和国”，函数“MID(A2,3,2)”将返回文本字符串“人民”。（18）MOD函数返回两数相除的余数。格式为：MOD(数值表达式1,数值表达式2)例如，在A1单元格中有数值123，在A2单元格中有数值12，函数“MOD(A1，A2)”将返回123除以12所得的余数3。（19）MONTH函数返回指定日期或引用单元格中的日期数据的月份（112）。格式为：MONTH(日期数据或日期单元格引用)例如，在A1单元格中有日期数据1988-11-1，函数“MONTH(A1)”将返回月份11。（20）NOW函数返回系统的当前日期和时间。格式为：NOW()例如，假设现在是2007年6月12日晚上8点35分，函数“NOW()”将返回这个年月日和时间2007-6-12 20:35。（21）OR函数在给出的所有参数中，当其值均为逻辑假（FALSE）时返回逻辑假（FALSE），只要有一个参数的值为逻辑真则返回逻辑真（TRUE）。格式为：OR(逻辑表达式1,逻辑表达式2)例如，函数“OR(32,253*8,5436+18)”将返回逻辑真（TRUE），因为第一个参数结果为TRUE。（22）RIGHT函数返回从一个文本字符串的最后一个字符开始，向左截取指定个数的子字符串。格式为：RIGHT(文本表达式,数值表达式)例如，在A3单元格中有文本数据“ABSDEFG”，函数“RIGHT(A3,9/3)”将返回字符串“EFG”。（23）ROW函数返回一个引用的行序号，在代码编写中会用到这个函数。格式为：ROW(单元格引用)（24）ROWS函数返回指定的单元格区域或者数组的行数，在代码编写中会用到这个函数。其格式为：ROWS(数组名或者区域引用)例如，函数“ROWS(A1:A258)”将返回258。（25）INDIRECT函数返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。格式为：INDIRECT(单元格引用字符串,逻辑值)说明：单元格的引用可以包含像A1样式的引用、R1C1样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果不是合法的单元格的引用，函数INDIRECT返回错误值#REF。逻辑值指明包含在单元格引用中的引用类型。如果为TRUE或省略，单元格引用被解释为A1样式的引用。如果为FALSE，单元格引用被解释为R1C1样式的引用。例如，有如下的工作表数据：函数“INDIRECT($A$1)”将返回25；函数“INDIRECT($A$2)”将返回ABCD。（26）RAND函数返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。格式为：RAND()说明：若要生成a与b之间的随机实数，可写成RAND()*(b-a)+a的形式；如果要使用函数RAND()生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。 例如，函数“RAND()”将返回介于0到1之间的一个随机数（变量）；函数“RAND()*100将返回大于或者等于0但小于100的一个随机数（变量）；函数“RAND()*(10-5)+5”将返回介于5和10之间的随机数。（27）SUM 函数返回所有数值参数值的和。格式为：SUM(数值表达式1,数值表达式2)说明：数值表达式可以是直接给出的数值，也可以是单元格引用。（28）SUMIF函数返回符合指定条件的单元格区域内的数值之和。格式为：SUMIF(单元格区域,相加的条件, 相加的实际单元格)说明：“单元格区域”中的单元格都必须是数字和名称、数组和包含数字的引用。空值和文本值将被忽略。“相加的条件”的形式可以是数字、表达式或文本。例如，条件可以表示为 32、32、32，如果是表达式或文本必须用引号括起来。如果省略参数“相加的实际单元格”则当“单元格区域”中的单元格符合条件时，它们既按条件计算，也执行相加。“相加的实际单元格”与“单元格区域”的大小和形状可以不同。 例如，有下列工作表数据：函数“SUMIF(A2:A5,200000,B2:B5)”在A2至A5区域中判断金额高于200,000元的单元格是A3至A5，返回对应的佣金（B3至B5）之和为64,000元。函数“SUMIF(A2:A5,200000)”在A2至A5区域中判断金额高于200,000元的单元格是A3至A5，因省略了“实际求和的单元格”参数，故返回A2至A5区域中满足条件的单元格（A3至A5）的金额之和为980,000元。 函数“SUMIF(A2:A5,=310000,B2:B3)”返回“单元格区域”中金额等于310,000元的佣金之和为22,000元。 （29）TEXT函数根据指定的数值格式将相应的数字转换为文本型数据。（30）TODAY函数返回系统日期。（31）VALUE函数将一个由数字组成的文本字符串转换为数值型数据。（32）WEEKDAY函数返回指定日期所对应的星期几。5.函数的输入如果创建带函数的公式，可直接在当前单元格输入，在函数编辑框和单元格中都会显示输入的公式和函数，而利用“插入函数”对话框将有助于输入函数。在公式中输入函数时，“插入函数”对话框将显示在当前单元格的下方，会显示出函数的名称、各个参数、各个参数的说明、函数的当前结果以及整个公式的当前结果，在对话框中单击某个函数会出现该函数的解释。当输入“=”和开头的几个字母或显示触发字符之后，Excel会在单元格的下方显示一个动态下拉列表，该列表中包含与这几个字母或该触发字符相匹配的有效函数、参数和名称。然后就可以将该下拉列表中的一项插入公式中。参数可直接从键盘输入，也可引用单元格区域中的数据（见后文“单元格数据的引用”部分），或者用鼠标单击单元格（注意，若要输入多个单元格的数据则要正确输入相应的分隔符号），这要根据实际情况灵活运用。6.函数的嵌套有时需要将某函数作为另一函数的参数使用，这就形成了函数的嵌套。嵌套函数最多可以嵌套64个级别的函数。下面的公式仅在一组数值(A2:A5)的平均值小于90时返回0，否则返回另一组数值(D3:D7)的和。IF(AVERAGE(A2:A5)90,0,SUM(D3:D7)AVERAGE和SUM函数嵌套在IF函数中。当嵌套函数作为参数使用时，它返回的值的类型必须与参数使用的类型相同。例如，如果参数返回一个逻辑值TRUE或者FALSE，那么嵌套函数也必须返回一个TRUE或者FALSE。否则，将显示#VALUE！错误信息。7.单元格数据的引用引用的作用在于标识工作表上的单元格或单元格区域，并告知Excel在何处查找公式中所使用的数据。通过引用，可以在一个公式中使用工作表不同部分中包含的数据，或者在多个公式中使用同一个单元格的数值，还可以引用同一个工作簿中其他工作表上的单元格和其他工作簿中的数据。引用其他工作簿中的单元格被称为“链接”或“外部引用”。（1）引用的样式单元格引用有以下几种样式：一个单元格的引用就用它的地址标识，如A列第10行处的单元格就可输成“A10”。不连续的单元格引用“,”运算符分隔各单元格，如“A2,A5,B12,D58”。同一行中连续单元格的引用可利用“:”运算符，如在A列第10行到第20行之间的单元格引用就输成“A10:A20”。同一列中连续单元格的引用也可利用“:”运算符，如第15行的A列到G列之间的单元格引用可输入“A15:G15”。某行中的所有单元格引用只需要输入行标识就行了，如第5行中的全部单元格输入“5:5”，第2行到第8行之间的全部单元格则输入“2:8”。某列中的全部单元格引用则只用列标识，如第C列中的全部单元格引用输入“C:C”，第B列到第G列之间的全部单元格引用输入“B:G”。某个单元格区域的引用包括以指定的两个单元格连线为对角线所围成的矩形区中所有的单元格的数据。如第F列第5行到第H列第10行之间的单元格区域引用输入“F5:H10”。 引用同一个工作簿中另一个工作表上的单元格区域，需要在单元格区域前面加上工作表的名称和一个感叹号。例如当前工作表是Sheet1，现要引用Sheet2中单元格区域，应输入下面的格式，感叹号的作用是将工作表引用与单元格区域引用分开。“SUM(Sheet2!A2: