常见问题如下:
如下:
=IF(FIND("3030",C3),1,3)
C3输入3030,返回1正常;输入其他值,返回#value!,不能返回3;
因为find公式中,第一个是要查的内容,第二个是被查的内容,比如你C3是223030,就会返回1,但如果找不到,find公式并不是返回0,而是返回#value
解决方法:
=IF(ISNUMBER(FIND("3030",C3)),1,3)
使用例:
=IF(ISNUMBER(FIND("o_gold",B2)),SUBSTITUTE(B2,"o_gold","gold_db.t"),IF(ISNUMBER(FIND("o_zt",B2)),SUBSTITUTE(B2,"o_zt","zt_db.t"),IF(ISNUMBER(FIND("o_privilege",B2)),SUBSTITUTE(B2,"o_privilege","privilege_db.t"),IF(ISNUMBER(FIND("o_arm_",B2)),SUBSTITUTE(B2,"o_arm_","arm_db."),IF(ISNUMBER(FIND("o_xf_trade",B2)),SUBSTITUTE(B2,"o_xf_trade","xf_trade_db.t"),IF(ISNUMBER(FIND("o_hms_",B2)),SUBSTITUTE(B2,"o_hms_","hms_db."),IF(ISNUMBER(FIND("o_common",B2)),SUBSTITUTE(B2,"o_common","common_db.t"),IF(ISNUMBER(FIND("o_loupan",B2)),SUBSTITUTE(B2,"o_loupan","loupan_db.t"),IF(ISNUMBER(FIND("o_xf",B2)),SUBSTITUTE(B2,"o_xf","xf_db.t"),IF(ISNUMBER(FIND("manage",B2)),SUBSTITUTE(B2,"manage","manage_db."),IF(ISNUMBER(FIND("o_event",B2)),SUBSTITUTE(B2,"o_event","event_db.t"),IF(ISNUMBER(FIND("o_hf_agent",B2)),SUBSTITUTE(B2,"o_hf_agent","hf_agent_db.t"),IF(ISNUMBER(FIND("o_crm",B2)),SUBSTITUTE(B2,"o_crm","crm_db.t"),IF(ISNUMBER(FIND("o_master_",B2)),SUBSTITUTE(B2,"o_master_","master_db."),IF(ISNUMBER(FIND("o_",B2)),SUBSTITUTE(B2,"o_","datahouse_db."),B2)))))))))))))))
含义是: 判断某字符串是否包含指定字符串,如果用其它字符串替换指定的字符串,如果不包含第一个指定,继续往下判断其它指定字符串,直到最后都不匹配,返回原字符串本身。