excel中的分层和随机化

Question

我正在开发一个优秀的医学风险工具，我正在尝试按医学风险水平对我的数据进行分层，并按风险水平对城市进行随机化。我希望在根据风险水平选择城市时，随机化是成比例的。

Total.City.Region Risk Score    Total.City.Regionl.Risk.Desc    City.Scaled
4.43                                  1. Extremely Low Risk        0.129
443.32                                2. Very Low Risk             0.164
44.33                                 1. Extremely Low Risk        0.132
110.83                                2. Very Low Risk             0.137
221.66                                2. Very Low Risk             0.146
288.16                                2. Very Low Risk             0.151
376.82                                2. Very Low Risk             0.158
4.43                                  1. Extremely Low Risk        0.129
44.33                                 1. Extremely Low Risk        0.132
110.83                                2. Very Low Risk             0.137

这是我数据的一部分。因此，每个城市都有总的城市风险得分，风险级别基于风险得分。然后我对分数进行了缩放以使其正常化。现在我想创建一个公式，根据风险级别对我的数据进行分层。(极低风险，非常低风险和低风险)，然后从每个级别(首先是极低风险，然后是非常低风险，然后是低风险)中随机选择城市(福克斯共5个城市)，但我希望这是成比例的。

关于如何做到这一点的任何帮助或建议都将是非常有帮助的

非常感谢你提前这么做。

Answer 1

如果我了解您的需求，并且您使用的是Office365，则可以使用SWITCH语句评估City.Scaled列并返回文本风险说明。下面是一个可以参考的示例：

=SWITCH(C2,IF(VALUE(C2) < 0.135,C2,""),"ExLow","low")

0.129   ExLow
0.164   low
0.132   ExLow
0.137   low
0.146   low
0.151   low
0.158   low
0.129   ExLow
0.132   ExLow
0.137   low

上面的switch公式中的第一项包含一个要匹配的表达式，接下来是一个比较值，如果匹配，则返回一个值，然后是一个默认值。您可以重复第二个和第三个匹配/返回对，最多可达126个。

Answer 2

我在测试数据上尝试了这个方法，它似乎起作用了。出于本例的目的，我们假设Total City Risk Score在A列，Risk Description在B列，Scaled Score在C列：

1. Sort on column B
2. 从D2开始，我们将执行以下公式：=IF(B2<>B3,ROW(B2),"")。把这个拖到底部。本质上，我们试图找到标记每个Risk Descriptions.
3. Starting G2块末尾的行，只需从1,2....until中列出您拥有的类别数量。我在示例数据中只看到了3个，所以1,2,3可能就是您在H1中所需要的了，让我们创建一个名为“

”的头文件。这将记录风险描述块的开始位置。我们稍后会回到这里，在I1中，

1. 创建一个叫做"End“的头部。这将记录风险描述块的结束位置。在I2中，我们将使用以下公式：=LARGE(D:D,3-G2+1)。同样，3表示风险描述的数量。向下拖动到我们正在制作的迷你桌子的末端。
2. 跳回到H2。设置H2等于1，H3应为：=I2-1。
3. 在J1中，创建一个名为"Size“的标题，以记录每个风险描述块中的条目数量。在J2中，使用公式：=I2-H2+1 (结束行-开始行+ 1)。把这个拖下来。
4. 快要完成了！现在在K1中，创建一个名为"Random“的头文件。在K2中放入以下公式：=H2+RANDBETWEEN(0,J2-1)。向下拖动。本质上，我们希望创建一个比块大小小0到1之间的随机数。然后我们将其添加到块的第一行。这在每个Risk Description.
5. In L1的风险描述块中随机生成一行，创建一个名为"Value“的标题。在L2中，使用公式：=INDIRECT("C"&K2)。向下拖动。这将返回所选随机行的缩放分数。如果需要其他数据，请将C更改为A或B。

现在在列L中，每个块都有一个随机条目。如果你想要每个5个，你可以刷新5次。

注意:这不是对选择两次的单个条目的控制。