excel中能不能让满足5个条件的数跳出来?
发布网友
发布时间：2024-01-07 06:53
共2个回答
热心网友
时间：2024-01-17 02:59
这个题用VBA。ALT+F11打开VBA编辑器，插入模块，将以下代码粘贴，F5运行。
Sub test()
Dim i, j As Integer
Dim k, l, m, n, str, rhm1, rhm2, rhm3 As String
Sheet1.Activate
n = Cells(13, 2).Value
rhm1 = Right(1 * Left(n, 1) + 1 * Right(n, 1), 1)
rhm2 = Right(1 * Left(n, 1) + 1 * Mid(n, 2, 1), 1)
rhm3 = Right(1 * Mid(n, 2, 1) + 1 * Right(n, 1), 1)
str = rhm1 & rhm2 & rhm3
l = Right(1 * Left(n, 1) + 1 * Right(n, 1) + 1 * Mid(n, 2, 1), 1)
j = 14
For i = 12 To 1 Step -1
m = Cells(i, 2).Value
k = Right(1 * Left(m, 1) + 1 * Right(m, 1) + 1 * Mid(m, 2, 1), 1)
If Right(m, 1) <> Left(m, 1) And Right(m, 1) <> Mid(m, 2, 1) And Left(m, 1) <> Mid(m, 2, 1) Then
If InStr(m, Right(n, 1)) + InStr(m, Left(n, 1)) + InStr(m, Mid(n, 2, 1)) > 0 And InStr(m, rhm1) + InStr(m, rhm2) + InStr(m, rhm3) > 0 Then
If rhm2 <> Right(1 * Left(m, 1) + 1 * Right(m, 1), 1) And rhm2 <> Right(1 * Left(m, 1) + 1 * Mid(m, 2, 1), 1) And rhm2 <> Right(1 * Mid(m, 2, 1) + 1 * Right(m, 1), 1) Then
If l <> Right(1 * Left(m, 1) + 1 * Right(m, 1), 1) And l <> Right(1 * Left(m, 1) + 1 * Mid(m, 2, 1), 1) And l <> Right(1 * Mid(m, 2, 1) + 1 * Right(m, 1), 1) And k <> l Then
j = j - 1
Cells(j, 3) = m
End If
End If
End If
End If
Next
End Sub
追问兄弟。辛苦了。我试了一下挺好的。就是发现一种情况好象不对，如：B13中输入336或363，在其他的单元格中例如B12输入623，运行宏。C列出现了623，而623含有B13中的两个数不符合条件1。麻烦你再看一下。追答将程序中的
If InStr(m, Right(n, 1)) + InStr(m, Left(n, 1)) + InStr(m, Mid(n, 2, 1)) > 0 And InStr(m, rhm1) + InStr(m, rhm2) + InStr(m, rhm3) > 0 Then
改为
If 1 * (InStr(n, Right(m, 1)) > 0) + 1 * (InStr(n, Left(m, 1)) > 0) + 1 * (InStr(n, Mid(m, 2, 1)) > 0) = -1 And 1 * (InStr(str, Right(m, 1)) > 0) + 1 * (InStr(str, Left(m, 1)) > 0) + 1 * (InStr(str, Mid(m, 2, 1)) > 0) = -1 Then
热心网友
时间：2024-01-17 02:59
追答扣号1722187970
