精华内容
下载资源
问答
  • 工作中常用27个Excel函数公式

    万次阅读 多人点赞 2018-01-04 13:50:37
    1、把公式产生的错误值显示为空 2、IF多条件判断返回值 三、统计公式 1、统计两个表格重复的内容 2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊求和 4、多...

    工作中常用的27个Excel函数公式

    一、数字处理
    1、取绝对值
    2、取整
    3、四舍五入
    二、判断公式
    1、把公式产生的错误值显示为空
    2、IF多条件判断返回值
    三、统计公式
    1、统计两个表格重复的内容
    2、统计不重复的总人数
    四、求和公式
    1、隔列求和
    2、单条件求和
    3、单条件模糊求和
    4、多条件模糊求和
    5、多表相同位置求和
    6、按日期和产品求和
    五、查找与引用公式
    1、单条件查找公式
    2、双向查找公式
    3、查找最后一条符合条件的记录。
    4、多条件查找
    5、指定区域最后一个非空值查找
    6、按数字区域间取对应的值
    六、字符串处理公式
    1、多单元格字符串合并
    2、截取除后3位之外的部分
    3、截取-前的部分
    4、截取字符串中任一段的公式
    5、字符串查找
    6、字符串查找一对多
    七、日期计算公式
    1、两日期相隔的年、月、天数计算
    2、扣除周末天数的工作日天数




    一、数字处理
    1、取绝对值
    =ABS(数字) 如: ABS(-2) =2

    2、向下取整
    =INT(数字) 如: INT(5.6)=5 ; INT(5.2)=5

    3、四舍五入
    =ROUND(数字,小数位数) 如: ROUND(5.6,0)=6 ; ROUND(5.2,0)=5

    二、判断公式

    1、把公式产生的错误值显示为空
    公式:C2
    =IFERROR(A2/B2,"") 如: IFERROR(2/0,"错误") = "错误"
    说明:如果是错误值则显示为空,否则正常显示。


    2、IF多条件判断返回值
    公式:C2
    =IF(AND(A2<500,B2="未到期"),"补款","")
    说明:两个条件同时成立用AND,任一个成立用OR函数。

    三、统计公式
    1、统计两个表格重复的内容
    公式:B2
    =COUNTIF(Sheet15!A:A,A2)
    说明:如果返回值大于0说明在另一个表中存在,0则不存在。



    2、统计不重复的总人数
    公式:C2
    =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
    说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。

    四、求和公式
    1、隔列求和
    公式:H3
    =SUMIF($A$2:$G$2,H$2,A3:G3)
    =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
    说明:如果标题行没有规则用第2个公式


    2、单条件求和
    公式:F2
    =SUMIF(A:A,E2,C:C)
    说明:SUMIF函数的基本用法


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


    4、多条件模糊求和
    公式:C11
    =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
    说明:在sumifs中可以使用通配符*


    5、多表相同位置求和
    公式:b2
    =SUM(Sheet1:Sheet19!B2)
    说明:在表中间删除或添加表后,公式结果会自动更新。


    6、按日期和产品求和
    公式:F2
    =SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
    说明:SUMPRODUCT可以完成多条件求和


    五、查找与引用公式
    1、单条件查找公式
    公式1:C11
    =VLOOKUP(B11,B3:F7,4,FALSE)
    说明:查找是VLOOKUP最擅长的,基本用法


    2、双向查找公式
    公式:
    =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
    说明:利用MATCH函数查找位置,用INDEX函数取值


    3、查找最后一条符合条件的记录。
    公式:详见下图
    说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值


    4、多条件查找
    公式:详见下图
    说明:公式原理同上一个公式


    5、指定区域最后一个非空值查找
    公式;详见下图
    说明:略


    6、按数字区域间取对应的值
    公式:详见下图
    公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。


    六、字符串处理公式
    1、多单元格字符串合并
    公式:c2
    =PHONETIC(A2:A7)
    说明:Phonetic函数只能对字符型内容合并,数字不可以。


    2、截取除后3位之外的部分
    公式:
    =LEFT(D1,LEN(D1)-3)
    说明:LEN计算出总长度,LEFT从左边截总长度-3个


    3、截取-前的部分
    公式:B2
    =Left(A1,FIND("-",A1)-1)
    说明:用FIND函数查找位置,用LEFT截取。


    4、截取字符串中任一段的公式
    公式:B1
    =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
    说明:公式是利用强插N个空字符的方式进行截取


    5、字符串查找
    公式:B2
    =IF(COUNT(FIND("河南",A2))=0,"否","是")
    说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的 个数,这里可以用来判断查找是否成功。


    6、字符串查找一对多
    公式:B2
    =IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
    说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果


    七、日期计算公式
    1、两日期相隔的年、月、天数计算
    A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:
    相隔多少天?=datedif(A1,B1,"d") 结果:557
    相隔多少月? =datedif(A1,B1,"m") 结果:18
    相隔多少年? =datedif(A1,B1,"Y") 结果:1
    不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6
    不考虑年相隔多少天?=datedif(A1,B1,"YD")  结果:192
    不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9
    datedif函数第3个参数说明:
    "Y" 时间段中的整年数。
    "M" 时间段中的整月数。
    "D" 时间段中的天数。
    "MD" 天数的差。忽略日期中的月和年。
    "YM" 月数的差。忽略日期中的日和年。
    "YD" 天数的差。忽略日期中的年。

    2、扣除周末天数的工作日天数
    公式:C2
    =NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)
    说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日








    展开全文
  • 1、把公式产生的错误值显示为空 2、IF多条件判断返回值 三、统计公式 1、统计两个表格重复的内容 2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊求和 4、多条件模糊求和 5、多表相同...


     

    一、数字处理
    1、取绝对值
    2、取整
    3、四舍五入
    二、判断公式
    1、把公式产生的错误值显示为空
    2、IF多条件判断返回值
    三、统计公式
    1、统计两个表格重复的内容
    2、统计不重复的总人数
    四、求和公式
    1、隔列求和
    2、单条件求和
    3、单条件模糊求和
    4、多条件模糊求和
    5、多表相同位置求和
    6、按日期和产品求和
    五、查找与引用公式
    1、单条件查找公式
    2、双向查找公式
    3、查找最后一条符合条件的记录。
    4、多条件查找
    5、指定区域最后一个非空值查找
    6、按数字区域间取对应的值
    六、字符串处理公式
    1、多单元格字符串合并
    2、截取除后3位之外的部分
    3、截取-前的部分
    4、截取字符串中任一段的公式
    5、字符串查找
    6、字符串查找一对多
    七、日期计算公式
    1、两日期相隔的年、月、天数计算
    2、扣除周末天数的工作日天数

     

    一、数字处理

    1、取绝对值

    =ABS(数字)

    2、取整

    =INT(数字)

    3、四舍五入

    =ROUND(数字,小数位数)

    二、判断公式

    1、把公式产生的错误值显示为空

    公式:C2
    =IFERROR(A2/B2,"")

    说明:如果是错误值则显示为空,否则正常显示。

    2、IF多条件判断返回值
    公式:C2
    =IF(AND(A2<500,B2="未到期"),"补款","")

    说明:两个条件同时成立用AND,任一个成立用OR函数。

    三、统计公式

    1、统计两个表格重复的内容

    公式:B2
    =COUNTIF(Sheet15!A:A,A2)

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

    2、统计不重复的总人数

    公式:C2

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

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

    四、求和公式

    1、隔列求和
    公式:H3

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

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

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

    2、单条件求和

    公式:F2

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

    说明:SUMIF函数的基本用法

    3、单条件模糊求和

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

    4、多条件模糊求和
    公式:C11
    =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)

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

    5、多表相同位置求和

    公式:b2

    =SUM(Sheet1:Sheet19!B2)

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

    6、按日期和产品求和

    公式:F2

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

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

    五、查找与引用公式

    1、单条件查找公式

    公式1:C11

    =VLOOKUP(B11,B3:F7,4,FALSE)

    说明:查找是VLOOKUP最擅长的,基本用法

    2、双向查找公式

    公式:
    =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

    说明:利用MATCH函数查找位置,用INDEX函数取值

    3、查找最后一条符合条件的记录。

    公式:详见下图

    说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值


    4、多条件查找

    公式:详见下图

    说明:公式原理同上一个公式

    5、指定区域最后一个非空值查找

    公式;详见下图

    说明:略

    6、按数字区域间取对应的值

    公式:详见下图

    公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。


    六、字符串处理公式

    1、多单元格字符串合并

    公式:c2

    =PHONETIC(A2:A7)

    说明:Phonetic函数只能对字符型内容合并,数字不可以。

    2、截取除后3位之外的部分

    公式:

    =LEFT(D1,LEN(D1)-3)

    说明:LEN计算出总长度,LEFT从左边截总长度-3个


    3、截取-前的部分

    公式:B2

    =Left(A1,FIND("-",A1)-1)

    说明:用FIND函数查找位置,用LEFT截取。

    4、截取字符串中任一段的公式
    公式:B1
    =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

    说明:公式是利用强插N个空字符的方式进行截取

    5、字符串查找

    公式:B2
    =IF(COUNT(FIND("河南",A2))=0,"否","是")

    说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。

    6、字符串查找一对多
    公式:B2
    =IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

    说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果

    七、日期计算公式

    1、两日期相隔的年、月、天数计算

    A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:
    相隔多少天?=datedif(A1,B1,"d") 结果:557

    相隔多少月? =datedif(A1,B1,"m") 结果:18

    相隔多少年? =datedif(A1,B1,"Y") 结果:1

    不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6

    不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192

    不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9

    datedif函数第3个参数说明:

    "Y" 时间段中的整年数。

    "M" 时间段中的整月数。

    "D" 时间段中的天数。

    "MD" 天数的差。忽略日期中的月和年。

    "YM" 月数的差。忽略日期中的日和年。

    "YD" 天数的差。忽略日期中的年。

     

    2、扣除周末天数的工作日天数

    公式:C2

    =NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)

    说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

    展开全文
  • Excel常用技巧——工作中最常用30个Excel函数公式

    万次阅读 多人点赞 2018-11-20 19:06:53
    1、把公式产生的错误值显示为空 2、IF多条件判断返回值 三、统计公式 1、统计两个表格重复的内容 2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊求和 4、多条件模糊求和 5...

    Excel常用技巧--工作中最常用的30个Excel函数公式

     

    目录

    一、数字处理

    1、取绝对值

    2、取整

    3、四舍五入

    二、判断公式

    1、把公式产生的错误值显示为空

    2、IF多条件判断返回值

    三、统计公式

    1、统计两个表格重复的内容

    2、统计不重复的总人数

    四、求和公式

    1、隔列求和

    2、单条件求和

    3、单条件模糊求和

    4、多条件模糊求和

    5、多表相同位置求和

    6、按日期和产品求和

    五、查找与引用公式

    1、单条件查找公式

    2、双向查找公式

    3、查找最后一条符合条件的记录。

    4、多条件查找

    5、指定区域最后一个非空值查找

    6、按数字区域间取对应的值

    六、字符串处理公式

    1、多单元格字符串合并

    2、截取除后3位之外的部分

    3、截取-前的部分

    4、截取字符串中任一段的公式

    5、字符串查找

    6、字符串查找一对多

    七、日期计算公式

    1、两日期相隔的年、月、天数计算

    2、扣除周末天数的工作日天数

    一、数字处理

    1、取绝对值

    =ABS(数字)

    2、取整

    =INT(数字)

    3、四舍五入

    =ROUND(数字,小数位数)

    二、判断公式

    1、把公式产生的错误值显示为空

    公式:C2

    =IFERROR(A2/B2,"")

    说明:如果是错误值则显示为空,否则正常显示。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    2、IF多条件判断返回值

    公式:C2

    =IF(AND(A2<500,B2="未到期"),"补款","")

    说明:两个条件同时成立用AND,任一个成立用OR函数。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    三、统计公式

    1、统计两个表格重复的内容

    公式:B2

    =COUNTIF(Sheet15!A:A,A2)

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

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    2、统计不重复的总人数

    公式:C2

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

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

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    四、求和公式

    1、隔列求和

    公式:H3

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

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

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

     

     

    2、单条件求和

    公式:F2

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

    说明:SUMIF函数的基本用法

     

     

    3、单条件模糊求和

    公式:详见下图

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

     

     

    4、多条件模糊求和

    公式:C11

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

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

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    5、多表相同位置求和

    公式:b2

    =SUM(Sheet1:Sheet19!B2)

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

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    6、按日期和产品求和

    公式:F2

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

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

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    五、查找与引用公式

    1、单条件查找公式

    公式1:C11

    =VLOOKUP(B11,B3:F7,4,FALSE)

    说明:查找是VLOOKUP最擅长的,基本用法

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    2、双向查找公式

    公式:

    =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

    说明:利用MATCH函数查找位置,用INDEX函数取值

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    3、查找最后一条符合条件的记录。

    公式:详见下图

    说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    4、多条件查找

    公式:详见下图

    说明:公式原理同上一个公式

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    5、指定区域最后一个非空值查找

    公式;详见下图

    说明:略

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    6、按数字区域间取对应的值

    公式:详见下图

    公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    六、字符串处理公式

    1、多单元格字符串合并

    公式:c2

    =PHONETIC(A2:A7)

    说明:Phonetic函数只能对字符型内容合并,数字不可以。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    2、截取除后3位之外的部分

    公式:

    =LEFT(D1,LEN(D1)-3)

    说明:LEN计算出总长度,LEFT从左边截总长度-3个

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    3、截取-前的部分

    公式:B2

    =Left(A1,FIND("-",A1)-1)

    说明:用FIND函数查找位置,用LEFT截取。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    4、截取字符串中任一段的公式

    公式:B1

    =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

    说明:公式是利用强插N个空字符的方式进行截取

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    5、字符串查找

    公式:B2

    =IF(COUNT(FIND("河南",A2))=0,"否","是")

    说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    6、字符串查找一对多

    公式:B2

    =IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

    说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    七、日期计算公式

    1、两日期相隔的年、月、天数计算

    A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:

    相隔多少天?=datedif(A1,B1,"d") 结果:557

    相隔多少月? =datedif(A1,B1,"m") 结果:18

    相隔多少年? =datedif(A1,B1,"Y") 结果:1

    不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6

    不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192

    不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9

    datedif函数第3个参数说明:

    "Y" 时间段中的整年数。

    "M" 时间段中的整月数。

    "D" 时间段中的天数。

    "MD" 天数的差。忽略日期中的月和年。

    "YM" 月数的差。忽略日期中的日和年。

    "YD" 天数的差。忽略日期中的年。

    2、扣除周末天数的工作日天数

    公式:C2

    =NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)

    说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

    展开全文
  • 目录 ...1、把公式产生的错误值显示为空 2、IF多条件判断返回值 三、统计公式 1、统计两个表格重复的内容 2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊...

     

     

    目录

    一、数字处理

    1、取绝对值

    2、取整

    3、四舍五入

    二、判断公式

    1、把公式产生的错误值显示为空

    2、IF多条件判断返回值

    三、统计公式

    1、统计两个表格重复的内容

    2、统计不重复的总人数

    四、求和公式

    1、隔列求和

    2、单条件求和

    3、单条件模糊求和

    4、多条件模糊求和

    5、多表相同位置求和

    6、按日期和产品求和

    五、查找与引用公式

    1、单条件查找公式

    2、双向查找公式

    3、查找最后一条符合条件的记录。

    4、多条件查找

    5、指定区域最后一个非空值查找

    6、按数字区域间取对应的值

    六、字符串处理公式

    1、多单元格字符串合并

    2、截取除后3位之外的部分

    3、截取-前的部分

    4、截取字符串中任一段的公式

    5、字符串查找

    6、字符串查找一对多

    七、日期计算公式

    1、两日期相隔的年、月、天数计算

    2、扣除周末天数的工作日天数

    一、数字处理

    1、取绝对值

    =ABS(数字)

    2、取整

    =INT(数字)

    3、四舍五入

    =ROUND(数字,小数位数)

    二、判断公式

    1、把公式产生的错误值显示为空

    公式:C2

    =IFERROR(A2/B2,"")

    说明:如果是错误值则显示为空,否则正常显示。

    Excel常用技巧——工作中最常用的30个Excel函数公式

    2、IF多条件判断返回值

    公式:C2

    =IF(AND(A2<500,B2="未到期"),"补款","")

    说明:两个条件同时成立用AND,任一个成立用OR函数。

    Excel常用技巧——工作中最常用的30个Excel函数公式

    三、统计公式

    1、统计两个表格重复的内容

    公式:B2

    =COUNTIF(Sheet15!A:A,A2)

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    2、统计不重复的总人数

    公式:C2

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

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    四、求和公式

    1、隔列求和

    公式:H3

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

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

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    2、单条件求和

    公式:F2

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

    说明:SUMIF函数的基本用法

    Excel常用技巧——工作中最常用的30个Excel函数公式

    3、单条件模糊求和

    公式:详见下图

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    4、多条件模糊求和

    公式:C11

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

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    5、多表相同位置求和

    公式:b2

    =SUM(Sheet1:Sheet19!B2)

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    6、按日期和产品求和

    公式:F2

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

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

    Excel常用技巧——工作中最常用的30个Excel函数公式

    五、查找与引用公式

    1、单条件查找公式

    公式1:C11

    =VLOOKUP(B11,B3:F7,4,FALSE)

    说明:查找是VLOOKUP最擅长的,基本用法

    Excel常用技巧——工作中最常用的30个Excel函数公式

    2、双向查找公式

    公式:

    =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

    说明:利用MATCH函数查找位置,用INDEX函数取值

    Excel常用技巧——工作中最常用的30个Excel函数公式

    3、查找最后一条符合条件的记录。

    公式:详见下图

    说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值

    Excel常用技巧——工作中最常用的30个Excel函数公式

    4、多条件查找

    公式:详见下图

    说明:公式原理同上一个公式

    Excel常用技巧——工作中最常用的30个Excel函数公式

    5、指定区域最后一个非空值查找

    公式;详见下图

    说明:略

    Excel常用技巧——工作中最常用的30个Excel函数公式

    6、按数字区域间取对应的值

    公式:详见下图

    公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    六、字符串处理公式

    1、多单元格字符串合并

    公式:c2

    =PHONETIC(A2:A7)

    说明:Phonetic函数只能对字符型内容合并,数字不可以。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    2、截取除后3位之外的部分

    公式:

    =LEFT(D1,LEN(D1)-3)

    说明:LEN计算出总长度,LEFT从左边截总长度-3个

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    3、截取-前的部分

    公式:B2

    =Left(A1,FIND("-",A1)-1)

    说明:用FIND函数查找位置,用LEFT截取。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    4、截取字符串中任一段的公式

    公式:B1

    =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))

    说明:公式是利用强插N个空字符的方式进行截取

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    5、字符串查找

    公式:B2

    =IF(COUNT(FIND("河南",A2))=0,"否","是")

    说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    6、字符串查找一对多

    公式:B2

    =IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")

    说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

     

    七、日期计算公式

    1、两日期相隔的年、月、天数计算

    A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:

    相隔多少天?=datedif(A1,B1,"d") 结果:557

    相隔多少月? =datedif(A1,B1,"m") 结果:18

    相隔多少年? =datedif(A1,B1,"Y") 结果:1

    不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6

    不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192

    不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9

    datedif函数第3个参数说明:

    "Y" 时间段中的整年数。

    "M" 时间段中的整月数。

    "D" 时间段中的天数。

    "MD" 天数的差。忽略日期中的月和年。

    "YM" 月数的差。忽略日期中的日和年。

    "YD" 天数的差。忽略日期中的年。

    2、扣除周末天数的工作日天数

    公式:C2

    =NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)

    说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

     

    Excel常用技巧——工作中最常用的30个Excel函数公式

     

    转载于:https://my.oschina.net/u/3999601/blog/2966906

    展开全文
  • Excel常用电子表格公式大全1-1

    千次阅读 2017-03-14 10:32:49
    一、数字处理  1、取绝对值  =ABS(数字) ... 1、把公式产生的错误值显示为空  公式:C2  =IFERROR(A2/B2,"")  说明:如果是错误值则显示为空,否则正常显示。  2、IF多条件
  • EXCEL基本操作(七)

    2020-03-22 19:00:00
    科普知识IF函数判断查找公式一、把公式产生的错误值显示为空,且把正确值正确显示1.1 公式表达=IFERROR(A2/B2,"")1.2...
  • Common Used Excel Formulas

    2019-07-01 11:38:34
    (adsbygoogle = window.adsbygoogle || []).push({}); 一、数字处理 ...1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,””) 说明:如果是错误值则显示为空,否则正常显示。 2、IF...
  • excel扩展函数整理

    2020-04-15 17:29:55
    标签:课堂笔记 已交 ...1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。 2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2=“未到期”),“补款”...
  • EXCEL函数

    2017-02-12 14:02:00
    把公式产生的错误值显示为空  公式:C2  =IF ERROR(A2/B2,"")  说明:如果是错误值则显示为空,否则正常显示。 隔列求和  公式:H3  =SUMIF($A$2:$G$2,H$2,A3:G3)  或  =SUMPRODUCT((MOD(COLUMN...
  • Excel 部分常用函数解析 字符函数 获取长度 =LEN(字符) 获取左右字符 =LEFT(字符,长度) =RIGHT(字符,长度) 获取数字个数 =COUNT(A1:H9) 取绝对值 ...把公式产生的错误值显示为空 =IFERROR(A
  • EXCEL函数公式

    热门讨论 2010-03-16 03:26:38
    统计单元格内不为空的格数 自动将销量前十名产品代号及销量填入表中 统计最大连续次数3个“不重复”个数统计在一列有重复姓名中,如何统计出具体有几人 计数问题 不相同 如何分班统计男女人数 在几百几千个...
  • 统计单元格内不为空的格数 自动将销量前十名产品代号及销量填入表中 统计最大连续次数3个“不重复”个数统计在一列有重复姓名中,如何统计出具体有几人 计数问题 不相同 如何分班统计男女人数 在几百几千个...
  • 一、数字处理  1、取绝对值 ... 1、把公式产生的错误值显示为空  公式:C2  =IFERROR(A2/B2,"")  说明:如果是错误值则显示为空,否则正常显示。  2、IF多条件判断返回值  公式:C2  =IF(AN...
  • excel使用

    2012-11-25 17:06:01
    一个常用例子,就是教师在统计学生成绩时,希望输入60以下分数时,能显示为“不及格”;输入60以上分数时,显示为“及格"。这样效果,利用IF函数可以很方便地实现。 假设成绩在A2单元格中,判断结果在A3...
  • 即: 如果日期是 1997 年 5 月 15 日, 那么 SYSDATE 显示为 'H 09-05-15'。 范围: Arabic Hijrah, English Hijrah, Gregorian, Japanese Imperial, Persian, ROC Official (Republic of China) 和 Thai Buddha。 ...
  • rar压缩软件.rar

    2016-02-13 10:52:44
    如果在命令行或配置文件中指定开关 -ilog ,RAR 将会处理压缩文件中遇到的错误 等写到日志文件中。读取开关 -ilog 描述获得更多信息。 固实压缩的文件列表 - rarfiles.lst ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~...
  • 如果在命令行或配置文件中指定开关 -ilog ,RAR 将会处理压缩文件中遇到的错误 等写到日志文件中。在 Unix 中,这个文件名 .rarlong,放在用户的 home 目录中。 在 Windows 中,它名 rar.log,放在 rar....
  • PT095164(去验证).rar

    2019-08-30 17:10:01
    如果赋值给列1小数位比较多话,列2就无法正常算出数值出来,系统默认保留两位,比如,列1输入0.000625,数量为1,此时列2显示为0.00。其他业务单据如销售出库单也有这样情况出现。 【R20150203-0354】 功能点:...
  • C#编程经验技巧宝典

    热门讨论 2008-06-01 08:59:33
    102 <br>0162 如何实现C#中用键完成TAB功能 102 <br>0163 如何限制文本框密码输入长度 102 <br>0164 数据输入为空提示 103 <br>0165 如何设置文本框光标到末尾 103 <br>0166 输入法调整...

空空如也

空空如也

1 2
收藏数 28
精华内容 11
关键字:

把公式产生的错误值显示为空