Excel跨工作表随机选文本报错求助：尝试多公式均失效

问题描述 #

我有一个包含约10000行数据的Excel工作表Sheet1，想要从另一个约1000行的工作表中随机选取文本，但尝试多个公式均报错，已尝试的公式如下：

=INDEX(HEX!$I$2:$I$257;RANDBETWEEN(2;257))
=INDEX(Colors[@Color];RANDBETWEEN(1;ROWS(Colors[@Color])))

错误原因分析 #

1. 第一个公式：

   • 参数分隔符使用了分号（仅部分欧洲语言版本Excel默认用分号，通用版本需用逗号）；
   • RANDBETWEEN(2;257)起始值错误：HEX!$I$2:$I$257区域共256行，INDEX对区域的索引从1开始对应I2，随机数范围应从1而非2开始。

2. 第二个公式：

   • Colors[@Color]是结构化引用的单行数据，ROWS(Colors[@Color])返回值为1，导致随机数范围固定为1，无法实现随机选取；应引用整个Color列而非单行。

修正后的公式 #

1. 针对普通单元格区域（如HEX表的I2:I257）：

=INDEX(HEX!$I$2:$I$257, RANDBETWEEN(1, ROWS(HEX!$I$2:$I$257)))

• 改用逗号分隔参数（适配通用Excel版本）；
• ROWS(HEX!$I$2:$I$257)自动计算区域行数，避免手动计数错误；
• 随机数从1开始，匹配INDEX的区域索引逻辑。

2. 针对结构化表格（Colors表的Color列）：

=INDEX(Colors[Color], RANDBETWEEN(1, ROWS(Colors[Color])))

• 去掉@符号，引用整个Color列；
• ROWS(Colors[Color])返回列的总行数，确保随机数覆盖所有行。

额外注意事项 #

• 若使用Excel 2019及更早版本，部分公式需按Ctrl+Shift+Enter以数组形式输入（365/2021版本无需此操作）；
• 若工作表名包含空格或特殊字符，需用单引号包裹，例如：='HEX Data'!$I$2:$I$257。

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