Inserimento formula SEEin VBA

Hai un problema con Excel?

Nuova discussione

Inserimento formula SE(E...in VBA
di leonardocarrani data: 12/02/2014 13:24:59
Salve a tutti...
Dovrei inserire in un foglio excel una macro che sostituisca il calcolo fatto dalle seguenti formule:
=SE(E(E6<0,0416666666666667;E7<0,0416666666666667);E7-E6;SE(E(F7=F6;E6>0,166666666666667;E7>0,166666666666667;((E7-E6)<0,0208333333333333));E7-E6;SE(E(E6>0,958333333333333;E7<0,0416666666666667);0,999988425925926-E6+E7;SE(E(F7>F6;E7<0,0416666666666667;E6>0,958333333333333);E7-E6;""))))

=SE(E(S6<0,0416666666666667;S7<0,0416666666666667);S7-S6;SE(E(T7=T6;S6>0,166666666666667;S7>0,166666666666667;((S7-S6)<0,0208333333333333));S7-S6;SE(E(S6>0,958333333333333;S7<0,0416666666666667);0,999988425925926-S6+S7;SE(E(T7>T6;S7<0,0416666666666667;S6>0,958333333333333);S7-S6;""))))

Il foglio che vi allego è un esempio di un file molto più ampio (circa 400.000 righe).
Vorrei utilizzare la macro per alleggerire e velocizzare il calcolo.
Ho provato a modificare una macro precedente realizzando il codice allegato, ma ovviamente non funziona!!!
Io non ci capisco proprio nulla...ci provo, ma sono duro di comprendonio...

Grazie a chi volesse aiutarmi...
Leonardo

Sub PROVA()

Application.ScreenUpdating = False

Dim rng()
Dim E As Long
Dim A As Double, B As Double, C As Double, D As Double
Dim K As Double, L As Double, M As Double, N As Double, O As Double
K = 4.16666666666667E-02
L = 0.958333333333333
M = 0.999988425925926
N = 0.166666666666667
O = 2.08333333333333E-02

Sheets("FREQUENZE").Select
Range("B6:B").NumberFormat = "[$-F400]h:mm:ss AM/PM"
rng = Range("E6:E" & Range("E" & Rows.Count).End(xlUp).Row)
ReDim arr(1 To UBound(rng, 1), 1 To 1)
A = rng(1, 1)
For E = 2 To UBound(rng, 1)
B = rng(E, 1)
C = rng(1, 2)
D = rng(E, 2)
If A > K And B < K Then
arr(E, 1) = B - A
ElseIf D = C And A > N And B > N And (B - A) < O Then
arr(E, 1) = B - A
ElseIf A > L And B < K Then
arr(E, 1) = M - A + B
ElseIf D > C And B < K And A > L Then
arr(E, 1) = B - A
Else: arr(E, 1) = ""
End If
A = B
Next
Range("B6:B" & Range("E" & Rows.Count).End(xlUp).Row) = arr
Application.ScreenUpdating = True
End Sub

di scossa data: 12/02/2014 13:51:59
Ciao,

puoi spiegare, con parole chiare, quella formula? Che risultato dovrebbe restituire in Q7 rispetto ai valori S6:T7?
di Mister_x (utente non iscritto) data: 13/02/2014 00:38:42
ciao

mi sono spaventato delle tue formule che ai postato
tua
B7=SE(E(E6<0,0416666666666667;E7<0,0416666666666667);E7-E6;SE(E(F7=F6;E6>0,166666666666667;E7>0,166666666666667;((E7-E6)<0,0208333333333333));E7-E6;SE(E(E6>0,958333333333333;E7<0,0416666666666667);0,999988425925926-E6+E7;SE(E(F7>F6;E7<0,0416666666666667;E6>0,958333333333333);E7-E6;""))))

mia
B7=SE(E7>=E6;E7-E6;E7+1-E6)

tua
Q7=SE(E(S6<0,0416666666666667;S7<0,0416666666666667);S7-S6;SE(E(T7=T6;S6>0,166666666666667;S7>0,166666666666667;((S7-S6)<0,0208333333333333));S7-S6;SE(E(S6>0,958333333333333;S7<0,0416666666666667);0,999988425925926-S6+S7;SE(E(T7>T6;S7<0,0416666666666667;S6>0,958333333333333);S7-S6;""))))

mia
Q7=SE(S7>=S6;S7-S6;S7+1-S6)

a questo punto cosa serve la macro quando tieni il calcolo in manuale e lo attivi come fai adesso con un F9?????

ciao mister_x

di Mister_x (utente non iscritto) data: 13/02/2014 00:54:31
riciao

comunque se vuoi utilizzare una macro ti passo questa semplice da mettere nel foglio FREQUENZE

riciao

Sub calcola()
Dim i As Long
  For i = 6 To Range("E" & Rows.Count).End(xlUp).Row
    If Cells(i + 1, "E") >= Cells(i, "E") Then
        Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
      Else
        Cells(i + 1, "B") = Cells(i + 1, "E") + 1 - Cells(i, "E")
    End If
  Next i
  For i = 6 To Range("S" & Rows.Count).End(xlUp).Row
     If Cells(i + 1, "S") >= Cells(i, "S") Then
        Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
      Else
        Cells(i + 1, "Q") = Cells(i + 1, "S") + 1 - Cells(i, "S")
    End If
  Next i
End Sub

di scossa data: 13/02/2014 08:33:56
@mister_x: abbiamo avuto la stessa "impressione"

Io però, considerando il notevole numero di righe, propongo una formula più leggera, senza SE().
Sempre per non appesantire il calcolo eviterei il SE(....;"") per lascaire la cella vuota.
in B6:: =ASS((F6+E6)-(F7+E7)) da trascinare in basso fino a dove serve in Q7:: =ASS((T7+S7)-(T6+S6)) da trascinare in basso fino a dove serve
di scossa data: 13/02/2014 08:48:43

P.S.: dimenticavo, la tua formula non è corretta: controlla il risultato in Q28 ..
=SE(S28>=S27;S28-S27;S28+1-S27)
di leonardocarrani data: 13/02/2014 09:42:45
Ciao e grazie per le risposte...
È vero che ho scritto che non ci capisco niente e che sicuramente le formule da me scritte possono essere in qualche modo semplificate, ma i controlli che ho messo devono essere fatti, perche c,è un motivo.
Ho chiesto il vostro aiuto perchè trattandosi di file con circa 400.000 righe da controllare, volevo evitare di inserire una formula in cella da copiare per tutte le righe necessarie ma far eseguire il calcolo direttamente alla macro, con le stesse condizioni delle formule "SE".

@scossa...
la formula funziona così:
deve restituire in B e in S la differenza di orario, seguendo le condizioni indicate nella formula.
SE E6<1.00.00 e E7<1.00.00 restituisci in B7 il risultato di E7-E6 altrimenti
SE F7=F6 e E6>4.00.00 e E7>4.00.00 e la differenza tra E7 ed E6 è < 0.30.00 restituisci in B7 il risultato di E7-E6 altrimenti
SE E6>23.00.00 e E7<1.00.00 restituisci in B7 il risultato di 23.59.59 - E6+E7 altrimenti
SE F7>F6 e E7<1.00.00 e E6>23.00.00 restituisci in B7 il risultato di E7-E6 altrimenti lascia la cella vuota.

Lo stesso vale per i risultati della colonna S, cambiando ovviamente i riferimenti delle celle.

Grazie
Leonardo

di Mister_x (utente non iscritto) data: 13/02/2014 10:39:08
ciao leonardo

sequendo il tuo ragionamento con i tuoi vari se() e non inventando nulla la Sub() principalmente che si potrebbe creare e' questa che ti posto

PS
RIMANENDO sempre che le funzioni proposte da me e da scossa fanno lo stesso lavoro e la sub() uguale

ciao Mister_x

Sub calcola()
Dim i As Long
  For i = 6 To Range("E" & Rows.Count).End(xlUp).Row
    If Cells(i, "E") < 1 / 24 And Cells(i + 1, "E") < 1 / 24 Then
      Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
    ElseIf Cells(i + 1, "F") = Cells(i, "F") And Cells(i, "E") > 4 / 24 And Cells(i + 1, "E") > 4 / 24 And _
            Cells(i + 1, "E") - Cells(i, "E") < 1 / 48 Then
      Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
    ElseIf Cells(i, "E") > 23 / 24 And Cells(i + 1, "E") < 1 / 24 Then
      Cells(i + 1, "B") = (1 - (1 / 86400)) - Cells(i + 1, "E") + Cells(i, "E")
    ElseIf Cells(i + 1, "F") > Cells(i, "F") And Cells(i, "E") > 23 / 24 Then
      Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
    Else
      Cells(i + 1, "B") = ""
    End If
  Next i
'''''''''''''''''''''''''''''''''''''''''''''''''''''''
 For i = 6 To Range("S" & Rows.Count).End(xlUp).Row
    If Cells(i, "S") < 1 / 24 And Cells(i + 1, "S") < 1 / 24 Then
      Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
    ElseIf Cells(i + 1, "T") = Cells(i, "T") And Cells(i, "S") > 4 / 24 And Cells(i + 1, "S") > 4 / 24 And _
            Cells(i + 1, "S") - Cells(i, "S") < 1 / 48 Then
      Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
    ElseIf Cells(i, "S") > 23 / 24 And Cells(i + 1, "S") < 1 / 24 Then
      Cells(i + 1, "Q") = (1 - (1 / 86400)) - Cells(i + 1, "S") + Cells(i, "S")
    ElseIf Cells(i + 1, "T") > Cells(i, "T") And Cells(i, "S") > 23 / 24 Then
      Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
    Else
      Cells(i + 1, "Q") = ""
    End If
  Next i
'''''
End Sub

'SE E6<1.00.00 e E7<1.00.00 restituisci in B7 il risultato di E7-E6 altrimenti
'SE F7=F6 e E6>4.00.00 e E7>4.00.00 e la differenza tra E7 ed E6 è < 0.30.00 restituisci in B7 il risultato di E7-E6 altrimenti
'SE E6>23.00.00 e E7<1.00.00 restituisci in B7 il risultato di 23.59.59 - E6+E7 altrimenti
'SE F7>F6 e E7<1.00.00 e E6>23.00.00 restituisci in B7 il risultato di E7-E6 altrimenti lascia la cella vuota.

di leonardocarrani data: 13/02/2014 11:13:33
Grazie
Ora proverò la macro...

Quello che ho trovato che non andava nella formula semplificata del SE che mi avete proposto è che mi restituiva il risultato della differenza di orari anche se la differenza era superiore a 30min e non lasciava la cella vuota se la condizione non era soddisfatta. Questo mi avrebbe creato problemi per controlli successivi...

Grazie
Leonardo

di Mister_x (utente non iscritto) data: 13/02/2014 12:12:46
riciao

in base alla tua ultima

^-^se la differenza era superiore a 30min e non lasciava la cella vuota se la condizione non era soddisfatta ^-^

era abbastanza che nelle funzioni proposte da me e scossa mettere un se()
=SE(SE(S7>=S6;S7-S6;S7+1-S6)<1/48;SE(S7>=S6;S7-S6;S7+1-S6);"")
=SE(ASS((T7+S7)-(T6+S6))<1/48;ASS((T7+S7)-(T6+S6));"")
cambiando i riferimenti per la colonna B:B

la sub() che ne scaturisce sfruttando la funzione ASS() proposta da scossa Abs() in VB e quella che ti posto sotto

ciao Mister_x

Sub calcola_Abs()
Dim i As Long
  For i = 6 To Range("E" & Rows.Count).End(xlUp).Row
    If Abs(Cells(i + 1, "E") + Cells(i, "F") - Cells(i, "E") - Cells(i, "F")) < 1 / 48 Then
        Cells(i + 1, "B") = Abs(Cells(i + 1, "E") + Cells(i, "F") - Cells(i, "E") - Cells(i, "F"))
      Else
        Cells(i + 1, "B") = ""
    End If
  Next i
  For i = 6 To Range("S" & Rows.Count).End(xlUp).Row
    If Abs(Cells(i + 1, "S") + Cells(i, "T") - Cells(i, "S") - Cells(i, "T")) < 1 / 48 Then
        Cells(i + 1, "Q") = Abs(Cells(i + 1, "S") + Cells(i, "T") - Cells(i, "S") - Cells(i, "T"))
      Else
        Cells(i + 1, "Q") = ""
    End If
  Next i
End Sub

di Mister_x (utente non iscritto) data: 13/02/2014 12:25:03
ciao

dove trovi
End(xlUp).Row
metti un - 1
End(xlUp).Row - 1

riciao
di leonardocarrani data: 13/02/2014 13:34:22
Grazie...

Ovviamente non era una critica, ci mancherebbe, solo la constatazione che quello rappresentato nelle formule che avevo allegato non era corrisposto dalle nuove, se non facendo qualche modifica o aggiunta.

Grazie ancora, comunque, anche solo per il tempo che perdete a stare dietro a quei "ciuchi" come me!
di scossa data: 13/02/2014 14:02:28
Ciao,

cit.: "la formula funziona così: ......."

io ti avevo chiesto "in parole chiare" cioè in modo descrittivo, non in formule:
"voglio la differenza tra l'orario E7 ed E6 tenendo conto che se il giorno .... bla bla......... allora bla...bla ...."

anche perché se le condizioni che indichi non sono presenti tra i dati del tuo file diventa difficile capire se il tutto è semplificabile o meno.

di leonardocarrani data: 13/02/2014 15:06:01
@scossa

Mi dispiace ma io credevo di essere stato "chiaro", probabilmente non è così.
Grazie comunque, perchè nonostante la mia esposizione non chiara siete riusciti a risolvere il problema.
Cercherò di fare più attenzione la prossima volta.

Allego codice che funziona benissimo...

Grazie ancora...
Leonardo

Sub FREQUENZE2()

Sheets("FREQUENZE").Select
Dim i As Long
  For i = 6 To Range("E" & Rows.Count).End(xlUp).Row
    If Cells(i, "E") < 1 / 24 And Cells(i + 1, "E") < 1 / 24 Then
      Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
    ElseIf Cells(i + 1, "F") = Cells(i, "F") And Cells(i, "E") > 4 / 24 And Cells(i + 1, "E") > 4 / 24 And _
            Cells(i + 1, "E") - Cells(i, "E") < 1 / 48 Then
      Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
    ElseIf Cells(i, "E") > 23 / 24 And Cells(i + 1, "E") < 1 / 24 Then
      Cells(i + 1, "B") = (1 - (1 / 86400)) - Cells(i, "E") + Cells(i + 1, "E")
    ElseIf Cells(i + 1, "F") > Cells(i, "F") And Cells(i, "E") > 23 / 24 And Cells(i + 1, "E") < 1 / 24 Then
      Cells(i + 1, "B") = Cells(i + 1, "E") - Cells(i, "E")
    Else
      Cells(i + 1, "B") = ""
    End If
  Next i
  
  
  
   For i = 6 To Range("S" & Rows.Count).End(xlUp).Row
    If Cells(i, "S") < 1 / 24 And Cells(i + 1, "S") < 1 / 24 Then
      Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
    ElseIf Cells(i + 1, "T") = Cells(i, "T") And Cells(i, "S") > 4 / 24 And Cells(i + 1, "S") > 4 / 24 And _
            Cells(i + 1, "S") - Cells(i, "S") < 1 / 48 Then
      Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
    ElseIf Cells(i, "S") > 23 / 24 And Cells(i + 1, "S") < 1 / 24 Then
      Cells(i + 1, "Q") = (1 - (1 / 86400)) - Cells(i, "S") + Cells(i + 1, "S")
    ElseIf Cells(i + 1, "T") > Cells(i, "T") And Cells(i, "S") > 23 / 24 And Cells(i + 1, "S") < 1 / 24 Then
      Cells(i + 1, "Q") = Cells(i + 1, "S") - Cells(i, "S")
    Else
      Cells(i + 1, "Q") = ""
    End If
  Next i
 
  End Sub

di Mister_x (utente non iscritto) data: 13/02/2014 15:28:24
ciao leonardo

un mio consiglio ti direi come sub() di utilizzare quella postata per ultimo in quanto con una mole di dati l'ultima dimezza il tempo di esecuzione
tanto per darti alcuni tempi in base alle due sub
quella che tu utilizzi impiega per i pochi dati in mio possesso un tempo di 0,21875
mentre l'ultima denominata ^ Sub calcola_Abs() ^ impiega 0,1875

adesso sta a te scegliere la migliore

ciao
di leonardocarrani data: 13/02/2014 16:14:29
È vero, è più veloce, e quando si tratta di elaborare molti dati non fa male risparmiare un pochino di tempo...

Grazie
Leonardo

Vuoi Approfondire?