Función JERARQUIA (RANK) en Excel – Nota adicional

En una entrada anterior he mencionado como usar la función JERARQUIA (RANK en la versión inglesa). Allí dimos la siguiente definición:

JERARQUIA (RANK): Devuelve la jerarquía de un número en una lista de números. La
jerarquía de un número es su tamaño en comparación con otros valores de la
lista. (Si ordenara la lista, la jerarquía del número sería su posición).

Pero a veces hay situaciones en las cuales dos miembros de la lista reciben el mismo ranking, como en este caso




Como podemos ver hay "empate" entre las celdas C5 y C6. Ambas reciben el ranking 14, y el 15 no existe.

Si queremos que en caso de "empate" el ranking se establezca por orden de aparición, podemos utilizar la siguiente fórmula:

=JERARQUIA(B4,sales)+CONTAR.SI($C$4:C4,C4)-1

Si copiamos esta fórmula en todo el rango de la columna D, veremos este resultado




En nuestra fórmula, la función CONTAR.SI (COUNTIF) evalúa cuantas veces aparece el número de ranking de la celda en el rango. Si aparece sólo una vez, el resultado de CONTAR.SI será 0 y el resultado será idéntico al de la fórmula original. Si el número de ranking ya figura en el rango, el resultado de la fórmula será incrementado en 1 por CONTAR.SI

El cuaderno con el ejemplo se puede descargar aquí


Categorías: Funciones&Formulas_,

Technorati Tags: RANK, JERARQUIA

Consolidación de datos con Tablas Dinámicas (Pivot Tables).

En anteriores entradas ya he tratado el tema de consolidación de datos en Excel:
1 – Consolidar Datos con Excel
2 – Consolidar datos con Subtotales
3 - Comparar listas con consolidación de datos

Otro método de consolidar datos en Excel es usando tablas dinámicas (pivot tables).
Consideremos el siguiente ejemplo (para descargar el archivo con el ejemplo apretar aquí). Tenemos un cuaderno Excel con una hoja por cada agente de ventas. En cada una de estas hojas anotamos las ventas por mes y por producto de cada agente.

Para consolidar las ventas de todos los agentes (en nuestro caso habrá sólo dos) en una sola hoja de cálculo usando tablas dinámicas, procedemos de la siguiente manera:
1 – Abrimos una hoja en blanco (en nuestro ejemplo será "consolidado") y activamos el menú Datos--->Informe de Gráficos y Tablas Dinámicos. En el diálogo que se abre elegimos la opción "rangos de consolidación múltiples"



2 – En el siguiente paso debemos instruir a Excel cuantos campos de página queremos. Para nuestro ejemplo elegimos "crear un solo campo de página"



3 – En el tercer paso debemos informar a Excel dónde se encuentran los rangos que contienen los datos. Hacemos esto apunto a los rangos en las hojas adecuadas (agente1 y agente2) y apretando el botón "agregar"



4 – Finalmente debemos decirle a Excel dónde ubicar la tabla dinámica (que consolida los datos)- En nuestro ejemplo la ubicamos en la celda A6



Apretamos "finalizar" y obtenemos la tabla dinámica.

Esta tabla muestra los totales para cada producto y por cada mes de todos los agentes de ventas. Si queremos ver los datos de un agente en particular apretamos el botón "Página 1" y elegimos entre "elemento 1" (agente 1) y "elemento 2" (agente 2).

Dado que los encabezamientos de las columnas tienen un formato de fecha, podemos agrupar los datos en trimestres en forma automática. Para hacer esto damos los siguientes pasos:
1 – Hacemos clic con el botón derecho del mouse en el encabezamiento del campo "columna", y apretamos "agrupar"



2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del mouse).



3 – Apretamos "aceptar" y la tabla se convierte en un reporte trimestral




Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Consolidación de Datos, Tablas Dinámicas

Graduar valores de una lista con funciones Excel (RANK, SMALL, LARGE, MAX, MIN)

To read this post in English, press here.

Una tarea bastante común con Excel es tener que graduar o investigar una lista de valores. Supongamos una lista de agentes de ventas de una compañía con los totales de ventas de cada uno de ellos en un período determinado



Supongamos que queremos saber cuanto fue el máximo de ventas, o quien fue el agente que más (o que menos) vendió, cual fue la segunda suma de ventas en orden decreciente (o creciente) y otras preguntas más. Todas esta tareas se pueden realizar utilizando las siguientes fórmulas (entre paréntesis aparecen los nombres en inglés):

MIN (MIN): Devuelve el valor mínimo de un conjunto de valores

MAX (MAX): Devuelve el valor máximo de un conjunto de valores

K.ESIMO.MENOR (SMALL): Devuelve el k-ésimo menor valor de un conjunto de datos. Utilice esta función para devolver valores con una posición relativa específica dentro de un conjunto de datos.

K.ESIMO.MAYOR (LARGE): Returns the k-th largest value in a data set

JERARQUIA (RANK): Devuelve la jerarquía de un número en una lista de números. La jerarquía de un número es su tamaño en comparación con otros valores de la lista. (Si ordenara la lista, la jerarquía del número sería su posición).

Para investigar el uso de estas funciones les sugiero descargar el archivo con los ejemplos.



Estas funciones son especialmente útiles cuando se las combinan con otras funciones. Por ejemplo, en la celda E14, para saber cual es la posición de un agente de acuerdo a sus ventas, utilizo una combinación de las funciones JERARQUÍA y BUSCARV

=JERARQUIA(BUSCARV(D14,A2:B20,2,0),Ventas)

Como es mi costumbre, y como pueden observar, utilizo nombres para rangos, lo que permite simplificar la fórmulas y volverlas más comprensibles. Una lista de los nombres en uso en el cuaderno aparece en la hoja (ver mi entrada sobre como pegar una lista de nombres).

Categorías: Funciones&Formulas_

Technorati Tags: MIN, MAX, SMALL, LARGE, RANK