15个Excel函数公式案例解读,便捷高效,办公必备
在数据的处理和分析中,函数或公式是使用率比较高的工具之一,但Excel的函数非常的繁多,想要全部掌握,几乎是不可能的,所以,我们必须掌握常用的函数,公式!
一、对Excel工作表中的数值向下取整
函数:Int。
功能:将数值向下取整为最接近的整数。
语法结构:=Int(值或单元格引用)。
目的:舍去“月薪”小数点后的值。
方法:
在目标单元格中输入公式;=INT(G3)。
解读:
Int函数的作用为向下取整,即不进行四舍五入,直接省去小数点后面的值。
二、对Excel工作表中的数值向上取整
函数:Roundup。
功能:向上舍入数字。
语法结构:=Roundup(值或单元格引用,小数位数)。
目的:对“月薪”向上取整。
方法:
在目标单元格中输入公式:=ROUNDUP(G3,0)。
解读:
Roundup函数的作用为“向上舍入”数字,即如果保留小数后还有值,一律“进一”。
三、对Excel工作表中的数值“四舍五入”
函数:Round。
功能:按指定的位数对数值“四舍五入”。
语法结构:=Round(值或单元格引用,小数位数)。
目的:对“月薪”四舍五入后,保留一位小数。
方法:
在目标单元格中输入公式:=ROUND(G3,1)。
解读:
Round函数对数字进行四舍五入,按照指定的小数位数保留值。
四、在Excel工作表中随机生成0-1之间的随机数
函数:Rand。
功能:返回大于或等于0小于1的平均分布随机数。
语法结构:=Rand()。
目的:在“备注”列随机生成0-1之间的随机数。
方法:
在目标单元格中输入公式:=RAND()。
解读:
如果对生成的随机值不满意,可以按F9(或Fn+F9)进行刷新。
五、在Excel工作表中随机生成指定范围内的随机值
函数:Randbetween。
功能:返回一个介于指定值之间的随机值。
语法结构:=Randbetween(开始值,结束值)。
目的:在“备注”列生成100-500之间的随机值。
方法:
在目标单元格中输入公式:=RANDBETWEEN(100,500)。
解读:
如果对生成的随机值不满意,可以按F9(或Fn+F9)进行刷新。
六、从Excel工作表中的身份证号码中提取出生年月。
函数:Text+Mid。
功能:Text函数功能为根据指定的格式将数值转换为文本,Mid函数的功能为:从字符串中指定的起始位置返回指定长度的字符。
语法结构:=Text(数值或单元格引用,格式代码);=Mid(值或单元格引用,起始位置,字符长度)。
目的:从身份证号码中提取出生年月的8位数值并转换为日期形式。
方法:
在目标单元格中输入公式:=TEXT(MID(C3,7,8),\”0-00-00\”)。
解读:
1、身份证号码中从第7位开始,长度为8的字符为出生年月。
2、首先利用Mid函数提取出生年月8位数字,然后用Text函数将其转换为日期格式。
七、从Excel工作表中的身份证号码中提取性别。
函数:If+Mod+Mid。
功能:Mod函数的功能为:返回两个数相除的余数。
语法结构:=Mod(被除数,除数)。
目的:从身份证号码中判断性别。
方法:
在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),\”男\”,\”女\”)。
解读:
1、身份证号码中的第17位数值代表性别,奇数为“男”,偶数为“女”。
2、首先利用Mid函数从身份证号码中提取第17位,并作为Mod函数的被除数,当除数为2时,其返回的值只有0或1两种,然后用If函数进行判断,如果值为1,则返回“男”,否则返回“女”。
八、从Excel工作表中的身份证号码中计算年龄。
函数:Datedif。
功能:以指定的方式统计两个日期之间的差值。
语法结构:=Datedif(开始日期,结束日期,统计方式)。常用的统计方式有三种,“Y”、“M”、“D”,即“年”、“月”、“日”。
目的:根据身份证号码计算对应的年龄。
方法:
在目标单元格中输入公式:=DATEDIF(TEXT(MID(C3,7,8),\”0-00-00\”),TODAY(),\”y\”)。
解读:
1、Datedif函数为系统隐藏函数,在官方的函数库中时无法找到对应的解读部分,在输入函数名称时,没有联想提示部分。且只能在16及以上版本中才能使用,WPS中也可以使用。
2、公式中首先利用Text+Mid从身份证号码中提取出生年月,然后和今天(Today())对比,计算两个日期之间的相差的年份(Y)。
3、此公式具有通用性,无论何时打开工作表,其年龄是自动计算的最新值,如果参数“结束日期”Today()替换成“2020-11-08”,其年龄不会自动变化,不会增长。
九、将Excel工作表中的数值可视化
函数:Rept。
功能:根据指定次数,重复文本。
语法结构:=Rept(文本,重复次数)。
目的:将“月薪”以图表的形式显示。
方法:
在目标单元格中输入公式:=REPT(\”|\”,G3/500)。
解读:
除以500是因为缩小G3单元格的值,否则在较小的空间中无法正常显示,在实际的应用中,需要灵活处理。
十、将Excel工作表中字符的首字符转换为大写
函数:Proper。
功能:将一个文本字符串中各英文单词的首字母转换为大写,其他字母转换为小写。
语法结构:=Proper(字符串或单元格引用)。
目的:将“拼音”中的第一个字母大写。
方法:
在目标单元格中输入公式:=PROPER(C3)。
十一、将Excel工作表中的字符全部大写
函数:Upper。
功能:将文本字符串转换成字母全部大写形式。
语法结构:=Upper(字符串或单元格引用)。
目的:将“拼音”全部大写。
方法:
在目标单元格中输入公式:=UPPER(C3)。
十二、将Excel工作表中的字符全部小写
函数:Lower。
功能:将一个字符串中的所有字母转换为小写形式。
语法结构:=Lower(字符串或单元格引用)。
目的:将“拼音”全部小写。
方法:
在目标单元格中输入公式:=LOWER(C3)。
十三、将Excel工作表中的日期转换为星期。
函数:Text。
功能:根据指定的格式将数值转换为文本。
语法结构:=Text(值或单元格引用,格式代码)。
目的:将“出生日期”中的值转换为对应的星期。
方法:
在目标单元格中输入公式:=TEXT(C3,\”aaaa\”)。
解读:
代码“aaaa”代表长星期,即星期X。
十四、计算出Excel工作表中的日期对应的周次。
函数:Weeknum。
功能:返回一年中的周次。
语法结构:=Weeknum(日期或单元格引用,计算方式)。
目的:返回“出生日期”对应的周次。
方法:
在目标单元格中输入公式:=WEEKNUM(C3,2)。
解读:
“计算方式”为2时,代表一周从星期一开始,星期日结束。
十五、将Excel工作表中的数值取整并大写。
函数:Numberstring。
功能:按照指定的格式对数字四舍五入取整并大写。
语法结构:=Numberstring(数字或单元格引用,格式代码)。
目的:对“月薪”大写。
方法:
在目标单元格中输入公式:=NUMBERSTRING(G3,2)。
解读:
1、Numberstring函数为系统隐藏函数,且只能在16及以上版本中应用,在WPS中同样可以使用。
2、格式代码“2”的作用为:将数值按照会计格式进行大写。
6个文本函数和常用函数的组合使用技巧,小函数,大作用
Excel的主要功能是数据处理,但是在使用的过程中,经常会遇到一些文本数据,例如提取指定的字符串,查找指定值的位置等等,这时候,掌握一定数量的文本函数是必不可少的。
一、文本函数Left+Find:提取指定的值。
目的:提取地址中的省市。
(一)基础函数解读。
1、left函数
功能:从一个字符串的第一个字符开始,返回指定长度的字符。
语法结构:=Left(字符串,[字符长度]),如果省略“字符长度”,则默认为1。
目的:提取指定单元格中的前3个字符。
方法:
在目标单元格中输入公式:=LEFT(B3,3)。
2、Find函数
功能:返回一个字符串在另一个字符串中的起始位置(区分大小写)。
语法结构:=Find(查找字符,被查找字符串,[查找开始位置])。
目的:查找“市”的位置。
方法:
在目标单元格中输入公式:=FIND(\”市\”,B3)。
(二)Left+Find:提取地址中的“省市”。
方法:
1、在目标单元格中输入公式:
2、快捷键Ctrl+Shift+Enter填充。
解读:
公式中使用Find函数查找“市”和“省”的位置,然后利用Left函数提取查找到的位置的左侧字符即可,但由于B列地址中可能有市,也可能有省,所以在Find函数中使用了一个常量数组{\”市\”,\”省\”},可以同时查找“市”和“省”。
二、文本函数Left+求和函数Sum:统计参会人数。
目的:统计各地区参会人数。
方法:
1、在目标单元格中输入公式:=SUM((LEFT(B$3:B$12,2)=F3)*C$3:C$12)
2、快捷键Ctrl+Shift+Enter填充。
解读:
公式中首先用Left函数提取B列单元格值的前2位,和F3单元格的值进行比较,形成一个True和False的数组,然后将此数组和C$3:C$12区域中的值相乘,最后用Sum函数求和,得到相应地区的总人数。
三、文本函数Len、Lenb:计算字符或字节数。
目的:计算字符串中的数字个数。
(一)基础函数解读。
1、Len函数。
功能:计算文本中的字符个数。
语法结构:=Len(字符串)。
目的:计算指定字符串的长度。
方法:
在目标单元格中输入公式:=LEN(B3)。
2、Lenb函数。
功能:计算文本中的字符个数,中文按照每个字2个字符计算。
语法结构:=Lenb(字符串)。
目的:计算指定字符串的长度。
方法:
在目标单元格中输入公式:=LENB(B3)。
解读:
从功能定义上看,Len函数和Lenb函数的作用是相同的,但结算的结果却是有区别的,Len函数计算的是字符的长度,即字符的个数,而Lenb函数计算的是字节的个数,每个中文按照2个字节来计算。
3、Len+Lenb:计算指定值中除中文外的字符长度。
方法:
在目标单元格中输入公式:=LEN(B3)*2-LENB(B3)。
解读:
对于公式的理解,可以先从结果入手,例如第一个字符串“电视机DSJ0090”中除中文外的字符长度为7,而7=字符长度-中文长度,即7=LEN(B3)-(LENB(B3)-LEN(B3)),整理之后的公式为=LEN(B3)*2-LENB(B3)。
四、文本函数Mid+Find+Len:获取特定字符串。
目的:提取字符串中的“部门”和“姓名”。
(一)基础函数Mid解读。
功能:从文本字符串中指定的位置起返回指定长度的字符。
语法结构:=Mid(字符串,起始位置,字符长度)。
目的:返回指定字符串中从第3个字符开始,长度为2的字符串。
方法:
在目标单元格中输入公式:=MID(B3,3,2)。
(二)提取字符串中的“部门”和“姓名”。
方法:
在目标单元格中输入公式:=MID(B3,1,FIND(\”:\”,B3)-1)、=MID(B3,FIND(\”:\”,B3)+1,LEN(B3))。
解读:
公式=MID(B3,1,FIND(\”:\”,B3)-1)中,首先利用Find函数获取“:”的位置,然后-1进行修正,也就是“部门”的长度,然后用Mid函数进行提取;公式=MID(B3,FIND(\”:\”,B3)+1,LEN(B3))中,同样也是用Find函数获取“:”的位置,然后+1进行修正,也就是“姓名”的起始位置,然后用Len获取字符串的总长度,来填充Mid函数的第3个参数,因为要提取的字符串最长也就是字符串长度。
(二)巧用分列法提取“部门”和“姓名”。
方法:
1、选定目标单元格。
2、【数据】-【分列】,选择【分隔符号】并【下一步】,在【分隔符号】的【其他】中输入:并【下一步】。
3、单击【目标区域】右侧的箭头,选择数据的存放位置,并单击箭头返回,【完成】-【确定】。
五、文本函数Rept:制作简易的销售图表。
(一)基础函数Rept解读。
功能:生成重复的字符。
语法结构:=Rept(重复的文本,重复的次数)。
目的:重复“部门获奖人员”信息2次。
方法:
在目标单元格中输入公式:=REPT(B3,2)。
(二)巧用Rept制作简易的销售图表。
方法:
1、在目标单元格中输入公式:=REPT(\”r\”,C3/10)。
2、设置单元格的字体为:Wingdings。
六、函数Concatenate+IF+Sum:评定员工考核绩效。
(一)基础函数Concatenate解读
功能:将多个文本合并到一起。
语法结构:=Concatenate(字符串1,字符串2……字符串N)。
目的:将个人信息连接成一个字符串。
方法:
在目标单元格中输入公式:=CONCATENATE(B3,C3,D3,E3)。
(二)评定员工考核绩效。
方法:
在目标单元格中输入公式:=CONCATENATE(SUM(C3:E3),\” | \”,IF(SUM(C3:E3)>240,\”优秀\”,\”一般\”))。
解读:
公式中首先利用Sum函数计算个人的总分,然后用IF函数判断总分的情况是“优秀”还是“一般”,最后用Concatenate将总分,“ | ”和判断的结果连接起来。
结束语:
文本从实际出发,对常用的文本函数及求和、判断等函数的综合应用做了详实的解读,对于使用技巧,你Get到了吗?欢迎在留言区留言讨论哦!
本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com
文章为作者独立观点不代本网立场,未经允许不得转载。