根据其他列中的特定标准在 Excel 中进行随机抽样

Question

我在 列 中有一个文件编号列表A，在 列 中有一个与文件关联的代码B，在 列 中有一个天数I。例如：

File      Code     Days
12345     AAAA     100
54321     AAAA      91
67890     BBBB     115
09876     CCCC     150

大约有 100 多行数据。我想根据以下标准随机抽取文件编号样本：

• 至少 20 个文件或 20%，以较大者为准。
• 每个代码至少有 1 个文件。
• 所有具有不与任何其他文件共享的代码的文件。
• 所有天数超过 150 天的文件。

这可能吗？

Answer 1

困难在于，由于文件数量与唯一代码数量、单个文件的代码、文件数 > 150 天、每个代码的文件数分布等未知比率，抽样可能有很多种方式。我不会尝试为所有可能的情况设计一个明确的解决方案，而是会描述一个可以让你找到解决方案的过程。每个步骤都有许多简单的方法可以在 Excel 中完成。

可以将其想象成三个锅：

• 您将在练习过程中将所选样本放入 Pot 1 中（该 Pot 将采用您使用所选文件名构建的列表形式）。我们将其称为样本列表。

• 第二个容器包含唯一代码的集合（这个容器将以唯一代码列表的形式出现；如果您不知道如何从包含重复项的列表中提取唯一值，请查找如何操作）。这将包含尚未由选定样本表示的代码。我们将其称为代码列表。

• 第三个奖池包含代表所需剩余样本数量的“代币”（这个奖池只是单元格中的一个数字，您在选择样本时会将其减少或计数）。我们将其称为剩余样本数量。计算 20 或 20% 中的较大者，并将该数字放入这个奖池中。

步骤如下：

1. 确定要使用的过采样限制。您的目标是 20% 的文件，但随机采样不会为您提供所有代码的统一 20%（除非您为此添加额外要求）。有些代码的选择率会低于 20%，有些则更高。非随机规则与 20% 无关。具有单个文件的代码将被 100% 采样。>150 天规则可能会为给定代码选择超过 20% 的文件。如果您按照此规则选择远高于 20% 的文件，则该代码已经过度代表，因此您可能不想从该代码中随机抽样更多。确定 >150 天规则的最大采样百分比（或样本数），超过该百分比，您将从随机采样中排除该代码。

2. 选择所有天数 >150 的文件并将其移动到样本列表。将剩余样本数减去这些文件的数量。从代码列表中删除这些文件所代表的代码（基于此文件集合的唯一代码）。

3. 如果此步骤导致任何代码超出过采样限制，请从源列表中删除与这些代码相关的所有文件。请注意，删除是比喻性的。目的是确保这些文件以后都不会被采样。您可以采用不需要实际删除文件的方式进行编码。

4. 在代码列表中的剩余文件和代码中，将任何文件移至样本列表，其中该文件是该代码的唯一文件。将剩余样本数减去这些文件的数量。从代码列表中删除这些文件所代表的代码。请注意，根据步骤 5 的实施方式，此步骤并非绝对必要。为了清晰起见，我将其作为一个单独的步骤，这样您就不需要从一个列表中随机选择。

5. 对于代码列表中的每个剩余代码，随机选择一个文件并将其移动到样本列表。将剩余样本数减去这些文件的数量。从代码列表中删除这些文件所代表的代码（删除代码可能是可选的，具体取决于您如何实现它；一旦完成此步骤，就不再需要代码列表了）。

6. 对于剩余样本计数中的文件数，从剩余文件中随机选择一个文件并将其移动到样本列表中。

您已经完成。

为您设计整个应用程序超出了本网站问题的预期范围。每个步骤都有很多方法，网站上以前的提问很好地介绍了实现这些步骤的公式和方法。如果您遇到困难，请提出新问题以寻求有关特定问题的帮助。例如，“如何从列表中随机选择一个值？” 就是一个合适的问题。