In questo articolo, impareremo come trovare l'ennesimo più piccolo con criteri e l'ennesimo più grande con criteri dalla tabella fornita in Excel.
Scenario:
In parole semplici, mentre si lavora con i numeri nei numeri di dati, a volte viene data una condizione, ad esempio quando è necessario cercare il quinto valore più alto dato. È possibile eseguire facilmente la soluzione a questo problema utilizzando le funzioni di Excel come spiegato di seguito.
L'ennesimo più grande con criteri
Come risolvere il problema?
Per questo articolo ci verrà richiesto di utilizzare la funzione LARGE. Ora faremo una formula con queste funzioni. Qui ci viene data una tabella e dobbiamo trovare l'ennesimo valore più grande nell'intervallo con determinati criteri.
Formula generica:
{ = GRANDE ( SE ( c_range = "valore" , intervallo ) , n ) }
c_range : gamma di criteri
valore : criteri corrispondono al valore
gamma : array di risultati
n : ennesimo più grande
Nota : Non inserire le parentesi graffe manualmente. Questa è una formula di matrice, deve usare Ctrl + Maiusc + Invio al posto di Enter dove restituisce #NUM! errore.
Esempio:
Tutti questi potrebbero essere fonte di confusione da capire. Quindi, testiamo questa formula eseguendola nell'esempio mostrato di seguito.
Qui abbiamo i dettagli dell'ordine con data dell'ordine, regione e prezzo dell'ordine.
Dobbiamo scoprire il quinto prezzo d'ordine più grande dalla regione orientale. Qui l'intervallo viene fornito utilizzando lo strumento excel dell'intervallo denominato.
regione (C3:C16)
prezzo (G3:G16)
In primo luogo, dobbiamo trovare il quinto valore più grande usando la funzione GRANDE e quindi eseguire l'operazione di somma su quei 5 valori. Ora useremo la seguente formula per ottenere la somma
Usa la formula:
{ = GRANDE ( SE ( regione = G5 , prezzo ) , F5 )}
Spiegazione:
- La funzione SE controlla i criteri che corrispondono a tutti i valori nella regione con un dato valore Est nella cella G5 e restituisce i corrispondenti valori VERI dalla fascia di prezzo.
= GRANDE ({58,41; 303,63; FALSO; 153,34; 82,84; FALSO; 520,01; FALSO; 177; FALSO; FALSO; 57,97; 97,72; FALSO}), F5)
- La funzione LARGE prende l'array che ha tutta la fascia di prezzo come mostrato sopra e restituisce il quinto valore più grande dall'array come mostrato nell'istantanea sottostante.
È possibile visualizzare la formula nella casella della formula come mostrato nell'istantanea sopra. Usa Ctrl + Maiusc + Invio per ottenere il risultato.
Come puoi vedere, la formula dell'array restituisce il quinto prezzo più grande $ 97,72 con la regione orientale.
Puoi anche alimentare array come array, criteri tra virgolette e valore n direttamente alla funzione invece di utilizzare il riferimento di cella o l'intervallo denominato.
Usa la formula:
{ = GRANDE ( SE ( DO3:DO16 = "Ovest" , RE3:RE16 ) , 3 )}
Utilizzo Ctrl + Maiusc + Invio
Puoi vedere che la formula funziona bene dove trova l'ennesimo valore più grande con criteri nella tabella.
L'ennesimo più basso con criteri
Come risolvere il problema?
Per questo articolo ci verrà richiesto di utilizzare la funzione PICCOLO. Ora faremo una formula con queste funzioni. Qui ci viene data una tabella e dobbiamo trovare l'ennesimo valore più piccolo nell'intervallo con determinati criteri.
Formula generica:
{ = PICCOLO ( SE ( c_range = "valore" , intervallo ) , n ) }
c_range : gamma di criteri
valore : criteri corrispondono al valore
gamma : array di risultati
n : ennesimo più grande
Nota : Non inserire le parentesi graffe manualmente. Questa è una formula di matrice, deve usare Ctrl + Maiusc + Invio al posto di solo Invio dove restituisce #NUM! errore.
Esempio:
Tutti questi potrebbero essere fonte di confusione da capire. Quindi, testiamo questa formula eseguendola nell'esempio mostrato di seguito.
Qui abbiamo i dettagli dell'ordine con data dell'ordine, regione e prezzo dell'ordine.
Dobbiamo trovare l'ennesimo prezzo d'ordine più piccolo dalla regione orientale. Qui l'intervallo viene fornito utilizzando lo strumento excel dell'intervallo denominato.
regione (C3:C16)
prezzo (G3:G16)
In primo luogo, dobbiamo trovare il quinto valore più piccolo o più basso utilizzando la funzione PICCOLO e quindi cercare l'operazione da eseguire sui valori. Ora useremo la seguente formula per ottenere la somma
Usa la formula:
{ = PICCOLO ( SE ( regione = G5 , prezzo ) , F5 )}
Spiegazione:
- La funzione SE controlla i criteri che corrispondono a tutti i valori nella regione con un dato valore Est nella cella G5 e restituisce i corrispondenti valori VERI dalla fascia di prezzo.
= GRANDE ({58,41; 303,63; FALSO; 153,34; 82,84; FALSO; 520,01; FALSO; 177; FALSO; FALSO; 57,97; 97,72; FALSO}), F5)
- La funzione SMALL prende l'array che ha tutta la fascia di prezzo come mostrato sopra e restituisce il quinto valore più grande dall'array come mostrato nell'istantanea sottostante.
È possibile visualizzare la formula nella casella della formula come mostrato nell'istantanea sopra. Utilizzo Ctrl + Maiusc + Invio per ottenere il risultato.
Come puoi vedere, la formula dell'array restituisce il quinto prezzo più piccolo $ 153,34 con regione orientale.
Puoi anche alimentare array come array, criteri tra virgolette e valore n direttamente alla funzione invece di utilizzare il riferimento di cella o l'intervallo denominato.
Usa la formula:
{ = PICCOLO ( SE ( C3:C16 = "ovest" , D3:D16 ) , 3 )}
Utilizzo Ctrl + Maiusc + Invio
Puoi vedere che la formula funziona bene dove trovi l'ennesimo valore più grande con criteri nella tabella.
Di seguito sono riportate alcune note osservative.
Appunti:
- La formula funziona solo con i numeri.
- Non inserire le parentesi graffe manualmente. Questa è una formula di matrice, deve usare Ctrl + Maiusc + Invio al posto di solo Invio dove restituisce #NUM! errore.
- Il valore di non può essere maggiore del valore del numero di criteri o restituisce #NUM! Errore.
- SE i criteri non corrispondono alla formula restituisce un errore.
- L'array di argomenti deve essere della stessa lunghezza altrimenti la funzione.
Spero che questo articolo su come trovare l'ennesimo più grande con criteri e l'ennesimo più piccolo con criteri in Excel sia esplicativo. Esplora altri articoli sui moduli di ricerca in Excel qui. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica
Come utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo la moltiplicazione dei valori in più matrici in Excel.
SOMMA se la data è compresa tra : Restituisce la SOMMA dei valori tra date o periodi specificati in Excel.
Somma se la data è maggiore della data indicata: Restituisce la SOMMA dei valori dopo la data o il periodo specificati in Excel.
2 modi per sommare per mese in Excel: Restituisce la SOMMA dei valori all'interno di un determinato mese specifico in Excel.
Come sommare più colonne con condizione: Restituisce la SOMMA dei valori su più colonne con condizione in excel
Come usare i caratteri jolly in Excel : Conta le celle che corrispondono alle frasi usando i caratteri jolly in Excel
Articoli popolari
50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.