使用vba来求解。假设你按如下位置输入各参数,则D列会列出结果:
代码:
Sub 对固定值按指定区间拆分指定个数()
Dim sGoal As Single, sMin As Single, sMax As Single, sResult As Single
Dim sMax1 As Single, sMin1 As Single
Dim sRnd As Single, sMean As Single
Dim iCount As Integer, i As Integer, j As Integer
Dim arr() As Single
Dim boo As Boolean
sGoal = ActiveSheet.Range("B1").Value
sMin = ActiveSheet.Range("B2").Value
sMax = ActiveSheet.Range("B3").Value
iCount = ActiveSheet.Range("B4").Value
If sMin = 0 Or sMax = 0 Then '最大值最小值任一为0或空,都视为不设区间
sMin = 0
sMax = sGoal
Else
If sMin = sMax Then
MsgBox "最大最小值不能相等!"
Exit Sub
End If
If sMin > sMax Then '使smax大于smin
sResult = sMin
sMin = sMax
sMax = sResult
End If
End If
If iCount < 1 Then
MsgBox "拆分个数不合理!"
Exit Sub
End If
If sMin * iCount > sGoal Or sMax * iCount < sGoal Then
MsgBox "最小值or最大值设置不合理!"
Exit Sub
End If
Range("D1").EntireColumn.ClearContents
sMean = sGoal / iCount
sMax1 = sMax
sMin1 = sMin
For i = 1 To iCount
ReDim Preserve arr(1 To i)
Randomize i
sRnd = Rnd
sResult = (sMax1 - sMin1) * sRnd + sMin1 '每一步结果
arr(i) = sResult
If sResult > sMean Then
sMax1 = sResult
sMin1 = sMin
Else
sMin1 = sResult
sMax1 = sMax
End If
Next i
For i = LBound(arr) To UBound(arr)
Range("D" & i).Value = arr(i) ' * sGoal
Next i
'最后修正数字,使得总计正好等于固定值
Do
boo = True
sResult = Application.WorksheetFunction.Sum(Range("D1:D" & iCount))
sMean = (sResult - sGoal) / iCount
If Abs(sMean) < 0.000001 Then
Exit Do
End If
For i = 1 To iCount
If Range("D" & i) - sMean < sMin Or Range("D" & i) - sMean > sMax Then
boo = False
Else
Range("D" & i) = Range("D" & i) - sMean
End If
Next i
Loop While Not boo
MsgBox "over"
End Sub
具体怎么操作 ,小白表示不懂 最好能给个公式什么的