Macro che ricerca combinazioni che danno un valore

[kar64]

Buon pomeriggio a tutti.
Ho un problema che non so se risolvibile tramite formule o macro (complessa).

Nel mio file ci sono degli importi ordinati per data.

Un esempio:

Codice: Seleziona tutto

Colonna A   Colonna B   Colonna C
10/03/2023   -292,15       70   
13/03/2023   292,15        70   
13/03/2023   -19.482,00    71   
14/03/2023   36,32         107
14/03/2023   19.482,00     71   
14/03/2023   -22.064,00    107
14/03/2023   19.489,20     107
15/03/2023   2.538,48      107
15/03/2023   -15.268,11    72   
16/03/2023   15.268,11     72   
16/03/2023   -8,80         73   
17/03/2023   8,80          73   
17/03/2023   -7.980,74     108
20/03/2023   12,21         108
20/03/2023   -3.000,00      
20/03/2023   7.968,75      108
20/03/2023   -0,22         108
21/03/2023   -1.006,11     106   
21/03/2023   3.000,00      74   
22/03/2023   1.006,11      106   
23/03/2023   -59,16        75   
24/03/2023   59,16         75   


La mia necessità è quella di "accomunare" tutte le combinazioni che nell'arco temporale di sette giorni diano somma algebrica uguale a ZERO.

Nell'esempio, in colonna "C" ho fatto questo lavoro manualmente (ho segnato con un numero progressivo le coppie che soddisfacevano questa condizione).

Ma le combinazioni possono essere date anche dalla somma algebrica di più valori come ad esempio quelle indicate dai progressivi 107 e 108.

Secondo voi è possibile ?

Grazie

[Marius44]

Ciao
Non credi sarebbe il caso di condividere il file (che tu hai e che noi dovremmo costruire, magari sbagliando)?

Ciao,
Mario

[kar64]

Scusami, grazie.

https://www.mediafire.com/file/6maxyh87rudv1x9/Ricerca.xlsx/file

[Anthony47]

Nel tuo file ci sono 254 date: vuoi fare (A) 254 ricerche, ognuna con i dati della DATA di partenza piu' i 6 giorni successivi oppore (B) vuoi considerare solo i dati di 1 settimana alla volta (da Lun a Dom)?
Ovviamente i risultati nel caso A i risultati sono tantitantitantissimi (ad esempio valutando solo le prime 7 date si ottengono 252 possibili soluzioni; proiettati su 254 date finiamo sulle 9000 combinazioni); nel caso B sono invece solo tante.
Per fare un esempio, il periodo dal 14-20 mar 2023 (quello che hai coperto dalle combinazioni 107 e 108) offre 15 possibili soluzioni.

Insomma il mio dubbio e' che rischi di passare da 700 dati puntuali a 8-10mila combinazioni, e non so che utilita' puoi trarre da questa massa di informazioni.
Sei quindi sicuro che quello che hai chiesto e' quello che ti serve? Puoi spiegare il problema da risolvere, così vediamo se ci sono magari altre vie per risolverlo?

[kar64]

Buongiorno Anthony,
la finalità della richiesta è questa:

la sommatoria degli importi può comportare uno sbilancio finale (nel caso della lista è -13,72).

Lo scopo è quello di "cercare" gli importi che si azzerano lasciando in vita quelli che "in linea presuntiva" compongano lo sbilancio del conto.

Nel caso specifico la penultima riga riporta proprio la cifra dello sbilancio (ma è solo un caso, ovviamente) e, quindi, in linea di principio tutti gli importi della colonna dovrebbero azzerarsi tra loro.

So che le combinazioni da creare per verificare le quadrature sono fattoriali, possiamo eventualmente restringere l'intervallo di 7 giorni a 3 giorni, ma per intervallo va inteso l'intervallo di "date", quindi gruppi di più righe (che non conosco a priori).

Grazie

[Anthony47]

Continuo ad avere dubbi sui risultati concretamente ottenibili, comunque vediamo di fare qualche passo avanti...

Per cominciare ho ripreso un precedente lavoro, dove avevo pubblicato un "modello" per cercare i possibili addendi di un elenco che fornissero una cera somma; vedi viewtopic.php?f=26&t=110098

Partendo da quel file ne ho pubblicato un secondo, CercaCombinaz_V1-308_PnN.xlsm, che puo' essere scaricato seguendo le informazioni presenti in questo messaggio: viewtopic.php?f=26&t=110098&p=666439#p666439

Usero' quel file per calcolare le possibili combinazioni; i risultati invece li accumuliamo all'interno di Foglio2 (che supponiamo in partenza sia vuoto).
Tutto questo viene fatto dalla Sub FeedCercaZero, corrispondente a questo codice:

Codice: Seleziona tutto

Sub FeedCercaZero()
Dim deSh As Worksheet, starD As Date
Dim I As Long, J As Long, MaxI As Long, NextO As Long
'
Sheets("Foglio1").Select
Set deSh = Sheets("Foglio2")            '<<< Il foglio per i risultati
'
MaxI = Cells(Rows.Count, "A").End(xlUp).Row
For I = 2 To MaxI
    If Cells(I, 1) <> "" And Cells(I, 1) <> starD Then
        starD = Cells(I, 1)
    'Cerca il fine "settimana":
        For J = 1 To 100
        'Debug.Print Cells(I + J, 1).Value
            If Cells(I + J, 1).Value > (starD + 6) Or (I + J) > MaxI Then Exit For
        Next J
        Debug.Print I, starD, J
    'Registra la data di partenza;
        NextO = deSh.Cells(Rows.Count, "B").End(xlUp).Row + 2
        deSh.Cells(NextO, 1) = Cells(I, 1).Value
    'Popola l'area dei dati e avvia la ricerca:
       Workbooks("CercaCombinaz_V1-308_PnN.xlsm").Sheets("Lavoro").Range("B2:B199").ClearContents
        Cells(I, 1).Resize(J, 1).Select                                     'Solo per debug
        Workbooks("CercaCombinaz_V1-308_PnN.xlsm").Sheets("Lavoro").Range("B2").Resize(J, 1).Value = Cells(I, 2).Resize(J, 1).Value
        Application.Run "'CercaCombinaz_V1-308_PnN.xlsm'!CercaComb308P", "True", 0.001
        Debug.Print , Range("BAA1").End(xlToLeft).Column
    'Copia i risultati in Foglio2:
        Range(Range("B1"), Range("BAA1").End(xlToLeft)).Resize(J + 1).Copy deSh.Cells(NextO, 2)
        Application.CutCopyMode = False
        ThisWorkbook.Activate
        DoEvents
    End If
Next I
MsgBox ("Analisi completata")
End Sub


Il codice va inserito in un "Modulo Standard" del progetto vba del tuo file

Operativamente:
-scarica il file CercaCombinaz_V1-308_PnN.xlsm
-inserisci il codice della Sub FeedCercaZero nel tuo file
-apri il file CercaCombinaz_V1-308_PnN.xlsm
-quando sei pronto lancia la Sub FeedCercaZero; poiche' la macro deve fare un "tot" di simulazioni (una per ogni data del tuo file) e ogni simulazione prevede la verifica di tutte le possibili combinazioni tra gli N addendi, il tutto portera' via "qualche minuto"

Il mio file di test e' byKAR64_C40222_Ricerca.xlsm; puo' essere scaricato qui: https://www.dropbox.com/scl/fi/nz7dmi86 ... tkpmj&dl=0

Contiene gia' la Sub FeedCercaZero e mostra in Foglio2 i risultati della mia prova.
Come si leggono i risultati di Foglio2:
-in colonna A c'e' la data di partenza
-in colonna B ci sono i dati relativa ai 7 giorni che partono dalla data di colonna A
-in colonna C, in verticale, sono marcati con "1" le combinazioni che danno luogo alla somma=0

Sono state esaminate 252 date (di partenza) e in totale sono state trovate 4351 combinazioni (abbastanza piu' bassa della stima che avevo fatto ieri sera).

Vedi che cosa si capisce da questo mio discorso e dal file demo che puoi scaricare, e dai un feedback.

[kar64]

Innanzitutto GRAZIE!!

Ho elaborato il risultato ottenuto (nella considerazione che tutte le righe che risultano "vuote", quindi senza neppure un "1" non hanno trovato accoppiamenti nei sette giorni. Mi confermi che ho interpretato bene il risultato ?)

Allego il file.
https://www.mediafire.com/file/sl0n6i1mfz9i80u/Cartel2.xlsm/file


In effetti i tuoi dubbi erano fondati.
Se la somma dell'elenco iniziale dava uno sbilancio di 13,74, la somma dei non accoppiati da un risultato enorme (oltre 115.000) a significare che gli accoppiamenti, forse, vanno oltre i sette giorni ...

Considerato che non ci ha impiegato neppure tanto tempo, chissà se si potesse modificare la routine ampliando il periodo da 7 a 15 (o renderlo parametrabile, ma non è detto che si ottenga la soluzione in ogni caso).

Comunque grazie per l'interessamento.

Ciao

[Anthony47]

Mi pare che nel nuovo file hai riportato ogni voce che, in Foglio2 del file da me elaborato, erano presenti con "nessun match".
Hai pero' trascurato che un certo dato potrebbe comparire in 7 analisi; esempio: i dati del 5 gennaio fanno parte della analisi che parte da1-gen, poi da 2-gen, poi da 3-gen, poi da 4-gen e infine che parte dal 5-gen. Quindi nel tuo tentativo di trovare "i single" dovrebbe bastare 1 match in tutto il periodo di 7 gg perche' il dato venga considerato accoppiato. Questo ridimensionerebbe drasticamente i numeri da considerare. Vedi se riesci a calcolare un nuovo elenco alla luce di questa informazione.

Tanto per farti stare preoccupato, mi pare che il criterio su cui tu stia ragionando potrebbe funzionare se il disallineamento fosse causato da 1 solo errore, mentre diventerebbe molto piu' incasinato se gli errori fossero 2, o 3 o...

[kar64]

Buonasera Anthony.
Non mi è chiaro il concetto che mi hai esposto.

Ti riallego il file con la macro che ho scritto (non guardare alla purezza e velocità del codice, per piacere .... )per definire l'assegnazione del "Si" o "No".

https://www.mediafire.com/file/6zokm2uu16bljrf/Cartel1.xlsm/file

In pratica segno con "No" tutte le righe che non hanno nessun "1" sotto le "x".

Di sicuro interpreto male il risultato.
Potresti spiegarmi ?

Grazie.

[Anthony47]

Se guardi il tuo file, mi marchi "No" il 2.63 dell'analisi fatta con data iniziale 2/01/2023; ma quel 2.63 ha gia' fatto il paio con il -2.63 dell'analisi del giorno 1/01/2023. Quindi non e' No

Ho rivisto la macro che analizza; ora mette i dati da colonna D, e in colonna C ti inserisco il numero riga del dato analizzato.
Nelle colonne A e B (che sono libere) puoi sommare il conteggio degli 1 di ogni numero riga, dovresti cosi' trovare i veri "orfani"
Io ho messo in A4 e B4 rispettivamente le formule

Codice: Seleziona tutto

=SE(B4<>"";SOMMA.SE($C$4:$C$1000;C4;$B$4:$B$1000);"DD")

=SE(C4<>"";CONTA.SE(F4:AGZ4;1);"")

Poi ho filtrato per colonna A=0 e mi vengono 169 orfani... sempre un bel numero

Il file scaricabile al solito link ora e' aggiornato con la nuova Sub FeedCercaZeroB che sostituisce la Sub FeedCercaZeroOld

[kar64]

Buongiorno Anthony,
ho rielaborato il file con le macro da te modificate e aggiunto le formule.

In effetti le 169 righe orfane non portano al risultato finale atteso.

Sicuramente il problema è quello che avevi paventato tu: ci saranno importi che invece di chiudersi in una relazione 1:n (che le macro intercettano) si chiuderanno in relazioni n:m ....

In ogni caso non posso che ringraziarti per il tempo dedicato alla problematica.

Alla prossima.
Ciao