今天和大家分享一组常用函数公式的使用方法，用心掌握这些函数，工作效率会大大提升。欢迎转发给更多有需要的人。
 
一、相关概念
 
(一)函数语法
 
由函数名+括号+参数组成
 
例： 求和函数：SUM(A1,B2,…) 。参数与参数之 间用逗号“,”隔开
 
(二)运算符
 
1. 公式运算符：加(+)、减(-)、乘(*)、除(/)、百分号(%)、乘幂(＾) 
 
2. 比较运算符：大与(>)、小于(=) 、不等于(<>)
 
3. 引用运算符：区域运算符(：)、联合运算符(,)
 
(三)单元格的相对引用与绝对引用
 
例： A1
 
 $A1 锁定第A列
 
 A$1 锁定第1行
 
 $A$1 锁定第A列与第1行
 
二、常用函数
 
(一)数学函数
 
1. 求和 =SUM(数值1,数值2,……)
 
2. 条件求和 =SUMIF(查找的范围,条件(即对象),要求和的范围)
 
例：(1)=SUMIF(A1:A4,”>=200”,B1:B4)
 
 函数意思：对第A1栏至A4栏中，大于等于200的数值对应的第B1列至B4列中数值求和
 
(2)=SUMIF(A1:A4,”<300”,C1:C4)
 
函数意思：对第A1栏至A4栏中，小于300的数值对应的第C1栏至C4栏中数值求和
 
3. 求个数 =COUNT(数值1,数值2,……)
 
例：(1) =COUNT(A1:A4) 函数意思：第A1栏至A4栏求个数
 
 (2) =COUNT(A1:C4) 函数意思：第A1栏至C4栏求个数
 
4. 条件求个数 =COUNTIF(范围,条件)
 
例：(1) =COUNTIF(A1:A4，”<>200”)
 
 函数意思：第A1栏至A4栏中不等于200的栏求个数
 
 (2) =COUNTIF(A1:C4,”>=1000”)
 
 函数意思：第A1栏至C4栏中大于等1000的栏求个数
 
5. 求算术平均数 =AVERAGE(数值1,数值2,……)
 
例：(1) =AVERAGE(A1,B2)
 
(2) =AVERAGE(A1:A4)
 
6. 四舍五入函数 =ROUND(数值,保留的小数位数)
 
7. 排位函数 =RANK(数值,范围,序别) 1-升序 0-降序
 
例：(1) =RANK(A1,A1:A4,1)
 
 函数意思：第A1栏在A1栏至A4栏中按升序排序，返回排名值。
 
 (2) =RANK(A1,A1:A4,0)
 
 函数意思：第A1栏在A1栏至A4栏中按降序排序，返回排名值。
 
8. 乘积函数 =PRODUCT(数值1,数值2,……)
 
9. 取绝对值 =ABS(数字)
 
10. 取整 =INT(数字)
 
(二)逻辑函数
 
条件函数：=IF(条件,执行条件,执行条件假)—可执行七层嵌套
 
例： A1=5 A2=3 A3=6 A4=7
 
=IF(A1>A2,1,0)=1(真)
 
=IF(A1
 
=IF(A1>A2,IF(A3>A4,8,9),1)=9
 
(三)文本函数
 
1. 截取函数： =LEFT(文本,截取长度)—从左边截取
 
=RIGHT(文本,截取长度)—从右边截取
 
=MID(文本,开始位,截取长度)—从中间截取
 
2. 计算字符长度：LEN(文本)—文本的长度
 
3. 合并字符函数：CONCATENATE(文本1,文本2,…)
 
4. 在字符串中查找特定字符：FIND(文本,范围,数值)—数值表示查找第几个
 
=FIND(“a”,“abcadeafga”,2)=4
 
=FIND(“a”,“abcadeafga”,3)=7
 
=FIND(“a”,“abcadeafga”,4)=10
 
5. 比较两个字符是否完全相符：EXACT(文本1,文本2)
 
=EXACT(“a”,“a”)=TRUE
 
=EXACT(“a”,“ab”)=FALSE
 
=EXACT(“a”,“A”)=FALSE
 
6. 将数值转化为文本：TEXT(数值,参数) )—参数一般为0
 
7. 将数值型字符转换为数值：VALUE(数值文本)
 
(四)判断公式
 
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. 单条件求和
 
3. 单条件模糊求和
 
4. 多条件模糊求和
 
5. 多表相同位置求和
 
公式：B2=SUM(Sheet1:Sheet19!B2)
 
说明：在表中间删除或添加表后，公式结果会自动更新。
 

  
 
 
6. 按日期和产品求和
 
(七)查找与引用公式
 
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. 按数字区域间取对应的值

起因：因领导层需要将电商平台利润核算自动化，原始数据不可以动，所以需要用到公式
 
（PS：其实所谓自动化不是Python / VBA办公自动化，就是嵌套一堆公式，然后每月更新电商后台基础数据，就可以出来利润结果~）
 
 
 
正文：
 
普通方法1：
 
直接使用=SUM(1/COUNTIF(A1:A1000,A1:A1000))
 
普通方法2：
 
=SUMPRODUCT(1/COUNTIF(A1:A500,A1:A500))
 
但是：
 
上面的两个方法都有一个BUG，如果选中的区域有空，那么就会报错 #DIV/0!，因为是除法，有空，就意味着有0，除以0就会报错。
 
 
进化方法1：
 
=1/COUNTIFS(A:A,A2)
 
再对此列进行求和
 
 
 
 
进化方法2：
 
1、=COUNTIF($A$1:A3,A3)
 
    1.1、需要锁定第一个单元格，下拉公式，这样区域就会不断增加，
 
2、=COUNTIF(B:B,1)
 
    2.1、再计算结果为1的数量
 
 
 
 
 
 
 
进化方法3：
 
先进行计算当列有多少数值，再使用INDIRECT函数引用此数值，进行套用未进化的方法1 / 未进化的方法2
 
因为此方法太麻烦，且不常用，故而就只是说一个概念留存~
 
 
 
 

 
 
 
 
 
 
 
 工作中常用的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)
 
 
 说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日
 

 
 
 

 
 
 
 
 
 
 

 
 
 

 
 
 

 参考资料：
 https://mp.weixin.qq.com/mp/profile_ext?action=home&__biz=MjM5NDYyNzAzNQ==&scene=110#wechat_redirect
 

 
 
 

 
 
 
 
 
 
 
 
 

iamlaosong文
 
要求从货运明细中统计各省某日发货点数量，公式如下：
 
=SUMPRODUCT((Sheet1!$L$2:$L$1747=B6) * (Sheet1!$M$2:$M1747=$A$1) / COUNTIF(Sheet1!$U$2:$U$1747,Sheet1!$U$2:$U$1747))
 
Sheet1是货运明细，其中L列是发货省，B6是要统计的省份；M列是发货日期，A1是要统计的日期，U列是发货点。
 
需要注意的是最大行数1747必须是有效行数，如果为了通用，把行数放大，比如2000，由于1747行后面没有数据，导致countif函数的统计结果为0，就会产生被零除的错误。
 
 
 
这种方法给客户后，客户说函数的运算量大，很卡。换个方法吧，先对明细建立一个查询，本想用SQL语句一次完成，可是，用于Excel的SQL语句既不支持where中直接引用单元格的值，也不支持count(distinct 字段名)这种写法，所以，最后采用先建立一个去重复的查询，再用sumifs求和。
 
1、建立查询的方法
 
数据选项卡—现有连接—浏览更多 或者 按快捷键Alt+D+D+D
 选择要查询的Excel文件和文件中的的工作表，就可以将相应工作表的数据取过来。表现形式选择表。
 
查询结果表放在W6开始的区域，如目前的结果是W6:Y61。
 
 
 
2、修改查询
 
查询结果表处点击右键，表格—编辑查询，查询语句如下：
 
select 日期,始发省份,count(*) as 网点数量 from (select distinct 日期,始发省份,揽收机构 from [Sheet1$]) group by 日期,始发省份
 
select 月份,始发省份,count(*) as 网点数量 from (select distinct 月份,始发省份,揽收机构 from [Sheet1$]) group by 月份,始发省份
 
结果如下：
 
 
 
日期
始发省份
网点数量
月份
始发省份
网点数量
10月01日
云南省
1
10月
浙江省
1
10月02日
云南省
1
10月
安徽省
47
10月05日
云南省
1
10月
云南省
2
10月09日
云南省
1
10月
河南省
1
10月10日
云南省
1
11月
湖北省
4
10月11日
云南省
1
11月
浙江省
1
10月12日
安徽省
1
11月
安徽省
16
10月12日
云南省
1
11月
云南省
2
10月13日
云南省
1
10月14日
云南省
2
10月15日
云南省
1
10月16日
安徽省
2
10月16日
云南省
1
10月17日
安徽省
3
10月17日
云南省
1
10月18日
安徽省
6
 
 
3、统计某日网点数
 
 
 
公式如下：
 
=SUMIFS($Y$6:$Y$1000,$W$6:$W$1000,TEXT($A$1,"M月D日"),$X$6:$X$1000,B6)
 
其中1000是预留的查询结果行数，可以大于实际行数，不影响统计结果，这样明细增加的时候就没问题了。
 
4、Excel使用distinct关键字时似乎很苛刻，除了上面提到的，对字段做处理后取不重复也不行，比如distinct left(日期,3)就不行。