Федотов В.Х. Нейронные сети в MS Excel

Подождите немного. Документ загружается.

в Excel. Результат не изменился. Установим эту позицию =37 и

вновь выполним вывод в Excel. Результат остался прежним -

каким был при первоначальной установке режимов

прогнозирования и ИПЦ =104,254. Таким образом, этот параметр

не влияет на результаты прогнозирования, а влияет только на

выбор точки тестирования. Это можно использовать для

«точечной» проверки правильности отклика обученной сети

т.п. Убедимся еще раз в этом, выбрав последний режим Тестовое

множество – случайное+последний пример. Количество

последних примеров =1 (Test set – random + last example.

Amount of last examples =1). Результаты остаются прежними.

5. Закроем нейроэмулятор. Загрузим неполное множество

данных – например, исключим обучающую выборку, т.е. первые

32 строки таблицы. Выделим последние 5 строк и загрузим сеть

(не забудьте при этом

снять флажок использования первой

строки таблицы под названия или включите в выделяемый

диапазон и строку с названиями тоже). Установим параметры

тестового множества: число тестовых примеров =1. Всего

примеров должно быть теперь пять. Посмотрим отклик сети и

диаграмму разброса.

Отклик сети (для последних пяти строк) Диаграмма разброса

Видно, что они изменились и соответствуют выборке из пяти

образцов. Образец для прогнозирования по-прежнему выделен

красным цветом. Выводим результаты в Excel и убеждаемся, что

прогноз ИПЦ остался прежним - 104,254.

6. Вновь закроем нейроэмулятор. Загрузим теперь только

один образец – прогнозный, т.е. 37-ю строку таблицы. Выделим

ее, загрузим сеть (сняв флажок использования первой

строки

таблицы под названия). Установим: число тестовых примеров =1.

Всего примеров должно быть также только 1. Посмотрим отклик

сети и диаграмму разброса.

Отклик сети (выбран только один

прогнозный образец)

Диаграмма разброса

Видно, что они опять изменились и соответствуют

единичной выборке (красная точка). Выводим результаты в

Excel. Убеждаемся, что прогноз ИПЦ =104,254 не изменился.

7. Выполним еще одно полезное упражнение. Проведем

прогнозирование на серии из 10 образцов. Предварительно

подготовим их в диапазоне ячеек D39-K48, включая и

предыдущий образец № 37 в строке 39. Целевую колонку (D)

заполним нулями.

D E F G H I J K

39 0 10000000 5000000 1000000 1000000 24030738 3,0 3,0

40 0 13142066 6983885,6 1959332 1813981 24030738 4,3 3,4

41 0 13799688 9423869,6 1576068 1771373 24030738 5,6 5,5

42 0 18674189 5660396,7 1362461 1936663 24030738 4,8 3,5

43 0 11754906 8523457,7 1987036 1891098 24030738 3,0 4,5

44 0 16837455 6379225,2 1285482 1930711 24030738 4,3 4,8

45 0 17218009 5090461,2 1895263 1590744 24030738 5,5 3,2

46 0 19095328 8663570,4 1196230 1390089 24030738 4,6 5,4

47 0 12721941 7147652,3 1630052 1948753 24030738 5,5 5,5

48 0 19120781 8653900,5 1202726 1183707 24030738 5,9 5,2

Закроем нейроэмулятор. Загрузим новое множество данных

для прогнозирования. Выделим последние 10 строк таблицы и

вновь загрузим эмулятор сети (сняв флажок использования

первой строки таблицы под названия). Установим параметры

тестового множества: число тестовых примеров =10, режим

прогнозирования Тестовое множество – блок примеров (Test

set – block of examples. First example of the block =1). Всего

примеров должно быть тоже 10. Посмотрим отклик сети и

диаграмму разброса.

Отклик сети (выбраны последние 10 строк) Диаграмма разброса

8. Сохраним результаты прогнозирования командой Вывод

(Output), задав область вывода в любых свободных ячейках

таблицы Excel в режиме – Вывод всех данных (Output all data).

Увидим точные прогнозные значения. Первое из них совпадает с

полученным ранее.

Вход х2 Вход х6 Выход (ИПЦ) Выход (Прогноз)

Column_2

(Input)

Column_6

(Input)

Column_0

(Output)

Column_0

(Predicted)

5000000 3 0 104,2542606

6983885,554 4,347470311 0 103,9581763

9423869,637 5,59659284 0 103,9435637

5660396,667 4,772265009 0 104,0692262

8523457,748 3,048636395 0 103,9443531

6379225,209 4,292017477 0 103,9820185

5090461,224 5,537619961 0 104,2746903

8663570,361 4,599615397 0 103,9442717

7147652,339 5,456798663 0 103,9561576

8653900,519 5,853652515 0 103,9444449

9. Заметим, что использованная выборка содержала довольно

однородные (близкие) значения. Представляет интерес

посмотреть на отклик сети для менее однородной выборки,

содержащей резкие всплески входных параметров. Поэтому

сформируем и такую выборку в диапазоне ячеек D49-K53.

Целевую колонку (D) заполним нулями.

D E F G H I J K

49 0 16540689 9520488,8 1355508 1651201 24030739 4,0 3,7

50 0 32528341 7272124 2118840 2019657 24030740 6,3 7,4

51 0 25650150 7996142,4 2138891 1264716 24030741 5,7 6,4

52 0 34638898 15231183 2415634 2031378 24030742 8,6 8,8

53 0 32098464 7454629,7 1137506 2194729 24030743 9,2 6,0

Сохраним результаты прогнозирования командой Вывод,

задав область вывода в других свободных ячейках таблицы Excel

в режиме – Вывод всех данных. Как видно из приведенной

ниже таблицы, качественное изменение выборки влияет на

результаты прогнозирования. Это говорит о том, что наш

нейропредсказатель чувствителен к вариации входных

параметров, и его можно считать адекватным.

10. И

наконец сравним нейропрогноз с нейрорегрессией.

Результаты показаны ниже.

Вход х2 Вход х6 Выход

(ИПЦ)

Нейро

прогноз

Column_2

(Input)

Column_6

(Input)

Column_0

(Output)

Column_0

(Predicted)

Нейро

регрес

сия

Откло

нение,

9520488,841 3,969155719 0 103,9434652 113,39 -9,03

7272123,966 6,314321984 0 103,9548892 60,55 -41,75

7996142,398 5,715291998 0 103,9466706 60,32 -41,97

15231182,6 8,634074031 0 103,9432125 34,85 -66,47

7454629,703 9,22298974 0 103,955365 74,75 -28,10

11. Закроем нейропредсказатель без сохранения результатов,

так как нам не нужно изменять параметры обученной сети.

12. Завершим сеанс Excel или продолжим работу с

полученным прогнозом средствами Excel.

Сеть Кохонена

Рассмотрим другую задачу – использование нейронных сетей

для решения задач классификации (кластеризации). В качестве

исходных данных выберем панельные данные по 5 родственным

предприятиям пищевой промышленности Чувашии

(ООО «Вавилон», ОАО «Акконд», ОАО «Новочебоксарская

макаронная фабрика» (НМФ) и др.). Для обучения используем

выборку из 32х5 =160 поквартальных данных за 8 лет,

сформированных на основе бухгалтерской отчетности этих

предприятий.

Сеть анализирует имеющиеся данные и формирует группы

(кластеры) однотипных с точки зрения выбранного нами

критерия образцов. Входы модели – 1 макроэкономический

(индекс потребительских цен - ИПЦ) и 10 микроэкономических

(выручка, себестоимость, рентабельность и др.) показателей

прошлой деятельности.

Алгоритм 6.

Сеть Кохонена вида «5х5»

1. Введем или загрузим исходные данные в Excel следующим

образом (таблица приведена не полностью).

I х1 х2 х3 х4 х5 х6 х7 х8 х9 х10

Объ-

ект

Квар-

тал

ИПЦ

Вы-

руч-

ка

Себе-

стои-

мость

При-

быль

от

реали-

зации

Балан-

совая

при-

быль

Стои-

мость

основ-

ных

фондов

Рен-

таб.

об-

щая

Рен-

таб.

собс-

твен-

ная

Чис-

ло

ра-

бот-

ни-

ков

Сред-

няя

зар-

пла-

та

Теку-

щая

лик-

вид-

но

сть

В-1 132,2 201,84 200,12 1,72 1,91 1561,20 1,2 4,7 14 125,2 0,7

В-2 130,3 206,15 204,13 2,02 2,10 1882,00 1,1 4,3 14 133,5 0,8

В-3 126,6 248,84 245,62 3,22 2,12 1902,64 1,1 4,3 14 145,0 0,8

В-4 115,4 243,19 240,14 2,94 1,08 2024,04 0,5 1,8 14 127,8 0,8

В-5 107,3 440,53 400,11 40,42 30,25 7553,44 4 19,5 21 352,2 0,9

В-6 105,6 484,26 422,13 62,12 36,78 8801,12 4,2 20,4 21 337,8 1,0

В-7 105,7 508,47 445,05 63,42 45,25 8144,66 5,6 27,1 21 320,0 1,0

В-8 104,5 554,50 484,44 67,92 52,05 9158,42 5,7 28,2 21 376,5 1,2

В-9 104 552,75 522,33 30,42 41,22 20156,12 2 12,8 28 387,7 1,1

В-10 103,5 564,30 522,18 42,12 46,78 20553,88 2,3 14,2 28 344,4 1,5

В-11 103,3 675,64 632,22 43,42 43,44 20914,26 2,1 13 29 487,3 1,5

В-12 100,5 700,21 637,12 48,68 39,40 21638,30 1,8 11,3 29 506,4 0,7

В-13 107,6 1272,2 1229,7 42,45 78,24 14612,04 5,4 21 30 1017 0,9

В-14 109,5 1493,4 1432,1 61,28 76,88 15820,06 4,9 19,1 35 1418 2,0

В-15 120,8 1858,1 1792,2 65,88 73,25 19026,42 3,8 15,1 31 1271 1,1

В-16 118 2029,9 1941,4 74,12 60,16 19286,48 3,1 10,7 31 1374 1,8

В-17 113,2 2931,5 2529,1 402,44 367,20 71561,20 5,1 22,6 32 1900 0,9

В-18 111,4 3333,7 2932,4 401,26 375,40 73882,00 5,1 22,4 39 2763 1,4

В-19 111,7 4148,2 3732,3 415,88 386,25 76142,64 5,1 22,4 33 2120 1,1

В-20 100,2 4229,2 3821,5 393,31 429,61 78429,68 5,5 24,2 33 2990 0,8

В-21 105,1 4812,1 4440,2 582,42 486,62 101561,4 4,8 20,6 34 3683 1,9

В-22 105,6 5513,4 4532,2 581,11 532,30 101882,4 5,2 22,4 39 3162 0,7

В-23 106,1 5757,5 4511,1 625,23 588,10 101142,4 5,8 25 39 2807 0,8

В-24 107,2 6562,8 4633,2 655,72 652,73 102336,8 6,4 27,4 45 2942 1,3

Объ-

ект

Квар-

тал

ИПЦ

Вы-

руч-

ка

Себе-

стои-

мость

При-

быль

от

реали-

зации

Балан-

совая

при-

быль

Стои-

мость

основ-

ных

фондов

Рен-

таб.

об-

щая

Рен-

таб.

собс-

твен-

ная

Чис-

ло

ра-

бот-

ни-

ков

Сред-

няя

зар-

пла-

та

Теку-

щая

лик-

вид-

но

сть

В-25 106,1 10433 4742,4 690,23 686,11 101561,4 6,8 22,1 45 2797 1,3

В-26 105 10462 4755,5 703,85 702,30 101882,4 6,9 22,4 44 2468 1,2

В-27 103,4 10400 4727,5 710,25 708,10 101142,4 7 25,4 47 3259 1,5

В-28 103,3 10609 4822,6 724,96 712,73 142943,2 5 26,2 61 2755 1,6

В-29 103,9 10625 4829,6 721,40 712,34 101561,4 7 22,6 58 4078 1,4

В-30 103,8 10631 4832,3 736,80 732,11 101882,4 7,2 22,5 57 3507 1,4

В-31 103,8 10631 4832,3 740,28 738,21 101142,4 7,3 25,1 52 3445 1,6

В-32 103,7 10984 4992,7 764,33 752,57 224401,1 3,4 25,2 52 2970 2,0

А-1 132,2 1160,3 1112,4 108,67 2086,0 11764,94 1,6 5,12 445 943,9 1,7

А-2 130,3 1169,4 1087,1 101,88 1912,0 14908,43 1,4 7,88 445 710,3 1,2

А-3 126,6 1288,2 1239,5 116,78 2458,0 13540,46 1,1 7,59 426 852,0 0,8

…

К-30 103,8 55238 10330 3896,7 4,66 34058,60 1,53 12,59 31 2504 1,5

К-31 103,8 32396 13351 3489,5 4,22 458641,2 9,26 2,48 30 2908 1,3

К-32 103,7 42539 10468 1864,0 4,44 1286884 0,17 15,69 28 2184 1,8

2.Нажмем кнопку с цветной картой Кохонена на панели

инструментов Neural Analysis. В окне Select data source зададим

параметры области данных. Входы – столбцы A-L, обучающие

примеры – строки 2-162. Выделим строку под названия входов –

поставим флажок. Выделим первую колонку под названия

обучающих примеров для последующего анализа результатов.

Нажимаем ОК.

3. Откроется основное окно программы, которое содержит

две

вкладки Project и Results. Перейдем на вкладку Project,

которая позволяет определить и предобработать исходные

данные. Кроме того, с этой вкладки можно сохранить обученную

нейросеть (Save Project) или загрузить сохраненный ранее

проект (Load Project).

4. Определим входы, для чего нажмем на кнопку Create

patterns. В открывшемся окне Select relevant columns выберем в

окне левого списка All columns все входы и

с помощью кнопки

>> переведем их в окно списка Selected. Отметим, что в отличие

от Winnet теперь все данные являются входными и участвуют в

обучении. Корректировку выбранных входов можно провести

кнопками < или << .

5. Проведем нормировку входов методом Mean/Variance.

Нажмем кнопку Normalize и установим в открывшемся окне

Inputs normalization переключатель Mean/Variance в

положение «включено». Нажмем ОК.

6. Часто при анализе используется много входов, имеющих

существенную линейную зависимость друг от друга. В этих

случаях встроенный в пакет Метод Главных Компонент (PCA)

позволяет автоматически существенно понизить размерность

входного пространства

. Для использования этой возможности

включим переключатель Extract principal components.

Подтвердим выбор нажатием кнопки ОК.

В открывшемся графическом окне PC Analyzer можно

контролируемо понизить размерность входов за счет

уменьшения числа учитываемых при обучении главных

компонент без существенной потери информативности. С

помощью соответствующего графика можно оценить потери

информации. Обычно они не должны превышать 10%.

На приведенном графике выбрано 7 главных компонент, а

потери информации оцениваются в 5-7%. Точное значение

составляет 6%, что можно позднее увидеть на вкладке Project.

Дальнейшее уменьшение числа главных компонент до 5

приводит уже к предельной ошибке 10-12%. Критический рост

ошибки происходит при сокращении числа компонент до 2-3 и

менее. Подтвердим выбор нажатием ОК и вернемся в основное

окно.

7. Следующий этап – создание сети-карты Кохонена. Здесь

можно руководствоваться правилом. Кластерную структуру, в

отличие от персептрона, лучше исследовать, когда число ячеек

сравнимо с числом примеров. При этом появятся пустые ячейки,

разграничивающие области данных. Нажмем кнопку Create

Network и перейдем в окно Dialog, где зададим параметры карты

5х5, т.е. число

ячеек по горизонтали и по вертикали равно 5.

Подтвердим выбранную конфигурацию сети нажатием кнопки

ОК. Далее автоматически начинается процесс обучения.

Подождем его окончания (несколько минут).

8. Обучение закончилось. Для анализа результатов перейдем

на вкладку Results. Здесь доступны следующие функции.

• Create New Map - раскраска карты Кохонена по любому

параметру с выбранной степенью градации.

• Cell Description - определение усредненных значений входов для

данного кластера (ячейки) и принадлежащих ему примеров.

• Find Cell - поиск кластера, которому принадлежит данный пример.

• Output - сохранение результатов в книге Excel.

9. Выполним команду Create New Map. Появится запрос

параметров карты. Выберем целевую колонку – ИПЦ, вид

раскраски – среднее и количество цветов - 2. Получим карту

Кохонена вида «5х5», содержащую 25 кластеров.

В окне карты имеется свое подменю, позволяющее

настраивать вид карты, ее цветовое оформление, показывать или

скрывать имена ячеек и отображать соответствие диапазона

целевого параметра цветовой гамме. Размеры кластеров

пропорциональны количеству образцов, отнесенных к каждому

из них. Видно, например, что кластеры А2, D4 и D5 примерно

одинаковы и содержат по 3-5 образцов, а кластеры

А1, А5 и др. –

содержат примерно по 10 образцов. Общая картина говорит о

том, что степень раздробленности все-таки высока и число

кластеров можно уменьшить.

Полученная карта является активной. Убедимся в этом,

щелкнув два раза мышью по первому кластеру (левой верхней

ячейке А1). Открывается окно Cell description анализа средних

значений параметров. Как видно

из следующего рисунка, в

данный кластер попало 9 образцов. Показаны их наименования,

значения параметров для центра кластера и средние значения

параметров по кластеру в целом.

Дополнительно предусмотрена возможность сохранения

изображения карты в формате *.bmp для последующего экспорта

через буфер обмена в документы MS Office. Для этого в окне

карты имеется меню Actions с командой Copy to Bitmap.

Выполнив ее, можно перейти в документ Word и вставить в него

полученную карту командой Вставка.

10. Наиболее важным моментом является то, что программа

позволяет управлять параметрами процесса обучения. Для этого

перейдем к главному меню Program, выберем пункт Preferences

и команду Set Custom. В открывшемся окне Program

preferences на вкладке Параметры обучения сети (Training

parameters) мы увидим используемые по умолчанию параметры

обучения. Первый из них - предельное число итераций (Number

of iteration =2000), второй

– начальная жесткость (Initial stiffness =

=2), третий – шаг (delta =0,01) и четвертый – коэффициент спада

(decay factor =0,001). Эти параметры рекомендуется модифици-

ровать только квалифицированным пользователям. В случае

критического нарушения обучения можно восстановить значения

по умолчанию командой Set Defaults в главном меню.

11. Сохраним нейропроект. Перейдем на вкладку Project и

выполним команду Save Project под именем, например,

Kohonen_Vavilon_5_5.kmp

12. Теперь можно закрыть окно программы Kohonen Map.

Дальнейший анализ удобнее проводить статистическими

методами в Excel.

13. Сохраним только результаты работы нейросети в Excel в

диапазоне ячеек А1-… с помощью команды Output (для

компактности данные показаны несколько иначе).

A B C … A B C …

B C … A B C … A B C

1 В-1 E 5 33 А-1 E 5 65 Н-1 E 5 97 Аг-1 E 5 129 К-1 E 4

2 В-2 E 5 34 А-2 E 5 66 Н-2 E 5 98 Аг-2 E 4 130 К-2 E 5

3 В-3 E 5 А-3 E 5 Н-3 E 4 Аг-3 E 4 К-3 E 4

4 В-4 E 5 А-4 E 5 Н-4 E 4 Аг-4 E 4 К-4 E 4

5 В-5 D 1 А-5 A 1 Н-5 E 1 Аг-5 E 3 К-5 D 2

6 В-6 D 1 А-6 A 1 Н-6 B 1 Аг-6 E 2 К-6 D 1

7 В-7 D 1 А-7 A 1 Н-7 E 1 Аг-7 E 1 К-7 C 2

8 В-8 D 1 А-8 A 1 Н-8 D 1 Аг-8 E 1 К-8 C 2

9 В-9 E 1 А-9 A 1 Н-9 E 2 Аг-9 E 1 К-9 E 1

10 В-10 E 2 А-10 A 1 Н-10 E 1 Аг-10 E 3 К10 D 1

11 В-11 E 2 А-11 A 1 Н-11 E 3 Аг-11 E 3 К11 E 2

12 В-12 E 1 А-12 A 1 Н-12 E 3 Аг-12 E 3 К12 E 1

13 В-13 D 1 А-13 A 1 Н-13 E 3 Аг-13 D 2 К13 D 2

14 В-14 D 3 А-14 A 2 Н-14 D 3 Аг-14 D 2 К14 D 1

15 В-15 D 5 А-15 A 2 Н-15 D 5 Аг-15 E 5 К15 D 5

16 В-16 D 4 А-16 A 2 Н-16 D 4 Аг-16 E 5 К16 D 4

17 В-17 C 1 А-17 A 3 Н-17 C 5 Аг-17 E 2 К17 D 4

18 В-18 B 2 А-18 A 3 Н-18 C 5 Аг-18 E 3 К18 C 3