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

    万次阅读 2018-01-04 13:50:37
    工作中常用27个Excel函数公式 一、数字处理 1、取绝对值 ...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)
    说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日








    展开全文
  • 2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊求和 4、多条件模糊求和 5、多表相同位置求和 6、按日期和产品求和 五、查找与引用公式 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,"")

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

    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、取整3、四舍五入二、判断公式1、把公式产生错误值显示为空2、IF多条件判断返回值三、统计公式1、统计两个表格重复内容2、统计不重复的总人数四、求和公式1、隔列求和2、单条件...

    目录

    一、数字处理
    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,"")

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

    203b6d63b55e8d734d10f4aed644117c.png

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

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

    44184231cd3ccdc95427468a70db7ca9.png

    三、统计公式

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

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

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

    ded2bd89107e28c722ae8616843db75a.png

    2、统计不重复的总人数

    公式:C2

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

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

    c5e774942e4460dccf0d546b92d1637a.png

    四、求和公式

    1、隔列求和
    公式:H3

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

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

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

    83f5f68d76d96b6eff90ed8d87fcb68b.png

    2、单条件求和

    公式:F2

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

    说明:SUMIF函数的基本用法

    da0f03898303bcc9adca410cb23cbe85.png

    3、单条件模糊求和

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

    e7375d439f899366cf192502a90688ed.png

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

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

    3569e7bd9093cdc4063f877f6b89a6d0.png

    5、多表相同位置求和

    公式:b2

    =SUM(Sheet1:Sheet19!B2)

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

    88acd44f954280d3f5b3ed6ed62809be.png

    6、按日期和产品求和

    公式:F2

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

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

    00c0019f27f5f20b5bcaaed50d26a877.png

    五、查找与引用公式

    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函数取值

    253f788086c70a7b8e999e4fee1c236f.png

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

    公式:详见下图

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

    af1be7c0b9d4b75b1886af16c71e15ba.png


    4、多条件查找

    公式:详见下图

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

    e70942290b53b9a381541e4ea4b8bfca.png

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

    公式;详见下图

    说明:略

    0dfb5c4f3cb6466b8a955056eabea266.png

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

    公式:详见下图

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

    7b4ff427fe0186963c78fe5499e326da.png


    六、字符串处理公式

    1、多单元格字符串合并

    公式:c2

    =PHONETIC(A2:A7)

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

    34e184c70321ee51d1d23993ba4e4513.png

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

    公式:

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

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

    a480d3c56a1259908dea1d38bcfdc18e.png


    3、截取-前的部分

    公式:B2

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

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

    8cf9993f8d188846e14bfdd7d07e03fa.png

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

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

    0ff8792d73695767862a4304257e41f9.png

    5、字符串查找

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

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

    6b81e83804c20f4ecca72d79df14be96.png

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

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

    98b15ea467d60fd172e053bfa068acbe.png

    七、日期计算公式

    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

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

    a667acf373ff251a1f32936c74a2049a.png

    延伸阅读

    • 一份优秀的数据分析报告应该这样写

    • 互联网运营书单推荐

    • 2020年抖音用户画像报告

    • 运营岗位面试时,第一分钟说什么?

    • 用好这8张思维导图,5倍提高你的工作效率

    • 职场人常用的10个工具 | SWOT/WBS/PDCA/思维导图…

    • 如何写一篇完整的活动策划方案?

    • 10个抖音上很火的Excel小技巧,一看就会

    • 5个珍藏的Chrome插件,你用过几个?

    • 职场:一根线条提升PPT档次98%

    点击查看更多运营知识

    0004dd27121364f59dd5e93a9289a7a5.png

    PS:欢迎各位运营喵投稿,投稿请发送邮件到admin@iyunying.org,优质稿件将发布在微信公众号。

    您的关注阅读就是对爱运营最大的支持!敬请关注爱运营,运营者个人微信:rubin_han

    点击“阅读全文”,查看更多运营文章f8de7514d40a6af94f3ddd86bece53f7.png
    展开全文
  • Excel常用技巧——工作中最常用30个Excel函数公式

    万次阅读 多人点赞 2018-11-20 19:06:53
    Excel常用技巧--工作中最常用30个Excel函数公式   目录 ...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函数公式

    展开全文
  • EXCEL函数公式

    热门讨论 2010-03-16 03:26:38
    excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)公式 按...
  • 05.IF的多条件判断公式:C2=IF(AND(A2<500,B2="未到期"),"补款","")说明:两个条件同时成立用AND,任一个成立用OR函数。三、统计公式06.统计两表重复公式:B2=COUNTIF(Sheet15!A:A,A2)说明:如果返回值大于0说明在...
  • 目录一、数字处理1、取绝对值2、取整3、四舍五入二、判断公式1、把公式产生错误值显示为空2、IF多条件判断返回值三、统计公式1、统计两个表格重复内容2、统计不重复的总人数四、求和公式1、隔列求和2、单条件...
  • 目录 一、数字处理 1、取绝对值 2、取整 3、四舍五入 二、判断公式 1、把公式产生错误值显示为空 ...2、统计不重复的总人数 四、求和公式 1、隔列求和 2、单条件求和 3、单条件模糊...
  • 本书既可以作为函数速查工具手册,又可以作为丰富的函数应用案例宝典,适合对Excel 公式与函数有需求的读者阅读。 第1章 公式与函数基础 1 1.1 了解公式 1 1.1.1 公式的组成部分 1 1.1.2 数据类型及其相互转换 2...
  • 7.4.2 统计满足条件的单元格数量 7.4.3 使用AND筛选条件 7.4.4 使用OR条件 7.4.5 综合使用AND和OR筛选条件 7.4.6 出现频率最高计数 7.4.7 确定特定文本出现频率 7.4.8 统计唯一值数目 7.4.9...
  • 7.4.2 统计满足条件的单元格数量 7.4.3 使用AND筛选条件 7.4.4 使用OR条件 7.4.5 综合使用AND和OR筛选条件 7.4.6 出现频率最高计数 7.4.7 确定特定文本出现频率 7.4.8 统计唯一值数目 7.4.9...
  • 变差函数

    2020-10-26 21:47:02
    由于统计推断一般都要进行重复采样,但储层属性参数有其特殊性,每个位置可能有个样本,所以对随机函数Z(u)提出了本征假设,试图采用随机函数理论来接近空间插值。 二阶平稳性是指同时满足下面两个条件: 1.2 ...
  • excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)公式 按...
  • 20.一个能计算是否有重复单元的函数 21.数字金额转中文大写 22.将数字转成英文 23.人民币大小写转换 24.获取区域颜色值 25.获取活动工作表名 26.获取最后一行行数 27.判断是否连接在线 28.币种转换 29.检验工作表...
  • 也可以将本书作为学习Excel 函数的参考书或习题集,以通过对本书中案例的演练来掌握常用的函数。 本书主要适用于希望快速掌握Excel函数相关知识,并使用公式和函数解决办公中实际问题的用户,适用于不同年龄段的办公...
  • excel使用

    2012-11-25 17:06:01
    此时可察看即将绘制的函数图像,发现并是我们所要的函数曲线,单击“下一步”按钮,选中“数据产生在列”项,给出数据区域,这时曲线就在我们面前了(如图8)。图8需要注意:如何确定自变量的初始值,数据点之间...
  • 技巧126 筛选状态下重复统计 第13章 高级筛选 技巧127 了解高级筛选 技巧128 设置高级筛选的条件区域 技巧129 使用定义名称取代高级筛选区域选择 技巧130 将筛选结果复制到其他工作表 技巧131 在...
  • E库多条件查询模块.ec E库模糊查询模块.ec E 电子琴.ec fhhs.ec filetime.ec FlyFox_hotkey.ec FlyFox_ram.ec font.ec Force.ec ForceSample.ec freemark 模块_取文件.ec ftp文件操作模块.ec gdiplus类模块.ec ...
  • │ │ 技巧218 统计不重复数量.xls │ │ 技巧219 单个区域排名.xls │ │ 技巧220 个区域排名.xls │ │ 技巧221 中国式排名.xls │ │ 技巧222 求第n大的不重复值.xls │ │ │ ├─第21章-查找与...
  • 1350个精品易语言模块提供下载

    热门讨论 2011-06-06 17:51:09
    E库多条件查询模块.ec E库模糊查询模块.ec E 电子琴.ec fhhs.ec filetime.ec FlyFox_hotkey.ec FlyFox_ram.ec font.ec Force.ec ForceSample.ec freemark 模块_取文件.ec ftp文件操作模块.ec gdiplus类模块.ec ...
  • 现代统计学与SAS应用

    2008-12-01 14:52:34
     第6节 完全拉丁方设计及其资料的统计分析  第7节 希腊拉丁方设计及其资料的统计分析  第8节 析因设计及其资料的统计分析  第9节 含区组因素析因设计及其资料的统计分析  第10节 正交设计及其资料...
  • 实例181 对数据进行多条件排序 实例182 对统计结果进行排序 实例183 按姓氏笔画排序 实例184 按姓氏拼音排序 实例185 按仓库分组统计图书库存(多列) 实例186 多表分组统计 实例187 使用compute 实例188 使用compute ...
  • 这种操作模式提供可重复的读取, 并确保在同一事务处理种对相同数据两次查询看到是相同值。 值范围: TRUE | FALSE 默认值: FALSE row_locking: 说明: 指定在表已更新或正在更新时是否获取行锁。如果设置为 ...
  • 实例058 用TreeSet生成不重复自动排序随机数组 实例059 Map映射集合实现省市级联选择框 第4章 字符串处理技术 4.1 格式化字符串 实例060 把数字格式化为货币字符串 实例061 格式化当前日期 实例062 货币金额...
  • 实例058 用TreeSet生成不重复自动排序随机数组 实例059 Map映射集合实现省市级联选择框 第4章 字符串处理技术 4.1 格式化字符串 实例060 把数字格式化为货币字符串 实例061 格式化当前日期 实例062 货币金额...
  •  6.4.5 使用单个R1C1公式替换个A1公式  6.4.6 记住列字母对应列号  6.5 条件格式必须使用R1C1引用  6.5.1 通过用户界面设置条件格式  6.5.2 使用VBA设置条件格式  6.5.3 找出G列中最大值  6.6 ...
  • 7.4.6 一个模板上个数据表显示 104 7.5 熟练操作我工作台 105 7.5.1 在我工作台中查找 105 7.5.2 多选单据、批量操作 107 7.5.3 导出数据 108 7.6 自定义打印 110 7.6.1 设置模板打印格式 111 7.6.2 填报...
  • 实例216 访问类中私有成员的函数 实例217 实现类的加法运算 实例218 在类中实现事件 实例219 命名空间的使用 实例220 模板的实现 实例221 const函数的使用 实例222 使用纯虚函数代替接口 实例223 定义嵌套类...
  • 实例216 访问类中私有成员的函数 实例217 实现类的加法运算 实例218 在类中实现事件 实例219 命名空间的使用 实例220 模板的实现 实例221 const函数的使用 实例222 使用纯虚函数代替接口 实例223 定义嵌套类...
  • 测试覆盖率

    2011-10-28 11:16:51
    也可以将缺陷计数作为一个或个缺陷参数的函数来报告,如作为缺陷密度报告中采用的严重性或状态参数的函数。这些分析类型分别为揭示软件可靠性的缺陷趋势或缺陷分布提供了判断依据。  例如,预期缺陷发现率将随着...

空空如也

空空如也

1 2 3
收藏数 46
精华内容 18
关键字:

多条件不重复统计的函数公式