如何用excel平均分配客户,求思路!
3000多各客户,已经分了700个组,每组人数不等,想用公式分配给甲乙丙丁四个人,要求:
1、每组客户尽可能平均分给不同的人;
2、分完后每人客户数量差不多.
求思路?谢谢
自己动手,丰衣足食...
请指正!核心算法如下:
c = CInt(b / n + 10)
ReDim arr(1 To n, 1 To c)
For a = 2 To b
x = Sheet1.Cells(a, 13)
x_min = UBound(arr, 2)
i_min = UBound(arr, 2)
tag_i = 0
tag_j = 0
For i = 1 To n
x_num = 0
i_num = 0
For j = 1 To UBound(arr, 2)
If arr(i, j) = x Then
x_num = x_num + 1
End If
If arr(i, j) "" Then
i_num = i_num + 1
End If
Next j
If x_num > 0 Then
If x_num < x_min Then
x_min = x_num
tag_i = i
tag_j = i_num + 1
End If
Else
If i_num < i_min Then
x_min = 0
i_min = i_num
tag_i = i
tag_j = i_num + 1
End If
End If
Next i
arr(tag_i, tag_j) = x
Sheet1.Cells(a, 14) = "顾问" & tag_i
Next a
作业帮用户2017-09-24举报