excel函数公式大全详解(excel函数公式讲解)

前沿拓展:

excel函数公式大全详解

数字处理

1、取绝对值

=ABS(数字)

2、取整

=INT(数字)

3、四舍五入

=ROUND(数字,小数位数)

END

统计公式

统计两个表格重复的内容

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明:如果返回值大于0说明在另一个表中存在,0则不存在。

请点击输入图片描述

统计不重复的总人数

公式:C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。

请点击输入图片描述

END

求和公式

隔列求和

公式:H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明:如果标题行没有规则用第2个公式

请点击输入图片描述

单条件求和

公式:F2

=SUMIF(A:A,E2,C:C)

说明:SUMIF函数的基本用法

请点击输入图片描述

单条件模糊求和

公式:详见下图

说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。

请点击输入图片描述

多条件模糊求和

公式:C11

=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

说明:在sumifs中可以使用通配符*

请点击输入图片描述

多表相同位置求和

公式:b2

=SUM(Sheet1:Sheet19!B2)

说明:在表中间删除或添加表后,公式结果会自动更新。

请点击输入图片描述

按日期和产品求和

公式:F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明:SUMPRODUCT可以完成多条件求和

请点击输入图片描述


15个Excel函数公式案例解读,便捷高效,办公必备

在数据的处理和分析中,函数或公式是使用率比较高的工具之一,但Excel的函数非常的繁多,想要全部掌握,几乎是不可能的,所以,我们必须掌握常用的函数,公式!

一、对Excel工作表中的数值向下取整

函数:Int。

功能:将数值向下取整为最接近的整数。

语法结构:=Int(值或单元格引用)。

目的:舍去“月薪”小数点后的值。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式;=INT(G3)。

解读:

Int函数的作用为向下取整,即不进行四舍五入,直接省去小数点后面的值。

二、对Excel工作表中的数值向上取整

函数:Roundup。

功能:向上舍入数字。

语法结构:=Roundup(值或单元格引用,小数位数)。

目的:对“月薪”向上取整。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=ROUNDUP(G3,0)。

解读:

Roundup函数的作用为“向上舍入”数字,即如果保留小数后还有值,一律“进一”。

三、对Excel工作表中的数值“四舍五入”

函数:Round。

功能:按指定的位数对数值“四舍五入”。

语法结构:=Round(值或单元格引用,小数位数)。

目的:对“月薪”四舍五入后,保留一位小数。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=ROUND(G3,1)。

解读:

Round函数对数字进行四舍五入,按照指定的小数位数保留值。

四、在Excel工作表中随机生成0-1之间的随机数

函数:Rand。

功能:返回大于或等于0小于1的平均分布随机数。

语法结构:=Rand()。

目的:在“备注”列随机生成0-1之间的随机数。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=RAND()。

解读:

如果对生成的随机值不满意,可以按F9(或Fn+F9)进行刷新。

五、在Excel工作表中随机生成指定范围内的随机值

函数:Randbetween。

功能:返回一个介于指定值之间的随机值。

语法结构:=Randbetween(开始值,结束值)。

目的:在“备注”列生成100-500之间的随机值。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=RANDBETWEEN(100,500)。

解读:

如果对生成的随机值不满意,可以按F9(或Fn+F9)进行刷新。

六、从Excel工作表中的身份证号码中提取出生年月。

函数:Text+Mid。

功能:Text函数功能为根据指定的格式将数值转换为文本,Mid函数的功能为:从字符串中指定的起始位置返回指定长度的字符。

语法结构:=Text(数值或单元格引用,格式代码);=Mid(值或单元格引用,起始位置,字符长度)。

目的:从身份证号码中提取出生年月的8位数值并转换为日期形式。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=TEXT(MID(C3,7,8),"0-00-00")。

解读:

1、身份证号码中从第7位开始,长度为8的字符为出生年月。

2、首先利用Mid函数提取出生年月8位数字,然后用Text函数将其转换为日期格式。

七、从Excel工作表中的身份证号码中提取性别。

函数:If+Mod+Mid。

功能:Mod函数的功能为:返回两个数相除的余数。

语法结构:=Mod(被除数,除数)。

目的:从身份证号码中判断性别。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。

解读:

1、身份证号码中的第17位数值代表性别,奇数为“男”,偶数为“女”。

2、首先利用Mid函数从身份证号码中提取第17位,并作为Mod函数的被除数,当除数为2时,其返回的值只有0或1两种,然后用If函数进行判断,如果值为1,则返回“男”,否则返回“女”。

八、从Excel工作表中的身份证号码中计算年龄。

函数:Datedif。

功能:以指定的方式统计两个日期之间的差值。

语法结构:=Datedif(开始日期,结束日期,统计方式)。常用的统计方式有三种,“Y”、“M”、“D”,即“年”、“月”、“日”。

目的:根据身份证号码计算对应的年龄。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=DATEDIF(TEXT(MID(C3,7,8),"0-00-00"),TODAY(),"y")。

解读:

1、Datedif函数为系统隐藏函数,在官方的函数库中时无法找到对应的解读部分,在输入函数名称时,没有联想提示部分。且只能在16及以上版本中才能使用,WPS中也可以使用。

2、公式中首先利用Text+Mid从身份证号码中提取出生年月,然后和今天(Today())对比,计算两个日期之间的相差的年份(Y)。

3、此公式具有通用性,无论何时打开工作表,其年龄是自动计算的最新值,如果参数“结束日期”Today()替换成“2020-11-08”,其年龄不会自动变化,不会增长。

九、将Excel工作表中的数值可视化

函数:Rept。

功能:根据指定次数,重复文本。

语法结构:=Rept(文本,重复次数)。

目的:将“月薪”以图表的形式显示。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=REPT("|",G3/500)。

解读:

除以500是因为缩小G3单元格的值,否则在较小的空间中无法正常显示,在实际的应用中,需要灵活处理。

十、将Excel工作表中字符的首字符转换为大写

函数:Proper。

功能:将一个文本字符串中各英文单词的首字母转换为大写,其他字母转换为小写。

语法结构:=Proper(字符串或单元格引用)。

目的:将“拼音”中的第一个字母大写。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=PROPER(C3)。

十一、将Excel工作表中的字符全部大写

函数:Upper。

功能:将文本字符串转换成字母全部大写形式。

语法结构:=Upper(字符串或单元格引用)。

目的:将“拼音”全部大写。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=UPPER(C3)。

十二、将Excel工作表中的字符全部小写

函数:Lower。

功能:将一个字符串中的所有字母转换为小写形式。

语法结构:=Lower(字符串或单元格引用)。

目的:将“拼音”全部小写。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=LOWER(C3)。

十三、将Excel工作表中的日期转换为星期。

函数:Text。

功能:根据指定的格式将数值转换为文本。

语法结构:=Text(值或单元格引用,格式代码)。

目的:将“出生日期”中的值转换为对应的星期。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=TEXT(C3,"aaaa")。

解读:

代码“aaaa”代表长星期,即星期X。

十四、计算出Excel工作表中的日期对应的周次。

函数:Weeknum。

功能:返回一年中的周次。

语法结构:=Weeknum(日期或单元格引用,计算方式)。

目的:返回“出生日期”对应的周次。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=WEEKNUM(C3,2)。

解读:

“计算方式”为2时,代表一周从星期一开始,星期日结束。

十五、将Excel工作表中的数值取整并大写。

函数:Numberstring。

功能:按照指定的格式对数字四舍五入取整并大写。

语法结构:=Numberstring(数字或单元格引用,格式代码)。

目的:对“月薪”大写。

15个Excel函数公式案例解读,便捷高效,办公必备

方法:

在目标单元格中输入公式:=NUMBERSTRING(G3,2)。

解读:

1、Numberstring函数为系统隐藏函数,且只能在16及以上版本中应用,在WPS中同样可以使用。

2、格式代码“2”的作用为:将数值按照会计格式进行大写。

拓展知识:

excel函数公式大全详解

excel常用公式函数有:IF函数、SUMIFS函数、COUNTIF、VLOOKUP函数,LOOKUP函数。

1、IF函数

IF函数一般是指程序设计或Excel等软件中的条件函数,根据指定的条件来判断其“真”(TRUE)、“假”(FALSE),根据逻辑计算的真假值,从而返回相应的内容。可以使用函数 IF 对数值和公式进行条件检测。

语法

IF(logical_test,value_if_true,value_if_false)

功能

IF函数是条件判断函数:如果指定条件的计算结果为 TRUE,IF函数将返回某个值;如果该条件的计算结果为 FALSE,则返回另一个值。

例如IF(测试条件,结果1,结果2),即如果满足“测试条件”则显示“结果1”,如果不满足“测试条件”则显示“结果2”。

参数

(1)Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。

例如,A10=100 就是一个逻辑表达式,如果单元格 A10 中的值等于 100,表达式即为 TRUE,否则为 FALSE。本参数可使用任何比较运算符(=(等于)、>(大于)、>=(大于等于)、<=(小于等于等运算符))。

(2)Value_if_true表示 logical_test 为 TRUE 时返回的值。

例如,如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE,则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 value_if_true 为空,则本参数返回 0。如果要显示 TRUE,则请为本参数使用逻辑值 TRUE。value_if_true 也可以是其他公式。

(3)Value_if_false表示 logical_test 为 FALSE 时返回的值。

例如,如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE,则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FALSE 且忽略了 value_if_false(即 value_if_true 后没有逗号),则会返回逻辑值 FALSE。

如果 logical_test 为 FALSE 且 value_if_false 为空(即 value_if_true 后有逗号,并紧跟着右括号),则本参数返回 0(零)。VALUE_if_false 也可以是其他公式。

2、SUMIF函数

SUMIF函数是Excel常用函数。使用 SUMIF 函数可以对报表范围中符合指定条件的值求和。Excel中sumif函数的用法是根据指定条件对若干单元格、区域或引用求和。

语法

SUMIF(range,criteria,sum_range)

1)range 为用于条件判断的单元格区域。

2)criteria 为确定哪些单元格将被相加求和的条件,其形式可以为数字、文本、表达式或单元格内容。例如,条件可以表示为 32、"32"、">32" 、"apples"或A1。条件还可以使用通配符:问号 (?) 和星号 (*),如需要求和的条件为第二个数字为2的,可表示为"?2*",从而简化公式设置。

3)sum_range 是需要求和的实际单元格。

3、Countif函数

Countif函数是Microsoft Excel中对指定区域中符合指定条件的单元格计数的一个函数,在WPS,Excel2003和Excel2007等版本中均可使用。

该函数的语法规则如下:

countif(range,criteria)

参数:range 要计算其中非空单元格数目的区域

参数:criteria 以数字、表达式或文本形式定义的条件

4、VLOOKUP函数

VLOOKUP函数是Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,在工作中都有广泛应用,例如可以用来核对数据,多个表格之间快速导入数据等函数功能。功能是按列查找,最终返回该列所需查询序列所对应的值;与之对应的HLOOKUP是按行查找的。

参数说明

Lookup_value为需要在数据表第一列中进行查找的数值。Lookup_value 可以为数值、引用或文本字符串。当vlookup函数第一参数省略查找值时,表示用0查找。

Table_array为需要在其中查找数据的数据表。使用对区域或区域名称的引用。

col_index_num为table_array 中查找数据的数据列序号。col_index_num 为 1 时,返回 table_array 第一列的数值,col_index_num 为 2 时,返回 table_array 第二列的数值,以此类推。如果 col_index_num 小于1,函数 VLOOKUP 返回错误值 #VALUE!;如果 col_index_num 大于 table_array 的列数,函数 VLOOKUP 返回错误值#REF!。

Range_lookup为一逻辑值,指明函数 VLOOKUP 查找时是精确匹配,还是近似匹配。如果为FALSE或0,则返回精确匹配,如果找不到,则返回错误值 #N/A。

如果 range_lookup 为TRUE或1,函数 VLOOKUP 将查找近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 lookup_value 的最大数值。如果range_lookup 省略,则默认为1。

5、LOOKUP函数

LOOKUP函数是Excel中的一种运算函数,实质是返回向量或数组中的数值,要求数值必须按升序排序。

使用方法

(1)向量形式:公式为 = LOOKUP(lookup_value,lookup_vector,result_vector)

式中 lookup_value—函数LOOKUP在第一个向量中所要查找的数值,它可以为数字、文本、逻辑值或包含数值的名称或引用;

lookup_vector—只包含一行或一列的区域lookup_vector 的数值可以为文本、数字或逻辑值;

result_vector—只包含一行或一列的区域其大小必须与 lookup_vector 相同。

(2)数组形式:公式为

= LOOKUP(lookup_value,array)

式中 array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup_value 进行比较。

例如:LOOKUP(5.2,{4.2,5,7,9,10})=5。

注意:array和lookup_vector的数据必须按升序排列,否则函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP找不到lookup_value,则查找array和 lookup_vector中小于lookup_value的最大数值。

如果lookup_value小于array和 lookup_vector中的最小值,函数LOOKUP返回错误值#N/A。另外还要注意:函数LOOKUP在查找字符方面是不支持通配符的,但可以使用FIND函数的形式来代替。

本回答被网友采纳

excel函数公式大全详解

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),"男","女")

2、出生年月(G列)

=TEXT(MID(E3,7,8),"0-00-00")

3、年龄公式(H列)

=DATEDIF(G3,TODAY(),"y")

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa")

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)

6、社会工龄(T列)

=DATEDIF(S3,NOW(),"y")

7、公司工龄(W列)

=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"

8、合同续签日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]过期0天;[<30]即将到期0天;还早")

10、工龄工资(AA列)

=MIN(700,DATEDIF($V3,NOW(),"y")*50)

11、生肖(AB列)

=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1

本回答被网友采纳

excel函数公式大全详解

1、IF函数条件判断

EXCEL的逻辑函数,执行真假值判断,根据逻辑计算的真假值,返回不同结果。可以使用函数 IF 对数值和公式进行条件检测。

相当于普通话的“如果”,常规用法是:IF(判断的条件,符合条件时的结果,不符合条件时的结果)。

2、多条件判断

AND函数对两个条件判断,如果同时符合,IF函数返回“有”,否则为无。

用比较通俗的话来说,就是做多选题,必须所有选项都符合才是对(true),否则就是错(false)。

3、条件求和

SUMIF用法是:SUMIF(条件区域,指定的求和条件,求和的区域)

用通俗的话描述就是:如果D2:D5区域的班级等于F2单元格的“一班”,就对C2、C5单元格对应的区域求和。

4、条件计数

COUNTIF函数统计条件区域中,符合指定条件的单元格个数。常规用法为:COUNTIF(条件区域,指定条件)。

5、条件查找

VLOOKUP函数的语法为:VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)。

本回答被网友采纳

excel函数公式大全详解

1、Excel表格中使用“/”这个符号代表除号,注意必须是在英文输入法状态下输入的斜杆符号。 例如A1除以B1,就是A1/B1。 2、与加号不同,除法没有单独的函数。 3、具体操作方式如此下:(1)打开excel文档;(2)选中某一单元格,在“fx”右侧键入除数与被除数,如“A1/B1”; (3)按下回车键即“enter”键,得到比值;(4)鼠标移到单元格右下角,直到鼠标变为黑色小十字;(5)下拉单元格,得到一系列比值,就可以了。其余Excel常见符号如下: 1、加是“+”,或者使用函数符号“sum”; 2、减是“-”; 3、乘是“*”。

本回答被网友采纳

原创文章,作者:趣淘网小编,如若转载,请注明出处:http://www.3322388.com/23964.html

发表评论

邮箱地址不会被公开。 必填项已用*标注