calcola la distanza

Hai un problema con Excel?

Nuova discussione

calcola la distanza
di rocco (utente non iscritto) data: 20/03/2017 18:37:10
ciao a tutti
sto provando a calcolare la distanza che intercorre, in una serie di celle,il valore 1
cioè, se il valore 1 si ripete dopo 9 celle avrei bisogno che un altra cella prendesse come valore questa distanza
io ho provato come non codice VBA che ho messo sotto ma mi conta sbagliato!
e non riesco a capire dove sta l'errore
se qualcuno mi potesse aiutare

Sub distanza()
'
' distanza Macro
' Macro registrata il 20/03/2017 
'

'
temp = 0
conta = 0
For io = 1 To 40
If Cells(io, 1) > 0 Then

If Cells(io, 1) > 1 Then
conta = conta + 1
End If
If Cells(io, 1) < 2 Then
If temp < conta Then
temp = conta
conta = 0
End If
End If

End If
Next io
Cells(10, 6) = temp

    Range("E18").Select
End Sub

di cromagno data: 20/03/2017 18:54:14
Ciao,
puoi spiegare esattamente (facendo riferimento alle celle del file che hai allegato) perchè il risultato dovrebbe essere 14??

P.S.
Stai provando col VBA perchè non vuoi usare le formule o per qualche motivo particolare?
di rocco (utente non iscritto) data: 20/03/2017 19:22:30
ciao e grazie per la risposta
sto provando con il vba perchè so usare a malapena questo

dovrebbe risultare 14 perchè la distanza piu lunga che c'è tra due 1 è in questo caso dalla cella riga 26 alla 39
alla cella 25 c'è un 1
poi per 14 celle ci sono valori superiori a 1
e alla cella 40 c'è un altro 1

spero di essermi spiegato

di cromagno data: 20/03/2017 19:52:18
Prova con il codice sotto:

Sub distanza()
Dim uriga As Long, Conta As Long, i As Long
Dim Inizio As Long, Finale As Long

uriga = Range("A" & Rows.Count).End(xlUp).Row
If uriga > 1 Then
    For i = 1 To uriga
        Conta = 0
        If Range("A" & i).Value = 1 And (i + 1) <= uriga Then
            For Inizio = i + 1 To uriga
                If Range("A" & Inizio).Value <> 1 Then
                    Conta = Conta + 1
                Else
                    i = Inizio - 1
                    GoTo prossimo
                End If
            Next Inizio
        End If
prossimo:
        If Conta > Finale Then
            Finale = Conta
        End If
    Next i
End If

Range("F10").Value = Finale
Range("F10").Select
End Sub

di cromagno data: 20/03/2017 20:01:17
Giusto per informazione....
lato formule, potresti usare questa formula matriciale (quindi da confermare con la combinazione di tasti CTRL+MAIUSC+INVIO anzichè solo con INVIO):

=MAX(FREQUENZA(SE($A$1:$A$100<>1;RIF.RIGA($A$1:$A$100));SE($A$1:$A$100=1;RIF.RIGA($A$1:$A$100))))

Ciao
Tore
di rocco (utente non iscritto) data: 20/03/2017 20:29:47
grazie cromagno
ma se io volessi cambiare il valore di ricerca 1 con 2 o 5 oppure 12
dove dovrei andare a mettere mano?

di cromagno data: 21/03/2017 00:40:03
Per la formula, devi cambiare le parti evidenziate in grassetto:

=MAX(FREQUENZA(SE($A$1:$A$100<>1;RIF.RIGA($A$1:$A$100));SE($A$1:$A$100=1;RIF.RIGA($A$1:$A$100))))

Per il codice, ho aggiunto un commento alla riga dove modificare il valore (codice sotto).

P.S.
Potresti mettere il valore che vuoi cercare in una cella e riferirti a quella, invece di modificare ogni volta il codice/formula.

Sub distanza()
Dim uriga As Long, Conta As Long, i As Long
Dim Inizio As Long, Finale As Long

uriga = Range("A" & Rows.Count).End(xlUp).Row
If uriga > 1 Then
    For i = 1 To uriga
        Conta = 0
        If Range("A" & i).Value = 1 And (i + 1) <= uriga Then    'Cambiare valore 1 con quello da ricercare
            For Inizio = i + 1 To uriga
                If Range("A" & Inizio).Value <> 1 Then  'Cambiare valore 1 con quello da ricercare
                    Conta = Conta + 1
                Else
                    i = Inizio - 1
                    GoTo prossimo
                End If
            Next Inizio
        End If
prossimo:
        If Conta > Finale Then
            Finale = Conta
        End If
    Next i
End If

Range("F10").Value = Finale
Range("F10").Select
End Sub

di rocco (utente non iscritto) data: 21/03/2017 06:53:22
ciao
ho provato a cambiare il valore 1 con 2 (anche cambiando 1 con il 2 in : ....And (i + 1) <= uriga Then? )
ma il risultato non è corretto perchè mi da 42 invece di 12
12 perchè la distanza piu lunga si trova dalla cella 60 alla 71

di Mister_x (utente non iscritto) data: 21/03/2017 10:03:38
ciao rocco

faccio un mio intervento a riguardo
come ti aveva raccomandato Cromagno ( che saluto ) era per il cambio di valore fare riferimento ad una cella ess F1 e mettere il valore da ricercare

quindi in questo caso la modifica della funzione viene in questo modo e anche quella della sub()
ps ho inserito anche il controllo di fine linea per non avere problemi al conteggio

=MAX(FREQUENZA(SE($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))<>F1;RIF.RIGA($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))));SE($A$1:$A$98=F1;RIF.RIGA($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))))))

nel file che ti posto ti metto anche una colonna di controllo con la funzione Max() quindi avrai 3 tipi di conteggi

ciao

Sub distanza()
Dim uriga As Long, Conta As Long, i As Long
Dim Inizio As Long, Finale As Long

uriga = Range("A" & Rows.Count).End(xlUp).Row
If uriga > 1 Then
    For i = 1 To uriga
        Conta = 0
        If Range("A" & i).Value = Range("F1") And (i + 1) <= uriga Then    'Cambiare valore 1 con quello da ricercare
            For Inizio = i + 1 To uriga
                If Range("A" & Inizio).Value <> Range("F1") Then  'Cambiare valore 1 con quello da ricercare
                    Conta = Conta + 1
                Else
                    i = Inizio - 1
                    GoTo prossimo
                End If
            Next Inizio
        End If
prossimo:
        If Conta > Finale Then
            Finale = Conta
        End If
    Next i
End If

Range("F10").Value = Finale
Range("F10").Select
End Sub

di rocco (utente non iscritto) data: 21/03/2017 14:24:08
grazie
lo sto provando ma funziona bene solo se metto 1 in F1
gia con il 2 in F1 mi da risultato sempre 42 invece di 12
ma a cosa sarà riferito sto 42?
non ho il modo ora di provare anche gli altri numeri dal 1 al 20
lo farò stasera
comunque anche cosi non funziona
di Mister_x (utente non iscritto) data: 21/03/2017 15:28:55
ciao

dovuto al fatto che da cella A33 fino a cella A76 non ci sono valori di 2 nel tuo esempio postato

controlla col mio file alla colonna B:B e vedrai il perché

ciao
di cromagno data: 21/03/2017 16:32:44
Ciao Mister_X,
grazie dell'intervento

infatti, quel 42 è giusto... se non bastessero tutte le "contro-prove" inserite da Mister_x, puoi aggiungere una formattazione condizionale in colonna A per avere subito un'idea del risultato da attendersi.

di Mister_x (utente non iscritto) data: 21/03/2017 18:58:04
ciao rocco

forse ho capito il tuo problema rivedendo un pochino il lavoro su alcuni valori immessi in F1 e ho elaborato questa nuova sub()
riallego il file anche con questa inserita

ciao

Sub DistanzaX_X()
Dim i As Long
Dim Conta As Long, RipC As Long
Dim ValriC As Variant
Dim inizio As Boolean
Conta = 0
RipC = 0
ValriC = Range("F1").Value
inizio = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = ValriC Then inizio = True
If inizio = True Then Conta = Conta + 1
 If Cells(i, 1) = ValriC Then
   If Conta > RipC Then
    RipC = Conta
   End If
  Conta = 0
 End If
Next i
Range("F12") = RipC - 1
End Sub

di cromagno data: 21/03/2017 20:38:48
Ciao Mister_x,
ho provato la nuova macro con il valore 18, ma restituisce 0.
Cosa dovrebbe dare la distanza "X_X" ?

Ciao
Tore
di rocco (utente non iscritto) data: 21/03/2017 21:54:02
OPS....
ho trovato dove sta il problema!
praticamente mi sono spiegato male io
scusatemi!
in pratica è giusto il valore che esce 42
io mi aspettavo il valore 12 perchè quando fa la ricerca con il 2 in F1 dovrebbe tenere conto non solo del 2 ma anche del 1!
e questo vale anche se ci fosse il 9 in F1...dovrebbe ricercare sia il 9 che 8 7 6 5 4 3 2 1

di rocco (utente non iscritto) data: 21/03/2017 22:51:51
quindi ho risolto cosi:

Sub distanza()
Dim i As Long
Dim Conta As Long, RipC As Long
Dim ValriC As Variant
Dim inizio As Boolean
Conta = 0
RipC = 0
ValriC = Range("F1").Value
inizio = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) < ValriC + 1 Then inizio = True
If inizio = True Then Conta = Conta + 1
 If Cells(i, 1) < ValriC + 1 Then
   If Conta > RipC Then
    RipC = Conta
   End If
  Conta = 0
 End If
Next i
Range("F12") = RipC - 1

End Sub

di Mister_x (utente non iscritto) data: 22/03/2017 00:40:09
ciao Cromagno

la mia ultima sub() rileva la max distanza che intercorre tra un primo valore e un secondo,
tra il secondo e il terzo, 3 e 4 ecc. ecc
nel caso del valore 18 abbiamo in questi un solo valore quindi non calcolabile, avendo solo un inizio di partenza e non un secondo di fine per calcolare la distanza, come dire partiamo dalla terra verso l'ignoto quindi non sapremo mai dove sara' la nostra fine,
se noti io ho messo un Boolean per determinare da dove iniziare il conteggio e non dalla prima cella come si suppone

il Problema adesso e' per Rocco che dovrebbe spiegare quel +1 a cosa serve
( If Cells(i, 1) < ValriC + 1 Then inizio = True )
in quanto ho provato la modifica fatta da lui ma non riesco a capire cosa sta contando in quanto se metto il valore 2 e 3 il risultato e' sempre 12 con 4 e 5 il risultato e' 6
Rocco puoi spiegare con parole semplici dove e' il problema ????? di questi valori e come fai a calcolare questi ?????

ciao
di rocco (utente non iscritto) data: 22/03/2017 22:13:53
ciao Mister_x
e grazie ancora per gli aiuti e scusami di nuovo per essermi spiegato male fin da l'inizio
ti spiego quel +1 che ho messo:
come fatto da te (che funzionava bene ma non mi serviva perchè mi sono spiegato male fin da l'inizio) prendeva come rifferimento da cercare il valore sulla cella F1
se era 9 prendeva solo i valori 9
io invece avevo bisogno che se in F1 ci fosse stato il valore 9 doveva cercare non solo il 9 ma anche 8 7 6 5 4 3 2 e 1 (quindi tutti i valori da 9 in giu)
quindi se mettevo solo < ValriC (riferito a F1) mi avrebbe cercato 8 7 6 5 4 3 2 e 1
invece mettendo < ValriC+1 mi cerca 9 8 7 6 5 4 3 2 e 1 proprio come volevo io

di Mister_x (utente non iscritto) data: 22/03/2017 23:59:54
ciao rocco

quindi tu dovevi cercare e calcolare anche il valore minore o uguale a quello inserito
a questo punto tutte le formule inserite in precedenza necessitavano solamente di un minore o uguale e funzionavano tutte

formula in excel
=MAX(FREQUENZA(SE($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))>F1;RIF.RIGA($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))));SE($A$1:$A$98<=F1;RIF.RIGA($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))))))

=SE(A2<=$F$1;0;B1+1)

ciao

=MAX(FREQUENZA(SE($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))>F1;RIF.RIGA($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))));SE($A$1:$A$98<=F1;RIF.RIGA($A$1:INDIRETTO("A"&MAX(INDICE((A1:A10000<>"")*RIF.RIGA(1:10000);)))))))
in B2 con valore in di partenza in B1 = 0
=SE(A2<=$F$1;0;B1+1)
mia sub() postata
Sub DistanzaX_X()
Dim i As Long
Dim Conta As Long, RipC As Long
Dim ValriC As Variant
Dim inizio As Boolean
Conta = 0
RipC = 0
ValriC = Range("F1").Value
inizio = False
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) <= ValriC Then inizio = True
If inizio = True Then Conta = Conta + 1
 If Cells(i, 1) <= ValriC Then
   If Conta > RipC Then
    RipC = Conta
   End If
  Conta = 0
 End If
Next i
Range("F12") = RipC - 1
End Sub

Vuoi Approfondire?