很简单，只需把下面的代码复制粘贴到Excel中，按下面的步骤运行ABC三段程序，训练过程就自动完成了。
首先打开Excel软件，新建一个空白表格。然后按 Alt+F11 组合键，打开编程窗口。
右侧的窗口是程序窗口，请按照图中标示①②的顺序，点开小框中的“+”号变为“-”号，再双击“Sheet1”，把光标移到③处单击，就可以向里面粘贴程序代码了。（代码附后）
粘贴完成后，按F5键运行程序。请按顺序依次选择运行以下三个程序：
“A准备数据()”
“B开始训练()”
“C开始推理()”
对程序的解读在这里：（点击可入）《》） 另附送一个强化学习源代码，在这里获得：《》'（以下是深度学习源代码，请从下一行开始复制到末尾）
'**********************神经网络反向传播法完整源代码*************************
Const 点阵首行 = 5 '点阵首行
Const 点阵首列 = 2 '点阵首列
Const x首行 = 5 '数据输入首行
Const x首列 = 5 '数据输入首列
Const x列宽 = 2 'x点阵宽度（列数）
Const x行高 = 2 'x点阵高度（行数）
Const x数量 = 4 '数据输入层数量
Const w2首行 = 5 '第2层权重w首行
Const w2首列 = 7 '第2层权重w首列
Const z2首行 = 5 '第2层加权求和首行
Const z2首列 = 13 '第2层加权求和首列
Const z2数量 = 3 '第2层数量
Const a首行 = 5 '第2层S函数输出首行
Const a首列 = 15 '第2层S函数输出首列
Const w3首行 = 5 '第3层权重w首行
Const w3首列 = 17 '第3层权重w首列
Const z3首行 = 5 '第3层加权求和z3首行
Const z3首列 = 22 '第3层加权求和z3首列
Const z3数量 = 2 '第3层数量
Const o首行 = 5 '第3层输出首行
Const o首列 = 24 '第3层输出首列
Const t首行 = 7 '正确解标签首行
Const t首列 = 3 '正确解标签首列
Const e首行 = 5 '均方误差首行
Const e首列 = 27 '均方误差首列
Const η首行 = 22 '学习率首行
Const η首列 = 8 '学习率首列
Const 数据首行 = 22 '推理应用数据首行
Const 数据首列 = 14 '推理应用数据首列
Sub A初始化()
Dim 列号 As Integer
Dim i As Integer
Dim j As Integer
Dim w2 As Range '第2层神经元的参数矩阵w2
Dim w3 As Range '第3层神经元的参数矩阵w3
Dim Δw2 As Range '第2层的梯度矩阵，即参数调整量矩阵
Dim Δw3 As Range '第3层的梯度矩阵，即参数调整量矩阵
'在首行写列号
For 列号 = 1 To 256
Cells(1, 列号).Value = 列号
Cells(1, 列号).Font.ColorIndex = 5 '蓝色
Next 列号
'画出4个点阵数据值
Dim 输入值(4) As Variant
输入值(0) = Array(1, 1, 0, 0)
输入值(1) = Array(0, 0, 1, 1)
输入值(2) = Array(1, 0, 1, 0)
输入值(3) = Array(0, 1, 0, 1)
For i = 0 To 3
For j = 0 To 1
Cells(点阵首行 + i * 6, 点阵首列 + j) = 输入值(i)(j)
Next j
For j = 2 To 3
Cells(点阵首行 + i * 6 + 1, 点阵首列 + j - 2) = 输入值(i)(j)
Next j
Next i
'设定4个输入数据x
For i = 0 To 3
For j = 0 To 3
Cells(x首行 + i * 6 + j, x首列) = 输入值(i)(j)
Next j
Cells(x首行 + i * 6 + j, x首列) = 1
Next i
'设定4个点阵的标准答案值
Dim 标签值(4) As Variant
标签值(0) = 0
标签值(1) = 0
标签值(2) = 1
标签值(3) = 1
For i = 0 To 3
Cells(点阵首行 + i * 6 + 2, 点阵首列 + 1) = 标签值(i)
Cells(点阵首行 + i * 6 + 1 + 2, 点阵首列 + 1) = 1 - 标签值(i)
Next i
Set w2 = Range(Cells(w2首行, w2首列), Cells(w2首行 + z2数量 - 1, w2首列 + x数量))
Set w3 = Range(Cells(w3首行, w3首列), Cells(w3首行 + z3数量 - 1, w3首列 + z2数量))
w2 = 初始化矩阵(w2, -1) '用正态分布随机数初始化第2层权重参数矩阵w2
w3 = 初始化矩阵(w3, -1) '用正态分布随机数初始化第3层权重参数矩阵w3
'初始化第2层梯度矩阵为0
Set Δw2 = Range(Cells(w2首行 + 6, w2首列), Cells(w2首行 + 6 + z2数量 - 1, w2首列 + x数量))
Δw2 = 初始化矩阵(Δw2, 0)
'初始化第3层梯度矩阵为0
Set Δw3 = Range(Cells(w3首行 + 6, w3首列), Cells(w3首行 + 6 + z3数量 - 1, w3首列 + z2数量))
Δw3 = 初始化矩阵(Δw3, 0)
'初始化b对应的输入为1
For i = 0 To x数量 - 1
Cells(x首行 + 4 + i * 6, x首列) = 1
Next i
Cells(a首行 + 3, a首列) = 1
Cells(a首行 + 9, a首列) = 1
'****************************************************************
'以下参数可修改
Cells(η首行, η首列) = 0.3 '设定学习率
Cells(η首行 + 2, η首列) = 500 '设定训练轮数
Cells(η首行, η首列 + 2) = 0 '实际执行轮数
'****************************************************************
End Sub
Sub B开始训练()
Dim iw2 As Range '第2层神经元的参数矩阵w2的初值矩阵
Dim iw3 As Range '第3层神经元的参数矩阵w3的初值矩阵
Dim x1 As Range '第1层神经元的输入矩阵x1
Dim w2 As Range '第2层神经元的参数矩阵w2
Dim z2 As Range '第2层神经元的累加和输入矩阵
Dim a2 As Range '第2层神经元的输出矩阵a2
Dim da2 As Range '第2层神经元的输出矩阵的导数
Dim pa2 As Range '第2层神经元的输出矩阵的数值微分
Dim i As Integer
Dim a3 As Range '第3层神经元的输入矩阵a3
Dim w3 As Range '第3层神经元的参数矩阵w3
Dim z3 As Range '第3层神经元的累加和输入矩阵
Dim o3 As Range '第3层神经元的输出矩阵
Dim do3 As Range '第3层神经元的输出矩阵的导数
Dim t As Range '目标结果矩阵
Dim ot As Range '目标结果矩阵的复制版
Dim ei As Single '输出结果的均方误差
Dim CT As Single '均方误差的累加和，即损失函数
Dim δ3 As Range '第3层的神经网络单元误差矩阵
Dim δ2 As Range '第2层的神经网络单元误差矩阵
Dim δw3 As Range '第3层的单元参数调整量矩阵
Dim δw2 As Range '第2层的单元参数调整量矩阵
Dim Δw3 As Range '第3层的梯度矩阵，即参数调整量矩阵
Dim Δw2 As Range '第2层的梯度矩阵，即参数调整量矩阵
Dim η As Single '学习率
Dim n As Long '训练轮数循环变量
Dim ns As Long '设置的训练轮数
Dim nc As Long '累计的训练轮数
Dim m As Long '写记录间隔次数
η = Cells(η首行, η首列) '取学习率
ns = Cells(η首行 + 2, η首列) '取设置的训练轮数
nc = Cells(η首行, η首列 + 2) '取实际的训练轮数
m = 0
For n = 1 To ns
'首先正向传播
CT = 0
'初始化第2层梯度矩阵为0
Set Δw2 = Range(Cells(w2首行 + 6, w2首列), Cells(w2首行 + 6 + z2数量 - 1, w2首列 + x数量))
Δw2 = 初始化矩阵(Δw2, 0)
'初始化第3层梯度矩阵为0
Set Δw3 = Range(Cells(w3首行 + 6, w3首列), Cells(w3首行 + 6 + z3数量 - 1, w3首列 + z2数量))
Δw3 = 初始化矩阵(Δw3, 0)
For i = 0 To 3 '4个输入矩阵x
Cells(5 + 2 * i, 26) = i + 1 '显示输入数据序号
'向第2层正向传播
Set x1 = Range(Cells(x首行 + 6 * i, x首列), Cells(x首行 + 6 * i + x数量, x首列))
Set w2 = Range(Cells(w2首行, w2首列), Cells(w2首行 + z2数量 - 1, w2首列 + x数量))
Set z2 = Range(Cells(z2首行, z2首列), Cells(z2首行 + z2数量 - 1, z2首列))
'求每个神经元所有输入的乘加和z2
z2.Value = Application.MMult(w2, x1)
'计算激活函数
Set a2 = Range(Cells(a首行, a首列), Cells(a首行 + z2数量 - 1, a首列))
a2.Value = S曲线激活函数(z2)
'求a2的导数，反向传播时使用
Set da2 = Range(Cells(a首行 + 6, a首列), Cells(a首行 + z2数量 - 1 + 6, a首列))
da2.Value = S曲线导数(a2)
'向第3层正向传播
Set a3 = Range(Cells(a首行, a首列), Cells(a首行 + z2数量, a首列))
Set w3 = Range(Cells(w3首行, w3首列), Cells(w3首行 + z3数量 - 1, w3首列 + z2数量))
Set z3 = Range(Cells(z3首行, z3首列), Cells(z3首行 + z3数量 - 1, z3首列))
'求每个神经元所有输入的乘加和z3
z3.Value = Application.MMult(w3, a3)
'计算激活函数
Set o3 = Range(Cells(o首行, o首列), Cells(o首行 + z3数量 - 1, o首列))
o3.Value = S曲线激活函数(z3)
'求o3的导数，反向传播时使用
Set do3 = Range(Cells(o首行 + 6, o首列), Cells(o首行 + z3数量 - 1 + 6, o首列))
do3.Value = S曲线导数(o3)
Set t = Range(Cells(t首行 + 6 * i, t首列), Cells(t首行 + 6 * i + z3数量 - 1, t首列))
Set ot = Range(Cells(o首行, o首列 + 1), Cells(o首行 + z3数量 - 1, o首列 + 1))
ot.Value = t.Value '复制标签数据的值
'计算与标准答案的误差
ei = 均方误差(o3, ot)
Cells(e首行 + 2 * i, e首列) = ei
'计算均方误差的累加和，即损失函数
CT = CT + ei
'下面进行反向传播
'向第3层反向传播
Set δ3 = Range(Cells(z3首行 + 6, z3首列), Cells(z3首行 + z3数量 - 1 + 6, z3首列))
δ3.Value = 输出层单元误差(o3, ot, do3)
'计算单个训练数据的第3层权重参数调整量，即对w3和b3的偏导数
Set δw3 = Range(Cells(w3首行 + 10, w3首列), Cells(w3首行 + 10 + z3数量 - 1, w3首列 + z2数量))
δw3 = 单元参数调整量(w3, a3, δ3)
'计算第3层梯度，即4个点阵图形的权重参数w3和b3的平均调整量Δw3和Δb3
Set Δw3 = Range(Cells(w3首行 + 6, w3首列), Cells(w3首行 + 6 + z3数量 - 1, w3首列 + z2数量))
Δw3 = 计算梯度(δw3, Δw3)
'向第2层反向传播
Set δ2 = Range(Cells(z2首行 + 6, z2首列), Cells(z2首行 + z2数量 - 1 + 6, z2首列))
δ2.Value = 隐藏层单元误差(w3, δ3, da2)
'计算单个训练数据的第2层权重参数调整量，即对w2和b2的偏导数
Set δw2 = Range(Cells(w2首行 + 10, w2首列), Cells(w2首行 + 10 + z2数量 - 1, w2首列 + x数量))
δw2 = 单元参数调整量(w2, x1, δ2)
'计算第2层梯度，即4个点阵图形的权重参数w3和b3的平均调整量Δw2和Δb2
Set Δw2 = Range(Cells(w2首行 + 6, w2首列), Cells(w2首行 + 6 + z2数量 - 1, w2首列 + x数量))
Δw2 = 计算梯度(δw2, Δw2)
Next i
nc = nc + 1
Cells(η首行, η首列 + 2) = nc '写累计的训练轮数
Cells(η首行 + 2, η首列 + 2) = CT '写均方误差的累加和，即损失函数
'根据梯度调整参数
w3 = 调整参数(η, Δw3, w3)
w2 = 调整参数(η, Δw2, w2)
DoEvents '释放控制权给操作系统
Next n
'进行推理应用测试
C开始推理
End Sub
Sub C开始推理()
Dim bx1 As Range '二维输入矩阵
Dim x1 As Range '第1层神经元的输入矩阵x1
Dim w2 As Range '第2层神经元的参数矩阵w2
Dim z2 As Range '第2层神经元的累加和输入矩阵
Dim a2 As Range '第2层神经元的输出矩阵a2
Dim i As Integer
Dim a3 As Range '第3层神经元的输入矩阵a3
Dim w3 As Range '第3层神经元的参数矩阵w3
Dim z3 As Range '第3层神经元的累加和输入矩阵
Dim o3 As Range '第3层神经元的输出矩阵
'********************************************************************************
'可在此任意指定4个点阵数据值（横或竖），用于检查能否正确识别
Dim 输入值(4) As Variant
输入值(0) = Array(1, 0, 1, 0) '竖
输入值(1) = Array(0, 0, 1, 1) '横
输入值(2) = Array(1, 1, 0, 0) '横
输入值(3) = Array(0, 1, 0, 1) '竖
'********************************************************************************
For i = 0 To 3
For j = 0 To 1
Cells(数据首行 + i * 3, 数据首列 + j) = 输入值(i)(j)
Next j
For j = 2 To 3
Cells(数据首行 + i * 3 + 1, 数据首列 + j - 2) = 输入值(i)(j)
Next j
Next i
For i = 0 To 3 '4个输入矩阵x
'向第2层传播
Set bx1 = Range(Cells(数据首行 + 3 * i, 数据首列), Cells(数据首行 + 3 * i + 1, 数据首列 + 1))
Set x1 = Range(Cells(数据首行, 数据首列 + 3), Cells(数据首行 + x数量, 数据首列 + 3))
x1.Value = 数据转纵列(bx1)
Set w2 = Range(Cells(w2首行, w2首列), Cells(w2首行 + z2数量 - 1, w2首列 + x数量))
Set z2 = Range(Cells(z2首行, z2首列), Cells(z2首行 + z2数量 - 1, z2首列))
z2.Value = Application.MMult(w2, x1)
Set a2 = Range(Cells(a首行, a首列), Cells(a首行 + z2数量 - 1, a首列))
a2.Value = S曲线激活函数(z2)
'向第3层传播
Set a3 = Range(Cells(a首行, a首列), Cells(a首行 + z2数量, a首列))
Set w3 = Range(Cells(w3首行, w3首列), Cells(w3首行 + z3数量 - 1, w3首列 + z2数量))
Set z3 = Range(Cells(z3首行, z3首列), Cells(z3首行 + z3数量 - 1, z3首列))
z3.Value = Application.MMult(w3, a3)
Set o3 = Range(Cells(数据首行 + 3 * i, 数据首列 + 6), Cells(数据首行 + 3 * i + z3数量 - 1, 数据首列 + 6))
' o3.Value = S曲线激活函数(z3)
o3.Value = softmax激活函数(z3)
If Cells(数据首行 + 3 * i, 数据首列 + 6) < 0.5 Then
Cells(数据首行 + 3 * i, 数据首列 + 7) = "横"
Else
Cells(数据首行 + 3 * i, 数据首列 + 7) = "竖"
End If
Next i
End Sub
Function 初始化矩阵(指定矩阵 As Range, 初值 As Integer) As Variant
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 指定矩阵.Rows.Count, 1 To 指定矩阵.Columns.Count)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
Dim zi As Single
If 初值 <> -1 Then
For i = 1 To 指定矩阵.Rows.Count
For j = 1 To 指定矩阵.Columns.Count
result(i, j) = 初值
Next j
Next i
Else '当 初值 = -1 时，采用正态分布值初始化
For i = 1 To 指定矩阵.Rows.Count
For j = 1 To 指定矩阵.Columns.Count
result(i, j) = Application.NormSInv(Rnd)
Next j
Next i
End If
' 返回处理后的矩阵
初始化矩阵 = result
End Function
Function 数组转纵列(输入数组 As Variant) As Variant
' 创建一个Variant数组用于存储结果
Dim m As Integer '纵列矩阵最大长度
Dim result() As Variant
' m = 4 + 1 '+1是为了增加b项
m = UBound(输入数组) - LBound(输入数组) + 1 + 1 '后+1是为了增加b项
ReDim result(1 To m, 1 To 1)
Dim i As Long, k As Long
Dim zi As Single
k = 1
For i = 0 To m - 2
zi = 输入数组(i)
result(k, 1) = zi
k = k + 1
Next i
result(k, 1) = 1
' 返回处理后的矩阵
数组转纵列 = result
End Function
Function 数据转纵列(输入矩阵 As Range) As Variant
' 创建一个Variant数组用于存储结果
Dim m As Integer '纵列矩阵最大长度
Dim result() As Variant
m = 输入矩阵.Rows.Count * 输入矩阵.Columns.Count + 1 '+1是为了增加b项
ReDim result(1 To m, 1 To 1)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long, k As Long
Dim zi As Single
k = 1
For i = 1 To 输入矩阵.Rows.Count
For j = 1 To 输入矩阵.Columns.Count
zi = 输入矩阵.Cells(i, j).Value
result(k, 1) = zi
k = k + 1
Next j
Next i
result(k, 1) = 1
' 返回处理后的矩阵
数据转纵列 = result
End Function
Function S曲线激活函数(输入矩阵 As Range) As Variant
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 输入矩阵.Rows.Count, 1 To 输入矩阵.Columns.Count)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
Dim zi As Single
For i = 1 To 输入矩阵.Rows.Count
For j = 1 To 输入矩阵.Columns.Count
zi = 输入矩阵.Cells(i, j).Value
result(i, j) = 1 / (1 + Exp(-zi))
Next j
Next i
' 返回处理后的矩阵
S曲线激活函数 = result
End Function
Function S曲线导数(输入矩阵 As Range) As Variant
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 输入矩阵.Rows.Count, 1 To 输入矩阵.Columns.Count)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
Dim zi As Single
For i = 1 To 输入矩阵.Rows.Count
For j = 1 To 输入矩阵.Columns.Count
zi = 输入矩阵.Cells(i, j).Value
result(i, j) = zi * (1 - zi)
Next j
Next i
' 返回处理后的矩阵
S曲线导数 = result
End Function
Function S曲线微分(输入矩阵 As Range) As Variant
'用数值微分法求导
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 输入矩阵.Rows.Count, 1 To 输入矩阵.Columns.Count)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
Dim zi As Single
Dim f1 As Single, f2 As Single '函数值
Dim gi As Single '梯度（导数）
Dim h As Single
h = 0.0001 '一个微小的偏移量
di = 0
For j = 1 To 输入矩阵.Columns.Count '列号
For i = 1 To 输入矩阵.Rows.Count '行号
zi = 输入矩阵.Cells(i, j).Value
f1 = 1 / (1 + Exp(-(zi + h)))
f2 = 1 / (1 + Exp(-(zi - h)))
gi = (f1 - f2) / (2 * h)
result(i, j) = gi
Next i
Next j
' 返回处理后的矩阵
S曲线微分 = result
End Function
Function softmax激活函数(输入矩阵 As Range) As Variant
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 输入矩阵.Rows.Count, 1 To 输入矩阵.Columns.Count)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
Dim zi As Single '输入矩阵中的某个元素
Dim sumy As Single '输入矩阵所有元素之和
Dim c As Single '输入矩阵中的最大元素
c = Application.Max(输入矩阵)
sumy = 0
For i = 1 To 输入矩阵.Rows.Count
For j = 1 To 输入矩阵.Columns.Count
zi = 输入矩阵.Cells(i, j).Value
zi = Exp(zi - c)
sumy = sumy + zi
Next j
Next i
For i = 1 To 输入矩阵.Rows.Count
For j = 1 To 输入矩阵.Columns.Count
zi = 输入矩阵.Cells(i, j).Value
zi = Exp(zi - c)
result(i, j) = zi / sumy
Next j
Next i
' 返回处理后的矩阵
softmax激活函数 = result
End Function
Function 均方误差(预测矩阵 As Range, 监督矩阵 As Range) As Single
' 创建一个Variant数组用于存储结果
Dim result As Single
Dim o As Single '预测矩阵的某个元素
Dim t As Single '监督矩阵的某个元素
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
result = 0
For i = 1 To 预测矩阵.Rows.Count
For j = 1 To 预测矩阵.Columns.Count
o = 预测矩阵.Cells(i, j).Value
t = 监督矩阵.Cells(i, j).Value
result = result + (t - o) ^ 2
Next j
Next i
' 返回处理后的矩阵
均方误差 = result / 2
End Function
Function 交叉熵误差(预测矩阵 As Range, 监督矩阵 As Range) As Single
' 创建一个Variant数组用于存储结果
Dim result As Single
Dim o As Single '预测矩阵的某个元素
Dim t As Single '监督矩阵的某个元素
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
Dim d As Single '加一个微小的值，以避免对0求log
d = 0.0000001
result = 0
For i = 1 To 预测矩阵.Rows.Count
For j = 1 To 预测矩阵.Columns.Count
o = 预测矩阵.Cells(i, j).Value
t = 监督矩阵.Cells(i, j).Value
result = result + t * Log(o + d)
Next j
Next i
' 返回处理后的矩阵
交叉熵误差 = -1 * result
End Function
Function 输出层单元误差(预测矩阵 As Range, 监督矩阵 As Range, 导数矩阵 As Range) As Variant
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 预测矩阵.Rows.Count, 1 To 预测矩阵.Columns.Count)
Dim o As Single '预测矩阵的某个元素
Dim t As Single '监督矩阵的某个元素
Dim d As Single '导数矩阵的某个元素
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
For i = 1 To 预测矩阵.Rows.Count
For j = 1 To 预测矩阵.Columns.Count
o = 预测矩阵.Cells(i, j).Value
t = 监督矩阵.Cells(i, j).Value
d = 导数矩阵.Cells(i, j).Value
result(i, j) = (o - t) * d
Next j
Next i
' 返回处理后的矩阵
输出层单元误差 = result
End Function
Function 隐藏层单元误差(w3参数矩阵 As Range, δ3误差矩阵 As Range, da2导数矩阵 As Range) As Variant
' 创建一个Variant数组用于w3矩阵的转置矩阵
Dim w3T() As Variant
ReDim w3T(1 To w3参数矩阵.Columns.Count, 1 To w3参数矩阵.Rows.Count) '行列转置
' 创建一个Variant数组用于存储结果
Dim wδ() As Variant
ReDim wδ(1 To da2导数矩阵.Rows.Count, 1 To da2导数矩阵.Columns.Count)
Dim result() As Variant
ReDim result(1 To da2导数矩阵.Rows.Count, 1 To da2导数矩阵.Columns.Count)
'转置矩阵
w3T = Application.Transpose(w3参数矩阵.Value)
wδ = Application.MMult(w3T, δ3误差矩阵)
Dim wδi As Single 'wδ矩阵的某个元素
Dim d As Single 'da2导数矩阵的某个元素
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
For i = 1 To da2导数矩阵.Rows.Count
For j = 1 To da2导数矩阵.Columns.Count
d = da2导数矩阵.Cells(i, j).Value
wδi = wδ(i, j)
result(i, j) = wδi * d
Next j
Next i
' 返回处理后的矩阵
隐藏层单元误差 = result
End Function
Function 单元参数调整量(参数矩阵 As Range, 输入矩阵 As Range, 单元误差矩阵 As Range) As Variant
' 计算单个训练数据的参数调整量
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 参数矩阵.Rows.Count, 1 To 参数矩阵.Columns.Count)
Dim δw As Single '单个训练数据的参数调整量?C/?w：
Dim x As Single '输入矩阵的某个元素
Dim δ As Single '单元误差矩阵的某个元素
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long
For i = 1 To 参数矩阵.Rows.Count '参数的行
For j = 1 To 参数矩阵.Columns.Count '参数的列
x = 输入矩阵.Cells(j, 1).Value
δ = 单元误差矩阵.Cells(i, 1).Value
δw = x * δ
result(i, j) = δw
Next j
Next i
' 返回处理后的矩阵
单元参数调整量 = result
End Function
Function 计算梯度(输入矩阵 As Range, 梯度矩阵 As Range) As Variant
' 计算批训练数据的参数调整量
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 输入矩阵.Rows.Count, 1 To 输入矩阵.Columns.Count)
' 遍历输入矩阵的每个元素
Dim i As Long, j As Long
Dim zi As Single '输入矩阵某个数据
Dim gi As Single '梯度矩阵某个数据
For i = 1 To 输入矩阵.Rows.Count
For j = 1 To 输入矩阵.Columns.Count
zi = 输入矩阵.Cells(i, j).Value
gi = 梯度矩阵.Cells(i, j).Value
gi = gi + zi
result(i, j) = gi
Next j
Next i
' 返回处理后的矩阵
计算梯度 = result
End Function
Function 调整参数(学习率 As Single, 梯度矩阵 As Range, 参数矩阵 As Range) As Variant
'根据梯度调整权重参数w和b
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 梯度矩阵.Rows.Count, 1 To 梯度矩阵.Columns.Count)
' 遍历梯度矩阵的每个元素
Dim i As Long, j As Long
Dim gi As Single '梯度矩阵某个数据
Dim wi As Single '参数矩阵某个数据
For i = 1 To 梯度矩阵.Rows.Count
For j = 1 To 梯度矩阵.Columns.Count
gi = 梯度矩阵.Cells(i, j).Value
wi = 参数矩阵.Cells(i, j).Value
wi = wi - 学习率 * gi
result(i, j) = wi
Next j
Next i
' 返回处理后的矩阵
调整参数 = result
End Function
Function 梯度下降(输入矩阵 As Range, 学习率 As Single, 循环次数 As Long) As Variant
' 创建一个Variant数组用于存储结果
Dim result() As Variant
ReDim result(1 To 输入矩阵.Rows.Count, 1 To 输入矩阵.Columns.Count)
' 遍历输入矩阵的每个元素并计算激活值
Dim i As Long, j As Long, k As Long
Dim zi As Single
Dim f1 As Single, f2 As Single '函数值
Dim 梯度 As Single '梯度（导数）
Dim h As Single
h = 0.0001 '一个微小的偏移量
For k = 1 To 循环次数
di = 0
For j = 1 To 输入矩阵.Columns.Count '列号
For i = 1 To 输入矩阵.Rows.Count '行号
zi = 输入矩阵.Cells(i, j).Value
f1 = (zi + h) ^ 2
f2 = (zi - h) ^ 2
梯度 = (f1 - f2) / (2 * h)
zi = zi - 学习率 * 梯度
输入矩阵.Cells(i, j) = zi
result(i, j) = zi
Next i
Next j
Next k
' 返回处理后的矩阵
梯度下降 = result
End Function