☰

Excel 中使用条件语句进行随机抽样？

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 中使用条件语句进行随机抽样？

我有一份姓名列表，每个姓名都有相关数据（每个姓名列出了多个项目）。我试图随机选择每个人要审核的数据。目前，电子表格将随机选择数据；但是，当我按姓名排序时，我发现只有一半的人有要审核的项目，而另一半没有。

是否可以在 Excel 中随机选择每个人名下的 x% 项目？

下面是一个非常小的样本量作为说明——实际电子表格有 100 个名称和数千行数据。

我需要一个公式来为每个“名称”随机选择给定百分比（例如 5%）的“数据点”。

（A 列） （B 列）
名称数据点
苏123
苏456
苏789
布赖恩586
布赖恩 566
布赖恩 949
布赖恩 928
鲍勃643
鲍勃235
鲍勃594

答案1

使用公式应该可以实现=rand()。在每一行中添加一个随机数。然后将随机数复制并粘贴为自身的值，这样它就会永久保留其值。然后您可以按随机数对其进行排序，将其保留为前 X 个值，并将其余值删除。然后按原始顺序将其恢复。

为了给您更精确的指导，我需要更好的解释或您的表格的屏幕截图。

答案2

扩展（但分支）MartinX 的回答：

我假设（因为您没有说其他的）A和 B是唯一有数据的列。如果不是这样，请将下面说明中的C和 替换D为可用的两列的名称。

  • 输入=RAND()单元格 C2 （我假设只有一个标题行，并且数据从第 2 行开始，如图所示）。
  • =C2>=LARGE(C$2:C$100*(A$2:A$100=A2), CEILING(0.5*COUNTIF(A$2:A$100,A2),1))在单元格中 输入D2，100用数字替换≥包含数据的最后一行的数字，并0.5用您的X% 值（即X ÷100；因此，对于 5%，使用 0.05），然后按Ctrl+ Shift+ Enter。 
  • 选择单元格 C2:D2并向下拖动/填充以用数据覆盖所有行。

列 D现在将填充TRUE和FALSE值。  X每个名称的行数的 %（向上舍入）将为TRUE。然后您可以直接使用此列表，或将其用作条件格式的基础，或任何您想要的。

笔记：

  • RAND()是一个易失性函数。每次更改任何内容时，所有值RAND()都将被重新评估/重新计算/更改。您可以通过关闭自动工作簿计算来防止这种情况发生。按照 MartinX 的建议，这样做可能更简单：复制并粘贴值。
  • 好的，所以列中 C填充了 0.000000 到 1.000000 之间的随机数。

  • Column 中的公式 D显然更复杂。我将反向解释它：从内向外，从右到左。

    • =COUNTIF(A$2:A$100,A2)计算与当前行同名的行数。因此，对于第 2、3 和 4 行（“Sue”），此值为 3。对于第 5、6、7 和 8 行（“Brian”），此值为 4。与往常一样，我们在 中使用美元符号 ( $)A$2:A$100 来表示我们始终希望查看该绝对单元格范围，但我们A2不使用美元符号来表示相对寻址，即列 A值在当前行。

    • CEILING(0.5*(the_above),1)给你X%（在我的示例中为 50%），四舍五入为具有该名称的人数。重要的是不要让此值等于 0。如果您希望总体上向下舍入，但仍将小于 1.00 的数字四舍五入为 1，请使用类似.MAX(INT(0.5*(the_above)),1)

      对于给定的示例数据（包含“Sue”和“Brian”），每行的计算结果为 2。如果您有 831 行数据，例如“John”，即为COUNTIF(A$2:A$100,A2)831，并且X是 5（即，我们要审计其中 5% = 0.05 的行），那么对于每个“John”行，这个值就是 42（0.05*831=41.55，四舍五入为 42）。

    • (A$2:A$100=A2)创建一个虚拟数组，该数组位于TRUE与当前行同名的行对应的位置，FALSE否则为零。因此，如果我们查看第 2 行（或第 3 行或第 4 行），这个虚拟数组将看起来像 {T, T, T, F, F, F, F, F, F, F}。
    • C$2:C$100*(A$2:A$100=A2)通过将上一步中的虚拟数组乘以 Column 中的随机数来扩展它 C。在数学上下文中，TRUE等于 1，FALSE 等于 0。因此，如果 Column 中的随机数 C 是 {0.83, 0.17 ,0.60, 0.42, 0.95, …}，那么这个虚拟数组将看起来像 {0.83, 0.17, 0.60, 0, 0, …}，即与当前人相对应的随机数，其他所有人都为零。
    • 也是，LARGE(C$2:C$100*(A$2:A$100=A2), CEILING(0.5*COUNTIF(A$2:A$100,A2),1)) 其中LARGE((virtual_array_of_selected_random_numbers), N)N是X% 的人数。这给出了N数组中第二大数字。因此，根据您给出的示例数据（包含“Sue”等）和我假设的数字（上文），“Sue”的值为 0.60（数组中第二大数字 0.83、0.17、0.60、0、0、...）。

    • =C2>=LARGE((that_mess))评估当前行中的随机数是否大于或等于N当前人的最大随机数。根据定义，这将适用于N当前人员的行数。

      功能LARGE是获得X每个人姓名下的项目百分比，如问题所要求的。继续上述示例，如果您有 831 行“John”的数据，并且X是 5（即，我们想审计这些行中的 5% = 0.05），那么CEILING(0.05*COUNTIF(A$2:A$100,A2),1)John 的值为 42，LARGE(…, 42)是第 42 个最大数字。因此，对于最大的 42 个数字来说，结果为真（但请参阅下一段）；即，对于42 行来说，结果为真。因此，这个布尔表达式在“John”的 831 行中的 5% 上为 TRUE。即，这自动执行了 MartinX 的答案中手动完成的步骤。C2>=(that_number)TRUE

      理论上，两个随机数相等是可能的。例如，Sue 的数字可能是 0.83、0.17 和 0.17。那么其中第二大的数字 ( ) 将是 0.17，因此对于 3 行来说，这是正确的。（即，你会得到比你想要的多一个。）这将会发生LARGE({those_numbers},2)TRUE非常很少。

  • D2 我们需要在带有Ctrl++Shift的单元格中输入公式， Enter 使其成为“数组公式”，这对于处理虚拟数组是必要的。

以下是示例结果：

          样本结果

（我为自己添加了一行来证明（至少）每个人都会选择一行（即使该行得到的随机数非常低）。）

相关内容