Question

countif和if函数配合使用

帮我看看这个公式有什么问题？第一列是地址，我想在第三列求出所在省份，用的函数是：=IF(COUNTIF(A330,"省"),MID(A330,1,FIND("省",A330)),IF(COUNTIF(A330,"内蒙古"),"内蒙古自治区",IF(COUNTIF(A330,"宁夏"),"宁夏自治区",IF(COUNTIF(A330,"北京"),"北京市",IF(COUNTIF(A330,"重庆"),"重庆市")))))，可是返回的值不对，不知道哪儿有问题，请帮帮忙

Answer 1

不能这么用，COUNTIF(A330,"省")，这是错误原因。
=IF(ISNUMBER(FIND("省",A330)),MID(A330,1,FIND("省",A330)),IF(ISNUMBER(FIND("内蒙古",A330)),"内蒙古自治区",IF(ISNUMBER(FIND("宁夏",A330)),"宁夏自治区",IF(ISNUMBER(FIND("北京市",A330)),"北京市",IF(ISNUMBER(FIND("重庆市",A330)),"重庆市")))))

Answer 2

看看是不是你要的：
=IF(COUNTIF(A1,"*省*"),MID(A1,1,FIND("省",A1)),IF(COUNTIF(A1,"*内蒙古*"),"内蒙古自治区",IF(COUNTIF(A1,"*宁夏*"),"宁夏自治区",IF(COUNTIF(A1,"*北京*"),"北京市",IF(COUNTIF(A1,"*重庆*"),"重庆市")))))