Come trovare il valore più vicino o prossimo in Excel?

Supponiamo di avere un elenco di numeri nella Colonna B e di dover determinare quale valore sia più vicino a un numero dato — ad esempio, 18. Utilizzando una formula matrice in Excel, puoi identificare questo valore in modo efficiente senza dover scorrere manualmente l'elenco.

Per iniziare, seleziona una cella vuota e inserisci la seguente formula. Una volta digitata la formula, assicurati di premere Ctrl + Shift + Enter invece di solo Invio. Questo garantirà che la formula venga eseguita come una formula matrice, necessaria affinché funzioni correttamente:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Questo approccio è più adatto quando è necessario recuperare il singolo numero più vicino da un intervallo continuo. Funziona bene nella maggior parte dei casi in cui l'accuratezza numerica e le corrispondenze esatte sono cruciali. Tuttavia, tieni presente che le formule matrice possono essere dispendiose in termini di risorse in set di dati molto grandi. Se riscontri problemi di prestazioni o ricevi messaggi di errore come #VALORE!, verifica attentamente i tuoi riferimenti di cella e assicurati di premere Ctrl + Shift + Enter correttamente.

Ci sono momenti in cui potresti non aver bisogno solo del singolo valore più vicino, ma desideri selezionare tutti i numeri che rientrano in un certo intervallo rispetto al tuo valore obiettivo — spesso chiamato intervallo di deviazione. Kutools per Excel offre una soluzione pratica tramite la sua funzione Seleziona Celle Speciali, permettendo una rapida selezione di tutti i valori entro una differenza specificata dal tuo obiettivo.

Ad esempio, supponiamo che il tuo valore obiettivo sia 18 e hai determinato un valore di deviazione di 2. Ciò significa che vuoi selezionare tutti i valori nel tuo intervallo compresi tra 16 (18–2) e 20 (18+2). Ecco come puoi ottenere ciò passo dopo passo:

1. Seleziona l'intervallo che desideri cercare (ad esempio, B3:B22), quindi vai su Kutools > Seleziona > Seleziona Celle Specifiche.

2. Nella finestra di dialogo Seleziona Celle Specifiche:

• Sotto Tipo di Selezione, scegli Cella.
• In Tipo Specifico:
  - Imposta il primo menu a discesa su Maggiore o uguale a e inserisci 16 nella casella.
  - Imposta il secondo menu a discesa su Minore o uguale a e inserisci 20.

3. Clicca OK per eseguire. Kutools ti notificherà quanti celle soddisfano i tuoi criteri ed evidenzierà tutti i valori più vicini entro la deviazione specificata come mostrato di seguito:

Questa soluzione è ideale per identificare rapidamente tutti i valori vicini in blocco, specialmente quando si gestiscono ampi intervalli con tolleranze variabili. Nota che l'accuratezza della tua selezione dipende dall'impostazione chiara della tua deviazione — se la tua deviazione è troppo stretta o ampia, potresti perdere dati rilevanti o includere valori indesiderati.

Per gli utenti che cercano automazione o che devono eseguire ricerche personalizzate del valore più vicino — sia per dati numerici che testuali — attraverso più fogli o grandi set di dati, una macro VBA può essere una soluzione efficiente e flessibile. Programmando Excel per controllare sistematicamente la differenza tra il tuo obiettivo e tutti i candidati, puoi recuperare non solo il numero più vicino, ma anche la stringa più vicina in base alla distanza del testo.

Questo approccio è vantaggioso quando è richiesta automazione integrata, specialmente su intervalli troppo grandi per metodi manuali o per l'applicazione di attività ricorrenti. Tuttavia, tieni presente che le macro VBA richiedono l'abilitazione delle macro e una certa familiarità con l'ambiente VBA. Prima di eseguire qualsiasi macro, esegui sempre il backup dei tuoi dati per prevenire perdite involontarie.

1. Clicca Sviluppatore  > Visual Basic. Nella finestra Microsoft Visual Basic for Applications, clicca Inserisci > Modulo, e copia il seguente codice nel modulo:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. Quindi, vai al tuo foglio di lavoro, e inserisci questa formula: =FindClosest(B3:B22, E2) in una cella vuota. Premi il tasto Invio per ottenere il valore più vicino.

Quando si esaminano o presentano i dati, è spesso utile identificare visivamente i valori più vicini a un obiettivo senza filtrare o riorganizzare i dati. La funzione Formattazione Condizionale incorporata di Excel ti consente di evidenziare le celle che sono più vicine al tuo valore obiettivo, rendendole facili da individuare a colpo d'occhio. Sebbene questo metodo non restituisca il valore esatto stesso, è efficace per un'analisi rapida dei dati e per dare enfasi visiva.

Il vantaggio principale di questo metodo è l'evidenziazione dinamica e non distruttiva che può adattarsi man mano che i dati o i valori obiettivo cambiano. È particolarmente adatto per dashboard, presentazioni e scenari di revisione dove la visibilità è fondamentale. Potrebbe essere meno preciso se più di un valore condivide la stessa “vicinanza” e non restituisce il valore stesso per ulteriori elaborazioni.

1. Seleziona l'intervallo di celle che desideri analizzare (ad esempio, B3:B22).

2. Nella scheda Home, clicca Formattazione Condizionale > Nuova Regola.

3. Scegli Usa una formula per determinare quali celle formattare nella finestra di dialogo. Poi, nella casella della formula, inserisci la seguente formula:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Clicca Formato e scegli un colore di evidenziazione, poi clicca OK e di nuovo OK per applicare la regola.

Questo evidenzierà tutte le celle nel tuo intervallo selezionato i cui valori sono altrettanto vicini al valore obiettivo in E2.

Se stai lavorando con grandi intervalli o riscontri risultati inaspettati, verifica attentamente che i tuoi riferimenti siano corretti e che i riferimenti assoluti/relativi siano impostati come previsto (usa $ per bloccare la cella obiettivo e i riferimenti dell’intervallo).