问题描述

最近在工作中需要用到圆桌随机来确定掉落的一个物品，里面有涉及到如何根据随机出的一个数值来确定这个数值所在的区间段。具体问题描述如下：

有5个物品A、B、C、D、E、无，它们的权重（为简单起见，设定总权重为10000）如下表所示：

现从1-10000中随机出一个权重值X，来判断X对应的物品是什么。

问题分析

现就以表中给出的顺序为物品排列顺序（事实上，不管A在第一个位置还是最后一个位置，只要权重一样，随机是均匀随机，结果最后与位置无关），列出各个物品的权重区间，如下：

如果随机出的权重值X=300，那么结果就为A；如果X=301，那么为B。

考虑到VLOOKUP、LOOKUP这类函数的模糊查找特性，构造一个数组存放每个物品的权重区间最小值，对该范围进行模糊查找获得最终的物品

Excel解决

• 添加辅助列获得权重区间最小值（D列即为辅助列）

• 利用LOOKUP函数得到结果
  • 公式：=LOOKUP(X,D2:D7,A2:A7)

• 利用VLOOKUP函数得到结果

• 公式：=VLOOKUP(X,CHOOSE({1,2},D2:D7,A2:A7),2,TRUE)
• 公式：=VLOOKUP(X,IF({0,1},A2:A7,D2:D7),2,TRUE)

VBA解决

思路同上，只不过直接编写代码获得结果，辅助列直接在代码内构建数组完成，代码如下

1 Option Explicit
 2 Option Base 1
 3 
 4 Sub FindPosition()
 5 Dim resultC As String, dataC As String '结果列resultC、数据列dataC
 6 Dim result() As String '保存结果的序列组result()
 7 Dim data() As Integer '保存每个结果的权重数据数组data()
 8 Dim dataT() As Integer '转换后的权重数据组dataT()
 9 Dim src As Integer '输入的数据
10 Dim dst As String '输出的结果
11 Dim n As Integer '结果、数据的个数n
12 Dim sum As Integer '总权重sum
13 
14 resultC = Range("F1").Value
15 dataC = Range("F2").Value
16 src = Range("F3").Value
17 n = Application.WorksheetFunction.Count(Range("B:B"))
18 sum = 0
19 
20 ReDim result(1 To n) As String
21 ReDim data(1 To n) As Integer
22 ReDim dataT(1 To n) As Integer
23 dataT(1) = 1
24 
25 Dim i As Integer
26 For i = 1 To n Step 1
27 result(i) = Range(resultC & i + 1)
28 data(i) = Range(dataC & i + 1)
29 sum = sum + data(i)
30 Next
31 
32 For i = 2 To n Step 1
33 dataT(i) = dataT(i - 1) + data(i - 1)
34 Next
35 
36 If src > sum Then
37 MsgBox "输入数据超出总权重范围"
38 End If
39 
40 dst = Application.WorksheetFunction.Lookup(src, dataT, result)
41 Range("F4") = dst
42 End Sub

输出结果如下