看不进去高深的理论,就下了个期刊小论文(基于Excel技术平台人工神经网络BP模型及应用_邹文安.pdf),配合下载的一个excel
干什么的呢,我猜是输入给定的二进制数,输出给定的二进制数,找到这个函数关系。
1 Const N = 7, M = 5, L = 4, P = 10
2 'N为输入层节点个数, M为隐含层节点个数, L为输出层节点个数, P为训练样本
3 Const PARA_eta = 0.5, PARA_alpha = 0.5, PARA_Emin = 0.2
4 'PARA_eta为学习率, PARA_alpha动量系数, PARA_Emin总误差的精度要求
5 Const Calcu_12 = 2
6 'Calcu_12取1为(常规BP算法);取2为(增加动量项);
7
8 Dim IN_PUT(1 To N), OUT_Y(1 To M), OUT_O(1 To L), OUT_dO(1 To L) As Variant
9 'IN_PUT(1 To N)为输入向量, OUT_Y(1 To M)为隐层输出向量, OUT_O(1 To L)为输出层输出向量,
10 'OUT_dO(1 To L)为期望输出向量,
11
12 Dim PARA_12(0 To N, 1 To M), PARA_23(0 To M, 1 To L) As Variant
13 'PARA_12(0 To N, 1 To M)为输入-隐层权值, PARA_23(0 To M, 1 To L)为隐层-输出权值
14
15 Dim PARA_12d1(1 To M), PARA_23d1(1 To L) As Variant '误差δ
16 Dim PARA_12d2(0 To N, 1 To M), PARA_23d2(0 To M, 1 To L) As Variant '误差Δ
17 Dim PARA_INT(0 To N, 1 To P), PARA_OUT(1 To L, 1 To P) As Variant
18 'PARA_INT(0 To N, 1 To P)为训练样本, PARA_OUT(1 To L, 1 To P)为期望输出
19
20 Dim PARA_E, PARA_Ep As Variant
21 'PARA_E为总输出误差, PARA_Ep训练样本误差
22
23 Dim q, p_i As Integer
24 Dim PARA_P(1 To 2, 1 To P) As Variant
25 Dim int_i1, int_i2, int_i3, int_i4, int_CC, I_Res As Integer
26 Dim int_p, int_k As Integer
27 Dim para_a1, para_a2, para_a3, time_a, time_b, C_C As Variant
28
29 'AA训练与测试
30 Sub AA训练与测试()
31 I_Res = MsgBox("欢迎使用训练网络程序!可以开始训练了吗?", 4 + 64, "<训练>提示!")
32 If I_Res = 6 Then
33 BB训练程序
34 If q > 10000 Then
35 Masg_box = MsgBox("总算结束了!“训练”运行结束。 ", 0 + 16, "<结束>提示!")
36 GoTo line1
37 End If
38 I_Res = MsgBox("是否测试网络?", 4 + 48, "<测试>提示!")
39 If I_Res = 6 Then
40 C_C = 0
41 CC测试程序
42 Masg_box = MsgBox("恭喜你!“测试”运行结束。 ", 0 + 0, "<结束>提示!")
43 End If
44 GoTo line1
45 End If
46 I_Res = MsgBox("欢迎使用测试网络程序!!!" & Chr(10) & "可以开始测试了吗?", 4 + 64, "<测试>提示!")
47 If I_Res = 6 Then
48 C_C = InputBox("请辅助测试用时所需的循环次数。", "<循环次数>提示!", 0)
49 If C_C <> "" Then
50 int_i1 = Len(C_C)
51 For int_i2 = 1 To int_i1
52 If Asc(Mid(C_C, int_i2, 1)) < 48 Or Asc(Mid(C_C, int_i2, 1)) > 57 Then
53 GoTo line1
54 End If
55 Next int_i2
56 CC测试程序
57 End If
58 End If
59 line1:
60 End Sub
61 '结束AA训练与测试
62
63 'AA训练程序
64 Function BB训练程序()
65
66 Worksheets("Sheet2").Activate '激活工作表Sheet2
67 '读取训练输入距阵
68 For int_i1 = 1 To P
69 PARA_INT(0, int_i1) = -1
70 For int_i2 = 1 To N
71 PARA_INT(int_i2, int_i1) = Sheet1.Cells(int_i2 + 1, int_i1).Value
72 Next int_i2
73 Next int_i1
74 '结束读取训练输入距阵
75
76 '读取期望输出距阵
77 For int_i1 = 1 To P
78 For int_i2 = 1 To L
79 PARA_OUT(int_i2, int_i1) = Sheet1.Cells(int_i2 + 10, int_i1).Value
80 Next int_i2
81 Next int_i1
82 '结束期望输出距阵
83
84 '权值距阵赋初值
85 For int_i1 = 1 To M
86 For int_i2 = 0 To N
87 PARA_12(int_i2, int_i1) = (Rnd() - 0.5)
88 Next int_i2
89 Next int_i1
90 For int_i1 = 1 To L
91 For int_i2 = 0 To M
92 If int_i2 Mod 2 Then '如果行数对2取余等于1则赋值为1
93 PARA_23(int_i2, int_i1) = 1
94 Else
95 PARA_23(int_i2, int_i1) = -1
96 End If
97 Next int_i2
98 Next int_i1
99 '结束权值距阵赋初值
100
101 '清除以有的输出距阵V
102 For int_i1 = 0 To N
103 For int_i2 = 1 To M + 2
104 Sheet2.Cells(int_i1 + 5, int_i2).Value = ""
105 Next int_i2
106 Next int_i1
107 '结束清除
108
109 '清除以有的输出距阵W
110 For int_i1 = 0 To M + 2
111 For int_i2 = 1 To L
112 Sheet2.Cells(int_i1 + 14, int_i2).Value = ""
113 Next int_i2
114 Next int_i1
115 '结束清除
116
117 q = 0
118 '总训练次数归零
119
120 Sheet2.Range(Cells(2, 5), Cells(3, 7)).ClearContents
121 time_a = Now()
122 '计算开始时刻
123
124 Stude1:
125 '学习准备
126 PARA_Ep = 0 '训练样本误差
127 '随机排序训练样本
128 For int_i1 = 1 To P
129 PARA_P(1, int_i1) = int_i1
130 PARA_P(2, int_i1) = Rnd()
131 Next int_i1
132 For int_i1 = 1 To P - 1
133 For int_i2 = int_i1 + 1 To P
134 If PARA_P(2, int_i1) > PARA_P(2, int_i2) Then '如果PARA_P第二行随机数 前面的大于后面的,那么进行交换,升序排列
135 para_a1 = PARA_P(1, int_i1)
136 PARA_P(1, int_i1) = PARA_P(1, int_i2) '第一行为序号,也跟着随机数同位移动,最终随机数升序,序号随机
137 PARA_P(1, int_i2) = para_a1
138 para_a2 = PARA_P(2, int_i1)
139 PARA_P(2, int_i1) = PARA_P(2, int_i2)
140 PARA_P(2, int_i2) = para_a2
141 End If
142 Next int_i2
143 Next int_i1
144 '结束随机排序训练样本
145 '结束学习准备
146
147 '开始学习
148 For int_p = 1 To P '循环10个(随机排位的)样本
149 int_k = PARA_P(1, int_p) '随机的int_k决定取出输入样本的第int_k个,也就是PARA_INT的第 int_k 列
150 For int_i1 = 1 To N
151 IN_PUT(int_i1) = PARA_INT(int_i1, int_k) '将总样本中某个样本放入一维数组
152 Next int_i1
153 For int_i1 = 1 To L
154 OUT_dO(int_i1) = PARA_OUT(int_i1, int_k) '将对应样本对应的期望输出输入一维数组
155 Next int_i1
156
157 '计算隐层各节点输出
158 For int_i1 = 1 To M
159 para_a1 = -1 * PARA_12(0, int_i1) '这个相当于偏移的b,或则称为阈值,取第一行权重值的相反数
160 For int_i2 = 1 To N
161 para_a1 = para_a1 + IN_PUT(int_i2) * PARA_12(int_i2, int_i1) 'IN_PUT为某个输入样本,
162 Next int_i2
163 OUT_Y(int_i1) = 1 / (1 + Exp(-para_a1))
164 Next int_i1
165 '结束计算实际隐层各节点输出
166
167 '计算输出层各节点输出
168 For int_i1 = 1 To L
169 para_a1 = -1 * PARA_23(0, int_i1)
170 For int_i2 = 1 To M
171 para_a1 = para_a1 + OUT_Y(int_i2) * PARA_23(int_i2, int_i1)
172 Next int_i2
173 OUT_O(int_i1) = 1 / (1 + Exp(-para_a1))
174 Next int_i1
175 '结束计算输出层各节点输出
176
177 '计算误差
178 para_a1 = 0
179 For int_i1 = 1 To L
180 para_a1 = para_a1 + (OUT_dO(int_i1) - OUT_O(int_i1)) ^ 2 'para_a1为某列的样本的所有元素的误差和delta?
181 Next int_i1
182 '训练样本误差的平方, 一个样本就是一列数。 PARA_E为总输出误差
183 PARA_Ep = PARA_Ep + para_a1
184 '累加 - 训练样本误差的平方
185 '结束计算误差
186
187 '计算δ 求PARA_23d1
188 For int_i1 = 1 To L
189 PARA_23d1(int_i1) = (OUT_dO(int_i1) - OUT_O(int_i1)) * (1 - OUT_O(int_i1)) * OUT_O(int_i1) 'deltajk
190 Next int_i1
191 ' 求 PARA_12d1
192 For int_i1 = 1 To M '权值动量项
193 para_a1 = 0
194 For int_i2 = 1 To L
195 para_a1 = para_a1 + PARA_23d1(int_i2) * PARA_23(int_i1, int_i2) 'ADD(deltajk*Wjk)
196 Next int_i2
197 PARA_12d1(int_i1) = para_a1 * (1 - OUT_Y(int_i1)) * OUT_Y(int_i1)
198 Next int_i1
199 '结束计算δ
200
201 Select Case Calcu_12 '取1为(常规BP算法);取2为(增加动量项);
202 Case 1
203 '权值调整(常规BP算法)
204 For int_i1 = 1 To L
205 PARA_23(0, int_i1) = PARA_23(0, int_i1) + PARA_eta * PARA_23d1(int_i1) * (-1)
206 For int_i2 = 1 To M
207 PARA_23(int_i2, int_i1) = PARA_23(int_i2, int_i1) + PARA_eta * PARA_23d1(int_i1) * OUT_Y(int_i2)
208 Next int_i2
209 Next int_i1
210 For int_i1 = 1 To M
211 PARA_12(0, int_i1) = PARA_12(0, int_i1) + PARA_eta * PARA_12d1(int_i1) * (-1)
212 For int_i2 = 1 To N
213 PARA_12(int_i2, int_i1) = PARA_12(int_i2, int_i1) + PARA_eta * PARA_12d1(int_i1) * IN_PUT(int_i2)
214 Next int_i2
215 Next int_i1
216 '结束权值调整(常规BP算法)
217 Case 2
218 '权值调整(增加动量项)
219 For int_i1 = 1 To L
220 PARA_23(0, int_i1) = PARA_23(0, int_i1) + PARA_eta * PARA_23d1(int_i1) * (-1)
221 PARA_23(0, int_i1) = PARA_23(0, int_i1) + PARA_alpha * PARA_23d2(0, int_i1)
222 PARA_23d2(0, int_i1) = PARA_eta * PARA_23d1(int_i1) * (-1)
223 For int_i2 = 1 To M
224 PARA_23(int_i2, int_i1) = PARA_23(int_i2, int_i1) + PARA_eta * PARA_23d1(int_i1) * OUT_Y(int_i2)
225 PARA_23(int_i2, int_i1) = PARA_23(int_i2, int_i1) + PARA_alpha * PARA_23d2(int_i2, int_i1)
226 PARA_23d2(int_i2, int_i1) = PARA_eta * PARA_23d1(int_i1) * OUT_Y(int_i2)
227 Next int_i2
228 Next int_i1
229 For int_i1 = 1 To M
230 PARA_12(0, int_i1) = PARA_12(0, int_i1) + PARA_eta * PARA_12d1(int_i1) * (-1)
231 PARA_12(0, int_i1) = PARA_12(0, int_i1) + PARA_alpha * PARA_12d2(0, int_i1)
232 PARA_12d2(0, int_i1) = PARA_eta * PARA_12d1(int_i1) * (-1)
233 For int_i2 = 1 To N
234 PARA_12(int_i2, int_i1) = PARA_12(int_i2, int_i1) + PARA_eta * PARA_12d1(int_i1) * IN_PUT(int_i2)
235 PARA_12(int_i2, int_i1) = PARA_12(int_i2, int_i1) + PARA_alpha * PARA_12d2(int_i2, int_i1)
236 PARA_12d2(int_i2, int_i1) = PARA_eta * PARA_12d1(int_i1) * IN_PUT(int_i2)
237 Next int_i2
238 Next int_i1
239 '结束权值调整(增加动量项)
240 End Select
241
242 q = q + 1
243 Sheet2.Cells(3, 1).Value = q
244 '输出总训练次数
245
246 If q > 10000 Then
247 GoTo Fin_Calcu
248 End If
249 '输出总训练次数超过一万次,误差未达到精度要求,结束训练。
250
251 '训练一次完毕,取下一个训练样本
252 Next int_p
253 '结束学习
254
255 PARA_E = Sqr(PARA_Ep / P)
256 '总输出误差
257 Sheet2.Cells(3, 2).Value = PARA_E
258
259 If PARA_E > PARA_Emin Then '检查误差是否达到精度要求 PARA_E为总输出误差
260 GoTo Stude1 '误差未达到精度要求,返回“学习准备”-“开始学习”
261 End If
262
263 Fin_Calcu:
264
265 time_b = Now()
266 '计算结束时刻
267
268 Sheet2.Cells(2, 5) = "用时"
269 Sheet2.Cells(3, 5) = time_b - time_a
270 Sheet2.Cells(2, 6) = "开始"
271 Sheet2.Cells(3, 6) = time_a
272 Sheet2.Cells(2, 7) = "结束"
273 Sheet2.Cells(3, 7) = time_b
274 '输出计算用时情况
275
276 '输出PARA_12距阵的值
277 For int_i1 = 0 To N
278 For int_i2 = 1 To M
279 Sheet2.Cells(int_i1 + 5, int_i2).Value = PARA_12(int_i1, int_i2)
280 Next int_i2
281 Next int_i1
282 '结束输出PARA_12距阵的值
283
284 '输出PARA_23距阵的值
285 For int_i1 = 0 To M
286 For int_i2 = 1 To L
287 Sheet2.Cells(int_i1 + 14, int_i2).Value = PARA_23(int_i1, int_i2)
288 Next int_i2
289 Next int_i1
290 '结束输出PARA_23距阵的值
291
292 End Function
293 'FIN AA训练程序
294
295 Function CC测试程序()
296
297 Worksheets("Sheet3").Activate '激活工作表Sheet3
298
299 '读取输入矩阵
300 For int_i1 = 1 To P
301 PARA_INT(0, int_i1) = -1
302 For int_i2 = 1 To N
303 PARA_INT(int_i2, int_i1) = Sheet1.Cells(int_i2 + 1, int_i1).Value
304 Next int_i2
305 Next int_i1
306 '结束读取输入矩阵
307
308 '读取权值矩阵
309 For int_i1 = 1 To M
310 For int_i2 = 0 To N
311 PARA_12(int_i2, int_i1) = Sheet2.Cells(int_i2 + 5, int_i1).Value 'V
312 Next int_i2
313 Next int_i1
314 For int_i1 = 1 To L
315 For int_i2 = 0 To M
316 PARA_23(int_i2, int_i1) = Sheet2.Cells(int_i2 + 14, int_i1).Value 'M
317 Next int_i2
318 Next int_i1
319 '结束权值距阵赋初值
320
321 Sheet3.Range(Cells(16, 3), Cells(17, 5)).ClearContents
322 time_a = Now()
323 '测试开始时刻
324
325 '辅助测试用时
326 Sheet3.Cells(16, 2) = ""
327 Sheet3.Cells(17, 2) = ""
328 If C_C = 0 Then
329 GoTo PION_100A
330 End If
331 Sheet3.Cells(16, 2) = "循环次数"
332 Sheet3.Cells(17, 2) = C_C
333 For int_CC = 1 To C_C
334 int_k = 0
335 For int_p = 1 To P
336 For int_i1 = 1 To N
337 IN_PUT(int_i1) = PARA_INT(int_i1, int_p)
338 Next int_i1
339 '计算隐层各节点输出
340 For int_i1 = 1 To M
341 para_a1 = -1 * PARA_12(0, int_i1)
342 For int_i2 = 1 To N
343 para_a1 = para_a1 + IN_PUT(int_i2) * PARA_12(int_i2, int_i1)
344 Next int_i2
345 OUT_Y(int_i1) = 1 / (1 + Exp(-para_a1))
346 Next int_i1
347 '结束计算实际隐层各节点输出
348
349 '计算输出层各节点输出
350 For int_i1 = 1 To L
351 para_a1 = -1 * PARA_23(0, int_i1)
352 For int_i2 = 1 To M
353 para_a1 = para_a1 + OUT_Y(int_i2) * PARA_23(int_i2, int_i1)
354 Next int_i2
355 OUT_O(int_i1) = 1 / (1 + Exp(-para_a1))
356 Next int_i1
357 '结束计算输出层各节点输出
358
359 '计算十进制结果
360 para_a1 = 0
361 For int_i1 = 1 To L
362 OUT_O(int_i1) = Round(OUT_O(int_i1))
363 para_a1 = para_a1 + OUT_O(int_i1) * 2 ^ (L - int_i1)
364 Next int_i1
365 '结束计算十进制结果
366 Next int_p
367 Next int_CC
368 '结束辅助测试用时
369 PION_100A:
370
371 int_k = 0
372 For int_p = 1 To P
373 For int_i1 = 1 To N
374 IN_PUT(int_i1) = PARA_INT(int_i1, int_p) '在10个样本中取出一个样本(一列)
375 Next int_i1
376 '计算隐层各节点输出
377 For int_i1 = 1 To M
378 para_a1 = -1 * PARA_12(0, int_i1)
379 For int_i2 = 1 To N
380 para_a1 = para_a1 + IN_PUT(int_i2) * PARA_12(int_i2, int_i1)
381 Next int_i2
382 OUT_Y(int_i1) = 1 / (1 + Exp(-para_a1))
383 Next int_i1
384 '结束计算实际隐层各节点输出
385
386 '计算输出层各节点输出
387 For int_i1 = 1 To L
388 para_a1 = -1 * PARA_23(0, int_i1)
389 For int_i2 = 1 To M
390 para_a1 = para_a1 + OUT_Y(int_i2) * PARA_23(int_i2, int_i1)
391 Next int_i2
392 OUT_O(int_i1) = 1 / (1 + Exp(-para_a1))
393 Next int_i1
394 '结束计算输出层各节点输出
395
396 '输出结果
397 para_a1 = 0
398 For int_i1 = 1 To L
399 OUT_O(int_i1) = Round(OUT_O(int_i1)) '将OUT_O四舍五入,要么是0,要么是1
400 Sheet3.Cells(int_p + 2, int_i1).Value = OUT_O(int_i1) 'sheet3中输出一行
401 para_a1 = para_a1 + OUT_O(int_i1) * 2 ^ (L - int_i1) '将二进制化为十进制
402 Next int_i1
403 Sheet3.Cells(int_p + 2, 6).Value = para_a1
404 '结束输出结果
405
406 If (para_a1 + 1) = int_p Then
407 int_k = int_k + 1
408 End If
409
410 Next int_p
411
412
413 Sheet3.Cells(13, 3).Value = 10 * int_k
414
415 time_b = Now()
416 '测试结束时刻
417
418 Sheet3.Cells(16, 3) = "测试用时"
419 Sheet3.Cells(17, 3) = time_b - time_a
420 Sheet3.Cells(16, 4) = "开始"
421 Sheet3.Cells(17, 4) = time_a
422 Sheet3.Cells(16, 5) = "结束"
423 Sheet3.Cells(17, 5) = time_b
424 '输出测试用时情况
425
426
427 End Function
428 'FIN BB测试程序
BP神经网络代码vba