Excel常用函数

1、 excel工作表函数与公式一、数学函数21.sum函数22.常用数值舍入函数23.sumif函数24.mod函数25.sumproduct函数36.subtotal函数37.rand函数3二、查询和引用函数41. lookup函数42.index函数53.match函数54.row、column函数55.offset函数6三、文本函数61.left函数62.find函数63.len函数64.right函数75.mid函数76.text函数7四、统计函数71.countif函数72.max函数73.rank函数8五、逻辑函数81.if函数82.and函数8六、财务函数81.sln函数82.db

2、函数83.vdb函数8七、日期和时间函数81.datedif函数82.now函数93. date函数94. eomonth函数9八、信息函数91.is类函数9一、数学函数1.sum函数 sum(数值1，数值2,数值n) 求和，返回某一单元格区域所有数字之和。 (alt+回车或求和快捷键)2.常用数值舍入函数 1）按指定小数位数计算 round函数 用于返回指定小数位数的四舍五入运算。 roundup函数 远离零值，向上舍入数字 roundwn函数 远离零值，向下（绝对值减小的方向）舍入数字 round(数值，小数位) 例：=round(21.5，-1) 四舍五入到小数点左侧一位 结果20 =r

3、ound（-1.475,2） 将-1.475四舍五入到两位小数 结果-1.48 2）按指定基数倍数舍入 ceiling函数 向上舍入 floor函数 向下舍去 floor(数字，舍入基数) 括号内参数符号要相同 3）数值取整 int函数 int(数字) 将数字向下舍入到最接近的整数。 trunc函数与roundwn类似3.sumif函数 sumif（区域，条件，求和区域） 按给定条件对指定单元格求和。 1)条件中可以使用通配符、问号（？）、星号（*）。问号匹配任意 单个字符，星号匹配任意一串字符。如果要查找实际的问号或星号， 请在该字符前键入波形符（）。 ii)当省略了sumif，则结果为区域

4、中符合条件的值之和。4.mod函数 返回两数相除的余数。结果的正负号与除数相同。 mod(被除数，除数)示例公式说明结果1=mod(3,2)3/2的余数12=mod(-3,2)-3/2的余数，符号与除数相同13=mod(3,-2)3/-2的余数，符号与除数相同-14=mod(-3,-2)-3/-2的余数，符号与除数相同-15.sumproduct函数 在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。 sumproduct（数组1，数组2，）1)乘积求和2)条件计数 二班数学出现次数=sumproduct(a2:a10=”二班”)*(b2:b10=”数学”)3) 条件求和 二班数学均

5、分=sumproduct(a2:a10=二班)*(b2:b10=数学)*(c2:c10)数学均分之和=sumproduct(b2:b10=数学)*(c2:c10)6.subtotal函数 返回列表或数据库中的分类汇总。 subtotal(函数序号，引用1，) a1 24 a2 3 a3 6 例：=subtotal（9，a1:a3）对a列使用sum函数计算出的分类汇总 结果：33=subtotal（1，a1:a3）对a列使用average函数计算出的分类汇总 结果：117.rand函数 返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时 都将返回一个新的随机实数。rand（） 若要生成a

6、与b之间的随机实数，使用：rand()*(b-a)+a示例公式说明结果1=rand()介于0和1之间的一个随机数变量2=rand()*100大于等于0但小于100的一个随机数变量二、查询和引用函数1. lookup函数 从单行或单列区域或者从一个数组返回值。 具有两种语法形式：向量形式和数组形式。 lookup（查找值，查找向量，返回向量）（1） 求a列最后一个文本 =lookup(“座”，a:a)（2）求a列最后一个数字，可输入以下公式： =lookup(9e+307,a:a)9e+307是接近于excel中能处理的最大数，可以认为是excel中最大正数。lookup在搜索不到搜索值时，会搜

7、索区域中最后一个小于搜索值的数据。（3） 求a列最后一个不为空单元格的内容，可以输入以下公式： =lookup(2,1/(a:a”),a:a)（4） 逆向查询 =lookup(1,0/(条件1)*(条件2)*(条件n),目标区域或数组) 注意事项：1）lookup函数的使用要求查询条件按照升序排列，所 以使用该函数前需要对表格进行排序处理，2）查询的条件可以高于 查询条件列的最大值，但不能低于查询条件列的最小值。vlookup函数 在表格数组的首列查找指定的值，并由此返回表格数组当前行 中指定列处的数值。 vlookup(查找值，数据表，序列数，匹配条件) 匹配条件为true(1)或省略，则返

8、回精确匹配值或近似匹配值， 为false(0)则只寻找精确匹配值。hlookup函数2.index函数 返回表格或数组中的元素值，此元素由行序号和列序号共同决定。 数组形式 index(数组，行序数，列序数) 行列序数不为0时，返回交叉处的单元格的值。行(列)序数为0，返回整行(列)的数组数值；行(列)以水平(垂直)单元格区域的形式输入；按键输入数组公式。公式说明结果=index(1,2； 3,4，0,2)返回数组常量中第一行、第二列的值并且返回数组常量中第二行、第二列的值 2 4引用形式 index(参考区域，行序数，列序数，区域序数) 3.match函数 返回在指定方式下与指定数值匹配的数

9、组中元素的相应位置。 match(查找值，查找区域，匹配类型) 匹配类型为1或省略，查找小于或等于查找值的最大数值，查找区 域按升序排列：-2，-1,0,1,2，a-z、false、true；为0时，查找 等于查找值的第一个数值，按任何顺序排列；为-1时，查找大于或 等于查找值查找值的最小数值，按降序排列。示例公式说明结果1=match(35,b2:b5,1)此处无正确的匹配，返回b2:b5中最接近下一个值的位置12=match(98,b2:b5,0)数据区域b2：b5中98的位置33=match(40,b2:b5,-1)由于数据区域b2:b5不是按降序排列，所以返回错误值#n/a4.row、

10、column函数1)row函数 row(参照区域) 返回引用的行号。 column函数 column(参照区域) 返回给定引用的列标。 rows函数 rows(数组) 返回引用或数组的行数。2）所组成的数字序列=int(row()-1)/2+1)返回的结果是1、1、2、2、3、3修改公式中的2为3，序列会变为1、1、1、2、2、2、3、3、3=mod(row(),3) 返回值1、2、0=mod(row()-1,3) 返回值0、1、2=mod(row()-2,3) 返回值2、0、1=mod(row()-3,3) 返回值1、2、0第一二行时不满足？=mod(row(),3)-1 返回值0、1、-1

11、=mod(row(),3)-2 返回值-1、0、-2=mod(row(),3)-3 返回值-2、-1、-3=mod(row()-1,3)+1 返回值1、2、3=mod(row()-1,3)+2 返回值2、3、4=mod(row()-1,3)+3 返回值3、4、55.offset函数 以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引 用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。 offset(参照区域，行数，列数，高度，宽度)三、文本函数1.left函数 根据所指定的字符数，返回文本字符串中第一个字符或前几个字符。 left(字符串，字符个数)2.find函数 用于在第

12、二个文本串中定位第一个文本串，并返回第一个文本串的 起始位置的值，该值从第二个文本串的第一个字符算起。 find(要查找的字符串，被查找的字符串，开始位置) 例：a2 michael jordan示例公式说明结果1=find(“m”,a2)a2字符串中第一个“m”的位置12=find(“a”,a2)a2字符串中第一个“a”的位置53=find(“a”,a2,6)a2字符串中从第6个字符开始查找第一个“a”位置13findb函数3.len函数 返回文本字符串中的字符个数。len(字符串) 例：a2 influential movie =len(a2) 结果：174.right函数 根据所指定的字

13、符数返回文本字符串中最后一个或多个字符。 rightb(字符串，字节个数)5.mid函数 返回文本字符串中从指定位置开始的特定数目的字符。 mid(字符串，开始位置，返回字符的个数)示例公式说明结果1=mid(a2,1,5)a2单元格字符串的5个字符，从第一个字符开始beaut2=mid(a2,7,20)a2单元格字符串的20个字符，从第7个字符开始ful flowers3=mid(a2,20,5)因为要提取的第一个字符的位置大于a2单元格字符串的长度返回空文本6.text函数 将数值转换为按指定数字格式表示的文本。 text(值，数值格式)示例公式说明结果1=a1&”占总gdp比重”&tex

14、t(b1,”0%”)将a1和b1单元格里的内容以及引号间的内容，合并为一句工业占总gdp比重45%2=a2&”的gdp为”&text(b2,”)将a2和b2单元格里的内容以及引号间的内容，合并为一句服务业的gdp为1000亿四、统计函数1.countif函数 计算区域中满足给定条件的单元格的个数。countif(区域，条件) counta函数：返回参数列表中非空值的单元格个数。 count函数：返回包含数字的单元格的个数。2.max函数 返回一组值中的最大值 max（数字2,数字2，） min函数：最小值3.rank函数 返回一个数字在数字列表中的排位。rank(数值，引用，排位方式) 排位方

15、式为0，降序排列；不为0，升序排列。五、逻辑函数1.if函数 if函数 if(条件，结果1，否则结果2) 根据对指定的条件计算结果为true或false,返回不同值。 例：=if(a1=100，“预算内”，“超出预算”)2.and函数 “与”运算，真真得真，真假得假,与if函数嵌套使用。 =and(true,false) 结果flase or函数：“或”运算,真真得真，真假得真。 not函数：“非”运算，对参数求反。 六、财务函数1.sln函数 返回某项资产在一个期间中的线性折旧值。 sln（原值，残值，折旧期限）2.db函数3.vdb函数七、日期和时间函数1.datedif函数 用于计算两个

16、日期之间的天数、月数或年数。 datedif（开始日期，终止日期，比较单位unit）unit返回“y”一段时期内完整的年数“m”一段时期内完整的月数“d”一段时期内的天数“md”开始日期和终止日期相差的天数。忽略日期的月数和年数“ym”开始日期和终止日期相差的月数。忽略日期的天数和年数“yd”开始日期和终止日期相差的天数。忽略日期的年数2.now函数 返回当前日期和时间所对应的序列号。3. date函数 date(年year，月month，日day) 返回代表特定日期的序列号。4. eomonth函数 eomonth(指定日期，月数) 返回指定月份数之前或之后的月份的最后一天的日期。dates

17、tring函数将日期转换为yy年mm月dd天numberstring函数将数值转换为大写（1汉字小写2汉字大写3汉字读数）numberstring(指定数值，返回类型1、2、3)若数值中有小数，则四舍五入后转换。八、信息函数1.is类函数可以检验数值的类型并根据参数取值返回ture或falseis*(value) v可以是空白、错误值、逻辑值、文本、数字或引用函数如果为下面的值，则返回tureisblank值为空白单元格iserr值为任意错误值（除去#n/a）iserror值为任意错误值（#n/a、#value!、#ref、#div/0等）islogical值为逻辑值isna值为错误值#n/a（值不存在）isnontext值为不是文本的任意项（值为空白单元格是返回ture）isnumber值为数字isref值为引用istext值为文本