Individuare il numero mancante di un elenco progressivo

Question

Salve a tutti.

Come è possibile individuare il numero mancante da una serie come da esempio che segue:

1

2

3

4

6

6

6

7... regolare fino a 25

26

26

28

etc

In pratica in una cella vorrei vedere il numero 5  e 27. Questo è un elenco manuale e che indica il progressivo dei mezzi di un piano di carico di un lotto di produzione.

Grazie per il Vs aiuto

Giovanni

Answer 1

Nel ringraziare per la soluzione, mi permetto di chiedere se possibile una piccola modifica: quando la serie è completa nella cella dove vi è la funzione appare "#VALORE!". Si potrebbe non visualizzare?

Ciao Giovanni,

Facta non verba ...

'==========>>

Option Explicit

'----------->>

Public Function NumeriMancanti(Intervallo As Range, _

                               Optional iMin, _

                               Optional iMax)

    Dim arrIn As Variant

    Dim oDic As Object, oDic2 As Object

    Dim arrOut() As Variant, arrKeys As Variant

    Dim i As Long, j As Long

    NumeriMancanti = CVErr(xlValue)

    arrIn = Intervallo.Value

    Set oDic = CreateObject("Scripting.Dictionary")

    Set oDic2 = CreateObject("Scripting.Dictionary")

    With Application

        If IsMissing(iMin) Then

            iMin = .Min(arrIn)

        End If

        If IsMissing(iMax) Then

            iMax = .Max(arrIn)

        End If

    End With

    With oDic

        For i = 1 To UBound(arrIn, 1)

            If Not .exists(arrIn(i, 1)) Then

                .Add key:=arrIn(i, 1), Item:=arrIn(i, 1)

            End If

        Next i

    End With

    With oDic2

        For i = iMin To iMax

            .Add key:=i, Item:=i

        Next i

    End With

    With oDic

        arrKeys = oDic2.keys

        For i = 0 To oDic2.Count - 1

            If Not .exists(arrKeys(i)) Then

                j = j + 1

                ReDim Preserve arrOut(1 To j)

                arrOut(j) = arrKeys(i)

            End If

        Next i

    End With

    If CBool(j) Then

        NumeriMancanti = Join(arrOut, ";")

Else

NumeriMancanti = vbNullString

    End If

    Set oDic = Nothing

    Set oDic2 = Nothing

End Function

'==========>>

Visto la risposta elegante di Paolo, ti farei notare che sia sempre possibile contrassegnare più di una risposta come Risposta; in questo modo riconosceresti la risoluzione del problema e, allo stesso tempo, il valido contributo di più rispondenti. 

===

Regards,

Norman

Answer 2

Salve a tutti.

Come è possibile individuare il numero mancante da una serie come da esempio che segue:

1

2

3

4

6

6

6

7... regolare fino a 25

26

26

28

etc

In pratica in una cella vorrei vedere il numero 5  e 27. Questo è un elenco manuale e che indica il progressivo dei mezzi di un piano di carico di un lotto di produzione.

Grazie per il Vs aiuto

Giovanni

=SE(MAX(A$1:A$100)<PICCOLO(SE(CONTA.SE(A$1:A$100;RIF.RIGA($A$1:$A$100))=0;RIF.RIGA($A$1:$A$100);"");RIF.RIGA(A1));"";PICCOLO(SE(CONTA.SE(A$1:A$100;RIF.RIGA($A$1:$A$100))=0;RIF.RIGA($A$1:$A$100);"");RIF.RIGA(A1)))

matriciale (da confermare con Ctrl+Maiusc+Invio) e da trascinare in basso fino a che restituisca casella vuota.

NB: in una cella? Ho realizzato ora. Beh, vedi si ti va bene prima così.

Answer 3

Salve a tutti.

Come è possibile individuare il numero mancante da una serie come da esempio che segue:

<cut>

Mettiamo che la tua serie inizi da A1, una possibile soluzione (se ho capito):

B2: =SE(O(A2=A1;A2=A1+1);"";"Manca: " & A1+1)

Espandi verso il basso la formula.

Answer 4

Ciao Giovanni,

Prova come segue:

Alt-F11 per aprire l'editor di VBA

Alt-IM per inserire un nuovo modulo di codice

Nel nuovo modulo vuoto, incolla il seguente codice:

'==========>>

Option Explicit

'----------->>

Public Function NumeriMancanti(Intervallo As Range, _

                               Optional iMin, _

                               Optional iMax)

    Dim arrIn As Variant

    Dim oDic As Object, oDic2 As Object

    Dim arrOut() As Variant, arrKeys As Variant

    Dim i As Long, j As Long

    NumeriMancanti = CVErr(xlValue)

    arrIn = Intervallo.Value

    Set oDic = CreateObject("Scripting.Dictionary")

    Set oDic2 = CreateObject("Scripting.Dictionary")

    With Application

        If IsMissing(iMin) Then

            iMin = .Min(arrIn)

        End If

        If IsMissing(iMax) Then

            iMax = .Max(arrIn)

        End If

   End With

    With oDic

        For i = 1 To UBound(arrIn, 1)

            If Not .exists(arrIn(i, 1)) Then

                .Add key:=arrIn(i, 1), Item:=arrIn(i, 1)

            End If

        Next i

    End With

    With oDic2

        For i = iMin To iMax

            .Add key:=i, Item:=i

        Next i

    End With

    With oDic

        arrKeys = oDic2.keys

        For i = 0 To oDic2.Count - 1

            If Not .exists(arrKeys(i)) Then

                j = j + 1

                ReDim Preserve arrOut(1 To j)

                arrOut(j) = arrKeys(i)

            End If

        Next i

    End With

    If CBool(j) Then

        NumeriMancanti = Join(arrOut, ";")

    End If

    Set oDic = Nothing

    Set oDic2 = Nothing

End Function

'==========>>

Alt-Q per chiudere l'editor di VBA e tornare a Excel.

Questa funzione può essere utilizzata come una funzione nativa di Excel, Essa può essere utilizzata anche direttamente in VBA. La funzione può essere utilizzata con una sequenza di numeri crescenti o decrescenti oppure con una sequenze non ordinata. La funzione accetterebbe anche una sequenza che comprende numeri e caratteri alfabetici. La funzione può essere utilizzata con un intervallo del tipo 1-d verticale o orizzontale. .

Questa  funzione accetta tre argomenti: il primo è l'intervallo di interesse; il secondo argomento definisce un valore minimo,  opzionale e il terzo argomento rappresenta un valore  massimo, opzionale.

Se solo il primo argomento, cioè l'intervallo, venga passato alla funzione, la funzione restituirà i numeri mancanti nell'intervallo. Se, oltre al primo argomento, un valore minimo venga passato alla funzione come secondo argomento opzionale, la funzione restituirà i numeri mancanti tra il minimo specificato e il massimo valore trovato nell'intervallo. Questo valore minimo può essere superiore o inferiore al valore minimo dell'intervallo. In modo analogo, il terzo argomento opzionale può essere passato alla funzione per precisare un massimo. Questo valore massimo può essere superiore o inferiore al valore massimo dell'intervallo. Inoltre, la funzione accetterà o restituirà numeri negativi.

Quindi, utilizzando la sequenza di numeri indicata nella tua domanda, la funzione potrebbe essere utilizata, ad esempio, come segue:

    =NumeriMancanti(A1:A29)         ===>       5;27

    =NumeriMancanti(A1:A29;-5)     ===>      -5;-4;-3;-2;-1;5;27

    =NumeriMancanti(A1:A36;6;35)  ===>       27;29;30;31;32;33;34;35

===

Regards,

Norman

Answer 5

Ciao Giovanni,

In pratica in una cella vorrei vedere il numero 5  e 27. 

Nella mia prima risposta, ho interpretato letteralmente la richiesta di fornire i numeri mancanti inunacella. Se questo era un modo di dire, e preferesti avere i risultati in celle verticalmente o orizzontalmente contigue, prova a sostituire il codice precedente con la seguente versione:

'==========>>

Option Explicit

'----------->>

Public Function NumeriMancanti(Intervallo As Range, _

                                Optional iMin, _

                                Optional iMax)

    Dim arrIn As Variant

    Dim oDic As Object, oDic2 As Object

    Dim arrOut() As Variant, arrKeys As Variant

    Dim i As Long, j As Long

    Dim blVertical As Boolean

    NumeriMancanti = CVErr(xlValue)

    arrIn = Intervallo.Value

    Set oDic = CreateObject("Scripting.Dictionary")

    Set oDic2 = CreateObject("Scripting.Dictionary")

    With Application

        If IsMissing(iMin) Then

            iMin = .Min(arrIn)

        End If

        If IsMissing(iMax) Then

            iMax = .Max(arrIn)

        End If

    End With

    With oDic

        For i = 1 To UBound(arrIn, 1)

            If Not .exists(arrIn(i, 1)) Then

                .Add key:=arrIn(i, 1), Item:=arrIn(i, 1)

            End If

        Next i

    End With

    With oDic2

        For i = iMin To iMax

            .Add key:=i, Item:=i

        Next i

    End With

    With oDic

        arrKeys = oDic2.keys

        For i = 0 To oDic2.Count - 1

            If Not .exists(arrKeys(i)) Then

                j = j + 1

                ReDim Preserve arrOut(1 To 1, 1 To j)

                arrOut(1, j) = arrKeys(i)

            End If

        Next i

    End With

    If CBool(j) Then

        With Application

            With .Caller

                blVertical = .Columns.Count <= .Rows.Count

            End With

            If blVertical Then

                NumeriMancanti = .Transpose(arrOut)

            Else

                NumeriMancanti = arrOut

            End If

        End With

    End If

    Set oDic = Nothing

    Set oDic2 = Nothing

End Function

'==========>>

Sul foglio, la funzione viene utilizzata in modo matriciale:  con intervallo abbondante rispetto a quello dei valori, immetti la formula che va confermata con Ctrl+Maiusc+Invio. 

===

Regards,

Norman