兼容vlookup等Excel常见函数

浏览次数：2348 最新更新：老慢于 2023-09-09

电子表格软件支持多种Excel函数处理各种不同类型的数据，以下是一些与Excel兼容的常用函数：

SUM函数:计算一列或一行数字的总和。
AVG函数:计算一列或一行数字的平均数。
COUNT函数:计算一列或一行数字的数量。
AVERAGEIF函数:根据条件计算一列或一行数字的平均值。
MAX函数:找出一列或一行数字中的最大值。
MIN函数:找出一列或一行数字中的最小值。
求和函数:SUMIF函数、SUM函数、AVERAGE函数，可以计算指定区域中所有满足条件的数字的总和。
平均值函数:AVERAGE函数、AVG函数，可以计算指定区域中所有数字的平均值。
最大值函数:MAX函数，MAX函数可以找出指定区域中所有数字中的最大值。
最小值函数:MIN函数，MIN函数可以找出指定区域中所有数字中的最小值。

这些函数只是一部分，还支持很多其他的Excel函数(参数带[ ]时表示其为可选参数，而不是必须参数）：

函数分类	函数用途	函数语法	函数示例
常用的计数函数	Count：统计时不包括空白、文本、逻辑值和错误	Count(Value1,Value2……)	Count(H$2:H$7)
常用的计数函数	Counta：统计时仅不包括空白单元格	Counta(Value1,Value2……)	CountA(H$2:H$7)
指定条件的计数函数	Countif ：根据条件计数	Countif(Range, Criteria)	COUNTIF(I:I,"品A")
指定条件的计数函数	Countifs：多个条件计数	Countifs(Criteria_Range1, Criteria1 ……)	COUNTIFS(I:I,"品A",J:J,2022)
常用的求和函数	Sum：进行基本数值求和	Sum(Number1, Number2…)	SUM(H4:H7)
	Sumif：根据条件求和	Sumif(Range, Criteria, Sum_Range…)	SUMIF(I:I,"品A",H:H)
	Sumifs：多个条件求和	Sumifs(Sum_Range, Criteria_Range1, Criteria1,…)	SUMIFS(H:H,I:I,"品A",J:J,2022)
		参数：Sum_Range为需要求和的单元格、区域或引用；
		Criteria_Range是用于条件判断的单元格区域；
		Criteria是由数字、逻辑表达式等组成的判定条件，为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。
	Sumproduct：用于返回相应数组或区域乘积的和	Sumproduct(Array1, [Array2], [Array3], ...)	SUMPRODUCT(H10:H13,I10:I13)
分类汇总求和（计算）的函数	Subtotal：返回一个数据列表的分类汇总	Subtotal(Function_Num, Ref1, [Ref2]…)
		参数：Function_num的常用类型
		1 求平均(average)	Subtotal(1,H$17:H$22)
		2 计数(count)	Subtotal(2,H$17:H$22)
		3 计数(counta)	Subtotal(3,H$17:H$22)
		4 求最大值(max)	Subtotal(4,H$17:H$22)
		5 求最小值(min)	Subtotal(5,H$17:H$22)
		9 求和(sum)	Subtotal(9,H$17:H$22)

常用的查找函数	Vlookup函数：用于在数据表的首列查找指定的数值，并返回数据表当前行中指定列处的值。	Vlookup(lookup_value,table_array,col_index_num,[range_lookup])	Vlookup(G21,'示例1-统计人年'!E:I,3,False)
		参数：Lookup_value 为需要在数据表首列进行搜索的值
		Table_array 为两列或多列数据，使用对区域或区域名称的引用。其第一列中的值是由lookup_value搜索的值。这些值可以是文本、数字或逻辑值。文本不区分大小写。
	Hlookup函数：使用方法类同于Vlookup，但两者的用法不同，Vlookup在列中垂直查找一个匹配的值，而Hlookup是在行中水平查找。	col_index_num 为 Table_array 中待返回的匹配值的列序号。
		range_lookup：True或省略，返回近似值；False，返回精确值；
		Hlookup(lookup_value,table_array,row_index_num,[range_lookup])
	Lookup函数	Lookup(lookup_value,lookup_vector,result_vector) 或Lookup(lookup_value,array)
	Lookup与Vlookup的区别	Lookup函数没有精确匹配，如果没有相等的值则取小于look_value最大的值；Lookup函数必须look_vector或array按升序排序； vlookup或hlookup可以指定列或行取值，而lookup第二个语法总是选择行或列的最后一个数值。
	Index函数：从数组中指定单元格取值	Index(Array,Row_Num,[Column_Num])	Index(C:D,34,2)
	Choose函数：从数字的列表中查找值	Choose(Index_Num,Value1,……)
	Offset函数：	Offset(Reference,Rows,Cols,[Height],[Width])	Offset(C26,3,2)
常用的引用函数	Columns：统计指定区域的列数	Columns(Array)	Columns(D:G)
	Rows：统计指定区域的行数	Rows(Array)	Rows(21:35)
	Match：得到给定值在列表中的位置	Match(lookup_value,lookup_array,[match_type])	MATCH(G24,'示例1-统计人年'!E5:E10,0)
	Match：得到给定值在列表中的位置	match_type类型： -1 查找大于或等于value的最小数值，array必须降序排列 1 查找小于或等于value的最大数值，array必须升序排列 0 查找等于value的第一个数值，array无需排序	MATCH(G24,'示例1-统计人年'!E5:E10,0)
如何获得单元格地址的函数	Column：返回指定单元格的列标号	Column(Reference)	Column(E40)
如何获得单元格地址的函数	Row：返回指定单元格的行标号	Row(Reference)	Row(E41)

提示：日期和时间都仅仅是数字而已，日期：从1900-1-1以1开始；时间：从0:00:00以0开始
时间函数	Now：显示当前系统的日期和时间	Now()	Now()
时间函数	Time：显示指定数值的时间	Time(Hour,Minute,Second)	Time(15,25,35)
日期函数	Today：显示当前系统的日期	Today()	Toady()
	Date：显示指定数值的日期	Date(Year,Month,Day)	DATE(2023,2,20)
	Year：得到日期的年数	Year(Serial_Number）	Year(Today())
	Month：得到日期的月数	Month(Serial_Number）	Month(Today())
	Day：得到日期的天数	Day(Serial_Number）	Day(Today())
	Edate：计算到期日	Edate(start_date,months)	EDATE("2023-02-20",12)
求两个日期之间的间隔数	DateDif函数：返回两个日期之间的年\月\日间隔数	Datadif(Start_date,End_date,Date_Type）
		参数：date_type类型：所需信息的返回类型。	2011-11-14
		D-时间段中的天数	Datedif(F$55,G$55,"D")
		M-时间段中的整月数	Datedif(F$55,G$55,"M")
		Y-时间段中的整年数	Datedif(F$55,G$55,"Y")
		YD-起始日期与结束日期的同年间隔天数，忽略日期中的年份	Datedif(F$55,G$55,"YD")
		MD-起始日期与结束日期的同月间隔天数，忽略日期中的月份和年份。	Datedif(F$55,G$55,"MD")
		YM-起始日期与结束日期的同年间隔月数，忽略日期中的年份	Datedif(F$55,G$55,"YM")
显示指定日期星期几	Weekday函数	Weekday(Serial_Number,[Return_Type])
		参数：return_type类型：
		1或省略：返回数字1（周日）到数字7（周六）	Weekday(today(),1)
		2：返回数字1（周一）到数字7（周日）	Weekday(today(),2)
		3：返回数字0（周一）到数字6（周日）	Weekday(today(),3)

删除Excel不能打印的字符	Clean函数：删除其中含有的无法打印的字符	Clean（text）	Clean(F68)
比较两单元格是否相同	用运算符“=”判断，不区分大小写	返回结果：“TURE”、“FALSE”	F68=G68
比较两单元格是否相同	用EXACT函数，区分大小写	Exact（text1,text2），返回结果：“TURE”、“FALSE”	Exact(F68,G68)
将几个文本字符串合并为一个文本字符串	用运算符“&（和号）”连接	Text1&Text2&Text3	F68&G68&H68
将几个文本字符串合并为一个文本字符串	Concatenate函数：连接文本字符串	Concatenate（Text1,Text2……Text30）	Concatenate(F68,G68,H68)
转换文本大小写	LOWER函数：所有大写转换为小写	Lower(Text)	Lower(F68)
	UPPER函数：所有小写转换为大写	Upper(Text)	Upper(F68)
	PROPER函数：首字母及任何非字母后的首字段转换大写，其他字母转换为小写	Proper(Text)	Proper(F68)
取出单元格中的部分字符	Left函数：从左边开始截取指定数目的字符	Left(Text,[Num_Chars])，Num_Chars如果忽略，则为1	Left(F68,2)
	Right函数：从右边开始截取指定数目的字符	Right(Text,[Num_Chars])，Num_Chars如果忽略，则为1	Right(F68,3)
	Mid函数：从文本指定位置开始截取指定数目的字符	Mid(Text,Start_Num,Num_Chars)	Mid(F68,2,3)
计算字符串的长度	Len函数：计算字符串的长度	Len(Text)	Len(E$68)
计算字符串的长度	Lenb函数：计算字符串中字节的长度	LenB(Text)	Lenb(E$68)
查找特定字符	Find函数：定位字符串，以字符数为单位返回起始位置编号	FIND(find_text,within_text,[start_num])	FIND("[",E$68)
查找特定字符	FindB函数：定位字符串，以字节数为单位返回起始位置编号	FINDB(find_text,within_text,[start_num])	FINDB("[",E$68)

常用的数学函数1	Round函数：按指定位数四舍五入	Round(number,num_digits)
		参数：num_digits为指定的位数，Number按此位数进行处理。
		num_digits>0，则四舍五入到指定的小数位；	Round(G$86,3)
		num_digits=0，则四舍五入到最接近的整数；	Round(G$86,0)
		num_digits<0，则在小数点左侧按指定位数四舍五入。	Round(G$86,-1)
	Roundup函数：向上舍入（沿绝对值增大的方向）	Roundup(Number,Num_Digits)	Roundup(G$86,-1)
	Rounddown函数：向下舍入（沿绝对值减小的方向）	Rounddown(Number,Num_Digits)	Rounddown(G$86,3)
	Trunc函数：向下舍入（沿绝对值减少的方向）	Trunc(Number,[Num_Digits])	Trunc(G$86,3)
	Int函数：将数值向下取整为最接近的整数	Int(Number)	Ind(G$86)
	Abs函数：生成绝对值	Abs(Number)	Abs(G$86)
	Mod函数：求两数相除后的余数	Mod(Number,Divisor)	Mod(12,5)
	Rand函数：生成随机数	Rand()	Rand()
常用的数学函数2	Average函数：求平均数,注意不包括空白和字符	Average(Number1,[Number2],……）	Average(G$89:G$99)
	Max函数：查找一组数的最大值	Max(Number1,[Number2],……）	Max(G$89:G$99)
	Min函数：查找一组数的最小值	Min(Number1,[Number2],……）	Min(G$89:G$99)
	注意	函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用，则只有数组或引用中的数字参与计算，空白单元格、逻辑值或文本则被忽略。
常用的数学函数3	Rank函数：返回一个数字在数字列表中的排位	Rank(Number,Ref,[Order]）	Rank(G94,G$89:G$99)
		参数：Ref为数字列表数组或对数字列表的引用；
		order为0或省略，则按照降序排列；否则按升序排列的列表。	Rank(G$99,G$89:G$99,1)
		注意：相同数值得到的排位相同，但会导致后续数字的序数空缺。	Rank(G94,G$89:G$99)
	Large函数：返回数据集中第K个最大值	Large(array,k）	Large(G$89:G$99,2)
	Small函数：返回数据集中的第K个最小值	Small(array,k）	Small(G$89:G$99,2)

常用的逻辑函数	IF函数：执行真假值判断，根据逻辑计算的真假值，返回不同结果。常用于对数值和公式进行条件检测。	IF(logical_test,[value_if_true],[value_if_false])	IF(ISNUMBER(F111),1,2)
	And函数：判断多个条件同时为真。所有参数都为真返回 TRUE；只要一个参数为假，即返回FLASE。	And(logical1,logical2, ...)	And(ISNUMBER（G111),ISNUMBER（F111))
	OR函数：判断任一参数为真。任何一个参数为TRUE，即返回TRUE；所有参数都为FALSE，才返回FALSE。	OR(logical1,logical2, ...)	OR(ISNUMBER（G111),ISNUMBER（F111))
判断是否数字或是否存在错误的函数	isNumber函数：判断引用的参数或指定单元格中的值是否为数字。	isNumber（value）	ISNUMBER（G111)
	iferror函数：通常用于找出公式计算中是否存在错误。	IFERROR(value, value_if_error)	IFERROR(2/0, "出错啦")
		参数：value，在公式计算不出错的情况下返回的值。	IFERROR经常和VLOOKUP一起使用，进行高阶查找数据： IFERROR(VLOOKUP(E2,A1:C11,3,0), "不存在该人")
		value_if_error，在公式计算出错的情况下返回的值
		一般计算出错的类型包括以下几种：
		#N/A：代表数据缺失
		#VALUE!：输入公式的方式错误或参与计算的单元格错误
		#REF!：引用的单元格错误（比如复制了已经删掉的单元格）
		#DIV/0! ：除数为 0 而导致的错误（除数不能为 0）
		#NUM! ：当公式或函数中某个数字有问题时产生错误值
		#NAME？：出现了表格不能识别的内容
		#NULL! ：使用了不正确的区域运算符或不正确的单元格引用。
		#####！：单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值

————————————————

立足经典、真Excel设计、社区版永久免费！立即下载使用，开启高效报表开发之旅！