第二批!整理的90个公式,学完你就能碾压99%的同事

Excel知识 2年前 (2021) 天骄
0
  全套总共90个案例,超级全面。今天,主要分享各种文本和时间函数的运用,同样是15个案例。坚持看完,你的水平肯定会更上一层楼。如果点赞数超过100个,明天继续分享。
第二批!整理的90个公式,学完你就能碾压99%的同事

16.姓名分离(LEFT/RIGHT函数)

字符合并分离是很常见的事儿,我们知道&可以将字符合并在一起,那用什么函数可以将字符分离开呢?A列的姓名都是3个字,分别在B列得到姓,C列得到名。

姓:

=LEFT(A2,1)
第二批!整理的90个公式,学完你就能碾压99%的同事

名:

=RIGHT(A2,2)
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

LEFT函数的语法如下:

LEFT(字符,N)

从左边提取字符N位。

RIGHT函数的语法如下:

RIGHT(字符,N)

从右边提取字符N位。

因为姓都是1位,就是从左边提取1位;名都是2位,就是从右边提取2位。

17.获取省份(FIND函数)

在一份快递单里,如何将省份提取出来,省份的字符数不确定?

=LEFT(A2,FIND("省",A2)-1)
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

FIND函数的语法如下:

FIND(要查找的字符,文本) 

从文本中查找字符出现的位置。

FIND("省",A2)获取省字出现的位置3,省份不包含省字,就是出现的位置减去1,也就是FIND("省",A2)-1,因为是从左边提取,所以用LEFT函数。

18.区分手机跟电话(LEN函数)

在一堆杂乱的号码中,如何快速区分这些是手机还是电话?

=IF(LEN(C2)=11,"手机","电话")
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

LEN函数的语法如下:

LEN(字符)

计算字符中有多少位数。

手机号码是11位,电话号码是不等于11位的。用LEN函数判断是否等于11位,如果是就显示手机,否则就显示电话。

19.手机号加密(REPLACE函数)

为了保护中奖者的隐私,电视台在公布中奖号码通常都是恭喜138尾号是1234的朋友中了一等奖,也就是中间部分号码不公布。如何将手机号4-7位用星号(*)表示,达到加密的效果。

=REPLACE(C2,4,4,"****")
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

REPLACE函数的语法如下:

REPLACE (字符,开始位置,替换的字符个数,替换成什么)

将字符中某一段字符替换成新字符。4-7位替换掉,就是开始位置为4,替换掉4个字符数,替换成****。

20.日期运算

我们经常可以看见类似的问题,这个产品再过10天就可以交货了,今天是12/26距离过年还有多少天的倒计时。在Excel中如何处理这些问题呢?

结束日期:

=A2+B2
第二批!整理的90个公式,学完你就能碾压99%的同事

间隔天数:

=B8-A8
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

我们都知道数字可以直接进行四则运算,其实日期也属于数字。将单元格的日期格式改成常规格式。这样我们就可以看到日期的本质,其实就是数字。

第二批!整理的90个公式,学完你就能碾压99%的同事

21.今天的日期及时间(TODAY/NOW函数)

我们经常会听到这样的问题:今天的日期?现在是几点了?当听见这些,第一反应就是拿起手机,查看日期跟时间。如果你现在坐在电脑前,也可以借助Excel帮你快速获取这些信息。

第二批!整理的90个公式,学完你就能碾压99%的同事

今天的日期用下面的公式:

=TODAY()

也可以使用快捷键Ctrl+;

现在的时间用下面的公式:

=NOW()

也可以使用快捷键Ctrl+Shift+;

原理分析

这2个函数是Excel中最简单的函数,没有任何参数。TODAY翻译过来就是今天,NOW 翻译过来就是现在,只需记住这2个的意思即可。

22.获取间隔N个月的日期(EDATE 函数)

已知开始日期跟间隔月份,如何获取结束日期?

=EDATE(A2,B2)
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

返回表示某个日期的序列号,该日期与指定日期相隔(之前或之后)指示的月份数。使用函数 EDATE 可以计算与发行日处于一月中同一天的到期日的日期。

函数语法如下:

EDATE(日期,间隔月份)

间隔月份为正数就是之后的日期,0就是本月,负数就是之前的日期。

=EDATE(A2,-1)

就是指定日期之前的1个月。

23.计算工龄(DATEDIF函数)

如何通过入职日期跟离职日期,计算工龄?工龄是以×年×个月的形式显示。

=DATEDIF(A2,B2+1,"y")&"年"&DATEDIF(A2,B2+1,"ym")&"个月"
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

DATEDIF函数的语法如下:

DATEDIF(开始日期,结束日期,间隔参数)

第 三 参 数 类 型:

"y"时间段中的整年数,"m"时间段中的整月数,"d"时间段中的天数。

"md"日期中天数的差,忽略日期中的月和年。"ym"日期中月数的差,忽略日期中的日和年。"yd"日期中天数的差,忽略日期中的年。

理解了第三参数的含义后,公式就容易理解很多。

DATEDIF(A2,B2+1,"y")&"年"

这部分就是获取相差的年份,DATEDIF函数在计算工龄的时候会相差1天,如2014/1/1 到2014/12/31这样本来是1年,而DATEDIF函数认为这样不足1年,只有2014/1/1到2015/1/1 这样才算1年,所以在结束日期后面+1,就刚好避免了这种误差。

DATEDIF(A2,B2+1,"ym")&"个月"

这部分是获取相差的月份。

年份跟月份最后连接起来就是×年×个月的形式。

24.文字跟数字分离(LEN函数)

在一堆字符串中,左边是文字,右边是数字,看字数长度又不确定,如何将两者分离开?

文字:

=LEFT(A2,LENB(A2)-LEN(A2))
第二批!整理的90个公式,学完你就能碾压99%的同事

数字:

=RIGHT(A2,2*LEN(A2)-LENB(A2))
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

LEN函数是统计字符数,他有一个带B的兄弟,LENB函数,语法跟LEN函数一样,但作用略有差异。LENB函数是统计字节数,文字按2字节算,数字按1字节算。根据这个特点就可以来进行公式设置。

文字的个数就是,总字节减去总字符数,比如广东电信10000,总字节数就是13,而总字符数是9,因为每个文字都多出一个字节数,13-9=4就刚刚好是4个文字。也就是:

=LENB(A2)-LEN(A2)

因为文字在左边,就用LEFT函数提取。

而数字的个数,就是总字符数减去文字的字符数,也就是:

=LEN(A2)-文字数=LEN(A2)-(LENB(A2)-LEN(A2))=2*LEN(A2)-LENB(A2)

其实很多时候计算都跟数学差不多,如果稍微有点数学基础,学习函数嵌套会更有帮助。

知道了数字个数,因为是从右边提取,就可以直接嵌套RIGHT函数。

25.提取金额(MIDB函数)

某位朋友有记账的习惯,会将每月的各项消费详细的记录起来,如何将这些金额提取出来,方便以后统计呢?

=MIDB(A2,SEARCHB("?",A2),2*LEN(A2)-LENB(A2))
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

在这里数字的位置是不确定的,唯一可以利用的条件就是字节这个条件。因为数字都是1个字节,而文字是2个字节,只要判断1个字节的开始位置就可以。

首先可以想到的是借助通配符问号(?)来查询,看看在什么位置,而查找字符的位置一般都是用FIND函数,现在是用字节,也就是用FINDB函数。但是输入公式后却显示错误值,怎么回事呢?

第二批!整理的90个公式,学完你就能碾压99%的同事

FINDB函数不支持使用通配符,而SEARCHB函数的用法跟FINDB一样,但支持使用通配符。

看到这么一段话,基本上确定下用SEARCHB函数来解决问题,在B2单元格输入公式,得到5,跟预想中的一样,文字是双字节,2个就是4,接下来就是数字,刚好就是第5位。

第二批!整理的90个公式,学完你就能碾压99%的同事

前面文字跟数字的分离,就提到了计算数字的个数。

而数字的个数,就是总字符数减去文字的字符数,也就是:

=LEN(A2)-文字数=LEN(A2)-(LENB(A2)-LEN(A2))=2*LEN(A2)-LENB(A2)

现在开始位置确定,要提取的数字长度确定,只需在外面嵌套一个MIDB函数即可,因为MID函数是根据字符提取,而MIDB才是根据字节提取。

其实写公式有的时候就是一个猜测,推断的过程。先猜测用某个函数解决,顺利的话就一次解决,不过有的时候猜测错误,就重新再猜测,看帮助了解相关函数之间的特点。了解后,再进一步测试,直到成功为止。

26.提取百分比数字(TEXT函数)

如何提取区间左边的百分比数字,也就是下限?

=TEXT(LEFT(A2,FIND("-",A2&"-")-1),"0%")
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

区间前几个都有包含-这个字符,而最后一个没有,如果直接用FIND函数查找的话,没有包含-会显示错误值。

A2&"-"的作用就是保证每个都包含-,这样用FIND函数查找就不会出现错误值,因为FIND函数默认情况下就是查找第一个-的位置,所以即使在最后面连接-,也不会影响前面查找到的位置。

本来知道-的位置,然后减去1,用LEFT函数从左边提取就应该没问题,但实际上最后一个出现了一个小小的问题,变成了1.01。

其实百分比的实质也是数字,101%就是1.01。TEXT函数的作用就是将数字转换成百分比。

TEXT函数语法如下:

TEXT(数字,要显示的格式)

27.将数字拆分到每个单元格(MID函数)

有一列数字,需要将这些数字,分别拆开到每一个单元格,该怎么做?

=MID($A2,COLUMN(A1),1)
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

拆分数字就是依然取第1位、第2位……第8位,也就是不断的改变MID的第2参数。

=MID($A2,1,1)=MID($A2, 2,1)

……

=MID($A2, 8,1)

如果第2参数用手工更改会比较麻烦,而且还可能出错,这时借助COLUMN函数就可以获取1到N的序号。

28.提取最后一个分割符的内容(TRIM函数)

在源数据中的都是以/作为分隔符号,如何提取最后一个分割符合/后面的内容呢?

=TRIM(RIGHT(SUBSTITUTE(A2,"/",REPT(" ",99)),99))
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

在最后面就用RIGHT函数提取,但提取的字符数不确定,所以不能直接提取。因为是以/作为分隔符号,我们可以将这个分割符号替换成99个空格,这样从右边提取99位,就保证能够提取到最右边的所有字符。

为了方便说明,我们将99换成10,这样就可以清晰得看到这部分SUBSTITUTE(A2,"/",REPT(" ",10))在编辑栏用F9键抹黑解读的结果。每个分隔符现在是变成了10个空格。

第二批!整理的90个公式,学完你就能碾压99%的同事

最后面的字符数+10个空格,用RIGHT函数从右边提取10位,因为最后面的字符数少于10,必然就能够全部提取到,同时也会提取到一部分空格。

第二批!整理的90个公式,学完你就能碾压99%的同事

为什么要将空格数还有提取的字符写成99?

因为我们要提取的字符数不确定,不过正常的话,字符数不会超过99,写99就是保证能将所有字符提取到。如果已知只有10个字符以内,我们写10也是完全可以。

因为提取出来的字符还包含空格,需要去除掉才可以。TRIM函数就是去除多余空格用的,语法如下:

TRIM(字符)

29.将多列内容合并成一列(PHONETIC函数)

ABC三列分别是省份、城市、区县,如何将这三列合并成一列变成地址?

=PHONETIC(A2:C2)
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

函数语法:

=PHONETIC(区域)

将区域中的文本合并在一起。

30.将标题跟内容合并在一个单元格换行显示(CHAR函数)

有一份员工工资明细表,现在要制作类似于工资条的形式,上面标题,下面内容。唯一不同的就是,合并后的内容在一个单元格内换行显示而已。

输入公式后,将单元格设置为自动换行。

=A$1&CHAR(10)&A2
第二批!整理的90个公式,学完你就能碾压99%的同事

原理分析

换行符在计算机当中的代码是10,所以在公式当中使用CHAR(10)后,再设置单元格格式为自动换行,即可实现结果换行显示。

CHAR函数的语法如下:

CHAR(代码)

代码只需记住几个常用的即可,比如生成大写字母A,就是65;生成√就是41420。

第二部分就先分享到这里,记得点赞收藏。

版权声明:天骄 发表于 2021-12-28 12:45:22。
转载请注明:第二批!整理的90个公式,学完你就能碾压99%的同事 | 艺财驿

暂无评论

暂无评论...