基于Excel生成跳过缺失号码的无重复BINGO卡片

我有一台旧的BINGO游戏摇奖机，部分号码球丢失，需要制作Excel表格生成BINGO卡片，要求跳过缺失号码，且生成的卡片自身无重复号码。

可用号码表 #

我之前用这个公式从数组取随机数：

=INDEX(Sheet1!$B$1:$B$14;INT((RAND()*(13)+1)))

参考标准BINGO卡片样式：5×5网格，首行依次为B、I、N、G、O，对应列从专属号码池取数，中心格通常为自由格。

具体实现方案 #

步骤1：整理各列有效号码池

先筛出每个字母对应的非空号码，排除缺失值：

• B列（1-15）：1,2,3,4,6,7,8,9,10,11,12,13,14,15（缺失5）
• I列（16-30）：16,17,18,19,20,21,22,23,24,25,26,27,28,29,30（无缺失）
• N列（31-45）：31,32,33,34,35,36,37,39,41,42,44,45（缺失38,40,43）
• G列（46-60）：46,47,48,50,51,52,53,54,55,56,57,58,59（缺失49）
• O列（61-75）：61,62,63,64,66,67,68,69,71,72,73,74,75（缺失65,70）

步骤2：Excel 365/2021动态数组方案（推荐）

用动态数组公式直接生成无重复随机数，自动跳过空值：

• B列卡片单元格（A2起）：

  =INDEX(FILTER(Sheet1!$B$1:$B$14,Sheet1!$B$1:$B$14<>""),SORTBY(SEQUENCE(COUNTA(Sheet1!$B$1:$B$14)),RANDARRAY(COUNTA(Sheet1!$B$1:$B$14)),1))
  

  输入后会自动溢出5个不重复随机数，取前5个即可。
• I列卡片单元格（B2起）：

  =INDEX(FILTER(Sheet1!$D$1:$D$15,Sheet1!$D$1:$D$15<>""),SORTBY(SEQUENCE(COUNTA(Sheet1!$D$1:$D$15)),RANDARRAY(COUNTA(Sheet1!$D$1:$D$15)),1))
  

• N列卡片单元格（C2起）：

  =LET(nums,FILTER(Sheet1!$F$1:$F$12,Sheet1!$F$1:$F$12<>""),INDEX(nums,SORTBY(SEQUENCE(COUNTA(nums)),RANDARRAY(COUNTA(nums)),1)))
  

  手动将中心格（C4）设为「FREE」或留空。
• G列卡片单元格（D2起）：

  =INDEX(FILTER(Sheet1!$H$1:$H$13,Sheet1!$H$1:$H$13<>""),SORTBY(SEQUENCE(COUNTA(Sheet1!$H$1:$H$13)),RANDARRAY(COUNTA(Sheet1!$H$1:$H$13)),1))
  

• O列卡片单元格（E2起）：

  =INDEX(FILTER(Sheet1!$J$1:$J$13,Sheet1!$J$1:$J$13<>""),SORTBY(SEQUENCE(COUNTA(Sheet1!$J$1:$J$13)),RANDARRAY(COUNTA(Sheet1!$J$1:$J$13)),1))
  

步骤3：旧版Excel兼容方案（无动态数组）

用数组公式结合COUNTIF避免重复，每个单元格需单独设置：

• B列第一个单元格（A2），按Ctrl+Shift+Enter输入：

  =INDEX(Sheet1!$B$1:$B$14,SMALL(IF(Sheet1!$B$1:$B$14<>"",ROW(Sheet1!$B$1:$B$14)-ROW(Sheet1!$B$1)+1),INT(RAND()*COUNTA(Sheet1!$B$1:$B$14)+1)))
  

• B列第二个单元格（A3），同样按数组公式输入：

  =INDEX(Sheet1!$B$1:$B$14,SMALL(IF((Sheet1!$B$1:$B$14<>"")*(Sheet1!$B$1:$B$14<>$A$2),ROW(Sheet1!$B$1:$B$14)-ROW(Sheet1!$B$1)+1),INT(RAND()*(COUNTA(Sheet1!$B$1:$B$14)-1)+1)))
  

  后续单元格依次添加已选号码的排除条件即可。

步骤4：卡片格式调整

• 将卡片首行（A1:E1）分别设为B、I、N、G、O，加粗居中
• 调整单元格为正方形，添加边框模拟实体BINGO卡片样式
• 中心格（C4）可填充灰色或标注「FREE」

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