Ridurre dimensioni file

[pany221]

Ciao a tutti,
ho un file molo pesante su cui sto lavorando...il mio problema è che essendo così pesante anche per un semplice copia incolla di valori ci mette un sacco; qualcuno per caso riesce a darmi delle indicazioni su come alleggerirlo riducendo magari le formule che ho utilizzato oppure facendo lo stesso procedimento tramite una macro???


Ecco...allego un piccolo estratto del file, evidenziate in giallo le colonne che devono essere mantenute invariate...nelle altre ci sono formule che possono essere modificate per ridurre la grandezza del file



http://www.filedropper.com/analisipupilfoglio1permacro

[Flash30005]

Tutto ciò che ottieni con una formula puoi ottenerlo (come valore) con una macro riducendo notevolmente le dimensioni del file
ma per poter progettare una macro bisognerebbe sapere quando deve intervenire, le operazioni che fai per l'aggiornamento dei dati.
insomma qualche delucidazione in più

ciao

[Anthony47]

Leggi il messaggio di Flash, sopra.

In linea di massima sono "mortali" le formule a matrice (quelle introdotte con Contr-Maiusc-Enter) che hanno un intervallo di riferimento indefinito; tipo quelle inserite in "Periodo di interesse" cella L3 (che tra l'altro non ho capito): =DEV.ST(SE($F:$F="PRE";$J:$J:$I:$I)) e che ho modificato in =DEV.ST(SE($F1:$F1000="PRE";$J1:$J1000))
Modifiche analoghe alle formule in M3 ed N3.
Queste modifiche non hanno cambiato la dimensione del file ma hanno drasticamente ridotto il tempo di ricalcolo

Altre modifiche con effetti meno evidenti sulle prestazioni:
G3 e sottostanti:
=SE(E:E=".";G2;E:E) --> =SE(E3=".";G2;E3)

L2 e adiacenti:
=MEDIA.SE($F:$F;"PRE";$J:$J) --> =MEDIA.SE($F1:$F1000;"xyz";$J1:$J1000)

In O3
=SE((E($F:$F="PRE";$J3>L$2-1,96*L$3;$J3<L$2+1,96*L$3));$J3) --> =SE((E(F4="PRE";$J4>L$2-1,96*L$3;$J4<L$2+1,96*L$3));$J4)

In S3:
=SE($F:$F="PRE";O:O;SE($F:$F="DURING";P:P;SE($F:$F="POST";Q:Q)))
--> =SE(F3="PRE";O3;SE($F3="DURING";P3;SE($F3="POST";Q3)))

In AH3
=SE(S3=FALSO;"ARTEFATTO";G:G) --> =SE(S3=FALSO;"ARTEFATTO";G3)

Su Foglio DIFFERENZE, ho scritto in A1 "artefatto" e ho modificato tutte le formule
Es D3
=SE('ANALISI PRELIMINARI'!C3="artefatto";"artefatto";'ANALISI PRELIMINARI'!C3-'ANALISI PRELIMINARI'!$B3)
--> =SE('ANALISI PRELIMINARI'!C3=$A$1;$A$1;'ANALISI PRELIMINARI'!C3-'ANALISI PRELIMINARI'!$B3)

NB: nelle formule dove ho sostituito es F:F con F1:F1000, se 1000 e' insufficiente si puo' mettere 10mila, o 100mila, e sara' sempre meglio che la colonna intera.

La maggior parte di spazio deriva dai dati contenuti nelle celle; sul foglio DIFFERENZE ci sono 450mila formule, molto semplici per la verita', ma il foglio alla fine occupa circa 4.6 mbyte dei 7.8 Mmbyte totali del file.
Il foglio "DIFFERENZE (sost con falso)" mi pare che contenga gli stessi 450mila valori, come valori e non come formula, e occupa circa 1.9 mbyte (mi sarei aspettato il doppio, a 8 byte/cella); e' evidente che non ha senso mantenere sia un foglio che l' altro, ma e' probabile che non si possa eliminare quello con le formule e in questo caso quello e' il foglio dove l' uso di una macro per calcolare quel tabellone (al posto delle formule) puo' risultare piu' conveniente, in termini di dimensione del file (ma se i dati cambiano in modo dinamico nel foglio "ANALISI PRELIMINARI" i risultati su "DIFFERENZE" rimangono disallineati fino alla riesecuzione della macro).

A parte questi interventi e queste considerazioni non ho capito il lavoro che complessivamente stai facendo, quindi non mi son fatta un'idea di come rendere il tutto piu' snello.

Ciao

[pany221]

Leggi il messaggio di Flash, sopra.

In linea di massima sono "mortali" le formule a matrice (quelle introdotte con Contr-Maiusc-Enter) che hanno un intervallo di riferimento indefinito; tipo quelle inserite in "Periodo di interesse" cella L3 (che tra l'altro non ho capito): =DEV.ST(SE($F:$F="PRE";$J:$J:$I:$I)) e che ho modificato in =DEV.ST(SE($F1:$F1000="PRE";$J1:$J1000))
Modifiche analoghe alle formule in M3 ed N3.
Queste modifiche non hanno cambiato la dimensione del file ma hanno drasticamente ridotto il tempo di ricalcolo

Altre modifiche con effetti meno evidenti sulle prestazioni:
G3 e sottostanti:
=SE(E:E=".";G2;E:E) --> =SE(E3=".";G2;E3)

L2 e adiacenti:
=MEDIA.SE($F:$F;"PRE";$J:$J) --> =MEDIA.SE($F1:$F1000;"xyz";$J1:$J1000)

In O3
=SE((E($F:$F="PRE";$J3>L$2-1,96*L$3;$J3<L$2+1,96*L$3));$J3) --> =SE((E(F4="PRE";$J4>L$2-1,96*L$3;$J4<L$2+1,96*L$3));$J4)

In S3:
=SE($F:$F="PRE";O:O;SE($F:$F="DURING";P:P;SE($F:$F="POST";Q:Q)))
--> =SE(F3="PRE";O3;SE($F3="DURING";P3;SE($F3="POST";Q3)))

In AH3
=SE(S3=FALSO;"ARTEFATTO";G:G) --> =SE(S3=FALSO;"ARTEFATTO";G3)

Su Foglio DIFFERENZE, ho scritto in A1 "artefatto" e ho modificato tutte le formule
Es D3
=SE('ANALISI PRELIMINARI'!C3="artefatto";"artefatto";'ANALISI PRELIMINARI'!C3-'ANALISI PRELIMINARI'!$B3)
--> =SE('ANALISI PRELIMINARI'!C3=$A$1;$A$1;'ANALISI PRELIMINARI'!C3-'ANALISI PRELIMINARI'!$B3)

NB: nelle formule dove ho sostituito es F:F con F1:F1000, se 1000 e' insufficiente si puo' mettere 10mila, o 100mila, e sara' sempre meglio che la colonna intera.

La maggior parte di spazio deriva dai dati contenuti nelle celle; sul foglio DIFFERENZE ci sono 450mila formule, molto semplici per la verita', ma il foglio alla fine occupa circa 4.6 mbyte dei 7.8 Mmbyte totali del file.
Il foglio "DIFFERENZE (sost con falso)" mi pare che contenga gli stessi 450mila valori, come valori e non come formula, e occupa circa 1.9 mbyte (mi sarei aspettato il doppio, a 8 byte/cella); e' evidente che non ha senso mantenere sia un foglio che l' altro, ma e' probabile che non si possa eliminare quello con le formule e in questo caso quello e' il foglio dove l' uso di una macro per calcolare quel tabellone (al posto delle formule) puo' risultare piu' conveniente, in termini di dimensione del file (ma se i dati cambiano in modo dinamico nel foglio "ANALISI PRELIMINARI" i risultati su "DIFFERENZE" rimangono disallineati fino alla riesecuzione della macro).

A parte questi interventi e queste considerazioni non ho capito il lavoro che complessivamente stai facendo, quindi non mi son fatta un'idea di come rendere il tutto piu' snello.

Ciao

grazie 1000
hai per caso allegato il file???

[Anthony47]

Il file l'ho manipolato troppo perche' possa garantire che sia ancora simile a quello da te pubblicato; quindi devi apportare in proprio le modifiche partendo dal tuo file.

Mi e' venuto all'occhio che il foglio che occupa piu' spazio e' "DIFFERENZE", quello con le formule; mentre l' equivalente con i soli dati occupa molto meno. Le formule fanno riferimento al foglio "ANALISI PRELIMINARI", che e' quello popolato dalla macro Trasponi3 che avevamo sviluppato in precedenza.
Quindi e' relativamente facile accodare una seconda macro alla Trasponi3 per calcolare tutto il contenuto del foglio "DIFFERENZE", in modo che occupi meno spazio.
Vorrei sapere se questo ragionamento ti torna, e se Si
-mi confermi che in "DIFFERENZE" vanno calcolate tante celle quante ne sono presenti in "ANALISI PRELIMINARI"?
-mi confermi che i FALSO che sono presenti in "DIFFERENZE (sost con falso)"sono la semplice sostituzione degli errori #DIV/0!?

Ciao

[pany221]

Il file l'ho manipolato troppo perche' possa garantire che sia ancora simile a quello da te pubblicato; quindi devi apportare in proprio le modifiche partendo dal tuo file.

Mi e' venuto all'occhio che il foglio che occupa piu' spazio e' "DIFFERENZE", quello con le formule; mentre l' equivalente con i soli dati occupa molto meno. Le formule fanno riferimento al foglio "ANALISI PRELIMINARI", che e' quello popolato dalla macro Trasponi3 che avevamo sviluppato in precedenza.
Quindi e' relativamente facile accodare una seconda macro alla Trasponi3 per calcolare tutto il contenuto del foglio "DIFFERENZE", in modo che occupi meno spazio.
Vorrei sapere se questo ragionamento ti torna, e se Si
-mi confermi che in "DIFFERENZE" vanno calcolate tante celle quante ne sono presenti in "ANALISI PRELIMINARI"?
-mi confermi che i FALSO che sono presenti in "DIFFERENZE (sost con falso)"sono la semplice sostituzione degli errori #DIV/0!?

Ciao

Ciao,
-SI, il foglio DIFFERENZE prende i dati dal foglio ANALISI PRELIMINARI, in modo particolare fa la differenza tra ciascun valore e il valore nella colonna media es: C3-B3 ANALISI PRELIMINARI (in D3 foglio DIFFERENZE), D3-B3 ANALISI PRELIMINARI (in E3 foglio DIFFERENZE) e così via...C4-B4 ANALISI PRELIMINARI (in D4 foglio DIFFERENZE), D4-B4 ANALISI PRELIMINARI (in E4 foglio DIFFERENZE) e così via...
-SI, in DIFFERENZE vanno calcolate tante celle quante in ANALISI PRELIMINARI

Allego il file che ho modificato con le vostre indicazioni, ho trovato il modo di eliminare il foglio DIFFERENZE (sost con falso) calcolando quello che mi serviva direttamente nel foglio DIFFERENZE in modo da avere un foglio in meno e ridurre le dimensioni del file. Non sono però riuscita a eliminare le formule matricali in foglio PERIODO DI INTERESSE (colonne L, M, N): non mi funziona la formula "normale".

Fatemi sapere se riuscite a produrre una macro da accodare a Trasponi 3 per processare le formule del foglio differenze

http://www.filedropper.com/foglioanalisifinale180trial


Grazie mille per il preziosissimo aiuto

[pany221]

RIALLEGO UNA VERSIONE RIDOTTA COME ESEMPIO DEL FILE...

http://www.filedropper.com/foglioanalis ... -ridotto_1

[Anthony47]

Ho preparato la macro "Differenze", che poi ho accodato alla Sub Trasponi3 (vedi Call Differenze, in fondo).
Il codice:

Codice: Seleziona tutto

Sub Differenze()
Dim AP As Worksheet, DIF As Worksheet, myAPB, DiffArr()
Dim WArr, LastA1 As Long, LastC1 As Long, LB1 As Long, LB2 As Long
'
Set AP = Sheets("ANALISI PRELIMINARI")
Set DIF = Sheets("DIFFERENZE")
LastA1 = AP.Cells(Rows.Count, 1).End(xlUp).Row
AP.Select
LastC1 = AP.Range(Range("A2"), Range("A" & LastA1)).Resize(, Columns.Count - 10).Find(What:="*", After:=Range("A2"), _
              SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious).Column
'
WArr = AP.Range(Range("A3"), Range("A" & LastA1)).Resize(, LastC1).Value
LB1 = LBound(WArr, 1): LB2 = LBound(WArr, 2)
DIF.Range("D3").Resize(LastA1 - 2, 5000).ClearContents
ReDim DiffArr(1 To (LastA1 - 2), 1 To LastC1)
yartef = DIF.Range("A1").Value
For i = LB1 To UBound(WArr, 1)
    myAPB = WArr(i, 2)
    For j = LB2 + 2 To UBound(WArr, 2)
        If IsError(myAPB) Or IsError(WArr(i, j)) Then
            DiffArr(i, j) = False
        Else
            If WArr(i, j) = yartef Then
                DiffArr(i, j) = yartef
            Else
                DiffArr(i, j) = WArr(i, j) - myAPB
            End If
        End If
    Next j
Next i
DIF.Select
    Columns("D:E").Insert Shift:=xlToRight
    DIF.Range("D3").Resize(UBound(DiffArr, 1), UBound(DiffArr, 2)).Value = DiffArr
    Columns("D:E").Delete Shift:=xlToLeft
End Sub


Questa calcola le celle da D3 in avanti nel foglio DIFFERENZE.
Il tutto e' integrato nel file disponibile qui: https://www.dropbox.com/s/4xrh7ayz4s3xk ... .xlsm?dl=0
Il file contiene anche il foglio "DIFFERENZE(Originale)", con alcune righe /colonne di formule originali e il resto cancellato; a spanne il confronto tra le differenze calcolate (nel foglio DIFFERENZE) e quelle originali sono uguali, ma sono sicuro che vorrai fare un ulteriore accurato collaudo...

Ho visto che nelle formule a matrice sul foglio " Periodo di interesse" avevi usato l' altezza 1.000.000, cioe' quasi quanto l'altezza totale delle colonne (1.048.576 righe); mi sono permesso di ridurla a 100.000, aumentala se i tuoi elenchi sono piu' lunghi.

Ciao

[pany221]

grazie funziona tutto