Scarica la cartella di lavoro di esempio
Questo tutorial mostrerà come calcolare il “percentile se”, recuperando il kns percentile in una furia di valori con criteri.
Funzione PERCENTILE
La funzione PERCENTILE viene utilizzata per calcolare il kns percentile di valori in un intervallo dove K è il valore percentile compreso tra 0 e 1 inclusi.
=PERCENTILE($D$2:$D$10,0.75)
Tuttavia, questo prende il percentile dell'intero intervallo di valori. Invece, per creare un "Percentile If", utilizzeremo la funzione PERCENTILE insieme alla funzione SE in una formula di matrice.
PERCENTILE SE
Combinando PERCENTILE e SE in una formula di matrice, possiamo essenzialmente creare una funzione "PERCENTILE SE" che funziona in modo simile a come funziona la funzione MEDIA.SE incorporata. Passiamo attraverso un esempio.
Abbiamo un elenco di punteggi ottenuti dagli studenti in due diverse materie:
Supponiamo che ci venga chiesto di trovare il 75ns percentili di punteggi ottenuti per ogni materia in questo modo:
Per fare ciò, possiamo annidare una funzione SE con il soggetto come i nostri criteri all'interno della funzione PERCENTILE in questo modo:
=PERCENTILE(SE(=, ),)
=PERCENTILE(SE($C$2:$C$10=$F3,$D$2:$D$10),0,75
Quando si utilizza Excel 2022 e versioni precedenti, è necessario inserire la formula dell'array premendo CTRL + MAIUSC + INVIO (invece di ACCEDERE), dicendo a Excel che la formula in una formula di matrice. Saprai che è una formula di matrice dalle parentesi graffe che appaiono attorno alla formula (vedi l'immagine in alto).
Come funziona la formula?
La funzione If valuta ogni cella nel nostro intervallo di criteri come VERO o FALSO, creando due matrici:
=PERCENTILE(SE({VERO; FALSO;FALSO; VERO; FALSO; VERO; FALSO; VERO; FALSO}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Successivamente, la funzione IF crea un singolo array, sostituendo ogni valore con FALSE se la sua condizione non è soddisfatta.
=PERCENTILE({0,81;FALSO;FALSO;0,42;FALSO;0,63;FALSO;0,58;FALSO}, 0,75)
Ora la funzione PERCENTILE salta i valori FALSE e calcola il 75ns percentile dei restanti valori (0,72 è il 75ns percentile).
PERCENTILE SE con criteri multipli
Per calcolare PERCENTILE SE con più criteri (in modo simile a come funziona la funzione AVERAGEIFS incorporata), puoi semplicemente moltiplicare i criteri insieme:
=PERCENTILE(SE((=)*(=),),)
=PERCENTILE(SE(($D$2:$D$10=$H2)*($C$2:$C$10=$G2),$E$2:$E$10),0,75)
Un altro modo per includere più criteri consiste nell'annidare più istruzioni IF all'interno della formula.
Suggerimenti e trucchi:
- Ove possibile, fare sempre riferimento alla posizione (k) da una cella di supporto e bloccare il riferimento (F4) in quanto ciò faciliterà le formule di riempimento automatico.
- Se stai utilizzando Excel 2022 o successivo, puoi inserire la formula senza CTRL + MAIUSC + INVIO.
- Per recuperare i nomi degli studenti che hanno ottenuto il massimo dei voti, abbinalo a INDEX MATCH.
PERCENTILE SE in Fogli Google
La funzione PERCENTILE SE funziona esattamente allo stesso modo in Fogli Google come in Excel:
