Office高级应用之Excel公式与函数

MicrosoftOffice——Excel2003公式——函数公式——函数函数是一些预先定义的内置公式，例如：1、SUM求和2、AVERAGE求平均值3、MAX求最大值4、MIN求最小值5、COUNTIF：统计给定区域内满足特定条件的个数6、RANK排位7、IF8、COUNT9、ROUND函数输入1、直接输入函数函数输入2、使用“公式选项板”输入函数函数输入3、利用“粘贴函数”功能

“插入”—“函数”函数函数的形式：函数名（参数1，参数2，……）参数：常量、单元格引用、区域、其他函数例子：=sum(a1:d1)；

=average(a1:a4，b5)函数形式

在公式或函数中使用单元格称为单元格的引用。(1)相对引用形式：A1、A1:D4特点：把公式复制到别处时，被引用的单元格地址将相应变化。如何变化？单元格引用(2)绝对引用形式：$A$1、$A$1:$D$4特点：把公式复制到别处时，被引用的单元格地址保持不变。单元格引用(3)混合引用形式：$A1、A$1特点：复制时，不加$的行（列）号发生变化，加$的行（列）号保持不变。单元格引用数学函数SUMSUMIFRoundSUM函数格式：SUM（number1，number2，……）功能：返回指定参数所对应的数值之和例如：SUM(A1:A3)、SUM(B2:B4,C5)、SUM(23,45,88)SUMIF函数SUMIF函数：用于计算符合指定条件的单元格区域内的数值进行求和格式为：SUMIF（range，criteria，sum_range）range是条件判断的单元格区域；criteria是指定条件表达式；sum_range

是需要计算的数值所在的单元格区域计算：属性值超过160,000的佣金的和(63,000)另：见题2（4）计算性别是“男”的英语总分等效于：SUMIF(B2:B5,“男”,C2:C5)ROUND函数格式：ROUND（number，nun_digits）功能：按指定的位数对数值进行四舍五入统计函数AverageMax/MinCOUNTCOUNTIFRANKAVERAGE函数功能：返回指定参数所对应数值的算术平均数格式：AVERAGE（number1，number2，……）number1，number2等是指定所要进行求平均值的参数注意：该函数只对参数的数值求平均数，如区域引用中包含了非数值的数据，则AVERAGE不把它包含在内。1(3)MAX、MINMAX函数：求参数列表中对应数字的最大值;MIN函数：求参数列表中对应数字的最小值;格式：MAX（number1，number2，……）

MIN（number1，number2，……）number1、number2、……：从中找出最大值或最小值的1～30个数字参数COUNT函数功能：用于返回数字参数的个数，即统计单元格区域中含有数值类型的单元格个数格式：COUNT(value1，value2，……)value1，value2，……表示包含或引用各类型数据的参数注意：1、函数可以最多附带上1～30个参数

2、只有数字类型的数据才能被统计类似函数：1、COUNTA函数：返回参数组中非空值的数目，即计算数组或单元格区域中数据项的个数；2、COUNTBLANK函数：计算某个单元格区域中空白单元格的数目；3、COUNTIF函数：计算区域中满足给定条件的单元格的个数。例子COUNTIF(D2:D5,”男”)功能：统计D2:D5区域中男性人数；COUNTIF（C2:C12,“<60”)功能：统计C2:C12单元格区域中成绩不及格的人数见练习题1（4）RANK返回一个数字在数字列表中的排位。格式：RANK

(number，ref，order)Number：为需要找到排位的数字。Ref

：为数字列表数组或对数字列表的引用。

Order：为０（零）或省略，降序；不为零，升序。

例：见题1（2）逻辑函数IFANDORIF函数IF（logical_test，value_if_true，value_if_false）logical_test：逻辑条件value_if_true：当值为“真”时的返回值value_if_false：当值为“假”时的返回值注意：在IF函数中三个参数中可以value_if_true或value_if_false，但不能同时省略例：2（2）、10（1）见题1（3）、2（2）文本函数Exact：比较两个文本字符串是否相同Concatenate：将多个字符文或单元格中的数据连接在一起Substitute：将字符串中的部分字符串以新字符串替代Replace：将某几位的文字以新的字符串替换。MID/LEFT/RIGHT功能：用来比较两个文本字符串是否相同。如果两个字符串相同，则返回“TRUE”，反之，则返回“FALSE”格式：EXACT（text1，text2）text1和text2：两个要比较的文本字符串注意：1、在字符串中如果有多余的空格，会被视为不同

2、EXACT函数在判别字符串的时候，会区分英文的大小写，但不考虑格式设置的差异EXACT函数功能：将多个字符文本或单元格中的数据连接在一起，显示在一个单元格中格式：CONCATENATE(text1，text2，……)text1，text2，……：需要连接的字符文本或引用的单元格注意：1、该函数最多可以附带30个参数

2、如果其中的参数不是引用的单元格，且为文本格式的，请给参数加上英文状态下的双引号使用“&”符连接也能达到相同的效果例：18（2）CONCATENATE函数功能：实现替换文本字符串中的某个特定字符串格式：SUBSTITUTE（text，old_text，new_text，instance_num）text：原始内容或是单元格地址old_text：要被替换的字符串new_text：替换old_text的新字符串如果字符串中含有多组相同的old_text时，可以使用参数instance_num来指定要被替换的字符串是文本字符串中的第几组。如果没有指定instance_num的值，默认的情况下，文本中的每一组old_text都会被替换为new_text。SUBSTITUTE函数功能：将某几位的文字以新的字符串替换。其替换功能与SUBSTITUTE函数大致类似格式：REPLACE（old_text，start_num，num_chars，new_text）old_text：原始的文本数据start_num：设置要从old_text的第几个字符位置开始替换num_chars：设置共有多少字符要被替换new_text：要用来替换的新字符串

例4（2）、8（1）REPLACE函数信息函数IsText

检测一个值是否为文本日期和时间函数Year(date)4（1）Month(date)Day(date)Now()4（1）HourMinuteSecond…查找和引用函数VLOOKUP函数功能：从一个数组或表格的最左列中查找含有特定值的字段，并返回表格数组当前行中其他列的值。V表示垂直方向。HLOOKUP函数功能：从一个数组或表格的最左列中查找含有特定值的字段，再返回同一列中某一指定单元格中的值格式：VLOOKUP（lookup_value，talbe_array，col_index_num，range_lookup）

look_value：要在数组中搜索的数据，它可以是数值、引用地址或文字字符串table_array：要搜索的数据表格、数组或数据库col_index_num：一个数字，代表要返回的值位于table_array中的第几列。range_lookup：一个逻辑值，如果其值为“TRUE”或被省略，则返回部分符合的数值；如果该值为“FALSE”时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃N/A”。例2（1）VLOOKUP函数功能：可以用来查询表格的第一行的数据

格式：HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）look_value：要在表格第一行中搜索的值

table_array：要搜索的数据表格、数组或数据库row_index_num：要返回的值位于table_array列中第几行

range_lookup：一个逻辑值，如果其值为“TRUE”或被省略，则返回部分符合的数值；如果该值为“FALSE”时，函数只会查找完全符合的数值，如果找不到，则返回错误值“＃N/A”。例9（1）HLOOKUP函数数据库函数是用于对存储在数据清单或数据库中的数据进行分析，判断其是否符合特定的条件。根据各自函数所具有的功能不同，可分为两大类：数据库信息函数：直接获取数据库中的信息数据库分析函数：分析数据库的数据信息数据库函数格式为：

函数名称（database，field，criteria）database：构成数据清单或数据库的单元格区域field：指定函数所使用的数据列criteria：一组包含给定条件的单元格区域

数据库函数数据库信息函数

DCOUNT函数：返回列表或数据库中满足指定条件的记录字段（列）中包含数值的单元格的个数DGET函数：从列表或数据库的列中提取符合指定条件的单个值DCOUNTA函数：返回列表或数据库中满足指定条件的记录字段（列）中非空单元格的个数数据库函数数据库函数数据库分析函数DAVERAGE函数：计算列表或数据库的列中满足指定条件的数值的平均值DMAX函数：返回列表或数据库的列中满足条件的最大值DPRODUCT函数：返回列表或数据库中满足指定条件的记录字段（列）中数值的乘积DSUM函数是用来返回列表或数据库中满足指定条件的记录字段（列）中的数字之和例5（2）财务函数PMT计算贷款按年、按月的偿还金额

PMT(rate,nper,pv,fv,type)rate:贷款利率nper:总贷款期限pv:贷款开始计算时已经入账的款项或一系列未来付款当前值的累积和（贷款金额）fv:未来值或在最后一次付款后希望得到的现金金额type：逻辑值，用以指定付款时间是在期初还是期末，1表示期初，0表示期末，默认为0【例】某店主决定向银行贷款50000元，年利息是6%，贷款年限5年，计算贷款按年偿还和按月偿还的金额各是多少？财务函数IPMT计算贷款每月应付的利息额（基于固定利率、等额分期付款的方式）

IPMT(rate,per,nper,pv,fv,type)rate：各期利率per：用于计算利息数额的期数nper：总贷款（投资）期【例】17(4)财务函数FV计算投资未来收益值

FV(rate,nper,pmt,pv,type)【例】管理者为某项工程进行投资，先投资50000元，年利率6%，并在接下来的5年中每年再投资5000年，那么5年后应得到的金额是多少？例：13(4)财务函数PV计算某项投资所需要的金额

PV(rate,nper,pmt,fv,type)【例】某个项目预计每年投资15000元，投资年限为10年，其回报年利率是15%，那