Funzione calcolo combinatorio
Come l’acqua cheta, che a lungo andare rompe i ponti, ho operato tenacemente in silenzio ottenendo un notevole risultato.
Intervenendo con correzioni, modifiche ed aggiustamenti di una interessantissima macro creata dal prof. Terzaghi, (http://www.terzaghi.it/excel/faq/risposte/15.htm),
sono riuscito a realizzare quanto era nelle tue aspettative.
Questo è il codice da inserire in un modulo:
‘==========================
Function comb_x2(c As Range) As Double
'Dim parziale As Double
'Dim i As Integer
'Dim j As Integer
'Dim k As Integer
'Dim l As Integer
'Dim m As Integer
'Dim n As Integer
'Dim o As Integer
'Dim p As Integer
'Dim q As Integer
'Dim r As Integer
'Dim NRighe As Integer
'Dim NColonne As Integer
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 2 Then
comb_x2 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 1
For j = i + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value
Next
Next
comb_x2 = parziale
End Function
Function comb_x3(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 3 Then
comb_x3 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 2
For j = i + 1 To NRighe - 1
For k = j + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value
Next
Next
Next
comb_x3 = parziale
End Function
Function comb_x4(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 4 Then
comb_x4 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 3
For j = i + 1 To NRighe - 2
For k = j + 1 To NRighe - 1
For l = k + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value
Next
Next
Next
Next
comb_x4 = parziale
End Function
Function comb_x5(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 5 Then
comb_x5 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 4
For j = i + 1 To NRighe - 3
For k = j + 1 To NRighe - 2
For l = k + 1 To NRighe - 1
For m = l + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value
Next
Next
Next
Next
Next
comb_x5 = parziale
End Function
Function comb_x6(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 5 Then
comb_x6 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 5
For j = i + 1 To NRighe - 4
For k = j + 1 To NRighe - 3
For l = k + 1 To NRighe - 2
For m = l + 1 To NRighe - 1
For n = m + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value * c.Cells(n, 1).Value
Next
Next
Next
Next
Next
Next
comb_x6 = parziale
End Function
Function comb_x7(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 5 Then
comb_x7 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 6
For j = i + 1 To NRighe - 5
For k = j + 1 To NRighe - 4
For l = k + 1 To NRighe - 3
For m = l + 1 To NRighe - 2
For n = m + 1 To NRighe - 1
For o = n + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value * c.Cells(n, 1).Value * c.Cells(o, 1).Value
Next
Next
Next
Next
Next
Next
Next
comb_x7 = parziale
End Function
Function comb_x8(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 5 Then
comb_x8 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 7
For j = i + 1 To NRighe - 6
For k = j + 1 To NRighe - 5
For l = k + 1 To NRighe - 4
For m = l + 1 To NRighe - 3
For n = m + 1 To NRighe - 2
For o = n + 1 To NRighe - 1
For p = o + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value * c.Cells(n, 1).Value * c.Cells(o, 1).Value * c.Cells(p, 1).Value
Next
Next
Next
Next
Next
Next
Next
Next
comb_x8 = parziale
End Function
Function comb_x9(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 5 Then
comb_x9 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 8
For j = i + 1 To NRighe - 7
For k = j + 1 To NRighe - 6
For l = k + 1 To NRighe - 5
For m = l + 1 To NRighe - 4
For n = m + 1 To NRighe - 3
For o = n + 1 To NRighe - 2
For p = o + 1 To NRighe - 1
For q = p + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value * c.Cells(n, 1).Value * c.Cells(o, 1).Value * c.Cells(p, 1).Value * c.Cells(q, 1).Value
Next
Next
Next
Next
Next
Next
Next
Next
Next
comb_x9 = parziale
End Function
Function comb_x10(c As Range) As Double
NColonne = c.Columns(c.Columns.Count).Column - c.Column + 1
NRighe = c.Rows(c.Rows.Count).Row - c.Row + 1
If NColonne <> 1 Or NRighe < 5 Then
comb_x10 = CVErr(1)
End If
parziale = 0
For i = 1 To NRighe - 9
For j = i + 1 To NRighe - 8
For k = j + 1 To NRighe - 7
For l = k + 1 To NRighe - 6
For m = l + 1 To NRighe - 5
For n = m + 1 To NRighe - 4
For o = n + 1 To NRighe - 3
For p = o + 1 To NRighe - 2
For q = p + 1 To NRighe - 1
For r = q + 1 To NRighe
parziale = parziale + c.Cells(i, 1).Value * c.Cells(j, 1).Value * c.Cells(k, 1).Value * c.Cells(l, 1).Value * c.Cells(m, 1).Value * c.Cells(n, 1).Value * c.Cells(o, 1).Value * c.Cells(p, 1).Value * c.Cells(q, 1).Value * c.Cells(r, 1).Value
Next
Next
Next
Next
Next
Next
Next
Next
Next
Next
comb_x10 = parziale
End Function ‘==========================
E più sotto l’allegato.
Ciao
Mario
p.s.: Alla prima vincita sostanziosa, sia io che Aurelio (ciao) saremo ovviamente tuoi ospiti per una vacanza spero!?!?!?!?
. .
Se insisti e resisti
raggiungi e conquisti.