Esta pregunta surge una vez cada década: ¿puedes hacer una mediana en una tabla dinámica? Tradicionalmente, la respuesta era no. Recuerdo que en el año 2000 contraté al MVP de Excel, Juan Pablo González, para escribir una macro increíble que creaba informes que parecían tablas dinámicas pero tenían medianas.
Entonces, cuando Alex en mi seminario de Houston Power Excel preguntó sobre la creación de medianas, respondí rápidamente "No". Pero entonces … me preguntaba si DAX tenía una función mediana. Una búsqueda rápida en Google y ¡Sí! Hay una función DAX para la mediana.
¿Qué se necesita para usar DAX en una tabla dinámica? Necesita la versión de Windows de Excel con Excel 2013 o más reciente.
Aquí está mi conjunto de datos muy simple que usaré para probar cómo se calculan las medianas de la tabla dinámica. Puede ver que la mediana de Chicago debe ser 5000 y la mediana de Cleveland debe ser 17000. En el caso de Chicago, hay cinco valores, por lo que la mediana será el tercer valor más alto. Pero para todo el conjunto de datos, hay 12 valores. Eso significa que la mediana está entre 11000 y 13000. Excel promedia esos dos valores para devolver 12000.
Para comenzar, seleccione una celda en sus datos y presione Ctrl + T para formatear los datos como una tabla.
Luego, elija Insertar, Pivot Table. En el cuadro de diálogo Insertar tabla dinámica, elija el cuadro para Agregar estos datos al modelo de datos.
En los campos de la tabla dinámica, elija Región y Distrito. Pero no elija Ventas. En su lugar, haga clic con el botón derecho en el encabezado de la Tabla y elija Nueva medida. "Medir" es un nombre elegante para un campo calculado. Las medidas son más poderosas que los campos calculados en tablas dinámicas normales.
En el cuadro de diálogo Definir medida, complete las cuatro entradas que se muestran a continuación:
- Nombre de la medida: mediana de ventas
- Fórmula
=MEDIAN((Sales)) - Formato de número: Número
- Lugares decimales: 0
Después de crear la medida, se agrega a la lista de campos, pero debe elegir la entrada para agregarla al área de Valores de la tabla dinámica. Como puede ver a continuación, la tabla dinámica calcula correctamente las medianas.
Ver video
Transcripción del video
Aprenda Excel de Podcast, Episodio 2197: Mediana en una tabla dinámica.
Tengo que decirte que estoy muy emocionado por esto. cuando estaba en Houston dando un seminario de Power Excel allí, y Alex levantó la mano y dijo: "Oye, ¿hay alguna forma de hacer una mediana en una tabla dinámica?" No, no puede hacer una mediana en una tabla dinámica. Recuerdo que hace 25 años, mi buen amigo, Juan Pablo Gonzales, en realidad trajo una macro para hacer el equivalente de la mediana, sin usar una tabla dinámica, simplemente no es posible.
Pero tenía una chispa. Dije, "Espera un segundo", abro un navegador y busco "DAX mediana" y, efectivamente, hay una función MEDIAN en DAX, lo que significa que podemos resolver este problema.
Muy bien, entonces, para usar DAX, debe estar en Excel 2013 o Excel 2016, o en Excel 2010, y tener el complemento Power Pivot; no es necesario que tenga la pestaña Power Pivot, solo tenemos que tener el modelo de datos. ¿Bien? Así que elegiré estos datos, los convertiré en una tabla con Ctrl + T, y le darán un nombre poco imaginativo de "Tabla 4". En su caso, podría ser "Tabla 1". E insertaremos una tabla dinámica, agregaremos estos datos al modelo de datos, hacemos clic en Aceptar y elegiremos Regional en el lado izquierdo, pero no Ventas. En su lugar, quiero crear una nueva medida calculada. Así que vengo aquí a la mesa, hago clic con el botón derecho y digo Agregar medida. Y esta medida se llamará "Mediana de ventas", y la fórmula será: = MEDIANA. Presione la pestaña allí y elija Ventas,paréntesis de cierre. Puedo elegir esto como un número con cero posiciones decimales, usar un separador de mil y hacer clic en Aceptar. Y, veamos, nuestro nuevo campo se agrega aquí, tenemos que marcarlo para agregarlo, y dice que la mediana para Midwest es 12,000.
Ahora revisemos eso. Entonces, aquí, todo el Medio Oeste, la mediana de todos los conjuntos de datos está entre 11,000 y 13,000. Por lo tanto, tiene un promedio de 11 y 13, que es exactamente lo que haría la mediana en Excel normal. Ahora agreguemos el distrito y dice que la mediana de los 5,000 de Chicago, la mediana de Cleveland es 17,000; Medio oeste total y gran total 12.000. Todo eso es correcto. ¿Qué tan asombroso es eso? Finalmente, mediana en una tabla dinámica, gracias a un campo calculado, o medida, como se le llama, y al Modelo de datos.
Ahora, muchos de mis consejos favoritos, los consejos para mi seminario de Power Excel en vivo, en este libro LIVe, Los 54 mejores consejos de todos los tiempos. Haga clic en esa "I" en la esquina superior derecha para leer más sobre el libro.
Bien. Resumen: ¿Puedes hacer una mediana en una tabla dinámica? Oh no, sí, sí puedes, gracias al modelo de datos. Puede crear una mediana; Ctrl + T para convertir sus datos en una tabla; Insertar tabla dinámica; y marque esa casilla, Agregar estos datos al modelo de datos; haga clic con el botón derecho en el nombre de la tabla y elija una nueva medida, y la medida será = MEDIANA ((Ventas)). Es impresionante.
Gracias a Alex por aparecer en mi seminario y hacer esa pregunta, gracias a ti por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo de Excel
Para descargar el archivo de Excel: pivot-table-median.xlsx
