sirve - tablas dinamicas excel ejemplos

Uso de QUARTILE en una tabla dinámica de Excel para resumir los datos por subpoblaciones

Question

Tengo una gran tabla de datos en una hoja de cálculo de Excel que, esencialmente, puede considerarse como una colección de valores para individuos identificados como pertenecientes a varias subpoblaciones:

 IndivID SubPopID Value
1 A 33.56
2 E 42.31
3 D 16.35
4 A 50.59
5 F 80.63
...

Hay más de 10,000 individuos en esta tabla y más de 50 subpoblaciones.

Me gustaría calcular el resumen de 5 números (Min, Q1, Mediana, Q3, Max) para cada una de las subpoblaciones.

Min y Max son fáciles en una tabla dinámica. Pero Q1, Q2 (mediana) y Q3 no parecen ser posibles.

Pregunta

¿Hay alguna forma de calcular los cuartiles para cada subpoblación en una tabla dinámica en Excel?

La respuesta debería verse así:

SubPopID Min Q1 Q2 Q3 Max
A 3.23 12.06 20.35 28.29 50.59
B
C
...

Answer 1

Encontré esta answer donde se describe una solución.

Utiliza el mismo enfoque descrito por Doug Glancy pero lo asocia con un campo calculado en la tabla dinámica. La respuesta da un ejemplo de archivo de Excel.

Answer 2

Mi experiencia es que no se pueden hacer percentiles en una tabla dinámica. Acabo de hacer una búsqueda en la web y no veo nada que contradiga eso. Creo que lo mejor que puede hacer es tener sus datos de origen en una tabla para poder consultarlos dinámicamente y luego usar la función de percentil en otra tabla.

He visto un artículo sobre el uso de PowerPivot, pero solo está disponible en Excel 2010 y parece difícil. Tengo el 2010, y cuando enfrenté un problema similar, opté por seguir la ruta que sugiero aquí.

EDITAR : Explicación de mi alternativa propuesta:

En respuesta a su pregunta en los comentarios, aquí está cómo calculo los percentiles de una tabla de datos:

Se basa en una fórmula única, que es la misma para cada celda desde F2 hasta H6. Es una fórmula matricial , lo que significa que se ingresa con Ctrl-Shift-Enter . Utiliza una instrucción IF para filtrar en los diversos SubPopIDs posibles. Aquí está la fórmula para F2:

=PERCENTILE(IF(Table1[SubPopID]=F$1,Table1[Value],""),$E2/100)

Answer 3

Si necesita verificar una condición para más de una columna (a = 1 Y b = 2) puede expandir la matriz usando el carácter *

=PERCENTILE((IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100)