Come trovare il valore più vicino in Excel?

Si supponga 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. L'utilizzo di una formula matriciale in Excel consente di identificarlo in modo efficiente senza dover scorrere manualmente l'elenco.

Per iniziare, seleziona una cella vuota e inserisci la seguente formula. Una volta digitata, assicurati di premere Ctrl + Maiusc + Invio invece del semplice tasto Invio: in questo modo la formula verrà eseguita come formula matriciale, un requisito indispensabile per il suo corretto funzionamento.

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

Questo approccio è particolarmente indicato quando occorre recuperare il numero più vicino da un intervallo continuo. Funziona bene nella maggior parte dei casi in cui precisione numerica e corrispondenze esatte sono fondamentali. Tuttavia, tieni presente che le formule matriciali possono risultare onerose in termini di risorse con set di dati molto grandi. Se si verificano problemi di prestazioni o compaiono messaggi di errore come #VALORE!, ricontrolla i riferimenti di cella e assicurati di aver premuto correttamente Ctrl + Maiusc + Invio.

A volte il singolo valore più vicino potrebbe non bastare: potresti aver bisogno di selezionare tutti i numeri compresi entro un certo intervallo rispetto al valore di destinazione, spesso definito “intervallo di deviazione”. Kutools per Excel offre una soluzione pratica con la sua funzione Seleziona celle speciali, che ti permette di selezionare rapidamente tutti i valori la cui differenza dal valore di destinazione rientra nell’intervallo specificato.

Ad esempio, si ipotizzi che il valore di destinazione sia 18 e che si sia stabilito un valore di deviazione pari a 2. Ciò significa che si desidera selezionare tutti i valori nell'intervallo compresi tra 16 (18–2) e 20 (18+2). Ecco come procedere passo dopo passo:

1. Seleziona l'intervallo da cercare (ad esempio, B3:B22), quindi vai su Kutools > Seleziona > Seleziona celle specifiche.

2. Nella finestra di dialogo Seleziona celle specifiche:

• In Seleziona tipo, scegli Cella.
• In Specifica tipo:
  - Imposta il primo elenco a discesa su Maggiore o uguale a e inserisci 16 nella casella.
  - Imposta il secondo elenco a discesa su Minore o uguale a e inserisci 20.

3. Fare clic su OK per eseguire. Kutools notificherà il numero di celle corrispondenti ai criteri e evidenzierà tutti i valori più vicini entro la deviazione specificata, come mostrato di seguito:

Questa soluzione è perfetta per individuare rapidamente tutti i valori prossimi in blocco, soprattutto quando si lavora con ampi intervalli e tolleranze variabili. Ricorda che la precisione della selezione dipende da un’impostazione chiara della deviazione: se troppo ristretta o troppo ampia, potresti perdere dati correlati o includere valori indesiderati.

Per chi cerca automazione o deve eseguire ricerche personalizzate del valore più vicino – sia numerico che testuale – su più fogli o set di dati di grandi dimensioni, una macro VBA rappresenta una soluzione efficiente e flessibile. Programmando Excel per confrontare sistematicamente il valore di destinazione con tutti i candidati, è possibile individuare non solo il numero più vicino, ma anche la stringa più simile in base alla distanza testuale.

Questo approccio si rivela particolarmente vantaggioso quando è necessaria un’integrazione automatizzata, soprattutto su intervalli troppo ampi per metodi manuali o per 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, effettua sempre un backup dei dati per prevenire eventuali perdite accidentali.

1. Fare clic su Sviluppo > Visual Basic. Nella finestra Microsoft Visual Basic, Applications Edition, fare clic su Inserisci > Modulo e copiare 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. Successivamente, torna al foglio di lavoro e inserisci questa formula: =FindClosest(B3:B22; E2) in una cella vuota. Premi Invio per ottenere il valore più vicino.

Quando si esaminano o presentano dati, è spesso utile identificare visivamente i valori più vicini a un obiettivo senza dover filtrare o riordinare i dati. La funzione integrata di Excel Utilizza la formattazione condizionale ti permette di evidenziare le celle più vicine al valore di destinazione, rendendole immediatamente visibili. Sebbene questo metodo non restituisca il valore esatto, è perfetto per un’analisi rapida dei dati e per dare subito risalto ai risultati chiave.

Il principale vantaggio di questo metodo è l’evidenziazione dinamica e non distruttiva, che si adatta automaticamente a qualsiasi modifica nei dati o nel valore di riferimento. È ideale per dashboard, presentazioni e contesti di revisione in cui la visibilità è essenziale. Tuttavia, può risultare meno preciso quando più valori condividono lo stesso livello di “prossimità” e non restituisce il valore individuato per ulteriori elaborazioni.

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

2. Nella scheda Home, fai clic su Utilizza la formattazione condizionale > Nuova regola.

3. Nella finestra di dialogo, scegliere Utilizza una formula per determinare le celle da formattare e, nella casella della formula, inserire la seguente formula:

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

4. Fare clic su Formato e scegliere un colore di evidenziazione, quindi fare clic su OK e nuovamente su OK per applicare la regola.

Verranno così evidenziate tutte le celle nel proprio intervallo selezionato i cui valori sono più vicini al valore obiettivo presente in E2.

Se si lavora con intervalli ampi o si ottengono risultati inaspettati, verificare attentamente che i riferimenti siano corretti e che i riferimenti assoluti e relativi siano impostati come previsto (utilizzare il simbolo $ per bloccare la cella di destinazione e i riferimenti all’intervallo).