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

Construir con Excel una tabla con las 10 primeras posiciones.

Uno de mis lectores me consulta como construir con Excel una tabla donde aparezcan los 10 alumnos de un curso que han conseguido el mejor puntaje. El pedido incluye que la tabla se actualice automáticamente a medida que se vayan ingresando nuevos datos.

Para construir este modelo tomaremos en consideración dos elementos:

1 – La función JERARQUIA (RANK en la versión inglesa)

2 – Rangos dinámicos con nombres.

Supongamos esta lista de alumnos con sus notas







Para la posición de cada uno de acuerdo al puntaje usamos la función JERARQUIA (RANK en la versión inglesa). En la celda C2 escribimos la fórmula =JERARQUIA(B2,puntaje) y la copiamos al resto de las celdas.
El argumento "puntaje" en la función es un rango dinámico definido en un nombre. La definición es =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1).

El objetivo del rango dinámico es permitir que la fórmula se vaya adaptando a medida que agregamos o quitamos alumnos de la lista.

Si observamos el resultado de la fórmula veremos que hay un problema





Ana y Enrique, al tener el mismo puntaje reciben el mismo número de posición. Ambos reciben la posición 10 y el próximo en la lista recibirá la posición 12.
Para solucionar este problema creamos un nuevo campo en la columna D, al que llamamos "posición sin empate", con la fórmula

=JERARQUIA(B2,puntaje)+CONTAR.SI($C$2:C2,C2)-1

Al ordenar la tabla en orden ascendente de "puntaje sin empate"




podemos ver las diferencias entre los resultados de ambas fórmulas.

Ahora podemos crear un cuadro que muestre los primeros diez alumnos del curso




En la columna F ponemos números (constantes) de 1 a 10.
En la columna G usamos la fórmula
=INDICE(alumno,COINCIDIR(F2,posicion_sin_empate,0)),
que usa los nombres

Alumno: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)

Posición_sin_empate: =DESREF(Hoja1!$D$2,0,0,CONTARA(Hoja1!$D:$D)-1,1)

En la columna H usamos, similarmente la fórmula
=INDICE(puntaje,COINCIDIR(F2,posicion_sin_empate,0))

El problema con esta tabla es que "deja afuera" a Enrique y a Pablo que tienen el mismo puntaje que Ana.

Lo que queremos lograr es una tabla que muestre todos los alumnos que comparten las primeras diez posiciones. En nuestro ejemplo hay 12 alumnos con los mejores diez puntajes.

Para solucionar este problema modificamos nuestra tabla y sus fórmulas:




1 – agregamos un campo (número de orden) con la fórmula
=SI(INDICE(posicion,COINCIDIR(FILA()-1,posicion_sin_empate,0))>10,"",FILA()-1)

2 – En la columna K usamos la fórmula
=SI(CELDA("contenido",J2)="","",BUSCARV(L2,alumno_posicion,3,0))

3 - En la columna L usamos la fórmula
=SI(CELDA("contenido",J2)="","",INDICE(alumno,COINCIDIR(FILA()-1,posicion_sin_empate,0)))

4 - En la columna M usamos la fórmula
=SI(CELDA("contenido",J2)="","",BUSCARV(L2,alumno_puntaje,2,0))

Todas estas fórmulas usan una función SI para condicionar el resultado. Si el valor en el campo "número de orden" nos es "blanco" las fórmulas darán el resultado buscado; en caso contrario el resultado es "blanco".

El número de líneas de esta tabla debe coincidir con el número de alumnos en el curso.

Estas fórmulas usan función CELDA que es volátil y también funciones de búsqueda INDICE, COINCIDIR y BUSCARV, con búsqueda exacta. En hojas con un gran número de alumnos esto puede causar que la recalculación sea lenta.

Una alternativa para esos casos es utilizar Tablas Dinámicas. Los pasos a dar son los siguientes:

1 – definimos un rango dinámico rango_
td: =DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),4)
para que nuestra tabla dinámica se adpate a los cambios en la lista de alumnos

2 – Ponemos el campo Alumnos en el área de filas; en el área de datos ponemos Posición y Puntaje.

3 - Abrimos el menú de configuración de campo




y apretamos el botón Avanzado

4 - en el diálogo de avanzado definimos Opciones de Autoordenar: ascendente; activamos la opción de mostrar los 10 valores inferiores; en ambas ventanillas de "Usar campo" elegimos "posición"




El resultado es



La ventaja de usar tablas dinámicas es que nos exime de escribir fórmulas complicadas y los cálculos son mucho más eficientes en términos de tiempo; la desventaja es que las posibilidades de formato son menores que en tablas normales de Excel.





Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Excel

La función DESREF (OFFSET) de Excel – Una explicación

La función DESREF de Excel (OFFSET en la versión inglesa) es una de esas funciones que causan cierta confusión a los usuarios novicios. Tal vez esta confusión esté relacionada al hecho que DESREF puede referirse tanto a una celda específica como a un rango de celdas. La sintaxis de la función difiere en cada uno de los casos.

En mis notas sobre actualización automática de datos y sobre manejo de pequeñas bases de datos con Excel, mostraba como usar DESREF para construir rangos dinámicos.

En la más reciente nota sobre bases de datos de imágenes en Excel mostraba también el otro uso de DESREF, crear una referencia a una única celda.

Tres hechos hay que tener en cuenta cuando queremos usar DESREF:

1 – DESREF puede crear una referencia tanto a una celda única como a un rango de celdas;

2 – La sintaxis en cada caso es distinta;

3 – Como definir el primer argumento de la función, al que llamaremos el "ancla"

La sintaxis de DESREF es

El "ancla" es la celda que es nuestro punto de partida. Por ejemplo, si escribimos esta fórmula: DESREF(A1,2,1), estamos estableciendo una referencia a la celda B3, como pueden ver en este ejemplo



Puesto en palabras, la fórmula dice: empezamos en A1 (el "ancla"), nos movemos 2 filas hacia abajo y una columna a la derecha y así llegamos a B3.

Con la ayuda de la función COINCIDIR y Validación de Datos, podemos construir un modelo dinámico a partir de nuestro ejemplo.
Definimos esta validación de datos en la celda A9




En la celda B9 sustituimos el segundo argumento (Filas) con la función COINCIDIR
=DESREF(A1,COINCIDIR(A9,A2:A6,0),1), para definir el número de filas en función del número de catalogo elegido. De esta manera DESREF nos dará el nombre del producto en forma dinámica.




Como pueden ver, cuando usamos DESREF para referirnos a una celda, omitimos los argumentos "alto" y "ancho".

Cuando usamos DESREF para referirnos a un rango, usamos cero como valor para los argumentos "filas" y "columnas". Por ejemplo, si queremos establecer una referencia a la tabla de productos en el ejemplo (el rango A1:B6) usamos la fórmula: =DESREF(A1,0,0,6,2).

En la nota manejo de pequeñas bases de datos con Excel hay un ejemplo de rango dinámico usando DESREF.


Categorías: Funciones&Formulas_

Technorati Tags: Excel, DESREF

Base de datos de imágenes en Excel

Excel nos permite manejar pequeñas bases de datos (y si hacemos esto es importante tener en cuenta las limitaciones de Excel en este tema). Sobre esto ya he escrito una serie de notas en el pasado.

Cada tanto veo consultas en los distintos foros sobre la posibilidad de manejar una base de datos con imágenes. La idea es, por ejemplo, tener un catálogo de partes o productos con sus respectivas imágenes en una hoja. En otra hoja tener la posibilidad de elegir un producto y que aparezca su imagen.
Si bien esto es posible y mostraremos en esta nota como hacerlo, me apresuro a aclarar que Excel no es la herramienta recomendable para este tipo de tareas.

Si a pesar de mis advertencias, han decidido crear un catálogo con imágenes en Excel, estos son los pasos a dar.

1 – Abrimos un cuaderno Excel en blanco. Cambiamos el nombre de la Hoja1 a "Base de datos" y el nombre de la Hoja2 a "Catalogo" (este paso no es indispensable).

2 – En la hoja "Base de datos" creamos nuestro catálogo de imágenes. En la columna A ponemos el número de catálogo, en la columna B la imagen del primer producto en nuestra lista y en la columna C la descripción del producto.






Como pueden ver he adaptado el tamaño de la imagen y el alto de la fila. Para este ejemplo he utilizado alguno de los productos de mi empresa (si alguno de ustedes se ocupa de irrigación, tuberías, aguas corrientes o gas, está invitado a visitar el sitio).

3 – Definimos un nombre que contenga la lista de números de catálogo, al que llamaremos "Lista". Para que este nombre sea dinámico usamos la siguiente fórmula:

=DESREF('Base de datos'!$A$2,0,0,CONTARA('Base de datos'!$A:$A)-1,1)

3 – Definimos otro nombre, "Imagen", con una fórmula que conecte el número de catálogo en la columna A con la imagen correspondiente en la columna B:

=DESREF('Base de datos'!$B$2,COINCIDIR(Catalogo!$A$3,Lista,0)-1,0,1,1)


4 – En la hoja "Catálogo" en la celda A3 creamos una lista desplegable con Validación de datos




5 – En la celda B3 copiamos la primer imagen de nuestro catalogo

Y ahora el truco importante: seleccionamos con un clic la imagen, activamos la barra de fórmulas con un solo clic y en ella escribimos "=Imagen" (sin las comillas).




6 - Para que aparezca la descripción correspondiente introducimos es fórmula en la celda C3

=DESREF('Base de datos'!A2,COINCIDIR(Catalogo!A3,Lista,0)-1,2)


Otra variante sería usar BUSCARV, para lo cual tendríamos primero que definir un nombre para el rango de datos, "Descripcion" que contiene esta fórmula

=DESREF('Base de datos'!$A$2,0,0,CONTARA('Base de datos'!$A:$A)-1,3)

y luego escribir la siguiente fórmula en la celda C3
=SI(ESBLANCO(A3),"",BUSCARV(A3,Descripcion,3,0))

Con esto terminamos nuestra tarea y disponemos de un catálogo dinámico de imágenes en Excel.
Como ven, hemos usado extensivamente la función DESREF en sus distintas variantes (referencia a celda y referencia a rango).

En esta nota hay un enlace para descargar una animación explicatoria de la técnica para construir el modelo


Categorías: Funciones&Formulas_, Manejo de Datos_, Varios_


Technorati Tags: Excel, Imagenes en Excel

Gráficos en Excel – Actualización automática de datos.

Supongamos un gráfico basado en una tabla de datos como esta:




Excel nos permite generar con facilidad un gráfico que represente el desarrollo de las ventas:




Ahora supongamos que queremos que el gráfico se actualice cada vez que agregamos datos a nuestra tabla.
Una posibilidad es hacerlo manualmente, para lo cual tendremos que activar el gráfico y en el menú Gráfico---Datos de origen y actualizar al rango de los datos



Pero existe la posibilidad de forzar a Excel a actualizar el gráfico en forma automática cada vez que agreguemos datos a nuestra tabla.
Para lograr esto definimos nombres que contienen fórmulas con la función DESREF para crear rangos automáticos, como ya vimos en la entrada sobre referencias dinámicas.
Para crear un gráfico que se actualice automáticamente seguimos estos pasos:
1 – creamos un nombre llamado Mes (Insertar---Nombres---Definir) con esta fórmula: =DESREF(Hoja1!$A$2,0,0,CONTARA(Hoja1!$A:$A)-1,1)
2 – creamos un segundo nombre que contendrá esta fórmula: =DESREF(Hoja1!$B$2,0,0,CONTARA(Hoja1!$B:$B)-1,1)
3 – Contrariamente a lo que parecería lógico, no aplicamos el nombre con el rango dinámico en lugar de el rango definido en la ventana Rango de Datos (ver arriba), sino en una fórmula que define el gráfico, llamada SERIES. Activamos el gráfico y seleccionamos una de las barras. Al hacer esto veremos la fórmula "SERIES" en la barra de fórmulas



En esta fórmula reemplazamos las referencias a los rangos por los nombres que acabamos de definir:

=SERIES(Hoja1!$B$1,grafico_aut_sp.xls!mes,grafico_aut_sp.xls!ventas,1)

Como se puede ver, Excel agrega automáticamente el nombre del cuaderno donde están definidos los nombres.

A partir de este momento, el gráfico se actualizará automáticamente cada vez que agreguemos los datos de un mes de ventas.

Y antes de finalizar la entrada, unas palabras sobre la fórmula SERIES. Esta fórmula es distinta de las que conocemos en Excel. No podemos utilizarla en celdas de una hoja y tampoco podemos combinar en ella funciones que usamos en las hojas de cálculo. La tarea de esta fórmula es definir los datos que se utilizan en un gráfico. Su sintaxis es la siguiente:
=SERIES(título, rango de la categoría, valores, orden)

Como hemos dicho, no podemos usar funciones regulares de Excel en esta fórmula, pero como hemos visto, si podemos usar nombres que contienen fórmulas comunes.


Categorías: Graficos_

Technorati Tags: Gráficos en Excel

Manejo de pequeñas bases de datos en Excel – Referencias dinámicas con nombres (NAMES).

En la entrada de ayer sobre tablas dinámicas vimos que cada vez que agregamos datos a la base de datos, debemos actualizar la referencia al rango. De no hacer esto, las nuevas líneas de datos no aparecerán en la tabla dinámica.
En el ejemplo de ayer hicimos esto manualmente. Excel nos permite construir referencias dinámicas, que se actualizan con los cambios en el tamaño de la base de datos. La técnica para hacer esto es utilizar "nombres" (NAMES).
Si no estás familiarizado con este tema, puedes ver mi nota sobre uso de nombres en Excel.
Como ya explicamos, los nombres pueden referirse no sólo a rangos sino también a fórmulas. Para crear el rango dinámico en nuestro caso definiremos un nombre que contendrá la formula DESREF (Offset en la versión inglesa). El archivo de este ejemplo se puede descargar aquí.
La sintaxis de esta fórmula es la siguiente; DESREF(referencia ;filas;columnas;alto;ancho) donde:

referencia: la celda en el ángulo superior derecho de la lista (en nuestro caso será A1);
filas: para nuestro uso será siempre 0
columnas: para nuestro uso será siempre 0
alto: la cantidad de filas en nuestra lista
ancho: la cantidad de columnas en nuestra lista.

Para convertir esta fórmula en dinámica, usaremos la función CONTARA para determinar los valores de los parámetros alto y ancho. Esta es nuestra fórmula:

=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1))


como se puede ver comenzamos en la celda A1, contamos cuantos valores hay en la columna A (que representan la cantidad de líneas de la tabla) y también contamos la cantidad de valores en la línea 1 (que representan la cantidad de columnas en la lista).

Ahora sólo nos queda reemplazar la referencia al rango de datos en la lista por el nombre que acabamos de definir. Los pasos a seguir son:
1 – activamos la Hoja2 y ubicamos el mouse en alguna de las celdas de la tabla dinámica
2 – abrimos el menú Tabla Dinámica--->Asistente



3 – pulsamos el botón "atrás"; en el diálogo que se abre reemplzamos el rango



con el nombre (la forma más práctica es pulsar F3)





A partir de este momento, la referencia de la tabla dinámica se adaptará automáticamente de acuerdo a la cantidad de líneas que agreguemos, o que borremos, a la base de datos.

Categorías: Funciones&Formulas_, Manejo de Datos_


Technorati Tags: Tablas Dinamicas, Pivot Tables