如何简化可禁用四则运算的电子表格随机选择公式？

LibreOffice Calc #

直接用FILTER筛选启用的运算符号，配合INDEX和单次调用的RANDBETWEEN实现随机选择，避免分步公式或多次随机函数调用的问题：

=IFERROR(INDEX(FILTER({"＋","－","×","÷"},T7:W7="x"),RANDBETWEEN(1,ROWS(FILTER({"＋","－","×","÷"},T7:W7="x")))),"请至少启用一种运算")

• 逻辑：FILTER({"＋","－","×","÷"},T7:W7="x")提取所有标记为'x'的运算符号；
• RANDBETWEEN(1,ROWS(...))生成1到启用运算数量之间的随机整数，仅调用一次，避免结果不一致；
• INDEX根据随机数选取对应运算符号，IFERROR处理无运算启用的异常情况。

Microsoft Excel #

新版Excel（支持动态数组） #

公式逻辑与LibreOffice一致：

=IFERROR(INDEX(FILTER({"＋","－","×","÷"},T7:W7="x"),RANDBETWEEN(1,ROWS(FILTER({"＋","－","×","÷"},T7:W7="x")))),"请至少启用一种运算")

旧版Excel（无动态数组支持） #

用AGGREGATE替代FILTER实现筛选，兼容旧版本：

=IFERROR(INDEX({"＋","－","×","÷"},AGGREGATE(15,6,ROW($1:$4)/(T7:W7="x"),RANDBETWEEN(1,COUNTIF(T7:W7,"x")))),"请至少启用一种运算")

• 逻辑：ROW($1:$4)/(T7:W7="x")生成对应启用运算的行号，未启用项返回错误；
• AGGREGATE(15,6,...)忽略错误值并提取排序后的行号，再用RANDBETWEEN随机选一个行号，最后通过INDEX匹配运算符号。

谷歌表格 #

采用FILTER+INDEX+RANDBETWEEN组合，公式如下：

=IFERROR(INDEX(FILTER({"＋","－","×","÷"},T7:W7="x"),RANDBETWEEN(1,ROWS(FILTER({"＋","－","×","÷"},T7:W7="x")))),"请至少启用一种运算")

谷歌表格原生支持动态数组，无需额外修改即可适配该逻辑。

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