Excel里的CNN反向传播:50次迭代的全过程

之前用 Excel 求解器直接跳到了最优参数。但那依赖的是求解器内部的数值方法，并不是反向传播。这一篇，我们在 Excel 里亲手实现反向传播的每一步——正向算输出、反向算 δ、累加梯度、更新参数、迭代 50 次。不依赖任何求解器，就是纯 Excel 公式。

这和之前的 Excel 实验是同一个思路，但规模大了很多：69 个参数、96 张图像、50 轮迭代。

一、实验设定

• 网络：3 个 3×3 过滤器 → 卷积层（3×4×4）→ 池化层（3×2×2）→ 输出层（3 单元）
• 参数：69 个（27 个过滤器分量 + 3 个卷积偏置 + 36 个输出权重 + 3 个输出偏置）
• 训练数据：96 张 6×6 手写数字图像
• 激活函数：Sigmoid
• 池化：最大池化
• 学习率：（小的正数，需要试错确定）
• 代价函数：

二、Excel 操作八步走

步骤 ①：读入 96 张图像数据

与 5-4 节相同，将 6×6 像素值和正解  读入工作表。

步骤 ②：设置参数初始值 + 学习率

69 个参数用标准正态分布随机数初始化，同时设定学习率 。

书中特别备注：学习率  需要反复试错——太小收敛慢，太大可能不收敛。

import random, math

random.seed(42)

# 69个参数的初始值
filters = [[[random.gauss(0, 0.5) for _ in range(3)] for _ in range(3)]
for _ in range(3)]     # 3个过滤器 × 3×3
b_F = [random.gauss(0, 0.5) for _ in range(3)]   # 卷积偏置
W_O = [[[[random.gauss(0, 0.5) for _ in range(2)] for _ in range(2)]
for _ in range(3)] for _ in range(3)]     # 输出权重 (3×3×2×2→3)
b_O = [random.gauss(0, 0.5) for _ in range(3)]    # 输出偏置
eta = 0.05# 学习率

步骤 ③：正向传播——算出各层输出和平方误差 C

对第 1 张图像，用当前参数计算：

• 卷积层加权输入  → 输出 
• 池化层输出 （最大池化）
• 输出层加权输入  → 输出 
• 平方误差 

步骤 ④：反向传播——计算 δ

这是核心。按 5-5 节的公式：

先算输出层 δ：

再递推到卷积层 δ：

步骤 ⑤：计算平方误差 C 关于各参数的偏导数

用步骤④求出的 ，按 5-5 节的公式计算：

• （过滤器分量梯度）
• （卷积偏置梯度）
• （输出权重梯度）
• （输出偏置梯度）

步骤 ⑥：对全部 96 张图像累加，得到  和 

将步骤③~⑤中为第 1 张图像建立的公式复制到全部 96 张。

然后将所有 96 张图像的  相加得到 ，将所有偏导数相加得到梯度分量：

步骤 ⑦：梯度下降更新参数

按基本公式更新全部 69 个参数：

在 Excel 中，就是在下方开辟新的参数区域，每个参数 = 旧值 -  × 梯度分量。

步骤 ⑧：重复 ③~⑦，共 50 次

将第 1 轮的参数更新结果作为第 2 轮的起始参数，重复正向→反向→更新。把块状区域复制 49 份，得到 50 轮迭代。

三、运行结果

完成 50 次迭代后：

解释这个数字：

• 96 张图像，每张平均平方误差 
• 平方误差最大值：当输出  而正解是  时，（更极端的可达 1.5）
• ——预测已经非常接近正解

四、用新图像测试

训练完成后，用没见过的 6×6 图像测试 CNN。

书中展示了一个测试用例：输入一个新的数字"3"的图像，输出的三个值中  最大——**CNN 正确判定为数字"3"**。

"""
测试：用训练后的参数预测新图像
"""
defpredict(image, filters, b_F, W_O, b_O):
# 卷积 + Sigmoid
    a_F = [[[0.0]*4for _ in range(4)] for _ in range(3)]
for k in range(3):
for i in range(4):
for j in range(4):
                s = sum(filters[k][p][q] * image[i+p][j+q]
for p in range(3) for q in range(3))
                a_F[k][i][j] = sigmoid(s + b_F[k])

# 最大池化
    a_P = [[[0.0]*2for _ in range(2)] for _ in range(3)]
for k in range(3):
for i in range(2):
for j in range(2):
                a_P[k][i][j] = max(
                    a_F[k][2*i][2*j], a_F[k][2*i][2*j+1],
                    a_F[k][2*i+1][2*j], a_F[k][2*i+1][2*j+1]
                )

# 输出层
    a_O = [0.0]*3
for n in range(3):
        s = sum(W_O[k][n][i][j] * a_P[k][i][j]
for k in range(3) for i in range(2) for j in range(2))
        a_O[n] = sigmoid(s + b_O[n])

# 最大输出对应的数字 = 判定结果
return a_O.index(max(a_O)) + 1# 1, 2, 或 3


# 测试一个新图像
test_image = [
    [1,1,1,1,1,1],
    [0,0,0,0,0,1],
    [1,1,1,1,1,1],
    [0,0,0,0,0,1],
    [1,1,1,1,1,1],
    [0,0,0,0,0,1],
]
result = predict(test_image, filters, b_F, W_O, b_O)
print(f"判定结果: 数字 {result}")

五、代价函数跟踪：确认梯度下降在"工作"

书中提到：跟踪 50 次迭代中  的变化，可以确认梯度下降法正在发挥作用—— 应该随着迭代次数单调递减（如果不减，说明学习率或初始值有问题）。

典型的代价函数下降曲线：

C_T
 │
 │  ••
 │      •••
 │            ••••
 │                    •••••____
 └──────────────────────────────▶ 迭代次数
  0                         50

六、反向传播 vs 求解器：两种方法的对比

之前的求解器方法证明了"CNN 结构能解决这个问题"，5-6 的反向传播法则证明了"反向传播在 CNN 中同样高效可用"。

七、小结

最后六篇文章，从"小恶魔扫描图像"的直觉出发，一路走到"在 Excel 中用反向传播训练 CNN 50 轮"——这是全书最综合的一章：

从最初的"一个隐藏单元做出判断"，到第 4 章的"误差反向传播在多层的递推"，再到现在的"卷积网络 + 反向传播"——28 篇文章，我们跟随一同用最基础的数学工具（偏导数、链式法则、Σ求和、Sigmoid），把深度学习最核心的数学拼图一块块搭完了。

全书正文到此结束。28 篇文章，如果从头跟到尾，你已经理解了神经网络从"做判断"到"自学习"的完整数学逻辑。剩下的就是挑一个你喜欢的框架（PyTorch、TensorFlow），用比 Excel 更方便的方式，让 GPU 替你跑这几千几万次矩阵运算。

恭喜你，完成了这场硬核而真诚的数学旅程。🎉