Excel 2003 e le somme impossibili

[femario72]

Salve a tutti sto cercando una soluzione ad un mio problema e vi chiedo se esiste una soluzione con le formule di Excel:
Ho la seguente colonna di numeri:
€ 15.657,86
€ 106.961,31
€ 24.174,52
€ 9.417,45
€ 81.370,04
€ 5.694,47
€ 18.924,05
€ 8.193,38
€ 16.931,65
€ 30.157,11
€ 13.073,07
€ 13.159,97
€ 78.005,41
€ 35.956,60
€ 13.432,94
€ 15.621,70
€ 15.199,55
€ 9.819,73
€ 27.643,23
€ 6.480,91
€ 128.174,58
€ 242.131,02
€ 7.175,20
€ 224.422,94
€ 17.913,81
€ 95.345,17
Mi servirebbe di sapere quali sono gli addendi (tra quelli che si trovano in colonna) che sommati tra di loro mi danno una somma pari ad € 758.120,57.
Ho provato ma non riesco a risolvere, vi chiedo se gentilmente mi sapete indicare se esiste la formula che esegue un'operazione del genere.
Grazie,
Ciao

[CANAPONE]

Ciao,

€ 15.657,86
€ 106.961,31
€ 24.174,52
€ 81.370,04
€ 16.931,65
€ 13.159,97
€ 9.819,73
€ 6.480,91
€ 128.174,58
€ 242.131,02
€ 17.913,81
€ 95.345,17

Spero sia d'aiuto

[femario72]

Ciao, grazie sei stato gentilissimo a rispondermi così velocemente. Ti posso chiedere come hai fatto? Esiste una formula per questi calcoli?
Ciao
Grazie di nuovo

[CANAPONE]

Ciao,

le formule purtroppo non sono d'aiuto.

Mi sono fatto prestare un Excel meno datato del mio (uso anch'io Excel 2003) ed ho messo alla prova una macro che ho trovato in rete.

Qui:

http://www.tushar-mehta.com/excel/templ ... mbinations

Si potesse adattare la macro anche al 2003: è veramente ganza.


Saluti da Firenze

[Anthony47]

Ciao femario72, benvenuto nel forum.
Anche noi italici avevamo fatto qualcosa, prova a guardare questa discussione:
viewtopic.php?f=26&t=94323#p539509

Ciao

[femario72]

Ciao,

le formule purtroppo non sono d'aiuto.

Mi sono fatto prestare un Excel meno datato del mio (uso anch'io Excel 2003) ed ho messo alla prova una macro che ho trovato in rete.

Qui:

http://www.tushar-mehta.com/excel/templ ... mbinations

Si potesse adattare la macro anche al 2003: è veramente ganza.


Saluti da Firenze

Ok grazie mille di nuovo, saluti da Padova
Ciao

[femario72]

Ciao femario72, benvenuto nel forum.
Anche noi italici avevamo fatto qualcosa, prova a guardare questa discussione:
viewtopic.php?f=26&t=94323#p539509

Ciao

Ok grazie mille ma per me è un pò arabo nel senso che sono un utente molto molto base rispetto voi, non è il mio campo l'informatica e purtroppo quando ho bisogno si vede....
Grazie
Ciao

[wallace&gromit]

Ciao a tutti, mi intrometto nel discorso
@Anthony: per fare funzionare la macro ho dovuto "semplificare" il MsgBox Rispo = ...., prendendo solo la prima riga, altrimenti mi segnalava un errore e andava in debug.
@Canapone: la macro che hai postato funziona anche con excel 2003.
Però rispetto a quella di Anthony ha qualche grave difetto: i risultati sono poco leggibili: viene riportata la somma (non serve, visto che è impostata dall'utente), la data di elaborazione (non so che vantaggio abbia) e l'ordine dei valori nel range invece dei valori stessi (la lettura del risultato non è per niente facile).
Ogni volta bisogna selezionare il range di partenza e non è pratico.
Inoltre non cancella i dati delle ricerche precedenti e ciò crea un po' un casino.
Però introduce la possibilità di numeri negativi, il che non è male.

[ricky53]

Ciao W&G,
anch'io quando mi sono dedicato a fare il "tester" della proposta di Anthony ho avuto lo stesso problema.
Anthony la ha risolto il 25/01/12 13:37 apportando una modifica al ciclo "For/Next", vedi quanto scritto in quella discussione.

[wallace&gromit]

Dice un altro vecchio saggio: leggere sempre fino in fondo!
chiedo venia
La macro postata da Canapone l'hai testata?
L'unico vantaggio che citavo (comprende i numeri negativi) in realtà lo fa egregiamente anche la macro di Anthony, che è esteticamente più valida!

[Anthony47]

Per favore non diciamo che il codice di Tushar Meta (un guru riconosciuto) e' peggiore di quello di Anthony, perche' altrimenti si arrabbia Zeus in cielo; il primo (e non unico) pregio e' la sua velocita': evidentemente usa un algoritmo logico nella risoluzione del problema, mentre io faccio solo uso di forza bruta per testare tutte le possibili combinazioni a gruppi di 2, di 3, di 4 ... fino a N-1.
Appena ho una settimana libera provo a smontarla per vedere se, passato il mal di testa, ci capisco qualcosa. Se nel frattempo qualcuno ha in mente un algoritmo matematico da adottare e me lo volesse suggerire...

Comunque ho elaborato una versione 1.2 della macro, con un messaggio iniziale piu' esteso e piu' documentativo; ad esempio viene riepilogato il valore che si andra' a cercare, quante operazioni saranno effettuate (in funzione del limite impostato nel codice) , con quali raggruppamenti, e quale percentuale del numero max di combinazioni che l' elenco potrebbe generare e' coperto da questi numeri.
Questo messaggio rende evidente come, con un elenco a 26 elementi pubblicato da femario, fermarsi a 2Milioni di combinazioni e' una inezia riepetto alle 67Milioni di combinazioni possibili.

E infatti per ottenere un risultato valido bisogna impostare almeno 30M combinazioni, che consente di calcolare fino a gruppi di 12 elementi... che sul mio pc richiedono pero' 7minuti e mezzo...
E per trovarli "tutti" bisogna impostarne 60M che richiedono 16 minuti.

La nuova macro:

Codice: Seleziona tutto

Dim VArr, WkArr(), WkIndex(), I As Integer, J As Integer, maxCol As Long, FlExit As Boolean
Dim Kappa As Integer, WResult As String, TgVal, LastLev As Long, II As Long, NElem As Integer

Sub CercaComb()
'Rev. 1.2
'
Dim Col2H As Double, Col2K As Double
'
maxCol = 2         '<<<  N° max di match
MaxCombin = 100000000   '<<<  N° max di combinazioni che saranno testate
'
FlExit = False
If maxCol > Columns.Count Then maxCol = Columns.Count - 3
TgVal = (InputBox("Valore target?"))
TgVal = Val(Replace(TgVal, ",", "."))    'Gestisce decimale sia "punto" che "virgola"
VArr = Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp))
NElem = UBound(VArr, 1)
ReDim WkArr(NElem): ReDim WkIndex(NElem)
Range("B1").Resize(NElem + 1, Columns.Count - 1).Clear
'
LastLev = 3
For I = 1 To NElem - 1
'modificato per calcolare anche il tot delle combinazioni (uso di Col2K e II)
    Col2H = Col2H + Evaluate("FACT(" & NElem & ")/FACT(" & I & ")/FACT(" & NElem - I & ")")
    If Col2H <= MaxCombin Then Col2K = Col2H - 1: II = I
    If Col2H <= MaxCombin Then Gruppidi = Gruppidi & " " & I
Next I


Rispo = MsgBox("Il valore target e': " & TgVal _
        & vbCrLf & "Impostato max combinazioni: " & Round(MaxCombin / 1000000, 1) & " Milioni" _
        & vbCrLf & "N° di combinazioni massime che saranno testate: " & Col2K - _
        Evaluate("FACT(" & NElem & ")/FACT(" & I & ")/FACT(" & NElem - I & ")") & vbCrLf _
        & "(Combinazione di " & NElem & " elementi a gruppi di " & Gruppidi & ")" & vbCrLf _
        & "Massimo " & maxCol & " risultati" & vbCrLf _
        & "(Corrispondente al " & Int(Col2K / Col2H * 100) & "% delle possibili combinazioni)" & vbCrLf _
        & vbCrLf & "OK per procedere, CANCEL per annullare e modificare i parametri", vbOKCancel)
       

If Rispo = vbCancel Then Exit Sub
sTimer = Timer
'
If TgVal = 0 Then GoTo ZeroVal
'
For LastLev = 1 To II
    For J = 0 To NElem
        WkArr(J) = "": WkIndex(J) = ""
    Next J
    Call Recur(1, NElem, 1)
DoEvents
Next LastLev
If FlExit = True Then mexflex = "(stop per limite di colonne massime da riportare)"
ZeroVal:
MsgBox ("Completato in " & Int(Timer - sTimer) & " Secondi" & vbCrLf & "Rilevati " & _
    Application.WorksheetFunction.CountIf(Range("1:1"), "x") _
    & " match" & vbCrLf & mexflex)
End Sub

Sub Recur(ByVal Iniz As Integer, ByVal Final As Integer, ByVal myLevel As Integer)
Dim myI As Integer, myK As Integer
For myI = Iniz To Final
    WkArr(myLevel) = VArr(myI, 1)
    WkIndex(myLevel) = myI
    If myLevel = LastLev Then
aaa = Application.WorksheetFunction.Sum(WkArr())

    If Round(Application.WorksheetFunction.Sum(WkArr()), 3) = Round(TgVal, 3) And FlExit = False Then
        Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = "x"
        mycol = Cells(1, Columns.Count).End(xlToLeft).Column
        If mycol > maxCol Then FlExit = True
        For myK = 1 To LastLev
            Cells(WkIndex(myK) + 1, mycol) = 1 'WkIndex(myK)
        Next myK
    End If
Else
    Call Recur(myI + 1, NElem, myLevel + 1)
    End If
If FlExit = True Then Exit For
Next myI
End Sub


Va inserita in un Modulo vuoto, in quell' ordine; poi si lancera' la Sub CercaComb

Nei relativi esperimenti ho anche verificato che esistono due combinazioni che producono il risultato cercato (758120,57):

Codice: Seleziona tutto

   15657,86   1      -   
  106961,31   1      1   
   24174,52   1      1   
    9417,45   -      1   
   81370,04   1      1   
    5694,47   -      1   
   18924,05   -      -   
    8193,38   -      1   
   16931,65   1      -   
   30157,11   -      -   
   13073,07   -      1   
   13159,97   1      1   
   78005,41   -      1   
    35956,6   -      1   
   13432,94   -      -   
    15621,7   -      -   
   15199,55   -      -   
    9819,73   1      1   
   27643,23   -      1   
    6480,91   1      -   
  128174,58   1      -   
  242131,02   1      1   
     7175,2   -      1   
  224422,94   -      -   
   17913,81   1      -   
   95345,17   1      1   


Ciao a tutti.

[Flash30005]

Se nel frattempo qualcuno ha in mente un algoritmo matematico da adottare e me lo volesse suggerire...

Un valido "algoritmo" potrebbe essere l'adattamento ad un processo logico ma sicuro di valutazione dello scostamento in più o in meno.
se dovessi indovinare un numero tra 1 e 100
con il sistema delle combinazioni ho ben 100 possibilità (a meno che il numero non è di molto inferiore a 100)
con il sistema casuale potrebbe centrare il numero al primo colpo come al 100° quindi non lineare

mentre se ho la possibilità di sapere che il numero che prendo in esame è maggiore o minore della meta allora posso ottenere il risultato in sole 7 scelte puntando sempre sulla metà di ciò del range rimanente sapendo se ho superato il limite oppure no
es: da 1 a 100
provo 50 <<<<<<<<<<<<<<<<<<<<<<<<<<< 1ª scelta
se è troppo piccolo provo 75
se maggiore provo 25
ammettiamo che sia più picolo
del numero Nx, quindi opto per 75 <<<<<<<< 2ª scelta
quindi 75 se maggiore del risultato
opto per 62 <<<<<<<<<<<<<<<<<<<<<<<<<<< 3ª scelta
se 62 è minore di Nx
prendo in considerazione 68 (62+75)/2 <<<<4ª scelta
se minore di Nx opto per 71 <<<<<<<<<<<<5ª scelta
se maggiore di Nx rimangono 69 e 70
ammettiamo che scelgo 69 <<<<<<<<<<<<<<6ª scelta
o è corretto oppure non rimane che
70 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<7ª scelta

Adesso si tratta di applicare questo metodo con n numeri prefissati
dei quali non si sa quanti dovranno comparire...
ma il risultato è garantito

ciao

[Anthony47]

Eh eh, Flash.
Certo se avessi combinazioni 2-a-2 o 3-a-3 sarebbe abbastanza facile lavorare in questo modo; anche se il poco tempo speso per riprogrammare la macro non sara' mai compensato dal tempo recuperato in tutte le simulazioni fatte al mondo, trattandosi di operazioni che si chiudono in pochi millisecondi (se gruppi di 2 o di 3, o anche di 5 o 6).
Trasferire il ragionamento quando simulo i gruppo 10-a-10 comincia mi riesce piu' difficile; ma non trascuro il suggerimento, perche' forse mi consente di "dimezzare" sempre il numero di cicli.
Ci lavorero' a mente piu' lucida...

Ciao

[Flash30005]

@Anthony
Leggendo il tuo post precedente e vedendo che possono esserci più possibilità
bisognerebbe sapere se l'esigenza è quella di valutarle tutte perché se così bisogna "scansionare" tutte le combinazioni possibili
altrimenti pensavo anche al fatto di concentrarsi sui centesimi ovvero sul 7 del valore da ricercare
anche la somma di tutti i valori in elenco dà 7 centesimi ma chiaramente essendo un valore elevato si potrebbe pensare di togliere tutte le combinazioni dei valori (a gruppi) che forniscano 10 centesimi come somma fino a giungere alla combinazione voluta, poi valutare se continuare a cercare ancora altre combinazioni valide.

ciao

[Anthony47]

Questa tecnica sarebbe inutile, perche' il problema non sono i valori da sommare ma il numero di somme da eseguire; insomma il tempo necessario per fare una verifica sui centesimi e' uguale a quella che serve per controllare sul valore intero.

Ciao