Condividi:        

Excel 2003 e le somme impossibili

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Excel 2003 e le somme impossibili

Postdi femario72 » 28/10/12 02:39

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
femario72
Newbie
 
Post: 4
Iscritto il: 28/10/12 02:29

Sponsor
 

Re: Excel 2003 e le somme impossibili

Postdi CANAPONE » 28/10/12 06:07

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
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 430
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Excel 2003 e le somme impossibili

Postdi femario72 » 28/10/12 14:31

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
femario72
Newbie
 
Post: 4
Iscritto il: 28/10/12 02:29

Re: Excel 2003 e le somme impossibili

Postdi CANAPONE » 28/10/12 15:13

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
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 430
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Excel 2003 e le somme impossibili

Postdi Anthony47 » 28/10/12 16:59

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

Ciao
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel 2003 e le somme impossibili

Postdi femario72 » 28/10/12 19:11

CANAPONE ha scritto: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
Newbie
 
Post: 4
Iscritto il: 28/10/12 02:29

Re: Excel 2003 e le somme impossibili

Postdi femario72 » 28/10/12 19:16

Anthony47 ha scritto: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
femario72
Newbie
 
Post: 4
Iscritto il: 28/10/12 02:29

Re: Excel 2003 e le somme impossibili

Postdi wallace&gromit » 29/10/12 15:26

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.
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: Excel 2003 e le somme impossibili

Postdi ricky53 » 29/10/12 16:53

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.
Dice il vecchio saggio provare e riprovare è l'unica strada per imparare

Più chiara è la vostra spiegazione
Più immediata sarà la nostra soluzione


. . . . . . . . . .
S.O. W10; Office 2003-10-13-16-19
Avatar utente
ricky53
Utente Senior
 
Post: 4565
Iscritto il: 11/04/09 19:29
Località: Italia

Re: Excel 2003 e le somme impossibili

Postdi wallace&gromit » 29/10/12 17:36

Dice un altro vecchio saggio: leggere sempre fino in fondo!
chiedo venia :oops:
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!
Office2016 + 2019 su win11
Avatar utente
wallace&gromit
Utente Senior
 
Post: 2174
Iscritto il: 16/01/12 14:21

Re: Excel 2003 e le somme impossibili

Postdi Anthony47 » 30/10/12 00:07

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.
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel 2003 e le somme impossibili

Postdi Flash30005 » 30/10/12 21:18

Anthony47 ha scritto: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... :roll:
ma il risultato è garantito :)

ciao
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: Excel 2003 e le somme impossibili

Postdi Anthony47 » 31/10/12 13:37

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
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Excel 2003 e le somme impossibili

Postdi Flash30005 » 31/10/12 16:10

@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
Flash
Win10 + Office 2010 Ita
"Fotografica" al servizio dell'immagine
Avatar utente
Flash30005
Moderatore
 
Post: 8517
Iscritto il: 27/09/07 11:44
Località: Roma +o-

Re: Excel 2003 e le somme impossibili

Postdi Anthony47 » 01/11/12 01:23

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
Avatar utente
Anthony47
Moderatore
 
Post: 19196
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "Excel 2003 e le somme impossibili":


Chi c’è in linea

Visitano il forum: Nessuno e 37 ospiti