ARTICLE · 1030485
Excel里的CNN:用求解器让69个参数自己＂学会＂识别数字
之前我们两次用 Excel 的求解器做最优化——一次是单变量函数的最小值，一次是神经网络的 14 个参数。现在轮到 CNN 了。
这一篇用 Excel 的求解器直接优化 CNN 的 69 个参数。虽然暂时做不到让求解器跑梯度下降，但求解器可以直接求出使 最小的参数——这是验证 CNN 结构是否有效的最直接方法。
一、实验设定
输入：96 张 6×6 像素的手写数字图像（数字 1、2、3 各约 32 张） 网络：3 个过滤器（3×3）→ 3 个卷积子层（4×4）→ 3 个池化子层（2×2）→ 输出层（3 单元） 激活函数：Sigmoid 池化：最大池化 代价函数：96 张图像平方误差的总和 工具：Excel 求解器（Solver）
二、Excel 操作六步走
步骤 ①：读入学习用的图像数据
把 96 张 6×6 的二值图像读入工作表。每张图的 36 个像素值（0 或 1）排列在工作表的一行中，同时标注正解 。
图像编号 | x11 x12 ... x66 | t1 t2 t3
1 | 1 1 ... 0 | 1 0 0 ← 数字"1"
2 | 1 1 ... 1 | 0 1 0 ← 数字"2"
...
96 | 1 0 ... 1 | 0 0 1 ← 数字"3"
步骤 ②：设置参数的初始值
69 个参数（27 个过滤器分量 + 3 个卷积偏置 + 36 个输出权重 + 3 个输出偏置）用标准正态分布随机数赋初始值。
步骤 ③：对第 1 张图像计算所有中间变量
按 5-3 节的关系式依次计算：
卷积层加权输入 （式 (1)） 卷积层输出 （式 (2)） 池化层输出 （式 (3)） 输出层加权输入 （式 (4)） 输出层输出 （式 (5)） 平方误差 （式 (6)）
在 Excel 中就是逐个单元格嵌套公式。
步骤 ④：把公式复制到 96 张图像
将步骤③中为第 1 张图像建立的公式，向下复制到其余 95 张图像上。这相当于批量计算——每张图像在同一套参数下跑一次正向传播，算出各自的 。
步骤 ⑤：算出
把所有 96 个 相加：
这就是要求解器最小化的目标单元格。
步骤 ⑥：用求解器执行最优化
在 Excel 的求解器插件中设置：
目标单元格：（设为"最小化"） 可变单元格：69 个参数（过滤器、权重、偏置） 求解方法：GRG Nonlinear（广义梯度下降）
运行求解器，它自动调整 69 个参数，直到 降到最小值。
三、求解结果
书中展示的实际运行结果：
——求解器找到了一组参数，使得对所有 96 张训练图像的预测误差精确为 0。这意味着 CNN 完美拟合了训练数据。
用 Python 来表达这个结果：
"""
验证：如果 CT=0，意味着对每张图像的预测都是精确的
C = 0.5 * ((t1-a1)^2 + (t2-a2)^2 + (t3-a3)^2) = 0
→ a1=t1, a2=t2, a3=t3 （Sigmoid输出在(0,1)，精确等于0或1需要z→∞）
→ 实际中CT接近0就说明预测非常准确
"""
四、用新图像测试：泛化能力
训练好之后，必须用没见过的图像测试——否则可能只是"背下来"了训练数据（过拟合）。
书中用了一个新的 6×6 图像（像字母 "I" 一样的数字 "1"）：
测试图像（像I的数字"1"）：
0 0 0 0 0 0
0 0 1 1 0 0
0 0 1 1 0 0
0 0 1 1 0 0
0 0 1 1 0 0
0 0 0 0 0 0
把这个测试图像输入到训练好的 CNN 中，输出层的三个值分别是：
：最大 → 判定为数字"1" ：较小 ：较小
CNN 正确识别了，并且这个图像与训练集中的任何一张都不完全一样——说明模型学到的是"竖直条"这个模式（而不是具体的像素排列），具备一定的泛化能力。
五、Excel 求解器的局限性
虽然求解器跑出了 的完美结果，但我们要清醒地认识到：
求解器 ≠ 反向传播：求解器内部用的是数值方法来近似梯度（类似数值微分），所以计算量极大——96 张图像 × 69 个参数 × 多次迭代，对求解器来说非常吃力。
实际问题中数据量大得多：96 张 6×6 的图像是刻意简化的例题。真实的 MNIST 有 60000 张 28×28 的图像，用求解器去优化上万甚至上百万个参数是不可能的。
所以需要误差反向传播法：如第 4 章学到的，反向传播法能在一次正向-反向过程中算出所有 69 个参数的梯度，计算效率比数值微分高几个数量级。5-5 和 5-6 节就将为 CNN 建立反向传播公式。
六、小结
Excel 求解器验证了 CNN 结构的有效性：69 个参数能完美拟合 96 张训练图像 六步操作流程：读入数据 → 设初始值 → 正向计算 → 复制公式 → 算 → 求解器优化 测试新图像表明 CNN 学到的是模式而非死记——有一定的泛化能力 求解器的方法无法扩展到大规模问题——这正是我们需要反向传播法的原因
用 Excel 跑 CNN 听起来很疯狂，但它证明了一件事：CNN 不是魔法，就是一层层明确可算的数学运算。