![根据其他列中的特定标准在 Excel 中进行随机抽样](https://linux22.com/image/1426746/%E6%A0%B9%E6%8D%AE%E5%85%B6%E4%BB%96%E5%88%97%E4%B8%AD%E7%9A%84%E7%89%B9%E5%AE%9A%E6%A0%87%E5%87%86%E5%9C%A8%20Excel%20%E4%B8%AD%E8%BF%9B%E8%A1%8C%E9%9A%8F%E6%9C%BA%E6%8A%BD%E6%A0%B7.png)
我在 列 中有一个文件编号列表A
,在 列 中有一个与文件关联的代码B
,在 列 中有一个天数I
。例如:
File Code Days
12345 AAAA 100
54321 AAAA 91
67890 BBBB 115
09876 CCCC 150
大约有 100 多行数据。我想根据以下标准随机抽取文件编号样本:
- 至少 20 个文件或 20%,以较大者为准。
- 每个代码至少有 1 个文件。
- 所有具有不与任何其他文件共享的代码的文件。
- 所有天数超过 150 天的文件。
这可能吗?
答案1
困难在于,由于文件数量与唯一代码数量、单个文件的代码、文件数 > 150 天、每个代码的文件数分布等未知比率,抽样可能有很多种方式。我不会尝试为所有可能的情况设计一个明确的解决方案,而是会描述一个可以让你找到解决方案的过程。每个步骤都有许多简单的方法可以在 Excel 中完成。
可以将其想象成三个锅:
您将在练习过程中将所选样本放入 Pot 1 中(该 Pot 将采用您使用所选文件名构建的列表形式)。我们将其称为样本列表。
第二个容器包含唯一代码的集合(这个容器将以唯一代码列表的形式出现;如果您不知道如何从包含重复项的列表中提取唯一值,请查找如何操作)。这将包含尚未由选定样本表示的代码。我们将其称为代码列表。
第三个奖池包含代表所需剩余样本数量的“代币”(这个奖池只是单元格中的一个数字,您在选择样本时会将其减少或计数)。我们将其称为剩余样本数量。计算 20 或 20% 中的较大者,并将该数字放入这个奖池中。
步骤如下:
确定要使用的过采样限制。您的目标是 20% 的文件,但随机采样不会为您提供所有代码的统一 20%(除非您为此添加额外要求)。有些代码的选择率会低于 20%,有些则更高。非随机规则与 20% 无关。具有单个文件的代码将被 100% 采样。>150 天规则可能会为给定代码选择超过 20% 的文件。如果您按照此规则选择远高于 20% 的文件,则该代码已经过度代表,因此您可能不想从该代码中随机抽样更多。确定 >150 天规则的最大采样百分比(或样本数),超过该百分比,您将从随机采样中排除该代码。
选择所有天数 >150 的文件并将其移动到样本列表。将剩余样本数减去这些文件的数量。从代码列表中删除这些文件所代表的代码(基于此文件集合的唯一代码)。
如果此步骤导致任何代码超出过采样限制,请从源列表中删除与这些代码相关的所有文件。请注意,删除是比喻性的。目的是确保这些文件以后都不会被采样。您可以采用不需要实际删除文件的方式进行编码。
在代码列表中的剩余文件和代码中,将任何文件移至样本列表,其中该文件是该代码的唯一文件。将剩余样本数减去这些文件的数量。从代码列表中删除这些文件所代表的代码。请注意,根据步骤 5 的实施方式,此步骤并非绝对必要。为了清晰起见,我将其作为一个单独的步骤,这样您就不需要从一个列表中随机选择。
对于代码列表中的每个剩余代码,随机选择一个文件并将其移动到样本列表。将剩余样本数减去这些文件的数量。从代码列表中删除这些文件所代表的代码(删除代码可能是可选的,具体取决于您如何实现它;一旦完成此步骤,就不再需要代码列表了)。
对于剩余样本计数中的文件数,从剩余文件中随机选择一个文件并将其移动到样本列表中。
您已经完成。
为您设计整个应用程序超出了本网站问题的预期范围。每个步骤都有很多方法,网站上以前的提问很好地介绍了实现这些步骤的公式和方法。如果您遇到困难,请提出新问题以寻求有关特定问题的帮助。例如,“如何从列表中随机选择一个值?” 就是一个合适的问题。