目录
- IF函数
- 等于判断
- 区间判断
- 与AND函数、OR函数一同使用
- IFNA函数和IFERROR函数
- LEFT、RIGHT、MID截取函数
- FIND函数、LEN函数
- SUBSTITUTE函数
- ASC函数、WIDECHAR函数
- 文本处理函数
- TEXT函数
- TEXTJOIN函数
- 日期函数
- DATE函数
- YEAR、MONTH、DAY,EDATE函数
- Datedif函数
- Weeknum、Weekday函数
IF函数
等于判断
-
参数:IF(logical_test, [value_if_true], [value_if false]
-
两种情况:判断是否超预算,如果C列的值大于B列,就是是,否则否!
-
公式:
=IF(C2>B2,“是”,“否”)
- 多重情况:
- 公式 =IF(B2=“易碎”,750,IF(B2=“一般”,300,0))
唯一要注意的地方,函数第三个参数就是否则的值,它是可以嵌套的!
区间判断
- 下例子:如何根据工龄算年假
- 公式 =IF(B2<2,5,IF(B2<=5,10,15))
注意 Excel中的公式,是从左往右执行的;第一个参数是<2 ;所以第二参数的条件必定包含了>=2,然后继续写接下的判断,再让它<=5即可…对于区间只需以此类推即可!
与AND函数、OR函数一同使用
- “且”条件判断
- 公式:=IF(AND(A2=“男”,B2>=60),1000,0)
Excel公式特性,实现“且”,利用一个单独函数,然后成为IF的一个参数,来参与判断条件!
- “或”条件判断:
- 公式:=IF(OR(B11>60,B11<40),1000,0)
- “且”“或”嵌套
- 公式
=IF(OR(AND(A19=“男”,B19>60),AND(A19=“女”,B19<40)),1000,0)
IFNA函数和IFERROR函数
- VLOOKUP函数出现错误:
有一处值出现错误?因为源数据根本不存在“张三”,所以会返回#N/A值,那么如何避免呢?
-
最外层再嵌套一个IFNA函数即可,也可以是IFERROR函数
-
公式 =IFNA(VLOOKUP(F3,A:C,3,0),“”)
- IFERROR函数,可以屏蔽一切错误原因
- 公式 =IFERROR((B4-C4)/B4,“”)
LEFT、RIGHT、MID截取函数
-
公式 =LEFT(A2,2)
-
公式 =RIGHT(A2,4)
- 公式 =MID(A2,3,2)
第二个参数表示从第几位开始,第三个参数表示,总共几位!
- 结合VLOOKUP函数一起使用
- 公式 =VLOOKUP(LEFT(B2,6)*1,地区码!A:B,2,0)
FIND函数、LEN函数
-
FIND()用来找某个字符的位置,返回一个数值
-
参数:FIND(find _text, within_text, [start _num])
- 和截取函数组合使用,比如例中,需要提取用户名,所以我们得先找到@符号在第几位,然后位数-1,就是呀截取的用户名!
公式:=LEFT(A2,FIND(“@”,A2)-1)
- LEN函数返字符串长度
- 嵌套,提取出域名
公式:=RIGHT(A2,LEN(A2)-FIND(“@”,A2))
总位数-“@”符号所在位置,就是right函数需要截取的位!
- 其他,提取域名方法,利用MID函数
公式 =MID(A2,FIND(“@”,A2)+1,1000)
找到@所在位数,开始取后面位数即可,1000是任意写的一个大范围,域名肯定没那么长,所以能一次性取到后面所有的值!
SUBSTITUTE函数
-
substitute 就是函数版的替换工具,属于文本型函数,输出的内容也是文本!
-
参数&公式 =SUBSTITUTE(A2,“.”,“-”)*1
再转化下格式,即可
- 实战题:将手机号码中间4位的内容,替换成*号
公式 =SUBSTITUTE(D2,MID(D2,4,4),“****”)
ASC函数、WIDECHAR函数
-
功能:ASC全角转半角,widechar半角转全角
-
ASC()
- widechar() 函数
文本处理函数
TEXT函数
-
TEXT 函数 用于将数值转换为特定格式的文本,从而灵活控制数据显示形式!
=TEXT(value, “format_code”)
value:需要格式化的数值、日期或时间。
format_code:用双引号包裹的格式代码,定义输出文本的样式。 -
注意事项:
TEXT 函数输出为文本类型,无法直接参与数值计算。
格式代码需严格遵循规则,错误代码会返回 #VALUE!。
日期/时间需确保原始数据是 Excel 认可的序列值。
可通过灵活组合格式代码,TEXT 函数可以满足复杂的数据显示需求 -
如何自动生成金额大写?
- 公式 :
=“人民币:”&TEXT(B11,“[DBNum2][$-zh-CN]G/通用格式”)
右键金额,找到数字大写的格式,自定义,复制到格式,用text函数来处理!
- 将日期格式化:将日期序列值转换为易读的文本格式
=TEXT(TODAY(), “yyyy-mm-dd”) → 返回当前日期,如 “2023-10-05”
=TEXT(A1, “dddd, mmmm dd, yyyy”) → 若A1为2023/10/5,返回 "Thursday, October 05, 2
- 数字格式化为货币:添加货币符号和千位分隔符
=TEXT(1234.5, “$#,##0.00”) → 返回 “$1,234.50”
=TEXT(500, “¥#,##0”) → 返回 “¥500”
- 百分比显示:将小数转换为百分比形式
=TEXT(0.25, “0.00%”) → 返回 “25.00%”
=TEXT(0.75, “0%”) → 返回 “75%”
- 自定义数字格式:控制小数位数或填充字符
=TEXT(123, “00000”) → 返回 “00123”(固定5位,不足补零)
=TEXT(3.1415, “0.00”) → 返回 “3.14”(保留两位小数)
- 时间格式化:转换时间序列值为文本
=TEXT(NOW(), “hh:mm:ss AM/PM”) → 返回当前时间,如 “03:45:30 PM”
=TEXT(0.75, “h:mm”) → 返回 “18:00”(0.75天=18小时)
- 条件性文本显示:用格式代码实现简单条件
=TEXT(A1, “[>100]超额;不足”) → 若A1=150,返回 “超额”;若A1=80,返回 “不足”
- 电话号码分段显示:格式化数字为电话号码
=TEXT(13912345678, “000-0000-0000”) → 返回 “139-1234-5678”
- 结合文本拼接:将格式化结果与其他文本组合
=TEXT(B2, “¥#,##0.00”) & " 元整" → 若B2=2500,返回 “¥2,500.00 元整”
- 常用格式代码说明:
TEXTJOIN函数
-
功能:链接文字作用
-
公式 =TEXTJOIN(“-”,TRUE,A2:D2) 忽略空白,大多数情况下都是数据是一样的
- 不忽略空白,面对数据不一样的情况下!也方便以后对数据进行处理,比如说可以进行,按照分隔符分列!
再对数据进行“分列”还原一下!
日期函数
DATE函数
- 如何提取身份证中的生日信息?用到DATE函数!
- 公式 =DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))
- DATE()函数,输入值总能返回一个正确的日期!
YEAR、MONTH、DAY,EDATE函数
- 实际应用:计算结款日期
-
YEAR、MONTH、DAY都是取一个日期中的,年月日
-
公式=DATE(YEAR(A2),MONTH(A2)+B2,DAY(A2)-1)
- EDATE()函数
公式 =EDATE(A2,B2)
Datedif函数
- 用Datedif函数计算工龄,datedif函数可以计算时间间隔!
- 公式 =DATEDIF(A2,B2,“Y”)
参数1为开始日期;参数2为结束日期;参数3是要计算的间隔值,“Y”是年,“M”是月,“D”是天…
Weeknum、Weekday函数
- 如何计算当前日期,是第几周?
- weeknum()函数
公式 =WEEKNUM(A3,2) 参数1是日期,参数2是选择一周开始天是什么!
- weekday()函数
公式 =WEEKDAY(A3,2)
- 实操题:将周末标记为红色
- 利用条件格式,加上weekday()函数来判断
ALT H L H M 打开新建条件格式,使用公式确定要设置格式的单元格,输入:weeekday(B1,2)>5
然后双击格式刷,将后面的日期统一一下格式即可!
- 这样再修改开始日期后,依旧可以利用已经设置好的格式,标记出新日期的周末~~