在数据的处理和分析中,函数或公式是使用率比较高的工具之一,但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”的作用为:将数值按照会计格式进行大写。 |
|
来自: hercules028 > 《excel》