[excel2007] Dimensioni massime file in KB

[ricky53]

Ciao,
non sono un "lottista" però con un file di esempio e con un numero piccolo di dati (dell'ordine delle centinaia) forse posso provare a buttare giù qualcosa.

COMMENTO: Anthony e Flash sono molto più bravi di me in materia

[Cele79]

Ciao!
nelle celle da H2 a Ak2 ci metti questi numeri 10 20 30 40 50 60 partendo da H2
In C3 ti dovrebbe uscire scritto che i numeri sono 6
In C4 puoi cambiare come gli vuoi combinare se metti per esempio 3
in C2 ti dovrebbe uscire che le combinazioni sono 20.
Ti ringrazio tanto

[ricky53]

Ciao,
manca qualcosa, infatti tu hai scritto

In C3 ti dovrebbe uscire scritto che i numeri sono 6
...
in C2 ti dovrebbe uscire che le combinazioni sono 20.

Come si ottengono i valori in queste due celle?
Hai delle formule o ... ?

Sarebbe utile un file di esempio

[Cele79]

ciao!
come faccio ad inviare il file cosi lo vedete?
in questo file pero c'è una parte che si aggiorna da un'altro file
succede qualcosa?

[ricky53]

Ciao,
allora invia entrambi i file, ovviamente, senza dati riservati !!!

Utilizza QUESTO LINK

[Cele79]

Questo è il link del file
[url]
http://www.filedropper.com/xmacrocombinazioni
[/url]
Okkio che non ho bloccato nessuna cella!
Buon divertimento e non sgridatemi per certe formule ma sono un principiante!

[Cele79]

Ho fatto tutto bene per farvi vedere il file?

[Cele79]

Ciao!
Siete riusciti a provare la tabella?
Una macro riesce a fare quello che chiedo o mi devo rassegnare?
Grazie!

[Anthony47]

Mosso a compassione per tanti MB sciupati ho adattato la vecchia macro che calcola le combinazioni di N elementi a gruppi di K che avevo pubblicato qui: viewtopic.php?p=584179#p584171
La nuova versione consente di indicare (nel listato della macro):
-l' eventuale indirizzo di una tabella (disposta in orizzontale) contenente il valore degli elementi da combinare (la precedente versione combinava 1, 2, 3, etc; ora posso dichiarare una tabella di elementi); se la prima cella della tabella e' vuota si usera' la sequenza 1, 2, 3, 4, ...
-la posizione in cui si trova il numero totale di elementi e la numerosita' dei gruppi (prima era fisso in B1 e B2)
-la posizione della tabella in cui saranno elencate le combinazioni calcolate (prima era fissa in A4)

La macro inoltre inserisce automaticamente in colonne I, O e P le formule per un numero di righe pari alle combinazioni calcolate, copiandole dalla riga 7

In questo modo, le dimensioni del file pubblicato hanno avuto questo andamento:
-valore iniziale, 3.81 MByte
-inserendo il calcolo delle combinazioni a gruppi di 2 per 19 elementi, 250 KByte
-inserendo il calcolo delle combinazioni a gruppi di 5 per 19 elementi, 951 KByte
-inserendo il calcolo delle combinazioni a gruppi di 5 per 25 elementi, 3.36 MByte
Immagino che il file possa essere salvato nella dimensione inferiore, per essere poi espanso (un foglio alla volta?) all' occorrenza.

Il codice completo della nuova macro:

Codice: Seleziona tutto

Public col(100), r, n, nr As Long, Col2() As Variant

Function comb2(k)
'by Anthony47; Variante che lavora con Col2()
col(k) = col(k - 1)
While col(k) < n - r + k
    col(k) = col(k) + 1
    If k < r Then
        comb2 (k + 1)
    Else
        nr = nr + 1
        For I = 1 To r
            Col2(nr - 1, I - 1) = col(I)
            'Cells(nr, i) = col(i)
        Next
    End If
Wend
End Function


Sub CombAnth()
'by Anthony47
Dim combArr(), I As Long, J As Long, curCalc
Dim myCombList As String, myMembri As String, myGroup As String, myDest As String
'
'Se M1 e' vuoto si combinano numeri interi da 1 a N
myCombList = "M2"               '<<< La cella dove comincia l' elenco delle voci da Combinare
myMembri = "C3"                 '<<< La cella che contiene il numero di valori da combinare
myGroup = "C4"                  '<<< La cella che contiene il numero di elementi per ogni gruppo
myDest = "J6"                   '<<< La cella da dove sara' creato l' elenco combinatorio
'
curCalc = Application.Calculation
Application.Calculation = xlManual
'
If Range(myCombList) <> "" Then
ReDim combArr(1 To 101)
    For I = 0 To 100
        If Range(myCombList).Offset(0, I) <> "" Then
            combArr(I + 1) = Range(myCombList).Offset(0, I).Value
        Else
            ReDim Preserve combArr(1 To I)
            Exit For
        End If
    Next I
End If

col2h = Evaluate("FACT(" & myMembri & ")/FACT(" & myGroup & ")/FACT(" & myMembri & "-" & myGroup & ")")
ReDim Col2(col2h, Range(myGroup) - 0)
'Ih = 1: Iv = 1
Range(myDest).Resize(Rows.Count - Range(myDest).Row - 1, 5).ClearContents   '<<<*** Vedi testo
Range("I8:P8").Resize(Rows.Count - 9, 8).ClearContents
nr = 0
k = 1
r = Range(myGroup)
n = Range(myMembri)
'[g1] = Timer
comb2 (k)
'
If UBound(combArr, 1) < 100 Then
    For I = LBound(Col2, 1) To UBound(Col2, 1)
        For J = LBound(Col2, 2) To UBound(Col2, 2)
            If Not IsEmpty(Col2(I, J)) Then Col2(I, J) = combArr(Col2(I, J))
        Next J
    Next I
End If
Range("I7:P7").Resize(col2h, 8).FillDown
Range(myDest).Resize(col2h, Range(myGroup)) = Col2
'[g2] = Timer
ReDim Col2(1, 1)
Application.Calculation = curCalc
Calculate
End Sub

Va inserito in un modulo a se' stante, e la riga Public deve essere rigorosamente in testa a tutto.

Va mandata in esecuzione la Sub CombAnth()

Nel codice suddetto c' e' da tenere a mente una limitazione: poiche' nel foglio di Cele79 l' area in cui vengono elencate le combinazioni e' contornato da altri dati, l' azzeramento iniziale viene fatto in modo fisso su una larghezza di 5 colonne (istruzione marcata <<<***, non potendo fare affidamento su metodi quali il calcolo della CurrentRegion; tenetelo presente se il codice viene esato un altri contesti.

Il file modificato e' disponibile qui:
http://rapidshare.com/share/05C754BCC92 ... 62F7FEF2D1

Ciao

[Cele79]

Ciao.
Ho provato la macro è funziona molto bene è ha ridotto di molto le dimensioni del file!
Ora vorrei chiedervi se è possibile farla partire in automatico.
Quando io apro il foglio deve gia partire e al modificare le celle C3 e C4.
Sempre se è possibile!
grazie!

[Anthony47]

Quel file ha anche problemi di tempi di ricalcolo, io non gli aggiungerei macro che partono da sole.

Comunque se vuoi puoi inserire una Call CombAnth in una macro di Workbook_Open o di Worksheet_Change che monitorizzi se il Change e' avvenuto in C4; quanto al cambiamento in C3, esso e' prodotto da formule e quindi non e' intercettabile; va cercata la/le modifica/che che alla fine modificano C4.

Ciao

[Cele79]

Ciao!
In che senso ha problemi di ricalcolo?
Potresti farmeli notare cosi provo a correggerli?
Per il calcolo della macro con il cambiamento di C4 mi devi far capire cosa devo fare perche non so da dove iniziare sempre che non chieda troppo!
Grazie

[Cele79]

Ciao Anthony.
Sino a quanti numeri posso combinare con la macro che hai fatto?
Se supero i 32-33 numeri mi resta fisso caricando e il file mi dice che non risponde!
grazie!

[Anthony47]

Il problema non e' la macro che calcola le combinazioni, ma il resto del foglio che richiede il suo tempo perche' le formule possano essere ricalcolate... E' quello a cui alludevo quando dicevo "Quel file ha anche problemi di tempi di ricalcolo".

Creando una situazione con 14950 combinazioni (es 26 * 4) la macro impiega circa 19 sec per completarsi, di cui 0.75 per copiare e calcolare le formule e 17.5 sec solo per il ricalcolo finale del foglio.
Con 33649 combinazioni (es 23 * 5) i tempi sono rispettivamente 47Sec, 2.7Sec e 42Sec.

Se elimini tutte le formule e ripeti la macro i tempi sono dell' ordine di 1-1.5 secondi; per calcolare le combinazioni di 35 elementi a gruppi di 5 (per un totale di circa 324mila combinazioni) la macro viene completata in circa 7 secondi; per 658mila combinazioni (40 * 5) impiega 13 secondi.

Non so valutare le tue formule, quindi non so se sono migliorabili; vedo numerose formule in forma di matrice, che per definizione sono abbastanze esigenti in termini di cpu, e numerose formule con funzioni "volatili", che invece sono "molto" esigenti in termini di cpu.

Per qualche spunto su come migliorare i tempi di elaborazione potresti guardare questo aricolo Microsoft: http://msdn.microsoft.com/en-us/library ... 12%29.aspx

Ciao

[Cele79]

Ciao!
Ho capito!
Mi potresti fare un elenco delle funzioni "volatili" perche cercando in internet non ho capito quali sono!
grazie!

[Anthony47]

Come detto nell' aricolo che ti ho linkato: Scarto, Adesso, Oggi, Casuale, Cella, Indiretto, Indice (*), Rif.Riga (*), Rif.Colonna (*)
Le funzioni marcate * sono dichiarate Volatili da Microsoft ma apparentemente non lo sono (che vuol dire che lo sono in situazioni particolari non documentate).
Una funzione "volatile" viene ricalcolata ad ogni ricalcolo sul workbook, mentre una funzione normale viene ricalcolata solo quando cambiano i suoi "predecessori". Questo significa che qualsiasi formula che ha un "predecessore" (anche lontano) contenente una funzione volatile diventera' a sua volta volatile e avra' bisogno di essere ricalcolata in continuazione; questo significa (tra l' altro) che potrebbero essere necessari piu' cicli di ricalcolo per arrivare a un risultato stabile.

Ora per prova creati un file che abbia poche centinaia di righe di combinazioni; seleziona AX7 (una cella contenente Scarto); premi il tasto "Individua dipendenti" 5 volte (Menu /Formule, gruppo Verifica formule); vedrai le frecce che si saranno propagate praticamente alla maggioranza delle celle (e se pigi ancora una volta la ragnatela si allarghera' ancora, ma i tempi di esecuzione dell' operazione cominciano a diventare pesanti; io ho contato 9 livelli di dipendenza in tutto). Questo significa che la maggioranza di formule si ricalcolano in continuazione, compreso le formule inserite in colonne O:P; il risultato sono tempi di ricalcolo che aumentano esponenzialmente in funzione di quante combinazioni sono state inserite.

La cura? Non conoscendo gli obiettivi di quei calcoli non ne ho idea. Qualche macro qua e la sicuramente possono alleggerire, ma e' una stima che dovresti approfondire.

Ciao

[Cele79]

ciao Anthony.
La macro va bene ma ho trovato un problema!
Se i numeri da combinare sono uguali alla classe la macro mi calcola la combinazione ma mi cancella tutto nelle colonne O,P e I
come mai?
Grazie

[Cele79]

Poi nella colonna I mi copia la cella I6 e la incolla per la lunghezza delle combinazioni se dopo metto altri numeri da combinare!

[Anthony47]

La macro cancella tutte le formule presenti in colonne I, O, P, da riga 8 in avanti, e poi compila solo quelle sulle righe compilate con le combinazioni.
Adesso ho notato che in colonna I la formula in I6 e' diversa da quelle successive e questo mette in crisi la copia delle formule di colonna I.
Per correggere, modifica questa istruzione:
Range("I7:P7").Resize(col2h, 8).FillDown
In
Range("I7:P7").Resize(col2h + 2, 8).FillDown

Penso che dovrebbe risolvere quanto segnalato.

Ciao

[Cele79]

Con la sostituzione funziona benissimo!
grazie