如何从Excel的A1:E5单元格区域非空单元格中随机选取值？

方法1：兼容所有Excel版本的数组公式 #

输入公式后，旧版本需按Ctrl+Shift+Enter确认，新版本直接回车：

=INDEX(A1:E5,SMALL(IF(A1:E5<>"",ROW(A1:E5)-ROW(A1)+1),RANDBETWEEN(1,COUNTA(A1:E5))),SMALL(IF(A1:E5<>"",COLUMN(A1:E5)-COLUMN(A1)+1),RANDBETWEEN(1,COUNTA(A1:E5))))

公式说明： #

• COUNTA(A1:E5)：统计目标区域内非空单元格的总数
• IF(A1:E5<>"",ROW(A1:E5)-ROW(A1)+1)：提取所有非空单元格相对于区域的行偏移量
• SMALL(...,RANDBETWEEN(...))：随机筛选出一个有效行/列偏移量
• INDEX：根据偏移量定位并取出对应单元格的值

方法2：Excel 365/2021及以上的动态数组公式 #

利用动态数组函数简化操作，公式更简洁直观：

=INDEX(TOCOL(A1:E5,1),RANDBETWEEN(1,ROWS(TOCOL(A1:E5,1))))

公式说明： #

• TOCOL(A1:E5,1)：将目标区域转换为单列，自动忽略空值
• ROWS(TOCOL(A1:E5,1))：获取非空值组成的单列的行数
• INDEX+RANDBETWEEN：随机选取单列中的一个值

方法3：VBA宏（一键触发选取） #

如果需要手动控制选取时机，可以使用宏实现：

Sub RandomNonEmptyCell()
    Dim targetRng As Range, cell As Range
    Dim nonEmptyList As Collection
    Set nonEmptyList = New Collection
    
    '指定目标区域
    Set targetRng = ThisWorkbook.ActiveSheet.Range("A1:E5")
    
    '收集所有非空单元格的值
    For Each cell In targetRng
        If cell.Value <> "" Then
            nonEmptyList.Add cell.Value
        End If
    Next cell
    
    '随机选取并输出
    If nonEmptyList.Count > 0 Then
        '这里将结果输出到F1单元格，可按需修改
        ThisWorkbook.ActiveSheet.Range("F1").Value = nonEmptyList.Item(Int(Rnd() * nonEmptyList.Count) + 1)
        MsgBox "随机选取的值：" & nonEmptyList.Item(Int(Rnd() * nonEmptyList.Count) + 1)
    Else
        MsgBox "目标区域内无有效非空单元格！"
    End If
End Sub

使用步骤： #

1. 按Alt+F11打开VBA编辑器
2. 右键当前工作簿 → 插入 → 模块
3. 粘贴上述代码，运行宏即可

内容的提问来源于stack exchange，提问作者Mohit