domingo, 24 de enero de 2010

Acceder los elementos de una tabla mediante fórmulas

Si bien podemos considerar que todas las hojas de Excel son una tabla, en Excel 2007 se introdujo una nueva herramienta llamada "Tablas". Esta herramienta permite darle un nombre a un rango determinado de celdas y poder acceder a los elementos de la tabla mediante una referencias, y no el nombre de la celda donde está el dato, que pueden usarse en formulas.

Las principales ventajas es que las referencias son más fáciles de usar que los nombres de las celdas ya que pueden tener el nombre que el usuario desee. Funciona de forma similar a la definición de un nombre para un rango.

Se puede cambiar el tamaño de la tabla o mover la tabla y esto ajustara todas las referencias a la tabla sin tener que cambiar las formulas una por una.

Si alguna de las columnas de la tabla tiene una fórmula esta se copiará automáticamente al resto de las líneas.

Las referencias a una tabla se componen de la siguiente forma : =NombreTabla[NombreColumna]

También se pueden acceder distintas partes de la tabla utilizando la siguiente notación


  • =NombreTabla[#Todas] Para acceder a la tabla completa.

  • =NombreTabla[#Datos] El rango de todos los datos, sin encabezados ni totales.

  • =NombreTabla[#Encabezados] Solamente incluye la primer línea de encabezados.

  • =NombreTabla[#Totales] Solamente incluye la última línea.



Veamos algunos ejemplos.


Utilizaremos una tabla de datos como la siguiente


Si todavía los datos no fueron convertidos a una tabla se debe seleccionar la opción tabla de la ficha Insertar. Luego se debe definir el nombre tblVentas. El nombre se puede cambiar seleccionado la ficha Herramientas de tabla (solo aparece cuando la celda activa es parte de la tabla) y completar el campo nombre de tabla.


Utilizando fórmulas similares a las explicadas en la nota de sumas condicionales con mas de un criterio, utilizaremos referencias a las tablas para obtener los resultados.

Si deseamos obtener la cantidad de productos para un determinado país podemos usar la fórmula =SUMAR.SI(TblVentas[Pais];"Argentina";TblVentas[Cantidad]).

Para obtener la cantidad de un producto determinado y en un solo país podemos usar la fórmula =SUMAR.SI.CONJUNTO(TblVentas[Cantidad];TblVentas[Pais];"Argentina";TblVentas[Producto];"Manzana").

También podemos combinar otras fórmulas de Excel y por ejemplo obtener un promedio de unidades =PROMEDIO(TblVentas[Cantidad]) o el precio promedio de todas las ventas =TblVentas[[#Totals];[Importe]]/TblVentas[[#Totals];[Cantidad]].

Para acceder al archivo del ejemplo pueden hacer clic aquí.

sábado, 2 de enero de 2010

Ranking con posiciones duplicadas usando la función JERARQUIA

En el artículo de hoy explicaré como utilizar la función JERARQUIA (o RANK para la versión en Inglés), que nos permite determinar la posición de un elemento dentro de la lista con valores numéricos. Cuando existen dos o más elementos que tienen el mismo valor la función les asigna el mismo. En algunos casos esto no es el comportamiento deseado por lo tanto veremos una variante para solucionarlo.

Primero explicaré como funciona la función JERARQUIA. Esta función toma tres parámetros, el primero indica la celda que contiene el valor a evaluar. Luego se indica el rango de celdas donde están todos los valores que forman parte del ranking. Finalmente se indica el orden 0 para ascendente, es decir el mayor valor tiene ranking 1, y 1 para descendente.


Veamos algunos ejemplos


Tenemos una lista de precipitaciones promedios como la siguiente :


En la columna C aplicamos la siguiente fórmula =JERARQUIA(B2;$B$2:$B$13;0) y obtenemos el ranking de mayor a menor.


En el puesto 9 tenemos dos meses, Agosto y Septiembre que tienen el mismo valor. De ahí saltamos al puesto 11 y no tenemos un mes en el puesto 10. Para solucionar este problema podemos utilizar una variante en la fórmula antes descripta: =JERARQUIA(B2;$B$2:$B$13;0)+CONTAR.SI($B$2:B2;B2)-1


La segunda parte de la formula cuenta cuantas veces aparece el valor de la fila que estamos calculando el ranking desde el principio de la tabla hasta la fila actual. Por lo tanto si el valor esta repetido el que figure mas abajo tendrá un ranking mayor. Es muy importante poner la referencia "$B$2:B2" fijando el comienzo del rango para que siempre empiece desde el principio de la tabla pero si copiamos la fórmula hacia abajo se incrementa el rango.