一大波常用函数公式,值得收藏!

《一大波常用函数公式》微信推送后,同学们很是喜爱,今天重发,小伙伴们可以收藏一下,在日常工作中如果有类似的问题,拿来即用。

话不多说,上菜——

1、查找重复内容公式:

=IF(COUNTIF(A:A,A2)>1,"重复","")

2、查找重复身份证号码公式:

=IF(COUNTIF(A:A,A2&"*")>1,"重复","")

3、用出生年月来计算年龄公式:

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

4、根据身份证号计算出生年月公式:

=--TEXT(MID(A2,7,8),"0!/00!/00")

5、根据身份证号码提取性别公式:

=IF(MOD(MID(A2,15,3),2),"男","女")

6、根据身份证号码计算退休时间:

=EDATE(TEXT(MID(A2,7,8),"0!/00!/00"),MOD(MID(A2,15,3),2)*120+600)

7、计算合同到期公式:

=EDATE(A4,3)

8、条件求和。

计算销售一部的销售总额:

=SUMIF(B:B,"销售一部",C:C)

9、多条件求和。

计算销售一部1000以上的销售额:

=SUMIFS(C:C,B:B,"销售一部",C:C,">1000")

10、计算某个日期是第几季度:

=LEN(2^MONTH(A2))&"季度"

11、指定条件的最小值:

{=MIN(IF(B2:B10="销售一部",C2:C10))}

注意:这里的公式最外层多了一对花括号。这就是传说中的数组公式,输入时需要按住shift+ctrl不放,按回车。花括号就会自动添加了,手工添加可是无效的哦。

12、指定条件的最大值:

{=MAX(IF(B2:B10="销售一部",C2:C10))}

这个公式和第一个公式的用法类似,只是将计算最小值的MIN函数换成了计算最大值的MAX函数。

13、指定条件的平均值:

=AVERAGEIF(B2:B10,"销售一部",C2:C10)

AVERAGEIF函数的语法换成普通话的意思大致是:

=AVERAGEIF(条件区域,指定的条件,计算平均值的区域)

第三个参数可以忽略,比如说使用下面这个公式:

=AVERAGEIF(C2:C10,">950")

就是用来计算销售额在950以上的平均值。

14、多条件计数:

=COUNTIFS(B2:B10,"销售一部",C2:C10,">950")

COUNTIFS函数的语法换成普通话的意思大致是:

=COUNTIFS(区域1,条件1, 区域2,条件2…区域N,条件N)

除此之外,还可以使用SUMPRODUCT函数计算多条件计数:

=SUMPRODUCT((B2:B10="销售一部")*(C2:C10>950))

用SUMPRODUCT函数多条件计数的语法,换成普通话的意思大致是:

=SUMPRODUCT((区域1=条件1)*(区域2=条件2)* (区域N=条件N))

15、多条件求和:

前面的内容中,咱们说过多条件求和的SUMIFS函数,如果使用SUMPRODUCT函数多条件求和,语法为:

=SUMPRODUCT((区域1=条件1)*(区域2=条件2)* 求和区域)

16、用VLOOKUP函数查询数据:

=VLOOKUP(C14,B1:C10,2,)

如果把VLOOKUP函数的语法换成普通话,意思大致是:

=VLOOKUP(查询的值,区域,返回第几列的内容,匹配类型)

VLOOKUP函数是使用率最高的函数之一了,日常的查询应用中经常会用到TA。这里有几个问题需要注意:

①第二参数区域的首列中必须要包含查询值。

②第三参数是数据区域的第几列,而不是工作表的第几列。

③如果第四参数忽略,VLOOKUP函数查找时模糊匹配,但要求数据源区域升序排序。

原文发布于微信公众号 - 数据的力量(shujudeliliang)

原文发表时间:2015-04-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏温安适的blog

以回溯解高速公路重建与正序全排列

3576
来自专栏炉边夜话

考场安排---图的着色原理之运用

试设计一算法,当给定一个图时G=(V,E),|V|=n,(Vi,Vj)ЄE,当且仅当有一个同学选了课程i和课程j,试给出一个考试安排方案N1,N2,N3…Nk,...

831
来自专栏数据结构与算法

2455 繁忙的都市

题目描述 Description        城市C是一个非常繁忙的大都市,城市中的道路十分的拥挤,于是市长决定对其中的道路进行改造。城市C的道路是这样分布的...

3849
来自专栏数据结构与算法

【BZOJ3203】保护出题人(动态规划,斜率优化)

在最优情况下,肯定是存在某只僵尸在到达重点的那一瞬间将其打死 我们现在知道了每只僵尸到达终点的时间,因为僵尸要依次打死。 所以我们假设血量的前缀和是\(s_...

1205
来自专栏数说工作室

【SAS Says】基础篇:8. 相关、回归等初步统计

SAS是一个专业的统计软件,前面我们介绍了很多数据管理、输出美化的东西,本节终于要介绍一点SAS做统计的知识了,不过,在基础篇中我们只大概介绍一下,更多统计分析...

2816
来自专栏xingoo, 一个梦想做发明家的程序员

推荐系统那点事 —— 基于Spark MLlib的特征选择

在机器学习中,一般都会按照下面几个步骤:特征提取、数据预处理、特征选择、模型训练、检验优化。那么特征的选择就很关键了,一般模型最后效果的好坏往往都是跟特征的选...

2199
来自专栏程序员叨叨叨

7.1 Cg 关键字第 7 章 输入\输出与语义绑定

第三章从 GPU 运行原理和数据流程的角度阐述了顶点着色程序和片段着色程序的输入输出,即,应用程序(宿主程序)将图元信息(顶点位置、法向量、纹理坐标等)传递给顶...

1023
来自专栏老马说编程

(34) 随机 / 计算机程序的思维逻辑

随机 本节,我们来讨论随机,随机是计算机程序中一个非常常见的需求,比如说: 各种游戏中有大量的随机,比如扑克游戏洗牌 微信抢红包,抢的红包金额是随机的 北京购...

2136
来自专栏落影的专栏

iOS开发-OpenGLES进阶教程2

教程 OpenGLES入门教程1-Tutorial01-GLKit OpenGLES入门教程2-Tutorial02-shader入门 OpenGLES入门教程...

2877
来自专栏数据结构与算法

2727:仙岛求药

2727:仙岛求药 查看 提交 统计 提问 总时间限制:1000ms内存限制:65536kB描述少年李逍遥的婶婶病了,王小虎介绍他去一趟仙灵岛,向仙女姐姐要仙丹...

2848

扫码关注云+社区