Recupero dei valori più grandi e più piccoli più vicini da un elenco in Microsoft Excel

Anonim

In Excel se hai un elenco di valori e non riesci a trovare una corrispondenza esatta, devi trovare il Più vicino Più grande o Più vicino Più piccolo valore. Ad esempio, se stai facendo un confronto di numeri e non trovi una corrispondenza esatta nell'intervallo, puoi utilizzare il numero successivo più grande o più piccolo e procedere avanti.

Possiamo usare una combinazione di Conte, Grande e Piccolo formula per ottenere il nostro output.

Countif viene utilizzato per contare il numero di volte in cui dati specifici vengono ripetuti in un intervallo di celle.

Sintassi =COUNTIF(intervallo,criteri)

Grande: Restituisce il k-esimo valore più grande in un set di dati.

Sintassi =LARGE(array,k)

Piccolo: Restituisce il k-esimo valore più piccolo in un set di dati.

Sintassi =SMALL(array,k)

Cerchiamo di capire qual è il numero più grande e più piccolo più vicino

Abbiamo una lista con numeri casuali 1,2,3,5,8,10 e abbiamo bisogno di una formula per valutare quanto segue:

- Se k è nell'elenco, restituisce k

- Se k non è nell'elenco, restituisce il valore successivo più alto. Ad esempio, se k =7, restituisce 8 (in caso di numero maggiore più vicino)

- Se k non è nell'elenco, restituisce il successivo valore più piccolo. Ad esempio, se k =7, restituisci 5

(in caso di numero inferiore più vicino)

Facciamo un esempio, abbiamo alcuni numeri casuali nella colonna A e dobbiamo trovare il valore in questo intervallo, che è il Più vicino Più piccolo o Più vicino Più grande alla cella B2 che contiene 46. Questo numero non è disponibile nella nostra gamma. Guarda lo screenshot qui sotto

Vediamo come calcoliamo il Numero più grande più vicino primo:

Possiamo controllare manualmente il valore successivo più grande a 46 che è 54.

Ora proviamo ad ottenere lo stesso risultato usando la formula -

  • Useremo una combinazione di GRANDE E COUNTIF funzioni per generare il numero più grande più vicino.
  • I criteri sono maggiore di '>' firma con Valore di ricerca, ad esempio cella B2
  • Il segno di maggiore combinato con il valore di ricerca restituirà 5 valori, ad esempio 54, 57, 61, 74, 83 che sono maggiori di 46.
  • Quando uniamo le funzioni Large & Countif, otterremo 54. Fare riferimento alla formula nelle celle G2 e G3 nell'istantanea sottostante.

PS: B2 in Countifformula è il valore di ricerca, ovvero 46

  • Nella cella C2, la formula utilizzata per trovare Più vicino Più grande il numero è =GRANDE($A$2:$A$8,CONTA.SE($A$2:$A$8;">"&B2))

Ora vediamo come calcoliamo il Numero più piccolo più vicino -

Possiamo controllare manualmente il successivo valore inferiore a 46 che è 28.

Otteniamo lo stesso output usando la formula -

  • Useremo una combinazione di PICCOLO E COUNTIFfunzioni per generare il numero più piccolo più vicino.
  • I criteri sono minore di '<' con valore di ricerca, ovvero cella B2
  • Meno di segno combinato con il valore di ricerca restituirà 2 valori, ovvero 16 e 28 che sono inferiori a 46.
  • Quando uniamo le funzioni Small &Countif, otteniamo 28. Fare riferimento alla formula nelle celle G5 e G6 nell'istantanea seguente.

Vedi sotto screenshot con formula:

Nota: la cella B2 in CountifFormula è il valore di ricerca, ovvero 46

  • Nella cella D2, la formula utilizzata per trovare Più vicino Più piccolo il numero è =PICCOLO($A$2:$A$8,CONTA.SE($A$2:$A$8;"<"&B2))

Questo è il modo in cui possiamo restituire il numero più grande più vicino e il numero più piccolo vicino.