Excel常用函数.docx

1、Excel常用函数 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五、逻辑函数

2、81.IF函数 82.AND函数 8六、财务函数 81.SLN函数 82.DB函数 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函数 远离零值，向下（绝对值减小的方向）舍入数字 ROUN

3、D(数值，小数位) 例：=ROUND(21.5，-1) 四舍五入到小数点左侧一位 结果20 =ROUND（-1.475,2） 将-1.475四舍五入到两位小数 结果-1.48 2）按指定基数倍数舍入 CEILING函数 向上舍入 FLOOR函数 向下舍去 FLOOR(数字，舍入基数) 括号内参数符号要相同 3）数值取整 INT函数 INT(数字) 将数字向下舍入到最接近的整数。 TRUNC函数与ROUNDWN类似3.SUMIF函数 SUMIF（区域，条件，求和区域） 按给定条件对指定单元格求和。 1)条件中可以使用通配符、问号（？）、星号（*）。问号匹配任意 单个字符，星号匹配任意一串字符。如

4、果要查找实际的问号或星号， 请在该字符前键入波形符（）。 )当省略了SUMIF，则结果为区域中符合条件的值之和。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)条件计数 二班数学出现次数=SUMP

5、RODUCT(A2:A10=”二班”)*(B2:B10=”数学”)3)条件求和 二班数学均分=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及小于

6、1的均匀分布随机实数，每次计算工作表时 都将返回一个新的随机实数。RAND（） 若要生成a与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是接近于EXCE

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

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

9、，行序数，列序数，区域序数) 3.MATCH函数 返回在指定方式下与指定数值匹配的数组中元素的相应位置。 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:

10、B5,-1)由于数据区域B2:B5不是按降序排列，所以返回错误值#N/A4.ROW、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)

11、返回值1、2、0第一二行时不满足？=MOD(ROW(),3)-1 返回值0、1、-1=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函数 根据所指定的字符数，返回文本字符

12、串中第一个字符或前几个字符。 LEFT(字符串，字符个数)2.FIND函数 用于在第二个文本串中定位第一个文本串，并返回第一个文本串的 起始位置的值，该值从第二个文本串的第一个字符算起。 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 Influen

13、tial movie =LEN(A2) 结果：174.RIGHT函数 根据所指定的字符数返回文本字符串中最后一个或多个字符。 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函数 将数值转换为按指定数字格式表示的

14、文本。 TEXT(值，数值格式)示例公式说明结果1=A1&”占总GDP比重”&TEXT(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(数值，引用，排位方式) 排位方式为0，降序排列；不为0，升序排列。五、逻辑函数1.I