Uso de Tablas en Excel - referencias estructuradas

El tema del uso de Tablas (Listas en Excel 2003) no es nuevo en este blog (un clic en la etiqueta Tablas/Listas en la nube de etiquetas les mostrará todas las notas). En mi opinión es una de las mejores innovaciones en Excel en los últimos años. Sin embargo no creo equivocarme si digo que relativamente pocos usuarios sacan provecho de esta herramienta.
Un posible motivo para esta reticencia sea la nueva sintaxis empleada para referenciar las celdas y rangos dentro de la tabla. Al principio esta sintaxis parece un tanto críptica para el usuario habituado a las referencias clásicas "columna/fila" (por ejemplo, A1).

Consideremos esta tabla de ventas por sucursal y zona

En la celda G4 usamos esta fórmula para extraer de la tabla las ventas de cierta zona

=SUMAR.SI($C$3:$C$12,G3,$D$3:$D$12)

Ahora convirtamos esta tabla en  una Tabla (cuando uso tabla con minúscula me refiero a un rango normal de datos) y veamos cómo aparecen las referencias en la fórmula

La fórmula aparece ahora así

=SUMAR.SI(TablaVentas[Zona],G3,TablaVentas[Ventas])

La sintaxis es evidente:

"TablaVentas" se refiere al nombre de la tabla

[Zona] es la referencia al campo (columna) que define la zona de cada sucursal; de la misma manera [Ventas] se refiere a la columna de las ventas.

Para referirnos a otros elementos de la tabla, como la fila de los encabezamientos, usamos distintas formas que resumo en esta tabla

Objeto	Sintaxis
Referencia a la tabla	=TablaVentas
La celda de la fila en la columna	=[@Ventas]
Fila de encabezados	TablaVentas[#Encabezados]
Toda la tabla	=SUMA(TablaVentas[#Todo])
Fila de totales	=TablaVentas[[#Totales],[Ventas]]

Una ventaja evidente de esta sintaxis es la claridad. No hace falta ver el rango referenciado para saber qué calcula la fórmula.
Esto nos permite crear fórmulas con mucha facilidad, como podemos ver

Al comenzar a escribir la fórmula Excel nos muestra las tablas como un rango definido; pero la mejor parte viene ahora

 Al poner el "["Excel nos muestra todos los rangos disponibles para nuestra fórmula. Así que si queremos calcular el total de ventas elegimos Ventas, ponemos el "]" para cerrar la definición del campo, cerramos el o los paréntesis necesarios y apretamos Enter. 
Para referirse a una celda dentro de la Tabla, Excel usa la notación [@nombre del campo]. Por ejemplo, si tenemos una columna de descuentos en nuestra tabla y queremos calcular las ventas neto

Más sobre la sintaxis y el uso de referencias  estructuradas pueden leer en esta nota del sitio Office de Microsoft.

Como sumar los ultimos n valores de un rango con celdas vacías

Aún no se ha secado la tinta de mi último post sobre la función INDICE y, por una de esas fantásticas casualidades, recibo esta consulta:

¿cómo puedo sumar los últimos tres valores de un rango que contiene celdas vacías?

La casualidad consiste en que la solución nos permitirá demostrar que INDICE da como resultado referencias a rangos y como ésto nos permite crear rangos dinámicos.

Supongamos estos datos

La idea es sumar los últimos tres valores de cada fila; en H2 debe ser 280, en H3 debe ser 130, etc. En el pasado he publicado un post sobre cálculos con los últimos n valores de un rango, pero esa técnica suponía que en el rango no había celdas vacías.

La solución es usar esta fórmula matricial (fórmulas que se ingresan apretando simultáneamente Ctrl-Mayúsuculas-Enter)

=SUMA(G2:INDICE(A2:G2,K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)))

Analizamos la fórmula de adentro hacia afuera:

La expresión COLUMNA(A2:G2)*(A2:G2<>"") produce un vector de valores númericos que pueden ser el número de columna o 0 si la celda del rango está vacía. Podemos mostrar el resultado de esta expresión en forma no matricial, es decir, aplicada a cada celda del rango

El vector de valores creado es el argumento de la expresión

K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)

que da como resultado el tercer valor en orden decreciente, es decir 3 (los valores del vector son 6,5,3,2,1 y 0).

Nuestra expresión se ha reducido ahora a INDICE(A2:G2,3) y aquí es donde podemos ver que INDICE da como resultado un rango, no un valor. El resultado de esta expresión es $C$2 y por lo tanto nuestra fórmula es ahora =SUMA(G2:$C$2), es decir =SUMA($C$2:$G$2), lo que nos da el resultado deseado.


De la misma manera podemos aplicar esta fórmula a las columnas en lugar de las filas, utilizando la función FILA() en lugar de la COLUMNA() y corrigiendo los rangos consecuentemente

En este video explico el funcionamiento de la fórmula usando la herramienta Auditoría de fórmula

La función INDICE, todo (o casi todo) lo que hay que saber

En la nota Traducción automática en hoja de Excel propuse dar un premio (el Planificador de Tareas JLD) a los primeros tres lectores que respondieron acertádamente a la pregunta qué tiene de particular la forma la forma en que se usa la función INDICE en el ejemplo de la nota.
Si bien hubo tres ganadores, ninguna respuesta fue totalmente exacta, lo que me impulsó a escribir esta nota.

La función INDICE es una de las más importantes y versátiles funciones del arsenal de Excel (y hay quien la corona como la más importante endilgándole superlatívamente el adjetivo "imponente").

La función INDICE permite encontrar el valor de un elemento dentro de una rango. Este rango puede ser un "vector" (un rango unidimensional de una fila como A2:M2 o de una columna como  A2:A10), o una matriz (un rango "rectangular", por ejemplo A2:M10). Para encontrar el elemento buscado debemos incluir uno o dos índices dependiendo se si trata de un vector o una matriz.

En este ejemplo

INDICE crea una referencia a la celda A2, cuyo valor es 40.  En este otro ejemplo

usamos la misma sintaxis a pesar de que el rango es "horizontal". En este caso INDICE crea una referencia a la celda B1.

Podemos concluir que:

• que cuando usamos vectores (rangos unidimensionales) INDICE interpreta el segundo argumento como fila o columna dependiendo de la orientación de vector

• cuando se refiere a un rango, la función INDICE crea una referencia a una celda (o celdas) en el rango.

Cuando usamos INDICE para referirnos a rangos bi-dimensionales, usamos los argumentos de fila y columna para referirnos al elemento buscado en la matriz

En este ejemplo INDICE crea una referencia a la celda B3.

Hasta aquí posiblemente no haya aportado nada dramático a los conocimientos de mis lectores. Ahora los invito a ver esta situación:

Cuando cuando falta el parámetro de fila, o es 0,  INDICE se refiere a toda la columna (en nuestro caso la columna B ya que el parámetro de columna es 2).  Pueden comprobarlo con

34+44+50+26+79+51+57 = 341

De la misma manera, si falta el parámetro columna, INDICE se refiere a toda la fila

47+50+18 = 115

Podemos aprovechar esta propiedad de INDICE para crear rangos dinámicos. La ventaja de usar INDICE en lugar de DESREF o INDIRECTO es que estas dos últimas son volátiles mientras que INDICE no lo es.
Un rango dinámico con INDICE se ve así

=A1:INDICE(A:A,CONTARA(A:A))

donde A1 es la primer celda del rango. Esta fórmula supone que no hay celdas vacías entre A1 y la última celda con valor de la columna A. Para usar esta fórmula como rango dinámica creamos un nombre que se refiera a la fórmula, por ejemplo el nombre "rango_dinamico"

Ahora podemos ver que a medida que agregamos valores en la columna a, el resultado en la celda C1 se va actualizando