Formula per ricerca assenza di data e ora sequenziale

[MIK27]

Ciao a tutti
Sono nuovo del Forum e desidero salutare tutti e ringraziarvi per il prezioso contributo che date.
Il mio problema consiste nel trovare in un foglio Excel una formula che verifichi da un lungo elenco di date sequenziali e orarie per tutto un'anno solare (circa 8600 righe)
es. 13/03/2015 00:00
13/06/2015 01:00
13/06/2015 02:00
che non ci siano buchi di date o anche solo di ore

Grazie Michele

[CANAPONE]

Ciao,

condivido una prima risposta

Se le date sono in A1:A8600

in B2 da copiare in basso

=O(ASS(ARROTONDA(A4-A5;2))<>0,04;ASS(ARROTONDA(A4-A3;2))<>0,04)

Filtrando i VERO evidenzi le coppie di data/ora non sequenziali.

Ci sono sicuramente soluzioni migliori.

[MIK27]

Ciao Canapone,grazie per la risposta, ma
mi puoi spiegare per favore, piu' semplicemente, come evidenziare eventuali buchi di data o ora , non essendo un esperto di excel.
( se inserisco la formula descritta in colonna B mi da' sempre VERO anche se ci sono delle date mancanti) !!???
Grazie
Ps le date sono tutte in colonna A1 nella forma gg/mm/aa hh:mm

[CANAPONE]

Ciao

ti condivido il file dove ho tirato giù le formule

https://www.dropbox.com/s/6wi3jz7irznnx ... .xlsx?dl=0

Forse può essere d'aiuto avere un piccolo estratto del file dove stai lavorando e sapere la versione di Excel in uso.


Saluti

Edit: segnalo un refuso nella precedente comunicazione.

La prima formula in B2 da copiare in basso deve leggere se ci sono differenze superiori ad un'ora verso la data precedente o successiva

=O(ASS(ARROTONDA(A2-A1;2))<>0,04;ASS(ARROTONDA(A2-A3;2))<>0,04)

[CANAPONE]

Ciao di nuovo,

avendo la lista delle date in A2:A8715 (incompleta dovrebbe esserci 365*24=8760 date/orari) per ottenere gli elementi mancanti
puoi provare ad adattare al tuo lavoro una formula del tipo

=SE.ERRORE(AGGREGA(15;6;ARROTONDA($A$2+1/24*RIF.RIGA($A$1:$A$8760);5)/VAL.ERRORE(CONFRONTA(ARROTONDA($A$2+1/24*RIF.RIGA($A$1:$A$8760);5);INDICE(ARROTONDA($A$2:$A$8715;5);0);0));RIF.RIGA(A1));"")

da copiare in basso finchè non produce più alcun risultato

AGGREGA è disponibile in Excel 2010 e versioni successive.

Excel 2007 richiederebbe

=SE.ERRORE(PICCOLO(SE(VAL.ERRORE(CONFRONTA(ARROTONDA($A$2+1/24*RIF.RIGA($A$1:$A$8760);5);INDICE(ARROTONDA($A$2:$A$87146;5);0);0));ARROTONDA($A$2+1/24*RIF.RIGA($A$1:$A$8760);5));RIF.RIGA(A1));"")

da confermare con control + maiusc+invio

Saluti


https://www.dropbox.com/s/jn055hpr68v1u ... .xlsx?dl=0

[MIK27]

Ciao Canapone,
ieri non ho avuto tempo , comunque il tuo post di ieri delle 08:44 dove mi hai mandato il file in Dropbox mi ha chiarito le idee ed effettivamente la formula sottoriportata funziona !

=O(ASS(ARROTONDA(A3-A4;2))<>0,04;ASS(ARROTONDA(A3-A2;2))<>0,04)
(appena c'e' un salto di ora mi da' la segnalazione "VERO")

Ti ringrazio tanto per l'aiuto che mi hai dato
Dovrei anche verificare dei fogli Excel con elechi di sole date senza ore (gg/mm/aa)
Volevo chiederti quale modifica fare a questa formula in quest'ultimo caso
Saluti e grazie di nuovo

[CANAPONE]

Ciao,

in A2 la prima data

in B3 accanto alla seconda data

=(ASS(A3-A4)*ASS(A3-A2)<>1)


selezionando i VERO dovresti individuare i buchi.

Anche in questo caso usando AGGREGA o PICCOLO(SE potresti ottenere in una colonna a parte tutte le date mancanti.

Ci sono sicuramente altri modi.

[MIK27]

Bene Grazie.
Ti dispiace inviarmi la formula per individuare in una colonna tutte le date e ore mancanti, sempre attraverso il link di Dropbox ?
io uso Excel 2007
Grazie di nuovo Saluti

[CANAPONE]

Ciao,

nell'esempio la lista incompleta è in A2:A8611

Per ottenere data/ora mancanti

Codice: Seleziona tutto

=SE.ERRORE(PICCOLO(SE(VAL.ERRORE(CONFRONTA(ARROTONDA($A$2+1/24*RIF.RIGA($A$1:$A$8760);5);INDICE(ARROTONDA($A$2:$A$86110;5);0);0));ARROTONDA($A$2+1/24*RIF.RIGA($A$1:$A$8760);5));RIF.RIGA(A1));"")

Per ottenere date mancanti

Codice: Seleziona tutto

=SE.ERRORE(PICCOLO(SE(VAL.ERRORE(CONFRONTA(INT($A$2+RIF.RIGA(A$1:$A$364));INDICE(INT($A$2:$A$8611);0);0));INT($A$2+RIF.RIGA($A$1:$A$364)));RIF.RIGA(A1));"")

Le formule richiedono lo stato matriciale: dopo essere state copiate devono essere riconfermate con control+maiusc+invio prima di ricopiarle in basso ed ottenere l'elenco.

Le formule sono parecchio impegnative per il processore. Fai qualche prova tenendo eventuali altri documenti chiusi.

Saluti


https://www.dropbox.com/s/o9h9tb2mff614 ... .xlsx?dl=0

[MIK27]

Si, funziona tutto , anche se effettivamente quest'ultima formula che ti da' direttamente i dati mancanti e' parecchio pesante(con il mio Intel Centrino dual core impiega circa 3 ore per processare un file di circa 8000 righe).
Comunque la formula che ti da' Vero o Falso credo sia il compromesso migliore ,tenuto conto che devo verificare ben piu' di 100 di questi file!
Ciao e Grazie di tutto

[Anthony47]

Direi che questo caso e' meglio affrontarlo con una macro invece che con le magie di canapone...
Ad esempio questa:

Codice: Seleziona tutto

Sub missing()
Dim WArr, ResArr(), LastA As Long, OneH As Double, OneHM As Double, I As Long, myLast As Double
Dim Dest As String, K As Long, jJ As Long, kK As Long
'
Dest = "G"             '<<< La colonna da cui si cominceranno a scrivere i risultati (2 colonne)
OneH = 1 / 24
OneHM = OneH + 0.001
LastA = Cells(Rows.Count, 1).End(xlUp).Row
WArr = Range("A1").Resize(LastA, 1).Value
'
maxh = (Int(Application.WorksheetFunction.Max(Range("A:A"))) - Int(Application.WorksheetFunction.Min(Range("A:A")))) * 24
'
ReDim ResArr(1 To maxh, 1 To 2)

Range(Dest & 1).Resize(Rows.Count, 2).ClearContents
jJ = 1: kK = 1
For I = LBound(WArr, 1) + 1 To UBound(WArr, 1)
    If IsDate(WArr(I, 1)) Then
        If myLast > 0 Then
            If WArr(I, 1) - myLast > OneHM Then
                ResArr(jJ, 1) = myLast + OneH: jJ = jJ + 1
                Do
                    If WArr(I, 1) - ResArr(jJ - 1, 1) > OneHM Then
                        ResArr(jJ, 1) = ResArr(jJ - 1, 1) + OneH: jJ = jJ + 1
                    Else
                        Exit Do
                    End If
                Loop
            End If
            For K = 1 To (Int(WArr(I, 1)) - (mylastd) - 1)
                ResArr(kK, 2) = mylastd + 1: kK = kK + 1
                mylastd = mylastd + 1
            Next K
        End If
        myLast = WArr(I, 1): mylastd = Int(myLast)
    End If
Next I
Range(Dest & 1).Resize(jJ, 2) = ResArr
End Sub


Da excel: Alt-F11 per aprire l' editor delle macro; Menu /Inserisci /Modulo; copia il codice e incollalo nel frame di dx. Personalizza la riga marcata <<< con la colonna in cui si cominceranno a scrivere i risultati (tot 2 colonne)
Torna quindi su Excel ed esegui la macro "missing": Alt-F8, seleziona "missing" dall' elenco di macro disponibili, premi Esegui.
Le colonne con i risultati andranno formattate manualmente.

Ciao

[MIK27]

Grazie Anthony per l'interessamento,ma per applicare la macro, scusami, ma dovresti spiegarmela passo passo in modo piu' semplice,in quanto la mia conoscenza di Excel e' limitata

Ciao

[Anthony47]

Devi seguire le istruzioni che ti avevo gia' inserito:

Da excel: [premi] Alt-F11 per aprire l' editor delle macro; Menu /Inserisci /Modulo; copia il codice e incollalo nel frame di dx. Personalizza la riga marcata <<< con la colonna in cui si cominceranno a scrivere i risultati (tot 2 colonne)
Torna quindi su Excel ed esegui la macro "missing": [premi] Alt-F8, seleziona "missing" dall' elenco di macro disponibili, premi Esegui.
Le colonne con i risultati andranno formattate manualmente.

Se vuoi mantenere la macro nel file allora dovrai salvarlo come ".xlsm", cioe' "file abilitato all' esecuzione di macro".

Ciao