Valutazione 4.87/ 5 (100.00%) 5838 voti

Condividi:        

Formula per ricerca assenza di data e ora sequenziale

Vuoi potenziare i tuoi documenti Word? Non sai come si fa una macro in Excel? Devi creare una presentazione in PowerPoint?
Oppure sei passato a OpenOffice e non sei sicuro di come lavorare al meglio?

Moderatori: Anthony47, Flash30005

Formula per ricerca assenza di data e ora sequenziale

Postdi MIK27 » 13/06/15 11:13

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
MIK27
Newbie
 
Post: 6
Iscritto il: 13/06/15 10:50

Sponsor
 

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi CANAPONE » 15/06/15 05:43

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.
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi MIK27 » 15/06/15 09:24

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
MIK27
Newbie
 
Post: 6
Iscritto il: 13/06/15 10:50

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi CANAPONE » 15/06/15 09:44

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)
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi CANAPONE » 15/06/15 18:56

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
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi MIK27 » 16/06/15 10:23

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
MIK27
Newbie
 
Post: 6
Iscritto il: 13/06/15 10:50

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi CANAPONE » 16/06/15 13:01

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.
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi MIK27 » 16/06/15 16:29

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
MIK27
Newbie
 
Post: 6
Iscritto il: 13/06/15 10:50

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi CANAPONE » 17/06/15 06:10

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
---------------
Excel 2004 su Mac
Excel 2010 su Windows 8
CANAPONE
Utente Senior
 
Post: 425
Iscritto il: 22/11/10 15:43
Località: Firenze

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi MIK27 » 21/06/15 09:39

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
MIK27
Newbie
 
Post: 6
Iscritto il: 13/06/15 10:50

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi Anthony47 » 22/06/15 00:14

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
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi MIK27 » 22/06/15 09:35

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
MIK27
Newbie
 
Post: 6
Iscritto il: 13/06/15 10:50

Re: Formula per ricerca assenza di data e ora sequenziale

Postdi Anthony47 » 22/06/15 13:22

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
Anthony
Win7 + Office 2010 Ita; Win 7 + Office 2013 Ita
Xp + Office 2003 Ita
E voi cosa usate? (per istruzioni vedere viewtopic.php?f=26&t=97449)
Avatar utente
Anthony47
Moderatore
 
Post: 13885
Iscritto il: 21/03/06 16:03
Località: Ivrea


Torna a Applicazioni Office Windows


Topic correlati a "Formula per ricerca assenza di data e ora sequenziale":


Chi c’è in linea

Visitano il forum: Nessuno e 11 ospiti