excel 随机数 生成 EXCEL 随机数

excel 随机数 生成   EXCEL 随机数

 

excel 随机数 生成   EXCEL 随机数

 

========B列-呼叫总计-比如有244497个=======================

Sub GenerateRandomIntegers()
Dim targetSum As Long
targetSum = 244497

Dim rangeSize As Integer
rangeSize = 274 ' G2:G2174 共274行

Dim randomValues() As Long
ReDim randomValues(1 To rangeSize)

Dim i As Integer
Dim currentSum As Long
Dim adjustment As Long

' 生成随机整数
For i = 1 To rangeSize
randomValues(i) = Int((1000 - 1 + 1) * Rnd + 1)
currentSum = currentSum + randomValues(i)
Next i

adjustment = targetSum - currentSum

' 对超出目标总和的数进行调整
Do While adjustment <> 0
Dim randomIndex As Integer
randomIndex = Int(Rnd() * rangeSize) + 1

If adjustment < 0 And randomValues(randomIndex) > 1 Then
randomValues(randomIndex) = randomValues(randomIndex) - 1
adjustment = adjustment + 1
ElseIf adjustment > 0 Then
randomValues(randomIndex) = randomValues(randomIndex) + 1
adjustment = adjustment - 1
End If
Loop

' 将随机整数分配给范围内的单元格
Range("B2:B" & rangeSize + 1).Value = WorksheetFunction.Transpose(randomValues)

' 检查总和
Dim total As Long
total = Application.WorksheetFunction.Sum(Range("B2:B" & rangeSize + 1))
Debug.Print "Total: " & total
End Sub

========D列-应答总计-比如有30059个=======================

Sub GenerateRandomIntegers()
Dim targetSum As Long
targetSum = 30059

Dim rangeSize As Integer
rangeSize = 274 ' G2:G2174 共274行

Dim randomValues() As Long
ReDim randomValues(1 To rangeSize)

Dim i As Integer
Dim currentSum As Long
Dim adjustment As Long

' 生成随机整数
For i = 1 To rangeSize
randomValues(i) = Int((120 - 1 + 1) * Rnd + 1)
currentSum = currentSum + randomValues(i)
Next i

adjustment = targetSum - currentSum

' 对超出目标总和的数进行调整
Do While adjustment <> 0
Dim randomIndex As Integer
randomIndex = Int(Rnd() * rangeSize) + 1

If adjustment < 0 And randomValues(randomIndex) > 1 Then
randomValues(randomIndex) = randomValues(randomIndex) - 1
adjustment = adjustment + 1
ElseIf adjustment > 0 Then
randomValues(randomIndex) = randomValues(randomIndex) + 1
adjustment = adjustment - 1
End If
Loop

' 将随机整数分配给范围内的单元格
Range("D2:D" & rangeSize + 1).Value = WorksheetFunction.Transpose(randomValues)

' 检查总和
Dim total As Long
total = Application.WorksheetFunction.Sum(Range("D2:D" & rangeSize + 1))
Debug.Print "Total: " & total
End Sub

========C列-接通总计-比如有180500个================================

Sub GenerateRandomIntegers()
Dim targetSum As Long
targetSum = 180500

Dim rangeSize As Integer
rangeSize = 274 ' G2:G2174 共274行

Dim randomValues() As Long
ReDim randomValues(1 To rangeSize)

Dim i As Integer
Dim currentSum As Long
Dim adjustment As Long

' 生成随机整数
For i = 1 To rangeSize
randomValues(i) = Int((700 - 1 + 1) * Rnd + 1)
currentSum = currentSum + randomValues(i)
Next i

adjustment = targetSum - currentSum

' 对超出目标总和的数进行调整
Do While adjustment <> 0
Dim randomIndex As Integer
randomIndex = Int(Rnd() * rangeSize) + 1

If adjustment < 0 And randomValues(randomIndex) > 1 And randomValues(randomIndex) < Range("B" & randomIndex + 1).Value Then
randomValues(randomIndex) = randomValues(randomIndex) - 1
adjustment = adjustment + 1
ElseIf adjustment > 0 Then
randomValues(randomIndex) = randomValues(randomIndex) + 1
adjustment = adjustment - 1
End If
Loop

' 将随机整数分配给范围内的单元格
Range("C2:C" & rangeSize + 1).Value = WorksheetFunction.Transpose(randomValues)

' 检查总和
Dim total As Long
total = Application.WorksheetFunction.Sum(Range("C2:C" & rangeSize + 1))
Debug.Print "Total: " & total
End Sub