Roger ha chiesto se c’era un modo per calcolare l’intervallo tra le occorrenze dei valori in un elenco. Ad esempio, ha diverse migliaia di numeri nella colonna A. Osservando il valore nella cella A351, l’ultima volta che il valore si è verificato nell’elenco era nella cella A246. Vorrebbe una formula che potrebbe essere inserita nella cella B351 e restituire 105, la differenza tra 351 e 246.

Questo approccio è difficile da implementare in Excel perché Excel non è molto bravo a cercare all’indietro, su una colonna. Se la premessa può essere invertita, l’attività diventa molto più semplice. Ad esempio, se una formula nella cella B246 potesse restituire il valore 105, indicando l’intervallo fino alla successiva occorrenza del valore nella cella A246, invece di calcolare l’ultima occorrenza. La formula seguente calcola l’occorrenza successiva del valore nella cella A1:

=MATCH(A1,A2:$A$65536,0)

Posiziona questa formula nella cella B1 e copiala comunque molte celle sono necessarie. Se il valore nella colonna A non si verifica di nuovo nella colonna, la formula restituisce l’errore # N / D. Se preferisci che la formula restituisca 0, allora funziona:

=IF(ISNA(MATCH(A1,A2:$A$65536,0)),0,MATCH(A1,A2:$A$65536,0))

Se devi assolutamente contare verso l’alto (trova l’occorrenza precedente invece dell’occorrenza successiva), il modo più semplice per farlo è con una funzione definita dall’utente. La seguente funzione, RowInterval, guarderà indietro attraverso un intervallo specificato e restituirà l’intervallo desiderato:

Function RowInterval(TestCell As Range, LookHere As Range) As Long     Dim varValue As Variant     Dim lngRow As Long

Application.Volatile     varValue = TestCell.Value          'Check for occurrences of the test value in the search range     If WorksheetFunction.CountIf(LookHere, varValue) > 0 Then         With LookHere             'Get the last row of the search range             lngRow = .Row + .Rows.Count - 1             'Start with the last cell in the search range and work up             Do Until .Item(lngRow, 1).Value = varValue                 lngRow = lngRow - 1             Loop         End With         'Subtract the number of the row containing the found occurrence         'from the number of the row containing the test value         RowInterval = TestCell.Row - lngRow     End If End Function

Per utilizzare la funzione, inserire la seguente formula nella cella B2, quindi copiare la formula sul numero di celle desiderate:

=RowInterval(A2,A$1:A1)

_Nota: _

Se desideri sapere come utilizzare le macro descritte in questa pagina (o in qualsiasi altra pagina dei siti ExcelTips), ho preparato una pagina speciale che include informazioni utili.

Fare clic qui per aprire quella pagina speciale in una nuova scheda del browser.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (2338) si applica a Microsoft Excel 97, 2000, 2002 e 2003. Puoi trovare una versione di questo suggerimento per l’interfaccia a nastro di Excel (Excel 2007 e versioni successive) qui:

Calculating the Interval between Occurrences.