Все разъяснения, пояснения......в приложении адрес файла найдёте тут
Ссылка Здесь
Тупо копируйте всю ниже расположенную хрень вставляете в модуль и можете уже образовывать свои первые в жизни кластеры в имеющихся данных.
Для критериев которые не представлены в приложении можно по аналогии что-то сделать...тут в основном показана методология..... Если нужны кластеры по другим параметрам то надо в начале найти центры кластеров для простоты работы алгоритмов (можно разными способами это делать в представленных функциях центры искались с помощью К-средних)
Function Bclaster1(ByRef rah As Range, Optional VolatileOn As Boolean = True) As Variant
Application.Volatile VolatileOn
Dim VOZ As Range
Dim i As Long
Dim smas(7) As Single
For Each VOZ In rah.Cells
smas(i) = VOZ.Value
i = i + 1
Next VOZ
Arrast = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
Dim Vxod(15, 7) As Double
Dim Wix(15, 3) As Double
Dim SRR As Double
Dim Otvet(3) As Double
Vxod(0, 0) = 1.25
Vxod(1, 0) = 2.0288
Vxod(2, 0) = 2.25074
Vxod(3, 0) = 1.1487
Vxod(4, 0) = 1.4299
Vxod(5, 0) = 2.1028
Vxod(6, 0) = 1.6762
Vxod(7, 0) = 1.4966
Vxod(8, 0) = 1.4656
Vxod(9, 0) = 1.4579
Vxod(10, 0) = 1.2202
Vxod(11, 0) = 1.07
Vxod(12, 0) = 1.3399
Vxod(13, 0) = 1.3153
Vxod(14, 0) = 1.3
Vxod(15, 0) = 3.19
Vxod(0, 0) = 1.25
Vxod(1, 0) = 2.0288
Vxod(2, 0) = 2.25074
Vxod(3, 0) = 1.1487
Vxod(4, 0) = 1.4299
Vxod(5, 0) = 2.1028
Vxod(6, 0) = 1.6762
Vxod(7, 0) = 1.4966
Vxod(8, 0) = 1.4656
Vxod(9, 0) = 1.4579
Vxod(10, 0) = 1.2202
Vxod(11, 0) = 1.07
Vxod(12, 0) = 1.3399
Vxod(13, 0) = 1.3153
Vxod(14, 0) = 1.3
Vxod(15, 0) = 3.19
Vxod(0, 2) = 0.90068
Vxod(1, 2) = 1.106
Vxod(2, 2) = 1.5457
Vxod(3, 2) = 0.8842
Vxod(4, 2) = 0.949
Vxod(5, 2) = 1.4304
Vxod(6, 2) = 1.6762
Vxod(7, 2) = 1.0126
Vxod(8, 2) = 0.9613
Vxod(9, 2) = 0.9907
Vxod(10, 2) = 0.8511
Vxod(11, 2) = 0.943
Vxod(12, 2) = 0.9818
Vxod(13, 2) = 1.0015
Vxod(14, 2) = 1.0511
Vxod(15, 2) = 2.235
Vxod(0, 3) = 1.7086
Vxod(1, 3) = 1.5317
Vxod(2, 3) = 0.96439
Vxod(3, 3) = 1.45885
Vxod(4, 3) = 1.5462
Vxod(5, 3) = 1.267
Vxod(6, 3) = 1.404
Vxod(7, 3) = 1.698
Vxod(8, 3) = 1.0661
Vxod(9, 3) = 1.7481
Vxod(10, 3) = 1.708
Vxod(11, 3) = 1.7577
Vxod(12, 3) = 1.441
Vxod(13, 3) = 1.5826
Vxod(14, 3) = 2.29
Vxod(15, 3) = 0.9967
Vxod(0, 4) = 2.1289
Vxod(1, 4) = 1.2665
Vxod(2, 4) = 1.3368
Vxod(3, 4) = 1.111
Vxod(4, 4) = 2.1706
Vxod(5, 4) = 2.0706
Vxod(6, 4) = 1.357
Vxod(7, 4) = 3.2485
Vxod(8, 4) = 1.2794
Vxod(9, 4) = 1.2215
Vxod(10, 4) = 1.3358
Vxod(11, 4) = 1.5945
Vxod(12, 4) = 1.2306
Vxod(13, 4) = 1.8365
Vxod(14, 4) = 1.35
Vxod(15, 4) = 1.5754
Vxod(0, 5) = 0.77625
Vxod(1, 5) = 1.1095
Vxod(2, 5) = 1.28907
Vxod(3, 5) = 0.9793
Vxod(4, 5) = 1.28187
Vxod(5, 5) = 0.8887
Vxod(6, 5) = 1.0243
Vxod(7, 5) = 0.7703
Vxod(8, 5) = 0.9238
Vxod(9, 5) = 1.3
Vxod(10, 5) = 1.0626
Vxod(11, 5) = 0.8781
Vxod(12, 5) = 1.6952
Vxod(13, 5) = 0.9347
Vxod(14, 5) = 1.2428
Vxod(15, 5) = 1.1611
Vxod(0, 6) = 1.7017
Vxod(1, 6) = 1.03686
Vxod(2, 6) = 0.951
Vxod(3, 6) = 0.93238
Vxod(4, 6) = 1.1162
Vxod(5, 6) = 1.5947
Vxod(6, 6) = 0.9389
Vxod(7, 6) = 2.2127
Vxod(8, 6) = 1.026
Vxod(9, 6) = 0.7255
Vxod(10, 6) = 0.9759
Vxod(11, 6) = 1.1127
Vxod(12, 6) = 0.8745
Vxod(13, 6) = 1.3756
Vxod(14, 6) = 0.957759
Vxod(15, 6) = 1.0708
Vxod(0, 7) = 1.0279
Vxod(1, 7) = 1.855
Vxod(2, 7) = 1.6137
Vxod(3, 7) = 1.8883
Vxod(4, 7) = 1.2936
Vxod(5, 7) = 1.1438
Vxod(6, 7) = 1.4752
Vxod(7, 7) = 1.0169
Vxod(8, 7) = 1.2299
Vxod(9, 7) = 1.2633
Vxod(10, 7) = 1.5143
Vxod(11, 7) = 1.1657
Vxod(12, 7) = 1.9391
Vxod(13, 7) = 1.822
Vxod(14, 7) = 1.96178
Vxod(15, 7) = 1.61435
Wix(0, 0) = 0
Wix(1, 0) = 1
Wix(2, 0) = 2
Wix(3, 0) = 3
Wix(4, 0) = 4
Wix(5, 0) = 5
Wix(6, 0) = 6
Wix(7, 0) = 7
Wix(8, 0) = 8
Wix(9, 0) = 9
Wix(10, 0) = 10
Wix(11, 0) = 11
Wix(12, 0) = 12
Wix(13, 0) = 13
Wix(14, 0) = 14
Wix(15, 0) = 15
Wix(0, 1) = 0.229
Wix(1, 1) = 0.5706
Wix(2, 1) = 0.72
Wix(3, 1) = 0.4921
Wix(4, 1) = 0.4181
Wix(5, 1) = 0.4351
Wix(6, 1) = 0.6068
Wix(7, 1) = 0.1747
Wix(8, 1) = 0.5
Wix(9, 1) = 0.4766
Wix(10, 1) = 0.382
Wix(11, 1) = 0.3034
Wix(12, 1) = 0.4919
Wix(13, 1) = 0.4037
Wix(14, 1) = 0.4404
Wix(15, 1) = 0.8604
Wix(0, 2) = 0.2109
Wix(1, 2) = 0.2507
Wix(2, 2) = 0.1907
Wix(3, 2) = 0.33158
Wix(4, 2) = 0.2241
Wix(5, 2) = 0.2922
Wix(6, 2) = 0.2321
Wix(7, 2) = 0.2289
Wix(8, 2) = 0.2832
Wix(9, 2) = 0.3
Wix(10, 2) = 0.344
Wix(11, 2) = 0.3065
Wix(12, 2) = 0.288
Wix(13, 2) = 0.2484
Wix(14, 2) = 0.2924
Wix(15, 2) = 0.093
Wix(0, 3) = 0.56
Wix(1, 3) = 0.1786
Wix(2, 3) = 0.08923
Wix(3, 3) = 0.1763
Wix(4, 3) = 0.3578
Wix(5, 3) = 0.2727
Wix(6, 3) = 0.16099
Wix(7, 3) = 0.5963
Wix(8, 3) = 0.2167
Wix(9, 3) = 0.2233
Wix(10, 3) = 0.2737
Wix(11, 3) = 0.39
Wix(12, 3) = 0.22
Wix(13, 3) = 0.3478
Wix(14, 3) = 0.2671
Wix(15, 3) = 0.0465
For i = 0 To 15
SRR = 0
For j = 0 To 7
SRR = SRR + (Vxod(i, j) - smas(j)) ^ 2
Next j
Arrast(i) = SRR
Next i
For k = 0 To 3
Otvet(k) = Wix((WorksheetFunction.Match(WorksheetFunction.Min(Arrast), Arrast, 0) - 1), k)
Next k
Bclaster1 = Otvet
End Function
Function Bclaster2(ByRef rah As Range, Optional VolatileOn As Boolean = True) As Variant
Application.Volatile VolatileOn
Dim VOZ As Range
Dim i As Long
Dim smas(5) As Single
For Each VOZ In rah.Cells
smas(i) = VOZ.Value
i = i + 1
Next VOZ
Arrast = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
Dim Vxod(15, 5) As Double
Dim Wix(15, 3) As Double
Dim SRR As Double
Dim Otvet(3) As Double
Vxod(0, 0) = 0.499333
Vxod(1, 0) = 0.348022
Vxod(2, 0) = 0.24798
Vxod(3, 0) = 0.2724637
Vxod(4, 0) = 0.41496
Vxod(5, 0) = 0.676086
Vxod(6, 0) = 0.631076
Vxod(7, 0) = 0.698138
Vxod(8, 0) = 0.463985
Vxod(9, 0) = 0.359247
Vxod(10, 0) = 0.569037
Vxod(11, 0) = 0.853551
Vxod(12, 0) = 0.483216
Vxod(13, 0) = 0.288557
Vxod(14, 0) = 0.5626649
Vxod(15, 0) = 0.276149
Vxod(0, 1) = 0.371333
Vxod(1, 1) = 0.3898305
Vxod(2, 1) = 0.4181
Vxod(3, 1) = 0.399758
Vxod(4, 1) = 0.4257679
Vxod(5, 1) = 0.2123188
Vxod(6, 1) = 0.209201
Vxod(7, 1) = 0.139627
Vxod(8, 1) = 0.324395
Vxod(9, 1) = 0.1819
Vxod(10, 1) = 0.22594
Vxod(11, 1) = 0.06994
Vxod(12, 1) = 0.212139
Vxod(13, 1) = 0.2761
Vxod(14, 1) = 0.182497
Vxod(15, 1) = 0.208046
Vxod(0, 2) = 0.129333
Vxod(1, 2) = 0.2621469
Vxod(2, 2) = 0.33391
Vxod(3, 2) = 0.32777
Vxod(4, 2) = 0.15927
Vxod(5, 2) = 0.11159
Vxod(6, 2) = 0.15972
Vxod(7, 2) = 0.162234
Vxod(8, 2) = 0.211619
Vxod(9, 2) = 0.45884
Vxod(10, 2) = 0.20502
Vxod(11, 2) = 0.0765027
Vxod(12, 2) = 0.304644
Vxod(13, 2) = 0.435323
Vxod(14, 2) = 0.254837
Vxod(15, 2) = 0.5158
Vxod(0, 3) = 0.147555
Vxod(1, 3) = 0.203107
Vxod(2, 3) = 0.336505
Vxod(3, 3) = 0.098792
Vxod(4, 3) = 0.43714
Vxod(5, 3) = 0.35072
Vxod(6, 3) = 0.3946759
Vxod(7, 3) = 0.112588
Vxod(8, 3) = 0.196109
Vxod(9, 3) = 0.161839
Vxod(10, 3) = 0.696304
Vxod(11, 3) = 0.257377
Vxod(12, 3) = 0.351483
Vxod(13, 3) = 0.525186
Vxod(14, 3) = 0.15985
Vxod(15, 3) = 0.1991379
Vxod(0, 4) = 0.311555
Vxod(1, 4) = 0.472599
Vxod(2, 4) = 0.1782
Vxod(3, 4) = 0.268357
Vxod(4, 4) = 0.281285
Vxod(5, 4) = 0.168357
Vxod(6, 4) = 0.390625
Vxod(7, 4) = 0.20811
Vxod(8, 4) = 0.10962
Vxod(9, 4) = 0.17759
Vxod(10, 4) = 0.14191
Vxod(11, 4) = 0.3142076
Vxod(12, 4) = 0.186768
Vxod(13, 4) = 0.249067
Vxod(14, 4) = 0.416666
Vxod(15, 4) = 0.373563
Vxod(0, 5) = 0.540888
Vxod(1, 5) = 0.3242937
Vxod(2, 5) = 0.48529
Vxod(3, 5) = 0.63285
Vxod(4, 5) = 0.281569
Vxod(5, 5) = 0.480917
Vxod(6, 5) = 0.214699
Vxod(7, 5) = 0.679299
Vxod(8, 5) = 0.694269
Vxod(9, 5) = 0.660569
Vxod(10, 5) = 0.161785
Vxod(11, 5) = 0.428415
Vxod(12, 5) = 0.4617486
Vxod(13, 5) = 0.225746
Vxod(14, 5) = 0.42348
Vxod(15, 5) = 0.427298
Wix(0, 0) = 0
Wix(1, 0) = 1
Wix(2, 0) = 2
Wix(3, 0) = 3
Wix(4, 0) = 4
Wix(5, 0) = 5
Wix(6, 0) = 6
Wix(7, 0) = 7
Wix(8, 0) = 8
Wix(9, 0) = 9
Wix(10, 0) = 10
Wix(11, 0) = 11
Wix(12, 0) = 12
Wix(13, 0) = 13
Wix(14, 0) = 14
Wix(15, 0) = 15
Wix(0, 1) = 0.541
Wix(1, 1) = 0.4317
Wix(2, 1) = 0.4275
Wix(3, 1) = 0.4352
Wix(4, 1) = 0.4
Wix(5, 1) = 0.5836
Wix(6, 1) = 0.3837
Wix(7, 1) = 0.66
Wix(8, 1) = 0.5232
Wix(9, 1) = 0.41
Wix(10, 1) = 0.307
Wix(11, 1) = 0.697
Wix(12, 1) = 0.4588
Wix(13, 1) = 0.2222
Wix(14, 1) = 0.5357
Wix(15, 1) = 0.325
Wix(0, 2) = 0.2691
Wix(1, 2) = 0.28058
Wix(2, 2) = 0.2753
Wix(3, 2) = 0.2962
Wix(4, 2) = 0.2563
Wix(5, 2) = 0.2249
Wix(6, 2) = 0.31
Wix(7, 2) = 0.2033
Wix(8, 2) = 0.2582
Wix(9, 2) = 0.3538
Wix(10, 2) = 0.1905
Wix(11, 2) = 0.1866
Wix(12, 2) = 0.2668
Wix(13, 2) = 0.2797
Wix(14, 2) = 0.2472
Wix(15, 2) = 0.325
Wix(0, 3) = 0.1898
Wix(1, 3) = 0.2877
Wix(2, 3) = 0.2971
Wix(3, 3) = 0.2685
Wix(4, 3) = 0.3429
Wix(5, 3) = 0.1915
Wix(6, 3) = 0.306
Wix(7, 3) = 0.1364
Wix(8, 3) = 0.2185
Wix(9, 3) = 0.237
Wix(10, 3) = 0.502
Wix(11, 3) = 0.1162
Wix(12, 3) = 0.2743
Wix(13, 3) = 0.498
Wix(14, 3) = 0.217
Wix(15, 3) = 0.3498
For i = 0 To 15
SRR = 0
For j = 0 To 5
SRR = SRR + (Vxod(i, j) - smas(j)) ^ 2
Next j
Arrast(i) = SRR
Next i
For k = 0 To 3
Otvet(k) = Wix((WorksheetFunction.Match(WorksheetFunction.Min(Arrast), Arrast, 0) - 1), k)
Next k
Bclaster2 = Otvet
End Function
Function Bclaster3(ByRef rah As Range, Optional VolatileOn As Boolean = True) As Variant
Application.Volatile VolatileOn
Dim VOZ As Range
Dim i As Long
Dim smas(1) As Single
For Each VOZ In rah.Cells
smas(i) = VOZ.Value
i = i + 1
Next VOZ
Arrast = Array(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
Dim Vxod(15, 1) As Double
Dim Wix(15, 3) As Double
Dim SRR As Double
Dim Otvet(3) As Double
Vxod(0, 0) = 1.12228
Vxod(1, 0) = 1.33857
Vxod(2, 0) = 1.952
Vxod(3, 0) = 0.78814
Vxod(4, 0) = 1.122
Vxod(5, 0) = 1.9895
Vxod(6, 0) = 0.8099
Vxod(7, 0) = 2.2275
Vxod(8, 0) = 2.4529
Vxod(9, 0) = 1.2232
Vxod(10, 0) = 1.6815
Vxod(11, 0) = 1.1632
Vxod(12, 0) = 1.8528
Vxod(13, 0) = 1.5497
Vxod(14, 0) = 0.8965
Vxod(15, 0) = 1.4451
Vxod(0, 1) = 2.5049
Vxod(1, 1) = 2.0174
Vxod(2, 1) = 2.3444
Vxod(3, 1) = 0.9907
Vxod(4, 1) = 1.181
Vxod(5, 1) = 0.8807
Vxod(6, 1) = 1.4444
Vxod(7, 1) = 1.7573
Vxod(8, 1) = 1.1644
Vxod(9, 1) = 1.6047
Vxod(10, 1) = 1.7319
Vxod(11, 1) = 0.7784
Vxod(12, 1) = 1.279
Vxod(13, 1) = 0.9464
Vxod(14, 1) = 1.9455
Vxod(15, 1) = 1.2976
Wix(0, 0) = 0
Wix(1, 0) = 1
Wix(2, 0) = 2
Wix(3, 0) = 3
Wix(4, 0) = 4
Wix(5, 0) = 5
Wix(6, 0) = 6
Wix(7, 0) = 7
Wix(8, 0) = 8
Wix(9, 0) = 9
Wix(10, 0) = 10
Wix(11, 0) = 11
Wix(12, 0) = 12
Wix(13, 0) = 13
Wix(14, 0) = 14
Wix(15, 0) = 15
Wix(0, 1) = 0.171
Wix(1, 1) = 0.2896
Wix(2, 1) = 0.3622
Wix(3, 1) = 0.4325
Wix(4, 1) = 0.4363
Wix(5, 1) = 0.6942
Wix(6, 1) = 0.3591
Wix(7, 1) = 0.63095
Wix(8, 1) = 0.843
Wix(9, 1) = 0.396
Wix(10, 1) = 0.4349
Wix(11, 1) = 0.4688
Wix(12, 1) = 0.6461
Wix(13, 1) = 0.5869
Wix(14, 1) = 0.296
Wix(15, 1) = 0.5189
Wix(0, 2) = 0.2434
Wix(1, 2) = 0.2278
Wix(2, 2) = 0.2519
Wix(3, 2) = 0.2629
Wix(4, 2) = 0.3358
Wix(5, 2) = 0.2184
Wix(6, 2) = 0.3356
Wix(7, 2) = 0.2143
Wix(8, 2) = 0.093
Wix(9, 2) = 0.2419
Wix(10, 2) = 0.2825
Wix(11, 2) = 0.3264
Wix(12, 2) = 0.2275
Wix(13, 2) = 0.2536
Wix(14, 2) = 0.2527
Wix(15, 2) = 0.2867
Wix(0, 3) = 0.5855
Wix(1, 3) = 0.4826
Wix(2, 3) = 0.3858
Wix(3, 3) = 0.3045
Wix(4, 3) = 0.2279
Wix(5, 3) = 0.087
Wix(6, 3) = 0.3054
Wix(7, 3) = 0.1547
Wix(8, 3) = 0.6395
Wix(9, 3) = 0.3623
Wix(10, 3) = 0.2825
Wix(11, 3) = 0.2047
Wix(12, 3) = 0.1264
Wix(13, 3) = 0.1595
Wix(14, 3) = 0.4513
Wix(15, 3) = 0.1943
For i = 0 To 15
SRR = 0
For j = 0 To 1
SRR = SRR + (Vxod(i, j) - smas(j)) ^ 2
Next j
Arrast(i) = SRR
Next i
For k = 0 To 3
Otvet(k) = Wix((WorksheetFunction.Match(WorksheetFunction.Min(Arrast), Arrast, 0) - 1), k)
Next k
Bclaster3 = Otvet
End Function
Function Clbuk(ByRef rah As Range, Optional VolatileOn As Boolean = True) As Variant
Application.Volatile VolatileOn
Set DANK = rah
If (DANK.Cells(1, 1) > 1) And (DANK.Cells(1, 1) <= 1.1) Then
Outsig = Array(0, 0.95071754, 0.045089359)
ElseIf (DANK.Cells(1, 1) > 1.1) And (DANK.Cells(1, 1) <= 1.17) Then
Outsig = Array(1, 0.895747593, 0.082150576)
ElseIf (DANK.Cells(1, 1) > 1.17) And (DANK.Cells(1, 1) <= 1.25) Then
Outsig = Array(2, 0.830878922, 0.150826378)
ElseIf (DANK.Cells(1, 1) > 1.25) And (DANK.Cells(1, 1) <= 1.35) Then
Outsig = Array(3, 0.775296465, 0.166493)
ElseIf (DANK.Cells(1, 1) > 1.35) And (DANK.Cells(1, 1) <= 1.46) Then
Outsig = Array(4, 0.691113592, 0.233028264)
ElseIf (DANK.Cells(1, 1) > 1.46) And (DANK.Cells(1, 1) <= 1.59) Then
Outsig = Array(5, 0.626258705, 0.257140469)
ElseIf (DANK.Cells(1, 1) > 1.59) And (DANK.Cells(1, 1) <= 1.75) Then
Outsig = Array(6, 0.597720673, 0.243692625)
ElseIf (DANK.Cells(1, 1) > 1.75) And (DANK.Cells(1, 1) <= 1.95) Then
Outsig = Array(7, 0.523138692, 0.300353872)
ElseIf (DANK.Cells(1, 1) > 1.95) And (DANK.Cells(1, 1) <= 2.2) Then
Outsig = Array(8, 0.485470749, 0.306978945)
ElseIf (DANK.Cells(1, 1) > 2.2) And (DANK.Cells(1, 1) <= 2.5) Then
Outsig = Array(9, 0.424331744, 0.310352368)
ElseIf (DANK.Cells(1, 1) > 2.5) And (DANK.Cells(1, 1) <= 2.9) Then
Outsig = Array(10, 0.3629406, 0.315335213)
ElseIf (DANK.Cells(1, 1) > 2.9) And (DANK.Cells(1, 1) <= 3.45) Then
Outsig = Array(11, 0.301373764, 0.30971869)
ElseIf (DANK.Cells(1, 1) > 3.45) And (DANK.Cells(1, 1) <= 4.3) Then
Outsig = Array(12, 0.259633122, 0.280269263)
ElseIf (DANK.Cells(1, 1) > 4.3) And (DANK.Cells(1, 1) <= 5.6) Then
Outsig = Array(13, 0.196705062, 0.265044984)
ElseIf (DANK.Cells(1, 1) > 5.6) And (DANK.Cells(1, 1) <= 8.2) Then
Outsig = Array(14, 0.153583812, 0.207455484)
ElseIf (DANK.Cells(1, 1) > 8.2) Then
Outsig = Array(15, 0.084612993, 0.178969138)
Else
Outsig = "BGDAN"
End If
Clbuk = Outsig
End Function
Edited by Bambuk, 24 February 2014 - 01:52.