如何使用Excel在同一行中查找并随机替换两个多个值的比率？

Question

根据我对您的问题的理解，请尝试此代码并确认它是否适合您。

在此示例中，样本数据位于 B1:F23。仅在第 3 列中检查 4。

按Alt+F11访问 VBA 编辑器。从插入菜单中插入一个模块。双击左窗格中的模块名称以访问其代码编辑器。现在将以下宏代码放入其中。

Sub Replace430()

Dim MyRange As Range
Dim RowCount As Long
Dim ColCount As Integer
Dim MyArray() As Variant

Dim i, j, k, percent30 As Long
Dim Count4 As Long
Const Replaced = 0   'Set Replaced Value
Const found = 18     'Set Find Value
Const Mycol = 3      'Correctly set the Column Number of Column in Range where 4 is to be checked
Set MyRange = Range("B2:F23")

RowCount = MyRange.Rows.Count
ColCount = MyRange.Columns.Count
'Get number of 4's againts 18
For i = 1 To RowCount
    If MyRange.Columns(1).Cells(i) = found Then
        For j = Mycol To Mycol
            If MyRange.Columns(j).Cells(i) = 4 Then
               Count4 = Count4 + 1
            End If
        Next j
    End If

Next i

ReDim MyArray(Count4 - 1, 2)
k = 0
For i = 1 To RowCount
    If MyRange.Columns(1).Cells(i) = found Then
        For j = Mycol To Mycol
            If MyRange.Columns(j).Cells(i) = 4 Then
               MyArray(k, 1) = i
               MyArray(k, 2) = j
               k = k + 1
            End If
        Next j
    End If

Next i

percent30 = 0.3 * Count4

Dim shufflearray()
ReDim shufflearray(Count4 - 1)
For i = 0 To Count4 - 1
    shufflearray(i) = i
Next i

'Shuffle the shufflearray() below


    Dim N As Long
    Dim Temp As Variant


    Randomize
    For N = LBound(shufflearray) To UBound(shufflearray)
        j = CLng(((UBound(shufflearray) - N) * Rnd) + N)

        If N <> j Then
            Temp = shufflearray(N)
            shufflearray(N) = shufflearray(j)
            shufflearray(j) = Temp
        End If
    Next N

'Use randomised values from shufflearray as array subscript to replace only 30% of 4's
For i = 0 To percent30 - 1
    MyRange.Columns(MyArray(shufflearray(i), 2)).Cells(MyArray(shufflearray(i), 1)).Value = Replaced
Next i


End Sub

在语句中正确设置范围在语句Set MyRange = Range("B2:F23") 中正确设置列号Const Mycol = 3

将文件保存为启用宏的工作簿，然后从工作表中按 ALT + F8 访问“宏运行”对话框。运行此宏 Replace430()。

进一步测试并确认。根据您的评论，这假设 4 仅出现在所选范围的第 3 列。其他任何地方的 4 暂时被忽略。

Answer 1