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

Supponiamo di avere un elenco di numeri nella Colonna B e di dover determinare quale valore è più vicino a un dato numero — ad esempio 18. L'utilizzo di una formula matrice in Excel ti consente di identificare rapidamente questo valore 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. In questo modo si garantirà che la formula venga eseguita come una formula matrice, cosa 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 dataset molto grandi. Se riscontri problemi di prestazioni o ricevi messaggi di errore come #VALORE!, ricontrolla 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 desiderare di selezionare tutti i numeri che rientrano in un certo intervallo dal 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 che tu abbia determinato un valore di deviazione di 2. Ciò significa che desideri selezionare tutti i valori nel tuo intervallo compresi tra 16 (18-2) e 20 (18+2). Ecco come puoi raggiungere questo obiettivo 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 Cell.
• In Tipo specifico:
  - Imposta la prima casella a discesa su Maggiore o uguale a e inserisci 16 nella casella.
  - Imposta la seconda casella 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 all'interno della 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 larga, potresti perdere dati rilevanti o includere valori indesiderati.

Per gli utenti che cercano automazione o che hanno bisogno di 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 quando si applicano attività ricorrenti. Tuttavia, tieni presente che le macro VBA richiedono l'abilitazione delle macro e una conoscenza di base dell'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 per Applicazioni, 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 integrata di Excel ti consente di evidenziare le celle che sono più vicine al tuo valore obiettivo, rendendole facili da individuare a colpo d'occhio. Anche se questo metodo non restituisce il valore esatto stesso, è efficace per analisi rapide dei dati e per dare enfasi visiva.

Il principale vantaggio di questo metodo è l'evidenziazione dinamica e non distruttiva che può adattarsi quando 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 fornisce 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 ugualmente più vicini al valore obiettivo in E2.

Se stai lavorando con grandi intervalli o riscontri risultati inaspettati, verifica nuovamente 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 di intervallo).