有如下工作(例子):EXCEL中有单元格M2,可能出现a、b、c、ab、bc、ac、abc、不存在a或b或c这这八种情况,现在在M3中要显示出这八种情况的可能存在的一种,使用公式如下,为能够容易看出层次,我如下方式写:
if( iserror(search(m2,"a"))
,
if(iserror(search(m2,"b"))
,
if(iserror(search(m2,"c")),"nothing",c")
,
if(iserror(search(m2,"c")),"b","b&c")
)
,
if(iserror(search(m2,"b"))
,
if(iserror(search(m2,"c")),"a","a&c")
,
if(iserror(search(m2,"c")),"a&b","a&b&c")
)
)
答案如下
'判断是否存在单个字串
Function LikeChar(text, char)
flag = Len(text) - Len(Replace(text, char, ""))
If flag = 0 Then
LikeChar = True
Else
LikeChar = False
End If
End Function
'输出八种情况中的一种
Function LikeChar2(str, str1, str2, str3)
If LikeChar(str, str1) Then
If LikeChar(str, str2) Then
If LikeChar(str, str3) Then
LikeChar2 = "无手续"
Else
LikeChar2 = str3
End If
Else
If LikeChar(str, str3) Then
LikeChar2 = str2
Else
LikeChar2 = str2 + "&" + str3
End If
End If
Else
If LikeChar(str, str2) Then
If LikeChar(str, str3) Then
LikeChar2 = str1
Else
LikeChar2 = str1 + "&" + str3
End If
Else
If LikeChar(str, str3) Then
LikeChar2 = str1 + "&" + str2
Else
LikeChar2 = str1 + "&" + str2 + "&" + str3
End If
End If
End If
End Function
|