La función JERARQUIA en tablas dinámicas.

Un lector me consulta cómo usar la función JERARQUIA en un campo calculado de una tabla dinámica.

Supongamos este ejemplo

Como pueden apreciar, el campo Jerarquía no es parte integral de la tabla dinámica. Sencillamente, lo hemos creado usando la función JERARQUIA.

Al no estar ligado a la tabla, si agregamos o quitamos vendedores en la lista de origen, éste campo no se adaptará a los cambios.

Lo ideal sería crear un campo calculado usando esta función. Pero lamentablemente no se puede hacer.

Como en casi todas lo que queremos hacer con Excel, existe una solución. Pero primero tenemos que definir qué es lo que queremos hacer.

Si la tarea es ordenar los vendedores por el total de ventas, podemos usar las funcionalidades del botón Avanzado del menú de Configuración de campo

En las opciones de Autoordenar señalamos Descendiente (o Ascendiente).



En la parte derecha del formulario se encuentra el comando Automostrar que nos permite mostrar los n valores superiores (o inferiores).

Después de aplicar Autoordenar la tabla se verá así

Si queremos agregar un campo Jerarquía que sea parte integral de la tabla dinámica tendremos que agregar dos campos auxiliares en la lista de origen.

El primer problema es que no podemos usar JERARQUIA en la lista de origen, ya que queremos calcular el orden por el total de ventas. Por lo tanto creamos una columna auxiliar que en cada línea de cada vendedor nos muestre el total de ventas de ese vendedor.

Esto lo solucionamos fácilmente con SUMAR.SI

Como pueden apreciar, las celdas en la columna C siempre muestran el total de ventas del vendedor.

El próximo paso es calcular el orden de rango de cada vendedor en base a la columna auxiliar. No podemos usar JERARQUIA ya que en caso de valores repetidos todos reciben el mismo número de jerarquía y el número siguiente es omitido.

En nuestro caso pueden ver que el 2 y el 3 no existen, tampoco el 5 y el 6, el 8, el 9, el 11 y el 12

Para superar este problema usaremos la fórmula que mostramos en la reseña sobre la función JERARQUIA

={SUMA(1/SI($C$2:$C$16>C2,CONTAR.SI($C$2:C16,$C$2:$C$16),9.999999999E+307))+1}

Se trata de una fórmula matricial que debe ser introducida apretando Ctrl+Mayúsculas+Enter. La explicación sobre la fórmula puede leerse en la nota mencionada.

Ahora podemos construir la tabla dinámica incluyendo el campo Jerarquia

Para obtener el resultado correcto en el campo Jerarquia, usamos la función MAX en la definición del campo



El último retoque es ocultar el total del campo Jerarquia (que no tiene ningún sentido), dándole a la fuente el mismo color del fondo de la celda

Technorati Tags: MS Excel