电子表格中的随机模拟与矩阵求解

随机、统计与条件函数（第 31 章，p.681–692）

蒙特卡洛与暴力枚举：求概率有三种方法——概率论计算、蒙特卡洛模拟、暴力列出全部结果。原书指出新手过度依赖蒙特卡洛，它是三者中唯一只给近似值的方法（每次重算都会变）；会算就算，不会算时，看似笨拙的穷举往往最省事。先算可能性空间大小：7d4 有 $4^7=16384$ 种，表格装得下。两种生成方法：用 Ctrl/Shift+方向键配合复制粘贴逐列翻倍（每加一颗骰子花的时间相同），或用公式——A 列放 0 到 16383，第 $k$ 颗骰子为 =MOD(FLOOR(A1/4^(k-1)),4)+1（即 4 进制展开；也可用 QUOTIENT；A 列从 0 而不是 1 开始，结果才按顺序排列）。原书指出复制粘贴法的好处是规模不影响操作量：加第三颗骰子新增 48 行，所花时间和加第二颗（12 行）一样；公式法更快但更容易要排错。2d6 只有 36 种，手写都比跑几千次模拟省事。支线 31.1 是计时赛：4d6 总和在 13–15 的概率，先蒙特卡洛估计（用 IF+AVERAGE 或 COUNTIF）再用 1296 行穷举求精确值，原书说熟手 5 分钟内能完成（本 wiki 穷举为 426/1296 ≈ 32.9%）。

相关的陷阱（支线 31.2）：两列各 1000 个独立 RAND()，前 10 行的相关系数可能高达 ±0.8，前 50 行偶尔超过 ±0.5，前 100 行可达 0.1–0.2，1000 行通常小于 0.05；再画散点图加线性趋势线，对比 $R^2$ 与 CORREL；均匀分布 $[0,1)$ 的标准差为 $1/(2\sqrt3)\approx0.2887$。教训：理解数据本质，不要过度依赖曲线拟合，尤其是外推——指数曲线与 S 形曲线在起步阶段几乎一样，但活跃玩家数不可能无限指数增长。统计背景见 描述统计与显著性检验。

洗牌与掉落表（第 32 章，p.693–700）

• COMBIN(60,7) 是 60 张牌抽 7 张起手的组合数（假设牌各不相同），PERMUT(1000,10) 是有序的前十名排列数（概率背景见 相依随机与抽牌概率）。
• 洗牌：52 格放 RAND()，旁边用 =RANK(C1,$C$1:$C$52) 得到 1–52 的随机排列（两个 RAND() 恰好相等的概率极小，可忽略），再用 VLOOKUP 或 INDEX 换成牌名：=INDEX($A$1:$A$52,RANK(B1,$B$1:$B$52),1)。VLOOKUP 只能在最左列查键，第三个参数是返回第几列；MATCH+INDEX 可以查任意位置，MATCH 第三个参数通常用 0（精确匹配）。
• 尽量不要隐藏行列：只有很小的箭头提示，跨隐藏区域复制粘贴或引用容易出错；中间数据放到单独工作表更好。
• 掉落表像每次都重洗的牌堆，各次掉落相互独立，所以不必每次洗整副牌。
• 等概率掉落：=INDIRECT("A"&RANDBETWEEN(1,6))；拼接多列可生成"前缀 + 武器 + 后缀"的随机武器，留空单元格可以掉普通武器。
• 加权掉落：简单比例就重复条目（6 普通、3 稀有、1 史诗）；概率不整时用累积下界表加近似查找：A 列 0、0.65、0.95、0.99、0.999，B 列普通/罕见/稀有/传说/史诗，=VLOOKUP(RAND(),A1:B5,2) 得到 65%/30%/4%/0.9%/0.1%。
• 支线 32.1：60 张牌含 20 张地，起手 7 张、之后每回合抽 1 张、每回合只能下 1 张地，求首次有 6 点法术力的回合（可用法术力 = min(回合数, 已抽地数)）。（本 wiki 用超几何分布精确计算：期望约 10.8 回合，中位数第 10 回合，第 6 回合就达到的概率约 21.7%。）
• 支线 32.2：用 COMBIN/PERMUT 算五张扑克牌型的概率（原书规定 A-2-3-4-5 不算顺子，注意别重复计数，各牌型加起来少于全部组合）。本 wiki 计算（分母 $\binom{{52}}{{5}}=2598960$）：一对 42.26%、两对 4.75%、三条 2.11%、顺子（不含同花顺）0.353%、同花（不含同花顺）0.197%、葫芦 0.144%、四条 0.024%、同花顺（不含皇家）32 种、皇家同花顺 4 种。
• 支线 32.3：用拼接和 INDIRECT 做随机游戏创意生成器（类型、题材、平台各一列），给 game jam 当随机约束。

迭代计算与逐回合模拟（第 35 章，p.717–721）

• 迭代计算：开启后允许循环引用，每次重算迭代指定次数。最大迭代设为 1 时，=A1+1 每次重算加 1，可以当作游戏时钟，模拟放置游戏的每秒收益或 PvP 中带冷却的技能：A1 =IF(B1>0,"","X")（冷却结束就放），B1 =IF(B1>0,B1-1,IF(ISBLANK(A1),0,10))（释放后冷却 10 秒）；多个技能可以算优先级（冷却中为 0），取等于 MAX 的那个。
• 再加一个全局计时器 J1 =J1+1、K1 放本回合动作、L1:L20 放 1–20、M1 =IF($J$1=L1,$K$1,M1) 向下填充，就能把每一刻的动作记录下来。
• 查循环引用错误：Excel 的"公式 > 追踪引用单元格/追踪从属单元格"会画箭头显示引用关系（原书称 OpenOffice Calc 有类似功能，Numbers 与 Google 表格当时没有）。
• 不支持迭代时：一行代表一个时间单位，每行引用上一行，向下复制；一个状态放不下一行就用固定行数的块，块间加边框。另一种做法是把四行内容分到四张工作表的第 1 行。
• 支线 35.1：两个随机石头剪刀布 AI 对战——支持迭代时用计时器把前 30 局胜负记到一列，并做一个"清空 C2 就重置"的总开关；不支持迭代时每行一局、向下填充 30 行，比较两种做法。

矩阵运算（第 37 章，p.731–738）

原书承认在表格里做矩阵运算不是最简单的，但其他工作都在表格里时，省去在不同软件之间导入导出。矩阵乘列向量的每一项是"该行与列向量逐项相乘再求和"，可以把输入矩阵和向量当作输入单元格、结果列向量当作输出单元格。

• 马尔可夫链：转移矩阵（列为当前状态、行为下一状态，每列之和为 1）旁放初始列向量，下一列用 =$B2*G$2+$C2*G$3+$D2*G$4（混合引用）向下、向右填充；大矩阵改用数组公式 =ArrayFormula(SUM(TRANSPOSE($B2:$D2)*(G$2:G$4)))（Ctrl+Shift+Enter）。转移矩阵中 1/3 应输入 =1/3 而不是小数，避免舍入误差；每列可以用 SUM 检查是否为 1。原书提醒 ArrayFormula() 是 Google 表格专有写法，Excel 用花括号表示，跨软件导入导出时要手动修正。终止状态那一行就是累积概率：首次超过 0.5 为中位数；相邻两列相减得到每步的结束概率，MAX+MATCH 找众数，与步数相乘求和得均值（原理见 马尔可夫链与状态机概率）。
• 收益矩阵：$Mc=p$，令 $p$ 全为 1（不能全为 0，否则只有零解），用 =MMULT(MINVERSE(B2:D4),E2:E4) 求 $c$ 后再除以总和归一化（方法见 收益矩阵与非传递机制求解）。矩阵不可逆时 MINVERSE 报 #NUM!，原书建议把某个值改动一点点再看结果。
• 规划求解 (Solver)：指定可变单元格和目标值，自动搜索使目标单元格尽量接近目标值；各求解器的搜索方法和容差不同（原书脚注推荐 Google 表格的 Linear Optimization 加载项）。
• 支线 37.1"信任但要验证"：在表格里复现第 22 章的马尔可夫链结果或第 25 章的收益矩阵解。支线 37.2 做 3×3 收益矩阵自动求解器，同时用逐步换行、乘 $-a/b$ 后相加的办法化成三角形式作为备份，找不到非零主元或末行全零时提示"有无穷多解"（如电钻版）；进阶到 4×4；终极挑战要求只改胜利得分，使最优比例变成石头:布:剪刀 = 1:2:4。

说明：为什么 MINVERSE 解石头剪刀布总是失败。对称零和游戏的收益矩阵是反对称矩阵（$M^T=-M$），选项数为奇数时行列式必为 0（$\det M=\det M^T=(-1)^n\det M$），所以 3×3、5×5 的对称零和收益矩阵永远不可逆，原书示例必须扰动一个单元格才能得到结果，这并非偶然。更稳妥的做法是把其中一个方程换成"概率之和为 1"再求逆，或用线性规划/规划求解。（本 wiki 的推导，用到的是线性代数中奇数阶反对称矩阵行列式为零的基本结论。）

原书已过时或需注意的地方（本页相关部分）

（菜单路径以 2026 年的 Google 表格与 Microsoft 365 为准，界面可能继续变化。）