在Microsoft Excel中选择随机样本的6种方法

在Microsoft Excel中选择随机样本的6种方法

您想从 Excel 数据中选择一个随机样本吗?

随机样本是从较大的总体中选取的记录,使得每个记录都有同等被选中的机会。

目标是确保样本尽可能准确地反映总体情况。如果任何一个群体被过度抽样或抽样不足,那么结果就会出现偏差,并导致不准确的结论。

随机抽样对于统计工作至关重要,因为它有助于消除任何偏见。

本篇文章将向您展示如何选择有重复项和无重复项的随机样本。获取本篇文章中使用的示例工作簿的副本以继续学习!

推荐:Proton GE如何提高SteamOS和Linux上的游戏兼容性

使用 RAND 函数选择随机样本

这种方法是最手动的,但如果您只需要一个样本,那么它是最容易设置和一次性使用的方法。

为此,您需要向数据添加一个包含随机数的辅助列。

这可用于按随机顺序对数据进行排序,并且可以将最上面的行作为随机样本。

在Microsoft Excel中选择随机样本的6种方法

这是身高和体重测量的数据列表。假设您想从中获取 5 个观测值的随机样本。

这是您需要做的。

= RAND ( )
  1. 使用上述公式向数据添加一列。将公式复制并粘贴到整个列中。

RAND函数将在列中生成一个介于 0 和 1 之间的随机十进制数。现在您可以根据这个随机列对数据进行排序。基于随机数进行排序将创建随机顺序。

  1. 选择整个范围,包括新的随机数列。
  2. 转到“数据”选项卡。
  3. 选择“过滤”命令。这将为数据集中的每个列标题添加排序和过滤切换。您可以使用它们对数据进行排序。

 提示:您还可以使用CtrlShift+L键盘快捷键为数据添加排序和过滤切换。

  1. 点击随机数列中的排序和过滤切换按钮。这将显示该列的排序选项
  2. 从菜单中选择从最小到最大排序选项。

您还可以选择从大到小排序选项,但这并不重要,因为无论哪种方式都会导致数据的随机顺序。

数据将以随机顺序出现,您可以复制并粘贴数据的前 5 行,并将其作为随机样本。

如果需要另一个随机样本,只需重复该过程。

当您再次对数据进行排序时,这将导致RAND函数重新计算并生成新的随机数,因此您将获得新的随机顺序。

推荐:咖啡厅餐厅食品WordPress主题Grand Restaurant

使用数据分析插件选择随机样本

所有版本的 Excel 都提供具有抽样功能的统计分析插件。

您可以启用数据分析工具库插件来使用此随机抽样。

但请注意,这只允许您从单列数字数据中抽样数据。它还会返回样本中的重复值,并且没有不重复值进行抽样的选项。

以下是如何使用数据分析工具库插件生成随机样本。

  1. 转到“数据”选项卡。
  2. 单击功能区分析部分中的数据分析按钮。仅当安装了插件后,此功能才可用。

这将打开数据分析菜单。

  1. 分析工具中选择采样选项。
  2. 按下“确定”按钮。

这将打开“采样”菜单,您可以在其中从几个输入和输出选项中进行选择。

  1. 选择要从中抽样的输入范围。这必须是一列数字数据,您可以从所选范围中包含或排除列标题标签。
  2. 如果所选的输入范围包含列标题标签,则选中标签选项,否则请不要选中。
  3. 选择随机作为抽样方法
  4. 在输入框中输入样本数量。此示例将返回随机样本中的 5 个值。
  5. 在“输出选项”部分中选择要输出随机样本的位置。此示例将使用“输出范围”选项并将结果放在单元格F3中。
  6. 按下“OK”按钮。

当您按下OK时,采样值将被添加到您想要的位置。

使用数组公式选择随机样本

获取样本的一个非常有趣的方法是使用动态数组公式返回随机数。

动态数组允许您从单个公式返回多个值,因此您可以使用它们返回数据的多个随机行。

=FILTER(
    SORTBY(
        B3:D17,
        RANDARRAY(ROWS(B3:D17))
    ),
    SEQUENCE(ROWS(B3:D17)) 

上述公式将从范围B3:D17中返回 5 个没有重复的随机行。

ROWS函数确定源数据中有多少行。在此示例中,范围B3 D17有 15 行。

然后, RANDARRAY函数创建一列介于 0 和 1 之间的随机值,其行数与范围B3:D17相同。

然后, SORTBY函数根据RANDARRAY函数生成的列对数据范围B3:D17进行排序。由于这是一列随机值,因此SORTBY函数将返回一个随机排序的范围。

然后对随机排序的范围使用 FILTER 函数来返回前 5 行作为随机样本

这是通过根据从 1 到数据范围的行数的序列进行过滤来实现的。

过滤条件SEQUENCE(ROWS(B3:D17)) results in a column where the first 5 values are TRUE. This cause the FILTER function to return the first 5 rows of the randomly sorted range.

这将为您提供一个随机样本,并且您可以通过按键轻松生成新的随机样本F9。这将导致RANDARRAY函数重新计算并返回新的随机行。

推荐:修复Windows 11上拒绝访问错误的5种方法

使用数组公式选择随机样本 [带重复项]

前面的公式方法返回一个没有任何重复行的随机样本,但您可能希望允许它们。

使用动态数组方法也是可以实现的。

=INDEX(
    B3:D17,
    RANDARRAY(
        5,
        1,
        1,
        ROWS(B3:D17),
        TRUE
    ),
    SEQUENCE(
        1,
        COLUMNS(B3:D17)
    )
)

上述公式将返回范围B3:D17中包含 5 行重复项的样本。

这里也使用了ROWS函数来获取数据中的行数。

然后使用RANDARRAY函数生成 5 行随机整数值,范围从 1 到行数。这是将包含在随机样本中的行号列表。

RANDARRAY函数允许返回重复值,因此这将导致随机样本可能具有重复项。

SEQUENCE函数将用于确保源数据的所有列都由INDEX函数返回。这将生成一个数组(如{1,2,3}本例所示),因为源数据中有 3 列。

然后,INDEX函数返回使用RANDARRAY函数生成的给定行集。

可以通过按下F9键盘上的重新计算公式来更新此示例。

使用 Power Query 选择随机样本

Power Query 是 Excel 内置的工具,用于导入数据并将其转换为所需的格式。

此方法将使用 Power Query 从具有重复的数据中获取随机样本。

假设您的数据在 Excel 表中,那么请按照以下步骤操作。

  1. 选择表格内的单元格。
  2. 转到“数据”选项卡。
  3. 选择功能区中获取和转换数据部分中的“从表/范围查询”命令。

这将打开 Power Query 编辑器。

  1. 转到“添加列”选项卡。
  2. 常规部分中选择自定义列选项。
  1. 新列名字段中为新列命名,例如“随机”
= List.Random(1)
  1. 在自定义列公式输入中输入上述M代码公式。
  2. 按下“OK”按钮。

这将创建一个列表,每行包含一个随机值。

  1. 单击“随机”列的列标题中的“提取”切换按钮。
  2. 选择提取值选项。
  1. 在“从列表中提取值”弹出菜单中,从分隔符列表中选择“无”。由于每个列表仅包含一个值,因此不需要分隔符。
  2. 按下“OK”按钮。

这将为列的每一行获取一个随机值。

  1. 单击“随机”列中的排序和过滤切换按钮。
  2. 从菜单中选择升序排序选项。

您也可以选择“降序排序”选项,结果相同。数据的顺序将被随机化。

现在您可以根据前几行进行筛选以获取所需的样本量。

  1. 转到“主页”选项卡。
  2. 单击保留行命令。
  3. 从菜单中选择保留顶行选项。
  1. 在“保留前几行”对话框中输入所需的样本大小。本示例将使用 5。
  2. 按下“OK”按钮。

现在,您可以通过删除“随机”列来清理数据。它的工作已经完成。

  1. 右键单击“随机”列标题。
  2. 从选项中选择删除。

然后,您可以将数据重新加载到 Excel 工作簿中。转到“主页”选项卡,单击“关闭并加载”命令,以获取将数据重新加载到表中的选项。

然后,您可以通过转到“数据”选项卡并选择“刷新”选项来生成另一个随机样本。

推荐:如何修复Windows 11激活错误代码0xc004c003

使用 Power Query 选择随机样本 [无重复]

此方法将使用 Power Query 从数据中获取没有重复的随机样本。

您需要像以前一样将表中的源数据加载到 Power Query 编辑器中。

一旦数据进入 Power Query 编辑器,您就可以按照以下步骤获取无重复的随机样本。

首先要做的是添加一个标识源数据行号的索引列。

  1. 转到“添加列”选项卡。
  2. 单击索引列选项菜单。
  3. 从 1 中选择。

这将在数​​据的第一行中创建一列从 1 开始的整数。稍后将使用它来连接另一个包含随机选择的行号的查询。

现在您需要创建一个新的空白查询来随机选择样本的行号。

  1. 转到Power Query 编辑器的“主页”选项卡。
  2. 单击“新源”
  3. 选择其他来源选项。
  4. 从子菜单选项中选择空白查询。

这将创建一个新的,您可以在其中构建行号的随机选择。

={1..5}
  1. 将上述公式添加到空白查询的公式栏中,然后按下Enter键。这将创建一个从 1 开始到 5 的连续数字列表。

将 5 替换为样本中所需的任意数量项目。由于样本是有放回的,因此该数字可能高于源数据中的实际行数。

当您创建列表时,Power Query 编辑器将显示“列表工具转换”选项卡。

  1. 单击“列表工具转换”选项卡中的“到表”命令。
  1. 在“到表”菜单中选择“无”作为分隔符。
  2. 按下“OK”按钮。
  1. 转到“添加列”选项卡。
  2. 选择自定义列选项。
  1. 新列名输入中为新列命名,例如“随机”
Number.RandomBetween(0.5,15.5)
  1. 将上述公式添加到自定义列公式输入中。
  2. 按下“OK”按钮。

这将为每一行创建一个介于 0.5 和 15.5 之间的随机数。这些数字将被四舍五入为最接近的整数,从而得到介于 1 到 15 之间的整数。此范围基于源数据中的总行数。

需要 0.5 到 15.5 的初始范围,以便每个行号都有相同的出现机会。

  1. 选择随机数列。
  2. 转到“变换”选项卡。
  3. 单击“舍入”选项。
  4. 从菜单中选择“圆形” 。
  1. 输入0作为小数位数
  2. 按下“OK”按钮。

现在您可以随机选择具有重复项的行号。

这可以与原始数据源合并。

  1. 转到“主页”选项卡。
  2. 选择合并查询命令。
  1. 选择随机行号列和索引列作为合并的基础。
  2. 选择添加了索引列的原始数据源。
  3. 选择索引列。
  4. 选择左外连接类型。这将返回随机行查询中的所有行以及源数据中的匹配行。
  5. 按下“OK”按钮。

这将创建一个列。每个表应包含与随机列中的值相对应的一行。

  1. 单击数据列中的扩展切换按钮。
  2. 取消选中使用原始列名作为前缀选项。
  3. 按下“OK”按钮。

您现在可以清理此查询并删除不需要的列。

  1. 按住Ctrl键并选择要删除的每一列。
  2. 右键单击列标题。
  3. 从选项中选择删除列。

现在您可以将结果加载到 Excel。转到“主页”选项卡并选择“关闭并加载”,然后将结果加载到表格中。

您可以随时通过转到“数据”选项卡并单击“刷新”命令来生成新样本。

结论

随机抽样是统计学中一项重要的技术。如果你使用 Excel 进行任何统计分析,你无疑会遇到这种需求。

基本辅助列技术和插件可轻松用于一次性案例。

但是,当您需要从数据中获取多个样本时,动态数组公式或 Power Query 解决方案将更适合。

推荐:7 种用于自动编码ChatGPT替代方案


发表评论