La función JERARQUIA en tablas dinámicas.

jueves, junio 18, 2009

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

Supongamos este ejemplo





función JERARQUIA en tablas dinámicas

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



función JERARQUIA en tablas dinámicas

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

función JERARQUIA en tablas dinámicas

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í



función JERARQUIA en tablas dinámicas

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



función JERARQUIA en tablas dinámicas

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


función JERARQUIA en tablas dinámicas

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}



función JERARQUIA en tablas dinámicas

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

función JERARQUIA en tablas dinámicas

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

función JERARQUIA en tablas dinámicas



Technorati Tags:

17 comments:

Hanzz 19 junio, 2009 15:58  

Muy bueno el blog. he aprendido muchas cosas, especialmente sobre el manejo del DESREF y los nombres.

Yo como reemplazo de jerarquía usaría:
-cosiderando un rango de numeros desde A1 a A10

-mayor a menor
=CONTAR.SI($A$1:$A$10;">"&A1)+CONTAR.SI($A1:$A$10;"="&A1)

-menor a mayor
=CONTAR.SI($A$1:$A$10;<"&A1)+CONTAR.SI($A1:$A$10;"="&A1)

te envío el libro a tu correo, para ver si te parece la formula.

Hanzz 19 junio, 2009 16:06  

oh! me equivoque en un $
Mayor a menor
=CONTAR.SI($A$3:$A$21;">"&A3)+CONTAR.SI($A$3:$A3;"="&A3)

y

menor a Mayor
=CONTAR.SI($A$3:$A$21;"<"&A3)+CONTAR.SI($A$3:$A3;"="&A3)

cuando veas el libro me entenderas, supongo

Elbert Villarreal 19 junio, 2009 16:56  

Hola Sr. Dunkelman, agradezco mucho toda su valiosa ayuda, solo una solicitud mas. Yo utilizo Excel en ingles, y no se cual es la función JERARQUIA, me puede decir cual es el nombre en ingles y en office Excel, Gracias.

Elbert Villarreal 19 junio, 2009 16:57  

Este es para tener el seguimiento de comentarios de esta entrada...

Jorge L. Dunkelman 19 junio, 2009 17:42  

Gracias por los conceptos y por compartir.
La fórmula es útil cuando queremos jerarquizar valores que no son números, como mostré en esta nota.

Jorge L. Dunkelman 19 junio, 2009 17:46  

Elbert
en inglés la función se llama RANK.
En esa nota hay enlaces a dos complementos para trducir funciones de un idioma al otro.

Jim McLean 19 junio, 2009 18:58  

Excelente solución. Gracias por el conocimiento que compartes. Para cuando un libro?

Robert Blanco 21 junio, 2009 05:11  

Coincido con el Sr. Jim, seguro mucha gente gustosa aguardará un libro suyo.

Robert Blanco 21 junio, 2009 05:13  

Por alguna razón no estoy pudiendo visualizar los caps en esta entrada.

Fernando José Moreno Mora 21 junio, 2009 06:34  

Hola Jorge.

No he logrado ver las imágenes, algo ha pasado con ellas.

Salu2.

Jorge L. Dunkelman 21 junio, 2009 12:29  

Acabo de recibir un mail de Picassa (el servicio de Google que maneja las imágenes en el blog) informando que han tenido un problema y que han tenido que cambiar la dirección WEB de ciertas imágenes. En sus palabras:
...also broke image links for some affected photos in your Blogger blog -- you will need to re-post those images using Blogger. This applies to images uploaded from Wednesday evening to late Thursday night, Pacific Standard Time...
En las próximas horas espero poder reponer las imágenes en la nota.

Jorge L. Dunkelman 21 junio, 2009 21:17  

El enlace a las imágenes ha sido corregido.

Fernando José Moreno Mora 23 junio, 2009 05:29  

Hola Jorge.

Gracias por actualizar las imágenes, menudo error de Picassa :S, interesante post, gracias.

Salu2.

Falysio 07 agosto, 2009 12:28  

Buenos Dias Jorge,

Soy un joven que se está iniciando en el maravilloso mundo de excel,
te he conocido a través de Google, realizando búsquedas de autocompletar, macros etc.. sueles ser de los primeros en Google.

Mi consulta sobre este tema es ¿ Qué formula se utiliza para realizar los totales de ventas de los vendedores?
Perdona si me explico mal, se van añadiendo vendedores y sus ventas a la izquierda, y en la derecha, realiza una autosuma de cada vendedor.
¿Que formula se puede utilizar para obtener esos datos?

Me gustaria me ayudase en esta pequeña inquietud, sigo bastante interesado su blogs que es realmente interesante y gratificado.

Un saludo,
Le agredezco su interés
y colaboracion

Jorge L. Dunkelman 07 agosto, 2009 16:36  

Hay varias posibilidades. Mi recomendación es usar tablas dinámicas o listas (tablas en Excel 2007).

Anónimo,  20 junio, 2012 19:17  

buenas estoy buscando ayuda a un problema que tengo, jorge no se si me puedas ayudar. Es posible ingresar una formula estadistica en el campo calculado? ejemplo la formula MODA.UNO

Jorge L. Dunkelman 20 junio, 2012 20:39  

No, no se puede usar MODA en un campo calculado. La solución es crear una columna auxiliar en la base de datos para obtener el resultado buscado. Por ejemplo, crear una columna auxiliar con la funciòn FRECUENCIA y luego usar MAX para totalizar los resultados. Puedes ordenar los resultados segùn el campo Frecuencia para ver quien ocupa el primer lugar.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP