Come trovare i percentile se i criteri in Excel
In questo articolo, impareremo come trovare il valore percentile se con determinati criteri in Excel
Scenario:
In parole semplici, quando si lavora con un set di dati lungo e disperso, a volte è necessario trovare il percentile di numeri con alcuni criteri sopra.
Ad esempio, trovare il percentile degli stipendi in un particolare dipartimento o avere più criteri su data, nomi, dipartimento o persino numeri di dati come stipendi al di sotto del valore o quantità al di sopra del valore.
Per questo devi estrarre manualmente i numeri richiesti e quindi calcolare il percentile di un array con criteri. Uso della funzione IF con formule di matrice.
Come risolvere il problema?
Devi pensare a come sia possibile eseguire operazioni logiche su array di tabelle utilizzando la funzione IF. SE la funzione in Excel è molto utile, ti consentirà di svolgere alcune attività difficili in Excel o in qualsiasi altro linguaggio di codifica. La funzione IF verifica le condizioni sull’array corrispondenti ai valori richiesti e restituisce il risultato come array corrispondente alle condizioni True come 1 e False come 0 Per questo problema, utilizzeremo le seguenti funzioni:
-
link: / funzioni-matematiche-come-usare-la-funzione-percentile [funzione PERCENTILE] -
link: / tips-if-condition-in-excel [IF function]
Richiederemo queste funzioni di cui sopra e un senso di base del funzionamento dei dati. le condizioni logiche sugli array possono essere applicate utilizzando operatori logici. Questi operatori logici lavorano sia sul testo che sui numeri. Di seguito ecco la formula generica. \ {} * le parentesi graffe sono lo strumento magico per eseguire formule di matrice con la funzione SE.
Formula generica:
|
\{* = ( op crit), percentile_array ), k ) }* |
Nota: per le parentesi graffe (\ {}) utilizzare Ctrl + Maiusc + Invio * quando si lavora con matrici o intervalli in Excel. Questo genererà parentesi graffe sulla formula per impostazione predefinita. NON provare a codificare i caratteri parentesi graffe.
intervallo: array, dove si applica la condizione op: operatore, operazione applicata crit: criteri applicati all’intervallo percentile_array: array, dove è necessario il percentile k: kth percentile (ad esempio 33% → k = 0,33 valore come numero)
Esempio:
Tutto ciò potrebbe creare confusione da capire. Quindi, testiamo questa formula eseguendola nell’esempio mostrato di seguito. Qui abbiamo i dati dei prodotti ricevuti da diverse regioni insieme alla data, alla quantità e al prezzo corrispondenti. Qui abbiamo i dati e dobbiamo trovare il 25 ° percentile o valore corrispondente al 25% considerando gli ordini ricevuti solo dalla regione “Est”. Ora sei pronto per ottenere il risultato desiderato utilizzando la formula seguente.
Usa la formula:
|
\{* = ( = “East” , D2:D11 ) , 0.25 ) } |
Spiegazione:
-
Regione B2: B11 = “Est”: controlla i valori nella regione dell’array in modo che corrispondano a “Est”.
-
L’operatore logico restituisce True per il valore corrispondente e False per il valore non corrispondente.
-
Ora la funzione SE restituisce solo i valori di prezzo corrispondenti a 1 e False così com’è. Di seguito è riportato l’intervallo restituito dalla funzione IF e ricevuto dalla funzione PERCENTILE.
\ {FALSE; 303.63; FALSE; 153.34; FALSE; 95.58; FALSE; FALSE; 177; FALSE}. La funzione PERCENTILE restituisce il prezzo percentile del 25% (k = 0,25) per la matrice restituita.
Qui gli array vengono forniti utilizzando il riferimento di cella. Di seguito è riportato il prezzo corrispondente al 25% dell’array.
Come puoi vedere, il prezzo è di 138,9, dai tre ordini da “Est” che hanno valori di prezzo 303,63, 153,34 e 95,58. Ora ottieni il valore del prezzo con percentile diverso semplicemente cambiando il valore k-esimo a 0,5 per il 50%, 0,75 per il 75% e 1 per il valore percentile del 100%.
Come puoi vedere, abbiamo tutti i valori percentili corrispondenti a determinati criteri. Ora usa la formula con il valore Data come criterio.
percentile se con criteri di data in Excel:
La data come criterio è una cosa complicata in Excel. Poiché Excel memorizza i valori della data come numero. Quindi, se il valore della data non viene riconosciuto da Excel, la formula restituisce un errore. Qui dobbiamo trovare il valore percentile del 25% con ordini ricevuti dopo il 15 gennaio 2019.
Usa la formula:
|
\{* = ( > H3 , D2:D11 ) , 0.25 ) } |
>: operatore maggiore di applicato sull’array di date.
Qui gli array vengono forniti utilizzando il riferimento di cella. Di seguito è riportato il prezzo corrispondente al 25% dell’array.
Come puoi vedere, 90,27 è il valore del 25 ° percentile con data successiva al 15 gennaio 2019. Ora ottieni il percentile per il diverso valore k-esimo.
Ecco tutti i valori percentili come risultati Ecco tutte le note osservative riguardanti l’utilizzo della formula.
Note:
-
Il percentile_array nella formula funziona solo con i numeri.
-
NON codificare le parentesi graffe con la formula.
-
Se la formula restituisce l’errore #VALORE, verificare che le parentesi graffe siano presenti nella formula come mostrato negli esempi nell’articolo.
-
Operazioni come uguale a (=), minore di uguale a (⇐), maggiore di (>) o non uguale a (<> *) possono essere eseguite all’interno di una formula applicata, solo con numeri.
Spero che questo articolo su Come trovare il percentile se con criteri in Excel sia esplicativo. Trova altri articoli sulle formule di somma qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected]
Articoli correlati:
link: / summing-excel-sumproduct-function [Come usare la funzione SUMPRODUCT in Excel]: Restituisce la SOMMA dopo la moltiplicazione dei valori in più array in Excel.
link: / summing-sum-if-date-is-between [SUM se la data è compresa tra]: Restituisce la SOMMA dei valori tra date date o periodo in Excel.
link: / summing-sum-if-date-is-greater-than-given-date [Sum se la data è maggiore della data specificata]: * Restituisce la SOMMA dei valori dopo la data o il periodo specificati in Excel.
link: / summing-2-ways-to-sum-by-month-in-excel [2 Ways to Sum by Month in Excel]: * Restituisce la SOMMA dei valori all’interno di un dato mese specifico in Excel.
link: / summing-how-to-sum-multiple-columns-with-condition [How to Sum Multiple Columns with Condition]: * Restituisce la SOMMA dei valori su più colonne con condizione in Excel.
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoia Excel per aumentare la produttività]: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come utilizzare la funzione CERCA.VERT in Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da intervalli diversi e lenzuola. link: / tips-countif-in-microsoft-excel [Come usare la funzione CONTA.SE in Excel]: Conta i valori con le condizioni usando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.