Vai al contenuto principale

Come trovare il valore più vicino o più vicino (numero o stringa di testo) in Excel?

Supponendo di avere un elenco di numeri in una colonna, e ora è necessario trovare il valore più vicino o più vicino a un dato valore dall'elenco di numeri. Come lo affronti? In realtà, puoi trovare il valore più vicino o il valore più vicino in Excel con i seguenti passaggi.

Trova il numero più vicino o più vicino con la formula di matrice

Ad esempio, hai un elenco di numeri nella colonna A e ora troverai il valore più vicino o il valore più vicino di 18 dalla colonna A. Puoi farlo come segue:

Seleziona una cella vuota e inserisci la formula seguente e premi il tasto CTRL + MAIUSC + INVIO chiavi insieme.

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

Nota: In questa formula di matrice di {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))},

  • B3: B22 è l'intervallo in cui vuoi trovare il valore specifico
  • E2 è il valore di ricerca con cui vuoi essere confrontato.
nota nastro La formula è troppo complicata da ricordare? Salva la formula come una voce di testo automatico per riutilizzarla con un solo clic in futuro!
Per saperne di più ...     Versione di prova gratuita

Seleziona facilmente tutti i numeri più vicini nell'intervallo di deviazione del valore specificato con Kutools per Excel

A volte, potresti voler scoprire e selezionare tutti i valori dell'armadio sul valore specificato in un intervallo. In realtà, possiamo definire un valore di deviazione e quindi applicare Kutools per Excel Seleziona celle speciali utilità per scoprire e selezionare facilmente tutti i valori più vicini all'interno dell'intervallo di diviazione di dare valore.

Kutools for Excel- Include più di 300 strumenti utili per Excel. 60 giorni di prova gratuita delle funzionalità complete, nessuna carta di credito richiesta! Get It Now

Ad esempio, nel nostro esempio definiamo il valore di deviazione come 2 e il valore dato è 18. Pertanto, dobbiamo scoprire e selezionare valori tra 16 (= 18-2) e 20 (= 18 + 2). Si prega di visualizzare i seguenti passaggi:

1. Seleziona l'intervallo in cui cercherai i valori più vicini al valore fornito, quindi fai clic su Kutools > Seleziona > Seleziona celle specifiche.

2. Nella finestra di dialogo Seleziona celle specifiche di apertura,
(1) Controllare il file Cella opzione nel Tipo di selezione sezione;
(2) Nella Tipo specifico sezione, fare clic sul primo elenco a discesa e selezionare Maggiore o uguale a da esso e digita 16 nella casella seguente, quindi selezionare Minore o uguale a dal secondo elenco a discesa e digitare 20 nella casella seguente. Vedi screenshot a sinistra:

3. Clicca il Ok pulsante per applicare questa utilità. Quindi viene visualizzata una finestra di dialogo che mostra quante celle sono state selezionate. E vedrai tutti i valori più vicini all'interno dell'intervallo di deviazione del valore dato sono selezionati come mostrato nell'immagine sottostante:


Trova la stringa di testo più vicina o più vicina con un pratico strumento

Se hai installato Kutools per Excel, puoi applicare la sua funzione Fuzzy Find per trovare facilmente le stringhe di testo più vicine da un determinato intervallo in Excel. Si prega di fare quanto segue:

Kutools for Excel- Include più di 300 strumenti utili per Excel. 60 giorni di prova gratuita delle funzionalità complete, nessuna carta di credito richiesta! Get It Now

1. Clic Kutools > Trovare > Ricerca fuzzy per abilitare il riquadro Ricerca fuzzy nella cartella di lavoro.

2. Nel riquadro Ricerca fuzzy, configurare come segue ;
(1) Controllare il file Specificato opzione e seleziona l'intervallo in cui cercherai le stringhe di testo più vicine;
(2) Controllare il file Trova per testo specificato opzione;
(3) Vai al Testo casella e digita il testo specificato di cui troverai le stringhe di testo più vicine;
(4) Nella Numero massimo di caratteri diversi casella, digita un numero. Nel mio caso, digito 1;
(5) Dentro La lunghezza della stringa di celle è almeno casella, digita un numero. Nel mio caso, digito 5;

3. Clicca il Trovare pulsante.

Ora espandi i risultati della ricerca e vedrai che tutte le stringhe di testo più vicine vengono elencate in blocco. Vedi screenshot:


Demo: seleziona tutti i valori più vicini nell'intervallo di deviazione di un dato valore

Kutools for Excel: Oltre 300 strumenti utili a portata di mano! Inizia oggi la tua prova gratuita di 30 giorni senza limitazioni di funzionalità. Scarica ora!

I migliori strumenti per la produttività in ufficio

Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   Più di 50 Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

scheda kte 201905


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do we do this if our data is filtered?
This comment was minimized by the moderator on the site
copy the filtered data to a new sheet
This comment was minimized by the moderator on the site
Using the formula how would you return the value next to 17 if there was another column next to number like names. So if 17 is the closest in rang the name next to 17 (John) would be returned?


Example: 18 is nearest to 17 so the return value would be John


Numbers Names
38 Tammy
17 John
20 Amy
This comment was minimized by the moderator on the site
You can use the Approximate match of VLOOKUP function to solve this problem.
=VLOOKUP(E2,A1:B15,2,TRUE)
This comment was minimized by the moderator on the site
I used this formula =INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0)) and it works great. However i have found that where the source number is exactly between two numbers in the range, the lower range number is selected to be the closest.

eg: Searching for the closest number to 9 in the range: 6, 8, 10, 12. It will chose 8 instead of 10. Rounding convention is to round up if exactly half way between. Is there a workaround? Thanks.
This comment was minimized by the moderator on the site
ITS NOT WORKING its #N/A somehow
This comment was minimized by the moderator on the site
Hi DAKOT,
=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)) is an array formula, after entering it, please remember to press the Ctrl + Shift + Enter keys together.
This comment was minimized by the moderator on the site
Hi,
im using that formula in finding closest date, it is working.but i want to add condition: closest date that is less than 30 days of the current date (today).it is possible?
Anyone can help please?thank you
This comment was minimized by the moderator on the site
Hi farolito,
How about changing the value you will compare with to =TODAY() in Cell D1?
This comment was minimized by the moderator on the site
Hi, I can get it to work, amazing, BUT not when I input '1' as my 'match_type', instead of the '0' that you used. I want to return values less than or equal to, not just closest to +/-. If I enter 1 instead of 0, it doesn't work. Thoughts on why this might be?
This comment was minimized by the moderator on the site
Great formula -thank you-just a quick question. Anyone know how to highlight the cell that is closest in the match so in long lists it is easy to find??
This comment was minimized by the moderator on the site
If you have the row number from the formula above then you could set a conditional formatting rule on the search array to highlight a cell if it lies on that row.
This comment was minimized by the moderator on the site
It does not work for me! Excel says that there is an error :(
This comment was minimized by the moderator on the site
My bad This will look for all nearest date to today in column d =LARGE(D:D,COUNTIF(d:D,">="&TODAY())) http://WWW.excelireland.com
This comment was minimized by the moderator on the site
Hi, I tried the above formula. however, it is giving me a #N/A. 1. Copy pasted the range from A2 to A43. 2. Formula given was : =INDEX(A2:A43,MATCH(MIN(ABS(A2:A43-H1)),ABS(A2:A43-H1),0)) 3. Press control +Shift +enter Can anybody help me as to what wrong I am doing. :(
This comment was minimized by the moderator on the site
Replace the " , " separator for " ; "
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations