Risoluzione formule SEE

Hai un problema con Excel?

Nuova discussione

Risoluzione formule SE(E
di leonardocarrani (utente non iscritto) data: 02/01/2014 12:19:51
Salve dovrei trasformare le seguenti formule EXCEL in codice VBA...
Seguendo altri vostri post precedenti ho provato a fare qualcosa ma non sono riuscito a trovare una soluzione.
Dovendo risolvere la cosa per una attività lavorativa ho deciso di provare a chiedervi nuovamente aiuto!
Ogni formula deve essere una Macro a se stante.
Ecco la pima:
=SE(E(A2<0,041666667;A1>0,958333333);0,999988426-A1+A2;SE(E(A2>A1);A2-A1;""))
deve essere eseguita per tutte le righe del foglio ovviamente cambiando ad ogni riga anche i riferimenti delle celle.

Ecco la seconda:
=CONTA.PIÙ.SE('DATI SMN'!$C$7:$C$1048576;$E13;'DATI SMN'!$F$7:$F$1048576;"<=0.00.02";'DATI SMN'!$E$7:$E$1048576;"0")

Grazie in anticipo per l'aiuto che vorrete e potrete darmi.
Leonardo
di leonardocarrani data: 02/01/2014 12:21:24
Chiedo scusa, ma mi sono accorto di aver pubblicato la discussione senza aver effettuato il login...
di Grograman (utente non iscritto) data: 02/01/2014 12:51:48
Basta registrarsi con il registratore di macro
di patel data: 02/01/2014 12:55:01
ormai dovresti sapere che per ricevere un aiuto serio devi
1) spiegare perché vuoi utilizzare il vba
2) allegare un file di esempio con anche il risultato desiderato per testare il codice
di leonardocarrani data: 02/01/2014 12:55:25
Cosa vuol dire "basta registrarsi con il registratore di macro"?
di patel data: 02/01/2014 14:23:04
evidentemente una battuta di inizio anno
di Grograman data: 02/01/2014 14:51:00
E non sta parlando della mia di frase!
di leonardocarrani data: 02/01/2014 15:23:28
Allora, ho allegato un piccolo file di esempio.
devo calcolare la differenza di tempo tra celle.
Esempio:
A2-A1 (il tempo è in colonna A)
e scrivere la differenza tempo in colonna F (F2... dove nel file allegato ho inserito la formula SE)
Questo deve essere fatto per tutte le righe con dati presenti nel foglio, ovviamente aggiornando le celle.

Nella colonna G ho inserito un'altra formula che mi trasforma il dato della colonna F (impostato con formato ora) da ORE a SECONDI.
di leonardocarrani data: 02/01/2014 15:25:36
Dimenticavo...
vorrei utilizzare una macro perchè, a differenza del file allegato, che è composti di sole 50 righe, quello originale che devo utilizzare è composto di circa 600.000 righe e il calcolo con la formula se in ogni cella mi rallenta molto il lavoro....

di Mister_x (utente non iscritto) data: 02/01/2014 17:19:07
ciao

per la prima domanda la sub() da inserire nel foglio in questione e questa riportata sotto
in base ai tuoi dati esposti

la seconda funzione che tu ai posto non vedo l'utolizzo di una sub() o macro dato che ha riferimenti assoluti e non vedo nessun foglio aggiunto da quello che hai postato da fare un riferimento di dati se ci sono variabili da applicare

ciao da mister_x

Option Explicit
Sub Calcola_col_F_G()
Dim i As Long
    Columns("F:F").Select
     Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Columns("G:G").Select
     Selection.NumberFormat = "General"
   [F1].Select
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  If Cells(i + 1, "A") < 0.041666667 And Cells(i, "A") > 0.958333333 Then
   Cells(i + 1, "F") = 0.999988426 - Cells(i, "A") + Cells(i + 1, "A")
   Cells(i + 1, "G") = Cells(i + 1, "I") * 86400
  ElseIf Cells(i + 1, "A") > Cells(i, "A") Then
   Cells(i + 1, "F") = Cells(i + 1, "A") - Cells(i, "A")
   Cells(i + 1, "G") = Cells(i + 1, "I") * 86400
  Else
   Cells(i + 1, "F") = ""
   Cells(i + 1, "G") = ""
  End If
Next i
End Sub

di leonardocarrani data: 02/01/2014 17:26:35
Per la seconda hai ragione... mi sono dimenticato di mettere il foglio nell'esempio...
Ma essendo una tabella fissa potrei lasciare la formula indicata...
Ora provo la macro che mi hai dato...
Grazie

di Mister_x (utente non iscritto) data: 02/01/2014 17:33:39
Riciao

nel postare non avevo fatto un cambio a dei riferimenti fatti per prova per non modificare i tuoi dati a celle di colonna F e G quindi ti posto la sub() esatta

riciao

Option Explicit
Sub Calcola_col_F_G()
Dim i As Long
    Columns("F:F").Select
     Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Columns("G:G").Select
     Selection.NumberFormat = "General"
   [F1].Select
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  If Cells(i + 1, "A") < 0.041666667 And Cells(i, "A") > 0.958333333 Then
   Cells(i + 1, "F") = 0.999988426 - Cells(i, "A") + Cells(i + 1, "A")
   Cells(i + 1, "G") = Cells(i + 1, "F") * 86400
  ElseIf Cells(i + 1, "A") > Cells(i, "A") Then
   Cells(i + 1, "F") = Cells(i + 1, "A") - Cells(i, "A")
   Cells(i + 1, "G") = Cells(i + 1, "F") * 86400
  Else
   Cells(i + 1, "F") = ""
   Cells(i + 1, "G") = ""
  End If
Next i
End Sub

di isy data: 02/01/2014 18:12:13
Ciao

In alternativa al codice indicato
Si potrebbe utilizzare un ciclo con Evaluate

Option Explicit

Sub Evaluate_F()
    Dim x   As String, y  As String
    With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        x = .Address
        y = .Offset(-1).Address
        .Offset(, 5) = Evaluate("IF(AND(" & x & "<0.041666667," & y & ">0.958333333),0.999988426-" & x & "+" & y & ",IF(AND(" & x & ">" & y & ")," & x & "-" & y & ",""""))")
    End With
End Sub

Sub Evaluate_G()
    Dim x   As String
    With Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)
        x = .Address
        .Offset(, 1) = Evaluate("if(isnumber(" & x & ")," & x & "*24*60*60,"""")")
    End With
End Sub

di leonardocarrani data: 03/01/2014 09:13:23
Per MisterX

grazie.
Ho provato e il codice funziona ma per l'uso che devo farne io è improponibile. Per analizzare circa 30.000 righe ci ha messo circa 45min.
Considerando che generalmente devo analizzare file con circa 600.000 righe ci vorrebbero 24ore.

Grazie
Comunque
di leonardocarrani data: 03/01/2014 09:18:04
Per Isy

provato i due codici ma non succede assolutamente niente. i campi delle colonne F e G rimangono vuoti.

Grazie
di isy data: 03/01/2014 09:36:28
Ciao

Cit: Considerando che generalmente devo analizzare file con circa 600.000 righe ci vorrebbero 24ore
Cit: provato i due codici ma non succede assolutamente niente. i campi delle colonne F e G rimangono vuoti.

Ho allegato il file con con il codice utilizzato, il codice opera correttamente.
Vedi allegato Evaluate.zip
di leonardocarrani data: 03/01/2014 10:43:57
Per Isy...

Grazie
Ho provato il tuo codice nel file che hai allegato e funziona, ma se aggiungo dei dati alle colonne A B C D E (ccome da file allegato), non succede niente...
Forse sto sbagliando qualcosa?

di isy (utente non iscritto) data: 03/01/2014 13:17:44
Ciao

Cit: Forse sto sbagliando qualcosa?
Sostituisci con questo differente codice che utilizza un array

Sostituisci il precedente codice con:

Option Explicit

Sub Evaluate_F()
    Dim Rng()
    Dim C As Long
    Dim A As Double
    Dim B As Double
    Dim K As Double, L As Double, M As Double
    K = 0.041666667
    L = 0.958333333
    M = 0.999988426
    
    Columns("F:F").NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    ReDim arr(1 To UBound(Rng, 1), 1 To 1)
    A = Rng(1, 1)
    For C = 2 To UBound(Rng, 1)
      B = Rng(C, 1)
      If B < K And A > L Then
        arr(C, 1) = M - A + B
      Else
        If B > A Then arr(C, 1) = B - A
      End If
      A = B
    Next
    Range("F1:F" & Range("A" & Rows.Count).End(xlUp).Row) = arr
End Sub

Sub Evaluate_G()
    Columns("G:G").NumberFormat = "General"
    Dim x   As String
    With Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row)
        x = .Address
        .Offset(, 1) = Evaluate("if(isnumber(" & x & ")," & x & "*86400,"""")")
    End With
End Sub

di leonardocarrani data: 03/01/2014 13:25:37
Ottimo...
adesso funziona...
Hai capito come mai l'altra non andava?

di MIster_x (utente non iscritto) data: 03/01/2014 13:34:07
ciao

deduco che nel tuo file oltre a dover utilizzare la macro tu abbia inserito in altre colonne delle formule perche' tu dici che con 30000 righe hai impiegato piu' di 45 minuti, io con piu' di 600000 righe' con la macro il calcolo e' stato fatto in 1 minuto e 45 secondi , come vedrai riportato in cella M1
questo ritardo succede perche ad ogni variazione di cella excel esegue un ricalcolo di tutti i dati , quindi a questo puntu e' preferibile fermare il ricalcolo delle formule e attuarlo alla fine della macro, vedi questa di esempio che ti posto modificata a riguardo
allego anche il tuo file con i 600000 e piu' record analizzati

cioa MIster_x

Option Explicit
Sub Calcola_col_F_G()
Dim i As Long
Dim tempo
tempo = Now
''''''''
Application.Calculation = xlManual
'''''''''
Range("F:F") = ""
    Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
Range("G:G") = ""
    Selection.NumberFormat = "General"
[F1].Select
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
  If Cells(i + 1, "A") < 0.041666667 And Cells(i, "A") > 0.958333333 Then
   Cells(i + 1, "F") = 0.999988426 - Cells(i, "A") + Cells(i + 1, "A")
   Cells(i + 1, "G") = Cells(i + 1, "F") * 86400
  ElseIf Cells(i + 1, "A") > Cells(i, "A") Then
   Cells(i + 1, "F") = Cells(i + 1, "A") - Cells(i, "A")
   Cells(i + 1, "G") = Cells(i + 1, "F") * 86400
  Else
   Cells(i + 1, "F") = ""
   Cells(i + 1, "G") = ""
  End If
Next i
'''''''''
Application.Calculation = xlAutomatic
Calculate
[M1] = Now - tempo
End Sub

di Mister_x (utente non iscritto) data: 03/01/2014 13:46:59
file troppo grosso impossibile allegarlo

ciao

di isy data: 03/01/2014 19:09:09
Ciao leonardocarrani

Cit: adesso funziona...
Hai capito come mai l'altra non andava?

Per correggere l'errore segnalato ho dovuto rivedere un refuso che hai inserito nella formula.
in F2 hai inserito la seguente
=SE(E(A2<0,041666667;A1>0,958333333);0,999988426-A1+A2;SE(E(A2>A1);A2-A1;""))
Se noti nella formula puoi evitare il seguente test: E(A2>A1)

Ho sostituito la formula con:
=SE(E(A2<0,041666667;A1>0,958333333);0,999988426-A1+A2;SE(A2>A1;A2-A1;""))

Segue il codice Vba corretto

Sono curioso di conoscere i tempi di elaborazione con i codici proposti

Option Explicit

Sub Evaluate_F()
    Dim x   As String, y  As String
    Columns("F:F").NumberFormat = "[$-F400]h:mm:ss AM/PM"
    With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        x = .Address
        y = .Offset(-1).Address
        .Offset(, 5) = Evaluate("IF(AND(" & x & "<0.041666667," & y & ">0.958333333),0.999988426-" & x & "+" & y & ",IF(" & x & ">" & y & "," & x & "-" & y & ",""""))")
    End With
End Sub

di Mister_x (utente non iscritto) data: 03/01/2014 23:33:44
ciao isy

dato che come ai detto eri curioso di sapere il tempo di elaborazione della tua sub(), ho fatto io la prova
su 665763 record e il tempo e' stato di 12 secondi contro la mia di 1:45
resta sempre il fatto che se sono presenti nel foglio altre formule queste ad ogni cambiamentio ti cella vengono elaborate quindi bisogna sempre fermare tale operazione ed effettuarla in ultimo dopo la sub()
altra cosa aggiungi gia' la variazione della colonna G:G dove il tempo viene trasformato in valore di secondi
per prova alla tua sub() ho fatto queste aggiunte di controllo del tempo e come vedi dalla foto allegata

ciao Mister_x

Sub Evaluate_F()
Dim tempo As Date
tempo = Now
    Dim x   As String, y  As String
    Columns("F:F").NumberFormat = "[$-F400]h:mm:ss AM/PM"
    With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        x = .Address
        y = .Offset(-1).Address
        .Offset(, 5) = Evaluate("IF(AND(" & x & "<0.041666667," & y & ">0.958333333),0.999988426-" & x & "+" & y & ",IF(" & x & ">" & y & "," & x & "-" & y & ",""""))")
    End With
[I665763] = Now - tempo
End Sub

di isy data: 04/01/2014 00:17:15
Ciao Mister_x

Prova con questo codice..

Option Explicit

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub Evaluate_F_G()
    Dim Via As Variant
    Dim Msec As Variant
    Via = GetTickCount
    Dim x   As String, y  As String
    Columns("F:F").NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Columns("G:G").NumberFormat = "General"
    
    With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        x = .Address
        y = .Offset(-1).Address
        .Offset(, 5) = Evaluate("IF(AND(" & x & "<0.041666667," & y & ">0.958333333),0.999988426-" & x & "+" & y & ",IF(" & x & ">" & y & "," & x & "-" & y & ",""""))")
        .Offset(, 6) = Evaluate(.Offset(, 5).Address & "*86400")
    End With
    
    Msec = GetTickCount - Via
    MsgBox "   " & Format$(Msec  3600000, "00") & ":" & Format$(((Msec - (Msec  3600000) * 3600000))  60000, "00") & ":" & Format$((Msec - (Msec  60000) * 60000) / 1000, "00.000")
End Sub

di Mister_x (utente non iscritto) data: 04/01/2014 00:56:40
ciao Isy

ti allego la nuova prova fatta con l'aggiunta della secoda colonna

ciao Mister_x

Vuoi Approfondire?