Funcion JERARQUIA en Excel – Aplicar a parte de una lista.

Uno de mis lectores me propone el siguiente problema: queremos ordenar una lista de arqueros de fútbol de acuerdo al promedio de goles recibidos. Parte de los arqueros en la lista aún no han jugado (suplentes) y por lo tanto no deben ser tenidos en cuenta.
Buscamos una solución dinámica, sin tener que manipular manualmente la lista. Es decir, que todas las tareas sean hechas por fórmulas.

El problema es una variación del modelo que mostré en la nota Construir una tabla en Excel con las primeras 10 posiciones.

Supongamos que esta es nuestra lista de arqueros

Para calcular los promedio hemos usado una función condicional SI, para que aquellos que aún no han jugado aparezcan con promedio 0. El promedio del Arquero 1 es calculado por la fórmula =SI(B2=0,-1,C2/B2).

Nuestro próximo paso es calcular el orden de rango en orden ascendiente, pero de manera que el primer número de orden sea cero. Para eso usamos la fórmula
=JERARQUIA(D2,$D$2:$D$21,1)-1



He ordenado la lista de acuerdo a los resultados de JERARQUIA para hacer más evidente la técnica que vamos a emplear.

Como pueden ver, y como ya hemos explicado, el primer número de orden que tenemos es 6 (tenemos seis arqueros con el número de orden 0). Para nuestros propósitos queremos mantener los ceros, que identifican a los arqueros que estarán fuera del ranking, pero queremos que fuera de ellos los números de orden comiencen del 1.

Para esto anotamos en la columna F esta fórmula

=SI(E2=0,0,JERARQUIA(E2,$E$2:$E$21,1)-CONTAR.SI($E$2:$E$21,"<0")+CONTAR.SI($D$2:D2,D2)-1)

13-Nov-2006: la fórmula ha sido corregida gracias al aporte de Jaizki (ver comentarios)

Ahora generamos una tabla auxiliar para mostrar sólo aquellos arqueros que hayan jugado, ordenados de acuerdo al promedio de goles recibidos.

En el rango I1:K21 creamos esta tabla



Creamos tres nombres con rangos dinámicos (por si en el futuro queremos agregar más arqueros a la lista)

arqueros =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
jerarquia =DESREF(Hoja1!$F$2,0,0,CONTARA(Hoja1!$F:$F)-1)
promedio =DESREF(Hoja1!$D$2,0,0,CONTARA(Hoja1!$D:$D)-1)

En la celda J2 anotamos esta fórmula =INDICE(arqueros,COINCIDIR(I2,jerarquia,0)) y la copiamos al resto del rango.

En la celda K2 anotamos la fórmula =INDICE(promedio,COINCIDIR(J3,arqueros,0)) y la copiamos al resto del rango

El resultado es



Para ocultar los resultados N/A usamos formato condicional. En la ventanilla de Fórmula del diálogo de formato condicional escribimos



En la pestaña de Tramas, elegimos blanco para el color de la fuente; en la pestaña de borde elegimos la opción "ninguno".

El resultado es



El formato condicional ha vuelto "invisibles" a las líneas de la tabla donde la celda de la columna J da un resultado de error.

De esta manera, sólo usando fórmulas, hemos generado una tabla de ranking dinámica.





Categorías: Funciones&Formulas_,

Technorati Tags: Excel

Formato Condicional en Excel con mas de tres condiciones

En más de una nota nos hemos referido al Formato Condicional de Excel. Con Formato Condicional podemos realizar tareas tan diversas como facilitar la lectura de tablas o crear gráficos tipo Gantt.

Esta herramienta tiene una serie de limitaciones:

- sólo permite definir tres condiciones
- sólo podemos dar formato al estilo de las fuentes, a los bordes y a las tramas

Al apretar el botón Formato del diálogo de Formato Condicional vemos este diálogo

Estas limitaciones pueden ser superadas con el complemento (Add In) desarrollado por Frank Kabel y Bob Phillips y que pueden descargar del sitio xlDynamic.

Este complemento permite definir hasta 30 formatos condicionales y extiende la capacidad a formar también números, alineación, fuentes y más.

Para usar este complemento, después de descargar el archivo, tenemos que instalarlo, como con todo complemento.

Para los lectores que no estén familiarizados con la instalación de complementos, he aquí las instrucciones:

1 – Descargamos el archivo y los guardamos en alguna carpeta. Por lo general Excel guarda los complementos en la carpeta Addins bajo el directorio como C:\Program Files\Microsoft Office\Office10\Library

2 – Abrimos Excel y en el menú Herramientas abrimos la opción Complementos

Apretamos el botón Examinar y elegimos la carpeta donde guardamos el complemento. Elegimos el complemento y apretamos Aceptar.

A partir de este momento el complemento está instalado y podemos usarlo




El menú del complemento se instala inmediatamente antes del menú de Ayuda (?).

Veamos ahora un ejemplo del uso de este complemento

Supongamos esta tabla



Para dar a cada celda un formato distinto, basado en el valor de la celda, necesitamos definir 6 formatos condicionales.
Empezamos por activar el complemento apretando el menú xlsTools



Si se trata de un cuaderno Excel que acabamos de abrir veremos este diálogo



Apretamos OK y veremos el diálogo de formato (después de seleccionar el rango A1:A6)



Apretamos el botón Add para empezar a establecer los formatos condicionales. A diferencia del Formato Condicional nativo de Excel, aquí no podemos cliquear las celdas para crear referencias, sino que debemos introducirlas manualmente.

Después de aplicar los formatos, apretando cada vez el botón Add,



luego de apretar OK, vemos, de acuerdo a nuestras definiciones, los siguientes formatos dependientes del valor de cada celda:






Categorías: Manejo de Datos_, Varios_

Technorati Tags: Excel

Función JERARQUIA en Excel - Rangos continuos

En la nota sobre la función JERARQUIA vimos que esta función asigna la misma posición relativa a valores duplicados en el rango que estamos considerando. La presencia de números duplicados afecta la jerarquía de los números subsiguientes. Por ejemplo, en una lista de números enteros, si un número aparece dos veces y tiene una jerarquía de 5, entonces el número siguiente en tamaño tendrá una jerarquía de 7 y ningún número en la lista tendrá la jerarquía 6.
En esa nota vimos una técnica para lograr que cada valor en la lista tenga un número de jerarquía único.

Uno de mis lectores me hace la siguiente pregunta:

Tenemos valores con sus jerarquías así:

valor	jerarquía
5	5
7	4
10	1
10	1
8	3

¿No hay manera de que en efecto, los dos valores de 10 tengan la jerarquía de 1 pero el 8 tenga jerarquía de 2, el 7 tenga jerarquía de 3 y el 5 tenga jerarquía de 4?
En otras palabras, lograr un rango continuo de orden de jerarquía.

En esta nota veremos como hacerlo, pero primero daremos un repaso al tema de la función JERARQUIA.

Supongamos esta lista de valores




A los valores duplicados les hemos puesto un fondo de color de acuerdo al orden de aparición, para poder distinguirlos cuando ordenemos la lista de acuerdo al orden de jerarquía.

Aplicamos la fórmula =JERARQUIA(A2, $A$2:$A$11) a la celda B2 y la copiamos al resto del rango (hasta la celda B11)



En el rango E1:F11 mostramos la misma lista ordenada de acuerdo al resultado de JERARQUIA.
Podemos ver que los dos valores 7 reciben el mismo número de posición (2) y que no existe el número de posición 3. Lo mismo sucede con el valor 2 de la lista que también aparece dos veces.

Si queremos que cada valor en la lista reciba un orden de jerarquía único, aplicamos la fórmula

=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A2:A$11,A2)-1




Si queremos revertir el orden de aparición de los duplicados (los de fondo morado aparecerán antes de los de fondo turquesa), usamos la fórmula

=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A$2:$A$11,A2)-CONTAR.SI($A$2:A2,A2)



Si queremos que cada valor muestre su verdadero orden de jerarquía, pero que el rango de jerarquías sea continuo (sin "saltos") creamos primero una tabla corriente con la función JERARQUIA



Luego escribimos esta fórmula matricial en la celda C2

={SUMA((SI($B$2:$B$11<B2,1/CONTAR.SI($B$2:$B$11,$B$2:$B$11),0)))+1}

y la copiamos a lo largo del rango C2:C11



En la tabla E1:G11 podemos comparar los resultados de usar la función JERARQUIA y la fórmula matricial.

Una explicación sobre la fórmula, analizando el resultado de C2:

1 – La expresión $B$2:$B$11<B2 genera esta matriz

$B$2:$B$11<B2	Resultado
9<9	FALSO
7<9	VERDADERO
1<9	VERDADERO
5<9	VERDADERO
9<9	FALSO
2<9	VERDADERO
6<9	VERDADERO
7<9	VERDADERO
2<9	VERDADERO
4<9	VERDADERO

2 – La expresión 1/CONTAR.SI($B$2:$B$11,$B$2:$B$11) genera la siguiente matriz

0.5
0.5
1
1
0.5
0.5
1
0.5
0.5
1

El argumento si_es_falso genera una matriz de 0 (cero)

Finalmente sumamos el resultado de la función SI y le agregamos 1. Para la celda C2 el esquema de cálculo es el siguiente:



Referencias a esta nota: Tushar Metha – Excel




Categorías: Funciones&Formulas_, Manejo de Datos_, Formulas Matriciales_

Technorati Tags: Excel