兼容vlookup等Excel常见函数

浏览次数:2275 最新更新:老慢 于 2023-09-09

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

  1. SUM函数:计算一列或一行数字的总和。
  2. AVG函数:计算一列或一行数字的平均数。
  3. COUNT函数:计算一列或一行数字的数量。
  4. AVERAGEIF函数:根据条件计算一列或一行数字的平均值。
  5. MAX函数:找出一列或一行数字中的最大值。
  6. MIN函数:找出一列或一行数字中的最小值。
  7. 求和函数:SUMIF函数、SUM函数、AVERAGE函数,可以计算指定区域中所有满足条件的数字的总和。
  8. 平均值函数:AVERAGE函数、AVG函数,可以计算指定区域中所有数字的平均值。
  9. 最大值函数:MAX函数,MAX函数可以找出指定区域中所有数字中的最大值。
  10. 最小值函数: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_type类型:
  -1  查找大于或等于value的最小数值,array必须降序排列
  1   查找小于或等于value的最大数值,array必须升序排列
  0   查找等于value的第一个数值,array无需排序
如何获得单元格地址的函数 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设计、社区版永久免费!立即下载使用,开启高效报表开发之旅!

Demo体验

返回顶层