Formula equivalente ma più veloce.

[Statix]

Ciao a tutti,
e ben ritrovati, vi ricordo che vi seguo sempre,
volevo chiedere se c'è una formula sostitutiva e che sia più veloce

Codice: Seleziona tutto

=SE($I6="";"";CONTA.SE(INDIRETTO("D"& RIF.RIGA()+1 & ":" & "H" & RIF.RIGA()+$I$4);D$2))

la formula non fa altro che contare un valore D$2 ,in un range variabile messo in $I$4,
la formula come si suol dire va tirata in basso per 6000 righe x 3 colonne = 18.000 celle è questo mi rallenta di molto
l'elaborazione.
ho pensato di sostituirla con Scarto ma non so di preciso se i risultati sono gli stessi.
grazie.

[ricky53]

Ciao,
se devi contare quante volte un valore messo in "D2" è presente in un intervallo contenuto in "I4" perchè utilizzi tutte quelle celle con la formula che hai riportato?

Non ne basta una sola ???

Probabilmente mi mancano informazioni per poter capire la necessità reale che hai.

[Statix]

Ciao ricky53,
se hai notato bene la formula va in funzione in base al valore in I6, se I6 è = "" , no conta.se
se I6 è = X conta.se range variabile ,
la formula va applicata ad ogni valore trovato della colonna I6:I6000,
in media nella colona I6:I6000 ci sono circa 300 valori utili ed ad ogni valore utile va applicata
la formula conta.se, ecco perché ho detto che tale formula va poi tirata per 6000 righe e per 3 colonne(in quanto ho 3 valori da contare indipendentemente)

[ricky53]

Ciao,
qualcosa non mi convince o mi mancano informazioni.

Puoi inviare uno schema dei dati o un'immagine e dire che valori ti aspetti in base i dati che ci invierai.

[Anthony47]

Questa credo sia equivalente, ma non so se sara' piu' veloce:

Codice: Seleziona tutto

=SE($I6="";"";CONTA.SE(SCARTO($D$1;RIF.RIGA();0;$I$4;5);D$2))


(se in I4 c' e' il numero delle righe che vuoi includere nel calcolo +1, allora userai $I$4-1).
Essendo sia Indiretto che Scarto funzioni "volatiti" il loro ricalcolo rallenta tutto il foglio; se i risultati non ti servono in modo dinamico (cioe' non e' indispensabile che quando cambi, ad esempio, D2 o I4 i dati cambino immediatamente) allora potresti valutare l' uso di una macro che all' occorrenza fa i callcoli e scrive i risultati.

Ciao

[Statix]

Ciao Anthony47,
ho da poco fatto alcune prove ,applicando una matrice alle 3 colonne,
cioè un somma -conta.se etc.etc.
i tempi si sono abbassati di circa 20 secondi per ogni ciclo,
ma ho dovuto rinunciare ad alcuni dati,
temo che si potrebbe risolvere solo con una macro,
il valore in $D$2 è l'unico che cambia ad ogni ciclo,For next da 1 a 90
mentre le formule rimangono fisse ,cioè è copiata da M6:M6004 ,
queste per 3 colonne,
la prima colonna M6:M6004 conta D2
la seconda colonna N6:N6004 conta E2
la terza colonna O6:O6004 conta F2

[Statix]

Ho fatto anche la prova a sostituire le formule con la tua i tempi rimangono invariati di pochi secondi 4/5
inoltre volevo dirti che se hai qualche idea riguardo una macro,
mi basta che elabora una sola colonna ,come da formula nel post.

[Anthony47]

Forse con un esempio di dati da elaborare si puo' provare a elaborare qualcosa di piu' efficiente; "forse".

Ciao

[Statix]

ti allego una foto ,
la formula

Codice: Seleziona tutto

=SE($I6="";"";CONTA.SE(INDIRETTO("D"& RIF.RIGA()+1 & ":" & "H" & RIF.RIGA()+$I$4);D$2))

è in M6:M6004 ed si attiva solo se in I6 c'è un record ,alla riga successiva
econta.se D2 per un range di righe da D:H variabile da I4
questo per tutti gli eventi dove c'è un valore in colonna I6:I6004

[Statix]

Ciao Anthony47,
ho riflettuto molto sulla macro,
secondo me ,la questione si può risolvere con semplice macro ,
basta che la macro vada a copiare la formula solo nella riga dove e presente un record in colonna I:I,
al massimo avrò 300 formule x 3 colonne =900 formule intotale contro le 18000.
quindi basta mettere le formule solo dove occorre.

[Statix]

Ciao Anthony47,
sono riuscito a fare la macro,
faccio alcune prove poi ti faccio sapere come va con i tempi di elaborazione.

[Statix]

Ciao Anthony47,
da una prima analisi ho risparmiato circa la metà del tempo 1:30 contro 3 minuti,
un ciclo di 90 x 2 minuti =180 minuti,risparmio la bellezza di 2 ore.
devo ancora modificare delle colonne applicando lo stesso metodo,
credo che alla fine arriverò a 1 minuto,in quanto alcune colonne le ho azzerate,
metto le formule con la macro solo all'accorrenza di quella ricerca,
in totale ho eliminato circa 30000 formule senza perdere nessun dato.

di tanto in tanto si accende la lampadina.

[ricky53]

Ciao,
un consiglio: lavora con le ARRAY invece di utilizzare, in lettura/aggiornamento, le celle del foglio i tempi saranno di almeno un ordine di grandezza inferiore.

Immagino che tu abbia disabilitato l'aggiornamento dello schermo e messo il ricalcolo a manuale.
In caso tu non lo abbia fatto, imposta questi due valori e ... la velocità aumenterà e di tanto.

Visto che utilizzi comunque una macro invece delle formule puoi mettere il risultato del calcolo ed anche qui ancora risparmio di tempo

[Statix]

Ciao ricky53,
per quanto riguardo le array stò a zero,appena ho tempo cerco di documentarmi,
se ti è possibile farmi un esempio,
invece per quanto riguarda schermo e aggiornamento manuale già avevo fatto.
terzo non ho capito bene,ma credo che non posso mettere direttamente i risultati,
le macro le uso inizialmente per mettere le formule dove sono richieste dopodiche uso dei cicli che mi cambiano dei valori
e le formule calcolano questi dati che infine salvo.,

[ricky53]

Ciao,
puoi inviare il codice che utilizzi e vedo se posso darti una mano per sostituire le celle con gli elementi delle ARRAY.

Il discorso sulle formule che hai fatto (... non mi è chiaro) lo vediamo insieme al codice che mi invierai.

Un file di esempio è utile, attenzione ai dati riservati.

[Statix]

Ciao Anthony57,
ciao ricky53,
volevo fare una rettifica al post precedente,
dopo aver fatto la gran parte di modifiche con le creazioni delle macro ,
sono riuscito a risparmiare 3 ore di elaborazione e no 2 come scritto in precedente post.
le formule eliminate sono circa 40000,tutto senza perdere nessun dato di elaborazione,
devo ancora eliminare le formule per il caricamento delle estrazioni della ruota, sono altre 36000 formule che spariranno,
sono tutte cerca.verticale, anche qui dovrò fare una macro che dal foglio archivio mi copia solo i valori della ruota scelta.
così vedo se riesco a risparmiare ancora qualche manciata di secondi.

a domani

[ricky53]

Ciao,
ma sei interessato al codice che utilizza ARRAY oppure NO?

[Anthony47]

Hummm... 1:30 per scansionare 6000 righe e scrivere 900 formule?? Dovrebbe prendere pochi secondi anche senza array...
Prova questa macro:

Codice: Seleziona tutto

Sub M6O6006()
Dim myArr, I As Long, myI4 As Long
'Application.EnableEvents = False
'Application.Calculation = xlCalculationManual
'
Range("M6:O6006").ClearContents
myArr = Range("I6:I6006").Value
myI4 = Range("I4").Value
For I = 0 To 6000
    If myArr(LBound(myArr, 1) + I, 1) <> "" Then
        Cells(6 + I, "M").FormulaLocal = "=Conta.se(D" & (6 + I) & ":H" & (6 + I + myI4) & ";D2)"
        Cells(6 + I, "N").FormulaLocal = "=Conta.se(D" & (6 + I) & ":H" & (6 + I + myI4) & ";E2)"
        Cells(6 + I, "O").FormulaLocal = "=Conta.se(D" & (6 + I) & ":H" & (6 + I + myI4) & ";F2)"
    End If
Next I
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Calculate
End Sub


Controlla se il risultato e' quello atteso (controlla in M6:O6000 le formule inserite) e il tempo di esecuzione richiesto; poi per prova togli, uno alla volta, l' apostrofo a inizio delle due righe tipo Application.etc etc e dicci la differenza di tempi di esecuzione.

Ciao

[Statix]

Ciao Anthony47,
ciao ricky43
x ricky43, scusami sono talmente preso ,volevo prima verificare le macro,
per Anthony47 ,ogni giorno che passa mi stupisci sempre di più,
i tempi si sono dimezzati 45 secondi,(impiega questo tempo in quanto dopo le formule faccio elaborare altri dati)
ho dovuto fare una modifica,il range doveva scendere di una riga,
nella macro dovrei aggiungere anche questa formula nella colonna K

Codice: Seleziona tutto

SE(SOMMA($M6:$O6)>=1;1;"")

e volendo fare anche la colonna I

Codice: Seleziona tutto

=SE(CONTA.SE($D6:$H6;$A$2)=1;$A6;"")

questa dovrebbe essere calcolata per prima giusto?

inoltre dovrei fare un'altra macro uguale
che nella colonna Q

Codice: Seleziona tutto

=SE.ERRORE(PICCOLO(INDIRETTO("P"& RIF.RIGA()+1 & ":" & "P" & RIF.RIGA()+$I$4);1));"")

e nella colonna R

Codice: Seleziona tutto

=$Q6-$I6

poi se hai un pò di tempo vorrei fare la copia delle estrazioni
cioè mi dovrebbe copiare i record in A, la data in C e scelta la ruota le estrazioni in D6:H6000,
il foglio Archivio è così strutturato(mi servono i dati dalla riga 410 in giù)
dati dalla riga 410
A record
C la data
D:H bari
I:M cagliari
N:R firenze
S:W genova
X:AB milano
AC:AG napoli
AH:AL palermo
AN:AQ roma
AR:AV torino
AW:BA venezia
BB:BF nazionale

[Anthony47]

La colonna I deve essere caricata completamente a inizio macro, perche' il loop guardera' il contenuto d quella colonna.
Piu' tardi provero' a inserire gli aggiornamenti per la colonna K, mentre non ho capito perche' dovresti fare una seconda macro per la colonna Q e non puoi invece aggiungere ulteriormente la Q nel lavoro che fa questa macro.
Il discorso delle "estrazioni" invece mi provoca sempre un po' di orticaria, vista la mia scarsa attitudine verso i giochi quali lotto e affini; forse pero' tu parli genericamente di selezionare una tabella da un foglio generale e copiarla nel foglio dove poi vengono fatte queste nuove elaborazioni. Se e' cosi' mi servirebbe pero' un file di esempio, cosi' il collaudo e' piu' semplice.

Ciao