跳至主要内容

Kutools for Office — 一套工具，五种功能。事半功倍。

如何在Excel中从数据列表随机填充值？

Author Kelly Last modified

在Excel中从预定义列表中随机选择值是一项常见的任务，广泛应用于数据分析、模拟、随机分配、抽样、测试场景等。例如，您可能希望模拟抽奖过程、为质量保证随机分配测试用例，或在团队成员之间随机分配任务。在Excel中实现这一目标可以大大提高您的工作效率，并减少手动选择时容易出现的错误。

本综合指南将向您介绍多种实现此目标的方法，从适合所有用户的简单公式方法，到使用VBA进行高级自动化，甚至还可以借助像Kutools for Excel这样的专业且用户友好的工具。每种方法都有其自身的优点和适用场景，以下将详细分析以帮助您选择最适合需求的解决方案。

使用公式从数据列表中随机填充值

在本节中，我们将引导您通过几种基于公式的实用方法从指定列表中随机填充值。这些解决方案不需要额外安装，可以在大多数现代版本的Excel中快速实施。

✅ 公式1：INDEX + RANDBETWEEN 函数

INDEX 和 RANDBETWEEN 函数的组合是一种经典且兼容各版本的方式，用于从列表中随机选取值。它适用于快速生成单个或多个随机值，允许重复选取的情况，如随机抽样或模拟数据生成。

要使用此方法，只需将以下公式复制或输入到空白单元格（例如B2），然后拖动填充柄向下填充所需的随机值数量。请注意，由于该公式涉及易失性函数（如RANDBETWEEN），每次工作表重新计算时结果都会变化。

=INDEX($A$2:$A$15, RANDBETWEEN(1, COUNTA($A$2:$A$15)))

INDEX + RANDBETWEEN functions to fill values randomly

🔍 此公式的详细解释：
  • A2:A15：表示您希望从中随机选择的值列表。
  • COUNTA($A$2:$A$15)：动态计算列表中的项目数量，确保如果列表长度发生变化时公式仍保持稳健。
  • RANDBETWEEN(1, n)：生成介于1和n之间的随机整数（n是列表中的项目数量）。
  • INDEX(range, number)：从您的列表中检索与随机选择的位置对应的项。

注意事项：由于值会在工作表的任何更改时刷新，如果您需要结果保持不变，请务必复制填充的单元格并将其粘贴为值。此外，此策略不排除重复项——如果需要唯一性，请考虑后面部分描述的方法或进行后处理。

 

✅ 公式2: INDEX + RANDARRAY 函数 (Excel 365 / 2021+)

INDEX 和 RANDARRAY 函数的组合适用于Excel 365和Excel 2021用户。此方法利用动态数组一次性输出批量随机选择，简化了需要一步完成许多随机抽取的工作流。当您需要快速获取一定数量的随机选择时特别有用。然而，需要注意的是，与前面的公式一样，这种方法不能保证批次内的结果唯一性。

要使用此解决方案，将公式输入到一个空白单元格中，例如B2，然后按Enter键。Excel将自动“溢出”生成的随机值到后续行。例如，以下公式输出列表中的5个随机值：

=INDEX(A2:A15, RANDARRAY(5, 1, 1, COUNTA(A2:A15), TRUE))

INDEX + RANDARRAY functions to fill values randomly

🔍 此公式的详细解释：
  • A2:A15：用于随机选择的指定数据列表。
  • COUNTA(A2:A15)：计算目标列表中的条目数。
  • RANDARRAY(5,1,1, COUNTA(...), TRUE)：生成5个介于1和列表中最后一个位置之间的随机整数，生成垂直数组（1列）。
  • INDEX(A2:A15, …)：将每个随机数映射到列表中的值。

提示：如果您需要不同数量的随机值，只需相应调整RANDARRAY(5,1, ...)中的5即可。始终记住，如果您需要结果保持固定，请复制公式输出并粘贴为值，因为基于公式的输出会随工作表的变化而更新。

💡 提示：由于RANDBETWEEN和RANDARRAY都是易失性的，任何工作表更改时输出都会更新。为了保留静态快照，请复制结果并使用“粘贴值”。

使用VBA从列表中随机填充值（高级和可定制的解决方案）

如果您需要自动化大规模随机值分配、防止重复或需要更多定制（例如在选择过程中应用复杂逻辑），那么使用VBA（Visual Basic for Applications）方法是理想的。VBA使您能够生成真正唯一的随机选择，应用自定义分布逻辑，并通过单一命令重复任务——有助于高级模拟、自动化随机分配或处理大量数据集。

此解决方案适用于熟悉宏的用户或那些希望自动化其Excel工作流的用户。

1. 单击“开发工具”>“Visual Basic”打开VBA编辑器（或按Alt + F11），这将打开Microsoft Visual Basic for Applications窗口。然后，转到“插入”>“模块”，并将以下代码粘贴到模块窗口中：

Sub RandomFillFromList_NoDuplicates()
    Dim srcRange As Range
    Dim destRange As Range
    Dim srcValues As Variant
    Dim destCount As Integer
    Dim usedIndexes As Object
    Dim i As Integer
    Dim randIndex As Integer
    
    On Error Resume Next
    Set srcRange = Application.InputBox("Select source list", "KutoolsforExcel", Type:=8)
    If srcRange Is Nothing Then Exit Sub
    
    Set destRange = Application.InputBox("Select destination range (number of random values to fill)", "KutoolsforExcel", Type:=8)
    If destRange Is Nothing Then Exit Sub
    
    srcValues = Application.Transpose(srcRange.Value)
    destCount = destRange.Cells.Count
    Set usedIndexes = CreateObject("Scripting.Dictionary")
    
    If UBound(srcValues) < destCount Then
        MsgBox "Not enough unique items in the source list to fill destination without duplicates.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    Randomize
    For i = 1 To destCount
        Do
            randIndex = Int(Rnd() * UBound(srcValues)) + 1
        Loop While usedIndexes.Exists(randIndex)
        
        usedIndexes(randIndex) = True
        destRange.Cells(i).Value = srcValues(randIndex)
    Next
End Sub

2. 通过单击 Run button VBA工具栏上的按钮运行宏。宏将提示您选择(a)源列表（要从中选择的值范围），以及(b)输出范围（对于提取的随机值数量，只需选择相同数量的单元格即可）。如果源列表足够大，则代码确保输出中没有重复值；否则，它将显示警告信息。

这种VBA方法提供了以下优点和注意事项：

  • 优点：确保随机、不重复的选择；允许处理非常大的列表和批次；易于自动化重复任务。
  • 缺点：需要启用宏的Excel文件。如果您的工作簿限制宏，则此方法可能不适合。如果目标数量超过源项目的数量，可能会发生错误。
  • 错误提醒：如果源列表中没有足够的唯一值满足请求，宏将会通知您。
  • 定制提示：您可以进一步修改代码，通过删除唯一性检查来允许多次出现，或者实现权重或过滤逻辑以适应更专业的场景。

使用Kutools for Excel从数据列表中随机选择并填充值（所有版本）

Kutools for Excel提供了一种便捷且交互式的解决方案，用于从列表中随机选择并填充值。它非常适合那些希望在不编写公式或代码的情况下处理随机分配的用户，或者需要以最少的手动输入快速批量处理选择的用户。Kutools还提供了控制输出的选项，例如选择的数量，并通过直观的对话框界面实现这一点。

Kutools for Excel 提供了超过 300 种高级功能，简化复杂任务，提升创造力与效率。 通过集成 AI 能力，Kutools 能够精准自动执行任务，让数据管理变得轻松简单。Kutools for Excel 的详细信息...         免费试用...

安装Kutools for Excel后，按照以下步骤使用其内置的随机选择功能：

  1. 选择包含您想随机选择的值的范围。
  2. 单击 Kutools > 区域 > 随机排序/选择区域。请参考下面的截图：
    click Sort / Select Range Randomly of kutools
  3. 在 随机排序/选择区域 对话框中，转到 选择 选项卡，并执行以下操作：
    • 指定要随机选择的单元格数量。
    • 确保在“选择类型”中选择了“选择随机单元格”选项。
    • 最后，单击 确定 按钮。
      set options in the dialog box
  4. 所指示数量的随机单元格将被突出显示或选中。然后，您可以根据需要复制并将这些单元格粘贴到其他地方。
    copy and paste the random cells

除了简单易用之外，Kutools方法还防止了手动随机化时常见的错误，并且不需要熟悉Excel的公式或宏设置。如果您希望选择中包含唯一值，请确保源列表大于计划选择的项目数量，并验证对话框中是否可以选择无重复项的选项（如有）。

🔚结论

在Excel中从预定义列表中随机填充值可以通过多种技术高效管理，这些技术适用于不同的知识水平和场景：

  • 对于所有Excel版本，INDEX加RANDBETWEEN公式快速可靠，适用于允许重复的列表中的随机选择。
  • 如果您使用的是Excel 365或2021，RANDARRAY + INDEX解决方案提供了更动态的批量选择，在需要一次性输出多个结果时加快流程。
  • 对于高度可定制的需求——例如确保无重复、自动化大规模随机分配或处理复杂的选择逻辑——VBA方法提供了最大的灵活性，但用户应熟悉如何运行宏。
  • 如果您偏好无需代码、用户友好的方法，Kutools for Excel允许您通过图形界面生成随机选择，适合需要快速结果的初学者和高级用户。

重要的是要考虑您是否需要唯一选择或允许重复，需要多少随机选择，以及您对Excel公式或宏的熟悉程度。在共享或保存随机结果之前，使用“粘贴为值”功能以防止意外重新计算。对于有兴趣探索更多Excel解决方案的用户，请访问我们的Excel教程部分以获取更多实用指南和技巧。

故障排除建议：仔细检查列表范围的准确性，注意使用易失性函数时的重新计算，并确保在使用基于代码的解决方案时，您的宏安全设置允许VBA执行。如果在使用VBA时发生错误（例如源列表大小不足），请按照提示重新考虑您的范围。

相关文章：

在Excel中根据条件随机选择单元格

在Excel中为单元格随机添加背景/填充颜色

最佳Office办公效率工具

🤖 Kutools AI 助手：以智能执行为基础，彻底革新数据分析 |代码生成 |自定义公式创建|数据分析与图表生成 |调用Kutools函数……
热门功能：查找、选中项的背景色或标记重复项 | 删除空行 | 合并列或单元格且不丢失数据 | 四舍五入……
高级LOOKUP：多条件VLookup|多值VLookup|多表查找|模糊查找……
高级下拉列表：快速创建下拉列表 |依赖下拉列表 | 多选下拉列表……
列管理器： 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较区域与列……
特色功能：网格聚焦 |设计视图 | 增强编辑栏 | 工作簿及工作表管理器 | 资源库（自动文本） | 日期提取 | 合并数据 | 加密/解密单元格 | 按名单发送电子邮件 | 超级筛选 | 特殊筛选（筛选粗体/倾斜/删除线等）……
15大工具集：12项 文本工具（添加文本、删除特定字符等）|50+种 图表 类型（甘特图等）|40+实用 公式（基于生日计算年龄等）|19项 插入工具（插入二维码、从路径插入图片等）|12项 转换工具（小写金额转大写、汇率转换等）|7项 合并与分割工具（高级合并行、分割单元格等）| ……
Kutools支持多种语言——可选择英语、西班牙语、德语、法语、中文等40多种语言！

通过Kutools for Excel提升您的Excel技能，体验前所未有的高效办公。 Kutools for Excel提供300多项高级功能，助您提升效率并节省时间。 点击此处获取您最需要的功能……

Office Tab为Office带来多标签界面，让您的工作更加轻松

  • 支持在Word、Excel、PowerPoint中进行多标签编辑与阅读。
  • 在同一个窗口的新标签页中打开和创建多个文档，而不是分多个窗口。
  • 可提升50%的工作效率，每天为您减少数百次鼠标点击！

所有Kutools加载项，一键安装

Kutools for Office套件包含Excel、Word、Outlook和PowerPoint的插件，以及Office Tab Pro，非常适合跨Office应用团队使用。

Excel Word Outlook Tabs PowerPoint
  • 全能套装——Excel、Word、Outlook和PowerPoint插件+Office Tab Pro
  • 单一安装包、单一授权——数分钟即可完成设置（支持MSI）
  • 协同更高效——提升Office应用间的整体工作效率
  • 30天全功能试用——无需注册，无需信用卡
  • 超高性价比——比单独购买更实惠