文章目录
- 一.其他函数:
- 1. match(lookup_value, lookup_array, [match_type])
- 2. index(array, row_num, [column_num])
- 3. offset(reference,rows,cols,height,width)
- 4. mid(text, start_num, num_chars)
- 5.find(find_text,within_text,start_num);
- 6.large(Array,k)
- 7.VLOOKUP(要查找的值, 查找区域, 要返回的结果在查找区域的第几列, 精确匹配或近似匹配):垂直方向查找
- 8.HLOOKUP():(要查找的值, 查找区域, 要返回的结果在查找区域的第几列, 精确匹配或近似匹配):水平方向查找
- 9.ROW()
- 10.COLUMN()
- 11. INDIRECT(ref_text, [a1])
- 12. Choose(index_num,value1, value2,...)
- 二.数学函数:
- 三.文本函数
- 四.统计函数
- 五.日期函数
一.其他函数:
1. match(lookup_value, lookup_array, [match_type])
函数作用:用于查找数据表格中的特定值,并返回其所在的位置
参数:match函数有3个参数,分别是:①查找值,②查找区域,③匹配方式,0表示精确匹配;-1表示查找大于或等于查找值的最小值,此时查找区域需要降序排列;1表示查找小于或等于查找值的最大值,此时查找区域需要升序排列。
案例:=MATCH(“赵六”,B5:B12,0)
2. index(array, row_num, [column_num])
函数作用:返回表格或区域中的值或值的引用。
参数:index函数共有三个三个一次为(数组或区域,行号,列号)
案列:=INDEX(A2:B3,2,1)—位于区域 A2:B3 中第二行和第一列交叉处的值。
3. offset(reference,rows,cols,height,width)
函数作用:以指定的(单元格或相连单元格区域的引用)为参照系,通过给定偏移量得到新的引用。返回的引用可以是一个单元格也可以是一个区域(可以指定行列数)。
参数:依次为参照单元格,行偏移量,列偏移量,返回几行,返回几列)
案例: =OFFSET(B2,2,1,1,1)–以B2单元格为参照系,向下2,向右1,返回一行一列。所以返回值100。
4. mid(text, start_num, num_chars)
是常用的字符串提取函数,它可以从字符串中指定的位置开始从左往右截取指定的字符个数。
5.find(find_text,within_text,start_num);
作用:用来对原始数据中某个字符串进行定位,以确定其位置。
参数解释说明:Find_text:要查找的字符串即目标字符。Within_text :被查找字符串。包含要查找目标字符的单元格或引用的字符串。就是说要在这个单元格或字符串内查找目标字符。Start_num :指定开始进行查找的字符。比如Start_num为1,则从单元格内第一个字符开始查找目标字符。如果忽略 start_num,则默认其为 1。
6.large(Array,k)
作用:用于返回一组数据中的第K个最大值。。
参数:Array被操作数组,k为是要返回的数据数组或单元格范围中的位置
案例:查询总分最高分数是多少=LARGE(D2:D13,1) 即可查询到最高分
7.VLOOKUP(要查找的值, 查找区域, 要返回的结果在查找区域的第几列, 精确匹配或近似匹配):垂直方向查找
8.HLOOKUP():(要查找的值, 查找区域, 要返回的结果在查找区域的第几列, 精确匹配或近似匹配):水平方向查找
9.ROW()
引用行的数据:函数主要是返回引用的行号
语法结构为:=ROW([Reference])ROW():
案例:比如在表格第2行输入=ROW(),则返回值为2;ROW(单元格地址): 返回单元格所在的行数,比如输入=ROW(A5),则返回值为5;ROW(开始行数:终止行数): 返回引用行的行数,结果为一组数字。比如输入=ROW(1:5) ,返回第1:5行所在的行数{1;2;3;4;5}。
10.COLUMN()
引用列的数据:获取列号
案例:=column() 返回公式所在单元格的列号,用数字表示;=column(E3) 返回指定单元格的列号,用数字表示,即E3单元格的列号5;=colunm(E3:H9) 返回左上角单元格的列号,用数字表示,即E3单元格的列号5
11. INDIRECT(ref_text, [a1])
文本字符串指定的引用
对单元格的引用,此单元格包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对作为文本字符串的单元格的引用。如果 ref_text不是合法的单元格引用,则INDIRECT返回错误值。[a1]:是逻辑值(TRUE、FALSE),用于指定包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略,ref_text被解释为A1-样式的引用。
12. Choose(index_num,value1, value2,…)
索引值指定参数列表中的数值。
如果index_num为1,则CHOOSE返回value1;如果为2,则CHOOSE返回value2,以此类推。如果 index_num 小于 1 或大于列表中最后一个值的索引号,则CHOOSE返回错误值 #VALUE!。如果index_num为小数,则在使用前将被截尾取整
二.数学函数:
1.INT(a)
截取整数部分:Int(5.6)–5
2.MOD(a,b)
求a除b的余数 MOD(10,3)–1
3.ROUND(a,b)
对a四舍五入,保留b位小数 Round(7.335,2)—7.34
4.ROUNDUP(a,b)
向上四舍五入–Round(7.335,2)—7.34
5.ROUNDDOWN(a,b)
向下四舍五入 Round(7.335,2)—7.33
6.ABS(a)
取a的绝对值 Abs(-1)—1
7.SQRT()
算术平方根 SQRT(4)–2
8.RAND()
产生0-1随机实数,可为浮点数
9.RANDBETWEEN(a,b)
生成指定区间的随机整数
三.文本函数
1.MID(a,b,c)
从字符串中指定的位置开始从左往右截取指定的字符个数。a是包含要提取字符的文本字符串,b是文本中要提取的第一个字符的位置。c指定希望MID从文本中返回字符的个数。
a) =MID(“sdkfj”,1,3)–sdk
2.LEFT(a,b)
从左取子串,a为被取的文本,b为截取字符个数 =LEFT(“dfgdfg”,3)—dfg
3.RIGHT(a,b)
从右取子串,a为被取的文本,b为截取字符个数 =right(“dfgdfg”,3)—kfg
4.LEN()
文本长度 =LEN(“kdsjvnksdn”)–16
5.TEXT()
数字转化文本格式 表示将数值转换成指定的数值格式,日期在内部存储也是数值
6.REPT(a,b)
文本重复,a表示重复的文本,b表示重复次数 =REPT(2,3)–222
7.REPLACE(o,m,n,x)
替换特定位置处的文本,o旧文本,m指定开始的位置,n指定替换的长度,x新文本,用于替换指定的旧文本 常见的把手机号码后四位屏蔽掉,输入公式=REPLACE(A2,8,4,“****”)
8.substitute(a,o,n,c)
替换特定符号的文本,a表示需要替换其中字符的文本,o表示需要替换的旧文本,n用于替换旧的文本,c可选用来指定要替换第几个出现的旧文本。=SUBSTITUTE(“odsfodfofofofsdf”,“o”,“X”,3)–odsfodfXfofofsdf
四.统计函数
1.MAX()
求最大
2.MIN()
求最小
3.SUM()
求和
4.COUNT()
数值计数
5.COUNTA()
计数包括文本
条件统计函数 若条件使用单元格不能直接引用,必须使用&符号相连接才行
6.AVERAGE()
求平均(会自动忽略文本求和数值)
7.COUNTIF(a,b)
条件计数(a,b),a为数组,b为条件
9.SUMIF(a,b)
条件求和(条件要用&来引用单元格,不能直接写),条件计数(a,b),a为数组,b为条件
10.AVERAGEIF(a,b)
条件平均, a为数组,b为条件
11.COUNTIFS()
多条件计数(里面的多条件是且的关系) 条件区域不分先后
12. SUMIFS()
多条件求和 求和区域必须在前面
13. AVERAGEIFS()
多条件平均 同上先选择统计区域,后跟条件区域.
14. FREQUENCY()
求数据分布频率
15. RANK(number,ref,[order]),排名次
函数名后面的参数中number为需要求排名的那个数值或者单元格名称(单元格内必须为数字),ref为排名的参照数值区域,order的为0和1,默认不用输入,得到的就是从大到小的排名,若是想求倒数第几,order的值请使用1。
五.日期函数
1.TODAY(): 返回当前日期
2.DATE() :计算给定的日期
3.NOW():当前日期和时间(电脑系统的时间)
4.WEEKNUM(日期):当前周次
5.YEAR/MONTH/DAY/HOUR/MINUTE/SECOND(日期)
拆分得年,月,日,小时,分钟和秒
6.EDATE():指定日期前后月份的日期
8.EOMONTH:某个月份最后一天的序列号
9.DATEDIF(开始日期,结束日期,第三参数):
计算日期差,开始日期要小于结束日期 ,不然会报错,datedif(第三参数的含义):
公式 | 参数 | 说明 |
---|---|---|
=DATEDIF(A2,B2,“Y”) | Y | 相差年数 |
=DATEDIF(A3,B3,“M”) | M | 相差总月数 |
=DATEDIF(A4,B4,“YM”) | YM | 一年内相差月数 |
=DATEDIF(A5,B5,“D”) | D | 相差总天数 |
=DATEDIF(A6,B6,“YD”) | YD | 一年内相差天数 |
=DATEDIF(A7,B7,“MD”) | MD | 一月内相差天数 |