第4章Excel2003电子表格处理-3-公式与函数

第4章Excel电子表格处理

---公式与函数一、公式公式的使用公式输入以等号“=”开始，等号后面是由运算符、常量、单元格地址、函数及括号组成的表达式公式中可以使用单元格引用或已命名的单元格区域名称Excel包含4种类型的运算符：算术运算符、关系运算符、文本运算符和引用运算符Excel运算符运算顺序：引用运算符（区域、联合、交叉）、负号（-）、百分比（%）、乘方（^）、乘（*）和除（/）、加（+）和减（-）、连接运算符（&）、比较运算符（=、<、>、>=、<=、<>）的顺序进行运算。二、函数函数是一些已定义的公式，使用Excel提供的内置函数可以简化和缩短工作表中的公式，特别适用于执行复杂计算的公式。函数可以单独使用，也可以出现在公式中，合理使用函数将大大提高表格计算的效率。在单元格中输入函数也要以等号“＝”开始。其一般格式为：＝函数名(参数1,参数2,……)函数的输入单击“插入”→“函数”菜单命令或单击工具栏上的“粘贴函数”。在单元格中直接输入数据引用根据公式中单元格的引用方式不同，可分为绝对引用、相对引用、混合引用和三维引用、多维引用。数据引用一、相对引用相对引用的含义：如果在公式中使用相对引用，当复制公式时，目标单元格公式中的引用会根据目标单元格和原单元格的相对位移而自动产生变化。

Excel2003系统默认，所有新创建的公式均使用相对引用。相对引用的表示：直接用列标加行标表示。如：A6，C4都是相对引用例如，将单元格E3中公式“=SUM(B3:D3)”复制到单元格E4中，则公式变为“=SUM(B4:D4)”。二、绝对引用绝对引用的含义：如果在公式中使用相对引用，当复制公式时，目标单元格公式中的引用不会根据目标单元格和原单元格的相对位移而产生变化。绝对引用的表示：在行号和列号前加入“$”。如：$A$6，$C$4都是绝对引用。例如，将单元格E3中公式“=SUM($B$3:$D$3)”复制到单元格E4中，则公式仍为“=SUM($B$3:$D$3)”。三、混合引用混合引用的含义：在公式中既使用了相对引用，又使用了绝对引用，当进行公式复制时，绝对引用部分保持不变，相对引用部分随单元格位置的变化而变化。混合引用的表示：在行号或列号前加入“$”。如：$A6，A$6，$C4，C$4都是混合引用。例如，将单元格A5中公式“=SUM(A$1:A$4)”复制到单元格B6中，则公式变为“=SUM(B$1:B$4)”。1、计数类函数1.SUM函数功能：对给定的所有参数进行求和运算。调用格式：SUM（n1，n2，...）

说明：n1,n2,…代表需要求和的单元格或单元格区域参数，是数值型数据。如果参数中出现了逻辑型数据或字符型数据，则将其作为0处理。2.AVERAGE函数功能：计算所有参数的算术平均值。调用格式：AVERAGE(n1，n2，...)。说明：n1、n2、...是要计算平均值的1～30个参数。3.COUNT函数

功能：在数据区内统计数值型单元格的数量。调用格式：COUNT(参数)。说明：参数是包含或引用各种类型数据(1～30个)，其中只有数值型的数据才能被统计。4.COUNTIF函数

功能：在数据区按条件统计非空单元格的数量。调用格式：COUNTIF(数据区域，逻辑表达式)

说明：“数据区域”为需要计算其中满足条件的单元格数目的单元格区域。“逻辑表达式”为统计的条件，其形式可以为数字、表达式或文本。5.MAX函数功能：对给定的所有参数进行求其中的最大值运算。调用格式：MAX（n1,n2,…）说明：n1,n2,…代表需要求最大值的单元格或单元格区域参数，参数只能是数值型数据。如果参数中出现了逻辑型数据或字符型数据，则将其作为0处理。6.MIN函数功能：返回给定参数表中的最小值。调用格式：MIN(n1，n2，...)。说明：n1，n2，...是要从中找出最小值的1到30个数字参数

注意:

公式中的标点符号都必须是英文标点符号使用函数帮助帮助→目录→使用数据→函数引用找到相应函数查看内容2、逻辑类函数IF函数功能：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。调用格式：IF（条件，为真时的值，为假时的值）说明：“条件”是计算结果为TRUE或FALSE的任何数值或表达式“条件”为TRUE时函数的返回值是“为真时的值”，“条件”为FALSE时函数的返回值是“为假时的值”。“为真时的值”和“为假时的值”可以是一个表达式。

RANK函数用途：返回某一数值在一列数值中的相对于其他数值的排位。语法：RANK（Number,ref,order）参数：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数（如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值大）。举例：如在C2单元格中输入公式：=RANK(B2,$B$2:$B$31,0)，确认后即可得出丁1同学的语文成绩在全班成绩中的排名结果。

3、文本函数LEFT

用途：根据指定的字符数返回文本串中的第一个或前几个字符。语法：LEFT(text，num_chars)。参数：Text是包含要提取字符的文本串;Num_chars指定函数要提取的字符数，它必须大于或等于0。如果num_chars大于文本长度，则LEFT返回所有文本。

如果省略num_chars，则假定其为1实例：如果A1=电脑爱好者，则LEFT(A1，2)返回“电脑”。RIGHT

用途：RIGHT根据所指定的字符数返回文本串中最后一个或多个字符。语法：RIGHT(text，num_chars)。参数：Text是包含要提取字符的文本串;Num_chars指定希望RIGHT提取的字符数，它必须大于或等于0。如果num_chars大于文本长度，则RIGHT返回所有文本。如果忽略num_chars，则假定其为1。实例：如果A1=学习的革命，则公式“=RIGHT(A1，2)”返回“革命”。MID用途：MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(text，start_num，num_chars)。参数：Text是包含要提取字符的文本串。Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推;Num_chars指定希望MID从文本中返回字符的个数;实例：如果a1=电子计算机，则公式“=MID(A1，3，2)”返回“计算”。4、日期函数YEAR函数用途:

返回某日期对应的年份。返回值为1900到9999之间的整数。语法:YEAR(serial_number)参数:Serial_number

为一个日期值，其中包含要查找年份的日期。应使用DATE函数来输入日期，或者将日期作为其他公式或函数的结果输入。例如，使用DATE(2008,5,23)输入2008年5月23日。如果日期以文本的形式输入，则会出现问题。NOW函数用途:

返回当前日期和时间所对应的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。语法:NOW()说明:MicrosoftExcel可将日期存储为可用于计算的序列号。默认情况下，1900年1月1日的序列号是1而2008年1月1日的序列号是39448，这是因为它距1900年1月1日有39448天。序列号中小数点右边的数字表示时间，左边的数字表示日期。例如，序列号.5表示时间为中午12:00。函数NOW只有在重新计算工作表，或执行含有此函数的宏时改变。它并不会随时更新。TODAY函数用途:

返回当前日期的序列号。序列号MicrosoftExcel日期和时间计算使用的日期-时间代码。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。语法:TODAY()说明:MicrosoftExcel可将日期存储为可用于计算的序列号。默认情况下，1900年1月1日的序列号是1而2008年1月1日的序列号是39448，这是因为它距1900年1月1日有39448天。5、其它函数MOD

用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(number，divisor)参数：Number为被除数，Divisor为除数(divisor不能为零)。实例：如果A1=51，则公式“=MOD(A1，4)”返回3;=MOD(-101，-2)返回–1。

DATEDIF用途：计算返回两个日期参数的差值。语法：=DATEDIF(date1,date2,“y”)、=DATEDIF(date1,date2,“m”)、=DATEDIF(date1,date2,“d”)

参数：date1代表前面一个日期，date2代表后面一个日期；y（m、d）要求返回两个日期相差的年（月、天）数。实例：在C23单元格中输入公式：=DATEDIF(A23,TODAY(),"y")，确认后返回系统当前日期(用TODAY()表示）与A23单元格中日期的差值，并返回相差的年数。

特别提醒：这是Excel中的一个隐藏函数，在函数向导中是找不到的，可以直接输入使用，对于计算年龄、工龄等非常有效。频率分布函数FREQUENCYFREQUENCY（data_array,bins_array）功能：求出指定区域中数值的频率分布说明：data_array：为一数组或对一FREQUENCY（data_array,bins_array）数组值的引用；bins_array：为一数值或对数据区域的引用，设置data_array进行频率计算的分段点用法：选择结果区域输入函数及其参数同时按下ctrl+shift+enter三个键结束输入（注意：不要按回车结束公式）选择函数：CHOOSECHOOSE(index_num,value1,value2,…)功能：使用index_num返回数值参数清单中的数值。函数名称：VLOOKUP主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的数值。

使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数说明：Lookup_value代表需要查找的数值；Table_array代表需要在其中查找数据的单元格区域；Col_index_num为在table_array区域中待返回的匹配值的列序号（当Col_index_num为2时,返回table_array第2列中的数值，为3时，返回第3列的值……）；Range_lookup为一逻辑值，如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。

应用举例：参见图7，我们在D65单元格中输入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，确认后，只要在B65单元格中输入一个学生的姓名（如丁48），D65单元格中即刻显示出该学生的语言成绩。

VLOOKUP特别提醒：Lookup_value参数必须在Table_array区域的首列中；如果忽略Range_lookup参数，则Table_array的首列必须进行排序。在此函数的向导中，有关Range_lookup参数的说法是错误的。第4个参数如选True（或省略），第一列中的数值必须按升序排列，否则不能返回正确的数值。如果为False，不必进行排序。6、财务函数财务函数1PMT固定利率下，投资或贷款等额的分期偿还额

2FV求按每期固定利率及期满的本息总和

3PV每期固定利率及期满的投资或贷款当前值的累积和三个财务函数Excel的财务函数凡是投资的金额都以负数形式表示，收益以正数形式表示。在介绍具体的财务函数之前，我们首先来了解一下财务函数中常见的参数：未来值

(fv)--在所有付款发生后的投资或贷款的价值。期间数(nper)--为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。付款(pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。现值(pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。利率(rate)--投资或贷款的利率或贴现率。类型(type)--付款期间内进行支付的间隔，如在月初或月末，用0或1表示。1求贷款分期偿还额PMT函数用途：基于固定利率及等额分期付款方式，返回贷款的每期付款额。语法：PMT(rate,nper,pv,fv,type)参数：Rate

贷款利率。Nper

该项贷款的付款总数。Pv

现值，或一系列未来付款的当前值的累积和，也称为本金。Fv

为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。Type

数字0或1，用以指定各期的付款时间是在期初还是期末。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的"分期付款"。比如借购房贷款或其它贷款时，可以计算每期的偿还额。2求某项投资的未来值FV函数用途：基于固定利率及等额分期付款方式，返回某项投资的未来值。语法：

FV(rate,nper,pmt,pv,type)参数：Rate

为各期利率。Nper

为总投资期，即该项投资的付款期总数。Pmt

为各期所应支付的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。如果忽略pmt，则必须包括pv参数。Pv

为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略PV，则假设其值为零，并且必须包括pmt参数。Type

数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用Excel函数FV进行计算后，可以帮助我们进行一些有计划、有目的、有效益的投资。说明：应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。在所有参数中，支出的款项，如银行存