excel逻辑函数使用(Excel中IS类判断信息函数在工作中的应用)
在Excel 2016中提供了14个IS函数,主要是用于判断数据类型、错误值、奇偶、空白单元格等 。工作中常见的IS函数分类如下:
常见的IS类函数
使用IS函数屏蔽错误值如下图,在B13单元格中,使用VLOOKUP函数利用A3单元格的姓名查询部门名称。因为在A13单元格中输入的姓名中间加入了空格,不匹配,从而无法查到对应信息而返回错误错 #N/A的错误值。
错误值
可使用下列公式将错误给屏蔽掉!即始果返回的值为错误值,则显示为空白单元格。
方法一:使用ISERROR函数
在B13单元格中输入下列公式:
= IF(ISERROR(VLOOKUP($A13,$A$2:$C$9,2,FALSE)),"",VLOOKUP($A13,$A$2:$C$9,2,FALSE))
公式解析如下:
即=IF(ISERROR(原公式,"",原公式)
先使用ISERROR判断公式VLOOKUP($A13,$A$2:$C$9,2,FALSE)返回的查找结果,如果结果为错误值,则ISERROR就会返回逻辑值TRUE。其在公式中作为IF的判断条件,判断条件为TRUE,则显示为空值,否则就正常显示VLOOKUP公式返回的结果值。
ISERROR函数用法
方法二:使用ISNA函数
在B13单元格中输入下列公式:
=IF(ISNA(VLOOKUP($A13,$A$2:$C$9,2,FALSE)),"",VLOOKUP($A13,$A$2:$C$9,2,FALSE))
ISNA函数是专门用来判断“#N/A”类的错误值。一般与VLOOKUP和MATCH函数配合使用。比起ISERROR函数来说,可以提高计算速度。
方法三:使用IFERROR或IFNA函数
在office 2007及以后的版本里,新增加了IFERROR和IFNA函数,进行错误值屏蔽时,巧妙地使用此函数,可以大大地减少公式的复杂度,使用方法如下:
IFERROR(原公式,"")
IFNA(原公式,"")
使用此函数,公式可简化为:
=IFERROR(VLOOKUP($A13,$A$2:$C$8,2,FALSE),"")
=IFNA(VLOOKUP($A13,$A$2:$C$9,2,FALSE),"")
在Excel中输入全数字身份证号码时,因为Excel最大精确的位数为15位,所以当输入18位数字时会自动变为科学计数显示,即使再改变格式为文本,后3位也将变成000,且无法逆转。因此,必须先设置单元格格式为“文本”类型,以文本存储18位身份证号码。
另外,现在18位身份证号码,有的是18位数字,有的是17位数字加一位检验码"X"。为了防止在录入时出错,可以先设置单元格的数据有效性进行限制,操作如下:
步骤01:如图,选择需要输入身份证号码的区域D2:D9。
步骤02:选择“数据”选项卡,在“数据工具”分组中,找到“数据验证”下拉菜单,单击“数据验证”。在“数据验证”对话框“设置”选项卡下,设置“验证条件”,在“允许”下拉菜单中选择“自定义”,在“公式”编辑框中输入如下公式:
=AND(ISTEXT(D2),LEN(D2)=18,ISNUMBER(--LEFT(D2,17)),OR(ISNUMBER(--RIGHT(D2,1)),EXACT(RIGHT(D2,1,"X")))
为了便于大家理解,下面将各段公式作以说明:
ISTEXT(D2):判断D2单元格是否为文本,
且
LEN(D2)=18:判断D2单元格字符个数是否为18个,
且
ISNUMBER(--LEFT(D2,17)):判断D2单元格前17位是否为数值,
且
OR(ISNUMBER(--RIGHT(D2,1)),EXACT(RIGHT(D2,1),"X")):判断D2单元格第18位是否为数值或者是字符"X"。
以上条件都满足,将返回TRUE,允许输入内容,否则将弹出错误。
注意:
--LEFT(D2,17)和--RIGHT(D2,1)前面的两个减号的目的是进行减负运算,将字符串转换为数值,如果是非数字将返回" #VALUE! "错误值。
数据验证对话框
步骤03:单击“确定”按钮后,进行验证。
使用IS函数根据身份证号码判断性别Excel中的ISEVEN和ISODD可以判断数字为偶数还是奇数。身份证号码中的第17位可以判断性别,如果为奇数则为男性,偶数为女性。
使用如下公式即可判断:
=IF(ISEVEN(MID(D2,17,1),"女","男")
或者:
=IF(ISODD(MID(D2,17,1)),"男","女")
得到结果如下:
判断男女
也可以使用MOD函数,根据能否被2整除,可以判断是否为偶数的原理进行性别判断。公式如下:
=IF(MOD(MID(D2,17,1),2)=0,"女","男")
免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com